How to use it inside the SELECT?

[ralexsander]

Hello,

I have a procedure that seeks some records in a table and see if any record of this search has sub-records, if you have it erases the line of the reegistro and inserts your child records, this is In a loop until there are no more children records. I wanted to know if it is possible to transform this procedure into a SELECT

Select all

--	Create First Level 
FOR bom IN CUR_BOM (pp.M_Product_ID) LOOP 
    INSERT INTO M_ProductionLine 
            (M_ProductionLine_ID, M_ProductionPlan_ID, Line, 
            AD_Client_ID,AD_Org_ID,IsActive,Created,CreatedBy,Updated,UpdatedBy, 
            M_Product_ID, MovementQty, M_Locator_ID) 
    VALUES 
            (NextNo, pp.M_ProductionPlan_ID, Line, 
            pp.AD_Client_ID,pp.AD_Org_ID,'Y',SysDate,0,SysDate,0, 
            bom.M_ProductBOM_ID, -pp.ProductionQty*bom.BOMQty, pp.M_Locator_ID); 
END LOOP; 
 
--	While we have BOMs 
LOOP 
    --	Are there non-stored BOMs to list details? 
    SELECT COUNT(*) INTO CountNo 
    FROM M_ProductionLine pl, M_Product p 
    WHERE pl.M_Product_ID=p.M_Product_ID 
      AND pl.M_ProductionPlan_ID=pp.M_ProductionPlan_ID 
      AND pl.Line<>100	--	Origin Line 
      AND p.IsBOM='Y' AND p.IsStocked='N'; 
    --	Nothing to do 
    EXIT WHEN (CountNo = 0); 
    -- 
 
    --	Resolve BOMs in ProductLine which are not stocked 
    FOR pl IN CUR_PLineBOM (pp.M_ProductionPlan_ID) LOOP 
      Line := pl.Line; 
      --	Resolve BOM Line in product line 
      FOR bom IN CUR_BOM (pl.M_Product_ID) LOOP 
              ResultStr := 'CreatingLine Products2'; 
              Line := Line + 10; 
              AD_Sequence_Next('M_ProductionLine', pp.AD_Client_ID, NextNo); 
              INSERT INTO M_ProductionLine 
                      (M_ProductionLine_ID, M_ProductionPlan_ID, Line, 
                      AD_Client_ID,AD_Org_ID,IsActive,Created,CreatedBy,Updated,UpdatedBy, 
                      M_Product_ID, MovementQty, M_Locator_ID) 
              VALUES 
                      (NextNo, pp.M_ProductionPlan_ID, Line, 
                      pp.AD_Client_ID,pp.AD_Org_ID,'Y',SysDate,0,SysDate,0, 
                      bom.M_ProductBOM_ID, pl.MovementQty*bom.BOMQty, pp.M_Locator_ID); 
      END LOOP; 
      --	Delete BOM line 
      DELETE  M_ProductionLine 
      WHERE 	M_ProductionLine_ID=pl.M_ProductionLine_ID; 
    END LOOP; 
END LOOP;	--	While we have BOMs

Thanks

[dr_gori]

Well, it is possible to pick up a query with Connect by all records that satisfy x condition.
But this condition is changing at runtime, there only with actual procedure.