|
SELECT
|
|
|
|
/* ==========================
|
|
IMPORT BE DETAILS
|
|
========================== */
|
|
|
|
boe.name AS "Import BE ID:Link/FTWZ BOE Import",
|
|
|
|
boe.transaction_id AS "Import BE No:Data",
|
|
|
|
DATE_FORMAT(
|
|
boe.transaction_date,
|
|
'%%d-%%m-%%Y'
|
|
) AS "Import BE Date:Data",
|
|
|
|
/* ==========================
|
|
BOE SHIPMENT DETAILS
|
|
========================== */
|
|
|
|
GROUP_CONCAT(
|
|
DISTINCT boe_ship.hawb_hbl_no
|
|
ORDER BY boe_ship.hawb_hbl_no
|
|
SEPARATOR ', '
|
|
) AS "HAWB / HBL No.:Data",
|
|
|
|
GROUP_CONCAT(
|
|
DISTINCT boe_ship.mawb_mbl_no
|
|
ORDER BY boe_ship.mawb_mbl_no
|
|
SEPARATOR ', '
|
|
) AS "MAWB / MBL No.:Data",
|
|
|
|
GROUP_CONCAT(
|
|
DISTINCT DATE_FORMAT(
|
|
boe_ship.mawb_mbl_date,
|
|
'%%d-%%m-%%Y'
|
|
)
|
|
ORDER BY boe_ship.mawb_mbl_date
|
|
SEPARATOR ', '
|
|
) AS "MAWB / MBL Date:Data",
|
|
|
|
GROUP_CONCAT(
|
|
DISTINCT DATE_FORMAT(
|
|
boe_ship.hawb_hbl_date,
|
|
'%%d-%%m-%%Y'
|
|
)
|
|
ORDER BY boe_ship.hawb_hbl_date
|
|
SEPARATOR ', '
|
|
) AS "HAWB / HBL Date:Data",
|
|
|
|
GROUP_CONCAT(
|
|
DISTINCT DATE_FORMAT(
|
|
boe_ship.igm_date,
|
|
'%%d-%%m-%%Y'
|
|
)
|
|
ORDER BY boe_ship.igm_date
|
|
SEPARATOR ', '
|
|
) AS "IGM Date (Import BE):Data",
|
|
|
|
DATE_FORMAT(
|
|
boe.request_out_of_charge_date,
|
|
'%%d-%%m-%%Y'
|
|
) AS "Request Out Of Charge Date (Import BE):Data",
|
|
|
|
DATE_FORMAT(
|
|
boe.request_assessment_date,
|
|
'%%d-%%m-%%Y'
|
|
) AS "Request Assessment Date (Import BE):Data",
|
|
|
|
DATE_FORMAT(
|
|
boe.request_submission_date,
|
|
'%%d-%%m-%%Y'
|
|
) AS "Request Submission Date (Import BE):Data",
|
|
|
|
/* ==========================
|
|
HOUSE BE DETAILS
|
|
========================== */
|
|
|
|
dta_summary.house_be_no
|
|
AS "House BE No:Data",
|
|
|
|
dta_summary.house_be_date
|
|
AS "House BE Date:Data",
|
|
|
|
dta_summary.house_request_out_of_charge
|
|
AS "Request Out Of Charge Date (House BE):Data",
|
|
|
|
dta_summary.house_request_assessment
|
|
AS "Request Assessment Date (House BE):Data",
|
|
|
|
dta_summary.house_request_submission
|
|
AS "Request Submission Date (House BE):Data"
|
|
|
|
FROM `tabFTWZ BOE Import` boe
|
|
|
|
/* ===========================================
|
|
BOE SHIPMENT
|
|
=========================================== */
|
|
|
|
LEFT JOIN `tabBOE Import Shipment` boe_ship
|
|
ON boe_ship.parent = boe.name
|
|
|
|
|
|
/* ===========================================
|
|
DTA SUMMARY (ONE ROW PER HAWB)
|
|
=========================================== */
|
|
|
|
LEFT JOIN (
|
|
|
|
SELECT
|
|
|
|
ds.hawb_hbl_no,
|
|
|
|
/* House BE No */
|
|
|
|
GROUP_CONCAT(
|
|
DISTINCT d.name
|
|
ORDER BY d.name
|
|
SEPARATOR ', '
|
|
) AS house_be_no,
|
|
|
|
/* House BE Date */
|
|
|
|
GROUP_CONCAT(
|
|
DISTINCT DATE_FORMAT(
|
|
d.transaction_date,
|
|
'%%d-%%m-%%Y'
|
|
)
|
|
ORDER BY d.transaction_date
|
|
SEPARATOR ', '
|
|
) AS house_be_date,
|
|
|
|
/* Request Out Of Charge */
|
|
|
|
GROUP_CONCAT(
|
|
DISTINCT DATE_FORMAT(
|
|
d.request_out_of_charge_date,
|
|
'%%d-%%m-%%Y'
|
|
)
|
|
ORDER BY d.request_out_of_charge_date
|
|
SEPARATOR ', '
|
|
) AS house_request_out_of_charge,
|
|
|
|
/* Request Assessment */
|
|
|
|
GROUP_CONCAT(
|
|
DISTINCT DATE_FORMAT(
|
|
d.request_assessment_date,
|
|
'%%d-%%m-%%Y'
|
|
)
|
|
ORDER BY d.request_assessment_date
|
|
SEPARATOR ', '
|
|
) AS house_request_assessment,
|
|
|
|
/* Request Submission */
|
|
|
|
GROUP_CONCAT(
|
|
DISTINCT DATE_FORMAT(
|
|
d.request_submission_date,
|
|
'%%d-%%m-%%Y'
|
|
)
|
|
ORDER BY d.request_submission_date
|
|
SEPARATOR ', '
|
|
) AS house_request_submission
|
|
|
|
FROM (
|
|
|
|
/* Remove duplicate shipment rows */
|
|
|
|
SELECT DISTINCT
|
|
parent,
|
|
hawb_hbl_no
|
|
FROM `tabDTA Sale Shipments`
|
|
|
|
) ds
|
|
|
|
INNER JOIN `tabFTWZ DTA Sale` d
|
|
ON d.name = ds.parent
|
|
|
|
GROUP BY
|
|
ds.hawb_hbl_no
|
|
|
|
) dta_summary
|
|
|
|
ON dta_summary.hawb_hbl_no = boe_ship.hawb_hbl_no
|
|
|
|
/* ==========================================
|
|
GROUP BY
|
|
========================================== */
|
|
|
|
GROUP BY
|
|
boe.name,
|
|
boe.transaction_id,
|
|
boe.transaction_date,
|
|
boe.request_out_of_charge_date,
|
|
boe.request_assessment_date,
|
|
boe.request_submission_date
|
|
|
|
|
|
/* ==========================================
|
|
ORDER BY
|
|
========================================== */
|
|
|
|
ORDER BY
|
|
boe.transaction_id,
|
|
boe.name;
|