Home » SQL & PL/SQL » SQL & PL/SQL » need help.. (Oracle 10.1.2.6)
icon5.gif  need help.. [message #577282] Thu, 14 February 2013 00:32 Go to next message
xWarriors
Messages: 9
Registered: February 2013
Junior Member
So i got code looking like this

BEGIN
    vt_Prs_Code.DELETE;
    vt_Ist_Code.DELETE;
    vt_Nmr_Code.DELETE;
    vt_Ctb_Period.DELETE;
    vt_Ctb_Object.DELETE;  
    vt_Pens_Capital.DELETE;     
    vt_Ctb_Income.DELETE;
    vt_Ctb_Rate.DELETE;
    vt_Ctb_Paid_Amount.DELETE;
    vt_Ctr_Name.DELETE;
    vt_Ctb_Id.DELETE;  
    vt_CtbIsInsured.DELETE;
    
    vt_Prs_Code_1.DELETE;
    vt_Ist_Code_1.DELETE;
    vt_Nmr_Code_1.DELETE;
    vt_Ctb_Period_1.DELETE;
    vt_Ctb_Object_1.DELETE;  
    vt_Pens_Capital_1.DELETE;  
    vt_Ctb_Income_1.DELETE;
    vt_Ctb_Rate_1.DELETE;
    vt_Ctb_Paid_Amount_1.DELETE;
    vt_Ctr_Name_1.DELETE;
    vt_Ctb_Id_1.DELETE;
    vt_CtbIsInsured_1.DELETE;
    
    vt_Ipr_Id.DELETE; 
    
    v_Tmp_Indx := 1;
    --0. 
    v_Disability := CASE WHEN p_Ins_Kind = 'P' THEN 0 ELSE 1 END; 
    --1.Palaižām kursoru, kurā izpildes laikā tiek atlasīta nepieciešama iemaksa
    --
    
    FOR vr_Ctb_Rec IN Cur_Get_Ctb_By_Ipr_Status(p_Status_Kind             => p_Status_Kind
                                               ,p_Selfemployed            => p_Selfemployed
                                               ,p_Service_Date            => p_Service_Date
                                               ,p_Ins_Kind                => p_Ins_Kind
                                               ,p_End_Date                => p_End_Date
                                               ,p_Selfemployed_End_Date   => p_Selfemployed_End_Date
                                               ,p_Selfemployed_Start_Date => p_Selfemployed_Start_Date
                                               ,p_Start_Date              => p_Start_Date
                                               ,p_Prs_Code                => p_Prs_Code
                                               --,p_Child_Birth_Date        => p_Child_Birth_Date
                                               ) 
    LOOP
    
        IF vr_Ctb_Rec.calculated_amount <> 0 OR vr_Ctb_Rec.income <> 0 THEN
            -- Izpilām funkciju "Aprēķināt 1.līmeņa pensijas kapitālu" (S.F.CS.1.4.5)
            --
            v_Return := Co_Interfaces.Calc_1Pens_Cap(p_Ctb_Id       => vr_Ctb_Rec.ctb_Id
                                                    ,p_Disability   => v_Disability
                                                    ,p_Pens_Cap     => v_Pens_Cap
                                                    ,p_Pens_Cap_Obj => v_Pens_Cap_Obj);   
     
     
     vkern.log_msg (code => 'F.CO.5.15.1', MESSAGE => 'vr_Ctb_Rec.ctb_Id' || ' => ' || vr_Ctb_Rec.ctb_Id);      
     vkern.log_msg (code => 'F.CO.5.15.1', MESSAGE => 'v_Disability' || ' => ' || v_Disability);
     vkern.log_msg (code => 'F.CO.5.15.1', MESSAGE => 'v_Pens_Cap' || ' => ' || v_Pens_Cap);
     vkern.log_msg (code => 'F.CO.5.15.1', MESSAGE => 'v_Pens_Cap_Obj' || ' => ' || v_Pens_Cap_Obj);
        
            vt_Ist_Code(v_Tmp_Indx)        := vr_Ctb_Rec.Ctb_Ist_Code;
            vt_Ctb_Object(v_Tmp_Indx)      := v_Pens_Cap_Obj;  
            vt_Pens_Capital(v_Tmp_Indx)    := v_Pens_Cap;
        ELSE
            vt_Ist_Code(v_Tmp_Indx)        := vr_Ctb_Rec.Ctb_Ist_Code;--'98';
            vt_Ctb_Object(v_Tmp_Indx)      := 0;  
            vt_Pens_Capital(v_Tmp_Indx)    := 0;
        END IF;
        --2.No katras atlasītas "iemaksas" veidojām "apdrošināšanas ierakstu"
        --
        
        vt_Prs_Code(v_Tmp_Indx)        := p_Prs_Code;
        --vt_Ist_Code(v_Tmp_Indx)        := vr_Ctb_Rec.Ctb_Ist_Code;
        vt_Nmr_Code(v_Tmp_Indx)        := vr_Ctb_Rec.Ctr_Code;
        vt_Ctb_Period(v_Tmp_Indx)      := TO_CHAR(vr_Ctb_Rec.ctb_Payment_Date,'RRRRMM');
        --vt_Ctb_Object(v_Tmp_Indx)      := v_Pens_Cap_Obj;  
        --vt_Pens_Capital(v_Tmp_Indx)    := v_Pens_Cap; 
        vt_Ctb_Income(v_Tmp_Indx)      := vr_Ctb_Rec.Ctb_Income;
        vt_Ctb_Rate(v_Tmp_Indx)        := vr_Ctb_Rec.Ctb_Rate;
        vt_Ctb_Paid_Amount(v_Tmp_Indx) := vr_Ctb_Rec.Ctb_Paid_Amount;
        vt_Ctr_Name(v_Tmp_Indx)        := vr_Ctb_Rec.Ctr_Name;
        vt_Ctb_Id(v_Tmp_Indx)          := vr_Ctb_Rec.Ctb_Id;
        vt_CtbIsInsured(v_Tmp_Indx)    := 0;

        v_Tmp_Indx := v_Tmp_Indx + 1;
    END LOOP; 
    
    --2.1. 
    IF p_Ins_Kind = 'B'
    THEN     
        FOR vr_ipr IN Ins_Periods_Maint.cur_Get_Period_of_Child_Care( p_Prs_Code => p_Prs_Code
                                                                    ,p_End_Date => NVL(p_End_Date, TO_DATE('01.3000','MM.RRRR')) )
        LOOP
            vt_Prs_Code(v_Tmp_Indx)        := p_Prs_Code;
            vt_Ist_Code(v_Tmp_Indx)        := vr_ipr.Ist_Code;
            vt_Nmr_Code(v_Tmp_Indx)        := '10000000001';
            vt_Ctb_Period(v_Tmp_Indx)      := TO_CHAR(vr_ipr.start_date,'RRRRMM');        
            vt_Ctb_Object(v_Tmp_Indx)      := vr_ipr.income;                                                    
            vt_Pens_Capital(v_Tmp_Indx)    := 0;  
            vt_Ctb_Income(v_Tmp_Indx)      := vr_ipr.income;     
            vt_Ctb_Rate(v_Tmp_Indx)        := NULL;
            vt_Ctb_Paid_Amount(v_Tmp_Indx) := NULL;
            vt_Ctr_Name(v_Tmp_Indx)        := NULL;
            vt_Ctb_Id(v_Tmp_Indx)          := NULL;
            vt_CtbIsInsured(v_Tmp_Indx)    := 0;
                
            v_Tmp_Indx := v_Tmp_Indx + 1;            
        END LOOP;
    END IF;     
    
    --3.Pārbaudām, vai "apdrošināšanas veids" nav ('B','I','P')
    --
    IF p_Ins_Kind IN ('P','B') THEN 
        --Izpildām funkciju "Izveidot darba ņēmēja iemaksas minimālās algas apmērā" (S.F.CO.5.15.1.1)
        --
        v_Return := Create_Ctr_Contributions(p_Prs_Code      => p_Prs_Code
                                            ,p_Start_Date    => NVL(p_Start_Date,TO_DATE('01.1000','MM.RRRR'))
                                            ,p_End_Date      => NVL(p_End_Date,TO_DATE('01.3000','MM.RRRR'))
                                            ,pt_Prs_Code     => vt_Prs_Code_1
                                            ,pt_Nmr_Code     => vt_Nmr_Code_1
                                            ,pt_Ist_Code     => vt_Ist_Code_1
                                            ,pt_Ctb_Period   => vt_Ctb_Period_1
                                            ,pt_Ctb_Object   => vt_Ctb_Object_1
                                            ,pt_Pens_Capital => vt_Pens_Capital_1
                                            ,pt_period_start_date => vt_period_start_date
                                            ,pt_period_end_date => vt_period_end_date
                                            ,pt_Ipr_Id       => vt_Ipr_Id
                                            ,p_Ins_Kind      => p_Ins_Kind);      
        --Pievienojām izveidotas iemaksas tam, kuras bija veidotas 2 punktā
        --
        IF vt_Prs_Code_1.COUNT > 0 THEN
            FOR v_Tmp_1_Indx IN vt_Prs_code_1.FIRST..vt_Prs_Code_1.LAST LOOP
                vt_Prs_Code(v_Tmp_Indx)        := vt_Prs_Code_1(v_Tmp_1_Indx);
                vt_Ist_Code(v_Tmp_Indx)        := vt_Ist_Code_1(v_Tmp_1_Indx);
                vt_Nmr_Code(v_Tmp_Indx)        := vt_Nmr_Code_1(v_Tmp_1_Indx);
                vt_Ctb_Period(v_Tmp_Indx)      := vt_Ctb_Period_1(v_Tmp_1_Indx);
                vt_Ctb_Object(v_Tmp_Indx)      := vt_Ctb_Object_1(v_Tmp_1_Indx);  --NULL;    PZ12_25020       
                vt_Pens_Capital(v_Tmp_Indx)    := vt_Pens_Capital_1(v_Tmp_1_Indx);  
                vt_Ctb_Income(v_Tmp_Indx)      := NULL; --vt_Ctb_Object_1(v_Tmp_1_Indx);     PZ12_25020     
                vt_Ctb_Rate(v_Tmp_Indx)        := NULL;
                vt_Ctb_Paid_Amount(v_Tmp_Indx) := NULL;
                vt_Ctr_Name(v_Tmp_Indx)        := NULL;
                vt_Ctb_Id(v_Tmp_Indx)          := NULL;
                vt_CtbIsInsured(v_Tmp_Indx)    := 0;
                v_Tmp_Indx := v_Tmp_Indx + 1;
            END LOOP;
        END IF;
                  
    vt_Prs_Code_1.DELETE;
    vt_Ist_Code_1.DELETE;
    vt_Nmr_Code_1.DELETE;
    vt_Ctb_Period_1.DELETE;
    vt_Ctb_Object_1.DELETE;  
    vt_Pens_Capital_1.DELETE;  
    vt_Ctb_Income_1.DELETE;
    vt_Ctb_Rate_1.DELETE;
    vt_Ctb_Paid_Amount_1.DELETE;
    vt_Ctr_Name_1.DELETE;
    vt_Ctb_Id_1.DELETE;
    vt_CtbIsInsured_1.DELETE;              
                  
        --4.Pārbaudām, vai "apdrošināšanas veids" nav 'M'
        --4.1.Jā "apdrošināšanas veids" = 'M'
        --
    END IF;
    IF (p_Ins_Kind = 'M') AND (NVL(p_Sharing,0) = 0) THEN          
        --4.1.1.Pārbaudām vai 2.punkta izveidota "apdrošināšanas iemaksas" kopa nav tukša
        --
        IF vt_Ctb_Period.COUNT > 0 THEN
            --Izpildīt funkciju "Aprēkināt vidējo iemaksu algu bērna kopšanas pabalstam" (S.F.CO.5.15.1.2)
            --
            v_Return := Calc_Avg_Ctb_For_B(p_Prs_Code         => p_Prs_Code   
                                          ,p_Status_Date      => p_Status_Date 
                                          ,p_Child_Birth_Date => p_Service_Date
                                          ,pt_Ctb_Period_In   => vt_Ctb_Period
                                          ,pt_Ctb_Object_In   => vt_Ctb_Object
                                          ,pt_Ctb_Id_In       => vt_Ctb_Id
                                          ,pt_Prs_Code_Out    => vt_Prs_Code_1
                                          ,pt_Ctb_Period_Out  => vt_Ctb_Period_1
                                          ,pt_Ctb_Object_Out  => vt_Ctb_Object_1); 
               
            --Pieškirām apdrošināšanas iemaksas
            --
            IF vt_Prs_Code_1.COUNT > 0 THEN
                vt_Prs_Code     := vt_Prs_Code_1;
                vt_Ctb_Period   := vt_Ctb_Period_1;
                vt_Ctb_Object   := vt_Ctb_Object_1;  
                    
                --Aizpildām ar NULL vērtībām tās tabulas, kuras nebija apstrādātas procedūras ipildes laikā
                FOR v_Counter IN vt_Prs_Code_1.FIRST..vt_Prs_Code_1.LAST LOOP
                    vt_Ist_Code(v_Counter)         := NULL;
                    vt_Nmr_Code(v_Counter)         := NULL; 
                    vt_Pens_Capital(v_Counter)     := NULL;  
                    vt_Ctb_Income(v_Tmp_Indx)      := NULL;
                    vt_Ctb_Rate(v_Tmp_Indx)        := NULL;
                    vt_Ctb_Paid_Amount(v_Tmp_Indx) := NULL;
                    vt_Ctr_Name(v_Tmp_Indx)        := NULL;
                    vt_Ctb_Id(v_Tmp_Indx)          := NULL; 
                    vt_CtbIsInsured(v_Tmp_Indx)    := 0;
                END LOOP;
            ELSE 
                vt_Prs_Code.DELETE;
                vt_Ctb_Period.DELETE;
                vt_Ctb_Object.DELETE;
                vt_Ist_Code.DELETE;
                vt_Nmr_Code.DELETE;
                vt_Pens_Capital.DELETE; 
                vt_Ctb_Income.DELETE;
                vt_Ctb_Rate.DELETE;
                vt_Ctb_Paid_Amount.DELETE;
                vt_Ctr_Name.DELETE;
                vt_Ctb_Id.DELETE;
                vt_CtbIsInsured.DELETE;
            END IF;
        END IF;     -- vt_Ctb_Period.COUNT > 0
    ELSIF (p_Ins_Kind IN ('S','A')) AND (NVL(p_Sharing,0) = 1) THEN          
        --4.2.1.Pārbaudām vai 2.punkta izveidota "apdrošināšanas iemaksas" kopa nav tukša
        --
        IF vt_Ctb_Period.COUNT > 0 THEN
            --Izpildīt funkciju "Apstrādāt iemaksu objekta maksimumu" (S.F.CO.5.15.1.3)
            v_Return := Calc_Ctb_Salary_Max(p_Prs_Code            => p_Prs_Code   
                                           ,p_Service_Date        => p_Service_Date
                                           ,pt_Ctb_Id_In          => vt_Ctb_Id
                                           ,pt_Ctb_Period_In      => vt_Ctb_Period
                                           ,pt_Ctb_Object_In      => vt_Ctb_Object
                                           ,pt_Nmr_Code_In        => vt_Nmr_Code
                                           ,pt_Ist_Code_In        => vt_Ist_Code
                                           ,pt_Prs_Code_Out       => vt_Prs_Code_1
                                           ,pt_Ctb_Period_Out     => vt_Ctb_Period_1
                                           ,pt_Ctb_Object_Out     => vt_Ctb_Object_1
                                           ,pt_Nmr_Code_Out       => vt_Nmr_Code_1
                                           ,pt_Ist_Code_Out       => vt_Ist_Code_1
                                           ,pt_Pens_Capital_Out   => vt_Pens_Capital_1
                                           ,pt_Ctb_Is_Insured_In  => vt_CtbIsInsured
                                           ,pt_Ctb_Is_Insured_Out => vt_CtbIsInsured_1); 
               
            --Pieškirām apdrošināšanas iemaksas
            --
            vt_Prs_Code.DELETE;
            vt_Ctb_Period.DELETE;
            vt_Ctb_Object.DELETE;
            vt_Ist_Code.DELETE;
            vt_Nmr_Code.DELETE;
            vt_Pens_Capital.DELETE; 
            vt_Ctb_Income.DELETE;
            vt_Ctb_Rate.DELETE;
            vt_Ctb_Paid_Amount.DELETE;
            vt_Ctr_Name.DELETE;
            vt_Ctb_Id.DELETE;
            vt_CtbIsInsured.DELETE;
            IF vt_Prs_Code_1.COUNT > 0 THEN
                vt_Prs_Code     := vt_Prs_Code_1;
                vt_Ctb_Period   := vt_Ctb_Period_1;
                vt_Ctb_Object   := vt_Ctb_Object_1;  
                vt_Ist_Code     := vt_Ist_Code_1;
                vt_Nmr_Code     := vt_Nmr_Code_1; 
                vt_Pens_Capital := vt_Pens_Capital_1;
                vt_CtbIsInsured := vt_CtbIsInsured_1;    
                --Aizpildām ar NULL vērtībām tās tabulas, kuras nebija apstrādātas procedūras ipildes laikā
                FOR v_Counter IN vt_Prs_Code_1.FIRST..vt_Prs_Code_1.LAST LOOP
                    vt_Ctb_Income(v_Counter)      := vt_Ctb_Object_1(v_Counter);
                    vt_Ctb_Rate(v_Counter)        := NULL;
                    vt_Ctb_Paid_Amount(v_Counter) := NULL;
                    vt_Ctr_Name(v_Counter)        := NULL;
                    vt_Ctb_Id(v_Counter)          := NULL;
                END LOOP;
            END IF;
        END IF;
    
    --ELSIF p_Ins_Kind NOT IN ('P','I','B','M') THEN
    ELSIF (p_Ins_Kind <> 'B' AND NVL(p_Sharing,0) = 0) THEN
        --4.2.2.1.Pārbaudām vai kopa "apdrošināšanas iemaksas" nav tukša
        -- 
        IF (vt_NMR_Code.COUNT > 0) AND (NVL(p_Sharing,0) = 0) THEN
            v_Group_Indx := 1;
            FOR v_Indx IN vt_Nmr_Code.FIRST..vt_Nmr_Code.LAST LOOP
                v_Group_Exists := FALSE;
                
                IF vt_Nmr_Code_1.COUNT > 0 THEN 
                    FOR v_Indx_1 IN vt_Nmr_Code_1.FIRST..vt_Nmr_Code_1.LAST LOOP
                        IF (vt_Nmr_Code_1(v_Indx_1) = vt_Nmr_Code(v_Indx)) AND
                           (vt_Ctb_Period_1(v_Indx_1) = vt_Ctb_Period(v_Indx)) AND
                           (vt_Ist_Code_1(v_Indx_1) = vt_Ist_Code(v_Indx))
                        THEN
                            v_Group_Exists := TRUE;
                            CASE WHEN vt_Ctb_Income_1.EXISTS(v_Indx_1)
                                 THEN vt_Ctb_Income_1(v_Indx_1) := vt_Ctb_Income_1(v_Indx_1) + vt_Ctb_Income(v_Indx);
                                 ELSE vt_Ctb_Income_1(v_Indx_1) := vt_Ctb_Income(v_Indx);
                            END CASE;
                            vt_Pens_Capital_1(v_Indx_1) := vt_Pens_Capital_1(v_Indx_1) + vt_Pens_Capital(v_Indx);
                            vt_Ctb_Object_1(v_Indx_1) := vt_Ctb_Object_1(v_Indx_1) + vt_Ctb_Object(v_Indx);
                            EXIT;
                        END IF;
                    END LOOP;
                ELSE
                    vt_Nmr_Code_1(v_Group_Indx)        := vt_Nmr_Code(v_Indx);
                    vt_Ctb_Period_1(v_Group_Indx)      := vt_Ctb_Period(v_Indx);
                    vt_Ist_Code_1(v_Group_Indx)        := vt_Ist_Code(v_Indx);
                    vt_Prs_Code_1(v_Group_Indx)        := vt_Prs_Code(v_Indx);
                    vt_Ctb_Object_1(v_Group_Indx)      := vt_Ctb_Object(v_Indx);
                    vt_Pens_Capital_1(v_Group_Indx)    := vt_Pens_Capital(v_Indx);
                    vt_Ctb_Income_1(v_Group_Indx)      := vt_Ctb_Income(v_Indx);
                    vt_Ctb_Rate_1(v_Group_Indx)        := vt_Ctb_Rate(v_Indx);
                    vt_Ctb_Paid_Amount_1(v_Group_Indx) := vt_Ctb_Paid_Amount(v_Indx);
                    vt_Ctr_Name_1(v_Group_Indx)        := vt_Ctr_Name(v_Indx);
                    vt_Ctb_Id_1(v_Group_Indx)          := vt_Ctb_Id(v_Indx);
                    vt_CtbIsInsured_1(v_Group_Indx)    := vt_CtbIsInsured(v_Indx);
                    v_Group_Indx   := v_Group_Indx + 1;
                    v_Group_Exists := TRUE;
                END IF;
                
                IF v_Group_Exists = FALSE THEN
                    vt_Nmr_Code_1(v_Group_Indx)        := vt_Nmr_Code(v_Indx);
                    vt_Ctb_Period_1(v_Group_Indx)      := vt_Ctb_Period(v_Indx);
                    vt_Ist_Code_1(v_Group_Indx)        := vt_Ist_Code(v_Indx);
                    vt_Prs_Code_1(v_Group_Indx)        := vt_Prs_Code(v_Indx);
                    vt_Ctb_Object_1(v_Group_Indx)      := vt_Ctb_Object(v_Indx);
                    vt_Pens_Capital_1(v_Group_Indx)    := vt_Pens_Capital(v_Indx);
                    vt_Ctb_Income_1(v_Group_Indx)      := vt_Ctb_Income(v_Indx);
                    vt_Ctb_Rate_1(v_Group_Indx)        := vt_Ctb_Rate(v_Indx);
                    vt_Ctb_Paid_Amount_1(v_Group_Indx) := vt_Ctb_Paid_Amount(v_Indx);
                    vt_Ctr_Name_1(v_Group_Indx)        := vt_Ctr_Name(v_Indx);
                    vt_Ctb_Id_1(v_Group_Indx)          := vt_Ctb_Id(v_Indx);  
                    vt_CtbIsInsured_1(v_Group_Indx)    := vt_CtbIsInsured(v_Indx);
                    v_Group_Indx := v_Group_Indx + 1;
                END IF;
            
            END LOOP;
        END IF;    -- vt_NMR_Code.COUNT > 0) AND (NVL(p_Sharing,0) = 0
        
        IF vt_Nmr_Code_1.COUNT > 0 THEN
            vt_Prs_Code.DELETE;
            vt_Ist_Code.DELETE;
            vt_Nmr_Code.DELETE;
            vt_Ctb_Period.DELETE;
            vt_Ctb_Object.DELETE;  
            vt_Pens_Capital.DELETE;   
            vt_Ctb_Income.DELETE;
            vt_Ctb_Rate.DELETE;
            vt_Ctb_Paid_Amount.DELETE;
            vt_Ctr_Name.DELETE;
            vt_Ctb_Id.DELETE;
            vt_CtbIsInsured.DELETE; 
            
            vt_Prs_Code        := vt_Prs_Code_1;
            vt_Ist_Code        := vt_Ist_Code_1;
            vt_Nmr_Code        := vt_Nmr_Code_1;
            vt_Ctb_Period      := vt_Ctb_Period_1;
            vt_Ctb_Object      := vt_Ctb_Object_1;  
            vt_Pens_Capital    := vt_Pens_Capital_1;
            vt_Ctb_Income      := vt_Ctb_Income_1;
            vt_Ctb_Rate        := vt_Ctb_Rate_1;
            vt_Ctb_Paid_Amount := vt_Ctb_Paid_Amount_1;
            vt_Ctr_Name        := vt_Ctr_Name_1;
            vt_Ctb_Id          := vt_Ctb_Id_1;
            vt_CtbIsInsured    := vt_CtbIsInsured_1;
        END IF;
    
    ELSIF p_Ins_Kind = 'B' THEN          
        --4.2.2.2.1 Pārbaudām vai 2.punkta izveidota "apdrošināšanas iemaksas" kopa nav tukša
        --
        IF vt_Ctb_Period.COUNT > 0 THEN
            --Izpildīt funkciju "Apstrādāt iemaksu objekta maksimumu bezdarbnieka pabalstam" (S.F.CO.5.15.1.4)
            v_Return := Calc_Ctb_Salary_Max_Workless
                                           (p_Prs_Code            => p_Prs_Code    -- !!!
                                           ,p_start_date          => p_start_date
                                           ,p_end_date            => p_end_date
                                           ,p_Service_Date        => p_Service_Date
                                           ,pt_Ctb_Period_In      => vt_Ctb_Period
                                           ,pt_Ctb_Object_In      => vt_Ctb_Object
                                           ,pt_Nmr_Code_In        => vt_Nmr_Code
                                           ,pt_Ist_Code_In        => vt_Ist_Code
                                           ,pt_Ctb_Is_Insured_In  => vt_CtbIsInsured
                                           ,pt_Ctb_Period_Out     => vt_Ctb_Period_1
                                           ,pt_Ctb_Object_Out     => vt_Ctb_Object_1
                                           ,pt_Nmr_Code_Out       => vt_Nmr_Code_1
                                           ,pt_Ist_Code_Out       => vt_Ist_Code_1
                                           ,pt_Ctb_Is_Insured_Out => vt_CtbIsInsured_1); 
               
            --Pieškirām apdrošināšanas iemaksas
            --
            vt_Prs_Code.DELETE;
            vt_Ctb_Period.DELETE;
            vt_Ctb_Object.DELETE;
            vt_Ist_Code.DELETE;
            vt_Nmr_Code.DELETE;
            vt_Pens_Capital.DELETE; 
            vt_Ctb_Income.DELETE;
            vt_Ctb_Rate.DELETE;
            vt_Ctb_Paid_Amount.DELETE;
            vt_Ctr_Name.DELETE;
            vt_Ctb_Id.DELETE; 
            vt_CtbIsInsured.DELETE;
            IF vt_Ctb_Period_1.COUNT > 0 THEN
                vt_Ctb_Period   := vt_Ctb_Period_1;
                vt_Ctb_Object   := vt_Ctb_Object_1;  
                vt_Ist_Code     := vt_Ist_Code_1;
                vt_Nmr_Code     := vt_Nmr_Code_1; 
                vt_CtbIsInsured := vt_CtbIsInsured_1;                    
                --Aizpildām ar NULL vērtībām tās tabulas, kuras nebija apstrādātas procedūras ipildes laikā
                FOR v_Counter IN vt_Ctb_Period_1.FIRST..vt_Ctb_Period_1.LAST LOOP
                    vt_Prs_Code(v_Counter)        := p_Prs_Code;
                    vt_Ctb_Income(v_Counter)      := NULL;
                    vt_Ctb_Rate(v_Counter)        := NULL;
                    vt_Ctb_Paid_Amount(v_Counter) := NULL;
                    vt_Ctr_Name(v_Counter)        := NULL;
                    vt_Ctb_Id(v_Counter)          := NULL;     
                    vt_Pens_Capital(v_Counter)    := NULL;
                END LOOP;
            END IF;
        END IF;     -- vt_Ctb_Period.COUNT > 0
        
    END IF;     -- p_Ins_Kind IN ('P','I') THEN 
    
    --5.Atgriežam veiksmīgas izpildes kodu un apdrošināšanas iemaksas
    --   
    
    IF vt_Ctb_Period.COUNT > 0 THEN
        pt_Prs_Code        := vt_Prs_Code;
        pt_Ist_Code        := vt_Ist_Code;
        pt_Nmr_Code        := vt_Nmr_Code;
        pt_Ctb_Period      := vt_Ctb_Period;
        pt_Ctb_Object      := vt_Ctb_Object;  
        pt_Pens_Capital    := vt_Pens_Capital;  
        pt_Ctb_Income      := vt_Ctb_Income;
        pt_Ctb_Rate        := vt_Ctb_Rate;
        pt_Ctb_Paid_Amount := vt_Ctb_Paid_Amount;
        pt_Ctr_Name        := vt_Ctr_Name;
        pt_Ctb_Id          := vt_Ctb_Id;
        pt_Ctb_Is_Insured  := vt_CtbIsInsured;   
    END IF;                                                             
    
    
    RETURN 0;
END;


pretty long.. so i need help in this one..
if Income := 0 then it needs to delete a certain row from table, where are income is 0 not some numbers
pic of table..
./fa/10653/0/table

some guy from my work told that the code needs to at the end
  • Attachment: dadada.JPG
    (Size: 64.70KB, Downloaded 155 times)

[Updated on: Thu, 14 February 2013 00:33]

Report message to a moderator

Re: need help.. [message #577284 is a reply to message #577282] Thu, 14 February 2013 00:44 Go to previous messageGo to next message
Littlefoot
Messages: 19541
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quite a lot of code for
delete from contributions
where income = 0

Though, I must have misunderstood the question.
Re: need help.. [message #577292 is a reply to message #577284] Thu, 14 February 2013 01:19 Go to previous messageGo to next message
xWarriors
Messages: 9
Registered: February 2013
Junior Member
now one guy told me that if there is one period where was 180, then next month they send info that income now from that previous month is -180, so in result it's 0, and it's showing up in table as from 31.01.2001 till 26.04.2001, but must be from 01.02.2001 till 26.04.2001
Re: need help.. [message #577295 is a reply to message #577292] Thu, 14 February 2013 01:22 Go to previous messageGo to next message
Michel Cadot
Messages: 58959
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't know for others but I don't understand a single word of what he says.

Regards
Michel

[Updated on: Thu, 14 February 2013 02:14]

Report message to a moderator

Re: need help.. [message #577301 is a reply to message #577295] Thu, 14 February 2013 01:59 Go to previous messageGo to next message
xWarriors
Messages: 9
Registered: February 2013
Junior Member
need to delete rows from output where vt_ctb_income is 0 in set of records

[Updated on: Thu, 14 February 2013 02:11]

Report message to a moderator

Re: need help.. [message #577303 is a reply to message #577301] Thu, 14 February 2013 02:15 Go to previous messageGo to next message
Michel Cadot
Messages: 58959
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
With any SQL or PL/SQL question, please, Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Regards
Michel
Re: need help.. [message #577351 is a reply to message #577303] Thu, 14 February 2013 06:31 Go to previous message
xWarriors
Messages: 9
Registered: February 2013
Junior Member
can close.. founded solution
 v_Indx2 := 0; 
    FOR v_Indx IN vt_Nmr_Code.FIRST..vt_Nmr_Code.LAST LOOP
        IF vt_Ctb_Income(v_Indx) <> 0 THEN            
            v_Indx2 := v_Indx2 + 1;
            pt_Prs_Code(v_Indx2)        := vt_Prs_Code(v_Indx);
            pt_Ist_Code(v_Indx2)        := vt_Ist_Code(v_Indx);
            pt_Nmr_Code(v_Indx2)        := vt_Nmr_Code(v_Indx);
            pt_Ctb_Period(v_Indx2)      := vt_Ctb_Period(v_Indx);
            pt_Ctb_Object(v_Indx2)      := vt_Ctb_Object(v_Indx);  
            pt_Pens_Capital(v_Indx2)    := vt_Pens_Capital(v_Indx);  
            pt_Ctb_Income(v_Indx2)      := vt_Ctb_Income(v_Indx);
            pt_Ctb_Rate(v_Indx2)        := vt_Ctb_Rate(v_Indx);
            pt_Ctb_Paid_Amount(v_Indx2) := vt_Ctb_Paid_Amount(v_Indx);
            pt_Ctr_Name(v_Indx2)        := vt_Ctr_Name(v_Indx);
            pt_Ctb_Id(v_Indx2)          := vt_Ctb_Id(v_Indx);
            pt_Ctb_Is_Insured(v_Indx2)  := vt_CtbIsInsured(v_Indx);   
        END IF;                                
   END LOOP;
Previous Topic: TIMESTAMP data type - how to get only 3 zeros in milli seconds scetion
Next Topic: how to find degree for parallel DML.
Goto Forum:
  


Current Time: Wed Sep 03 01:03:10 CDT 2014

Total time taken to generate the page: 0.10626 seconds