984 lines
23 KiB
Plaintext
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;
|
|
|