PO Master Detailed Report in Oracle EBS | PO Master Detailed Report in Oracle Apps R12 | Purchasing | Purchase Order | XML | EBS | Oracle Apps R12 | Oracle EBS

 

PO MASTER DETAILED REPORT

 

Registration steps for Oracle Reports

1.   Once we get the Requirement from Business, develop a query in TOAD/SQL Developer.

2.   Once Query has been done, place the query in Data Model SQL Block

       3.   After Adding the Parameter in User Parameters tab and successfull compilation, save the report XXRBCSAMPLE.rdf as extension

     4.     Move the .rdf to Custom Top /reports/US Folder with Executable Permissions by Using WINSCP Tool

       5.     After Login to the Application,Switch to Application Developer--> Concurrent --> Executable, give name, Short Name, application would be Custom Top Application, type is Oracle Reports , Execution file name should be given .rdf name and Save the Changes

    6.     Switch to Concurrent Program Window, Enter Concurrent Program Name, Short Name, Applications and below we have to give Executable Short Name. Output Type is Text. If we have Any parameters ,press parameters tab and add Parameters. saving the Changes

    7.     Attach our Concurrent Program in Request Group Window

    8.   Switch to respective responsibility and run the request

Requirement

Sample oracle report with xml report So will take Purchase order Report

Once requirement from business we develop query toad or sql developer and below query is PO Master Report 

select      pha.agent_id

                  ,pha.type_lookup_code

                  ,pha.segment1 po_number

                  ,pha.terms_id

                  ,pla.item_id

                  ,pla.item_description

                  ,pla.quantity,pla.unit_price

from        po_headers_all      pha

                ,po_lines_all pla

                ,per_all_people_f papf

                ,fnd_lookup_values flv

where 1=1

                and pha.po_header_id                  = pla.po_header_id and pha.agent_id    = papf.person_id

                and pha.type_lookup_code         = flv.lookup_code

                and flv.lookup_type                                       = 'POXMUB_DOCUMENT_TYPE'

                and trunc(sysdate) between nvl(trunc(papf.effective_start_date),trunc(sysdate)) and nvl(trunc(papf.effective_end_date),trunc(sysdate))

                and pha.org_id                                                 = :p_org_id

                and pha.segment1                                          = :po_number

                and flv.meaning                                                = nvl(:p_po_type,flv.meaning);

Place the Query in SQL developer and run.

Red color mark refers Master columns and yellow color mark refers child columns.

Once Query has been done after login to the Report builder and click on new then open like below

Click on DATA MODEL

After that click on SQL block shown in the below which is marked with red color

And then we have to place a query in Data Model SQL block and then click on OK Button

Once click on Ok and separate the columns which are master columns and child columns

Whenever come to user parameter tab automatically created the parameters in report and if we want to add any parameters in User parameters and their datatypes.

We have passed some runtime conditions for PO TYPE and BUYER NAME

So, add the lexical parameters in data model block as shown in the below

 Once add the lexical parameters in data model block then give the conditions for that in AFTER PARAMETER TRIGGER as shown in the below

Get the item name by using item id so, I used the formulae column as shown in the below

We have to click on FORMULAE COLUMN and give the name and set the datatype then click on PL/SQL Formulae write the logic as shown in the below

And we have to use one more formula column used for line amount as shown in the below

We have to use two PLACE HOLDER COLUMNS in report for item name and agent name with help of formulae column and click on compile and successfully compiled as shown in the below

 We have to summary columns for Purchase Order Total Amount and as shown in the below

 Once report is done then compile the report as shown in the below

Once successfully compiled then I we will save the report .rdf Extension as shown in the below

Once the RDF report file is saved ,after that we have move that file from local to the custom top report US folder with help of WINSCP tool.

Next, we have to login the application

After login the application switch the application developer concurrentexecutable

 

 We will give the name, short name, application, execution method is oracle report and execution file name should be our .rdf file name as shown in the below and we will save the changes.

  Next switch the concurrent program

Open concurrent program window we will give the program name, short name, application as shown in the below

Give executable short name and output type is xml

If we have any parameter add the parameters and save changes as shown the below

After that then we wilI create the value sets for org id ,Po number, Po type and Buyer name as shown in the below

 I will use $FLEX$ for po number below seen like

And we have to add the value sets in parameters and save changes shown below.

And we have to add our concurrent program in request group window and save changes as shown in the below

 Next, we have to switch the XML Administrator Responsibility HOME

And click on data definition tab then Create the data definition along with short name and concurrent program short name and data definition code should be matched.

Next, we have to switch the templates then we have to a create new template and add the .rtf layout in template as shown in the below

Once template successfully uploaded then switch respective responsibility then run the request As shown in the below

We have to click on submit new request in SRS window.

We have to open our concurrent program and we have to check the template visible or not as shown in the below

 We have to select the values for parameters and then run the program.

Finally the Output