<ROOT>
 <APPS_INITIALIZE_DATA>
  <USER_NAME>ENGINATICS</USER_NAME>
  <RESPONSIBILITY_KEY>SYSTEM_ADMINISTRATOR</RESPONSIBILITY_KEY>
  <APPLICATION_SHORT_NAME>SYSADMIN</APPLICATION_SHORT_NAME>
 </APPS_INITIALIZE_DATA>
<REPORTS>
<!-- loader xml for Enginatics Blitz Report: XXAMZ OTIF Delivered Orders(XXONT1253) -->
 <REPORTS_ROW>
  <GUID>064745ADC01CF924E065000000000001</GUID><ENABLED>Y</ENABLED>
  <SQL_TEXT>--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, &apos;,&apos;) WITHIN GROUP(
    ORDER BY
        order_number, item_code
    )                              tracking_number,
    LISTAGG(DISTINCT carrier_name, &apos;,&apos;) WITHIN GROUP(
    ORDER BY
        order_number, item_code
    )                              carrier_name
FROM
    (
        SELECT
            decode(mp.attribute5, &apos;RAD-1&apos;, &apos;Janus-RAD1&apos;, mp.attribute5) ship_from,
            shipto.attribute5                                           site,
            to_char(ooha.order_number)                                  order_number,
            rtrim(oola.line_number
                  || &apos;.&apos;
                  || oola.shipment_number
                  || &apos;.&apos;
                  || oola.option_number
                  || &apos;.&apos;
                  || oola.component_number, &apos;.&apos;)                              line_number,
            msi.segment1                                                item_code,
            shipto.attribute7                                           business_unit,
            ooha.attribute5                                             site_type,
            ooha.attribute3                                             demand_type,
            to_char(ooha.ordered_date, &apos;YYYY-MM&apos;)                       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 = &apos;CLOSED&apos;
                     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, &apos;YYYY-MM-DD HH24:MI:SS&apos;)            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 = &apos;RECEIVE&apos;
                    AND rsh.receipt_num IS NOT NULL
                    AND rsl.quantity_received &gt; 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 = &apos;Y&apos;
            AND ooha.cancelled_flag = &apos;N&apos;
            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 =&apos;RAD&apos;
            AND mp.attribute5 != &apos;RAD-1&apos;
            AND oola.flow_status_code != &apos;CANCELLED&apos;
            AND wdd.attribute7 IS NOT NULL
            AND wdd.released_status = &apos;C&apos;
            AND wdd.source_code = &apos;OE&apos;
            AND ooha.attribute3 NOT IN ( &apos;ARMA&apos;,&apos;RMA&apos;)
          --  AND ooha.order_number =&apos;126559&apos;
            AND EXISTS (
                SELECT
                    1
                FROM
                    apps.wsh_delivery_details wdd1
                WHERE
                        1 = 1
                    AND source_code = &apos;OE&apos;
                    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, &apos;,&apos;) WITHIN GROUP(
        ORDER BY
            order_number, item_code
        ),
    LISTAGG(DISTINCT carrier_name, &apos;,&apos;) WITHIN GROUP(
    ORDER BY
        order_number, item_code
    )    carrier_name
FROM
    (
        SELECT
            decode(mp.attribute5, &apos;RAD-1&apos;, &apos;Janus-RAD1&apos;, mp.attribute5) ship_from,
            shipto.attribute5                                           site,
            to_char(ooha.order_number)                                  order_number,
            rtrim(oola.line_number
                  || &apos;.&apos;
                  || oola.shipment_number
                  || &apos;.&apos;
                  || oola.option_number
                  || &apos;.&apos;
                  || oola.component_number, &apos;.&apos;)                              line_number,
            msi.segment1                                                item_code,
            shipto.attribute7                                           business_unit,
            ooha.attribute5                                             site_type,
            ooha.attribute3                                             demand_type,
            to_char(ooha.ordered_date, &apos;YYYY-MM&apos;)                       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 = &apos;CLOSED&apos;
                     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, &apos;YYYY-MM-DD HH24:MI:SS&apos;)            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 = &apos;RECEIVE&apos;
                    AND rsh.receipt_num IS NOT NULL
                    AND rsl.quantity_received &gt; 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 = &apos;Y&apos;
            AND ooha.cancelled_flag = &apos;N&apos;
            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 =&apos;RAD&apos;
            AND mp.attribute5 != &apos;RAD-1&apos;
            AND oola.flow_status_code != &apos;CANCELLED&apos;
            AND wdd.attribute7 IS NOT NULL
            AND wdd.released_status = &apos;C&apos;
            AND wdd.source_code = &apos;OE&apos;
            AND ooha.attribute3 in ( &apos;ARMA&apos;,&apos;RMA&apos;)
   -- and ooha.order_number =&apos;126559&apos;
            AND EXISTS (
                SELECT
                    1
                FROM
                    apps.wsh_delivery_details wdd1
                WHERE
                        1 = 1
                    AND source_code = &apos;OE&apos;
                    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)</SQL_TEXT>
  <REPORT_TRANSLATIONS>
   <REPORT_TRANSLATIONS_ROW>
    <LANGUAGE>US</LANGUAGE>
    <REPORT_NAME>XXAMZ OTIF Delivered Orders(XXONT1253)</REPORT_NAME>
   </REPORT_TRANSLATIONS_ROW>
  </REPORT_TRANSLATIONS>
  <CATEGORY_ASSIGNMENTS>
  </CATEGORY_ASSIGNMENTS>
  <ANCHORS>
   <ANCHORS_ROW>
    <ANCHOR>1=1</ANCHOR>
   </ANCHORS_ROW>
  </ANCHORS>
  <PARAMETERS>
   <PARAMETERS_ROW>
    <SORT_ORDER>1</SORT_ORDER>
    <DISPLAY_SEQUENCE>10</DISPLAY_SEQUENCE>
    <ANCHOR>1=1</ANCHOR>
    <SQL_TEXT>trunc(TO_DATE(wdd1.attribute7, &apos;YYYY-MM-DD HH24:MI:SS&apos;))&gt;=trunc(:delivered_date_from)</SQL_TEXT>
    <PARAMETER_TYPE_DSP>Date</PARAMETER_TYPE_DSP>
    <PARAMETER_TRANSLATIONS>
     <PARAMETER_TRANSLATIONS_ROW>
      <LANGUAGE>US</LANGUAGE>
      <PARAMETER_NAME>Delivered From Date</PARAMETER_NAME>
     </PARAMETER_TRANSLATIONS_ROW>
    </PARAMETER_TRANSLATIONS>
   </PARAMETERS_ROW>
   <PARAMETERS_ROW>
    <SORT_ORDER>2</SORT_ORDER>
    <DISPLAY_SEQUENCE>20</DISPLAY_SEQUENCE>
    <ANCHOR>1=1</ANCHOR>
    <SQL_TEXT>trunc(TO_DATE(wdd1.attribute7, &apos;YYYY-MM-DD HH24:MI:SS&apos;))&lt;=trunc(:delivered_date_to)</SQL_TEXT>
    <PARAMETER_TYPE_DSP>Date</PARAMETER_TYPE_DSP>
    <PARAMETER_TRANSLATIONS>
     <PARAMETER_TRANSLATIONS_ROW>
      <LANGUAGE>US</LANGUAGE>
      <PARAMETER_NAME>Delivered To Date</PARAMETER_NAME>
     </PARAMETER_TRANSLATIONS_ROW>
    </PARAMETER_TRANSLATIONS>
   </PARAMETERS_ROW>
  </PARAMETERS>
  <TEMPLATES>
  </TEMPLATES>
  <DEFAULT_TEMPLATES>
  </DEFAULT_TEMPLATES>
  <UPLOAD_COLUMNS>
  </UPLOAD_COLUMNS>
  <UPLOAD_PARAMETERS>
  </UPLOAD_PARAMETERS>
  <UPLOAD_SQLS>
  </UPLOAD_SQLS>
 </REPORTS_ROW>
</REPORTS>
</ROOT>