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 -=======================================================

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)   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.

Thank You,

Ibrahem Soliman.