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).
Page 1 / 1
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;
/
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.