Versions Compared

Old Version 91

changes.mady.by.user Hunt, Christopher

Saved on

compared with

New Version 92

changes.mady.by.user Hunt, Christopher

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

Table of Contents

System Profile

System Full NameCanadian Port State Control System
Product Owner(s)Bill Henderson
Business OwnerNaim Nazha

Technology Assessment

Database Platform and VersionOracle 18c
Development Language and FrameworkClassic ASP
Javascript
HTML
Visual Basic 6 (COM)
Fytek PDF Writer DLL
Operating System and VersionWindows Server 2016

Environment Access Information

DEV

User (admin): greenj

Pass: See RDIMS #13232403

...

ACC

User (admin): greenj

Pass: See RDIMS #13232403

...

PROD

User (admin): greenj

Pass: See RDIMS #13232403

...

Source Code:
            Dev

System

Overview

<…either point to some document or put some info / diagram here>

Good To Know

Installation of new THETIS certificate

...

  1. Ask Web Group to create a valid CSR that can be sent to EMSA. This CSR is used by EMSA to generate a new certificate to allow communication with THETIS.
  2. Send the CSR to EMSA along with any provided forms filled out in conjunction with the CPSCS business owner.
  3. EMSA will create a signed digital certificate (*.pem) and send that back to the CPSCS business owners.  
  4. Send the PEM file to the Web Group and ask them to use the PEM file to generate the PFX file. 
  5. Using the PFX file, the certificate can be installed on the appropriate servers.  Be sure to refer to "CPSCS - <NO IM> - CPSCS-THETIS web service cannot access THETIS web service" below for further instructions when installing the certificate on the server.  Long story short: explicit permission must be given to the app pool requesting to use the certificate for communication with THETIS.
  6. Ask the Web Group to install the certificate on ACC servers (wwwappstest).  
  7. Ask the Web Group to send us the PFX file and password so that we can install the certificate on the appropriate dev server (hosting the CPSCS-THETIS web service).
  8. Note: latest certificates for CPSCS-THETIS web service can be found in the following directory and subdirectories: \\tc4s0b\groups\AFCCB\MS_APPS\CPSCS\THETIS\Certificates.

Test Vessel

When performing testing, particularly in production, care must be taken to avoid corrupting or polluting the CPSCS database.  Testing is generally performed using the Douglas Bay (or Douglas Bay - Test) vessel - IMO number 7411636. This vessel and its inspections are ignored in reporting - and should be ignored if you are creating new reports for the CPSCS stakeholders.

If you need an account for user testing, you should log into the CPSCS web application as an admin user (see RDIMS #13232403) to locate a test user account.  When in doubt, contact the client admin to identify a good account to use for testing.

Log Files

Log files for CPSCS and its associated web services and can be found in the following locations:

...

To test the connection to THETIS without the web site (if the test above failed), use SOAP UI to make a call to the THETIS production web service.  To avoid any possible issues or complications, always make a call to GetShipParticularsJSON with a valid IMO number. This call will connect to the THETIS web service (via the CPSCS-THETIS web service) and attempt to download updated vessel information.

Testing: Inspection Types

When creating test inspections, it is best to stick with the following inspection types:

...

All other inspection types may result in testing issues and are not compatible with THETIS and APCIS (as of October 2019).

Testing: Operational Controls

Please note that operational controls can not be selected for Initial inspections. In addition, changing the inspection type to Initial will clear all currently selected operational controls associated with the inspection you are working with on the CPSCS web site.

How-To and Fixes


CPSCS - IM295914 - PAC - NADKARC - urgent - CPSCS Inspection creation (submission) error

Problem: Client got an error trying to submit a saved inspection:

(Inspection:InspectionTombstone Method)​
strSQL = 'Inspection:InspectionTombstone Method (1): VID=759742, CurrVID = 759741, SaveID = 759741'​
Invalid procedure call or argument​ 

...

Saved inspection now shows up and user will try submitting again.

CPSCS - <NO IM> - CPSCS-THETIS web service cannot access THETIS web service

Problem:

On calling the thetis webservice this error is returned: [GetShipParticulars Error] Unexpected error has occured: '(5): The request was aborted: Could not create SSL/TLS secure channel.

...

  • Start - Run "mmc".
  • Add snap-ins for Certificates: My User and Computer LocalComputer Account.
  • For both My User and Local Computer Account:
    • Right-click Personal - All Tasks - Import.
    • Browse and select "cpscs-ws.tc.gc.ca.pfx".
    • Password can be found here: \\tc4s0b\groups\AFCCB\MS_APPS\CPSCS\THETIS\Certificates\March 2018\Certificate Installation Instruction.txtImage Added
    • Web team will provide you the password 
    • Next right-click Intermediate Certificate Authorities - All Tasks - Import.
    • Browse and select emsa-prod.ca-bundle.cer and install to Intermediate Certificates.
    • Look under the Intermediate Certificates and double-click on the MarApps Production Intermediate - SHA256 certificate.
    • Open the Certification Path tab and double-click on the EMSA Root CA certificate.
    • Install this certificate to the Trusted Root Certification Authorities.> All Tasks> Manage Private Keys
    • Image Added
    • Add 'iis apppool\cpscs-ws'
    • That should be it. Test the certificate by running the web service

To grant access:

  • Use elevated powershell to find the Guid key for this certificate in
    C:\ProgramData\Microsoft\Crypto\RSA\MachineKeys\
    • $Cert = Get-Item Cert:\LocalMachine\My\<Thumbprint of Certificate>
    • $Cert.PrivateKey.CspKeyContainerInfo.UniqueKeyContainerName

      • <Thumbprint of Certificate> can be found from MMC: Certificates(Local Computer)->Personal->Certificates->cpscs-ws.tc.gc.ca

      • In Certificate property window, it’s at the bottom of the Details tab

      • If a key is not returned, the certificate may not have been properly installed and need to be reinstalled.

    • Full path to key is C:\ProgramData\Microsoft\Crypto\RSA\MachineKeys\<key returned>
    • To to grant access, run following with key returned:

      ICACLS <Full path to key> "IIS AppPool\CPSCS-Thetis:R"

Info from DOS:


Asuquo Eniang
commented Nov 26, 2019
 
This confirms that IIS AppPool\CPSCS-Thetis account access to the certificate key file is the issue; Here's a before and after screen shots of permissions on this folder, on dev server:
BeforeAndAfter.png

...

Asuquo Eniang
commented Nov 23, 2019
 
(edited)
Researching the error "The request was aborted: Could not create SSL/TLS secure channel." further, this Stack Overflow page suggests to use ICAL to register certificate with ASP.
  • Install Certificate in Dev LocalMachine
    1. Instructions located here:
      \\tc4s0b\groups\AFCCB\MS_APPS\CPSCS\THETIS\Certificates\March 2018
    2. If this certificate is not installed, you'll get: Unexpected error has occured: '(91): Object reference not set to an instance of an object. when service is called.
    3. It is possible for a certificate to exist in MMC console but missing a file entry in C:\ProgramData\Microsoft\Crypto\RSA\MachineKeys
  • Find certificate path using tool FindPrivateKey.exe
    1. Copy the tool FindPrivateKey.exe from
      \\tc4s0b\groups\AFCCB\MS_APPS\CPSCS\THETIS\Certificates\March 2018 to a location on Dev server
    2. open command prompt as administrator, navigate to location of tool.
    3. Run command: FindPrivateKey.exe My LocalMachine -n "MarApps Production Intermediate - SHA256" -a
    4. Copy and save the result: C:\ProgramData\Microsoft\Crypto\RSA\MachineKeys\ff85bd97ae6a76c3dcbc4925e3eb6ca3_518d76a8-4ded-49de-b4a9-d981cc355562
  • Use ICALS to grant IIS AppPool access to the .cer file, with path:
    1. ICACLS C:\ProgramData\Microsoft\Crypto\RSA\MachineKeys\ff85bd97ae6a76c3dcbc4925e3eb6ca3_518d76a8-4ded-49de-b4a9-d981cc355562 /grant "IIS AppPool\CPSCS-Thetis:R"
    2. Confirm result: Successfully processed 1 files; Failed processing 0 files

...

Asuquo Eniang
commented Nov 25, 2019
 
(edited)
After granting access following steps below, service was tested and passed on three servers 336, 337 and 338:
Log can be found here: \\ncrws536\wwwfiles\cpscs-ws\logs
Asuquo Eniang
commented Nov 25, 2019
 
The solution below failed on first try on Prod server. 
FindPrivateKey.exe My LocalMachine -n "MarApps Production Intermediate - SHA256" -a
FindPrivateKey failed for the following reason:
No certificates with key 'MarApps Production Intermediate - SHA256' found in the store.
We need to verify that the certificate is properly installed.
Look under Certificates(Local Computer)->Personal->Certificates
Image
Next we use elevated powershell to find the Guid key for this certificate in
C:\ProgramData\Microsoft\Crypto\RSA\MachineKeys\
Run:


$Cert = Get-Item Cert:\LocalMachine\My\<Thumbprint of Certificate>


$Cert.PrivateKey.CspKeyContainerInfo.UniqueKeyContainerName



Where <Thumbprint of Certificate> can be found from MMC: Certificates(Local Computer)->Personal->Certificates->cpscs-ws.tc.gc.ca

In Certificate property window, it’s at the bottom of the Details tab. See attached image.


If a key is not returned, the certificate may not have been properly installed.

Right click and delete it from the console. Install the certificate again. Instructions can be found here:

\\tc4s0b\groups\AFCCB\MS_APPS\CPSCS\THETIS\Certificates\March 2018



Once the certificate is installed, this command should be able to return a full path to the key:

FindPrivateKey.exe My LocalMachine -n "MarApps Production Intermediate - SHA256" -a


To to grant access, run following with key returned:

ICACLS <Full path to key> "IIS AppPool\CPSCS-Thetis:R"





Asuquo Eniang
commented Nov 23, 2019
 
@Justin Green  I found a way to grant IIS AppPool access to the thetis certificate (MarApps Production...)
I tested on dev and it worked. You can find more details in the child tasks of bug 4195.
Web team would have to follow these steps to correct the issue. Step 1 can be skipped if certificate is already installed.
  • Install Certificate in Prod LocalMachine
    1. Instructions located here:
      \\tc4s0b\groups\AFCCB\MS_APPS\CPSCS\THETIS\Certificates\March 2018
    2. If this certificate is not installed, you'll get: Unexpected error has occured: '(91): Object reference not set to an instance of an object. when service is called.
  • Find certificate path using tool FindPrivateKey.exe
    1. Copy the tool FindPrivateKey.exe from
      \\tc4s0b\groups\AFCCB\MS_APPS\CPSCS\THETIS\Certificates\March 2018 to a location on server
    2. open command prompt as administrator, navigate to location of tool.
    3. Run command: FindPrivateKey.exe My LocalMachine -n "MarApps Production Intermediate - SHA256" -a
    4. Copy and save the result: C:\ProgramData\Microsoft\Crypto\RSA\MachineKeys\ff85bd97ae6a76c3dcbc4925e3eb6ca3_518d76a8-4ded-49de-b4a9-d981cc355562
  • Use ICALS to grant IIS AppPool access to the .cer file, with path:
    1. ICACLS C:\ProgramData\Microsoft\Crypto\RSA\MachineKeys\ff85bd97ae6a76c3dcbc4925e3eb6ca3_518d76a8-4ded-49de-b4a9-d981cc355562 /grant "IIS AppPool\CPSCS-Thetis":R
    2. Confirm result: Successfully processed 1 files; Failed processing 0 files

Error: Call Sign must confirm to Basic and ITU data validation standards

Problem:

On updating Vessel Tombstone page user getting error, Call Sign must confirm to Basic and ITU data validation standards, for the call sign J8QY1. 

...

See the attached email from APCIS support. They have mentioned :
According to the DVR for Ship Callsigns, callsigns shall be formatted according to ITU Radio Regulations Section III-Formation of call signs, in one of the following 11 formats:

 ImageImage Modified

where a=any alpha character A-Z incl., n=any digit 0-9 incl., N=only digits 2-9
Here the callsign J8QY1 is formed according to the 5-character format “aNaaN” but according to the rules the last digit should be from 2 to 9 (other than the digits 0 or 1).  The last digit of the callsign J8QY1 is 1, so this callsign does not comply to DVR. This is why this callsign is not accepted by the APCIS system.

If ship call sign is unavailable or it does not comply the DVR, it is recommended to enter “n/a” into the field “Callsign” of the inspection report.

THETIS Export Error: Invalid Procedure Call or argument

Problem:
While user export the inspection to THETIS its throwing error Invalid Procedure Call or argument. Issue occurs only if no deficiency has been reported in the inspection. 

...

Asuquo Eniang
commented Sep 12, 2019
 
Debugging full web site in visual studio, with breakpoint on line 474 of vel.asp file does not return error: Inspection is successfully posted:
WebDebug_Passes.png

CPSCS-IM287825-NCR - Tower C 10-D125 -JONESSI- CPSCS glitch - ORIENT SKY (IMO 9792022)

Problem:

  • 0 of 1 inspections successfully sent to THETIS
  • Inspection ID 731326: ERROR: THETIS Export Error T.IE.5: [PostInspectionResultsJson Error] Deficiencies: ISM deficiency 15150 - ISM but no deficiency marked as ISM Related (IMO: 9792022)

...

Asuquo Eniang
commented Sep 17, 2019
 
(edited)
The error is:
    • 0 of 1 inspections successfully sent to THETIS
    • Inspection ID 731326: ERROR: THETIS Export Error T.IE.5: [PostInspectionResultsJson Error] Deficiencies: ISM deficiency 15150 - ISM but no deficiency marked as ISM Related (IMO: 9792022)
According to Simon:
9792022 ORIENT SKY: This error is the reason the report was submitted.  It’s claiming that none of the deficiencies were marked as ISM related, which is a prerequisite to having an ISM deficiency.  Deficiency 14615 has in fact been marked as ISM related.

To find this deficiency, we run the following query:
select d.DEFICIENCY_ID,
    d.DEF_CODE_ID,
    d.INSPECTION_ID,
    d.ISM_RELATED_IND,
    dc.CODE,
    dc.THETIS_CD,
    dc.APCIS_CD,
    d.NOTES
From Deficiency d Left Join S_DEFICIENCY_CODE dc on d.def_code_id = dc.def_code_id

Where d.INSPECTION_ID = 731326 and dc.code = 14615
It returns:
Image
We can see that the THETIS_CD column is null. To check with Bill on what the correct code should be.

CIC Questionnaire Glitch: when NO or N/A is selected as answer its not saving 

Problem:
While adding new inspection if user select No or N/A as the answer of CIC Questionnaire, the info doesn't save.

...

  1. Login to the CPSCS Production as Admin user.
  2. From the Menu Select Admin
  3. Click on the Questionnaire Button from the left panel.
  4. Click on the Action button for CIC on Emergency Systems and Procedures which populate the cic questions.
    1. Image
  5. Here, the issue is with the first question. Click on modify Action Button.
  6. For the "Possible values if you selected radio buttons " there is a space between the answers. i.e. YES; NO; N/A
  7. Remove the spaces, Click Update and checked in the inspection. Now its showing the option N/A is selected.
    1. Image
  8. Because of the space the correct data was not retrieved and selection was not populated.
  9. Send email to the user about resolution.

APCIS Export Error A.IE.2: : Conversion failed when converting date and/or time from character string. 

Problem:
The inspection could not be exported to APCIS and giving  APCIS Export Error A.IE.2: Conversion failed when converting date and/or time from character string. (IMO: 9433315)<br />WARNING: Certificate code 524 is not a valid APCIS certificate.

...

The NULL and n/a APCIS_Code can be ignored as those Certificates are related to THETIS. 

THETIS Export Error: Deficiencies: value  of Default Description is not valid 

Problem:
The inspection could not be exported to THETIS because Deficiencies: value  of Default Description is not valid (IMO:XXXXXXX)

...

Notes from parent bug shows that this item was action-ed by Justin. Missing THETIS codes were added. Not sure if client retried submitting after that.
To be sure, ran this query:
--get deficiencies
select d.deficiency_id,
    d.def_code_id, d.inspection_id, d.notes
from deficiency d where inspection_id=725110
result:
Image
Next, we find deficiency
--get default descriptions associated with the deficiency_id's
select dd.DEFICIENCY_ID, dd.DEF_DESC_ID, d.ENGLISH_VALUE, d.THETIS_CD
from deficiency_description dd, s_deficiency_description d
where dd.DEF_DESC_ID = d.DEF_DESC_ID and
(dd.deficiency_id = 706510
or dd.deficiency_id = 706510
or dd.deficiency_id = 706508
or dd.deficiency_id = 706509
or dd.deficiency_id = 706511
or dd.deficiency_id = 706512)
result:
Image
We find no missing thetis codes. Same with the other inspections. To ask user to try exporting again.


CPSCS :APCIS webservice Ro Related Detainable Defects Bug

Problem:
On exporting detention inspection to APCIS, its sending RO related deficiencies even though inspector has not selected RO related deficiency. 

...

vessel_id = 743759 obtained in initial assessment:
Run this query to confirm typo in vessel name reported in ticket:
select i.inspection_id, i.vessel_id, i.imo_no, i.date_of_inspection, v.vessel_name
from inspection i, vessel v
where i.imo_no=9272199 and i.date_of_inspection = to_date('2018-11-05', 'YYYY-MM-DD')
    and i.vessel_id = v.vessel_id
Result: vessel_id = 743759
Done
Issue can (and should) be fixed by system admin according to scrum master. Email sent to Simon to fix issue. Awaiting feedback.
Before update, check vessel names:
Run
select vessel_id, vessel_name, vessel_name_uc
from vessel where vessel_id = 743759
Result:
Image
Run this query to update vessel name:
update vessel
set
    vessel_name='CHAMPION CONTEST',
    vessel_name_uc='CHAMPION CONTEST';
where vessel_id=743759
commit;
Result: 1 row updated. Commit complete.
Run select to confirm values after update:
select vessel_id, vessel_name, vessel_name_uc
from vessel where vessel_id = 743759
Result:
Image
Names successfully updates

APCIS Export Error: APCIS asking for certificate which is not on the inspection report

Problem:
The inspection could not be exported to APCIS because APCIS asking for certificate 530 when there is no Code 530 certificate in the inspection report. 

...

  1. Login as CPSCS admin and search for IMO 9794680. From the URL get the inspection id = 729870
  2. SELECT
        sct.title_id,
        sct.code,
        sct.english_value
    FROM
        s_certificate_title   sct,
        certificates          c
    WHERE
        c.title_id = sct.title_id
        AND c.inspection_id = 729870;
  3. The result contains certificate 530 Civil Liability for Oil Pollution Damage. Title_ID=600126
  4. select * from certificates where title_id = 600126 and inspection_id = 729870; 
  5. Image
  6. Check the date of Inspection.
    1.  select DATE_OF_INSPECTION from inspection where inspection_id = 729870;
    2. Image
  7. So here date of issue is after date of first visit. Lets search for the certificate where issue date is higher than date of inspection.
    1. SELECT
          c.certificate_id,
          c.title_id,
          c.date_issued,
          i.date_of_inspection,
          sct.title_id,
          sct.code,
          sct.english_value
      FROM
          certificates          c,
          inspection            i,
          s_certificate_title   sct
      WHERE
          c.inspection_id = i.inspection_id
          AND c.date_issued > i.date_of_inspection
          AND c.title_id = sct.title_id
          AND i.inspection_id = 729870;
    2. Image
  8. These both certificates are not showing up on the inspection report. Lets reset the date and inform the client to correct the date of certificate issue. 
    1.  update certificates set date_issued = to_date('2019-01-01','YYYY-MM-DD') where certificate_id in(904712,904713);
       commit;
  9. Now verify both the certificates are shows up in CPSCS (https://wwwapps2.tc.gc.ca/saf-sec-sur/4/cpscs-scepc). 
    1. Image

Multiple issues, one after the other was fixed:

Problem:

  1. 0 of 1 inspections successfully sent to APCIS<br /><hr width="100%">Inspection ID 729507: ERROR: APCIS Export Error A.IE.2: Inspection Actions shall not be empty (IMO: 9283538)<br />WARNING: Certificate code 526 is not a valid APCIS certificate. (IMO: 9283538)
  2. 0 of 1 inspections successfully sent to APCIS<br /><hr width="100%">Inspection ID 729507: ERROR: APCIS Export Error A.IE.2: Invalid Deficiency Convention Code '5001' shall not according code deficiency '1209' (IMO: 9283538)
  3. 0 of 1 inspections successfully sent to APCIS<br /><hr width="100%">Inspection ID 729507: ERROR: APCIS Export Error A.IE.2: There are more than one certificate with code '510' (IMO: 9283538)

...

Run query:
select convention_id, def_code_id, deficiency_id from deficiency where inspection_id=729507;
Result:
Image
select def_code_id, apcis_cd from s_convention where convention_id=10726;
Result:
Image
select def_code_id, code, apcis_cd from s_deficiency_code where def_code_id=22;
Result:
Image
From spreadsheet: \\tc4s0b\groups\AFCCB\MS_APPS\CPSCS\CODE EXTRACTS\APCIS\January 2017\Deficiencies.xls
Deficiency code 01209 is mapped to Convention 1020 in column D

Update APCIS code in DB:
update s_convention set apcis_cd=1050 where convention_id=10726;
Result: 1 row updated.
Sent client instruction to try the export again. It came back with a different error. This is the third different error raised by this vessel export. To be investigated in a different task.

Get all the deficiencies and their associated default descriptions

Solution:

The has been generated using the following query.

...

Exported the result in excel spreadsheet and sent to the client. 

CPSCS: Duplicate MMSI Numbers.

Problem:
Client want to change the MMSI number to 371518000 for vessel SALINAS, IMO 9805324. But MMSI 371518000 is already using by some other vessel.

...

Email has been sent to the client, see the attachment. 
Closing the SMGS ticket.



CPSCS: Export Error - THETIS certificates [DOS 2267]

Problem:
Client gets and invalid certificate error when exporting to THETIS:

...

In returned data THETIS_UPLOAD_DTE = 09-JUL-19 which means this item was successfully uploaded. Contact client and ask if this is still an issue. May have been resolved by client.

THETIS export issue: THETIS Export Error T.IE.5: Deficiencies: The Convention SOLAS ch. II-1 Parts C, D, E, F is not valid for deficiency 04111- Damage control plan 

Problem:
Client encounters an issue when attempting to export an inspection to THETIS.   Error code T.IE.5 (convention issue).

...

  1. Let's get the inspection ID from the CSPCS Web site and Login as Admin.
  2. Go to Vetting - Export Queue. Look for IMO 9397858 under THETIS heading and click on the inspection..
  3. Grab the inspection id from the URL. Here IID=729867
  4. Check all the conventions associated with inspection ID 729867:
    1.    SELECT
          sdc.code DEFCODE,
          sdc.thetis_cd,
          sc.convention_id,
          sc.english_value Convention,
          sc.reference Reference,
          sc.thetis_cd Con_Thetis_Cd
      FROM
          deficiency d,
          s_deficiency_code sdc,
          s_convention sc
      WHERE
          d.inspection_id = 729867
          AND d.def_code_id = sdc.def_code_id 
          AND sc.convention_id=d.convention_id; 
    2. For the deficiency 04111  the convention_id  2117 and THETIS_CODE=01022, Convention Detail= SOLAS ch. II-1, Reference=SOLAS CHII-1 Reg 19. 
  5. Let's check for this code in \\tc4s0b\Groups\ MS_APPS\CPSCS\CODE EXTRACTS\THETIS\JULY 4 2019 (PROD)\Conventions.xls does the code refers to correct description.
  6. In the Convention table the description is The Convention SOLAS ch. II-1 Parts C, D, E, F  refers to THETIS CODE 01022 .
  7. Now lets check the deficiency exist for thetis_code 01022.
    1.  Select def_code_id,english_value from s_convention where thetis_cd = '01022' and def_code_id='04111';
    2. The result shows the there is no record found.
  8. The Inspection has SOLAS ch. II-1 selected. Let's check how many times this convention is used? Convention_Id=2117
    1. select * from deficiency  where convention_id=2117;
    2. Result shows this convention is used two times.
    3. SOLAS 06 Amend / II-1 / Reg. 19 (IID=724131, Date_Created=19-02-21)
      SOLAS 89/90 Amend / II-1 / Reg. 23-1 (IID=729867, Date_Created=19-08-01)
    4. 724131 was inspected in February.  Was this sent to THETIS? Yes.
  9. What conventions are valid for deficiency code 04111?
    1. Check \\tc4s0b\Groups\AFCCB\MS_APPS\CPSCS\THETIS\Paris MoU Deficiencies  01112016.pdf
    2. According to that document, only SOLAS ch. II-1 Parts A, A-1, B, B-1,B-2, B-3, B-4 is valid. This maps to THETIS_CODE 01021.
    3. def_code_id = 245 (select def_code_id from s_deficiency_code where code=04111;)
  10. Can we change the convention_id for 245?  How many times is 245 used?
    1. select * from deficiency where def_code_id = 245;

    2. Used a lot.  Convention reference (Paris MoU Deficiencies  01112016.pdf) is dated 2016.  When were the deficiencies (245) recorded?

    3.  select * from inspection i, deficiency d where d.def_code_id = 245 and d.inspection_id = i.inspection_id;

    4. Three inspections recorded after 2016: 729867,724812,724131

  11. To fix this issue, we need to change the mapping in s_convention:(But after consulting with Product owner)
    1. update s_convention set thetis_cd='01021', apcis_cd='01021' where convention_id = 2117;
      commit;
  12. Received confirmation from the client so lets apply the above query.
           

THETIS export issue: THETIS Export Error T.IE.5: Statutory Certificates: value  of Title is not valid

Problem:
Client encounters an issue when attempting to export an inspection to THETIS (or APCIS).  Error code T.IE.5 (certificate title issue).

...

  1. Go to https://portal.emsa.europa.eu/login.html
  2. Login using the username/password to access the EMSA THETIS portal. Credentials found in RDIMS 13232403: see the  Prod App Credentials for the CPSCS-THETIS WS.
  3. Using the menu towards the top of the screen, click Portals - THETIS - Homepage.
  4. Click "Documents" in the menu bar.
  5. Click the "Code Extracts" link.
  6. Click the "Background Tables DDMMYYYY.zip" link.  The associated date in the link changes monthly to reflect the current codes in use by THETIS.
  7. Click the Download link.
  8. Extract the download ZIP file to a new CODE EXTRACT folder:  \\tc4s0b\groups\afccb\ms_apps\cpscs\CODE EXTRACTS\{Month Day Year).
In the most recent code extract folder, open the statutoryCertificate Excel file. We want to see if there is a THETIS code for the 
Engine International Air Pollution Prevention certificate.  In the latest extract, found the certificate associated with THETIS code 526.

Update the production database with the missing THETIS code (run against CPSCSP.WORLD):
update s_certificate_title set thetis_cd = '526' where code = '526';
commit;

 Contact client - issue is resolved. (see attachments)

THETIS export issue: THETIS Export Error T.IE.2 (and possibly other issues related to THETIS and APCIS inspection exports):

Problem:
Client encounters an issue when attempting to export an inspection to THETIS (or APCIS).  Error code T.IE.2.

...


update s_system set THETIS_PROXY_URL_TXT = 'https://wwwapps.tc.gc.ca/saf-sec-sur/4/cpscs-ws/service.asmx';

--
Details:

To test export using the CPSCS VB6 DLL:

...

Solution:
CPSCS: How-to add a new PMOU manual revision

How-To: Get latest code extracts from EMSA THETIS portal

  1. Go to https://portal.emsa.europa.eu/login.html
  2. Login using the username/password to access the EMSA THETIS portal. Credentials found in RDIMS 13232403: see the  Prod App Credentials for the CPSCS-THETIS WS.
  3. Using the menu towards the top of the screen, click Portals - THETIS - Homepage.
  4. Click "Documents" in the menu bar.
  5. Click the "Code Extracts" link.
  6. Click the "Background Tables DDMMYYYY.zip" link.  The associated date in the link changes monthly to reflect the current codes in use by THETIS.
  7. Click the Download link.
  8. Extract the download ZIP file to a new CODE EXTRACT folder:  \\tc4s0b\groups\afccb\ms_apps\cpscs\CODE EXTRACTS\{Month Day Year).

How-To: Get latest code extracts from APCIS portal

  1. Contact product owner / business owner for CPSCS and ask for the latest code extract from APCIS.
  2. Product owner / business owner identified in RDIMS #14140748.
  3. Once extracts are received, create a new directory (Current Month + Year) in the following directory: \\tc4s0b\Groups\AFCCB\MS_APPS\CPSCS\CODE EXTRACTS\APCIS
  4. Place the code extracts in that folder.

To: Step through and debug the CPSCS VB6 dll

1. Open cpscsgroup.vbg.

2. In project1, open the cmdTest_Click.

...

*FYI If you update the dll or create a new one, you will need to register the DLL and recreate all COM objects. You can do this by deleting the old COM objects, and then creating new ones. Point to the new dll as the reference file when you are creating them. If you don't do this you will get an error similar to  'ASP 0177 : 800401f3' Server.CreateObject Failed

Oracle Database Account Locked

Problem:
Clients appear to be locked out of the web application due to the following error: ORA-28000: the account is locked.

...

Source of issue identified: CPSCS-INNAV .NET CORE API was attempting to access production database with the wrong password.   Repeated attempts to hit PROD locked the account.  Team Kraken is updating the CPSCS-INNAV .NET CORE API code and pipelines to avoid similar issues from occurring in the future. CPSCS production data was NOT affected by this issue. Sent details to client (see Attachments).

THETIS Export: The MMSI contains too many digits when exporting, inspection stuck in THETIS export queue.

Problem:
Client is locked out of the vessel tombstone and cannot complete the export due to issue above.

...

Worked. Length = 9.  Contacting clients and closing tickets.

Generate XML for inspection sent to APCIS

Problem:
Client needs to have the XML for an inspection exported to APCIS.

...

  • Back in the cmdTest_Click function, you'll want to make a call to the APCIS object's InspectionExport function: call apc.InspectionExport("727159","E") - make sure that all other function and sub-procedure calls are commented out. You do not want to accidentally run any further code. 
  • In the InspectionExport function, place a breakpoint on the following line: strResult = PostWebservice(strAPCISServer, strSoapAction, strXML) 
  • Go back to the cmdTest_Click function in Project1 and click the Start button.
  • When the form opens, be sure to select the PROD database radio button then click CPSCS Test button.
  • Step-through until you hit the breakpoint in the InspectionExport function.
  • In the Immediate window, type "?strParam" and hit Enter: this will display the full JSON call for the current inspection being exported.  Grab a copy of that JSON data and save it elsewhere.  
  • Stop the debugger: you will not actually send anything to APCIS at this time.
Step 4: Convert JSON data to XML
Take each of the JSON data dumps created in Steps 3A and 3B above and convert them to XML.   Tools to do this should be available online (e.g. http://www.utilities-online.info/xmltojson/#.XOwkIqJKiUk).
Send the XML data to the client to have APCIS review and let us know why errors are occurring and closing tickets.
Will record this information in Confluence.


THETIS Export: Place of last survey is not valid

Problem:
Client gets the following error when attempting to upload an inspection to THETIS: "Inspection ID XXXXX: ERROR: THETIS Export Error T.IE.5: [PostInspectionResultsJson Error] Statutory Certificates: value  of Place of last survey is not valid (IMO: YYYYYYY)", where XXXXXX is the inspection ID and YYYYYYY is the vesssel IMO number.

...

Inspection should now upload to THETIS. Contact client to have them confirm.

THETIS Export: Date of issue must be before date of first visit

Problem:
Client gets the following error when attempting to upload an inspection to THETIS: "Inspection ID XXXXX: ERROR: THETIS Export Error T.IE.5: [PostInspectionResultsJson Error] Statutory Certificates: Date of issue must be before date of first visit (IMO: YYYYYYY)", where XXXXXX is the inspection ID and YYYYYYY is the vesssel IMO number.

...

Sending e-mail to client (see attachments).
 

APCIS Export: Empty Deficiency Action List error

Problem:
Client gets the following error when attempting to export a CPSCS inspection to APCIS: "APCIS Export Error A.IE.1: Empty deficiency action list () for deficiency #YY (IMO: XXXXXXX)" (where XXXXXXX is the inspection vessel's IMO number and YY is the number of the deficiency as reported by the CPSCS).

...

Advise client that the code 17 should be removed for deficiency code 02101. (see attachments)

Delete a  CPSCS Inspection

Problem:
Delete an inspection from CPSCS given IMO number and inspection date.

...

Delete the inspections listed (in CPSCSP.WORLD database):

KOTA EKSPRES IMO 9644990
To find the proper inspection ID:

...

Inspection ID associated with St. John's is IID = 725886.  Delete this one.

Global Echo IMO 9633288
To find the proper inspection ID:
SELECT
    i.inspection_id,
    so.english_value
FROM
    inspection i,
    s_office so,
    tm008_port tm008
WHERE

...

We need to specify is_vetted = 0 because client wants the inspection showing in the vetting list to be deleted.  An inspection only appears in the Vetting list if it has NOT been vetted (is_vetted = 0);
IID = 725968
Chiara D’Amato, IMO 9423267

SELECT
    i.inspection_id,
    so.english_value
FROM
    inspection i,
    s_office so,
    tm008_port tm008
WHERE
    imo_no = 9423267
    AND TO_CHAR(date_of_inspection,'DD/MM/YYYY') = '24/04/2019'
    AND i.inspection_port_cd = tm008.port_cd
    AND tm008.office_id = so.office_id;

...

The delete script can be found at: \\tc4s0b\groups\AFCCB\MS_APPS\CPSCS\scripts; file name is CPSCS_delete_inspection.sql. Replace the "&&inspection_id" value with the appropriate values identified above. If the query works, comment out "rollback;" and and uncomment "commit;" before running a second time.
KOTA EKSPRES IMO 9644990
delete from inspection where inspection_id = 725886;
delete from deficiency_action_code where deficiency_id in (select deficiency_id from deficiency where inspection_id = 725886);
delete from deficiency_description where deficiency_id in (select deficiency_id from deficiency where inspection_id = 725886);
delete from certificates where inspection_id = 725886;
delete from deficiency where inspection_id = 725886;
delete from detention where inspection_id = 725886;
delete from inspection_area where inspection_id = 725886;
delete from operational_control where inspection_id = 725886;
delete from vessel_action_code where inspection_id = 725886;
Delete From Tm028_Xref_Inspection_Up Where Inspection_Id = 725886;
update vessel set last_inspection_id = null where last_inspection_id = 725886;
delete from inspection where inspection_id = 725886;
rollback;
-- commit;

Global Echo IMO 9633288
delete from inspection where inspection_id = 725968;
delete from deficiency_action_code where deficiency_id in (select deficiency_id from deficiency where inspection_id = 725968);
delete from deficiency_description where deficiency_id in (select deficiency_id from deficiency where inspection_id = 725968);
delete from certificates where inspection_id = 725968;
delete from deficiency where inspection_id = 725968;
delete from detention where inspection_id = 725968;
delete from inspection_area where inspection_id = 725968;
delete from operational_control where inspection_id = 725968;
delete from vessel_action_code where inspection_id = 725968;
Delete From Tm028_Xref_Inspection_Up Where Inspection_Id = 725968;
update vessel set last_inspection_id = null where last_inspection_id = 725968;
delete from inspection where inspection_id = 725968;
rollback;
-- commit;
Chiara D’Amato, IMO 9423267
delete from inspection where inspection_id = 726228;
delete from deficiency_action_code where deficiency_id in (select deficiency_id from deficiency where inspection_id = 726228);
delete from deficiency_description where deficiency_id in (select deficiency_id from deficiency where inspection_id = 726228);
delete from certificates where inspection_id = 726228;
delete from deficiency where inspection_id = 726228;
delete from detention where inspection_id = 726228;
delete from inspection_area where inspection_id = 726228;
delete from operational_control where inspection_id = 726228;
delete from vessel_action_code where inspection_id = 726228;
Delete From Tm028_Xref_Inspection_Up Where Inspection_Id = 726228;
update vessel set last_inspection_id = null where last_inspection_id = 726228;
delete from inspection where inspection_id = 726228;
rollback;
-- commit;
Inspections deleted. Contacting client (see attachments).

...

Here we require to delete Test Vessel Inspections associated with billuser account.

In the email, client has mentioned about three vessels.
“CPSCS Test Vessel 1” (IMO 1114561) 
TEST VESSEL (IMO 3456755)
SECOND TEST VESSEL V5.2.1 (IMO 2000016)
Lets first login to Prod and check the test vessels in the Vetting list. The result is as below:
IMO Number Vessel Name      Date of Inspection
1114561  CPSCS TEST VESSEL 1    2019 07 04
2000016  SECOND TEST VESSEL V5.2.1   2019 07 04
3456755  TEST VESSEL      2019 07 05
Now lets confirm that are these inspections are related to billuser account? Aldo find the inspection id.
    select inspection_id, date_of_inspection,created_by_user_id from inspection where imo_no='1114561';
select inspection_id, date_of_inspection,created_by_user_id from inspection where imo_no='2000016';
select inspection_id, date_of_inspection,created_by_user_id from inspection where imo_no='3456755';
IMO  Inspection_ID Inspection_Date Created_by_user_id
1114561 728787   19-07-04  236
2000016 728866   19-07-04  236
3456755 728806   19-07-05  236
select username from user_profile where user_id='236';
Here USERNAME = BILLUSER
Now lets delete the inspection for IMO=1114561 and Inspection_ID=728787
In the \\tc4s0b\groups\AFCCB\MS_APPS\CPSCS\SQL Queries directory, grab the following sql from CPSCS_delete_inspection.sql and replace "&&inspection_id" with "728787". 
We want to first make sure that running these commands won't have unexpected consequences in CPSCS.  Add a "rollback;" at the end of the delete so that you can verify that a reasonable numbers of rows were deleted.  
For example, only one inspection should be deleted.  You may also have as many as 10-20 deficiencies and certificates deleted. 
But if you see hundreds of rows updated, further investigation may be required - contact current CPSCS "SME" in DSD or contact CPSCS client to discuss with them.
delete from inspection where inspection_id = 728787;
delete from deficiency_action_code where deficiency_id in (select deficiency_id from deficiency where inspection_id = 728787);
delete from deficiency_description where deficiency_id in (select deficiency_id from deficiency where inspection_id = 728787);
delete from certificates where inspection_id = 728787;
delete from deficiency where inspection_id = 728787;
delete from detention where inspection_id = 728787;
delete from inspection_area where inspection_id = 728787;
delete from operational_control where inspection_id = 728787;
delete from vessel_action_code where inspection_id = 728787;
Delete From Tm028_Xref_Inspection_Up Where Inspection_Id = 728787;
update vessel set last_inspection_id = null where last_inspection_id = 728787;
delete from inspection where inspection_id = 728787;
rollback;
-- commit;
The result is as below:
Error starting at line : 2 in command -
delete from inspection where inspection_id = 728787
Error report -
ORA-02292: integrity constraint (CPSCSW.FK_DEFICIEN_REF_134_INSPECTI) violated - child record found
1 row deleted.
1 row deleted.
2 rows deleted.
1 row deleted.
1 row deleted.
1 row deleted.
1 row deleted.
1 row deleted.
1 row deleted.
1 row updated.
1 row deleted.(Here is where the inspection is deleted.)
Rollback complete.
This looks good. Let's run it for real by replacing "rollback;" with "commit;"
Now lets confirm the inspection is deleted from the Vetting list by login to production site.
We are good the inspection is deleted from the Vetting queue.
Now lets repeat same steps to delete the inspection for IMO=2000016 and Inspection_ID=728866
delete from inspection where inspection_id = 728866;
delete from deficiency_action_code where deficiency_id in (select deficiency_id from deficiency where inspection_id = 728866);
delete from deficiency_description where deficiency_id in (select deficiency_id from deficiency where inspection_id = 728866);
delete from certificates where inspection_id = 728866;
delete from deficiency where inspection_id = 728866;
delete from detention where inspection_id = 728866;
delete from inspection_area where inspection_id = 728866;
delete from operational_control where inspection_id = 728866;
delete from vessel_action_code where inspection_id = 728866;
Delete From Tm028_Xref_Inspection_Up Where Inspection_Id = 728866;
update vessel set last_inspection_id = null where last_inspection_id = 728866;
delete from inspection where inspection_id = 728866;
rollback;
Error starting at line : 1 in command -
delete from inspection where inspection_id = 728866
Error report -
ORA-02292: integrity constraint (CPSCSW.FK_DEFICIEN_REF_134_INSPECTI) violated - child record found
10 rows deleted.
2 rows deleted.
3 rows deleted.
2 rows deleted.
1 row deleted.
10 rows deleted.
8 rows deleted.
8 rows deleted.
2 rows deleted.
1 row updated.
1 row deleted.
Rollback complete.
Here multiple rows are deleted. 
Lets check the deficiency table. For inspection_id = 728866 there are two deficiency_id (709327,709328)
Here all the 10 records in the deficiency_action_code are created on 2019-08-07 and created by the user BILLUSER.
Same way checked all the tables where multiple records deleted and found that those records are  created on 2019-08-07 and created by the user BILLUSER.

So now we are good to go.
Lets run the delete queries with commit and delete the inspection.

Now lets repeat same steps to delete the inspection for IMO=3456755 and Inspection_ID=728806  

delete from inspection where inspection_id = 728806;
delete from deficiency_action_code where deficiency_id in (select deficiency_id from deficiency where inspection_id = 728806);
delete from deficiency_description where deficiency_id in (select deficiency_id from deficiency where inspection_id = 728806);
delete from certificates where inspection_id = 728806;
delete from deficiency where inspection_id = 728806;
delete from detention where inspection_id = 728806;
delete from inspection_area where inspection_id = 728806;
delete from operational_control where inspection_id = 728806;
delete from vessel_action_code where inspection_id = 728806;
Delete From Tm028_Xref_Inspection_Up Where Inspection_Id = 728806;
update vessel set last_inspection_id = null where last_inspection_id = 728806;
delete from inspection where inspection_id = 728806;
rollback;
Query Result:

Error starting at line : 1 in command -
delete from inspection where inspection_id = 728806
Error report -
ORA-02292: integrity constraint (CPSCSW.FK_DEFICIEN_REF_134_INSPECTI) violated - child record found
1 row deleted.
1 row deleted.
1 row deleted.
1 row deleted.
1 row deleted.
1 row deleted.
1 row deleted.
1 row deleted.
1 row deleted.
1 row updated.
1 row deleted.
Rollback complete.

Let's run with commit.
Now lets confirm the inspection is deleted from the Vetting list by login to production site.
We are good the inspection is deleted from the Vetting queue.
All the three inspections are deleted so inform the client and close the SMGS ticket.

Default Descriptions listing Error on THETIS export

Problem:
0 of 1 inspections successfully sent to THETIS<br /><hr width="100%">Inspection ID 724621: ERROR: THETIS Export Error T.IE.5: [PostInspectionResultsJson Error] Deficiencies: value of Default Description is not valid (IMO: 9321914)

Solution:
See TFS 14115 or TFS 14005 for appropriate solutions.

Deficiency Action Error on THETIS Upload

Problem:
Inspection ID 724621: ERROR: THETIS Export Error T.IE.5: [PostInspectionResultsJson Error] Deficiencies: Deficiency Actions is missing (IMO: 9321914)

Solution:
See TFS 14165 for an appropriate solution.

Nature of Defect Issue

Problem:

Inspection ID 723726: ERROR: THETIS Export Error T.IE.5: [PostInspectionResultsJson Error] Deficiencies: The Nature of Defect Fee not paid for delivery cargo residues is not valid for deficiency 01102 - Cargo Ship Safety Construction (including exempt.) (IMO: 6725432)

...

http://tfsprod:8080/tfs/Marine Safety/Marine Safety Portfolio/_workitems?id=14006


Default Descriptions Error on THETIS Export

Problem:

Receiving the following error on export to THETIS:

...

http://tfsprod:8080/tfs/Marine%20Safety/Marine%20Safety%20Portfolio/_workitems?id=14005

 

CPSCS - IM268001 - VTP Issues

Problem:

Last week we had our CPSCS - VTP finally come back as there was problem since the ORCALE Migration and now over the weekend there was the “Security Patch” and systems went down. CPSCS’s VTP did not come back up.

...

http://tfsprod:8080/tfs/Marine%20Safety/Marine%20Safety%20Portfolio/_workitems?id=13995

 

CPSCS - IM267488 - CPSCS Active Port Calls not working

Problem: 

Please pass on to Marine Safety IT to investigate why the Active Port Calls are not working in CPSCS, I have checked a number of the regions and district offices and no port calls are being displayed. See below Vancouver.

...

http://tfsprod:8080/tfs/Marine%20Safety/Marine%20Safety%20Portfolio/_workitems?id=13942

 

CPSCS - <NO IM> - THETIS and APCIS Export/Import not working following DB migration

Problem:

Client reports that THETIS and APCIS exports are not working following the Oracle migration on January 19, 2019.

...

http://tfsprod:8080/tfs/Marine%20Safety/Marine%20Safety%20Portfolio/_workitems?id=13901

 

CPSCS-EXCEL: Spreadsheet not working following DB migration

Problem:

Spreadsheet stopped responding after Oracle migration.  See Discussion for steps used to solve the issues and Acceptance criteria for solution.

...

http://tfsprod:8080/tfs/Marine%20Safety/Marine%20Safety%20Portfolio/_workitems?id=13889

 

 

CPSCS - IM263543 - NCR - Tower C - 10 - D127 - HENDERW - Issue With Link in RuleCheck

Problem:

Hello,

The Transport Canada Marine Inspectors are all currently experiencing an error with the ‘EMSA RuleCheck’ hyper-link.
The link seems to be broken and does not take them to the right place. Currently the link goes to https://rulecheck.emsa.europa.eu/emsaweb/srcweb/checkdeficiencies/RcCheckItems.jsp?shipType=340&constructionDate=29/10/2003&deficiencyCode=04118
This appears to go nowhere and none of the inspectors are able to access RuleCheck.

Al Rushwan suggested that I could contact you for assistance in this case. Here is an image with the hyperlink in blue.

...

http://tfsprod:8080/tfs/Marine%20Safety/Marine%20Safety%20Portfolio/_workitems?id=13522

 

 

CPSCS - IM263215 - GOTLAN SOFIA - IMO 9328144 - Export Error URGENT

Problem:

Fixed initial error but new error on upload:

...

http://tfsprod:8080/tfs/Marine%20Safety/Marine%20Safety%20Portfolio/_workitems?id=13456

CPSCS- IM261825 - QUE - RMK - NM - CPSCS - Guay, Mathieu - Port de Rimouski disparu de la liste.

Problem:

24 hours as important in Rimouski office as not getting the arrival information to target vessels. 

...

http://tfsprod:8080/tfs/Marine%20Safety/Marine%20Safety%20Portfolio/_workitems?id=13292

 

CPSCS - IM261581 - NCR - Tower C - 10 - D127 - HENDERW - Error Submitting ATENI, IMO 9656175 - Prince Rupert to CPSCS

Problem:

NCR - Tower C - 10 - D127 - HENDERW - Error Submitting ATENI, IMO 9656175 - Prince Rupert to CPSCS

William D Henderson - 6139797809

IMO 9656175 is not allowing submission to CPSCS.
Attached is email with error message in it.

...

NCR - Tower C - 10 - D127 - HENDERW - Error Submitting ATENI, IMO 9656175 - Prince Rupert to CPSCS

William D Henderson - 6139797809

IMO 9656175 is not allowing submission to CPSCS.
Attached is email with error message in it.

 

CPSCS - IM261600 - Submission Reports in CPSCS Web (SALDANHA BAY, IMO 9458377)

Problem:

Dear Service Desk:

Please pass on to Marine Safety IT – Inspector in Sept-iles   second one in last couple days can not submit completed inspection reports into CPSCS. The vessel in question is SALDANHA BAY, IMO 9458377  - user Denis Rodrigue.  The error message returned is below.
Inspection is in User’s saved inspections.   I tried it here at HQ and get the same error.

Regards

...

http://tfsprod:8080/tfs/Marine%20Safety/Marine%20Safety%20Portfolio/_workitems?id=13107

 

CPSCS - IM260498 - NCR - Tower C - 10 - D127 - HENDERW - Connection with APCIS Webservice - URGENT

Problem:

NCR - Tower C - 10 - D127 - HENDERW - Connection with APCIS Webservice - URGENT
William  Henderson - 6139797809

...

NCR - Tower C - 10 - D127 - HENDERW - Connection with APCIS Webservice - URGENT
William  Henderson - 6139797809

 

CPSCS - IM258313 - NCR - Tower C-8-D101-HENDERW-APCIS error reporting - Defect Photos

Problem:

Could you let me know before I go to TMOU meeting the problem we are having with the uploading of Deficiency photos is it our side or APCIS side.
Example of the error follows:

4 of 6 inspections successfully sent to APCIS<br /><hr width="100%">Inspection ID 720769: ERROR: APCIS Export Error A.IE.2: Error : UploadInspectionReport : ??????? "http://server.soap.apcis.tmou.org/inspection/v1:ShipDeadweight" ??????????????: ????????
"0" ??????????????? ? ????? ?????? ??? ???? ?????? "http://server.soap.apcis.tmou.org/inspection/v1:T_ShipDeadweight" — ???? ??????????? MinInclusive. (IMO: 7235707)<br /><hr width="100%">Inspection ID 720771: OK (IMO: 9597563)<br /><hr width="100%">Inspection ID 720773: ERROR: APCIS Export Error A.IE.2: Invalid Surveying Authority value in certificate with code '503' (IMO: 9679799)<br />WARNING: Certificate code 528 is not a valid APCIS certificate.
 (IMO: 9679799)<br /><hr width="100%">Inspection ID 720787: OK (IMO: 9537628)<br /><hr width="100%">Inspection ID 720786: OK (IMO: 9207443)<br /><hr width="100%">Inspection ID 720770: ERROR: APCIS Export Error A.IE.3: Error : UploadDeficiencyImages :
Access Denied for putReportDeficiencyPhotos methodERROR: APCIS Export Error A.IE.3: Error : UploadDeficiencyImages : Access Denied for putReportDeficiencyPhotos method (IMO: 9595888)<br />WARNING: Certificate code 531 is not a valid APCIS certificate.
 (IMO: 9595888)

...

http://tfsprod:8080/tfs/Marine%20Safety/Marine%20Safety%20Portfolio/_workitems?id=12679

 

CPSCS - IM257341 - CPSCS list of Vessel with Defect Photos - URGENT

Problem:

Dear service Desk: 

...

http://tfsprod:8080/tfs/Marine%20Safety/Marine%20Safety%20Portfolio/_workitems?id=12421

 

CPSCS - IM256472 - ATL NS DAR - George M Anderson - CPSC Report List of Vessels

Problem:

ATL NS DAR - George M Anderson - CPSC Report List of Vessels
Attached full email.

...

http://tfsprod:8080/tfs/Marine%20Safety/Marine%20Safety%20Portfolio/_workitems?id=12395

 

CPSCS - IM256403 - Three uploads to THETIS failed due to Default Description errors

Problem:

See attached THETIS error: 

...

http://tfsprod:8080/tfs/Marine%20Safety/Marine%20Safety%20Portfolio/_workitems?id=12262

 

CPSCS - IM256332 - COSRICH Lake IMO 9646986 - MMSI Duplicate - URGENT

Problem:

Dear Service Desk: 

...

http://tfsprod:8080/tfs/Marine%20Safety/Marine%20Safety%20Portfolio/_workitems?id=12248

 

CPSCS - IM256232 - PSC Inspection Changes - Error Message - Can't change deficiency action code

Problem:

 Please pass on to Marine Safety IT to investigate why I can not change a deficiency action taken code from code 17 to Code 15 and add the next port. See attached what should be changed to and the error message. 

...

 http://tfsprod:8080/tfs/Marine%20Safety/Marine%20Safety%20Portfolio/_workitems?id=12244

 

CPSCS - IM256258 - CPSCS inspection submission error - MESABI, IMO 9359600

Problem:

Dear Service desk: 

...

http://tfsprod:8080/tfs/Marine%20Safety/Marine%20Safety%20Portfolio/_workitems?id=12235

 

CPSCS - IM255238 - NCR - Tower C - 10 D127 - HENDERW - APCIS Export Error investigate why

Problem:

NCR - Tower C - 10 D127 - HENDERW - APCIS Export Error investigate why

William D Henderson - 61397978

See attachment

...

http://tfsprod:8080/tfs/Marine%20Safety/Marine%20Safety%20Portfolio/_workitems?id=12203

 

CPSCS - IM255287 - NCR - Tower C - 10 - D127 - HENDERW - APCIS EXPORT FOR Detention CAP PASADO, IMO 9311799 - -RO RELAT

Problem:


Dear Service Desk:

Could you please pass this on to Marine Safety IT to determine why RO Related was uploaded to APCIS via the batch as per comments from APCIS (attached) when in fact looking at the report in CPSCS the related Deficiencies does not have RO related checked.
Please determine why and let us know the result.

Regards

...

http://tfsprod:8080/tfs/Marine%20Safety/Marine%20Safety%20Portfolio/_workitems?id=12201

 

CPSCS - IM267488 - CPSCS Active Port Calls not working

Problem:

Dear service Desk: 

...

http://tfsprod:8080/tfs/Marine%20Safety/Marine%20Safety%20Portfolio/_workitems?id=13942

 

CPSCS - IM261600 - Submission Reports in CPSCS Web (SALDANHA BAY, IMO 9458377)

Problem:

Dear Service Desk:

Please pass on to Marine Safety IT – Inspector in Sept-iles   second one in last couple days can not submit completed inspection reports into CPSCS. The vessel in question is SALDANHA BAY, IMO 9458377  - user Denis Rodrigue.  The error message returned is below.
Inspection is in User’s saved inspections.   I tried it here at HQ and get the same error.

Regards

...

http://tfsprod:8080/tfs/Marine%20Safety/Marine%20Safety%20Portfolio/_workitems?id=13107


CPSCS - IM310089 - CPSCS: Server Certificate Expiring

Problem:

The SSL certificate for apcis.tmou.org which is installed on our web servers – for the CPSCS web application - will expire on June 11, 2020.


Solution:

Provide a replacement certificate before it expires:

In Dev

  • Create a new folder under \\tc4s0b\groups\AFCCB\MS_APPS\CPSCS\CERTIFICATES\ and store the new APCIS certificate (apcis.tmou.org.cer) in that folder, ex. July 2021. 
  • Ensure that you can access the file when you Remote into NCRWS535
  • Remote into NCRWS535.
  • From NCRWS535, open the Certificate Manager, under Personal/Certificates, find the existing apcis.tmou.org

...

To verify in ACC,  go to the Vetting.Export Queue Tab and select one of the vessels, then click export

Image Modified

After ACC confirmed, request that the certificate be installed in PROD by the web team.  Also request access to the web.config to make changes (compare/kdiff) etc.

To verify in PROD, ask the client to check  in CPSCS 


CPSCS - IM312504 - A list of the inspectors in Marine Safety (by specific role, or for all).

Problem:

Is there a way of getting a list (Excel or CV) all of the inspectors in Marine Safety with a designation of “Port State Control Officer”?


Solution:

This solution could apply to any specific type of inspector role, or for all.

The list of possible inspector roles can be found in the S_USER_ROLE table in the CPSCSP database. In the case of this ticket we were looking for the "PSCO" (Port State Control Officer) role.

Code Block
languagesql
titleGet List of Inspector Roles
collapsetrue
Select * from S_USER_ROLE;

...

The returned data from the above can then be exported or copied into whatever file format the requester is asking for.



CPSCS - IM312526 - Remove (or modify) a deficiency. Find a deficiency in the DB from the website or client given deficiency #

Problem:

The client will give you a deficiency # that is not the same as what you need to drill for in the database to get to the same record.

" I am attempting to modify two deficiencies in the 2020-07-02 inspection report for the GOLDEN ARCUS (IMO 9743162). I am trying to delete deficiency 15150, and I am trying to remove the “ISM-related” mark from deficiency 10138. "


Solution:

Log into CPSCSWEB : https://wwwapps2.tc.gc.ca/saf-sec-sur/4/cpscs-scepc/ using the login/pw from the password file.

Click on "Vessel Search" and search the IMO# given in the ticket.

Click on the Details button:

Image Modified


Click on the Inspections button:

Image Modified


Click on the Details button:

Image Modified


Click on the Deficiencies tab:

Image Modified


Look for the deficiencies # that client stipulated and press the details button:

Image Modified


The pop-up will have the actual DB value ("DID") to query against:

Image Modified


Logged into CPSCSP.world:

Code Block
languagesql
titleGet the Deficiency
collapsetrue
select * from cpscsw.deficiency t where t.Deficiency_ID = 719372; 

  (as per the example above)

From this point the deficiency record can be modified or deleted.

...

Code Block
languagesql
titleDelete the Deficiency
collapsetrue
--15150 deficiency # from client
select * from cpscsw.deficiency t where t.Deficiency_ID = 719372;

--deleteing a defeciancy requires this order;
delete from CPSCSW.deficiency_description where Deficiency_ID = 719372;
delete from CPSCSW.deficiency_images where Deficiency_ID = 719372;
delete from CPSCSW.deficiency_action_code where Deficiency_ID = 719372;
delete from CPSCSW.deficiency where Deficiency_ID = 719372;



CPSCS - IM321375 - APCIS Export Failure: "Empty deficiency action list() for deficiency #xxxxx (IMO XXXXXXX)"

Problem:

Export failure for an inspection with deficiencies.

Image Modified

"Inspection report for the CALLISTO (IMO 9040077) failed to export to APCIS."

Solution:

The solution is based on a previous one described above.

APCIS does not allow Code 30 (“Grounds for detention") and Code 17 (“Rectify deficiency before departure”) on the same deficiency. 

...

Advise client that the code 17 (“Rectify deficiency before departure”) should be removed from all deficiencies where both it and "Grounds for detention" are used, and then try the export to APCIS again.

CPSCS - Server object error 'ASP 0177 : 800401f3' Server.CreateObject Failed

Problem:

When running CPSCS locally in Visual Studio you get the following error:  Server object error 'ASP 0177 : 800401f3' Server.CreateObject Failed

Solution:

The solution is to open a command prompt 'run as administrator' and unregister and re-register the DLL using the following commands:


Code Block
regsvr32 -u C:\Users\graingm\source\repos\CPSCS\src\CPSCS_ROOT\CPSCS\CPSCS_Objects\cpscs6010.dll

regsvr32 C:\Users\graingm\source\repos\CPSCS\src\CPSCS_ROOT\CPSCS\CPSCS_Objects\cpscs6010.dll

Then rerun the application.


CPSCS - Application is down

Problem:

CPSCS is down

Solution:

Contact the web services team. They will do the following to resolve the issue:

...

Step #3/4 seems to be the common fix for this type of problem: the component services are being hung-up, so the team restarts the server


CPSCS - Application is down - EXPLAINED

Problem:

CPSCS is down

Solution:

CPSCS might have become inaccessible because the printing function will sometimes freeze up and stall the entire website. In this case, we follow the steps provided in solution, above.

...

To summarize, we want to remove and conflicting COM+ Components from the server (build.Report.1 / aspSmartUpload / CPSCS6010.dll) and utilize these dlls by registering them in CMD Prompt instead: regsvr32 [path to dll]. You will need to shut down the application pool and running components before attempting.


CPSCS - Vetting Vessel Needs to be put back in the Export Queue

Problem:

ON occasion, we may need to put a record back in the APCIS Export Queue, to do so  Bill H will provide the IMO NO and the Inspection ID. Make sure they are both in the select otherwise  you will get several records that shouldn't be  processed

...