SELECT /*+ Ordered */ 
     'CWP_MP' AS "[Version].[Version Name]"
     , SHIP.TOSITEID AS "[Location].[Location]"
     , SHIP.FROMSITEID AS "[Sub Location].[Sub Location]"
     , SHIP.ITEM AS "[Item].[Item]"
     , TO_CHAR(SHIP.BILLINGDATE, 'YYYY-MM-DD') AS "[Time].[Day]"

, DENSE_RANK() OVER (
           PARTITION BY SHIP.TOSITEID
                      , SHIP.FROMSITEID
                      , SHIP.ITEM
                      , TRUNC(SHIP.BILLINGDATE)
           ORDER BY SHIP.BILLINGDOC
                  , SHIP.BILLINGITEM
                  , SHIP.CONTAINERNO
       ) AS "[Seq].[Seq]"

     , SHIP.BILLINGQTY AS "Actual Shipment_Ship Qty"
     , ROUND(SHIP.USDAMOUNT, 2) AS "Actual Shipment_Amount(USD)"
     , ROUND(SHIP.KRWAMOUNT, 0) AS "Actual Shipment_Amount(KRW)"
     , ROUND(SHIP.BILLINGAMOUNT, 2) AS "Actual Shipment_Ship Amount"
     , SHIP.BILLINGCURRENCY AS "Actual Shipment_Ship Currency"
     , SHIP.QTYUOM AS "Actual Shipment_UOM"

     , TO_CHAR(SHIP.BILLINGDATE, 'YYYY-MM-DD') AS "Actual Shipment_Ship Date"
     , TO_CHAR(SHIP.ENTRYDATE, 'YYYY-MM-DD') AS "Actual Shipment_Entry Date"
     , TRUNC(SHIP.ENTRYDATE) - TRUNC(SHIP.BILLINGDATE) AS "Actual Shipment_Late Day"

     , TO_CHAR(SHIP.BILLINGDATE, 'IYYYIW') AS "Actual Shipment_Ship Week"
     , TO_CHAR(SHIP.ENTRYDATE, 'IYYYIW') AS "Actual Shipment_Entry Week"
     , (TRUNC(SHIP.ENTRYDATE, 'IW') - TRUNC(SHIP.BILLINGDATE, 'IW')) / 7 AS "Actual Shipment_Late Week"

     , SHIP.EVENTTYPE AS "Actual Shipment_Event Type"
     , SHIP.PONO AS "Actual Shipment_PO No"
     , SHIP.POITEM AS "Actual Shipment_PO Item"
     , SHIP.INVOICENO AS "Actual Shipment_Invoice No"
     , SHIP.INVOICEITEM AS "Actual Shipment_Invoice Item"

     , SHIP.VESSELNAME AS "Actual Shipment_Vessel Name"
     , SHIP.CONTAINERNO AS "Actual Shipment_Container No"
     , SHIP.TRANSPORTMODE AS "Actual Shipment_TP Mode"
     , SHIP.BODNAME AS "Actual Shipment_BOD"

     , TO_CHAR(SHIP.SCMETA, 'YYYY-MM-DD') AS "Actual Shipment_SCM ETA"
     , TO_CHAR(SHIP.ETD, 'YYYY-MM-DD') AS "Actual Shipment_ETD"
     , TO_CHAR(SHIP.ETA, 'YYYY-MM-DD') AS "Actual Shipment_ETA"

     , SHIP.FINALDEST AS "Actual Shipment_Final Dest"
     , SHIP.SOLDTONAME AS "Actual Shipment_Sold To Name"
     , SHIP.SOLDTOPARTY AS "Actual Shipment_Sold To Party"

     , SHIP.PLANT AS "Actual Shipment_Plant"
     , SHIP.STORAGELOC AS "Actual Shipment_Storage Location"
     , SHIP.CUSTOMERPOQTY AS "Actual Shipment_Customer PO Qty"

     , TO_CHAR(SHIP.CUSTOMERPODATE, 'YYYY-MM-DD') AS "Actual Shipment_Sold To Date"
     , TO_CHAR(SHIP.CUSTOMERPOREQ, 'YYYY-MM-DD') AS "Actual Shipment_Customer PO Date"
     , TO_CHAR(SHIP.ACTUALDELIVERYDATE, 'YYYY-MM-DD') AS "Actual Shipment_Actual Delivery Date"
     , TO_CHAR(SHIP.PICKABLEDATE, 'YYYY-MM-DD') AS "Actual Shipment_Pickable Date"
     , TO_CHAR(SHIP.GIDATE, 'YYYY-MM-DD') AS "Actual Shipment_GI Date"

     , SHIP.FROMOBJECTID AS "Actual Shipment_From Object"
     , SHIP.TOOBJECTID AS "Actual Shipment_To Object"
     , SHIP.INCOTERMS1 AS "Actual Shipment_Incoterms1"
     , SHIP.INCOTERMS2 AS "Actual Shipment_Incoterms2"
     , SHIP.DISTCHANNEL AS "Actual Shipment_Dist Channel"

     , SHIP.SONO AS "Actual Shipment_SO No"
     , SHIP.SOITEM AS "Actual Shipment_SO Item"
     , SHIP.SALESORG AS "Actual Shipment_Sales Org"
     , SHIP.LCNO AS "Actual Shipment_LC No"
     , SHIP.BLNO AS "Actual Shipment_BL No"

     , SHIP.CONTAINERSIZE AS "Actual Shipment_Container Size"
     , SHIP.BILLINGDOC AS "Actual Shipment_Billing Doc"
     , SHIP.BILLINGITEM AS "Actual Shipment_Billing Item"

     , NVL(TO_CHAR(SHIP.UPDTTM, 'YYYY-MM-DD HH24:MI:SS'), '-') AS "Actual Shipment_Update Time"

FROM   VDY_SHIPMENT SHIP

WHERE  SHIP.ITEM = ATTB.ITEM
AND    SHIP.TOSITEID = TOSITE.SITEID
AND    SHIP.FROMSITEID = MF.SITEID(+)
AND    SHIP.TOSITEID = SITEGROUP.SITEID

-- 2026-09-18부터 2026-09-21까지 전체 데이터
AND    SHIP.BILLINGDATE >= TO_DATE('20260901', 'YYYYMMDD')
AND    SHIP.BILLINGDATE <  TO_DATE('20261231', 'YYYYMMDD')

AND    SHIP.EVENTTYPE IN ('IT', 'MB', 'SO', 'MS', 'REQ')

ORDER BY
       TRUNC(SHIP.BILLINGDATE)
     , "[Seq].[Seq]"
     , SHIP.PONO
     , SHIP.POITEM
     , SHIP.INVOICENO
     , SHIP.INVOICEITEM
     , SHIP.TOSITEID
     , SHIP.FROMSITEID
     , SHIP.ITEM;