[Home] [Help]
882: /* Bug 5315176 - Added the union clause below as EWOR table is
883: not populated if the work order is in draft status */
884: CURSOR c_network_assets (p_wip_entity_id NUMBER) IS
885: select cii.instance_id,cii.inventory_item_id,cii.serial_number
886: from csi_item_instances cii,
887: eam_work_order_route ewor
888: where ewor.wip_entity_id=p_wip_entity_id
889: and cii.instance_id=ewor.instance_id
890: union
888: where ewor.wip_entity_id=p_wip_entity_id
889: and cii.instance_id=ewor.instance_id
890: union
891: select mena.maintenance_object_id,cii.inventory_item_id,cii2.serial_number
892: from csi_item_instances cii,
893: mtl_eam_network_assets mena,
894: mtl_parameters mp,
895: csi_item_instances cii2
896: where cii.instance_number = p_asset_number
891: select mena.maintenance_object_id,cii.inventory_item_id,cii2.serial_number
892: from csi_item_instances cii,
893: mtl_eam_network_assets mena,
894: mtl_parameters mp,
895: csi_item_instances cii2
896: where cii.instance_number = p_asset_number
897: and mena.network_object_id = cii.instance_id
898: and cii2.instance_id = mena.maintenance_object_id
899: and cii.inventory_item_id = p_asset_group_id
1178: If( l_maint_obj_type = 3) then
1179: BEGIN
1180: select network_asset_flag
1181: into l_route_asset
1182: from CSI_Item_Instances
1183: where instance_id = l_maint_obj_id;
1184: EXCEPTION /* bug fix 2716439 */
1185: WHEN NO_DATA_FOUND THEN --no distributions in MTA
1186: l_route_asset := 'N';
1212: If l_asset_count =0 then
1213: select count(*)
1214: into l_asset_count
1215: from mtl_eam_network_assets mena,
1216: csi_item_instances cii,
1217: mtl_parameters mp
1218: where cii.instance_number = p_asset_number
1219: and mena.network_object_id = cii.instance_id
1220: and cii.inventory_item_id = p_asset_group_id
2073: If( l_maint_obj_type = 3) then
2074: BEGIN
2075: select network_asset_flag
2076: into l_route_asset
2077: from CSI_Item_Instances
2078: where instance_id = l_maint_obj_id;
2079: EXCEPTION
2080: WHEN NO_DATA_FOUND THEN
2081: l_route_asset := 'N';
2097: If l_asset_count =0 then
2098: select count(*)
2099: into l_asset_count
2100: from mtl_eam_network_assets mena,
2101: csi_item_instances cii,
2102: mtl_parameters mp
2103: where cii.instance_number = l_asset_number
2104: and mena.network_object_id = cii.instance_id
2105: and cii.inventory_item_id = l_inventory_item_id
2144: where ewor.wip_entity_id = p_entity_id_tab(l_index)
2145: union /* Added the union clause for Bug 5315176 */
2146: select mena.maintenance_object_id
2147: from mtl_eam_network_assets mena,
2148: csi_item_instances cii,
2149: mtl_parameters mp
2150: where cii.instance_number = l_asset_number
2151: and mena.network_object_id = cii.instance_id
2152: and cii.inventory_item_id = l_inventory_item_id
2180: where ewor.wip_entity_id = p_entity_id_tab(l_index)
2181: union /* Added the union clause for Bug 5315176 */
2182: select mena.maintenance_object_id
2183: from mtl_eam_network_assets mena,
2184: csi_item_instances cii,
2185: mtl_parameters mp
2186: where cii.instance_number = l_asset_number
2187: and mena.network_object_id = cii.instance_id
2188: and cii.inventory_item_id = l_inventory_item_id
2218: where ewor.wip_entity_id = p_entity_id_tab(l_index)
2219: union /* Added the union clause for Bug 5315176 */
2220: select mena.maintenance_object_id
2221: from mtl_eam_network_assets mena,
2222: csi_item_instances cii,
2223: mtl_parameters mp
2224: where cii.instance_number = l_asset_number
2225: and mena.network_object_id = cii.instance_id
2226: and cii.inventory_item_id = l_inventory_item_id
2254: where ewor.wip_entity_id = p_entity_id_tab(l_index)
2255: union /* Added the union clause for Bug 5315176 */
2256: select mena.maintenance_object_id
2257: from mtl_eam_network_assets mena,
2258: csi_item_instances cii,
2259: mtl_parameters mp
2260: where cii.instance_number = l_asset_number
2261: and mena.network_object_id = cii.instance_id
2262: and cii.inventory_item_id = l_inventory_item_id
5031: -- Function : Rollup total cost for Asset Item
5032: -- Parameters
5033: -- IN :
5034: -- p_inventory_item_id IN NUMBER Required
5035: -- inventory_item_id in csi_item_instances
5036: -- p_serial_number IN VARCHAR2 Required
5037: -- serial_number in csi_item_instances
5038: -- p_period_set_name IN VARCHAR2 Required
5039: -- period_set_name in ORG_ACCT_PERIODS_V
5033: -- IN :
5034: -- p_inventory_item_id IN NUMBER Required
5035: -- inventory_item_id in csi_item_instances
5036: -- p_serial_number IN VARCHAR2 Required
5037: -- serial_number in csi_item_instances
5038: -- p_period_set_name IN VARCHAR2 Required
5039: -- period_set_name in ORG_ACCT_PERIODS_V
5040: -- p_beginning_period_name IN VARCHAR2 Required
5041: -- starting period name for EAM cost Rollup
5307:
5308: /* Get the maintenance_object_id for the asset */
5309: select cii.instance_id
5310: into l_maintenance_object_id
5311: from csi_item_instances cii
5312: where cii.serial_number = p_serial_number
5313: and cii.inventory_item_id = p_inventory_item_id;
5314:
5315:
7949: wdj.maintenance_object_id
7950: from wip_cost_txn_interface wcti,
7951: wip_discrete_jobs wdj,
7952: wip_entities we,
7953: csi_item_instances cii
7954: where wcti.group_id = p_wcti_group_id
7955: and wdj.wip_entity_id = wcti.wip_entity_id
7956: and we.wip_entity_id = wcti.wip_entity_id
7957: and we.entity_type in (6,7)
7969: p_wip_entity_id NUMBER) IS
7970: select cii.inventory_item_id,
7971: cii.serial_number,
7972: msn.gen_object_id
7973: from csi_item_instances cii,
7974: eam_work_order_route ewor,
7975: mtl_serial_numbers msn,
7976: wip_discrete_jobs wdj
7977: where wdj.organization_id = p_org_id
8382: -- Oracle Applications 11i.9 --
8383: -- --
8384: -- HISTORY: --
8385: -- 03/29/05 Anjali R Modified for eAM Requirements Project - R12. --
8386: -- changes include reference to CSI_ITEM_INSTANCES--
8387: -- table from MTL_EAM_NETWORK_ASSETS. --
8388: -- 11/26/02 Ray Thng Created --
8389: ----------------------------------------------------------------------------
8390: PROCEDURE get_charge_asset (
8390: PROCEDURE get_charge_asset (
8391: p_api_version IN NUMBER,
8392: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
8393: p_wip_entity_id IN NUMBER,
8394: x_inventory_item_id OUT NOCOPY csi_item_instances.inventory_item_id%TYPE,
8395: x_serial_number OUT NOCOPY csi_item_instances.serial_number%TYPE,
8396: x_maintenance_object_id OUT NOCOPY mtl_serial_numbers.gen_object_id%TYPE,
8397: x_return_status OUT NOCOPY VARCHAR2,
8398: x_msg_count OUT NOCOPY NUMBER,
8391: p_api_version IN NUMBER,
8392: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
8393: p_wip_entity_id IN NUMBER,
8394: x_inventory_item_id OUT NOCOPY csi_item_instances.inventory_item_id%TYPE,
8395: x_serial_number OUT NOCOPY csi_item_instances.serial_number%TYPE,
8396: x_maintenance_object_id OUT NOCOPY mtl_serial_numbers.gen_object_id%TYPE,
8397: x_return_status OUT NOCOPY VARCHAR2,
8398: x_msg_count OUT NOCOPY NUMBER,
8399: x_msg_data OUT NOCOPY VARCHAR2)
8405:
8406: l_parent_wip_entity_id wip_discrete_jobs.parent_wip_entity_id%TYPE;
8407: l_parent_maint_object_type wip_discrete_jobs.maintenance_object_type%TYPE;
8408: l_parent_maint_object_id wip_discrete_jobs.maintenance_object_source%TYPE;
8409: l_parent_network_asset_flag csi_item_instances.network_asset_flag%TYPE;
8410: l_parent_inventory_item_id csi_item_instances.inventory_item_id%TYPE;
8411: l_parent_serial_number csi_item_instances.serial_number%TYPE;
8412: l_maint_object_type wip_discrete_jobs.maintenance_object_type%TYPE;
8413: l_maint_object_id wip_discrete_jobs.maintenance_object_source%TYPE;
8406: l_parent_wip_entity_id wip_discrete_jobs.parent_wip_entity_id%TYPE;
8407: l_parent_maint_object_type wip_discrete_jobs.maintenance_object_type%TYPE;
8408: l_parent_maint_object_id wip_discrete_jobs.maintenance_object_source%TYPE;
8409: l_parent_network_asset_flag csi_item_instances.network_asset_flag%TYPE;
8410: l_parent_inventory_item_id csi_item_instances.inventory_item_id%TYPE;
8411: l_parent_serial_number csi_item_instances.serial_number%TYPE;
8412: l_maint_object_type wip_discrete_jobs.maintenance_object_type%TYPE;
8413: l_maint_object_id wip_discrete_jobs.maintenance_object_source%TYPE;
8414: l_inventory_item_id csi_item_instances.inventory_item_id%TYPE;
8407: l_parent_maint_object_type wip_discrete_jobs.maintenance_object_type%TYPE;
8408: l_parent_maint_object_id wip_discrete_jobs.maintenance_object_source%TYPE;
8409: l_parent_network_asset_flag csi_item_instances.network_asset_flag%TYPE;
8410: l_parent_inventory_item_id csi_item_instances.inventory_item_id%TYPE;
8411: l_parent_serial_number csi_item_instances.serial_number%TYPE;
8412: l_maint_object_type wip_discrete_jobs.maintenance_object_type%TYPE;
8413: l_maint_object_id wip_discrete_jobs.maintenance_object_source%TYPE;
8414: l_inventory_item_id csi_item_instances.inventory_item_id%TYPE;
8415: l_serial_number csi_item_instances.serial_number%TYPE;
8410: l_parent_inventory_item_id csi_item_instances.inventory_item_id%TYPE;
8411: l_parent_serial_number csi_item_instances.serial_number%TYPE;
8412: l_maint_object_type wip_discrete_jobs.maintenance_object_type%TYPE;
8413: l_maint_object_id wip_discrete_jobs.maintenance_object_source%TYPE;
8414: l_inventory_item_id csi_item_instances.inventory_item_id%TYPE;
8415: l_serial_number csi_item_instances.serial_number%TYPE;
8416: BEGIN
8417: -- Standard call to check for call compatibility
8418: IF NOT FND_API.Compatible_API_Call(l_api_version,p_api_version,l_api_name,G_PKG_NAME) THEN
8411: l_parent_serial_number csi_item_instances.serial_number%TYPE;
8412: l_maint_object_type wip_discrete_jobs.maintenance_object_type%TYPE;
8413: l_maint_object_id wip_discrete_jobs.maintenance_object_source%TYPE;
8414: l_inventory_item_id csi_item_instances.inventory_item_id%TYPE;
8415: l_serial_number csi_item_instances.serial_number%TYPE;
8416: BEGIN
8417: -- Standard call to check for call compatibility
8418: IF NOT FND_API.Compatible_API_Call(l_api_version,p_api_version,l_api_name,G_PKG_NAME) THEN
8419: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
8454: l_inventory_item_id,
8455: l_serial_number
8456: FROM wip_discrete_jobs wdj,
8457: wip_discrete_jobs pwdj,
8458: csi_item_instances cii,
8459: csi_item_instances pcii
8460: WHERE wdj.wip_entity_id = p_wip_entity_id
8461: AND cii.instance_id (+) = wdj.maintenance_object_id
8462: AND pwdj.wip_entity_id (+) = wdj.parent_wip_entity_id
8455: l_serial_number
8456: FROM wip_discrete_jobs wdj,
8457: wip_discrete_jobs pwdj,
8458: csi_item_instances cii,
8459: csi_item_instances pcii
8460: WHERE wdj.wip_entity_id = p_wip_entity_id
8461: AND cii.instance_id (+) = wdj.maintenance_object_id
8462: AND pwdj.wip_entity_id (+) = wdj.parent_wip_entity_id
8463: AND pcii.instance_id (+) = pwdj.maintenance_object_id;