, , ,

How to Develop XML Publisher Report (XMLP) or BI Publisher Reports?

Training  of XML Publisher Report (XMLP) or BI Publisher Reports:

How to Develop XML Publisher Report (XMLP) or BI Publisher Reports?

hey Guys Now adays BI Publisher is in Boom technology formally it know as XMLP means XML Publisher.
We can develop this by using following steps:

1)Develop the Report(.rdf) Only DataModel

2)Save the report and Compoile and Generate the data in .xml
           Format File=>Generate to File => XML

3)Open the Ms-Word document Design the Layout include the follwing Tool Bar options called Form and Templete builder

4)Define the Table and Specify the Titles and Labels whatever we would like to print

5)Include ‘Text Form Field’ Double CLick => Add Help Text button enter the following syntax : Here G_USER_ID is Group name from .rdf Data Model Note: It means Indirectley Repeating Frame starting

6)Define the Fields which we would like to display Include ‘Text Form Field’ Double CLick => Add Help Text button enter the following syntax ?
Note: Define all the fields like this

7)We have to close the Repeating Frame (For each Loop) Include ‘Text Form Field’ Double CLick => Add Help Text button enter the following syntax

8)Save the document in .rtf(Rich Text File format)

9)Load the .xml file data and generate the Output in pdf Format Data=>Load XMl Data=>Load the .xml File=>Once Data is Loaded succesfully

10)Generate the Preview as per this Preview=>PDF

Registering in Apps

1)Develop the .rdf file and .rtf file

2)Register both .rdf and .rtf file

.rdf File Registration

1)Move the .rdf file into CUS_TOP11.5.0ReportsUS

2)Create Execuiteable

Concurrent Program Output = XML

Request Group

Responsibility

User

SRS

3)Copy the Concurrent Program Short name .rtf File

RTF Registration:

1)Goto the XML Publisher Administrator Responsibility

2) Creation of Data Defination
Select Data Definations form Enter Data Defination

Name = Enter Any Name

Code = Concurrent Program Short Name

Application = Any application

startdate = Current Date

3)Copy the DataDefination name we have to add this to the Templete

4)Creation of Template form

Open the Templete Form

Enter Templete Name : Any Name

DataDefination : enter data defination whatever we have created

Code : Concurrent Program Short Name

Type : rtf

application : any Application

File : Upload the .rtf file

Laguage : English

Territory : United States

5)Select Apply button

After register the .rtf file submit concurrent Program from SRS window system will automatically pick the .rtf file and generates the Output.

If we want to customze the .rtf file we have to download by using templete , Customize thenupload the .rtf templeteme, a.sal, b.sal, b.empno, b.ename from emp a, emp b where a.mgr=b.empno and a.sal>b.sal

/by
, , , , ,

Payables Open Interface’s Control File

load data infile ‘AP_INV.TXT’
append into table    AP_INVOICES_INTERFACE
fields terminated by ‘^’ optionally enclosed by ‘”‘
trailing nullcols
    (INVOICE_NUM              CHAR “RTRIM(:INVOICE_NUM)”,
     INVOICE_TYPE_LOOKUP_CODE   CHAR “RTRIM(:INVOICE_TYPE_LOOKUP_CODE)”,
     INVOICE_DATE               DATE “DD-MON-RRRR”,
     VENDOR_NUM                 CHAR “RTRIM(:VENDOR_NUM)”,
     VENDOR_SITE_CODE           FILLER,
     INVOICE_AMOUNT             CHAR “RTRIM(:INVOICE_AMOUNT)”,
     TERMS_NAME                 CHAR “RTRIM(:TERMS_NAME)”,
     DESCRIPTION                CHAR “RTRIM(:DESCRIPTION)”,
     DUMMY1 FILLER,
     DUMMY2 FILLER,
     DUMMY3 FILLER,
     DUMMY4 FILLER,
     GL_DATE  DATE “TO_DATE((TO_CHAR(:GL_DATE,’DD-‘)||DECODE(TO_CHAR(:GL_DATE,’MON’),’AUG’,’SEP’,’SEP’,’SEP’,’OCT’,’OCT’,TO_CHAR(:GL_DATE,’MON’))||TO_CHAR(:GL_DATE,’-YYYY’)))”,
     VOUCHER_NUM                CHAR “RTRIM(:VOUCHER_NUM)”,
     INVOICE_RECEIVED_DATE      DATE,
     AMOUNT_APPLICABLE_TO_DISCOUNT,
     TERMS_DATE                 DATE,
     SOURCE                     CONSTANT ‘MAXCIM INVOICE’,
     PAYMENT_CURRENCY_CODE      CONSTANT ‘USD’,
     PAYMENT_METHOD_LOOKUP_CODE CONSTANT ‘CHECK’,
     CALC_TAX_DURING_IMPORT_FLAG CONSTANT ‘N’,
     ORG_ID                     CONSTANT ‘166’,
     SHIP_TO_LOCATION           CONSTANT ‘Materials Warehouse’,
     INVOICE_CURRENCY_CODE      CONSTANT ‘USD’,
     creation_date              SYSDATE,
     last_updated_by            CONSTANT 1093,
     last_update_date           SYSDATE,
     created_by                 CONSTANT 1093,
     INVOICE_ID “AP_INVOICES_INTERFACE_S.NEXTVAL”)

load data infile ‘AP_LINE_TEST.TXT’
append into table    AP_INVOICE_LINES_INTERFACE
fields terminated by ‘^’ optionally enclosed by ‘”‘
trailing nullcols
    (REFERENCE_2              CHAR “RTRIM(:REFERENCE_2)”,
     TYPE              FILLER,
     ACCOUNTING_DATE         DATE “DD-MON-RRRR”,
     DESCRIPTION          CHAR “RTRIM(:DESCRIPTION)”,
     DIST_CODE_COMBINATION_ID          CHAR “RTRIM(:DIST_CODE_COMBINATION_ID)”,
     LAST_UPDATED_BY          CHAR “RTRIM(:LAST_UPDATED_BY)”,
     AMOUNT                  CHAR “RTRIM(:AMOUNT)”,
     CREATED_BY          CHAR “RTRIM(:CREATED_BY)”,
     CREATION_DATE          DATE “DD-MON-RRRR”,
     ATTRIBUTE1          CHAR “RTRIM(:ATTRIBUTE1)”,
     ATTRIBUTE2          CHAR “RTRIM(:ATTRIBUTE2)”,
     ATTRIBUTE3          CHAR “RTRIM(:ATTRIBUTE3)”,
     TYPE_1099          CHAR “RTRIM(:TYPE_1099)”,
     UNIT_OF_MEAS_LOOKUP_CODE      CHAR “RTRIM(:UNIT_OF_MEAS_LOOKUP_CODE)”,
     TAX_RATE              CHAR “RTRIM(:TAX_RATE)”,
     QUANTITY_INVOICED      CHAR “RTRIM(:QUANTITY_INVOICED)”,
     UNIT_PRICE          CHAR “RTRIM(:UNIT_PRICE)”,
     INVOICE_ID “AP_INVOICE_LINES_INTERFACE_S.NEXTVAL”)

/by
, , , , ,

What is Query for getting customers having multiple accounts?

SELECT hca.account_number,rc.customer_name, hp.party_type,hp.party_name
 FROM hz_parties hp, hz_Cust_Accounts hca,ar.ra_customers rc
WHERE hp.party_id = hca.party_id
and rc.customer_id=hca.cust_Account_id
  AND hca.cust_account_id IN (
         SELECT   customer_id
             FROM ap_bank_account_uses_all bauses,
                  ap_bank_accounts_all bacct
            WHERE bauses.external_bank_account_id = bacct.bank_account_id
              AND primary_flag = ‘Y’
         GROUP BY customer_id)
          HAVING COUNT (DISTINCT (external_bank_account_id)) > 1)
/by
, , , ,

What is Value Sets? What are Types Of Value Sets?

Oracle Application Object Library uses values; value sets and validation tables as important components of key FLEXFIELDs, descriptive FLEXFIELDs, and Standard Request Submission. This section helps you understand, use and change values, value sets, and validation tables. When you first define your FLEXFIELDs, you choose how many segments you want to use and what order you want them to appear. You also choose how you want to validate each of your segments. The decisions you make affect how you define your value sets and your values. You define your value sets first, either before or while you define your FLEXFIELD segment structures. You typically define your individual values only after your FLEXFIELD has been completely defined (and frozen and compiled). Depending on what type of value set you use, you may not need to predefine individual values at all before you can use your FLEXFIELD.
You can share value sets among segments in different FLEXFIELDs, segments in different structures of the same FLEXFIELD, and even segments within the same FLEXFIELD structure. You can share value sets across key and descriptive FLEXFIELDs. You can also use value sets for report parameters for your reports that use the Standard Request Submission feature.
Because the conditions you specify for your value sets determine what values you can use with them, you should plan both your values and your value sets at the same time. For example, if your values are 01, 02 instead of 1, 2, you would define the value set with Right–Justify Zero–fill set to Yes.
Value set is nothing but List of Values with validations. We can use the Value Sets when ever the Concurrent Program has parameters and while defining the Flex Fields. We have to attach the value sets to the Concurrent Program. Validations are depending on Client Requirement.
Value sets are of 8 types.There are several validation types that affect the way users enter and use segment or parameter values:
1. None (not validated at all)
2. Independent
3. Dependent
4. Table
5. Special (advanced)
6. Pair (advanced)
7. Translatable Independent
8. Translatable Dependent

You cannot change the validation type of an existing value set, since your changes affect all FLEXFIELDs and report parameters that use the same value set.

None: You use a None type value set when you want to allow users to enter any value so long as that value meets the value set formatting rules. That is, the value must not exceed the maximum length you define for your value set, and it must meet any format requirements for that value set. For example, if the value set does not allow alphabetic characters, your user could not enter the value ABC, but could enter the value 456 (for a value set with maximum length of three). The values of the segment using this value set are not otherwise validated, and they do not have descriptions. Because a NONE value set is not validated, a segment that uses this value set does not provide a list of values for your users. A segment that uses this value set (that is, a non–validated segment) cannot use FLEXFIELD value security rules to restrict the values a user can enter.
Independent > An Independent value set provides a predefined list of values for a segment. These values can have an associated description. For example, the value 01 could have a description of ‘Company 01’. The meaning of a value in this value set does not depend on the value of any other segment. Independent values are stored in an Oracle Application Object Library table. You define independent values using an Oracle Applications window, Segment Values.

Table > A table–validated value set provides a predefined list of values like an independent set, but its values are stored in an application table. You define which table you want to use, along with a WHERE cause to limit the values you want to use for your set. Typically, you use a table–validated set when you have a table whose values are already maintained in an application table (for example, a table of vendor names maintained by a Define Vendors form). Table validation also provides some advanced features such as allowing a segment to depend upon multiple prior segments in the same structure.

Dependent > A dependent value set is similar to an independent value set, except that the available values in the list and the meaning of a given value depend on which independent value was selected in a prior segment of the FLEXFIELD structure. You can think of a dependent value set as a collection of little value sets, with one little set for each independent value in the corresponding independent value set. You must define your independent value set before you define the dependent value set that depends on it. You define dependent values in the Segment Values windows, and your values are stored in an Oracle Application Object Library table.

Special and Pair Value Sets:
Special and pair value sets provide a mechanism to allow a”FLEXFIELD–within–a–FLEXFIELD”. These value sets are primarily used for Standard Request Submission parameters. You do not generally use these value sets for normal FLEXFIELD segments. Special and Pair value sets use special validation routines you define. For example, you can define validation routines to provide another FLEXFIELD as a value set for a single segment or to provide a range FLEXFIELD as a value set for a pair of segments.

Translatable Independent and Translatable Dependent :A Translatable Independent value set is similar to Independent value set in that it provides a predefined list of values for a segment. However, a translated value can be used. A Translatable Dependent value set is similar to Dependent value set in that the available values in the list and the meaning of a given value depend on which independent value was selected in a prior segment of the FLEXFIELD structure. However, a translated value can be used. FLEXFIELD Value Security cannot be used with Translatable Independent or Translatable Dependent value sets. For format validation, translatable value sets must use the format type Char. The maximum size must be no greater than 150. The Number Only option and the Right–justify and Zero–Fill Numbers option cannot be used with translatable value sets. Range FLEXFIELDs cannot use Translatable Independent or Translatable Dependent value sets.

/by
, , , ,

Navigation Path for Value Set Creation?

Navigation Path for Value Set Creation:
Application-> Validation -> Set.
Once we create Independent & Dependent valueset then we can attach values to the valueset by using the following Navigation.
Application -> -> Validation
-> Values (To create values for value set)
NOTE: Once we attach any value to Independent & Dependent we can’t delete that value, but we can disable that value. Duplicate values are not allowed in list of values.

Develop a Report using Query and by creating valueset :Select USER_ID, USERNAME From FND_USER Where
USER_ID Between :X AND :Y
:$FLEX$ – It is One of the Oracle applications Key word which we use to get the prevents parameter value in current list of values “WHERE Clause”. We can be use Table Values in the “Where Clause Box”.
Query Using: $FLEX$
Select
VENDOR_SITE_ID
From
PO_VENDOR_SITE_ALL
Where
VENODR_ID = :$FLEX$.VEN_TABLE
Note: We can give Where Clause Condition in creation of Second Value Set.
Practical:Query:
Select *
From ORF_ORGANIZATION_DEFINATIONS
Where ORGANIZATION_ID = :P_ORG_ID
And BUSINESS_GROUP_ID = :P_BUSINESS_GROUP_ID

In Where Clause write the statement as
Where
BUSINESS_GROUP_ID = :$FLEX$.BUISINESS_GROUP

Range:
When ever we have to restrict the user with in the given values we use Range. For example when ever our parameter is having “From Date and To Date” we have to use Range option to restrict the user to enter the values between Low and High.
Note:
Pre defined value set for date is “FND_DATE” and its default format is “DD-MON-YY”.Alias name is mandatory when we are specifying ‘:$FLEX$’ and Column Name in ‘Additional Column’.

/by