, , ,

How to Insert Attachments in R12 Using API

This article explains how to insert attachments using Oracle API’s from Back end. Working on attachments need some knowledge of how an attachment functions (Definitions, Entity, Document Category, Entities and Blocks, Primary Keys in attachments) in Oracle Apps. To understand the below API and what it actually does its advisable to read the Attachments Chapter in Developers Guide.


Primary Key information that uniquely identifies the product (such as the INVOICE_ID).

Below API takes single file from a shared drive and inserts into fnd_lobs. Then it’s attached to a particular Id (like check_id, invoice_id..) using the combination of pk1_value, entity name and category.

This sample shows how an attachment API works. You can use it to customize/enhance to requirement.

//Tested in R12.1.3

–Create a Server Directories in Oracle
–This will be done by DBA mostly
–Physical directory must be created inorder to save the files into server

l_rowid ROWID;
l_attached_document_id NUMBER;
l_document_id NUMBER;
l_media_id NUMBER;
l_category_id number;
l_pk1_value fnd_attached_documents.pk1_value%type:= 999999; –Primary Key information that uniquely identifies the information
l_description fnd_documents_tl.description%type:= ‘Customer Invoice Attachment’;
l_filename VARCHAR2(240) := ‘Sales_Invoice_14Nov2016.pdf’;
l_file_path varchar2(240) := ‘SALE_INVOICE_PATH’; –Server Directory Path for upload files
l_seq_num NUMBER;
l_blob_data BLOB;
l_blob BLOB;
l_bfile BFILE;
l_byte NUMBER;
l_fnd_user_id NUMBER;
l_short_datatype_id NUMBER;
x_blob BLOB;
fils BFILE;
blob_length integer;
l_entity_name varchar2(100) := ‘CUSTOM_LOAD’; –Must be defined before or use existing ones. Table: FND_DOCUMENT_ENTITIES
l_category_name VARCHAR2(100) := ‘Other’; –Must be defined before or use existing ones.

fnd_global.apps_initialize (&USER_ID, &RESP_ID, &RESP_APPL_ID);

SELECT fnd_documents_s.NEXTVAL
INTO l_document_id

SELECT fnd_attached_documents_s.NEXTVAL
INTO l_attached_document_id

SELECT NVL (MAX (seq_num), 0) + 10
INTO l_seq_num
FROM fnd_attached_documents
WHERE pk1_value = l_pk1_value AND entity_name = l_entity_name;

— Select User_id
SELECT user_id
INTO l_fnd_user_id
from apps.fnd_user
WHERE user_name = ‘OPERATIONS’; –Username who will be uploading file.

— Get Data type id for Short Text types of attachments
SELECT datatype_id
INTO l_short_datatype_id
FROM apps.fnd_document_datatypes

— Select Category id for Attachments
SELECT category_id
INTO l_category_id
FROM apps.fnd_document_categories_vl
WHERE USER_NAME = l_category_name;

— Select nexvalues of document id, attached document id and
SELECT apps.fnd_documents_s.NEXTVAL,
into l_document_id,l_attached_document_id

SELECT MAX (file_id) + 1
INTO l_media_id
FROM fnd_lobs;

fils := BFILENAME (l_file_path, l_filename);

— Obtain the size of the blob file
DBMS_LOB.fileopen (fils, DBMS_LOB.file_readonly);
blob_length := DBMS_LOB.getlength (fils);
DBMS_LOB.fileclose (fils);

— Insert a new record into the table containing the
— filename you have specified and a LOB LOCATOR.
— Return the LOB LOCATOR and assign it to x_blob.

INSERT INTO fnd_lobs
(file_id, file_name, file_content_type, upload_date,
expiration_date, program_name, program_tag, file_data,
LANGUAGE, oracle_charset, file_format
VALUES (l_media_id, l_filename, ‘application/pdf’,–‘text/plain’,
‘US’, ‘UTF8’, ‘binary’
RETURNING file_data
INTO x_blob;

— Load the file into the database as a BLOB
DBMS_LOB.OPEN (fils, DBMS_LOB.lob_readonly);
DBMS_LOB.OPEN (x_blob, DBMS_LOB.lob_readwrite);
DBMS_LOB.loadfromfile (x_blob, fils, blob_length);

— Close handles to blob and file
DBMS_LOB.CLOSE (x_blob);

DBMS_OUTPUT.put_line (‘FND_LOBS File Id Created is ‘ || l_media_id);


— This package allows user to share file across multiple orgs or restrict to single org

(x_rowid => l_rowid,
x_document_id => l_document_id,
x_creation_date => SYSDATE,
x_created_by => l_fnd_user_id,
x_last_update_date => SYSDATE,
x_last_updated_by => l_fnd_user_id,
x_last_update_login => fnd_profile.VALUE(‘LOGIN_ID’),
x_datatype_id => l_short_datatype_id,
X_security_id => 21, –Security ID defined in your Attchments, Usaully SOB ID/ORG_ID
x_publish_flag => ‘N’, –This flag allow the file to share across multiple organization
x_category_id => l_category_id,
x_security_type => 1,
x_usage_type => ‘S’,
x_language => ‘US’,
x_description => l_description,
x_file_name => l_filename,
x_media_id => l_media_id


— Description informations will be stored in below table based on languages.
(x_document_id => l_document_id,
x_creation_date => SYSDATE,
x_created_by => l_fnd_user_id,
x_last_update_date => SYSDATE,
x_last_updated_by => l_fnd_user_id,
x_last_update_login => fnd_profile.VALUE(‘LOGIN_ID’),
x_language => ‘US’,
x_description => l_description

(x_rowid => l_rowid,
x_attached_document_id => l_attached_document_id,
x_document_id => l_document_id,
x_creation_date => SYSDATE,
x_created_by => l_fnd_user_id,
x_last_update_date => SYSDATE,
x_last_updated_by => l_fnd_user_id,
x_last_update_login => fnd_profile.VALUE(‘LOGIN_ID’),
x_seq_num => l_seq_num,
x_entity_name => l_entity_name,
x_column1 => NULL,
x_pk1_value => l_pk1_value,
x_pk2_value => NULL,
x_pk3_value => NULL,
x_pk4_value => NULL,
x_pk5_value => NULL,
x_automatically_added_flag => ‘N’,
x_datatype_id => 6,
x_category_id => l_category_id,
x_security_type => 1,
X_security_id => 21, –Security ID defined in your Attchments, Usaully SOB ID/ORG_ID
x_publish_flag => ‘Y’,
x_language => ‘US’,
x_description => l_description,
x_file_name => l_filename,
x_media_id => l_media_id
DBMS_OUTPUT.put_line (‘MEDIA ID CREATED IS ‘ || l_media_id);

Learn more on Attachments on this article.

, , , ,

R12 Supplier Contact creation API

Below are the steps to create Supplier contacts in Oracle EBS R12.
AP_SUP_SITE_CONTACT_INT interface used in 11i for loading supplier contact. Currently in R12 contacts can not be loaded using the interface table.

ap_vendor_pub_pkg.create_vendor_contact API is used in R12 and the program has to be registered as a concurrent program.

### Sample R12 Code

v_party_usg_assignment_id := NULL;
v_relationship_id := NULL;
v_directional_flag := NULL;
v_rel_rowid := NULL;
fnd_file.put_line (fnd_file.LOG,
‘Vendor code :’
|| rec.vendor_name
|| ‘  Vendor site code :’
|| rec.vendor_site_code
|| ‘  Person Last Name : ‘
|| rec.person_last_name
l_vendor_contact.vendor_id := rec.vendor_id;
l_vendor_contact.vendor_site_id := rec.vendor_site_id;
l_vendor_contact.org_id := rec.org_id;
l_vendor_contact.person_first_name := rec.person_first_name;
l_vendor_contact.person_middle_name := rec.person_middle_name;
l_vendor_contact.person_last_name := rec.person_last_name;
l_vendor_contact.phone := rec.phone;
l_vendor_contact.email_address := rec.email_address;
p_init_msg_list := fnd_api.g_true;
p_commit := fnd_api.g_false;
x_return_status := NULL;
x_msg_count := NULL;
x_msg_data := NULL;

IF rec.process_flag = ‘I’
fnd_file.put_line (fnd_file.LOG, ‘Creating contacts….’);
(p_api_version             => p_api_version,
p_init_msg_list           => p_init_msg_list,
p_commit                  => p_commit,
x_return_status           => x_return_status,
x_msg_count               => x_msg_count,
x_msg_data                => x_msg_data,
p_vendor_contact_rec      => l_vendor_contact,
x_vendor_contact_id       => l_vendor_contact.vendor_contact_id,
x_per_party_id            => l_vendor_contact.per_party_id,
x_rel_party_id            => l_vendor_contact.relationship_id,
x_rel_id                  => l_vendor_contact.rel_party_id,
x_org_contact_id          => l_vendor_contact.org_contact_id,
x_party_site_id           => l_vendor_contact.party_site_id
error_handling (rec.r_id, x_return_status, x_msg_count, x_msg_data);
fnd_file.put_line (fnd_file.LOG, ‘*************’);

, , ,

How to Create RMA in R12 Order Management

How to create RMA in Oracle Order Management:

I am taking the case of creation of Return Sales Order with Receipt and Credit Memo.

1.Create New Sales Order with Order Type = “Return Only . ( we can take Mixed or any other that allow return line)

2.Since Header Order Type is “Return Only”, based on the transaction Type Setup ,  System will default Line Type .In transaction type set the Default RMA = “Return (Receipt)” , with this Setup system will populate the Line Type = “Return (Receipt)”  , enter other details like
Item #
Qty etc.

3.Go to Return tab ( Lines) and Enter Return Reason (There is LOV for Return Reason select any relevent value from LOV ).
4.Book the RMA.

5.Requery the RMA , check the Line Status , It should be “Awaiting Return”.

6.Select Receipt (Purchasing Responsibility) 

7.Goto Customer Tab and Enter the RMA Num .

8.Press Find Button.

9.Ignore the receipt Header , and go to Receipts form 

10.Select the line

11.Press Save,System will ask for the Sub Inventory.Best bet is enter “Stores” ( Here I am taking the case Routing = Direct Delivery)

12.Press Save Button. Save action will automatically submit the “Receiving Transaction Processor (RTP) concurrent Program.

13.Check Order line status , it should be “Returned”.

14.Go to View > request . Submit Concurrent Program “Workflow background Process”
              with Item Type = OM Order Line
              Process Deferred = Yes
              Process Timeout = No.

Query Order line again , Status Should be “Closed”

15.Step # 14 also submit “Autoinvoice” concurrent Program.
16.Goto Transaction (Receivable Responsibility)

            Go to Find Window
             Enter Sales Order # in Sales Order Number Field
             Press Find
System should display your Invoice (Credit memo)

Examine the Invoice and you are good to go.