Create Or Replace Procedure Sales_Hist_Update_Bkp Is
  Type Type_Name Is Record(
    Sku_Item_Key            Ordm_Int.Dwi_Rtl_Sls_Retrn_Line_Bkp.Sku_Item_Key%Type,
    Locationno              Ordm_Int.Dwi_Rtl_Sls_Retrn_Line_Bkp.Locationno%Type,
    Bsns_Unit_Key           Ordm_Int.Dwi_Rtl_Sls_Retrn_Line_Bkp.Bsns_Unit_Key%Type,
    Act_Item_Cost_Amt       Ordm_Int.Dwi_Rtl_Sls_Retrn_Line_Bkp.Item_Cost_Amt%Type,
    Act_Rglr_Unit_Price_Amt Ordm_Int.Dwi_Rtl_Sls_Retrn_Line_Bkp.Rglr_Unit_Price_Amt%Type,
    Cost_Cal_Amt            Ordm_Int.Dwi_Rtl_Sls_Retrn_Line_Bkp.Rglr_Unit_Price_Amt%Type,
    Rtl_Cal_Amt             Ordm_Int.Dwi_Rtl_Sls_Retrn_Line_Bkp.Rglr_Unit_Price_Amt%Type,
    Rtl_Cal_Amt1            Ordm_Int.Dwi_Rtl_Sls_Retrn_Line_Bkp.Rglr_Unit_Price_Amt%Type,
    Unitssold               Stage_Ordm.Dwh_Skulocationbymonth.Unitssold%Type,
    Markdownunitssold       Stage_Ordm.Dwh_Skulocationbymonth.Markdownunitssold%Type);
  Type Type_Name_1 Is Record(
    Trx_Nbr               Ordm_Int.Dwi_Rtl_Sls_Retrn_Line_Bkp.Trx_Nbr%Type,
    Trx_Line_Item_Seq_Nbr Ordm_Int.Dwi_Rtl_Sls_Retrn_Line_Bkp.Trx_Line_Item_Seq_Nbr%Type,
    Markdwn               Ordm_Int.Dwi_Rtl_Sls_Retrn_Line_Bkp.Mrkdn_Amt%Type,
    Markdup               Ordm_Int.Dwi_Rtl_Sls_Retrn_Line_Bkp.Mrkup_Amt%Type);
  t_Type_Name Type_Name;
  Type Rtl_Tbl Is Table Of t_Type_Name%Type Index By Pls_Integer;
  t_Rtl_Tbl_1 Rtl_Tbl;

  --l_Error_Count Number;
  Ex_Dml_Errors Exception;
  Pragma Exception_Init(Ex_Dml_Errors, -24381);
  l_Error_Count1 Number;
  Type Ref_Cursor Is Ref Cursor;
  C1 Ref_Cursor;

  v_Sql Varchar2(4000);
  --cursor to update values for Markdwn, Markdup columns.

Begin

  --Common cursor to update values for Item_Cost_Amt, Rglr_Unit_Price_Amt

  v_Sql := 'Select /*+ leading (b,a,c) */ distinct a.Sku_Item_Key,
           a.Locationno,
           a.Bsns_Unit_Key,
           a.Item_Cost_Amt,
           a.Rglr_Unit_Price_Amt,
           c.Salescostdollars / c.Unitssold Cost_Cal_Amt,
           (c.Salesretaildollars - c.Markdownretaildollars) /
           (c.Unitssold - c.Markdownunitssold) Rtl_Cal_Amt,
           c.Salesretaildollars / c.Unitssold Rtl_Cal_Amt1,
           c.Unitssold,
           c.Markdownunitssold
      From Ordm_Int.Dwi_Rtl_Sls_Retrn_Line_bkp a,
           Stage_Ordm.Dwh_Voyageschedule        b,
           Stage_Ordm.Dwh_Skulocationbymonth    c
     Where a.Bsns_Unit_Key = b.Voyageno
       And a.Locationno = b.Locationno
       And a.Sku_Item_Key = c.Sku
       And a.Locationno = c.Locationno
       And b.Fiscal_Month_Year = c.Month_Date
       And Unitssold != 0
       And a.sku_item_key=1794844 And a.bsns_unit_key=''3208'' And a.locationno=24';-- Query retrieving one row for these values.

  Open C1 For v_Sql || ' ' || ' and a.Item_Cost_Amt is null';
  Loop
    Begin
      Fetch C1 Bulk Collect
        Into t_Rtl_Tbl_1 Limit 10000;
      Exit When C1%Notfound;
    
      Forall Indx In 1 .. t_Rtl_Tbl_1.Count Save Exceptions
      
        Insert Into Temp
          (Sku_Item_Key, Locationno, Bsns_Unit_Key, Cost_Cal_Amt)
        Values
          (t_Rtl_Tbl_1(Indx).Sku_Item_Key,
           t_Rtl_Tbl_1(Indx).Locationno,
           t_Rtl_Tbl_1(Indx).Bsns_Unit_Key,
           t_Rtl_Tbl_1(Indx).Cost_Cal_Amt);-- Zero rows inserted
    
    Exception
      When Ex_Dml_Errors Then
        l_Error_Count1 := Sql%Bulk_Exceptions.Count;
        Dbms_Output.Put_Line('Number of failures l_Error_Count1: ' ||
                             l_Error_Count1);
        For i In 1 .. l_Error_Count1 Loop
          Dbms_Output.Put_Line('Error: ' || i || ' Array Index: ' || Sql%Bulk_Exceptions(i)
                               .Error_Index || ' Message: ' ||
                               Sqlerrm(-sql%Bulk_Exceptions(i).Error_Code));
        End Loop;
    End;
    Commit;
  End Loop;
  Close C1;

End;

CREATE OR REPLACE Procedure Sales_Hist_Update_bkp Is
  Type Type_Name Is Record(
    Sku_Item_Key            Ordm_Int.Dwi_Rtl_Sls_Retrn_Line_bkp.Sku_Item_Key%Type,
    Locationno              Ordm_Int.Dwi_Rtl_Sls_Retrn_Line_bkp.Locationno%Type,
    Bsns_Unit_Key           Ordm_Int.Dwi_Rtl_Sls_Retrn_Line_bkp.Bsns_Unit_Key%Type,
    Act_Item_Cost_Amt       Ordm_Int.Dwi_Rtl_Sls_Retrn_Line_bkp.Item_Cost_Amt%Type,
    Act_Rglr_Unit_Price_Amt Ordm_Int.Dwi_Rtl_Sls_Retrn_Line_bkp.Rglr_Unit_Price_Amt%Type,
    Cost_Cal_Amt            Ordm_Int.Dwi_Rtl_Sls_Retrn_Line_bkp.Rglr_Unit_Price_Amt%Type,
    Rtl_Cal_Amt             Ordm_Int.Dwi_Rtl_Sls_Retrn_Line_bkp.Rglr_Unit_Price_Amt%Type,
    Rtl_Cal_Amt1            Ordm_Int.Dwi_Rtl_Sls_Retrn_Line_bkp.Rglr_Unit_Price_Amt%Type,
    Unitssold               Stage_Ordm.Dwh_Skulocationbymonth.Unitssold%Type,
    Markdownunitssold       Stage_Ordm.Dwh_Skulocationbymonth.Markdownunitssold%Type);
  Type Type_Name_1 Is Record(
    Trx_Nbr               Ordm_Int.Dwi_Rtl_Sls_Retrn_Line_bkp.Trx_Nbr%Type,
    Trx_Line_Item_Seq_Nbr Ordm_Int.Dwi_Rtl_Sls_Retrn_Line_bkp.Trx_Line_Item_Seq_Nbr%Type,
    Markdwn               Ordm_Int.Dwi_Rtl_Sls_Retrn_Line_bkp.Mrkdn_Amt%Type,
    Markdup               Ordm_Int.Dwi_Rtl_Sls_Retrn_Line_bkp.Mrkup_Amt%Type);
  t_Type_Name  Type_Name;
  t_Type_Name1 Type_Name_1;
  Type Rtl_Tbl Is Table Of t_Type_Name%Type Index By Pls_Integer;
  Type Rtl_Tbl_1 Is Table Of t_Type_Name1%Type Index By Pls_Integer;
  t_Rtl_Tbl_1 Rtl_Tbl;
  t_Rtl_Tbl_2 Rtl_Tbl;
  t_Rtl_Tbl_3 Rtl_Tbl;
  t_Rtl_Tbl_4 Rtl_Tbl_1;
  --l_Error_Count Number;
  Ex_Dml_Errors Exception;
  Pragma Exception_Init(Ex_Dml_Errors, -24381);
  l_Error_Count1 Number;
  l_Error_Count2 Number;
  l_Error_Count3 Number;
  l_Error_Count4 Number;
  Type Ref_Cursor Is Ref Cursor;
  C1    Ref_Cursor;
  C2    Ref_Cursor;
  C3    Ref_Cursor;
  v_Sql Varchar2(4000);
  --cursor to update values for Markdwn, Markdup columns.
  Cursor C4 Is
    Select t.Trx_Nbr,
           t.Trx_Line_Item_Seq_Nbr,
           Case
             When t.Act_Unit_Price_Amt < t.Rglr_Unit_Price_Amt Then
              (t.Rglr_Unit_Price_Amt - t.Act_Unit_Price_Amt) * t.Qty
             Else
              Null
           End Markdwn,
           Case
             When t.Act_Unit_Price_Amt > t.Rglr_Unit_Price_Amt Then
              (t.Act_Unit_Price_Amt - t.Rglr_Unit_Price_Amt) * t.Qty
             Else
              Null
           End Markdup
      From Ordm_Int.Dwi_Rtl_Sls_Retrn_Line_bkp t;
Begin

  --Common cursor to update values for Item_Cost_Amt, Rglr_Unit_Price_Amt

  v_Sql := 'Select /*+ leading (b,a,c) */ distinct a.Sku_Item_Key,
           a.Locationno,
           a.Bsns_Unit_Key,
           a.Item_Cost_Amt,
           a.Rglr_Unit_Price_Amt,
           c.Salescostdollars / c.Unitssold Cost_Cal_Amt,
           (c.Salesretaildollars - c.Markdownretaildollars) /
           (c.Unitssold - c.Markdownunitssold) Rtl_Cal_Amt,
           c.Salesretaildollars / c.Unitssold Rtl_Cal_Amt1,
           c.Unitssold,
           c.Markdownunitssold
      From Ordm_Int.Dwi_Rtl_Sls_Retrn_Line_bkp a,
           Stage_Ordm.Dwh_Voyageschedule        b,
           Stage_Ordm.Dwh_Skulocationbymonth    c
     Where a.Bsns_Unit_Key = b.Voyageno
       And a.Locationno = b.Locationno
       And a.Sku_Item_Key = c.Sku
       And a.Locationno = c.Locationno
       And b.Fiscal_Month_Year = c.Month_Date
       And Unitssold != 0
       And a.sku_item_key=1794844 And a.bsns_unit_key=''3208'' And a.locationno=24';

 Open C1 For v_Sql || ' ' || ' and a.Item_Cost_Amt is null';
  Loop
    Begin
      Fetch C1 Bulk Collect
        Into t_Rtl_Tbl_1 Limit 10000;
      Exit When C1%Notfound;

      Forall Indx In 1 .. t_Rtl_Tbl_1.Count Save Exceptions

                         --Update Dwi_Rtl_Sls_Retrn_Line_bkp for Item_Cost_Amt values from cusror where Item_Cost_Amt Is Null
                          
        Update Ordm_Int.Dwi_Rtl_Sls_Retrn_Line_bkp t
           Set t.Item_Cost_Amt = t_Rtl_Tbl_1(Indx).Cost_Cal_Amt
         Where t.Sku_Item_Key = t_Rtl_Tbl_1(Indx).Sku_Item_Key
           And t.Locationno = t_Rtl_Tbl_1(Indx).Locationno
           And t.Bsns_Unit_Key = t_Rtl_Tbl_1(Indx).Bsns_Unit_Key
           And t.Item_Cost_Amt Is Null;

    Exception
      When Ex_Dml_Errors Then
        l_Error_Count1 := Sql%Bulk_Exceptions.Count;
        Dbms_Output.Put_Line('Number of failures l_Error_Count1: ' ||
                             l_Error_Count1);
        For i In 1 .. l_Error_Count1 Loop
          Dbms_Output.Put_Line('Error: ' || i || ' Array Index: ' || Sql%Bulk_Exceptions(i)
                               .Error_Index || ' Message: ' ||
                               Sqlerrm(-sql%Bulk_Exceptions(i).Error_Code));
        End Loop;
    End;
    Commit;
  End Loop;
  Close C1;

Open C2 For v_Sql || ' ' || ' and c.Unitssold > c.Markdownunitssold And  a.Rglr_Unit_Price_Amt Is Null';
  Loop
    Begin
      Fetch C2 Bulk Collect
        Into t_Rtl_Tbl_2 Limit 10000;
      Exit When C2%Notfound;

      Forall Indx In 1 .. t_Rtl_Tbl_2.Count Save Exceptions

                         --Update Dwi_Rtl_Sls_Retrn_Line_bkp for Rglr_Unit_Price_Amt values from cusror where Rglr_Unit_Price_Amt Is Null and
                         --Unitssold > Markdownunitssold of Dwh_Skulocationbymonth table
                          
                          Update Ordm_Int.Dwi_Rtl_Sls_Retrn_Line_bkp t
           Set t.Rglr_Unit_Price_Amt = t_Rtl_Tbl_2(Indx).Rtl_Cal_Amt
         Where t.Sku_Item_Key = t_Rtl_Tbl_2(Indx).Sku_Item_Key
           And t.Locationno = t_Rtl_Tbl_2(Indx).Locationno
           And t.Bsns_Unit_Key =t_Rtl_Tbl_2(Indx).Bsns_Unit_Key
           And t.Rglr_Unit_Price_Amt Is Null;

    Exception
      When Ex_Dml_Errors Then
        l_Error_Count2 := Sql%Bulk_Exceptions.Count;
        Dbms_Output.Put_Line('Number of failures l_Error_Count2: ' ||
                             l_Error_Count2);
        For i In 1 .. l_Error_Count2 Loop
          Dbms_Output.Put_Line('Error: ' || i || ' Array Index: ' || Sql%Bulk_Exceptions(i)
                               .Error_Index || ' Message: ' ||
                               Sqlerrm(-sql%Bulk_Exceptions(i).Error_Code));
        End Loop;
    End;
    Commit;
  End Loop;
  Close C2;

  Open C3 For v_Sql || ' ' || ' and  c.Unitssold <= c.Markdownunitssold And  a.Rglr_Unit_Price_Amt Is Null';
  Loop
    Begin
      Fetch C3 Bulk Collect
        Into t_Rtl_Tbl_3 Limit 10000;
      Exit When C3%Notfound;

      Forall Indx In 1 .. t_Rtl_Tbl_3.Count Save Exceptions

                         --Update Dwi_Rtl_Sls_Retrn_Line_bkp for Rglr_Unit_Price_Amt values from cusror where Rglr_Unit_Price_Amt Is Null and
                         --Unitssold <= Markdownunitssold of Dwh_Skulocationbymonth table
                          Update Ordm_Int.Dwi_Rtl_Sls_Retrn_Line_bkp t
           Set t.Rglr_Unit_Price_Amt = t_Rtl_Tbl_3(Indx).Rtl_Cal_Amt
         Where t.Sku_Item_Key = t_Rtl_Tbl_3(Indx).Sku_Item_Key
           And t.Locationno = t_Rtl_Tbl_3(Indx).Locationno
           And t.Bsns_Unit_Key = t_Rtl_Tbl_3(Indx).Bsns_Unit_Key
           And t.Rglr_Unit_Price_Amt Is Null;

    Exception
      When Ex_Dml_Errors Then
        l_Error_Count3 := Sql%Bulk_Exceptions.Count;
        Dbms_Output.Put_Line('Number of failures l_Error_Count3: ' ||
                             l_Error_Count3);
        For i In 1 .. l_Error_Count3 Loop
          Dbms_Output.Put_Line('Error: ' || i || ' Array Index: ' || Sql%Bulk_Exceptions(i)
                               .Error_Index || ' Message: ' ||
                               Sqlerrm(-sql%Bulk_Exceptions(i).Error_Code));
        End Loop;
    End;
    Commit;
  End Loop;
  Close C3;

  Open C4;
  Loop
    Begin
      Fetch C4 Bulk Collect
        Into t_Rtl_Tbl_4 Limit 10000;
      Exit When C4%Notfound;
      --Update markdwn and markup values for table Dwi_Rtl_Sls_Retrn_Line_bkp from cursor 2.
      --This update is done after updation of Item_Cost_Amt, Rglr_Unit_Price_Amt values.
      Forall Indx In 1 .. t_Rtl_Tbl_4.Count Save Exceptions

                          Update Ordm_Int.Dwi_Rtl_Sls_Retrn_Line_bkp t
           Set t.Mrkdn_Amt = t_Rtl_Tbl_4(Indx).Markdwn,
               t.Mrkup_Amt = t_Rtl_Tbl_4(Indx).Markdup
         Where t.Trx_Nbr = t_Rtl_Tbl_4(Indx).Trx_Nbr
           And t.Trx_Line_Item_Seq_Nbr = t_Rtl_Tbl_4(Indx).Trx_Line_Item_Seq_Nbr;

    Exception
      When Ex_Dml_Errors Then
        l_Error_Count4 := Sql%Bulk_Exceptions.Count;
        Dbms_Output.Put_Line('Number of failures l_Error_Count4: ' ||
                             l_Error_Count4);
        For i In 1 .. l_Error_Count4 Loop
          Dbms_Output.Put_Line('Error: ' || i || ' Array Index: ' || Sql%Bulk_Exceptions(i)
                               .Error_Index || ' Message: ' ||
                               Sqlerrm(-sql%Bulk_Exceptions(i).Error_Code));
        End Loop;
    End;
    Commit;
  End Loop;
  Close C4;

End;

Fetch C1 Bulk Collect
        Into t_Rtl_Tbl_1 Limit 10000;
      Exit When C1%Notfound;

EXIT WHEN t_Rtl_Tbl_4.Count = 0