How To Set a Default Sequence For Invoice Number in Oracle Payables application
Hello, This is Ibrahem Soliman from AppsLead.
Here we are going to write about How to set a default sequence for invoice number in oracle payables application
Hope you find this post easy and useful and waiting for any comments or inquiries.
step 1 Open screen invoice in payables responsibility
step 2
Open personalization form Help –>Diagnostic —>Custom Code –> Personalize
step 3
Create New personalization
3.1 Create Personalization Condition as below
Trigger Event : WHEN-NEW-ITEM-INSTANCE
Trigger Object : INV_SUM_FOLDER.INVOICE_NUM
Condition :
${item.INV_SUM_FOLDER.INVOICE_DATE.VALUE} is not null and ${Item.INV_SUM_FOLDER.DOC_CATEGORY_NAME.value} is not null
and :INV_SUM_FOLDER.INVOICE_ID Is Null
AND :INV_SUM_FOLDER.INVOICE_NUM Is Null
step 4
4.1 Create Function in Database
-=======================================================
Then go to Invoice Screen and try to create new invoice, it will generate its Invoice number automatically from the personalization.
For any inquiries, contact me back.
step 1 Open screen invoice in payables responsibility



CREATE FUNCTION XXGENERATINVOICENUMBER (PINVOICEDATE DATE) RETURN VARCHAR2 IS LVOUCHARNUMBER VARCHAR2 (30); BEGIN SELECT TO_CHAR (NVL (MAX (DSA.DOC_SEQUENCE_VALUE), 0) + 1) INTO LVOUCHARNUMBER FROM FND_DOC_SEQUENCE_CATEGORIES FDSC, FND_DOC_SEQUENCE_ASSIGNMENTS FDSA, AP_DOC_SEQUENCE_AUDIT DSA, AP_INVOICES_ALL AI WHERE AI.DOC_CATEGORY_CODE = FDSC.CODE AND AI.DOC_SEQUENCE_ID = DSA.DOC_SEQUENCE_ID AND DSA.DOC_SEQUENCE_ASSIGNMENT_ID = FDSA.DOC_SEQUENCE_ASSIGNMENT_ID AND FDSA.CATEGORY_CODE = FDSC.CODE AND DSA.DOC_SEQUENCE_ID = (SELECT DOC_SEQUENCE_ID FROM FND_DOC_SEQUENCE_ASSIGNMENTS FDSA WHERE TRUNC (FDSA.START_DATE) <= TRUNC (NVL (PINVOICEDATE, SYSDATE)) AND ( TRUNC (FDSA.END_DATE) >= TRUNC (NVL (PINVOICEDATE, SYSDATE)) OR FDSA.END_DATE IS NULL));
RETURN LVOUCHARNUMBER; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN ‘ ‘; END;
========================================================= 4.2 Follow below how to use this database function in personalization: 4.2.1 Seq : 10 4.2.2 Type : Property 4.2.3 Object Type : Item 4.2.4 Target Object : INV_SUM_FOLDER.INVOICE_NUM 4.2.5 Property Name : Value 4.2.6 Value : =GENERATINVOICENUMBER(:INV_SUM_FOLDER.INVOICE_DATE)
For any inquiries, contact me back.