Insert Lookup Values API

1. Create a table to store data that will be inserted into Lookup.

--CREATE TABLE XX_TEMP_TABLE
--  (
--    X_LOOKUP_CODE         VARCHAR2(30 BYTE),
--    X_LOOKUP_MEANING      VARCHAR2(80 BYTE),
--    X_LOOKUP_DESCRIPTION  VARCHAR2(240 BYTE),
--    X_LOOKUP_TAG          VARCHAR2(30 BYTE),
--    X_LOOKUP_START_DATE   DATE,
--    X_LOOKUP_END_DATE     DATE,
--    X_LOOKUP_ENABLED_FLAG VARCHAR2(1 BYTE),
--    X_LOOKUP_CONTEXT      VARCHAR2(30 BYTE),
--    X_LOOKUP_ATTRIBUTE1   VARCHAR2(150 BYTE),
--    X_LOOKUP_ATTRIBUTE2   VARCHAR2(150 BYTE),
--    X_LOOKUP_ATTRIBUTE3   VARCHAR2(150 BYTE),
--    X_LOOKUP_ATTRIBUTE4   VARCHAR2(150 BYTE),
--    X_LOOKUP_ATTRIBUTE5   VARCHAR2(150 BYTE),
--    X_LOOKUP_ATTRIBUTE6   VARCHAR2(150 BYTE),
--    X_LOOKUP_ATTRIBUTE7   VARCHAR2(150 BYTE),
--    X_LOOKUP_ATTRIBUTE8   VARCHAR2(150 BYTE),
--    X_LOOKUP_ATTRIBUTE9   VARCHAR2(150 BYTE),
--    X_LOOKUP_ATTRIBUTE10  VARCHAR2(150 BYTE)
--  );

2. Script to Insert Values

set serveroutput on;
-- API to Create Lookup Values - "FND_LOOKUP_VALUES"


DECLARE
   CURSOR get_lookup_details
   IS
      SELECT   ltype.application_id,
               ltype.customization_level,
               ltype.creation_date,
               ltype.created_by,
               ltype.last_update_date,
               ltype.last_updated_by,
               ltype.last_update_login,
               tl.lookup_type,
               tl.security_group_id,
               tl.view_application_id,
               tl.description,
               tl.meaning
        FROM   fnd_lookup_types_tl tl, fnd_lookup_types ltype
       WHERE       ltype.lookup_type = 'XX_USER_CUSTOMER_MAPPING'
               AND ltype.lookup_type = tl.lookup_type
               AND language = 'US';

   CURSOR get_VALUES
   IS
      SELECT   * FROM XX_TEMP_TABLE where x_lookup_code not in ( select lookup_code
        FROM   fnd_lookup_values_vl
       WHERE       lookup_type = 'XX_USER_CUSTOMER_MAPPING'
)
      ;--'CO12505A';

   l_rowid   VARCHAR2 (100) := 0;

   BEGIN
   FOR i IN get_lookup_details
   LOOP
      FOR j IN get_VALUES
      LOOP
         l_rowid := NULL;
       
         BEGIN
            fnd_lookup_values_pkg.insert_row (
               x_rowid                 => l_rowid,
               x_lookup_type           => i.lookup_type,
               x_security_group_id     => i.security_group_id,
               x_view_application_id   => i.view_application_id,
               x_lookup_code           => j.X_LOOKUP_CODE,
               x_tag                   => j.X_LOOKUP_TAG,
               x_attribute_category    => j.X_LOOKUP_CONTEXT,
               x_attribute1            => j.X_LOOKUP_ATTRIBUTE1,
               x_attribute2            => j.X_LOOKUP_ATTRIBUTE2,
               x_attribute3            => j.X_LOOKUP_ATTRIBUTE3,
               x_attribute4            => j.X_LOOKUP_ATTRIBUTE4,
               x_enabled_flag          => 'Y',
               x_start_date_active     => j.X_LOOKUP_START_DATE,
               x_end_date_active       => j.X_LOOKUP_END_DATE,
               x_territory_code        => NULL,
               x_attribute5            => j.X_LOOKUP_ATTRIBUTE5,
               x_attribute6            => j.X_LOOKUP_ATTRIBUTE6,
               x_attribute7            => j.X_LOOKUP_ATTRIBUTE7,
               x_attribute8            => j.X_LOOKUP_ATTRIBUTE8,
               x_attribute9            => j.X_LOOKUP_ATTRIBUTE9,
               x_attribute10           => j.X_LOOKUP_ATTRIBUTE10,
               x_attribute11           => NULL,
               x_attribute12           => NULL,
               x_attribute13           => NULL,
               x_attribute14           => NULL,
               x_attribute15           => NULL,
               x_meaning               => j.X_LOOKUP_MEANING,
               x_description           => j.X_LOOKUP_DESCRIPTION,
               x_creation_date         => SYSDATE,
               x_created_by            => 1230, --i.created_by,
               x_last_update_date      => SYSDATE, --i.last_update_date,
               x_last_updated_by       => 1230,--i.last_updated_by,
               x_last_update_login     => i.last_update_login
            );
            COMMIT;
            DBMS_OUTPUT.put_line (j.X_LOOKUP_CODE || ' loaded');
         EXCEPTION
            WHEN OTHERS
            THEN
               DBMS_OUTPUT.put_line ('Inner Exception: ' || SQLERRM);
         END;
      END LOOP;
   END LOOP;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('Main Exception: ' || SQLERRM);
END;

Good Article:

http://allthingsoracle.com/generating-xml-from-sql-and-pl-sql-part-1/

http://allthingsoracle.com/generating-xml-from-sql-and-plsql-part-2/


Generating XML

XMLTYPE

The easiest way to create an XML document, is using the constructor of XMLTYPE. This constructor can have several datatypes as input, like a CLOB and VARCHAR2, but as we’re going to base our XML on table data, we’re using a REF CURSOR.

DBMS_XMLGEN

The DBMS_XMLGEN built-in is similar to the XMLTYPE constructor, but accepts a query directly:

dbms_xmldom

With the XMLTYPE constructor and DBMS_XMLGEN package, you can create simple XML documents, fast and easy. When you need to create more advanced XML documents or want to have more control on how your XML document looks like, DBMS_XMLDOM can be used. The DBMS_XMLDOM package is a bit more complicated as you’ll have to create the entire document by calling functions and procedures of the package.
The following example creates an XML document with the department information retrieved from the query. In short, this is how it works: create new elements and add them as a (child) node.

XML Basics -1

What's the best way of exchanging data between different sources without worrying about how the receiver will use it? What's the best way of creating documents with the right content without worrying how it should be displayed on
the web and then able to display them with all the flexibility one could get?

XML and related topics viz. XSL, DTD, DOM, SAX and Schemas.

XML stands for eXtensible Markup Language. In contrast to HTML that describes visual presentation, XML
describes data in an easily readable format but without any indication of how the data is to be displayed. It is a
database-neutral and device-neutral format. Since XML is truly extensible, rather than a fixed set of elements like
HTML, use of XML will eventually eliminate the need for browser developers and middle-ware tools to add special
HTML tags (extensions).

Listing 1 is an example of a simple XML document.
<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" href="Employee.XSL"?>
<Employees>
<Empl id="1">
<FirstName>
Chuck
</FirstName>
<LastName>
White
</LastName>
<Dept>
Finance
</Dept>
</Empl>
</Employees>
Listing 1: Employee.xml


XSL
As explained earlier, XML is more focussed on defining the data, therefore, we need a mechanism to define how this
data should be displayed in browsers, cell phones or any other such devices. This is exactly what XSL (eXtensible Style
Language) does. It defines the rules to interpret the elements of the XML document.
XSL at its most basic provides a capability similar to a "mail merge." The style sheet contains a template of the
desired result structure, and identifies data in the source document to insert into this template. This model for
merging data and templates is referred to as the template-driven model and works well on regular and repetitive data.
Listing 2 is an example of an XSL document for the XML document shown in Listing 1.
<?xml version='1.0'?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/TR/WD-xsl">
Design, Develop, and Deploying Your Applications
Page 2 of 11 Paper # 214
<xsl:template match="/">
<HTML>
<BODY>
<h1>Employee Details</h1>
<xsl:for-each select="Employees/Empl">
<b>Empl #
<xsl:value-of select="@id" /> </b>
<i>First Name :
<xsl:value-of select="FirstName" /> </i>
<i>Last Name :
<xsl:value-of select="LastName" /> </i>
<i>Dept :
<xsl:value-of select="Dept" /> </i>
</xsl:for-each>
</BODY>
</HTML>
</xsl:template>
</xsl:stylesheet>
Listing 2: Employee.xsl
Figure 1 shows the way the browser interprets the Employee.xml document when combined with the Employee.xsl
document.
Figure 1: Output in browser when Employees.xml is called.
Design, Develop, and Deploying Your Applications


DTD

DTD (Document Type Definition) is a set of rules or grammar that we define to construct our own XML rules (also called
a "vocabulary"). In other words, a DTD provides the rules that define the elements and structure of our new
language.
This is comparable to defining table structures in Oracle for a new system. As we define the columns of a table,
determine the datatypes of the columns, determine if the column is 'Null' allowed or not, the DTD defines the
structure for the XML document. Listing 3 is an example of a basic DTD. The detailed syntax of DTD is covered
later in the paper.
<Employees>
<Empl>
<FirstName>
</FirstName>
<LastName>
</LastName>
<Dept>
</Dept>
</Empl>
</Employees>
Listing 3: Employee DTD


DOM
The Document Object Model (DOM) is a simple, hierarchical naming system that makes all of the objects in the
page, such as text, images, forms etc accessible to us. It is merely a set of plans that allow us to reconstruct the
document to a greater or lesser extent.
By definition, a complete model is one that allows us to reconstruct the whole document down to the smallest detail.
An incomplete DOM is anything less than that.
For the reader's information, the W3 DOM recognizes seventeen types of node objects for XML: Attribute,
CDATASection, Comment, DOMImplementation, Data, Document, DocumentType, DocumentFragment, Element,
Entity, EntityReference, NamedNodeMap, Node, NodeList, Notation, ProcessingInstruction, Text
For a detailed description of other node types, the reader is encouraged to visit the W3 web site at
http://www.w3.org/TR/WD-DOM/object-index.html.


SAX
Simple API for XML (SAX) is one of the two basic APIs for manipulating XML. It is used primarily on the server
side because of its characteristics of not storing the entire document in memory and processing it very fast. However,
SAX should be used mainly for reading XML documents or changing simple contents. Using it to do large-scale
manipulations like re-ordering chapters in a book or any such activities will make it extremely complicated, not that it
cannot be done.


SCHEMA
It’s a mechanism by which rules can be defined to govern the structure and content relationship within a document.
XML Schema Structures specifies the XML Schema definition language, which offers facilities for describing the
structure and constraining the contents of XML 1.0 documents. The schema language, which is itself represented in
XML 1.0 and uses namespaces, substantially reconstructs and considerably extends the capabilities, found in XML 1.0
document type definitions (DTDs). This specification depends on XML Schema Part 2: Datatypes.
Design, Develop, and Deploying Your Applications
Page 4 of 11 Paper # 214
XML Schema Datatypes is part 2 of the specification of the XML Schema language. It defines facilities for defining
datatypes. The datatype language, which is itself represented in XML 1.0, provides a superset of the capabilities found
in XML 1.0 document type definitions (DTDs) for specifying datatypes on elements and attributes.

NAMESPACES
With XML namespaces developers can qualify element names uniquely on the Web and thus avoid conflicts between
elements with the same name. The association of a Universal Resource Identifier (URI) with a namespace is purely to
ensure that two elements with the same name can remain unambiguous; no matter what the URI points to.

WELL-FORMED VS. VALID XML DOCUMENTS:Well-formed documents are those that conform to basic rules of XML such as a) the document must have only one
root element, b) it must have start and end tags for every element etc.
Valid documents are not only well-formed but also have been validated against a DTD (or a schema). A parser usually
does the validations.

XML RTF Template Techniques

Tag Description	Tag Syntax
Enter into Loop	<?for-each: group_name?>
Exit the Loop	<?end for-each?>
Display any Column	<?column_name?>
Start IF condition	<?if: column_name=1?>
End IF condition	<?end if?>
To access report level place holders	/main_group_name/column_name
To use SQL functions	<?xdofx:trim(column_name)?>
To display current date	<?xdofx:sysdate?>
To display page number	<?fo:page-number?>
Start Body	<?start:body?>
End Body	<?end body?>
For Page Break	Press Ctrl + Enter before end of loop
Sort	<?sort:column_name?>

https://blogs.oracle.com/xmlpublisher/dynamic-sorting-or-sort-by-parameter

<?for-each: group_name?>

<?sort:elementName;'descending';data-type='text'?>

elementName is element within for-each group

xdofx: substr, instr and if else cases

<?xdofx:Instr('abcabcabc','a',2)?>
<?xdofx:substr('abc-defg',1,Instr('abc-defg','-',1))?>
<?xdofx:if 1=1 then substr('abc-defg',1,Instr('abc-defg','-',1)) else substr('abcd-defg',1,Instr('abcd-defg','-',1)) end if?>
<?xdofx:if AE_PER_GRADE!='' then substr(AE_PER_GRADE,1,Instr(AE_PER_GRADE,'-',1)) else substr(AE_TERM_GRADE,1,Instr(AE_TERM_GRADE,'-',1)) end if?>

Parameterized Popup in OAF for R12.1.3

As per Dev Guide, There are two popup screen we can prepare in OAF, embedded and Parametrized, oracle has its tutorial on embedded popup, but little documentation on parametrized popups.

We will create a parameterized popup.

1. Create a package for popup as below

xxcom.oracle.apps.ibe.popup.server
 => create VOs
=> add VO to local AM

xxcom.oracle.apps.ibe.popup.webui

Identify the item which needs to be displayed as popup



ceate a stack layout on the item to be displayed as popup.

add one region, as popup, and another as link

Add caption

 
  above region is popup region



We need to create a popup region, which I prefer as Tablelayout, other only option is messagecomponnetlayout.

 


Now see How the popRN is defined



See How Parameter is Passed:

this is defined in parameter, while creating the popup region
param1={@ProductCode}&param2={@InventoryItemId}

 ProductCode and InventoryItemId are here the viewAttribute of the OrderLineVO, and can be called in below manner in PopUpCO, which is controller of additional regionn define here as popUpProductRN




Below is the code snippet of popUpProductRN controller



  public void processRequest(OAPageContext pageContext, OAWebBean webBean)
  {
    super.processRequest(pageContext, webBean);

      pageContext.writeDiagnostics(this, "popup > processRequest Starts****",6);
      String param1 =(String)pageContext.getParameter("param1");
      String param2 = (String)pageContext.getParameter("param2");


//Param1 is ProductCode Passed from View Attribute of the base page'
//param2 is inventoryitemid passed from viewObject's viewatribute of base page.
       try
      {
        popUpProductInfoVOImpl prodInfoVO =
          (popUpProductInfoVOImpl)am.findViewObject("popUpProductInfoVO1");
        prodInfoVO.setWhereClause(null);
        prodInfoVO.setWhereClause("CUST_ACCOUNT_ID=" + custAccId +
                                  " and SHIP_FROM_ORG_ID=" + InvOrgId +
                                  " and ORG_ID=" + pageContext.getOrgId() +
                                  "  and PRODUCT_CODE='" + product + "'");
        prodInfoVO.executeQuery();

      } catch (Exception e)
      {
      }

      try
      {
        popUpProductSpecVOImpl prodSpecVO =
          (popUpProductSpecVOImpl)am.findViewObject("popUpProductSpecVO1");
        prodSpecVO.setWhereClause(null);
        prodSpecVO.setWhereClause("CUST_ACCOUNT_ID=" + custAccId +
                                  " and ORGANIZATION_ID=" + InvOrgId +
                                  " and ORG_ID=" + pageContext.getOrgId() +
                                  "  and PRODUCT_CODE='" + product + "'");
        prodSpecVO.executeQuery();
      } catch (Exception e)
      {
      }

      try
      {
        OAImageBean productImage =
          (OAImageBean)webBean.findChildRecursive("productImage");
        productImage.setSource("/OA_MEDIA/ITEMS_IMAGES/" + product + ".jpg");
      } catch (Exception e)
      {
       }

      }
  }

 Popup page open up as below: