[Home] [Help]
1104: /* Bug 5315176 - Added the union clause below as EWOR table is
1105: not populated if the work order is in draft status */
1106: CURSOR c_network_assets (p_wip_entity_id NUMBER) IS
1107: select cii.instance_id,cii.inventory_item_id,cii.serial_number
1108: from csi_item_instances cii,
1109: eam_work_order_route ewor
1110: where ewor.wip_entity_id=p_wip_entity_id
1111: and cii.instance_id=ewor.instance_id
1112: union
1110: where ewor.wip_entity_id=p_wip_entity_id
1111: and cii.instance_id=ewor.instance_id
1112: union
1113: select mena.maintenance_object_id,cii.inventory_item_id,cii2.serial_number
1114: from csi_item_instances cii,
1115: mtl_eam_network_assets mena,
1116: mtl_parameters mp,
1117: csi_item_instances cii2
1118: where cii.instance_number = p_asset_number
1113: select mena.maintenance_object_id,cii.inventory_item_id,cii2.serial_number
1114: from csi_item_instances cii,
1115: mtl_eam_network_assets mena,
1116: mtl_parameters mp,
1117: csi_item_instances cii2
1118: where cii.instance_number = p_asset_number
1119: and mena.network_object_id = cii.instance_id
1120: and cii2.instance_id = mena.maintenance_object_id
1121: and cii.inventory_item_id = p_asset_group_id
1400: If( l_maint_obj_type = 3) then
1401: BEGIN
1402: select network_asset_flag
1403: into l_route_asset
1404: from CSI_Item_Instances
1405: where instance_id = l_maint_obj_id;
1406: EXCEPTION /* bug fix 2716439 */
1407: WHEN NO_DATA_FOUND THEN --no distributions in MTA
1408: l_route_asset := 'N';
1434: If l_asset_count =0 then
1435: select count(*)
1436: into l_asset_count
1437: from mtl_eam_network_assets mena,
1438: csi_item_instances cii,
1439: mtl_parameters mp
1440: where cii.instance_number = p_asset_number
1441: and mena.network_object_id = cii.instance_id
1442: and cii.inventory_item_id = p_asset_group_id
2295: If( l_maint_obj_type = 3) then
2296: BEGIN
2297: select network_asset_flag
2298: into l_route_asset
2299: from CSI_Item_Instances
2300: where instance_id = l_maint_obj_id;
2301: EXCEPTION
2302: WHEN NO_DATA_FOUND THEN
2303: l_route_asset := 'N';
2319: If l_asset_count =0 then
2320: select count(*)
2321: into l_asset_count
2322: from mtl_eam_network_assets mena,
2323: csi_item_instances cii,
2324: mtl_parameters mp
2325: where cii.instance_number = l_asset_number
2326: and mena.network_object_id = cii.instance_id
2327: and cii.inventory_item_id = l_inventory_item_id
2366: where ewor.wip_entity_id = p_entity_id_tab(l_index)
2367: union /* Added the union clause for Bug 5315176 */
2368: select mena.maintenance_object_id
2369: from mtl_eam_network_assets mena,
2370: csi_item_instances cii,
2371: mtl_parameters mp
2372: where cii.instance_number = l_asset_number
2373: and mena.network_object_id = cii.instance_id
2374: and cii.inventory_item_id = l_inventory_item_id
2402: where ewor.wip_entity_id = p_entity_id_tab(l_index)
2403: union /* Added the union clause for Bug 5315176 */
2404: select mena.maintenance_object_id
2405: from mtl_eam_network_assets mena,
2406: csi_item_instances cii,
2407: mtl_parameters mp
2408: where cii.instance_number = l_asset_number
2409: and mena.network_object_id = cii.instance_id
2410: and cii.inventory_item_id = l_inventory_item_id
2440: where ewor.wip_entity_id = p_entity_id_tab(l_index)
2441: union /* Added the union clause for Bug 5315176 */
2442: select mena.maintenance_object_id
2443: from mtl_eam_network_assets mena,
2444: csi_item_instances cii,
2445: mtl_parameters mp
2446: where cii.instance_number = l_asset_number
2447: and mena.network_object_id = cii.instance_id
2448: and cii.inventory_item_id = l_inventory_item_id
2476: where ewor.wip_entity_id = p_entity_id_tab(l_index)
2477: union /* Added the union clause for Bug 5315176 */
2478: select mena.maintenance_object_id
2479: from mtl_eam_network_assets mena,
2480: csi_item_instances cii,
2481: mtl_parameters mp
2482: where cii.instance_number = l_asset_number
2483: and mena.network_object_id = cii.instance_id
2484: and cii.inventory_item_id = l_inventory_item_id
5253: -- Function : Rollup total cost for Asset Item
5254: -- Parameters
5255: -- IN :
5256: -- p_inventory_item_id IN NUMBER Required
5257: -- inventory_item_id in csi_item_instances
5258: -- p_serial_number IN VARCHAR2 Required
5259: -- serial_number in csi_item_instances
5260: -- p_period_set_name IN VARCHAR2 Required
5261: -- period_set_name in ORG_ACCT_PERIODS_V
5255: -- IN :
5256: -- p_inventory_item_id IN NUMBER Required
5257: -- inventory_item_id in csi_item_instances
5258: -- p_serial_number IN VARCHAR2 Required
5259: -- serial_number in csi_item_instances
5260: -- p_period_set_name IN VARCHAR2 Required
5261: -- period_set_name in ORG_ACCT_PERIODS_V
5262: -- p_beginning_period_name IN VARCHAR2 Required
5263: -- starting period name for EAM cost Rollup
5600:
5601: /* Get the maintenance_object_id for the asset */
5602: select cii.instance_id
5603: into l_maintenance_object_id
5604: from csi_item_instances cii
5605: where cii.serial_number = p_serial_number
5606: and cii.inventory_item_id = p_inventory_item_id;
5607:
5608:
8522: wdj.maintenance_object_id
8523: from wip_cost_txn_interface wcti,
8524: wip_discrete_jobs wdj,
8525: wip_entities we,
8526: csi_item_instances cii
8527: where wcti.group_id = p_wcti_group_id
8528: and wdj.wip_entity_id = wcti.wip_entity_id
8529: and we.wip_entity_id = wcti.wip_entity_id
8530: and we.entity_type in (6,7)
8542: p_wip_entity_id NUMBER) IS
8543: select cii.inventory_item_id,
8544: cii.serial_number,
8545: msn.gen_object_id
8546: from csi_item_instances cii,
8547: eam_work_order_route ewor,
8548: mtl_serial_numbers msn,
8549: wip_discrete_jobs wdj
8550: where wdj.organization_id = p_org_id
8955: -- Oracle Applications 11i.9 --
8956: -- --
8957: -- HISTORY: --
8958: -- 03/29/05 Anjali R Modified for eAM Requirements Project - R12. --
8959: -- changes include reference to CSI_ITEM_INSTANCES--
8960: -- table from MTL_EAM_NETWORK_ASSETS. --
8961: -- 11/26/02 Ray Thng Created --
8962: ----------------------------------------------------------------------------
8963: PROCEDURE get_charge_asset (
8963: PROCEDURE get_charge_asset (
8964: p_api_version IN NUMBER,
8965: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
8966: p_wip_entity_id IN NUMBER,
8967: x_inventory_item_id OUT NOCOPY csi_item_instances.inventory_item_id%TYPE,
8968: x_serial_number OUT NOCOPY csi_item_instances.serial_number%TYPE,
8969: x_maintenance_object_id OUT NOCOPY mtl_serial_numbers.gen_object_id%TYPE,
8970: x_return_status OUT NOCOPY VARCHAR2,
8971: x_msg_count OUT NOCOPY NUMBER,
8964: p_api_version IN NUMBER,
8965: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
8966: p_wip_entity_id IN NUMBER,
8967: x_inventory_item_id OUT NOCOPY csi_item_instances.inventory_item_id%TYPE,
8968: x_serial_number OUT NOCOPY csi_item_instances.serial_number%TYPE,
8969: x_maintenance_object_id OUT NOCOPY mtl_serial_numbers.gen_object_id%TYPE,
8970: x_return_status OUT NOCOPY VARCHAR2,
8971: x_msg_count OUT NOCOPY NUMBER,
8972: x_msg_data OUT NOCOPY VARCHAR2)
8978:
8979: l_parent_wip_entity_id wip_discrete_jobs.parent_wip_entity_id%TYPE;
8980: l_parent_maint_object_type wip_discrete_jobs.maintenance_object_type%TYPE;
8981: l_parent_maint_object_id wip_discrete_jobs.maintenance_object_source%TYPE;
8982: l_parent_network_asset_flag csi_item_instances.network_asset_flag%TYPE;
8983: l_parent_inventory_item_id csi_item_instances.inventory_item_id%TYPE;
8984: l_parent_serial_number csi_item_instances.serial_number%TYPE;
8985: l_maint_object_type wip_discrete_jobs.maintenance_object_type%TYPE;
8986: l_maint_object_id wip_discrete_jobs.maintenance_object_source%TYPE;
8979: l_parent_wip_entity_id wip_discrete_jobs.parent_wip_entity_id%TYPE;
8980: l_parent_maint_object_type wip_discrete_jobs.maintenance_object_type%TYPE;
8981: l_parent_maint_object_id wip_discrete_jobs.maintenance_object_source%TYPE;
8982: l_parent_network_asset_flag csi_item_instances.network_asset_flag%TYPE;
8983: l_parent_inventory_item_id csi_item_instances.inventory_item_id%TYPE;
8984: l_parent_serial_number csi_item_instances.serial_number%TYPE;
8985: l_maint_object_type wip_discrete_jobs.maintenance_object_type%TYPE;
8986: l_maint_object_id wip_discrete_jobs.maintenance_object_source%TYPE;
8987: l_inventory_item_id csi_item_instances.inventory_item_id%TYPE;
8980: l_parent_maint_object_type wip_discrete_jobs.maintenance_object_type%TYPE;
8981: l_parent_maint_object_id wip_discrete_jobs.maintenance_object_source%TYPE;
8982: l_parent_network_asset_flag csi_item_instances.network_asset_flag%TYPE;
8983: l_parent_inventory_item_id csi_item_instances.inventory_item_id%TYPE;
8984: l_parent_serial_number csi_item_instances.serial_number%TYPE;
8985: l_maint_object_type wip_discrete_jobs.maintenance_object_type%TYPE;
8986: l_maint_object_id wip_discrete_jobs.maintenance_object_source%TYPE;
8987: l_inventory_item_id csi_item_instances.inventory_item_id%TYPE;
8988: l_serial_number csi_item_instances.serial_number%TYPE;
8983: l_parent_inventory_item_id csi_item_instances.inventory_item_id%TYPE;
8984: l_parent_serial_number csi_item_instances.serial_number%TYPE;
8985: l_maint_object_type wip_discrete_jobs.maintenance_object_type%TYPE;
8986: l_maint_object_id wip_discrete_jobs.maintenance_object_source%TYPE;
8987: l_inventory_item_id csi_item_instances.inventory_item_id%TYPE;
8988: l_serial_number csi_item_instances.serial_number%TYPE;
8989: BEGIN
8990: -- Standard call to check for call compatibility
8991: IF NOT FND_API.Compatible_API_Call(l_api_version,p_api_version,l_api_name,G_PKG_NAME) THEN
8984: l_parent_serial_number csi_item_instances.serial_number%TYPE;
8985: l_maint_object_type wip_discrete_jobs.maintenance_object_type%TYPE;
8986: l_maint_object_id wip_discrete_jobs.maintenance_object_source%TYPE;
8987: l_inventory_item_id csi_item_instances.inventory_item_id%TYPE;
8988: l_serial_number csi_item_instances.serial_number%TYPE;
8989: BEGIN
8990: -- Standard call to check for call compatibility
8991: IF NOT FND_API.Compatible_API_Call(l_api_version,p_api_version,l_api_name,G_PKG_NAME) THEN
8992: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
9027: l_inventory_item_id,
9028: l_serial_number
9029: FROM wip_discrete_jobs wdj,
9030: wip_discrete_jobs pwdj,
9031: csi_item_instances cii,
9032: csi_item_instances pcii
9033: WHERE wdj.wip_entity_id = p_wip_entity_id
9034: AND cii.instance_id (+) = wdj.maintenance_object_id
9035: AND pwdj.wip_entity_id (+) = wdj.parent_wip_entity_id
9028: l_serial_number
9029: FROM wip_discrete_jobs wdj,
9030: wip_discrete_jobs pwdj,
9031: csi_item_instances cii,
9032: csi_item_instances pcii
9033: WHERE wdj.wip_entity_id = p_wip_entity_id
9034: AND cii.instance_id (+) = wdj.maintenance_object_id
9035: AND pwdj.wip_entity_id (+) = wdj.parent_wip_entity_id
9036: AND pcii.instance_id (+) = pwdj.maintenance_object_id;