AR Distributed Order Orchestration Report

SELECT DISTINCT
    'Create' AS changeOperation,
    DLA.ORG_ID AS OrgId,
    NVL(WFC.UNIT_AMOUNT, (DFLA.ORDERED_QTY * DLA.UNIT_SELLING_PRICE)) AS Amount,
    DHA.SOURCE_ORDER_SYSTEM AS SalesOrderSource,
    'Distributed Order Orchestration' AS BatchSourceName,
    'Invoice' AS CustomerTrxTypeName,
    DHA.ORDER_NUMBER AS SalesOrder,
    DHA.ORDERED_DATE AS SalesOrderDate,
    'User' AS ConversionType,
    1 AS ConversionRate,
    'LIFO' AS CreditMethodForAccountRule,
    'LIFO' AS CreditMethodForInstallments,
    'FREIGHT CHARGES' AS Description,
    DLA.INVENTORY_ITEM_ID AS InventoryItemId,
    DHA.LEGAL_ENTITY_ID AS LegalEntityId,
    'FREIGHT' AS LineType,
    DFLA.ship_to_party_id AS OrigSystemShipPartyId,
    DFLA.SHIP_TO_PARTY_SITE_ID AS OrigSystemShipPartySiteId,
    (
        SELECT DISTINCT HZPA1.PARTY_ID
        FROM HZ_RELATIONSHIPS HZRE,
             HZ_PARTIES HZPA,
             HZ_PARTIES HZPA1,
             HZ_ORG_CONTACTS HZOC,
             HZ_CONTACT_POINTS HZCP
        WHERE HZPA.party_id = HZRE.object_id
          AND HZPA1.party_id = HZRE.subject_id
          AND HZRE.relationship_code = 'CONTACT_OF'
          AND HZRE.subject_type = 'PERSON'
          AND HZRE.directional_flag = 'F'
          AND HZRE.RELATIONSHIP_ID = HZOC.PARTY_RELATIONSHIP_ID(+)
          AND HZRE.object_type IN ('ORGANIZATION', 'PERSON')
          AND HZCP.owner_table_id(+) = HZRE.subject_id
          AND HZCP.RELATIONSHIP_ID(+) = HZRE.RELATIONSHIP_ID
          AND HZCP.owner_table_name(+) = 'HZ_PARTIES'
          AND HZCP.primary_flag(+) = 'Y'
          AND HZCP.status(+) = 'A'
          AND HZRE.RELATIONSHIP_ID = DFLA.SHIP_TO_PARTY_CONTACT_ID
    ) AS OrigSystemShipPtyContactId,
    DHA.SOLD_TO_PARTY_ID AS OrigSystemSoldPartyId,
    (
        SELECT hcsu.CUST_ACCT_SITE_ID
        FROM hz_cust_site_uses_all hcsu,
             hz_cust_acct_sites_all hcas,
             hz_party_sites hps,
             hz_parties hp,
             hz_locations hzl
        WHERE hcsu.site_use_code = 'BILL_TO'
          AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id
          AND hcas.party_site_id = hps.party_id
          AND hps.party_id = hp.party_id
          AND hps.location_id = hzl.location_id
          AND hcsu.site_use_id = DFLA.bill_to_site_use_id
    ) AS OrigSystemBillAddressId,
    DFLA.BILL_TO_CONTACT_ID AS OrigSystemBillContactId,
    DFLA.BILL_TO_CUSTOMER_ID AS OrigSystemBillCustomerId,
    NVL(DHA.CUSTOMER_PO_NUMBER,'N') AS PurchaseOrder,
    DFLA.ORDERED_QTY AS Quantity,
    DFLA.ORDERED_QTY AS QuantityOrdered,
    DFLA.ACTUAL_SHIP_DATE AS ShipDateActual,
    DOO_CROSS_REFERENCE.GETFULFILLMENTVALUE('TERM_ID', 'PAYMENT_TERMS', DHA.PAYMENT_TERM_ID) AS PaymentTermsId,
    DLA.UNIT_SELLING_PRICE AS UnitSellingPrice,
    NVL(DFLA.FULFILL_ORG_ID, wnd.ORGANIZATION_ID) AS WarehouseId,
    'DOO' AS FLEX_Context,
    14 AS FLEX_NumOfSegments,
    DHA.ORDER_NUMBER AS Source_Order_Number,
    DHA.SOURCE_ORDER_SYSTEM AS Source_Order_System,
    DHA.ORDER_NUMBER AS DOO_Order_Number,
    DHA.ORDER_TYPE_CODE AS Order_Type,
    DFLA.FULFILL_LINE_ID AS Fulfillment_Line_ID,
    NVL(DOPA.PRICE_ADJUSTMENT_ID,DFLA.FULFILL_LINE_ID) AS Price_Adjustment_ID,
    NVL(DFLD.DELIVERY_NAME,wnd.DELIVERY_ID) AS Delivery_Name,
    NVL(DFLD.WAYBILL_NUMBER, NVL(wnd.WAYBILL,wdd.TRACKING_NUMBER)) AS WayBill_Number,
    NVL(DFLD.BILL_OF_LADING_NUMBER,wnd.BILL_OF_LADING_NUMBER) AS Bill_of_Lading_Number,
    NVL(DFLA.CUSTOMER_ITEM_ID,0) AS Customer_Item,
    DFLA.SOURCE_SCHEDULE_NUMBER AS Source_Schedule_Number,
    NVL(DFLA.FULFILLMENT_SPLIT_REF_ID,1) AS Fulfill_Line_Split_Reference,
    DFLA.ORG_ID AS Profit_Center_Business_Unit,
    1 AS period,
    DOO_CROSS_REFERENCE.GETFULFILLMENTVALUE('CURRENCY_CODE', 'CURRENCIES', DHA.TRANSACTIONAL_CURRENCY_CODE) AS currencyCode,
    DFLA.ORDERED_UOM AS unitCode
FROM DOO_HEADERS_ALL DHA,
     DOO_LINES_ALL DLA,
     DOO_FULFILL_LINES_ALL DFLA,
     DOO_PRICE_ADJUSTMENTS DOPA,
     DOO_FULFILL_LINE_DETAILS DFLD,
     WSH_NEW_DELIVERIES WND,
     WSH_DELIVERY_DETAILS WDD,
     WSH_DELIVERY_ASSIGNMENTS WDA,
     WSH_FREIGHT_COSTS WFC
WHERE DHA.header_id = DLA.header_id
  AND DHA.header_id = DFLA.header_id
  AND DLA.header_id = DFLA.header_id
  AND DLA.line_id = DFLA.line_id
  AND DHA.request_cancel_date IS NULL
  AND DHA.order_type_code NOT IN ('Sales_Return')
  AND DFLA.FULFILL_LINE_ID = DOPA.FULFILL_LINE_ID(+)
  AND DFLA.FULFILL_LINE_ID = DFLD.FULFILL_LINE_ID(+)
  AND WND.DELIVERY_ID = WDA.DELIVERY_ID
  AND WDA.DELIVERY_DETAIL_ID = WDD.DELIVERY_DETAIL_ID
  AND WDD.ORGANIZATION_ID = WND.ORGANIZATION_ID
  AND WND.DELIVERY_ID = WFC.DELIVERY_ID
  AND WDD.SOURCE_HEADER_NUMBER = DHA.ORDER_NUMBER
  AND (WND.DELIVERY_NAME = :IP_DELIVERY_NAME OR TO_CHAR(WND.DELIVERY_ID) = TO_CHAR(:IP_DELIVERY_NAME))
  AND DFLD.DELIVERY_NAME IS NOT NULL
  AND ROWNUM = 1
ORDER BY FULFILLMENT_LINE_ID;