Script for deleting Duplicate sample for OPM module

CREATE OR REPLACE PROCEDURE APPS.xx_sample_del_proc(p_lot in varchar,p_sampling_event_id in number)
AS
  l_sample_no gmd_samples.sample_no%type;
  l_lot_number gmd_samples.lot_number%type;
 -- l_source     gmd_samples.source%type;
  l_inv_item_id NUMBER;
  l_samp_event_id gmd_Samples.sampling_event_id%type;
  l_user_id   gmd_Samples.last_updated_by%type;
  errbuf  VARCHAR2(2000);
  retcode NUMBER;
  l_request_id NUMBER;
  l_appl_id    NUMBER;
  l_resp_id    NUMBER;
  l_boolean    BOOLEAN;
  l_count      NUMBER;
 -- l_count1      NUMBER;

 /**********************************************************************************************/
 /* Created by DJHA  DOYENSYS
 /*This Procedure will delete/disable the duplicate sample created via procurement and Inventory........
 >disable the INV sample If Inventory source comes along with supplier for procurement
 >Disable the INV sample if Inventory source duplicates with Inventory itelse for same LOT*/

 BEGIN


/* Formatted on 2019/08/19 11:24 (Formatter Plus v4.8.8) */

FOR I IN   (select count(1) cnt
 from gmd_Samples
 where lot_number=p_lot
 group by lot_number,batch_id)

LOOP

/*********************Checkking count of LOT NO***************************/
 
  IF  I.CNT >1
 
  THEN
   xxttk_error_msg('In xx_sample_del_proc.Count:'||i.cnt);
  /********************Obtaining source forthat current LOT***************************/
 
 FOR I1 IN (SELECT LISTAGG (SOURCE,',') WITHIN GROUP (ORDER BY SOURCE)AS SOURCES
 FROM gmd_Samples
 where lot_number=p_lot)

 LOOP

 IF I1.SOURCES IN ('I,S') OR I1.SOURCES IN ('I,I')

 THEN

 BEGIN

 BEGIN

 /*********************Obtaining details for INV source which is going to be deleted ************************************/
  xxttk_error_msg('In Loop:'||i1.sources);
  SELECT sample_no, lot_number, inventory_item_id,sampling_event_id,last_updated_by
    INTO l_sample_no, l_lot_number, l_inv_item_id,l_samp_event_id,l_user_id
    FROM gmd_samples gs
    WHERE LOT_NUMBER=p_lot
    and sampling_event_id= p_sampling_event_id
    AND SOURCE='I';
   
    EXCEPTION
   
    WHEN OTHERS THEN
   
    FND_FILE.PUT_LINE(FND_FILE.LOG,'Error While Deriving sample details..');
   
    end;

    UPDATE gmd_samples
    SET attribute1 = 'DUPLICATE CANCEL',
    attribute_category = 'PDD QA Acceptance Info'
    WHERE sampling_event_id = l_samp_event_id;

/**************************Calling procedure to disable INV Source Sample****************************************/
  xxttk_error_msg('Call xxttk_del_samples_initialize. Sample:'||l_sample_no||' Lot:'||l_lot_number);
    xxttk_del_samples_initialize(
    l_sample_no ,
    l_lot_number ,
  l_user_id);
 
  COMMIT;
   
    EXCEPTION

 WHEN OTHERS THEN

   FND_FILE.PUT_LINE(FND_FILE.LOG,SQLERRM||' '||SQLCODE);

END;

END IF;

END LOOP;


END IF;

END LOOP;

 EXCEPTION
  WHEN OTHERS THEN
    NULL;

END;
/