[Home] [Help]
1545: WHERE SIMULATION_PLAN_ID = p_simulation_plan_id
1546: AND UNIT_CONFIG_HEADER_ID NOT IN
1547: (SELECT DISTINCT FU.UNIT_CONFIG_HEADER_ID
1548: FROM AHL_FLEET_UNIT_ASSOCS FU,
1549: AHL_UNIT_CONFIG_HEADERS UC
1550: WHERE SIMULATION_PLAN_ID = p_simulation_plan_id
1551: AND UC.UNIT_CONFIG_HEADER_ID = FU.UNIT_CONFIG_HEADER_ID
1552: AND ahl_util_uc_pkg.get_uc_status_code(UC.UNIT_CONFIG_HEADER_ID) NOT IN ('DRAFT', 'EXPIRED')
1553: );
1660: -- To get unit config id.
1661: CURSOR ahl_unit_config_header_csr (p_item_instance_id IN NUMBER) IS
1662: SELECT name, active_start_date, active_end_date, master_config_id, unit_config_header_id,
1663: unit_config_status_code
1664: FROM ahl_unit_config_headers
1665: WHERE csi_item_instance_id = p_item_instance_id
1666: --AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate) AND
1667: -- trunc(sysdate) < trunc(nvl(active_end_date,sysdate+1))
1668: AND parent_uc_header_id IS NULL;
1666: --AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate) AND
1667: -- trunc(sysdate) < trunc(nvl(active_end_date,sysdate+1))
1668: AND parent_uc_header_id IS NULL;
1669:
1670: l_name ahl_unit_config_headers.name%TYPE;
1671: l_active_start_date DATE;
1672: l_active_end_date DATE;
1673: l_master_config_id NUMBER;
1674: l_unit_config_header_id NUMBER;
12796: -- installed on a instance which is not a UC.
12797: -- see second issue reported in bug# 8326078
12798: CURSOR ahl_unit_config_header_csr IS
12799: SELECT csi_item_instance_id
12800: FROM ahl_unit_config_headers uc, csi_item_instances ii
12801: WHERE uc.csi_item_instance_id = ii.instance_id
12802: AND trunc(nvl(uc.active_start_date,sysdate)) <= trunc(sysdate)
12803: AND trunc(sysdate) < trunc(nvl(uc.active_end_date,sysdate+1))
12804: AND trunc(nvl(ii.active_start_date,sysdate)) <= trunc(sysdate)
12818: -- installed on a instance which is not a UC.
12819: -- see second issue reported in bug# 8326078
12820: CURSOR ahl_unit_itemcat_csr(p_mtl_category_id IN NUMBER) IS
12821: SELECT csi_item_instance_id
12822: FROM ahl_unit_config_headers uc, csi_item_instances ii
12823: WHERE uc.csi_item_instance_id = ii.instance_id
12824: AND trunc(nvl(uc.active_start_date,sysdate)) <= trunc(sysdate)
12825: AND trunc(sysdate) < trunc(nvl(uc.active_end_date,sysdate+1))
12826: AND trunc(nvl(ii.active_start_date,sysdate)) <= trunc(sysdate)
12891: AND trunc(nvl(cii.active_start_date, sysdate)) <= trunc(sysdate)
12892: AND trunc(sysdate) < trunc(nvl(cii.active_end_date, sysdate+1))
12893: )
12894: ) valid_inst
12895: WHERE /*(p_opt_uc = 1 AND exists (select 'x' from ahl_unit_config_headers
12896: where csi_item_instance_id = valid_inst.instance_id
12897: AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
12898: AND trunc(sysdate) < trunc(nvl(active_end_date,sysdate+1))
12899: AND unit_config_status_code <> 'DRAFT'
12901: ) -- get UCs only.
12902: )
12903: OR */ -- this cursor is not used when p_opt_uc = 1
12904: -- get components.
12905: (p_opt_uc = 2 AND not exists (select 'x' from ahl_unit_config_headers
12906: where csi_item_instance_id = valid_inst.instance_id
12907: AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
12908: AND trunc(sysdate) < trunc(nvl(active_end_date,sysdate+1))
12909: -- instance is a UC if in status draft.
12912: )
12913: )
12914: OR
12915: -- get all but ignore UCs as they have alredy been selected.
12916: (p_opt_uc = 0 AND not exists (select 'x' from ahl_unit_config_headers
12917: where csi_item_instance_id = valid_inst.instance_id
12918: AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
12919: AND trunc(sysdate) < trunc(nvl(active_end_date,sysdate+1))
12920: -- instance is a UC if in status draft.
12973: AND itc.organization_id = cii2.inv_master_organization_id
12974: AND itc.inventory_item_id = cii2.inventory_item_id
12975: ) -- get root nodes matching item category.
12976:
12977: AND ((p_opt_uc = 1 AND exists (select 'x' from ahl_unit_config_headers
12978: where csi_item_instance_id = valid_inst.instance_id
12979: AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
12980: AND trunc(sysdate) < trunc(nvl(active_end_date,sysdate+1))
12981: AND unit_config_status_code <> 'DRAFT'
12983: ) -- get UCs only.
12984: )
12985: OR
12986: -- get components.
12987: (p_opt_uc = 2 AND not exists (select 'x' from ahl_unit_config_headers
12988: where csi_item_instance_id = valid_inst.instance_id
12989: AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
12990: AND trunc(sysdate) < trunc(nvl(active_end_date,sysdate+1))
12991: -- instance is a UC if in status draft.
12995: )
12996: OR
12997: -- when process option is ALL.
12998: -- get components in this case too as UC's have already been selected.
12999: (p_opt_uc = 0 AND not exists (select 'x' from ahl_unit_config_headers
13000: where csi_item_instance_id = valid_inst.instance_id
13001: AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
13002: AND trunc(sysdate) < trunc(nvl(active_end_date,sysdate+1))
13003: -- instance is a UC if in status draft.
13031: -- JKJain, NR Analysis and Forecasting
13032: -- get all valid uc headers in a simulation plan
13033: CURSOR get_sim_plan_uc_root_csr (c_simulation_plan_id IN NUMBER)IS
13034: SELECT distinct UC.csi_item_instance_id
13035: from AHL_UNIT_CONFIG_HEADERS UC, AHL_FLEET_UNIT_ASSOCS FU
13036: WHERE FU.simulation_plan_id = c_simulation_plan_id
13037: and UC.UNIT_CONFIG_HEADER_ID = FU.UNIT_CONFIG_HEADER_ID
13038: and nvl(FU.association_end,sysdate) >= sysdate ;
13039:
13690: trunc(sysdate) < trunc(nvl(cii.active_end_date, sysdate+1))
13691: AND mr.item_instance_id = cii.instance_id
13692: )
13693: ) valid_inst
13694: WHERE (p_opt_uc = 1 AND exists (select 'x' from ahl_unit_config_headers
13695: where csi_item_instance_id = valid_inst.instance_id
13696: AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
13697: AND trunc(sysdate) < trunc(nvl(active_end_date,sysdate+1))
13698: AND unit_config_status_code <> 'DRAFT'
13700: ) -- get UCs only.
13701: )
13702: OR
13703: -- get components.
13704: (p_opt_uc = 2 AND not exists (select 'x' from ahl_unit_config_headers
13705: where csi_item_instance_id = valid_inst.instance_id
13706: AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
13707: AND trunc(sysdate) < trunc(nvl(active_end_date,sysdate+1))
13708: -- instance is a UC if in status draft.
13711: )
13712: )
13713: OR
13714: -- get all but do not select draft UCs as Process Unit will raise error.
13715: (p_opt_uc = 0 AND not exists (select 'x' from ahl_unit_config_headers
13716: where csi_item_instance_id = valid_inst.instance_id
13717: AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
13718: AND trunc(sysdate) < trunc(nvl(active_end_date,sysdate+1))
13719: AND unit_config_status_code = 'DRAFT'
13762: AND itc.organization_id = cii2.inv_master_organization_id
13763: AND itc.inventory_item_id = cii2.inventory_item_id
13764: ) -- get root nodes matching item category.
13765: -- either UC or components.
13766: AND ((p_opt_uc = 1 AND exists (select 'x' from ahl_unit_config_headers
13767: where csi_item_instance_id = valid_inst.instance_id
13768: AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
13769: AND trunc(sysdate) < trunc(nvl(active_end_date,sysdate+1))
13770: AND unit_config_status_code <> 'DRAFT'
13772: ) -- get UCs only.
13773: )
13774: OR
13775: -- get components.
13776: (p_opt_uc = 2 AND not exists (select 'x' from ahl_unit_config_headers
13777: where csi_item_instance_id = valid_inst.instance_id
13778: AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
13779: AND trunc(sysdate) < trunc(nvl(active_end_date,sysdate+1))
13780: -- instance is a UC if in status draft.
13783: )
13784: )
13785: OR
13786: -- when process option is ALL. Ignore Draft UC's
13787: (p_opt_uc = 0 AND not exists (select 'x' from ahl_unit_config_headers
13788: where csi_item_instance_id = valid_inst.instance_id
13789: AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
13790: AND trunc(sysdate) < trunc(nvl(active_end_date,sysdate+1))
13791: AND unit_config_status_code = 'DRAFT'