=== VIEWS (user_views text, NAFITHTCS) ===

--- REFINERY_CARDS_VIEW
select "ID","TN","CARD_NUMBER","BOX_NO","CURRENT_BALANCE","CREATION_DATE","CREATED_BY","UPDATE_DATE","UPDATED_BY","STATUS_CODE","LAST_TRIP_ID"
    from REFINERY_CARDS


--- REFINERY_CARDS_LOG_VIEW
select "ID","ENTITIY_ID","METHOD_NAME","BALANCE_BEFORE","BALANCE_AFTER","CREATED_BY",to_char( "CREATION_DATE",'dd/mm/yyyy HH24:MI') CREATION_DATE,"UPDATED_BY",to_char(LAST_UPDATE_DATE,'dd/mm/yyyy HH24:MI') LAST_UPDATE_DATE, "AMOUNT","PN"
    from REFINERY_CARDS_LOG


--- REFINERY_COMMISSION_CLAIM_VIEW
select "ID","COMMISSION_CLAIM_NO",to_char("COMMISSION_CLAIM_DATE",'dd/mm/yyyy') COMMISSION_CLAIM_DATE,
to_char(trunc(COMMISSION_CLAIM_DATE) , 'yyyymmdd') as COMMISSION_CLAIM_DATE_STRING,
"STATUS_CODE" ,
(select count(id) from refinery_wage_claim tr where tr.COMMISSION_claim_id = REFINERY_COMMISSION_CLAIM.id) trip_cnt
from REFINERY_COMMISSION_CLAIM


--- REFINERY_COMMISSION_LOG_VIEW
select n.login,t."ID",t."ENTITIY_ID",t."METHOD_NAME",to_char(t."LOG_DATE",'dd/mm/yyyy HH24:MI') LOG_DATE,t."USER_ID",t."USER_IP",t."OLD_DATA",t."NEW_DATA" from refinery_commission_claim_log t
join ntp.ntpusers n ON
t.user_id = n.id(+)
order by t.id desc


--- REFINERY_TRIPS_VIEW
select REFINERY_TRIPS."ID",
"SECOND_ADVANCE_PAYMENT",
"FIRST_ADVANCE_PAYMENT",
"PN",REFINERY_TRIPS."STATUS_CODE",
to_char("DISPATCH_DATE",'dd/mm/yyyy') DISPATCH_DATE,
to_char("ACTUAL_LOADING_DATE",'dd/mm/yyyy') ACTUAL_LOADING_DATE,
DateToString("ACTUAL_LOADING_DATE") ACTUAL_LOADING_DATE_STRING,
DATETOSTRING(RECEIVED_DATE) RECEIVED_DATE_STRING,
ACTUAL_LOADING_DATE LOADING_DATE,
"ACTUAL_LOADING_TIME",
Datetostring("DISCHARGE_ENTRY_DATE") DISCHARGE_ENTRY_DATE_STRING,
"LOADING_CENTER","CLIENT_NO",comp.display_name CLIENT_NAME,
REFINERY_TRIPS."TN","TRN",REFINERY_TRIPS."NNPN","MATERIAL_NO",
"MENAGATE_NO","EMPTY_WEIGHT",
"GROSS_WEIGHT","NET_LOADING_WEIGHT",
"STANDARD_UNITS_WEIGHT","LITERS_WEIGHT",
"DENSITY","TEMPERATURE","FLAG",
"DISCHARGE_AUTHORIZTION_NO",
"DISCHARGE_AUTHORIZTION_YEAR",
"DISCHARGE_ENTRY_TIME",
"DISCHARGE_EXIT_DATE",
"DISCHARGE_EXIT_TIME",
"ACTUAL_GROSS_WEIGHT",
"ACTUAL_EMPTY_WEIGHT",
"NET_DISCHARGE_WEIGHT",
"NET_WAGE" ,REFINERY_TRIPS.DIESEL_CHARGE,
wa.epayment,

decode(paid_wallet.wallet_number,null,wa.wallet_number,paid_wallet.wallet_number) wallet_number,
decode(paid_wallet.company_code,null,wa.company_code,paid_wallet.company_code) company_code,
decode(paid_wallet.owner_nnpn,null,wa.owner_nnpn,paid_wallet.owner_nnpn) owner_nnpn,
decode(paid_wallet.owner_name,null,wa.owner_name,paid_wallet.owner_name) owner_name,
decode(paid_wallet.owner_name_english,null,wa.owner_name_english,paid_wallet.owner_name_english) owner_name_english,
decode(paid_wallet.iban,null,wa.iban,paid_wallet.iban) iban,
decode(paid_wallet.bank_account,null,wa.bank_account,paid_wallet.bank_account) bank_account,
decode(paid_wallet.bank_name,null,wa.bank_name,paid_wallet.bank_name) bank_name,

decode(wa.id,null,'0','1') as HAVE_PAYMENT_METHOD,
drvr_wallet.wallet_number driver_wallet_number,
 decode(REFINERY_TRIPS.WAGE_CLAIM_ID ,null,'0','1') as HAS_WAGE_CLAIM,
RECEIVED_BY,RECEIVED_DATE,PAYMENTED_BY,PAYMENTED_DATE,
REFINERY_TRIPS.Payment_Options,
REFINERY_TRIPS.VOUCHER_SERIAL_NUMBER,

--Datetostring("PAYMENTED_DATE") ,

--decode(REFINERY_TRIPS.Status_Code,'EXCHANGED',Datetostring(REFINERY_TRIPS.PAYMENTED_DATE),'READY_TO_PAY',Datetostring(REFINERY_TRIPS.Received_Date),
--'DISCHARGED',Datetostring(REFINERY_TRIPS.ACTUAL_LOADING_DATE)) PAYMENTED_DATE_STRING,

Datetostring(REFINERY_TRIPS.ACTUAL_LOADING_DATE) PAYMENTED_DATE_STRING,

DISCHARGE_ENTRY_DATE,to_char("DISCHARGE_ENTRY_DATE",'dd/mm/yyyy') DISCHARGE_ENTRY_DATE_DISPLAY,
to_char("PAYMENTED_DATE",'dd/mm/yyyy') PAYMENTED_DATE_DISPLAY,
"WATER_AMOUNT","SEDIMENT_AMOUNT",REFINERY_TRIPS."TRIP_STATUS",REFINERY_TRIPS."USER_ID",REFINERY_TRIPS."LAST_UPDATE_DATE","DAY_TIME","PROGRAM_ID","WORK_STN_ID","EXCHANGE_CLAIM_NO","PAID_AMOUNT_WATER",
WAGE_CLAIM_ID,
rwc.wage_claim_no,
info.name_ar DRVR,
REVERSE_PAYMENT,
S.FIRSTNAME RECEIVED_BY_NAME,
LT.AR STATUS_CODE_AR,remarks,
REFINERY_TRIPS.Loading_No,
(NET_LOADING_WEIGHT - net_discharge_weight)  as "WEIGHT_DIIERANCE",
CARD.CARD_NUMBER,CARD.CURRENT_BALANCE,
decode(wa.epayment,null,unistr('\0644\0627\0020\064A\0648\062C\062F'),'0',unistr('\062D\0648\0627\0644\0629'),'1',unistr('\0627\0644\0643\062A\0631\0648\0646\064A')) as  PAYMENT_METHOD,
--decode(wa.epayment,null,0,'0',1,'1',2) as  PAYMENT_METHOD_CODE,
decode(REFINERY_TRIPS.PAYMENT_OPTIONS,NULL,decode(wa.epayment,null,0,'0',1,'1',2),'CACH_PAYMENT',3) as  PAYMENT_METHOD_CODE,
Datetostring("PAYMENTED_DATE") PAYMENTED_DATE_STRING_SEARCH,

REFINERY_TRIPS.DINARAK_REFERENCE_ID , REFINERY_WALLET_ID ,
(CASE WHEN WATER_AMOUNT > 0 THEN 1
   ELSE 0 END) as WATER_EXIST,

decode(paid_wallet.epayment,null,'','0',unistr('\062D\0648\0627\0644\0629'),'1',unistr('\0627\0644\0643\062A\0631\0648\0646\064A')) as  paid_epayment
,"TRIP_NET_WAGE" , "REMAINING_BALANCE" ,penalty_amount , penalty_quantity

from REFINERY_TRIPS,ntp.lt_caption lt,refinery_wage_claim rwc,drivers_info info,refinery_company comp,REFINERY_WALLET wa,NTP.NTPUSERS S,REFINERY_WALLET drvr_wallet,
REFINERY_CARDS CARD,REFINERY_WALLET paid_wallet
where  LT.CODE(+) = 'REFINERYTRIP.STATUS.'|| REFINERY_TRIPS.STATUS_CODE
and rwc.id(+)=REFINERY_TRIPS.Wage_Claim_Id
and wa.tn(+)=REFINERY_TRIPS.tn
and info.nnpn(+)=REFINERY_TRIPS.Nnpn
and info.status_code(+) = 'COMPLETED'
and comp.code(+) = CLIENT_NO
AND s.id(+)=REFINERY_TRIPS.Received_By
and to_char(drvr_wallet.driver_nnpn(+))=REFINERY_TRIPS.Nnpn
and CARD.TN(+) = REFINERY_TRIPS.tn
AND card.Status_Code(+) = 'ACTIVE'
and wa.status_code(+) = 'ACTIVE'
and drvr_wallet.Status_Code(+)='ACTIVE'
and paid_wallet.id(+)=REFINERY_TRIPS.Refinery_Wallet_Id


--- REFINERY_TRIPS_LOG_VIEW
select n.login,t."ID",t."ENTITIY_ID",t."METHOD_NAME",to_char(t."LOG_DATE",'dd/mm/yyyy HH24:MI') LOG_DATE,t."USER_ID",t."USER_IP",t."OLD_DATA",t."NEW_DATA" from refinery_trips_LOG t
join ntp.ntpusers n ON
t.user_id = n.id(+)
order by t.id desc


--- REFINERY_VARIABLES_VIEW
select rownum ID,GROUP_ID ,to_char("START_DATE",'yyyy-mm-dd') "START_DATE" , Datetostring("START_DATE") START_DATE_STRING, to_char("END_DATE",'yyyy-mm-dd') "END_DATE", Datetostring("END_DATE") END_DATE_STRING,"DIESEL_LITER_PRICE","TON_WAGE","TON_PRICE","DRIVER_TIP","PERMIT_DISPATCH","WAYBILL_FEE","COMMISSION","TOLERANCE_PERCENTAGE","FIRST_ADVANCE_PAYMENT" /*, TON_PRICE_CORRECTION */ from (
  select   start_date,end_date,key_name,key_value ,GROUP_ID from REFINERY_VARIABLES where KEY_NAME != 'AUTDIT_MODE'
)
pivot
(
   sum(key_value)
   for key_name in ('DIESEL_LITER_PRICE' DIESEL_LITER_PRICE,'TON_WAGE' TON_WAGE,'TON_PRICE' TON_PRICE,'DRIVER_TIP' DRIVER_TIP,'PERMIT_DISPATCH' PERMIT_DISPATCH,'WAYBILL_FEE' WAYBILL_FEE,'COMMISSION' COMMISSION,'TOLERANCE_PERCENTAGE' TOLERANCE_PERCENTAGE, 'FIRST_ADVANCE_PAYMENT' FIRST_ADVANCE_PAYMENT /* , 'TON_PRICE_CORRECTION'  TON_PRICE_CORRECTION */)
)
order by 1


--- REFINERY_WAGE_CLAIM_VIEW
select REFINERY_WAGE_CLAIM."ID","WAGE_CLAIM_NO",to_char("WAGE_CLAIM_DATE",'dd/mm/yyyy') WAGE_CLAIM_DATE,REFINERY_WAGE_CLAIM."STATUS_CODE" ,COMMISSION_CLAIM_ID,
 decode(REFINERY_WAGE_CLAIM.COMMISSION_CLAIM_ID ,null,'0','1') as HAS_COMMISSION_CLAIM,
(select count(id) from refinery_trips tr where tr.wage_claim_id = REFINERY_WAGE_CLAIM.id) trip_cnt,comm.commission_claim_no
from REFINERY_WAGE_CLAIM,refinery_commission_claim comm
where comm.id(+)=REFINERY_WAGE_CLAIM.COMMISSION_CLAIM_ID


--- REFINERY_WAGE_CLAIM_LOG_VIEW
select n.login,t."ID",t."ENTITIY_ID",t."METHOD_NAME",to_char(t."LOG_DATE",'dd/mm/yyyy HH24:MI') LOG_DATE,t."USER_ID",t."USER_IP",t."OLD_DATA",t."NEW_DATA" from refinery_wage_claim_log t
join ntp.ntpusers n ON
t.user_id = n.id(+)
order by t.id desc


--- REFINERY_WALLET_VIEW
select "ID"
,"TN"
,"COMPANY_CODE"
,"EPAYMENT"
,decode("OWNER_NNPN",null,DRIVER_NNPN,OWNER_NNPN) OWNER_NNPN
,decode("OWNER_NNPN",null,DRIVER_NNPN,OWNER_NNPN) NNPN
,"WALLET_NUMBER"
,decode("OWNER_NAME",null,DRIVER_NAME,OWNER_NAME) OWNER_NAME
,"CREATION_DATE"
,"DRIVER_NAME"
,"DRIVER_NNPN"
,"IBAN"
,"BANK_ACCOUNT"
,"BANK_NAME"
,"CREATED_BY"
,"UPDATE_DATE"
,"UPDATED_BY"
,"STATUS_CODE"
,"BANK_BRANCH","OWNER_NAME_ENGLISH",
----,DECODE("EPAYMENT",0,unistr('\062D\0648\0627\0644\0629'),unistr('\0645\062D\0641\0638\0629\0020\0627\0644\0643\062A\0631\0648\0646\064A\0629')) PAYMENT_METHOD,
( CASE
    WHEN DRIVER_NNPN is not null THEN 'DRAIVER_WALLET'
    WHEN EPAYMENT = 0 THEN 'BANK'

    ELSE 'EWALLET'
END ) PAYMENT_METHOD
from REFINERY_WALLET


--- REFINERY_WALLET_LOG_VIEW
select n.login,t."ID",t."ENTITIY_ID",t."METHOD_NAME",to_char(t."LOG_DATE",'dd/mm/yyyy HH24:MI') LOG_DATE,t."USER_ID",t."USER_IP",t."OLD_DATA",t."NEW_DATA" from refinery_WALLET_LOG t
join ntp.ntpusers n ON
t.user_id = n.id(+)
order by t.id desc


--- REFINERY_DIESEL_REPORT_VIEW
select T.ID, t.pn,t.tn,T.STATUS_CODE,
t.discharge_authoriztion_no,c.creation_date,
s.firstname,
t.DIESEL_CHARGE,

decode (c.method_name,'FIRST_ADVANCED_PAYMENT',unistr('\0634\062D\0646\0020\0644\0644\062A\062D\0645\064A\0644'),'SECOND_ADVANCED_PAYMENT',unistr('\0634\062D\0646\0020\0644\0644\062A\0641\0631\064A\063A'),'SECOND_ADVANCED_PAYMENT_REVERSE',unistr('\0627\0644\063A\0627\0621\0020\0634\062D\0646\0020\0627\0644\062A\0641\0631\064A\063A'),'FIRST_ADVANCED_PAYMENT_REVERSE',unistr('\0627\0644\063A\0627\0621\0020\0634\062D\0646\0020\0627\0644\062A\062D\0645\064A\0644')) method_name,

c.balance_before,c.balance_after,C.AMOUNT from refinery_trips_view t,refinery_cards_log c,NTP.NTPUSERS s

where c.PN=t.PN
and s.id(+)=to_number(c.created_by)

ORDER BY  CREATION_DATE


--- REFINERY_CLAIMS_REPORT_VIEW
select t."ID",t.WAGE_CLAIM_ID,  t.STATUS_CODE,TRIP_STATUS, "DISCHARGE_AUTHORIZTION_NO",discharge_authoriztion_year, to_char(Discharge_Entry_Date , 'dd/mm/yyyy' ) Discharge_Entry_Date, client_no, '' client_NAME,'' serial,loading_no , to_char(actual_loading_date , 'dd/mm/yyyy') actual_loading_date ,  NET_LOADING_WEIGHT,WATER_AMOUNT , NET_DISCHARGE_WEIGHT
,(NET_LOADING_WEIGHT - net_discharge_weight)  as "WEIGHT_DIIERANCE",
TON_WAGE ,CALCULATED_QUANTITY_TON  ,QUANTITY_WAGE ,w.wage_claim_no, TOLERANCE_QUANTITY ,
(CASE
    WHEN Water_amount > 0 THEN
          'YES'
      ELSE 'NO'
      END)  WATER_EXIST,


      CASE
    WHEN (NET_LOADING_WEIGHT - net_discharge_weight) < TOLERANCE_QUANTITY THEN
          ltrim(to_char(0, '0.000'))
      ELSE ltrim(to_char(PENALTY_QUANTITY, '9999999.999'))
      END  PENALTY_QUANTITY,

      --ltrim(to_char(PENALTY_QUANTITY, '9999999.999')) PENALTY_QUANTITY  ,
      ltrim(to_char(TON_PRICE, '9999999.999')) TON_PRICE  ,
      PENALTY_AMOUNT,
      ltrim(to_char(DRIVER_TIP, '9999999.999')) DRIVER_TIP  ,
      ltrim(to_char(PERMIT_DISPATCH, '9999999.999')) PERMIT_DISPATCH  ,
      WAYBILL_FEE,DIESEL_LITER_PRICE,
      ltrim(to_char((select sum(amount) from REFINERY_CARDS_LOG where pn = t.pn),'9999999.99')) advance_payment_FOR_DIESEL,
         ltrim(to_char(COMMISSION, '9999999.999')) COMMISSION  ,
         ltrim(to_char(NET_WAGE, '9999999.999')) NET_WAGE,T.TN,info.name_ar DRVR

    from REFINERY_TRIPS t,refinery_wage_claim w,DRIVERS_INFO info

    where w.id=t.wage_claim_id
     and info.nnpn(+)=t.Nnpn
and info.status_code(+) = 'COMPLETED'


--- REFINERY_CLAIMS_REPORT
select "ID",STATUS_CODE,TRIP_STATUS, "DISCHARGE_AUTHORIZTION_NO",discharge_authoriztion_year, Discharge_Entry_Date, client_no, '' client_NAME,'' serial,loading_no , actual_loading_date,  NET_LOADING_WEIGHT,Water_amount , net_discharge_weight ,(NET_LOADING_WEIGHT - net_discharge_weight)  as "WEIGHT_DIIERANCE",
TON_WAGE ,CALCULATED_QUANTITY_TON  ,QUANTITY_WAGE , TOLERANCE_QUANTITY ,
(CASE
    WHEN Water_amount > 0 THEN
          'YES'
      ELSE 'NO'
      END)  WATER_EXIST,
      ltrim(to_char(PENALTY_QUANTITY, '9999999.99')) PENALTY_QUANTITY  ,
      ltrim(to_char(TON_PRICE, '9999999.99')) TON_PRICE  ,
      PENALTY_AMOUNT,
      ltrim(to_char(DRIVER_TIP, '9999999.99')) DRIVER_TIP  ,
      ltrim(to_char(PERMIT_DISPATCH, '9999999.99')) PERMIT_DISPATCH  ,
      WAYBILL_FEE,DIESEL_LITER_PRICE,
      ltrim(to_char((select sum(amount) from REFINERY_CARDS_LOG where pn = REFINERY_TRIPS.pn),'9999999.99')) advance_payment_FOR_DIESEL,
      COMMISSION ,NET_WAGE
    from REFINERY_TRIPS


=== FUNCTIONS ===

--- CALCULATEREFINARYDIESEL
FUNCTION "CALCULATEREFINARYDIESEL" (RefineryId in numeric)

  return float as



  DieslCount      number;

  LoadingDate   date;

  CURRENT_BALANCE float;

  DIESEL_LITER_PRICE float;

  FIRST_ADVANCE_PAYMENT float;

begin



  select LOADING_DATE,CURRENT_BALANCE

    into LoadingDate,CURRENT_BALANCE

    from refinery_trips_view where id = RefineryId;



  SELECT ( select DIESEL_CHARGE from REFINERY_TRIPS where ID = RefineryId)

  into DieslCount FROM DUAL;



 select key_value into FIRST_ADVANCE_PAYMENT

        from refinery_variables

        where KEY_NAME = 'FIRST_ADVANCE_PAYMENT'

        and sysdate between start_date and end_date +1 ;



  if DieslCount = 0 then   -- No First Payment

   return FIRST_ADVANCE_PAYMENT+CURRENT_BALANCE;

  else if DieslCount = 1 then  -- No Second Payment



        -- Get Liter current price

        select key_value into DIESEL_LITER_PRICE

        from refinery_variables

        where KEY_NAME = 'DIESEL_LITER_PRICE'

        and LoadingDate between start_date and end_date;



        return (DIESEL_LITER_PRICE * 350)+CURRENT_BALANCE-FIRST_ADVANCE_PAYMENT;

   else

       return CURRENT_BALANCE;

   end if;

   end if;



end CalculateRefinaryDiesel;


--- CALCULATEREFINARYAMOUNT
FUNCTION             "CALCULATEREFINARYAMOUNT" (RefineryId in numeric)

  return float as

  FIRST_ADVANCE_PAYMENT float;

  DIESEL_LITER_PRICE    float;

  TON_WAGE              float;

  TON_PRICE             float;

  DRIVER_TIP            float;

  PERMIT_DISPATCH       float;

  WAYBILL_FEE           float;

  COMMISSION            float;

  TOLERANCE_PERCENTAGE  float;

  NetLoadingWeight      int;

  NetDischargeWeight    int;

  CalculatedAmount      float;

  WaterAmount           int;

  FineQuantity          float;

  AllowQuantity         float;

  LoadingDate         Date;

 truckNumber                  int;

  REMAINING_BALANCE   float;

begin

  select tn,net_loading_weight,

         net_discharge_weight,

         water_amount,

         actual_loading_date

    into  truckNumber,NetLoadingWeight, NetDischargeWeight, WaterAmount, LoadingDate

    from refinery_trips

   where id = RefineryId;

  select key_value

    into FIRST_ADVANCE_PAYMENT

    from refinery_variables

   where KEY_NAME = 'FIRST_ADVANCE_PAYMENT'

     and LoadingDate between start_date and end_date;

  select key_value

    into DIESEL_LITER_PRICE

    from refinery_variables

   where KEY_NAME = 'DIESEL_LITER_PRICE'

     and LoadingDate between start_date and end_date;

  select key_value

    into TON_WAGE

    from refinery_variables

   where KEY_NAME = 'TON_WAGE'

     and LoadingDate between start_date and end_date;

  select key_value

    into TON_PRICE

    from refinery_variables

   where KEY_NAME = 'TON_PRICE'

     and LoadingDate between start_date and end_date;

  select key_value

    into DRIVER_TIP

    from refinery_variables

   where KEY_NAME = 'DRIVER_TIP'

     and LoadingDate between start_date and end_date;

  select key_value

    into PERMIT_DISPATCH

    from refinery_variables

   where KEY_NAME = 'PERMIT_DISPATCH'

     and LoadingDate between start_date and end_date;

  select key_value

    into WAYBILL_FEE

    from refinery_variables

   where KEY_NAME = 'WAYBILL_FEE'

     and LoadingDate between start_date and end_date;

  select key_value

    into COMMISSION

    from refinery_variables

   where KEY_NAME = 'COMMISSION'

     and LoadingDate between start_date and end_date;

  select key_value

    into TOLERANCE_PERCENTAGE

    from refinery_variables

   where KEY_NAME = 'TOLERANCE_PERCENTAGE'

     and LoadingDate between start_date and end_date;



  AllowQuantity := FLOOR(NetLoadingWeight * TOLERANCE_PERCENTAGE);



  FineQuantity := (NetLoadingWeight - NetDischargeWeight - WaterAmount);



  IF FineQuantity <= AllowQuantity THEN

    FineQuantity := 0;

  else

    FineQuantity := FineQuantity - AllowQuantity;

  end if;



  FineQuantity := (FineQuantity / 1000) * TON_PRICE;



  IF WaterAmount >= (NetLoadingWeight - NetDischargeWeight) THEN

    CalculatedAmount := (NetLoadingWeight) / 1000;

  ELSE

    CalculatedAmount := (NetDischargeWeight + WaterAmount) / 1000;

  end if;



  CalculatedAmount := (CalculatedAmount * TON_WAGE) - FineQuantity -

                      DRIVER_TIP - PERMIT_DISPATCH - WAYBILL_FEE -

                      (DIESEL_LITER_PRICE * 350) - COMMISSION;



  --- calcualte amount after remaining balance

  select remaining_balance

    into REMAINING_BALANCE

    from REFINERY_REMAINING_BALANCE

   where tn = truckNumber ;

   CalculatedAmount := CalculatedAmount +REMAINING_BALANCE;





  return ROUND(CalculatedAmount, 3);



end CalculateRefinaryAmount;


--- CALCULATEREFINARYAMOUNT6
FUNCTION             "CALCULATEREFINARYAMOUNT6" (RefineryId in numeric)

  return float as

  FIRST_ADVANCE_PAYMENT float;

  DIESEL_LITER_PRICE    float;

  TON_WAGE              float;

  TON_PRICE             float;

  DRIVER_TIP            float;

  PERMIT_DISPATCH       float;

  WAYBILL_FEE           float;

  COMMISSION            float;

  TOLERANCE_PERCENTAGE  float;

  NetLoadingWeight      int;

  NetDischargeWeight    int;

  CalculatedAmount      float;

  WaterAmount           int;

  FineQuantity          float;

  AllowQuantity         float;

  LoadingDate         Date;

 truckNumber                  int;

  REMAINING_BALANCE   float;

begin

  select tn,net_loading_weight,

         net_discharge_weight,

         water_amount,

         actual_loading_date

    into  truckNumber,NetLoadingWeight, NetDischargeWeight, WaterAmount, LoadingDate

    from refinery_trips

   where id = RefineryId;

  select key_value

    into FIRST_ADVANCE_PAYMENT

    from refinery_variables

   where KEY_NAME = 'FIRST_ADVANCE_PAYMENT'

     and LoadingDate between start_date and end_date;

  select key_value

    into DIESEL_LITER_PRICE

    from refinery_variables

   where KEY_NAME = 'DIESEL_LITER_PRICE'

     and LoadingDate between start_date and end_date;

  select key_value

    into TON_WAGE

    from refinery_variables

   where KEY_NAME = 'TON_WAGE'

     and LoadingDate between start_date and end_date;

  select key_value

    into TON_PRICE

    from refinery_variables

   where KEY_NAME = 'TON_PRICE'

     and LoadingDate between start_date and end_date;

  select key_value

    into DRIVER_TIP

    from refinery_variables

   where KEY_NAME = 'DRIVER_TIP'

     and LoadingDate between start_date and end_date;

  select key_value

    into PERMIT_DISPATCH

    from refinery_variables

   where KEY_NAME = 'PERMIT_DISPATCH'

     and LoadingDate between start_date and end_date;

  select key_value

    into WAYBILL_FEE

    from refinery_variables

   where KEY_NAME = 'WAYBILL_FEE'

     and LoadingDate between start_date and end_date;

  select key_value

    into COMMISSION

    from refinery_variables

   where KEY_NAME = 'COMMISSION'

     and LoadingDate between start_date and end_date;

  select key_value

    into TOLERANCE_PERCENTAGE

    from refinery_variables

   where KEY_NAME = 'TOLERANCE_PERCENTAGE'

     and LoadingDate between start_date and end_date;



  AllowQuantity := FLOOR(NetLoadingWeight * TOLERANCE_PERCENTAGE);



  FineQuantity := (NetLoadingWeight - NetDischargeWeight - WaterAmount);



  IF FineQuantity <= AllowQuantity THEN

    FineQuantity := 0;

  else

    FineQuantity := FineQuantity - AllowQuantity;

  end if;



  FineQuantity := (FineQuantity / 1000) * TON_PRICE;



  IF WaterAmount >= (NetLoadingWeight - NetDischargeWeight) THEN

    CalculatedAmount := (NetLoadingWeight) / 1000;

  ELSE

    CalculatedAmount := (NetDischargeWeight + WaterAmount) / 1000;

  end if;



  CalculatedAmount := (CalculatedAmount * TON_WAGE) - FineQuantity -

                      DRIVER_TIP - PERMIT_DISPATCH - WAYBILL_FEE -

                      (DIESEL_LITER_PRICE * 350) - COMMISSION;



  --- calcualte amount after remaining balance

  select remaining_balance

    into REMAINING_BALANCE

    from REFINERY_REMAINING_BALANCE

   where tn = truckNumber ;

   CalculatedAmount := CalculatedAmount +REMAINING_BALANCE;





  return ROUND(CalculatedAmount, 3);



end CalculateRefinaryAmount6;


--- CALCULATEREFINARYAMOUNTREPORT
FUNCTION             "CALCULATEREFINARYAMOUNTREPORT" (RefineryId in numeric,calType in numeric)

  return float as

  FIRST_ADVANCE_PAYMENT float;

  DIESEL_LITER_PRICE    float;

  TON_WAGE              float;

  TON_PRICE             float;

  DRIVER_TIP            float;

  PERMIT_DISPATCH       float;

  WAYBILL_FEE           float;

  COMMISSION            float;

  TOLERANCE_PERCENTAGE  float;

  NetLoadingWeight      int;

  NetDischargeWeight    int;

  CalculatedAmount      float;

  WaterAmount           int;

  FineQuantity          float;

  AllowQuantity         float;

  LoadingDate         Date;

begin



    select net_loading_weight,

           net_discharge_weight,

           water_amount,

           actual_loading_date

      into NetLoadingWeight, NetDischargeWeight, WaterAmount, LoadingDate

      from refinery_trips

     where id = RefineryId;

    select key_value

      into FIRST_ADVANCE_PAYMENT

      from refinery_variables

     where KEY_NAME = 'FIRST_ADVANCE_PAYMENT'

       and LoadingDate between start_date and end_date;

    select key_value

      into DIESEL_LITER_PRICE

      from refinery_variables

     where KEY_NAME = 'DIESEL_LITER_PRICE'

       and LoadingDate between start_date and end_date;

    select key_value

      into TON_WAGE

      from refinery_variables

     where KEY_NAME = 'TON_WAGE'

       and LoadingDate between start_date and end_date;

    select key_value

      into TON_PRICE

      from refinery_variables

     where KEY_NAME = 'TON_PRICE'

       and LoadingDate between start_date and end_date;

    select key_value

      into DRIVER_TIP

      from refinery_variables

     where KEY_NAME = 'DRIVER_TIP'

       and LoadingDate between start_date and end_date;

    select key_value

      into PERMIT_DISPATCH

      from refinery_variables

     where KEY_NAME = 'PERMIT_DISPATCH'

       and LoadingDate between start_date and end_date;

    select key_value

      into WAYBILL_FEE

      from refinery_variables

     where KEY_NAME = 'WAYBILL_FEE'

       and LoadingDate between start_date and end_date;

    select key_value

      into COMMISSION

      from refinery_variables

     where KEY_NAME = 'COMMISSION'

       and LoadingDate between start_date and end_date;

    select key_value

      into TOLERANCE_PERCENTAGE

      from refinery_variables

     where KEY_NAME = 'TOLERANCE_PERCENTAGE'

       and LoadingDate between start_date and end_date;



    AllowQuantity := FLOOR(NetLoadingWeight * TOLERANCE_PERCENTAGE);



    FineQuantity := (NetLoadingWeight - NetDischargeWeight - WaterAmount);



    IF FineQuantity <= AllowQuantity THEN

      FineQuantity := 0;

    else

      FineQuantity := FineQuantity - AllowQuantity;

    end if;



    FineQuantity := (FineQuantity / 1000) * TON_PRICE;



    IF WaterAmount >= (NetLoadingWeight - NetDischargeWeight) THEN

      CalculatedAmount := (NetLoadingWeight) / 1000;

    ELSE

      CalculatedAmount := (NetDischargeWeight + WaterAmount) / 1000;

    end if;



    if calType = 0 then

      CalculatedAmount := (CalculatedAmount * TON_WAGE) - FineQuantity -

                          DRIVER_TIP - PERMIT_DISPATCH - WAYBILL_FEE -

                          (DIESEL_LITER_PRICE * 350) - COMMISSION;



      return ROUND(CalculatedAmount, 4);



  else



    return ROUND(CalculatedAmount*TON_WAGE, 4);

  end if;



end CalculateRefinaryAmountReport;

