DBA Data[Home] [Help]

APPS.BOMPXINQ SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 147

    SELECT max(MAXIMUM_BOM_LEVEL)
	INTO max_level
	FROM BOM_PARAMETERS
	WHERE (org_id = -1
		or
		(org_id <> -1 and ORGANIZATION_ID = org_id)
	      );
Line: 241

		SELECT
			COMPONENT_CODE,
			LOOP_FLAG,
			PLAN_LEVEL
		FROM BOM_SMALL_EXPL_TEMP
		WHERE GROUP_ID = c_group_id
		AND LOOP_FLAG = 1;
Line: 250

  SELECT NVL( TOP_ALTERNATE_DESIGNATOR, 'none' ), ORGANIZATION_ID
	INTO top_alt, org_id
	FROM BOM_SMALL_EXPL_TEMP
	WHERE GROUP_ID = grp_id
	AND ROWNUM = 1
	AND PLAN_LEVEL = 0;
Line: 266

  	SELECT
  	substrb(MIF.ITEM_NUMBER || ' ' || BBM.ALTERNATE_BOM_DESIGNATOR,1,16)
  	INTO cur_msgstr
  	FROM MTL_ITEM_FLEXFIELDS MIF, BOM_BILL_OF_MATERIALS BBM
  	WHERE MIF.ORGANIZATION_ID = BBM.ORGANIZATION_ID
  	AND MIF.ITEM_ID = BBM.ASSEMBLY_ITEM_ID
  	AND BBM.ASSEMBLY_ITEM_ID = cur_item_id
  	AND BBM.ORGANIZATION_ID = org_id
  	AND (
		((top_alt = 'none') AND BBM.ALTERNATE_BOM_DESIGNATOR IS NULL)
		OR
		((top_alt <> 'none')
	  	AND (
		      ( EXISTS ( SELECT NULL FROM BOM_BILL_OF_MATERIALS BBM1
		        WHERE BBM1.ORGANIZATION_ID = org_id
		        AND BBM1.ASSEMBLY_ITEM_ID = cur_item_id
		        AND BBM1.ALTERNATE_BOM_DESIGNATOR = top_alt)
		        AND BBM.ALTERNATE_BOM_DESIGNATOR = top_alt
                      )
		      OR
		      ( NOT EXISTS (SELECT NULL FROM BOM_BILL_OF_MATERIALS BBM2
                        WHERE BBM2.ORGANIZATION_ID = org_id
                        AND BBM2.ASSEMBLY_ITEM_ID = cur_item_id
                        AND BBM2.ALTERNATE_BOM_DESIGNATOR = top_alt)
		        AND BBM.ALTERNATE_BOM_DESIGNATOR IS NULL
                      )
	            )
	  	)
  	   );
Line: 371

       Select BET.bill_sequence_id curBSI,
              BET.component_sequence_id curCSI,
              BET.component_item_id curCII,
              BET.common_bill_sequence_id curCBSI,
              msi1.organization_id t_master_org_id,
              msi1.primary_uom_code t_master_uom,
              msi2.primary_uom_code t_child_uom
       from   BOM_SMALL_EXPL_TEMP BET, bom_bill_of_materials bbm,  mtl_system_items msi1, mtl_system_items msi2
       where  BET.bill_sequence_id <> BET.common_bill_sequence_id
       and    bbm.bill_sequence_id =  BET.common_bill_sequence_id
       and    msi1.inventory_item_id = BET.component_item_id
       and    msi1.organization_id =  bbm.organization_id
       and    msi2.inventory_item_id = BET.component_item_id
       and    msi2.organization_id = BET.organization_id
       and    BET.group_id = grp_id;
Line: 392

    select conversion_rate
    from   mtl_uom_conversions_view
    where primary_uom_code = t_master_uom and
                uom_code = t_child_uom and
                inventory_item_id = t_inv_id and
                organization_id = t_master_org_id;
Line: 414

    insert into bom_small_expl_temp
      (
	group_id,
	bill_sequence_id,
	component_sequence_id,
	organization_id,
	top_item_id,
	component_item_id,
	plan_level,
	extended_quantity,
        basis_type,
	component_quantity,
	sort_order,
	program_update_date,
	top_bill_sequence_id,
	component_code,
	loop_flag,
	top_alternate_designator,
	bom_item_type,
	parent_bom_item_type
       )
	select
	grp_id,
	bom.bill_sequence_id,
	NULL,
	org_id,
	item_id,
	item_id,
	0,
	expl_qty,
	1,
	1,
	lpad('1', X_SortWidth, '0'),
	sysdate,
	bom.bill_sequence_id,
	nvl(comp_code, lpad(item_id, 16, '0')),
	2,
	alt_desg,
	msi.bom_item_type,
	msi.bom_item_type
	from bom_bill_of_materials bom, mtl_system_items msi
	where bom.assembly_item_id = item_id
	and   bom.organization_id = org_id
	and   nvl(alternate_bom_designator, 'NONE') =
		nvl(alt_desg, 'NONE')
	and   msi.organization_id = org_id
	and   inventory_item_id = item_id;
Line: 519

   select count(*) into  cnt
   from   mtl_parameters
   where  organization_id = cost_organization_id
          and organization_id = org_id;
Line: 543

    select msi.primary_uom_code, msi.organization_id into
           t_master_uom, t_master_org_id
    from   mtl_system_items msi, bom_bill_of_materials bbm
    where  cr.curCBSI = bbm.bill_sequence_id and
           bbm.organization_id = msi.organization_id and
           msi.inventory_item_id = cr.curCII;
Line: 550

    select msi.primary_uom_code into t_child_uom
    from   mtl_system_items msi
    where  msi.inventory_item_id = cr.curCII and
           msi.organization_id = cr.curOI;
Line: 557

    select conversion_rate into t_conversion_rate
    from   mtl_uom_conversions_view
    where primary_uom_code = t_master_uom and
          uom_code = t_child_uom and
          inventory_item_id = cr.curCII and
          organization_id = t_master_org_id;
Line: 582

/* Bug 9355186 : Individual updates converted into bulk update
    if is_cost_organization <> 'Y' then
       UPDATE BOM_SMALL_EXPL_TEMP
       SET    item_cost = item_cost*t_conversion_rate
       WHERE  group_id = cr.curGI and
              component_sequence_id = cr.curCSI and
              bill_sequence_id = cr.curBSI and
              common_bill_sequence_id = cr.curCBSI;
Line: 593

    UPDATE BOM_SMALL_EXPL_TEMP
    SET    component_quantity = trunc(component_quantity/t_conversion_rate, 22), --Bug 9173185 fix
           extended_quantity = extended_quantity/t_conversion_rate,
--           item_cost = item_cost*t_conversion_rate,
           primary_uom_code = cr.curPUC
    WHERE  group_id = cr.curGI and
           component_sequence_id = cr.curCSI and
           bill_sequence_id = cr.curBSI and
           common_bill_sequence_id = cr.curCBSI;
Line: 606

   UPDATE /*+ index(BOM_SMALL_EXPL_TEMP BOM_SMALL_EXPL_TEMP_n1) */ BOM_SMALL_EXPL_TEMP
   SET
   -- Bug 2157325 Begin
   -- If cost_organization is Master organization then the item cost should be
   -- calculated by multiplying the conversion_rate.
   item_cost = decode(is_cost_organization,'Y',item_cost,item_cost*t_conversion_rate(i)),
   component_quantity = trunc(component_quantity/t_conversion_rate(i), 22), --Bug 8977128 fix
   extended_quantity = extended_quantity/t_conversion_rate(i)
   WHERE  group_id = grp_id and
     component_sequence_id = curCSI(i) and
     bill_sequence_id = curBSI(i) and
     common_bill_sequence_id = curCBSI(i);
Line: 711

     SELECT
     TOP_BILL_SEQUENCE_ID      ,
     BILL_SEQUENCE_ID          ,
     COMMON_BILL_SEQUENCE_ID   ,
     ORGANIZATION_ID           ,
     COMPONENT_SEQUENCE_ID     ,
     COMPONENT_ITEM_ID         ,
     BASIS_TYPE		       ,
     COMPONENT_QUANTITY        ,
     PLAN_LEVEL                ,
     EXTENDED_QUANTITY         ,
     SORT_ORDER                ,
     GROUP_ID                  ,
     TOP_ALTERNATE_DESIGNATOR  ,
     COMPONENT_YIELD_FACTOR    ,
     TOP_ITEM_ID               ,
     COMPONENT_CODE            ,
     INCLUDE_IN_ROLLUP_FLAG    ,
     LOOP_FLAG                 ,
     PLANNING_FACTOR           ,
     OPERATION_SEQ_NUM         ,
     BOM_ITEM_TYPE             ,
     PARENT_BOM_ITEM_TYPE      ,
     ASSEMBLY_ITEM_ID          ,
     WIP_SUPPLY_TYPE           ,
     ITEM_NUM                  ,
     EFFECTIVITY_DATE          ,
     DISABLE_DATE              ,
     IMPLEMENTATION_DATE       ,
     OPTIONAL                  ,
     SUPPLY_SUBINVENTORY       ,
     SUPPLY_LOCATOR_ID         ,
     COMPONENT_REMARKS         ,
     CHANGE_NOTICE             ,
     OPERATION_LEAD_TIME_PERCENT,
     MUTUALLY_EXCLUSIVE_OPTIONS ,
     CHECK_ATP                  ,
     REQUIRED_TO_SHIP           ,
     REQUIRED_FOR_REVENUE       ,
     INCLUDE_ON_SHIP_DOCS       ,
     LOW_QUANTITY               ,
     HIGH_QUANTITY              ,
     SO_BASIS                   ,
     OPERATION_OFFSET           ,
     CURRENT_REVISION           ,
     LOCATOR                    ,
     CONTEXT                    ,
     ATTRIBUTE1                 ,
     ATTRIBUTE2                 ,
     ATTRIBUTE3                 ,
     ATTRIBUTE4                 ,
     ATTRIBUTE5                 ,
     ATTRIBUTE6                 ,
     ATTRIBUTE7                 ,
     ATTRIBUTE8                 ,
     ATTRIBUTE9                 ,
     ATTRIBUTE10                ,
     ATTRIBUTE11                ,
     ATTRIBUTE12                ,
     ATTRIBUTE13                ,
     ATTRIBUTE14                ,
     ATTRIBUTE15                ,
     ITEM_COST                  ,
     EXTEND_COST_FLAG
     FROM  bom_small_expl_temp
     WHERE
     Organization_id = l_organization_id
     AND GROUP_ID    =  l_group_id;
Line: 793

	   SELECT count (*) into l_org_count from
     	       per_organization_structures
	   WHERE  INV_ORGHIERARCHY_PVT.ORG_HIERARCHY_ACCESS(Org_hierarchy_name)='Y'
	   AND    name = Org_hierarchy_name;
Line: 807

         SELECT organization_name into l_org_name
         FROM   org_organization_definitions
         WHERE  organization_id = l_organization_id;
Line: 836

		SELECT assembly_item_id INTO c_assembly_item_id
		FROM   bom_bill_of_materials
		WHERE  assembly_item_id = l_assembly_item_id
		AND    organization_id = t_org_code_list(I)
		AND    nvl(ALTERNATE_BOM_DESIGNATOR,'NONE')=
		       nvl(Alternate_bm_designator,'NONE') ;
Line: 850

		SELECT COST_TYPE_ID into l_cst_type_id
		FROM  cst_item_cost_type_v
		WHERE inventory_item_id = Assembly_item_id
		AND   cost_type_id = c_Cost_type_id
		AND   organization_id = t_org_code_list(I);
Line: 862

	SELECT MAXIMUM_BOM_LEVEL INTO max_level
	FROM BOM_PARAMETERS
	WHERE ORGANIZATION_ID = t_org_code_list(I);
Line: 866

	SELECT bom_explosion_temp_s.nextval
	INTO  l_group_id from dual;
Line: 869

	DELETE from bom_small_expl_temp where group_id =l_group_id;
Line: 1007

	   DELETE from bom_small_expl_temp where group_id =l_group_id;
Line: 1018

		bom_export_tab.delete;
Line: 1026

		bom_export_tab.delete;
Line: 1033

		bom_export_tab.delete;
Line: 1041

		bom_export_tab.delete;
Line: 1049

		bom_export_tab.delete;
Line: 1057

		bom_export_tab.delete ;
Line: 1067

  SELECT concatenated_segments
  INTO   l_item_name
  FROM   mtl_system_items_kfv
  WHERE  inventory_item_id = P_item_id
  AND    organization_id   = P_organization_id;
Line: 1083

  SELECT organization_code
  INTO   l_org_code
  FROM   mtl_parameters
  WHERE  organization_id = P_organization_id;
Line: 1113

    SELECT concatenated_segments
    FROM   mtl_item_locations_kfv
    WHERE  inventory_location_id = P_locator_id
    AND    organization_id       = P_organization_id;
Line: 1132

    SELECT specific_assembly_comment,
           assembly_type,
           common_assembly_item_id,
           common_organization_id,
           original_system_reference,
           alternate_bom_designator,
           attribute_category,
           attribute1,
           attribute2,
           attribute3,
           attribute4,
           attribute5,
           attribute6,
           attribute7,
           attribute8,
           attribute9,
           attribute10,
           attribute11,
           attribute12,
           attribute13,
           attribute14,
           attribute15
    FROM   bom_bill_of_materials
    WHERE  bill_sequence_id = p_bill_sequence_id;
Line: 1159

      SELECT revision,
             description,
             effectivity_date,
             attribute_category,
             attribute1,
             attribute2,
             attribute3,
             attribute4,
             attribute5,
             attribute6,
             attribute7,
             attribute8,
             attribute9,
             attribute10,
             attribute11,
             attribute12,
             attribute13,
             attribute14,
             attribute15
      FROM   mtl_item_revisions
      WHERE  inventory_item_id = P_assembly_item_id
      AND    organization_id   = P_organization_id;
Line: 1289

    SELECT effectivity_date,
           disable_date,
           operation_seq_num,
           acd_type,
           item_num,
           basis_type,
           component_quantity,
           planning_factor,
           component_yield_factor,
           include_in_cost_rollup,
           wip_supply_type,
           so_basis,
           optional,
           mutually_exclusive_options,
           check_atp,
           shipping_allowed,
           required_to_ship,
           required_for_revenue,
           include_on_ship_docs,
           quantity_related,
           supply_subinventory,
           low_quantity,
           high_quantity,
           component_remarks,
           from_end_item_unit_number,
           to_end_item_unit_number,
           enforce_int_requirements,
           supply_locator_id,
           attribute_category,
           attribute1,
           attribute2,
           attribute3,
           attribute4,
           attribute5,
           attribute6,
           attribute7,
           attribute8,
           attribute9,
           attribute10,
           attribute11,
           attribute12,
           attribute13,
           attribute14,
           attribute15
    FROM   bom_inventory_components
    WHERE  bill_sequence_id      = P_bill_sequence_id
    AND    component_sequence_id = P_component_sequence_id
    AND    component_item_id     = P_component_item_id;
Line: 1339

    SELECT implementation_date,
           substitute_component_id,
           substitute_item_quantity,
           enforce_int_requirements,
           attribute_category,
           attribute1,
           attribute2,
           attribute3,
           attribute4,
           attribute5,
           attribute6,
           attribute7,
           attribute8,
           attribute9,
           attribute10,
           attribute11,
           attribute12,
           attribute13,
           attribute14,
           attribute15
    FROM   bom_substitute_components_v
    WHERE  component_sequence_id = P_component_sequence_id;
Line: 1363

    SELECT component_reference_designator,
           implementation_date,
           ref_designator_comment,
           attribute_category,
           attribute1,
           attribute2,
           attribute3,
           attribute4,
           attribute5,
           attribute6,
           attribute7,
           attribute8,
           attribute9,
           attribute10,
           attribute11,
           attribute12,
           attribute13,
           attribute14,
           attribute15
    FROM   bom_reference_designators_v
    WHERE  component_sequence_id = P_component_sequence_id;
Line: 1386

    SELECT operation_seq_num,
           attribute_category,
           attribute1,
           attribute2,
           attribute3,
           attribute4,
           attribute5,
           attribute6,
           attribute7,
           attribute8,
           attribute9,
           attribute10,
           attribute11,
           attribute12,
           attribute13,
           attribute14,
           attribute15
    FROM   bom_component_operations
    WHERE  component_sequence_id = P_component_sequence_id;
Line: 1663

    SELECT organization_id
    FROM   mtl_parameters
    WHERE  organization_code = P_organization_code;
Line: 1668

    SELECT inventory_item_id
    FROM   mtl_system_items
    WHERE  segment1        = P_assembly_item_name
    AND    organization_id = l_organization_id;
Line: 1677

  G_Header_Record_id_Tbl.DELETE;
Line: 1678

  G_bom_header_tbl.DELETE;
Line: 1679

  G_bom_revisions_tbl.DELETE;
Line: 1680

  G_bom_components_tbl.DELETE;
Line: 1681

  G_bom_ref_designators_tbl.DELETE;
Line: 1682

  G_bom_sub_components_tbl.DELETE;
Line: 1683

  G_bom_comp_ops_tbl.DELETE;
Line: 1826

         X_bom_header_tbl.DELETE;
Line: 1827

         X_bom_revisions_tbl.DELETE;
Line: 1828

         X_bom_components_tbl.DELETE;
Line: 1829

         X_bom_ref_designators_tbl.DELETE;
Line: 1830

         X_bom_sub_components_tbl.DELETE;
Line: 1831

         X_bom_comp_ops_tbl.DELETE;
Line: 1837

         X_bom_header_tbl.DELETE;
Line: 1838

         X_bom_revisions_tbl.DELETE;
Line: 1839

         X_bom_components_tbl.DELETE;
Line: 1840

         X_bom_ref_designators_tbl.DELETE;
Line: 1841

         X_bom_sub_components_tbl.DELETE;
Line: 1842

         X_bom_comp_ops_tbl.DELETE;
Line: 1848

         X_bom_header_tbl.DELETE;
Line: 1849

         X_bom_revisions_tbl.DELETE;
Line: 1850

         X_bom_components_tbl.DELETE;
Line: 1851

         X_bom_ref_designators_tbl.DELETE;
Line: 1852

         X_bom_sub_components_tbl.DELETE;
Line: 1853

         X_bom_comp_ops_tbl.DELETE;