주문-수금 판매 주문 처리 데이터 템플릿

WITH BaseOrders AS (
    SELECT
        dhead.HEADER_ID,
        dhead.ORDER_NUMBER AS SalesOrder,
        dhead.CREATION_DATE,
        dhead.CREATED_BY,
        (SELECT u.USERNAME FROM PER_USERS u WHERE u.USER_GUID = dhead.CREATED_BY AND ROWNUM = 1) AS UserName,
        dhead.SUBMITTING_BU_ID,
        dhead.AMOUNT AS SalesOrderTotalAmount,
        hp_sold.PARTY_NAME AS CustomerName,
        dhead.SALES_CHANNEL_CODE AS SalesChannel,
        dfl.REQUEST_SHIP_DATE AS RequestedDeliveryDate
    FROM
        DOO_HEADERS_ALL dhead
    JOIN
        DOO_FULFILL_LINES_ALL dfl ON dhead.HEADER_ID = dfl.HEADER_ID
    JOIN
        HZ_CUST_ACCOUNTS hc_sold ON dhead.SOLD_TO_CUSTOMER_ID = hc_sold.CUST_ACCOUNT_ID
    JOIN
        HZ_PARTIES hp_sold ON hc_sold.PARTY_ID = hp_sold.PARTY_ID
    WHERE
        dhead.OBJECT_VERSION_NUMBER = 1
        AND dfl.LINE_NUMBER = 1 -- To avoid duplicating header-level events for each line
        AND dhead.CREATION_DATE BETWEEN TO_DATE(:p_start_date, 'YYYY-MM-DD') AND TO_DATE(:p_end_date, 'YYYY-MM-DD')
)
-- 1. Sales Order Created
SELECT
    bo.SalesOrder,
    'Sales Order Created' AS ActivityName,
    bo.CREATION_DATE AS EventTime,
    bo.UserName,
    bo.SalesOrderTotalAmount,
    bo.CustomerName,
    bo.SalesChannel,
    bo.RequestedDeliveryDate,
    NULL AS ActualDeliveryDate,
    NULL AS PaymentDueDate,
    CASE WHEN bo.CREATED_BY LIKE 'FUSION_APPS%' THEN 'true' ELSE 'false' END AS IsAutomated
FROM BaseOrders bo

UNION ALL

-- 2. Credit Check Performed (inferred from Credit Hold Release)
SELECT
    bo.SalesOrder,
    'Credit Check Performed' AS ActivityName,
    dha.RELEASED_DATE AS EventTime,
    (SELECT u.USERNAME FROM PER_USERS u WHERE u.USER_GUID = dha.RELEASED_BY AND ROWNUM = 1) AS UserName,
    bo.SalesOrderTotalAmount,
    bo.CustomerName,
    bo.SalesChannel,
    bo.RequestedDeliveryDate,
    NULL AS ActualDeliveryDate,
    NULL AS PaymentDueDate,
    CASE WHEN dha.RELEASED_BY LIKE 'FUSION_APPS%' THEN 'true' ELSE 'false' END AS IsAutomated
FROM DOO_HOLDS_ALL dha
JOIN BaseOrders bo ON dha.HEADER_ID = bo.HEADER_ID
WHERE dha.HOLD_CODE = '[Your Credit Check Hold Code]' AND dha.RELEASED_FLAG = 'Y' AND dha.RELEASED_DATE IS NOT NULL

UNION ALL

-- 3. Credit Hold Applied
SELECT
    bo.SalesOrder,
    'Credit Hold Applied' AS ActivityName,
    dha.APPLIED_DATE AS EventTime,
    (SELECT u.USERNAME FROM PER_USERS u WHERE u.USER_GUID = dha.APPLIED_BY AND ROWNUM = 1) AS UserName,
    bo.SalesOrderTotalAmount,
    bo.CustomerName,
    bo.SalesChannel,
    bo.RequestedDeliveryDate,
    NULL AS ActualDeliveryDate,
    NULL AS PaymentDueDate,
    CASE WHEN dha.APPLIED_BY LIKE 'FUSION_APPS%' THEN 'true' ELSE 'false' END AS IsAutomated
FROM DOO_HOLDS_ALL dha
JOIN BaseOrders bo ON dha.HEADER_ID = bo.HEADER_ID
WHERE dha.HOLD_CODE = '[Your Credit Check Hold Code]' AND dha.APPLIED_DATE IS NOT NULL

UNION ALL

-- 4. Order Confirmed (inferred from status 'Awaiting Shipping')
SELECT
    bo.SalesOrder,
    'Order Confirmed' AS ActivityName,
    dfl.STATUS_CHANGE_DATE AS EventTime,
    (SELECT u.USERNAME FROM PER_USERS u WHERE u.USER_GUID = dfl.LAST_UPDATED_BY AND ROWNUM = 1) AS UserName,
    bo.SalesOrderTotalAmount,
    bo.CustomerName,
    bo.SalesChannel,
    bo.RequestedDeliveryDate,
    NULL AS ActualDeliveryDate,
    NULL AS PaymentDueDate,
    CASE WHEN dfl.LAST_UPDATED_BY LIKE 'FUSION_APPS%' THEN 'true' ELSE 'false' END AS IsAutomated
FROM DOO_FULFILL_LINES_ALL dfl
JOIN BaseOrders bo ON dfl.HEADER_ID = bo.HEADER_ID
WHERE dfl.STATUS_CODE = 'AWAIT_SHIP'

UNION ALL

-- 5. Inventory Reserved
SELECT 
    bo.SalesOrder,
    'Inventory Reserved' AS ActivityName,
    irl.LAST_UPDATE_DATE AS EventTime,
    (SELECT u.USERNAME FROM PER_USERS u WHERE u.USER_GUID = irl.LAST_UPDATED_BY AND ROWNUM = 1) AS UserName,
    bo.SalesOrderTotalAmount,
    bo.CustomerName,
    bo.SalesChannel,
    bo.RequestedDeliveryDate,
    NULL AS ActualDeliveryDate,
    NULL AS PaymentDueDate,
    CASE WHEN irl.LAST_UPDATED_BY LIKE 'FUSION_APPS%' THEN 'true' ELSE 'false' END AS IsAutomated
FROM INV_RESERVATIONS irl
JOIN DOO_FULFILL_LINES_ALL dfl ON irl.DEMAND_SOURCE_LINE_ID = dfl.FULFILL_LINE_ID
JOIN BaseOrders bo ON dfl.HEADER_ID = bo.HEADER_ID
WHERE irl.DEMAND_SOURCE_TYPE_ID = 2 -- Order Entry

UNION ALL

-- 6. Goods Picked (inferred from delivery detail status 'Staged')
SELECT
    bo.SalesOrder,
    'Goods Picked' AS ActivityName,
    wdd.LAST_UPDATE_DATE AS EventTime,
    (SELECT u.USERNAME FROM PER_USERS u WHERE u.USER_GUID = wdd.LAST_UPDATED_BY AND ROWNUM = 1) AS UserName,
    bo.SalesOrderTotalAmount,
    bo.CustomerName,
    bo.SalesChannel,
    bo.RequestedDeliveryDate,
    NULL AS ActualDeliveryDate,
    NULL AS PaymentDueDate,
    CASE WHEN wdd.LAST_UPDATED_BY LIKE 'FUSION_APPS%' THEN 'true' ELSE 'false' END AS IsAutomated
FROM WSH_DELIVERY_DETAILS wdd
JOIN BaseOrders bo ON wdd.SOURCE_HEADER_NUMBER = bo.SalesOrder
WHERE wdd.RELEASED_STATUS = 'S' -- 'S' typically means Staged/Picked

UNION ALL

-- 7. Goods Shipped
SELECT
    bo.SalesOrder,
    'Goods Shipped' AS ActivityName,
    wnd.INITIAL_PICKUP_DATE AS EventTime,
    (SELECT u.USERNAME FROM PER_USERS u WHERE u.USER_GUID = wnd.LAST_UPDATED_BY AND ROWNUM = 1) AS UserName,
    bo.SalesOrderTotalAmount,
    bo.CustomerName,
    bo.SalesChannel,
    bo.RequestedDeliveryDate,
    NULL AS ActualDeliveryDate,
    NULL AS PaymentDueDate,
    CASE WHEN wnd.LAST_UPDATED_BY LIKE 'FUSION_APPS%' THEN 'true' ELSE 'false' END AS IsAutomated
FROM WSH_NEW_DELIVERIES wnd
JOIN WSH_DELIVERY_ASSIGNMENTS wda ON wnd.DELIVERY_ID = wda.DELIVERY_ID
JOIN WSH_DELIVERY_DETAILS wdd ON wda.DELIVERY_DETAIL_ID = wdd.DELIVERY_DETAIL_ID
JOIN BaseOrders bo ON wdd.SOURCE_HEADER_NUMBER = bo.SalesOrder
WHERE wnd.STATUS_CODE = 'CL' -- Closed/Shipped

UNION ALL

-- 8. Goods Delivered
SELECT
    bo.SalesOrder,
    'Goods Delivered' AS ActivityName,
    wnd.ULTIMATE_DROPOFF_DATE AS EventTime,
    (SELECT u.USERNAME FROM PER_USERS u WHERE u.USER_GUID = wnd.LAST_UPDATED_BY AND ROWNUM = 1) AS UserName,
    bo.SalesOrderTotalAmount,
    bo.CustomerName,
    bo.SalesChannel,
    bo.RequestedDeliveryDate,
    wnd.ULTIMATE_DROPOFF_DATE AS ActualDeliveryDate,
    NULL AS PaymentDueDate,
    CASE WHEN wnd.LAST_UPDATED_BY LIKE 'FUSION_APPS%' THEN 'true' ELSE 'false' END AS IsAutomated
FROM WSH_NEW_DELIVERIES wnd
JOIN WSH_DELIVERY_ASSIGNMENTS wda ON wnd.DELIVERY_ID = wda.DELIVERY_ID
JOIN WSH_DELIVERY_DETAILS wdd ON wda.DELIVERY_DETAIL_ID = wdd.DELIVERY_DETAIL_ID
JOIN BaseOrders bo ON wdd.SOURCE_HEADER_NUMBER = bo.SalesOrder
WHERE wnd.ULTIMATE_DROPOFF_DATE IS NOT NULL

UNION ALL

-- 9. Invoice Created
SELECT
    bo.SalesOrder,
    'Invoice Created' AS ActivityName,
    rct.TRX_DATE AS EventTime,
    (SELECT u.USERNAME FROM PER_USERS u WHERE u.USER_GUID = rct.CREATED_BY AND ROWNUM = 1) AS UserName,
    bo.SalesOrderTotalAmount,
    bo.CustomerName,
    bo.SalesChannel,
    bo.RequestedDeliveryDate,
    NULL AS ActualDeliveryDate,
    aps.DUE_DATE AS PaymentDueDate,
    CASE WHEN rct.CREATED_BY LIKE 'FUSION_APPS%' THEN 'true' ELSE 'false' END AS IsAutomated
FROM RA_CUSTOMER_TRX_ALL rct
JOIN RA_CUSTOMER_TRX_LINES_ALL rctl ON rct.CUSTOMER_TRX_ID = rctl.CUSTOMER_TRX_ID
JOIN AR_PAYMENT_SCHEDULES_ALL aps ON rct.CUSTOMER_TRX_ID = aps.CUSTOMER_TRX_ID
JOIN BaseOrders bo ON rctl.INTERFACE_LINE_ATTRIBUTE1 = bo.SalesOrder
WHERE rctl.INTERFACE_LINE_CONTEXT = 'ORDER ENTRY' AND rctl.LINE_TYPE = 'LINE'

UNION ALL

-- 10. Invoice Corrected (Credit Memo)
SELECT
    bo.SalesOrder,
    'Invoice Corrected' AS ActivityName,
    rct_cm.TRX_DATE AS EventTime,
    (SELECT u.USERNAME FROM PER_USERS u WHERE u.USER_GUID = rct_cm.CREATED_BY AND ROWNUM = 1) AS UserName,
    bo.SalesOrderTotalAmount,
    bo.CustomerName,
    bo.SalesChannel,
    bo.RequestedDeliveryDate,
    NULL AS ActualDeliveryDate,
    NULL AS PaymentDueDate,
    CASE WHEN rct_cm.CREATED_BY LIKE 'FUSION_APPS%' THEN 'true' ELSE 'false' END AS IsAutomated
FROM RA_CUSTOMER_TRX_ALL rct_cm
JOIN RA_CUSTOMER_TRX_LINES_ALL rctl_cm ON rct_cm.CUSTOMER_TRX_ID = rctl_cm.CUSTOMER_TRX_ID
JOIN RA_CUSTOMER_TRX_ALL rct_orig ON rct_cm.PREVIOUS_CUSTOMER_TRX_ID = rct_orig.CUSTOMER_TRX_ID
JOIN RA_CUSTOMER_TRX_LINES_ALL rctl_orig ON rct_orig.CUSTOMER_TRX_ID = rctl_orig.CUSTOMER_TRX_ID
JOIN BaseOrders bo ON rctl_orig.INTERFACE_LINE_ATTRIBUTE1 = bo.SalesOrder
WHERE rctl_orig.INTERFACE_LINE_CONTEXT = 'ORDER ENTRY' AND rctl_cm.LINE_TYPE = 'LINE'

UNION ALL

-- 11. Payment Received
SELECT
    bo.SalesOrder,
    'Payment Received' AS ActivityName,
    araa.APPLY_DATE AS EventTime,
    (SELECT u.USERNAME FROM PER_USERS u WHERE u.USER_GUID = araa.CREATED_BY AND ROWNUM = 1) AS UserName,
    bo.SalesOrderTotalAmount,
    bo.CustomerName,
    bo.SalesChannel,
    bo.RequestedDeliveryDate,
    NULL AS ActualDeliveryDate,
    NULL AS PaymentDueDate,
    CASE WHEN araa.CREATED_BY LIKE 'FUSION_APPS%' THEN 'true' ELSE 'false' END AS IsAutomated
FROM AR_RECEIVABLE_APPLICATIONS_ALL araa
JOIN RA_CUSTOMER_TRX_ALL rct ON araa.APPLIED_CUSTOMER_TRX_ID = rct.CUSTOMER_TRX_ID
JOIN RA_CUSTOMER_TRX_LINES_ALL rctl ON rct.CUSTOMER_TRX_ID = rctl.CUSTOMER_TRX_ID
JOIN BaseOrders bo ON rctl.INTERFACE_LINE_ATTRIBUTE1 = bo.SalesOrder
WHERE araa.STATUS = 'APP' AND rctl.INTERFACE_LINE_CONTEXT = 'ORDER ENTRY' AND rctl.LINE_TYPE = 'LINE'

UNION ALL

-- 12. Order Line Closed
SELECT
    bo.SalesOrder,
    'Order Line Closed' AS ActivityName,
    dfl.LAST_UPDATE_DATE AS EventTime,
    (SELECT u.USERNAME FROM PER_USERS u WHERE u.USER_GUID = dfl.LAST_UPDATED_BY AND ROWNUM = 1) AS UserName,
    bo.SalesOrderTotalAmount,
    bo.CustomerName,
    bo.SalesChannel,
    bo.RequestedDeliveryDate,
    NULL AS ActualDeliveryDate,
    NULL AS PaymentDueDate,
    CASE WHEN dfl.LAST_UPDATED_BY LIKE 'FUSION_APPS%' THEN 'true' ELSE 'false' END AS IsAutomated
FROM DOO_FULFILL_LINES_ALL dfl
JOIN BaseOrders bo ON dfl.HEADER_ID = bo.HEADER_ID
WHERE dfl.STATUS_CODE = 'CLOSED'

UNION ALL

-- 13. Order Closed
SELECT
    bo.SalesOrder,
    'Order Closed' AS ActivityName,
    dhead.LAST_UPDATE_DATE AS EventTime,
    (SELECT u.USERNAME FROM PER_USERS u WHERE u.USER_GUID = dhead.LAST_UPDATED_BY AND ROWNUM = 1) AS UserName,
    bo.SalesOrderTotalAmount,
    bo.CustomerName,
    bo.SalesChannel,
    bo.RequestedDeliveryDate,
    NULL AS ActualDeliveryDate,
    NULL AS PaymentDueDate,
    CASE WHEN dhead.LAST_UPDATED_BY LIKE 'FUSION_APPS%' THEN 'true' ELSE 'false' END AS IsAutomated
FROM DOO_HEADERS_ALL dhead
JOIN BaseOrders bo ON dhead.HEADER_ID = bo.HEADER_ID
WHERE dhead.STATUS_CODE = 'CLOSED'

UNION ALL

-- 14. Order Cancelled
SELECT
    bo.SalesOrder,
    'Order Cancelled' AS ActivityName,
    dhead.LAST_UPDATE_DATE AS EventTime,
    (SELECT u.USERNAME FROM PER_USERS u WHERE u.USER_GUID = dhead.LAST_UPDATED_BY AND ROWNUM = 1) AS UserName,
    bo.SalesOrderTotalAmount,
    bo.CustomerName,
    bo.SalesChannel,
    bo.RequestedDeliveryDate,
    NULL AS ActualDeliveryDate,
    NULL AS PaymentDueDate,
    CASE WHEN dhead.LAST_UPDATED_BY LIKE 'FUSION_APPS%' THEN 'true' ELSE 'false' END AS IsAutomated
FROM DOO_HEADERS_ALL dhead
JOIN BaseOrders bo ON dhead.HEADER_ID = bo.HEADER_ID
WHERE dhead.STATUS_CODE = 'CANCELED'

# BICC Data Store (PVO) and Column Selection Manifest
# This manifest outlines the PVOs and columns to select in the BICC UI for the extract job.

# PVO for Sales Order Header information (Created, Confirmed, Closed, Cancelled events)
PVO: FscmTopModelAM.DooTopAM.Header
Columns:
  - HeaderId -> SalesOrder
  - CreationDate -> EventTime (for 'Sales Order Created')
  - CreatedBy -> UserName (for 'Sales Order Created')
  - LastUpdateDate # For incremental filtering
  - StatusCode
  - SubmittedDate -> EventTime (for 'Order Confirmed')
  - SubmittedBy -> UserName (for 'Order Confirmed')
  - OrderedTotal -> SalesOrderTotalAmount
  - SoldToPartyName -> CustomerName
  - SourceSalesChannelCode -> SalesChannel
  - RequestShipDate -> RequestedDeliveryDate
  - ClosedDate -> EventTime (for 'Order Closed')
  - CanceledFlag
  - CanceledDate -> EventTime (for 'Order Cancelled')

# PVO for Sales Order Lines (Line Closed event)
PVO: FscmTopModelAM.DooTopAM.FulfillLine
Columns:
  - HeaderId -> SalesOrder
  - ActualCompletionDate -> EventTime (for 'Order Line Closed')
  - LastUpdateDate # For incremental filtering
  - LastUpdatedBy -> UserName
  - StatusName # To confirm closed status

# PVO for Holds (Credit Hold Applied event)
PVO: FscmTopModelAM.DooTopAM.HoldInstance
Columns:
  - SourceHeaderId -> SalesOrder
  - CreationDate -> EventTime (for 'Credit Hold Applied')
  - CreatedBy -> UserName
  - HoldName # To filter for credit-related holds

# PVO for Shipments (Picked, Shipped, Delivered events)
PVO: FscmTopModelAM.ScmTopAM.ShipmentLine
Columns:
  - SourceHeaderNumber -> SalesOrder
  - PickedDate -> EventTime (for 'Goods Picked')
  - ShippedDate -> EventTime (for 'Goods Shipped')
  - ActualDeliveryDate -> ActualDeliveryDate & EventTime (for 'Goods Delivered')
  - LastUpdateDate # For incremental filtering
  - LastUpdatedBy -> UserName

# PVO for Invoices (Invoice Created, Invoice Corrected events)
PVO: FscmTopModelAM.ArTopAM.ReceivableInvoice
Columns:
  - InterfaceHeaderAttribute1 -> SalesOrder # Link to SO via reference field
  - TrxDate -> EventTime (for 'Invoice Created')
  - CreatedBy -> UserName
  - DueDate -> PaymentDueDate
  - PreviousTrxNumber # If populated, indicates a correction
  - CreationDate # Can be used for 'Invoice Corrected' if a new record is made
  - LastUpdateDate # For incremental filtering

# PVO for Payments (Payment Received event)
PVO: FscmTopModelAM.ArTopAM.CashReceiptApplication
Columns:
  - AppliedCustomerTrxId # ID to link back to the invoice
  - ApplyDate -> EventTime (for 'Payment Received')
  - CreatedBy -> UserName
  - LastUpdateDate # For incremental filtering