, , , ,

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;

/by
, ,

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;
/by
, , ,

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.
/by
, ,

Oracle R12 – RFQ to PO Receipt Cycle

This Post is about Oracle applications RFQ to Receipt Cycle.In this Post I will explain the Cycle from RFQ to PO Receipt with screen shots.

High -Lights of this Cycle is  to Create:

  • RFQ
  • Quote
  • Purchase Order
  • Receive against PO.

Once RFQ is created , Submit concurrent job “Request to Print the RFQ” for a supplier. On completion this job will increment print count for that supplier as shown below.

In below  Oracle Apps UIs we can see
  1. RFQ # 308,
  2. Supplier Info from Supplier List and
  3. Price Breaks.
 
Print RFQ for all the Suppliers by means of Concurrent Program available in Oracle Apps


Once we print the RFQ , status of RFQ become Printed , and print count will increment for all suppliers .Since we got response from the Office Supplier , Inc Site – OFFICESUPPLIER , Responded field populated for this supplier only . 

  
 
From the RFQ , Select Tools &amp;gt; Copy Doc .It will Create Quotations as shown below.
  1. Enter the Supplier Name for whom you want to create Quote.
  2. Press OK and it will Create Quotation.
  1.  Query for Quotation # 502.
  2. Create Purchase Order Agreement from Quotation by selected Tools &amp;gt; Copy Doc
  3. Press Ok and it will Create Purchase Order Agreement. 
 Query for PO Agreement and Approve it
Once Oracle Purchase agreement got approved , creates the releases for Blanket PO Agreement .In our example PO Agreement Release we have item Test001 , BUT Item Test001 has restricted to be ordered from supplier from “Approval Supplier list”, and as  our Supplier is not part of any Approve Supplier list , system will throw Error.
 
For my test , I just remove the Item Test001 and Approve the Oracle Purchase Order Release and finally did the receipt against PO.
/by