[Home] [Help]
339: l_child_inst_tbl t_id_tbl;
340: l_child_rel_tbl t_id_tbl;
341: l_child_matchflag_tbl t_flag_tbl;
342: l_child_partinfo_tbl t_partinfo_tbl;
343: l_pos_ref FND_LOOKUPS.meaning%TYPE;
344: l_root_mc_hdr_id NUMBER;
345: l_root_mc_part BOOLEAN;
346: l_root_ata_code AHL_MC_RELATIONSHIPS.ATA_CODE%TYPE; -- SATHAPLI::Enigma code changes, 02-Sep-2008
347:
399: --Given an relationship_id, get all of its immediate children from MC
400: --display_order will determine the sequence of installed and empty sibling nodes
401: -- rbhavsar::FP Bug# 6268202, performance tuning
402: -- Fetch the mc_header_id.
403: -- Fetch the position reference meaning and necessity from fnd_lookups too.
404: -- SATHAPLI::Enigma code changes, 02-Sep-2008 - fetch the ata_code as well
405: CURSOR get_mc_children(c_relationship_id NUMBER) IS
406: SELECT rel.mc_header_id,
407: rel.parent_relationship_id parent_rel_id,
408: rel.relationship_id,
409: rel.display_order,
410: rel.ata_code,
411: (SELECT fnd.meaning
412: FROM fnd_lookups fnd
413: WHERE fnd.lookup_type = 'AHL_POSITION_REFERENCE'
414: AND fnd.lookup_code = rel.position_ref_code
415: AND trunc(nvl(fnd.start_date_active, SYSDATE)) <= trunc(SYSDATE)
416: AND trunc(nvl(fnd.end_date_active, SYSDATE+1)) > trunc(SYSDATE)
415: AND trunc(nvl(fnd.start_date_active, SYSDATE)) <= trunc(SYSDATE)
416: AND trunc(nvl(fnd.end_date_active, SYSDATE+1)) > trunc(SYSDATE)
417: ) pos_ref_meaning,
418: (SELECT fnd.meaning
419: FROM fnd_lookups fnd
420: WHERE fnd.lookup_type = 'AHL_POSITION_NECESSITY'
421: AND fnd.lookup_code = rel.position_necessity_code
422: AND trunc(nvl(fnd.start_date_active, SYSDATE)) <= trunc(SYSDATE)
423: AND trunc(nvl(fnd.end_date_active, SYSDATE+1)) > trunc(SYSDATE)
438: AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
439:
440: --Given an relationship_id, get all of its descendants from MC
441: -- rbhavsar::FP Bug# 6268202, performance tuning
442: -- fetch the position reference meaning and necessity from fnd_lookups too
443: -- SATHAPLI::Enigma code changes, 02-Sep-2008 - fetch the ata_code as well
444: CURSOR get_mc_descendants(c_relationship_id NUMBER) IS
445: SELECT rel.parent_relationship_id parent_rel_id,
446: rel.relationship_id,
446: rel.relationship_id,
447: rel.display_order,
448: rel.ata_code,
449: (SELECT fnd.meaning
450: FROM fnd_lookups fnd
451: WHERE fnd.lookup_type = 'AHL_POSITION_REFERENCE'
452: AND fnd.lookup_code = rel.position_ref_code
453: AND trunc(nvl(fnd.start_date_active, SYSDATE)) <= trunc(SYSDATE)
454: AND trunc(nvl(fnd.end_date_active, SYSDATE+1)) > trunc(SYSDATE)
453: AND trunc(nvl(fnd.start_date_active, SYSDATE)) <= trunc(SYSDATE)
454: AND trunc(nvl(fnd.end_date_active, SYSDATE+1)) > trunc(SYSDATE)
455: ) pos_ref_meaning,
456: (SELECT fnd.meaning
457: FROM fnd_lookups fnd
458: WHERE fnd.lookup_type = 'AHL_POSITION_NECESSITY'
459: AND fnd.lookup_code = rel.position_necessity_code
460: AND trunc(nvl(fnd.start_date_active, SYSDATE)) <= trunc(SYSDATE)
461: AND trunc(nvl(fnd.end_date_active, SYSDATE+1)) > trunc(SYSDATE)
500: -- rbhavsar:: FP Bug# 6268202, performance tuning
501: -- Cursor to get the position reference for a given relationship id.
502: CURSOR get_pos_ref_csr(c_relationship_id NUMBER) IS
503: SELECT fnd.meaning
504: FROM ahl_mc_relationships rel, fnd_lookups fnd
505: WHERE rel.relationship_id = c_relationship_id AND
506: fnd.lookup_code = rel.position_ref_code AND
507: fnd.lookup_type = 'AHL_POSITION_REFERENCE' AND
508: TRUNC(NVL(fnd.start_date_active, SYSDATE)) <= TRUNC(SYSDATE) AND
574: l_get_mc_child get_mc_children%ROWTYPE;
575:
576: --Function to get position_necessity meaning for a given relationship_id
577: FUNCTION position_necessity(p_relationship_id NUMBER) RETURN VARCHAR2 IS
578: l_pos_necessity_meaning fnd_lookups.meaning%TYPE;
579: CURSOR get_position_necessity(c_relationship_id NUMBER) IS
580: SELECT F.meaning
581: FROM ahl_mc_relationships A,
582: fnd_lookup_values_vl F