3 Replies Latest reply: Jan 10, 2013 11:32 PM by Atul R RSS

    Generate Automatic Number based on Organization

    Atul R
      Dear Experts,

      I am working on form personalization where i want to generate automatic numbering for Inter-org transfer --> shipment number.

      I have done some personalization on same and successfully generated automatic numbers for the Inter org shipment number. (By creating sequence and function)

      CREATE SEQUENCE APPS.XX_IOT_SHIP_NUM
      START WITH 25
      MAXVALUE 9999
      MINVALUE 0
      NOCYCLE
      NOCACHE
      ORDER;

      CREATE OR REPLACE function APPS.XX_IOT_SHIP_NUM_GENERATOR return varchar2 is
      v_num varchar2(100);
      begin
      select
      decode(length(XX_IOT_SHIP_NUM.nextval),1,'000'||XX_IOT_SHIP_NUM.nextval,
      2,'00'||XX_IOT_SHIP_NUM.nextval,
      3,'0'||XX_IOT_SHIP_NUM.nextval,
      4,XX_IOT_SHIP_NUM.nextval,
      XX_IOT_SHIP_NUM.nextval)
      into v_num
      from dual;

      return v_num;

      end;


      Now Numbers are automatically generating, but issue is that number should generate organization wise. for eg 4 different orgs will have its own unique sequence number.

      Application Verision : R12.1.3
      Database version : Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production

      Kindly guide me for achieving this.

      Thanks,
      Atul
        • 1. Re: Generate Automatic Number based on Organization
          Paarthy-Oracle
          Are you following the steps provided in the below note ?

          Generate Inter-Org Shipment Number? / Must Enter Shipment Number Manually for Inter Org Transfer (Doc ID 144171.1)

          Did you try to create a different sequences for each organization and call the corresponding sequence by validating the Organization id\ code.
          (The organization id should be available in the same block INTERORG_XFER ).

          Please try the same and share your experiences.

          Regards,
          Paarthy
          • 2. Re: Generate Automatic Number based on Organization
            Atul R
            Hi Parthy,

            According to this (Doc ID 144171.1) the shipment number will generate for only one org, that can be possible without downloading the form also.

            How can we create sequence for perticular org. It is not possible to create sequence according to the orgs.

            In that case i have tried different solution to achieve this,

            created a table :

            CREATE TABLE APPS.Testing_SHIPMENT__SEQ_TEST
            (
            TRANSACTION_ID NUMBER,
            PREVIOUS_SEQUENCE NUMBER,
            CURRENT_SEQUENCE NUMBER,
            NEXT_SEQUENCE NUMBER,
            ORG_NO NUMBER,
            TRANSACTION_TYPE_ID NUMBER
            );


            created a trigger :

            CREATE OR REPLACE TRIGGER Testing_SHIPMENT_TRG_TEST
            --BEFORE INSERT ON MTL_TXN_REQUEST_HEADERS
            AFTER INSERT ON MTL_MATERIAL_TRANSACTIONS
            FOR EACH ROW
            WHEN( OLD.TRANSACTION_TYPE_ID = 21 )
            DECLARE
            PRAGMA autonomous_transaction;
            NEXT_NUMBER NUMBER;
            BEGIN
            NEXT_NUMBER := :NEW.SHIPMENT_NUMBER +1;
            INSERT INTO APPS.Testing_SHIPMENT__SEQ_TEST values(:NEW.TRANSACTION_ID,NEXT_NUMBER - 2,NEXT_NUMBER - 1,NEXT_NUMBER,:NEW.ORGANIZATION_ID);
            commit;
            END;


            Then in form personalization level for shipment number i wrote :

            Condition
            --------------------------------
            Trigger Event : WHEN-NEW-ITEM-INSTANCE
            Trigger Object : INTERORG_XFER.SHIPMENT_NUMBER
            Condiition : :INTERORG_XFER.SHIPMENT_NUMBER is null

            Action :
            ---------------------------------------------------------------------------
            Seq : 10
            Type: Property
            object type : Item
            Target Object : INTERORG_XFER.SHIPMENT_NUMBER
            Property Name: VALUE
            Value : =(select max(next_sequence) from APPS.Testing_SHIPMENT__SEQ_TEST
            where org_no = fnd_profile.value('MFG_ORGANIZATION_ID')
            )


            I can generate the Numbers according to organization wise but still it is in Testing phase. Getting the errors Shipment number can not be duplicate.

            If you have any other solution or improvement in the code then share.

            Thanks,
            Atul