Modify the Dataset to Add Project Information

You can modify the SQL query of a predefined dataset to add these data fields for projects: Project Number, Project Name, Task Number, Task Name, Expenditure Type, and Expenditure Type Description.

BI Publisher retrieves the project data from the tables you specify in the SQL query.

Access the Catalog for BI Publisher Enterprise.
In the Folders pane of the Catalog, navigate to the Custom folder and click Financials, Joint Venture Management.
Click the Joint Venture Joint Interest Billing folder to access the predefined templates and the data model.
Click Expand below the Data Models folder to open it. It includes the data model for Joint Interest Billing. Click Edit to update this data model.
On the Joint Interest Billing page, select the JVDistributionDetails dataset.
Make sure that the Diagram tab is selected and then scroll down to access the data columns.
Locate the JV_DISTRIBUTION_DETAILS column, which contains the data fields for processed distributions. You‘ll update the SQL query for this column to add the project data.
With this column selected, click Edit Selected Data Set (pencil icon).

On Edit Data Set – JV Distribution Details window, update the SQL query in the relevant sections to add three pieces of information: the project data fields, the tables to retrieve the project data from, and the conditions for retrieving the project data.

The predefined SQL query includes code for both general ledger and subledger transactions. You must add these pieces of query for both transactions.

Add the following pieces of code displayed here in bold for the query of general ledger transactions. The surrounding code is provided to help identify the context for the code.

SQL for the project data fields:

acch.document_number as JOURNAL_REFERENCE, :P_FROM_DATE,:P_TO_DATE,
:P_REPORT_DATE,proj.segment1 as PROJECT_NUMBER, proj.name as PROJECT_NAME,
task.element_number as TASK_NUMBER, task.name as TASK_NAME,
expen.expenditure_type_name as EXPENDITURE_TYPE_NAME, expen.description as
EXPENDITURE_DESCRIPTION from jv_distributions dist, jv_transactions tran,
JV_STAKEHOLDERS_VL stake, gl_code_combinations comb,

SQL for the tables to retrieve the project data from:

fnd_kf_str_instances_vl struc, fnd_kf_segments_vl seg,
fnd_kf_labeled_segments labseg , hz_parties party , jv_accounting_headers acch
, PJF_PROJECTS_ALL_VL proj,PJF_PROJ_ELEMENTS_VL task,PJF_EXP_TYPES_VL expen where

SQL for the conditions for retrieving the project data:

and tran.application_id = '-99' and tran.project_id = proj.PROJECT_ID (+)
and tran.task_id = task.PROJ_ELEMENT_ID (+) and tran.expenditure_type_id =  expen.EXPENDITURE_TYPE_ID (+)
and tran.header_id = gl.je_header_id and tran.line_num = gl.je_line_num

Add the following pieces of code displayed here in bold for the query of subledger transactions. The surrounding code is provided to help identify the context for the code. Note that the code for subledger transactions comes after the code tag union All:

SQL for the project data fields:

union ALL select case when :P_TEMPLATE_SOURCE = 'ORA_JV_STAKEHOLDER' then
 stake.STAKEHOLDER_NAME || ' '|| jv.COA_INSTANCE_NUMBER || ' '|| dist.currency_code
 else jv.JOINT_VENTURE_NAME || '_' || stake.STAKEHOLDER_NAME || ' '||
 jv.COA_INSTANCE_NUMBER || ' '|| dist.currency_code end SPLIT_KEY_ATTR ,
acch.document_number as JOURNAL_REFERENCE,:P_FROM_DATE,:P_TO_DATE,:P_REPORT_DATE
 ,proj.segment1 as PROJECT_NUMBER, proj.name as PROJECT_NAME, task.element_number
 as TASK_NUMBER, task.name as TASK_NAME,expen.expenditure_type_name as
 EXPENDITURE_TYPE_NAME, expen.description as EXPENDITURE_DESCRIPTION from
 jv_distributions dist, jv_transactions tran, JV_STAKEHOLDERS_VL stake,
 gl_code_combinations comb,

SQL for the tables to retrieve the project data from:

fnd_kf_str_instances_vl struc, fnd_kf_segments_vl seg,
 fnd_kf_labeled_segments labseg , hz_parties party ,jv_accounting_headers acch
 , PJF_PROJECTS_ALL_VL proj,PJF_PROJ_ELEMENTS_VL task,PJF_EXP_TYPES_VL expen where

SQL for the conditions for retrieving the project data:

and tran.application_id not in (-99,-10568)and tran.project_id =
proj.PROJECT_ID (+) and tran.task_id = task.PROJ_ELEMENT_ID (+) and
tran.expenditure_type_id = expen.EXPENDITURE_TYPE_ID (+) and tran.header_id =
 gl.ae_header_id and tran.line_num = gl.ae_line_num

Click OK to save the query.
If the system displays a window to enter values for lexical references in SQL Lines, enter LINES and click OK.
Click the save icon.