CSI Asset Tracking pending error transactions for all serial number/instance in your system

Description
Categories: Draft
Columns: Transaction Id, Transaction Type Id, Instance Id, Inventory Item Id, Active Start Date, Active End Date, Asset Creation Code, Old Location Id, New Location Id, Serial Number ...
When OAT is installed, INV transactions are interfaced to create/update related instances in Installed Base, then the transactions further interfaced to FA to create/update the related fixed assets.
Whether a INV transaction need be interfaced to FA is determined by the installed base transaction_status. If it is at 'PENDING' then it need be processed to FA.
There are two concurrent programs ... 
When OAT is installed, INV transactions are interfaced to create/update related instances in Installed Base, then the transactions further interfaced to FA to create/update the related fixed assets.
Whether a INV transaction need be interfaced to FA is determined by the installed base transaction_status. If it is at 'PENDING' then it need be processed to FA.
There are two concurrent programs to process 'PENDING' install base transactions to Oracle Assets: 'Create Asset: Interface Inventory Transaction to Oracle Assets' and 'Interface Move Transaction to Oracle Assets'
   more
SELECT citd.transaction_id,
                         ct.transaction_type_id,
                       citd.instance_id,
                       citd.inventory_item_id,
                       citd.active_start_date,
                       citd.active_end_date,
                       msi.asset_creation_code,
                       ciih.old_location_id,
                       ciih.new_location_id,
                       citd.serial_number,
                       citd.lot_number,
                       ct.transaction_type_id,
                      citd.transaction_quantity,
                      citd.source_txn_type_name,
                      cia.fa_asset_id,
                      cia.asset_quantity,
                      cia.active_end_date ,
                      cia.fa_location_id IB_fa_location,
                      cia.fa_sync_flag,
                      cte.processed_flag,
                      cte.error_text,
                      cia.active_end_date
             FROM csi_transactions ct,
                     csi_item_instances_h ciih,
                     csi_inst_txn_details_v citd,
                     csi_i_assets cia,
                     mtl_system_items_b msi,
                     csi_txn_errors cte
           WHERE ct.transaction_status_code = 'PENDING'
               AND ct.transaction_id = ciih.transaction_id
               AND ciih.instance_id = citd.instance_id
               AND ciih.transaction_id  = citd.transaction_id
               AND cia.instance_id (+)  = citd.instance_id
               AND msi.inventory_item_id  = citd.inventory_item_id
               AND msi.organization_id = citd.inv_master_organization_id
               AND NOT EXISTS
                   (SELECT 'x'
                       FROM csi_inst_txn_details_v citd2
                      WHERE citd2.instance_id  = citd.instance_id
                          AND citd2.transaction_id  < citd.transaction_id
                         AND citd2.transaction_status_code = 'PENDING'
                     )
              AND cte.transaction_id (+) = ct.transaction_id
Parameter Name SQL text Validation
Status
ct.transaction_status_code=:status
LOV