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
CREATE OR REPLACE function APPS.XX_IOT_SHIP_NUM_GENERATOR return varchar2 is
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 126.96.36.199.0 - 64bit Production
Kindly guide me for achieving this.
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.
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
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 )
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);
Then in form personalization level for shipment number i wrote :
Trigger Event : WHEN-NEW-ITEM-INSTANCE
Trigger Object : INTERORG_XFER.SHIPMENT_NUMBER
Condiition : :INTERORG_XFER.SHIPMENT_NUMBER is null
Seq : 10
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.