eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.AHL_UC_INSTANCE_PVT dependencies on CSI_II_RELATIONSHIPS

Line 410: FROM csi_ii_relationships

406: SELECT relationship_id,
407: object_version_number,
408: object_id,
409: subject_id
410: FROM csi_ii_relationships
411: START WITH object_id = c_instance_id
412: AND trunc(nvl(active_start_date, SYSDATE)) <= trunc(SYSDATE)
413: AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE)
414: CONNECT BY object_id = PRIOR subject_id

Line 520: --Ensure no current user makes change to the same csi_ii_relationships record

516: FND_MESSAGE.set_token('NAME', 'instance_id');
517: FND_MESSAGE.set_token('VALUE', p_instance_id);
518: FND_MSG_PUB.add;
519: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
520: --Ensure no current user makes change to the same csi_ii_relationships record
521: ELSIF l_object_version_number <> p_csi_ii_ovn THEN
522: FND_MESSAGE.set_name( 'AHL','AHL_COM_RECORD_CHANGED' );
523: FND_MSG_PUB.add;
524: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;

Line 539: --The following lines are used to update the position_reference column in csi_ii_relationships

535: FND_MSG_PUB.add;
536: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
537: END IF;
538:
539: --The following lines are used to update the position_reference column in csi_ii_relationships
540: --First, get transaction_type_id .
541: AHL_UTIL_UC_PKG.getcsi_transaction_id('UC_UPDATE',l_transaction_type_id, l_return_value);
542: IF NOT l_return_value THEN
543: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;

Line 556: CSI_II_RELATIONSHIPS_PUB.update_relationship(

552: l_csi_relationship_rec.relationship_type_code := 'COMPONENT-OF';
553: l_csi_relationship_rec.object_id := l_object_id;
554: l_csi_relationship_rec.subject_id := l_subject_id;
555: l_csi_relationship_tbl(1) := l_csi_relationship_rec;
556: CSI_II_RELATIONSHIPS_PUB.update_relationship(
557: p_api_version => 1.0,
558: p_relationship_tbl => l_csi_relationship_tbl,
559: p_txn_rec => l_csi_transaction_rec,
560: x_return_status => l_return_status,

Line 720: l_position_reference csi_ii_relationships.position_reference%TYPE;

716: l_subject_id NUMBER;
717: l_object_id NUMBER;
718: l_csi_relationship_id NUMBER;
719: l_object_version_number NUMBER;
720: l_position_reference csi_ii_relationships.position_reference%TYPE;
721: l_position_necessity FND_LOOKUP_VALUES.lookup_code%TYPE;
722: l_transaction_type_id NUMBER;
723: l_return_value BOOLEAN;
724: l_dummy_num NUMBER;

Line 750: FROM csi_ii_relationships

746: object_version_number,
747: object_id,
748: subject_id,
749: to_number(position_reference) position_reference
750: FROM csi_ii_relationships
751: START WITH object_id = c_instance_id
752: AND trunc(nvl(active_start_date, SYSDATE)) <= trunc(SYSDATE)
753: AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE)
754: CONNECT BY object_id = PRIOR subject_id

Line 766: FROM csi_ii_relationships

762: AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
763:
764: CURSOR check_instance_non_leaf(c_instance_id NUMBER) IS
765: SELECT subject_id
766: FROM csi_ii_relationships
767: WHERE object_id = c_instance_id
768: AND relationship_type_code = 'COMPONENT-OF'
769: AND trunc(nvl(active_start_date, SYSDATE)) <= trunc(SYSDATE)
770: AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);

Line 790: FROM csi_ii_relationships

786: --(from the branch node's perspective)
787: CURSOR get_1st_level_subunits(c_instance_id NUMBER) IS
788: /*This query is replaced by the one below it for performance gain
789: SELECT subject_id
790: FROM csi_ii_relationships
791: WHERE subject_id IN (SELECT csi_item_instance_id
792: FROM ahl_unit_config_headers
793: WHERE trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE))
794: START WITH object_id = c_instance_id

Line 804: FROM csi_ii_relationships

800: AND trunc(nvl(active_start_date, SYSDATE)) <= trunc(SYSDATE)
801: AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE)
802: MINUS
803: SELECT subject_id
804: FROM csi_ii_relationships
805: WHERE subject_id IN (SELECT csi_item_instance_id
806: FROM ahl_unit_config_headers
807: WHERE trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE))
808: START WITH object_id IN (SELECT subject_id

Line 809: FROM csi_ii_relationships

805: WHERE subject_id IN (SELECT csi_item_instance_id
806: FROM ahl_unit_config_headers
807: WHERE trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE))
808: START WITH object_id IN (SELECT subject_id
809: FROM csi_ii_relationships
810: WHERE subject_id IN (SELECT csi_item_instance_id
811: FROM ahl_unit_config_headers
812: WHERE trunc(nvl(active_end_date,SYSDATE+1)) > trunc(SYSDATE))
813: START WITH object_id = c_instance_id

Line 830: FROM csi_ii_relationships i

826: AND trunc(nvl(active_start_date, SYSDATE)) <= trunc(SYSDATE)
827: AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
828: */
829: SELECT i.subject_id
830: FROM csi_ii_relationships i
831: WHERE EXISTS (SELECT 'x'
832: FROM ahl_unit_config_headers u
833: WHERE u.csi_item_instance_id = i.subject_id
834: AND trunc(nvl(u.active_end_date, SYSDATE+1)) > trunc(SYSDATE))

Line 836: FROM csi_ii_relationships ci

832: FROM ahl_unit_config_headers u
833: WHERE u.csi_item_instance_id = i.subject_id
834: AND trunc(nvl(u.active_end_date, SYSDATE+1)) > trunc(SYSDATE))
835: AND NOT EXISTS (SELECT ci.object_id
836: FROM csi_ii_relationships ci
837: WHERE (EXISTS (SELECT 'x'
838: FROM ahl_unit_config_headers ui
839: WHERE ui.csi_item_instance_id = ci.object_id)
840: AND ci.object_id <> c_instance_id)

Line 971: --Ensure no current user makes change to the same csi_ii_relationships record

967: FND_MESSAGE.set_token('NAME', 'instance_id');
968: FND_MESSAGE.set_token('VALUE', p_instance_id);
969: FND_MSG_PUB.add;
970: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
971: --Ensure no current user makes change to the same csi_ii_relationships record
972: ELSIF l_object_version_number <> p_csi_ii_ovn THEN
973: FND_MESSAGE.set_name( 'AHL','AHL_COM_RECORD_CHANGED' );
974: FND_MSG_PUB.add;
975: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;

Line 978: --The following lines are used to update the position_reference column in csi_ii_relationships

974: FND_MSG_PUB.add;
975: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
976: END IF;
977:
978: --The following lines are used to update the position_reference column in csi_ii_relationships
979: --First, get transaction_type_id .
980: AHL_UTIL_UC_PKG.getcsi_transaction_id('UC_UPDATE',l_transaction_type_id, l_return_value);
981: IF NOT l_return_value THEN
982: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;

Line 993: CSI_II_RELATIONSHIPS_PUB.expire_relationship(

989: --Set CSI relationship record
990: l_csi_relationship_rec.relationship_id := l_csi_relationship_id;
991: l_csi_relationship_rec.object_version_number := l_object_version_number;
992:
993: CSI_II_RELATIONSHIPS_PUB.expire_relationship(
994: p_api_version => 1.0,
995: p_relationship_rec => l_csi_relationship_rec,
996: p_txn_rec => l_csi_transaction_rec,
997: x_instance_id_lst => l_csi_instance_id_lst,

Line 1297: l_position_reference csi_ii_relationships.position_reference%TYPE;

1293: l_subject_id NUMBER;
1294: l_object_id NUMBER;
1295: l_csi_relationship_id NUMBER;
1296: l_object_version_number NUMBER;
1297: l_position_reference csi_ii_relationships.position_reference%TYPE;
1298: l_transaction_type_id NUMBER;
1299: l_dummy NUMBER;
1300: l_uc_status_code VARCHAR2(30);
1301: l_active_uc_status_code VARCHAR2(30);

Line 1361: FROM csi_ii_relationships

1357: object_version_number,
1358: object_id,
1359: subject_id,
1360: position_reference
1361: FROM csi_ii_relationships
1362: START WITH object_id = c_instance_id
1363: AND trunc(nvl(active_start_date, SYSDATE)) <= trunc(SYSDATE)
1364: AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE)
1365: CONNECT BY object_id = PRIOR subject_id

Line 2252: l_position_reference csi_ii_relationships.position_reference%TYPE;

2248: l_subject_id NUMBER;
2249: l_object_id NUMBER;
2250: l_csi_relationship_id NUMBER;
2251: l_object_version_number NUMBER;
2252: l_position_reference csi_ii_relationships.position_reference%TYPE;
2253: l_sub_mc_header_id NUMBER := NULL;
2254: l_top_relationship_id NUMBER := NULL;
2255: l_position_id NUMBER := NULL;
2256: l_parent_relationship_id NUMBER;

Line 2320: FROM csi_ii_relationships

2316: object_version_number,
2317: object_id,
2318: subject_id,
2319: to_number(position_reference) position_id
2320: FROM csi_ii_relationships
2321: START WITH object_id = c_instance_id
2322: AND trunc(nvl(active_start_date, SYSDATE)) <= trunc(SYSDATE)
2323: AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE)
2324: CONNECT BY object_id = PRIOR subject_id

Line 2344: FROM csi_ii_relationships

2340:
2341: --Cursor to check whether c_parent_instance_id's child position c_relationship_id is empty
2342: CURSOR check_position_empty(c_parent_instance_id NUMBER, c_relationship_id NUMBER) IS
2343: SELECT subject_id
2344: FROM csi_ii_relationships
2345: WHERE object_id = c_parent_instance_id
2346: AND position_reference = to_char(c_relationship_id)
2347: AND subject_id IS NOT NULL
2348: AND relationship_type_code = 'COMPONENT-OF'

Line 3028: CSI_II_RELATIONSHIPS_PUB.create_relationship(

3024: l_csi_relationship_rec.position_reference := to_number(p_x_uc_instance_rec.relationship_id);
3025: l_csi_relationship_rec.subject_id := l_new_instance_id;
3026: l_csi_relationship_tbl(1) := l_csi_relationship_rec;
3027:
3028: CSI_II_RELATIONSHIPS_PUB.create_relationship(
3029: p_api_version => 1.0,
3030: p_relationship_tbl => l_csi_relationship_tbl,
3031: p_txn_rec => l_csi_transaction_rec,
3032: x_return_status => l_return_status,

Line 3318: l_position_reference csi_ii_relationships.position_reference%TYPE;

3314: l_subject_id NUMBER;
3315: l_object_id NUMBER;
3316: l_csi_relationship_id NUMBER;
3317: l_object_version_number NUMBER;
3318: l_position_reference csi_ii_relationships.position_reference%TYPE;
3319: l_mc_header_id NUMBER;
3320: l_sub_uc_header_id NUMBER;
3321: l_parent_relationship_id NUMBER;
3322: l_instance_type VARCHAR2(1);

Line 3391: FROM csi_ii_relationships

3387: object_version_number,
3388: object_id,
3389: subject_id,
3390: to_number(position_reference) position_id
3391: FROM csi_ii_relationships
3392: START WITH object_id = c_instance_id
3393: AND trunc(nvl(active_start_date, SYSDATE)) <= trunc(SYSDATE)
3394: AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE)
3395: CONNECT BY object_id = PRIOR subject_id

Line 3415: FROM csi_ii_relationships

3411:
3412: --Cursor to check whether c_parent_instance_id's child position c_relationship_id is empty
3413: CURSOR check_position_empty(c_parent_instance_id NUMBER, c_relationship_id NUMBER) IS
3414: SELECT subject_id
3415: FROM csi_ii_relationships
3416: WHERE object_id = c_parent_instance_id
3417: AND position_reference = to_char(c_relationship_id)
3418: AND subject_id IS NOT NULL
3419: AND relationship_type_code = 'COMPONENT-OF'

Line 3425: FROM csi_ii_relationships

3421: AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
3422:
3423: CURSOR check_instance_leaf(c_instance_id NUMBER) IS
3424: SELECT subject_id
3425: FROM csi_ii_relationships
3426: WHERE object_id = c_instance_id
3427: AND relationship_type_code = 'COMPONENT-OF'
3428: AND trunc(nvl(active_start_date, SYSDATE)) <= trunc(SYSDATE)
3429: AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);

Line 3443: FROM csi_ii_relationships

3439:
3440: CURSOR get_1st_level_subunits(c_instance_id NUMBER) IS
3441: /*This query is replaced by the query below it for performance gain.
3442: SELECT subject_id
3443: FROM csi_ii_relationships
3444: WHERE subject_id IN (SELECT csi_item_instance_id
3445: FROM ahl_unit_config_headers
3446: WHERE trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE))
3447: START WITH object_id = c_instance_id

Line 3457: FROM csi_ii_relationships

3453: AND trunc(nvl(active_start_date, SYSDATE)) <= trunc(SYSDATE)
3454: AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE)
3455: MINUS
3456: SELECT subject_id
3457: FROM csi_ii_relationships
3458: WHERE subject_id IN (SELECT csi_item_instance_id
3459: FROM ahl_unit_config_headers
3460: WHERE trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE))
3461: START WITH object_id IN (SELECT subject_id

Line 3462: FROM csi_ii_relationships

3458: WHERE subject_id IN (SELECT csi_item_instance_id
3459: FROM ahl_unit_config_headers
3460: WHERE trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE))
3461: START WITH object_id IN (SELECT subject_id
3462: FROM csi_ii_relationships
3463: WHERE subject_id IN (SELECT csi_item_instance_id
3464: FROM ahl_unit_config_headers
3465: WHERE trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE))
3466: START WITH object_id = c_instance_id

Line 3483: FROM csi_ii_relationships i

3479: AND trunc(nvl(active_start_date, SYSDATE)) <= trunc(SYSDATE)
3480: AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
3481: */
3482: SELECT i.subject_id
3483: FROM csi_ii_relationships i
3484: WHERE EXISTS (SELECT 'x'
3485: FROM ahl_unit_config_headers u
3486: WHERE u.csi_item_instance_id = i.subject_id
3487: AND trunc(nvl(u.active_end_date, SYSDATE+1)) > trunc(SYSDATE))

Line 3489: FROM csi_ii_relationships ci

3485: FROM ahl_unit_config_headers u
3486: WHERE u.csi_item_instance_id = i.subject_id
3487: AND trunc(nvl(u.active_end_date, SYSDATE+1)) > trunc(SYSDATE))
3488: AND NOT EXISTS (SELECT ci.object_id
3489: FROM csi_ii_relationships ci
3490: WHERE (EXISTS (SELECT 'x'
3491: FROM ahl_unit_config_headers ui
3492: WHERE ui.csi_item_instance_id = ci.object_id)
3493: AND ci.object_id <> c_instance_id)

Line 3543: FROM csi_ii_relationships

3539: AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
3540:
3541: CURSOR check_extra_node(c_object_id NUMBER, c_subject_id NUMBER) IS
3542: SELECT relationship_id, object_version_number
3543: FROM csi_ii_relationships
3544: WHERE object_id = c_object_id
3545: AND subject_id = c_subject_id
3546: AND position_reference IS NULL
3547: AND relationship_type_code = 'COMPONENT-OF'

Line 3554: FROM csi_ii_relationships

3550:
3551: -- SATHAPLI::FP ER 6504147, 18-Nov-2008
3552: CURSOR check_unasgnd_extra_node_csr(p_parent_instance_id NUMBER, p_instance_id NUMBER) IS
3553: SELECT relationship_id, object_version_number
3554: FROM csi_ii_relationships
3555: WHERE object_id IN (
3556: SELECT ii.object_id
3557: FROM csi_ii_relationships ii
3558: START WITH ii.subject_id = p_parent_instance_id

Line 3557: FROM csi_ii_relationships ii

3553: SELECT relationship_id, object_version_number
3554: FROM csi_ii_relationships
3555: WHERE object_id IN (
3556: SELECT ii.object_id
3557: FROM csi_ii_relationships ii
3558: START WITH ii.subject_id = p_parent_instance_id
3559: AND ii.relationship_type_code = 'COMPONENT-OF'
3560: AND trunc(nvl(ii.active_start_date, SYSDATE)) <= trunc(SYSDATE)
3561: AND trunc(nvl(ii.active_end_date, SYSDATE+1)) > trunc(SYSDATE)

Line 3582: FROM csi_ii_relationships

3578: AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
3579:
3580: CURSOR check_instance_installed(c_instance_id NUMBER) IS
3581: SELECT 'X'
3582: FROM csi_ii_relationships
3583: WHERE subject_id = c_instance_id
3584: AND position_reference IS NOT NULL
3585: --for extra node, it is still available for its sibling nodes even
3586: --if it is installed and not removed

Line 3855: --case, it might also be a leaf node in csi_ii_relationships if all of its descendants are empty)

3851: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3852: END IF;
3853:
3854: --Check the instance to be installed is a leaf node, branch node or sub-unit top node(in this
3855: --case, it might also be a leaf node in csi_ii_relationships if all of its descendants are empty)
3856: OPEN get_uc_header(p_instance_id);
3857: FETCH get_uc_header INTO l_sub_uc_header_id, l_mc_header_id;
3858: IF get_uc_header%FOUND THEN
3859: -- ACL :: R12 Changes

Line 4052: CSI_II_RELATIONSHIPS_PUB.update_relationship(

4048: l_csi_relationship_rec.object_id := p_parent_instance_id;
4049: l_csi_relationship_rec.subject_id := p_instance_id;
4050: l_csi_relationship_rec.position_reference := to_char(p_relationship_id);
4051: l_csi_relationship_tbl(1) := l_csi_relationship_rec;
4052: CSI_II_RELATIONSHIPS_PUB.update_relationship(
4053: p_api_version => 1.0,
4054: p_relationship_tbl => l_csi_relationship_tbl,
4055: p_txn_rec => l_csi_transaction_rec,
4056: x_return_status => l_return_status,

Line 4094: CSI_II_RELATIONSHIPS_PUB.expire_relationship(

4090: -- Set CSI relationship record
4091: l_csi_relationship_rec.relationship_id := l_csi_relationship_id;
4092: l_csi_relationship_rec.object_version_number := l_object_version_number;
4093:
4094: CSI_II_RELATIONSHIPS_PUB.expire_relationship(
4095: p_api_version => 1.0,
4096: p_relationship_rec => l_csi_relationship_rec,
4097: p_txn_rec => l_csi_transaction_rec,
4098: x_instance_id_lst => l_csi_instance_id_lst,

Line 4121: CSI_II_RELATIONSHIPS_PUB.create_relationship(

4117: l_csi_relationship_new_rec.object_id := p_parent_instance_id;
4118: l_csi_relationship_new_rec.subject_id := p_instance_id;
4119: l_csi_relationship_new_rec.position_reference := to_char(p_relationship_id);
4120: l_csi_relationship_tbl(1) := l_csi_relationship_new_rec;
4121: CSI_II_RELATIONSHIPS_PUB.create_relationship(
4122: p_api_version => 1.0,
4123: p_relationship_tbl => l_csi_relationship_tbl,
4124: p_txn_rec => l_csi_transaction_rec,
4125: x_return_status => l_return_status,

Line 4151: CSI_II_RELATIONSHIPS_PUB.create_relationship(

4147: l_csi_relationship_rec.object_id := p_parent_instance_id;
4148: l_csi_relationship_rec.subject_id := p_instance_id;
4149: l_csi_relationship_rec.position_reference := to_char(p_relationship_id);
4150: l_csi_relationship_tbl(1) := l_csi_relationship_rec;
4151: CSI_II_RELATIONSHIPS_PUB.create_relationship(
4152: p_api_version => 1.0,
4153: p_relationship_tbl => l_csi_relationship_tbl,
4154: p_txn_rec => l_csi_transaction_rec,
4155: x_return_status => l_return_status,

Line 4444: FROM csi_ii_relationships

4440: l_msg_data VARCHAR2(2000);
4441: l_relationship_id NUMBER;
4442: CURSOR check_relationship_id(c_subject_id NUMBER, c_relationship_id NUMBER) IS
4443: SELECT 'X'
4444: FROM csi_ii_relationships
4445: WHERE subject_id = c_subject_id
4446: AND position_reference = to_char(c_relationship_id)
4447: AND relationship_type_code = 'COMPONENT-OF'
4448: AND trunc(nvl(active_start_date, SYSDATE)) <= trunc(SYSDATE)

Line 4669: FROM csi_ii_relationships i1

4665: -- not installed in any position so far.
4666: --
4667: AND NOT EXISTS (
4668: SELECT 1
4669: FROM csi_ii_relationships i1
4670: WHERE i1.subject_id = C.instance_id
4671: AND i1.relationship_type_code = 'COMPONENT-OF'
4672: AND trunc(nvl(i1.active_start_date, SYSDATE)) <= trunc(SYSDATE)
4673: AND trunc(nvl(i1.active_end_date, SYSDATE+1)) >trunc(SYSDATE)

Line 4881: --Parent instance could be either in ahl_unit_config_headers(top node) or in csi_ii_relationships

4877: );
4878: -- Cursor for checking parent instance.
4879: CURSOR check_parent_instance(c_instance_id NUMBER)
4880: IS
4881: --Parent instance could be either in ahl_unit_config_headers(top node) or in csi_ii_relationships
4882: --(as the subject_id)
4883: SELECT
4884: 'x'
4885: FROM

Line 4896: csi_ii_relationships

4892:
4893: SELECT
4894: 'x'
4895: FROM
4896: csi_ii_relationships
4897: WHERE
4898: subject_id = c_instance_id
4899: AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE)
4900: AND trunc(nvl(active_start_date, SYSDATE)) <= trunc(SYSDATE);

Line 4908: csi_ii_relationships

4904: IS
4905: SELECT
4906: object_id
4907: FROM
4908: csi_ii_relationships
4909: WHERE
4910: object_id NOT IN (SELECT subject_id
4911: FROM csi_ii_relationships
4912: WHERE relationship_type_code = 'COMPONENT-OF'

Line 4911: FROM csi_ii_relationships

4907: FROM
4908: csi_ii_relationships
4909: WHERE
4910: object_id NOT IN (SELECT subject_id
4911: FROM csi_ii_relationships
4912: WHERE relationship_type_code = 'COMPONENT-OF'
4913: AND trunc(nvl(active_start_date,SYSDATE)) <= trunc(SYSDATE)
4914: AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE))
4915: START WITH subject_id = c_instance_id

Line 4981: FROM csi_ii_relationships

4977: WHERE item_association_id = c_item_association_id;
4978:
4979: CURSOR get_csi_ii_relationship_ovn (c_instance_id NUMBER) IS
4980: SELECT object_version_number
4981: FROM csi_ii_relationships
4982: WHERE subject_id = c_instance_id
4983: AND position_reference IS NULL
4984: AND relationship_type_code = 'COMPONENT-OF'
4985: AND trunc(nvl(active_start_date,SYSDATE)) <= trunc(SYSDATE)

Line 5417: FROM csi_ii_relationships i2

5413: AND
5414: EXISTS
5415: (
5416: SELECT 1
5417: FROM csi_ii_relationships i2
5418: WHERE i2.subject_id = C.instance_id
5419: AND i2.position_reference IS NULL --because parent is not extra
5420: -- SATHAPLI::FP ER 6504147, 18-Nov-2008
5421: -- include extra nodes of all the parents uptill root

Line 5425: FROM csi_ii_relationships i3

5421: -- include extra nodes of all the parents uptill root
5422: -- AND i2.object_id = NVL(c_parent_instance_id, -1)
5423: AND i2.object_id IN (
5424: SELECT i3.object_id
5425: FROM csi_ii_relationships i3
5426: START WITH i3.subject_id = nvl(c_parent_instance_id, -1)
5427: AND i3.relationship_type_code = 'COMPONENT-OF'
5428: AND trunc(nvl(i3.active_start_date, SYSDATE)) <= trunc(SYSDATE)
5429: AND trunc(nvl(i3.active_end_date, SYSDATE+1)) > trunc(SYSDATE)

Line 5447: FROM csi_ii_relationships i1

5443: OR
5444: (NOT EXISTS
5445: (
5446: SELECT 1
5447: FROM csi_ii_relationships i1
5448: WHERE i1.subject_id = C.instance_id
5449: AND i1.relationship_type_code = 'COMPONENT-OF'
5450: AND trunc(nvl(i1.active_start_date, SYSDATE)) <= trunc(SYSDATE)
5451: AND trunc(nvl(i1.active_end_date, SYSDATE+1)) >trunc(SYSDATE)

Line 5501: FROM ahl_unit_config_headers UH, csi_ii_relationships CR

5497: UH.master_config_id mc_header_id,
5498: UH.unit_config_status_code uc_status_code,
5499: UH.active_end_date,
5500: CR.object_id parent_instance_id
5501: FROM ahl_unit_config_headers UH, csi_ii_relationships CR
5502: WHERE UH.csi_item_instance_id = CR.subject_id (+) AND
5503: CR.relationship_type_code (+) = 'COMPONENT-OF' AND
5504: trunc(nvl(CR.active_start_date (+), SYSDATE)) <= trunc(SYSDATE) AND
5505: trunc(nvl(CR.active_end_date (+), SYSDATE+1)) > trunc(SYSDATE)

Line 5552: FROM csi_ii_relationships i3

5548: -- include extra nodes of all the parents uptill root
5549: -- U.parent_instance_id = nvl(c_parent_instance_id, -1)
5550: U.parent_instance_id IN (
5551: SELECT i3.object_id
5552: FROM csi_ii_relationships i3
5553: START WITH i3.subject_id = nvl(c_parent_instance_id, -1)
5554: AND i3.relationship_type_code = 'COMPONENT-OF'
5555: AND trunc(nvl(i3.active_start_date, SYSDATE)) <= trunc(SYSDATE)
5556: AND trunc(nvl(i3.active_end_date, SYSDATE+1)) > trunc(SYSDATE)

Line 5567: FROM csi_ii_relationships CI

5563: FROM DUAL
5564: )
5565: AND EXISTS
5566: (SELECT 1
5567: FROM csi_ii_relationships CI
5568: WHERE CI.object_id = U.parent_instance_id
5569: AND CI.subject_id = U.csi_instance_id
5570: AND CI.position_reference IS NULL
5571: AND CI.relationship_type_code = 'COMPONENT-OF'

Line 5705: --Parent instance could be either in ahl_unit_config_headers(top node) or in csi_ii_relationships

5701: WHERE workorder_id = c_workorder_id;
5702: l_wip_entity_id NUMBER;
5703:
5704: CURSOR check_parent_instance(c_instance_id NUMBER) IS
5705: --Parent instance could be either in ahl_unit_config_headers(top node) or in csi_ii_relationships
5706: --(as the subject_id)
5707: SELECT 'x'
5708: FROM ahl_unit_config_headers
5709: WHERE csi_item_instance_id = c_instance_id

Line 5713: FROM csi_ii_relationships

5709: WHERE csi_item_instance_id = c_instance_id
5710: AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE)
5711: UNION ALL
5712: SELECT 'x'
5713: FROM csi_ii_relationships
5714: WHERE subject_id = c_instance_id
5715: AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE)
5716: AND trunc(nvl(active_start_date, SYSDATE)) <= trunc(SYSDATE);
5717:

Line 5723: FROM csi_ii_relationships

5719:
5720: -- SATHAPLI::Bug#4912576 fix::SQL ID 14402149 --
5721: /*
5722: SELECT object_id
5723: FROM csi_ii_relationships
5724: WHERE object_id NOT IN (SELECT subject_id
5725: FROM csi_ii_relationships
5726: WHERE relationship_type_code = 'COMPONENT-OF'
5727: AND trunc(nvl(active_start_date,SYSDATE)) <= trunc(SYSDATE)

Line 5725: FROM csi_ii_relationships

5721: /*
5722: SELECT object_id
5723: FROM csi_ii_relationships
5724: WHERE object_id NOT IN (SELECT subject_id
5725: FROM csi_ii_relationships
5726: WHERE relationship_type_code = 'COMPONENT-OF'
5727: AND trunc(nvl(active_start_date,SYSDATE)) <= trunc(SYSDATE)
5728: AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE))
5729: START WITH subject_id = c_instance_id

Line 5739: FROM csi_ii_relationships co

5735: AND trunc(nvl(active_start_date,SYSDATE)) <= trunc(SYSDATE)
5736: AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
5737: */
5738: SELECT object_id
5739: FROM csi_ii_relationships co
5740: WHERE NOT EXISTS
5741: (
5742: SELECT 'X'
5743: FROM csi_ii_relationships ci

Line 5743: FROM csi_ii_relationships ci

5739: FROM csi_ii_relationships co
5740: WHERE NOT EXISTS
5741: (
5742: SELECT 'X'
5743: FROM csi_ii_relationships ci
5744: WHERE ci.relationship_type_code = 'COMPONENT-OF' AND
5745: ci.subject_id = co.object_id AND
5746: trunc(nvl(ci.active_start_date,SYSDATE)) <= trunc(SYSDATE) AND
5747: trunc(nvl(ci.active_end_date, SYSDATE+1)) > trunc(SYSDATE)

Line 5766: FROM csi_ii_relationships

5762: AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
5763:
5764: CURSOR get_csi_ii_relationship_ovn (c_instance_id NUMBER) IS
5765: SELECT object_version_number
5766: FROM csi_ii_relationships
5767: WHERE subject_id = c_instance_id
5768: AND position_reference IS NULL
5769: AND relationship_type_code = 'COMPONENT-OF'
5770: AND trunc(nvl(active_start_date,SYSDATE)) <= trunc(SYSDATE)

Line 5828: --ahl_unit_config_headers or csi_ii_relationships(for non-top node)

5824: l_wip_entity_id := NULL;
5825: END IF;
5826:
5827: --Validate p_parent_instance_id is Null(for top node) or existing in
5828: --ahl_unit_config_headers or csi_ii_relationships(for non-top node)
5829: IF p_parent_instance_id IS NOT NULL THEN
5830: OPEN check_parent_instance(p_parent_instance_id);
5831: FETCH check_parent_instance INTO l_dummy_char;
5832: IF check_parent_instance%NOTFOUND THEN

Line 6483: ' About to call CSI_II_RELATIONSHIPS_PUB.create_relationship');

6479: l_csi_relationship_tbl(1) := l_csi_relationship_rec;
6480:
6481: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
6482: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_full_name,
6483: ' About to call CSI_II_RELATIONSHIPS_PUB.create_relationship');
6484: END IF;
6485:
6486: CSI_II_RELATIONSHIPS_PUB.create_relationship(
6487: p_api_version => 1.0,

Line 6486: CSI_II_RELATIONSHIPS_PUB.create_relationship(

6482: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_full_name,
6483: ' About to call CSI_II_RELATIONSHIPS_PUB.create_relationship');
6484: END IF;
6485:
6486: CSI_II_RELATIONSHIPS_PUB.create_relationship(
6487: p_api_version => 1.0,
6488: p_relationship_tbl => l_csi_relationship_tbl,
6489: p_txn_rec => l_csi_transaction_rec,
6490: x_return_status => l_return_status,

Line 6496: ' After call to CSI_II_RELATIONSHIPS_PUB.create_relationship'||

6492: x_msg_data => l_msg_data);
6493:
6494: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
6495: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_full_name,
6496: ' After call to CSI_II_RELATIONSHIPS_PUB.create_relationship'||
6497: ' l_return_status => '||l_return_status);
6498: END IF;
6499:
6500: IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN