/*==============================================================
  Declare Record Types
  ===============================================================*/
  TYPE recParcel is RECORD(
    parcelid number);
  
  TYPE recDemurrage is RECORD(
    demurrageid number);
    
  TYPE recLoadDischarge is RECORD(
    loaddischargeid number);
    
  /*=============================================================
  Declare Table Types
  ===============================================================*/
  
  TYPE tParcel IS TABLE OF recParcel
    INDEX BY BINARY_INTEGER;    
    
  TYPE tDemurrage IS TABLE OF recDemurrage
    INDEX BY BINARY_INTEGER;
    
  TYPE tLoadDischarge IS TABLE OF recLoadDischarge
    INDEX BY BINARY_INTEGER;

 PROCEDURE DeleteLifting(pLIFTINGID IN NUMBER, pUSERID IN NUMBER)
  AS

  --LOCAL VARIABLES
  nPARCELID NUMBER;
  nLOADDISCHARGEID NUMBER;
  nDEMURAGEID NUMBER;
  nPARCELCOUNT NUMBER;
  nDEMURAGECOUNT NUMBER;
  nLOADCOUNT NUMBER;
  
  tblParcel tParcel;
  tblDemurrage tDemurrage;
  tblLoadDischarge tLoadDischarge;
  
  nIndex number:=1;
  iIndex number:=1;
  
  --Declare cursors
  CURSOR cParcels IS
      SELECT distinct parcelid
      FROM ST_PARCEL
      WHERE liftingid = pliftingid;
     
  CURSOR cDemurrage IS
      SELECT DEMURAGEID
      FROM ST_DEMURAGE
      WHERE LOADDISCHARGEID IN (SELECT LOADDISCHARGEID
                              FROM ST_LOADDISCHARGE
                              WHERE LIFTINGID = pLiftingID);
                              
  CURSOR cLoadDischarge IS
      SELECT LOADDISCHARGEID
      FROM ST_LOADDISCHARGE
      WHERE LIFTINGID = pLIFTINGID;                           
   
  BEGIN

  	 -- CHECK HOW MANY RECORDS EXIST  
  	SELECT COUNT(*)
	INTO nPARCELCOUNT
	FROM ST_PARCEL
	WHERE LIFTINGID = pLIFTINGID;
	
	IF nPARCELCOUNT <> 0 THEN
	   FOR cvParcel in cParcels LOOP
      tblParcel(nIndex).parcelid:=cvParcel.parcelid;
      nIndex := nIndex +1;
    END LOOP;
    nIndex := 1;
	END IF;
	
	SELECT COUNT(*)
	INTO nDEMURAGECOUNT
	FROM ST_DEMURAGE
	WHERE LOADDISCHARGEID IN (SELECT LOADDISCHARGEID
                              FROM ST_LOADDISCHARGE
                              WHERE LIFTINGID = pLiftingID);
							  
	IF nDEMURAGECOUNT <> 0 THEN
	   FOR cvDemurrage in cDemurrage LOOP
      tbldemurrage(nIndex).demurrageid := cvDemurrage.demurageid;
      nIndex := nIndex +1;
    END LOOP;
    nIndex := 1;
	END IF;
							  
	SELECT COUNT(*)
	INTO nLOADCOUNT
	FROM ST_LOADDISCHARGE
	WHERE LIFTINGID = pLIFTINGID;
	
   IF nLOADCOUNT <> 0 THEN
   	FOR cvLoadDischarge in cLoadDischarge LOOP
      tblLoadDischarge(nIndex).LoadDischargeid := cvLoadDischarge.LoadDischargeid;
      nIndex := nIndex +1;
    END LOOP;
    nIndex :=1;
   END IF;
   
    --Delete all Parcel Records for the lifting
    DELETE FROM ST_PARCEL
    WHERE LIFTINGID = pLiftingID;

    --Delete all Demurrage Records
    DELETE FROM ST_DEMURAGE
    WHERE LOADDISCHARGEID IN (SELECT LOADDISCHARGEID
                              FROM ST_LOADDISCHARGE
                              WHERE LIFTINGID = pLiftingID);

    --Delete all the load discharge Records
    DELETE FROM ST_LOADDISCHARGE
    WHERE LIFTINGID = pLiftingID;


    --Delete all the MatchedDeal Records
    DELETE FROM ST_MATCHDEAL
    WHERE LiftingID = pLiftingID;


    --Delete the lifting record, and delete the lifting record if it matches the
    --the jointLIftingID
    DELETE FROM ST_LIFTING
    WHERE LIFTINGID = pLIFTINGID;
	
	/* ===================================================================================================
 Purpose: to update the st_audit table when a record is deleted
 ====================================================================================================*/ 
  UPDATE ST_AUDIT
	SET DELETEUSERID = pUserID,
		DELETEDATE = SYSDATE
		WHERE RECORDID = pLIFTINGID
		AND DELETEDATE IS NULL
		AND TABLENAME = 'ST_LIFTING';	
	
		
	IF nPARCELCOUNT <> 0 THEN
		FOR iIndex in tblParcel.FIRST..tblParcel.LAST LOOP
      nParcelID := tblParcel.parcelid;
   
    UPDATE ST_AUDIT
		SET DELETEUSERID = pUserID,
			DELETEDATE = SYSDATE
			WHERE RECORDID = nPARCELID
			AND DELETEDATE IS NULL
			AND TABLENAME = 'ST_PARCEL';	
    END LOOP;
	END IF;
	
	-- UPDATE THE DEMURAGE RECORD IN ST_AUDIT
	IF nDEMURAGECOUNT <> 0 THEN
  for iIndex in tblDemurrage.FIRST..tblDemurrage.LAST LOOP
    nDemurageID := tblDemurrage.demurageID;
    
		UPDATE ST_AUDIT
		SET DELETEUSERID = pUserID,
			DELETEDATE = SYSDATE
			WHERE RECORDID = nDEMURAGEID
			AND TABLENAME = 'ST_DEMURAGE';
    END LOOP;
	END IF; 
	
	--UPDATE THE LOADDISCHARGE RECORD IN ST_AUDIT
	IF nLOADCOUNT <> 0 THEN
    for iindex in tblLoaddischarge.FIRST.. tblLoadDischarge.LASt LOOP
      nLoaddischargeID := tblLoaddischarge.loaddischargeid;
      
    UPDATE ST_AUDIT
		SET DELETEUSERID = pUserID,
			DELETEDATE = SYSDATE
			WHERE RECORDID = nLOADDISCHARGEID
			AND TABLENAME = 'ST_LOADDISCHARGE';
      
    END LOOP;
	END IF;

    COMMIT;

  END DeleteLifting;

SELECT COUNT(*)
	INTO nPARCELCOUNT
	FROM ST_PARCEL
	WHERE LIFTINGID = pLIFTINGID;

	
	IF nPARCELCOUNT <> 0 THEN
	   FOR cvParcel in cParcels LOOP
      tblParcel(nIndex).parcelid:=cvParcel.parcelid;
      nIndex := nIndex +1;
    END LOOP;