NTP_V2/docs/refinery-db-objects.txt

984 lines
23 KiB
Plaintext

=== 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;