CREATE OR REPLACE PACKAGE mip_quotation IS -- Author : HARDYA -- Created : 15/11/2007 11:27:58 -- Purpose : Handle life-cycle of quotations TYPE t_tab_messages IS TABLE OF VARCHAR2(240); /** Determines whether the given enquiry is ready to quote for i.e. have all the mandatory fields been completed %param p_enqu_id the id of the enquiry to be checked %return TRUE if the enquiry can be quoted for */ FUNCTION ready_for_quote(p_enqu_id IN enquiries.id%TYPE) RETURN BOOLEAN; /** Determines whether the given enquiry is ready to quote for i.e. have all the mandatory fields been completed %param p_enqu_id the id of the enquiry to be checked %p_mandatory_checks contains reasons for the enquiry *not* being ready to quote for %p_quote_is_ready TRUE if the enquiry can be quoted for */ PROCEDURE ready_for_quote(p_enqu_id IN enquiries.id%TYPE ,p_mandatory_checks OUT mip_mandatory.t_mandatory_checks ,p_enquiry_is_ready OUT BOOLEAN); /** Validate an enquiry row of date for mandatory fields Store the results for later retrieval (must be same transaction) %param p_rec enquiry data to be validated */ PROCEDURE validate(p_rec IN mip_enquiries_helper.t_rec_enquiries); /** Retrieve the results of a mandatory field validation for the given field Must be performed in the same transaction as the validate %param p_field_name enquiry field who validation message is required %return validation error message or NULL if validation was successful */ FUNCTION validation_result(p_enqu_id IN enquiries.id%TYPE ,p_field_name IN VARCHAR2) RETURN VARCHAR2; /** Generate quotes in response to a 'request for quote' against an enquiry %param p_enqu_id the id of the enquiry to be checked %param p_prty_id the id of the party that requested the quotes %param p_owner_prty_id the id of party that owns the quotes (defaults to the requestor) */ PROCEDURE produce_quotes(p_enqu_id IN enquiries.id%TYPE ,p_rfq_prty_id IN parties.id%TYPE ,p_owner_prty_id IN parties.id%TYPE DEFAULT NULL); FUNCTION return_mandatory_messages(p_enqu_id IN enquiries.id%TYPE) RETURN VARCHAR2; /** Make quote available %param p_qute_id id of the quote to be marked as available */ PROCEDURE make_quote_available(p_qute_id IN quotes.id%TYPE); /** Accept a quote %param p_qute_id id of the quote to be accepted %param p_prty_id id of the person accepting the quote %param p_owner_prty_id id of the person who owns the accepted quote (defaults to acceptor) %param p_description optional description to be recorded with the event %param p_event_date optional date for this event (defaults to now) */ PROCEDURE accept_quote(p_qute_id IN quotes.id%TYPE ,p_prty_id IN parties.id%TYPE ,p_owner_prty_id IN parties.id%TYPE DEFAULT NULL ,p_description quote_events.description%TYPE DEFAULT NULL ,p_event_date IN DATE DEFAULT SYSDATE); /** Reject a quote %param p_qute_id id of the quote to be rejected %param p_prty_id id of the person rejecting the quote %param p_owner_prty_id id of the person who owns the rejected quote (defaults to rejector) %param p_description optional description to be recorded with the event %param p_event_date optional date for this event (defaults to now) */ PROCEDURE reject_quote(p_qute_id IN quotes.id%TYPE ,p_prty_id IN parties.id%TYPE ,p_owner_prty_id IN parties.id%TYPE DEFAULT NULL ,p_description quote_events.description%TYPE DEFAULT NULL ,p_event_date IN DATE DEFAULT SYSDATE); /** Reject all quotes associated with enquiry %param p_enqu_id id of the enquiry for qhich all quotes are to be rejected %param p_prty_id id of the person rejecting the quotes %param p_owner_prty_id id of the person who owns the rejected quotes (defaults to rejector) %param p_description optional description to be recorded with the event %param p_event_date optional date for this event (defaults to now) */ PROCEDURE reject_all_quotes(p_enqu_id IN enquiries.id%TYPE ,p_prty_id IN parties.id%TYPE ,p_owner_prty_id IN parties.id%TYPE DEFAULT NULL ,p_description quote_events.description%TYPE DEFAULT NULL ,p_event_date IN DATE DEFAULT SYSDATE); /** Select a quote for detailed quotation %param p_qute_id id of the quote to be selected %param p_prty_id id of the person selecting the quote %param p_owner_prty_id id of the person who owns the selected quote (defaults to selector) %param p_description optional description to be recorded with the event %param p_event_date optional date for this event (defaults to now) */ PROCEDURE select_quote(p_qute_id IN quotes.id%TYPE ,p_prty_id IN parties.id%TYPE ,p_owner_prty_id IN parties.id%TYPE DEFAULT NULL ,p_description quote_events.description%TYPE DEFAULT NULL ,p_event_date IN DATE DEFAULT SYSDATE); /** Lapse a quote %param p_qute_id id of the quote to be selected %param p_description optional description to be recorded with the event %param p_event_date optional date for this event (defaults to now) */ PROCEDURE lapse_quote(p_qute_id IN quotes.id%TYPE ,p_description quote_events.description%TYPE DEFAULT NULL ,p_event_date IN DATE DEFAULT SYSDATE); PROCEDURE lapse_quotes_job; /** Delete a quote %param p_qute_id id of the quote to be deleted %param p_message reason for not deleting quote %return boolean {*} TRUE - quote has been deleted {*} FALSE or UNKNOWN - quote has not been deleted */ FUNCTION delete_quote(p_qute_id IN quotes.id%TYPE ,p_message OUT VARCHAR2) RETURN BOOLEAN; /** Delete a quote %param p_qute_id id of the quote to be deleted %return reason for not deleting quote */ FUNCTION delete_quote(p_qute_id IN quotes.id%TYPE) RETURN VARCHAR2; /** Delete all quotes associated with an enquiry %param p_enqu_id id of the quote to be deleted %param p_message reason for not deleting quote %return boolean {*} TRUE - all quotes have been deleted {*} FALSE or UNKNOWN - quotes have not been deleted */ FUNCTION delete_quotes_for_enquiry(p_enqu_id IN enquiries.id%TYPE ,p_message OUT VARCHAR2) RETURN BOOLEAN; /** Return a message if the Tripartite agreement is broached %param p_rec record containing enquiry details to be checked %return message is the agreement was broached */ FUNCTION tripartite_agreement_message(p_rec IN mip_enquiries_helper.t_rec_enquiries) RETURN VARCHAR2; FUNCTION get_qmax_from_mesc(p_mesc_code IN meter_size_codes.code%TYPE) RETURN meter_size_codes.qmax%TYPE; END mip_quotation; / CREATE OR REPLACE PACKAGE BODY mip_quotation IS PROCEDURE email_agent_q_sel(p_enqu_id IN enquiries.id%TYPE); PROCEDURE email_agent_quote_acceptance(p_enqu_id enquiries.id%TYPE); PROCEDURE email_agent_mq_available(p_enqu_id enquiries.id%TYPE); PROCEDURE email_iu_quote_acceptance(p_enqu_id enquiries.id%TYPE); PROCEDURE email_support(p_subject IN VARCHAR2 ,p_enqu_id IN enquiries.id%TYPE); SUBTYPE t_internal_or_external IS VARCHAR2(8); gc_internal_reason CONSTANT t_internal_or_external := 'INTERNAL'; gc_external_reason CONSTANT t_internal_or_external := 'EXTERNAL'; SUBTYPE t_manual_or_automatic_quote IS VARCHAR2(2); gc_manual_quote CONSTANT t_manual_or_automatic_quote := 'MQ'; gc_automatic_quote CONSTANT t_manual_or_automatic_quote := 'AQ'; g_email_plain_body CLOB; g_email_html_body CLOB; gc_newline CONSTANT CHAR(2) DEFAULT CHR(10) || CHR(13); SUBTYPE t_enqu IS enquiries%ROWTYPE; SUBTYPE t_email_reason IS VARCHAR2(80); gc_agent_mq_req_notification t_email_reason := 'AGENT_MQ_REQ_NOTIFICATION'; gc_agent_q_sel_notification t_email_reason := 'AGENT_Q_SEL_NOTIFICATION'; gc_agent_q_acc_notification t_email_reason := 'AGENT_Q_ACC_NOTIFICATION'; gc_agent_mq_avail_notification t_email_reason := 'AGENT_MQ_AVAIL_NOTIFICATION'; gc_int_q_acc_notification t_email_reason := 'INTERNAL_Q_ACC_NOTIFICATION'; gc_int_mq_req_notification t_email_reason := 'AGENT_MQ_REQ_NOTIFICATION'; TYPE t_rec_additional_costs IS RECORD( adit_code additional_items.code%TYPE ,svcpt_code service_pressure_types.code%TYPE ,lead_time additional_items.lead_time%TYPE ,selling_price costs.selling_price%TYPE ,cost_price costs.cost_price%TYPE ,delivery_cost costs.delivery_cost%TYPE); SUBTYPE t_quote_row IS quotes%ROWTYPE; TYPE t_rec_attachment IS RECORD( content wwv_flow_files.blob_content%TYPE := empty_blob ,filename wwv_flow_files.filename%TYPE ,mime_type wwv_flow_files.mime_type%TYPE); TYPE t_rec_metr_details IS RECORD( code meters.code%TYPE ,mesc_code meters.mesc_code%TYPE ,mety_code meters.mety_code%TYPE ,prty_id meters.prty_id%TYPE); PROCEDURE pl(p_in VARCHAR2 ,p_line IN NUMBER DEFAULT NULL) IS BEGIN $IF mip_debug_constants.debugging OR mip_debug_constants.quotation $THEN mip_debug.pl(p_unit => $$PLSQL_UNIT ,p_line => p_line ,p_in => p_in); $END NULL; END pl; PROCEDURE add_quote_reason(p_enqu_id IN enquiries.id%TYPE ,p_reason IN VARCHAR2 --quote_reasoning.reason%TYPE ,p_internal_or_external IN quote_reasoning.internal_or_external%TYPE) IS BEGIN pl('add_quote_reason:' || p_enqu_id || ':' || p_reason ,$$PLSQL_LINE); BEGIN INSERT INTO quote_reasoning (enqu_id ,reason ,internal_or_external ,id) VALUES (p_enqu_id ,SUBSTR(p_reason ,1 ,239) ,p_internal_or_external ,qure_seq.nextval); EXCEPTION WHEN OTHERS THEN pl('add_quote_reason:' || SQLERRM ,$$PLSQL_LINE); RAISE; END; END add_quote_reason; PROCEDURE add_quote_event(p_qute_id IN quotes.id%TYPE ,p_qust_code quote_statuses.code%TYPE ,p_description quote_events.description%TYPE DEFAULT NULL ,p_event_date DATE DEFAULT SYSDATE) IS BEGIN pl('add_quote_event:entry:' || p_qute_id || ':' || p_qust_code ,$$PLSQL_LINE); INSERT INTO quote_events (qute_id ,qust_code ,event_date ,description ,id) VALUES (p_qute_id ,p_qust_code ,p_event_date ,p_description ,quev_seq.nextval); pl('add_quote_event:exit' ,$$PLSQL_LINE); END add_quote_event; PROCEDURE add_enquiry_event(p_enqu_id IN enquiries.id%TYPE ,p_enst_code enquiry_status_types.code%TYPE ,p_description enquiry_events.description%TYPE DEFAULT NULL ,p_event_date DATE DEFAULT SYSDATE) IS BEGIN INSERT INTO enquiry_events (enqu_id ,enst_code ,event_date ,description ,id) VALUES (p_enqu_id ,p_enst_code ,p_event_date ,p_description ,enev_seq.nextval); END add_enquiry_event; PROCEDURE add_quote_role(p_qute_id IN quotes.id%TYPE ,p_prty_id IN parties.id%TYPE ,p_rt_code quote_roles.rt_code%TYPE ,p_description quote_events.description%TYPE DEFAULT NULL ,p_start_date DATE DEFAULT SYSDATE) IS BEGIN INSERT INTO quote_roles (qute_id ,prty_id ,rt_code ,start_date ,description) VALUES (p_qute_id ,p_prty_id ,p_rt_code ,p_start_date ,p_description); END add_quote_role; PROCEDURE accept_quote(p_qute_id IN quotes.id%TYPE ,p_prty_id IN parties.id%TYPE ,p_owner_prty_id IN parties.id%TYPE DEFAULT NULL ,p_description quote_events.description%TYPE DEFAULT NULL ,p_event_date IN DATE DEFAULT SYSDATE) IS l_enqu_id enquiries.id%TYPE; BEGIN add_quote_event(p_qute_id => p_qute_id ,p_qust_code => 'ACCEPTED' ,p_event_date => p_event_date ,p_description => p_description); add_quote_role(p_qute_id => p_qute_id ,p_prty_id => nvl(p_owner_prty_id ,p_prty_id) ,p_rt_code => 'Q ACC' ,p_start_date => p_event_date); add_quote_role(p_qute_id => p_qute_id ,p_prty_id => p_prty_id ,p_rt_code => 'Q SUBACC' ,p_start_date => p_event_date); SELECT enqu_id INTO l_enqu_id FROM quotes WHERE id = p_qute_id; email_agent_quote_acceptance(p_enqu_id => l_enqu_id); email_iu_quote_acceptance(p_enqu_id => l_enqu_id); END accept_quote; PROCEDURE reject_quote(p_qute_id IN quotes.id%TYPE ,p_prty_id IN parties.id%TYPE ,p_owner_prty_id IN parties.id%TYPE DEFAULT NULL ,p_description quote_events.description%TYPE DEFAULT NULL ,p_event_date IN DATE DEFAULT SYSDATE) IS BEGIN add_quote_event(p_qute_id => p_qute_id ,p_qust_code => 'REJECTED' ,p_event_date => p_event_date ,p_description => p_description); add_quote_role(p_qute_id => p_qute_id ,p_prty_id => nvl(p_owner_prty_id ,p_prty_id) ,p_rt_code => 'Q REJ' ,p_start_date => p_event_date); add_quote_role(p_qute_id => p_qute_id ,p_prty_id => p_prty_id ,p_rt_code => 'Q SUBREJ' ,p_start_date => p_event_date); END reject_quote; PROCEDURE lapse_quote(p_qute_id IN quotes.id%TYPE ,p_description quote_events.description%TYPE DEFAULT NULL ,p_event_date IN DATE DEFAULT SYSDATE) IS BEGIN add_quote_event(p_qute_id => p_qute_id ,p_qust_code => 'LAPSED' ,p_event_date => p_event_date ,p_description => p_description); END lapse_quote; PROCEDURE reject_all_quotes(p_enqu_id IN enquiries.id%TYPE ,p_prty_id IN parties.id%TYPE ,p_owner_prty_id IN parties.id%TYPE DEFAULT NULL ,p_description quote_events.description%TYPE DEFAULT NULL ,p_event_date IN DATE DEFAULT SYSDATE) IS BEGIN FOR l_rec IN (SELECT id FROM quotes WHERE enqu_id = p_enqu_id) LOOP reject_quote(p_qute_id => l_rec.id ,p_prty_id => p_prty_id ,p_owner_prty_id => p_owner_prty_id ,p_event_date => p_event_date ,p_description => p_description); END LOOP; END reject_all_quotes; PROCEDURE select_quote(p_qute_id IN quotes.id%TYPE ,p_prty_id IN parties.id%TYPE ,p_owner_prty_id IN parties.id%TYPE DEFAULT NULL ,p_description quote_events.description%TYPE DEFAULT NULL ,p_event_date IN DATE DEFAULT SYSDATE) IS l_enqu_id enquiries.id%TYPE; BEGIN pl('select_quote:entry:' || p_qute_id ,$$PLSQL_LINE); add_quote_event(p_qute_id => p_qute_id ,p_qust_code => 'SELECTED' ,p_event_date => p_event_date ,p_description => p_description); add_quote_role(p_qute_id => p_qute_id ,p_prty_id => nvl(p_owner_prty_id ,p_prty_id) ,p_rt_code => 'Q SEL' ,p_description => p_description ,p_start_date => p_event_date); FOR l_rec IN (SELECT id FROM quotes WHERE enqu_id = (SELECT enqu_id FROM quotes WHERE id = p_qute_id) AND id <> p_qute_id) LOOP add_quote_event(p_qute_id => l_rec.id ,p_qust_code => 'SELREJ' ,p_event_date => p_event_date); END LOOP; SELECT enqu_id INTO l_enqu_id FROM quotes WHERE id = p_qute_id; email_agent_q_sel(l_enqu_id); END select_quote; FUNCTION delete_quote(p_qute_id IN quotes.id%TYPE ,p_message OUT VARCHAR2) RETURN BOOLEAN IS l_qust_code quote_statuses.code%TYPE; l_quote_deleted BOOLEAN; l_enqu_id enquiries.id%TYPE; TYPE t_rowid IS TABLE OF ROWID; l_rowid t_rowid := t_rowid(); BEGIN pl('delete_quote:entry:' || p_qute_id ,$$PLSQL_LINE); BEGIN SELECT enqu_id INTO l_enqu_id FROM quotes WHERE id = p_qute_id; EXCEPTION WHEN no_data_found THEN NULL; END; cout_assert.isnotnull(p_value => l_enqu_id ,p_message => 'Unable to find quote ' || p_qute_id); SELECT qust_code INTO l_qust_code FROM v_current_quote_status WHERE qute_id = p_qute_id; IF l_qust_code IN ('SELECTED' ,'ACCEPTED') THEN l_quote_deleted := FALSE; p_message := 'Unable to delete quote ' || p_qute_id || ' as it has a status of ' || INITCAP(l_qust_code); ELSE -- -- delete all associations with this quote -- DELETE FROM quote_events WHERE qute_id = p_qute_id; DELETE FROM apex_application_files aaf WHERE aaf.name IN (SELECT uri FROM documents docu ,document_roles doro WHERE doro.qute_id = p_qute_id AND doro.docu_id = docu.id AND docu.docu_type = 'INDO'); DELETE FROM document_events doev WHERE doev.docu_id IN (SELECT docu_id FROM document_roles doro WHERE doro.qute_id = p_qute_id); -- document roles knows which files should be deleted -- through a FK -- 1. Gather the rowids of the documents first, -- 2. Remove the document role -- 3. Remove the associated document SELECT ROWID BULK COLLECT INTO l_rowid FROM documents WHERE id IN (SELECT docu_id FROM document_roles WHERE qute_id = p_qute_id); DELETE FROM document_roles doro WHERE doro.qute_id = p_qute_id; FORALL l_idx IN INDICES OF l_rowid DELETE FROM documents WHERE ROWID = l_rowid(l_idx); DELETE FROM quote_items WHERE qute_id = p_qute_id; DELETE FROM quote_roles WHERE qute_id = p_qute_id; -- -- record that the quote was deleted -- add_quote_reason(p_enqu_id => l_enqu_id ,p_reason => 'QUOTE ' || p_qute_id || ' DELETED BY REQUEST' ,p_internal_or_external => gc_internal_reason); -- -- delete the quote itself -- DELETE FROM quotes WHERE id = p_qute_id; l_quote_deleted := TRUE; END IF; pl('delete_quote:exit:' || p_qute_id || ':' || CASE l_quote_deleted WHEN TRUE THEN 'TRUE' ELSE 'FALSE' END ,$$PLSQL_LINE); RETURN(l_quote_deleted = TRUE); EXCEPTION WHEN OTHERS THEN cout_err.report_and_stop; END delete_quote; FUNCTION delete_quote(p_qute_id IN quotes.id%TYPE) RETURN VARCHAR2 IS l_dummy BOOLEAN; l_message VARCHAR2(240); BEGIN pl('delete_quote(msg):entry:' || p_qute_id); l_dummy := delete_quote(p_qute_id => p_qute_id ,p_message => l_message); pl('delete_quote(msg):exit:' || l_message); RETURN(l_message); END delete_quote; FUNCTION delete_quotes_for_enquiry(p_enqu_id IN enquiries.id%TYPE ,p_message OUT VARCHAR2) RETURN BOOLEAN IS l_quotes_deleted BOOLEAN := TRUE; BEGIN pl('delete_quotes_for_enquiry:entry:' || p_enqu_id ,$$PLSQL_LINE); FOR l_quote IN (SELECT id FROM quotes WHERE enqu_id = p_enqu_id) LOOP IF NOT delete_quote(p_qute_id => l_quote.id ,p_message => p_message) THEN l_quotes_deleted := FALSE; EXIT; END IF; END LOOP; pl('delete_quotes_for_enquiry:' || CASE l_quotes_deleted WHEN TRUE THEN 'TRUE' ELSE 'FALSE' END ,$$PLSQL_LINE); RETURN(l_quotes_deleted = TRUE); END delete_quotes_for_enquiry; PROCEDURE lapse_quotes_job IS l_current_date DATE := trunc(SYSDATE); BEGIN FOR cur_quote IN (SELECT v.qute_id FROM v_current_quote_status v ,quotes q WHERE v.qust_code IN ('AV' ,'SELECTED') AND v.qute_id = q.id AND q.valid_until < l_current_date) LOOP lapse_quote(p_qute_id => cur_quote.qute_id ,p_description => 'Quote lapsed automatically by system.'); END LOOP; END lapse_quotes_job; FUNCTION not_already_submitted(p_enqu_id IN enquiries.id%TYPE) RETURN BOOLEAN IS l_dummy NUMBER; BEGIN SELECT 1 INTO l_dummy FROM enquiry_events WHERE enst_code = 'SUBMITTED' AND enqu_id = p_enqu_id AND rownum < 2; RETURN FALSE; EXCEPTION WHEN no_data_found THEN RETURN TRUE; END not_already_submitted; FUNCTION start_quote(p_enqu_id IN enquiries.id%TYPE ,p_manual_or_automatic IN VARCHAR2 DEFAULT gc_automatic_quote ,p_rfq_prty_id IN parties.id%TYPE ,p_owner_prty_id IN parties.id%TYPE) RETURN quotes.id%TYPE IS l_qute_id quotes.id%TYPE; BEGIN pl('start_quote:entry' ,$$PLSQL_LINE); INSERT INTO quotes (id ,qute_type ,enqu_id ,valid_from ,valid_until ,created_on ,created_by) VALUES (qute_seq.nextval ,p_manual_or_automatic ,p_enqu_id ,trunc(SYSDATE) ,(SELECT VALUE + trunc(SYSDATE) FROM system_configuration syco WHERE syco.parameter = 'QUOTE_LAPSE_LIMIT') ,SYSDATE ,USER) RETURNING id INTO l_qute_id; add_quote_event(p_qute_id => l_qute_id ,p_qust_code => 'INP' ,p_event_date => SYSDATE); INSERT INTO quote_roles (prty_id ,qute_id ,rt_code ,start_date) VALUES (p_rfq_prty_id ,l_qute_id ,'Q RFQ' ,SYSDATE); INSERT INTO quote_roles (prty_id ,qute_id ,rt_code ,start_date) VALUES (nvl(p_owner_prty_id ,p_rfq_prty_id) ,l_qute_id ,'Q OWN' ,SYSDATE); pl('start_quote:exit' ,$$PLSQL_LINE); RETURN l_qute_id; END start_quote; PROCEDURE make_quote_available(p_qute_id IN quotes.id%TYPE) IS l_qute_type quotes.qute_type%TYPE; l_enqu_id enquiries.id%TYPE; BEGIN pl('make_quote_available' ,$$PLSQL_LINE); add_quote_event(p_qute_id => p_qute_id ,p_qust_code => 'AV' ,p_event_date => SYSDATE); SELECT qute_type ,enqu_id INTO l_qute_type ,l_enqu_id FROM quotes WHERE id = p_qute_id; IF l_qute_type = 'MQ' THEN email_agent_mq_available(l_enqu_id); END IF; END make_quote_available; PROCEDURE mark_enquiry_submitted(p_enqu_id IN enquiries.id%TYPE) IS BEGIN pl('mark_enquiry_submitted' ,$$PLSQL_LINE); add_enquiry_event(p_enqu_id => p_enqu_id ,p_enst_code => 'SUBMITTED' ,p_event_date => SYSDATE); END mark_enquiry_submitted; PROCEDURE mark_enquiry_quoted(p_enqu_id IN enquiries.id%TYPE) IS BEGIN pl('mark_enquiry_quoted' ,$$PLSQL_LINE); add_enquiry_event(p_enqu_id => p_enqu_id ,p_enst_code => 'QUOTED' ,p_event_date => SYSDATE); END mark_enquiry_quoted; PROCEDURE mark_enquiry_invalid(p_enqu_id IN enquiries.id%TYPE) IS BEGIN pl('mark_enquiry_invalid' ,$$PLSQL_LINE); add_enquiry_event(p_enqu_id => p_enqu_id ,p_enst_code => 'INVALID' ,p_event_date => SYSDATE); END mark_enquiry_invalid; FUNCTION get_system_configuration_value(p_parameter IN system_configuration.parameter%TYPE) RETURN system_configuration.value%TYPE IS l_value system_configuration.value%TYPE; BEGIN SELECT VALUE INTO l_value FROM system_configuration WHERE parameter = p_parameter; RETURN l_value; EXCEPTION WHEN no_data_found THEN pl('SYSTEM_CONFIGURATION ERROR - PARAMETER ''' || p_parameter || ''' NOT FOUND' ,$$PLSQL_LINE); RETURN 'SYSTEM_CONFIGURATION ERROR - PARAMETER ''' || p_parameter || ''' NOT FOUND'; END get_system_configuration_value; FUNCTION get_manual_quote_recipient RETURN system_configuration.value%TYPE IS BEGIN RETURN get_system_configuration_value('EMAIL_ADDRESS_MANUAL_QUOTE'); END get_manual_quote_recipient; FUNCTION get_automatic_quote_recipient RETURN system_configuration.value%TYPE IS BEGIN RETURN get_system_configuration_value('EMAIL_ADDRESS_AUTOMATIC_QUOTE'); END get_automatic_quote_recipient; FUNCTION get_support_recipient RETURN system_configuration.value%TYPE IS BEGIN RETURN get_system_configuration_value('EMAIL_ADDRESS_SUPPORT'); END get_support_recipient; FUNCTION get_internal_user_recipient RETURN system_configuration.value%TYPE IS BEGIN RETURN get_system_configuration_value('EMAIL_ADDRESS_INTERNAL_USER'); END get_internal_user_recipient; FUNCTION get_system_name RETURN system_configuration.value%TYPE IS BEGIN RETURN 'WEBMIP ' || v('SYSTEM_ENVIRONMENT'); END get_system_name; FUNCTION get_customer_support_telephone RETURN system_configuration.value%TYPE IS BEGIN RETURN get_system_configuration_value('TELEPHONE_CUSTOMER_SUPPORT'); END get_customer_support_telephone; FUNCTION get_enty_description(p_enqu_id IN enquiries.id%TYPE) RETURN enquiry_types.description%TYPE IS l_description enquiry_types.description%TYPE; BEGIN SELECT description INTO l_description FROM enquiry_types enty ,enquiries enqu WHERE enty.code = enqu.enty_code AND enqu.id = p_enqu_id; RETURN l_description; END get_enty_description; FUNCTION get_quote_document(p_enqu_id IN enquiries.id%TYPE ,p_docu_id IN documents.id%TYPE DEFAULT NULL) RETURN t_rec_attachment IS l_docu_id documents.id%TYPE; l_doc_details t_rec_attachment; BEGIN pl('get_quote_document:entry:p_enqu_id=' || p_enqu_id ,$$PLSQL_LINE); l_docu_id := p_docu_id; IF l_docu_id IS NULL THEN -- find the 'selected' quote document SELECT doro.docu_id INTO l_docu_id FROM document_roles doro ,quote_events quev ,quotes qute WHERE qute.enqu_id = p_enqu_id AND quev.qute_id = qute.id AND quev.qust_code = 'SELECTED' AND doro.qute_id = quev.qute_id; END IF; pl('l_docu_id=' || l_docu_id ,$$PLSQL_LINE); -- get the document details and content SELECT wff.filename ,wff.mime_type ,wff.blob_content INTO l_doc_details.filename ,l_doc_details.mime_type ,l_doc_details.content FROM documents docu ,apex_application_files wff WHERE docu.uri = wff.name AND docu.id = l_docu_id; pl('l_doc_details.content.length=' || dbms_lob.getlength(l_doc_details.content) ,$$PLSQL_LINE); pl('get_quote_document:exit:filename=' || l_doc_details.filename ,$$PLSQL_LINE); RETURN l_doc_details; END get_quote_document; PROCEDURE al(p_in IN VARCHAR2) IS BEGIN dbms_lob.writeappend(g_email_plain_body ,LENGTH(p_in || gc_newline) ,p_in || gc_newline); dbms_lob.writeappend(g_email_html_body ,LENGTH(p_in || '
') ,p_in || '
'); END al; PROCEDURE al_contact_details IS BEGIN al('The I&C Non Standard Team'); al('National Grid Metering'); IF mip_quotation_document.using_old_address THEN al('4 Abbotts Lane'); al('Coventry'); al('CV1 4AY'); al(' '); al('Phone: 02476 286 322'); al('Fax: 02476 286 044'); al('Email: ic.nonstandard1@uk.ngrid.com'); ELSE al('35 Homer Road'); al('Solihull'); al('B91 3QJ'); al(' '); al('T+44(0) 121 424 8000'); al('F+44(0) 121 424 8841'); al('Email: IC.Nonstandard1@nationalgrid'); END IF; END al_contact_details; PROCEDURE open_body IS BEGIN pl('open_body:entry'); dbms_lob.createtemporary(lob_loc => g_email_plain_body ,cache => TRUE); dbms_lob.open(lob_loc => g_email_plain_body ,open_mode => dbms_lob.lob_readwrite); dbms_lob.createtemporary(lob_loc => g_email_html_body ,cache => TRUE); dbms_lob.open(lob_loc => g_email_html_body ,open_mode => dbms_lob.lob_readwrite); dbms_lob.writeappend(g_email_html_body ,LENGTH('') ,''); al(' '); al('THIS IS AN AUTOMATED EMAIL - PLEASE DO NOT REPLY AS EMAILS RECEIVED AT THIS ADDRESS CANNOT BE RESPONDED TO.'); al(' '); al(' '); pl('open_body:exit'); END open_body; FUNCTION get_quote_row(p_qute_id IN quotes.id%TYPE) RETURN t_quote_row IS l_row t_quote_row; BEGIN SELECT * INTO l_row FROM quotes WHERE id = p_qute_id; RETURN l_row; END get_quote_row; PROCEDURE produce_quote_summary(p_qute_id IN quotes.id%TYPE) IS l_quote_row t_quote_row; BEGIN pl('produce_quote_summary' ,$$PLSQL_LINE); l_quote_row := get_quote_row(p_qute_id); al('Quote summary for Quote Reference Number: ' || p_qute_id); al('This quote is valid from ' || TO_CHAR(l_quote_row.valid_from ,'ddth Month YYYY') || ' to ' || TO_CHAR(l_quote_row.valid_until ,'ddth Month YYYY')); IF l_quote_row.qute_type = 'AQ' THEN FOR l_sum IN (SELECT * FROM v_quote_details WHERE quote_id = p_qute_id) LOOP IF l_sum.module_code IS NOT NULL THEN al('Module code: ' || l_sum.module_code); END IF; IF l_sum.lead_time IS NOT NULL THEN al('Lead time: ' || l_sum.lead_time || ' days'); END IF; IF l_sum.additional_items IS NOT NULL THEN al('Additional items: ' || l_sum.additional_items); END IF; IF l_sum.bas_code IS NOT NULL THEN al('Base code: ' || l_sum.bas_code); END IF; IF l_sum.hou_code IS NOT NULL THEN al('Housing code: ' || l_sum.hou_code); END IF; IF l_sum.qmax IS NOT NULL THEN al('Qmax: ' || l_sum.qmax); END IF; IF l_sum.qmin IS NOT NULL THEN al('Qmin: ' || l_sum.qmin); END IF; IF l_sum.inlet_orientation IS NOT NULL THEN al('Inlet orientation: ' || l_sum.inlet_orientation); END IF; IF l_sum.outlet_orientation IS NOT NULL THEN al('Outlet orientation: ' || l_sum.outlet_orientation); END IF; IF l_sum.total_cost IS NOT NULL THEN al('Total cost: £' || l_sum.total_cost); END IF; END LOOP; ELSE al('This quote was produced manually and we are unable to provide a summary, refer to the online quotation document for details.'); END IF; END produce_quote_summary; PROCEDURE close_body IS BEGIN dbms_lob.writeappend(g_email_html_body ,LENGTH('') ,''); END close_body; PROCEDURE produce_enquiry_entry_details(p_enqu_id IN enquiries.id%TYPE) IS l_enty_code enquiries.enty_code%TYPE; FUNCTION return_value(p_enqu_id IN enquiries.id%TYPE ,p_field_name IN VARCHAR2) RETURN VARCHAR2 IS l_sql VARCHAR2(240); l_cursor_id INTEGER; l_rows INTEGER; l_return VARCHAR2(80); BEGIN l_cursor_id := dbms_sql.open_cursor; l_sql := 'select ' || p_field_name || ' from enquiries where id = :id'; dbms_sql.parse(l_cursor_id ,l_sql ,dbms_sql.native); dbms_sql.bind_variable(l_cursor_id ,':id' ,p_enqu_id); dbms_sql.define_column(l_cursor_id ,1 ,l_return ,80); l_rows := dbms_sql.execute_and_fetch(l_cursor_id); dbms_sql.column_value(l_cursor_id ,1 ,l_return); dbms_sql.close_cursor(l_cursor_id); RETURN l_return; END return_value; BEGIN pl('produce_enquiry_entry_details' ,$$PLSQL_LINE); SELECT enty_code INTO l_enty_code FROM enquiries WHERE id = p_enqu_id; FOR l_rec IN (SELECT dir.field_name ,dir.description FROM data_item_roles dir WHERE dir.enty_code = l_enty_code ORDER BY TO_NUMBER(dir.display_sequence)) LOOP al(l_rec.description || ': ' || return_value(p_enqu_id => p_enqu_id ,p_field_name => l_rec.field_name)); END LOOP; END produce_enquiry_entry_details; PROCEDURE produce_reasoning_summary(p_enqu_id IN enquiries.id%TYPE) IS BEGIN pl('produce_reasoning_summary' ,$$PLSQL_LINE); al(' '); al(' '); al(get_system_name || ' used the following reasoning in reaching this decision:'); FOR l_rec IN (SELECT reason ,internal_or_external FROM quote_reasoning WHERE enqu_id = p_enqu_id ORDER BY id) LOOP al(l_rec.reason); END LOOP; END produce_reasoning_summary; PROCEDURE email_to_agent(p_enqu_id IN enquiries.id%TYPE ,p_email_reason IN t_email_reason) IS l_agent_prty_id parties.id%TYPE; l_agent_comt_code contact_mechanism_types.code%TYPE; l_agent_comt_description contact_mechanism_types.description%TYPE; l_agent_contact_value contact_mechanisms.contact_value%TYPE; l_email_subject VARCHAR2(240); l_email_recipient VARCHAR2(240); l_agent_first_name parties.first_name%TYPE; l_agent_last_name parties.last_name%TYPE; l_attachment t_rec_attachment; BEGIN pl('email_to_agent:enter:p_enqu_id=' || p_enqu_id || ', p_email_reason=' || p_email_reason ,$$PLSQL_LINE); BEGIN l_agent_prty_id := mip_enquiry.get_enquiry_role(p_enquiryid => p_enqu_id ,p_rolecode => 'ENQ OWN'); pl('l_agent_prty_id=' || l_agent_prty_id ,$$PLSQL_LINE); EXCEPTION WHEN OTHERS THEN pl('mip_enquiry.get_enquiry_role returned ' || SQLERRM ,$$PLSQL_LINE); RAISE; END; BEGIN SELECT agent_comt_code ,comt.description ,agent_contact_value INTO l_agent_comt_code ,l_agent_comt_description ,l_agent_contact_value FROM enquiries ,contact_mechanism_types comt WHERE enquiries.id = p_enqu_id AND comt.code = enquiries.agent_comt_code; EXCEPTION WHEN OTHERS THEN pl('email_to_agent:' || SQLERRM ,$$PLSQL_LINE); RAISE; END; open_body; IF l_agent_comt_code = 'EMAIL' THEN l_email_recipient := l_agent_contact_value; ELSE l_email_recipient := get_automatic_quote_recipient; SELECT first_name ,last_name INTO l_agent_first_name ,l_agent_last_name FROM parties WHERE id = l_agent_prty_id; al('The Agent, ' || l_agent_first_name || ' ' || l_agent_last_name || ' has requested that they be contacted via ' || l_agent_comt_description || '(' || l_agent_contact_value || ')'); al(' '); al('The information to be passed on is:'); al(' '); END IF; pl('l_email_recipient=' || l_email_recipient ,$$PLSQL_LINE); IF p_email_reason = gc_agent_q_sel_notification THEN --Scenario one: Automatic quote generation notification l_email_subject := get_system_name || ': Automatic Quote Selected'; l_attachment := get_quote_document(p_enqu_id => p_enqu_id); al('Dear Sir/Madam,'); al(' '); al('Enquiry reference number: ' || p_enqu_id); al(' '); al('Thank you for using the National Grid Metering Online I&C quotations system. Your enquiry has generated an automatic quotation. To accept this quotation; either submit a work request via IX, complete the acceptance form on the quotation letter, or click the accept button on the Online I&C quotations system. Please note that this quotation is valid for 90 days from the date specified in the quotation.'); al(' '); al('Please use the Enquiry reference number at the top of this email on any future correspondence relating to this request.'); al(' '); al('Regards'); al(' '); al_contact_details; ELSIF p_email_reason = gc_agent_q_acc_notification THEN -- Scenario two: Quotation acceptance notification l_email_subject := get_system_name || ': Quote Acceptance'; al('Dear Sir/Madam,'); al(' '); al('Enquiry reference number: ' || p_enqu_id); al(' '); al('Thank you for accepting National Grid Metering¿s quotation using the Online I&C Quotations System. We will acknowledge your acceptance and provide you with a planning letter within the agreed timescales for the work type involved. '); al(' '); al('Please use the Enquiry reference number at the top of this email on any future correspondence relating to this request.'); al(' '); al('For further information or clarity, please do not hesitate to get in contact'); al(' '); al('Regards'); al(' '); al_contact_details; ELSIF p_email_reason = gc_agent_mq_req_notification THEN -- Scenario three: Manual quote requested l_email_subject := get_system_name || ': Request for Manual Quote'; al('Dear Sir/Madam,'); al(' '); al('Enquiry reference number: ' || p_enqu_id); al(' '); al('Thank you for requesting a quotation using the National Grid Metering Online I&C Quotations System. There are no automatic quotations available for this request, and a manual quotation will be prepared and uploaded to the website within the agreed timescales for the work type involved. We will notify you when this quotation is ready for your consideration.'); al(' '); al('Please use the Enquiry reference number at the top of this email on any future correspondence relating to this request.'); al(' '); al('Regards'); al(' '); al_contact_details; ELSIF p_email_reason = gc_agent_mq_avail_notification THEN -- Scenario four: Manual quote produced and ready to view. l_email_subject := get_system_name || ': Manual Quote Available'; l_attachment := get_quote_document(p_enqu_id => p_enqu_id); al('Dear Sir/Madam,'); al(' '); al('Enquiry reference number: ' || p_enqu_id); al(' '); al('Your quotation is now ready to view on the National Grid Metering I&C Online Quotation System.'); al(' '); al('Please note that this quotation is valid for 90 days from the date specified in the quotation. To accept this quotation, please submit a work request via IX, complete the acceptance form on the quotation letter, or click the accept button on the Online I&C quotations system. Please note that this quotation is valid for 90 days from the date specified in the quotation.'); al(' '); al('Please use the Enquiry reference number at the top of this email on any future correspondence relating to this request.'); al(' '); al('Regards'); al(' '); al_contact_details; END IF; close_body; BEGIN pl('email_to_agent:l_email_recipient=' || l_email_recipient || ':' || 'l_email_subject=' || l_email_subject ,$$PLSQL_LINE); mip_email.send_email_clob(p_recipient => l_email_recipient ,p_body => g_email_plain_body ,p_body_html => g_email_html_body ,p_subject => l_email_subject); IF dbms_lob.getlength(lob_loc => l_attachment.content) > 0 THEN mip_email.add_attachment(p_attachment => l_attachment.content ,p_filename => l_attachment.filename ,p_mime_type => l_attachment.mime_type); END IF; EXCEPTION WHEN OTHERS THEN pl('mip_email.send_email_clob returned ' || SQLERRM ,$$PLSQL_LINE); END; pl('email_to_agent:exit' ,$$PLSQL_LINE); END email_to_agent; PROCEDURE email_to_internal_users(p_enqu_id IN enquiries.id%TYPE ,p_email_reason IN t_email_reason) IS l_email_subject VARCHAR2(240); l_email_recipient VARCHAR2(240) := get_internal_user_recipient; l_qute_id quotes.id%TYPE; l_postcode enquiries.install_postcode%TYPE; l_shortcode parties.shortcode%TYPE; BEGIN pl('email_internal_users:entry:p_enqu_id=' || p_enqu_id || ', p_email_reason=' || p_email_reason ,$$PLSQL_LINE); SELECT p.shortcode INTO l_shortcode FROM v_latest_rt_code_for_enro v ,parties p WHERE rt_code = 'ENQ SUPP' AND v.enqu_id = p_enqu_id AND p.id = v.prty_id; SELECT e.install_postcode INTO l_postcode FROM enquiries e WHERE e.id = p_enqu_id; open_body; IF p_email_reason = gc_int_q_acc_notification THEN -- Scenario five: Accepted quote + system data l_email_subject := get_system_name || ': Quote Acceptance Notification'; al('Hi team,'); al(' '); al('Enquiry reference number: ' || p_enqu_id); al(' '); al('Please process this enquiry, as the quotation has been accepted.'); al(' '); SELECT qute_id INTO l_qute_id FROM quote_events quev ,quotes qute WHERE qute.enqu_id = p_enqu_id AND quev.qute_id = qute.id AND quev.qust_code = 'ACCEPTED'; --Insert Supplier Shortcode, Enquiry Ref and Network here new in purging update(09) al('Supplier Shortcode: ' || l_shortcode); al('Enquiry Reference: ' || p_enqu_id); al('Network: ' || mip_regions.get_region_for_postcode(l_postcode)); --get all the other fields for the enquiry to display produce_enquiry_entry_details(p_enqu_id); produce_quote_summary(l_qute_id); -- al(' '); al('Regards'); al(' '); al('I&C Online Quotations System'); ELSIF p_email_reason = gc_int_mq_req_notification THEN -- Scenario six: Request for manual quote + system data l_email_subject := get_system_name || ': Request For Manual Quotation Notification'; al('Hi team,'); al(' '); al('Enquiry reference number: ' || p_enqu_id); al(' '); al('Please provide a manual quotation as the I&C Online Quotation System was unable to generate an automatic quote.'); al(' '); SELECT qute_id INTO l_qute_id FROM quote_events quev ,quotes qute WHERE qute.enqu_id = p_enqu_id AND quev.qute_id = qute.id AND quev.qust_code = 'INP'; --Insert Supplier Shortcode, Enquiry Ref and Network here new in purging update(09) al('Supplier Shortcode: ' || l_shortcode); al('Enquiry Reference: ' || p_enqu_id); al('Network: ' || mip_regions.get_region_for_postcode(l_postcode)); produce_enquiry_entry_details(p_enqu_id); produce_reasoning_summary(p_enqu_id); al(' '); al('Regards'); al(' '); al('I&C Online Quotations System'); END IF; close_body; BEGIN pl('email_to_internal_users:l_email_recipient=' || l_email_recipient || ':' || 'l_email_subject=' || l_email_subject ,$$PLSQL_LINE); mip_email.send_email_clob(p_recipient => l_email_recipient ,p_body => g_email_plain_body ,p_body_html => g_email_html_body ,p_subject => l_email_subject); EXCEPTION WHEN OTHERS THEN pl('mip_email.send_email_clob returned ' || SQLERRM ,$$PLSQL_LINE); END; pl('email_internal_users:exit' ,$$PLSQL_LINE); END email_to_internal_users; PROCEDURE email_agent_q_sel(p_enqu_id IN enquiries.id%TYPE) IS BEGIN pl('email_agent_q_sel:entry' ,$$PLSQL_LINE); email_to_agent(p_enqu_id => p_enqu_id ,p_email_reason => gc_agent_q_sel_notification); pl('email_agent_q_sel:exit' ,$$PLSQL_LINE); END email_agent_q_sel; PROCEDURE email_agent_mq_available(p_enqu_id enquiries.id%TYPE) IS BEGIN email_to_agent(p_enqu_id => p_enqu_id ,p_email_reason => gc_agent_mq_avail_notification); END email_agent_mq_available; PROCEDURE email_agent_request_for_mq(p_enqu_id IN enquiries.id%TYPE) IS BEGIN pl('email_request_for_mq:enter' ,$$PLSQL_LINE); email_to_agent(p_enqu_id => p_enqu_id ,p_email_reason => gc_agent_mq_req_notification); email_to_internal_users(p_enqu_id => p_enqu_id ,p_email_reason => gc_int_mq_req_notification); pl('email_request_for_mq:exit' ,$$PLSQL_LINE); END email_agent_request_for_mq; PROCEDURE email_agent_quote_acceptance(p_enqu_id IN enquiries.id%TYPE) IS BEGIN email_to_agent(p_enqu_id => p_enqu_id ,p_email_reason => gc_agent_q_acc_notification); END email_agent_quote_acceptance; PROCEDURE email_iu_quote_acceptance(p_enqu_id enquiries.id%TYPE) IS BEGIN email_to_internal_users(p_enqu_id ,gc_int_q_acc_notification); END email_iu_quote_acceptance; PROCEDURE email_iu_request_for_mq(p_enqu_id enquiries.id%TYPE) IS BEGIN email_to_internal_users(p_enqu_id ,gc_int_mq_req_notification); END email_iu_request_for_mq; PROCEDURE email_support(p_subject IN VARCHAR2 ,p_enqu_id IN enquiries.id%TYPE) IS BEGIN pl('email_support' ,$$PLSQL_LINE); open_body; al('MIP_QUOTATION: Enquiry ' || p_enqu_id || ' (' || get_enty_description(p_enqu_id) || ')' || ' - processing error occurred'); FOR l_qure IN (SELECT reason FROM quote_reasoning WHERE enqu_id = p_enqu_id ORDER BY id) LOOP al(l_qure.reason); END LOOP; close_body; BEGIN mip_email.send_email_clob(p_recipient => get_support_recipient ,p_body => g_email_plain_body ,p_body_html => g_email_html_body ,p_subject => get_system_name || ' : ' || p_subject || ' : Enquiry Reference Number: ' || p_enqu_id); EXCEPTION WHEN OTHERS THEN pl('mip_email.send_email_clob returned ' || SQLERRM ,$$PLSQL_LINE); END; END email_support; PROCEDURE request_manual_quote(p_enqu_id IN enquiries.id%TYPE ,p_rfq_prty_id IN parties.id%TYPE ,p_owner_prty_id IN parties.id%TYPE DEFAULT NULL) IS l_qute_id quotes.id%TYPE; BEGIN pl('request_manual_quote' ,$$PLSQL_LINE); l_qute_id := start_quote(p_enqu_id => p_enqu_id ,p_manual_or_automatic => gc_manual_quote ,p_rfq_prty_id => p_rfq_prty_id ,p_owner_prty_id => p_owner_prty_id); email_agent_request_for_mq(p_enqu_id => p_enqu_id); END request_manual_quote; PROCEDURE ready_for_quote(p_enqu_id IN enquiries.id%TYPE ,p_mandatory_checks OUT mip_mandatory.t_mandatory_checks ,p_enquiry_is_ready OUT BOOLEAN) IS BEGIN pl('ready_for_quote(1) - enqu_id=' || p_enqu_id ,$$PLSQL_LINE); p_enquiry_is_ready := mip_enquiries_helper.check_mandatory(p_id => p_enqu_id ,p_mandatory_checks => p_mandatory_checks); END ready_for_quote; FUNCTION ready_for_quote(p_enqu_id IN enquiries.id%TYPE) RETURN BOOLEAN IS l_mandatory_checks mip_mandatory.t_mandatory_checks; l_enquiry_is_ready BOOLEAN; l_idx BINARY_INTEGER; BEGIN pl('ready_for_quote(2) - enqu_id=' || p_enqu_id ,$$PLSQL_LINE); ready_for_quote(p_enqu_id => p_enqu_id ,p_mandatory_checks => l_mandatory_checks ,p_enquiry_is_ready => l_enquiry_is_ready); l_idx := l_mandatory_checks.first; LOOP EXIT WHEN l_idx IS NULL; pl(l_mandatory_checks(l_idx) .field_name || ':' || l_mandatory_checks(l_idx).error_message); l_idx := l_mandatory_checks.next(l_idx); END LOOP; RETURN l_enquiry_is_ready; END ready_for_quote; PROCEDURE validate(p_rec IN mip_enquiries_helper.t_rec_enquiries) IS l_mandatory_checks mip_mandatory.t_mandatory_checks; l_idx NUMBER; BEGIN DELETE FROM validation_results WHERE enqu_id = p_rec.id; IF NOT mip_enquiries_helper.check_mandatory(p_rec => p_rec ,p_mandatory_checks => l_mandatory_checks) THEN l_idx := l_mandatory_checks.first; LOOP EXIT WHEN l_idx IS NULL; INSERT INTO validation_results (enqu_id ,field_name ,error_message) VALUES (p_rec.id ,l_mandatory_checks(l_idx).field_name ,l_mandatory_checks(l_idx).error_message); l_idx := l_mandatory_checks.next(l_idx); END LOOP; END IF; EXCEPTION WHEN OTHERS THEN pl('validate:' || SQLERRM ,$$PLSQL_LINE); RAISE; END validate; FUNCTION validation_result(p_enqu_id IN enquiries.id%TYPE ,p_field_name IN VARCHAR2) RETURN VARCHAR2 IS l_error_message validation_results.error_message%TYPE; BEGIN pl('validation_result:entry:' || p_enqu_id || ':' || p_field_name ,$$PLSQL_LINE); SELECT error_message INTO l_error_message FROM validation_results WHERE field_name = upper(p_field_name) AND enqu_id = p_enqu_id; pl('validation_result:exit:' || l_error_message ,$$PLSQL_LINE); RETURN l_error_message; EXCEPTION WHEN no_data_found THEN pl('validation_result:exit:NDF' ,$$PLSQL_LINE); RETURN NULL; WHEN OTHERS THEN pl('validation_result:exit:EXCEPTION:' || SQLERRM ,$$PLSQL_LINE); RAISE; END validation_result; FUNCTION return_mandatory_messages(p_enqu_id IN enquiries.id%TYPE) RETURN VARCHAR2 IS l_mandatory_messages VARCHAR2(4000); l_mandatory_checks mip_mandatory.t_mandatory_checks; l_enquiry_is_ready BOOLEAN; l_idx INTEGER; BEGIN pl('return_mandatory_messages' ,$$PLSQL_LINE); ready_for_quote(p_enqu_id => p_enqu_id ,p_mandatory_checks => l_mandatory_checks ,p_enquiry_is_ready => l_enquiry_is_ready); l_idx := l_mandatory_checks.first; LOOP EXIT WHEN l_idx IS NULL; l_mandatory_messages := l_mandatory_checks(l_idx).field_name || ':' || l_mandatory_checks(l_idx) .error_message; l_idx := l_mandatory_checks.next(l_idx); END LOOP; RETURN l_mandatory_messages; END return_mandatory_messages; FUNCTION get_u_meter_size(p_qmax IN NUMBER) RETURN meter_size_codes.code%TYPE IS l_meter_size_code meter_size_codes.code%TYPE; BEGIN pl('get_u_meter_size:entry' ,$$PLSQL_LINE); -- get the smallest meter code that will support the given Qmax SELECT code INTO l_meter_size_code FROM (SELECT code FROM meter_size_codes mesc WHERE qmax >= p_qmax AND mesc.valid_for_new_meter = 'YES' ORDER BY qmax) WHERE rownum < 2; pl('get_u_meter_size:exit:' || l_meter_size_code ,$$PLSQL_LINE); RETURN l_meter_size_code; EXCEPTION WHEN no_data_found THEN pl('get_u_meter_size:exit:NDF' ,$$PLSQL_LINE); RETURN NULL; END get_u_meter_size; FUNCTION get_qmax_from_mesc(p_mesc_code IN meter_size_codes.code%TYPE) RETURN meter_size_codes.qmax%TYPE IS l_qmax meter_size_codes.qmax%TYPE; BEGIN SELECT qmax INTO l_qmax FROM meter_size_codes WHERE code = p_mesc_code; RETURN l_qmax; EXCEPTION WHEN no_data_found THEN cout_err.report_and_stop(p_exception_message => 'Unable to find Qmax for Meter Size Code of ' || p_mesc_code); END get_qmax_from_mesc; FUNCTION get_seq_u_meter_size(p_existing_mesc_code IN meter_size_codes.code%TYPE ,p_n_or_p IN VARCHAR2 DEFAULT 'N') RETURN meter_size_codes.code%TYPE IS l_next_required BOOLEAN := upper(SUBSTR(p_n_or_p ,1 ,1)) = 'N'; l_prev_mesc_code meter_size_codes.code%TYPE; l_next_mesc_code meter_size_codes.code%TYPE; BEGIN pl('get_seq_u_meter_size:entry:' || p_existing_mesc_code || ':' || p_n_or_p); SELECT lag(code) over(ORDER BY qmax) AS prev_mesc_code ,lead(code) over(ORDER BY qmax) AS next_mesc_code INTO l_prev_mesc_code ,l_next_mesc_code FROM meter_size_codes WHERE code LIKE 'U%'; IF l_next_required THEN pl('get_seq_u_meter_size:exit:' || l_next_mesc_code ,$$PLSQL_LINE); RETURN l_next_mesc_code; ELSE pl('get_seq_u_meter_size:exit:' || l_prev_mesc_code ,$$PLSQL_LINE); RETURN l_prev_mesc_code; END IF; EXCEPTION WHEN no_data_found THEN pl('get_seq_u_meter_size:exit:NDF' ,$$PLSQL_LINE); RETURN NULL; END get_seq_u_meter_size; FUNCTION get_next_u_meter_size(p_existing_mesc_code IN meter_size_codes.code%TYPE) RETURN meter_size_codes.code%TYPE IS BEGIN RETURN(get_seq_u_meter_size(p_existing_mesc_code ,'N')); END get_next_u_meter_size; FUNCTION get_previous_u_meter_size(p_existing_mesc_code IN meter_size_codes.code%TYPE) RETURN meter_size_codes.code%TYPE IS BEGIN RETURN(get_seq_u_meter_size(p_existing_mesc_code ,'P')); END get_previous_u_meter_size; FUNCTION get_metr_details(p_metr_code IN meters.code%TYPE) RETURN t_rec_metr_details IS l_rec_metr_details t_rec_metr_details; BEGIN pl('get_metr_details:enter:' || p_metr_code); SELECT code ,mesc_code ,mety_code ,prty_id INTO l_rec_metr_details.code ,l_rec_metr_details.mesc_code ,l_rec_metr_details.mety_code ,l_rec_metr_details.prty_id FROM meters WHERE code = p_metr_code; pl('get_metr_details:exit:' || p_metr_code); RETURN(l_rec_metr_details); END get_metr_details; FUNCTION get_existing_metr_details(p_enqu t_enqu) RETURN t_rec_metr_details IS l_rec_metr_details t_rec_metr_details; BEGIN pl('get_existing_metr_details:entry' ,$$PLSQL_LINE); IF nvl(p_enqu.existing_metr_code ,'OTHER') <> 'OTHER' THEN l_rec_metr_details := get_metr_details(p_enqu.existing_metr_code); ELSE l_rec_metr_details.mesc_code := p_enqu.existing_mesc_code; IF l_rec_metr_details.mesc_code IS NULL THEN l_rec_metr_details.mesc_code := get_u_meter_size(p_enqu.qmax); END IF; l_rec_metr_details.mety_code := p_enqu.existing_mety_code; END IF; pl('get_existing_metr_details:exit:' || l_rec_metr_details.code || ':' || l_rec_metr_details.mesc_code || ':' || l_rec_metr_details.mety_code || ':' || l_rec_metr_details.prty_id ,$$PLSQL_LINE); RETURN l_rec_metr_details; END get_existing_metr_details; /** Required meter can be within one 'U'-size of existing meter */ FUNCTION valid_meter_size_change(p_existing_meter_size_code IN meter_size_codes.code%TYPE ,p_required_meter_size_code IN meter_size_codes.code%TYPE) RETURN BOOLEAN IS l_dummy NUMBER; BEGIN pl('valid_meter_size_change' ,$$PLSQL_LINE); IF p_existing_meter_size_code = p_required_meter_size_code THEN RETURN TRUE; END IF; SELECT NULL INTO l_dummy FROM (SELECT code AS existing_mesc ,lead(code) over(ORDER BY qmax) AS required_mesc FROM meter_size_codes WHERE code LIKE 'U%') WHERE (existing_mesc = p_existing_meter_size_code AND required_mesc = p_required_meter_size_code) OR (existing_mesc = p_required_meter_size_code AND required_mesc = p_existing_meter_size_code); pl('valid_meter_size_change:exit:TRUE' ,$$PLSQL_LINE); RETURN TRUE; EXCEPTION WHEN no_data_found THEN pl('valid_meter_size_change:exit:FALSE' ,$$PLSQL_LINE); RETURN FALSE; END valid_meter_size_change; PROCEDURE survey_required(p_enqu IN t_enqu ,p_manual_or_automatic_quote IN OUT t_manual_or_automatic_quote) IS l_svcpt_code service_pressure_types.code%TYPE; l_rec_metr_details t_rec_metr_details; l_required_mesc_code meter_size_codes.code%TYPE; l_manual_or_automatic_quote t_manual_or_automatic_quote := gc_automatic_quote; BEGIN pl('survey_required' ,$$PLSQL_LINE); -- Site survey required for: -- Existing meter is (rotary or turbine) AND OFMAT enquiry; IF p_enqu.enty_code = 'OFMAT' THEN -- get existing meter details l_rec_metr_details := get_existing_metr_details(p_enqu); IF l_rec_metr_details.mety_code IN ('ROTARY' ,'TURBINE') THEN l_manual_or_automatic_quote := gc_manual_quote; add_quote_reason(p_enqu.id ,'Site Survey is required for OFMAT of an existing ' || INITCAP(l_rec_metr_details.mety_code) || ' meter.' ,gc_external_reason); END IF; -- ROTARY / TURBINE END IF; -- OFMAT IF l_manual_or_automatic_quote = gc_manual_quote THEN p_manual_or_automatic_quote := gc_manual_quote; END IF; END survey_required; FUNCTION tripartite_agreement_message(p_rec IN mip_enquiries_helper.t_rec_enquiries) RETURN VARCHAR2 IS l_tab_messages mip_tripartite.t_tab_messages; l_return VARCHAR2(4000); l_valid BOOLEAN; l_idx INTEGER; BEGIN pl('tripartite_agreement_message' ,$$PLSQL_LINE); l_valid := mip_tripartite.valid_enquiry(p_rec => p_rec ,p_tab_messages => l_tab_messages); -- processing an enquiry that has not passed the tripartite agreement -- infers a processing error IF NOT l_valid THEN l_idx := l_tab_messages.first; LOOP EXIT WHEN l_idx IS NULL; l_return := l_return || l_tab_messages(l_idx); l_idx := l_tab_messages.next(l_idx); END LOOP; END IF; RETURN l_return; END tripartite_agreement_message; FUNCTION tripartite_agreement_satisfied(p_enqu_id IN enquiries.id%TYPE) RETURN BOOLEAN IS l_tab_messages mip_tripartite.t_tab_messages; l_valid BOOLEAN; l_idx INTEGER; BEGIN pl('tripartite_agreement_satisfied' ,$$PLSQL_LINE); l_valid := mip_tripartite.valid_enquiry(p_enqu_id => p_enqu_id ,p_tab_messages => l_tab_messages); -- processing an enquiry that has not passed the tripartite agreement -- infers a processing error IF NOT l_valid THEN l_idx := l_tab_messages.first; LOOP EXIT WHEN l_idx IS NULL; add_quote_reason(p_enqu_id => p_enqu_id ,p_reason => l_tab_messages(l_idx) ,p_internal_or_external => gc_external_reason); l_idx := l_tab_messages.next(l_idx); END LOOP; mark_enquiry_invalid(p_enqu_id); email_support(p_subject => 'MIP_QUOTATION: Tripartite failure with Enquiry ' || p_enqu_id ,p_enqu_id => p_enqu_id); END IF; RETURN l_valid; END tripartite_agreement_satisfied; PROCEDURE manual_or_automatic_quote(p_enqu IN t_enqu ,p_manual_or_automatic_quote OUT t_manual_or_automatic_quote) IS l_dummy NUMBER; BEGIN pl('manual_or_automatic_quote' ,$$PLSQL_LINE); p_manual_or_automatic_quote := gc_automatic_quote; IF p_enqu.enty_code IN ('OTHER' ,'CHANGE CAPACITY') THEN p_manual_or_automatic_quote := gc_manual_quote; add_quote_reason(p_enqu.id ,'Enquiry type is ''' || get_enty_description(p_enqu.id) || '''.' ,gc_external_reason); END IF; survey_required(p_enqu => p_enqu ,p_manual_or_automatic_quote => p_manual_or_automatic_quote); IF p_enqu.twin_stream_required = 'YES' THEN p_manual_or_automatic_quote := gc_manual_quote; add_quote_reason(p_enqu.id ,'Twin stream required.' ,gc_external_reason); END IF; IF p_enqu.bypass_required IS NOT NULL THEN BEGIN SELECT 1 INTO l_dummy FROM bypass_reasons WHERE description = p_enqu.bypass_required AND code <> '1'; p_manual_or_automatic_quote := gc_manual_quote; add_quote_reason(p_enqu.id ,'Bypass required.' ,gc_external_reason); EXCEPTION WHEN no_data_found THEN -- bypass reason 1 -- no action required NULL; END; END IF; IF p_enqu.required_metering_pressure > 21 THEN p_manual_or_automatic_quote := gc_manual_quote; add_quote_reason(p_enqu.id ,'Required metering pressure is greater than 21mbar.' ,gc_external_reason); END IF; IF p_enqu.convertor_required = 'YES' THEN p_manual_or_automatic_quote := gc_manual_quote; add_quote_reason(p_enqu.id ,'Convertor required.' ,gc_external_reason); END IF; IF p_enqu.job_description IS NOT NULL THEN p_manual_or_automatic_quote := gc_manual_quote; add_quote_reason(p_enqu.id ,'Job Description field was entered.' ,gc_external_reason); END IF; IF p_enqu.downstream_booster_or_compress = 'YES' THEN p_manual_or_automatic_quote := gc_manual_quote; add_quote_reason(p_enqu.id ,'Booster or compressor is present downstream of the meter module.' ,gc_external_reason); END IF; IF p_enqu.annual_quantity > 732 * 1000 THEN p_manual_or_automatic_quote := gc_manual_quote; add_quote_reason(p_enqu.id ,'Required Annual Quantity is in excess of 732MWh.' ,gc_external_reason); END IF; IF p_enqu.existing_convertor = 'YES' AND p_enqu.enty_code NOT IN ('REMOVE' ,'STD REMOVE' ,'ADVERSARIAL') THEN p_manual_or_automatic_quote := gc_manual_quote; add_quote_reason(p_enqu.id ,'Convertor is present.' ,gc_external_reason); END IF; IF p_enqu.existing_logger = 'YES' AND p_enqu.enty_code IN ('EXCHANGE' ,'STD EXCHANGE') THEN p_manual_or_automatic_quote := gc_manual_quote; add_quote_reason(p_enqu.id ,'Logger is present.' ,gc_external_reason); END IF; IF p_enqu.enty_code IN ('OFMAT') AND NOT (p_enqu.existing_mety_code = 'DIAPHRAGM') AND (SUBSTR(nvl(p_enqu.required_mesc_code ,get_u_meter_size(p_enqu.qmax)) ,1) <> 'U') THEN p_manual_or_automatic_quote := gc_manual_quote; add_quote_reason(p_enqu.id ,'OFMAT request for a non-''U''-sized Diaphragm meter.' ,gc_external_reason); END IF; IF p_enqu.enty_code IN ('OFMAT' ,'EXCHANGE' ,'STD EXCHANGE' ,'REMOVE' ,'STD REMOVE' ,'ADVERSARIAL') AND p_enqu.existing_metr_code IS NULL AND (p_enqu.existing_mesc_code IS NULL OR p_enqu.existing_mesc_code = 'OTHER' OR p_enqu.existing_mety_code IS NULL) THEN p_manual_or_automatic_quote := gc_manual_quote; add_quote_reason(p_enqu.id ,get_enty_description(p_enqu.enty_code) || ' request has insufficient details describing the existing meter.' ,gc_external_reason); END IF; -- check postcode IF NOT mip_regions.valid_postcode_format(p_enqu.install_postcode) THEN p_manual_or_automatic_quote := gc_manual_quote; add_quote_reason(p_enqu.id ,'Installation postcode is of an unrecognized format.' ,gc_external_reason); ELSIF mip_regions.get_region_for_postcode(p_enqu.install_postcode) IS NULL THEN p_manual_or_automatic_quote := gc_manual_quote; add_quote_reason(p_enqu.id ,'Unable to determine pricing region for given installation postcode.' ,gc_external_reason); END IF; IF p_manual_or_automatic_quote = gc_manual_quote THEN add_quote_reason(p_enqu.id ,'- Manual quote required.' ,gc_internal_reason); END IF; END manual_or_automatic_quote; FUNCTION get_housing(p_hou_code IN modules.hou_code%TYPE ,p_regi_code regions.code%TYPE) RETURN t_rec_additional_costs IS l_rec_costs t_rec_additional_costs; BEGIN pl('get_housing' ,$$PLSQL_LINE); SELECT selling_price ,cost_price ,delivery_cost INTO l_rec_costs.selling_price ,l_rec_costs.cost_price ,l_rec_costs.delivery_cost FROM (SELECT selling_price ,cost_price ,delivery_cost FROM (SELECT decode(regi_code ,p_regi_code ,1 ,999) AS accuracy ,hou_code ,selling_price ,cost_price ,delivery_cost ,ROWID FROM v_hoco cost WHERE hou_code = p_hou_code AND SYSDATE BETWEEN cost.effective_from AND cost.effective_to AND (regi_code = p_regi_code OR regi_code IS NULL) ORDER BY 1) WHERE rownum < 2); RETURN l_rec_costs; EXCEPTION WHEN no_data_found THEN RETURN l_rec_costs; END get_housing; FUNCTION get_base(p_bas_code IN modules.hou_code%TYPE ,p_regi_code regions.code%TYPE) RETURN t_rec_additional_costs IS l_rec_costs t_rec_additional_costs; BEGIN pl('get_base' ,$$PLSQL_LINE); SELECT selling_price ,cost_price ,delivery_cost INTO l_rec_costs.selling_price ,l_rec_costs.cost_price ,l_rec_costs.delivery_cost FROM (SELECT selling_price ,cost_price ,delivery_cost FROM (SELECT decode(regi_code ,p_regi_code ,1 ,999) AS accuracy ,bas_code ,selling_price ,cost_price ,delivery_cost ,ROWID FROM v_baco cost WHERE bas_code = p_bas_code AND SYSDATE BETWEEN cost.effective_from AND cost.effective_to AND (regi_code = p_regi_code OR regi_code IS NULL) ORDER BY 1) WHERE rownum < 2); RETURN l_rec_costs; EXCEPTION WHEN no_data_found THEN RETURN l_rec_costs; END get_base; FUNCTION get_aico(p_adit_code IN costs.adit_code%TYPE ,p_regi_code IN regions.code%TYPE ,p_enty_code IN enquiry_types.code%TYPE ,p_mety_code IN meter_types.code%TYPE ,p_mesc_code IN meter_size_codes.code%TYPE) RETURN t_rec_additional_costs IS l_rec_costs t_rec_additional_costs; BEGIN pl('get_aico:' || p_adit_code || ':' || p_regi_code ,$$PLSQL_LINE); SELECT lead_time ,selling_price ,cost_price ,delivery_cost ,adit_code INTO l_rec_costs.lead_time ,l_rec_costs.selling_price ,l_rec_costs.cost_price ,l_rec_costs.delivery_cost ,l_rec_costs.adit_code FROM additional_items adit ,(SELECT selling_price ,cost_price ,delivery_cost ,adit_code FROM (SELECT CASE WHEN cost.enty_code IS NULL THEN 0 ELSE 1000 END + CASE WHEN cost.regi_code IS NULL THEN 0 ELSE 100 END + CASE WHEN cost.mety_code IS NULL THEN 0 ELSE 10 END + CASE WHEN cost.mesc_code IS NULL THEN 0 ELSE 1 END AS accuracy ,adit_code ,selling_price ,cost_price ,delivery_cost ,ROWID FROM v_aico cost WHERE adit_code = p_adit_code AND SYSDATE BETWEEN cost.effective_from AND cost.effective_to AND ((regi_code = p_regi_code) OR (regi_code IS NULL)) AND ((cost.enty_code = p_enty_code) OR (enty_code IS NULL)) AND ((cost.mety_code = p_mety_code) OR (mety_code IS NULL)) AND ((cost.mesc_code = p_mesc_code) OR (mesc_code IS NULL)) ORDER BY 1 DESC) WHERE rownum < 2) cost WHERE adit.code = cost.adit_code AND adit.code = p_adit_code; pl('get_aico:exit' ,$$PLSQL_LINE); RETURN l_rec_costs; EXCEPTION WHEN no_data_found THEN pl('get_aico:exit:NDF' ,$$PLSQL_LINE); RETURN l_rec_costs; WHEN OTHERS THEN pl('get_aico:exit:' || SQLERRM ,$$PLSQL_LINE); RAISE; END get_aico; FUNCTION get_laco(p_enty_code IN enquiry_types.code%TYPE ,p_regi_code IN regions.code%TYPE ,p_mety_code IN meter_types.code%TYPE ,p_mesc_code IN meter_size_codes.code%TYPE DEFAULT NULL ,p_svcp_code IN service_pressures.code%TYPE DEFAULT NULL) RETURN t_rec_additional_costs IS l_rec_costs t_rec_additional_costs; BEGIN pl('get_laco:entry:' || p_enty_code || ':' || p_regi_code || ':' || p_mety_code || ':' || p_mesc_code || ':' || p_svcp_code ,$$PLSQL_LINE); SELECT selling_price ,cost_price ,delivery_cost ,svcpt_code INTO l_rec_costs.selling_price ,l_rec_costs.cost_price ,l_rec_costs.delivery_cost ,l_rec_costs.svcpt_code FROM (SELECT decode(regi_code ,p_regi_code ,0 ,1) * 100 + decode(cost.svcpt_code ,NULL ,1 ,0) * 10 + decode(cost.mesc_code ,NULL ,1 ,0) AS accuracy ,enty_code ,mety_code ,cost.svcpt_code ,mesc_code ,selling_price ,cost_price ,delivery_cost ,cost.rowid ,svcp.code AS svcp_code FROM v_laco cost ,service_pressures svcp WHERE SYSDATE BETWEEN cost.effective_from AND cost.effective_to AND (regi_code = p_regi_code OR regi_code IS NULL) AND cost.svcpt_code(+) = svcp.svcpt_code AND enty_code = p_enty_code AND mety_code = p_mety_code AND svcp.code = p_svcp_code AND ((cost.mesc_code = p_mesc_code) OR (mesc_code IS NULL)) ORDER BY 1 DESC) WHERE rownum < 2; pl('get_laco:exit:' || l_rec_costs.selling_price); RETURN l_rec_costs; EXCEPTION WHEN no_data_found THEN RETURN l_rec_costs; END get_laco; FUNCTION get_svcpt_for_svcp(p_svcp_code IN service_pressures.code%TYPE) RETURN service_pressure_types.code%TYPE IS l_svcpt_code service_pressure_types.code%TYPE; BEGIN pl('get_svcpt_for_svcp:entry:p_svcp_code=' || p_svcp_code ,$$PLSQL_LINE); BEGIN SELECT svcpt_code INTO l_svcpt_code FROM service_pressures WHERE code = p_svcp_code; EXCEPTION WHEN no_data_found THEN l_svcpt_code := NULL; END; pl('get_svcpt_for_svcp:exit:' || l_svcpt_code ,$$PLSQL_LINE); RETURN l_svcpt_code; END get_svcpt_for_svcp; FUNCTION get_lead_time(p_enty_code enquiry_types.code%TYPE ,p_mety_code meter_types.code%TYPE ,p_svcp_code service_pressures.code%TYPE ,p_qmax NUMBER) RETURN NUMBER IS l_days NUMBER; l_qmax NUMBER; BEGIN pl('get_lead_time:' || p_enty_code || ':' || p_mety_code || ':' || p_svcp_code || ':' || p_qmax ,$$PLSQL_LINE); l_qmax := greatest(nvl(p_qmax ,1) ,1); SELECT days INTO l_days FROM (SELECT enty_code ,mety_code ,svcp.code AS svcp_code ,from_qmax ,to_qmax ,days FROM v_lead_times v ,service_pressures svcp WHERE v.svcpt_code = svcp.svcpt_code(+)) v WHERE l_qmax > v.from_qmax AND l_qmax <= v.to_qmax AND v.enty_code = p_enty_code AND v.mety_code = p_mety_code AND ((v.svcp_code = p_svcp_code) OR (v.svcp_code IS NULL AND p_svcp_code IS NULL)); pl('get_lead_time:exit:' || l_days ,$$PLSQL_LINE); RETURN l_days; EXCEPTION WHEN no_data_found THEN pl('get_lead_time:exit:NDF' ,$$PLSQL_LINE); RETURN NULL; WHEN OTHERS THEN pl('get_lead_time:exit:' || SQLERRM ,$$PLSQL_LINE); RAISE; END get_lead_time; FUNCTION get_lead_time_desc(p_enty_code enquiry_types.code%TYPE ,p_mety_code meter_types.code%TYPE ,p_svcp_code service_pressures.code%TYPE ,p_qmax NUMBER) RETURN VARCHAR2 IS l_desc VARCHAR2(400); BEGIN FOR l_rec IN (SELECT description FROM (SELECT 1 ,'Enquiry Type is ' || description AS description FROM enquiry_types WHERE code = p_enty_code UNION SELECT 2 ,'Meter Type is ' || description FROM meter_types WHERE code = p_mety_code UNION SELECT 3 ,'Service Pressure is ' || svcpt.description FROM service_pressure_types svcpt ,service_pressures svcp WHERE svcp.svcpt_code = svcpt.code AND svcp.code = p_svcp_code UNION SELECT 4 ,'Qmax is ' || p_qmax FROM dual WHERE p_qmax IS NOT NULL) ORDER BY 1) LOOP l_desc := l_desc || l_rec.description || ', '; END LOOP; l_desc := SUBSTR(l_desc ,1 ,LENGTH(l_desc) - 2) || '.'; RETURN l_desc; END get_lead_time_desc; PROCEDURE produce_module_quotes(p_enqu IN t_enqu ,p_rfq_prty_id IN parties.id%TYPE ,p_owner_prty_id IN parties.id%TYPE DEFAULT NULL ,p_manual_or_automatic_quote IN OUT t_manual_or_automatic_quote) IS l_produced_automatic_quote BOOLEAN; l_this_is_automatic_quote BOOLEAN; l_regi_code regions.code%TYPE := mip_regions.get_region_for_postcode(p_enqu.install_postcode); l_qute_id quotes.id%TYPE; l_additional_costs t_rec_additional_costs; l_quote_document VARCHAR2(240); l_existing_rec_metr_details t_rec_metr_details; l_required_mesc_code meter_size_codes.code%TYPE; l_required_qmax meter_size_codes.qmax%TYPE; BEGIN pl('produce_module_quotes:entry:' || p_enqu.id || ':' || p_enqu.enty_code ,$$PLSQL_LINE); cout_assert.istrue(p_enqu.enty_code IN ('INSTALL' ,'STD INSTALL' ,'EXCHANGE' ,'CAPACITY CHANGE' ,'STD EXCHANGE') ,'Attempted to produce an install or exchange quote for enquiry of type ' || p_enqu.enty_code); cout_assert.isnotnull(l_regi_code ,'Attempted to produce an install, exchange or change of capacity quote for enquiry for a installation postcode (' || p_enqu.install_postcode || ') without a region.'); IF p_enqu.enty_code IN ('INSTALL' ,'STD INSTALL') THEN add_quote_reason(p_enqu.id ,p_reason => 'Attempting an automatic installation quote for ' || p_enqu.id || '.' ,p_internal_or_external => gc_internal_reason); ELSIF p_enqu.enty_code IN ('EXCHANGE' ,'STD EXCHANGE') THEN add_quote_reason(p_enqu.id ,p_reason => 'Attempting an automatic exchange quote for ' || p_enqu.id || '.' ,p_internal_or_external => gc_internal_reason); ELSIF p_enqu.enty_code IN ('CHANGE CAPACITY') THEN add_quote_reason(p_enqu.id ,p_reason => 'Attempting an automatic change capacity quote for ' || p_enqu.id || '.' ,p_internal_or_external => gc_internal_reason); END IF; pl('p_enqu.required_mesc_code:' || p_enqu.required_mesc_code ,$$PLSQL_LINE); pl('p_enqu.qmax:' || p_enqu.qmax ,$$PLSQL_LINE); IF p_enqu.required_mesc_code IS NOT NULL AND p_enqu.required_mesc_code <> 'OTHER' THEN l_required_qmax := get_qmax_from_mesc(p_enqu.required_mesc_code); ELSE l_required_qmax := p_enqu.qmax; END IF; pl('l_required_qmax:' || l_required_qmax ,$$PLSQL_LINE); IF NOT p_manual_or_automatic_quote = gc_manual_quote THEN cout_assert.istrue((l_required_mesc_code IS NULL AND l_required_qmax IS NOT NULL) OR (l_required_mesc_code IS NOT NULL AND l_required_qmax IS NULL) ,p_message => 'EITHER l_required_mesc_code must be null OR l_required_qmax must be null'); pl('Looking for modules to match the following: Service Pressure ' || p_enqu.required_svcp_code || ', Outlet Pressure ' || p_enqu.required_metering_pressure || CASE l_required_mesc_code IS NULL WHEN TRUE THEN 'and required Qmax ' || l_required_qmax ELSE 'and required Meter Size Code ' || l_required_mesc_code END || '.'); FOR l_rec_module IN (SELECT modu.code AS modu_code ,modu.selling_price AS modu_selling_price ,modu.cost_price AS modu_cost_price ,modu.delivery_cost AS modu_delivery_cost ,NULL AS modu_lead_time ,modu.hou_code AS hou_code ,modu.inlet_orientation AS modu_inlet_orientation ,modu.outlet_orientation AS modu_outlet_orientation ,modu.qmax ,NULL AS hou_selling_price ,NULL AS hou_cost_price ,NULL AS hou_delivery_cost ,modu.bas_code AS bas_code ,NULL AS bas_selling_price ,NULL AS bas_cost_price ,NULL AS bas_delivery_cost ,metr.code AS metr_code ,metr.qnom ,metr.qmax AS metr_qmax ,metr.qmin ,metr.mesc_code ,metr.mety_code ,NULL AS laco_mety_code ,NULL AS laco_svcpt_code ,NULL AS laco_mesc_code ,NULL AS laco_selling_price ,NULL AS laco_cost_price ,NULL AS laco_delivery_cost ,NULL AS amr_cost_id ,NULL AS amr_selling_price ,NULL AS amr_cost_price ,NULL AS amr_delivery_cost ,NULL AS amr_lead_time ,NULL AS ems_cost_id ,NULL AS ems_selling_price ,NULL AS ems_cost_price ,NULL AS ems_delivery_cost ,NULL AS ems_lead_time ,NULL AS bypass_cost_id ,NULL AS bypass_selling_price ,NULL AS bypass_cost_price ,NULL AS bypass_delivery_cost ,NULL AS bypass_lead_time ,NULL AS lifting_gear_cost_id ,NULL AS lifting_gear_selling_price ,NULL AS lifting_gear_cost_price ,NULL AS lifting_gear_delivery_cost ,NULL AS lifting_gear_lead_time ,NULL AS purging_cost_id ,NULL AS purging_selling_price ,NULL AS purging_cost_price ,NULL AS purging_delivery_cost ,NULL AS purging_lead_time ,NULL AS self_purging_cost_id ,NULL AS self_purging_selling_price ,NULL AS self_purging_cost_price ,NULL AS self_purging_delivery_cost ,NULL AS self_purging_lead_time FROM (SELECT modu.code ,modu.metr_code ,modu.hou_code ,modu.bas_code ,modu.qmax ,svcp_code ,outlet_pressure ,cnor_i.description AS inlet_orientation ,cnor_o.description AS outlet_orientation ,selling_price ,cost_price ,delivery_cost FROM modules modu ,connection_orientations cnor_i ,connection_orientations cnor_o ,(SELECT modu_code ,selling_price ,cost_price ,delivery_cost FROM (SELECT row_number() over(PARTITION BY modu_code ORDER BY(decode(regi_code, l_regi_code, 1, 999))) AS accuracy ,modu_code ,selling_price ,cost_price ,delivery_cost ,ROWID FROM v_moco cost WHERE SYSDATE BETWEEN cost.effective_from AND cost.effective_to AND (regi_code = l_regi_code OR regi_code IS NULL)) WHERE accuracy <= 1) cost WHERE modu.code = cost.modu_code(+) AND modu.inlet_cnor_code = cnor_i.code AND modu.outlet_cnor_code = cnor_o.code AND SYSDATE BETWEEN modu.valid_from AND nvl(modu.valid_to ,SYSDATE)) modu ,(SELECT metr.code ,metr.qmax ,metr.qmin ,metr.qnom ,metr.mety_code ,metr.mesc_code FROM meters metr WHERE SYSDATE BETWEEN metr.valid_from AND nvl(metr.valid_to ,SYSDATE)) metr WHERE modu.svcp_code = p_enqu.required_svcp_code AND modu.outlet_pressure = p_enqu.required_metering_pressure AND metr.code = modu.metr_code -- -- either been given a meter size code to match -- or a module qmax value to exceed -- AND ((metr.mesc_code = l_required_mesc_code) OR (modu.qmax >= l_required_qmax))) LOOP l_this_is_automatic_quote := TRUE; add_quote_reason(p_enqu.id ,p_reason => 'Considering module : ' || l_rec_module.modu_code ,p_internal_or_external => gc_internal_reason); -- -- check whether we have the required prices -- if we do not, then we may need to produce a manual quote -- IF l_rec_module.modu_selling_price IS NULL THEN l_this_is_automatic_quote := FALSE; add_quote_reason(p_enqu.id ,p_reason => 'Unable to find selling price for module ' || l_rec_module.modu_code || CASE l_regi_code WHEN NULL THEN '' ELSE ' for region code ' || l_regi_code END || '.' ,p_internal_or_external => gc_internal_reason); END IF; l_rec_module.modu_lead_time := get_lead_time(p_enty_code => p_enqu.enty_code ,p_mety_code => l_rec_module.mety_code ,p_svcp_code => p_enqu.required_svcp_code ,p_qmax => l_rec_module.qmax); IF l_rec_module.modu_lead_time IS NULL THEN l_this_is_automatic_quote := FALSE; add_quote_reason(p_enqu.id ,p_reason => 'Unable to find lead time for module ' || l_rec_module.modu_code || '. (' || get_lead_time_desc(p_enty_code => p_enqu.enty_code ,p_mety_code => p_enqu.existing_mety_code ,p_svcp_code => p_enqu.required_svcp_code ,p_qmax => p_enqu.qmax) || ')' ,p_internal_or_external => gc_internal_reason); END IF; l_additional_costs := get_laco(p_enty_code => p_enqu.enty_code ,p_regi_code => l_regi_code ,p_mety_code => l_rec_module.mety_code ,p_mesc_code => l_rec_module.mesc_code ,p_svcp_code => p_enqu.required_svcp_code); l_rec_module.laco_svcpt_code := l_additional_costs.svcpt_code; l_rec_module.laco_mety_code := l_rec_module.mety_code; l_rec_module.laco_selling_price := l_additional_costs.selling_price; l_rec_module.laco_cost_price := l_additional_costs.cost_price; l_rec_module.laco_delivery_cost := l_additional_costs.delivery_cost; IF l_rec_module.laco_selling_price IS NULL THEN l_this_is_automatic_quote := FALSE; add_quote_reason(p_enqu.id ,p_reason => 'Unable to find Labour Cost (selling price) for this Enquiry Type Code: ' || p_enqu.enty_code || ', Meter Type Code:' || l_rec_module.mety_code || ', Meter Size Code:' || l_rec_module.mesc_code || ', Service Pressure Code:' || p_enqu.required_svcp_code || CASE l_regi_code WHEN NULL THEN '' ELSE ' for region code ' || l_regi_code END || '.' ,p_internal_or_external => gc_internal_reason); END IF; IF p_enqu.base_required = 'YES' THEN IF l_rec_module.bas_code IS NOT NULL THEN l_additional_costs := get_base(p_bas_code => l_rec_module.bas_code ,p_regi_code => l_regi_code); l_rec_module.bas_selling_price := l_additional_costs.selling_price; l_rec_module.bas_cost_price := l_additional_costs.cost_price; l_rec_module.bas_delivery_cost := l_additional_costs.delivery_cost; IF l_rec_module.bas_selling_price IS NULL THEN l_this_is_automatic_quote := FALSE; add_quote_reason(p_enqu.id ,p_reason => 'Unable to find selling price for base ' || l_rec_module.bas_code || '.' ,p_internal_or_external => gc_internal_reason); END IF; ELSE l_this_is_automatic_quote := FALSE; add_quote_reason(p_enqu.id ,p_reason => 'Module is not available with base.' ,p_internal_or_external => gc_internal_reason); END IF; END IF; IF p_enqu.housing_required = 'YES' THEN IF l_rec_module.hou_code IS NOT NULL THEN l_additional_costs := get_housing(p_hou_code => l_rec_module.hou_code ,p_regi_code => l_regi_code); l_rec_module.hou_selling_price := l_additional_costs.selling_price; l_rec_module.hou_cost_price := l_additional_costs.cost_price; l_rec_module.hou_delivery_cost := l_additional_costs.delivery_cost; IF l_rec_module.hou_selling_price IS NULL THEN l_this_is_automatic_quote := FALSE; add_quote_reason(p_enqu.id ,p_reason => 'Unable to find selling price for housing ' || l_rec_module.hou_code || '.' ,p_internal_or_external => gc_internal_reason); END IF; ELSE l_this_is_automatic_quote := FALSE; add_quote_reason(p_enqu.id ,p_reason => 'Module is not available with housing.' ,p_internal_or_external => gc_internal_reason); END IF; END IF; IF p_enqu.amr_required = 'YES' THEN l_additional_costs := get_aico(p_adit_code => 'AMR' ,p_regi_code => l_regi_code ,p_enty_code => p_enqu.enty_code ,p_mety_code => l_rec_module.mety_code ,p_mesc_code => l_rec_module.mesc_code); l_rec_module.amr_selling_price := l_additional_costs.selling_price; l_rec_module.amr_cost_price := l_additional_costs.cost_price; l_rec_module.amr_delivery_cost := l_additional_costs.delivery_cost; l_rec_module.amr_lead_time := l_additional_costs.lead_time; IF l_rec_module.amr_selling_price IS NULL THEN l_this_is_automatic_quote := FALSE; add_quote_reason(p_enqu.id ,p_reason => 'Unable to find selling price for AMR.' ,p_internal_or_external => gc_internal_reason); END IF; IF l_rec_module.amr_lead_time IS NULL THEN l_this_is_automatic_quote := FALSE; add_quote_reason(p_enqu.id ,p_reason => 'Unable to find lead time for AMR.' ,p_internal_or_external => gc_internal_reason); END IF; END IF; IF p_enqu.ems_required = 'YES' THEN l_additional_costs := get_aico(p_adit_code => 'EMS' ,p_regi_code => l_regi_code ,p_enty_code => p_enqu.enty_code ,p_mety_code => l_rec_module.mety_code ,p_mesc_code => l_rec_module.mesc_code); l_rec_module.ems_selling_price := l_additional_costs.selling_price; l_rec_module.ems_cost_price := l_additional_costs.cost_price; l_rec_module.ems_delivery_cost := l_additional_costs.delivery_cost; l_rec_module.ems_lead_time := l_additional_costs.lead_time; IF l_rec_module.ems_selling_price IS NULL THEN l_this_is_automatic_quote := FALSE; add_quote_reason(p_enqu.id ,p_reason => 'Unable to find selling price for EMS.' ,p_internal_or_external => gc_internal_reason); END IF; IF l_rec_module.ems_lead_time IS NULL THEN l_this_is_automatic_quote := FALSE; add_quote_reason(p_enqu.id ,p_reason => 'Unable to find lead time for EMS.' ,p_internal_or_external => gc_internal_reason); END IF; END IF; IF p_enqu.bypass_required = 'YES' THEN l_additional_costs := get_aico(p_adit_code => 'BYPASS' ,p_regi_code => l_regi_code ,p_enty_code => p_enqu.enty_code ,p_mety_code => l_rec_module.mety_code ,p_mesc_code => l_rec_module.mesc_code); l_rec_module.bypass_selling_price := l_additional_costs.selling_price; l_rec_module.bypass_cost_price := l_additional_costs.cost_price; l_rec_module.bypass_delivery_cost := l_additional_costs.delivery_cost; l_rec_module.bypass_lead_time := l_additional_costs.lead_time; IF l_rec_module.bypass_selling_price IS NULL THEN l_this_is_automatic_quote := FALSE; add_quote_reason(p_enqu.id ,p_reason => 'Unable to find selling price for BYPASS.' ,p_internal_or_external => gc_internal_reason); END IF; IF l_rec_module.bypass_lead_time IS NULL THEN l_this_is_automatic_quote := FALSE; add_quote_reason(p_enqu.id ,p_reason => 'Unable to find lead time for BYPASS.' ,p_internal_or_external => gc_internal_reason); END IF; END IF; -- -- Purging required on Fixed Costs basis -- IF p_enqu.purging_required = 'FC' THEN l_additional_costs := get_aico(p_adit_code => 'PURGING' ,p_regi_code => l_regi_code ,p_enty_code => p_enqu.enty_code ,p_mety_code => l_rec_module.mety_code ,p_mesc_code => l_rec_module.mesc_code); l_rec_module.purging_selling_price := l_additional_costs.selling_price; l_rec_module.purging_cost_price := l_additional_costs.cost_price; l_rec_module.purging_delivery_cost := l_additional_costs.delivery_cost; l_rec_module.purging_lead_time := l_additional_costs.lead_time; IF l_rec_module.purging_selling_price IS NULL THEN l_this_is_automatic_quote := FALSE; add_quote_reason(p_enqu.id ,p_reason => 'Unable to find selling price for PURGING.' ,p_internal_or_external => gc_internal_reason); END IF; END IF; -- -- Purging required on Time and Materials basis -- IF p_enqu.purging_required = 'TM' THEN l_rec_module.purging_selling_price := NULL; l_rec_module.purging_cost_price := NULL; l_rec_module.purging_delivery_cost := NULL; l_rec_module.purging_lead_time := NULL; END IF; -- -- Purging to be performed by customer -- IF p_enqu.purging_required = 'NO' THEN l_rec_module.self_purging_selling_price := NULL; l_rec_module.self_purging_cost_price := NULL; l_rec_module.self_purging_delivery_cost := NULL; l_rec_module.self_purging_lead_time := NULL; END IF; /* Lifting gear required UNLESS LP Diaphragm */ IF NOT (l_rec_module.mety_code = 'DIAPHRAGM' AND l_rec_module.laco_svcpt_code = 'LP') THEN l_additional_costs := get_aico(p_adit_code => 'LIFTING GEAR' ,p_regi_code => l_regi_code ,p_enty_code => p_enqu.enty_code ,p_mety_code => l_rec_module.mety_code ,p_mesc_code => l_rec_module.mesc_code); l_rec_module.lifting_gear_selling_price := l_additional_costs.selling_price; l_rec_module.lifting_gear_cost_price := l_additional_costs.cost_price; l_rec_module.lifting_gear_delivery_cost := l_additional_costs.delivery_cost; l_rec_module.lifting_gear_lead_time := l_additional_costs.lead_time; IF l_rec_module.lifting_gear_selling_price IS NULL THEN l_this_is_automatic_quote := FALSE; add_quote_reason(p_enqu.id ,p_reason => 'Unable to find selling price for LIFTING GEAR.' ,p_internal_or_external => gc_internal_reason); END IF; IF l_rec_module.lifting_gear_lead_time IS NULL THEN l_this_is_automatic_quote := FALSE; add_quote_reason(p_enqu.id ,p_reason => 'Unable to find lead time for LIFTING GEAR.' ,p_internal_or_external => gc_internal_reason); END IF; END IF; IF l_this_is_automatic_quote THEN l_produced_automatic_quote := TRUE; add_quote_reason(p_enqu_id => p_enqu.id ,p_reason => 'Producing an automatic quote.' ,p_internal_or_external => gc_internal_reason); l_qute_id := start_quote(p_enqu_id => p_enqu.id ,p_manual_or_automatic => gc_automatic_quote ,p_rfq_prty_id => p_rfq_prty_id ,p_owner_prty_id => p_owner_prty_id); INSERT INTO quote_items (id ,qute_id ,enty_code ,mety_code ,svcpt_code ,mesc_code ,cost_price ,selling_price ,delivery_price ,lead_time ,quit_type) VALUES (quit_seq.nextval ,l_qute_id ,p_enqu.enty_code ,l_rec_module.laco_mety_code ,l_rec_module.laco_svcpt_code ,l_rec_module.laco_mesc_code ,l_rec_module.laco_cost_price ,l_rec_module.laco_selling_price ,l_rec_module.laco_delivery_cost ,l_rec_module.modu_lead_time ,'LQI'); INSERT INTO quote_items (id ,qute_id ,modu_code ,qmax ,qmin ,inlet_orientation ,outlet_orientation ,cost_price ,selling_price ,delivery_price ,quit_type) VALUES (quit_seq.nextval ,l_qute_id ,l_rec_module.modu_code ,l_rec_module.qmax ,l_rec_module.qmin ,l_rec_module.modu_inlet_orientation ,l_rec_module.modu_outlet_orientation ,l_rec_module.modu_cost_price ,l_rec_module.modu_selling_price ,l_rec_module.modu_delivery_cost ,'MQI'); IF p_enqu.housing_required = 'YES' THEN INSERT INTO quote_items (id ,qute_id ,hou_code ,cost_price ,selling_price ,delivery_price ,quit_type) VALUES (quit_seq.nextval ,l_qute_id ,l_rec_module.hou_code ,l_rec_module.hou_cost_price ,l_rec_module.hou_selling_price ,l_rec_module.hou_delivery_cost ,'HQI'); END IF; IF p_enqu.base_required = 'YES' THEN INSERT INTO quote_items (id ,qute_id ,bas_code ,cost_price ,selling_price ,delivery_price ,quit_type) VALUES (quit_seq.nextval ,l_qute_id ,l_rec_module.bas_code ,l_rec_module.bas_cost_price ,l_rec_module.bas_selling_price ,l_rec_module.bas_delivery_cost ,'BQI'); END IF; IF p_enqu.amr_required = 'YES' THEN INSERT INTO quote_items (id ,qute_id ,adit_code ,cost_price ,selling_price ,delivery_price ,lead_time ,quit_type) VALUES (quit_seq.nextval ,l_qute_id ,'AMR' ,l_rec_module.amr_cost_price ,l_rec_module.amr_selling_price ,l_rec_module.amr_delivery_cost ,l_rec_module.amr_lead_time ,'AQI'); END IF; IF p_enqu.ems_required = 'YES' THEN INSERT INTO quote_items (id ,qute_id ,adit_code ,cost_price ,selling_price ,delivery_price ,lead_time ,quit_type) VALUES (quit_seq.nextval ,l_qute_id ,'EMS' ,l_rec_module.ems_cost_price ,l_rec_module.ems_selling_price ,l_rec_module.ems_delivery_cost ,l_rec_module.ems_lead_time ,'AQI'); END IF; IF p_enqu.bypass_required = 'YES' THEN INSERT INTO quote_items (id ,qute_id ,adit_code ,cost_price ,selling_price ,delivery_price ,lead_time ,quit_type) VALUES (quit_seq.nextval ,l_qute_id ,'BYPASS' ,l_rec_module.bypass_cost_price ,l_rec_module.bypass_selling_price ,l_rec_module.bypass_delivery_cost ,l_rec_module.bypass_lead_time ,'AQI'); END IF; IF p_enqu.purging_required IN ('TM' ,'FC') OR p_enqu.enty_code = 'ADVERSARIAL' THEN INSERT INTO quote_items (id ,qute_id ,adit_code ,cost_price ,selling_price ,delivery_price ,lead_time ,quit_type) VALUES (quit_seq.nextval ,l_qute_id ,'PURGING' ,l_rec_module.purging_cost_price ,l_rec_module.purging_selling_price ,l_rec_module.purging_delivery_cost ,l_rec_module.purging_lead_time ,'AQI'); END IF; IF p_enqu.purging_required IN ('NO') THEN INSERT INTO quote_items (id ,qute_id ,adit_code ,cost_price ,selling_price ,delivery_price ,lead_time ,quit_type) VALUES (quit_seq.nextval ,l_qute_id ,'SELFPURGING' ,l_rec_module.self_purging_cost_price ,l_rec_module.self_purging_selling_price ,l_rec_module.self_purging_delivery_cost ,l_rec_module.self_purging_lead_time ,'AQI'); END IF; IF l_rec_module.lifting_gear_selling_price IS NOT NULL THEN INSERT INTO quote_items (id ,qute_id ,adit_code ,cost_price ,selling_price ,delivery_price ,lead_time ,quit_type) VALUES (quit_seq.nextval ,l_qute_id ,'LIFTING GEAR' ,l_rec_module.lifting_gear_cost_price ,l_rec_module.lifting_gear_selling_price ,l_rec_module.lifting_gear_delivery_cost ,l_rec_module.lifting_gear_lead_time ,'AQI'); END IF; pl('call mip_quotation_document.generate_quote_pdf' ,$$PLSQL_LINE); -- Generate the quote PDF /*BEGIN*/ l_quote_document := mip_quotation_document.generate_quote_pdf(p_quote_id => l_qute_id); /* EXCEPTION WHEN OTHERS THEN cout_err.report_and_stop; END; */ pl('return mip_quotation_document.generate_quote_pdf' ,$$PLSQL_LINE); add_quote_reason(p_enqu_id => p_enqu.id ,p_reason => 'Produced Quote Document ' || l_quote_document || '.' ,p_internal_or_external => gc_internal_reason); make_quote_available(l_qute_id); END IF; -- automatic quote END LOOP; END IF; -- not manual quote IF l_produced_automatic_quote THEN p_manual_or_automatic_quote := gc_automatic_quote; add_quote_reason(p_enqu_id => p_enqu.id ,p_reason => '-- Produced an automatic quote.' ,p_internal_or_external => gc_internal_reason); --email_aq_generated(p_enqu.id); ELSE p_manual_or_automatic_quote := gc_manual_quote; add_quote_reason(p_enqu_id => p_enqu.id ,p_reason => '-- Automatic quote failed - Manual quote required.' ,p_internal_or_external => gc_internal_reason); END IF; pl('produce_module_quotes:exit' ,$$PLSQL_LINE); END produce_module_quotes; PROCEDURE produce_install_quotes(p_enqu IN t_enqu ,p_rfq_prty_id IN parties.id%TYPE ,p_owner_prty_id IN parties.id%TYPE DEFAULT NULL ,p_manual_or_automatic_quote IN OUT t_manual_or_automatic_quote) IS l_regi_code regions.code%TYPE := mip_regions.get_region_for_postcode(p_enqu.install_postcode); BEGIN pl('produce_install_quotes' ,$$PLSQL_LINE); cout_assert.istrue(p_enqu.enty_code IN ('STD INSTALL' ,'INSTALL') ,'Attempted to produce an installation quote for enquiry of type ' || p_enqu.enty_code); cout_assert.isnotnull(l_regi_code ,'Attempted to produce an installation quote for enquiry for a installation postcode (' || p_enqu.install_postcode || ') without a region.'); produce_module_quotes(p_enqu => p_enqu ,p_rfq_prty_id => p_rfq_prty_id ,p_owner_prty_id => p_owner_prty_id ,p_manual_or_automatic_quote => p_manual_or_automatic_quote); pl('produce_install_quotes:exit' ,$$PLSQL_LINE); END produce_install_quotes; PROCEDURE produce_exchange_quotes(p_enqu IN t_enqu ,p_rfq_prty_id IN parties.id%TYPE ,p_owner_prty_id IN parties.id%TYPE DEFAULT NULL ,p_manual_or_automatic_quote IN OUT t_manual_or_automatic_quote) IS l_regi_code regions.code%TYPE := mip_regions.get_region_for_postcode(p_enqu.install_postcode); BEGIN pl('produce_exchange_quotes:entry:' || p_enqu.enty_code ,$$PLSQL_LINE); cout_assert.istrue(p_enqu.enty_code IN ('EXCHANGE' ,'STD EXCHANGE') ,'Attempted to produce an exchange quote for enquiry of type ' || p_enqu.enty_code); cout_assert.isnotnull(l_regi_code ,'Attempted to produce an exchange quote for enquiry for a installation postcode (' || p_enqu.install_postcode || ') without a region.'); produce_module_quotes(p_enqu => p_enqu ,p_rfq_prty_id => p_rfq_prty_id ,p_owner_prty_id => p_owner_prty_id ,p_manual_or_automatic_quote => p_manual_or_automatic_quote); pl('produce_exchange_quotes:exit' ,$$PLSQL_LINE); END produce_exchange_quotes; PROCEDURE produce_change_capacity_quotes(p_enqu IN t_enqu ,p_rfq_prty_id IN parties.id%TYPE ,p_owner_prty_id IN parties.id%TYPE DEFAULT NULL ,p_manual_or_automatic_quote IN OUT t_manual_or_automatic_quote) IS l_regi_code regions.code%TYPE := mip_regions.get_region_for_postcode(p_enqu.install_postcode); BEGIN pl('produce_change_capacity_quotes' ,$$PLSQL_LINE); cout_assert.istrue(p_enqu.enty_code IN ('CHANGE CAPACITY') ,'Attempted to produce a change capacity quote for enquiry of type ' || p_enqu.enty_code); cout_assert.isnotnull(l_regi_code ,'Attempted to produce a change capacity quote for enquiry for a installation postcode (' || p_enqu.install_postcode || ') without a region.'); produce_module_quotes(p_enqu => p_enqu ,p_rfq_prty_id => p_rfq_prty_id ,p_owner_prty_id => p_owner_prty_id ,p_manual_or_automatic_quote => p_manual_or_automatic_quote); pl('produce_change_capacity_quotes:exit' ,$$PLSQL_LINE); END produce_change_capacity_quotes; PROCEDURE produce_labour_only_quotes(p_enqu IN t_enqu ,p_rfq_prty_id IN parties.id%TYPE ,p_owner_prty_id IN parties.id%TYPE DEFAULT NULL ,p_manual_or_automatic_quote IN OUT t_manual_or_automatic_quote) IS l_produced_automatic_quote BOOLEAN; l_this_is_automatic_quote BOOLEAN; l_regi_code regions.code%TYPE := mip_regions.get_region_for_postcode(p_enqu.install_postcode); l_lead_time NUMBER; l_qute_id quotes.id%TYPE; l_labour_costs t_rec_additional_costs; l_quote_document VARCHAR2(240); l_lifting_gear_costs t_rec_additional_costs; l_purging_costs t_rec_additional_costs; l_self_purging_costs t_rec_additional_costs; l_required_qmax NUMBER; l_existing_rec_metr_details t_rec_metr_details; BEGIN pl('produce_labour_only_quotes:entry:' || p_enqu.id || ':' || p_enqu.enty_code ,$$PLSQL_LINE); cout_assert.istrue(p_enqu.enty_code IN ('REMOVE' ,'STD REMOVE' ,'ADVERSARIAL' ,'OFMAT' ,'ALTERATION') ,'Attempted to produce a labour-only quote for enquiry of type ' || p_enqu.enty_code); cout_assert.isnotnull(l_regi_code ,'Attempted to produce quote for enquiry for a installation postcode (' || p_enqu.install_postcode || ') without a region.'); l_this_is_automatic_quote := TRUE; l_existing_rec_metr_details := get_existing_metr_details(p_enqu); add_quote_reason(p_enqu.id ,p_reason => 'Attempting an automatic labour quote for ' || p_enqu.id || '.' || 'Enquiry Code=' || p_enqu.enty_code || ', SVCP ' || p_enqu.required_svcp_code || ', Meter Type Code=' || l_existing_rec_metr_details.mety_code || ', Meter Size Code=' || l_existing_rec_metr_details.mesc_code || '.' ,p_internal_or_external => gc_internal_reason); l_labour_costs := get_laco(p_enty_code => p_enqu.enty_code ,p_regi_code => l_regi_code ,p_mety_code => l_existing_rec_metr_details.mety_code ,p_mesc_code => l_existing_rec_metr_details.mesc_code ,p_svcp_code => p_enqu.required_svcp_code); IF l_labour_costs.selling_price IS NULL THEN l_this_is_automatic_quote := FALSE; add_quote_reason(p_enqu.id ,p_reason => 'Unable to find Labour Cost (selling price) for this Enquiry Type Code: ' || p_enqu.enty_code || ', Meter Type Code:' || l_existing_rec_metr_details.mety_code || ', Meter Size Code:' || l_existing_rec_metr_details.mesc_code || ', Service Pressure Code:' || p_enqu.required_svcp_code || CASE l_regi_code WHEN NULL THEN '' ELSE ' for region code ' || l_regi_code END || '.' ,p_internal_or_external => gc_internal_reason); END IF; --over-ride the enquiry qmax if an existing meter is specified as --we can get the real qmax value (off the module) IF l_existing_rec_metr_details.mesc_code IS NOT NULL AND l_existing_rec_metr_details.mesc_code <> 'OTHER' THEN l_required_qmax := get_qmax_from_mesc(l_existing_rec_metr_details.mesc_code); ELSE l_required_qmax := p_enqu.qmax; END IF; l_lead_time := get_lead_time(p_enty_code => p_enqu.enty_code ,p_mety_code => l_existing_rec_metr_details.mety_code ,p_svcp_code => p_enqu.required_svcp_code ,p_qmax => l_required_qmax); IF l_lead_time IS NULL THEN l_this_is_automatic_quote := FALSE; add_quote_reason(p_enqu.id ,p_reason => 'Unable to find lead time for ' || get_lead_time_desc(p_enty_code => p_enqu.enty_code ,p_mety_code => l_existing_rec_metr_details.mety_code ,p_svcp_code => p_enqu.required_svcp_code ,p_qmax => p_enqu.qmax) ,p_internal_or_external => gc_internal_reason); END IF; -- -- Purging required on Fixed Costs basis -- IF p_enqu.purging_required = 'FC' THEN l_purging_costs := get_aico(p_adit_code => 'PURGING' ,p_regi_code => l_regi_code ,p_enty_code => p_enqu.enty_code ,p_mety_code => l_existing_rec_metr_details.mety_code ,p_mesc_code => l_existing_rec_metr_details.mesc_code); IF l_purging_costs.selling_price IS NULL THEN l_this_is_automatic_quote := FALSE; add_quote_reason(p_enqu.id ,p_reason => 'Unable to find selling price for PURGING.' ,p_internal_or_external => gc_internal_reason); END IF; END IF; -- -- Purging required on Time and Materials basis -- IF p_enqu.purging_required = 'TM' OR p_enqu.enty_code = 'ADVERSARIAL' THEN l_purging_costs.selling_price := NULL; l_purging_costs.cost_price := NULL; l_purging_costs.delivery_cost := NULL; l_purging_costs.lead_time := NULL; END IF; -- -- Purging to be carried out by customer -- IF p_enqu.purging_required = 'TM' OR p_enqu.enty_code = 'ADVERSARIAL' THEN l_self_purging_costs.selling_price := NULL; l_self_purging_costs.cost_price := NULL; l_self_purging_costs.delivery_cost := NULL; l_self_purging_costs.lead_time := NULL; END IF; /* Lifting gear required UNLESS LP Diaphragm */ IF NOT (l_existing_rec_metr_details.mety_code = 'DIAPHRAGM' AND l_labour_costs.svcpt_code = 'LP') THEN l_lifting_gear_costs := get_aico(p_adit_code => 'LIFTING GEAR' ,p_regi_code => l_regi_code ,p_enty_code => p_enqu.enty_code ,p_mety_code => l_existing_rec_metr_details.mety_code ,p_mesc_code => l_existing_rec_metr_details.mesc_code); IF l_lifting_gear_costs.selling_price IS NULL THEN l_this_is_automatic_quote := FALSE; add_quote_reason(p_enqu.id ,p_reason => 'Unable to find selling price for LIFTING GEAR.' ,p_internal_or_external => gc_internal_reason); END IF; IF l_lifting_gear_costs.lead_time IS NULL THEN l_this_is_automatic_quote := FALSE; add_quote_reason(p_enqu.id ,p_reason => 'Unable to find lead time for LIFTING GEAR.' ,p_internal_or_external => gc_internal_reason); END IF; END IF; IF l_this_is_automatic_quote THEN l_produced_automatic_quote := TRUE; add_quote_reason(p_enqu_id => p_enqu.id ,p_reason => 'producing an automatic quote. ' ,p_internal_or_external => gc_internal_reason); l_qute_id := start_quote(p_enqu_id => p_enqu.id ,p_manual_or_automatic => gc_automatic_quote ,p_rfq_prty_id => p_rfq_prty_id ,p_owner_prty_id => p_owner_prty_id); INSERT INTO quote_items (id ,qute_id ,enty_code ,svcpt_code ,mesc_code ,mety_code ,cost_price ,selling_price ,delivery_price ,lead_time ,quit_type) VALUES (quit_seq.nextval ,l_qute_id ,p_enqu.enty_code ,l_labour_costs.svcpt_code ,l_existing_rec_metr_details.mesc_code ,l_existing_rec_metr_details.mety_code ,l_labour_costs.cost_price ,l_labour_costs.selling_price ,l_labour_costs.delivery_cost ,l_lead_time ,'LQI'); IF l_lifting_gear_costs.selling_price IS NOT NULL THEN INSERT INTO quote_items (id ,qute_id ,adit_code ,cost_price ,selling_price ,delivery_price ,lead_time ,quit_type) VALUES (quit_seq.nextval ,l_qute_id ,'LIFTING GEAR' ,l_lifting_gear_costs.cost_price ,l_lifting_gear_costs.selling_price ,l_lifting_gear_costs.delivery_cost ,l_lifting_gear_costs.lead_time ,'AQI'); END IF; IF p_enqu.purging_required IN ('TM' ,'FC') THEN INSERT INTO quote_items (id ,qute_id ,adit_code ,cost_price ,selling_price ,delivery_price ,lead_time ,quit_type) VALUES (quit_seq.nextval ,l_qute_id ,'PURGING' ,l_purging_costs.cost_price ,l_purging_costs.selling_price ,l_purging_costs.delivery_cost ,l_purging_costs.lead_time ,'AQI'); END IF; -- -- Purging to be carried out by customer -- IF p_enqu.purging_required IN ('NO') THEN INSERT INTO quote_items (id ,qute_id ,adit_code ,cost_price ,selling_price ,delivery_price ,lead_time ,quit_type) VALUES (quit_seq.nextval ,l_qute_id ,'SELFPURGING' ,l_self_purging_costs.cost_price ,l_self_purging_costs.selling_price ,l_self_purging_costs.delivery_cost ,l_self_purging_costs.lead_time ,'AQI'); END IF; -- Generate the quote PDF l_quote_document := mip_quotation_document.generate_quote_pdf(p_quote_id => l_qute_id); add_quote_reason(p_enqu_id => p_enqu.id ,p_reason => 'produced quote document ' || l_quote_document || ' . ' ,p_internal_or_external => gc_internal_reason); make_quote_available(l_qute_id); END IF; -- automatic quote IF l_produced_automatic_quote THEN p_manual_or_automatic_quote := gc_automatic_quote; add_quote_reason(p_enqu_id => p_enqu.id ,p_reason => ' -- Produced an automatic quote.' ,p_internal_or_external => gc_internal_reason); ELSE p_manual_or_automatic_quote := gc_manual_quote; add_quote_reason(p_enqu_id => p_enqu.id ,p_reason => '-- Automatic quote failed - Manual quote required.' ,p_internal_or_external => gc_internal_reason); END IF; pl('produce_labour_only_quotes:exit' ,$$PLSQL_LINE); END produce_labour_only_quotes; PROCEDURE produce_removal_quotes(p_enqu IN t_enqu ,p_rfq_prty_id IN parties.id%TYPE ,p_owner_prty_id IN parties.id%TYPE DEFAULT NULL ,p_manual_or_automatic_quote IN OUT t_manual_or_automatic_quote) IS BEGIN pl('produce_removal_quotes:entry' ,$$PLSQL_LINE); cout_assert.istrue(p_enqu.enty_code IN ('REMOVE' ,'STD REMOVE' ,'ADVERSARIAL') ,'Attempted to produce a removal quote for enquiry of type ' || p_enqu.enty_code); produce_labour_only_quotes(p_enqu => p_enqu ,p_rfq_prty_id => p_rfq_prty_id ,p_owner_prty_id => p_owner_prty_id ,p_manual_or_automatic_quote => p_manual_or_automatic_quote); pl('produce_removal_quotes:exit' ,$$PLSQL_LINE); END produce_removal_quotes; PROCEDURE produce_ofmat_quotes(p_enqu IN t_enqu ,p_rfq_prty_id IN parties.id%TYPE ,p_owner_prty_id IN parties.id%TYPE DEFAULT NULL ,p_manual_or_automatic_quote IN OUT t_manual_or_automatic_quote) IS BEGIN pl('produce_ofmat_quotes:entry' ,$$PLSQL_LINE); cout_assert.istrue(p_enqu.enty_code IN ('OFMAT') ,'Attempted to produce an OFMAT quote for enquiry of type ' || p_enqu.enty_code); produce_labour_only_quotes(p_enqu => p_enqu ,p_rfq_prty_id => p_rfq_prty_id ,p_owner_prty_id => p_owner_prty_id ,p_manual_or_automatic_quote => p_manual_or_automatic_quote); pl('produce_ofmat_quotes:exit' ,$$PLSQL_LINE); END produce_ofmat_quotes; PROCEDURE produce_alteration_quotes(p_enqu IN t_enqu ,p_rfq_prty_id IN parties.id%TYPE ,p_owner_prty_id IN parties.id%TYPE DEFAULT NULL ,p_manual_or_automatic_quote IN OUT t_manual_or_automatic_quote) IS BEGIN pl('produce_alteration_quotes:entry' ,$$PLSQL_LINE); cout_assert.istrue(p_enqu.enty_code IN ('ALTERATION') ,'Attempted to produce an ALTERATION quote for enquiry of type ' || p_enqu.enty_code); produce_labour_only_quotes(p_enqu => p_enqu ,p_rfq_prty_id => p_rfq_prty_id ,p_owner_prty_id => p_owner_prty_id ,p_manual_or_automatic_quote => p_manual_or_automatic_quote); pl('produce_alteration_quotes:exit' ,$$PLSQL_LINE); END produce_alteration_quotes; PROCEDURE produce_addon_quotes(p_enqu IN t_enqu ,p_rfq_prty_id IN parties.id%TYPE ,p_owner_prty_id IN parties.id%TYPE DEFAULT NULL ,p_manual_or_automatic_quote IN OUT t_manual_or_automatic_quote) IS l_produced_automatic_quote BOOLEAN; l_this_is_automatic_quote BOOLEAN; l_regi_code regions.code%TYPE := mip_regions.get_region_for_postcode(p_enqu.install_postcode); l_qute_id quotes.id%TYPE; l_svcpt_code service_pressure_types.code%TYPE; l_lifting_gear_costs t_rec_additional_costs; l_amr_costs t_rec_additional_costs; l_ems_costs t_rec_additional_costs; l_bypass_costs t_rec_additional_costs; l_purging_costs t_rec_additional_costs; l_quote_document VARCHAR2(240); l_existing_rec_metr_details t_rec_metr_details; BEGIN pl('produce_addon_quotes:entry' ,$$PLSQL_LINE); cout_assert.istrue(p_enqu.enty_code IN ('ADDON') ,'Attempted to produce an ADDON quote for enquiry of type ' || p_enqu.enty_code); cout_assert.isnotnull(l_regi_code ,'Attempted to produce a quote for enquiry for a installation postcode (' || p_enqu.install_postcode || ') without a region.'); add_quote_reason(p_enqu.id ,p_reason => 'Attempting an automatic addon quote for ' || p_enqu.id || '.' || CASE p_enqu.amr_required WHEN 'YES' THEN 'AMR is required. ' END || CASE p_enqu.ems_required WHEN 'YES' THEN 'EMS is required. ' END || CASE p_enqu.bypass_required WHEN 'YES' THEN 'Bypass is required. ' END ,p_internal_or_external => gc_internal_reason); l_this_is_automatic_quote := TRUE; l_existing_rec_metr_details := get_existing_metr_details(p_enqu); -- -- check whether we have the required prices -- if we do not, then we may need to produce a manual quote -- IF p_enqu.amr_required = 'YES' THEN l_amr_costs := get_aico(p_adit_code => 'AMR' ,p_regi_code => l_regi_code ,p_enty_code => p_enqu.enty_code ,p_mety_code => l_existing_rec_metr_details.mety_code ,p_mesc_code => l_existing_rec_metr_details.mesc_code); IF l_amr_costs.selling_price IS NULL THEN l_this_is_automatic_quote := FALSE; add_quote_reason(p_enqu.id ,p_reason => 'Unable to find selling price for AMR.' ,p_internal_or_external => gc_internal_reason); END IF; IF l_amr_costs.lead_time IS NULL THEN l_this_is_automatic_quote := FALSE; add_quote_reason(p_enqu.id ,p_reason => 'Unable to find lead time for AMR.' ,p_internal_or_external => gc_internal_reason); END IF; END IF; IF p_enqu.ems_required = 'YES' THEN l_ems_costs := get_aico(p_adit_code => 'EMS' ,p_regi_code => l_regi_code ,p_enty_code => p_enqu.enty_code ,p_mety_code => l_existing_rec_metr_details.mety_code ,p_mesc_code => l_existing_rec_metr_details.mesc_code); IF l_ems_costs.selling_price IS NULL THEN l_this_is_automatic_quote := FALSE; add_quote_reason(p_enqu.id ,p_reason => 'Unable to find selling price for EMS.' ,p_internal_or_external => gc_internal_reason); END IF; IF l_ems_costs.lead_time IS NULL THEN l_this_is_automatic_quote := FALSE; add_quote_reason(p_enqu.id ,p_reason => 'Unable to find lead time for EMS.' ,p_internal_or_external => gc_internal_reason); END IF; END IF; IF p_enqu.bypass_required = 'YES' THEN l_bypass_costs := get_aico(p_adit_code => 'BYPASS' ,p_regi_code => l_regi_code ,p_enty_code => p_enqu.enty_code ,p_mety_code => l_existing_rec_metr_details.mety_code ,p_mesc_code => l_existing_rec_metr_details.mesc_code); IF l_bypass_costs.selling_price IS NULL THEN l_this_is_automatic_quote := FALSE; add_quote_reason(p_enqu.id ,p_reason => 'Unable to find selling price for BYPASS.' ,p_internal_or_external => gc_internal_reason); END IF; IF l_bypass_costs.lead_time IS NULL THEN l_this_is_automatic_quote := FALSE; add_quote_reason(p_enqu.id ,p_reason => 'Unable to find lead time for BYPASS.' ,p_internal_or_external => gc_internal_reason); END IF; END IF; -- -- Purging required on Fixed Costs basis -- IF p_enqu.purging_required = 'FC' THEN l_purging_costs := get_aico(p_adit_code => 'PURGING' ,p_regi_code => l_regi_code ,p_enty_code => p_enqu.enty_code ,p_mety_code => l_existing_rec_metr_details.mety_code ,p_mesc_code => l_existing_rec_metr_details.mesc_code); IF l_purging_costs.selling_price IS NULL THEN l_this_is_automatic_quote := FALSE; add_quote_reason(p_enqu.id ,p_reason => 'Unable to find selling price for PURGING.' ,p_internal_or_external => gc_internal_reason); END IF; END IF; -- -- Purging required on Time and Materials basis -- IF p_enqu.purging_required = 'TM' OR p_enqu.enty_code = 'ADVERSARIAL' THEN l_purging_costs.selling_price := NULL; l_purging_costs.cost_price := NULL; l_purging_costs.delivery_cost := NULL; l_purging_costs.lead_time := NULL; END IF; l_svcpt_code := get_svcpt_for_svcp(p_enqu.required_svcp_code); /* Lifting gear required UNLESS LP Diaphragm */ IF NOT (p_enqu.existing_mety_code = 'DIAPHRAGM' AND l_svcpt_code = 'LP') THEN l_lifting_gear_costs := get_aico(p_adit_code => 'LIFTING GEAR' ,p_regi_code => l_regi_code ,p_enty_code => p_enqu.enty_code ,p_mety_code => l_existing_rec_metr_details.mety_code ,p_mesc_code => l_existing_rec_metr_details.mesc_code); IF l_lifting_gear_costs.selling_price IS NULL THEN l_this_is_automatic_quote := FALSE; add_quote_reason(p_enqu.id ,p_reason => 'Unable to find selling price for LIFTING GEAR.' ,p_internal_or_external => gc_internal_reason); END IF; IF l_lifting_gear_costs.lead_time IS NULL THEN l_this_is_automatic_quote := FALSE; add_quote_reason(p_enqu.id ,p_reason => 'Unable to find lead time for LIFTING GEAR.' ,p_internal_or_external => gc_internal_reason); END IF; END IF; IF l_this_is_automatic_quote THEN l_produced_automatic_quote := TRUE; add_quote_reason(p_enqu_id => p_enqu.id ,p_reason => 'Producing an automatic quote.' ,p_internal_or_external => gc_internal_reason); l_qute_id := start_quote(p_enqu_id => p_enqu.id ,p_manual_or_automatic => gc_automatic_quote ,p_rfq_prty_id => p_rfq_prty_id ,p_owner_prty_id => p_owner_prty_id); IF p_enqu.amr_required = 'YES' THEN INSERT INTO quote_items (id ,qute_id ,enty_code ,mety_code ,adit_code ,cost_price ,selling_price ,delivery_price ,quit_type) VALUES (quit_seq.nextval ,l_qute_id ,p_enqu.enty_code ,p_enqu.existing_mety_code ,l_amr_costs.adit_code ,l_amr_costs.cost_price ,l_amr_costs.selling_price ,l_amr_costs.delivery_cost ,'AQI'); END IF; IF p_enqu.ems_required = 'YES' THEN INSERT INTO quote_items (id ,qute_id ,enty_code ,mety_code ,adit_code ,cost_price ,selling_price ,delivery_price ,lead_time ,quit_type) VALUES (quit_seq.nextval ,l_qute_id ,p_enqu.enty_code ,p_enqu.existing_mety_code ,l_ems_costs.adit_code ,l_ems_costs.cost_price ,l_ems_costs.selling_price ,l_ems_costs.delivery_cost ,l_ems_costs.lead_time ,'AQI'); END IF; IF p_enqu.bypass_required = 'YES' THEN INSERT INTO quote_items (id ,qute_id ,enty_code ,mety_code ,adit_code ,cost_price ,selling_price ,delivery_price ,lead_time ,quit_type) VALUES (quit_seq.nextval ,l_qute_id ,p_enqu.enty_code ,p_enqu.existing_mety_code ,l_ems_costs.adit_code ,l_ems_costs.cost_price ,l_ems_costs.selling_price ,l_ems_costs.delivery_cost ,l_ems_costs.lead_time ,'AQI'); END IF; IF p_enqu.purging_required IN ('TM' ,'FC') OR p_enqu.enty_code = 'ADVERSARIAL' THEN INSERT INTO quote_items (id ,qute_id ,adit_code ,cost_price ,selling_price ,delivery_price ,lead_time ,quit_type) VALUES (quit_seq.nextval ,l_qute_id ,'PURGING' ,l_purging_costs.cost_price ,l_purging_costs.selling_price ,l_purging_costs.delivery_cost ,l_purging_costs.lead_time ,'AQI'); END IF; IF l_lifting_gear_costs.selling_price IS NOT NULL THEN INSERT INTO quote_items (id ,qute_id ,adit_code ,cost_price ,selling_price ,delivery_price ,lead_time ,quit_type) VALUES (quit_seq.nextval ,l_qute_id ,l_lifting_gear_costs.adit_code ,l_lifting_gear_costs.cost_price ,l_lifting_gear_costs.selling_price ,l_lifting_gear_costs.delivery_cost ,l_lifting_gear_costs.lead_time ,'AQI'); END IF; -- Generate the quote PDF l_quote_document := mip_quotation_document.generate_quote_pdf(p_quote_id => l_qute_id); add_quote_reason(p_enqu_id => p_enqu.id ,p_reason => 'Produced Quote Document ' || l_quote_document || '.' ,p_internal_or_external => gc_internal_reason); make_quote_available(l_qute_id); END IF; -- automatic quote IF l_produced_automatic_quote THEN p_manual_or_automatic_quote := gc_automatic_quote; add_quote_reason(p_enqu_id => p_enqu.id ,p_reason => '-- Produced an automatic quote.' ,p_internal_or_external => gc_internal_reason); ELSE p_manual_or_automatic_quote := gc_manual_quote; add_quote_reason(p_enqu_id => p_enqu.id ,p_reason => '-- Automatic quote failed - Manual quote required.' ,p_internal_or_external => gc_internal_reason); END IF; pl('produce_addon_quotes:exit' ,$$PLSQL_LINE); END produce_addon_quotes; PROCEDURE produce_automatic_quotes(p_enqu IN t_enqu ,p_rfq_prty_id IN parties.id%TYPE ,p_owner_prty_id IN parties.id%TYPE DEFAULT NULL ,p_manual_or_automatic_quote IN OUT t_manual_or_automatic_quote) IS BEGIN pl('produce_automatic_quotes:entry' ,$$PLSQL_LINE); cout_assert.istrue(p_manual_or_automatic_quote = gc_automatic_quote ,p_message => 'Attempted to produce automatic quote for enquiry marked as manual only'); IF p_enqu.enty_code IN ('INSTALL' ,'STD INSTALL') THEN produce_install_quotes(p_enqu => p_enqu ,p_rfq_prty_id => p_rfq_prty_id ,p_owner_prty_id => p_owner_prty_id ,p_manual_or_automatic_quote => p_manual_or_automatic_quote); ELSIF p_enqu.enty_code IN ('EXCHANGE' ,'STD EXCHANGE') THEN produce_exchange_quotes(p_enqu => p_enqu ,p_rfq_prty_id => p_rfq_prty_id ,p_owner_prty_id => p_owner_prty_id ,p_manual_or_automatic_quote => p_manual_or_automatic_quote); ELSIF p_enqu.enty_code IN ('REMOVE' ,'STD REMOVE' ,'ADVERSARIAL') THEN produce_removal_quotes(p_enqu => p_enqu ,p_rfq_prty_id => p_rfq_prty_id ,p_owner_prty_id => p_owner_prty_id ,p_manual_or_automatic_quote => p_manual_or_automatic_quote); ELSIF p_enqu.enty_code IN ('OFMAT') THEN produce_ofmat_quotes(p_enqu => p_enqu ,p_rfq_prty_id => p_rfq_prty_id ,p_owner_prty_id => p_owner_prty_id ,p_manual_or_automatic_quote => p_manual_or_automatic_quote); ELSIF p_enqu.enty_code IN ('ALTERATION') THEN produce_alteration_quotes(p_enqu => p_enqu ,p_rfq_prty_id => p_rfq_prty_id ,p_owner_prty_id => p_owner_prty_id ,p_manual_or_automatic_quote => p_manual_or_automatic_quote); ELSIF p_enqu.enty_code IN ('ADDON') THEN produce_addon_quotes(p_enqu => p_enqu ,p_rfq_prty_id => p_rfq_prty_id ,p_owner_prty_id => p_owner_prty_id ,p_manual_or_automatic_quote => p_manual_or_automatic_quote); ELSE cout_err.report_and_stop(p_exception_message => 'Attempted to produce automatic quote for unexpected enquiry type of ' || p_enqu.enty_code); END IF; pl('produce_automatic_quotes:exit' ,$$PLSQL_LINE); END produce_automatic_quotes; PROCEDURE produce_quotes(p_enqu_id IN enquiries.id%TYPE ,p_rfq_prty_id IN parties.id%TYPE ,p_owner_prty_id IN parties.id%TYPE DEFAULT NULL) IS l_manual_or_automatic_quote t_manual_or_automatic_quote; l_enqu t_enqu; BEGIN pl('produce_quotes' ,$$PLSQL_LINE); IF NOT ready_for_quote(p_enqu_id) THEN pl('Not all mandatory fields for Enquiry ID=' || p_enqu_id || ' have been completed' ,$$PLSQL_LINE); END IF; cout_assert.istrue(ready_for_quote(p_enqu_id) ,'Not all mandatory fields for Enquiry ID=' || p_enqu_id || ' have been completed'); cout_assert.istrue(not_already_submitted(p_enqu_id) ,'This enquiry has already been submitted for quotation'); mark_enquiry_submitted(p_enqu_id); -- remove any spurious values mip_enquiries_helper.purge_fields(p_id => p_enqu_id); SELECT * INTO l_enqu FROM enquiries WHERE id = p_enqu_id; IF tripartite_agreement_satisfied(p_enqu_id) THEN manual_or_automatic_quote(p_enqu => l_enqu ,p_manual_or_automatic_quote => l_manual_or_automatic_quote); IF l_manual_or_automatic_quote = gc_manual_quote THEN request_manual_quote(p_enqu_id => l_enqu.id ,p_rfq_prty_id => p_rfq_prty_id ,p_owner_prty_id => p_owner_prty_id); ELSE produce_automatic_quotes(p_enqu => l_enqu ,p_rfq_prty_id => p_rfq_prty_id ,p_owner_prty_id => p_owner_prty_id ,p_manual_or_automatic_quote => l_manual_or_automatic_quote); IF l_manual_or_automatic_quote = gc_automatic_quote THEN mark_enquiry_quoted(p_enqu_id); ELSE request_manual_quote(p_enqu_id => l_enqu.id ,p_rfq_prty_id => p_rfq_prty_id ,p_owner_prty_id => p_owner_prty_id); END IF; -- END IF; -- manual or automatic quote END IF; -- tripartite agreement pl('exit without error' ,$$PLSQL_LINE); EXCEPTION WHEN OTHERS THEN pl(SQLERRM ,$$PLSQL_LINE); cout_err.report_and_stop; END produce_quotes; END mip_quotation; /