Question

Is there any way to Supplier no created with (city name and serial no)?

  • 12 December 2020
  • 1 reply
  • 294 views
A
Rank
Userlevel 5
Badge +8

Hi Guys,

 

Kindly assist us that how generate automatically supplier no with City name and serial no at  the creating of Supplier (IFS 10, Aurena).

AK
This topic has been closed for comments

1 reply

durette
Rank
Userlevel 7
Badge +18

Hi,

I’m not aware of a way to do this, but here’s a custom solution. The code is below.

 

I created a custom logical unit called CSupplierPrefix to hold prefixes and their current IDs.

 

Because this is at the database level, it should also work with Aurena.

 

From Enterprise Explorer, the custom page for configuring this solution is ifsapf:cpgtbwCSupplierPrefix

 

If you want to use one of these, create a supplier where the ID is just the prefix.

 

After saving, refresh the screen. The supplier ID will pull from the series.

 

and the ID will be incremented.

 

 


/*
Create a custom logical unit CSupplierPrefix with attributes PREFIX and ID.
*/


DECLARE
   lu_        Custom_Lus_TAB.Lu%TYPE := 'CSupplierPrefix';
   lu_type_   Custom_Lus_TAB.Lu_Type%TYPE := Custom_Field_Lu_Types_API.DB_CUSTOM_LU;
   info_msg_  CLOB;
   info_page_msg_  CLOB;
BEGIN
   info_msg_ := NULL;
   info_msg_ := Message_SYS.Construct('Custom Lu Export');
   Message_SYS.Add_Attribute(info_msg_, 'TABLE_NAME', 'C_SUPPLIER_PREFIX_CLT');
   Message_SYS.Add_Attribute(info_msg_, 'VIEW_NAME', 'C_SUPPLIER_PREFIX_CLV');
   Message_SYS.Add_Attribute(info_msg_, 'PACKAGE_NAME', 'C_SUPPLIER_PREFIX_CLP');
   Message_SYS.Add_Attribute(info_msg_, 'PROMPT', 'Supplier Prefixes');
   Message_SYS.Add_Attribute(info_msg_, 'PUBLISHED', 'FALSE');
   Message_SYS.Add_Attribute(info_msg_, 'USED', 'TRUE');
   Message_SYS.Add_Attribute(info_msg_, 'ROWKEY', '9505790F19664C2F9EEAB4803B71F2EC');
   Message_SYS.Add_Attribute(info_msg_, 'NOTE', '');
   Custom_Lus_API.Register(lu_, info_msg_);

   info_msg_ := NULL;
   info_msg_ := Message_SYS.Construct('Custom Field Attributes');
   Message_SYS.Add_Attribute(info_msg_, 'DATA_TYPE', 'NUMBER');
   Message_SYS.Add_Attribute(info_msg_, 'FORMAT', 'UNFORMATTED');
   Message_SYS.Add_Attribute(info_msg_, 'DATA_LENGTH', '');
   Message_SYS.Add_Attribute(info_msg_, 'DEFAULT_VALUE_TYPE', 'NO_DEFAULT_VALUE');
   Message_SYS.Add_Attribute(info_msg_, 'DEFAULT_VALUE', '');
   Message_SYS.Add_Attribute(info_msg_, 'MANDATORY', 'FALSE');
   Message_SYS.Add_Attribute(info_msg_, 'INSERTABLE', 'TRUE');
   Message_SYS.Add_Attribute(info_msg_, 'UPDATEABLE', 'TRUE');
   Message_SYS.Add_Attribute(info_msg_, 'QUERYABLE', 'TRUE');
   Message_SYS.Add_Attribute(info_msg_, 'LOV', 'TRUE');
   Message_SYS.Add_Attribute(info_msg_, 'INDEXED', 'FALSE');
   Message_SYS.Add_Attribute(info_msg_, 'PRIVATE', 'FALSE');
   Message_SYS.Add_Attribute(info_msg_, 'INDEX_NAME', '');
   Message_SYS.Add_Attribute(info_msg_, 'PROMPT', 'Id');
   Message_SYS.Add_Attribute(info_msg_, 'CUSTOM_FIELD_TYPE', 'PERSISTENT_FIELD');
   Message_SYS.Add_Attribute(info_msg_, 'CUSTOM_FIELD_IMPL_TYPE', 'NOT_APPLICABLE');
   Message_SYS.Add_Attribute(info_msg_, 'EXPRESSION', '');
   Message_SYS.Add_Attribute(info_msg_, 'METADATA', '');
   Message_SYS.Add_Attribute(info_msg_, 'LU_REFERENCE', '');
   Message_SYS.Add_Attribute(info_msg_, 'UI_OBJECT', 'DEFAULT');
   Message_SYS.Add_Attribute(info_msg_, 'PUBLISHED', 'TRUE');
   Message_SYS.Add_Attribute(info_msg_, 'USED', 'TRUE');
   Message_SYS.Add_Attribute(info_msg_, 'ROWKEY', '9FF7934EF59A46399931C13519B1ED39');
   Message_SYS.Add_Attribute(info_msg_, 'READ_ONLY_EXPRESSION', 'FALSE');
   Message_SYS.Add_Attribute(info_msg_, 'NOTE', '');
   Message_SYS.Add_Attribute(info_msg_, 'ENABLED_ON_REPORTS', 'TRUE');
   Message_SYS.Add_Attribute(info_msg_, 'CONSTRAINT_TYPE', 'RESTRICTED');
   Message_SYS.Add_Attribute(info_msg_, 'LOV_VIEW', '');
   Message_SYS.Add_Attribute(info_msg_, 'ALTERNATE_KEY', 'FALSE');
   Custom_Field_Attributes_API.Register(lu_, lu_type_, 'ID', info_msg_);

   info_msg_ := NULL;
   info_msg_ := Message_SYS.Construct('Custom Field Attributes');
   Message_SYS.Add_Attribute(info_msg_, 'DATA_TYPE', 'STRING');
   Message_SYS.Add_Attribute(info_msg_, 'FORMAT', 'UNFORMATTED');
   Message_SYS.Add_Attribute(info_msg_, 'DATA_LENGTH', '100');
   Message_SYS.Add_Attribute(info_msg_, 'DEFAULT_VALUE_TYPE', 'NO_DEFAULT_VALUE');
   Message_SYS.Add_Attribute(info_msg_, 'DEFAULT_VALUE', '');
   Message_SYS.Add_Attribute(info_msg_, 'MANDATORY', 'FALSE');
   Message_SYS.Add_Attribute(info_msg_, 'INSERTABLE', 'TRUE');
   Message_SYS.Add_Attribute(info_msg_, 'UPDATEABLE', 'TRUE');
   Message_SYS.Add_Attribute(info_msg_, 'QUERYABLE', 'TRUE');
   Message_SYS.Add_Attribute(info_msg_, 'LOV', 'TRUE');
   Message_SYS.Add_Attribute(info_msg_, 'INDEXED', 'TRUE');
   Message_SYS.Add_Attribute(info_msg_, 'PRIVATE', 'FALSE');
   Message_SYS.Add_Attribute(info_msg_, 'INDEX_NAME', 'CF$_C_SUPPLIER_PR3121558805_IX');
   Message_SYS.Add_Attribute(info_msg_, 'PROMPT', 'Prefix');
   Message_SYS.Add_Attribute(info_msg_, 'CUSTOM_FIELD_TYPE', 'PERSISTENT_FIELD');
   Message_SYS.Add_Attribute(info_msg_, 'CUSTOM_FIELD_IMPL_TYPE', 'NOT_APPLICABLE');
   Message_SYS.Add_Attribute(info_msg_, 'EXPRESSION', '');
   Message_SYS.Add_Attribute(info_msg_, 'METADATA', '');
   Message_SYS.Add_Attribute(info_msg_, 'LU_REFERENCE', '');
   Message_SYS.Add_Attribute(info_msg_, 'UI_OBJECT', 'DEFAULT');
   Message_SYS.Add_Attribute(info_msg_, 'PUBLISHED', 'TRUE');
   Message_SYS.Add_Attribute(info_msg_, 'USED', 'TRUE');
   Message_SYS.Add_Attribute(info_msg_, 'ROWKEY', 'D6E479746BA24029B00E3BB98525D94A');
   Message_SYS.Add_Attribute(info_msg_, 'READ_ONLY_EXPRESSION', 'FALSE');
   Message_SYS.Add_Attribute(info_msg_, 'NOTE', '');
   Message_SYS.Add_Attribute(info_msg_, 'ENABLED_ON_REPORTS', 'TRUE');
   Message_SYS.Add_Attribute(info_msg_, 'CONSTRAINT_TYPE', 'RESTRICTED');
   Message_SYS.Add_Attribute(info_msg_, 'LOV_VIEW', '');
   Message_SYS.Add_Attribute(info_msg_, 'ALTERNATE_KEY', 'TRUE');
   Custom_Field_Attributes_API.Register(lu_, lu_type_, 'PREFIX', info_msg_);

   info_msg_ := NULL;
   info_page_msg_ :=  NULL;
   info_msg_ := Message_SYS.Construct('Custom Pages');
   Message_SYS.Add_Attribute(info_msg_, 'LU', 'CSupplierPrefix');
   Message_SYS.Add_Attribute(info_msg_, 'PACKAGE_NAME', 'C_SUPPLIER_PREFIX_CLP');
   Message_SYS.Add_Attribute(info_msg_, 'PAGE_TITLE', 'Supplier Prefixes and IDs');
   Message_SYS.Add_Attribute(info_msg_, 'VIEW_NAME', 'C_SUPPLIER_PREFIX_CLV');
   Message_SYS.Add_Attribute(info_msg_, 'PAGE_TYPE', 'TABLE_WINDOW');
   Message_SYS.Add_Attribute(info_msg_, 'DEFAULT_HOME', 'TRUE');
   Message_SYS.Add_Attribute(info_msg_, 'NOTES', '');
info_page_msg_ := info_page_msg_ ||'<?xml version="1.0" encoding="utf-16"?>
<PageLayout xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <PageItems>
    <PageItem>
      <Name>CF$_ID</Name>
      <ControlType>Column</ControlType>
      <Ordinal>1</Ordinal>
      <Properties>
        <Property Name="SqlColumn">
          <Value>CF$_ID</Value>
        </Property>
        <Property Name="Ordinal">
          <Value>1</Value>
        </Property>
        <Property Name="RecordSelectorItem">
          <Value>True</Value>
        </Property>
        <Property Name="Insertable">
          <Value>True</Value>
        </Property>
        <Property Name="UpdatePermissions">
          <Value>UpdateAllowed</Value>
        </Property>
        <Property Name="Queryable">
          <Value>True</Value>
        </Property>
        <Property Name="Visible">
          <Value>True</Value>
        </Property>
        <Property Name="Location">
          <Value>0, 0</Value>
        </Property>
        <Property Name="FieldDataType">
          <Value>Number</Value>
        </Property>
        <Property Name="FieldFormat">
          <Value>Unformated</Value>
        </Property>
        <Property Name="LogicalParent">
          <Value>[NaturalParent]</Value>
        </Property>
        <Property Name="Text">
          <Value>Id</Value>
        </Property>
        <Property Name="Width">
          <Value>97</Value>
        </Property>
        <Property Name="MultiLine">
          <Value>False</Value>
        </Property>
        <Property Name="FieldMaxLength">
          <Value>0</Value>
        </Property>
        <Property Name="CharacterCasing">
          <Value>Normal</Value>
        </Property>
        <Property Name="TextAlign">
          <Value>Right</Value>
        </Property>
        <Property Name="LinkAware">
          <Value>False</Value>
        </Property>
        <Property Name="TabIndex">
          <Value>1</Value>
        </Property>
      </Properties>
    </PageItem>
    <PageItem>
      <Name>CF$_PREFIX</Name>
      <ControlType>Column</ControlType>
      <Ordinal>2</Ordinal>
      <Properties>
        <Property Name="SqlColumn">
          <Value>CF$_PREFIX</Value>
        </Property>
        <Property Name="Ordinal">
          <Value>2</Value>
        </Property>
        <Property Name="RecordSelectorItem">
          <Value>False</Value>
        </Property>
        <Property Name="Insertable">
          <Value>True</Value>
        </Property>
        <Property Name="UpdatePermissions">
          <Value>UpdateAllowed</Value>
        </Property>
        <Property Name="Queryable">
          <Value>True</Value>
        </Property>
        <Property Name="Visible">
          <Value>True</Value>
        </Property>
        <Property Name="Location">
          <Value>0, 0</Value>
        </Property>
        <Property Name="FieldDataType">
          <Value>String</Value>
        </Property>
        <Property Name="FieldFormat">
          <Value>Unformated</Value>
        </Property>
        <Property Name="LogicalParent">
          <Value>[NaturalParent]</Value>
        </Property>
        <Property Name="Text">
          <Value>Prefix</Value>
        </Property>
        <Property Name="Width">
          <Value>194</Value>
        </Property>
        <Property Name="MultiLine">
          <Value>False</Value>
        </Property>
        <Property Name="FieldMaxLength">
          <Value>100</Value>
        </Property>
        <Property Name="UseEditor">
          <Value>True</Value>
        </Property>
        <Property Name="CharacterCasing">
          <Value>Normal</Value>
        </Property>
        <Property Name="TextAlign">
          <Value>Left</Value>
        </Property>
        <Property Name="LinkAware">
          <Value>False</Value>
        </Property>
        <Property Name="TabIndex">
          <Value>2</Value>
        </Property>
      </Properties>
    </PageItem>
  </PageItems>
</PageLayout>';
   Message_SYS.Add_Clob_Attribute(info_msg_, 'XML_DATA', info_page_msg_);
   Message_SYS.Add_Attribute(info_msg_, 'ROWKEY', '81118365C3C64E5DB17CD8520F5DEE75');
   Custom_Pages_API.Register('cpgtbwCSupplierPrefix', info_msg_);

END;
/

BEGIN
   Custom_Lus_API.deploy__('CSupplierPrefix');
END;
/

/*
Create custom package
*/

CREATE OR REPLACE PACKAGE c_supplier_prefix_api AUTHID DEFINER IS
module_  CONSTANT VARCHAR2(25) := 'FNDBAS';
lu_name_ CONSTANT VARCHAR2(25) := 'CSupplierPrefix';

FUNCTION is_a_prefix(
   supplier_id_ IN supplier_info_tab.supplier_id%TYPE) RETURN BOOLEAN;

FUNCTION get_suffix_id(
   prefix_ IN c_supplier_prefix_clv.cf$_prefix%TYPE) RETURN c_supplier_prefix_clv.cf$_id%TYPE;

PROCEDURE increment_suffix_id(
   prefix_ IN c_supplier_prefix_clv.cf$_prefix%TYPE);

PROCEDURE init;

END c_supplier_prefix_api;
/

CREATE OR REPLACE PACKAGE BODY c_supplier_prefix_api IS

FUNCTION is_a_prefix(
   supplier_id_ IN supplier_info_tab.supplier_id%TYPE) RETURN BOOLEAN
IS
BEGIN
   FOR rec_ IN (
      SELECT /*+ FIRST_ROWS(1) */ 1
        FROM c_supplier_prefix_clv
       WHERE cf$_prefix = supplier_id_
   ) LOOP
      RETURN TRUE;
   END LOOP;
   RETURN FALSE;
END is_a_prefix;


FUNCTION get_suffix_id(
   prefix_ IN c_supplier_prefix_clv.cf$_prefix%TYPE) RETURN c_supplier_prefix_clv.cf$_id%TYPE
IS
   return_ c_supplier_prefix_clv.cf$_id%TYPE;
BEGIN
   SELECT cf$_id
     INTO return_
     FROM c_supplier_prefix_clv
    WHERE cf$_prefix = prefix_;
   RETURN return_;
END get_suffix_id;


PROCEDURE increment_suffix_id(
   prefix_ IN c_supplier_prefix_clv.cf$_prefix%TYPE)
IS
   id_         c_supplier_prefix_clv.cf$_id%TYPE;
   info_       VARCHAR2(32767);
   objkey_     c_supplier_prefix_clv.objkey%TYPE;
   objversion_ c_supplier_prefix_clv.objversion%TYPE;
   attr_       VARCHAR2(32767);
BEGIN
   id_ := get_suffix_id(prefix_) + 1;
   SELECT objkey, objversion
     INTO objkey_, objversion_
     FROM c_supplier_prefix_clv
    WHERE cf$_prefix = prefix_;
   client_sys.clear_attr(attr_);
   client_sys.add_to_attr('CF$_ID', id_, attr_);
   c_supplier_prefix_clp.modify__(info_, objkey_, objversion_, attr_, 'DO');
END increment_suffix_id;


PROCEDURE init IS BEGIN NULL; END init;

END c_supplier_prefix_api;
/


/*
Create custom Oracle trigger
*/

CREATE OR REPLACE TRIGGER c_supplier_info_bi
   BEFORE INSERT ON supplier_info_tab
   FOR EACH ROW
DECLARE
   prefix_ supplier_info_tab.supplier_id%TYPE;
BEGIN
   prefix_ := :new.supplier_id;
   IF c_supplier_prefix_api.is_a_prefix(prefix_) THEN
      :new.supplier_id :=
         prefix_ || c_supplier_prefix_api.get_suffix_id(prefix_);
      c_supplier_prefix_api.increment_suffix_id(prefix_);
   END IF;
END;
/

 

If you find this forum useful, please consider paying back to the community in your own areas of expertise. -- Kevin Durette - https://www.linkedin.com/in/kevindurette
Terms & ConditionsCookie settings