Skip to main content

Hello,

 

We are using Apps 10, UPD13.

 

We have sales parts in SITE1 (approx. 500K) and are trying to create the same sales parts in SIT99 using the script below but are getting an error message that the part already exists. They already exist as inventory parts in both SITE1 and SIT99. 

 

Is anyone able to help us with resolving the error and creating the sales parts in SIT99 that already exist in SITE1?

 

Thank you,

Joann

 

DECLARE
  p0 VARCHAR2(32) := NULL;
  p1 VARCHAR2(32) := NULL;
  p2 VARCHAR2(32) := NULL;
  attr_ VARCHAR2(32000);
  action_ VARCHAR2(30) := 'DO';

 

CURSOR get_part_info_ IS
    SELECT i.CONTRACT, i.PART_NO, p.CF$_SALES_GROUP
    FROM  inventory_part i
     left outer join sales_part s
     on i.part_no = s.part_no
     and i.contract = s.contract
     left outer join
    PRODUCT_CODE_X_REF_CLV p
    on i.second_commodity = p.cf$_comm_group_2
    and i.part_product_code = p.cf$_product_code
    where
     i.contract = 'SIT99'
     and i.part_no like '%2'
     and length(i.part_no) = 11
     and s.part_no is null
     and p.CF$_SALES_GROUP is not null
     ;
   part_ get_part_info_%rowtype;

 

BEGIN

 

OPEN get_part_info_ ;
  FETCH get_part_info_  INTO part_;
  CLOSE get_part_info_ ;

 

   FOR rec_ IN get_part_info_ LOOP
--        Client_SYS.Clear_Attr( attr_);
--        Client_SYS.Add_To_Attr('CONTRACT', part_.CONTRACT, attr_);
--        Client_SYS.Add_To_Attr('COMPANY', '99', attr_);
--        Client_SYS.Add_To_Attr('CATALOG_NO', part_.PART_NO, attr_);
--        Client_SYS.Add_To_Attr('PART_NO', part_.PART_NO, attr_);
--        Client_SYS.Add_To_Attr('CONV_FACTOR', 1, attr_);
--        Client_SYS.Add_To_Attr('INVERTED_CONV_FACTOR', 1, attr_);
--        Client_SYS.Add_To_Attr('PRICE_CONV_FACTOR', 1, attr_);
--        Client_SYS.Add_To_Attr('LIST_PRICE', 0, attr_);
--        Client_SYS.Add_To_Attr('CATALOG_GROUP', part_.CF$_SALES_GROUP, attr_);
--        Client_SYS.Add_To_Attr('CATALOG_TYPE_DB', 'INV', attr_);
--        Client_SYS.Add_To_Attr('CLOSE_TOLERANCE', 0, attr_);
--        Client_SYS.Add_To_Attr('TAXABLE', 'True', attr_);
--        Client_SYS.Add_To_Attr('SOURCING_OPTION_DB', 'INVENTORYORDER', attr_);
--        Client_SYS.Add_To_Attr('SALES_UNIT_MEAS', 'EA', attr_);
--        Client_SYS.Add_To_Attr('PRICE_UNIT_MEAS', 'EA', attr_);
--        Client_SYS.Add_To_Attr('SALES_PRICE_UNIT_ID', '*', attr_);
--        Client_SYS.Add_To_Attr('SALES_PRICE_UNIT_ID', '*', attr_);
--        Client_SYS.Add_To_Attr('SALES_PRICE_GROUP_ID', '*', attr_);
--        Client_SYS.Add_To_Attr('USE_PRICE_INCL_TAX', 'False', attr_);
--        SALES_PART_API.new__(p0, p1, p2, attr_, action_);

sales_part_api.create_sales_part (
    part_.contract,         --   contract_                IN VARCHAR2,
    part_.part_no,          --   catalog_no_              IN VARCHAR2,
    1,                      --   conv_factor_             IN NUMBER,
    1,                      --   inverted_conv_factor_    IN NUMBER,
    1,                      --   price_conv_factor_       IN NUMBER,
    0,                      --   list_price_              IN NUMBER,
    0,                      --   expected_average_price_  IN NUMBER,
    0,                      --   minimum_qty_             IN NUMBER,
    0,                      --   close_tolerance_         IN NUMBER,
    NULL,            --   date_of_replacement_     IN DATE,
    'Inventory Order',      --   sourcing_option_         IN VARCHAR2,
    'Active part',          --   activeind_               IN VARCHAR2,
    'True',                 --   taxable_                 IN VARCHAR2,
    'False',                --   export_to_external_app_  IN VARCHAR2,
    'Do not create SM object',  --   create_sm_object_option_ IN VARCHAR2,
    NULL,                   --   rule_id_                 IN VARCHAR2,
    'EA',                   --   sales_unit_meas_         IN VARCHAR2,
    'EA',                   --   price_unit_meas_         IN VARCHAR2,
    '*',                    --   sales_price_group_id_    IN VARCHAR2,
    part_.CF$_SALES_GROUP,  --   catalog_group_           IN VARCHAR2,
    NULL,                   --   discount_group_          IN VARCHAR2,
    NULL,                   --   tax_code_                IN VARCHAR2,
    NULL,                   --   replacement_part_no_     IN VARCHAR2,
    NULL,                   --   delivery_type_           IN VARCHAR2,
    NULL,                   --   sales_part_rebate_group_ IN VARCHAR2,
    NULL                    --   tax_class_id_            IN VARCHAR2 DEFAULT NULL 
   );
END LOOP;
END;


Error report -
ORA-20112: SalesPart.FND_RECORD_EXIST: The Sales Part already exists.
ORA-06512: at "NORT1APP.ERROR_SYS", line 140
ORA-06512: at "NORT1APP.ERROR_SYS", line 422
ORA-06512: at "NORT1APP.SALES_PART_API", line 2605
ORA-06512: at "NORT1APP.SALES_PART_API", line 2609
ORA-06512: at "NORT1APP.SALES_PART_API", line 8170
ORA-06512: at "NORT1APP.SALES_PART_API", line 8255
ORA-06512: at "NORT1APP.SALES_PART_API", line 8270
ORA-06512: at "NORT1APP.SALES_PART_API", line 6226
ORA-06512: at "NORT1APP.SALES_PART_API", line 6231
ORA-06512: at line 64
ORA-06512: at line 64
 

Hi @jtobin ,

 

You could try to add a test to make sure that the sales part does not yet exist.

It would be something like:

IF (Sales_Part_API.Exists(part_.contract, part_.part_no) <> TRUE) THEN
   sales_part_api.create_sales_part (
      part_.contract,         --   contract_                IN VARCHAR2,
      part_.part_no,          --   catalog_no_              IN VARCHAR2,
      …
      );
END IF;


Best regards,

Christian

Hi @jtobin ,

why not creating a simple migration job (procedure name MIGRATE_SOURCE_DATA) instead of wrting a PL/SQL program for this purpose?

Migration tool (search for “Migration Job” in the Navigator).

Source_name: SALES_PART

Where clause: contract=’SITE1’

In the tab “Method list” insert the view_name SALES_PART (the target).

Go to the “Source mapping” tab and change the value of the source column for CONTRACT to SIT99.

Execute your job.

You can also join other database views in the “source_name” or add other “Where clauses” if you want.

/Jens

@ChristianJ  and @Jens  Thank you, both, for your assistance.  I have forwarded your suggestions to the user.

I would try to simplify the query like this to not select already existing parts in the site and to make it possible to run several times in case some of the parts in error:

AND NOT EXISTS (SELECT NULL FROM inventory_part t WHERE t.part_no = i.part_no AND t.contract = 'SIT99')

Replying a year later...

I'll bet records in PRODUCT_CODE_X_REF_CLV aren't unique for a given combination of Commodity Groups 1 and 2. I recommend fixing the data, then setting those fields as an alternate composite key if that's the design intent.

 

CURSOR get_part_info_ IS
   SELECT i.contract,
          i.part_no,
          p.cf$_sales_group
     FROM inventory_part i
LEFT JOIN sales_part s
       ON i.part_no = s.part_no
      AND i.contract = s.contract
LEFT JOIN product_code_x_ref_clv p -- <-- join blows up cardinality
       ON i.second_commodity  = p.cf$_comm_group_2
      AND i.part_product_code = p.cf$_product_code
    WHERE i.contract = 'SIT99'
      AND i.part_no LIKE '%2'
      AND LENGTH(i.part_no) = 11
      AND s.part_no IS NULL
      AND p.cf$_sales_group IS NOT NULL;

 

Reply


Terms & ConditionsCookie settings