---

CREATE OR REPLACE package body xxcstportal_pub_imos_cargo_request_pkg is
  -- ------------------------------------------
  procedure sendCargoRequestToIMOS(p_status varchar2, p_request_num varchar2, p_action varchar2) is
  begin
    xxcstportal_imos_cargo_request_pkg_s.sendCargoRequestToIMOS(p_status => p_status, p_request_num => p_request_num, p_action => p_action);
  end;
  -- ------------------------------------------
  procedure sendCargoVersion(p_packet_id varchar2, p_action varchar2) is
    l_dummy                number;
    l_mail_body            clob;
    l_style                varchar2(1000) := 'style="border: 1px solid #c1c7d0; padding: 3px 4px;"';
    l_begin_new            varchar2(1000);
    l_begin_update         varchar2(1000);
    l_table_header         varchar2(1000);
    l_table                clob := '';
    l_end                  varchar2(100) := '</tbody></table></div></html>';
    l_from                 varchar2(1000);
    l_tls_code             varchar2(1000);
    l_trader_email         varchar2(1000);
    l_subject              varchar2(4000);
    l_short_subject        varchar2(90);
    l_subject_prefix       varchar2(10) := '';
 
    l_earliest_laycan_from varchar2(100);
    l_corresponding_laycan_to varchar2(100);
    l_first_port_no varchar2(100);
    l_discharge_country varchar2(100);
    l_total_quantity varchar2(100);
    l_request_nums varchar2(4000);
    l_formatted_laycan_from varchar2(100);
    l_formatted_laycan_to  varchar2(100);
 
    l_email_intro CLOB;
 
    cursor c_data is
      WITH max_version AS (
        SELECT 
            "requestID",
            "VERSION",
            "CREATED_BY",
            "ID",
            "remarks",
            "laycanTo",
            "laycanFrom",
            "charterer",
            "requestNum"
        FROM (
            SELECT 
                "requestID",
                "VERSION",
                "CREATED_BY",
                "ID",
                "remarks",
                "laycanTo",
                "laycanFrom",
                "charterer",
                "requestNum",
                "DELETED_DATE",
                ROW_NUMBER() OVER (PARTITION BY "requestID" ORDER BY "VERSION" DESC) AS rn
            FROM 
                xxcstportal_pub.cargo_request_s
            WHERE "requestID" NOT IN (
                SELECT DISTINCT "requestID"
                FROM xxcstportal_pub.cargo_request_statuses_s
                WHERE "statusId" IN ('f_req_canceled','f_req_deleted')
            )
        ) t 
        WHERE rn = 1
      ),
      port_counts AS (
        SELECT 
            cr."ID",
            crid."gradeSeq",
            COUNT(DISTINCT CASE WHEN cri_l."function" = 'L' THEN cri_l."ID" END) as l_count,
            COUNT(DISTINCT CASE WHEN cri_d."function" = 'D' THEN cri_d."ID" END) as d_count
        FROM xxcstportal_pub.cargo_request_packet_s crp
        JOIN max_version cr ON cr."requestID" = crp."requestID"
        JOIN xxcstportal_pub.cargo_request_itinerary_s crid ON cr."ID" = crid."requestID" AND crid."function" = 'D'
        LEFT JOIN xxcstportal_pub.cargo_request_itinerary_s cri_l ON cr."ID" = cri_l."requestID" AND cri_l."grade" = crid."grade" AND cri_l."gradeSeq" = crid."gradeSeq" AND cri_l."function" = 'L'
        LEFT JOIN xxcstportal_pub.cargo_request_itinerary_s cri_d ON cr."ID" = cri_d."requestID" AND cri_d."grade" = crid."grade" AND cri_d."gradeSeq" = crid."gradeSeq" AND cri_d."function" = 'D'
        WHERE "packetID" = p_packet_id
        GROUP BY cr."ID", crid."gradeSeq"
      )
      select
        "requestNum",
        "typeOfSaleD" as "typeOfSale",
        "charterer",
        "grade",
        CASE WHEN pc.l_count = 2 AND pc.d_count = 1 THEN "quantityL" ELSE "quantity" END as "quantity",
        "details",
        "remarks",
        "origin",
        "loading_port_info",
        "loading_rate_info",
        "discharge_port_info",
        "discharge_rate_info",
        "laycanFrom",
        "laycanTo",
        "EMAIL"
      from (
        select 
        cr."requestNum" as "requestNum",
        crid."typeOfSale" as "typeOfSaleD",
        cr."charterer" as "charterer",
        crid."grade" as "grade",
        crid."quantity" as "quantity",
        cril."quantity" as "quantityL", 
        cril."details" as "details",
        cr."remarks" as "remarks",
        crid."origin" as "origin",
        cril."portNo" as "portNoL",
        cril."terminal" as "terminalL",
        cril."termsCode" as "termsCodeL",
        cril."loadDischargeRate" as "loadingRate",
        crid."portNo" as "portNoD",
        crid."terminal" as "terminalD",
        crid."termsCode" as "termsCodeD", 
        crid."loadDischargeRate" as "dischargeRate",
        cr."laycanFrom" as "laycanFrom",
        cr."laycanTo" "laycanTo",
        u."EMAIL" as "EMAIL",
        cr."ID" as request_id,
        crid."gradeSeq" as grade_seq,
        (select "name" from imos_ports_s where "portNo" = cril."portNo") || ' ' || cril."terminal" as "loading_port_info",
        cril."loadDischargeRate" || ' ' || 
        (select "name" from imos_laytime_terms_s where "code" = cril."termsCode") as "loading_rate_info",
        (select "name" from imos_ports_s where "portNo"= crid."portNo") || ' ' || crid."terminal" as "discharge_port_info",
        crid."loadDischargeRate" || ' ' || 
        (select "name" from imos_laytime_terms_s where "code" = crid."termsCode") as "discharge_rate_info"
        FROM xxcstportal_pub.cargo_request_packet_s crp
        left join max_version cr on cr."requestID" = crp."requestID"
        left join xxcstportal_pub.cargo_request_itinerary_s crid ON cr."ID" = crid."requestID" and crid."function"= 'D'
        left join xxcstportal_pub.cargo_request_itinerary_s cril ON cr."ID" = cril."requestID" and cril."grade" = crid."grade" and cril."gradeSeq" = crid."gradeSeq" and cril."function" = 'L'
        left join xxcstportal_pub."USER_" u on cr."CREATED_BY" = u."USERNAME"
        where "packetID" = p_packet_id
      ) data
      LEFT JOIN port_counts pc ON data.request_id = pc."ID" AND data.grade_seq = pc."gradeSeq"
      order by data."requestNum", data.grade_seq;
 
  begin
    IF p_action = 'UPDATE' THEN
      l_subject_prefix := 'UPD: ';
      l_email_intro := 
        '<html>'||
          '<head><meta charset="utf-8"><title>Information about contract changes from Customer Portal</title></head>'||
          '<body>'||
            '<p>Dear colleagues!</p>'||
            '<p>'||' '||'</p>'||
            '<p>'||'Changes to the contract have been sent to IMOS:'||'</p>';
    ELSE 
      l_subject_prefix := '';
      l_email_intro := 
        '<html>'||
          '<head><meta charset="utf-8"><title>Information about new contract from Customer Portal</title></head>'||
          '<body>'||
            '<p>Dear colleagues!</p>'||
            '<p>'||' '||'</p>'||
            '<p>'||'A new contract has been created in IMOS:'||'</p>';
    END IF;
 
    BEGIN
        SELECT cr."laycanFrom", cr."laycanTo"
        INTO l_earliest_laycan_from, l_corresponding_laycan_to
        FROM xxcstportal_pub.cargo_request_packet_s crp
        JOIN xxcstportal_pub.new_order_header_s cr ON cr."requestID" = crp."requestID"
        WHERE 
            crp."packetID" = p_packet_id
            AND cr."laycanFrom" = (
                SELECT MIN(cr2."laycanFrom")
                FROM xxcstportal_pub.cargo_request_packet_s crp2
                JOIN xxcstportal_pub.new_order_header_s cr2 ON cr2."requestID" = crp2."requestID"
                WHERE crp2."packetID" = p_packet_id
            )
        FETCH FIRST 1 ROW ONLY;
 
        l_formatted_laycan_from := TO_CHAR(TO_DATE(l_earliest_laycan_from, 'DD-MON-YYYY'), 'DD-MON');
        l_formatted_laycan_to := TO_CHAR(TO_DATE(l_corresponding_laycan_to, 'DD-MON-YYYY'), 'DD-MON');
 
        SELECT MIN(ip."name")  
        INTO l_first_port_no
        FROM xxcstportal_pub.cargo_request_packet_s crp
        JOIN xxcstportal_pub.new_order_header_s cr ON cr."requestID" = crp."requestID"
        JOIN xxcstportal_pub.cargo_request_itinerary_s cri ON cr."ID" = cri."requestID"
        JOIN imos_ports_s ip ON cri."portNo" = ip."portNo"  
        WHERE crp."packetID" = p_packet_id  
        AND cri."function" = 'L';
 
        SELECT t."country"
        INTO l_discharge_country
        FROM (
            SELECT cri."portNo", ip."country", ROW_NUMBER() OVER (ORDER BY cri."seq" DESC) as rn
            FROM xxcstportal_pub.cargo_request_packet_s crp
            JOIN xxcstportal_pub.new_order_header_s cr ON cr."requestID" = crp."requestID"
            JOIN xxcstportal_pub.cargo_request_itinerary_s cri ON cr."ID" = cri."requestID"
            JOIN imos_ports_s ip ON cri."portNo" = ip."portNo"
            WHERE crp."packetID" = p_packet_id
            AND cri."function" = 'D'
        ) t
        WHERE rn = 1;
 
      SELECT SUM(cr."cpQty"), LISTAGG(TRIM(cr."requestNum"), ', ') WITHIN GROUP (ORDER BY cr."requestNum")
      INTO l_total_quantity
      FROM xxcstportal_pub.cargo_request_packet_s crp
      JOIN xxcstportal_pub.new_order_header_s cr ON cr."requestID" = crp."requestID"
      WHERE crp."packetID" = p_packet_id;

      SELECT LISTAGG(DISTINCT TRIM(cr."requestNum"), ', ') WITHIN GROUP (ORDER BY TRIM(cr."requestNum"))
      INTO l_request_nums
      FROM xxcstportal_pub.cargo_request_packet_s crp
      JOIN xxcstportal_pub.new_order_header_s cr ON cr."requestID" = crp."requestID"
      WHERE crp."packetID" = p_packet_id;
 
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            l_earliest_laycan_from := '';
            l_corresponding_laycan_to := '';
            l_formatted_laycan_from := '';
            l_formatted_laycan_to := '';
            l_first_port_no := '';
            l_discharge_country := '';
            \
            l_total_quantity := '0';
            l_request_nums := '';
    END;
 
    l_subject := l_request_nums || 'TBN' || l_subject_prefix || 'Laycan: ' || l_formatted_laycan_from || '-' || l_formatted_laycan_to || ' ' || 
                 l_first_port_no || ' - ' || l_discharge_country || ' Qty: ' || l_total_quantity;
 
    IF LENGTH(l_subject) > 86 THEN
        l_short_subject := SUBSTR(l_subject, 1, 86) || '..';
    ELSE
        l_short_subject := l_subject;
    END IF;
 
    select max(USER_NAME), max(SETTING_CODE) 
    into l_from, l_tls_code
    from xxapps.xxapps_sendmail_tls_settings_t;
 
    l_table_header :=
      '<div class="table-wrap">'||
      '<table><tbody>'||
        '<tr>'||
          '<th '||l_style||'>Customer Portal ID</th>'||
          '<th '||l_style||'>B2B/RSO</th>'||
          '<th '||l_style||'>Counterparty</th>'||
          '<th '||l_style||'>Product</th>'||
          '<th '||l_style||'>Volume</th>'||
          '<th '||l_style||'>Details</th>'||
          '<th '||l_style||'>Remarks</th>'||
          '<th '||l_style||'>Origin</th>'||
          '<th '||l_style||'>POL</th>'||
          '<th '||l_style||'>Loading rate</th>'||
          '<th '||l_style||'>Laycan start date</th>'||
          '<th '||l_style||'>Laycan end date</th>'||
          '<th '||l_style||'>POD</th>'||
          '<th '||l_style||'>Discharge rate</th>'||
        '</tr>';
 
    for r in c_data loop 
        if l_trader_email is null then
            l_trader_email := r."EMAIL";
        end if;
 
        l_table := l_table || 
          '<tr>'||
            '<td '||l_style||'>'||r."requestNum"||'</td>'||
            '<td '||l_style||'>'||r."typeOfSale"||'</td>'||
            '<td '||l_style||'>'||r."charterer"||'</td>'||
            '<td '||l_style||'>'||r."grade"||'</td>'||
            '<td '||l_style||'>'||r."quantity"||'</td>'||
            '<td '||l_style||'>'||r."details"||'</td>'||
            '<td '||l_style||'>'||r."remarks"||'</td>'||
            '<td '||l_style||'>'||r."origin"||'</td>'||
            '<td '||l_style||'>'||r."loading_port_info"||'</td>'||
            '<td '||l_style||'>'||r."loading_rate_info"||'</td>'||
            '<td '||l_style||'>'||r."laycanFrom"||'</td>'||
            '<td '||l_style||'>'||r."laycanTo"||'</td>'||
            '<td '||l_style||'>'||r."discharge_port_info"||'</td>'||
            '<td '||l_style||'>'||r."discharge_rate_info"||'</td>'||
          '</tr>';
      end loop;
 
    l_mail_body := to_clob(l_email_intro||l_table_header||l_table||l_end);
 
    l_dummy := xxapps.xxapps_sendmail_pkg.send_mail(
        p_to              => l_trader_email,
        p_subject         => l_short_subject,
        p_from_email_name => 'IMOS Notifications',
        p_from            => l_from,
        p_convert_type    => 'html',
        p_tls_code        => l_tls_code,
        p_email_body      => l_mail_body
    );
  end;
  -- ------------------------------------------
end;
/