MDR Report

Report code: mdr_report_2. Opens the documents_master module in print mode (print=browse&print_browse=all&type=mdr_report_2); the dispatcher documents_master_handler_print_browse.inc.php includes documents_master_handler_print_mdr_report_2.php, 2,051 lines, fully on $pdo. One document per row, grouped by discipline and document type, with a Table Summary per discipline on the left. Output is an HTML page in a new tab (Preview) or an .xlsx (Excel); both come from the same arrays, so the numbers are identical.

How this page was verified

Traced query by query in master on 2026-09-06, then the numbers were checked by hand against the KTP production snapshot (inact_ktp_eris, project SNS). The screenshots are from inact-ktp.eris.place on that snapshot. KTP runs an older, customised copy of this handler; where it differs, the instance differences say so.

MDR Report, the filter form · click to enlarge

view=custom renders documents_master_print_mdr_report_2_custom.htm. It posts back as GET with these fields:

Field	Meaning
`project`	the project; defaults to the first project the user may see, and the form pre-selects the header's project
`field[]`, `operator[]`, `what[]`	filter rows; operators `equals`, `not_equal`, `greater_than`, `less_than`, `less_than_equals`, `contains`, `start_with`, `not_contain`
`actual_date_from`, `actual_date_to`	d/m/Y; the summary window, see below. They do not filter the rows
`mdr_report_2_download_as`	`html` for Preview, `excel` for the download
`person_id`	the user, only printed in the header

Each filter field is routed to one of three queries. This matters, because a field that belongs to the library query silently drops every document that has no library row:

Field in the form	Applied to	Note
Document Number, Document Title	MDR query and routing query	the two column names differ (`dm_docmaster_number` / `rout_doc_number`)
Discipline, Document Category, Weight Factor, Contractor Name (`dm_originator_code`), and any column of the project's MDR template	MDR query	Contractor Name is rewritten to `company_code` for the routing query
Rev	routing query (`mrf.rout_rev`)	sets `$is_search_routing`: documents without a routing are dropped
Issued Status, Status Return, Incoming Transmittal, Outgoing Transmittal, Remarks, Date Received, Due Date, Date Returned, Originator	library query (`d.*`)	sets `$is_search_doclib`: documents without a library row are dropped. Dates are converted from d/m/Y
Over Due Status	library query	`Equals` keeps rows where the due date is still ahead (`doc_due_date − today > 0`), the row has an issued status and no result code; `Not Equal` keeps the rest. Since INA01-852 (master, 2026-09-07) and INA27-302 (Jadestone) the date expression is branched per driver; JOTRE, Timas and Medco still carry the SQL Server-only `DATEDIFF(day, ...)`, which errors on MySQL and PostgreSQL. See Known issues for what the filter actually selects
Document Status Flow	applied in PHP after the rows are built	`equals` keeps only that state, anything else excludes it

Several rows on the same field are joined with OR; different fields with AND.

The five queries, in order

Reference lists. Projects, _getIssuedList() (issued statuses joined to ts_progress_gate, ordered by gate sequence), disciplines, document types, the MDR template columns (getProjectSelectedColumn), companies.
The MDR. ts_documents_master for the project with the MDR filters, ordered by discipline then document type. This list decides which documents appear: a document that is in the library but not in the MDR is never printed. Grouping keys: dm_discipline_identifier then dm_document_type; an empty value groups under 0.
Routings to ignore. Two counts over ts_map_routing_to for sent routings (rout_have_send = 1) that are not in ts_routmaster_temp: recipients per routing, and recipients whose rout_is_oninbox = 0. When the two are equal, nobody has the routing in an inbox any more and the routing is added to $routIdListWithNoUserInbox and excluded from step 4. Routings still in ts_routmaster_temp are also excluded there.
The routing per document. ts_routing joined to ts_map_routing_file (rout_type = 1, the main file), ts_documents_master and ts_documents on the document number, for sent routings, with the _R (return) revisions removed. Ordered by discipline, type, document number, progress gate sequence, rout_id. The loop stores one row per document number and revision and one per document; because it overwrites, the last row wins: the routing on the highest gate, and among those the highest rout_id. That routing's revision becomes the Rev column and the key for the library lookup.
The library rows. ts_documents for the project, _R revisions removed, joined to ts_doc_resultcode for the return status, plus two computed columns: due_day (days from due date to today) and due_day_return (days late on return). Stored by document number and revision, and, only when that revision has a routing from step 4, by document number and issued status. That second index is what the Issued Status flags, the ordinal issue blocks and the whole progress calculation read. A library row for a revision that was never routed, or whose routing has left every inbox, is invisible to them.

Per-document columns

MDR Report, HTML preview on the KTP snapshot · click to enlarge

One row per MDR document, grouped under a discipline row and a document-type row. Each row is built from three records: the MDR row (ts_documents_master), the routing picked in step 4, and the library row (ts_documents) of that routing's revision, picked in step 5. The last two can be missing, and most of the cells people argue about come down to which routing and which library row were picked.

Which routing, which library row

The routing is the sent routing on the highest progress gate, and among those the one with the highest rout_id. Routings that nobody has in an inbox any more (step 3) and routings still in ts_routmaster_temp are skipped. Its revision becomes the Rev column.
The library row is the ts_documents row with that document number and that revision, _R return rows excluded. Every column from Status Recv to Over Due Status reads this one row. When the routed revision has no library row, the document prints Not Received Yet and the rest of the row is blank.

Take SNS-C-CC-10-002 on the screenshots. Its library holds revisions A (IFR), B and B1 (IFA), 0 and 0R (AFC), each with a _R twin. The routing on the highest gate is the one for 0R, so Rev is 0R, Status Recv is AFC, and the received date, transmittals, due date and return status are those of revision 0R, whatever happened to A, B, B1 and 0. The earlier revisions only surface in the Issued Status flags and in the ordinal issue blocks.

Document, weight, progress and revision

The first column block: document, title, discipline, weight, progress, the routing revision and the received status · click to enlarge

Column	Source	Notes
Document Number, Document Title	`dm_docmaster_number`, `dm_docmaster_title`	from the MDR row
Discipline	discipline label of the group	shows Code Package Identifier instead when `project_vdr` is on; that flag is hard-coded to `0`
Weight (%)	`dm_weight_factor`	blank when 0 or empty
Progress (%)	Σ `progress_per_issued` over the statuses, see Progress	blank when 0
Issued Date block: Plan Date, Actual Date, Progress (%) per issued status	only when the MDR template enables the `dm_plan_date_all_issued` column	Plan Date is always empty: the MDR query does not select that column. Actual Date is the first `doc_send_received` among the library rows with that status. Progress (%) is that status's `progress_per_issued`
Rev	`rout_rev` of the picked routing	the routing's revision, not the newest library revision
Status Recv	`issued_code` of the library row	the issued status the revision was received under

Received, reviewed, returned

The second block: received and due dates, transmittals, Document Status Flow, the Issued Status flags and Contractor Name · click to enlarge

These columns follow one revision through its life, and each is written by a different step of the routing. Knowing which step writes a column tells you why it is blank.

Column	Column of `ts_documents`	Written when
Date Received from Contractor	`doc_send_received`	the revision enters the library: transmittal in, MDR upload, or the D - Transmit publish
Incoming Transmittal	`doc_submission_number`	same moment; the contractor's transmittal number
Due Date	`doc_due_date`	same moment; `get_santos_duedate()` adds the review time in working days to the received date, skipping the National Holiday list
Document Status Flow	derived	see the next section
Over Due	`due_day`, computed in the query	today minus Due Date. Printed only while the row is Under review and the number is positive; blank once the approver has signed off, however late the return was
Over Due Status	the word `Overdue`	same rule as Over Due
Status Return	`doc_res_name`, through `doc_res_id`	the approver's sign-off: the Return Code chosen on the sign-off form
Outgoing Transmittal	`outgoing_transmittal_no`	the return transmittal is generated, whichever path did it: the approver's close with a return mail, Ready to Return, or the batch return (`routing_handler_post.php`, `routing_handler_ajax.php`, `routing.inc.php`)
Date Returned to Contractor	`doc_issued_date`	in practice never. The live return paths write `doc_appdate` (the sign-off date, printed as Date Return in the issue blocks) and, on master since 2026-08, `doc_returned_date`. Only the legacy transmittal-register code in `routing.inc.php` and the library edit form write `doc_issued_date`. On the KTP snapshot 0 of 7,299 SNS rows have it, which is why the column is empty on every screenshot here
Remarks	`doc_notes`	the library row's notes

due_day is a driver-specific expression: DATEDIFF(CURRENT_TIMESTAMP, doc_due_date) on MySQL and EXTRACT(DAY FROM CURRENT_TIMESTAMP - doc_due_date) on PostgreSQL, both "days since the due date". On SQL Server the arguments are the other way round, DATEDIFF(day, CURRENT_TIMESTAMP, doc_due_date), days until the due date. See Known issues.

Jadestone only: Plan Date, Status and Next Expected Submission

Jadestone project AKT: the three INA27-298 columns sit between Date Received from Contractor and Outgoing Transmittal · click to enlarge

Added on Jadestone by INA27-298 (Done, merged to main on 2026-09-06). Master and the other forks do not have them.

Plan Date against Date Received gives Ahead, Late or Achieve; Next Expected Submission prints the code with its description · click to enlarge

Column	Value	Blank when
Plan Date	the plan date entered on the MDR Progress tab for the issued status of the printed revision: `ts_mdr_progress.progress_content`, key `two`, entry `<Status Recv>`, field `plan_date`. Not `dm_plan_date_all_issued`, which Jadestone does not have	the document has no progress row, or that status has no plan date
Status	Plan Date against Date Received from Contractor, dates only: `Late` when received after the plan, `Ahead` when before, `Achieve` when the same day	either date is missing
Next Expected Submission	`next_expected_id` of the same library row, printed as `code - description` through `ts_issued_status` (`IFA - Issued for Approval`). `0`, NULL and empty print blank, unlike the raw value inside Document Status Flow	the approver picked nothing. The whole column is absent when the `enable_next_issue_code` config is off, the same gate the other Next Expected Submission fields use (INA27-171). Plan Date and Status always show

The Excel output has the same three columns in the same place. The handler loads every ts_mdr_progress row of the project up front for the plan dates; the progress percentages are not touched by this change.

Document Status Flow

Evaluated on the library row of the routed revision, in this order:

Condition, checked in this order	Text	Key used by the filter
no library row, or an empty `issued_code`	`Not Received Yet`	`not_received_yet`
`outgoing_transmittal_no` is filled	`<next_expected_id> - Not yet submitted by <originator company>`	`not_yet_submitted`
otherwise, `doc_res_name` is filled	`Return to <originator company>`	`return_to_contractor`
otherwise	`<issued_code> - Under <owner company code> review`; when `due_day > 0` also Over Due and `Overdue`	`under_review`

Read it as a life cycle. Under review while the owner has the document. Return to once the approver has signed off but the return transmittal is not out yet. Not yet submitted once it is out, and the ball is with the contractor.

The status named in "Not yet submitted" is not the current status. It is next_expected_id, the Next Expected Submission the approver chose on the sign-off form next to the Return Code (routing_resp.htm refuses the sign-off without both). That is why the KTP rows above read AFC - Not yet submitted by KTP - KSO TIMAS PRATIWI on revisions whose Status Recv is IFA and Status Return is APPROVED: the approver approved the IFA and asked for AFC next. The field is whatever the approver picked. On the KTP snapshot it holds IFR, IFA, AFC, ASB, FI, FINAL, the value 0 on 1,365 rows (the unselected option) and one nan, and the report prints it as is, so 0 - Not yet submitted by ... is a real line.

Two consequences follow. "Not yet submitted" beats "Return to" whenever an outgoing transmittal number exists, even when the Return Code was a rejection. And a document returned without a transmittal number stays at "Return to" for good.

Issued Status flags and Contractor Name

Issued Status: 1 when the document has a routed revision under that status. Contractor Name is the project owner on every row · click to enlarge

Column	Value	Notes
Issued Status, one column per issued status of the project	`1` when the document has at least one library row under that status whose revision has a routing from step 4, else `0`	unlike Status Recv, the flags look back over all revisions. `SNS-C-CC-10-001` prints 1 1 0: IFR and IFA were routed, and its AFC revision `0` exists in the library only as the `0_R` return row, so AFC stays 0
Contractor Name	the company whose `company_code` equals the MDR row's `dm_originator_code`	the MDR query aliases the project owner code as `dm_originator_code`, so every row prints the owner (`KTP - KSO TIMAS PRATIWI`), never the document's originator. Medco fixed this, see Instance differences

The ordinal issue blocks

The ordinal blocks: one set of nine columns per issue the document went through · click to enlarge

To the right of Contractor Name the report repeats a nine-column block per issue: 1st Issue [1], 2nd Issue [2], and so on. The number of blocks is the largest number of issued statuses any printed document reached, so when one document went IFR, IFA, AFC and ASB the print has four blocks for everybody, and a document that reached fewer leaves the rest blank.

For one document the blocks are its issued statuses in the order its revisions were first routed (step 4's order: gate sequence, then rout_id): one block per status, not per revision. The columns:

Column	Source
Status	`issued_code`
Date Received	`doc_send_received`
Trans No.	`doc_submission_number`, the incoming transmittal
Rev	`doc_rev`
Due Date	`doc_due_date`
Date Return	`doc_appdate`, the approver's sign-off date
Over Due	`due_day_return`, days the return was late: `doc_appdate − doc_due_date` on PostgreSQL, `doc_issued_date − doc_due_date` on MySQL, `doc_due_date − doc_appdate` on SQL Server. Blank unless positive
Trans No.	`outgoing_transmittal_no`, the return transmittal
Return Status	`doc_res_name`

A document with several revisions under one issue prints them stacked in the block · click to enlarge

When a status has several library rows (revisions B and B1 under IFA on this screenshot) every cell of the block stacks the values with line breaks, one line per revision, in doc_no order. The main columns keep showing the single routed revision.

Because MySQL computes this Over Due from doc_issued_date, which nothing fills any more, the block's Over Due column is blank on every MySQL instance, KTP included.

Progress

Every number in the Progress column and in the Table Summary is built from one quantity, progress_per_issued: the progress one document earns for one issued status. Three inputs go into it.

Input	Where it comes from	On the KTP project SNS
Weight factor of the document	`ts_documents_master.dm_weight_factor`, filled by Import MDR. The `Weight (%)` column of the report	2.94 for most documents; empty on some
Percentage of the issued status	the progress gate the status hangs on (`ts_progress_gate`, joined by `_getIssuedList()`). Each gate has two numbers, Submission % and Completion %, explained in the next section	IFR 30 / 40, IFA 60 / 75, AFC 95 / 100, ASB 100 / 100, FI 100 / 100 (Submission % / Completion %)
Whether the document reached the status	a library row with that `issued_code` whose revision was routed, see query 5 above

Submission % and Completion %, the two numbers behind a gate

The Progress Gate reference on Jadestone (Setting, Project, Multi Reference, Reference = Progress Gate, project AKT): one row per gate with Sequence, Gate Code, Submission % and Completion % · click to enlarge

The column names on ts_progress_gate are the most confusing part of this report, and the part a developer coming back to INACT after a while trips over first. The screen calls the two numbers Submission % and Completion %. The table calls them submission_progress and percentage. Nothing in the name percentage says which of the two it is, so read it as Completion % every time you meet it in the code.

On the screen	Column	The document earns it when	Written by
Submission %	`submission_progress`	the revision is published to the Document Library at that issued status. That is the moment Document Control officially receives the document for review, which is where the name comes from	`updateMDRProgress('two', ...)` in `tracking_function.inc.php`
Completion %	`percentage`	the routing is closed and its result code is an approval (`ts_doc_resultcode.doc_res_action = approved`). A rejection leaves Submission % standing	`updateMDRProgress('three', ...)` in the same file

Both land in ts_mdr_progress.progress_content, key one, as percentage_progress, with earned_value = weight_factor × percentage_progress / 100. The Progress tab of the MDR displays them, and its Recalculate button replays the same rules from the library.

When step two fires depends on whether the routing has a D - Transmit (Document Control) step:

Routing with a D. The D signs off, routing_handler_doccon_lib_publish.php copies the file into the library, and routing_handler_post.php calls updateMDRProgress('two', ...) right after. submitExtrans() in routing.inc.php, the transmittal-out path that publishes several documents at once, does the same.
Routing without a D. The MDR upload itself publishes the file (documents_master_multiupload_upload.php), and step two runs right there, because no Document Control step will come later.

Step three fires from two places, both meaning "the routing is finished": the approver's final sign-off, straight after the approval mail goes out (routing_handler_post.php), and the generation of the return transmittal. updateMDRProgressThree looks up the routing's result code, and only approved swaps Submission % for Completion %.

So on KTP project SNS an IFA revision is worth 60 the day Document Control publishes it and 75 the day the approver closes it with an approving code. On Jadestone the two columns are equal on every gate (50 / 50, 70 / 70, 100 / 100), so the difference never shows there.

The rule, per document and per issued status, in gate order:

if the document never reached the status:      progress_per_issued = 0
else:
    pct = Submission % of the gate   (master: always, because of the missing query)
    pct = Completion % of the gate   (KTP: when that issue's revision was approved)
    progress_per_issued = weight_factor × pct / 100

Then the same value is added in three places, provided the issue's Actual Date falls inside the summary window (no window: always):

the document's Progress (%), the sum over its statuses;
the discipline's <issued> (%) cell in the Table Summary, the sum over the discipline's documents;
the Total row of the Table Summary, the sum over the project.

Worked example, three Civil documents on KTP

Document	Weight	Reached	IFR	IFA	AFC	ASB	FI	Progress (%)
`SNS-C-CC-10-001`	2.94	IFR approved, IFA approved. The AFC revision `0` was routed but its only library row is the return `0_R`, so AFC does not count	2.94 × 40% = 1.18	2.94 × 75% = 2.21	0	0	0	3.38
`SNS-C-CC-10-002`	2.94	IFR approved, IFA approved, AFC approved on `0R`	2.94 × 40% = 1.18	2.94 × 75% = 2.21	2.94 × 100% = 2.94	0	0	6.32
`SNS-C-CC-10-023`	empty	never received	0	0	0	0	0	blank
Summary row for these three		Total Doc Submitted 3	2.35	4.41	2.94

The two numbers in bold under Progress are the ones on the KTP screenshot, 3.38 and 6.32. Master would print 2.65 and 5.44 for the same rows, because it always takes Submission % (30, 60, 95). The third document still counts as 1 in Total Doc Submitted although it has never been received; that column counts MDR rows, not submissions.

Read the whole discipline the same way: C - Civil on KTP has Total Doc Submitted 73, IFR 39.98, IFA 79.38, AFC 101.58. Each of those cells is the column sum of progress_per_issued over the 73 documents; IFA is bigger than IFR because the IFA percentage is bigger, and AFC is bigger again because most Civil documents reached it at 100%. Nothing is divided by anything: the cells are sums of weight × percentage, so they grow with the number of documents and pass 100 whenever the weights add up to more than 100.

Two things follow that the team keeps rediscovering:

A status counts once per document, however many revisions were issued under it. Three IFA revisions still give one IFA contribution.
Statuses not reached contribute nothing in master. KTP's copy instead carries the previous status's value into the empty cells of the summary, which is why its ASB column equals its AFC column; master leaves ASB blank until a document is actually issued As Built.

Group totals

The Total, discipline and document-type rows carry the document count, the weight sum and the progress sum · click to enlarge

Every group row (discipline, document type, the Total row) shows the count of documents, the sum of their weight factors and the sum of their Progress (%). The counts are of MDR rows that survived the filters, whether or not they were ever received.

The Table Summary

The left-hand table, titled Table Summary (Actual Date from <min> to <max>). The dates are the form's window, or, when empty, the earliest and latest doc_send_received among the printed documents.

Column	Value
Discipline	one row per discipline that has MDR rows
Total Doc Submitted	the number of MDR rows in the discipline, received or not. The name is misleading; `Not Received Yet` documents are in it
`<issued> (%)`, one per issued status	Σ `progress_per_issued` of that status over the discipline's documents, inside the window. Blank when 0
Total	the same sums over the project

Because each cell is a sum of weight × percentage, the columns read as earned progress points per status, and they can exceed 100 when the weights are large or many documents passed the status. A project whose MDR has no weight factors shows an empty summary: every product is 0 and 0 prints blank. That is why the Jadestone screenshot of this report has a full document list and an empty summary.

The window

The actual_date_from / actual_date_to fields do not filter the document list; they decide which issues are counted in the sums. The Actual Date of an issue is the first doc_send_received among that status's rows. An issue outside the window still shows its 1 in the Issued Status flags and its dates in the ordinal blocks, but adds nothing to Progress (%) or to the summary. Set the window to a month to get that month's earned progress per discipline.

Known issues

Progress never uses Completion % in master (the missing ts_mdr_progress query above). Port the KTP query or drop the branch; as it stands the report and the Progress tab of the MDR disagree.
Over Due Status filter: fixed on master and Jadestone, still broken elsewhere, and selecting the wrong rows everywhere. The hard-coded DATEDIFF(day, CURRENT_TIMESTAMP, d.doc_due_date) errored on MySQL and PostgreSQL; INA01-852 (master, 2026-09-07) and INA27-302 (Jadestone) branch it per driver, JOTRE and Timas (MySQL) still have the error, Medco (SQL Server) never had it. But on every driver the filter computes doc_due_date − today and Equals keeps rows where that is positive, so "Over Due Status equals" returns the documents still inside their review time. That matches the reversed Over Due column on SQL Server and contradicts the correct column on MySQL and PostgreSQL.
Over Due is reversed on SQL Server. Both due_day and due_day_return have their arguments the wrong way round on the sqlsrv branch. The main Over Due column counts days until the due date and stamps Overdue on documents still inside their review time, and the Over Due inside the issue blocks shows early returns instead of late ones. Medco runs on SQL Server.
Date Returned to Contractor is always empty, and on MySQL so is Over Due inside the issue blocks: both read doc_issued_date, which the live return paths never write (KTP: 0 of 7,299 rows). doc_appdate, or master's doc_returned_date, is the column that holds the return date.
Document Status Flow prints next_expected_id raw, so an approver who left Next Expected Submission unselected produces 0 - Not yet submitted by ... (1,365 rows on KTP).
Plan Date is always empty. The MDR query selects six columns and dm_plan_date_all_issued is not one of them.
A library row without a routing is invisible to the Issued Status flags, the issue blocks and progress. Documents received by transmittal but never routed print as Not Received Yet with Issued Status all 0.
Routings that left every inbox are ignored (step 3), so a document whose only routing was fully processed falls back to an older routing or to none.
Contractor Name is the project owner for every row, not the document's originator.
The header label "MDR Report Sumary" in the menu is a seed typo; this report's own title is fine.

Instance differences

Checked on 2026-09-06 by diffing the handler.

Instance	Difference
KTP (`ina-ktp`, 340 lines differ)	The `ts_mdr_progress` query is present, so approved issues use the gate's Completion %. For a status the document has not reached, the summary cell carries the previous status's value forward (`$progress_summary_from_prev_issued_code`), which makes the summary cumulative. The form's field values are the old `doc_number` / `doc_title` names and a `Remarks (MDR)` filter exists. Contractor Name is the project owner, as in master.
Jadestone (68 lines)	Older form (`docmasterMakeFieldCol`), no template-column filters; the transmittal columns are "Outgoing Transmittal" and "Return Transmittal" instead of "Incoming" and "Outgoing". Reads `ts_mdr_progress` for the Plan Date column only, so progress still scores every issue with Submission %. Three extra columns after Date Received from Contractor: Plan Date, Status and Next Expected Submission, see above. Weight factors exist on 27 of the 55 AKT rows only (snapshot 2026-09-07); AAL and AAL-VS have none, so their summaries print blank. INA27-302 (Done): the Over Due Status filter runs on MySQL, see Known issues. Its Report heading reads "Project".
JOTRE (101 lines)	Reads `dm_phase` and derives a sequence number from the document number. Same missing query as master.
Timas (111 lines)	A `condition=B` mode groups by discipline and package, and eleven extra MDR filter fields (`dm_pic_document`, `dm_wp_no`, `dm_package_number`, ...). Same missing query.
Prima Energy (78 lines)	The older form (`docmasterMakeFieldCol`, no template-column filters) and the unbranched Over Due Status filter, so that filter errors on this MySQL instance. Checked by diff on 2026-09-08, not traced.
Medco (8 lines)	Contractor Name comes from `dm_originator_code` on the MDR row, the real originator, instead of the project owner.

DMS reports: the other entries and how they are dispatched.
MDR Report Summary: the count-based companion.
Import MDR: where weight factors and disciplines come from.
Upload: what creates the library rows and routings this report reads.

Master Doc. Register

Document Routing

DMS

MDR Report

The filter form

The five queries, in order