APPS.BOM_VALIDATE_RTG_REVISION SQL Statements

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

Line: 41

        *                 transaction type is Update or Delete and the record
        *                 does not exist then the return status would be an
        *                 error as well. Mesg_Token_Table will carry the
        *                 error messsage and the tokens associated with the
        *                 message.
        *********************************************************************/
      PROCEDURE Check_Existence
      (  p_rtg_revision_rec      IN  Bom_Rtg_Pub.Rtg_Revision_Rec_Type
       , p_rtg_rev_unexp_rec     IN  Bom_Rtg_Pub.Rtg_Rev_Unexposed_Rec_Type
       , x_old_rtg_revision_rec  IN OUT NOCOPY Bom_Rtg_Pub.Rtg_Revision_Rec_Type
       , x_old_rtg_rev_unexp_rec IN OUT NOCOPY Bom_Rtg_Pub.Rtg_Rev_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_token_tbl      Error_Handler.Token_Tbl_Type;

Line: 108

        (BOM_RTG_Globals.G_OPR_UPDATE, BOM_Rtg_Globals.G_OPR_DELETE)
        THEN
          l_token_tbl(1).token_name  := 'ASSEMBLY_ITEM_NAME';

Line: 175

        SELECT change_notice
        FROM  mtl_rtg_item_revisions
        WHERE inventory_item_id = p_rtg_rev_unexp_rec.assembly_item_id
        AND organization_id = p_rtg_rev_unexp_rec.organization_id
        AND process_revision = p_rtg_revision_rec.revision;

Line: 194

        IF  p_rtg_revision_rec.transaction_type= BOM_Rtg_Globals.G_OPR_UPDATE
        THEN
          open c_created_by_eco;

Line: 262

         SELECT process_revision
         FROM mtl_rtg_item_revisions
         WHERE inventory_item_id = p_rtg_rev_unexp_rec.assembly_item_id
         AND organization_id = p_rtg_rev_unexp_rec.organization_id
         AND effectivity_date <= sysdate  --added by arudresh, bug: 3756380
	 AND IMPLEMENTATION_DATE IS NOT NULL
         ORDER BY effectivity_date desc, process_revision desc;

Line: 326

            l_token_tbl.delete(3);

Line: 336

            SELECT routing_sequence_id
            INTO l_dummy
            FROM bom_operational_routings
            WHERE assembly_item_id = p_rtg_rev_Unexp_Rec.assembly_item_id
            AND organization_id  =  p_rtg_rev_Unexp_Rec.organization_id
            AND ((alternate_routing_designator is null and p_rtg_revision_rec.alternate_routing_code is null) --modified for 12558505
            or (alternate_routing_designator = p_rtg_revision_rec.alternate_routing_code));

Line: 348

            l_token_tbl.delete(2) ;

Line: 366

            SELECT 1
            INTO l_dummy
            FROM dual
            WHERE not exists
            (SELECT 1
            FROM mtl_rtg_item_revisions
            WHERE organization_id = p_rtg_rev_Unexp_Rec.Organization_Id
            AND inventory_item_id = p_rtg_rev_Unexp_Rec.assembly_Item_Id
            AND process_revision  = p_rtg_revision_Rec.revision
            );

Line: 400

              BOM_Rtg_Globals.G_OPR_UPDATE AND
              p_old_rtg_revision_Rec.start_effective_date <>
              p_rtg_revision_rec.start_effective_date AND
              TRUNC(NVL(p_rtg_revision_rec.start_effective_date,SYSDATE))
              < TRUNC(SYSDATE)
           )
        THEN
          x_return_status := FND_API.G_RET_STS_ERROR;

Line: 417

           l_token_tbl.delete(3) ;

Line: 424

        IF p_rtg_revision_rec.transaction_type <> BOM_Rtg_Globals.G_OPR_DELETE
        THEN
        BEGIN
          SELECT 1
          INTO l_dummy
          FROM dual
          WHERE p_rtg_revision_rec.start_effective_date >
          (SELECT nvl(max(effectivity_date),
          p_rtg_revision_rec.start_effective_Date-1)
          FROM  mtl_rtg_item_revisions
          WHERE inventory_item_id  = p_rtg_rev_Unexp_Rec.assembly_Item_Id
          AND   organization_id    = p_rtg_rev_Unexp_Rec.Organization_Id
          AND   process_revision  < p_rtg_revision_rec.Revision
          )
          AND p_rtg_revision_rec.start_effective_date <
          (SELECT nvl(min(effectivity_date),
          p_rtg_revision_rec.start_effective_Date+1)
          FROM mtl_rtg_item_revisions
          WHERE inventory_item_id =p_rtg_rev_Unexp_Rec.assembly_Item_Id
          AND organization_id   = p_rtg_rev_Unexp_Rec.Organization_Id
          AND process_revision  > p_rtg_revision_rec.Revision
          );

Line: 459

          l_token_tbl.delete(3) ;

Line: 463

        IF p_rtg_revision_rec.transaction_type = BOM_Rtg_Globals.G_OPR_DELETE
        THEN
          SELECT effectivity_date into l_effectivity_date
          FROM mtl_rtg_item_revisions
          WHERE inventory_item_id =p_rtg_rev_Unexp_Rec.assembly_Item_Id
          AND organization_id   = p_rtg_rev_Unexp_Rec.Organization_Id
          AND process_revision  = p_rtg_revision_rec.Revision;

Line: 474

            (  p_message_name       => 'BOM_RTG_REV_CANNOT_DELETE'
             , p_mesg_token_tbl     => l_mesg_token_tbl
             , x_mesg_token_tbl     => l_mesg_token_tbl
             , p_token_tbl          => l_token_tbl
            );