3 Replies Latest reply: Oct 16, 2013 2:54 PM by EdHayes3 RSS

    Sales Order Import

    726000
      I have created a simple script to insert data into the "oe_headers_iface_all" and "oe_lines_iface_all" tables (script below for reference). When I look in the interface tables the data looks fine and I can also see the data via the corrections screen in order management. If I import the orders using the validate only option then all looks ok. If I import the order the data from the header tables is purged but the data in the lines tables remains. No errors are reported in the log file or "oe_processing_msgs". If I look in the base tables then I can see the order header but I cannot see the order line. I'm confused, does naybody have any thoughts on this

      insert into oe_headers_iface_all (
      order_source_id,
      orig_sys_document_ref,
      creation_date,
      created_by,
      last_update_date,
      last_updated_by,
      operation_code,
      org_id,
      sold_to_org_id,
      invoice_to_org_id,
      order_type_id--,
      --booked_flag
      )
      values (
      1001 --order_source_id
      ,'13042011002' --orig_sys_document_ref
      ,sysdate --creation_date
      ,-1 --created_by
      ,sysdate --last_update_date
      ,-1 --last_updated_by
      ,'INSERT' --operation_code
      ,701 -- org_id -- eaga clean energy --
      ,1245977 sold_to_org_id po31 7dw-116 pelham road --
      ,498696 invoice_to_org_id
      ,1021 -- pv solar order
      --,'y' --booked_flag
      );


      insert into oe_lines_iface_all (
      order_source_id
      ,orig_sys_document_ref
      ,orig_sys_line_ref
      ,inventory_item_id
      ,ordered_quantity
      ,order_quantity_uom
      ,operation_code
      ,org_id
      ,created_by
      ,creation_date
      ,last_updated_by
      ,last_update_date
      ,request_date
      )
      values (
      1001 --order_source_id
      ,'13042011002' --orig_sys_document_ref
      ,'1' --orig_sys_line_ref
      ,239001 inventory_item_id 18402.00.eag zen power kp200 --
      , 1 --ordered_quantity
      , 'EA' -- order quantity uom --
      ,'INSERT' --operation_code
      ,701 -- org_id -- eaga clean energy
      , -1 --created_by
      , sysdate --creation_date
      , -1 --last_updated_by
      , sysdate --last_update_date
      , sysdate -- request_date --
      );
        • 1. Re: Sales Order Import
          user316761
          seems your order header was created successfully, but, lines having problem.

          It's is normal that oracle deletes the header and leaves lines table records (orphan). I had a TAR in the past - to clarify how can the header records be deleted while leaving the orphan lines. Oracle response was, users can upload data table by table, so they don't want to enforce any check. (it looks odd, oracle follows different standard in different module).


          are there any error message for lines?. is the item - customer order enabled?.

          Ganesan.

          --------------------------------------------------------------------------------------------------------------
          Doc ID: 189974.1
          cause: As per <BUG:2112092>
          The correction form does not have the ability to cascade deletions by design.
          Hence, this is not a bug.
          By design there is no attempt to maintain referential integrity in the
          interface tables.
          fix:
          There are two alternatives or work arounds:
          1. Set the Reject flag on the order header/lines to Y by checking the reject
          check box on the correction form.

          2. Physically, delete the lines and the order data.

          ------------------------------------------------------------------------------------------------------------
          • 2. Re: Sales Order Import
            siakhooi-2029222
            i faced this problem, eventually found that the inventory_item (inventory_item_id) has been set to inactive. after activate back in the Item Master, everything goes fine.
            • 3. Re: Sales Order Import
              EdHayes3

              How did you solve the problem?