create or replace PACKAGE FS_RailQuotation /*FS-260520-01*/

AS

/*

-----------------------------------------------------------------------------------------------------------------------------------

Modification history

----------------------------------------------------------------------------------------------------------------------------------

|| Version When Who What

|| -------------------------------------------------------------------------------------------------------------------------------

|| FS-26052020-01 26-MAY-2020 Muthuraj B FS_RailQuotation - This package contains Rail quotation details.

|| FS-19082020-01 19-AUG-2020 Harish Kumar.D Added getLov_customerdetails_prc.

-----------------------------------------------------------------------------------------------------------------------------------

*/



TYPE t_list IS REF cursor;



PROCEDURE getLov_reference_prc (

p_fsofo_id IN FSRQH_RailQuotationHeader.fsofo_id%TYPE,

p_searchtext IN FSRQH_RailQuotationHeader.fsrqh_shipmenttype%TYPE,

p_page_index IN fsdcl_documentationchangelog.fsdcl_recordid%TYPE,

p_page_size IN fsdcl_documentationchangelog.fsdcl_recordid%TYPE,

p_sort_field IN fsdcl_documentationchangelog.fsdcl_tablename%TYPE,

p_sort_type IN fsdcl_documentationchangelog.fsdcl_tablename%TYPE,

p_reference_count OUT FSRQH_RailQuotationHeader.fsofo_id%TYPE,

p_reference OUT FS_RailQuotation.t_list,

p_issuccess OUT FSRQH_RailQuotationHeader.fsrqh_shipmenttype%TYPE,

p_message OUT FS_RailQuotation.t_list);



PROCEDURE getLov_copyreference_prc (

p_fsofo_id IN FSRQH_RailQuotationHeader.fsofo_id%TYPE,

p_searchtext IN FSRQH_RailQuotationHeader.fsrqh_shipmenttype%TYPE,

p_page_index IN fsdcl_documentationchangelog.fsdcl_recordid%TYPE,

p_page_size IN fsdcl_documentationchangelog.fsdcl_recordid%TYPE,

p_sort_field IN fsdcl_documentationchangelog.fsdcl_tablename%TYPE,

p_sort_type IN fsdcl_documentationchangelog.fsdcl_tablename%TYPE,

p_reference_count OUT FSRQH_RailQuotationHeader.fsofo_id%TYPE,

p_reference OUT FS_RailQuotation.t_list,

p_issuccess OUT FSRQH_RailQuotationHeader.fsrqh_shipmenttype%TYPE,

p_message OUT FS_RailQuotation.t_list);

PROCEDURE getLov_customerdetails_prc (

p_fsofo_id IN FSRQH_RailQuotationHeader.fsofo_id%TYPE,

p_fsbue_id IN fsbue_businessentity.fsbue_id%TYPE,

p_customeraddress_list OUT FS_RailQuotation.t_list,

p_issuccess OUT fsbue_businessentity.fsbue_name%TYPE,

p_message OUT FS_RailQuotation.t_list);



PROCEDURE getlist_fsrqh_prc ( --getlist for rail quotation

p_customer_id IN FSRQH_RailQuotationHeader.fsbue_idcustomer%TYPE,

p_fsofo_id IN FSRQH_RailQuotationHeader.fsofo_id%TYPE,

p_quotationtype IN FSRQH_RailQuotationHeader.fsrqh_shipmenttype%TYPE,

p_validfrom IN FSRQH_RailQuotationHeader.fsrQH_validfrom%TYPE,

p_validto IN FSRQH_RailQuotationHeader.fsrQH_validto%TYPE,

p_status IN FSRQH_RailQuotationHeader.fsrQH_status%TYPE,

p_shipmenttype IN FSRQH_RailQuotationHeader.fsrqh_shipmenttype%TYPE,

p_referenceno IN FSRQH_RailQuotationHeader.fsrqh_id%TYPE,

p_fromstation IN FSRQH_RailQuotationHeader.fsbue_idcustomer%TYPE,

p_tostation IN FSRQH_RailQuotationHeader.fsbue_idcustomer%TYPE,

p_carrier_id IN FSRQH_RailQuotationHeader.fsbue_idcustomer%TYPE,

p_quotationparty IN FSRQH_RailQuotationHeader.fsrqh_quotationpartytype%TYPE,

p_partner IN FSRQH_RailQuotationHeader.fsofo_idpartner%TYPE,

p_salesoffice IN FSRQH_RailQuotationHeader.fsofo_idpartner%TYPE,

p_page_index IN NUMBER,

p_page_size IN NUMBER,

p_sort_field IN VARCHAR2,

p_sort_type IN VARCHAR2,

p_quotation_count OUT NUMBER,

p_quotation_list OUT FS_RailQuotation.t_list,

p_issuccess OUT VARCHAR2,

p_message OUT FS_RailQuotation.t_list);



PROCEDURE Get_generaldetails_Prc ( --header edit

p_fsrqh_id IN FSRQH_RailQuotationHeader.fsrqh_id%TYPE,

p_fsofo_id IN FSRQH_RailQuotationHeader.fsofo_id%TYPE,

p_general_details OUT FS_RailQuotation.t_list,

p_route_details OUT FS_RailQuotation.t_list,

p_issuccess OUT FSRTH_RailTariffHeader.fsrth_referencenumber %TYPE,

p_message OUT FS_RailQuotation.t_list);



PROCEDURE Get_rateretrive_Prc ( --SP to fetch charges details using rate retrive button

p_fsofo_id IN FSRQH_RailQuotationHeader.fsofo_id%TYPE,

p_customer_id IN FSRQH_RailQuotationHeader.fsrqh_id%TYPE,

p_shipmenttype IN FSRTH_RailTariffHeader.fsrth_referencenumber%TYPE,

p_carrier IN fsbue_businessentity.fsbue_name%TYPE,

p_routedetails IN fsdcl_documentationchangelog.fsdcl_content%TYPE,

p_header_details OUT FS_RailQuotation.t_list,

p_rate_details OUT FS_RailQuotation.t_list,

p_issuccess OUT FSRTH_RailTariffHeader.fsrth_referencenumber%TYPE,

p_message OUT FS_RailQuotation.t_list);



PROCEDURE Get_localcharges_Prc ( --SP to fetch local charges details

p_fsofo_id IN FSRQH_RailQuotationHeader.fsofo_id%TYPE,

p_customer_id IN FSRQH_RailQuotationHeader.fsrqh_id%TYPE,

p_exportimport IN FSRTH_RailTariffHeader.fsrth_referencenumber%TYPE,

p_shipmenttype IN FSRTH_RailTariffHeader.fsrth_referencenumber%TYPE,

p_route IN fsdcl_documentationchangelog.fsdcl_content%TYPE,

p_rate_details OUT FS_RailQuotation.t_list,

p_issuccess OUT FSRTH_RailTariffHeader.fsrth_referencenumber%TYPE,

p_message OUT FS_RailQuotation.t_list);



/*PROCEDURE Get_ratemanagement_Prc ( --charges details(not in use)

p_fsofo_id IN FSRQH_RailQuotationHeader.fsofo_id%TYPE,

p_fsprt_fromstation IN FSRQH_RailQuotationHeader.fsrqh_id%TYPE,

p_fsprt_tostation IN FSRQH_RailQuotationHeader.fsrqh_id%TYPE,

p_fsloc_origin IN FSRQH_RailQuotationHeader.fsrqh_id%TYPE,

p_fsloc_destination IN FSRQH_RailQuotationHeader.fsrqh_id%TYPE,

p_carrier_details OUT FS_RailQuotation.t_list,

p_freight_details OUT FS_RailQuotation.t_list,

p_surcharge_details OUT FS_RailQuotation.t_list,

p_localcharge_details OUT FS_RailQuotation.t_list,

p_issuccess OUT FSRTH_RailTariffHeader.fsrth_referencenumber%TYPE,

p_message OUT FS_RailQuotation.t_list);*/





/*PROCEDURE Get_header_Prc ( --header edit

p_fsrqh_id IN FSRQH_RailQuotationHeader.fsrqh_id%TYPE,

p_fsofo_id IN FSRQH_RailQuotationHeader.fsofo_id%TYPE,

p_rate_details OUT FS_RailQuotation.t_list,

p_transport_details OUT FS_RailQuotation.t_list,

p_issuccess OUT FSRTH_RailTariffHeader.fsrth_referencenumber %TYPE,

p_message OUT FS_RailQuotation.t_list); */

PROCEDURE Getlist_charges_Prc ( --charges details

p_fsrqt_id IN FSRQH_RailQuotationHeader.fsrqh_id%TYPE,

p_fsofo_id IN FSRQH_RailQuotationHeader.fsofo_id%TYPE,

p_route IN FSRQH_RailQuotationHeader.fsrqh_remarks%TYPE,

p_general_details OUT FS_RailQuotation.t_list,

p_header_details OUT FS_RailQuotation.t_list,

p_freight_details OUT FS_RailQuotation.t_list,

p_surcharge_details OUT FS_RailQuotation.t_list,

p_localcharge_details OUT FS_RailQuotation.t_list,

p_precarrige_details OUT FS_RailQuotation.t_list,

p_precarriegecharge_details OUT FS_RailQuotation.t_list,

p_oncarrige_details OUT FS_RailQuotation.t_list,

p_oncarriegecharge_details OUT FS_RailQuotation.t_list,

p_issuccess OUT FSRTH_RailTariffHeader.fsrth_referencenumber %TYPE,

p_message OUT FS_RailQuotation.t_list);



PROCEDURE Get_charges_Prc ( --charges details

p_fsrqr_id IN FSRQH_RailQuotationHeader.fsrqh_id%TYPE,

p_fsofo_id IN FSRQH_RailQuotationHeader.fsofo_id%TYPE,

p_charge_details OUT FS_RailQuotation.t_list,

p_issuccess OUT FSRTH_RailTariffHeader.fsrth_referencenumber %TYPE,

p_message OUT FS_RailQuotation.t_list);



/*PROCEDURE Getlist_intermodel_Prc ( --intermodel details(not in use)

p_fsrqh_id IN FSRQH_RailQuotationHeader.fsrqh_id%TYPE,

p_fsofo_id IN FSRQH_RailQuotationHeader.fsofo_id%TYPE,

p_precarrige_details OUT FS_RailQuotation.t_list,

p_precarriegecharge_details OUT FS_RailQuotation.t_list,

p_oncarrige_details OUT FS_RailQuotation.t_list,

p_oncarriegecharge_details OUT FS_RailQuotation.t_list,

p_issuccess OUT FSRTH_RailTariffHeader.fsrth_referencenumber %TYPE,

p_message OUT FS_RailQuotation.t_list);*/



PROCEDURE Get_intermodel_Prc ( --intermodel details

p_fsrqu_id IN FSRQH_RailQuotationHeader.fsrqh_id%TYPE,

p_fsofo_id IN FSRQH_RailQuotationHeader.fsofo_id%TYPE,

p_carrige_details OUT FS_RailQuotation.t_list,

p_issuccess OUT FSRTH_RailTariffHeader.fsrth_referencenumber %TYPE,

p_message OUT FS_RailQuotation.t_list);



PROCEDURE put_fsrqh_prc(

p_fsrqh_id IN OUT FSRQH_RailQuotationHeader.fsrqh_id%TYPE,

p_fsrqs_id IN OUT FSRQS_RailQuOtationStatus.fsrqs_id%TYPE,

p_fsofo_id IN FSRQH_RailQuotationHeader.fsofo_id%TYPE,

p_fsbue_idcustomer IN FSRQH_RailQuotationHeader.fsbue_idcustomer%TYPE,

p_fsofo_idsalesoffice IN FSRQH_RailQuotationHeader.fsofo_idsalesoffice%TYPE,

p_fsrqh_quotationtype IN FSRQH_RailQuotationHeader.fsrqh_quotationtype%TYPE,

p_fsrqh_shipmenttype IN FSRQH_RailQuotationHeader.fsrqh_shipmenttype%TYPE,

p_fsrqh_mcq IN FSRQH_RailQuotationHeader.fsrqh_minimumconfirmedquantity%TYPE,

p_fsrqh_mcqtype IN FSRQH_RailQuotationHeader.fsrqh_minimumconfirmedquantitytype%TYPE,

p_fsrqh_referencenumber IN OUT FSRQH_RailQuotationHeader.fsrqh_referencenumber%TYPE,

p_fsrqh_referencedate IN FSRQH_RailQuotationHeader.fsrqh_referencedate%TYPE,

p_fsrqh_validfrom IN FSRQH_RailQuotationHeader.fsrqh_validfrom%TYPE,

p_fsrqh_validto IN FSRQH_RailQuotationHeader.fsrqh_validto%TYPE,

p_fsrqh_customerdetails IN FSRQH_RailQuotationHeader.fsrqh_customerdetails%TYPE,

p_fsrqh_incoterms IN FSRQH_RailQuotationHeader.fsrqh_incoterms%TYPE,

p_fsrqh_isexportimport IN FSRQH_RailQuotationHeader.fsrqh_isexportimport%TYPE,

p_fscry_idquotation IN FSRQH_RailQuotationHeader.fscry_idquotation%TYPE,

p_fsrqh_remarks IN FSRQH_RailQuotationHeader.fsrqh_remarks%TYPE,

p_fsrqh_status IN FSRQH_RailQuotationHeader.fsrqh_status%TYPE,

p_fsrqc_id IN OUT FSRQC_RailQuotationCarrier.fsrqc_id%TYPE,

p_fsbue_idcarrier IN FSRQC_RailQuotationCarrier.fsbue_idcarrier%TYPE,

p_fsrqh_isshowcarrier IN FSRQH_RailQuotationHeader.fsrqh_isshowcarrier%TYPE,

p_fsrqh_quotationpartytype IN FSRQH_RailQuotationHeader.fsrqh_quotationpartytype%TYPE,

p_fsofo_idpartner IN FSRQH_RailQuotationHeader.fsofo_idpartner%TYPE,

p_fsrqh_weight IN FSRQH_RailQuotationHeader.fsrqh_weight%TYPE,

p_fsmea_idweight IN FSRQH_RailQuotationHeader.fsmea_idweight%TYPE,

p_fsrqh_volume IN FSRQH_RailQuotationHeader.fsrqh_volume%TYPE,

p_fsrqh_withdrawreason IN FSRQH_RailQuotationHeader.fsrqh_withdrawreason%TYPE,

p_fsdlm_id IN FSRQH_RailQuotationHeader.fsrqh_remarks%TYPE,

p_fsrqh_purgeyear IN FSRQH_RailQuotationHeader.fsrqh_purgeyear%TYPE,

p_user_id IN FSRQH_RailQuotationHeader.fsrqh_createdby%TYPE,

p_record_status IN FSRQH_RailQuotationHeader.fsrqh_status%TYPE,

p_issuccess OUT FSRQH_RailQuotationHeader.fsrqh_status%TYPE,

p_message OUT FS_RailQuotation.t_list);



PROCEDURE put_fsrqt_prc(

p_fsrqt_id IN OUT FSRQT_RailQuotationTransportStation.fsrqt_id%TYPE,

p_fsofo_id IN FSRQT_RailQuotationTransportStation.fsofo_id%TYPE,

p_fsrqh_id IN FSRQT_RailQuotationTransportStation.fsrqh_id%TYPE,

p_fsloc_idplaceoforigin IN FSRQT_RailQuotationTransportStation.fsloc_idplaceoforigin%TYPE,

p_fsrat_idfromstation IN FSRQT_RailQuotationTransportStation.fsrat_idfromstation%TYPE,

p_fsrat_idtostation IN FSRQT_RailQuotationTransportStation.fsrat_idtostation%TYPE,

p_fsloc_idplaceofdestination IN FSRQT_RailQuotationTransportStation.fsloc_idplaceofdestination%TYPE,

p_fsrqt_travellingtime IN FSRQT_RailQuotationTransportStation.fsrqt_travellingtime%TYPE,

p_fsrqt_dayoffrequency IN FSRQT_RailQuotationTransportStation.fsrqt_dayoffrequency%TYPE,

p_fseqt_sizetype IN FSRQH_RailQuotationHeader.fsrqh_remarks%TYPE,

p_fscmg_id IN FSRQH_RailQuotationHeader.fsrqh_remarks%TYPE,

p_routeexist IN FSRQH_RailQuotationHeader.fsrqh_remarks%TYPE,

p_fsrqt_purgeyear IN FSRQT_RailQuotationTransportStation.fsrqt_purgeyear%TYPE,

p_user_id IN FSRQT_RailQuotationTransportStation.fsrqt_createdby%TYPE,

p_record_status IN FSRQT_RailQuotationTransportStation.fsrqt_purgeyear%TYPE,

p_issuccess OUT FSRQT_RailQuotationTransportStation.fsrqt_purgeyear%TYPE,

p_message OUT FS_RailQuotation.t_list);



PROCEDURE put_fsrqr_prc(

p_fsrqr_id IN OUT FSRQR_RailQuotationRate.fsrqr_id%TYPE,

p_fsofo_id IN FSRQR_RailQuotationRate.fsofo_id%TYPE,

p_fsrqt_id IN FSRQR_RailQuotationRate.fsrqt_id%TYPE,

p_fschg_id IN FSRQR_RailQuotationRate.fschg_id%TYPE,

p_fslrr_id IN FSRQR_RailQuotationRate.fslrr_id%TYPE,

p_fsrqr_basis IN FSRQR_RailQuotationRate.fsrqr_basis%TYPE,

p_fseqt_sizetype IN FSRQR_RailQuotationRate.fseqt_sizetype%TYPE,

p_fscmg_id IN FSRQR_RailQuotationRate.fscmg_id%TYPE,

p_fsrtr_id IN FSRQR_RailQuotationRate.fsrtr_id%TYPE,

p_fsrsr_id IN FSRQR_RailQuotationRate.fsrsr_id%TYPE,

p_fsrqu_id IN FSRQR_RailQuotationRate.fsrqu_id%TYPE,

p_fsrqr_weight IN FSRQR_RailQuotationRate.fsrqr_weight%TYPE,

p_fsmea_id IN FSRQR_RailQuotationRate.fsrqr_purgeyear%TYPE,

p_fsrqr_volume IN FSRQR_RailQuotationRate.fsrqr_volume%TYPE,

p_fsrqr_Tariffrate IN FSRQR_RailQuotationRate.fsrqr_Tariffrate%TYPE,

p_fsrqr_ratemarkuptype IN FSRQR_RailQuotationRate.fsrqr_ratemarkuptype%TYPE,

p_fsrqr_ratemarkupvalue IN FSRQR_RailQuotationRate.fsrqr_ratemarkupvalue%TYPE,

p_fsrqr_sellingrate IN FSRQR_RailQuotationRate.fsrqr_sellingrate%TYPE,

p_fscry_id IN FSRQR_RailQuotationRate.fscry_id%TYPE,

p_fsrqr_tariffvolume IN FSRQR_RailQuotationRate.fsrqr_tariffvolume%TYPE,

p_fsrqr_volumemarkuptype IN FSRQR_RailQuotationRate.fsrqr_volumemarkuptype%TYPE,

p_fsrqr_volumemarkupvalue IN FSRQR_RailQuotationRate.fsrqr_volumemarkupvalue%TYPE,

p_fsrqr_volumesellingrate IN FSRQR_RailQuotationRate.fsrqr_volumesellingrate%TYPE,

p_fsrqr_tariffweight IN FSRQR_RailQuotationRate.fsrqr_tariffweight%TYPE,

p_fsrqr_weightmarkuptype IN FSRQR_RailQuotationRate.fsrqr_weightmarkuptype%TYPE,

p_fsrqr_weightmarkupvalue IN FSRQR_RailQuotationRate.fsrqr_weightmarkupvalue%TYPE,

p_fsrqr_weightsellingrate IN FSRQR_RailQuotationRate.fsrqr_weightsellingrate%TYPE,

p_fsbue_idcarrier IN FSRQR_RailQuotationRate.fsbue_idcarrier%TYPE,

p_fsofo_idsalesoffice IN FSRQR_RailQuotationRate.fsofo_idsalesoffice%TYPE,

p_fsrqr_isexportimport IN FSRQR_RailQuotationRate.fsrqr_isexportimport%TYPE,

p_fsrqr_remarks IN FSRQR_RailQuotationRate.fsrqr_remarks%TYPE,

p_fschg_inclusive IN FSRQR_RailQuotationRate.fsrqr_remarks%TYPE,

p_fsrqr_purgeyear IN FSRQR_RailQuotationRate.fsrqr_purgeyear%TYPE,

p_user_id IN FSRQR_RailQuotationRate.fsrqr_createdby%TYPE,

p_record_status IN FSRQR_RailQuotationRate.fsrqr_purgeyear%TYPE,

p_issuccess OUT FSRQR_RailQuotationRate.fsrqr_purgeyear%TYPE,

p_message OUT FS_RailQuotation.t_list);



PROCEDURE put_fsrqu_prc (

p_fsrqu_id IN OUT FSRQU_RailQuotationIntermodel.fsrqu_id%TYPE,

p_fsofo_id IN FSRQU_RailQuotationIntermodel.fsofo_id%TYPE,

p_fsrqh_id IN FSRQU_RailQuotationIntermodel.fsrqh_id%TYPE,

p_fsrqu_shipmenttype IN FSRQU_RailQuotationIntermodel.fsrqu_shipmenttype%TYPE,

p_fsrqu_ismultimode IN FSRQU_RailQuotationIntermodel.fsrqu_ismultimode%TYPE,

p_fsrqu_ismultistop IN FSRQU_RailQuotationIntermodel.fsrqu_ismultistop%TYPE,

p_fsloc_idfrom IN FSRQU_RailQuotationIntermodel.fsloc_idfrom%TYPE,

p_fsrqu_zipcodefrom IN FSRQU_RailQuotationIntermodel.fsrqu_zipcodefrom%TYPE,

p_fsloc_idto IN FSRQU_RailQuotationIntermodel.fsloc_idto%TYPE,

p_fsrqu_zipcodeto IN FSRQU_RailQuotationIntermodel.fsrqu_zipcodeto%TYPE,

p_fsrqu_modeoftransport IN FSRQU_RailQuotationIntermodel.fsrqu_modeoftransport%TYPE,

p_fsrqu_transit IN FSRQU_RailQuotationIntermodel.fsrqu_transit%TYPE,

p_fsrqu_transittype IN FSRQU_RailQuotationIntermodel.fsrqu_transittype%TYPE,

p_fsrqu_specialinstruction IN FSRQU_RailQuotationIntermodel.fsrqu_specialinstruction%TYPE,

p_fsrqu_fromlocationaddress IN FSRQU_RailQuotationIntermodel.fsrqu_fromlocationaddress%TYPE,

p_fsrqu_tolocationaddress IN FSRQU_RailQuotationIntermodel.fsrqu_tolocationaddress%TYPE,

p_fsrqu_locationtype IN FSRQU_RailQuotationIntermodel.fsrqu_locationtype%TYPE,

p_fsrqu_idparent IN FSRQU_RailQuotationIntermodel.fsrqu_idparent%TYPE,

p_fsrqu_purgeyear IN FSRQU_RailQuotationIntermodel.fsrqu_purgeyear%TYPE,

p_user_id IN FSRQU_RailQuotationIntermodel.fsrqu_createdby%TYPE,

p_record_status IN FSRQU_RailQuotationIntermodel.fsrqu_purgeyear%TYPE,

p_issuccess OUT FSRQU_RailQuotationIntermodel.fsrqu_purgeyear%TYPE,

p_message OUT FS_RailQuotation.t_list);

PROCEDURE put_fsrql_prc(

p_fsrql_id IN OUT FSRQL_RailQuotationEmail.fsrql_id%TYPE,

p_fsofo_id IN FSRQL_RailQuotationEmail.fsofo_id%TYPE,

p_fsrqh_id IN FSRQL_RailQuotationEmail.fsrqh_id%TYPE,

p_fsrql_email IN FSRQL_RailQuotationEmail.fsrql_email%TYPE,

p_fsrql_purgeyear IN FSRQL_RailQuotationEmail.fsrql_purgeyear%TYPE,

p_user_id IN FSRQL_RailQuotationEmail.fsrql_createdby%TYPE,

p_record_status IN FSRQL_RailQuotationEmail.fsrql_purgeyear%TYPE,

p_issuccess OUT FSRQL_RailQuotationEmail.fsrql_purgeyear%TYPE,

p_message OUT FS_RailQuotation.t_list);





PROCEDURE put_statuschange_prc(

p_fsrqh_id IN OUT FSRQH_RailQuotationHeader.fsrqh_id%TYPE,

p_fsrqs_id IN OUT FSRQS_RailQuOtationStatus.fsrqs_id%TYPE,

p_fsofo_id IN FSRQH_RailQuotationHeader.fsofo_id%TYPE,

p_fsrqh_status IN FSRQH_RailQuotationHeader.fsrqh_status%TYPE,

p_fsrqh_withdrawreason IN FSRQH_RailQuotationHeader.fsrqh_withdrawreason%TYPE,

p_user_id IN FSRQS_RailQuOtationStatus.fsrqs_createdby%TYPE,

p_record_status IN FSRQS_RailQuOtationStatus.fsrqs_status%TYPE,

p_issuccess OUT FSRQS_RailQuOtationStatus.fsrqs_status%TYPE,

p_message OUT FS_RailQuotation.t_list);



END FS_RailQuotation;



create or replace PACKAGE BODY FS_RailQuotation

AS

PROCEDURE getLov_reference_prc (

p_fsofo_id IN FSRQH_RailQuotationHeader.fsofo_id%TYPE,

p_searchtext IN FSRQH_RailQuotationHeader.fsrqh_shipmenttype%TYPE,

p_page_index IN fsdcl_documentationchangelog.fsdcl_recordid%TYPE,

p_page_size IN fsdcl_documentationchangelog.fsdcl_recordid%TYPE,

p_sort_field IN fsdcl_documentationchangelog.fsdcl_tablename%TYPE,

p_sort_type IN fsdcl_documentationchangelog.fsdcl_tablename%TYPE,

p_reference_count OUT FSRQH_RailQuotationHeader.fsofo_id%TYPE,

p_reference OUT FS_RailQuotation.t_list,

p_issuccess OUT FSRQH_RailQuotationHeader.fsrqh_shipmenttype%TYPE,

p_message OUT FS_RailQuotation.t_list)

AS

l_query CLOB;

l_query_cnt CLOB;



BEGIN

IF FS_SECURITY.SQL_INJECTION_CHECK_FNC (p_searchtext) = 'Y' OR

FS_SECURITY.SQL_INJECTION_CHECK_FNC (p_sort_field) = 'Y' OR

FS_SECURITY.SQL_INJECTION_CHECK_FNC (p_sort_type) = 'Y'

THEN

l_query :='SELECT NULL AS fsrqh_id ,

NULL AS fsrqh_referencenumber

FROM FSRQH_RailQuotationHeader fsrqh

WHEER 1=2 ' ;



ELSE

l_query :='SELECT fsrqh.fsrqh_id AS fsrqh_id ,

fsrqh.fsrqh_referencenumber AS fsrqh_referencenumber

FROM FSRQH_RailQuotationHeader fsrqh

WHERE(('''|| p_searchtext|| ''' IS NULL) OR

( (UPPER(fsrqh.fsrqh_referencenumber) LIKE '''|| UPPER (p_searchtext)|| '%'||''')

)) ';



l_query_cnt := 'SELECT COUNT(fsrqh_id) FROM (' || l_query || ') ';



p_reference_count :=0;



IF p_page_index > 0 THEN



EXECUTE IMMEDIATE l_query_cnt INTO p_reference_count;



l_query := l_query || ' order by ';

l_query := l_query || p_sort_field;

l_query := l_query || ' ';

l_query := l_query || p_sort_type;

l_query := l_query || ' offset ( ('|| p_page_index|| ' - 1) * '|| p_page_size|| ') rows fetch next '|| p_page_size|| ' rows only ';



END IF;



OPEN p_reference FOR l_query;



END IF;

p_issuccess := fs_constants.k_success;

OPEN p_message FOR

SELECT fs_constants.k_success_msgcode AS msg_code,

NULL AS msg_value

FROM FSDUL_Dual;



EXCEPTION

WHEN OTHERS THEN

p_issuccess := fs_constants.k_failure;

fs_exception.raise_exception;



END getLov_reference_prc;



PROCEDURE getLov_copyreference_prc (

p_fsofo_id IN FSRQH_RailQuotationHeader.fsofo_id%TYPE,

p_searchtext IN FSRQH_RailQuotationHeader.fsrqh_shipmenttype%TYPE,

p_page_index IN fsdcl_documentationchangelog.fsdcl_recordid%TYPE,

p_page_size IN fsdcl_documentationchangelog.fsdcl_recordid%TYPE,

p_sort_field IN fsdcl_documentationchangelog.fsdcl_tablename%TYPE,

p_sort_type IN fsdcl_documentationchangelog.fsdcl_tablename%TYPE,

p_reference_count OUT FSRQH_RailQuotationHeader.fsofo_id%TYPE,

p_reference OUT FS_RailQuotation.t_list,

p_issuccess OUT FSRQH_RailQuotationHeader.fsrqh_shipmenttype%TYPE,

p_message OUT FS_RailQuotation.t_list)

AS

l_query CLOB;

l_query_cnt CLOB;



BEGIN

IF FS_SECURITY.SQL_INJECTION_CHECK_FNC (p_searchtext) = 'Y' OR

FS_SECURITY.SQL_INJECTION_CHECK_FNC (p_sort_field) = 'Y' OR

FS_SECURITY.SQL_INJECTION_CHECK_FNC (p_sort_type) = 'Y'

THEN

l_query :='SELECT NULL AS fsrqh_id ,

NULL AS fsrqh_referencenumber

FROM fsdul_dual fsdul

WHEER 1=2 ' ;



ELSE

l_query :='SELECT fsrqh.fsrqh_id AS fsrqh_id ,

fsrqh.fsrqh_referencenumber AS fsrqh_referencenumber,

partytype.fsgec_description AS Quotationpartytype,

fsrqh.fsrqh_validto AS fsrqh_validto,

fsgec_export.fsgec_description AS fsgec_export_description,

fsbue.fsbue_name AS Customername,

fsofo.fsofo_organizationname AS Partnername

FROM FSRQH_RailQuotationHeader fsrqh

LEFT JOIN fsbue_businessentity fsbue ON (fsbue.fsbue_id=fsrqh.fsbue_idcustomer)

LEFT JOIN fsofo_officeorganization fsofo ON (fsofo.fsofo_id=fsrqh.fsofo_idpartner)

JOIN FSGEC_GeneralCode partytype ON (partytype.fsgec_value=fsrqh.fsrqh_quotationpartytype AND partytype.fsgec_referencename=''QUOTATIONPARTYTYPE'')

JOIN fsgec_generalcode fsgec_export ON (fsgec_export.fsgec_referencename = ''BOOKINGTYPE'' AND fsgec_export.fsgec_value =fsrqh.fsrqh_isexportimport)

WHERE(('''|| p_searchtext|| ''' IS NULL) OR

( (UPPER(fsrqh.fsrqh_referencenumber) LIKE '''|| UPPER (p_searchtext)|| '%'||''')

)) ';



l_query_cnt := 'SELECT COUNT(fsrqh_id) FROM (' || l_query || ') ';



p_reference_count :=0;



IF p_page_index > 0 THEN



EXECUTE IMMEDIATE l_query_cnt INTO p_reference_count;



l_query := l_query || ' order by ';

l_query := l_query || p_sort_field;

l_query := l_query || ' ';

l_query := l_query || p_sort_type;

l_query := l_query || ' offset ( ('|| p_page_index|| ' - 1) * '|| p_page_size|| ') rows fetch next '|| p_page_size|| ' rows only ';



END IF;



OPEN p_reference FOR l_query;



END IF;

p_issuccess := fs_constants.k_success;

OPEN p_message FOR

SELECT fs_constants.k_success_msgcode AS msg_code,

NULL AS msg_value

FROM FSDUL_Dual;



EXCEPTION

WHEN OTHERS THEN

p_issuccess := fs_constants.k_failure;

fs_exception.raise_exception;



END getLov_copyreference_prc;

PROCEDURE getLov_customerdetails_prc (

p_fsofo_id IN FSRQH_RailQuotationHeader.fsofo_id%TYPE,

p_fsbue_id IN fsbue_businessentity.fsbue_id%TYPE,

p_customeraddress_list OUT FS_RailQuotation.t_list,

p_issuccess OUT fsbue_businessentity.fsbue_name%TYPE,

p_message OUT FS_RailQuotation.t_list)



IS



BEGIN

OPEN p_customeraddress_list FOR

SELECT fsbua.fsbua_id AS fsbua_id,

fsbua.fsbua_buildingname ||','||fsbua.fsbua_street||','||fsloc.fsloc_name||','||fscsd.fscsd_name||','||fscty.fscty_name||'-'||fszip.fszip_code AS fsbua_address

FROM FSBUA_BusinessEntityAddress fsbua

LEFT JOIN FSLOC_Location fsloc ON (fsloc.fsloc_id = fsbua.fsloc_id)

LEFT JOIN FSCTY_Country fscty ON (fscty.fscty_id=fsloc.fscty_id)

LEFT JOIN FSCSD_Countrysubdivision fscsd ON (fsloc.fscsd_id = fscsd.fscsd_id)

LEFT JOIN fszip_zipcode fszip ON (fszip.fszip_id = fsbua.fszip_id)

WHERE fsbua.fsbue_id=p_fsbue_id

AND fsbua.fsbua_addresstype='O';



p_issuccess := fs_constants.k_success;

OPEN p_message FOR

SELECT fs_constants.k_success_msgcode AS msg_code,

NULL AS msg_value

FROM FSDUL_Dual;



EXCEPTION

WHEN OTHERS THEN

p_issuccess := fs_constants.k_failure;

fs_exception.raise_exception;



END getLov_customerdetails_prc;

PROCEDURE getlist_fsrqh_prc ( --getlist for rail quotation

p_customer_id IN FSRQH_RailQuotationHeader.fsbue_idcustomer%TYPE,

p_fsofo_id IN FSRQH_RailQuotationHeader.fsofo_id%TYPE,

p_quotationtype IN FSRQH_RailQuotationHeader.fsrqh_shipmenttype%TYPE,

p_validfrom IN FSRQH_RailQuotationHeader.fsrQH_validfrom%TYPE,

p_validto IN FSRQH_RailQuotationHeader.fsrQH_validto%TYPE,

p_status IN FSRQH_RailQuotationHeader.fsrQH_status%TYPE,

p_shipmenttype IN FSRQH_RailQuotationHeader.fsrqh_shipmenttype%TYPE,

p_referenceno IN FSRQH_RailQuotationHeader.fsrqh_id%TYPE,

p_fromstation IN FSRQH_RailQuotationHeader.fsbue_idcustomer%TYPE,

p_tostation IN FSRQH_RailQuotationHeader.fsbue_idcustomer%TYPE,

p_carrier_id IN FSRQH_RailQuotationHeader.fsbue_idcustomer%TYPE,

p_quotationparty IN FSRQH_RailQuotationHeader.fsrqh_quotationpartytype%TYPE,

p_partner IN FSRQH_RailQuotationHeader.fsofo_idpartner%TYPE,

p_salesoffice IN FSRQH_RailQuotationHeader.fsofo_idpartner%TYPE,

p_page_index IN NUMBER,

p_page_size IN NUMBER,

p_sort_field IN VARCHAR2,

p_sort_type IN VARCHAR2,

p_quotation_count OUT NUMBER,

p_quotation_list OUT FS_RailQuotation.t_list,

p_issuccess OUT VARCHAR2,

p_message OUT FS_RailQuotation.t_list)

IS

l_query CLOB;

l_query_cnt CLOB;

BEGIN

IF FS_SECURITY.SQL_INJECTION_CHECK_FNC (p_status) = 'Y' OR

FS_SECURITY.SQL_INJECTION_CHECK_FNC (p_shipmenttype) = 'Y' OR

FS_SECURITY.SQL_INJECTION_CHECK_FNC (p_quotationtype) = 'Y' OR

FS_SECURITY.SQL_INJECTION_CHECK_FNC (p_quotationparty) = 'Y' OR

FS_SECURITY.SQL_INJECTION_CHECK_FNC (p_sort_field) = 'Y' OR

FS_SECURITY.SQL_INJECTION_CHECK_FNC (p_sort_type) = 'Y'

THEN

l_query_cnt := 'SELECT 0 FROM FSDUL_Dual';



l_query := 'SELECT NULL AS fsrqh_id,

NULL AS customer_name,

NULL AS quotationtype_description,

NULL AS shipmenttype_description,

NULL AS fsrqh_referencenumber,

NULL AS carrier_name,

NULL AS from_station,

NULL AS to_station,

NULL AS fsrqh_validfrom,

NULL AS fsrqh_validto,

NULL AS fsgec_statusdescription,

NULL AS fsgec_incotermsdescription,

NULL AS fsgec_export_description

FROM FSDUL_Dual

WHERE 1=2 ';



EXECUTE IMMEDIATE l_query_cnt INTO p_quotation_count;



ELSE

l_query := 'SELECT fsrqh.fsrqh_id AS fsrqh_id,

fsbue.fsbue_name AS customer_name,

fsgec_quotationtype.fsgec_description AS quotationtype_description,

fsgec_shipmenttype.fsgec_description AS shipmenttype_description,

fsrqh.fsrqh_referencenumber AS fsrqh_referencenumber,

(SELECT listagg (DISTINCT fsbue_carrier.fsbue_name,'','' ON overflow TRUNCATE WITH COUNT)

within GROUP (ORDER BY fsrqc.fsrqc_id)

FROM FSRQC_RailQuotationCarrier fsrqc

JOIN FSBUE_Businessentity fsbue_carrier ON (fsbue_carrier.fsbue_id=fsrqc.fsbue_idcarrier)

WHERE fsrqc.fsrqh_id=fsrqh.fsrqh_id

) AS carrier_name,

(SELECT listagg (DISTINCT fromstation.fsrat_name,'','' ON overflow TRUNCATE WITH COUNT)

within GROUP (ORDER BY fsrqt.fsrqt_id)

FROM FSRQT_RailQuotationTransportStation fsrqt

JOIN FSRAT_Railwaystationmaster fromstation ON (fromstation.fsrat_id=fsrqt.fsrat_idfromstation)

WHERE fsrqt.fsrqh_id=fsrqh.fsrqh_id

) AS from_station,



(SELECT listagg (DISTINCT tostation.fsrat_name,'','' ON overflow TRUNCATE WITH COUNT)

within GROUP (ORDER BY fsrqt.fsrqt_id)

FROM FSRQT_RailQuotationTransportStation fsrqt

JOIN FSRAT_Railwaystationmaster tostation ON (tostation.fsrat_id=fsrqt.fsrat_idtostation)

WHERE fsrqt.fsrqh_id=fsrqh.fsrqh_id

) AS to_station,

fsrqh.fsrqh_validfrom AS fsrqh_validfrom,

fsrqh.fsrqh_validto AS fsrqh_validto,

fsgec_status.fsgec_description AS fsgec_statusdescription,

fsgec_incoterms.fsgec_description AS fsgec_incotermsdescription,

fsgec_export.fsgec_description AS fsgec_export_description

FROM FSRQH_RailQuotationHeader fsrqh

JOIN fsbue_businessentity fsbue ON (fsbue.fsbue_id=fsrqh.fsbue_idcustomer)

JOIN fsgec_generalcode fsgec_quotationtype ON (fsgec_quotationtype.fsgec_referencename = ''QUOTATIONTYPE'' AND fsgec_quotationtype.fsgec_value = fsrqh.fsrqh_quotationtype )

JOIN fsgec_generalcode fsgec_shipmenttype ON (fsgec_shipmenttype.fsgec_referencename = ''SHIPMENTTYPE'' AND fsgec_shipmenttype.fsgec_value = fsrqh.fsrqh_shipmenttype )

-- JOIN FSRQC_RailQuotationCarrier fsrqc ON (fsrqc.fsrqh_id=fsrqh.fsrqh_id)

-- jOIN FSBUE_Businessentity fsbue_carrier ON (fsbue_carrier.fsbue_id=fsrqc.fsbue_idcarrier)

JOIN fsgec_generalcode fsgec_status ON (fsgec_status.fsgec_referencename = ''QUOTATIONSTATUS'' AND fsgec_status.fsgec_value = fsrqh.fsrqh_status )

JOIN fsgec_generalcode fsgec_incoterms ON (fsgec_incoterms.fsgec_referencename = ''BOOKINGINCOTERMS'' AND fsgec_incoterms.fsgec_value =fsrqh.fsrqh_incoterms)

JOIN fsgec_generalcode fsgec_export ON (fsgec_export.fsgec_referencename = ''BOOKINGTYPE'' AND fsgec_export.fsgec_value =fsrqh.fsrqh_isexportimport)

WHERE ('''|| p_status||''' IS NULL OR fsrqh.fsrqh_status ='''|| p_status ||''' )

AND ( '''|| p_shipmenttype||''' IS NULL OR fsrqh.fsrqh_shipmenttype ='''|| p_shipmenttype ||''' )

AND ( '''|| p_quotationtype||''' IS NULL OR fsrqh.fsrqh_quotationtype = '''|| p_quotationtype ||''' )

AND ( '''|| p_customer_id||''' IS NULL OR fsrqh.fsbue_idcustomer = '''|| p_customer_id ||''' )

AND ( '''|| p_referenceno||''' IS NULL OR fsrqh.fsrqh_id = '''|| p_referenceno ||''' )

AND ( '''|| p_validfrom||''' IS NULL OR TRUNC(fsrqh.fsrqh_validfrom) >= '''|| TRUNC(p_validfrom) ||''')

AND ( '''|| p_validto||''' IS NULL OR TRUNC(fsrqh.fsrqh_validto) <= '''||TRUNC(p_validto)||''')

AND ( '''|| p_quotationparty||''' IS NULL OR fsrqh.fsrqh_quotationpartytype = '''||p_quotationparty||''')

AND ( '''|| p_partner||''' IS NULL OR fsrqh.fsofo_idpartner = '''||p_partner||''')

AND ( '''|| p_salesoffice||''' IS NULL OR fsrqh.fsofo_idsalesoffice = '''||p_salesoffice||''')

AND ( '''|| p_carrier_id||''' IS NULL OR ( EXISTS (

SELECT ''X''

FROM FSRQC_RailQuotationCarrier fsrqc

WHERE fsrqc.fsrqh_id=fsrqh.fsrqh_id

AND fsrqc.fsbue_idcarrier = '''|| p_carrier_id|| ''' )))

AND ( '''|| p_fromstation||''' IS NULL OR ( EXISTS (

SELECT ''X''

FROM FSRQT_RailQuotationTransportStation fsrqt_from

WHERE fsrqt_from.fsrqh_id=fsrqh.fsrqh_id

AND fsrqt_from.fsrat_idfromstation = '''|| p_fromstation|| ''' )))

AND ( '''|| p_tostation||''' IS NULL OR ( EXISTS (

SELECT ''X''

FROM FSRQT_RailQuotationTransportStation fsrqt_to

WHERE fsrqt_to.fsrqh_id=fsrqh.fsrqh_id

AND fsrqt_to.fsrat_idtostation = '''|| p_tostation|| ''' ))) ';







l_query_cnt := 'SELECT COUNT(fsrqh_id) FROM (' || l_query || ') ';

p_quotation_count := 0;

IF p_page_index > 0 THEN

EXECUTE IMMEDIATE l_query_cnt INTO p_quotation_count;

l_query := l_query || ' order by ';

l_query := l_query || p_sort_field;

l_query := l_query || ' ';

l_query := l_query || p_sort_type;

l_query := l_query || ' offset ( ('|| p_page_index|| ' - 1) * '|| p_page_size|| ') rows fetch next '|| p_page_size|| ' rows only ';

END IF;

OPEN p_quotation_list FOR l_query;

END IF;

p_issuccess := fs_cONstants.k_success;

OPEN p_message FOR

SELECT fs_cONstants.k_success_msgcode AS msg_code,

NULL AS msg_value

FROM FSDUL_Dual;



EXCEPTION

WHEN OTHERS THEN

p_issuccess := fs_cONstants.k_failure;

fs_exceptiON.raise_exceptiON;

END getlist_fsrqh_prc;





PROCEDURE Get_generaldetails_Prc ( --header edit

p_fsrqh_id IN FSRQH_RailQuotationHeader.fsrqh_id%TYPE,

p_fsofo_id IN FSRQH_RailQuotationHeader.fsofo_id%TYPE,

p_general_details OUT FS_RailQuotation.t_list,

p_route_details OUT FS_RailQuotation.t_list,

p_issuccess OUT FSRTH_RailTariffHeader.fsrth_referencenumber %TYPE,

p_message OUT FS_RailQuotation.t_list)

AS

BEGIN



OPEN p_general_details FOR

SELECT fsrqh.fsrqh_id AS fsrqh_id,

fsrqh.fsbue_idcustomer AS fsbue_idcustomer,

fsbue.fsbue_name AS customer_name,

fsrqh.fsrqh_quotationtype AS fsrqh_quotationtype,

fsgec_quotationtype.fsgec_description AS quotationtype_description,

fsrqh.fsofo_idpartner AS fsofo_idpartner,

fsofo_partner.fsofo_organizationname AS parter_name,

quotationpartytype.fsgec_description AS quotationpartytype_description ,

fsrqh.fsrqh_quotationpartytype AS fsrqh_quotationpartytype,

fsrqh.fsrqh_incoterms AS incoterms,

fsgec_incoterms.fsgec_description AS fsgec_incotermsdescription,

fsrqh.fsrqh_isexportimport AS export_import,

fsgec_export.fsgec_description AS fsgec_export_description,

fsrqh.fsrqh_shipmenttype AS fsrqh_shipmenttype,

fsgec_shipmenttype.fsgec_description AS shipmenttype_description,

fsrqh.fsrqh_minimumconfirmedquantity AS fsrqh_minimumconfirmedquantity,

fsrqh.fsrqh_minimumconfirmedquantitytype AS fsrqh_minimumconfirmedquantitytype,

fsgec_mcqtype.fsgec_description AS mcqtype_description,

fsrqh.fsrqh_validfrom AS fsrqh_validfrom,

fsrqh.fsrqh_validto AS fsrqh_validto,

fsrqh.fsrqh_customerdetails AS fsrqh_customerdetails,

fsrqh.fscry_idquotation AS fscry_idquotation,

fscry.fscry_code AS quotation_currency,

fscry.fscry_description AS quotation_currency_description,

(SELECT listagg (DISTINCT fsrqc.fsbue_idcarrier,',' ON overflow TRUNCATE WITH COUNT)

within GROUP (ORDER BY fsrqc.fsrqc_id)

FROM FSRQC_RailQuotationCarrier fsrqc

WHERE fsrqc.fsrqh_id=fsrqh.fsrqh_id

) AS fsbue_idcarrier,

(SELECT listagg (DISTINCT fsbue.fsbue_name,',' ON overflow TRUNCATE WITH COUNT)

within GROUP (ORDER BY fsrqc.fsrqc_id)

FROM FSRQC_RailQuotationCarrier fsrqc

JOIN fsbue_businessentity fsbue ON(fsbue.fsbue_id=fsrqc.fsbue_idcarrier)

WHERE fsrqc.fsrqh_id=fsrqh.fsrqh_id

) AS carrier_name,

-- fsrqc.fsbue_idcarrier AS fsbue_idcarrier,

-- fsbue_carrier.fsbue_name AS carrier_name,

fsrqh.fsofo_idsalesoffice AS fsofo_idsalesoffice,

fsofo.fsofo_organizationname AS sales_office,

(SELECT listagg (DISTINCT fsrqd.fsdlm_id,',' ON overflow TRUNCATE WITH COUNT)

within GROUP (ORDER BY fsrqd.fsrqd_id)

FROM FSRQD_Railquotationdeal fsrqd

WHERE fsrqd.fsrqh_id=fsrqh.fsrqh_id

) AS deal,

(SELECT listagg (DISTINCT fsldm.fsdlm_referencenumber,',' ON overflow TRUNCATE WITH COUNT)

within GROUP (ORDER BY fsrqd.fsrqd_id)

FROM FSRQD_Railquotationdeal fsrqd

JOIN FSDLM_Dealmanagement fsldm ON (fsldm.fsdlm_id=fsrqd.fsdlm_id)

WHERE fsrqd.fsrqh_id=fsrqh.fsrqh_id

) AS deal_id,

fsrqh.fsrqh_remarks AS fsrqh_remarks,

fsrqh.fsrqh_weight AS fsrqh_weight,

fsrqh.fsmea_idweight AS fsmea_idweight,

fsmea.fsmea_isocode AS fsmea_isocode,

fsrqh.fsrqh_volume AS fsrqh_volume,

fsrqh.fsrqh_isshowcarrier AS fsrqh_isshowcarrier

FROM FSRQH_RailQuotationHeader fsrqh

LEFT JOIN fsbue_businessentity fsbue ON (fsbue.fsbue_id=fsrqh.fsbue_idcustomer)

LEFT JOIN fsmea_measurement fsmea ON (fsmea.fsmea_id=fsrqh.fsmea_idweight)

LEFT JOIN FSOFO_Officeorganization fsofo_partner ON (fsofo_partner.fsofo_id=fsrqh.fsofo_idpartner)

JOIN fsgec_generalcode fsgec_quotationtype ON (fsgec_quotationtype.fsgec_referencename = 'QUOTATIONTYPE' AND fsgec_quotationtype.fsgec_value = fsrqh.fsrqh_quotationtype )

JOIN fsgec_generalcode fsgec_shipmenttype ON (fsgec_shipmenttype.fsgec_referencename = 'SHIPMENTTYPE' AND fsgec_shipmenttype.fsgec_value = fsrqh.fsrqh_shipmenttype )

LEFT JOIN fsgec_generalcode fsgec_mcqtype ON (fsgec_mcqtype.fsgec_referencename = 'QUOTATIONMCQ' AND fsgec_mcqtype.fsgec_value = fsrqh.fsrqh_minimumconfirmedquantitytype )

JOIN fsgec_generalcode fsgec_incoterms ON (fsgec_incoterms.fsgec_referencename = 'BOOKINGINCOTERMS' AND fsgec_incoterms.fsgec_value =fsrqh.fsrqh_incoterms)

JOIN fsgec_generalcode fsgec_export ON (fsgec_export.fsgec_referencename = 'BOOKINGTYPE' AND fsgec_export.fsgec_value =fsrqh.fsrqh_isexportimport)

JOIN fsgec_generalcode quotationpartytype ON (quotationpartytype.fsgec_referencename='QUOTATIONPARTYTYPE' AND quotationpartytype.fsgec_value=fsrqh.fsrqh_quotationpartytype )

--JOIN FSRQC_RailQuotationCarrier fsrqc ON (fsrqc.fsrqh_id=fsrqh.fsrqh_id)

--LEFT JOIN FSBUE_Businessentity fsbue_carrier ON (fsbue_carrier.fsbue_id=fsrqc.fsbue_idcarrier)

JOIN FSCRY_Currency fscry ON (fscry.fscry_id=fsrqh.fscry_idquotation)

LEFT JOIN FSOFO_Officeorganization fsofo ON (fsofo.fsofo_id=fsrqh.fsofo_idsalesoffice)

WHERE fsrqh.fsrqh_id=p_fsrqh_id;

OPEN p_route_details FOR

SELECT fsrqh.fsrqh_id AS fsrqh_id,

fsrqt.fsrqt_id AS fsrqt_id,

fsrqt.fsloc_idplaceoforigin AS fsloc_idplaceoforigin,

fsloc_orignplace.fsloc_name AS placeoforigin,

fsrqt.fsrat_idfromstation AS fsrat_idfromstation,

fromstation.fsrat_name AS from_station,

fsrqt.fsrat_idtostation AS fsrat_idtostation,

tostation.fsrat_name AS to_station,

fsrqt.fsloc_idplaceofdestination AS fsloc_idplaceofdestination,

fsloc_destinationplace.fsloc_name AS placeofdestination,

(SELECT listagg (DISTINCT fsrqe.fseqt_sizetype,',' ON overflow TRUNCATE WITH COUNT)

within GROUP (ORDER BY fsrqe.fseqt_sizetype)

FROM T_FSRQE_RailQuotationEquipment fsrqe

WHERE fsrqe.fsrqt_id=fsrqt.fsrqt_id

) AS fseqt_sizetype,

(SELECT listagg (DISTINCT fscmg.fscmg_code,',' ON overflow TRUNCATE WITH COUNT)

within GROUP (ORDER BY fsrqm.fsrqm_id)

FROM FSRQM_RailQuotationCommodity fsrqm

JOIN FSCMG_Commoditygroup fscmg ON ( fscmg.fscmg_id=fsrqm.fscmg_id)

WHERE fsrqm.fsrqt_id=fsrqt.fsrqt_id

) AS fscmg_code ,

(SELECT listagg (DISTINCT fsrqm.fscmg_id,',' ON overflow TRUNCATE WITH COUNT)

within GROUP (ORDER BY fsrqm.fsrqm_id)

FROM FSRQM_RailQuotationCommodity fsrqm

WHERE fsrqm.fsrqt_id=fsrqt.fsrqt_id

) AS commodityid

FROM FSRQH_RailQuotationHeader fsrqh

JOIN FSRQT_RailQuotationTransportStation fsrqt ON (fsrqt.fsrqh_id=fsrqh.fsrqh_id)

JOIN FSRAT_Railwaystationmaster fromstation ON (fromstation.fsrat_id=fsrqt.fsrat_idfromstation)

JOIN FSRAT_Railwaystationmaster tostation ON (tostation.fsrat_id=fsrqt.fsrat_idtostation)

JOIN FSLOC_Location fsloc_orignplace ON (fsloc_orignplace.fsloc_id=fsrqt.fsloc_idplaceoforigin)

JOIN FSLOC_Location fsloc_destinationplace ON (fsloc_destinationplace.fsloc_id=fsrqt.fsloc_idplaceofdestination)

WHERE fsrqh.fsrqh_id=p_fsrqh_id;



p_issuccess := fs_constants.k_success;



OPEN p_message FOR

SELECT fs_constants.k_success_msgcode AS msg_code,

NULL AS msg_value

FROM dual;



EXCEPTION

WHEN OTHERS

THEN

p_issuccess := fs_constants.k_failure;

fs_exception.raise_exception;



END Get_generaldetails_Prc;





PROCEDURE Get_rateretrive_Prc ( --SP to fetch charges details using rate retrive button

p_fsofo_id IN FSRQH_RailQuotationHeader.fsofo_id%TYPE,

p_customer_id IN FSRQH_RailQuotationHeader.fsrqh_id%TYPE,

p_shipmenttype IN FSRTH_RailTariffHeader.fsrth_referencenumber%TYPE,

p_carrier IN fsbue_businessentity.fsbue_name%TYPE,

p_routedetails IN fsdcl_documentationchangelog.fsdcl_content%TYPE,

p_header_details OUT FS_RailQuotation.t_list,

p_rate_details OUT FS_RailQuotation.t_list,

p_issuccess OUT FSRTH_RailTariffHeader.fsrth_referencenumber%TYPE,

p_message OUT FS_RailQuotation.t_list)

AS



v_route fsdcl_documentationchangelog.fsdcl_content%TYPE;



BEGIN



v_route:=REPLACE(p_routedetails,'\','');





OPEN p_header_details FOR

WITH route_details AS

(SELECT jt.originid,jt.Destinationid,jt.FromStationid,jt.ToStationid,jt.Sequence,

LISTAGG(DISTINCT jt.ContainerType, ',') WITHIN GROUP (ORDER BY 1) ContainerType,

LISTAGG(DISTINCT jt.CommodityGroup, ',') WITHIN GROUP (ORDER BY 1) CommodityGroup

FROM

JSON_TABLE(v_route,'$[*]'

COLUMNS ( originid VARCHAR2(100) PATH '$.Orgin',

Destinationid VARCHAR2(100) PATH '$.Destination',

FromStationid VARCHAR2(100) PATH '$.From',

ToStationid VARCHAR2(100) PATH '$.To' ,

Sequence NUMBER PATH '$.Sequence',

NESTED PATH '$.ContainerType[*]'

COLUMNS (ContainerType VARCHAR2(100) PATH '$.Code'),

NESTED PATH '$.CommodityGroup[*]'

COLUMNS (CommodityGroup VARCHAR2(100) PATH '$.Id')

)

) jt

GROUP BY jt.originid,jt.Destinationid,jt.FromStationid,jt.ToStationid,jt.Sequence)



SELECT fsrtt.fsrtt_id AS fsrtt_id,

fsrth.fsbue_idline AS fsbue_idline,

fsbue.fsbue_name AS carrier_name,

fsrtt.fsloc_idplaceoforigin AS fsloc_idplaceoforigin,

fsloc_orginplace.fsloc_name AS placeoforigin,

fsrtt.fsrat_idfromstation AS fsrat_idfromstation,

fromstation.fsrat_name AS fromstation,

fsrtt.fsrat_idtostation AS fsrat_idtostation,

tostation.fsrat_name AS tostation,

fsrtt.fsloc_idplaceofdestination AS fsloc_idplaceofdestination,

fsloc_destinationplace.fsloc_name AS destinationplace,

fsrtt.fsrtt_travellingtime AS fsrtt_travellingtime,

(SELECT listagg (DISTINCT fsrtf.fsrtf_dayoffrequency,', ' ON overflow TRUNCATE WITH COUNT)

within GROUP (ORDER BY fsrtf.fsrtt_id)

FROM FSRTF_RailTariffTransportFrequency fsrtf

WHERE fsrtf.fsrtt_id=fsrtt.fsrtt_id

) AS fsrtf_dayoffrequency,



(SELECT listagg (DISTINCT fsgec_frequency.fsgec_description,', ' ON overflow TRUNCATE WITH COUNT)

within GROUP (ORDER BY fsrtf.fsrtt_id)

FROM FSRTF_RailTariffTransportFrequency fsrtf

JOIN fsgec_generalcode fsgec_frequency ON (fsgec_frequency.fsgec_referencename = 'FREQUENCY' AND fsgec_frequency.fsgec_value = fsrtf.fsrtf_dayoffrequency)

WHERE fsrtf.fsrtt_id=fsrtt.fsrtt_id

) AS fsrtf_frequencydescription,

route_details.sequence AS route_sequence

FROM FSRTH_RailTariffHeader fsrth

LEFT JOIN fsbue_businessentity fsbue ON (fsbue.fsbue_id=fsrth.fsbue_idline)

JOIN FSRTT_RailTariffTransportStation fsrtt ON (fsrtt.fsrth_id=fsrth.fsrth_id)

LEFT JOIN Fsloc_Location fsloc_orginplace ON (fsloc_orginplace.fsloc_id=fsrtt.fsloc_idplaceoforigin)

LEFT JOIN Fsloc_Location fsloc_destinationplace ON (fsloc_destinationplace.fsloc_id=fsrtt.fsloc_idplaceofdestination)

JOIN FSRAT_Railwaystationmaster fromstation ON (fromstation.fsrat_id=fsrtt.fsrat_idfromstation)

JOIN FSRAT_Railwaystationmaster tostation ON (tostation.fsrat_id=fsrtt.fsrat_idtostation)

JOIN route_details ON (NVL(fsrtt.fsloc_idplaceoforigin,0)=NVL(route_details.originid,0) AND fsrtt.fsrat_idfromstation=route_details.FromStationid

AND fsrtt.fsrat_idtostation=route_details.ToStationid AND NVL(fsrtt.fsloc_idplaceofdestination,0)=NVL(route_details.Destinationid,0))

WHERE fsrth.fsrth_shipmenttype = p_shipmenttype

AND (p_carrier IS NULL OR fsrth.fsbue_idline IN (SELECT REGEXP_SUBSTR (p_carrier, '[^,]+', 1,LEVEL) AS carrier FROM FSDUL_DUAL

CONNECT BY REGEXP_SUBSTR (p_carrier,'[^,]+', 1, LEVEL) IS NOT NULL))

AND (p_customer_id IS NULL OR EXISTS (SELECT 'X'

FROM FSRTB_RailTariffCustomer fsrtb

WHERE fsrtb.fsrth_id=fsrth.fsrth_id

AND fsrtb.fsbue_idcustomer= p_customer_id))

AND EXISTS (SELECT 'X'

FROM FSRTR_RailTariffRate fsrtr

WHERE fsrtr.fsrtt_id=fsrtt.fsrtt_id

AND (route_details.CommodityGroup IS NULL OR fsrtr.fscmg_id IN (SELECT REGEXP_SUBSTR (route_details.CommodityGroup, '[^,]+', 1,LEVEL) AS CommodityGroup FROM FSDUL_DUAL

CONNECT BY REGEXP_SUBSTR (route_details.CommodityGroup,'[^,]+', 1, LEVEL) IS NOT NULL))

AND (route_details.ContainerType IS NULL OR fsrtr.fseqt_sizetype IN (SELECT REGEXP_SUBSTR (route_details.ContainerType, '[^,]+', 1,LEVEL) AS ContainerType FROM FSDUL_DUAL

CONNECT BY REGEXP_SUBSTR (route_details.ContainerType,'[^,]+', 1, LEVEL) IS NOT NULL)))

AND (fsrth.fsrth_referencenumber,fsrth.fsrth_versionnumber) IN (SELECT fsrth.fsrth_referencenumber,MAX(fsrth.fsrth_versionnumber)

FROM fsrth_railtariffheader fsrth

GROUP BY fsrth.fsrth_referencenumber) ;



OPEN p_rate_details FOR



WITH route_details AS

(SELECT jt.originid,jt.Destinationid,jt.FromStationid,jt.ToStationid,jt.Sequence,

LISTAGG(DISTINCT jt.ContainerType, ',') WITHIN GROUP (ORDER BY 1) ContainerType,

LISTAGG(DISTINCT jt.CommodityGroup, ',') WITHIN GROUP (ORDER BY 1) CommodityGroup

FROM

JSON_TABLE(v_route,'$[*]'

COLUMNS ( originid VARCHAR2(100) PATH '$.Orgin',

Destinationid VARCHAR2(100) PATH '$.Destination',

FromStationid VARCHAR2(100) PATH '$.From',

ToStationid VARCHAR2(100) PATH '$.To' ,

Sequence NUMBER PATH '$.Sequence',

NESTED PATH '$.ContainerType[*]'

COLUMNS (ContainerType VARCHAR2(100) PATH '$.Code'),

NESTED PATH '$.CommodityGroup[*]'

COLUMNS (CommodityGroup VARCHAR2(100) PATH '$.Id')

)

) jt

GROUP BY jt.originid,jt.Destinationid,jt.FromStationid,jt.ToStationid,jt.Sequence)

SELECT fsrtr.fsrtr_id AS fsrtr_id,

fsrth.fsbue_idline AS fsbue_carrier,

fsrth.fsrth_referencenumber AS fsrth_referencenumber,

fsrtr.fschg_id AS fschg_id,

fschg.fschg_code AS fschg_code,

fschg.fschg_description AS fschg_description,

fsrtr.fseqt_sizetype AS fseqt_sizetype,

fsrtr.fscmg_id AS fscmg_id,

fscmg.fscmg_code AS fscmg_code,

fscmg.fscmg_description AS fscmg_description,

fsrtr.fsrtr_buyingrate AS fsrtr_buyingrate, --Minimum rate

fsrtr.fsrtr_buyingratemarkuptype AS fsrtr_buyingratemarkuptype,

markuptype.fsgec_description AS buyingmarkuptypedescription,

fsrtr.fsrtr_buyingratemarkupvalue AS fsrtr_buyingratemarkupvalue,

fsrtr.fsrtr_sellingrate AS fsrtr_sellingrate,

fsrtr.fsrtr_buyingweight AS fsrtr_buyingweight, --per ton

fsrtr.fsrtr_buyingweightmarkuptype AS fsrtr_buyingweightmarkuptype,

markuptype_weight.fsgec_description AS buyingmarkuptypeweight_description,

fsrtr.fsrtr_buyingweightmarkupvalue AS fsrtr_buyingweightmarkupvalue,

fsrtr.fsrtr_sellingweight AS fsrtr_sellingweight,

fsrtr.fsrtr_buyingvolume AS fsrtr_buyingvolume, --per cpm

fsrtr.fsrtr_buyingvolumemarkuptype AS fsrtr_buyingvolumemarkuptype,

markuptype_volume.fsgec_description AS buyingmarkuptypevolume_description,

fsrtr.fsrtr_buyingvolumemarkupvalue AS fsrtr_buyingvolumemarkupvalue,

fsrtr.fsrtr_sellingvolume AS fsrtr_sellingvolume,

fsrtr.fscry_id AS fscry_id,

fscry.fscry_code AS fscry_code,

fscry.fscry_description AS fscry_description,

fsrtr.fsrtr_remarks AS fsrtr_remarks,

CASE WHEN fsrtt.fsloc_idplaceoforigin IS NOT NULL AND fsrtt.fsloc_idplaceofdestination IS NULL THEN (SELECT fschg.fschg_code AS fschg_code

FROM fschg_chargemaster fschg

WHERE fschg.fschg_code =fs_configuration.get_value_fnc (

p_fsofo_id => NULL,

p_fscon_referencename => 'TARIFFMANAGEMENT',

p_fscnp_parametercode => 'RAIL_PRE',

p_fscon_keyValue1 => NULL,

p_fscon_keyValue2 => NULL,

p_fscon_keyValue3 => NULL ))

WHEN fsrtt.fsloc_idplaceoforigin IS NULL AND fsrtt.fsloc_idplaceofdestination IS NOT NULL THEN (SELECT fschg.fschg_code AS fschg_code

FROM fschg_chargemaster fschg

WHERE fschg.fschg_code =fs_configuration.get_value_fnc (

p_fsofo_id => NULL,

p_fscon_referencename => 'TARIFFMANAGEMENT',

p_fscnp_parametercode => 'RAIL_ONC',

p_fscon_keyValue1 => NULL,

p_fscon_keyValue2 => NULL,

p_fscon_keyValue3 => NULL ))

WHEN fsrtt.fsloc_idplaceoforigin IS NOT NULL AND fsrtt.fsloc_idplaceofdestination IS NOT NULL THEN (SELECT listagg (DISTINCT fschg.fschg_code,',' ON overflow TRUNCATE WITH COUNT)

within GROUP (ORDER BY fschg.fschg_id)

FROM fschg_chargemaster fschg

WHERE fschg.fschg_code IN (fs_configuration.get_value_fnc (

p_fsofo_id => NULL,

p_fscon_referencename => 'TARIFFMANAGEMENT',

p_fscnp_parametercode => 'RAIL_ONC',

p_fscon_keyValue1 => NULL,

p_fscon_keyValue2 => NULL,

p_fscon_keyValue3 => NULL ),

fs_configuration.get_value_fnc (

p_fsofo_id => NULL,

p_fscon_referencename => 'TARIFFMANAGEMENT',

p_fscnp_parametercode => 'RAIL_PRE',

p_fscon_keyValue1 => NULL,

p_fscon_keyValue2 => NULL,

p_fscon_keyValue3 => NULL ))

)





ELSE NULL END AS include,

CASE WHEN fsrtt.fsloc_idplaceoforigin IS NOT NULL AND fsrtt.fsloc_idplaceofdestination IS NULL THEN TO_CHAR((SELECT fschg.fschg_id AS fschg_id

FROM fschg_chargemaster fschg

WHERE fschg.fschg_code =fs_configuration.get_value_fnc (

p_fsofo_id => NULL,

p_fscon_referencename => 'TARIFFMANAGEMENT',

p_fscnp_parametercode => 'RAIL_PRE',

p_fscon_keyValue1 => NULL,

p_fscon_keyValue2 => NULL,

p_fscon_keyValue3 => NULL )))

WHEN fsrtt.fsloc_idplaceoforigin IS NULL AND fsrtt.fsloc_idplaceofdestination IS NOT NULL THEN TO_CHAR((SELECT fschg.fschg_id AS fschg_id

FROM fschg_chargemaster fschg

WHERE fschg.fschg_code =fs_configuration.get_value_fnc (

p_fsofo_id => NULL,

p_fscon_referencename => 'TARIFFMANAGEMENT',

p_fscnp_parametercode => 'RAIL_ONC',

p_fscon_keyValue1 => NULL,

p_fscon_keyValue2 => NULL,

p_fscon_keyValue3 => NULL )))

WHEN fsrtt.fsloc_idplaceoforigin IS NOT NULL AND fsrtt.fsloc_idplaceofdestination IS NOT NULL THEN TO_CHAR((SELECT listagg (DISTINCT fschg.fschg_id,',' ON overflow TRUNCATE WITH COUNT)

within GROUP (ORDER BY fschg.fschg_id)

FROM fschg_chargemaster fschg

WHERE fschg.fschg_code IN (fs_configuration.get_value_fnc (

p_fsofo_id => NULL,

p_fscon_referencename => 'TARIFFMANAGEMENT',

p_fscnp_parametercode => 'RAIL_ONC',

p_fscon_keyValue1 => NULL,

p_fscon_keyValue2 => NULL,

p_fscon_keyValue3 => NULL ),

fs_configuration.get_value_fnc (

p_fsofo_id => NULL,

p_fscon_referencename => 'TARIFFMANAGEMENT',

p_fscnp_parametercode => 'RAIL_PRE',

p_fscon_keyValue1 => NULL,

p_fscon_keyValue2 => NULL,

p_fscon_keyValue3 => NULL )))

)





ELSE NULL END AS includechargeid,

'T' AS Charge_Type,

fsrtt.fsloc_idplaceoforigin AS fsloc_idplaceoforigin,

fsrtt.fsrat_idfromstation AS fsrat_idfromstation,

fsrtt.fsrat_idtostation AS fsrat_idtostation,

fsrtt.fsloc_idplaceofdestination AS fsloc_idplaceofdestination

FROM FSRTH_RailTariffHeader fsrth

JOIN FSRTT_RailTariffTransportStation fsrtt ON (fsrtt.fsrth_id=fsrth.fsrth_id)

JOIN FSRTR_RailTariffRate fsrtr ON (fsrtr.fsrtt_id=fsrtt.fsrtt_id)

JOIN fschg_chargemaster fschg ON (fschg.fschg_id=fsrtr.fschg_id)

JOIN fscmg_commoditygroup fscmg ON (fscmg.fscmg_id=fsrtr.fscmg_id)

JOIN fscry_currency fscry ON (fscry.fscry_id=fsrtr.fscry_id)

JOIN FSGEC_GeneralCode markuptype ON (markuptype.fsgec_referencename='MARKUPTYPE' AND markuptype.fsgec_value=fsrtr.fsrtr_buyingratemarkuptype )

LEFT JOIN FSGEC_GeneralCode markuptype_weight ON (markuptype_weight.fsgec_referencename='MARKUPTYPE' AND markuptype_weight.fsgec_value=fsrtr.fsrtr_buyingweightmarkuptype )

LEFT JOIN FSGEC_GeneralCode markuptype_volume ON (markuptype_volume.fsgec_referencename='MARKUPTYPE' AND markuptype_volume.fsgec_value=fsrtr.fsrtr_buyingvolumemarkuptype )

JOIN route_details ON (NVL(fsrtt.fsloc_idplaceoforigin,0)=NVL(route_details.originid,0) AND fsrtt.fsrat_idfromstation=route_details.FromStationid

AND fsrtt.fsrat_idtostation=route_details.ToStationid AND NVL(fsrtt.fsloc_idplaceofdestination,0)=NVL(route_details.Destinationid,0))

WHERE fsrth.fsrth_shipmenttype = p_shipmenttype

AND fschg.fschg_type='F'

AND (p_carrier IS NULL OR (CASE WHEN fsrth.fsrth_shipmenttype = 'F' THEN fsrth.fsbue_idline ELSE fsrth.fsofo_idsalesoffice END) IN (SELECT REGEXP_SUBSTR (p_carrier, '[^,]+', 1,LEVEL) AS carrier FROM FSDUL_DUAL

CONNECT BY REGEXP_SUBSTR (p_carrier,'[^,]+', 1, LEVEL) IS NOT NULL))

AND (route_details.CommodityGroup IS NULL OR fsrtr.fscmg_id IN (SELECT REGEXP_SUBSTR (route_details.CommodityGroup, '[^,]+', 1,LEVEL) AS CommodityGroup FROM FSDUL_DUAL

CONNECT BY REGEXP_SUBSTR (route_details.CommodityGroup,'[^,]+', 1, LEVEL) IS NOT NULL))

AND (route_details.ContainerType IS NULL OR fsrtr.fseqt_sizetype IN (SELECT REGEXP_SUBSTR (route_details.ContainerType, '[^,]+', 1,LEVEL) AS ContainerType FROM FSDUL_DUAL

CONNECT BY REGEXP_SUBSTR (route_details.ContainerType,'[^,]+', 1, LEVEL) IS NOT NULL))

AND (p_customer_id IS NULL OR EXISTS (SELECT 'X'

FROM FSRTB_RailTariffCustomer fsrtb

WHERE fsrtb.fsrth_id=fsrth.fsrth_id

AND fsrtb.fsbue_idcustomer= p_customer_id))

AND (fsrth.fsrth_referencenumber,fsrth.fsrth_versionnumber) IN (SELECT fsrth.fsrth_referencenumber,MAX(fsrth.fsrth_versionnumber)

FROM fsrth_railtariffheader fsrth

GROUP BY fsrth.fsrth_referencenumber)





UNION ALL

SELECT fsrsr.fsrsr_id AS fsrtr_id,

fsrsr.fsbue_idcarrier AS fsbue_carrier,

NULL AS fsrth_referencenumber,

fsrsr.fschg_id AS fschg_id,

fschg.fschg_code AS fschg_code,

fschg.fschg_description AS fschg_description,

fsrsr.fseqt_sizetype AS fseqt_sizetype,

fsrsr.fscmg_id AS fscmg_id,

fscmg.fscmg_code AS fscmg_code,

fscmg.fscmg_description AS fscmg_description,

NULL AS fsrtr_buyingrate,

fsrsr.fsrsr_calculationprocedure AS fsrtr_buyingratemarkuptype,

markuptype.fsgec_description AS buyingmarkuptypedescription,

fsrsr.fsrsr_value AS fsrtr_buyingratemarkupvalue,

fsrsr.fsrsr_rate AS fsrtr_sellingrate,

NULL AS fsrtr_buyingweight,

NULL AS fsrtr_buyingweightmarkuptype,

NULL AS buyingmarkuptypeweight_description,

NULL AS fsrtr_buyingweightmarkupvalue,

fsrsr.fsrsr_perton AS fsrtr_sellingweight,

NULL AS fsrtr_buyingvolume,

NULL AS fsrtr_buyingvolumemarkuptype,

NULL AS buyingmarkuptypevolume_description,

NULL AS fsrtr_buyingvolumemarkupvalue,

fsrsr.fsrsr_percbm AS fsrtr_sellingvolume,

fsrsr.fscry_id AS fscry_id,

fscry.fscry_code AS fscry_code,

fscry.fscry_description AS fscry_description,

fsrsr.fsrsr_remarks AS fsrtr_remarks,

NULL AS include,

NULL AS includechargeid,

'S' AS Charge_Type,

fsrst.fsloc_idplaceoforigin AS fsloc_idplaceoforigin,

fsrst.fsrat_idfromstation AS fsrat_idfromstation,

fsrst.fsrat_idtostation AS fsrat_idtostation,

fsrst.fsloc_idplaceofdestination AS fsloc_idplaceofdestination

FROM FSRST_Railsurchargetransportstation fsrst

JOIN FSRSR_Railsurchargerate fsrsr ON (fsrsr.fsrst_id=fsrst.fsrst_id)

JOIN fschg_chargemaster fschg ON (fschg.fschg_id=fsrsr.fschg_id)

JOIN fscmg_commoditygroup fscmg ON (fscmg.fscmg_id=fsrsr.fscmg_id)

JOIN fscry_currency fscry ON (fscry.fscry_id=fsrsr.fscry_id)

JOIN FSGEC_GeneralCode markuptype ON (markuptype.fsgec_referencename='MARKUPTYPE' AND markuptype.fsgec_value=fsrsr.fsrsr_calculationprocedure )

JOIN route_details ON (NVL(fsrst.fsloc_idplaceoforigin,0)=NVL(route_details.originid,0) AND fsrst.fsrat_idfromstation=route_details.FromStationid

AND fsrst.fsrat_idtostation=route_details.ToStationid AND NVL(fsrst.fsloc_idplaceofdestination,0)=NVL(route_details.Destinationid,0))

WHERE fsrsr.fsrsr_shipmenttype = p_shipmenttype

AND fschg.fschg_type='S'

AND (p_carrier IS NULL OR fsrsr.fsbue_idcarrier IN (SELECT REGEXP_SUBSTR (p_carrier, '[^,]+', 1,LEVEL) AS carrier FROM FSDUL_DUAL

CONNECT BY REGEXP_SUBSTR (p_carrier,'[^,]+', 1, LEVEL) IS NOT NULL))

AND (route_details.CommodityGroup IS NULL OR fsrsr.fscmg_id IN (SELECT REGEXP_SUBSTR (route_details.CommodityGroup, '[^,]+', 1,LEVEL) AS CommodityGroup FROM FSDUL_DUAL

CONNECT BY REGEXP_SUBSTR (route_details.CommodityGroup,'[^,]+', 1, LEVEL) IS NOT NULL))

AND (route_details.ContainerType IS NULL OR fsrsr.fseqt_sizetype IN (SELECT REGEXP_SUBSTR (route_details.ContainerType, '[^,]+', 1,LEVEL) AS ContainerType FROM FSDUL_DUAL

CONNECT BY REGEXP_SUBSTR (route_details.ContainerType,'[^,]+', 1, LEVEL) IS NOT NULL)) ;







p_issuccess := fs_constants.k_success;



OPEN p_message FOR

SELECT fs_constants.k_success_msgcode AS msg_code,

NULL AS msg_value

FROM dual;



EXCEPTION

WHEN OTHERS

THEN

p_issuccess := fs_constants.k_failure;

fs_exception.raise_exception;



END Get_rateretrive_Prc;



PROCEDURE Get_localcharges_Prc ( --SP to fetch local charges details

p_fsofo_id IN FSRQH_RailQuotationHeader.fsofo_id%TYPE,

p_customer_id IN FSRQH_RailQuotationHeader.fsrqh_id%TYPE,

p_exportimport IN FSRTH_RailTariffHeader.fsrth_referencenumber%TYPE,

p_shipmenttype IN FSRTH_RailTariffHeader.fsrth_referencenumber%TYPE,

p_route IN fsdcl_documentationchangelog.fsdcl_content%TYPE,

p_rate_details OUT FS_RailQuotation.t_list,

p_issuccess OUT FSRTH_RailTariffHeader.fsrth_referencenumber%TYPE,

p_message OUT FS_RailQuotation.t_list)



AS



BEGIN



OPEN p_rate_details FOR

WITH route_details AS

(SELECT jt.originid,jt.Destinationid,jt.FromStationid,jt.ToStationid,jt.Sequence,

LISTAGG(jt.ContainerType, ',') WITHIN GROUP (ORDER BY 1) ContainerType,

LISTAGG(jt.CommodityGroup, ',') WITHIN GROUP (ORDER BY 1) CommodityGroup

FROM

JSON_TABLE(REPLACE(p_route,'\',''),'$[*]'

COLUMNS ( originid VARCHAR2(100) PATH '$.Orgin',

Destinationid VARCHAR2(100) PATH '$.Destination',

FromStationid VARCHAR2(100) PATH '$.From',

ToStationid VARCHAR2(100) PATH '$.To' ,

Sequence NUMBER PATH '$.Sequence',

NESTED PATH '$.ContainerType[*]'

COLUMNS (ContainerType VARCHAR2(100) PATH '$.Code'),

NESTED PATH '$.CommodityGroup[*]'

COLUMNS (CommodityGroup VARCHAR2(100) PATH '$.Id')

)

) jt

GROUP BY jt.originid,jt.Destinationid,jt.FromStationid,jt.ToStationid,jt.Sequence)

SELECT fslrr.fslrr_id AS fsrtr_id,

NULL AS fsbue_carrier,

fslrr.fschg_id AS fschg_id,

fschg.fschg_code AS fschg_code,

fschg.fschg_description AS fschg_description,

fslrr.fseqt_sizetype AS fseqt_sizetype,

fslrr.fscmg_id AS fscmg_id,

fscmg.fscmg_code AS fscmg_code,

fscmg.fscmg_description AS fscmg_description,

fslrr.fscry_id AS fscry_id,

fscry.fscry_code AS fscry_code,

fscry.fscry_description AS fscry_description,

fslrr.fslrr_amount AS amount,

fslrr.fslrr_isexportimport AS fslrr_isexportimport,

gec_export.fsgec_description AS gec_exportdescription,

fslrs.fsrat_idfrom AS fsrat_idfromstation,

fslrs.fsrat_idto AS fsrat_idtostation ,

route_details.sequence AS route_sequence

FROM FSLRS_Localchargerailstation fslrs

JOIN FSLRR_Localchargerailrate fslrr ON (fslrr.fslrr_uicardreference=fslrs.fslrs_uicardreference and fslrr.fslrr_uigroupingreference=fslrs.fslrs_uigroupingreference)

JOIN FSCHG_ChargeMaster fschg ON (fschg.fschg_id=fslrr.fschg_id)

JOIN FSCMG_Commoditygroup fscmg ON ( fscmg.fscmg_id=fslrr.fscmg_id)

JOIN FSCRY_Currency fscry ON (fscry.fscry_id=fslrr.fscry_id)

JOIN FSGEC_GeneralCode gec_export ON (gec_export.fsgec_referencename='BOOKINGTYPE' AND gec_export.fsgec_value=fslrr.fslrr_isexportimport)

JOIN route_details ON ( fslrs.fsrat_idfrom=route_details.FromStationid AND fslrs.fsrat_idto=route_details.ToStationid)

WHERE fslrs.fsbue_idcustomer= p_customer_id

AND fslrr.fslrr_isexportimport = p_exportimport

AND fslrr.fslrr_chargetype = p_shipmenttype

AND fschg.fschg_type IN ('LI','LE')

AND route_details.CommodityGroup IS NULL OR fslrr.fscmg_id IN (SELECT REGEXP_SUBSTR (route_details.CommodityGroup, '[^,]+', 1,LEVEL) AS CommodityGroup FROM FSDUL_DUAL

CONNECT BY REGEXP_SUBSTR (route_details.CommodityGroup,'[^,]+', 1, LEVEL) IS NOT NULL)

AND route_details.ContainerType IS NULL OR fslrr.fseqt_sizetype IN (SELECT REGEXP_SUBSTR (route_details.ContainerType, '[^,]+', 1,LEVEL) AS ContainerType FROM FSDUL_DUAL

CONNECT BY REGEXP_SUBSTR (route_details.ContainerType,'[^,]+', 1, LEVEL) IS NOT NULL);



p_issuccess := fs_constants.k_success;



OPEN p_message FOR

SELECT fs_constants.k_success_msgcode AS msg_code,

NULL AS msg_value

FROM dual;



EXCEPTION

WHEN OTHERS

THEN

p_issuccess := fs_constants.k_failure;

fs_exception.raise_exception;



END Get_localcharges_Prc;







/*PROCEDURE Get_ratemanagement_Prc ( --charges details

p_fsofo_id IN FSRQH_RailQuotationHeader.fsofo_id%TYPE,

p_fsprt_fromstation IN FSRQH_RailQuotationHeader.fsrqh_id%TYPE,

p_fsprt_tostation IN FSRQH_RailQuotationHeader.fsrqh_id%TYPE,

p_fsloc_origin IN FSRQH_RailQuotationHeader.fsrqh_id%TYPE,

p_fsloc_destination IN FSRQH_RailQuotationHeader.fsrqh_id%TYPE,

p_carrier_details OUT FS_RailQuotation.t_list,

p_freight_details OUT FS_RailQuotation.t_list,

p_surcharge_details OUT FS_RailQuotation.t_list,

p_localcharge_details OUT FS_RailQuotation.t_list,

p_issuccess OUT FSRTH_RailTariffHeader.fsrth_referencenumber%TYPE,

p_message OUT FS_RailQuotation.t_list)

AS

BEGIN



OPEN p_carrier_details FOR

SELECT fsrtt.fsrtt_id AS fsrtt_id,

fsrth.fsbue_idline AS fsbue_idline,

fsbue.fsbue_name AS carrier_name,

fsrtt.fsloc_idplaceoforigin AS fsloc_idplaceoforigin,

fsloc_orginplace.fsloc_name AS placeoforigin,

fsrtt.fsrat_idfromstation AS fsrat_idfromstation,

fromstation.fsrat_name AS fromstation,

fsrtt.fsrat_idtostation AS fsrat_idtostation,

tostation.fsrat_name AS tostation,

fsrtt.fsloc_idplaceofdestination AS fsloc_idplaceofdestination,

fsloc_destinationplace.fsloc_name AS destinationplace,

fsrtt.fsrtt_travellingtime AS fsrtt_travellingtime,

(SELECT listagg (DISTINCT fsrtf.fsrtf_dayoffrequency,', ' ON overflow TRUNCATE WITH COUNT)

within GROUP (ORDER BY fsrtf.fsrtt_id)

FROM FSRTF_RailTariffTransportFrequency fsrtf

WHERE fsrtf.fsrtt_id=fsrtt.fsrtt_id

) AS fsrtf_dayoffrequency

FROM FSRTH_RailTariffHeader fsrth

JOIN fsbue_businessentity fsbue ON (fsbue.fsbue_id=fsrth.fsbue_idline)

JOIN FSRTT_RailTariffTransportStation fsrtt ON (fsrtt.fsrth_id=fsrth.fsrth_id)

JOIN Fsloc_Location fsloc_orginplace ON (fsloc_orginplace.fsloc_id=fsrtt.fsloc_idplaceoforigin)

JOIN Fsloc_Location fsloc_destinationplace ON (fsloc_destinationplace.fsloc_id=fsrtt.fsloc_idplaceofdestination)

JOIN FSRAT_Railwaystationmaster fromstation ON (fromstation.fsrat_id=fsrtt.fsrat_idfromstation)

JOIN FSRAT_Railwaystationmaster tostation ON (tostation.fsrat_id=fsrtt.fsrat_idtostation)

WHERE fsrtt.fsloc_idplaceoforigin = p_fsloc_origin

AND fsrtt.fsrat_idfromstation = p_fsprt_fromstation

AND fsrtt.fsrat_idtostation = p_fsprt_tostation

AND fsrtt.fsloc_idplaceofdestination= p_fsloc_destination;





OPEN p_freight_details FOR

SELECT fsrtr.fsrtr_id AS fsrtr_id,

fsrtr.fschg_id AS fschg_id,

fschg.fschg_code AS fschg_code,

fschg.fschg_description AS fschg_description,

fsrtr.fseqt_sizetype AS fseqt_sizetype,

fsrtr.fscmg_id AS fscmg_id,

fscmg.fscmg_code AS fscmg_code,

fscmg.fscmg_description AS fscmg_description,

fsrtr.fsrtr_buyingrate AS fsrtr_buyingrate, --Minimum rate

fsrtr.fsrtr_buyingratemarkuptype AS fsrtr_buyingratemarkuptype,

markuptype.fsgec_description AS buyingmarkuptypedescription,

fsrtr.fsrtr_buyingratemarkupvalue AS fsrtr_buyingratemarkupvalue,

fsrtr.fsrtr_sellingrate AS fsrtr_sellingrate,

fsrtr.fsrtr_buyingweight AS fsrtr_buyingweight, --per ton

fsrtr.fsrtr_buyingweightmarkuptype AS fsrtr_buyingweightmarkuptype,

markuptype_weight.fsgec_description AS buyingmarkuptypeweight_description,

fsrtr.fsrtr_buyingweightmarkupvalue AS fsrtr_buyingweightmarkupvalue,

fsrtr.fsrtr_sellingweight AS fsrtr_sellingweight,

fsrtr.fsrtr_buyingvolume AS fsrtr_buyingvolume, --per cpm

fsrtr.fsrtr_buyingvolumemarkuptype AS fsrtr_buyingvolumemarkuptype,

markuptype_volume.fsgec_description AS buyingmarkuptypevolume_description,

fsrtr.fsrtr_buyingvolumemarkupvalue AS fsrtr_buyingvolumemarkupvalue,

fsrtr.fsrtr_sellingvolume AS fsrtr_sellingvolume,

fsrtr.fscry_id AS fscry_id,

fscry.fscry_code AS fscry_code,

fscry.fscry_description AS fscry_description,

fsrtr.fsrtr_remarks AS fsrtr_remarks,

NULL AS include

FROM FSRTT_RailTariffTransportStation fsrtt

JOIN FSRTR_RailTariffRate fsrtr ON (fsrtr.fsrtt_id=fsrtt.fsrtt_id)

JOIN fschg_chargemaster fschg ON (fschg.fschg_id=fsrtr.fschg_id)

JOIN fscmg_commoditygroup fscmg ON (fscmg.fscmg_id=fsrtr.fscmg_id)

JOIN fscry_currency fscry ON (fscry.fscry_id=fsrtr.fscry_id)

JOIN FSGEC_GeneralCode markuptype ON (markuptype.fsgec_referencename='MARKUPTYPE' AND markuptype.fsgec_value=fsrtr.fsrtr_buyingratemarkuptype )

LEFT JOIN FSGEC_GeneralCode markuptype_weight ON (markuptype_weight.fsgec_referencename='MARKUPTYPE' AND markuptype_weight.fsgec_value=fsrtr.fsrtr_buyingweightmarkuptype )

LEFT JOIN FSGEC_GeneralCode markuptype_volume ON (markuptype_volume.fsgec_referencename='MARKUPTYPE' AND markuptype_volume.fsgec_value=fsrtr.fsrtr_buyingvolumemarkuptype )

WHERE fsrtt.fsloc_idplaceoforigin = p_fsloc_origin

AND fsrtt.fsrat_idfromstation = p_fsprt_fromstation

AND fsrtt.fsrat_idtostation = p_fsprt_tostation

AND fsrtt.fsloc_idplaceofdestination= p_fsloc_destination;



OPEN p_surcharge_details FOR

SELECT fsrsr.fsrsr_id AS fsrtr_id,

fsrsr.fschg_id AS fschg_id,

fschg.fschg_code AS fschg_code,

fschg.fschg_description AS fschg_description,

fsrsr.fseqt_sizetype AS fseqt_sizetype,

fsrsr.fscmg_id AS fscmg_id,

fscmg.fscmg_code AS fscmg_code,

fscmg.fscmg_description AS fscmg_description,

fsrsr.fsrsr_value AS surcharge_value,

fsrsr.fsrsr_calculationprocedure AS calc_proc,

markuptype.fsgec_description AS calc_procdescription,

fsrsr.fsrsr_rate AS amount, --for lcl shipment

fsrsr.fsrsr_perton AS fsrtr_sellingweight,

fsrsr.fsrsr_percbm AS fsrtr_sellingvolume,

fsrsr.fscry_id AS fscry_id,

fscry.fscry_code AS fscry_code,

fscry.fscry_description AS fscry_description,

fsrsr.fsrsr_remarks AS fsrtr_remarks

FROM FSRST_Railsurchargetransportstation fsrst

JOIN FSRSR_Railsurchargerate fsrsr ON (fsrsr.fsrst_id=fsrst.fsrst_id)

JOIN fschg_chargemaster fschg ON (fschg.fschg_id=fsrsr.fschg_id)

JOIN fscmg_commoditygroup fscmg ON (fscmg.fscmg_id=fsrsr.fscmg_id)

JOIN fscry_currency fscry ON (fscry.fscry_id=fsrsr.fscry_id)

JOIN FSGEC_GeneralCode markuptype ON (markuptype.fsgec_referencename='MARKUPTYPE' AND markuptype.fsgec_value=fsrsr.fsrsr_calculationprocedure )

WHERE fsrst.fsloc_idplaceoforigin = p_fsloc_origin

AND fsrst.fsrat_idfromstation = p_fsprt_fromstation

AND fsrst.fsrat_idtostation = p_fsprt_tostation

AND fsrst.fsloc_idplaceofdestination= p_fsloc_destination;





OPEN p_localcharge_details FOR

SELECT fslrr.fslrr_id AS fsrqr_id,

fslrr.fschg_id AS fschg_id,

fschg.fschg_code AS fschg_code,

fschg.fschg_description AS fschg_description,

fslrr.fslrr_basis AS charge_basis,

gec_basis.fsgec_description AS fslrr_basisdescription,

fslrr.fseqt_sizetype AS fseqt_sizetype,

fslrr.fscmg_id AS fscmg_id,

fscmg.fscmg_code AS fscmg_code,

fscmg.fscmg_description AS fscmg_description,

fslrr.fslrr_amount AS amount,

fslrr.fscry_id AS fscry_id,

fscry.fscry_code AS fscry_code,

fscry.fscry_description AS fscry_description,

fslrr.fslrr_isexportimport AS fslrr_isexportimport,

gec_export.fsgec_description AS gec_exportdescription

FROM FSLRS_Localchargerailstation fslrs

JOIN FSLRR_Localchargerailrate fslrr ON (fslrr.fslrs_id=fslrs.fslrs_id)

JOIN FSCHG_ChargeMaster fschg ON (fschg.fschg_id=fslrr.fschg_id)

JOIN FSCMG_Commoditygroup fscmg ON ( fscmg.fscmg_id=fslrr.fscmg_id)

JOIN FSCRY_Currency fscry ON (fscry.fscry_id=fslrr.fscry_id)

JOIN FSGEC_GeneralCode gec_basis ON (gec_basis.fsgec_referencename='BASIS' AND gec_basis.fsgec_value=fslrr.fslrr_basis )

JOIN FSGEC_GeneralCode gec_export ON (gec_export.fsgec_referencename='BOOKINGTYPE' AND gec_export.fsgec_value=fslrr.fslrr_isexportimport)

WHERE fslrs.fsrat_idfrom = p_fsprt_fromstation

AND fslrs.fsrat_idto = p_fsprt_tostation;



p_issuccess := fs_constants.k_success;



OPEN p_message FOR

SELECT fs_constants.k_success_msgcode AS msg_code,

NULL AS msg_value

FROM dual;



EXCEPTION

WHEN OTHERS

THEN

p_issuccess := fs_constants.k_failure;

fs_exception.raise_exception;



END Get_ratemanagement_Prc;







PROCEDURE Get_header_Prc ( --header edit

p_fsrqh_id IN FSRQH_RailQuotationHeader.fsrqh_id%TYPE,

p_fsofo_id IN FSRQH_RailQuotationHeader.fsofo_id%TYPE,

p_rate_details OUT FS_RailQuotation.t_list,

p_transport_details OUT FS_RailQuotation.t_list,

p_issuccess OUT FSRTH_RailTariffHeader.fsrth_referencenumber %TYPE,

p_message OUT FS_RailQuotation.t_list)

AS

BEGIN



OPEN p_rate_details FOR

SELECT fsrqh.fsrqh_id AS fsrqh_id,

fsrqh.fsbue_idcustomer AS fsbue_idcustomer,

fsbue.fsbue_name AS customer_name,

fsrqh.fsrqh_quotationtype AS fsrqh_quotationtype,

fsgec_quotationtype.fsgec_description AS quotationtype_description,

fsrqh.fsrqh_shipmenttype AS fsrqh_shipmenttype,

fsgec_shipmenttype.fsgec_description AS shipmenttype_description,

fsrqh.fsrqh_incoterms AS incoterms,

fsgec_incoterms.fsgec_description AS fsgec_incotermsdescription,

fsrqh.fsrqh_isexportimport AS export_import,

fsgec_export.fsgec_description AS fsgec_export_description,

fsrqh.fscry_idquotation AS fscry_idquotation,

fscry.fscry_code AS quotation_currency,

fscry.fscry_description AS quotation_currency_description,

fsrqh.fsrqh_referencenumber AS fsrqh_referencenumber,

fsrqh.fsrqh_validfrom AS fsrqh_validfrom,

fsrqh.fsrqh_validto AS fsrqh_validto,

fsrqh.fsrqh_status AS fsrqh_status,

fsgec_status.fsgec_description AS fsgec_statusdescription

FROM FSRQH_RailQuotationHeader fsrqh

JOIN fsbue_businessentity fsbue ON (fsbue.fsbue_id=fsrqh.fsbue_idcustomer)

JOIN fsgec_generalcode fsgec_quotationtype ON (fsgec_quotationtype.fsgec_referencename = 'QUOTATIONTYPE' AND fsgec_quotationtype.fsgec_value = fsrqh.fsrqh_quotationtype )

JOIN fsgec_generalcode fsgec_shipmenttype ON (fsgec_shipmenttype.fsgec_referencename = 'SHIPMENTTYPE' AND fsgec_shipmenttype.fsgec_value = fsrqh.fsrqh_shipmenttype )

JOIN fsgec_generalcode fsgec_status ON (fsgec_status.fsgec_referencename = 'QUOTATIONSTATUS' AND fsgec_status.fsgec_value = fsrqh.fsrqh_status )

JOIN fsgec_generalcode fsgec_incoterms ON (fsgec_incoterms.fsgec_referencename = 'BOOKINGINCOTERMS' AND fsgec_incoterms.fsgec_value =fsrqh.fsrqh_incoterms)

JOIN fsgec_generalcode fsgec_export ON (fsgec_export.fsgec_referencename = 'BOOKINGTYPE' AND fsgec_export.fsgec_value =fsrqh.fsrqh_isexportimport)

JOIN FSCRY_Currency fscry ON (fscry.fscry_id=fsrqh.fscry_idquotation)

WHERE fsrqh.fsrqh_id=p_fsrqh_id;



OPEN p_transport_details FOR

SELECT fsrqh.fsrqh_id AS fsrqh_id,

fsrqc.fsbue_idcarrier AS fsbue_idcarrier,

fsbue_carrier.fsbue_name AS carrier_name,

fsrqt.fsloc_idplaceoforigin AS fsloc_idplaceoforigin,

fsloc_orignplace.fsloc_name AS placeoforigin,

fsrqt.fsrat_idfromstation AS fsrat_idfromstation,

fromstation.fsrat_name AS from_station,

fsrqt.fsrat_idtostation AS fsrat_idtostation,

tostation.fsrat_name AS to_station,

fsrqt.fsloc_idplaceofdestination AS fsloc_idplaceofdestination,

fsloc_destinationplace.fsloc_name AS placeofdestination,

FSRQT_Travellingtime AS transit_days,

FSRQT_DayofFrequency AS frequency

FROM FSRQH_RailQuotationHeader fsrqh

JOIN FSRQC_RailQuotationCarrier fsrqc ON (fsrqc.fsrqh_id=fsrqh.fsrqh_id)

jOIN FSBUE_Businessentity fsbue_carrier ON (fsbue_carrier.fsbue_id=fsrqc.fsbue_idcarrier)

JOIN FSRQT_RailQuotationTransportStation fsrqt ON (fsrqt.fsrqh_id=fsrqh.fsrqh_id)

JOIN FSRAT_Railwaystationmaster fromstation ON (fromstation.fsrat_id=fsrqt.fsrat_idfromstation)

JOIN FSRAT_Railwaystationmaster tostation ON (tostation.fsrat_id=fsrqt.fsrat_idtostation)

JOIN FSLOC_Location fsloc_orignplace ON (fsloc_orignplace.fsloc_id=fsrqt.fsloc_idplaceoforigin)

JOIN FSLOC_Location fsloc_destinationplace ON (fsloc_destinationplace.fsloc_id=fsrqt.fsloc_idplaceofdestination)

WHERE fsrqh.fsrqh_id=p_fsrqh_id;



p_issuccess := fs_constants.k_success;



OPEN p_message FOR

SELECT fs_constants.k_success_msgcode AS msg_code,

NULL AS msg_value

FROM dual;



EXCEPTION

WHEN OTHERS

THEN

p_issuccess := fs_constants.k_failure;

fs_exception.raise_exception;



END Get_header_Prc;*/

PROCEDURE Getlist_charges_Prc ( --charges details

p_fsrqt_id IN FSRQH_RailQuotationHeader.fsrqh_id%TYPE,

p_fsofo_id IN FSRQH_RailQuotationHeader.fsofo_id%TYPE,

p_route IN FSRQH_RailQuotationHeader.fsrqh_remarks%TYPE,

p_general_details OUT FS_RailQuotation.t_list,

p_header_details OUT FS_RailQuotation.t_list,

p_freight_details OUT FS_RailQuotation.t_list,

p_surcharge_details OUT FS_RailQuotation.t_list,

p_localcharge_details OUT FS_RailQuotation.t_list,

p_precarrige_details OUT FS_RailQuotation.t_list,

p_precarriegecharge_details OUT FS_RailQuotation.t_list,

p_oncarrige_details OUT FS_RailQuotation.t_list,

p_oncarriegecharge_details OUT FS_RailQuotation.t_list,

p_issuccess OUT FSRTH_RailTariffHeader.fsrth_referencenumber %TYPE,

p_message OUT FS_RailQuotation.t_list)

AS

BEGIN



OPEN p_general_details FOR

SELECT fsrqh.fsrqh_id AS fsrqh_id,

fsrqh.fsbue_idcustomer AS fsbue_idcustomer,

fsbue.fsbue_name AS customer_name,

fsrqh.fsrqh_quotationtype AS fsrqh_quotationtype,

fsgec_quotationtype.fsgec_description AS quotationtype_description,

quotationpartytype.fsgec_description AS quotationpartytype_description ,

fsrqh.fsrqh_quotationpartytype AS fsrqh_quotationpartytype,

fsrqh.fsrqh_incoterms AS incoterms,

fsgec_incoterms.fsgec_description AS fsgec_incotermsdescription,

fsrqh.fsrqh_isexportimport AS export_import,

fsgec_export.fsgec_description AS fsgec_export_description,

fsrqh.fsrqh_shipmenttype AS fsrqh_shipmenttype,

fsgec_shipmenttype.fsgec_description AS shipmenttype_description,

fsrqh.fsrqh_validfrom AS fsrqh_validfrom,

fsrqh.fsrqh_validto AS fsrqh_validto,

fsrqh.fscry_idquotation AS fscry_idquotation,

fscry.fscry_code AS quotation_currency,

fscry.fscry_description AS quotation_currency_description,

fsrqh.fsrqh_isshowcarrier AS fsrqh_isshowcarrier,

fsrqh.fsrqh_status AS fsrqh_status,

fsgec_status.fsgec_description AS fsgec_statusdescription,

fsrqh.fsrqh_referencenumber AS fsrqh_referencenumber,

fsrqh.fsofo_idpartner AS fsofo_idpartner,

fsofo_partner.fsofo_organizationname AS parter_name,

fsrqh.fsrqh_minimumconfirmedquantity AS fsrqh_minimumconfirmedquantity,

fsrqh.fsrqh_minimumconfirmedquantitytype AS fsrqh_minimumconfirmedquantitytype,

fsgec_mcqtype.fsgec_description AS mcqtype_description,

fsrqh.fsrqh_customerdetails AS fsrqh_customerdetails,

(SELECT listagg (DISTINCT fsrqc.fsbue_idcarrier,',' ON overflow TRUNCATE WITH COUNT)

within GROUP (ORDER BY fsrqc.fsrqc_id)

FROM FSRQC_RailQuotationCarrier fsrqc

WHERE fsrqc.fsrqh_id=fsrqh.fsrqh_id

) AS fsbue_idcarrier,

(SELECT listagg (DISTINCT fsbue.fsbue_name,',' ON overflow TRUNCATE WITH COUNT)

within GROUP (ORDER BY fsrqc.fsrqc_id)

FROM FSRQC_RailQuotationCarrier fsrqc

JOIN fsbue_businessentity fsbue ON(fsbue.fsbue_id=fsrqc.fsbue_idcarrier)

WHERE fsrqc.fsrqh_id=fsrqh.fsrqh_id

) AS carrier_name,

fsrqh.fsofo_idsalesoffice AS fsofo_idsalesoffice,

fsofo.fsofo_organizationname AS sales_office,

(SELECT listagg (DISTINCT fsrqd.fsdlm_id,',' ON overflow TRUNCATE WITH COUNT)

within GROUP (ORDER BY fsrqd.fsrqd_id)

FROM FSRQD_Railquotationdeal fsrqd

WHERE fsrqd.fsrqh_id=fsrqh.fsrqh_id

) AS deal,

(SELECT listagg (DISTINCT fsldm.fsdlm_referencenumber,',' ON overflow TRUNCATE WITH COUNT)

within GROUP (ORDER BY fsrqd.fsrqd_id)

FROM FSRQD_Railquotationdeal fsrqd

JOIN FSDLM_Dealmanagement fsldm ON (fsldm.fsdlm_id=fsrqd.fsdlm_id)

WHERE fsrqd.fsrqh_id=fsrqh.fsrqh_id

) AS deal_id,

fsrqh.fsrqh_weight AS fsrqh_weight,

fsrqh.fsmea_idweight AS fsmea_idweight,

fsmea.fsmea_isocode AS fsmea_isocode,

fsrqh.fsrqh_volume AS fsrqh_volume

FROM FSRQH_RailQuotationHeader fsrqh

LEFT JOIN fsbue_businessentity fsbue ON (fsbue.fsbue_id=fsrqh.fsbue_idcustomer)

LEFT JOIN fsmea_measurement fsmea ON (fsmea.fsmea_id=fsrqh.fsmea_idweight)

LEFT JOIN FSOFO_Officeorganization fsofo_partner ON (fsofo_partner.fsofo_id=fsrqh.fsofo_idpartner)

JOIN fsgec_generalcode fsgec_quotationtype ON (fsgec_quotationtype.fsgec_referencename = 'QUOTATIONTYPE' AND fsgec_quotationtype.fsgec_value = fsrqh.fsrqh_quotationtype )

JOIN fsgec_generalcode fsgec_shipmenttype ON (fsgec_shipmenttype.fsgec_referencename = 'SHIPMENTTYPE' AND fsgec_shipmenttype.fsgec_value = fsrqh.fsrqh_shipmenttype )

JOIN fsgec_generalcode fsgec_incoterms ON (fsgec_incoterms.fsgec_referencename = 'BOOKINGINCOTERMS' AND fsgec_incoterms.fsgec_value =fsrqh.fsrqh_incoterms)

JOIN fsgec_generalcode fsgec_export ON (fsgec_export.fsgec_referencename = 'BOOKINGTYPE' AND fsgec_export.fsgec_value =fsrqh.fsrqh_isexportimport)

LEFT JOIN fsgec_generalcode fsgec_mcqtype ON (fsgec_mcqtype.fsgec_referencename = 'QUOTATIONMCQ' AND fsgec_mcqtype.fsgec_value = fsrqh.fsrqh_minimumconfirmedquantitytype )

JOIN fsgec_generalcode quotationpartytype ON (quotationpartytype.fsgec_referencename='QUOTATIONPARTYTYPE' AND quotationpartytype.fsgec_value=fsrqh.fsrqh_quotationpartytype )

JOIN fsgec_generalcode fsgec_status ON (fsgec_status.fsgec_referencename = 'QUOTATIONSTATUS' AND fsgec_status.fsgec_value = fsrqh.fsrqh_status )

--JOIN FSRQC_RailQuotationCarrier fsrqc ON (fsrqc.fsrqh_id=fsrqh.fsrqh_id)

--LEFT JOIN FSBUE_Businessentity fsbue_carrier ON (fsbue_carrier.fsbue_id=fsrqc.fsbue_idcarrier)

JOIN FSCRY_Currency fscry ON (fscry.fscry_id=fsrqh.fscry_idquotation)

LEFT JOIN FSOFO_Officeorganization fsofo ON (fsofo.fsofo_id=fsrqh.fsofo_idsalesoffice)

WHERE fsrqh.fsrqh_id=p_fsrqt_id;



OPEN p_header_details FOR

SELECT DISTINCT fsrqh.fsrqh_id AS fsrqh_id,

fsrqt.fsrqt_id AS fsrqt_id,

fsrqr.fsbue_idcarrier AS fsbue_idcarrier,

fsrqr.fsofo_idsalesoffice AS fsofo_idsalesoffice,

fsbue_carrier.fsbue_name AS carrier_name,

fsofo.fsofo_organizationname AS fsofo_organizationname,

fsrqt.fsloc_idplaceoforigin AS fsloc_idplaceoforigin,

fsloc_orignplace.fsloc_name AS placeoforigin,

fsrqt.fsrat_idfromstation AS fsrat_idfromstation,

fromstation.fsrat_name AS from_station,

fsrqt.fsrat_idtostation AS fsrat_idtostation,

tostation.fsrat_name AS to_station,

fsrqt.fsloc_idplaceofdestination AS fsloc_idplaceofdestination,

fsloc_destinationplace.fsloc_name AS placeofdestination,

fsrqt.fsrqt_travellingtime AS fsrqt_travellingtime,

fsrqt.fsrqt_dayoffrequency AS fsrqt_dayoffrequency,

fsgec_frequency.fsgec_description AS fsrqt_frequencydescription,

fsrqh.fsrqh_isshowcarrier AS fsrqh_isshowcarrier

FROM FSRQH_RailQuotationHeader fsrqh

JOIN FSRQT_RailQuotationTransportStation fsrqt ON (fsrqt.fsrqh_id=fsrqh.fsrqh_id)

JOIN FSRQR_RailQuotationRate fsrqr ON (fsrqr.fsrqt_id=fsrqt.fsrqt_id)

LEFT JOIN FSBUE_Businessentity fsbue_carrier ON (fsbue_carrier.fsbue_id=fsrqr.fsbue_idcarrier)

LEFT JOIN FSOFO_Officeorganization fsofo ON (fsofo.fsofo_id=fsrqr.fsofo_idsalesoffice)

JOIN FSRAT_Railwaystationmaster fromstation ON (fromstation.fsrat_id=fsrqt.fsrat_idfromstation)

JOIN FSRAT_Railwaystationmaster tostation ON (tostation.fsrat_id=fsrqt.fsrat_idtostation)

LEFT JOIN fsgec_generalcode fsgec_frequency ON (fsgec_frequency.fsgec_referencename = 'FREQUENCY' AND fsgec_frequency.fsgec_value = fsrqt.fsrqt_dayoffrequency)

JOIN FSLOC_Location fsloc_orignplace ON (fsloc_orignplace.fsloc_id=fsrqt.fsloc_idplaceoforigin)

JOIN FSLOC_Location fsloc_destinationplace ON (fsloc_destinationplace.fsloc_id=fsrqt.fsloc_idplaceofdestination)

WHERE fsrqt.fsrqh_id=p_fsrqt_id;





OPEN p_freight_details FOR

SELECT fsrqr.fsrqr_id AS fsrqr_id,

fsrqt.fsrqt_id AS fsrqt_id,

fsrqr.fschg_id AS fschg_id,

fsrqr.fsbue_idcarrier AS fsbue_idcarrier,

fsrqr.fsofo_idsalesoffice AS fsofo_idsalesoffice,

fsbue.fsbue_name AS carriername,

fsrqr.fsofo_idsalesoffice AS organizationname,

fschg.fschg_code AS fschg_code,

fschg.fschg_description AS fschg_description,

fsrqr.fseqt_sizetype AS fseqt_sizetype,

fsrqr.fscmg_id AS fscmg_id,

fscmg.fscmg_code AS fscmg_code,

fscmg.fscmg_description AS fscmg_description,

fsrqr.fsrqr_Tariffrate AS fsrqr_Tariffrate,

fsrqr.fsrqr_ratemarkuptype AS fsrqr_ratemarkuptype,

markuptype.fsgec_description AS ratemarkuptype_description,

fsrqr.fsrqr_ratemarkupvalue AS fsrqr_ratemarkupvalue,

fsrqr.fsrqr_sellingrate AS fsrqr_sellingrate,

fsrqr.fsrqr_tariffvolume AS fsrqr_tariffvolume,

fsrqr.fsrqr_volumemarkuptype AS fsrqr_volumemarkuptype,

markuptype_volume.fsgec_description AS markuptype_volumedescription,

fsrqr.fsrqr_volumemarkupvalue AS fsrqr_volumemarkupvalue,

fsrqr.fsrqr_volumesellingrate AS fsrqr_volumesellingrate,

fsrqr.fsrqr_tariffweight AS fsrqr_tariffweight,

fsrqr.fsrqr_weightmarkuptype AS fsrqr_weightmarkuptype,

markuptype_weight.fsgec_description AS markuptype_weightdescription,

fsrqr.fsrqr_weightmarkupvalue AS fsrqr_weightmarkupvalue,

fsrqr.fsrqr_weightsellingrate AS fsrqr_weightsellingrate,

fsrqr.fscry_id AS fscry_id,

fscry.fscry_code AS fscry_code,

fscry.fscry_description AS fscry_description,

(SELECT listagg (DISTINCT fsrqi.fschg_id,', ' ON overflow TRUNCATE WITH COUNT)

within GROUP (ORDER BY fsrqi.fsrqi_id)

FROM FSRQI_RailQuotationInclusiveCharge fsrqi

WHERE fsrqi.fsrqr_id=fsrqr.fsrqr_id

) AS inclusive_chargeid,

(SELECT listagg (DISTINCT fschg.fschg_code,', ' ON overflow TRUNCATE WITH COUNT)

within GROUP (ORDER BY fsrqi.fsrqi_id)

FROM FSRQI_RailQuotationInclusiveCharge fsrqi

JOIN fschg_chargemaster fschg ON (fschg.fschg_id=fsrqi.fschg_id)

WHERE fsrqi.fsrqr_id=fsrqr.fsrqr_id

) AS inclusive_charge,

fsrqr.fsrqr_remarks AS fsrqr_remarks

FROM FSRQH_RailQuotationHeader fsrqh

JOIN FSRQT_RailQuotationTransportStation fsrqt ON (fsrqt.fsrqh_id=fsrqh.fsrqh_id)

JOIN FSRQR_RailQuotationRate fsrqr ON (fsrqr.fsrqt_id=fsrqt.fsrqt_id)

JOIN FSCHG_ChargeMaster fschg ON (fschg.fschg_id=fsrqr.fschg_id)

JOIN FSCMG_Commoditygroup fscmg ON ( fscmg.fscmg_id=fsrqr.fscmg_id)

JOIN FSCRY_Currency fscry ON (fscry.fscry_id=fsrqr.fscry_id)

LEFT JOIN fsbue_businessentity fsbue ON (fsbue.fsbue_id=fsrqr.fsbue_idcarrier)

LEFT JOIN fsofo_officeorganization fsofo ON (fsofo.fsofo_id=fsrqr.fsofo_idsalesoffice)

LEFT JOIN FSGEC_GeneralCode markuptype ON (markuptype.fsgec_referencename='MARKUPTYPE' AND markuptype.fsgec_value=fsrqr.fsrqr_ratemarkuptype )

LEFT JOIN FSGEC_GeneralCode markuptype_volume ON (markuptype_volume.fsgec_referencename='MARKUPTYPE' AND markuptype_volume.fsgec_value=fsrqr.fsrqr_volumemarkuptype )

LEFT JOIN FSGEC_GeneralCode markuptype_weight ON (markuptype_weight.fsgec_referencename='MARKUPTYPE' AND markuptype_weight.fsgec_value=fsrqr.fsrqr_weightmarkuptype )

WHERE fsrqt.fsrqh_id=p_fsrqt_id

AND fschg.fschg_type='F'

AND (p_route IS NULL OR fsrqt.fsrqt_id IN (SELECT REGEXP_SUBSTR (p_route, '[^,]+', 1,LEVEL) AS route_id FROM FSDUL_DUAL

CONNECT BY REGEXP_SUBSTR (p_route,'[^,]+', 1, LEVEL) IS NOT NULL));





OPEN p_surcharge_details FOR

SELECT fsrqr.fsrqr_id AS fsrqr_id,

fsrqt.fsrqt_id AS fsrqt_id,

fsrqr.fsbue_idcarrier AS fsbue_idcarrier,

fsrqr.fsofo_idsalesoffice AS fsofo_idsalesoffice,

fsbue.fsbue_name AS carriername,

fsrqr.fsofo_idsalesoffice AS organizationname,

fsrqr.fschg_id AS fschg_id,

fschg.fschg_code AS fschg_code,

fschg.fschg_description AS fschg_description,

fsrqr.fseqt_sizetype AS fseqt_sizetype,

fsrqr.fscmg_id AS fscmg_id,

fscmg.fscmg_code AS fscmg_code,

fscmg.fscmg_description AS fscmg_description,

fsrqr.fsrqr_ratemarkuptype AS Calc_proc,

markuptype.fsgec_description AS calc_procdescription,

fsrqr.fsrqr_Tariffrate AS fsrqr_value,

fsrqr.fsrqr_sellingrate AS amount,

fsrqr.fsrqr_volumesellingrate AS fsrqr_volumesellingrate,

fsrqr.fsrqr_weightsellingrate AS fsrqr_weightsellingrate,

fsrqr.fscry_id AS fscry_id,

fscry.fscry_code AS fscry_code,

fscry.fscry_description AS fscry_description,

fsrqr.fsrqr_remarks AS fsrqr_remarks

FROM FSRQH_RailQuotationHeader fsrqh

JOIN FSRQT_RailQuotationTransportStation fsrqt ON (fsrqt.fsrqh_id=fsrqh.fsrqh_id)

JOIN FSRQR_RailQuotationRate fsrqr ON (fsrqr.fsrqt_id=fsrqt.fsrqt_id)

JOIN FSCHG_ChargeMaster fschg ON (fschg.fschg_id=fsrqr.fschg_id)

JOIN FSCMG_Commoditygroup fscmg ON ( fscmg.fscmg_id=fsrqr.fscmg_id)

JOIN FSCRY_Currency fscry ON (fscry.fscry_id=fsrqr.fscry_id)

LEFT JOIN fsbue_businessentity fsbue ON (fsbue.fsbue_id=fsrqr.fsbue_idcarrier)

LEFT JOIN fsofo_officeorganization fsofo ON (fsofo.fsofo_id=fsrqr.fsofo_idsalesoffice)

JOIN FSGEC_GeneralCode markuptype ON (markuptype.fsgec_referencename='MARKUPTYPE' AND markuptype.fsgec_value=fsrqr.fsrqr_ratemarkuptype )

WHERE fsrqt.fsrqh_id=p_fsrqt_id

AND fschg.fschg_type='S'

AND (p_route IS NULL OR fsrqt.fsrqt_id IN (SELECT REGEXP_SUBSTR (p_route, '[^,]+', 1,LEVEL) AS route_id FROM FSDUL_DUAL

CONNECT BY REGEXP_SUBSTR (p_route,'[^,]+', 1, LEVEL) IS NOT NULL));

OPEN p_localcharge_details FOR

SELECT fsrqr.fsrqr_id AS fsrqr_id,

fsrqt.fsrqt_id AS fsrqt_id,

fsrqr.fschg_id AS fschg_id,

fschg.fschg_code AS fschg_code,

fschg.fschg_description AS fschg_description,

fsrqr.fsrqr_basis AS fsrqr_basis,

gec_basis.fsgec_description AS gec_basisdescription,

fsrqr.fseqt_sizetype AS fseqt_sizetype,

fsrqr.fscmg_id AS fscmg_id,

fscmg.fscmg_code AS fscmg_code,

fscmg.fscmg_description AS fscmg_description,

fsrqr.fsrqr_sellingrate AS amount,

fsrqr.fscry_id AS fscry_id,

fscry.fscry_code AS fscry_code,

fscry.fscry_description AS fscry_description,

fsrqr.fsrqr_isexportimport AS export_import,

export.fsgec_description AS export_importdescription,

fsrqr.fsrqr_remarks AS fsrqr_remarks,

fsrqr.fsrqr_volumesellingrate AS fsrqr_volumesellingrate,

fsrqr.fsrqr_weightsellingrate AS fsrqr_weightsellingrate

FROM FSRQT_RailQuotationTransportStation fsrqt

JOIN FSRQR_RailQuotationRate fsrqr ON (fsrqr.fsrqt_id=fsrqt.fsrqt_id)

JOIN FSCHG_ChargeMaster fschg ON (fschg.fschg_id=fsrqr.fschg_id)

JOIN FSCMG_Commoditygroup fscmg ON ( fscmg.fscmg_id=fsrqr.fscmg_id)

JOIN FSCRY_Currency fscry ON (fscry.fscry_id=fsrqr.fscry_id)

JOIN FSGEC_GeneralCode gec_basis ON (gec_basis.fsgec_referencename='BASIS' AND gec_basis.fsgec_value=fsrqr.fsrqr_basis )

JOIN FSGEC_GeneralCode export ON (export.fsgec_referencename='BOOKINGTYPE' AND export.fsgec_value=fsrqr.fsrqr_isexportimport )

WHERE fsrqt.fsrqh_id=p_fsrqt_id

AND fschg.fschg_type IN ('LI','LE')

AND (p_route IS NULL OR fsrqt.fsrqt_id IN (SELECT REGEXP_SUBSTR (p_route, '[^,]+', 1,LEVEL) AS route_id FROM FSDUL_DUAL

CONNECT BY REGEXP_SUBSTR (p_route,'[^,]+', 1, LEVEL) IS NOT NULL));



OPEN p_precarrige_details FOR

SELECT fsrqu.fsrqu_id AS fsrqu_id,

NVL(fsrqu.fsrqu_idparent,fsrqu.fsrqu_id) AS fsrqu_idparent,

fsrqu.fsloc_idfrom AS fsloc_idfrom,

fsloc_fromlocation.fsloc_name AS from_location,

fsrqu.fsrqu_zipcodefrom AS fsrqu_zipcodefrom,

fsrqu.fsloc_idto AS fsloc_idto,

fsloc_tolocation.fsloc_name AS to_location,

fsrqu.fsrqu_zipcodeto AS fsrqu_zipcodeto,

fsrqu.fsrqu_modeoftransport AS fsrqu_modeoftransport,

fsgec_transportmode.fsgec_description AS fsgec_modeoftransportdescription,

fsrqu.fsrqu_transit AS fsrqu_transit,

fsrqu.fsrqu_transittype AS fsrqu_transittype,

fsgec_transittype.fsgec_description AS fsgec_transittypedescription,

fsrqu.fsrqu_specialinstruction AS fsrqu_specialinstruction,

fsrqu.fsrqu_fromlocationaddress AS fsrqu_fromlocationaddress,

fsrqu.fsrqu_tolocationaddress AS fsrqu_tolocationaddress,

fsrqu.fsrqu_locationtype AS fsrqu_locationtype,

fsrqu.fsrqu_ismultimode AS fsrqu_ismultimode,

fsrqu.fsrqu_ismultistop AS fsrqu_ismultistop

FROM FSRQU_RailQuotationIntermodel fsrqu

JOIN FSLOC_Location fsloc_fromlocation ON (fsloc_fromlocation.fsloc_id=fsrqu.fsloc_idfrom)

JOIN FSLOC_Location fsloc_tolocation ON (fsloc_tolocation.fsloc_id=fsrqu.fsloc_idto)

JOIN fsgec_generalcode fsgec_transportmode ON (fsgec_transportmode.fsgec_referencename = 'MODEOFTRANSPORT' AND fsgec_transportmode.fsgec_value = fsrqu.fsrqu_modeoftransport)

JOIN fsgec_generalcode fsgec_transittype ON (fsgec_transittype.fsgec_referencename = 'TRANSITTYPE' AND fsgec_transittype.fsgec_value = fsrqu.fsrqu_transittype)

WHERE fsrqu.fsrqh_id=p_fsrqt_id

AND fsrqu.fsrqu_shipmenttype='P';





OPEN p_precarriegecharge_details FOR

SELECT fsrqr.fsrqr_id AS fsrqr_id,

fsrqr.fsrqu_id AS fsrqu_id,

fsrqr.fschg_id AS fschg_id,

fschg.fschg_code AS fschg_code,

fschg.fschg_description AS fschg_description,

fsrqr.fseqt_sizetype AS fseqt_sizetype,

fsrqr.fsrqr_weight AS fsrqr_weight,

fsrqr.fsmea_id AS fsmea_id,

fsmea.fsmea_code AS fsmea_code,

fsmea.fsmea_name AS fsmea_name,

fsrqr.fsrqr_volume AS fsrqr_volume,

fsrqr.fsrqr_Tariffrate AS fsrqr_Tariffrate,

fsrqr.fsrqr_ratemarkuptype AS fsrqr_ratemarkuptype,

markuptype.fsgec_description AS markuptype_description,

fsrqr.fsrqr_ratemarkupvalue AS fsrqr_ratemarkupvalue,

fsrqr.fsrqr_sellingrate AS fsrqr_sellingrate,

fsrqr.fscry_id AS fscry_id,

fscry.fscry_code AS fscry_code,

fscry.fscry_description AS fscry_description

FROM FSRQU_RailQuotationIntermodel fsrqu

JOIN FSRQR_RailQuotationRate fsrqr ON (fsrqr.fsrqu_id=fsrqu.fsrqu_id AND fsrqu.fsrqu_idparent IS NULL)

JOIN FSCHG_ChargeMaster fschg ON (fschg.fschg_id=fsrqr.fschg_id)

JOIN FSCRY_Currency fscry ON (fscry.fscry_id=fsrqr.fscry_id)

JOIN FSMEA_Measurement fsmea ON (fsmea.fsmea_id=fsrqr.fsmea_id)

JOIN FSGEC_GeneralCode markuptype ON (markuptype.fsgec_referencename='MARKUPTYPE' AND markuptype.fsgec_value=fsrqr.fsrqr_ratemarkuptype )

WHERE fsrqu.fsrqh_id=p_fsrqt_id

AND fschg.fschg_type ='I'

AND fsrqu.fsrqu_shipmenttype='P';









OPEN p_oncarrige_details FOR

SELECT fsrqu.fsrqu_id AS fsrqu_id,

NVL(fsrqu.fsrqu_idparent,fsrqu.fsrqu_id) AS fsrqu_idparent,

fsrqu.fsloc_idfrom AS fsloc_idfrom,

fsloc_fromlocation.fsloc_name AS from_location,

fsrqu.fsrqu_zipcodefrom AS fsrqu_zipcodefrom,

fsrqu.fsloc_idto AS fsloc_idto,

fsloc_tolocation.fsloc_name AS to_location,

fsrqu.fsrqu_zipcodeto AS fsrqu_zipcodeto,

fsrqu.fsrqu_modeoftransport AS fsrqu_modeoftransport,

fsgec_transportmode.fsgec_description AS fsgec_modeoftransportdescription,

fsrqu.fsrqu_transit AS fsrqu_transit,

fsrqu.fsrqu_transittype AS fsrqu_transittype,

fsgec_transittype.fsgec_description AS fsgec_transittypedescription,

fsrqu.fsrqu_specialinstruction AS fsrqu_specialinstruction,

fsrqu.fsrqu_fromlocationaddress AS fsrqu_fromlocationaddress,

fsrqu.fsrqu_tolocationaddress AS fsrqu_tolocationaddress,

fsrqu.fsrqu_locationtype AS fsrqu_locationtype,

fsrqu.fsrqu_ismultimode AS fsrqu_ismultimode,

fsrqu.fsrqu_ismultistop AS fsrqu_ismultistop

FROM FSRQU_RailQuotationIntermodel fsrqu

JOIN FSLOC_Location fsloc_fromlocation ON (fsloc_fromlocation.fsloc_id=fsrqu.fsloc_idfrom)

JOIN FSLOC_Location fsloc_tolocation ON (fsloc_tolocation.fsloc_id=fsrqu.fsloc_idto)

JOIN fsgec_generalcode fsgec_transportmode ON (fsgec_transportmode.fsgec_referencename = 'MODEOFTRANSPORT' AND fsgec_transportmode.fsgec_value = fsrqu.fsrqu_modeoftransport)

JOIN fsgec_generalcode fsgec_transittype ON (fsgec_transittype.fsgec_referencename = 'TRANSITTYPE' AND fsgec_transittype.fsgec_value = fsrqu.fsrqu_transittype)

WHERE fsrqu.fsrqh_id=p_fsrqt_id

AND fsrqu.fsrqu_shipmenttype='O';





OPEN p_oncarriegecharge_details FOR

SELECT fsrqr.fsrqr_id AS fsrqr_id,

fsrqr.fsrqu_id AS fsrqu_id,

fsrqr.fschg_id AS fschg_id,

fschg.fschg_code AS fschg_code,

fschg.fschg_description AS fschg_description,

fsrqr.fseqt_sizetype AS fseqt_sizetype,

fsrqr.fsrqr_weight AS fsrqr_weight,

fsrqr.fsmea_id AS fsmea_id,

fsmea.fsmea_code AS fsmea_code,

fsmea.fsmea_name AS fsmea_name,

fsrqr.fsrqr_volume AS fsrqr_volume,

fsrqr.fsrqr_Tariffrate AS fsrqr_Tariffrate,

fsrqr.fsrqr_ratemarkuptype AS fsrqr_ratemarkuptype,

markuptype.fsgec_description AS markuptype_description,

fsrqr.fsrqr_ratemarkupvalue AS fsrqr_ratemarkupvalue,

fsrqr.fsrqr_sellingrate AS fsrqr_sellingrate,

fsrqr.fscry_id AS fscry_id,

fscry.fscry_code AS fscry_code,

fscry.fscry_description AS fscry_description

FROM FSRQU_RailQuotationIntermodel fsrqu

JOIN FSRQR_RailQuotationRate fsrqr ON (fsrqr.fsrqu_id=fsrqu.fsrqu_id AND fsrqu.fsrqu_idparent IS NULL)

JOIN FSCHG_ChargeMaster fschg ON (fschg.fschg_id=fsrqr.fschg_id)

JOIN FSCRY_Currency fscry ON (fscry.fscry_id=fsrqr.fscry_id)

JOIN FSMEA_Measurement fsmea ON (fsmea.fsmea_id=fsrqr.fsmea_id)

JOIN FSGEC_GeneralCode markuptype ON (markuptype.fsgec_referencename='MARKUPTYPE' AND markuptype.fsgec_value=fsrqr.fsrqr_ratemarkuptype )

WHERE fsrqu.fsrqh_id=p_fsrqt_id

AND fschg.fschg_type ='I'

AND fsrqu.fsrqu_shipmenttype='O';



p_issuccess := fs_constants.k_success;



OPEN p_message FOR

SELECT fs_constants.k_success_msgcode AS msg_code,

NULL AS msg_value

FROM dual;



EXCEPTION

WHEN OTHERS

THEN

p_issuccess := fs_constants.k_failure;

fs_exception.raise_exception;



END Getlist_charges_Prc;





PROCEDURE Get_charges_Prc ( --charges details

p_fsrqr_id IN FSRQH_RailQuotationHeader.fsrqh_id%TYPE,

p_fsofo_id IN FSRQH_RailQuotationHeader.fsofo_id%TYPE,

p_charge_details OUT FS_RailQuotation.t_list,

p_issuccess OUT FSRTH_RailTariffHeader.fsrth_referencenumber %TYPE,

p_message OUT FS_RailQuotation.t_list)

AS

BEGIN



OPEN p_charge_details FOR

SELECT fsrqr.fsrqr_id AS fsrqr_id,

fsrqr.fschg_id AS fschg_id,

fschg.fschg_code AS fschg_code,

fschg.fschg_description AS fschg_description,

fsrqr.fseqt_sizetype AS fseqt_sizetype,

fsrqr.fsrqr_basis AS fsrqr_basis,

gec_basis.fsgec_description AS gec_basisdescription,

fsrqr.fscmg_id AS fscmg_id,

fscmg.fscmg_code AS fscmg_code,

fscmg.fscmg_description AS fscmg_description,

fsrqr.fsrqr_Tariffrate AS fsrqr_Tariffrate,

fsrqr.fsrqr_ratemarkuptype AS fsrqr_ratemarkuptype,

markuptype.fsgec_description AS ratemarkuptype_description,

fsrqr.fsrqr_ratemarkupvalue AS fsrqr_ratemarkupvalue,

fsrqr.fsrqr_sellingrate AS fsrqr_sellingrate,

fsrqr.fsrqr_tariffvolume AS fsrqr_tariffvolume,

fsrqr.fsrqr_volumemarkuptype AS fsrqr_volumemarkuptype,

markuptype_volume.fsgec_description AS markuptype_volumedescription,

fsrqr.fsrqr_volumemarkupvalue AS fsrqr_volumemarkupvalue,

fsrqr.fsrqr_volumesellingrate AS fsrqr_volumesellingrate,

fsrqr.fsrqr_tariffweight AS fsrqr_tariffweight,

fsrqr.fsrqr_weightmarkuptype AS fsrqr_weightmarkuptype,

markuptype_weight.fsgec_description AS markuptype_weightdescription,

fsrqr.fsrqr_weightmarkupvalue AS fsrqr_weightmarkupvalue,

fsrqr.fsrqr_weightsellingrate AS fsrqr_weightsellingrate,

fsrqr.fscry_id AS fscry_id,

fscry.fscry_code AS fscry_code,

fscry.fscry_description AS fscry_description,

(SELECT listagg (DISTINCT fschg.fschg_code,', ' ON overflow TRUNCATE WITH COUNT)

within GROUP (ORDER BY fsrqi.fsrqi_id)

FROM FSRQI_RailQuotationInclusiveCharge fsrqi

JOIN fschg_chargemaster fschg ON (fschg.fschg_id=fsrqi.fschg_id)

WHERE fsrqi.fsrqr_id=fsrqr.fsrqr_id

) AS inclusive_charge,

fsrqr.fsrqr_remarks AS fsrqr_remarks ,

NULL AS charge_basis,

fsrqr.fsrqr_isexportimport AS export_import

FROM FSRQR_RailQuotationRate fsrqr

JOIN FSCHG_ChargeMaster fschg ON (fschg.fschg_id=fsrqr.fschg_id)

JOIN FSCMG_Commoditygroup fscmg ON ( fscmg.fscmg_id=fsrqr.fscmg_id)

JOIN FSCRY_Currency fscry ON (fscry.fscry_id=fsrqr.fscry_id)

JOIN FSGEC_GeneralCode gec_basis ON (gec_basis.fsgec_referencename='BASIS' AND gec_basis.fsgec_value=fsrqr.fsrqr_basis )

JOIN FSGEC_GeneralCode markuptype ON (markuptype.fsgec_referencename='MARKUPTYPE' AND markuptype.fsgec_value=fsrqr.fsrqr_ratemarkuptype )

JOIN FSGEC_GeneralCode markuptype_volume ON (markuptype_volume.fsgec_referencename='MARKUPTYPE' AND markuptype_volume.fsgec_value=fsrqr.fsrqr_volumemarkuptype )

JOIN FSGEC_GeneralCode markuptype_weight ON (markuptype_weight.fsgec_referencename='MARKUPTYPE' AND markuptype_weight.fsgec_value=fsrqr.fsrqr_weightmarkuptype )

WHERE fsrqr.fsrqr_id=p_fsrqr_id;



p_issuccess := fs_constants.k_success;



OPEN p_message FOR

SELECT fs_constants.k_success_msgcode AS msg_code,

NULL AS msg_value

FROM dual;



EXCEPTION

WHEN OTHERS

THEN

p_issuccess := fs_constants.k_failure;

fs_exception.raise_exception;



END Get_charges_Prc;









/*PROCEDURE Getlist_intermodel_Prc ( --intermodel details

p_fsrqh_id IN FSRQH_RailQuotationHeader.fsrqh_id%TYPE,

p_fsofo_id IN FSRQH_RailQuotationHeader.fsofo_id%TYPE,

p_precarrige_details OUT FS_RailQuotation.t_list,

p_precarriegecharge_details OUT FS_RailQuotation.t_list,

p_oncarrige_details OUT FS_RailQuotation.t_list,

p_oncarriegecharge_details OUT FS_RailQuotation.t_list,

p_issuccess OUT FSRTH_RailTariffHeader.fsrth_referencenumber %TYPE,

p_message OUT FS_RailQuotation.t_list)

AS

BEGIN



OPEN p_precarrige_details FOR

SELECT fsrqu.fsrqu_id AS fsrqu_id,

fsrqu.fsloc_idfrom AS fsloc_idfrom,

fsloc_fromlocation.fsloc_name AS from_location,

fsrqu.fsrqu_zipcodefrom AS fsrqu_zipcodefrom,

fsrqu.fsloc_idto AS fsloc_idto,

fsloc_tolocation.fsloc_name AS to_location,

fsrqu.fsrqu_zipcodeto AS fsrqu_zipcodeto,

fsrqu.fsrqu_modeoftransport AS fsrqu_modeoftransport,

fsgec_transportmode.fsgec_description AS fsgec_modeoftransportdescription,

fsrqu.fsrqu_transit AS fsrqu_transit,

fsrqu.fsrqu_transittype AS fsrqu_transittype,

fsgec_transittype.fsgec_description AS fsgec_transittypedescription,

fsrqu.fsrqu_specialinstruction AS fsrqu_specialinstruction,

fsrqu.fsrqu_fromlocationaddress AS fsrqu_fromlocationaddress,

fsrqu.fsrqu_tolocationaddress AS fsrqu_tolocationaddress,

fsrqu.fsrqu_locationtype AS fsrqu_locationtype

FROM FSRQU_RailQuotationIntermodel fsrqu

JOIN FSLOC_Location fsloc_fromlocation ON (fsloc_fromlocation.fsloc_id=fsrqu.fsloc_idfrom)

JOIN FSLOC_Location fsloc_tolocation ON (fsloc_tolocation.fsloc_id=fsrqu.fsloc_idto)

JOIN fsgec_generalcode fsgec_transportmode ON (fsgec_transportmode.fsgec_referencename = 'MODEOFTRANSPORT' AND fsgec_transportmode.fsgec_value = fsrqu.fsrqu_modeoftransport)

JOIN fsgec_generalcode fsgec_transittype ON (fsgec_transittype.fsgec_referencename = 'TRANSITTYPE' AND fsgec_transittype.fsgec_value = fsrqu.fsrqu_transittype)

WHERE fsrqu.fsrqh_id=p_fsrqh_id;





OPEN p_precarriegecharge_details FOR

SELECT fsrqr.fsrqr_id AS fsrqr_id,

fsrqr.fschg_id AS fschg_id,

fschg.fschg_code AS fschg_code,

fschg.fschg_description AS fschg_description,

fsrqr.fseqt_sizetype AS fseqt_sizetype,

fsrqr.fsrqr_weight AS fsrqr_weight,

fsrqr.fsmea_id AS fsmea_id,

fsmea.fsmea_code AS fsmea_code,

fsmea.fsmea_name AS fsmea_name,

fsrqr.fsrqr_volume AS fsrqr_volume,

fsrqr.fsrqr_Tariffrate AS fsrqr_Tariffrate,

fsrqr.fsrqr_ratemarkuptype AS fsrqr_ratemarkuptype,

markuptype.fsgec_description AS markuptype_description,

fsrqr.fsrqr_ratemarkupvalue AS fsrqr_ratemarkupvalue,

fsrqr.fsrqr_sellingrate AS fsrqr_sellingrate,

fsrqr.fscry_id AS fscry_id,

fscry.fscry_code AS fscry_code,

fscry.fscry_description AS fscry_description

FROM FSRQU_RailQuotationIntermodel fsrqu

JOIN T_FSRQR_RailQuotationRate fsrqr ON (fsrqr.fsrqu_id=fsrqu.fsrqu_id)

JOIN FSCHG_ChargeMaster fschg ON (fschg.fschg_id=fsrqr.fschg_id)

JOIN FSCRY_Currency fscry ON (fscry.fscry_id=fsrqr.fscry_id)

JOIN FSMEA_Measurement fsmea ON (fsmea.fsmea_id=fsrqr.fsmea_id)

JOIN FSGEC_GeneralCode markuptype ON (markuptype.fsgec_referencename='MARKUPTYPE' AND markuptype.fsgec_value=fsrqr.fsrqr_ratemarkuptype )

WHERE fsrqu.fsrqh_id=p_fsrqh_id

AND fschg.fschg_type ='I';









OPEN p_oncarrige_details FOR

SELECT fsrqu.fsrqu_id AS fsrqu_id,

fsrqu.fsloc_idfrom AS fsloc_idfrom,

fsloc_fromlocation.fsloc_name AS from_location,

fsrqu.fsrqu_zipcodefrom AS fsrqu_zipcodefrom,

fsrqu.fsloc_idto AS fsloc_idto,

fsloc_tolocation.fsloc_name AS to_location,

fsrqu.fsrqu_zipcodeto AS fsrqu_zipcodeto,

fsrqu.fsrqu_modeoftransport AS fsrqu_modeoftransport,

fsgec_transportmode.fsgec_description AS fsgec_modeoftransportdescription,

fsrqu.fsrqu_transit AS fsrqu_transit,

fsrqu.fsrqu_transittype AS fsrqu_transittype,

fsgec_transittype.fsgec_description AS fsgec_transittypedescription,

fsrqu.fsrqu_specialinstruction AS fsrqu_specialinstruction,

fsrqu.fsrqu_fromlocationaddress AS fsrqu_fromlocationaddress,

fsrqu.fsrqu_tolocationaddress AS fsrqu_tolocationaddress,

fsrqu.fsrqu_locationtype AS fsrqu_locationtype

FROM FSRQU_RailQuotationIntermodel fsrqu

JOIN FSLOC_Location fsloc_fromlocation ON (fsloc_fromlocation.fsloc_id=fsrqu.fsloc_idfrom)

JOIN FSLOC_Location fsloc_tolocation ON (fsloc_tolocation.fsloc_id=fsrqu.fsloc_idto)

JOIN fsgec_generalcode fsgec_transportmode ON (fsgec_transportmode.fsgec_referencename = 'MODEOFTRANSPORT' AND fsgec_transportmode.fsgec_value = fsrqu.fsrqu_modeoftransport)

JOIN fsgec_generalcode fsgec_transittype ON (fsgec_transittype.fsgec_referencename = 'TRANSITTYPE' AND fsgec_transittype.fsgec_value = fsrqu.fsrqu_transittype)

WHERE fsrqu.fsrqh_id=p_fsrqh_id;





OPEN p_oncarriegecharge_details FOR

SELECT fsrqr.fsrqr_id AS fsrqr_id,

fsrqr.fschg_id AS fschg_id,

fschg.fschg_code AS fschg_code,

fschg.fschg_description AS fschg_description,

fsrqr.fseqt_sizetype AS fseqt_sizetype,

fsrqr.fsrqr_weight AS fsrqr_weight,

fsrqr.fsmea_id AS fsmea_id,

fsmea.fsmea_code AS fsmea_code,

fsmea.fsmea_name AS fsmea_name,

fsrqr.fsrqr_volume AS fsrqr_volume,

fsrqr.fsrqr_Tariffrate AS fsrqr_Tariffrate,

fsrqr.fsrqr_ratemarkuptype AS fsrqr_ratemarkuptype,

markuptype.fsgec_description AS markuptype_description,

fsrqr.fsrqr_ratemarkupvalue AS fsrqr_ratemarkupvalue,

fsrqr.fsrqr_sellingrate AS fsrqr_sellingrate,

fsrqr.fscry_id AS fscry_id,

fscry.fscry_code AS fscry_code,

fscry.fscry_description AS fscry_description

FROM FSRQU_RailQuotationIntermodel fsrqu

JOIN T_FSRQR_RailQuotationRate fsrqr ON (fsrqr.fsrqu_id=fsrqu.fsrqu_id)

JOIN FSCHG_ChargeMaster fschg ON (fschg.fschg_id=fsrqr.fschg_id)

JOIN FSCRY_Currency fscry ON (fscry.fscry_id=fsrqr.fscry_id)

JOIN FSMEA_Measurement fsmea ON (fsmea.fsmea_id=fsrqr.fsmea_id)

JOIN FSGEC_GeneralCode markuptype ON (markuptype.fsgec_referencename='MARKUPTYPE' AND markuptype.fsgec_value=fsrqr.fsrqr_ratemarkuptype )

WHERE fsrqu.fsrqh_id=p_fsrqh_id

AND fschg.fschg_type ='I';



p_issuccess := fs_constants.k_success;



OPEN p_message FOR

SELECT fs_constants.k_success_msgcode AS msg_code,

NULL AS msg_value

FROM dual;



EXCEPTION

WHEN OTHERS

THEN

p_issuccess := fs_constants.k_failure;

fs_exception.raise_exception;



END Getlist_intermodel_Prc;*/



PROCEDURE Get_intermodel_Prc ( --intermodel details

p_fsrqu_id IN FSRQH_RailQuotationHeader.fsrqh_id%TYPE,

p_fsofo_id IN FSRQH_RailQuotationHeader.fsofo_id%TYPE,

p_carrige_details OUT FS_RailQuotation.t_list,

p_issuccess OUT FSRTH_RailTariffHeader.fsrth_referencenumber %TYPE,

p_message OUT FS_RailQuotation.t_list)

AS

BEGIN



OPEN p_carrige_details FOR

SELECT fsrqu.fsrqu_id AS fsrqu_id,

fsrqu.fsloc_idfrom AS fsloc_idfrom,

fsloc_fromlocation.fsloc_name AS from_location,

fsrqu.fsrqu_zipcodefrom AS fsrqu_zipcodefrom,

fsrqu.fsloc_idto AS fsloc_idto,

fsloc_tolocation.fsloc_name AS to_location,

fsrqu.fsrqu_zipcodeto AS fsrqu_zipcodeto,

fsrqu.fsrqu_modeoftransport AS fsrqu_modeoftransport,

fsgec_transportmode.fsgec_description AS fsgec_modeoftransportdescription,

fsrqu.fsrqu_transit AS fsrqu_transit,

fsrqu.fsrqu_transittype AS f