Oracle Apps Miscellaneous Receipt(RG Update)

Step 1  : get the data from User ( Item name (Segment1), Organization code )


Step 2  : Check what are the mandatory column in front end ( Oracle Apps from level )

Mandatory column : source_code, source_line_id, source_header_id, process_flag,
                  last_update_date, last_updated_by, creation_date, created_by,
                  last_update_login, inventory_item_id, organization_id,
                  transaction_quantity,PRIMARY_QUANTITY, transaction_uom, transaction_date,
                  subinventory_code, locator_id, transaction_type_id,TRANSACTION_MODE
                 
Column value    : 1, 1, 1, 1, sysdate, 1150, SYSDATE, 1150,1150,
             misb.inventory_item_id, misb.organization_id,
             CONCAT('-',SUM (moqd.primary_transaction_quantity)) qty,    ------  Quantity must be negative value
                SUM(NVL(MOQD.TRANSACTION_QUANTITY,0)),
                transaction_uom_code, SYSDATE, moqd.subinventory_code,
                    inventory_location_id, 100001,3         
      
Step 3: Check what are the mandatory column in 'mtl_transactions_interface'  table level

        account id - distribution_account_id column in 'mtl_transactions_interface'  table
       ( select * from gl_code_combinations_kfv where CONCATENATED_SEGMENTS = '14.000000.51117.00.000.00.0000')
       select * from mtl_transaction_types  where TRANSACTION_TYPE_NAME = 'Miscellaneous Issue(RG Update)'

Note : quantity must be > 0


INSERT INTO mtl_transactions_interface
            (source_code, source_line_id, source_header_id, process_flag,
             last_update_date, last_updated_by, creation_date, created_by,
             last_update_login, inventory_item_id, organization_id,
             transaction_quantity, transaction_uom, transaction_date,
             subinventory_code, locator_id, transaction_type_id,TRANSACTION_MODE)
   (   SELECT 1, 1, 1, 1, SYSDATE, 1150, SYSDATE, 1150, 1150, misb.inventory_item_id,
       misb.organization_id, xit.current_on_hand, primary_uom_code, SYSDATE,
       xit.subinventory,
       (SELECT DISTINCT inventory_location_id
                   FROM mtl_item_locations_kfv
                  WHERE concatenated_segments =
                                            xit.LOCATOR
                    AND subinventory_code = xit.subinventory
                    AND organization_id = misb.organization_id
                    )
                                                        inventory_location_id,
       100002, 3
  FROM mtl_system_items_b misb,
       org_organization_definitions ood,
       xx_item_temp xit
 WHERE 1 = 1
   AND ood.organization_id = misb.organization_id
   AND misb.segment1 = xit.part_no
   AND ood.organization_code = xit.org_name)


--------------------------------------------------------------------------------------