Task #18294
Updated by Redmine Admin 3 months ago
[10. QL 04.03.25 - Revenue - TB vs Listing.xlsx](/uploads/dd842fe9eaa1b96a9a09ddcdad653929/10.\_QL_04.03.25\_-\_Revenue\_-\_TB_vs_Listing.xlsx) <table> <tr> <td>Module</td> <td>Status</td> <td>Updated Attachment/Comments</td> <td>Original Difference without VAT</td> <td>Original Discrepancy Type</td> <td>Revaluated Difference without VAT</td> <td>RCA</td> <td>Revaluated Discrepancy Type</td> <td>Bug Fixed?</td> <td>Estimate</td> </tr> <tr> <td>Ocean Master Manifest</td> <td>Done</td> <td> [OCEAN_IMP_MANIFEST.xlsx](/uploads/8f0b5129916634c8de476073540932b4/OCEAN_IMP_MANIFEST.xlsx) </td> <td>8,775</td> <td>Underbilling</td> <td>0</td> <td> List\[1\] </td> <td>No Discrepancy</td> <td> \[1\]Yes </td> <td> </td> </tr> <tr> <td>Ocean Master Manifest BL</td> <td>Done</td> <td> [MASTER_BLS](/uploads/6045838c8758eed05122419013337882/OCEAN_IMP_MASTER_BLS.xlsx) \[18\] Deletion of BL in 2023 </td> <td>385,125</td> <td>Underbilling</td> <td>74,750</td> <td> List\[3\] Overbilling\[4\] Underbilling\[5\] </td> <td>Overbilling</td> <td> \[3\]Yes \[4\]No \[5\]No </td> <td> </td> </tr> <tr> <td>Ocean House BL</td> <td>Done</td> <td> [HOUSE_BLS.xlsx](/uploads/140a18946b84c53784af4435ca706450/HOUSE_BLS.xlsx) \[16\] Deletion of BL in 2023 </td> <td>411,775</td> <td>Underbilling</td> <td> \-54,925 </td> <td> List\[2\] Overbilling\[6\] Underbilling\[17\] </td> <td>Underbilling</td> <td> \[2\]Yes \[6\]No \[17\]No </td> <td> </td> </tr> <tr> <td>Ocean Transhipment - BOE</td> <td>Done</td> <td> [Ocean Transhipment - BOE.xlsx](/uploads/561ab0247720069252f2e8324883b3e1/Ocean_Transhipment\_-\_BOE.xlsx) </td> <td>15,925</td> <td>Underbilling</td> <td> \-15,925 </td> <td> Underbilling\[7\] </td> <td>Underbilling</td> <td> \[7\]No </td> <td> </td> </tr> <tr> <td>Air Master BL</td> <td>Done</td> <td> [AIR_IMP_MASTER_AWBs.xlsx](/uploads/942b6aa17166bda4b8aea6f2c9949a0e/AIR_IMP_MASTER_AWBs.xlsx) \[15\] Credit Note </td> <td>1,659,125</td> <td>Overbilling</td> <td>4,225</td> <td> List\[8\] Overbilling\[9\] </td> <td>Overbilling</td> <td> \[8\]Yes \[9\]Yes </td> <td> </td> </tr> <tr> <td>Air House BL</td> <td>Done</td> <td> [Air Import House WayBill.xlsx](/uploads/9fbcc0016a3844b028022cb5947120a4/Air_Import_House_WayBill.xlsx) </td> <td>3,700</td> <td>Overbilling</td> <td> \-15,800 </td> <td> List\[31\] Underbilling\[19\] Overbilling\[20\] </td> <td> </td> <td> </td> <td> </td> </tr> <tr> <td>Ocean Export Master BL</td> <td>Done</td> <td> [OCEAN_EXP_BLS.xlsx](/uploads/435c97d6fb299d9fb7e7f0a3bcd843a7/OCEAN_EXP_BLS.xlsx) </td> <td>1,320</td> <td>Overbilling</td> <td>1,155</td> <td> List\[11\] Overbilling\[12\] Overbilling\[13\] Underbilling\[14\] </td> <td>Overbilling</td> <td> \[11\]Yes \[12\]No \[13\]No \[14\]No </td> <td> </td> </tr> <tr> <td>Air Courier Category 3</td> <td>Done</td> <td> [Air Import CMPL Courier 3.xlsx](/uploads/a7f925144e122cd9bfe36ffe9a49d6cc/Air_Import_CMPL_Courier_3.xlsx) [Air Import NOT CMPL Courier 3.xlsx](/uploads/f937cd57535a1adcfa6d217f7bbd1eb6/Air_Import_NOT_CMPL_Courier_3.xlsx) </td> <td>2,420,600</td> <td>Overbilling</td> <td> \-107,350 </td> <td> List\[25\] Underbilling\[21\] Underbilling\[22\] </td> <td> </td> <td> \]25\]Incomplete \[21\]No \[22\]No </td> <td> </td> </tr> <tr> <td>Air Courier Category 4</td> <td>Done</td> <td> [Air Import CMPL Courier 4.xlsx](/uploads/d2c75254fcbf9f059acad519ac6a227b/Air_Import_CMPL_Courier_4.xlsx) [Air Import NOT CMPL Courier 4.xlsx](/uploads/9c1752e2a40c9ad467736af491d159ae/Air_Import_NOT_CMPL_Courier_4.xlsx) </td> <td>1,436,825</td> <td>Underbilling</td> <td> \-99.175 </td> <td> List\[25\] Underbilling\[26\] Overbilling\[27\] </td> <td> </td> <td> \]25\]Incomplete \[26\]No \[27\]No </td> <td> </td> </tr> <tr> <td>Air Transhipment</td> <td>Done</td> <td> </td> <td>9,900</td> <td>Overbilling</td> <td> \-47,200 </td> <td> List\[28\] </td> <td>Underbilling</td> <td> \[29\]No </td> <td> </td> </tr> <tr> <td>Ocean Transhipment</td> <td>Done</td> <td> </td> <td>732,315</td> <td>Underbilling</td> <td> \-143,830 </td> <td> List\[30\] </td> <td>Underbilling</td> <td> \[30\]No </td> <td> </td> </tr> <tr> <td>Air Transhipment AWBs</td> <td>Done</td> <td> </td> <td>973,475</td> <td>Underbilling</td> <td>137,150</td> <td> List\[29\] </td> <td>Overbilling</td> <td> \[29\]No </td> <td> </td> </tr> <tr> <td>Air Master Manifest</td> <td>Done</td> <td> [Air Import Manifest.xlsx](/uploads/b987b8c91d83d62ff73008d77342a3c0/Air_Import_Manifest.xlsx) </td> <td>NA</td> <td>NA</td> <td>50</td> <td> List\[24\] </td> <td>Underbilling</td> <td> \[24\]Yes </td> <td> </td> </tr> <tr> <td>Ocean Export Manifest</td> <td>Done</td> <td> [Ocean Export Manifest.xlsx](/uploads/630b66492f1e0b8f067b34c03e01102f/Ocean_Export_Manifest.xlsx) </td> <td>NA</td> <td>NA</td> <td> \-325 </td> <td> List\[23\] </td> <td>Underbilling</td> <td> \[23\]Yes </td> <td> </td> </tr> </table> \[1\] - #459 - List Provided caters for skipped vessels \[2\] - #468 - List provided fetches cases for deletions that occured in 2025 and only fetches the first House WayBill marked as Partshippped. \[3\] - #466 **-** List Provided included skipped vessels and missed deleted BLs which occured 2025 but were billed in 2024 \[4\] - #469 **-** Case when in month of June an IMP type BL has been Manifested and billed. In month July the same BL has been amended to TSP and re-billed for TSP without refunding the IMP. Bug. \[5\] - #469 **-** Case when a BL has multiple containers, manifest and billed. The BL goes through an amendment and one of the containers is deleted. The BL is still active in Manifest, however next month billing a refund is processed. Bug. \[6\] - #106 - Overbilling to to skipped vessel being in the subsequent month of billing e.g Billed in JANUARY and made skipped in February \[7\] - #461 Issue lies in source retrieval of data in Oracle. Need to fetch distinct integration rules info for both export and import voyages in query MainInfoTBD cursor. Error is thrown in SQL: \[SP_INSERT_OCEAN_IMP_TSH_BILLING_XML\]. The error is: Violation of PRIMARY KEY constraint 'PK_MainTBDInfo'. Cannot insert duplicate key in object 'dbo.MainTBDInfo'. The duplicate key value is (928998a2-e63c-40f0-9a65-47a1adb349fd, ATP24005482, CNTAO, ATP24005550). \[8\] - #458 List Updated \[9\] - #453 When identifying part shipment, it should ignore ClientCode, else we will have a case where an AWB is of type apart shipped and all clients will be billed twice. This occurs only for CodeShared flights. E.g. AF code shared part of the AWB with MK \[11\] - #463 - List Updated \[12\] - #462 - Booking billed twice in a single month. 1 case found for year 2024 \[13\] - #464 - Overbilling due to actual departure date was not removed in vesdep warehousign table when MPA removed the actual departure date time \[14\] - #465 **-** Underbilling due to loading date not present in warehousing table MainAMQ while this record is present in Oracle CCS \[15\] - Air Master BL- Credit Note of amount Rs. 12,350 done outside the CCS/Billing system for Special Handling Codes DIP/HUM \[16\] - Ocean House BL - Deletions of BLs valued at Rs. 5,850 occured in 2024 for 2023 Manifested records \[17\] - #467 **-** Underbilling Ocean House BL - Skipped should be ignored when BL arrives in another Vessel. Those cases are being treated as 'Billed' when in reality process billing has not actually billed them. Refer to \[Bill\] column = No \[18\] - Ocean Master BL - Deletions of BLs valued at Rs. 12,675 occured in 2024 for 2023 Manifested records \[19\] - #480 **-** RCA Pending \[20\] - #480 **-** RCA Pending \[21\] - COUR3 - NOT CMPL - 3 Cases found for '176-75472132','176-96723690','239-23937944' which do not exist in db. Integration sql timeout. Rs150 impacted \[22\] - COUR3 - CMPL - Underbilling: Cases identified whereby Records do not exist in CCS_WAREHOUSING 52 Cases found for '881-25911255','881-25812360','MPLAF470170524B' which exist in db but not billed 981 Cases found for '176-02903891','176-95908772','176-96409655' which do not exist in db. Integration sql timeout \[23\] - #491 **-** List Updated \[24\] - #498 - List Updated \[25\] - #493 **-** List Courier with several scenarios updated. - Incomplete. \[26\] - Underbilling: Cases identified whereby Records do not exist in CCS_WAREHOUSING 73 Cases found for '176-75472132','176-96723690','239-23937944' which do not exist in db. Integration sql timeout \[27\] - 2 Cases where billed COUR4 instead of COUR3 - LW502210663DE, CS011895129CZ due to Change of Category not saved in Warehousing \[28\] - Query Pending \[29\] - Query Pending \[30\] - #652 - List Updated Query Pending \[31\] - #520 - List Updated