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

Description

Categories: Draft

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 ... more

Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS

Copy

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