[Home] [Help]
1252: -- bug 16214422
1253: /*
1254: CURSOR c (h_id IN VARCHAR2) IS
1255: SELECT sl.line_number
1256: FROM mtl_system_items_kfv msik, so_lines sl
1257: WHERE sl.inventory_item_id = msik.inventory_item_id
1258: AND header_id = h_id;
1259: */
1260:
1624: id NUMBER;
1625:
1626: CURSOR c (i_num VARCHAR2) IS
1627: SELECT cii.instance_id
1628: FROM qa_csi_item_instances cii, mtl_system_items_kfv msik
1629: WHERE cii.instance_number = i_num
1630: AND cii.last_vld_organization_id = msik.organization_id;
1631:
1632: BEGIN
2969: -- After Single Scan LOV
2970: wild := value;
2971:
2972: sql_string := 'SELECT to_char(sl.line_number), msik.concatenated_segments
2973: FROM mtl_system_items_kfv msik, so_lines sl
2974: WHERE sl.inventory_item_id = msik.inventory_item_id
2975: AND header_id like :1';
2976:
2977: OPEN x_ref FOR sql_string USING wild;
3384: -- will have a base revision by default. But we want this sql to
3385: -- fetch revision only if the item is revision controlled. kabalakr.
3386:
3387: sql_string := 'SELECT mir.revision, mir.effectivity_date
3388: FROM mtl_item_revisions mir, mtl_system_items msi
3389: WHERE mir.inventory_item_id = :1
3390: AND mir.organization_id = :2
3391: AND mir.revision like :3
3392: AND mir.inventory_item_id = msi.inventory_item_id
3482:
3483: sql_string := 'SELECT
3484: cii.instance_number, cii.instance_description
3485: FROM
3486: csi_item_instances cii, mtl_system_items_b msib, mtl_parameters mp
3487: WHERE
3488: msib.organization_id = mp.organization_id and
3489: msib.organization_id = cii.last_vld_organization_id and
3490: msib.inventory_item_id = cii.inventory_item_id and
3531:
3532: sql_string := 'SELECT
3533: distinct msn.serial_number, msn.descriptive_text
3534: FROM
3535: mtl_serial_numbers msn, csi_item_instances cii, mtl_system_items_b msib, mtl_parameters mp
3536: WHERE
3537: msib.organization_id = mp.organization_id and
3538: msib.organization_id = cii.last_vld_organization_id and
3539: msib.inventory_item_id = cii.inventory_item_id and
3995: -- After Single Scan LOV
3996: wild := value;
3997:
3998: sql_string := 'SELECT cii.instance_number, cii.serial_number
3999: FROM qa_csi_item_instances cii, mtl_system_items_kfv msik
4000: WHERE cii.inventory_item_id = msik.inventory_item_id
4001: AND cii.last_vld_organization_id = msik.organization_id
4002: AND instance_number like :1
4003: ORDER BY 1';
4026:
4027: l_item_id := qa_flex_util.get_item_id(p_org_id, p_item_name);
4028:
4029: sql_string := 'SELECT cii.instance_number, cii.serial_number
4030: FROM qa_csi_item_instances cii, mtl_system_items_kfv msik
4031: WHERE cii.inventory_item_id = msik.inventory_item_id
4032: AND cii.last_vld_organization_id = msik.organization_id
4033: AND cii.inventory_item_id = :1
4034: AND trunc(sysdate) BETWEEN trunc(nvl(cii.active_start_date, sysdate))
4059:
4060: l_item_id := qa_flex_util.get_item_id(p_org_id, p_item_name);
4061:
4062: sql_string := 'SELECT cii.serial_number, msik.concatenated_segments
4063: FROM qa_csi_item_instances cii, mtl_system_items_kfv msik
4064: WHERE cii.inventory_item_id = msik.inventory_item_id
4065: AND cii.inv_master_organization_id = msik.organization_id
4066: AND msik.inventory_item_id = :1
4067: AND cii.serial_number like :2
4220: -- After Single Scan LOV
4221: wild := value;
4222:
4223: sql_string := 'SELECT concatenated_segments, description
4224: FROM mtl_system_items_kfv
4225: WHERE organization_id = :1
4226: AND concatenated_segments like :2
4227: ORDER BY concatenated_segments';
4228:
4248: -- After Single Scan LOV
4249: wild := value;
4250:
4251: sql_string := 'SELECT concatenated_segments, description
4252: FROM mtl_system_items_kfv
4253: WHERE organization_id = :1
4254: AND concatenated_segments like :2
4255: ORDER BY concatenated_segments';
4256:
4652: -- After Single Scan LOV
4653: wild := value;
4654:
4655: sql_string := 'SELECT concatenated_segments, description
4656: FROM mtl_system_items_kfv
4657: WHERE organization_id = :1
4658: AND concatenated_segments like :2
4659: ORDER BY concatenated_segments';
4660:
4703: -- Comp Item with WIP Job dependency.
4704: --
4705: l_sql_string :=
4706: 'SELECT concatenated_segments, description
4707: FROM mtl_system_items_kfv
4708: WHERE organization_id = :1 AND
4709: concatenated_segments like :2 AND
4710: inventory_item_id IN (
4711: SELECT inventory_item_id
4720: -- Comp Item with Flow Production Line dependency.
4721: --
4722: l_sql_string :=
4723: 'SELECT concatenated_segments, description
4724: FROM mtl_system_items_kfv
4725: WHERE organization_id = :1 AND
4726: concatenated_segments like :2 AND
4727: inventory_item_id IN (
4728: SELECT inventory_item_id
4740: -- Comp Item with BOM Component Dependency.
4741: --
4742: l_sql_string :=
4743: 'SELECT concatenated_segments, description
4744: FROM mtl_system_items_kfv
4745: WHERE organization_id = :1 AND
4746: concatenated_segments like :2 AND
4747: inventory_item_id IN (
4748: SELECT bic.component_item_id
4785: wild := value;
4786:
4787: --dgupta: Start R12 EAM Integration. Bug 4345492
4788: sql_string := 'select distinct msikfv.concatenated_segments, msikfv.description
4789: from mtl_system_items_b_kfv msikfv, mtl_parameters mp
4790: where msikfv.organization_id = mp.organization_id
4791: and msikfv.eam_item_type in (1,3)
4792: and mp.maint_organization_id = :1
4793: and msikfv.concatenated_segments like :2
4825: -- show all activities asssociated to the asset group
4826: -- If no match found or if asset group passed in is null, lov is empty
4827: /*
4828: sql_string := 'SELECT meaav.activity, meaav.activity_description
4829: FROM mtl_eam_asset_activities_v meaav, mtl_system_items_b msib
4830: WHERE msib.organization_id = :1
4831: and meaav. maintenance_object_id = :2 --pass asset group inventory_item_id
4832: and (meaav.end_date_active is null or meaav.end_date_active > sysdate)
4833: and (meaav.start_date_active is null or meaav.start_date_active < sysdate)
4840: sql_string := 'SELECT
4841: msib.concatenated_segments activity ,
4842: msib.description activity_description
4843: FROM mtl_eam_asset_activities meaav,
4844: mtl_system_items_b_kfv msib
4845: WHERE msib.organization_id = :1
4846: AND meaav. maintenance_object_id = :2 --pass asset group inventory_item_id
4847: AND (meaav.end_date_active is null
4848: OR meaav.end_date_active > sysdate)
4858: -- show all activities associated to asset group and asset number
4859: -- if exact match not found, lov is empty.
4860: /*
4861: sql_string := 'SELECT meaav.activity, meaav.activity_description
4862: FROM mtl_eam_asset_activities_v meaav, mtl_system_items_b msib
4863: WHERE msib.organization_id = :1
4864: and meaav.maintenance_object_id = :2 --pass asset instance_id
4865: and meaav.maintenance_object_type = 3 --serialized item
4866: and (meaav.end_date_active is null or meaav.end_date_active > sysdate)
4873: sql_string := 'SELECT
4874: msi.concatenated_segments activity ,
4875: msi.description activity_description
4876: FROM mtl_eam_asset_activities meaa,
4877: mtl_system_items_b_kfv msi
4878: WHERE msi.organization_id = :1
4879: AND meaa.maintenance_object_id = :2 --pass asset instance_id
4880: AND meaa.maintenance_object_type = 3 --serialized item
4881: AND (meaa.end_date_active is null
5203: id NUMBER;
5204: --rownum=1 =>better performance since all rows have same inventory_item_id
5205: CURSOR c (o_id NUMBER, a_group VARCHAR2) IS
5206: SELECT msikfv.inventory_item_id
5207: FROM mtl_system_items_b_kfv msikfv, mtl_parameters mp
5208: WHERE msikfv.organization_id = mp.organization_id
5209: and msikfv.eam_item_type in (1,3)
5210: and mp.maint_organization_id = o_id
5211: and msikfv.concatenated_segments = a_group
5628: msi.serial_number_control_code,
5629: msi.lot_control_code,
5630: msi.primary_uom_code
5631: FROM
5632: mtl_system_items msi
5633: WHERE msi.organization_id = org_id AND
5634: msi.inventory_item_id = p_item_id;
5635:
5636: cascaded_dep_elements_list VARCHAR2(32000);