AWS DevOps Training in Chennai

create or replace PACKAGE /*FS-250620-01*/ FS_AirQuotationManagement
AUTHID DEFINER
AS
/*
-----------------------------------------------------------------------------------------------------------------------------------
Modification History
----------------------------------------------------------------------------------------------------------------------------------
|| Version When Who What
|| -------------------------------------------------------------------------------------------------------------------------------
|| FS-020620-01 02-JUN-2020 Praveen Kumar. D Package created to define Air Quotation.
|| FS-250620-01 25-JUN-2020 Praveen Kumar. D Get_Ratefromtariff_Prc procedure added.
-----------------------------------------------------------------------------------------------------------------------------------
*/

TYPE t_list IS REF CURSOR;



PROCEDURE getLov_Airquotationreference_prc (
p_fsofo_id IN fsaqh_airquotationheader.fsofo_id%TYPE, -- To get air tariff reference number
p_type IN fsaqh_airquotationheader.fsaqh_referencenumber%TYPE ,
p_searchtext IN fsaqh_airquotationheader.fsaqh_referencenumber%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_Airquotation_count OUT fsaqh_airquotationheader.fsaqh_id%TYPE,
p_Airquotationreference OUT FS_AirQuotationManagement.t_list,
p_issuccess OUT fsbkg_booking.fsbkg_referencenumber%TYPE,
p_message OUT FS_AirQuotationManagement.t_list);

PROCEDURE getLov_copyreference_prc (
p_fsofo_id IN fsaqh_airquotationheader.fsofo_id%TYPE, -- To get copy reference number
p_searchtext IN fsaqh_airquotationheader.fsaqh_referencenumber%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_Airquotation_count OUT fsaqh_airquotationheader.fsaqh_id%TYPE,
p_Airquotationreference OUT FS_AirQuotationManagement.t_list,
p_issuccess OUT fsbkg_booking.fsbkg_referencenumber%TYPE,
p_message OUT FS_AirQuotationManagement.t_list);

PROCEDURE GetList_fsaqh_Prc ( --search for air quotation
p_fsofo_id IN fsaqh_airquotationheader.fsofo_id%TYPE,
p_fsbue_idcustomer IN fsaqh_airquotationheader.fsbue_idcustomer%TYPE,
p_fsaqh_quotationtype IN fsaqh_airquotationheader.fsaqh_quotationtype%TYPE,
p_fsaqh_shipmenttype IN fsaqh_airquotationheader.fsaqh_shipmenttype%TYPE,
p_fsaqh_referencenumber IN fsaqh_airquotationheader.fsaqh_id%TYPE,
p_fsaqh_validfrom IN fsaqh_airquotationheader.fsaqh_validfrom%TYPE,
p_fsaqh_validto IN fsaqh_airquotationheader.fsaqh_validto%TYPE,
p_fsbue_idcarrier IN fsaqh_airquotationheader.fsbue_idcustomer%TYPE,
p_fsart_idfromAirport IN fsaqt_airquotationtransportstation.fsart_idfromAirport%TYPE,
p_fsart_idtoAirport IN fsaqt_airquotationtransportstation.fsart_idtoAirport%TYPE,
p_quotationparty IN fsaqh_airquotationheader.fsaqh_quotationtype%TYPE,
p_partner IN fsaqh_airquotationheader.fsofo_id%TYPE,
p_fsaqh_status IN fsaqh_airquotationheader.fsaqh_status%TYPE,
p_page_index IN fsaqh_airquotationheader.fsaqh_id%TYPE,
p_page_size IN fsaqh_airquotationheader.fsaqh_id%TYPE,
p_sort_field IN fsaqh_airquotationheader.fsaqh_referencenumber%TYPE,
p_sort_type IN fsaqh_airquotationheader.fsaqh_referencenumber%TYPE,
p_fsaqh_count OUT fsaqh_airquotationheader.fsaqh_id%TYPE,
p_fsaqh_list OUT FS_AirQuotationManagement.t_list,
p_issuccess OUT fsaqh_airquotationheader.fsaqh_status%TYPE,
p_message OUT FS_AirQuotationManagement.t_list);

PROCEDURE Get_airquotationratedetails_Prc ( --Quotation creation
p_fsaqh_id IN fsaqh_airquotationheader.fsaqh_id%TYPE,
p_fsofo_id IN fsaqh_airquotationheader.fsofo_id%TYPE,
p_airquotationrateheader_details OUT FS_AirQuotationManagement.t_list,
p_airquotationrate_details OUT FS_AirQuotationManagement.t_list,
p_issuccess OUT fsaqh_airquotationheader.fsaqh_status%TYPE,
p_message OUT FS_AirQuotationManagement.t_list);

PROCEDURE Get_Ratefromtariff_Prc ( --get rates from tariff
p_fsofo_id IN fsaqh_airquotationheader.fsofo_id%TYPE,
p_fsbue_idcustomer IN fsaqh_airquotationheader.fsbue_idcustomer%TYPE,
p_fssqh_shipmenttype IN fsaqh_airquotationheader.fsaqh_shipmenttype%TYPE,
p_fsbue_idcarrier IN fsbue_businessentity.fsbue_name%TYPE,
p_route IN fsdcl_documentationchangelog.fsdcl_content%TYPE,
p_header_details OUT FS_AirQuotationManagement.t_list,
p_Tariff_details OUT FS_AirQuotationManagement.t_list,
p_weightdetails OUT FS_AirQuotationManagement.t_list,
p_issuccess OUT fsaqh_airquotationheader.fsaqh_shipmenttype%TYPE,
p_message OUT FS_AirQuotationManagement.t_list);

PROCEDURE Get_localcharges_Prc ( --get local charges
p_fsofo_id IN fssqh_seaquotationheader.fsofo_id%TYPE,
p_fsbue_idcustomer IN fssqh_seaquotationheader.fsbue_idcustomer%TYPE,
p_fsofo_idpartner IN fssqh_seaquotationheader.fsofo_idpartner%TYPE,
p_fssqh_shipmenttype IN fssqh_seaquotationheader.fssqh_shipmenttype%TYPE,
p_fssqh_isexportimport IN fssqh_seaquotationheader.fssqh_isexportimport%TYPE,
p_route IN fsdcl_documentationchangelog.fsdcl_content%TYPE,
p_Tariff_deatils OUT FS_AirQuotationManagement.t_list,
p_issuccess OUT fssqh_seaquotationheader.fssqh_shipmenttype %TYPE,
p_message OUT FS_AirQuotationManagement.t_list);


PROCEDURE Get_airquotationtariff_Prc ( --Retrive quotation details
p_fsaqh_id IN fsaqh_airquotationheader.fsaqh_id%TYPE,
p_fsaqt_id IN fsaqh_airquotationheader.fsaqh_remarks%TYPE,
p_fsofo_id IN fsaqh_airquotationheader.fsofo_id%TYPE,
p_airquotationhdrdetails OUT FS_AirQuotationManagement.t_list,
p_airquotationport_details OUT FS_AirQuotationManagement.t_list,
p_airquotationrate_details OUT FS_AirQuotationManagement.t_list,
p_surcharge_details OUT FS_AirQuotationManagement.t_list,
p_localcharge_details OUT FS_AirQuotationManagement.t_list,
p_weightdetails OUT FS_AirQuotationManagement.t_list,
p_precarriage_details OUT FS_AirQuotationManagement.t_list,
p_precarriagecharge_details OUT FS_AirQuotationManagement.t_list,
p_oncarriage_details OUT FS_AirQuotationManagement.t_list,
p_oncarriagecharge_details OUT FS_AirQuotationManagement.t_list,
p_issuccess OUT fsaqh_airquotationheader.fsaqh_status%TYPE,
p_message OUT FS_AirQuotationManagement.t_list);

PROCEDURE Get_intermodel_Prc (
p_fsaqh_id IN fsaqh_airquotationheader.fsaqh_id%TYPE,
p_fsaqu_id IN FSAQU_AirQuotationIntermodel.fsaqu_id%TYPE,
p_fsofo_id IN fsaqh_airquotationheader.fsofo_id%TYPE,
p_precarriage_details OUT FS_AirQuotationManagement.t_list,
p_precarriagecharge_details OUT FS_AirQuotationManagement.t_list,
p_oncarriage_details OUT FS_AirQuotationManagement.t_list,
p_oncarriagecharge_details OUT FS_AirQuotationManagement.t_list,
p_issuccess OUT fsaqh_airquotationheader.fsaqh_shipmenttype%TYPE,
p_message OUT FS_AirQuotationManagement.t_list);

PROCEDURE put_fsaqh_prc(
p_fsaqh_id IN OUT FSAQH_AirQuotationHeader.fsaqh_id%TYPE,
p_fsofo_id IN FSAQH_AirQuotationHeader.fsofo_id%TYPE,
p_fsaqh_referencenumber IN OUT FSAQH_AirQuotationHeader.fsaqh_referencenumber%TYPE,
p_fsaqh_referencedate IN FSAQH_AirQuotationHeader.fsaqh_referencedate%TYPE,
p_fsbue_idcustomer IN FSAQH_AirQuotationHeader.fsbue_idcustomer%TYPE,
p_fsofo_idsalesoffice IN FSAQH_AirQuotationHeader.fsofo_idsalesoffice%TYPE,
p_fsaqh_customerdetails IN FSAQH_AirQuotationHeader.fsaqh_customerdetails%TYPE,
p_fsaqh_quotationtype IN FSAQH_AirQuotationHeader.fsaqh_quotationtype%TYPE,
p_fsaqh_shipmenttype IN FSAQH_AirQuotationHeader.fsaqh_shipmenttype%TYPE,
p_fsaqh_minimumconfirmedquantity IN FSAQH_AirQuotationHeader.fsaqh_minimumconfirmedquantity%TYPE,
p_fsaqh_minimumconfirmedquantitytype IN FSAQH_AirQuotationHeader.fsaqh_minimumconfirmedquantitytype%TYPE,
p_fsaqh_incoterms IN FSAQH_AirQuotationHeader.fsaqh_incoterms%TYPE,
p_fsaqh_isexportimport IN FSAQH_AirQuotationHeader.fsaqh_isexportimport%TYPE,
p_fscry_idquotation IN FSAQH_AirQuotationHeader.fscry_idquotation%TYPE,
p_fsaqh_validfrom IN FSAQH_AirQuotationHeader.fsaqh_validfrom%TYPE,
p_fsaqh_validto IN FSAQH_AirQuotationHeader.fsaqh_validto%TYPE,
p_fsaqh_weight IN FSAQH_AirQuotationHeader.fsaqh_weight%TYPE,
p_fsmea_id IN FSAQH_AirQuotationHeader.fsmea_id%TYPE,
p_fsaqh_volume IN FSAQH_AirQuotationHeader.fsaqh_volume%TYPE,
p_fsaqh_quotationpartytype IN FSAQH_AirQuotationHeader.fsaqh_quotationpartytype%TYPE,
p_fsofo_idpartner IN FSAQH_AirQuotationHeader.fsofo_idpartner%TYPE,
p_fsaqh_isshowcarrier IN FSAQH_AirQuotationHeader.fsaqh_isshowcarrier%TYPE,
p_fsaqh_withdrawreason IN FSAQH_AirQuotationHeader.fsaqh_withdrawreason%TYPE,
p_fsaqh_status IN FSAQH_AirQuotationHeader.fsaqh_status%TYPE,
p_fsaqh_remarks IN FSAQH_AirQuotationHeader.fsaqh_remarks%TYPE,
p_user_id IN FSAQH_AirQuotationHeader.fsaqh_createdby%TYPE,
p_record_status IN FSAQH_AirQuotationHeader.fsaqh_purgeyear%TYPE,
p_issuccess OUT FSAQH_AirQuotationHeader.fsaqh_purgeyear%TYPE,
p_message OUT FS_AirQuotationManagement.t_list);

PROCEDURE put_fsaqc_prc(
p_fsaqc_id IN OUT FSAQC_AirQuotationCarrier.fsaqc_id%TYPE,
p_fsofo_id IN FSAQC_AirQuotationCarrier.fsofo_id%TYPE,
p_fsaqh_id IN FSAQC_AirQuotationCarrier.fsaqh_id%TYPE,
p_fsbue_idcarrier IN FSAQC_AirQuotationCarrier.fsbue_idcarrier%TYPE,
p_user_id IN FSAQC_AirQuotationCarrier.fsaqc_createdby%TYPE,
p_record_status IN FSAQC_AirQuotationCarrier.fsaqc_purgeyear%TYPE,
p_issuccess OUT FSAQC_AirQuotationCarrier.fsaqc_purgeyear%TYPE,
p_message OUT FS_AirQuotationManagement.t_list);

PROCEDURE put_fsaqd_prc(
p_fsaqd_id IN OUT FSAQD_AirQuotationDeal.fsaqd_id%TYPE,
p_fsofo_id IN FSAQD_AirQuotationDeal.fsofo_id%TYPE,
p_fsaqh_id IN FSAQD_AirQuotationDeal.fsaqh_id%TYPE,
p_fsdlm_id IN FSAQD_AirQuotationDeal.fsdlm_id%TYPE,
p_user_id IN FSAQD_AirQuotationDeal.fsaqd_createdby%TYPE,
p_record_status IN FSAQD_AirQuotationDeal.fsaqd_purgeyear%TYPE,
p_issuccess OUT FSAQD_AirQuotationDeal.fsaqd_purgeyear%TYPE,
p_message OUT FS_AirQuotationManagement.t_list);

PROCEDURE put_fsaqt_prc(
p_fsaqt_id IN OUT FSAQT_AirQuotationTransportStation.fsaqt_id%TYPE,
p_fsofo_id IN FSAQT_AirQuotationTransportStation.fsofo_id%TYPE,
p_fsaqh_id IN FSAQT_AirQuotationTransportStation.fsaqh_id%TYPE,
p_fsart_fromport IN FSAQH_AirQuotationHeader.fsaqh_remarks%TYPE,
p_fsart_toport IN FSAQH_AirQuotationHeader.fsaqh_remarks%TYPE,
p_transhipmentport IN FSAQH_AirQuotationHeader.fsaqh_remarks%TYPE,
p_fsaqt_travellingtime IN FSAQT_AirQuotationTransportStation.fsaqt_travellingtime%TYPE,
p_fsaqt_dayoffrequency IN FSAQT_AirQuotationTransportStation.fsaqt_dayoffrequency%TYPE,
p_routeexist IN FSAQH_AirQuotationHeader.fsaqh_remarks%TYPE,
p_user_id IN FSAQT_AirQuotationTransportStation.fsaqt_createdby%TYPE,
p_record_status IN FSAQT_AirQuotationTransportStation.fsaqt_purgeyear%TYPE,
p_issuccess OUT FSAQT_AirQuotationTransportStation.fsaqt_purgeyear%TYPE,
p_message OUT FS_AirQuotationManagement.t_list);

PROCEDURE put_fsaql_prc( -- Air Quotation Email
p_fsaql_id IN OUT FSAQL_AirQuotationEmail.fsaql_id%TYPE,
p_fsofo_id IN FSAQL_AirQuotationEmail.fsofo_id%TYPE,
p_fsaqh_id IN FSAQL_AirQuotationEmail.fsaqh_id%TYPE,
p_fsaql_email IN FSAQH_AirQuotationheader.fsaqh_remarks%TYPE,
p_user_id IN FSAQL_AirQuotationEmail.fsaql_createdby%TYPE,
p_record_status IN FSAQL_AirQuotationEmail.fsaql_purgeyear%TYPE,
p_issuccess OUT FSAQL_AirQuotationEmail.fsaql_purgeyear%TYPE,
p_message OUT FS_AirQuotationManagement.t_list);

PROCEDURE put_fsaqe_prc(
p_fsaqe_id IN OUT FSAQE_AirQuotationEquipment.fsaqe_id%TYPE,
p_fsofo_id IN FSAQE_AirQuotationEquipment.fsofo_id%TYPE,
p_fsaqt_id IN FSAQE_AirQuotationEquipment.fsaqt_id%TYPE,
p_fseqt_sizetypeuld IN FSAQE_AirQuotationEquipment.fseqt_sizetypeuld%TYPE,
p_fsaqe_cargotype IN FSAQE_AirQuotationEquipment.fsaqe_cargotype%TYPE,
p_fscmg_group IN FSAQH_AirQuotationheader.fsaqh_remarks%TYPE,
p_user_id IN FSAQE_AirQuotationEquipment.fsaqe_createdby%TYPE,
p_record_status IN FSAQE_AirQuotationEquipment.fsaqe_purgeyear%TYPE,
p_issuccess OUT FSAQE_AirQuotationEquipment.fsaqe_purgeyear%TYPE,
p_message OUT FS_AirQuotationManagement.t_list);

PROCEDURE put_fsaqu_prc(
p_fsaqu_id IN OUT FSAQU_AirQuotationIntermodel.fsaqu_id%TYPE,
p_fsofo_id IN FSAQU_AirQuotationIntermodel.fsofo_id%TYPE,
p_fsaqh_id IN FSAQU_AirQuotationIntermodel.fsaqh_id%TYPE,
p_fsaqu_shipmenttype IN FSAQU_AirQuotationIntermodel.fsaqu_shipmenttype%TYPE,
p_fsaqu_ismultimode IN FSAQU_AirQuotationIntermodel.fsaqu_ismultimode%TYPE,
p_fsaqu_ismultistop IN FSAQU_AirQuotationIntermodel.fsaqu_ismultistop%TYPE,
p_fsloc_idfrom IN FSAQU_AirQuotationIntermodel.fsloc_idfrom%TYPE,
p_fsaqu_zipcodefrom IN FSAQU_AirQuotationIntermodel.fsaqu_zipcodefrom%TYPE,
p_fsaqu_zipcodeto IN FSAQU_AirQuotationIntermodel.fsaqu_zipcodeto%TYPE,
p_fsloc_idto IN FSAQU_AirQuotationIntermodel.fsloc_idto%TYPE,
p_fsaqu_modeoftransport IN FSAQU_AirQuotationIntermodel.fsaqu_modeoftransport%TYPE,
p_fsaqu_transit IN FSAQU_AirQuotationIntermodel.fsaqu_transit%TYPE,
p_fsaqu_transittype IN FSAQU_AirQuotationIntermodel.fsaqu_transittype%TYPE,
p_fsaqu_specialinstruction IN FSAQU_AirQuotationIntermodel.fsaqu_specialinstruction%TYPE,
p_fsaqu_fromlocationaddress IN FSAQU_AirQuotationIntermodel.fsaqu_fromlocationaddress%TYPE,
p_fsaqu_tolocationaddress IN FSAQU_AirQuotationIntermodel.fsaqu_tolocationaddress%TYPE,
p_fsaqu_locationtype IN FSAQU_AirQuotationIntermodel.fsaqu_locationtype%TYPE,
p_fsaqu_idparent IN FSAQU_AirQuotationIntermodel.fsaqu_idparent%TYPE,
p_fsaqu_purgeyear IN FSAQU_AirQuotationIntermodel.fsaqu_purgeyear%TYPE,
p_user_id IN FSAQU_AirQuotationIntermodel.fsaqu_createdby%TYPE,
p_record_status IN FSAQU_AirQuotationIntermodel.fsaqu_purgeyear%TYPE,
p_issuccess OUT FSAQU_AirQuotationIntermodel.fsaqu_purgeyear%TYPE,
p_message OUT FS_AirQuotationManagement.t_list);

PROCEDURE put_fsaqr_prc(
p_fsaqr_id IN OUT FSAQR_AirQuotationRate.fsaqr_id%TYPE,
p_fsofo_id IN FSAQR_AirQuotationRate.fsofo_id%TYPE,
p_fsaqt_id IN FSAQR_AirQuotationRate.fsaqt_id%TYPE,
p_fsaqu_id IN FSAQR_AirQuotationRate.fsaqu_id%TYPE,
p_fsatr_id IN FSAQR_AirQuotationRate.fsatr_id%TYPE,
p_fsasr_id IN FSAQR_AirQuotationRate.fsasr_id%TYPE,
p_fslar_id IN FSAQR_AirQuotationRate.fslar_id%TYPE,
p_fschg_id IN FSAQR_AirQuotationRate.fschg_id%TYPE,
p_fsaqr_basis IN FSAQR_AirQuotationRate.fsaqr_basis%TYPE,
p_fsaqr_cargotype IN FSAQR_AirQuotationRate.fsaqr_cargotype%TYPE,
p_fseqt_sizetypeuld IN FSAQR_AirQuotationRate.fseqt_sizetypeuld%TYPE,
p_fscmg_id IN FSAQR_AirQuotationRate.fscmg_id%TYPE,
p_fsaqr_weighttype IN FSAQR_AirQuotationRate.fsaqr_weighttype%TYPE,
p_fscry_id IN FSAQR_AirQuotationRate.fscry_id%TYPE,
p_fsaqr_ispax IN FSAQR_AirQuotationRate.fsaqr_ispax%TYPE,
p_fsaqr_weight IN FSAQR_AirQuotationRate.fsaqr_weight%TYPE,
p_fsmea_id IN FSAQR_AirQuotationRate.fsmea_id%TYPE,
p_fsaqr_tariffrate IN FSAQR_AirQuotationRate.fsaqr_tariffrate%TYPE,
p_fsaqr_markuptype IN FSAQR_AirQuotationRate.fsaqr_markuptype%TYPE,
p_fsaqr_markupvalue IN FSAQR_AirQuotationRate.fsaqr_markupvalue%TYPE,
p_fsaqr_sellingrate IN FSAQR_AirQuotationRate.fsaqr_sellingrate%TYPE,
p_fsaqr_mintariffrate IN FSAQR_AirQuotationRate.fsaqr_mintariffrate%TYPE,
p_fsaqr_minmarkuptype IN FSAQR_AirQuotationRate.fsaqr_minmarkuptype%TYPE,
p_fsaqr_minmarkupvalue IN FSAQR_AirQuotationRate.fsaqr_minmarkupvalue%TYPE,
p_fsaqr_minsellingrate IN FSAQR_AirQuotationRate.fsaqr_minsellingrate%TYPE,
p_fsaqr_additionaldayfee IN FSAQR_AirQuotationRate.fsaqr_additionaldayfee%TYPE,
p_fsaqr_pivotweight IN FSAQR_AirQuotationRate.fsaqr_pivotweight%TYPE,
p_fsbue_idcarrier IN FSAQR_AirQuotationRate.fsbue_idcarrier%TYPE,
p_fsaqr_isexportimport IN FSAQR_AirQuotationRate.fsaqr_isexportimport%TYPE,
p_fsaqr_remarks IN FSAQR_AirQuotationRate.fsaqr_remarks%TYPE,
p_inclusivecharge IN FSAQR_AirQuotationRate.fsaqr_remarks%TYPE,
p_user_id IN FSAQR_AirQuotationRate.fsaqr_createdby%TYPE,
p_record_status IN FSAQR_AirQuotationRate.fsaqr_purgeyear%TYPE,
p_issuccess OUT FSAQR_AirQuotationRate.fsaqr_purgeyear%TYPE,
p_message OUT FS_AirQuotationManagement.t_list);

PROCEDURE put_fsaqw_prc(
p_fsaqw_id IN OUT FSAQW_AirQuotationSlabRate.fsaqw_id%TYPE,
p_fsofo_id IN FSAQW_AirQuotationSlabRate.fsofo_id%TYPE,
p_fsaqr_id IN FSAQW_AirQuotationSlabRate.fsaqr_id%TYPE,
p_fsatm_id IN FSAQW_AirQuotationSlabRate.fsatm_id%TYPE,
p_fsaqw_buyingrate IN FSAQW_AirQuotationSlabRate.fsaqw_buyingrate%TYPE,
p_fsaqw_markupvalue IN FSAQW_AirQuotationSlabRate.fsaqw_markupvalue%TYPE,
p_fsaqw_sellingrate IN FSAQW_AirQuotationSlabRate.fsaqw_sellingrate%TYPE,
P_fsaqw_weight IN FSAQW_AirQuotationSlabRate.fsaqw_weight%TYPE,
p_user_id IN FSAQR_AirQuotationRate.fsaqr_createdby%TYPE,
p_record_status IN FSAQR_AirQuotationRate.fsaqr_purgeyear%TYPE,
p_issuccess OUT FSAQR_AirQuotationRate.fsaqr_purgeyear%TYPE,
p_message OUT FS_AirQuotationManagement.t_list);


END FS_AirQuotationManagement;



create or replace PACKAGE BODY FS_AirQuotationManagement
AS

PROCEDURE getLov_Airquotationreference_prc (
p_fsofo_id IN fsaqh_airquotationheader.fsofo_id%TYPE, -- To get air tariff reference number
p_type IN fsaqh_airquotationheader.fsaqh_referencenumber%TYPE ,
p_searchtext IN fsaqh_airquotationheader.fsaqh_referencenumber%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_Airquotation_count OUT fsaqh_airquotationheader.fsaqh_id%TYPE,
p_Airquotationreference OUT FS_AirQuotationManagement.t_list,
p_issuccess OUT fsbkg_booking.fsbkg_referencenumber%TYPE,
p_message OUT FS_AirQuotationManagement.t_list)
AS
l_query CLOB;
l_query_cnt CLOB;
BEGIN
IF
FS_SECURITY.SQL_INJECTION_CHECK_FNC (p_type) = 'Y' OR
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 fsaqh_id ,
NULL AS fsaqh_referencenumber
FROM fsaqh_airquotationheader fsaqh
WHEER 1=2 ' ;

ELSE
l_query :='SELECT fsaqh.fsaqh_id AS fsaqh_id ,
fsaqh.fsaqh_referencenumber AS fsaqh_referencenumber
FROM fsaqh_airquotationheader fsaqh
WHERE ('''|| p_type || ''' IS NULL OR fsaqh.fsaqh_quotationtype = '''|| p_type || ''')
AND (('''|| p_searchtext|| ''' IS NULL) OR ( (UPPER(fsaqh.fsaqh_referencenumber) LIKE '''||'%'|| UPPER (p_searchtext)|| '%'||'''))) ';

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

p_Airquotation_count :=0;

IF p_page_index > 0 THEN

EXECUTE IMMEDIATE l_query_cnt INTO p_Airquotation_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_Airquotationreference 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_Airquotationreference_prc;

PROCEDURE getLov_copyreference_prc (
p_fsofo_id IN fsaqh_airquotationheader.fsofo_id%TYPE, -- To get copy reference number
p_searchtext IN fsaqh_airquotationheader.fsaqh_referencenumber%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_Airquotation_count OUT fsaqh_airquotationheader.fsaqh_id%TYPE,
p_Airquotationreference OUT FS_AirQuotationManagement.t_list,
p_issuccess OUT fsbkg_booking.fsbkg_referencenumber%TYPE,
p_message OUT FS_AirQuotationManagement.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 fsaqh_id ,
NULL AS fsaqh_referencenumber,
NULL AS Quotationpartytype,
NULL AS fsaqh_validto,
NULL AS fsgec_export_description,
NULL AS Customername,
NULL AS Partnername
FROM fsdul_dual fsdul
WHEER 1=2 ' ;

ELSE
l_query :='SELECT fsaqh.fsaqh_id AS fsaqh_id ,
fsaqh.fsaqh_referencenumber AS fsaqh_referencenumber,
partytype.fsgec_description AS Quotationpartytype,
fsaqh.fsaqh_validto AS fsaqh_validto,
fsgec_export.fsgec_description AS fsgec_export_description,
fsbue.fsbue_name AS Customername,
fsofo.fsofo_organizationname AS Partnername
FROM fsaqh_airquotationheader fsaqh
LEFT JOIN fsbue_businessentity fsbue ON (fsbue.fsbue_id=fsaqh.fsbue_idcustomer)
LEFT JOIN fsofo_officeorganization fsofo ON (fsofo.fsofo_id=fsaqh.fsofo_idpartner)
JOIN FSGEC_GeneralCode partytype ON (partytype.fsgec_referencename=''QUOTATIONPARTYTYPE'' AND partytype.fsgec_value=fsaqh.fsaqh_quotationpartytype )
JOIN fsgec_generalcode fsgec_export ON (fsgec_export.fsgec_referencename = ''BOOKINGTYPE'' AND fsgec_export.fsgec_value =fsaqh.fsaqh_isexportimport)
WHERE (('''|| p_searchtext|| ''' IS NULL) OR ( (UPPER(fsaqh.fsaqh_referencenumber) LIKE '''|| UPPER (p_searchtext)|| '%'||'''))) ';

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

p_Airquotation_count :=0;

IF p_page_index > 0 THEN

EXECUTE IMMEDIATE l_query_cnt INTO p_Airquotation_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_Airquotationreference 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 GetList_fsaqh_Prc ( --search for air quotation
p_fsofo_id IN fsaqh_airquotationheader.fsofo_id%TYPE,
p_fsbue_idcustomer IN fsaqh_airquotationheader.fsbue_idcustomer%TYPE,
p_fsaqh_quotationtype IN fsaqh_airquotationheader.fsaqh_quotationtype%TYPE,
p_fsaqh_shipmenttype IN fsaqh_airquotationheader.fsaqh_shipmenttype%TYPE,
p_fsaqh_referencenumber IN fsaqh_airquotationheader.fsaqh_id%TYPE,
p_fsaqh_validfrom IN fsaqh_airquotationheader.fsaqh_validfrom%TYPE,
p_fsaqh_validto IN fsaqh_airquotationheader.fsaqh_validto%TYPE,
p_fsbue_idcarrier IN fsaqh_airquotationheader.fsbue_idcustomer%TYPE,
p_fsart_idfromAirport IN fsaqt_airquotationtransportstation.fsart_idfromAirport%TYPE,
p_fsart_idtoAirport IN fsaqt_airquotationtransportstation.fsart_idtoAirport%TYPE,
p_quotationparty IN fsaqh_airquotationheader.fsaqh_quotationtype%TYPE,
p_partner IN fsaqh_airquotationheader.fsofo_id%TYPE,
p_fsaqh_status IN fsaqh_airquotationheader.fsaqh_status%TYPE,
p_page_index IN fsaqh_airquotationheader.fsaqh_id%TYPE,
p_page_size IN fsaqh_airquotationheader.fsaqh_id%TYPE,
p_sort_field IN fsaqh_airquotationheader.fsaqh_referencenumber%TYPE,
p_sort_type IN fsaqh_airquotationheader.fsaqh_referencenumber%TYPE,
p_fsaqh_count OUT fsaqh_airquotationheader.fsaqh_id%TYPE,
p_fsaqh_list OUT FS_AirQuotationManagement.t_list,
p_issuccess OUT fsaqh_airquotationheader.fsaqh_status%TYPE,
p_message OUT FS_AirQuotationManagement.t_list)
AS
l_query CLOB;
l_query_cnt CLOB;

BEGIN
IF FS_SECURITY.SQL_INJECTION_CHECK_FNC (p_fsaqh_quotationtype) = 'Y' OR
FS_SECURITY.SQL_INJECTION_CHECK_FNC (p_fsaqh_shipmenttype) = 'Y' OR
FS_SECURITY.SQL_INJECTION_CHECK_FNC (p_quotationparty) = 'Y' OR
FS_SECURITY.SQL_INJECTION_CHECK_FNC (p_fsaqh_status) = 'Y' OR
FS_SECURITY.SQL_INJECTION_CHECK_FNC (p_sort_field) = 'Y' OR
FS_SECURITY.SQL_INJECTION_CHECK_FNC (p_sort_type) = 'Y'
THEN
p_fsaqh_count := 0;
OPEN p_fsaqh_list FOR SELECT NULL AS fsaqh_id,
NULL AS fsaqh_customername,
NULL AS fsaqh_quotationtype,
NULL AS fsaqh_quotationtype_description,
NULL AS fsaqh_shipmenttype,
NULL AS fsaqh_shipmenttype_description,
NULL AS fsaqh_referencenumber,
NULL AS fsaqh_carriername,
NULL AS fsart_fromport,
NULL AS fsart_toport,
NULL AS fsaqh_validfrom,
NULL AS fsaqh_validto,
NULL AS fsaqh_status_description
--NULL AS fsaqh_versionnumber
FROM FSDUL_Dual
WHERE 1=2 ;

ELSE
l_query := '
SELECT fsaqh.fsaqh_id AS fsaqh_id,
fsbue_customer.fsbue_name AS fsaqh_customername,
fsaqh.fsaqh_quotationtype AS fsaqh_quotationtype,
fsgec_quotationtype.fsgec_description AS fsaqh_quotationtype_description,
fsaqh.fsaqh_shipmenttype AS fsaqh_shipmenttype,
fsgec_shipmenttype.fsgec_description AS fsaqh_shipmenttype_description,
fsaqh.fsaqh_referencenumber AS fsaqh_referencenumber,
(SELECT listagg (DISTINCT fsbue.fsbue_name,'', '' ON overflow TRUNCATE WITH COUNT)
within GROUP (ORDER BY fsaqc.fsaqc_id)
FROM fsaqc_airquotationcarrier fsaqc
JOIN fsbue_businessentity fsbue ON (fsbue.fsbue_id=fsaqc.fsbue_idcarrier)
WHERE fsaqc.fsaqh_id= fsaqc.fsaqh_id
) AS fsaqh_carriername,
fsart_fromairport.fsart_name AS fsart_fromport,
fsart_toairport.fsart_name AS fsart_toport,
fsaqh.fsaqh_validfrom AS fsaqh_validfrom,
fsaqh.fsaqh_validto AS fsaqh_validto,
fsgec_status.fsgec_description AS fsaqh_status_description
--fsaqh.fsaqh_versionnumber AS fsaqh_versionnumber
FROM fsaqh_airquotationheader fsaqh
JOIN fsaqt_airquotationtransportstation fsaqt ON (fsaqt.fsaqh_id=fsaqh.fsaqh_id)
JOIN FSART_AirportMaster fsart_fromairport ON (fsart_fromairport.fsart_id=fsaqt.fsart_idfromairport)
JOIN FSART_AirportMaster fsart_toairport ON (fsart_toairport.fsart_id=fsaqt.fsart_idtoairport)
JOIN fsbue_businessentity fsbue_customer ON (fsbue_customer.fsbue_id=fsaqh.fsbue_idcustomer)
--JOIN fsaqc_airquotationcarrier fsaqc ON (fsaqc.fsaqh_id=fsaqh.fsaqh_id)
-- JOIN fsbue_businessentity fsbue_carrier ON (fsbue_carrier.fsbue_id=fsaqc.fsbue_idcarrier)
JOIN FSGEC_GeneralCode fsgec_quotationtype ON (fsgec_quotationtype.fsgec_value=fsaqh.fsaqh_quotationtype AND fsgec_quotationtype.fsgec_referencename=''QUOTATIONTYPE'')
JOIN FSGEC_GeneralCode fsgec_shipmenttype ON (fsgec_shipmenttype.fsgec_value=fsaqh.fsaqh_shipmenttype AND fsgec_shipmenttype.fsgec_referencename=''AIRSHIPMENTTYPE'')
JOIN FSGEC_GeneralCode fsgec_status ON (fsgec_status.fsgec_value=fsaqh.fsaqh_status AND fsgec_status.fsgec_referencename=''QUOTATIONSTATUS'')
WHERE 1=1 ';

IF p_fsaqh_validfrom IS NOT NULL THEN
l_query := l_query || ' AND TRUNC(fsaqh.fsaqh_validfrom) >= '''|| TRUNC(p_fsaqh_validfrom) ||'''' ;
END IF;

IF p_fsaqh_validto IS NOT NULL THEN
l_query := l_query || ' AND TRUNC(fsaqh.fsaqh_validto) <= '''|| TRUNC(p_fsaqh_validto)||'''' ;
END IF;

IF p_fsaqh_status IS NOT NULL THEN
l_query := l_query || ' AND fsaqh.fsaqh_status = '''|| p_fsaqh_status|| '''';
END IF;

IF p_fsbue_idcustomer IS NOT NULL THEN
l_query := l_query || ' AND fsaqh.fsbue_idcustomer = '''|| p_fsbue_idcustomer|| ''' ';
END IF;

IF p_fsaqh_referencenumber IS NOT NULL THEN
l_query := l_query || ' AND fsaqh.fsaqh_referencenumber = '''|| p_fsaqh_referencenumber|| '''';
END IF;

IF p_fsaqh_quotationtype IS NOT NULL THEN
l_query := l_query || ' AND fsaqh.fsaqh_quotationtype = '''|| p_fsaqh_quotationtype|| '''';
END IF;

IF p_fsaqh_shipmenttype IS NOT NULL THEN
l_query := l_query || ' AND fsaqh.fsaqh_shipmenttype = '''|| p_fsaqh_shipmenttype|| '''';
END IF;

IF p_fsbue_idcarrier IS NOT NULL THEN
l_query := l_query || ' AND EXISTS (SELECT ''Y'' FROM fsaqc_airquotationcarrier fsaqc
WHERE fsaqc.fsaqh_id=fssqh.fsaqh_id
AND fsaqc.fsbue_idcarrier='''|| p_fsbue_idcarrier|| ''')';
END IF;

IF p_fsart_idfromAirport IS NOT NULL THEN
l_query := l_query || ' AND fsaqt.fsart_idfromAirport ='''|| p_fsart_idfromAirport|| '''';
END IF;

IF p_fsart_idtoAirport IS NOT NULL THEN
l_query := l_query || ' AND fsaqt.fsart_idtoAirport ='''|| p_fsart_idtoAirport|| '''';
END IF;

IF p_partner IS NOT NULL THEN
l_query := l_query || ' AND fsaqh.fsofo_idpartner = '''|| p_partner|| '''';
END IF;

IF p_quotationparty IS NOT NULL THEN
l_query := l_query || ' AND fsaqh.fsaqh_quotationpartytype = '''|| p_quotationparty|| '''';
END IF;

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

p_fsaqh_count := 0;

IF p_page_index > 0 THEN

EXECUTE IMMEDIATE l_query_cnt INTO p_fsaqh_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_fsaqh_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_fsaqh_Prc;

PROCEDURE Get_airquotationratedetails_Prc ( --Quotation creation
p_fsaqh_id IN fsaqh_airquotationheader.fsaqh_id%TYPE,
p_fsofo_id IN fsaqh_airquotationheader.fsofo_id%TYPE,
p_airquotationrateheader_details OUT FS_AirQuotationManagement.t_list,
p_airquotationrate_details OUT FS_AirQuotationManagement.t_list,
p_issuccess OUT fsaqh_airquotationheader.fsaqh_status%TYPE,
p_message OUT FS_AirQuotationManagement.t_list)
AS
BEGIN
OPEN p_airquotationrateheader_details FOR
SELECT fsaqh.fsaqh_id AS fsaqh_id,
fsaqh.fsbue_idcustomer AS fsbue_idcustomer,
fsbue_customer.fsbue_name AS fsaqh_customername,
fsaqh.fsaqh_customerdetails AS fsbue_customerdetail,
quotationpartytype.fsgec_description AS quotationpartytype_description ,
fsaqh.fsaqh_quotationpartytype AS fsaqh_quotationpartytype,
fsaqh.fsofo_idpartner AS fsofo_idpartner,
fsofo_partner.fsofo_organizationname AS parter_name,
--fsbua.fsbua_buildingname||' '||fsbua.fsbua_street||' '||fsbua.fsbua_pobox||' '||fsbua.fsbua_phone||' '||fsbua.fsbua_fax AS fsbue_customerdetail,
fsaqh.fsaqh_quotationtype AS fsaqh_quotationtype,
fsgec_quotationtype.fsgec_description AS fsaqh_quotationtype_description,
fsaqh.fsaqh_shipmenttype AS fsaqh_shipmenttype,
fsgec_shipmenttype.fsgec_description AS fsaqh_shipmenttype_description,
fsaqh.fsaqh_minimumconfirmedquantity AS fsaqh_minimumconfirmedquantity,
fsaqh.fsaqh_minimumconfirmedquantitytype AS fsaqh_minimumconfirmedquantitytype,
fsgec_mcq.fsgec_description AS fsaqh_mcqtype_description,
fsaqh.fsaqh_weight AS fsaqh_weight,
fsaqh.fsmea_id AS fsmea_id,
fsmea_weight.fsmea_isocode AS fsaqh_weight_unit,
fsaqh.fsaqh_volume AS fsaqh_volume,
fsaqh.fsaqh_incoterms AS fsaqh_incoterms,
fsgec_incoterms.fsgec_description AS fsaqh_incoterms_description,
fsaqh.fscry_idquotation AS fscry_idquotation,
fscry.fscry_code AS fscry_code,
fsaqh.fsaqh_isexportimport AS fsaqh_isexportimport,
fsgec_importexport.fsgec_description AS fsaqh_isexportimport_description,
(SELECT listagg (DISTINCT fsaqc.fsbue_idcarrier,',' ON overflow TRUNCATE WITH COUNT)
within GROUP (ORDER BY fsaqc.fsaqc_id)
FROM fsaqc_airquotationcarrier fsaqc
WHERE fsaqc.fsaqh_id = fsaqh.fsaqh_id) AS fsaqh_carrierid,
(SELECT listagg (DISTINCT fsbue_carrier.fsbue_name,',' ON overflow TRUNCATE WITH COUNT)
within GROUP (ORDER BY fsaqc.fsaqc_id)
FROM fsaqc_airquotationcarrier fsaqc
JOIN fsbue_businessentity fsbue_carrier ON (fsbue_carrier.fsbue_id=fsaqc.fsbue_idcarrier)
WHERE fsaqc.fsaqh_id = fsaqh.fsaqh_id) AS fsaqh_carriername,
fsaqh.fsofo_idsalesoffice AS fsofo_idsalesoffice,
fsofo.fsofo_organizationname AS sales_office,
fsaqh.fsaqh_remarks AS fsaqh_remarks,
(SELECT listagg (DISTINCT fsaqd.fsdlm_id,',' ON overflow TRUNCATE WITH COUNT)
within GROUP (ORDER BY fsaqd.fsaqd_id)
FROM fsaqd_airquotationdeal fsaqd
WHERE fsaqd.fsaqh_id = fsaqh.fsaqh_id) AS DEALID,
(SELECT listagg (DISTINCT fsdlm.fsdlm_referencenumber,',' ON overflow TRUNCATE WITH COUNT)
within GROUP (ORDER BY fsaqd.fsaqd_id)
FROM fsaqd_airquotationdeal fsaqd
JOIN fsdlm_dealmanagement fsdlm ON (fsdlm.fsdlm_id=fsaqd.fsdlm_id)
WHERE fsaqd.fsaqh_id = fsaqh.fsaqh_id) AS fsdlm_referencenumber
FROM fsaqh_airquotationheader fsaqh
LEFT JOIN fsbue_businessentity fsbue_customer ON (fsbue_customer.fsbue_id=fsaqh.fsbue_idcustomer)
LEFT JOIN FSOFO_Officeorganization fsofo ON (fsofo.fsofo_id=fsaqh.fsofo_idsalesoffice)
LEFT JOIN FSOFO_Officeorganization fsofo_partner ON (fsofo_partner.fsofo_id=fsaqh.fsofo_idpartner)
LEFT JOIN fsmea_measurement fsmea_weight ON (fsmea_weight.fsmea_id=fsaqh.fsmea_id)
JOIN fscry_currency fscry ON (fscry.fscry_id=fsaqh.fscry_idquotation)
JOIN fsgec_generalcode quotationpartytype ON (quotationpartytype.fsgec_referencename='QUOTATIONPARTYTYPE' AND quotationpartytype.fsgec_value=fsaqh.fsaqh_quotationpartytype )
JOIN FSGEC_GeneralCode fsgec_quotationtype ON (fsgec_quotationtype.fsgec_value=fsaqh.fsaqh_quotationtype AND fsgec_quotationtype.fsgec_referencename='QUOTATIONTYPE')
JOIN FSGEC_GeneralCode fsgec_shipmenttype ON (fsgec_shipmenttype.fsgec_value=fsaqh.fsaqh_shipmenttype AND fsgec_shipmenttype.fsgec_referencename='AIRSHIPMENTTYPE')
LEFT JOIN FSGEC_GeneralCode fsgec_mcq ON (fsgec_mcq.fsgec_value=fsaqh.fsaqh_minimumconfirmedquantitytype AND fsgec_mcq.fsgec_referencename='QUOTATIONMCQ')
JOIN FSGEC_GeneralCode fsgec_incoterms ON (fsgec_incoterms.fsgec_value=fsaqh.fsaqh_incoterms AND fsgec_incoterms.fsgec_referencename='BOOKINGINCOTERMS')
JOIN FSGEC_GeneralCode fsgec_importexport ON (fsgec_importexport.fsgec_value=fsaqh.fsaqh_isexportimport AND fsgec_importexport.fsgec_referencename='BOOKINGTYPE')
WHERE fsaqh.fsaqh_id=p_fsaqh_id;

OPEN p_airquotationrate_details FOR
SELECT fsart_fromport.fsart_name AS fsart_fromport,
fsaqt.fsart_idfromAirport AS fsart_idfromAirport,
fsart_toport.fsart_name AS fsart_toport,
fsaqt.fsart_idtoAirport AS fsart_idtoAirport,
(SELECT listagg (DISTINCT transhipment.fsart_name,',' ON overflow TRUNCATE WITH COUNT)
within GROUP (ORDER BY fsaqp.fsaqp_id)
FROM fsaqp_airquotationtranshipment fsaqp
JOIN fsart_airportmaster transhipment ON (transhipment.fsart_id=fsaqp.fsart_idtranshipment)
WHERE fsaqp.fsaqt_id = fsaqt.fsaqt_id) AS fsprt_transhipment,
(SELECT listagg (DISTINCT fsaqp.fsart_idtranshipment,',' ON overflow TRUNCATE WITH COUNT)
within GROUP (ORDER BY fsaqp.fsaqp_id)
FROM fsaqp_airquotationtranshipment fsaqp
WHERE fsaqp.fsaqt_id = fsaqt.fsaqt_id) AS transhipmentid,
(SELECT listagg (DISTINCT fsaqe.fsaqe_cargotype,',' ON overflow TRUNCATE WITH COUNT)
within GROUP (ORDER BY fsaqe.fsaqe_id)
FROM fsaqe_airquotationequipment fsaqe
WHERE fsaqe.fsaqt_id = fsaqt.fsaqt_id) AS fsaqe_cargotype,
(SELECT listagg (DISTINCT fsaqe.fseqt_sizetypeuld,',' ON overflow TRUNCATE WITH COUNT)
within GROUP (ORDER BY fsaqe.fsaqe_id)
FROM fsaqe_airquotationequipment fsaqe
WHERE fsaqe.fsaqt_id = fsaqt.fsaqt_id) AS fseqt_sizetypeuld,
(SELECT listagg (DISTINCT fsaqm.fscmg_id,',' ON overflow TRUNCATE WITH COUNT)
within GROUP (ORDER BY fsaqm.fsaqm_id)
FROM fsaqm_airquotationcommodity fsaqm
WHERE fsaqm.fsaqt_id = fsaqt.fsaqt_id) AS commodityid,
(SELECT listagg (DISTINCT fscmg.fscmg_code,',' ON overflow TRUNCATE WITH COUNT)
within GROUP (ORDER BY fsaqm.fsaqm_id)
FROM fsaqm_airquotationcommodity fsaqm
JOIN fscmg_commoditygroup fscmg ON (fscmg.fscmg_id=fsaqm.fscmg_id)
WHERE fsaqm.fsaqt_id = fsaqt.fsaqt_id) AS fscmg_groupcode
FROM fsaqt_airquotationtransportstation fsaqt
JOIN fsart_airportmaster fsart_fromport ON (fsart_fromport.fsart_id = fsaqt.fsart_idfromAirport)
JOIN fsart_airportmaster fsart_toport ON (fsart_toport.fsart_id = fsaqt.fsart_idtoAirport)
WHERE fsaqt.fsaqh_id=p_fsaqh_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 FSDUL_Dual;

EXCEPTION
WHEN OTHERS THEN
p_issuccess := fs_constants.k_failure;
fs_exception.raise_exception;

END Get_airquotationratedetails_Prc;

PROCEDURE Get_Ratefromtariff_Prc ( --get rates from tariff
p_fsofo_id IN fsaqh_airquotationheader.fsofo_id%TYPE,
p_fsbue_idcustomer IN fsaqh_airquotationheader.fsbue_idcustomer%TYPE,
p_fssqh_shipmenttype IN fsaqh_airquotationheader.fsaqh_shipmenttype%TYPE,
p_fsbue_idcarrier IN fsbue_businessentity.fsbue_name%TYPE,
p_route IN fsdcl_documentationchangelog.fsdcl_content%TYPE,
p_header_details OUT FS_AirQuotationManagement.t_list,
p_Tariff_details OUT FS_AirQuotationManagement.t_list,
p_weightdetails OUT FS_AirQuotationManagement.t_list,
p_issuccess OUT fsaqh_airquotationheader.fsaqh_shipmenttype%TYPE,
p_message OUT FS_AirQuotationManagement.t_list)
AS

v_route fsdcl_documentationchangelog.fsdcl_content%TYPE;
BEGIN

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

OPEN p_header_details FOR
WITH route_details AS
(SELECT jt.Fromportid,jt.Toportid,jt.CargoType,jt.UldType,
LISTAGG(DISTINCT jt.CommodityGroup, ',') WITHIN GROUP (ORDER BY 1) CommodityGroup,
LISTAGG(DISTINCT jt.Via, ',') WITHIN GROUP (ORDER BY 1) Viaport
FROM
JSON_TABLE(v_route,'$[*]'
COLUMNS (
Fromportid VARCHAR2(100) PATH '$.From',
Toportid VARCHAR2(100) PATH '$.To' ,
CargoType VARCHAR2(100) PATH '$.CargoType' ,
UldType VARCHAR2(100) PATH '$.UldType' ,
NESTED PATH '$.CommodityGroup[*]'
COLUMNS (CommodityGroup VARCHAR2(100) PATH '$.Id') ,
NESTED PATH '$.Via[*]'
COLUMNS (Via VARCHAR2(100) PATH '$.Id')
)
) jt
GROUP BY jt.Fromportid,jt.Toportid,jt.CargoType,jt.UldType)
SELECT fsath.fsath_id AS fsath_id,
fsatt.fsatt_id AS fsatt_id,
fsath.fsbue_idline AS fsbue_idline,
fsbue.fsbue_name AS fsbue_name,
fsart_fromport.fsart_name AS fsart_fromport,
fsatt.fsart_idfromAirport AS fsart_idfromAirport,
fsart_toport.fsart_name AS fsart_toport,
fsatt.fsart_idtoAirport AS fsart_idtoAirport,
fsatt.fsatt_transitdays AS fsatt_transitdays,
(SELECT listagg (DISTINCT fsatp.fsart_idtranshipment,',' ON overflow TRUNCATE WITH COUNT)
within GROUP (ORDER BY fsatp.fsatp_id)
FROM fsatp_airtarifftranshipment fsatp
WHERE fsatp.fsatt_id = fsatt.fsatt_id) AS transhipmentid,
(SELECT listagg (DISTINCT transhipment.fsart_name,',' ON overflow TRUNCATE WITH COUNT)
within GROUP (ORDER BY fsatp.fsatp_id)
FROM fsatp_airtarifftranshipment fsatp
JOIN fsart_airportmaster transhipment ON (transhipment.fsart_id=fsatp.fsart_idtranshipment)
WHERE fsatp.fsatt_id = fsatt.fsatt_id) AS transhipment,
(SELECT listagg (DISTINCT fsatf.fsatf_dayoffrequency,',' ON overflow TRUNCATE WITH COUNT)
within GROUP (ORDER BY fsatf.fsatf_id)
FROM fsatf_airtarifftransportfrequency fsatf
WHERE fsatf.fsatt_id=fsatt.fsatt_id
) AS fsatf_dayoffrequency,
(SELECT listagg (DISTINCT fsgec.fsgec_description,',' ON overflow TRUNCATE WITH COUNT)
within GROUP (ORDER BY fsatf.fsatf_id)
FROM fsatf_airtarifftransportfrequency fsatf
JOIN fsgec_generalcode fsgec ON (fsgec.fsgec_referencename='FREQUENCY' AND fsgec.fsgec_value=fsatf.fsatf_dayoffrequency)
WHERE fsatf.fsatt_id=fsatt.fsatt_id
) AS fsatf_dayoffrequency_description
FROM fsath_airtariffheader fsath
JOIN fsatt_airtarifftransport fsatt ON (fsatt.fsath_id=fsath.fsath_id)
JOIN fsbue_businessentity fsbue ON (fsbue.fsbue_id=fsath.fsbue_idline)
JOIN fsart_airportmaster fsart_fromport ON (fsart_fromport.fsart_id = fsatt.fsart_idfromAirport)
JOIN fsart_airportmaster fsart_toport ON (fsart_toport.fsart_id = fsatt.fsart_idtoAirport)
JOIN route_details ON (route_details.Fromportid=fsatt.fsart_idfromairport AND route_details.Toportid=fsatt.fsart_idtoairport)
WHERE fsath.fsath_status ='P'
AND (fsath.fsath_referencenumber,fsath.fsath_versionnumber) IN (SELECT fsath.fsath_referencenumber,MAX(fsath.fsath_versionnumber)
FROM fsath_airtariffheader fsath
GROUP BY fsath.fsath_referencenumber)
AND (p_fsbue_idcarrier IS NULL OR fsath.fsbue_idline IN (SELECT REGEXP_SUBSTR (p_fsbue_idcarrier, '[^,]+', 1,LEVEL) AS carrier FROM FSDUL_DUAL
CONNECT BY REGEXP_SUBSTR (p_fsbue_idcarrier,'[^,]+', 1, LEVEL) IS NOT NULL))
AND (p_fsbue_idcustomer IS NULL OR EXISTS (SELECT 'X'
FROM fsatb_airtariffcustomer fsatb
WHERE fsatb.fsath_id=fsath.fsath_id
AND fsatb.fsbue_idcustomer=p_fsbue_idcustomer))
AND EXISTS (SELECT 'X'
FROM fsatr_airtariffrate fsatr
WHERE fsatr.fsatt_id=fsatt.fsatt_id
AND fsatr.fsatr_ratetype=p_fssqh_shipmenttype
AND (route_details.CargoType IS NULL OR fsatr.fsatr_cargouldtype IN (SELECT REGEXP_SUBSTR (route_details.CargoType, '[^,]+', 1,LEVEL) AS cargouldtype FROM FSDUL_DUAL
CONNECT BY REGEXP_SUBSTR (route_details.CargoType,'[^,]+', 1, LEVEL) IS NOT NULL))
AND (route_details.UldType IS NULL OR fsatr.fseqt_sizetypeuld IN (SELECT REGEXP_SUBSTR (route_details.UldType, '[^,]+', 1,LEVEL) AS uldtype FROM FSDUL_DUAL
CONNECT BY REGEXP_SUBSTR (route_details.UldType,'[^,]+', 1, LEVEL) IS NOT NULL))
AND (route_details.CommodityGroup IS NULL OR fsatr.fscmg_id IN (SELECT REGEXP_SUBSTR (route_details.CommodityGroup, '[^,]+', 1,LEVEL) AS fscmg_id FROM FSDUL_DUAL
CONNECT BY REGEXP_SUBSTR (route_details.CommodityGroup,'[^,]+', 1, LEVEL) IS NOT NULL)))
AND (EXISTS (SELECT 'Y' FROM fsatp_airtarifftranshipment fsatt_transhipment
WHERE fsatt_transhipment.fsatt_id=fsatt.fsatt_id
AND fsatt_transhipment.fsart_idtranshipment IN (SELECT REGEXP_SUBSTR (route_details.viaport, '[^,]+', 1,LEVEL) AS viaport FROM FSDUL_DUAL
CONNECT BY REGEXP_SUBSTR (route_details.viaport,'[^,]+', 1, LEVEL) IS NOT NULL)) OR (route_details.viaport IS NULL));

OPEN p_Tariff_details FOR
WITH route_details AS
(SELECT jt.Fromportid,jt.Toportid,jt.CargoType,jt.UldType,
LISTAGG(DISTINCT jt.CommodityGroup, ',') WITHIN GROUP (ORDER BY 1) CommodityGroup,
LISTAGG(DISTINCT jt.Via, ',') WITHIN GROUP (ORDER BY 1) Viaport
FROM
JSON_TABLE(v_route,'$[*]'
COLUMNS (
Fromportid VARCHAR2(100) PATH '$.From',
Toportid VARCHAR2(100) PATH '$.To' ,
CargoType VARCHAR2(100) PATH '$.CargoType' ,
UldType VARCHAR2(100) PATH '$.UldType' ,
NESTED PATH '$.CommodityGroup[*]'
COLUMNS (CommodityGroup VARCHAR2(100) PATH '$.Id') ,
NESTED PATH '$.Via[*]'
COLUMNS (Via VARCHAR2(100) PATH '$.Id')
)
) jt
GROUP BY jt.Fromportid,jt.Toportid,jt.CargoType,jt.UldType)
SELECT TO_CHAR(fsath.fsath_id) AS fsath_id,
fsatt.fsatt_id AS fsatt_id,
fsatr.fsatr_id AS fsatr_id,
fschg.fschg_id AS fschg_id,
fschg.fschg_code AS fschg_code,
fschg.fschg_description AS fschg_description,
fsatr.fsatr_cargouldtype AS fsatr_cargouldtype,
fsgec.fsgec_description AS fsatr_cargouldtype_description,
fsatr.fseqt_sizetypeuld AS fseqt_sizetypeuld,
fsatr.fscmg_id AS fscmg_id,
fscmg.fscmg_code AS fscmg_code,
fscmg.fscmg_description AS fscmg_description,
fsatr.fsatr_buyingrate AS fsatr_buyingrate,
fsatr.fsatr_markuptype AS fsatr_markuptype,
fsgec_markuptype.fsgec_description AS fsatr_markuptype_description,
fsatr.fsatr_minimummarkuptype As fsatr_minimummarkuptype,
fsgec_minmarkuptype.fsgec_description AS fsatr_minmarkuptype_description,
fsatr.fsatr_minimumsellingrate AS fsatr_minimumsellingrate,
fsatr.fsatr_minimumbuyingrate AS fsatr_minimumbuyingrate,
fsatr.fsatr_minimummarkupvalue AS fsatr_minimummarkupvalue,
fsatr.fsatr_markupvalue AS fsatr_markupvalue,
fsatr.fsatr_sellingrate AS fsatr_sellingrate,
fsatr.fsatr_additionaldayfee AS fsatr_additionaldayfee,
fsatr.fsatr_pivotweight AS fsatr_pivotweight,
(SELECT listagg (DISTINCT fsati.fschg_id,',' ON overflow TRUNCATE WITH COUNT)
within GROUP (ORDER BY fsati.fsati_id)
FROM fsati_airtariffinclusivecharge fsati
WHERE fsati.fsatr_id = fsatr.fsatr_id) AS inclusive_chargeid,
(SELECT listagg (DISTINCT fschg.fschg_code,',' ON overflow TRUNCATE WITH COUNT)
within GROUP (ORDER BY fsati.fsati_id)
FROM fsati_airtariffinclusivecharge fsati
JOIN fschg_chargemaster fschg ON (fschg.fschg_id=fsati.fschg_id)
WHERE fsati.fsatr_id = fsatr.fsatr_id) AS inclusive_chargedescription,
fsatr.fscry_id AS fscry_id,
fscry.fscry_code AS fscry_code,
fsatr.fsatr_remarks AS fsatr_remarks,
fsatr.fsatr_ispax AS fsatr_ispax,
fsgec_pax.fsgec_description AS fsatr_ispax_description,
fsath.fsbue_idline AS fsbue_idline,
fsath.fsath_referencenumber AS fsath_referencenumber,
fsatt.fsart_idfromAirport AS fsart_idfromAirport,
fsatt.fsart_idtoAirport AS fsart_idtoAirport,
(SELECT listagg (DISTINCT fsatp.fsart_idtranshipment,',' ON overflow TRUNCATE WITH COUNT)
within GROUP (ORDER BY fsatp.fsatp_id)
FROM fsatp_airtarifftranshipment fsatp
WHERE fsatp.fsatt_id = fsatt.fsatt_id) AS transhipmentid
FROM fsath_airtariffheader fsath
JOIN fsatt_airtarifftransport fsatt ON (fsatt.fsath_id=fsath.fsath_id)
JOIN fsatr_airtariffrate fsatr ON (fsatr.fsatt_id=fsatt.fsatt_id)
LEFT JOIN fsgec_generalcode fsgec ON (fsgec.fsgec_referencename='AIRTARIFFCARGOTYPE' AND fsgec.fsgec_value=fsatr.fsatr_cargouldtype)
JOIN fschg_chargemaster fschg ON (fschg.fschg_id=fsatr.fschg_id)
JOIN fscmg_commoditygroup fscmg ON (fscmg.fscmg_id=fsatr.fscmg_id)
JOIN fscry_currency fscry ON (fscry.fscry_id =fsatr.fscry_id)
JOIN fsgec_generalcode fsgec_pax ON (fsgec_pax.fsgec_value=fsatr.fsatr_ispax AND fsgec_pax.fsgec_referencename='AIRTARIFFPAX')
JOIN fsgec_generalcode fsgec_markuptype ON (fsgec_markuptype.fsgec_referencename='AIRMARKUPTYPE' AND fsgec_markuptype.fsgec_value=fsatr.fsatr_markuptype )
LEFT JOIN fsgec_generalcode fsgec_minmarkuptype ON (fsgec_minmarkuptype.fsgec_referencename='MARKUPTYPE' AND fsgec_minmarkuptype.fsgec_value=fsatr.fsatr_minimummarkuptype )
JOIN route_details ON (route_details.Fromportid=fsatt.fsart_idfromairport AND route_details.Toportid=fsatt.fsart_idtoairport)
WHERE fsath.fsath_status ='P'
AND (fsath.fsath_referencenumber,fsath.fsath_versionnumber) IN (SELECT fsath.fsath_referencenumber,MAX(fsath.fsath_versionnumber)
FROM fsath_airtariffheader fsath
GROUP BY fsath.fsath_referencenumber)
AND (p_fsbue_idcarrier IS NULL OR fsath.fsbue_idline IN (SELECT REGEXP_SUBSTR (p_fsbue_idcarrier, '[^,]+', 1,LEVEL) AS carrier FROM FSDUL_DUAL
CONNECT BY REGEXP_SUBSTR (p_fsbue_idcarrier,'[^,]+', 1, LEVEL) IS NOT NULL))
AND (p_fsbue_idcustomer IS NULL OR EXISTS (SELECT 'X'
FROM fsatb_airtariffcustomer fsatb
WHERE fsatb.fsath_id=fsath.fsath_id
AND fsatb.fsbue_idcustomer=p_fsbue_idcustomer)
)
AND fsatr.fsatr_ratetype=p_fssqh_shipmenttype
AND (route_details.CargoType IS NULL OR fsatr.fsatr_cargouldtype IN (SELECT REGEXP_SUBSTR (route_details.CargoType, '[^,]+', 1,LEVEL) AS cargouldtype FROM FSDUL_DUAL
CONNECT BY REGEXP_SUBSTR (route_details.CargoType,'[^,]+', 1, LEVEL) IS NOT NULL))
AND (route_details.UldType IS NULL OR fsatr.fseqt_sizetypeuld IN (SELECT REGEXP_SUBSTR (route_details.UldType, '[^,]+', 1,LEVEL) AS uldtype FROM FSDUL_DUAL
CONNECT BY REGEXP_SUBSTR (route_details.UldType,'[^,]+', 1, LEVEL) IS NOT NULL))
AND (route_details.CommodityGroup IS NULL OR fsatr.fscmg_id IN (SELECT REGEXP_SUBSTR (route_details.CommodityGroup, '[^,]+', 1,LEVEL) AS fscmg_id FROM FSDUL_DUAL
CONNECT BY REGEXP_SUBSTR (route_details.CommodityGroup,'[^,]+', 1, LEVEL) IS NOT NULL))
AND (EXISTS (SELECT 'Y' FROM fsatp_airtarifftranshipment fsatt_transhipment
WHERE fsatt_transhipment.fsatt_id=fsatt.fsatt_id
AND fsatt_transhipment.fsart_idtranshipment IN (SELECT REGEXP_SUBSTR (route_details.viaport, '[^,]+', 1,LEVEL) AS viaport FROM FSDUL_DUAL
CONNECT BY REGEXP_SUBSTR (route_details.viaport,'[^,]+', 1, LEVEL) IS NOT NULL)) OR (route_details.viaport IS NULL))
UNION ALL--
SELECT TO_CHAR(fsath.fsath_id) AS fsath_id,
fsatt.fsatt_id AS fsatt_id,
fsatr.fsatr_id AS fsatr_id,
fschg.fschg_id AS fschg_id,
fschg.fschg_code AS fschg_code,
fschg.fschg_description AS fschg_description,
fsatr.fsatr_cargouldtype AS fsatr_cargouldtype,
fsgec.fsgec_description AS fsatr_cargouldtype_description,
fsatr.fseqt_sizetypeuld AS fseqt_sizetypeuld,
fsatr.fscmg_id AS fscmg_id,
fscmg.fscmg_code AS fscmg_code,
fscmg.fscmg_description AS fscmg_description,
fsatr.fsatr_buyingrate AS fsatr_buyingrate,
fsatr.fsatr_markuptype AS fsatr_markuptype,
fsgec_markuptype.fsgec_description AS fsatr_markuptype_description,
fsatr.fsatr_minimummarkuptype As fsatr_minimummarkuptype,
fsgec_minmarkuptype.fsgec_description AS fsatr_minmarkuptype_description,
fsatr.fsatr_minimumsellingrate AS fsatr_minimumsellingrate,
fsatr.fsatr_minimumbuyingrate AS fsatr_minimumbuyingrate,
fsatr.fsatr_minimummarkupvalue AS fsatr_minimummarkupvalue,
fsatr.fsatr_markupvalue AS fsatr_markupvalue,
fsatr.fsatr_sellingrate AS fsatr_sellingrate,
fsatr.fsatr_additionaldayfee AS fsatr_additionaldayfee,
fsatr.fsatr_pivotweight AS fsatr_pivotweight,
(SELECT listagg (DISTINCT fsati.fschg_id,',' ON overflow TRUNCATE WITH COUNT)
within GROUP (ORDER BY fsati.fsati_id)
FROM fsati_airtariffinclusivecharge fsati
WHERE fsati.fsatr_id = fsatr.fsatr_id) AS inclusive_chargeid,
(SELECT listagg (DISTINCT fschg.fschg_code,',' ON overflow TRUNCATE WITH COUNT)
within GROUP (ORDER BY fsati.fsati_id)
FROM fsati_airtariffinclusivecharge fsati
JOIN fschg_chargemaster fschg ON (fschg.fschg_id=fsati.fschg_id)
WHERE fsati.fsatr_id = fsatr.fsatr_id) AS inclusive_chargedescription,
fsatr.fscry_id AS fscry_id,
fscry.fscry_code AS fscry_code,
fsatr.fsatr_remarks AS fsatr_remarks,
fsatr.fsatr_ispax AS fsatr_ispax,
fsgec_pax.fsgec_description AS fsatr_ispax_description,
fsath.fsbue_idline AS fsbue_idline,
fsath.fsath_referencenumber AS fsath_referencenumber,
fsatt.fsart_idfromAirport AS fsart_idfromAirport,
fsatt.fsart_idtoAirport AS fsart_idtoAirport,
(SELECT listagg (DISTINCT fsatp.fsart_idtranshipment,',' ON overflow TRUNCATE WITH COUNT)
within GROUP (ORDER BY fsatp.fsatp_id)
FROM fsatp_airtarifftranshipment fsatp
WHERE fsatp.fsatt_id = fsatt.fsatt_id) AS transhipmentid
FROM fsath_airtariffheader fsath
JOIN fsatt_airtarifftransport fsatt ON (fsatt.fsath_id=fsath.fsath_id)
JOIN fsatr_airtariffrate fsatr ON (fsatr.fsatt_id=fsatt.fsatt_id)
LEFT JOIN fsgec_generalcode fsgec ON (fsgec.fsgec_referencename='AIRTARIFFCARGOTYPE' AND fsgec.fsgec_value=fsatr.fsatr_cargouldtype)
JOIN fschg_chargemaster fschg ON (fschg.fschg_id=fsatr.fschg_id)
JOIN fscmg_commoditygroup fscmg ON (fscmg.fscmg_id=fsatr.fscmg_id)
JOIN fscry_currency fscry ON (fscry.fscry_id =fsatr.fscry_id)
JOIN fsgec_generalcode fsgec_pax ON (fsgec_pax.fsgec_value=fsatr.fsatr_ispax AND fsgec_pax.fsgec_referencename='AIRTARIFFPAX')
JOIN fsgec_generalcode fsgec_markuptype ON (fsgec_markuptype.fsgec_referencename='AIRMARKUPTYPE' AND fsgec_markuptype.fsgec_value=fsatr.fsatr_markuptype )
LEFT JOIN fsgec_generalcode fsgec_minmarkuptype ON (fsgec_minmarkuptype.fsgec_referencename='MARKUPTYPE' AND fsgec_minmarkuptype.fsgec_value=fsatr.fsatr_minimummarkuptype )
JOIN route_details ON (route_details.Fromportid=fsatt.fsart_idfromairport AND route_details.Toportid=fsatt.fsart_idtoairport)
WHERE fsath.fsath_status ='P'
AND (fsath.fsath_referencenumber,fsath.fsath_versionnumber) IN (SELECT fsath.fsath_referencenumber,MAX(fsath.fsath_versionnumber)
FROM fsath_airtariffheader fsath
GROUP BY fsath.fsath_referencenumber)
AND (p_fsbue_idcarrier IS NULL OR fsath.fsbue_idline IN (SELECT REGEXP_SUBSTR (p_fsbue_idcarrier, '[^,]+', 1,LEVEL) AS carrier FROM FSDUL_DUAL
CONNECT BY REGEXP_SUBSTR (p_fsbue_idcarrier,'[^,]+', 1, LEVEL) IS NOT NULL))
AND (p_fsbue_idcustomer IS NULL OR NOT EXISTS (SELECT 'X'
FROM fsatb_airtariffcustomer fsatb
WHERE fsatb.fsath_id=fsath.fsath_id
AND fsatb.fsbue_idcustomer=p_fsbue_idcustomer)
)
AND fsatr.fsatr_ratetype=p_fssqh_shipmenttype
AND (route_details.CargoType IS NULL OR fsatr.fsatr_cargouldtype IN (SELECT REGEXP_SUBSTR (route_details.CargoType, '[^,]+', 1,LEVEL) AS cargouldtype FROM FSDUL_DUAL
CONNECT BY REGEXP_SUBSTR (route_details.CargoType,'[^,]+', 1, LEVEL) IS NOT NULL))
AND (route_details.UldType IS NULL OR fsatr.fseqt_sizetypeuld IN (SELECT REGEXP_SUBSTR (route_details.UldType, '[^,]+', 1,LEVEL) AS uldtype FROM FSDUL_DUAL
CONNECT BY REGEXP_SUBSTR (route_details.UldType,'[^,]+', 1, LEVEL) IS NOT NULL))
AND (route_details.CommodityGroup IS NULL OR fsatr.fscmg_id IN (SELECT REGEXP_SUBSTR (route_details.CommodityGroup, '[^,]+', 1,LEVEL) AS fscmg_id FROM FSDUL_DUAL
CONNECT BY REGEXP_SUBSTR (route_details.CommodityGroup,'[^,]+', 1, LEVEL) IS NOT NULL))
AND (EXISTS (SELECT 'Y' FROM fsatp_airtarifftranshipment fsatt_transhipment
WHERE fsatt_transhipment.fsatt_id=fsatt.fsatt_id
AND fsatt_transhipment.fsart_idtranshipment IN (SELECT REGEXP_SUBSTR (route_details.viaport, '[^,]+', 1,LEVEL) AS viaport FROM FSDUL_DUAL
CONNECT BY REGEXP_SUBSTR (route_details.viaport,'[^,]+', 1, LEVEL) IS NOT NULL)) OR (route_details.viaport IS NULL))
AND NOT EXISTS (SELECT 'X'
FROM fsath_airtariffheader fsath
JOIN fsatt_airtarifftransport fsatt ON (fsatt.fsath_id=fsath.fsath_id)
JOIN fsatr_airtariffrate fsatr ON (fsatr.fsatt_id=fsatt.fsatt_id)
LEFT JOIN fsgec_generalcode fsgec ON (fsgec.fsgec_referencename='AIRTARIFFCARGOTYPE' AND fsgec.fsgec_value=fsatr.fsatr_cargouldtype)
JOIN fschg_chargemaster fschg ON (fschg.fschg_id=fsatr.fschg_id)
JOIN fscmg_commoditygroup fscmg ON (fscmg.fscmg_id=fsatr.fscmg_id)
JOIN fscry_currency fscry ON (fscry.fscry_id =fsatr.fscry_id)
JOIN fsgec_generalcode fsgec_pax ON (fsgec_pax.fsgec_value=fsatr.fsatr_ispax AND fsgec_pax.fsgec_referencename='AIRTARIFFPAX')
JOIN fsgec_generalcode fsgec_markuptype ON (fsgec_markuptype.fsgec_referencename='AIRMARKUPTYPE' AND fsgec_markuptype.fsgec_value=fsatr.fsatr_markuptype )
LEFT JOIN fsgec_generalcode fsgec_minmarkuptype ON (fsgec_minmarkuptype.fsgec_referencename='MARKUPTYPE' AND fsgec_minmarkuptype.fsgec_value=fsatr.fsatr_minimummarkuptype )
JOIN route_details ON (route_details.Fromportid=fsatt.fsart_idfromairport AND route_details.Toportid=fsatt.fsart_idtoairport)
WHERE fsath.fsath_status ='P'
AND (fsath.fsath_referencenumber,fsath.fsath_versionnumber) IN (SELECT fsath.fsath_referencenumber,MAX(fsath.fsath_versionnumber)
FROM fsath_airtariffheader fsath
GROUP BY fsath.fsath_referencenumber)
AND (p_fsbue_idcarrier IS NULL OR fsath.fsbue_idline IN (SELECT REGEXP_SUBSTR (p_fsbue_idcarrier, '[^,]+', 1,LEVEL) AS carrier FROM FSDUL_DUAL
CONNECT BY REGEXP_SUBSTR (p_fsbue_idcarrier,'[^,]+', 1, LEVEL) IS NOT NULL))
AND EXISTS (SELECT 'X'
FROM fsatb_airtariffcustomer fsatb
WHERE fsatb.fsath_id=fsath.fsath_id
AND fsatb.fsbue_idcustomer=p_fsbue_idcustomer)
)
AND fsatr.fsatr_ratetype=p_fssqh_shipmenttype
AND (route_details.CargoType IS NULL OR fsatr.fsatr_cargouldtype IN (SELECT REGEXP_SUBSTR (route_details.CargoType, '[^,]+', 1,LEVEL) AS cargouldtype FROM FSDUL_DUAL
CONNECT BY REGEXP_SUBSTR (route_details.CargoType,'[^,]+', 1, LEVEL) IS NOT NULL))
AND (route_details.UldType IS NULL OR fsatr.fseqt_sizetypeuld IN (SELECT REGEXP_SUBSTR (route_details.UldType, '[^,]+', 1,LEVEL) AS uldtype FROM FSDUL_DUAL
CONNECT BY REGEXP_SUBSTR (route_details.UldType,'[^,]+', 1, LEVEL) IS NOT NULL))
AND (route_details.CommodityGroup IS NULL OR fsatr.fscmg_id IN (SELECT REGEXP_SUBSTR (route_details.CommodityGroup, '[^,]+', 1,LEVEL) AS fscmg_id FROM FSDUL_DUAL
CONNECT BY REGEXP_SUBSTR (route_details.CommodityGroup,'[^,]+', 1, LEVEL) IS NOT NULL))
AND (EXISTS (SELECT 'Y' FROM fsatp_airtarifftranshipment fsatt_transhipment
WHERE fsatt_transhipment.fsatt_id=fsatt.fsatt_id
AND fsatt_transhipment.fsart_idtranshipment IN (SELECT REGEXP_SUBSTR (route_details.viaport, '[^,]+', 1,LEVEL) AS viaport FROM FSDUL_DUAL
CONNECT BY REGEXP_SUBSTR (route_details.viaport,'[^,]+', 1, LEVEL) IS NOT NULL)) OR (route_details.viaport IS NULL))
UNION ALL
SELECT fsast.fsast_uigroupingreference AS fsath_id,
fsast.fsast_id AS fsatt_id,
fsasr.fsasr_id AS fsatr_id,
fschg.fschg_id AS fschg_id,
fschg.fschg_code AS fschg_code,
fschg.fschg_description AS fschg_description,
NULL AS fsatr_cargouldtype,
NULL AS fsatr_cargouldtype_description,
NULL AS fseqt_sizetypeuld,
fsasr.fscmg_id AS fscmg_id,
fscmg.fscmg_code AS fscmg_code,
fscmg.fscmg_description AS fscmg_description,
NULL AS fsatr_buyingrate,
fsasr.fsasr_calculationprocedure AS fsatr_markuptype,
fsgec_calcprc.fsgec_description AS fsatr_markuptype_description,
NULL AS fsatr_minimummarkuptype,
NULL AS fsatr_minmarkuptype_description,
NULL AS fsatr_minimumsellingrate,
NULL AS fsatr_minimumbuyingrate,
NULL AS fsatr_minimummarkupvalue,
fsasr.fsasr_value AS fsatr_markupvalue,
fsasr.fsasr_rate AS fsatr_sellingrate,
NULL AS fsatr_additionaldayfee,
NULL AS fsatr_pivotweight,
NULL AS inclusive_chargeid,
NULL AS inclusive_chargedescription,
fsasr.fscry_id AS fscry_id,
fscry.fscry_code AS fscry_code,
fsasr.fsasr_remarks AS fsasr_remarks,
NULL AS fsatr_ispax,
NULL AS fsatr_ispax_description,
fsasr.fsbue_idcarrier AS fsbue_idline,
NULL AS fsath_referencenumber,
fsast.fsart_idfromairport AS fsart_idfromAirport,
fsast.fsart_idtoairport AS fsart_idtoAirport,
NULL AS transhipmentid
FROM fsast_airsurchargetransport fsast
JOIN fsasr_airsurchargerate fsasr ON (fsast.fsast_id=fsasr.fsast_id)
JOIN fschg_chargemaster fschg ON (fschg.fschg_id=fsasr.fschg_id)
JOIN fscmg_commoditygroup fscmg ON (fscmg.fscmg_id=fsasr.fscmg_id)
JOIN fscry_currency fscry ON (fscry.fscry_id =fsasr.fscry_id)
LEFT JOIN FSGEC_GeneralCode fsgec_calcprc ON (fsgec_calcprc.fsgec_referencename = 'MARKUPTYPE' AND fsgec_calcprc.fsgec_value = fsasr.fsasr_calculationprocedure)
JOIN route_details ON (route_details.Fromportid=fsast.fsart_idfromairport AND route_details.Toportid=fsast.fsart_idtoairport)
WHERE fsasr.fsasr_shipmenttype = p_fssqh_shipmenttype
AND (p_fsbue_idcarrier IS NULL OR fsasr.fsbue_idcarrier IN (SELECT REGEXP_SUBSTR (p_fsbue_idcarrier, '[^,]+', 1,LEVEL) AS carrier FROM FSDUL_DUAL
CONNECT BY REGEXP_SUBSTR (p_fsbue_idcarrier,'[^,]+', 1, LEVEL) IS NOT NULL))
AND (route_details.CommodityGroup IS NULL OR fsasr.fscmg_id IN (SELECT REGEXP_SUBSTR (route_details.CommodityGroup, '[^,]+', 1,LEVEL) AS commodity FROM FSDUL_DUAL
CONNECT BY REGEXP_SUBSTR (route_details.CommodityGroup,'[^,]+', 1, LEVEL) IS NOT NULL)) ;

OPEN p_weightdetails FOR
WITH route_details AS
(SELECT jt.Fromportid,jt.Toportid,jt.CargoType,jt.UldType,
LISTAGG(DISTINCT jt.CommodityGroup, ',') WITHIN GROUP (ORDER BY 1) CommodityGroup,
LISTAGG(DISTINCT jt.Via, ',') WITHIN GROUP (ORDER BY 1) Viaport
FROM
JSON_TABLE(v_route,'$[*]'
COLUMNS (
Fromportid VARCHAR2(100) PATH '$.From',
Toportid VARCHAR2(100) PATH '$.To' ,
CargoType VARCHAR2(100) PATH '$.CargoType' ,
UldType VARCHAR2(100) PATH '$.UldType' ,
NESTED PATH '$.CommodityGroup[*]'
COLUMNS (CommodityGroup VARCHAR2(100) PATH '$.Id') ,
NESTED PATH '$.Via[*]'
COLUMNS (Via VARCHAR2(100) PATH '$.Id')
)
) jt
GROUP BY jt.Fromportid,jt.Toportid,jt.CargoType,jt.UldType)
SELECT fsatr.fsatr_id AS fsatr_id,
fsatm.fsatm_buyingrate AS fsatm_buyingrate,
fsatm.fsatm_sellingrate AS fsatm_sellingrate,
fsatm.fsatm_markupvalue AS fsatm_markupvalue,
fsatd.fsatd_weight AS fsatd_weight,
fsatm.fsatm_id AS fsatm_id
FROM fsath_airtariffheader fsath
JOIN fsatt_airtarifftransport fsatt ON (fsatt.fsath_id=fsath.fsath_id)
JOIN fsatr_airtariffrate fsatr ON (fsatr.fsatt_id=fsatt.fsatt_id)
JOIN fsatm_airtariffslabrate fsatm ON (fsatm.fsatr_id=fsatr.fsatr_id)
JOIN FSATD_AirTariffweightSlab fsatd ON (fsatd.fsatd_id=fsatm.fsatd_id)
JOIN route_details ON (route_details.Fromportid=fsatt.fsart_idfromairport AND route_details.Toportid=fsatt.fsart_idtoairport)
WHERE fsath.fsath_status ='P'
AND (p_fsbue_idcarrier IS NULL OR fsath.fsbue_idline IN (SELECT REGEXP_SUBSTR (p_fsbue_idcarrier, '[^,]+', 1,LEVEL) AS carrier FROM FSDUL_DUAL
CONNECT BY REGEXP_SUBSTR (p_fsbue_idcarrier,'[^,]+', 1, LEVEL) IS NOT NULL))
AND (p_fsbue_idcustomer IS NULL OR EXISTS (SELECT 'X'
FROM fsatb_airtariffcustomer fsatb
WHERE fsatb.fsath_id=fsath.fsath_id
AND fsatb.fsbue_idcustomer=p_fsbue_idcustomer))
AND fsatr.fsatr_ratetype=p_fssqh_shipmenttype
AND (route_details.CargoType IS NULL OR fsatr.fsatr_cargouldtype IN (SELECT REGEXP_SUBSTR (route_details.CargoType, '[^,]+', 1,LEVEL) AS cargouldtype FROM FSDUL_DUAL
CONNECT BY REGEXP_SUBSTR (route_details.CargoType,'[^,]+', 1, LEVEL) IS NOT NULL))
AND (route_details.UldType IS NULL OR fsatr.fseqt_sizetypeuld IN (SELECT REGEXP_SUBSTR (route_details.UldType, '[^,]+', 1,LEVEL) AS uldtype FROM FSDUL_DUAL
CONNECT BY REGEXP_SUBSTR (route_details.UldType,'[^,]+', 1, LEVEL) IS NOT NULL))
AND (route_details.CommodityGroup IS NULL OR fsatr.fscmg_id IN (SELECT REGEXP_SUBSTR (route_details.CommodityGroup, '[^,]+', 1,LEVEL) AS fscmg_id FROM FSDUL_DUAL
CONNECT BY REGEXP_SUBSTR (route_details.CommodityGroup,'[^,]+', 1, LEVEL) IS NOT NULL))
AND (EXISTS (SELECT 'Y' FROM fsatp_airtarifftranshipment fsatt_transhipment
WHERE fsatt_transhipment.fsatt_id=fsatt.fsatt_id
AND fsatt_transhipment.fsart_idtranshipment IN (SELECT REGEXP_SUBSTR (route_details.viaport, '[^,]+', 1,LEVEL) AS viaport FROM FSDUL_DUAL
CONNECT BY REGEXP_SUBSTR (route_details.viaport,'[^,]+', 1, LEVEL) IS NOT NULL)) OR (route_details.viaport IS NULL))
AND (fsath.fsath_referencenumber,fsath.fsath_versionnumber) IN (SELECT fsath.fsath_referencenumber,MAX(fsath.fsath_versionnumber)
FROM fsath_airtariffheader fsath
GROUP BY fsath.fsath_referencenumber);

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

PROCEDURE Get_localcharges_Prc ( --get local charges
p_fsofo_id IN fssqh_seaquotationheader.fsofo_id%TYPE,
p_fsbue_idcustomer IN fssqh_seaquotationheader.fsbue_idcustomer%TYPE,
p_fsofo_idpartner IN fssqh_seaquotationheader.fsofo_idpartner%TYPE,
p_fssqh_shipmenttype IN fssqh_seaquotationheader.fssqh_shipmenttype%TYPE,
p_fssqh_isexportimport IN fssqh_seaquotationheader.fssqh_isexportimport%TYPE,
p_route IN fsdcl_documentationchangelog.fsdcl_content%TYPE,
p_Tariff_deatils OUT FS_AirQuotationManagement.t_list,
p_issuccess OUT fssqh_seaquotationheader.fssqh_shipmenttype %TYPE,
p_message OUT FS_AirQuotationManagement.t_list)
AS
BEGIN

OPEN p_Tariff_deatils FOR
WITH route_details AS
(SELECT jt.Fromportid,jt.Toportid,jt.CargoType,jt.UldType,
LISTAGG(DISTINCT jt.CommodityGroup, ',') WITHIN GROUP (ORDER BY 1) CommodityGroup,
LISTAGG(DISTINCT jt.Via, ',') WITHIN GROUP (ORDER BY 1) Viaport
FROM
JSON_TABLE(REPLACE(p_route,'\',''),'$[*]'
COLUMNS (
Fromportid VARCHAR2(100) PATH '$.From',
Toportid VARCHAR2(100) PATH '$.To' ,
CargoType VARCHAR2(100) PATH '$.CargoType' ,
UldType VARCHAR2(100) PATH '$.UldType' ,
NESTED PATH '$.CommodityGroup[*]'
COLUMNS (CommodityGroup VARCHAR2(100) PATH '$.Id') ,
NESTED PATH '$.Via[*]'
COLUMNS (Via VARCHAR2(100) PATH '$.Id')
)
) jt
GROUP BY jt.Fromportid,jt.Toportid,jt.CargoType,jt.UldType)
SELECT fsalp.fslap_id AS fsalp_id,
fslar.fslar_id AS fslar_id,
fschg.fschg_id AS fschg_id,
fschg.fschg_code AS fschg_code,
fschg.fschg_description AS fschg_description,
fslar.fslar_basis AS fslar_basis,
basis.fsgec_description AS basis_description,
fslar.fseqt_sizetype AS fseqt_sizetype,
fslar.fscmg_id AS fscmg_id,
fscmg.fscmg_code AS fscmg_code,
fscmg.fscmg_description AS fscmg_description,
fslar.fslar_amount AS fslar_amount,
fslar.fscry_id AS fscry_id,
fscry.fscry_code AS fscry_code,
fslar.fslar_isexportimport AS fslar_isexportimport,
fsgec_importexport.fsgec_description AS fsalr_isexportimport_description,
fslar.fslar_remarks AS fslar_remarks
FROM fslap_localchargeairport fsalp
JOIN fslar_localchargeairrate fslar ON (fslar.fslar_uigroupingreference=fsalp.fslap_uigroupingreference AND fslar.fslar_uicardreference = fsalp.fslap_uicardreference)
JOIN fschg_chargemaster fschg ON (fschg.fschg_id=fslar.fschg_id)
JOIN fscmg_commoditygroup fscmg ON (fscmg.fscmg_id=fslar.fscmg_id)
JOIN fscry_currency fscry ON (fscry.fscry_id =fslar.fscry_id)
JOIN FSGEC_GeneralCode fsgec_importexport ON ( fsgec_importexport.fsgec_referencename='BOOKINGTYPE' AND fsgec_importexport.fsgec_value=fslar.fslar_isexportimport)
JOIN FSGEC_GeneralCode basis ON (basis.fsgec_referencename='LOCALCHARGEBASIS' AND basis.fsgec_value=fslar.fslar_basis )
JOIN route_details ON (route_details.Fromportid=fsalp.fsart_idfrom AND route_details.Toportid=fsalp.fsart_idto)
WHERE (p_fsbue_idcustomer IS NULL OR fsalp.fsbue_idcustomer=p_fsbue_idcustomer)
AND (p_fsofo_idpartner IS NULL OR fsalp.fsofo_idpartner=p_fsofo_idpartner)
AND (route_details.UldType IS NULL OR fslar.fseqt_sizetype IN (SELECT REGEXP_SUBSTR (route_details.UldType, '[^,]+', 1,LEVEL) AS sizetype FROM FSDUL_DUAL
CONNECT BY REGEXP_SUBSTR (route_details.UldType,'[^,]+', 1, LEVEL) IS NOT NULL))
AND (route_details.CommodityGroup IS NULL OR fslar.fscmg_id IN (SELECT REGEXP_SUBSTR (route_details.CommodityGroup, '[^,]+', 1,LEVEL) AS fscmg_id FROM FSDUL_DUAL
CONNECT BY REGEXP_SUBSTR (route_details.CommodityGroup,'[^,]+', 1, LEVEL) IS NOT NULL))
AND (p_fssqh_isexportimport IS NULL OR fslar.fslar_isexportimport =p_fssqh_isexportimport);

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

PROCEDURE Get_airquotationtariff_Prc ( --Retrive quotation details
p_fsaqh_id IN fsaqh_airquotationheader.fsaqh_id%TYPE,
p_fsaqt_id IN fsaqh_airquotationheader.fsaqh_remarks%TYPE,
p_fsofo_id IN fsaqh_airquotationheader.fsofo_id%TYPE,
p_airquotationhdrdetails OUT FS_AirQuotationManagement.t_list,
p_airquotationport_details OUT FS_AirQuotationManagement.t_list,
p_airquotationrate_details OUT FS_AirQuotationManagement.t_list,
p_surcharge_details OUT FS_AirQuotationManagement.t_list,
p_localcharge_details OUT FS_AirQuotationManagement.t_list,
p_weightdetails OUT FS_AirQuotationManagement.t_list,
p_precarriage_details OUT FS_AirQuotationManagement.t_list,
p_precarriagecharge_details OUT FS_AirQuotationManagement.t_list,
p_oncarriage_details OUT FS_AirQuotationManagement.t_list,
p_oncarriagecharge_details OUT FS_AirQuotationManagement.t_list,
p_issuccess OUT fsaqh_airquotationheader.fsaqh_status%TYPE,
p_message OUT FS_AirQuotationManagement.t_list)
AS
BEGIN

OPEN p_airquotationhdrdetails FOR
SELECT fsaqh.fsaqh_id AS fsaqh_id,
fsaqh.fsbue_idcustomer AS fsbue_idcustomer,
fsbue_customer.fsbue_name AS fsaqh_customername,
fsaqh.fsaqh_customerdetails AS fsbue_customerdetail,
quotationpartytype.fsgec_description AS quotationpartytype_description ,
fsaqh.fsaqh_quotationpartytype AS fsaqh_quotationpartytype,
fsaqh.fsofo_idpartner AS fsofo_idpartner,
fsofo_partner.fsofo_organizationname AS parter_name,
fsaqh.fsaqh_quotationtype AS fsaqh_quotationtype,
fsgec_quotationtype.fsgec_description AS fsaqh_quotationtype_description,
fsaqh.fsaqh_shipmenttype AS fsaqh_shipmenttype,
fsgec_shipmenttype.fsgec_description AS fsaqh_shipmenttype_description,
fsaqh.fsaqh_minimumconfirmedquantitytype AS fsaqh_minimumconfirmedquantitytype,
fsgec_mcq.fsgec_description AS fsaqh_mcqtype_description,
fsaqh.fsaqh_weight AS fsaqh_weight,
fsaqh.fsmea_id AS fsmea_id,
fsmea_weight.fsmea_isocode AS fsaqh_weight_unit,
fsaqh.fsaqh_volume AS fsaqh_volume,
fsaqh.fsaqh_incoterms AS fsaqh_incoterms,
fsgec_incoterms.fsgec_description AS fsaqh_incoterms_description,
fsaqh.fscry_idquotation AS fscry_idquotation,
fscry.fscry_code AS fscry_code,
fsaqh.fsaqh_isexportimport AS fsaqh_isexportimport,
fsgec_importexport.fsgec_description AS fsaqh_isexportimport_description,
fsaqh.fsaqh_validfrom AS fsaqh_validfrom,
fsaqh.fsaqh_validto AS fsaqh_validto,
fsaqh.fsaqh_isshowcarrier AS fsaqh_isshowcarrier,
(SELECT listagg (DISTINCT fsaqc.fsbue_idcarrier,',' ON overflow TRUNCATE WITH COUNT)
within GROUP (ORDER BY fsaqc.fsaqc_id)
FROM fsaqc_airquotationcarrier fsaqc
WHERE fsaqc.fsaqh_id = fsaqh.fsaqh_id) AS fsaqh_carrierid,
(SELECT listagg (DISTINCT fsbue_carrier.fsbue_name,',' ON overflow TRUNCATE WITH COUNT)
within GROUP (ORDER BY fsaqc.fsaqc_id)
FROM fsaqc_airquotationcarrier fsaqc
JOIN fsbue_businessentity fsbue_carrier ON (fsbue_carrier.fsbue_id=fsaqc.fsbue_idcarrier)
WHERE fsaqc.fsaqh_id = fsaqh.fsaqh_id) AS fsaqh_carriername,
fsaqh.fsofo_idsalesoffice AS fsofo_idsalesoffice,
fsofo.fsofo_organizationname AS sales_office,
fsaqh.fsaqh_remarks AS fsaqh_remarks,
(SELECT listagg (DISTINCT fsaqd.fsdlm_id,',' ON overflow TRUNCATE WITH COUNT)
within GROUP (ORDER BY fsaqd.fsaqd_id)
FROM fsaqd_airquotationdeal fsaqd
WHERE fsaqd.fsaqh_id = fsaqh.fsaqh_id) AS DEALID,
(SELECT listagg (DISTINCT fsdlm.fsdlm_referencenumber,',' ON overflow TRUNCATE WITH COUNT)
within GROUP (ORDER BY fsaqd.fsaqd_id)
FROM fsaqd_airquotationdeal fsaqd
JOIN fsdlm_dealmanagement fsdlm ON (fsdlm.fsdlm_id=fsaqd.fsdlm_id)
WHERE fsaqd.fsaqh_id = fsaqh.fsaqh_id) AS fsdlm_referencenumber,
fsaqh.fsaqh_minimumconfirmedquantity AS fsaqh_minimumconfirmedquantity,
fsaqh.fsaqh_status AS fsaqh_status,
fsgec_status.fsgec_description AS fsaqh_status_description,
fsaqh.fsaqh_referencenumber AS fsaqh_referencenumber
FROM fsaqh_airquotationheader fsaqh
LEFT JOIN fsbue_businessentity fsbue_customer ON (fsbue_customer.fsbue_id=fsaqh.fsbue_idcustomer)
JOIN fscry_currency fscry ON (fscry.fscry_id=fsaqh.fscry_idquotation)
LEFT JOIN FSOFO_Officeorganization fsofo ON (fsofo.fsofo_id=fsaqh.fsofo_idsalesoffice)
LEFT JOIN FSOFO_Officeorganization fsofo_partner ON (fsofo_partner.fsofo_id=fsaqh.fsofo_idpartner)
LEFT JOIN fsmea_measurement fsmea_weight ON (fsmea_weight.fsmea_id=fsaqh.fsmea_id)
JOIN FSGEC_GeneralCode fsgec_quotationtype ON (fsgec_quotationtype.fsgec_value=fsaqh.fsaqh_quotationtype AND fsgec_quotationtype.fsgec_referencename='QUOTATIONTYPE')
JOIN FSGEC_GeneralCode fsgec_shipmenttype ON (fsgec_shipmenttype.fsgec_value=fsaqh.fsaqh_shipmenttype AND fsgec_shipmenttype.fsgec_referencename='AIRSHIPMENTTYPE')
JOIN FSGEC_GeneralCode fsgec_incoterms ON (fsgec_incoterms.fsgec_value=fsaqh.fsaqh_incoterms AND fsgec_incoterms.fsgec_referencename='BOOKINGINCOTERMS')
LEFT JOIN FSGEC_GeneralCode fsgec_mcq ON (fsgec_mcq.fsgec_value=fsaqh.fsaqh_minimumconfirmedquantitytype AND fsgec_mcq.fsgec_referencename='QUOTATIONMCQ')
JOIN FSGEC_GeneralCode fsgec_importexport ON (fsgec_importexport.fsgec_value=fsaqh.fsaqh_isexportimport AND fsgec_importexport.fsgec_referencename='BOOKINGTYPE')
JOIN fsgec_generalcode quotationpartytype ON (quotationpartytype.fsgec_referencename='QUOTATIONPARTYTYPE' AND quotationpartytype.fsgec_value=fsaqh.fsaqh_quotationpartytype )
JOIN FSGEC_GeneralCode fsgec_status ON (fsgec_status.fsgec_value=fsaqh.fsaqh_status AND fsgec_status.fsgec_referencename='QUOTATIONSTATUS')
WHERE fsaqh.fsaqh_id=p_fsaqh_id;

OPEN p_airquotationport_details FOR
SELECT DISTINCT fsaqh.fsaqh_id AS fsaqh_id,
fsaqc.fsbue_idcarrier AS fsbue_idcarrier,
fsbue_carrier.fsbue_name AS fsbue_carriername,
fsaqt.fsart_idfromairport AS fsart_idfromairport,
fsaqt.fsart_idtoairport AS fsart_idtoairport,
fsart_from.fsart_name AS fsart_fromname,
fsart_to.fsart_name AS fsart_toname ,
(SELECT listagg (DISTINCT transhipment.fsart_name,',' ON overflow TRUNCATE WITH COUNT)
within GROUP (ORDER BY fsaqp.fsaqp_id)
FROM fsaqp_airquotationtranshipment fsaqp
JOIN fsart_airportmaster transhipment ON (transhipment.fsart_id=fsaqp.fsart_idtranshipment)
WHERE fsaqp.fsaqt_id = fsaqt.fsaqt_id) AS fsprt_transhipment,
(SELECT listagg (DISTINCT fsaqp.fsart_idtranshipment,',' ON overflow TRUNCATE WITH COUNT)
within GROUP (ORDER BY fsaqp.fsaqp_id)
FROM fsaqp_airquotationtranshipment fsaqp
WHERE fsaqp.fsaqt_id = fsaqt.fsaqt_id) AS transhipmentid,
(SELECT listagg (DISTINCT fsaqm.fscmg_id,',' ON overflow TRUNCATE WITH COUNT)
within GROUP (ORDER BY fsaqm.fsaqm_id)
FROM fsaqm_airquotationcommodity fsaqm
WHERE fsaqm.fsaqt_id = fsaqt.fsaqt_id) AS fscmg_id,
(SELECT listagg (DISTINCT fscmg.fscmg_code,',' ON overflow TRUNCATE WITH COUNT)
within GROUP (ORDER BY fsaqm.fsaqm_id)
FROM fsaqm_airquotationcommodity fsaqm
JOIN fscmg_commoditygroup fscmg ON (fscmg.fscmg_id=fsaqm.fscmg_id)
WHERE fsaqm.fsaqt_id = fsaqt.fsaqt_id) AS fscmg_code,
(SELECT listagg (DISTINCT fscmg.fscmg_description,',' ON overflow TRUNCATE WITH COUNT)
within GROUP (ORDER BY fsaqm.fsaqm_id)
FROM fsaqm_airquotationcommodity fsaqm
JOIN fscmg_commoditygroup fscmg ON (fscmg.fscmg_id=fsaqm.fscmg_id)
WHERE fsaqm.fsaqt_id = fsaqt.fsaqt_id) AS fscmg_description,
(SELECT listagg (DISTINCT fsaqe.fseqt_sizetypeuld,',' ON overflow TRUNCATE WITH COUNT)
within GROUP (ORDER BY fsaqe.fsaqe_id)
FROM fsaqe_airquotationequipment fsaqe
WHERE fsaqe.fsaqt_id = fsaqt.fsaqt_id) AS fseqt_sizetypeuld,
(SELECT listagg (DISTINCT fsaqe.fsaqe_cargotype,',' ON overflow TRUNCATE WITH COUNT)
within GROUP (ORDER BY fsaqe.fsaqe_id)
FROM fsaqe_airquotationequipment fsaqe
WHERE fsaqe.fsaqt_id = fsaqt.fsaqt_id) AS fsaqe_cargotype,
(SELECT listagg (DISTINCT cargotype.fsgec_description,',' ON overflow TRUNCATE WITH COUNT)
within GROUP (ORDER BY fsaqe.fsaqe_id)
FROM fsaqe_airquotationequipment fsaqe
JOIN fsgec_generalcode cargotype ON (cargotype.fsgec_value=fsaqe.fsaqe_cargotype AND cargotype.fsgec_referencename='AIRTARIFFCARGOTYPE')
WHERE fsaqe.fsaqt_id = fsaqt.fsaqt_id
) AS fsaqe_cargotype_description,
fsaqt.fsaqt_travellingtime AS fsaqt_travellingtime,
fsaqt.fsaqt_dayoffrequency AS fsaqt_dayoffrequency,
fsgec.fsgec_description AS fsaqt_dayoffrequency_desccription,
fsaqt.fsaqt_id AS fsaqt_id
FROM fsaqh_airquotationheader fsaqh
JOIN fsaqt_airquotationtransportstation fsaqt ON (fsaqt.fsaqh_id=fsaqh.fsaqh_id)
JOIN FSART_AirportMaster fsart_from ON (fsart_from.fsart_id=fsaqt.fsart_idfromairport)
JOIN FSART_AirportMaster fsart_to ON (fsart_to.fsart_id=fsaqt.fsart_idtoairport)
LEFT JOIN fsaqc_airquotationcarrier fsaqc ON (fsaqc.fsaqh_id=fsaqh.fsaqh_id)
LEFT JOIN FSBUE_Businessentity fsbue_carrier ON (fsbue_carrier.fsbue_id=fsaqc.fsbue_idcarrier)
LEFT JOIN fsgec_generalcode fsgec ON (fsgec.fsgec_referencename='FREQUENCY' AND fsgec.fsgec_value=fsaqt.fsaqt_dayoffrequency)
WHERE fsaqh.fsaqh_id=p_fsaqh_id ;

OPEN p_airquotationrate_details FOR
SELECT fsaqr.fsaqt_id AS fsaqt_id,
fsaqr.fsaqr_id AS fsaqr_id,
fsaqr.fsbue_idcarrier AS fsbue_idcarrier,
fsbue.fsbue_name AS carriername,
fsaqr.fschg_id AS fschg_id,
fschg.fschg_code AS fschg_code,
fschg.fschg_description AS fschg_description,
fsaqr.fsaqr_cargotype AS fsaqr_cargotype,
cargotype.fsgec_description AS cargotype_description,
fsaqr.fseqt_sizetypeuld AS fseqt_sizetypeuld,
fscmg.fscmg_id AS fscmg_id,
fscmg.fscmg_code AS fscmg_code,
fscmg.fscmg_description AS fscmg_description,
fsaqr.fsaqr_tariffrate AS fsaqr_tariffrate,
fsaqr.fsaqr_markuptype AS fsaqr_markuptype,
fsgec_markuptype.fsgec_description AS fsaqr_markuptype_description,
fsaqr.fsaqr_markupvalue AS fsaqr_markupvalue,
fsaqr.fsaqr_sellingrate AS fsaqr_sellingrate,
fsaqr.fsaqr_mintariffrate AS fsaqr_mintariffrate,
fsaqr.fsaqr_minmarkuptype AS fsaqr_minmarkuptype,
fsaqr.fsaqr_minmarkupvalue AS fsaqr_minmarkupvalue,
fsaqr.fsaqr_minsellingrate AS fsaqr_minsellingrate,
fsaqr.fsaqr_additionaldayfee AS fsaqr_additionaldayfee,
fsaqr.fsaqr_pivotweight AS fsaqr_pivotweight ,
(SELECT listagg (DISTINCT fschg.fschg_code,', ' ON overflow TRUNCATE WITH COUNT)
within GROUP (ORDER BY fsaqi.fsaqi_id)
FROM fsaqi_airquotationinclusivecharge fsaqi
JOIN fschg_chargemaster fschg ON (fschg.fschg_id=fsaqi.fschg_id)
WHERE fsaqi.fsaqr_id = fsaqr.fsaqr_id) AS fsaqi_inclusivechargecode,
(SELECT listagg (DISTINCT fsaqi.fschg_id,', ' ON overflow TRUNCATE WITH COUNT)
within GROUP (ORDER BY fsaqi.fsaqi_id)
FROM fsaqi_airquotationinclusivecharge fsaqi
WHERE fsaqi.fsaqr_id = fsaqr.fsaqr_id) AS fsaqi_inclusivechargeid,
fscry.fscry_code AS fscry_code,
fsaqr.fsaqr_remarks AS fsaqr_remarks,
fsaqr.fsaqr_ispax AS fsaqr_ispax,
fsgec_pax.fsgec_description AS fsaqr_ispax_description
FROM fsaqr_airquotationrate fsaqr
JOIN fschg_chargemaster fschg ON (fschg.fschg_id=fsaqr.fschg_id)
JOIN fscmg_commoditygroup fscmg ON (fscmg.fscmg_id=fsaqr.fscmg_id)
JOIN fscry_currency fscry ON (fscry.fscry_id=fsaqr.fscry_id)
LEFT JOIN fsbue_businessentity fsbue ON (fsbue.fsbue_id=fsaqr.fsbue_idcarrier)
JOIN fsgec_generalcode cargotype ON (cargotype.fsgec_value=fsaqr.fsaqr_cargotype AND cargotype.fsgec_referencename='AIRTARIFFCARGOTYPE')
JOIN fsgec_generalcode fsgec_markuptype ON (fsgec_markuptype.fsgec_value=fsaqr.fsaqr_markuptype AND fsgec_markuptype.fsgec_referencename='CARGOMARKUPTYPE')
JOIN fsgec_generalcode fsgec_pax ON (fsgec_pax.fsgec_value=fsaqr.fsaqr_ispax AND fsgec_pax.fsgec_referencename='AIRTARIFFPAX')
WHERE EXISTS (SELECT 'X'
FROM fsaqt_airquotationtransportstation fsaqt
WHERE fsaqt.fsaqt_id=fsaqr.fsaqt_id
AND fsaqt.fsaqh_id=p_fsaqh_id
AND (p_fsaqt_id IS NULL OR fsaqt.fsaqt_id IN (SELECT REGEXP_SUBSTR (p_fsaqt_id, '[^,]+', 1,LEVEL) AS route_id FROM FSDUL_DUAL
CONNECT BY REGEXP_SUBSTR (p_fsaqt_id,'[^,]+', 1, LEVEL) IS NOT NULL)));

OPEN p_weightdetails FOR
SELECT fsaqw.fsaqw_weight AS fsaqw_weight,
fsaqw.fsaqw_buyingrate AS fsaqw_buyingrate ,
fsaqw.fsaqw_sellingrate AS fsaqw_sellingrate ,
fsaqw.fsaqw_markupvalue AS fsaqw_markupvalue,
fsaqw.fsaqw_id AS fsaqw_id,
fsaqw.fsatm_id AS fsatm_id,
fsaqw.fsaqr_id AS fsaqr_id
FROM fsaqr_airquotationrate fsaqr
JOIN fsaqw_airquotationslabrate fsaqw ON (fsaqw.fsaqr_id=fsaqr.fsaqr_id)
WHERE EXISTS (SELECT 'X'
FROM fsaqt_airquotationtransportstation fsaqt
WHERE fsaqt.fsaqt_id=fsaqr.fsaqt_id
AND fsaqt.fsaqh_id=p_fsaqh_id
AND (p_fsaqt_id IS NULL OR fsaqt.fsaqt_id IN (SELECT REGEXP_SUBSTR (p_fsaqt_id, '[^,]+', 1,LEVEL) AS route_id FROM FSDUL_DUAL
CONNECT BY REGEXP_SUBSTR (p_fsaqt_id,'[^,]+', 1, LEVEL) IS NOT NULL)));

OPEN p_surcharge_details FOR
SELECT fsaqr.fsaqt_id AS fsaqt_id,
fsaqr.fsaqr_id AS fsaqr_id,
fschg.fschg_id AS fschg_id,
fschg.fschg_code AS fschg_code,
fschg.fschg_description AS fschg_description,
fscmg.fscmg_id AS fscmg_id,
fscmg.fscmg_code AS fscmg_code,
fscmg.fscmg_description AS fscmg_description,
fsaqr.fscry_id AS fscry_id,
fscry.fscry_code AS fscry_code,
fsaqr.fsaqr_markuptype AS calcproc,
fsgec_markuptype.fsgec_description AS calcproc_description,
fsaqr.fsaqr_markupvalue AS fsaqr_markupvalue,
fsaqr.fsaqr_sellingrate AS fsaqr_sellingrate,
fsaqr.fsaqr_remarks AS fsaqr_remarks
FROM fsaqr_airquotationrate fsaqr
JOIN fschg_chargemaster fschg ON (fschg.fschg_id=fsaqr.fschg_id)
JOIN fscmg_commoditygroup fscmg ON (fscmg.fscmg_id=fsaqr.fscmg_id)
JOIN fscry_currency fscry ON (fscry.fscry_id=fsaqr.fscry_id)
JOIN fsgec_generalcode fsgec_markuptype ON (fsgec_markuptype.fsgec_value=fsaqr.fsaqr_markuptype AND fsgec_markuptype.fsgec_referencename='MARKUPTYPE' )
WHERE EXISTS (SELECT 'X'
FROM fsaqt_airquotationtransportstation fsaqt
WHERE fsaqt.fsaqt_id=fsaqr.fsaqt_id
AND fsaqt.fsaqh_id=p_fsaqh_id
AND (p_fsaqt_id IS NULL OR fsaqt.fsaqt_id IN (SELECT REGEXP_SUBSTR (p_fsaqt_id, '[^,]+', 1,LEVEL) AS route_id FROM FSDUL_DUAL
CONNECT BY REGEXP_SUBSTR (p_fsaqt_id,'[^,]+', 1, LEVEL) IS NOT NULL)));

OPEN p_localcharge_details FOR
SELECT fsaqr.fsaqt_id AS fsaqt_id,
fsaqr.fsaqr_id AS fsaqr_id,
fschg.fschg_id AS fschg_id,
fschg.fschg_code AS fschg_code,
fschg.fschg_description AS fschg_description,
fscmg.fscmg_id AS fscmg_id,
fscmg.fscmg_code AS fscmg_code,
fscmg.fscmg_description AS fscmg_description,
fsaqr.fscry_id AS fscry_id,
fscry.fscry_code AS fscry_code,
fsaqr.fsaqr_sellingrate AS fsaqr_sellingrate,
fsaqr.fsaqr_remarks AS fsaqr_remarks,
fsaqr.fsaqr_basis AS fsaqr_basis,
gec_basis.fsgec_description AS gec_basisdescription,
fsaqr.fsaqr_isexportimport AS export_import,
export.fsgec_description AS export_importdescription
FROM fsaqr_airquotationrate fsaqr
JOIN fschg_chargemaster fschg ON (fschg.fschg_id=fsaqr.fschg_id)
JOIN fscmg_commoditygroup fscmg ON (fscmg.fscmg_id=fsaqr.fscmg_id)
JOIN fscry_currency fscry ON (fscry.fscry_id=fsaqr.fscry_id)
JOIN fsgec_generalcode gec_basis ON (gec_basis.fsgec_value=fsaqr.fsaqr_basis AND gec_basis.fsgec_referencename='LOCALCHARGEBASIS' )
JOIN FSGEC_GeneralCode export ON (export.fsgec_referencename='BOOKINGTYPE' AND export.fsgec_value=fsaqr.fsaqr_isexportimport )
WHERE EXISTS (SELECT 'X'
FROM fsaqt_airquotationtransportstation fsaqt
WHERE fsaqt.fsaqt_id=fsaqr.fsaqt_id
AND fsaqt.fsaqh_id=p_fsaqh_id
AND (p_fsaqt_id IS NULL OR fsaqt.fsaqt_id IN (SELECT REGEXP_SUBSTR (p_fsaqt_id, '[^,]+', 1,LEVEL) AS route_id FROM FSDUL_DUAL
CONNECT BY REGEXP_SUBSTR (p_fsaqt_id,'[^,]+', 1, LEVEL) IS NOT NULL)));

OPEN p_precarriage_details FOR
SELECT fsaqu.fsaqu_id AS fsaqu_id,
NVL(fsaqu.fsaqu_idparent,fsaqu.fsaqu_id) AS fsaqu_idparent,
fsaqu.fsloc_idfrom AS fsloc_idfrom,
fsloc_fromlocation.fsloc_name AS fromlocation,
fsaqu.fsaqu_fromlocationaddress AS fsaqu_fromlocationaddress,
fsaqu.fsaqu_zipcodefrom AS fsaqu_zipcodefrom,
fsaqu.fsaqu_zipcodeto AS fsaqu_zipcodeto,
fsaqu.fsloc_idto AS fsloc_idto,
fsloc_tolocation.fsloc_name AS tolocation,
fsaqu.fsaqu_modeoftransport AS fsaqu_modeoftransport,
fsgec_transportmode.fsgec_description AS fsaqu_modeoftransport_description,
fsaqu.fsaqu_transit AS fsaqu_transit,
fsaqu.fsaqu_transittype AS fsaqu_transittype,
fsgec_transittype.fsgec_description AS fsgec_transittypedescription,
fsaqu.fsaqu_specialinstruction AS fsaqu_specialinstruction,
fsaqu.fsaqu_tolocationaddress AS fsaqu_tolocationaddress,
fsaqu.fsaqu_ismultimode AS fsaqu_ismultimode,
fsaqu.fsaqu_ismultistop AS fsaqu_ismultistop,
fsaqu.fsaqu_shipmenttype AS fsaqu_shipmenttype
FROM FSAQU_AirQuotationIntermodel fsaqu
JOIN FSLOC_Location fsloc_fromlocation ON (fsloc_fromlocation.fsloc_id=fsaqu.fsloc_idfrom)
JOIN FSLOC_Location fsloc_tolocation ON (fsloc_tolocation.fsloc_id=fsaqu.fsloc_idto)
JOIN fsgec_generalcode fsgec_transportmode ON (fsgec_transportmode.fsgec_referencename = 'MODEOFTRANSPORT' AND fsgec_transportmode.fsgec_value = fsaqu.fsaqu_modeoftransport)
JOIN fsgec_generalcode fsgec_transittype ON (fsgec_transittype.fsgec_referencename = 'TRANSITTYPE' AND fsgec_transittype.fsgec_value = fsaqu.fsaqu_transittype)
WHERE fsaqu.fsaqh_id=p_fsaqh_id
AND fsaqu.fsaqu_shipmenttype='P';

OPEN p_precarriagecharge_details FOR
SELECT fsaqr.fsaqr_id AS fsaqr_id,
fsaqu.fsaqu_id AS fsaqu_id,
fsaqr.fschg_id AS fschg_id,
fschg.fschg_code AS fschg_code,
fschg.fschg_description AS fschg_description,
fsaqr.fsaqr_weight AS fsaqr_weight,
fsmea.fsmea_isocode AS fsaqr_weight_unit,
fsaqr.fsaqr_tariffrate AS fsaqr_tariffrate,
fsaqr.fsaqr_markuptype AS fsaqr_markuptype,
markuptype.fsgec_description AS fsaqr_markuptype_description,
fsaqr.fsaqr_sellingrate AS fsaqr_sellingrate,
fsaqr.fscry_id AS fscry_id,
fscry.fscry_code AS fscry_code
FROM FSAQU_AirQuotationIntermodel fsaqu
JOIN fsaqr_airquotationrate fsaqr ON (fsaqr.fsaqu_id=fsaqu.fsaqu_id AND fsaqu.fsaqu_idparent IS NULL)
JOIN fsmea_measurement fsmea ON (fsmea.fsmea_id=fsaqr.fsmea_id)
JOIN fschg_chargemaster fschg ON (fschg.fschg_id=fsaqr.fschg_id)
JOIN fscry_currency fscry ON (fscry.fscry_id =fsaqr.fscry_id)
JOIN FSGEC_GeneralCode markuptype ON (markuptype.fsgec_referencename='MARKUPTYPE' AND markuptype.fsgec_value=fsaqr.fsaqr_markuptype )
WHERE fsaqu.fsaqh_id=p_fsaqh_id
AND fsaqu.fsaqu_shipmenttype='P';

OPEN p_oncarriage_details FOR
SELECT fsaqu.fsaqu_id AS fsaqu_id,
NVL(fsaqu.fsaqu_idparent,fsaqu.fsaqu_id) AS fsaqu_idparent,
fsaqu.fsloc_idfrom AS fsloc_idfrom,
fsloc_fromlocation.fsloc_name AS fromlocation,
fsaqu.fsaqu_fromlocationaddress AS fsaqu_fromlocationaddress,
fsaqu.fsaqu_zipcodefrom AS fsaqu_zipcodefrom,
fsaqu.fsaqu_zipcodeto AS fsaqu_zipcodeto,
fsaqu.fsloc_idto AS fsloc_idto,
fsloc_tolocation.fsloc_name AS tolocation,
fsaqu.fsaqu_modeoftransport AS fsaqu_modeoftransport,
fsgec_transportmode.fsgec_description AS fsaqu_modeoftransport_description,
fsaqu.fsaqu_transit AS fsaqu_transit,
fsaqu.fsaqu_transittype AS fsaqu_transittype,
fsgec_transittype.fsgec_description AS fsgec_transittypedescription,
fsaqu.fsaqu_specialinstruction AS fsaqu_specialinstruction,
fsaqu.fsaqu_tolocationaddress AS fsaqu_tolocationaddress,
fsaqu.fsaqu_ismultimode AS fsaqu_ismultimode,
fsaqu.fsaqu_ismultistop AS fsaqu_ismultistop,
fsaqu.fsaqu_shipmenttype AS fsaqu_shipmenttype
FROM fsaqu_airquotationintermodel fsaqu
JOIN FSLOC_Location fsloc_fromlocation ON (fsloc_fromlocation.fsloc_id=fsaqu.fsloc_idfrom)
JOIN FSLOC_Location fsloc_tolocation ON (fsloc_tolocation.fsloc_id=fsaqu.fsloc_idto)
JOIN fsgec_generalcode fsgec_transportmode ON (fsgec_transportmode.fsgec_referencename = 'MODEOFTRANSPORT' AND fsgec_transportmode.fsgec_value = fsaqu.fsaqu_modeoftransport)
JOIN fsgec_generalcode fsgec_transittype ON (fsgec_transittype.fsgec_referencename = 'TRANSITTYPE' AND fsgec_transittype.fsgec_value = fsaqu.fsaqu_transittype)
WHERE fsaqu.fsaqh_id=p_fsaqh_id
AND fsaqu.fsaqu_shipmenttype='O';

OPEN p_oncarriagecharge_details FOR
SELECT fsaqr.fsaqr_id AS fsaqr_id,
fsaqu.fsaqu_id AS fsaqu_id,
fsaqr.fschg_id AS fschg_id,
fschg.fschg_code AS fschg_code,
fschg.fschg_description AS fschg_description,
fsaqr.fsaqr_weight AS fsaqr_weight,
fsmea.fsmea_isocode AS fsaqr_weight_unit,
fsaqr.fsaqr_tariffrate AS fsaqr_tariffrate,
fsaqr.fsaqr_markuptype AS fsaqr_markuptype,
markuptype.fsgec_description AS fsaqr_markuptype_description,
fsaqr.fsaqr_sellingrate AS fsaqr_sellingrate,
fsaqr.fscry_id AS fscry_id,
fscry.fscry_code AS fscry_code
FROM FSAQU_AirQuotationIntermodel fsaqu
JOIN fsaqr_airquotationrate fsaqr ON (fsaqr.fsaqu_id=fsaqu.fsaqu_id AND fsaqu.fsaqu_idparent IS NULL)
JOIN fsmea_measurement fsmea ON (fsmea.fsmea_id=fsaqr.fsmea_id)
JOIN fschg_chargemaster fschg ON (fschg.fschg_id=fsaqr.fschg_id)
JOIN fscry_currency fscry ON (fscry.fscry_id =fsaqr.fscry_id)
JOIN FSGEC_GeneralCode markuptype ON (markuptype.fsgec_referencename='MARKUPTYPE' AND markuptype.fsgec_value=fsaqr.fsaqr_markuptype )
WHERE fsaqu.fsaqh_id=p_fsaqh_id
AND fsaqu.fsaqu_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 FSDUL_Dual;

EXCEPTION
WHEN OTHERS THEN
p_issuccess := fs_constants.k_failure;
fs_exception.raise_exception;

END Get_airquotationtariff_Prc;

PROCEDURE Get_intermodel_Prc (
p_fsaqh_id IN fsaqh_airquotationheader.fsaqh_id%TYPE,
p_fsaqu_id IN FSAQU_AirQuotationIntermodel.fsaqu_id%TYPE,
p_fsofo_id IN fsaqh_airquotationheader.fsofo_id%TYPE,
p_precarriage_details OUT FS_AirQuotationManagement.t_list,
p_precarriagecharge_details OUT FS_AirQuotationManagement.t_list,
p_oncarriage_details OUT FS_AirQuotationManagement.t_list,
p_oncarriagecharge_details OUT FS_AirQuotationManagement.t_list,
p_issuccess OUT fsaqh_airquotationheader.fsaqh_shipmenttype%TYPE,
p_message OUT FS_AirQuotationManagement.t_list)

AS

BEGIN

OPEN p_precarriage_details FOR
SELECT fsaqu.fsaqu_id AS fsaqu_id,
NVL(fsaqu.fsaqu_idparent,fsaqu.fsaqu_id) AS fsaqu_idparent,
fsaqu.fsloc_idfrom AS fsloc_idfrom,
fsloc_fromlocation.fsloc_name AS fromlocation,
fsaqu.fsaqu_fromlocationaddress AS fsaqu_fromlocationaddress,
fsaqu.fsaqu_zipcodefrom AS fsaqu_zipcodefrom,
fsaqu.fsaqu_zipcodeto AS fsaqu_zipcodeto,
fsaqu.fsloc_idto AS fsloc_idto,
fsloc_tolocation.fsloc_name AS tolocation,
fsaqu.fsaqu_modeoftransport AS fsaqu_modeoftransport,
fsgec_transportmode.fsgec_description AS fsaqu_modeoftransport_description,
fsaqu.fsaqu_transit AS fsaqu_transit,
fsaqu.fsaqu_transittype AS fsaqu_transittype,
fsgec_transittype.fsgec_description AS fsgec_transittypedescription,
fsaqu.fsaqu_specialinstruction AS fsaqu_specialinstruction,
fsaqu.fsaqu_tolocationaddress AS fsaqu_tolocationaddress,
fsaqu.fsaqu_ismultimode AS fsaqu_ismultimode,
fsaqu.fsaqu_ismultistop AS fsaqu_ismultistop,
fsaqu.fsaqu_shipmenttype AS fsaqu_shipmenttype
FROM FSAQU_AirQuotationIntermodel fsaqu
JOIN FSLOC_Location fsloc_fromlocation ON (fsloc_fromlocation.fsloc_id=fsaqu.fsloc_idfrom)
JOIN FSLOC_Location fsloc_tolocation ON (fsloc_tolocation.fsloc_id=fsaqu.fsloc_idto)
JOIN fsgec_generalcode fsgec_transportmode ON (fsgec_transportmode.fsgec_referencename = 'MODEOFTRANSPORT' AND fsgec_transportmode.fsgec_value = fsaqu.fsaqu_modeoftransport)
JOIN fsgec_generalcode fsgec_transittype ON (fsgec_transittype.fsgec_referencename = 'TRANSITTYPE' AND fsgec_transittype.fsgec_value = fsaqu.fsaqu_transittype)
WHERE fsaqu.fsaqh_id=p_fsaqh_id
AND NVL(fsaqu.fsaqu_idparent,fsaqu.fsaqu_id)=p_fsaqu_id
AND fsaqu.fsaqu_shipmenttype='P';

OPEN p_precarriagecharge_details FOR
SELECT fsaqr.fsaqr_id AS fsaqr_id,
fsaqr.fschg_id AS fschg_id,
fschg.fschg_code AS fschg_code,
fschg.fschg_description AS fschg_description,
fsaqr.fsaqr_weight AS fsaqr_weight,
fsmea.fsmea_isocode AS fsaqr_weight_unit,
fsaqr.fsaqr_tariffrate AS fsaqr_tariffrate,
fsaqr.fsaqr_markuptype AS fsaqr_markuptype,
markuptype.fsgec_description AS fsaqr_markuptype_description,
fsaqr.fsaqr_sellingrate AS fsaqr_sellingrate,
fsaqr.fscry_id AS fscry_id,
fscry.fscry_code AS fscry_code
FROM fsaqr_airquotationrate fsaqr
JOIN fsmea_measurement fsmea ON (fsmea.fsmea_id=fsaqr.fsmea_id)
JOIN fschg_chargemaster fschg ON (fschg.fschg_id=fsaqr.fschg_id)
JOIN fscry_currency fscry ON (fscry.fscry_id =fsaqr.fscry_id)
JOIN FSGEC_GeneralCode markuptype ON (markuptype.fsgec_referencename='MARKUPTYPE' AND markuptype.fsgec_value=fsaqr.fsaqr_markuptype )
WHERE fsaqr.fsaqu_id= p_fsaqu_id;

OPEN p_oncarriage_details FOR
SELECT fsaqu.fsaqu_id AS fsaqu_id,
NVL(fsaqu.fsaqu_idparent,fsaqu.fsaqu_id) AS fsaqu_idparent,
fsaqu.fsloc_idfrom AS fsloc_idfrom,
fsloc_fromlocation.fsloc_name AS fromlocation,
fsaqu.fsaqu_fromlocationaddress AS fsaqu_fromlocationaddress,
fsaqu.fsaqu_zipcodefrom AS fsaqu_zipcodefrom,
fsaqu.fsaqu_zipcodeto AS fsaqu_zipcodeto,
fsaqu.fsloc_idto AS fsloc_idto,
fsloc_tolocation.fsloc_name AS tolocation,
fsaqu.fsaqu_modeoftransport AS fsaqu_modeoftransport,
fsgec_transportmode.fsgec_description AS fsaqu_modeoftransport_description,
fsaqu.fsaqu_transit AS fsaqu_transit,
fsaqu.fsaqu_transittype AS fsaqu_transittype,
fsgec_transittype.fsgec_description AS fsgec_transittypedescription,
fsaqu.fsaqu_specialinstruction AS fsaqu_specialinstruction,
fsaqu.fsaqu_tolocationaddress AS fsaqu_tolocationaddress,
fsaqu.fsaqu_ismultimode AS fsaqu_ismultimode,
fsaqu.fsaqu_ismultistop AS fsaqu_ismultistop,
fsaqu.fsaqu_shipmenttype AS fsaqu_shipmenttype
FROM fsaqu_airquotationintermodel fsaqu
JOIN FSLOC_Location fsloc_fromlocation ON (fsloc_fromlocation.fsloc_id=fsaqu.fsloc_idfrom)
JOIN FSLOC_Location fsloc_tolocation ON (fsloc_tolocation.fsloc_id=fsaqu.fsloc_idto)
JOIN fsgec_generalcode fsgec_transportmode ON (fsgec_transportmode.fsgec_referencename = 'MODEOFTRANSPORT' AND fsgec_transportmode.fsgec_value = fsaqu.fsaqu_modeoftransport)
JOIN fsgec_generalcode fsgec_transittype ON (fsgec_transittype.fsgec_referencename = 'TRANSITTYPE' AND fsgec_transittype.fsgec_value = fsaqu.fsaqu_transittype)
WHERE fsaqu.fsaqh_id=p_fsaqh_id
AND NVL(fsaqu.fsaqu_idparent,fsaqu.fsaqu_id)=p_fsaqu_id
AND fsaqu.fsaqu_shipmenttype='O';

OPEN p_oncarriagecharge_details FOR
SELECT fsaqr.fsaqr_id AS fsaqr_id,
fsaqr.fschg_id AS fschg_id,
fschg.fschg_code AS fschg_code,
fschg.fschg_description AS fschg_description,
fsaqr.fsaqr_weight AS fsaqr_weight,
fsmea.fsmea_isocode AS fsaqr_weight_unit,
fsaqr.fsaqr_tariffrate AS fsaqr_tariffrate,
fsaqr.fsaqr_markuptype AS fsaqr_markuptype,
markuptype.fsgec_description AS fsaqr_markuptype_description,
fsaqr.fsaqr_sellingrate AS fsaqr_sellingrate,
fsaqr.fscry_id AS fscry_id,
fscry.fscry_code AS fscry_code
FROM fsaqr_airquotationrate fsaqr
JOIN fsmea_measurement fsmea ON (fsmea.fsmea_id=fsaqr.fsmea_id)
JOIN fschg_chargemaster fschg ON (fschg.fschg_id=fsaqr.fschg_id)
JOIN fscry_currency fscry ON (fscry.fscry_id =fsaqr.fscry_id)
JOIN FSGEC_GeneralCode markuptype ON (markuptype.fsgec_referencename='MARKUPTYPE' AND markuptype.fsgec_value=fsaqr.fsaqr_markuptype )
WHERE fsaqr.fsaqu_id= p_fsaqu_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 FSDUL_Dual;

EXCEPTION
WHEN OTHERS THEN
p_issuccess := fs_constants.k_failure;
fs_exception.raise_exception;


END Get_intermodel_Prc;

PROCEDURE put_fsaqh_prc(
p_fsaqh_id IN OUT FSAQH_AirQuotationHeader.fsaqh_id%TYPE,
p_fsofo_id IN FSAQH_AirQuotationHeader.fsofo_id%TYPE,
p_fsaqh_referencenumber IN OUT FSAQH_AirQuotationHeader.fsaqh_referencenumber%TYPE,
p_fsaqh_referencedate IN FSAQH_AirQuotationHeader.fsaqh_referencedate%TYPE,
p_fsbue_idcustomer IN FSAQH_AirQuotationHeader.fsbue_idcustomer%TYPE,
p_fsofo_idsalesoffice IN FSAQH_AirQuotationHeader.fsofo_idsalesoffice%TYPE,
p_fsaqh_customerdetails IN FSAQH_AirQuotationHeader.fsaqh_customerdetails%TYPE,
p_fsaqh_quotationtype IN FSAQH_AirQuotationHeader.fsaqh_quotationtype%TYPE,
p_fsaqh_shipmenttype IN FSAQH_AirQuotationHeader.fsaqh_shipmenttype%TYPE,
p_fsaqh_minimumconfirmedquantity IN FSAQH_AirQuotationHeader.fsaqh_minimumconfirmedquantity%TYPE,
p_fsaqh_minimumconfirmedquantitytype IN FSAQH_AirQuotationHeader.fsaqh_minimumconfirmedquantitytype%TYPE,
p_fsaqh_incoterms IN FSAQH_AirQuotationHeader.fsaqh_incoterms%TYPE,
p_fsaqh_isexportimport IN FSAQH_AirQuotationHeader.fsaqh_isexportimport%TYPE,
p_fscry_idquotation IN FSAQH_AirQuotationHeader.fscry_idquotation%TYPE,
p_fsaqh_validfrom IN FSAQH_AirQuotationHeader.fsaqh_validfrom%TYPE,
p_fsaqh_validto IN FSAQH_AirQuotationHeader.fsaqh_validto%TYPE,
p_fsaqh_weight IN FSAQH_AirQuotationHeader.fsaqh_weight%TYPE,
p_fsmea_id IN FSAQH_AirQuotationHeader.fsmea_id%TYPE,
p_fsaqh_volume IN FSAQH_AirQuotationHeader.fsaqh_volume%TYPE,
p_fsaqh_quotationpartytype IN FSAQH_AirQuotationHeader.fsaqh_quotationpartytype%TYPE,
p_fsofo_idpartner IN FSAQH_AirQuotationHeader.fsofo_idpartner%TYPE,
p_fsaqh_isshowcarrier IN FSAQH_AirQuotationHeader.fsaqh_isshowcarrier%TYPE,
p_fsaqh_withdrawreason IN FSAQH_AirQuotationHeader.fsaqh_withdrawreason%TYPE,
p_fsaqh_status IN FSAQH_AirQuotationHeader.fsaqh_status%TYPE,
p_fsaqh_remarks IN FSAQH_AirQuotationHeader.fsaqh_remarks%TYPE,
p_user_id IN FSAQH_AirQuotationHeader.fsaqh_createdby%TYPE,
p_record_status IN FSAQH_AirQuotationHeader.fsaqh_purgeyear%TYPE,
p_issuccess OUT FSAQH_AirQuotationHeader.fsaqh_purgeyear%TYPE,
p_message OUT FS_AirQuotationManagement.t_list)
AS
v_fsaqh_referencenumber FSAQH_AirQuotationHeader.fsaqh_referencenumber%TYPE;
BEGIN
IF p_record_status =fs_constants.k_insert_status THEN
SELECT 'QHA'||LPAD(quotationair_seq.NEXTVAL,7,0) INTO v_fsaqh_referencenumber
FROM fsdul_dual;

p_fsaqh_referencenumber := v_fsaqh_referencenumber;
END IF;

FSAQH_AirQuotationHeader_PKG.put_fsaqh_prc(
p_fsaqh_id => p_fsaqh_id ,
p_fsofo_id => p_fsofo_id ,
p_fsaqh_referencenumber => p_fsaqh_referencenumber ,
p_fsaqh_referencedate => p_fsaqh_referencedate ,
p_fsbue_idcustomer => p_fsbue_idcustomer ,
p_fsofo_idsalesoffice => p_fsofo_idsalesoffice ,
p_fsaqh_customerdetails => p_fsaqh_customerdetails ,
p_fsaqh_quotationtype => p_fsaqh_quotationtype ,
p_fsaqh_shipmenttype => p_fsaqh_shipmenttype ,
p_fsaqh_minimumconfirmedquantity => p_fsaqh_minimumconfirmedquantity ,
p_fsaqh_minimumconfirmedquantitytype => p_fsaqh_minimumconfirmedquantitytype ,
p_fsaqh_incoterms => p_fsaqh_incoterms ,
p_fsaqh_isexportimport => p_fsaqh_isexportimport ,
p_fscry_idquotation => p_fscry_idquotation ,
p_fsaqh_validfrom => p_fsaqh_validfrom ,
p_fsaqh_validto => p_fsaqh_validto ,
p_fsaqh_weight => p_fsaqh_weight ,
p_fsmea_id => p_fsmea_id ,
p_fsaqh_volume => p_fsaqh_volume ,
p_fsaqh_status => p_fsaqh_status ,
p_fsaqh_remarks => p_fsaqh_remarks ,
p_fsaqh_quotationpartytype => p_fsaqh_quotationpartytype,
p_fsofo_idpartner => p_fsofo_idpartner,
p_fsaqh_isshowcarrier => p_fsaqh_isshowcarrier,
p_fsaqh_withdrawreason => p_fsaqh_withdrawreason,
p_fsaqh_purgeyear => TO_CHAR(SYSDATE,'YYYY') ,
p_user_id => p_user_id ,
p_record_status => p_record_status ,
p_issuccess => p_issuccess ,
p_message => p_message
);


EXCEPTION
WHEN OTHERS
THEN
p_issuccess := fs_constants.k_failure;
fs_exception.raise_exception;
END put_fsaqh_prc;

PROCEDURE put_fsaqc_prc(
p_fsaqc_id IN OUT FSAQC_AirQuotationCarrier.fsaqc_id%TYPE,
p_fsofo_id IN FSAQC_AirQuotationCarrier.fsofo_id%TYPE,
p_fsaqh_id IN FSAQC_AirQuotationCarrier.fsaqh_id%TYPE,
p_fsbue_idcarrier IN FSAQC_AirQuotationCarrier.fsbue_idcarrier%TYPE,
p_user_id IN FSAQC_AirQuotationCarrier.fsaqc_createdby%TYPE,
p_record_status IN FSAQC_AirQuotationCarrier.fsaqc_purgeyear%TYPE,
p_issuccess OUT FSAQC_AirQuotationCarrier.fsaqc_purgeyear%TYPE,
p_message OUT FS_AirQuotationManagement.t_list)
AS
BEGIN
FSAQC_AirQuotationCarrier_PKG.put_fsaqc_prc(
p_fsaqc_id => p_fsaqc_id ,
p_fsofo_id => p_fsofo_id ,
p_fsaqh_id => p_fsaqh_id ,
p_fsbue_idcarrier => p_fsbue_idcarrier ,
p_fsaqc_purgeyear => TO_CHAR(SYSDATE,'YYYY') ,
p_user_id => p_user_id ,
p_record_status => p_record_status ,
p_issuccess => p_issuccess ,
p_message => p_message
);

EXCEPTION
WHEN OTHERS
THEN
p_issuccess := fs_constants.k_failure;
fs_exception.raise_exception;
END put_fsaqc_prc;

PROCEDURE put_fsaql_prc(
p_fsaql_id IN OUT FSAQL_AirQuotationEmail.fsaql_id%TYPE,
p_fsofo_id IN FSAQL_AirQuotationEmail.fsofo_id%TYPE,
p_fsaqh_id IN FSAQL_AirQuotationEmail.fsaqh_id%TYPE,
p_fsaql_email IN FSAQH_AirQuotationheader.fsaqh_remarks%TYPE,
p_user_id IN FSAQL_AirQuotationEmail.fsaql_createdby%TYPE,
p_record_status IN FSAQL_AirQuotationEmail.fsaql_purgeyear%TYPE,
p_issuccess OUT FSAQL_AirQuotationEmail.fsaql_purgeyear%TYPE,
p_message OUT FS_AirQuotationManagement.t_list)
AS
BEGIN
FOR i IN (SELECT REGEXP_SUBSTR (p_fsaql_email, '[^,]+', 1,LEVEL) AS fsaql_email FROM FSDUL_DUAL
CONNECT BY REGEXP_SUBSTR (p_fsaql_email,'[^,]+', 1, LEVEL) IS NOT NULL) LOOP
FSAQL_AirQuotationEmail_PKG.put_fsaql_prc(
p_fsaql_id => p_fsaql_id ,
p_fsofo_id => p_fsofo_id ,
p_fsaqh_id => p_fsaqh_id ,
p_fsaql_email => i.fsaql_email ,
p_fsaql_purgeyear => TO_CHAR(SYSDATE,'YYYY') ,
p_user_id => p_user_id ,
p_record_status => p_record_status ,
p_issuccess => p_issuccess ,
p_message => p_message
);
END LOOP;

EXCEPTION
WHEN OTHERS
THEN
p_issuccess := fs_constants.k_failure;
fs_exception.raise_exception;

END put_fsaql_prc;

PROCEDURE put_fsaqd_prc(
p_fsaqd_id IN OUT FSAQD_AirQuotationDeal.fsaqd_id%TYPE,
p_fsofo_id IN FSAQD_AirQuotationDeal.fsofo_id%TYPE,
p_fsaqh_id IN FSAQD_AirQuotationDeal.fsaqh_id%TYPE,
p_fsdlm_id IN FSAQD_AirQuotationDeal.fsdlm_id%TYPE,
p_user_id IN FSAQD_AirQuotationDeal.fsaqd_createdby%TYPE,
p_record_status IN FSAQD_AirQuotationDeal.fsaqd_purgeyear%TYPE,
p_issuccess OUT FSAQD_AirQuotationDeal.fsaqd_purgeyear%TYPE,
p_message OUT FS_AirQuotationManagement.t_list)
AS
BEGIN
FSAQD_AirQuotationDeal_PKG.put_fsaqd_prc(
p_fsaqd_id => p_fsaqd_id ,
p_fsofo_id => p_fsofo_id ,
p_fsaqh_id => p_fsaqh_id ,
p_fsdlm_id => p_fsdlm_id ,
p_fsaqd_purgeyear => TO_CHAR(SYSDATE,'YYYY'),
p_user_id => p_user_id ,
p_record_status => p_record_status ,
p_issuccess => p_issuccess ,
p_message => p_message
);

EXCEPTION
WHEN OTHERS
THEN
p_issuccess := fs_constants.k_failure;
fs_exception.raise_exception;
END put_fsaqd_prc;

PROCEDURE put_fsaqt_prc(
p_fsaqt_id IN OUT FSAQT_AirQuotationTransportStation.fsaqt_id%TYPE,
p_fsofo_id IN FSAQT_AirQuotationTransportStation.fsofo_id%TYPE,
p_fsaqh_id IN FSAQT_AirQuotationTransportStation.fsaqh_id%TYPE,
p_fsart_fromport IN FSAQH_AirQuotationHeader.fsaqh_remarks%TYPE,
p_fsart_toport IN FSAQH_AirQuotationHeader.fsaqh_remarks%TYPE,
p_transhipmentport IN FSAQH_AirQuotationHeader.fsaqh_remarks%TYPE,
p_fsaqt_travellingtime IN FSAQT_AirQuotationTransportStation.fsaqt_travellingtime%TYPE,
p_fsaqt_dayoffrequency IN FSAQT_AirQuotationTransportStation.fsaqt_dayoffrequency%TYPE,
p_routeexist IN FSAQH_AirQuotationHeader.fsaqh_remarks%TYPE,
p_user_id IN FSAQT_AirQuotationTransportStation.fsaqt_createdby%TYPE,
p_record_status IN FSAQT_AirQuotationTransportStation.fsaqt_purgeyear%TYPE,
p_issuccess OUT FSAQT_AirQuotationTransportStation.fsaqt_purgeyear%TYPE,
p_message OUT FS_AirQuotationManagement.t_list)
AS
v_transhipment_sequence fsaqt_airquotationtransportstation.fsaqt_id%TYPE;
v_fsaqp_id fsaqt_airquotationtransportstation.fsaqt_id%TYPE;
BEGIN

IF p_record_status=fs_constants.k_update_status AND p_routeexist='Y' AND p_fsaqt_id IS NOT NULL THEN

UPDATE fsaqi_airquotationinclusivecharge fsaqi SET fsaqi.fsaqi_lastupdatedby=p_user_id,fsaqi.fsaqi_lastupdatedon=SYSDATE
WHERE fsaqi.fsaqr_id IN (SELECT fsaqr.fsaqr_id FROM fsaqr_airQuotationRate fsaqr WHERE fsaqr.fsaqt_id=p_fsaqt_id);

DELETE FROM fsaqi_airquotationinclusivecharge fsaqi WHERE fsaqi.fsaqr_id IN (SELECT fsaqr.fsaqr_id FROM fsaqr_airQuotationRate fsaqr WHERE fsaqr.fsaqt_id=p_fsaqt_id);

UPDATE fsaqr_airQuotationRate fsaqr SET fsaqr.fsaqr_lastupdatedby=p_user_id,fsaqr.fsaqr_lastupdatedon=SYSDATE
WHERE fsaqr.fsaqt_id=p_fsaqt_id;

DELETE FROM fsaqr_airQuotationRate fsaqr WHERE fsaqr.fsaqt_id=p_fsaqt_id;

END IF;

FSAQT_AirQuotationTransportStation_PKG.put_fsaqt_prc(
p_fsaqt_id => p_fsaqt_id ,
p_fsofo_id => p_fsofo_id ,
p_fsaqh_id => p_fsaqh_id ,
p_fsart_idfromAirport => p_fsart_fromport ,
p_fsart_idtoAirport => p_fsart_toport ,
p_fsaqt_travellingtime => p_fsaqt_travellingtime,
p_fsaqt_dayoffrequency => p_fsaqt_dayoffrequency,
p_fsaqt_purgeyear => TO_CHAR(SYSDATE,'YYYY'),
p_user_id => p_user_id ,
p_record_status => p_record_status ,
p_issuccess => p_issuccess ,
p_message => p_message );


IF p_issuccess=fs_constants.k_success AND p_record_status=fs_constants.k_insert_status AND p_transhipmentport IS NOT NULL THEN

FOR k in (SELECT REGEXP_SUBSTR (p_transhipmentport, '[^,]+', 1,LEVEL) AS transhipmentport FROM FSDUL_DUAL
CONNECT BY REGEXP_SUBSTR (p_transhipmentport,'[^,]+', 1, LEVEL) IS NOT NULL) LOOP

v_transhipment_sequence:= NVL(v_transhipment_sequence,0)+ 1;

fsaqp_airquotationtranshipment_pkg.put_fsaqp_prc(
p_fsaqp_id => v_fsaqp_id ,
p_fsofo_id => p_fsofo_id ,
p_fsaqt_id => p_fsaqt_id ,
p_fsaqp_sequence => v_transhipment_sequence ,
p_fsart_idtranshipment => k.transhipmentport ,
p_fsaqp_purgeyear => TO_CHAR(SYSDATE,'YYYY') ,
p_user_id => p_user_id ,
p_record_status => p_record_status ,
p_issuccess => p_issuccess ,
p_message => p_message );

END LOOP;

ELSIF p_issuccess=fs_constants.k_success AND p_record_status=fs_constants.k_update_status THEN

DELETE FROM fsaqp_airquotationtranshipment WHERE fsaqt_id=p_fsaqt_id;

IF p_transhipmentport IS NOT NULL THEN

FOR k in (SELECT REGEXP_SUBSTR (p_transhipmentport, '[^,]+', 1,LEVEL) AS transhipmentport FROM FSDUL_DUAL
CONNECT BY REGEXP_SUBSTR (p_transhipmentport,'[^,]+', 1, LEVEL) IS NOT NULL) LOOP

v_transhipment_sequence:= NVL(v_transhipment_sequence,0)+ 1;

fsaqp_airquotationtranshipment_pkg.put_fsaqp_prc(
p_fsaqp_id => v_fsaqp_id ,
p_fsofo_id => p_fsofo_id ,
p_fsaqt_id => p_fsaqt_id ,
p_fsaqp_sequence => v_transhipment_sequence ,
p_fsart_idtranshipment => k.transhipmentport ,
p_fsaqp_purgeyear => TO_CHAR(SYSDATE,'YYYY') ,
p_user_id => p_user_id ,
p_record_status => fs_constants.k_insert_status ,
p_issuccess => p_issuccess ,
p_message => p_message );

END LOOP;

END IF;

END IF;



EXCEPTION
WHEN OTHERS
THEN
p_issuccess := fs_constants.k_failure;
fs_exception.raise_exception;

END put_fsaqt_prc;

PROCEDURE put_fsaqe_prc(
p_fsaqe_id IN OUT FSAQE_AirQuotationEquipment.fsaqe_id%TYPE,
p_fsofo_id IN FSAQE_AirQuotationEquipment.fsofo_id%TYPE,
p_fsaqt_id IN FSAQE_AirQuotationEquipment.fsaqt_id%TYPE,
p_fseqt_sizetypeuld IN FSAQE_AirQuotationEquipment.fseqt_sizetypeuld%TYPE,
p_fsaqe_cargotype IN FSAQE_AirQuotationEquipment.fsaqe_cargotype%TYPE,
p_fscmg_group IN FSAQH_AirQuotationheader.fsaqh_remarks%TYPE,
p_user_id IN FSAQE_AirQuotationEquipment.fsaqe_createdby%TYPE,
p_record_status IN FSAQE_AirQuotationEquipment.fsaqe_purgeyear%TYPE,
p_issuccess OUT FSAQE_AirQuotationEquipment.fsaqe_purgeyear%TYPE,
p_message OUT FS_AirQuotationManagement.t_list)
AS
v_fsaqm_id fsaqe_airquotationequipment.fsaqe_id%TYPE;
BEGIN

IF p_record_status=fs_constants.k_insert_status AND p_fsaqe_cargotype IS NOT NULL THEN

FOR i IN (SELECT REGEXP_SUBSTR (p_fsaqe_cargotype, '[^,]+', 1,LEVEL) AS fsaqe_cargotype FROM FSDUL_DUAL
CONNECT BY REGEXP_SUBSTR (p_fsaqe_cargotype,'[^,]+', 1, LEVEL) IS NOT NULL) LOOP
FSAQE_AirQuotationEquipment_PKG.put_fsaqe_prc(
p_fsaqe_id => p_fsaqe_id ,
p_fsofo_id => p_fsofo_id ,
p_fsaqt_id => p_fsaqt_id ,
p_fseqt_sizetypeuld => p_fseqt_sizetypeuld ,
p_fsaqe_cargotype => i.fsaqe_cargotype ,
p_fsaqe_purgeyear => TO_CHAR(SYSDATE,'YYYY') ,
p_user_id => p_user_id ,
p_record_status => p_record_status ,
p_issuccess => p_issuccess ,
p_message => p_message
);
END LOOP;

ELSIF p_record_status=fs_constants.k_update_status THEN

DELETE FROM FSAQE_AirQuotationEquipment WHERE fsaqt_id=p_fsaqt_id;

IF p_fsaqe_cargotype IS NOT NULL THEN

FOR i IN (SELECT REGEXP_SUBSTR (p_fsaqe_cargotype, '[^,]+', 1,LEVEL) AS fsaqe_cargotype FROM FSDUL_DUAL
CONNECT BY REGEXP_SUBSTR (p_fsaqe_cargotype,'[^,]+', 1, LEVEL) IS NOT NULL) LOOP
FSAQE_AirQuotationEquipment_PKG.put_fsaqe_prc(
p_fsaqe_id => p_fsaqe_id ,
p_fsofo_id => p_fsofo_id ,
p_fsaqt_id => p_fsaqt_id ,
p_fseqt_sizetypeuld => p_fseqt_sizetypeuld ,
p_fsaqe_cargotype => i.fsaqe_cargotype ,
p_fsaqe_purgeyear => TO_CHAR(SYSDATE,'YYYY') ,
p_user_id => p_user_id ,
p_record_status => fs_constants.k_insert_status ,
p_issuccess => p_issuccess ,
p_message => p_message
);
END LOOP;

END IF;

END IF;


IF p_record_status=fs_constants.k_insert_status AND p_fscmg_group IS NOT NULL THEN

FOR j IN (SELECT REGEXP_SUBSTR (p_fscmg_group, '[^,]+', 1,LEVEL) AS fscmg_group FROM FSDUL_DUAL
CONNECT BY REGEXP_SUBSTR (p_fscmg_group,'[^,]+', 1, LEVEL) IS NOT NULL) LOOP

FSAQM_AirQuotationCommodity_PKG.put_fsaqm_prc(
p_fsaqm_id => v_fsaqm_id ,
p_fsofo_id => p_fsofo_id ,
p_fsaqt_id => p_fsaqt_id ,
p_fscmg_id => j.fscmg_group ,
p_fsaqm_purgeyear => TO_CHAR(SYSDATE,'YYYY') ,
p_user_id => p_user_id ,
p_record_status => p_record_status ,
p_issuccess => p_issuccess ,
p_message => p_message
);

END LOOP;

ELSIF p_record_status=fs_constants.k_update_status THEN

DELETE FROM FSAQM_AirQuotationCommodity WHERE fsaqt_id=p_fsaqt_id;

IF p_fscmg_group IS NOT NULL THEN

FOR j IN (SELECT REGEXP_SUBSTR (p_fscmg_group, '[^,]+', 1,LEVEL) AS fscmg_group FROM FSDUL_DUAL
CONNECT BY REGEXP_SUBSTR (p_fscmg_group,'[^,]+', 1, LEVEL) IS NOT NULL) LOOP

FSAQM_AirQuotationCommodity_PKG.put_fsaqm_prc(
p_fsaqm_id => v_fsaqm_id ,
p_fsofo_id => p_fsofo_id ,
p_fsaqt_id => p_fsaqt_id ,
p_fscmg_id => j.fscmg_group ,
p_fsaqm_purgeyear => TO_CHAR(SYSDATE,'YYYY') ,
p_user_id => p_user_id ,
p_record_status => p_record_status ,
p_issuccess => p_issuccess ,
p_message => p_message
);

END LOOP;

END IF;

END IF;


EXCEPTION
WHEN OTHERS
THEN
p_issuccess := fs_constants.k_failure;
fs_exception.raise_exception;
END put_fsaqe_prc;


PROCEDURE put_fsaqu_prc(
p_fsaqu_id IN OUT FSAQU_AirQuotationIntermodel.fsaqu_id%TYPE,
p_fsofo_id IN FSAQU_AirQuotationIntermodel.fsofo_id%TYPE,
p_fsaqh_id IN FSAQU_AirQuotationIntermodel.fsaqh_id%TYPE,
p_fsaqu_shipmenttype IN FSAQU_AirQuotationIntermodel.fsaqu_shipmenttype%TYPE,
p_fsaqu_ismultimode IN FSAQU_AirQuotationIntermodel.fsaqu_ismultimode%TYPE,
p_fsaqu_ismultistop IN FSAQU_AirQuotationIntermodel.fsaqu_ismultistop%TYPE,
p_fsloc_idfrom IN FSAQU_AirQuotationIntermodel.fsloc_idfrom%TYPE,
p_fsaqu_zipcodefrom IN FSAQU_AirQuotationIntermodel.fsaqu_zipcodefrom%TYPE,
p_fsaqu_zipcodeto IN FSAQU_AirQuotationIntermodel.fsaqu_zipcodeto%TYPE,
p_fsloc_idto IN FSAQU_AirQuotationIntermodel.fsloc_idto%TYPE,
p_fsaqu_modeoftransport IN FSAQU_AirQuotationIntermodel.fsaqu_modeoftransport%TYPE,
p_fsaqu_transit IN FSAQU_AirQuotationIntermodel.fsaqu_transit%TYPE,
p_fsaqu_transittype IN FSAQU_AirQuotationIntermodel.fsaqu_transittype%TYPE,
p_fsaqu_specialinstruction IN FSAQU_AirQuotationIntermodel.fsaqu_specialinstruction%TYPE,
p_fsaqu_fromlocationaddress IN FSAQU_AirQuotationIntermodel.fsaqu_fromlocationaddress%TYPE,
p_fsaqu_tolocationaddress IN FSAQU_AirQuotationIntermodel.fsaqu_tolocationaddress%TYPE,
p_fsaqu_locationtype IN FSAQU_AirQuotationIntermodel.fsaqu_locationtype%TYPE,
p_fsaqu_idparent IN FSAQU_AirQuotationIntermodel.fsaqu_idparent%TYPE,
p_fsaqu_purgeyear IN FSAQU_AirQuotationIntermodel.fsaqu_purgeyear%TYPE,
p_user_id IN FSAQU_AirQuotationIntermodel.fsaqu_createdby%TYPE,
p_record_status IN FSAQU_AirQuotationIntermodel.fsaqu_purgeyear%TYPE,
p_issuccess OUT FSAQU_AirQuotationIntermodel.fsaqu_purgeyear%TYPE,
p_message OUT FS_AirQuotationManagement.t_list)
AS
BEGIN
FSAQU_AirQuotationIntermodel_PKG.put_fsaqu_prc
(
p_fsaqu_id => p_fsaqu_id ,
p_fsofo_id => p_fsofo_id ,
p_fsaqh_id => p_fsaqh_id ,
p_fsaqu_shipmenttype => p_fsaqu_shipmenttype ,
p_fsaqu_ismultimode => p_fsaqu_ismultimode ,
p_fsaqu_ismultistop => p_fsaqu_ismultistop ,
p_fsloc_idfrom => p_fsloc_idfrom ,
p_fsaqu_zipcodefrom => p_fsaqu_zipcodefrom ,
p_fsaqu_zipcodeto => p_fsaqu_zipcodeto ,
p_fsloc_idto => p_fsloc_idto ,
p_fsaqu_modeoftransport => p_fsaqu_modeoftransport ,
p_fsaqu_transit => p_fsaqu_transit ,
p_fsaqu_transittype => p_fsaqu_transittype ,
p_fsaqu_specialinstruction => p_fsaqu_specialinstruction ,
p_fsaqu_fromlocationaddress => p_fsaqu_fromlocationaddress ,
p_fsaqu_tolocationaddress => p_fsaqu_tolocationaddress ,
p_fsaqu_locationtype => p_fsaqu_locationtype ,
p_fsaqu_idparent => p_fsaqu_idparent ,
p_fsaqu_purgeyear => p_fsaqu_purgeyear ,
p_user_id => p_user_id ,
p_record_status => p_record_status ,
p_issuccess => p_issuccess ,
p_message => p_message
);

EXCEPTION
WHEN OTHERS
THEN
p_issuccess := fs_constants.k_failure;
fs_exception.raise_exception;




END put_fsaqu_prc;


PROCEDURE put_fsaqr_prc(
p_fsaqr_id IN OUT FSAQR_AirQuotationRate.fsaqr_id%TYPE,
p_fsofo_id IN FSAQR_AirQuotationRate.fsofo_id%TYPE,
p_fsaqt_id IN FSAQR_AirQuotationRate.fsaqt_id%TYPE,
p_fsaqu_id IN FSAQR_AirQuotationRate.fsaqu_id%TYPE,
p_fsatr_id IN FSAQR_AirQuotationRate.fsatr_id%TYPE,
p_fsasr_id IN FSAQR_AirQuotationRate.fsasr_id%TYPE,
p_fslar_id IN FSAQR_AirQuotationRate.fslar_id%TYPE,
p_fschg_id IN FSAQR_AirQuotationRate.fschg_id%TYPE,
p_fsaqr_basis IN FSAQR_AirQuotationRate.fsaqr_basis%TYPE,
p_fsaqr_cargotype IN FSAQR_AirQuotationRate.fsaqr_cargotype%TYPE,
p_fseqt_sizetypeuld IN FSAQR_AirQuotationRate.fseqt_sizetypeuld%TYPE,
p_fscmg_id IN FSAQR_AirQuotationRate.fscmg_id%TYPE,
p_fsaqr_weighttype IN FSAQR_AirQuotationRate.fsaqr_weighttype%TYPE,
p_fscry_id IN FSAQR_AirQuotationRate.fscry_id%TYPE,
p_fsaqr_ispax IN FSAQR_AirQuotationRate.fsaqr_ispax%TYPE,
p_fsaqr_weight IN FSAQR_AirQuotationRate.fsaqr_weight%TYPE,
p_fsmea_id IN FSAQR_AirQuotationRate.fsmea_id%TYPE,
p_fsaqr_tariffrate IN FSAQR_AirQuotationRate.fsaqr_tariffrate%TYPE,
p_fsaqr_markuptype IN FSAQR_AirQuotationRate.fsaqr_markuptype%TYPE,
p_fsaqr_markupvalue IN FSAQR_AirQuotationRate.fsaqr_markupvalue%TYPE,
p_fsaqr_sellingrate IN FSAQR_AirQuotationRate.fsaqr_sellingrate%TYPE,
p_fsaqr_mintariffrate IN FSAQR_AirQuotationRate.fsaqr_mintariffrate%TYPE,
p_fsaqr_minmarkuptype IN FSAQR_AirQuotationRate.fsaqr_minmarkuptype%TYPE,
p_fsaqr_minmarkupvalue IN FSAQR_AirQuotationRate.fsaqr_minmarkupvalue%TYPE,
p_fsaqr_minsellingrate IN FSAQR_AirQuotationRate.fsaqr_minsellingrate%TYPE,
p_fsaqr_additionaldayfee IN FSAQR_AirQuotationRate.fsaqr_additionaldayfee%TYPE,
p_fsaqr_pivotweight IN FSAQR_AirQuotationRate.fsaqr_pivotweight%TYPE,
p_fsbue_idcarrier IN FSAQR_AirQuotationRate.fsbue_idcarrier%TYPE,
p_fsaqr_isexportimport IN FSAQR_AirQuotationRate.fsaqr_isexportimport%TYPE,
p_fsaqr_remarks IN FSAQR_AirQuotationRate.fsaqr_remarks%TYPE,
p_inclusivecharge IN FSAQR_AirQuotationRate.fsaqr_remarks%TYPE,
p_user_id IN FSAQR_AirQuotationRate.fsaqr_createdby%TYPE,
p_record_status IN FSAQR_AirQuotationRate.fsaqr_purgeyear%TYPE,
p_issuccess OUT FSAQR_AirQuotationRate.fsaqr_purgeyear%TYPE,
p_message OUT FS_AirQuotationManagement.t_list)
AS

v_fsaqi_id fsaqi_airquotationinclusivecharge.fsaqi_id%TYPE;

BEGIN
FSAQR_AirQuotationRate_PKG.put_fsaqr_prc(
p_fsaqr_id => p_fsaqr_id ,
p_fsofo_id => p_fsofo_id ,
p_fsaqt_id => p_fsaqt_id ,
p_fsaqu_id => p_fsaqu_id ,
p_fsatr_id => p_fsatr_id ,
p_fsasr_id => p_fsasr_id ,
p_fslar_id => p_fslar_id ,
p_fschg_id => p_fschg_id ,
p_fsaqr_basis => p_fsaqr_basis ,
p_fsaqr_cargotype => p_fsaqr_cargotype ,
p_fseqt_sizetypeuld => p_fseqt_sizetypeuld ,
p_fscmg_id => p_fscmg_id ,
p_fsaqr_weighttype => p_fsaqr_weighttype ,
p_fscry_id => p_fscry_id ,
p_fsaqr_ispax => p_fsaqr_ispax ,
p_fsaqr_weight => p_fsaqr_weight ,
p_fsmea_id => p_fsmea_id ,
p_fsaqr_tariffrate => p_fsaqr_tariffrate ,
p_fsaqr_markuptype => p_fsaqr_markuptype ,
p_fsaqr_markupvalue => p_fsaqr_markupvalue ,
p_fsaqr_sellingrate => p_fsaqr_sellingrate ,
p_fsaqr_mintariffrate => p_fsaqr_mintariffrate ,
p_fsaqr_minmarkuptype => p_fsaqr_minmarkuptype ,
p_fsaqr_minmarkupvalue => p_fsaqr_minmarkupvalue ,
p_fsaqr_minsellingrate => p_fsaqr_minsellingrate ,
p_fsaqr_remarks => p_fsaqr_remarks ,
p_fsaqr_additionaldayfee => p_fsaqr_additionaldayfee ,
p_fsaqr_pivotweight => p_fsaqr_pivotweight ,
p_fsbue_idcarrier => p_fsbue_idcarrier,
p_fsaqr_isexportimport => p_fsaqr_isexportimport,
p_fsaqr_purgeyear => TO_CHAR(SYSDATE,'YYYY') ,
p_user_id => p_user_id ,
p_record_status => p_record_status ,
p_issuccess => p_issuccess ,
p_message => p_message
);

IF p_inclusivecharge IS NOT NULL AND p_issuccess=fs_constants.k_success AND p_record_status=fs_constants.k_insert_status THEN

FOR i IN (SELECT REGEXP_SUBSTR (p_inclusivecharge, '[^,]+', 1,LEVEL) AS fschg_id FROM FSDUL_DUAL
CONNECT BY REGEXP_SUBSTR (p_inclusivecharge,'[^,]+', 1, LEVEL) IS NOT NULL) LOOP

fsaqi_airquotationinclusivecharge_PKG.put_fsaqi_prc(
p_fsaqi_id => v_fsaqi_id ,
p_fsofo_id => p_fsofo_id ,
p_fsaqr_id => p_fsaqr_id ,
p_fschg_id => i.fschg_id ,
p_fsaqi_purgeyear => TO_CHAR(SYSDATE,'YYYY') ,
p_user_id => p_user_id ,
p_record_status => p_record_status ,
p_issuccess => p_issuccess ,
p_message => p_message
);

END LOOP;

ELSIF p_issuccess=fs_constants.k_success AND p_record_status=fs_constants.k_update_status THEN

DELETE FROM fsaqi_airquotationinclusivecharge WHERE fsaqr_id=p_fsaqr_id;

IF p_inclusivecharge IS NOT NULL THEN

FOR i IN (SELECT REGEXP_SUBSTR (p_inclusivecharge, '[^,]+', 1,LEVEL) AS fschg_id FROM FSDUL_DUAL
CONNECT BY REGEXP_SUBSTR (p_inclusivecharge,'[^,]+', 1, LEVEL) IS NOT NULL) LOOP

fsaqi_airquotationinclusivecharge_PKG.put_fsaqi_prc(
p_fsaqi_id => v_fsaqi_id ,
p_fsofo_id => p_fsofo_id ,
p_fsaqr_id => p_fsaqr_id ,
p_fschg_id => i.fschg_id ,
p_fsaqi_purgeyear => TO_CHAR(SYSDATE,'YYYY') ,
p_user_id => p_user_id ,
p_record_status => fs_constants.k_insert_status ,
p_issuccess => p_issuccess ,
p_message => p_message
);

END LOOP;

END IF;

END IF;

EXCEPTION
WHEN OTHERS
THEN
p_issuccess := fs_constants.k_failure;
fs_exception.raise_exception;

END put_fsaqr_prc;

PROCEDURE put_fsaqw_prc(
p_fsaqw_id IN OUT FSAQW_AirQuotationSlabRate.fsaqw_id%TYPE,
p_fsofo_id IN FSAQW_AirQuotationSlabRate.fsofo_id%TYPE,
p_fsaqr_id IN FSAQW_AirQuotationSlabRate.fsaqr_id%TYPE,
p_fsatm_id IN FSAQW_AirQuotationSlabRate.fsatm_id%TYPE,
p_fsaqw_buyingrate IN FSAQW_AirQuotationSlabRate.fsaqw_buyingrate%TYPE,
p_fsaqw_markupvalue IN FSAQW_AirQuotationSlabRate.fsaqw_markupvalue%TYPE,
p_fsaqw_sellingrate IN FSAQW_AirQuotationSlabRate.fsaqw_sellingrate%TYPE,
P_fsaqw_weight IN FSAQW_AirQuotationSlabRate.fsaqw_weight%TYPE,
p_user_id IN FSAQR_AirQuotationRate.fsaqr_createdby%TYPE,
p_record_status IN FSAQR_AirQuotationRate.fsaqr_purgeyear%TYPE,
p_issuccess OUT FSAQR_AirQuotationRate.fsaqr_purgeyear%TYPE,
p_message OUT FS_AirQuotationManagement.t_list)
AS
BEGIN
FSAQW_AirQuotationSlabRate_PKG.put_fsaqw_prc(
p_fsaqw_id => p_fsaqw_id,
p_fsofo_id => p_fsofo_id,
p_fsaqr_id => p_fsaqr_id,
p_fsatm_id => p_fsatm_id,
p_fsaqw_buyingrate => p_fsaqw_buyingrate,
p_fsaqw_markupvalue => p_fsaqw_markupvalue,
p_fsaqw_sellingrate => p_fsaqw_sellingrate,
P_fsaqw_weight => P_fsaqw_weight,
p_fsaqw_purgeyear => TO_CHAR(SYSDATE,'YYYY'),
p_user_id => p_user_id,
p_record_status => p_record_status,
p_issuccess => p_issuccess,
p_message => p_message);

EXCEPTION
WHEN OTHERS
THEN
p_issuccess := fs_constants.k_failure;
fs_exception.raise_exception;

END put_fsaqw_prc;



END FS_AirQuotationManagement;