XXAMZ OTIF Delivered Orders(XXONT1253)

Description

Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS

Contact us to schedule a demo or if you need help with the installation

--Non ARMA , OTIF at line level
SELECT
    ship_from,
    site,
    order_number,
    line_number                    line_number,
    item_code,
    item_desc,
    uin,
    business_unit,
    site_type,
    demand_type,
    order_month,
    category,
    MIN(ordered_quantity)          ordered_quantity,
    SUM(shipped_quantity)          shipped_quantity,
    SUM(quantity_received)         quantity_received,
    MAX(requisition_creation_date) requisition_creation_date,
    MAX(so_creation_date)          so_creation_date,
    MAX(promise_date)              promise_date,
    MAX(need_by_date)              need_by_date,
    MAX(inv_allocation_date)       inv_allocation_date,
    MAX(pick_date)                 pick_date,
    MAX(ship_date)                 ship_date,
    MAX(delivered_date)            delivered_date,
    MAX(receive_date)              receive_date,
    LISTAGG(DISTINCT tracking_number, ',') WITHIN GROUP(
    ORDER BY
        order_number, item_code
    )                              tracking_number,
    LISTAGG(DISTINCT carrier_name, ',') WITHIN GROUP(
    ORDER BY
        order_number, item_code
    )                              carrier_name
FROM
    (
        SELECT
            decode(mp.attribute5, 'RAD-1', 'Janus-RAD1', mp.attribute5) ship_from,
            shipto.attribute5                                           site,
            to_char(ooha.order_number)                                  order_number,
            rtrim(oola.line_number
                  || '.'
                  || oola.shipment_number
                  || '.'
                  || oola.option_number
                  || '.'
                  || oola.component_number, '.')                              line_number,
            msi.segment1                                                item_code,
            shipto.attribute7                                           business_unit,
            ooha.attribute5                                             site_type,
            ooha.attribute3                                             demand_type,
            to_char(ooha.ordered_date, 'YYYY-MM')                       order_month,
            (
                SELECT
                    concatenated_segments
                FROM
                    apps.mtl_categories_kfv  mck,
                    apps.mtl_item_categories mic
                WHERE
                        mic.category_id = mck.category_id
                    AND mic.inventory_item_id = msi.inventory_item_id
                    AND category_set_id = 1100000041
                    AND organization_id = 101
                    AND ROWNUM = 1
            )                                                           category,
            msi.description                                             item_desc,
            msi.attribute9                                              uin,
            wdd.src_requested_quantity                                  ordered_quantity,
            wdd.shipped_quantity                                        shipped_quantity,
           -- wdd.requested_quantity                                      requested_quantity,
            receipt.quantity_received                                   quantity_received,
            prh.creation_date                                           requisition_creation_date,
            oola.creation_date                                          so_creation_date,
            oola.promise_date                                           promise_date,
            oola.request_date                                           need_by_date,
            (
                SELECT
                    trunc(wpb.last_update_date)
                FROM
                    apps.wsh_picking_batches wpb
                WHERE
                    wpb.batch_id = wdd.batch_id
            )                                                           inv_allocation_date,
            (
                SELECT
                    MAX(trunc(trl.status_date))
                FROM
                    apps.mtl_txn_request_lines_v trl,
                    apps.mtl_txn_request_headers trh
                WHERE
                        trl.line_id = wdd.move_order_line_id
                    AND trl.header_id = trh.header_id
                    AND trl.line_status = 5
            )                                                           pick_date,
     --For orders shipped outside of DSCS below code is added to consider oola.last_update_date
            CASE
                WHEN oola.flow_status_code = 'CLOSED'
                     AND wnd.confirm_date IS NULL THEN
                    nvl(oola.actual_shipment_date, oola.last_update_date)
                ELSE
                    wnd.confirm_date
            END                                                         ship_date,
            TO_DATE(wdd.attribute7, 'YYYY-MM-DD HH24:MI:SS')            delivered_date,
            receipt.creation_date                                       receive_date,
            wdd.tracking_number                                         tracking_number,
            (
                SELECT
                    carrier_name
                FROM
                    apps.wsh_carriers_v wcv
                WHERE
                        wcv.carrier_id = wnd.carrier_id
                    AND ROWNUM = 1
            )                                                           carrier_name,
            wdd.attribute6                                              expected_delivery_date,
            oola.attribute6                                             first_attempted_delivery_date
        FROM
            apps.oe_order_headers_all         ooha,
            apps.oe_order_lines_all           oola,
            apps.mtl_parameters               mp,
            apps.ar_customers                 ac,
            apps.mtl_system_items_kfv         msi,
            apps.hr_all_organization_units    hou,
            apps.hz_cust_site_uses_all        hcsua,
            apps.hz_cust_acct_sites_all       hcasa,
            apps.hz_party_sites               hps,
            apps.hz_locations                 hl,
            apps.hz_parties                   hp,
            apps.hz_cust_site_uses_all        hcsuab,
            apps.hz_cust_acct_sites_all       hcasab,
            apps.hz_party_sites               hpsb,
            apps.hz_locations                 hlb,
            apps.hz_parties                   hpb,
            apps.po_requisition_lines_all     prl,
            apps.po_requisition_headers_all   prh,
            apps.per_all_people_f             ppf,
            apps.mtl_parameters               mpw,
            apps.po_location_associations_all pla,
            apps.mtl_parameters               shipto,
            apps.wsh_delivery_details         wdd,
            apps.wsh_delivery_assignments     wda,
            apps.wsh_new_deliveries           wnd,
            (
                SELECT DISTINCT
                    MAX(rcv.creation_date)
                    OVER(PARTITION BY oola1.line_id) creation_date,
                    SUM(rsl.quantity_received)
                    OVER(PARTITION BY oola1.line_id) quantity_received,
                    oola1.line_id
                FROM
                    apps.oe_order_lines_all       oola1,
                    apps.rcv_shipment_lines       rsl,
                    apps.po_requisition_lines_all prla,
                    apps.rcv_shipment_headers     rsh,
                    apps.rcv_transactions         rcv
                WHERE
                        oola1.source_document_line_id = prla.requisition_line_id (+)
                    AND prla.requisition_line_id = rsl.requisition_line_id
                    AND rsh.shipment_header_id = rsl.shipment_header_id
                    AND rcv.shipment_line_id = rsl.shipment_line_id
                    AND transaction_type = 'RECEIVE'
                    AND rsh.receipt_num IS NOT NULL
                    AND rsl.quantity_received > 0
            )                                 receipt
        WHERE
                ooha.header_id = oola.header_id
            AND oola.inventory_item_id = msi.inventory_item_id
            AND msi.organization_id = oola.ship_from_org_id
            AND mp.organization_id = msi.organization_id
            AND ac.customer_id = ooha.sold_to_org_id
            AND hou.organization_id = ooha.org_id
            AND ooha.booked_flag = 'Y'
            AND ooha.cancelled_flag = 'N'
            AND ooha.ship_to_org_id = hcsua.site_use_id (+)
            AND hcsua.cust_acct_site_id = hcasa.cust_acct_site_id (+)
            AND hcasa.party_site_id = hps.party_site_id (+)
            AND hps.location_id = hl.location_id (+)
            AND hps.party_id = hp.party_id (+)
            AND ooha.invoice_to_org_id = hcsuab.site_use_id
            AND hcsuab.cust_acct_site_id = hcasab.cust_acct_site_id
            AND hcasab.party_site_id = hpsb.party_site_id
            AND hpsb.location_id = hlb.location_id
            AND hpsb.party_id = hpb.party_id
            AND oola.source_document_line_id = prl.requisition_line_id (+)
            AND prh.requisition_header_id (+) = prl.requisition_header_id
            AND prh.preparer_id = ppf.person_id (+)
            AND trunc(sysdate) BETWEEN trunc(ppf.effective_start_date(+)) AND trunc(ppf.effective_end_date(+))
            AND mpw.organization_id = oola.ship_from_org_id
            AND hcsua.site_use_id = pla.site_use_id (+)
            AND pla.organization_id = shipto.organization_id (+)
            AND oola.line_id = receipt.line_id (+)
            AND oola.line_id = wdd.source_line_id (+)
            AND wdd.delivery_detail_id = wda.delivery_detail_id (+)
            AND wnd.delivery_id (+) = wda.delivery_id
            AND mp.attribute3 ='RAD'
            AND mp.attribute5 != 'RAD-1'
            AND oola.flow_status_code != 'CANCELLED'
            AND wdd.attribute7 IS NOT NULL
            AND wdd.released_status = 'C'
            AND wdd.source_code = 'OE'
            AND ooha.attribute3 NOT IN ( 'ARMA','RMA')
          --  AND ooha.order_number ='126559'
            AND EXISTS (
                SELECT
                    1
                FROM
                    apps.wsh_delivery_details wdd1
                WHERE
                        1 = 1
                    AND source_code = 'OE'
                    AND oola.line_id = wdd1.source_line_id
            )
    ) inner_sql
GROUP BY
    ship_from,
    site,
    line_number,
    order_number,
    item_code,
    item_desc,
    uin,
    business_unit,
    site_type,
    demand_type,
    order_month,
    category
HAVING
    MIN(ordered_quantity) = SUM(shipped_quantity)
-- ARMA , OTIF at Item level
UNION
SELECT
    ship_from,
    site,
    order_number,
    NULL line_number,
    item_code,
    item_desc,
    uin,
    business_unit,
    site_type,
    demand_type,
    order_month,
    category,
    MIN(ordered_quantity),
    SUM(shipped_quantity),
--sum(requested_quantity),
    SUM(quantity_received),
    MAX(requisition_creation_date),
    MAX(so_creation_date),
    MAX(promise_date),
    MAX(need_by_date),
    MAX(inv_allocation_date),
    MAX(pick_date),
    MAX(ship_date),
    MAX(delivered_date),
    MAX(receive_date),
    LISTAGG(DISTINCT tracking_number, ',') WITHIN GROUP(
        ORDER BY
            order_number, item_code
        ),
    LISTAGG(DISTINCT carrier_name, ',') WITHIN GROUP(
    ORDER BY
        order_number, item_code
    )    carrier_name
FROM
    (
        SELECT
            decode(mp.attribute5, 'RAD-1', 'Janus-RAD1', mp.attribute5) ship_from,
            shipto.attribute5                                           site,
            to_char(ooha.order_number)                                  order_number,
            rtrim(oola.line_number
                  || '.'
                  || oola.shipment_number
                  || '.'
                  || oola.option_number
                  || '.'
                  || oola.component_number, '.')                              line_number,
            msi.segment1                                                item_code,
            shipto.attribute7                                           business_unit,
            ooha.attribute5                                             site_type,
            ooha.attribute3                                             demand_type,
            to_char(ooha.ordered_date, 'YYYY-MM')                       order_month,
            (
                SELECT
                    concatenated_segments
                FROM
                    apps.mtl_categories_kfv  mck,
                    apps.mtl_item_categories mic
                WHERE
                        mic.category_id = mck.category_id
                    AND mic.inventory_item_id = msi.inventory_item_id
                    AND category_set_id = 1100000041
                    AND organization_id = 101
                    AND ROWNUM = 1
            )                                                           category,
            msi.description                                             item_desc,
            msi.attribute9                                              uin,
            SUM(wdd.src_requested_quantity)
            OVER(PARTITION BY order_number, msi.segment1)               ordered_quantity,
            wdd.shipped_quantity                                        shipped_quantity,
            wdd.requested_quantity                                      requested_quantity,
            receipt.quantity_received                                   quantity_received,
            prh.creation_date                                           requisition_creation_date,
            oola.creation_date                                          so_creation_date,
            oola.promise_date                                           promise_date,
            oola.request_date                                           need_by_date,
            (
                SELECT
                    trunc(wpb.last_update_date)
                FROM
                    apps.wsh_picking_batches wpb
                WHERE
                    wpb.batch_id = wdd.batch_id
            )                                                           inv_allocation_date,
            (
                SELECT
                    MAX(trunc(trl.status_date))
                FROM
                    apps.mtl_txn_request_lines_v trl,
                    apps.mtl_txn_request_headers trh
                WHERE
                        trl.line_id = wdd.move_order_line_id
                    AND trl.header_id = trh.header_id
                    AND trl.line_status = 5
            )                                                           pick_date,
     --For orders shipped outside of DSCS below code is added to consider oola.last_update_date
            CASE
                WHEN oola.flow_status_code = 'CLOSED'
                     AND wnd.confirm_date IS NULL THEN
                    nvl(oola.actual_shipment_date, oola.last_update_date)
                ELSE
                    wnd.confirm_date
            END                                                         ship_date,
            TO_DATE(wdd.attribute7, 'YYYY-MM-DD HH24:MI:SS')            delivered_date,
            receipt.creation_date                                       receive_date,
            wdd.tracking_number                                         tracking_number,
            (
                SELECT
                    carrier_name
                FROM
                    apps.wsh_carriers_v wcv
                WHERE
                        wcv.carrier_id = wnd.carrier_id
                    AND ROWNUM = 1
            )                                                           carrier_name,
            wdd.attribute6                                              expected_delivery_date,
            oola.attribute6                                             first_attempted_delivery_date
        FROM
            apps.oe_order_headers_all         ooha,
            apps.oe_order_lines_all           oola,
            apps.mtl_parameters               mp,
            apps.ar_customers                 ac,
            apps.mtl_system_items_kfv         msi,
            apps.hr_all_organization_units    hou,
            apps.hz_cust_site_uses_all        hcsua,
            apps.hz_cust_acct_sites_all       hcasa,
            apps.hz_party_sites               hps,
            apps.hz_locations                 hl,
            apps.hz_parties                   hp,
            apps.hz_cust_site_uses_all        hcsuab,
            apps.hz_cust_acct_sites_all       hcasab,
            apps.hz_party_sites               hpsb,
            apps.hz_locations                 hlb,
            apps.hz_parties                   hpb,
            apps.po_requisition_lines_all     prl,
            apps.po_requisition_headers_all   prh,
            apps.per_all_people_f             ppf,
            apps.mtl_parameters               mpw,
            apps.po_location_associations_all pla,
            apps.mtl_parameters               shipto,
            apps.wsh_delivery_details         wdd,
            apps.wsh_delivery_assignments     wda,
            apps.wsh_new_deliveries           wnd,
            (
                SELECT DISTINCT
                    MAX(rcv.creation_date)
                    OVER(PARTITION BY oola1.line_id) creation_date,
                    SUM(rsl.quantity_received)
                    OVER(PARTITION BY oola1.line_id) quantity_received,
                    oola1.line_id
                FROM
                    apps.oe_order_lines_all       oola1,
                    apps.rcv_shipment_lines       rsl,
                    apps.po_requisition_lines_all prla,
                    apps.rcv_shipment_headers     rsh,
                    apps.rcv_transactions         rcv
                WHERE
                        oola1.source_document_line_id = prla.requisition_line_id (+)
                    AND prla.requisition_line_id = rsl.requisition_line_id
                    AND rsh.shipment_header_id = rsl.shipment_header_id
                    AND rcv.shipment_line_id = rsl.shipment_line_id
                    AND transaction_type = 'RECEIVE'
                    AND rsh.receipt_num IS NOT NULL
                    AND rsl.quantity_received > 0
            )                                 receipt
        WHERE
                ooha.header_id = oola.header_id
            AND oola.inventory_item_id = msi.inventory_item_id
            AND msi.organization_id = oola.ship_from_org_id
            AND mp.organization_id = msi.organization_id
            AND ac.customer_id = ooha.sold_to_org_id
            AND hou.organization_id = ooha.org_id
            AND ooha.booked_flag = 'Y'
            AND ooha.cancelled_flag = 'N'
            AND ooha.ship_to_org_id = hcsua.site_use_id (+)
            AND hcsua.cust_acct_site_id = hcasa.cust_acct_site_id (+)
            AND hcasa.party_site_id = hps.party_site_id (+)
            AND hps.location_id = hl.location_id (+)
            AND hps.party_id = hp.party_id (+)
            AND ooha.invoice_to_org_id = hcsuab.site_use_id
            AND hcsuab.cust_acct_site_id = hcasab.cust_acct_site_id
            AND hcasab.party_site_id = hpsb.party_site_id
            AND hpsb.location_id = hlb.location_id
            AND hpsb.party_id = hpb.party_id
            AND oola.source_document_line_id = prl.requisition_line_id (+)
            AND prh.requisition_header_id (+) = prl.requisition_header_id
            AND prh.preparer_id = ppf.person_id (+)
            AND trunc(sysdate) BETWEEN trunc(ppf.effective_start_date(+)) AND trunc(ppf.effective_end_date(+))
            AND mpw.organization_id = oola.ship_from_org_id
            AND hcsua.site_use_id = pla.site_use_id (+)
            AND pla.organization_id = shipto.organization_id (+)
            AND oola.line_id = receipt.line_id (+)
            AND oola.line_id = wdd.source_line_id
            AND wdd.delivery_detail_id = wda.delivery_detail_id (+)
            AND wnd.delivery_id (+) = wda.delivery_id
            AND mp.attribute3 ='RAD'
            AND mp.attribute5 != 'RAD-1'
            AND oola.flow_status_code != 'CANCELLED'
            AND wdd.attribute7 IS NOT NULL
            AND wdd.released_status = 'C'
            AND wdd.source_code = 'OE'
            AND ooha.attribute3 in ( 'ARMA','RMA')
   -- and ooha.order_number ='126559'
            AND EXISTS (
                SELECT
                    1
                FROM
                    apps.wsh_delivery_details wdd1
                WHERE
                        1 = 1
                    AND source_code = 'OE'
                    AND oola.line_id = wdd1.source_line_id
            )
    ) inner_sql
GROUP BY
    ship_from,
    site,
    order_number,
    item_code,
    item_desc,
    uin,
    business_unit,
    site_type,
    demand_type,
    order_month,
    category
HAVING
    MIN(ordered_quantity) = SUM(shipped_quantity)
Parameter Name SQL text Validation
Delivered From Date
trunc(TO_DATE(wdd1.attribute7, 'YYYY-MM-DD HH24:MI:SS'))>=trunc(:delivered_date_from)
Date
Delivered To Date
trunc(TO_DATE(wdd1.attribute7, 'YYYY-MM-DD HH24:MI:SS'))<=trunc(:delivered_date_to)
Date
Download
 
Blitz Report™

Blitz Report™ provides multiple benefits: