PO Receipt and DELIVER in Oracle EBS r12

 SELECT DISTINCT receipt_num, rt.creation_date, itm.segment1, itm.description,

                mtt.transaction_quantity tq, mtt.primary_quantity pq,

                mtt.actual_cost ac_cost, mtt.transaction_cost t_cost

           FROM rcv_shipment_headers rsh,

                rcv_shipment_lines rsl,

                rcv_transactions rt,

                mtl_material_transactions mtt,

                mtl_system_items_b itm

          WHERE rsh.shipment_header_id = rsl.shipment_header_id

            AND rsl.shipment_header_id = rt.shipment_header_id

            AND rsl.shipment_line_id = rt.shipment_line_id

            AND rt.transaction_id = mtt.rcv_transaction_id

            AND mtt.inventory_item_id = itm.inventory_item_id

            AND rt.transaction_type = 'DELIVER'

            AND mtt.transaction_type_id = 18

            AND mtt.creation_date BETWEEN TO_DATE ('01-FEB-2022') AND TO_DATE ('28-FEB-2022')

Find the transactions interface Pending Transactions in Oracle EBS r12

 select a.transaction_status_code, a.PROCESSING_STATUS_CODE,a.PROCESSING_MODE_CODE, a.FROM_ORGANIZATION_ID, a.TRANSACTION_DATE, a.SUBINVENTORY,b.ORGANIZATION_CODE, b.ORGANIZATION_NAME,

a.TO_ORGANIZATION_ID,p.ORGANIZATION_CODE From_ORG_Code,p.ORGANIZATION_NAME From_ORG,  a.* 

from rcv_transactions_interface a , Org_organization_definitions b, Org_organization_definitions p

where  a.TO_ORGANIZATION_ID = b.ORGANIZATION_ID

and a.FROM_ORGANIZATION_ID = p.ORGANIZATION_ID(+)

--and a.PROCESSING_STATUS_CODE ='PENDING'

and a.transaction_status_code in('ERROR','PENDING')

select * from  rcv_transactions_interface a 

where a.transaction_status_code = 'ERROR' 

and a.PROCESSING_STATUS_CODE='COMPLETED'

and a.PROCESSING_MODE_CODE ='IMMEDIATE' 

--and a.TRANSACTION_DATE <= '29-JUN-2021'

and a.TO_ORGANIZATION_ID in (select b.ORGANIZATION_ID from Org_organization_definitions b where b.ORGANIZATION_NAME like '%-XX-%')

OPM Batch COSTALLOC in Oracle EBS r12

 SELECT   l.organization_code org_code, l.organization_name org_name,

            a.batch_no, SUM (NVL (b.cost_alloc, 0)) cost_alloc,

            DECODE (a.batch_status,

                    2, 'WIP',

                    3, 'Completed',

                    4, 'Closed'

                   ) bstatus

       FROM gme_batch_header a,

            gme_material_details b,

            org_organization_definitions l

      WHERE a.batch_id = b.batch_id

        AND b.organization_id = l.organization_id

        AND a.creation_date >= LAST_DAY (ADD_MONTHS (SYSDATE, -2)) + 1

        AND a.batch_status IN (3, 4)

     HAVING SUM (NVL (b.cost_alloc, 0)) <> 1

   GROUP BY l.organization_code,

            l.organization_name,

            a.batch_no,

            a.batch_status,

            a.creation_date

   ORDER BY org_code, cost_alloc DESC;

UnClosed Production Batch(OPM) in Oracle EBS r12

   

 SELECT   locations || '_XX' locations, org_code, org_name,

            COUNT (batch_id) numberofbatch, batch_status

       FROM (SELECT SUBSTR (o.organization_name, 1, 3) locations,

                    o.organization_code org_code,

                    o.organization_name org_name, bh.batch_id, bh.batch_no,

                    bh.actual_start_date, bh.actual_cmplt_date,

                    bh.batch_close_date,

                    DECODE (batch_status,

                            2, 'WIP',

                            3, 'Completed'

                           ) batch_status

               FROM gme_batch_header bh, org_organization_definitions o

              WHERE bh.batch_status IN (2, 3)

                AND bh.organization_id = o.organization_id

                AND TRUNC (bh.actual_start_date) > '31-DEC-2002')

   GROUP BY locations, org_code, org_name, batch_status

   ORDER BY 1;

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;