[Home] [Help]
1712: is
1713: select 'X'
1714: FROM ahl_mc_relationships mcr,
1715: ahl_mc_headers_b mch,
1716: ahl_mc_path_position_nodes mcp
1717: WHERE mcp.mc_id = mch.mc_id
1718: AND mcp.SEQUENCE = ( SELECT MAX(SEQUENCE) FROM ahl_mc_path_position_nodes WHERE path_position_id = NVL(c_position_path_id,-1) )
1719: AND mch.mc_header_id = mcr.mc_header_id
1720: AND mch.version_number = NVL(mcp.version_number, mch.version_number)
1714: FROM ahl_mc_relationships mcr,
1715: ahl_mc_headers_b mch,
1716: ahl_mc_path_position_nodes mcp
1717: WHERE mcp.mc_id = mch.mc_id
1718: AND mcp.SEQUENCE = ( SELECT MAX(SEQUENCE) FROM ahl_mc_path_position_nodes WHERE path_position_id = NVL(c_position_path_id,-1) )
1719: AND mch.mc_header_id = mcr.mc_header_id
1720: AND mch.version_number = NVL(mcp.version_number, mch.version_number)
1721: AND mcp.position_key= mcr.position_key;
1722:
1722:
1723: CURSOR validate_eff_mc_id(c_position_path_id NUMBER)
1724: is
1725: SELECT 'X'
1726: FROM ahl_mc_path_position_nodes mcp ,
1727: ahl_route_effectivities re
1728: WHERE mcp.mc_id =re.mc_id
1729: AND mcp.path_position_id=NVL(c_position_path_id,-1) and mcp.sequence=0;
1730:
1736: mcp.path_position_id
1737: FROM
1738: ahl_mc_relationships mcr,
1739: ahl_mc_headers_b mch,
1740: ahl_mc_path_position_nodes mcp,
1741: ahl_route_effectivities re
1742: WHERE
1743: re.mc_id IS NOT NULL AND
1744: re.mc_id = mch.mc_id AND
1745: mch.mc_header_id =mcr.mc_header_id AND
1746: mch.mc_id = mcp.mc_id AND
1747: mch.version_number = NVL(mcp.version_number, mch.version_number) AND
1748: mcr.position_key = mcp.position_key AND
1749: mcp.SEQUENCE = ( SELECT MAX(SEQUENCE) FROM ahl_mc_path_position_nodes WHERE path_position_id = NVL(c_position_path_id,-1) ) AND
1750: mcp.path_position_id = NVL(c_position_path_id,-1);
1751:
1752: SELECT DISTINCT
1753: mcp.path_position_id
1753: mcp.path_position_id
1754: FROM
1755: ahl_mc_relationships mcr
1756: , ahl_mc_headers_b mch
1757: , ahl_mc_path_position_nodes mcp
1758: , AHL_ROUTE_EFFECTIVITIES_V re
1759: WHERE
1760: mch.mc_header_id = mcr.mc_header_id
1761: and mch.mc_id = mcp.mc_id
1761: and mch.mc_id = mcp.mc_id
1762: and re.MC_ID = mch.MC_ID
1763: and mch.version_number = nvl(mcp.version_number, mch.version_number)
1764: and mcr.position_key = mcp.position_key
1765: and mcp.sequence = (select max(sequence) from ahl_mc_path_position_nodes where path_position_id = nvl(c_position_path_id,-1))
1766: and mcp.path_position_id = nvl(c_position_path_id,-1);*/
1767:
1768:
1769: BEGIN
3944: fnd.MEANING
3945: FROM
3946: ahl_mc_relationships mcr
3947: , ahl_mc_headers_b mch
3948: , ahl_mc_path_position_nodes mcp
3949: , AHL_ROUTE_EFFECTIVITIES_V re
3950: , fnd_lookup_values_vl fnd
3951: , AHL_RT_OPER_MATERIALS AOB
3952: WHERE
3954: and mch.mc_id = mcp.mc_id
3955: and re.MC_ID = mch.MC_ID
3956: and mch.version_number = nvl(mcp.version_number, mch.version_number)
3957: and mcr.position_key = mcp.position_key
3958: and mcp.sequence = (select max(sequence) from ahl_mc_path_position_nodes where path_position_id = nvl(c_position_path_id,-1))
3959: and mcp.path_position_id = nvl(c_position_path_id,-1)
3960: and fnd.LOOKUP_TYPE = 'AHL_POSITION_REFERENCE'
3961: and fnd.LOOKUP_CODE = mcr.POSITION_REF_CODE
3962: ;
4072: CD.organization_code
4073: FROM
4074: ahl_mc_relationships mcr,
4075: ahl_mc_headers_b mch,
4076: ahl_mc_path_position_nodes mcp,
4077: ahl_item_associations_v igass ,
4078: AHL_ROUTE_EFFECTIVITIES re ,
4079: -- ahl_mtl_items_non_ou_v mtl,
4080: AHL_ITEM_COMP_V CD ,
4084: and mch.mc_id = mcp.mc_id
4085: and re.MC_ID = mch.MC_ID
4086: and mch.version_number = nvl(mcp.version_number, mch.version_number)
4087: and mcr.position_key = mcp.position_key
4088: and mcp.sequence = (select max(sequence) from ahl_mc_path_position_nodes where path_position_id = nvl(p_position_path_id,'-1'))
4089: and mcp.path_position_id = nvl(p_position_path_id,'-1')
4090: and mcr.item_group_id = igass.item_group_id
4091: and igass.INVENTORY_ITEM_ID = CD.inventory_item_id
4092: and igass.INVENTORY_ORG_ID = CD.inventory_master_org_id
4371: flv.lookup_code = amr.position_ref_code AND
4372: flv.meaning = c_mc_position and
4373: nvl(amr.parent_relationship_id, -1) not in (
4374: select rel.relationship_id
4375: from AHL_MC_PATH_POSITION_NODES ampp, AHL_MC_RULE_STATEMENTS amrs, ahl_mc_headers_b amh2,ahl_mc_rules_b mcrule,ahl_mc_relationships rel
4376: where ampp.mc_id = c_mc_id and
4377: ampp.path_position_node_id = amrs.subject_id and
4378: amrs.subject_type = 'POSITION' and
4379: amrs.operator = 'MUST_HAVE' and
4399: nvl(amr.active_end_date, SYSDATE + 1) > SYSDATE AND
4400: amr.position_key = c_mc_position_key and
4401: nvl(amr.parent_relationship_id, -1) not in (
4402: select rel.relationship_id
4403: from AHL_MC_PATH_POSITION_NODES ampp, AHL_MC_RULE_STATEMENTS amrs, ahl_mc_headers_b amh2,ahl_mc_rules_b mcrule,ahl_mc_relationships rel
4404: where ampp.mc_id = c_mc_id and
4405: ampp.path_position_node_id = amrs.subject_id and
4406: amrs.subject_type = 'POSITION' and
4407: amrs.operator = 'MUST_HAVE' and