APPS.BOM_VALIDATE_REF_DESIGNATOR SQL Statements

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

Line: 51

  X_Delete constant number := 3;

Line: 56

    Select brd.component_reference_designator
    From bom_reference_designators brd
    Where brd.component_sequence_id = p_old_component_sequence_id
    And nvl(brd.acd_type, X_Add) = X_Add
    Union
    Select brd.component_reference_designator
    From bom_reference_designators brd,
         bom_inventory_components bic
    Where DECODE(bic.old_component_sequence_id, NULL,
		 bic.component_sequence_id,
		 bic.old_component_sequence_id) = p_old_component_sequence_id
    And   bic.component_sequence_id = brd.component_sequence_id
    And   bic.implementation_date is null
    And   brd.acd_type = X_Add
    Minus
    Select brd.component_reference_designator
    From bom_reference_designators brd,
         bom_inventory_components bic
    Where DECODE(bic.old_component_sequence_id, NULL,
		 bic.component_sequence_id,
		 bic.old_component_sequence_id) = p_old_component_sequence_id
    And   bic.component_sequence_id = brd.component_sequence_id
    And   bic.implementation_date is null
    And   brd.acd_type = X_Delete;

Line: 84

		Select count(*)
		Into   X_TotalQuantity
		From   bom_reference_designators brd ,
         	       bom_inventory_components bic
    		Where  brd.component_sequence_id = p_old_component_sequence_id
    		And    bic.component_sequence_id = brd.component_sequence_id
    		And    bic.implementation_date is NOT NULL
    		And    nvl(brd.acd_type, 1) = 1 ;

Line: 138

	SELECT component_quantity
          FROM bom_inventory_components
         WHERE component_sequence_id = p_component_sequence_id
           AND quantity_related      = 1;

Line: 144

	SELECT acd_type, old_component_sequence_id
	  FROM bom_inventory_components bic
         WHERE bic.component_sequence_id = p_component_sequence_id;

Line: 177

        l_token_tbl.delete ;

Line: 232

	SELECT acd_type, old_component_sequence_id
	  FROM bom_inventory_components bic
         WHERE bic.component_sequence_id =
	       p_Ref_Desg_Unexp_Rec.component_sequence_id;

Line: 238

	SELECT component_quantity
          FROM bom_inventory_components
         WHERE component_sequence_id =
	       p_Ref_Desg_Unexp_rec.component_sequence_id
           AND quantity_related      = 1;

Line: 248

  SELECT 1
  INTO l_dummy
  FROM bom_bill_of_materials
  WHERE bill_sequence_id = source_bill_sequence_id
  AND bill_sequence_id = p_ref_desg_unexp_rec.bill_sequence_id;

Line: 265

  select basis_type into l_basis_type from bom_components_b where component_sequence_id = p_Ref_Desg_Unexp_Rec.component_sequence_id;

Line: 279

SELECT msi.bom_enabled_flag
INTO l_assy_bom_enabled
FROM mtl_system_items_b msi,
bom_bill_of_materials bbom
WHERE bbom.bill_sequence_id = p_Ref_Desg_Unexp_Rec.bill_sequence_id
AND bbom.assembly_item_id = msi.inventory_item_id
AND bbom.organization_id = msi.organization_id;

Line: 349

                   SELECT component_reference_designator
                     INTO l_dummy
                     FROM bom_reference_designators brd,
                          bom_inventory_components bic
                    WHERE bic.component_sequence_id =
                                p_ref_desg_unexp_rec.component_sequence_id
                      AND brd.component_sequence_id = bic.old_component_sequence_id
                      AND brd.component_reference_designator =
                          p_ref_designator_rec.reference_designator_name ;

Line: 405

                l_token_tbl.delete;   -- Added by MK on 11/14/00

Line: 430

    	SELECT 'Non-Standard Comp'
      	  INTO l_dummy
      	  FROM bom_inventory_Components bic
         WHERE bic.component_sequence_id  =
	       p_Ref_Desg_Unexp_Rec.component_sequence_id
       	   AND bic.bom_item_type in (1, 2, 3); /*MODEL,OPTION CLASS,PLANNING*/

Line: 475

	  SELECT 'Planning Bill'
	    INTO l_dummy
	    FROM sys.dual
       	   WHERE EXISTS (SELECT 'Planning Item'
		     	     FROM bom_bill_of_materials bom,
			      	  mtl_system_items msi,
				  bom_inventory_components bic
		    	    WHERE msi.bom_item_type	= 3 /* PLANNING */
			      AND msi.inventory_item_id = bom.assembly_item_id
		      	      AND msi.organization_id   = bom.organization_id
			      AND bom.bill_sequence_id = bic.bill_sequence_id
			      AND bic.component_sequence_id =
				  p_Ref_Desg_Unexp_Rec.Component_sequence_id
			  );

Line: 528

    * If a ref. designator is being added or deleted, and if the Quantity
    * Related is 1 then their must be a check that the number of designators
    * is equal to the component_quantity.
    *************************************************************************/
    IF (p_ref_designator_rec.Transaction_Type = Bom_GLOBALS.G_OPR_CREATE) THEN
    BEGIN

       OPEN c_QuantityRelated;

Line: 591

                l_token_tbl.delete ; -- Added by MK on 11/14/00

Line: 617

/* When the Designator name is updated with New_designator, It should be checked that the new_desinator
does not exists already */

     IF ( p_ref_designator_rec.new_reference_designator is not null
          and p_ref_designator_rec.new_reference_designator <> FND_API.G_MISS_CHAR
            and p_ref_designator_rec.transaction_type = Bom_Globals.G_OPR_UPDATE) THEN

        select count(*) into l_temp_var
          FROM    BOM_REFERENCE_DESIGNATORS
      	  WHERE   COMPONENT_REFERENCE_DESIGNATOR =  p_ref_designator_rec.new_reference_designator
    	  AND     COMPONENT_SEQUENCE_ID = p_Ref_Desg_Unexp_Rec.component_sequence_id
    	  AND     NVL(DECODE(ACD_TYPE, FND_API.G_MISS_NUM, null, acd_type), 0) =
          NVL(DECODE(p_ref_designator_rec.acd_type, FND_API.G_MISS_NUM, null, p_ref_designator_rec.acd_type), 0) ;

Line: 752

* Procedure     : Check_Entity_Delete
* Parameters IN : Reference Designator Record as given by the User
*                 Reference Designator Unexposed Record
* Parameters OUT: Return_Status - Indicating success or faliure
*                 Mesg_Token_Tbl - Filled with any errors or warnings
* Purpose       : Entity Delete procedure will verify if the entity can
*		  can be deleted without violating any business rules.
*		  In case of errors the Mesg_token_Tbl is populated and
*		  process return with a status other than 'S'
*		  Warning will not prevent the entity from being deleted.
********************************************************************/

PROCEDURE Check_Entity_Delete
(   x_return_status             IN OUT NOCOPY VARCHAR2
,   x_Mesg_Token_Tbl		IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
,   p_ref_designator_rec        IN  Bom_Bo_Pub.Ref_Designator_Rec_Type
,   p_Ref_Desg_Unexp_Rec	IN  Bom_Bo_Pub.Ref_Desg_Unexposed_Rec_Type
)
IS
l_return_status               VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;

Line: 783

 	SELECT 'Related'
	  INTO l_dummy
	  FROM bom_inventory_components
	 WHERE quantity_related = 1
	   AND component_sequence_id =
	       p_ref_desg_Unexp_rec.component_sequence_id;

Line: 818

END Check_Entity_Delete;

Line: 831

*                 error if the operation is UPDATE and the record DOES NOT
*                 EXIST.
*                 In case of UPDATE if the record exists then the procedure
*                 will return the old record in the old entity parameters
*                 with a success status.
****************************************************************************/
PROCEDURE Check_Existence
(  p_ref_designator_rec         IN  Bom_Bo_Pub.Ref_Designator_Rec_Type
 , p_ref_desg_unexp_rec         IN  Bom_Bo_Pub.Ref_Desg_Unexposed_Rec_Type
 , x_old_ref_designator_rec     IN OUT NOCOPY Bom_Bo_Pub.Ref_Designator_Rec_Type
 , x_old_ref_desg_unexp_rec     IN OUT NOCOPY Bom_Bo_Pub.Ref_Desg_Unexposed_Rec_Type
 , x_Mesg_Token_Tbl             IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
 , x_Return_Status              IN OUT NOCOPY VARCHAR2
)
IS
	l_Mesg_Token_Tbl	Error_Handler.Mesg_Token_Tbl_Type;

Line: 879

                 (Bom_Globals.G_OPR_UPDATE, Bom_Globals.G_OPR_DELETE)
        THEN
                Error_Handler.Add_Error_Token
                (  x_Mesg_token_tbl => l_Mesg_Token_Tbl
                 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
                 , p_message_name  => 'BOM_REF_DESG_DOESNOT_EXIST'
                 , p_token_tbl     => l_token_tbl
                 );

Line: 907

                                                   Bom_Globals.G_OPR_UPDATE;

Line: 933

	SELECT revised_item_sequence_id
	  FROM bom_inventory_components
	 WHERE component_item_id= p_ref_desg_unexp_rec.component_item_id
	   AND operation_seq_num=p_ref_designator_rec.operation_sequence_number
	   AND effectivity_date = p_ref_designator_rec.start_effective_date
	   AND bill_sequence_id = p_ref_desg_unexp_rec.bill_sequence_id;

Line: 994

        SELECT 'parent not disabled'
          INTO l_dummy
          FROM bom_inventory_components bic
         WHERE bic.component_sequence_id =
               p_Ref_Desg_Unexp_Rec.component_sequence_id
           AND NVL(bic.acd_type, 0)  <> 3;

Line: 1097

PROCEDURE Check_Entity_Delete
(   x_return_status             IN OUT NOCOPY VARCHAR2
,   x_Mesg_Token_Tbl            IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
,   p_bom_ref_designator_rec        IN  Bom_Bo_Pub.Bom_Ref_Designator_Rec_Type
,   p_bom_Ref_Desg_Unexp_Rec        IN  Bom_Bo_Pub.Bom_Ref_Desg_Unexp_Rec_Type
)
IS
        l_ref_designator_rec    Bom_Bo_Pub.Ref_Designator_rec_Type;

Line: 1117

        Bom_Validate_Ref_Designator.Check_Entity_Delete
        (  p_ref_designator_rec         => l_ref_designator_rec
         , p_ref_desg_unexp_rec         => l_Ref_Desg_unexp_rec
         , x_return_status              => x_return_status
         , x_mesg_token_tbl             => x_mesg_token_tbl
        );

Line: 1125

END Check_Entity_Delete;