API to delete the Price List (qp_price_list_pub.process_price_list)

CREATE OR REPLACE PROCEDURE APPS.geps_price_list_delete_sql
(
errbuf                      OUT VARCHAR2
,retcode                   OUT NUMBER
)
as
cursor rout_det is
select
*
from
geps_price_list_stg
where process_flag=1;
   gpr_return_status                                 VARCHAR2 (1) := NULL;
   gpr_msg_count                                     NUMBER := 0;
   gpr_msg_data                                      VARCHAR2 (2000);
   gpr_price_list_rec                                qp_price_list_pub.price_list_rec_type;
   gpr_price_list_val_rec                            qp_price_list_pub.price_list_val_rec_type;
   gpr_price_list_line_tbl                           qp_price_list_pub.price_list_line_tbl_type;
   gpr_price_list_line_val_tbl                       qp_price_list_pub.price_list_line_val_tbl_type;
   gpr_qualifiers_tbl                                qp_qualifier_rules_pub.qualifiers_tbl_type;
   gpr_qualifiers_val_tbl                            qp_qualifier_rules_pub.qualifiers_val_tbl_type;
   gpr_pricing_attr_tbl                              qp_price_list_pub.pricing_attr_tbl_type;
   gpr_pricing_attr_val_tbl                          qp_price_list_pub.pricing_attr_val_tbl_type;
   ppr_price_list_rec                                qp_price_list_pub.price_list_rec_type;
   ppr_price_list_val_rec                            qp_price_list_pub.price_list_val_rec_type;
   ppr_price_list_line_tbl                           qp_price_list_pub.price_list_line_tbl_type;
   ppr_price_list_line_val_tbl                       qp_price_list_pub.price_list_line_val_tbl_type;
   ppr_qualifiers_tbl                                qp_qualifier_rules_pub.qualifiers_tbl_type;
   ppr_qualifiers_val_tbl                            qp_qualifier_rules_pub.qualifiers_val_tbl_type;
   ppr_pricing_attr_tbl                              qp_price_list_pub.pricing_attr_tbl_type;
   ppr_pricing_attr_val_tbl                          qp_price_list_pub.pricing_attr_val_tbl_type;
   k                                                 NUMBER := 1;
   j                                                 NUMBER := 1;
   v_list_line_id qp_list_lines_v.LIST_LINE_ID%type;
     l_item_id   varchar2(50);
  l_header_id   Number;
  v_err_msg varchar2(500);
  v_count number;
BEGIN

 FND_FILE.PUT_LINE(FND_FILE.LOG,'Start program');
For c in rout_det
Loop
begin
select inventory_item_id
into
l_item_id
from mtl_system_items_b
where segment1=c.Product_value
and organization_id=104;
select list_header_id into l_header_id from qp_list_headers
where name=c.Price_List_Name  ;
begin
select LIST_LINE_ID into v_list_line_id
from qp_list_lines_v
where LIST_HEADER_ID=l_header_id
and product_attr_value=l_item_id;
exception
when no_data_found then
v_list_line_id := null;
update geps_price_list_stg
set process_flag=4,
error_message='no_data_found in pricelist'
where
Product_value =c.Product_value
and Price_List_Name=c.Price_List_Name
and process_flag=c.process_flag;
commit;
when others then
v_list_line_id := null;
end;

 FND_FILE.PUT_LINE(FND_FILE.LOG,'Start program1');

if v_list_line_id is not null then
 FND_FILE.PUT_LINE(FND_FILE.LOG,'Start program2');
   oe_debug_pub.initialize;
   oe_debug_pub.setdebuglevel (5);
   oe_msg_pub.initialize;
--dbms_output.put_line('after get price list ');
   /* setup the list_header rec for update */
   gpr_price_list_rec.list_header_id                        := l_header_id;
   gpr_price_list_rec.NAME                                  := c.Price_List_Name;
   gpr_price_list_rec.list_type_code                        := 'PRL';
   --gpr_price_list_rec.description                           := '<price_list_description>';
   gpr_price_list_rec.operation                             := qp_globals.g_opr_update;
-- delete the price list line rec
   gpr_price_list_line_tbl (k).list_header_id               := l_header_id;
   gpr_price_list_line_tbl (k).list_line_id                 := v_list_line_id;
   gpr_price_list_line_tbl (k).list_line_type_code          := 'PLL';
   gpr_price_list_line_tbl (k).operation                    := qp_globals.g_opr_delete;
--dbms_output.put_line('before process price list ');
   qp_price_list_pub.process_price_list (p_api_version_number          => 1
                                       , p_init_msg_list               => fnd_api.g_false
                                       , p_return_values               => fnd_api.g_false
                                       , p_commit                      => fnd_api.g_false
                                       , x_return_status               => gpr_return_status
                                       , x_msg_count                   => gpr_msg_count
                                       , x_msg_data                    => gpr_msg_data
                                       , p_price_list_rec              => gpr_price_list_rec
                                       , p_price_list_line_tbl         => gpr_price_list_line_tbl
                                       , p_pricing_attr_tbl            => gpr_pricing_attr_tbl
                                       , x_price_list_rec              => ppr_price_list_rec
                                       , x_price_list_val_rec          => ppr_price_list_val_rec
                                       , x_price_list_line_tbl         => ppr_price_list_line_tbl
                                       , x_price_list_line_val_tbl     => ppr_price_list_line_val_tbl
                                       , x_qualifiers_tbl              => ppr_qualifiers_tbl
                                       , x_qualifiers_val_tbl          => ppr_qualifiers_val_tbl
                                       , x_pricing_attr_tbl            => ppr_pricing_attr_tbl
                                       , x_pricing_attr_val_tbl        => ppr_pricing_attr_val_tbl
                                        );

    IF gpr_return_status = fnd_api.g_ret_sts_success THEN
      COMMIT;
FND_FILE.PUT_LINE(FND_FILE.LOG,'The Item deleted from the price list is Sucessfull');
update geps_price_list_stg
set process_flag=2,
error_message='Success'
where
Product_value =c.Product_value
and Price_List_Name=c.Price_List_Name
and process_flag=c.process_flag;
commit;
    ELSE
FND_FILE.PUT_LINE(FND_FILE.LOG,'The Item deleted from the price list Failed');
      ROLLBACK;
   update geps_price_list_stg
set process_flag=3,
error_message='Error'
where
Product_value =c.Product_value
and Price_List_Name=c.Price_List_Name
and process_flag=c.process_flag;
commit;
END IF;
FND_FILE.PUT_LINE(FND_FILE.LOG,'ss6 ');
end if;
exception when others then
v_err_msg :=SQLERRM;
update geps_price_list_stg
set process_flag=3,
error_message=v_err_msg
where
Product_value =c.Product_value
and Price_List_Name=c.Price_List_Name
and process_flag=c.process_flag;

end;
end loop;
end;
/