, , ,

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.

https://docs.oracle.com/cd/E26401_01/doc.122/e22961/T302934T462356.htm

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
CREATE OR REPLACE DIRECTORY SALE_INVOICE_PATH AS ‘/files/SALE_INVOICE/’;

SET SERVEROUTPUT ON;
DECLARE
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.

BEGIN
–Enter USER_ID,RESP_ID,RESP_APPL_ID
fnd_global.apps_initialize (&USER_ID, &RESP_ID, &RESP_APPL_ID);

SELECT fnd_documents_s.NEXTVAL
INTO l_document_id
FROM DUAL;

SELECT fnd_attached_documents_s.NEXTVAL
INTO l_attached_document_id
FROM DUAL;

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
WHERE NAME = ‘FILE’;

— 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,
apps.fnd_attached_documents_s.NEXTVAL
into l_document_id,l_attached_document_id
FROM DUAL;

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’,
SYSDATE, NULL, ‘FNDATTCH’, NULL, EMPTY_BLOB (), –l_blob_data,
‘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_LOB.CLOSE (fils);

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

COMMIT;

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

fnd_documents_pkg.insert_row
(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
);

commit;

— Description informations will be stored in below table based on languages.
fnd_documents_pkg.insert_tl_row
(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
);
commit;

fnd_attached_documents_pkg.insert_row
(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
);
COMMIT;
DBMS_OUTPUT.put_line (‘MEDIA ID CREATED IS ‘ || l_media_id);
end;
/

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

BEGIN
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’
THEN
fnd_file.put_line (fnd_file.LOG, ‘Creating contacts….’);
ap_vendor_pub_pkg.create_vendor_contact
(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, ‘*************’);
END IF;
END;

, ,

How to Reading XML data using PLSQL

This Document explains the steps of how we read an XML data file using PLSQL and write them to oracle tables.

Create a UTL Directory:

This involves the following steps. Creating a Logical directory and giving the Permissions to access this directory using an URL. Using the XML Dom Parser Procedures to read the Xml file , parse it
and then load it into the respective columns.

1) Create a logical directory
Create or replace directory TEST_LOB_DIR as ‘/data/tst1/attachment’
It creates a directory with Owner SYS.Grant privileges on this directory to Apps.

2) Create a physical directory at the given location ‘/data/tst1/attachment’
Permissions on this directory too.This directory has to be a UTL directory.

3) Edit the conf file at this path
Cd $IAS_ORACLE_HOME/Apache/Apache/conf
==>/apps/applmgr/tst1ora/IAS/Apache/Apache/conf
Edit the file apps.conf
Alias /attachment/ “/data/tst1/attachment/”
<Location /attachment/>
Order allow, deny
Allow from all
</Location>

4) Bounce the Apache.

Create a procedure to parse and retrieve the XML data and then insert it into appropriate columns in the database.


Used two PLSQL packages for these –
1. XMLPARSER
2. XMLDOM

1. XMLParser – Procedures


i.)parse(p Parser, url VARCHAR2)
Description – The parse procedure takes two parameters which are the parse object and the url of the
xml file that has to parsed.
ii.) setValidationMode(p Parser, yes BOOLEAN)
Checks whether XML is Valid or not.
iii.) setBaseDir(p Parser, dir VARCHAR2)
Sets the base url or directory path.
iv.) getDocument(p Parser)
Get the Document which has to be parsed.

2. XMLDOM
DOMDocument
DOMELEMENT
DOMNODELIST
DOMNODE
DOMNamedNodeMap


FUNCTIONS –
getNodeName(n DOMNode) RETURN VARCHAR2 – Retrieves the Name of the Node
getNodeValue(n DOMNode) RETURN VARCHAR2 – Retrieves the Value of the Node
getElementsByTagName(doc DOMDocument, tagname IN VARCHAR2)- Retrieves the elements in
the by tag name
getDocumentElement(doc DOMDocument) RETURN DOMElement – Retrieves the root element of
the document
getFirstChild(n DOMNode) RETURN DOMNode – Retrieves the first child of the node
getLength(nl DOMNodeList) RETURN NUMBER- Retrieves the number of items in the list.

Sample XML File

-<vapi_HRMS>
– <record>
  <EmpID>O01006923</EmpID> 
  <vcEmp_Full_Name>Vinod Mudakkayil</vcEmp_Full_Name> 
  <vcDesignation>VP</vcDesignation> 
  <chLevel>L8</chLevel> 
  <chGrade>-</chGrade> 
  <vcDepartment>HR-Recruitment</vcDepartment> 
  <vcProgram_Name>HR – Recruitment</vcProgram_Name> 
  <vcSub_Program_Name>HR-Recruitment</vcSub_Program_Name> 
  <vcEmail_ID>[email protected]</vcEmail_ID> 
  <vcCentername>Bangalore</vcCentername> 
  <intSeniorID>01040101</intSeniorID> 
  <Hop_Id>101006923</Hop_Id> 
  <VP_Id>101006923</VP_Id> 
  <VCEMP_STATUS>CORPORATE</VCEMP_STATUS> 
  <vcEmp_First_Name>Vinod</vcEmp_First_Name> 
  <vcEmp_Last_Name>Mudakkayil</vcEmp_Last_Name> 
  <vcEmp_Gender>Male</vcEmp_Gender> 
  <vcEmp_DOJ>2005/04/07</vcEmp_DOJ> 
  <dob>4-Mar-1963</dob> 
  </record>
– <record>
  <EmpID>P01006923</EmpID> 
  <vcEmp_Full_Name>Vinod Mudakkayil</vcEmp_Full_Name> 
  <vcDesignation>VP</vcDesignation> 
  <chLevel>L8</chLevel> 
  <chGrade>-</chGrade> 
  <vcDepartment>HR-Recruitment</vcDepartment> 
  <vcProgram_Name>HR – Recruitment</vcProgram_Name> 
  <vcSub_Program_Name>HR-Recruitment</vcSub_Program_Name> 
  <vcEmail_ID>[email protected]</vcEmail_ID> 
  <vcCentername>Bangalore</vcCentername> 
  <intSeniorID>01040101</intSeniorID> 
  <Hop_Id>01006923,101006923,O01006923</Hop_Id> 
  <VP_Id>O01006923</VP_Id> 
  <VCEMP_STATUS>CORPORATE</VCEMP_STATUS> 
  <vcEmp_First_Name>Vinod</vcEmp_First_Name> 
  <vcEmp_Last_Name>Mudakkayil</vcEmp_Last_Name> 
  <vcEmp_Gender>Male</vcEmp_Gender> 
  <vcEmp_DOJ>2005/04/07</vcEmp_DOJ> 
  <dob>4-Mar-1963</dob> 
  </record>
  </vapi_HRMS>

Sample Code:

— Call the procedure —
PROCEDURE xml_perse (
      errbuf    OUT   VARCHAR2,
      retcode   OUT   NUMBER,
      dir             VARCHAR2,
      inpfile         VARCHAR2
   )
   IS
      p         xmlparser.parser;
      doc       xmldom.domdocument;
      docelem   DBMS_XMLDOM.domelement;
— prints elements in a document
   BEGIN
— new parser
      p := xmlparser.newparser;
— set some characteristics
      xmlparser.setvalidationmode (p, FALSE);
      fnd_file.put_line (fnd_file.LOG, ‘ xml_perse Validated’);
–xmlparser.setErrorLog(p, dir || ‘/’ || errfile);
      xmlparser.setbasedir (p, dir);
      fnd_file.put_line (fnd_file.LOG, ‘ xml_perse set path’);
— parse input file
      xmlparser.parse (p, dir || ‘/’ || inpfile);
      fnd_file.put_line (fnd_file.LOG, ‘ xml_perse parse’);
— get document
      doc := xmlparser.getdocument (p);
      fnd_file.put_line (fnd_file.LOG, ‘ xml_perse get document’);
— Print document elements
      DBMS_OUTPUT.put (‘The elements are: ‘);
      printelements (doc);
      COMMIT;
   EXCEPTION
      WHEN OTHERS
      THEN
         –SQLERRM
         DBMS_OUTPUT.put (SQLERRM);
   END xml_perse;

   PROCEDURE printelements (doc xmldom.domdocument)
   IS
      nl1                  xmldom.domnodelist;
      nl2                  xmldom.domnodelist;
      nl3                  xmldom.domnodelist;
      nl4                  xmldom.domnodelist;
      nl5                  xmldom.domnodelist;
     
      len1                 NUMBER;
      len2                 NUMBER;
      len3                 NUMBER;
      len4                 NUMBER;
      len5                 NUMBER;
     
      n1                   xmldom.domnode;
      n2                   xmldom.domnode;
      n3                   xmldom.domnode;
      n4                   xmldom.domnode;
    
      nnm                  xmldom.domnamednodemap;
      attrname             VARCHAR (1000);
      attrval              VARCHAR (1000);
      v_empid              VARCHAR2 (1000);
      v_emp_full_name      VARCHAR2 (1000);
      v_designation        VARCHAR2 (1000);
    
   BEGIN
— get all elements
      fnd_file.put_line (fnd_file.LOG, ‘get all elements’);
      nl1 := xmldom.getelementsbytagname (doc, ‘record’);
      nl2 := xmldom.getelementsbytagname (doc, ‘EmpID’);
      nl3 := xmldom.getelementsbytagname (doc, ‘vcEmp_Full_Name’);
      nl4 := xmldom.getelementsbytagname (doc, ‘vcDesignation’);
     
      fnd_file.put_line (fnd_file.LOG, ‘Length of the Elements’);
— Length of the Elements
      len1 := xmldom.getlength (nl1);
      len2 := xmldom.getlength (nl2);
      len3 := xmldom.getlength (nl3);
      len4 := xmldom.getlength (nl4);     

— loop through elements
      FOR i IN 0 .. len1 – 1
      LOOP
         v_empid := NULL;
         v_emp_full_name := NULL;
         v_designation := NULL;
        
         n1 := xmldom.item (nl1, i);
         n2 := xmldom.item (nl2, i);
         n3 := xmldom.item (nl3, i);
         n4 := xmldom.item (nl4, i);        

         v_empid := xmldom.getnodevalue (n2);
         v_emp_full_name := xmldom.getnodevalue (n3);
         v_designation := xmldom.getnodevalue (n4);
         
         fnd_file.put_line (fnd_file.LOG, ‘***************************’);
         fnd_file.put_line (fnd_file.LOG, v_empid);
         fnd_file.put_line (fnd_file.LOG, v_emp_full_name);
         fnd_file.put_line (fnd_file.LOG, v_designation);
        
         fnd_file.put_line (fnd_file.LOG, ‘***************************’);

         DELETE FROM xx_employee_temp;

         –WHERE status = ‘S’;
         INSERT INTO xx_employee_temp
                     (empid, emp_full_name, designation,status, error_description
                     )
              VALUES (v_empid, v_emp_full_name, v_designation,NULL, NULL
                     );

         DBMS_OUTPUT.put_line (‘ ‘);
      END LOOP;

      fnd_file.put_line (fnd_file.LOG, ‘Inserted’);
   END printelements;