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 2321: FROM csi_ii_relationships

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

Line 2345: FROM csi_ii_relationships

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

Line 3034: CSI_II_RELATIONSHIPS_PUB.create_relationship(

3030: l_csi_relationship_rec.position_reference := to_number(p_x_uc_instance_rec.relationship_id);
3031: l_csi_relationship_rec.subject_id := l_new_instance_id;
3032: l_csi_relationship_tbl(1) := l_csi_relationship_rec;
3033:
3034: CSI_II_RELATIONSHIPS_PUB.create_relationship(
3035: p_api_version => 1.0,
3036: p_relationship_tbl => l_csi_relationship_tbl,
3037: p_txn_rec => l_csi_transaction_rec,
3038: x_return_status => l_return_status,

Line 3328: l_position_reference csi_ii_relationships.position_reference%TYPE;

3324: l_subject_id NUMBER;
3325: l_object_id NUMBER;
3326: l_csi_relationship_id NUMBER;
3327: l_object_version_number NUMBER;
3328: l_position_reference csi_ii_relationships.position_reference%TYPE;
3329: l_mc_header_id NUMBER;
3330: l_sub_uc_header_id NUMBER;
3331: l_parent_relationship_id NUMBER;
3332: l_instance_type VARCHAR2(1);

Line 3402: FROM csi_ii_relationships

3398: object_version_number,
3399: object_id,
3400: subject_id,
3401: to_number(position_reference) position_id
3402: FROM csi_ii_relationships
3403: START WITH object_id = c_instance_id
3404: AND trunc(nvl(active_start_date, SYSDATE)) <= trunc(SYSDATE)
3405: AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE)
3406: CONNECT BY object_id = PRIOR subject_id

Line 3426: FROM csi_ii_relationships

3422:
3423: --Cursor to check whether c_parent_instance_id's child position c_relationship_id is empty
3424: CURSOR check_position_empty(c_parent_instance_id NUMBER, c_relationship_id NUMBER) IS
3425: SELECT subject_id
3426: FROM csi_ii_relationships
3427: WHERE object_id = c_parent_instance_id
3428: AND position_reference = to_char(c_relationship_id)
3429: AND subject_id IS NOT NULL
3430: AND relationship_type_code = 'COMPONENT-OF'

Line 3436: FROM csi_ii_relationships

3432: AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
3433:
3434: CURSOR check_instance_leaf(c_instance_id NUMBER) IS
3435: SELECT subject_id
3436: FROM csi_ii_relationships
3437: WHERE object_id = c_instance_id
3438: AND relationship_type_code = 'COMPONENT-OF'
3439: AND trunc(nvl(active_start_date, SYSDATE)) <= trunc(SYSDATE)
3440: AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);

Line 3454: FROM csi_ii_relationships

3450:
3451: CURSOR get_1st_level_subunits(c_instance_id NUMBER) IS
3452: /*This query is replaced by the query below it for performance gain.
3453: SELECT subject_id
3454: FROM csi_ii_relationships
3455: WHERE subject_id IN (SELECT csi_item_instance_id
3456: FROM ahl_unit_config_headers
3457: WHERE trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE))
3458: START WITH object_id = c_instance_id

Line 3468: FROM csi_ii_relationships

3464: AND trunc(nvl(active_start_date, SYSDATE)) <= trunc(SYSDATE)
3465: AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE)
3466: MINUS
3467: SELECT subject_id
3468: FROM csi_ii_relationships
3469: WHERE subject_id IN (SELECT csi_item_instance_id
3470: FROM ahl_unit_config_headers
3471: WHERE trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE))
3472: START WITH object_id IN (SELECT subject_id

Line 3473: FROM csi_ii_relationships

3469: WHERE subject_id IN (SELECT csi_item_instance_id
3470: FROM ahl_unit_config_headers
3471: WHERE trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE))
3472: START WITH object_id IN (SELECT subject_id
3473: FROM csi_ii_relationships
3474: WHERE subject_id IN (SELECT csi_item_instance_id
3475: FROM ahl_unit_config_headers
3476: WHERE trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE))
3477: START WITH object_id = c_instance_id

Line 3494: FROM csi_ii_relationships i

3490: AND trunc(nvl(active_start_date, SYSDATE)) <= trunc(SYSDATE)
3491: AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
3492: */
3493: SELECT i.subject_id
3494: FROM csi_ii_relationships i
3495: WHERE EXISTS (SELECT 'x'
3496: FROM ahl_unit_config_headers u
3497: WHERE u.csi_item_instance_id = i.subject_id
3498: AND trunc(nvl(u.active_end_date, SYSDATE+1)) > trunc(SYSDATE))

Line 3500: FROM csi_ii_relationships ci

3496: FROM ahl_unit_config_headers u
3497: WHERE u.csi_item_instance_id = i.subject_id
3498: AND trunc(nvl(u.active_end_date, SYSDATE+1)) > trunc(SYSDATE))
3499: AND NOT EXISTS (SELECT ci.object_id
3500: FROM csi_ii_relationships ci
3501: WHERE (EXISTS (SELECT 'x'
3502: FROM ahl_unit_config_headers ui
3503: WHERE ui.csi_item_instance_id = ci.object_id)
3504: AND ci.object_id <> c_instance_id)

Line 3554: FROM csi_ii_relationships

3550: AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
3551:
3552: CURSOR check_extra_node(c_object_id NUMBER, c_subject_id NUMBER) IS
3553: SELECT relationship_id, object_version_number
3554: FROM csi_ii_relationships
3555: WHERE object_id = c_object_id
3556: AND subject_id = c_subject_id
3557: AND position_reference IS NULL
3558: AND relationship_type_code = 'COMPONENT-OF'

Line 3565: FROM csi_ii_relationships

3561:
3562: -- SATHAPLI::FP ER 6504147, 18-Nov-2008
3563: CURSOR check_unasgnd_extra_node_csr(p_parent_instance_id NUMBER, p_instance_id NUMBER) IS
3564: SELECT relationship_id, object_version_number
3565: FROM csi_ii_relationships
3566: WHERE object_id IN (
3567: SELECT ii.object_id
3568: FROM csi_ii_relationships ii
3569: START WITH ii.subject_id = p_parent_instance_id

Line 3568: FROM csi_ii_relationships ii

3564: SELECT relationship_id, object_version_number
3565: FROM csi_ii_relationships
3566: WHERE object_id IN (
3567: SELECT ii.object_id
3568: FROM csi_ii_relationships ii
3569: START WITH ii.subject_id = p_parent_instance_id
3570: AND ii.relationship_type_code = 'COMPONENT-OF'
3571: AND trunc(nvl(ii.active_start_date, SYSDATE)) <= trunc(SYSDATE)
3572: AND trunc(nvl(ii.active_end_date, SYSDATE+1)) > trunc(SYSDATE)

Line 3593: FROM csi_ii_relationships

3589: AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
3590:
3591: CURSOR check_instance_installed(c_instance_id NUMBER) IS
3592: SELECT 'X'
3593: FROM csi_ii_relationships
3594: WHERE subject_id = c_instance_id
3595: AND position_reference IS NOT NULL
3596: --for extra node, it is still available for its sibling nodes even
3597: --if it is installed and not removed

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

3867: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3868: END IF;
3869:
3870: --Check the instance to be installed is a leaf node, branch node or sub-unit top node(in this
3871: --case, it might also be a leaf node in csi_ii_relationships if all of its descendants are empty)
3872: OPEN get_uc_header(p_instance_id);
3873: FETCH get_uc_header INTO l_sub_uc_header_id, l_mc_header_id;
3874: IF get_uc_header%FOUND THEN
3875: -- ACL :: R12 Changes

Line 4068: CSI_II_RELATIONSHIPS_PUB.update_relationship(

4064: l_csi_relationship_rec.object_id := p_parent_instance_id;
4065: l_csi_relationship_rec.subject_id := p_instance_id;
4066: l_csi_relationship_rec.position_reference := to_char(p_relationship_id);
4067: l_csi_relationship_tbl(1) := l_csi_relationship_rec;
4068: CSI_II_RELATIONSHIPS_PUB.update_relationship(
4069: p_api_version => 1.0,
4070: p_relationship_tbl => l_csi_relationship_tbl,
4071: p_txn_rec => l_csi_transaction_rec,
4072: x_return_status => l_return_status,

Line 4110: CSI_II_RELATIONSHIPS_PUB.expire_relationship(

4106: -- Set CSI relationship record
4107: l_csi_relationship_rec.relationship_id := l_csi_relationship_id;
4108: l_csi_relationship_rec.object_version_number := l_object_version_number;
4109:
4110: CSI_II_RELATIONSHIPS_PUB.expire_relationship(
4111: p_api_version => 1.0,
4112: p_relationship_rec => l_csi_relationship_rec,
4113: p_txn_rec => l_csi_transaction_rec,
4114: x_instance_id_lst => l_csi_instance_id_lst,

Line 4137: CSI_II_RELATIONSHIPS_PUB.create_relationship(

4133: l_csi_relationship_new_rec.object_id := p_parent_instance_id;
4134: l_csi_relationship_new_rec.subject_id := p_instance_id;
4135: l_csi_relationship_new_rec.position_reference := to_char(p_relationship_id);
4136: l_csi_relationship_tbl(1) := l_csi_relationship_new_rec;
4137: CSI_II_RELATIONSHIPS_PUB.create_relationship(
4138: p_api_version => 1.0,
4139: p_relationship_tbl => l_csi_relationship_tbl,
4140: p_txn_rec => l_csi_transaction_rec,
4141: x_return_status => l_return_status,

Line 4167: CSI_II_RELATIONSHIPS_PUB.create_relationship(

4163: l_csi_relationship_rec.object_id := p_parent_instance_id;
4164: l_csi_relationship_rec.subject_id := p_instance_id;
4165: l_csi_relationship_rec.position_reference := to_char(p_relationship_id);
4166: l_csi_relationship_tbl(1) := l_csi_relationship_rec;
4167: CSI_II_RELATIONSHIPS_PUB.create_relationship(
4168: p_api_version => 1.0,
4169: p_relationship_tbl => l_csi_relationship_tbl,
4170: p_txn_rec => l_csi_transaction_rec,
4171: x_return_status => l_return_status,

Line 4466: FROM csi_ii_relationships

4462: l_msg_data VARCHAR2(2000);
4463: l_relationship_id NUMBER;
4464: CURSOR check_relationship_id(c_subject_id NUMBER, c_relationship_id NUMBER) IS
4465: SELECT 'X'
4466: FROM csi_ii_relationships
4467: WHERE subject_id = c_subject_id
4468: AND position_reference = to_char(c_relationship_id)
4469: AND relationship_type_code = 'COMPONENT-OF'
4470: AND trunc(nvl(active_start_date, SYSDATE)) <= trunc(SYSDATE)

Line 4691: FROM csi_ii_relationships i1

4687: -- not installed in any position so far.
4688: --
4689: AND NOT EXISTS (
4690: SELECT 1
4691: FROM csi_ii_relationships i1
4692: WHERE i1.subject_id = C.instance_id
4693: AND i1.relationship_type_code = 'COMPONENT-OF'
4694: AND trunc(nvl(i1.active_start_date, SYSDATE)) <= trunc(SYSDATE)
4695: AND trunc(nvl(i1.active_end_date, SYSDATE+1)) >trunc(SYSDATE)

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

4936: );
4937: -- Cursor for checking parent instance.
4938: CURSOR check_parent_instance(c_instance_id NUMBER)
4939: IS
4940: --Parent instance could be either in ahl_unit_config_headers(top node) or in csi_ii_relationships
4941: --(as the subject_id)
4942: SELECT
4943: 'x'
4944: FROM

Line 4955: csi_ii_relationships

4951:
4952: SELECT
4953: 'x'
4954: FROM
4955: csi_ii_relationships
4956: WHERE
4957: subject_id = c_instance_id
4958: AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE)
4959: AND trunc(nvl(active_start_date, SYSDATE)) <= trunc(SYSDATE);

Line 4967: csi_ii_relationships

4963: IS
4964: SELECT
4965: object_id
4966: FROM
4967: csi_ii_relationships
4968: WHERE
4969: object_id NOT IN (SELECT subject_id
4970: FROM csi_ii_relationships
4971: WHERE relationship_type_code = 'COMPONENT-OF'

Line 4970: FROM csi_ii_relationships

4966: FROM
4967: csi_ii_relationships
4968: WHERE
4969: object_id NOT IN (SELECT subject_id
4970: FROM csi_ii_relationships
4971: WHERE relationship_type_code = 'COMPONENT-OF'
4972: AND trunc(nvl(active_start_date,SYSDATE)) <= trunc(SYSDATE)
4973: AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE))
4974: START WITH subject_id = c_instance_id

Line 5076: FROM csi_ii_relationships

5072: WHERE item_association_id = c_item_association_id;
5073:
5074: CURSOR get_csi_ii_relationship_ovn (c_instance_id NUMBER) IS
5075: SELECT object_version_number
5076: FROM csi_ii_relationships
5077: WHERE subject_id = c_instance_id
5078: AND position_reference IS NULL
5079: AND relationship_type_code = 'COMPONENT-OF'
5080: AND trunc(nvl(active_start_date,SYSDATE)) <= trunc(SYSDATE)

Line 5522: FROM csi_ii_relationships i2

5518: AND
5519: EXISTS
5520: (
5521: SELECT 1
5522: FROM csi_ii_relationships i2
5523: WHERE i2.subject_id = C.instance_id
5524: AND i2.position_reference IS NULL --because parent is not extra
5525: -- SATHAPLI::FP ER 6504147, 18-Nov-2008
5526: -- include extra nodes of all the parents uptill root

Line 5530: FROM csi_ii_relationships i3

5526: -- include extra nodes of all the parents uptill root
5527: -- AND i2.object_id = NVL(c_parent_instance_id, -1)
5528: AND i2.object_id IN (
5529: SELECT i3.object_id
5530: FROM csi_ii_relationships i3
5531: START WITH i3.subject_id = nvl(c_parent_instance_id, -1)
5532: AND i3.relationship_type_code = 'COMPONENT-OF'
5533: AND trunc(nvl(i3.active_start_date, SYSDATE)) <= trunc(SYSDATE)
5534: AND trunc(nvl(i3.active_end_date, SYSDATE+1)) > trunc(SYSDATE)

Line 5552: FROM csi_ii_relationships i1

5548: OR
5549: (NOT EXISTS
5550: (
5551: SELECT 1
5552: FROM csi_ii_relationships i1
5553: WHERE i1.subject_id = C.instance_id
5554: AND i1.relationship_type_code = 'COMPONENT-OF'
5555: AND trunc(nvl(i1.active_start_date, SYSDATE)) <= trunc(SYSDATE)
5556: AND trunc(nvl(i1.active_end_date, SYSDATE+1)) >trunc(SYSDATE)

Line 5606: FROM ahl_unit_config_headers UH, csi_ii_relationships CR

5602: UH.master_config_id mc_header_id,
5603: UH.unit_config_status_code uc_status_code,
5604: UH.active_end_date,
5605: CR.object_id parent_instance_id
5606: FROM ahl_unit_config_headers UH, csi_ii_relationships CR
5607: WHERE UH.csi_item_instance_id = CR.subject_id (+) AND
5608: CR.relationship_type_code (+) = 'COMPONENT-OF' AND
5609: trunc(nvl(CR.active_start_date (+), SYSDATE)) <= trunc(SYSDATE) AND
5610: trunc(nvl(CR.active_end_date (+), SYSDATE+1)) > trunc(SYSDATE)

Line 5659: FROM csi_ii_relationships i3

5655: -- include extra nodes of all the parents uptill root
5656: -- U.parent_instance_id = nvl(c_parent_instance_id, -1)
5657: U.parent_instance_id IN (
5658: SELECT i3.object_id
5659: FROM csi_ii_relationships i3
5660: START WITH i3.subject_id = nvl(c_parent_instance_id, -1)
5661: AND i3.relationship_type_code = 'COMPONENT-OF'
5662: AND trunc(nvl(i3.active_start_date, SYSDATE)) <= trunc(SYSDATE)
5663: AND trunc(nvl(i3.active_end_date, SYSDATE+1)) > trunc(SYSDATE)

Line 5674: FROM csi_ii_relationships CI

5670: FROM DUAL
5671: )
5672: AND EXISTS
5673: (SELECT 1
5674: FROM csi_ii_relationships CI
5675: WHERE CI.object_id = U.parent_instance_id
5676: AND CI.subject_id = U.csi_instance_id
5677: AND CI.position_reference IS NULL
5678: AND CI.relationship_type_code = 'COMPONENT-OF'

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

5808: WHERE workorder_id = c_workorder_id;
5809: l_wip_entity_id NUMBER;
5810:
5811: CURSOR check_parent_instance(c_instance_id NUMBER) IS
5812: --Parent instance could be either in ahl_unit_config_headers(top node) or in csi_ii_relationships
5813: --(as the subject_id)
5814: SELECT 'x'
5815: FROM ahl_unit_config_headers
5816: WHERE csi_item_instance_id = c_instance_id

Line 5820: FROM csi_ii_relationships

5816: WHERE csi_item_instance_id = c_instance_id
5817: AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE)
5818: UNION ALL
5819: SELECT 'x'
5820: FROM csi_ii_relationships
5821: WHERE subject_id = c_instance_id
5822: AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE)
5823: AND trunc(nvl(active_start_date, SYSDATE)) <= trunc(SYSDATE);
5824:

Line 5830: FROM csi_ii_relationships

5826:
5827: -- SATHAPLI::Bug#4912576 fix::SQL ID 14402149 --
5828: /*
5829: SELECT object_id
5830: FROM csi_ii_relationships
5831: WHERE object_id NOT IN (SELECT subject_id
5832: FROM csi_ii_relationships
5833: WHERE relationship_type_code = 'COMPONENT-OF'
5834: AND trunc(nvl(active_start_date,SYSDATE)) <= trunc(SYSDATE)

Line 5832: FROM csi_ii_relationships

5828: /*
5829: SELECT object_id
5830: FROM csi_ii_relationships
5831: WHERE object_id NOT IN (SELECT subject_id
5832: FROM csi_ii_relationships
5833: WHERE relationship_type_code = 'COMPONENT-OF'
5834: AND trunc(nvl(active_start_date,SYSDATE)) <= trunc(SYSDATE)
5835: AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE))
5836: START WITH subject_id = c_instance_id

Line 5846: FROM csi_ii_relationships co

5842: AND trunc(nvl(active_start_date,SYSDATE)) <= trunc(SYSDATE)
5843: AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
5844: */
5845: SELECT object_id
5846: FROM csi_ii_relationships co
5847: WHERE NOT EXISTS
5848: (
5849: SELECT 'X'
5850: FROM csi_ii_relationships ci

Line 5850: FROM csi_ii_relationships ci

5846: FROM csi_ii_relationships co
5847: WHERE NOT EXISTS
5848: (
5849: SELECT 'X'
5850: FROM csi_ii_relationships ci
5851: WHERE ci.relationship_type_code = 'COMPONENT-OF' AND
5852: ci.subject_id = co.object_id AND
5853: trunc(nvl(ci.active_start_date,SYSDATE)) <= trunc(SYSDATE) AND
5854: trunc(nvl(ci.active_end_date, SYSDATE+1)) > trunc(SYSDATE)

Line 5873: FROM csi_ii_relationships

5869: AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
5870:
5871: CURSOR get_csi_ii_relationship_ovn (c_instance_id NUMBER) IS
5872: SELECT object_version_number
5873: FROM csi_ii_relationships
5874: WHERE subject_id = c_instance_id
5875: AND position_reference IS NULL
5876: AND relationship_type_code = 'COMPONENT-OF'
5877: AND trunc(nvl(active_start_date,SYSDATE)) <= trunc(SYSDATE)

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

5931: l_wip_entity_id := NULL;
5932: END IF;
5933:
5934: --Validate p_parent_instance_id is Null(for top node) or existing in
5935: --ahl_unit_config_headers or csi_ii_relationships(for non-top node)
5936: IF p_parent_instance_id IS NOT NULL THEN
5937: OPEN check_parent_instance(p_parent_instance_id);
5938: FETCH check_parent_instance INTO l_dummy_char;
5939: IF check_parent_instance%NOTFOUND THEN

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

6586: l_csi_relationship_tbl(1) := l_csi_relationship_rec;
6587:
6588: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
6589: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_full_name,
6590: ' About to call CSI_II_RELATIONSHIPS_PUB.create_relationship');
6591: END IF;
6592:
6593: CSI_II_RELATIONSHIPS_PUB.create_relationship(
6594: p_api_version => 1.0,

Line 6593: CSI_II_RELATIONSHIPS_PUB.create_relationship(

6589: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_full_name,
6590: ' About to call CSI_II_RELATIONSHIPS_PUB.create_relationship');
6591: END IF;
6592:
6593: CSI_II_RELATIONSHIPS_PUB.create_relationship(
6594: p_api_version => 1.0,
6595: p_relationship_tbl => l_csi_relationship_tbl,
6596: p_txn_rec => l_csi_transaction_rec,
6597: x_return_status => l_return_status,

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

6599: x_msg_data => l_msg_data);
6600:
6601: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
6602: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_full_name,
6603: ' After call to CSI_II_RELATIONSHIPS_PUB.create_relationship'||
6604: ' l_return_status => '||l_return_status);
6605: END IF;
6606:
6607: IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN

Line 6765: FROM csi_ii_relationships CIR

6761: AND AIA.interchange_type_code IN ('1-WAY INTERCHANGEABLE', '2-WAY INTERCHANGEABLE')
6762: AND nvl(AIA.revision, nvl(CII.inventory_revision, -1)) = nvl(CII.inventory_revision, -1)
6763: -- Not an installed child instance
6764: AND (NOT EXISTS (SELECT 1
6765: FROM csi_ii_relationships CIR
6766: WHERE CIR.subject_id = CII.instance_id
6767: AND CIR.relationship_type_code = 'COMPONENT-OF'
6768: AND trunc(nvl(CIR.active_start_date, SYSDATE)) <= trunc(SYSDATE)
6769: AND trunc(nvl(CIR.active_end_date, SYSDATE+1)) >trunc(SYSDATE))

Line 6807: FROM ahl_unit_config_headers UH, csi_ii_relationships CR

6803: UH.master_config_id mc_header_id,
6804: UH.unit_config_status_code uc_status_code,
6805: UH.active_end_date,
6806: CR.object_id parent_instance_id
6807: FROM ahl_unit_config_headers UH, csi_ii_relationships CR
6808: WHERE UH.csi_item_instance_id = CR.subject_id (+) AND
6809: CR.relationship_type_code (+) = 'COMPONENT-OF' AND
6810: trunc(nvl(CR.active_start_date (+), SYSDATE)) <= trunc(SYSDATE) AND
6811: trunc(nvl(CR.active_end_date (+), SYSDATE+1)) > trunc(SYSDATE)

Line 7158: FROM ahl_unit_config_headers UH, csi_ii_relationships CR

7154: UH.master_config_id mc_header_id,
7155: UH.unit_config_status_code uc_status_code,
7156: UH.active_end_date,
7157: CR.object_id parent_instance_id
7158: FROM ahl_unit_config_headers UH, csi_ii_relationships CR
7159: WHERE UH.csi_item_instance_id = CR.subject_id (+) AND
7160: CR.relationship_type_code (+) = 'COMPONENT-OF' AND
7161: trunc(nvl(CR.active_start_date (+), SYSDATE)) <= trunc(SYSDATE) AND
7162: trunc(nvl(CR.active_end_date (+), SYSDATE+1)) > trunc(SYSDATE)