Unprocessed Shipping Transactions in Oracle EBS r12

 SELECT   'Unprocessed Shipping Transactions' trntype, org.organization_id,

            org.organization_code, org.organization_name, COUNT (*) totaltrn,

            msi.segment1 item_code, error_explanation, ERROR_CODE,

            wdd.delivery_detail_id, wnd.delivery_id, tt.shipment_number,

            wdd.source_header_number, wdd.source_line_id, tt.transaction_date,

            tt.expected_arrival_date, gp.period_code, msi.inventory_item_id,

            wdd.source_line_number order_line_number, wdd.shipped_quantity,

            tt.transaction_uom, tt.subinventory_code

       FROM wsh_delivery_details wdd,

            wsh_delivery_assignments wda,

            wsh_new_deliveries wnd,

            wsh_delivery_legs wdl,

            wsh_trip_stops wts,

            mtl_transactions_interface tt,

            mtl_system_items_b msi,

            org_organization_definitions org,

            gmf_period_statuses gp

      WHERE wdd.source_code = 'OE'

        AND wdd.released_status = 'C'

        AND wdd.inv_interfaced_flag IN ('N', 'P')

        AND wdd.organization_id = org.organization_id

        AND wda.delivery_detail_id = wdd.delivery_detail_id

        AND wnd.delivery_id = wda.delivery_id

--AND wdd.organization_id = tt.organization_id(+)

        AND wdd.inventory_item_id = tt.inventory_item_id(+)

        AND wnd.status_code IN ('CL', 'IT')

        AND wdl.delivery_id = wnd.delivery_id

        AND wts.pending_interface_flag IN ('Y', 'P')

        AND gp.period_code = gp.period_code

        AND wdd.inventory_item_id = msi.inventory_item_id

        AND msi.organization_id = wdd.organization_id

        AND TRUNC (wts.actual_departure_date) >= TRUNC (gp.start_date)

        AND TRUNC (wts.actual_departure_date) <= TRUNC (gp.end_date)

--AND TRUNC(WTS.ACTUAL_DEPARTURE_DATE) BETWEEN :P_FROM_DATE and :P_TO_DATE

---AND TRUNC(WTS.ACTUAL_DEPARTURE_DATE) BETWEEN TO_DATE('01-MAY-2018 00:00:00','DD-MON-YYYY HH24:MI:SS' ) AND

---TO_DATE('31-MAY-2018 23:59:59','DD-MON-YYYY HH24:MI:SS')

        AND wdl.pick_up_stop_id = wts.stop_id

   GROUP BY org.organization_id,

            org.organization_code,

            org.organization_name,

            error_explanation,

            ERROR_CODE,

            wdd.shipped_quantity,

            msi.inventory_item_id,

            tt.shipment_number,

            wdd.delivery_detail_id,

            tt.transaction_uom,

            wnd.delivery_id,

            tt.subinventory_code,

            msi.segment1,

            wdd.source_line_number,

            wdd.source_line_id,

            tt.transaction_date,

            wdd.source_header_number,

            tt.expected_arrival_date,

            gp.period_code

   ORDER BY org.organization_name DESC;