How to check RTP stuck records?

Query for -- How to check RTP (Receiving Open Interface (ROI) Transactions) stuck records.....


 SELECT RTI.*, rti.DESTINATION_TYPE_CODE,
 rti.interface_transaction_id   --           'interface_transaction_id'
, rhi.header_interface_id       --                 'header_interface_id'
, mp.ORGANIZATION_CODE         --                  'Org'
, rti.source_document_code     --                  'Type'
, pha.segment1                --                   'PO Number'
, pla.line_num                --                   'PO Line'
, to_char(rti.transaction_date, 'dd-MON-yyyy')  -- 'Transaction Date'
, rti.transaction_type      
, rti.PROCESSING_MODE_CODE
, rti.processing_status_code
, rhi.processing_status_code
, to_char(rti.creation_date, 'dd-MON-yyyy')  creation_date
, rti.quantity
, msi.segment1                              --     'Item'
, msi.item_type                            --      'Item Type'
, rti.item_description
, poh.error_message
, replace(poh.error_message,chr(10),' ') ,msi.organization_id--,poh.*
--, replace(pot.error_message,chr(10),' ')
FROM   po.po_interface_errors         poh
,      po.po_interface_errors         pot
,      po.rcv_transactions_interface  rti
,      po.rcv_headers_interface       rhi
,      inv.mtl_parameters             mp
,      po.po_headers_all              pha
,      po.po_lines_all                pla
,      inv.mtl_system_items_b         msi
WHERE 1=1
AND   rti.header_interface_id      = rhi.header_interface_id   (+)
AND   rti.interface_transaction_id = pot.interface_transaction_id (+)
AND   rhi.header_interface_id      = poh.interface_header_id (+)
--    mapping to PO
AND   rti.po_header_id = pha.po_header_id AND pha.org_id=8527
AND   rti.po_header_id = pla.po_header_id
AND   rti.po_line_id = pla.po_line_id
--    mapping to item
AND   mp.organization_id = msi.organization_id AND msi.organization_id=106
AND   rti.item_id = msi.inventory_item_id
 and   pha.segment1='1008007890' --in ('20231192' ,'20230863')-- PUT PO NUMBER HERE
--AND pla.line_num IN (14,57)
Order by mp.ORGANIZATION_CODE, pha.segment1, pla.line_num

Profile Option Values for Responsibility

Profile Option Values for Responsibility

SELECT   resp.responsibility_name,
           SUBSTR (pro1.user_profile_option_name, 1, 60) Profile,
           DECODE (pov.level_id,
                   10001,
                   'Site',
                   10002,
                   'Application',
                   10003,
                   'Resp',
                   10004,
                   'User')
              Option_Level,
           DECODE (pov.level_id,
                   10001,
                   'Site',
                   10002,
                   appl.application_short_name,
                   10003,
                   resp.responsibility_name,
                   10004,
                   u.user_name)
              Level_Value,
           NVL (pov.profile_option_value, 'Is Null') Profile_option_Value
    FROM   fnd_profile_option_values pov,
           fnd_responsibility_tl resp,
           fnd_application appl,
           fnd_user u,
           fnd_profile_options pro,
           fnd_profile_options_tl pro1
   WHERE       1 = 1
           --AND pro1.user_profile_option_name LIKE ('%MO: Operating Unit%')
           AND pro.profile_option_name = pro1.profile_option_name
           AND pro.profile_option_id = pov.profile_option_id
           AND UPPER (resp.responsibility_name) LIKE
                 UPPER ('%INV') /* comment this line  if you need to check profiles for all responsibilities */
           AND pov.level_value = resp.responsibility_id(+)
           AND pov.level_value = appl.application_id(+)
           AND pov.level_value = u.user_id(+) and resp.language='US' and pro1.language='US'
ORDER BY   1, 2

Query for Open PO - Purchase Order with Item Attachments

Query for Open PO - Purchase Order with Item Attachments

SELECT line_num,item_number,PO_NUMBER ,
                item_description,
                unit_meas_lookup_code,quantity, unit_price,
                line_status, po_line_id from (
SELECT DISTINCT PLV.line_num, msi1.segment1 item_number, (select segment1 from po_headers where po_header_id= PLV.po_header_id) PO_NUMBER,
                PLV.item_description item_description,
                PLV.unit_meas_lookup_code, PLV.quantity, PLV.unit_price,
                NVL (PLV.closed_code, 'OPEN') line_status, PLV.po_line_id
           FROM po_lines PLV,
                ( SELECT msi.*
        FROM apps.fnd_document_datatypes   dat,
             apps.fnd_document_entities_tl det,
             apps.fnd_documents_tl         dt,
             apps.fnd_documents            d,
             apps.fnd_attached_documents   ad,
             apps.fnd_documents_long_text  fdlt,
             apps.fnd_documents_short_text fdst,
             apps.mtl_system_items_b msi
       WHERE d.document_id = ad.document_id
         AND dt.document_id = d.document_id
         AND dt.LANGUAGE = USERENV('LANG')
         AND d.datatype_id = dat.datatype_id
         AND dat.LANGUAGE = USERENV('LANG')
         AND ad.entity_name = det.data_object_code
         AND det.LANGUAGE = USERENV('LANG')
         AND ad.pk1_value = msi.organization_id
         AND ad.pk2_value = msi.inventory_item_id
         --AND dt.description = v_chr_desc --TRIM(rec_attach.description)
         AND dt.media_id = fdlt.media_id(+)
         AND dt.media_id = fdst.media_id(+)
         AND ad.entity_name = 'MTL_SYSTEM_ITEMS'
         AND dat.user_name IN ('Long Text', 'Short Text')
         AND dt.description ='ATTACHMET'
         AND msi.inventory_item_status_code ='Active'
        -- AND msi.segment1 = 'VE3006'
         AND msi.organization_id = 701) msi1,
                financials_system_parameters fsp,
                po_line_locations pllv,
                po_distributions pdv,
                org_organization_definitions ood
          WHERE msi1.inventory_item_id = PLV.item_id
            AND NVL (msi1.organization_id, fsp.inventory_organization_id) = fsp.inventory_organization_id
            AND PLV.po_line_id = pllv.po_line_id
            AND PLV.po_line_id = pdv.po_line_id
            AND pllv.line_location_id = pdv.line_location_id
            AND ood.organization_id = pllv.ship_to_organization_id
            AND NVL (PLV.closed_code, 'OPEN') NOT IN
                                                 ('FINALLY CLOSED', 'CLOSED')
            AND NVL (PLV.cancel_flag, 'N') = 'N'
            AND EXISTS (
                   SELECT 'x'
                     FROM po_line_locations pll
                    WHERE pll.po_line_id = PLV.po_line_id
                      AND NVL (pll.closed_code, 'OPEN') NOT IN
                                                 ('FINALLY CLOSED', 'CLOSED')
                      AND NVL (pll.cancel_flag, 'N') = 'N'
                      AND pll.quantity - pll.quantity_received >
                                                         pll.qty_rcv_tolerance))
     
            

Receiving Transaction Processor Errors

+---------------------------------------------------------------------------+

RVCTP module: Receiving Transaction Processor
+---------------------------------------------------------------------------+

Current system time is ......

+---------------------------------------------------------------------------+

RVTUQ-010: Subroutine rvtuqsurcv() returned error

Cause:        Subroutine rvtuqsurcv() returned an internal error.

Action:        Note this error number and the actions you are trying to perform. Contact your system administrator for
RVTUQ-040: ORA-20001: APP--20110: Encountered the error in trigger JAI_RCV_RSL_AR

Cause:        A SQL error has occurred in RVTUQ.  The SQL error is &LSQL_ERR.

Action:        Take note of both this error number and the actions you were t
Cannot read value for profile option INV_DEBUG_TRACE in routine &ROUTINE.
Concurrent program RVCTP returned error

Cause:        RVCTP encountered an internal error.  Parameters: RVCTP,IMMEDIATE,37169,

Action:        Note both this error number and the actions you are trying to perform. Contact your system adm
RVTBM-008: Subroutine rvtbm_immediate_mngr() returned error

Cause:        Subroutine rvtbm_immediate_mngr() returned an internal error.

Action:        Note this error number and the actions you are trying to perform. Contact your syste
RVTBM-120: Subroutine rvtptcontrol() returned error

Cause:        Subroutine rvtptcontrol() returned an internal error.

Action:        Note this error number and the actions you are trying to perform. Contact your system administrator
Cannot read value for profile option INV_DEBUG_TRACE in routine &ROUTINE.
Concurrent Manager encountered an error while attempting to start your immediate concurrent program RVCTP. Routine &ROUTINE received a return code of failure.


Executing request completion options...