1. Implementing Joint Venture Management
  2. Modify the Dataset to Add Account Hierarchy

Modify the Dataset to Add Account Hierarchy

Modify the predefined dataset to add the hierarchy for the accounts used in the joint venture transactions.

This task consists of three parts:

  • Modify the distribution details dataset to add account combination.

  • Create a new dataset to provide the details of the account hierarchy.

  • Create a parent-child link between the account combination and the account hierarchy dataset that you created.

Modify the Distribution Details Dataset to Add Account Combination

  1. Access the Catalog for BI Publisher Enterprise.
  2. In the Folders pane of the Catalog, navigate to the Custom folder and click Financials, Joint Venture Management.
  3. Click the Joint Venture Joint Interest Billing folder to access the predefined templates and the data model.
  4. 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.
  5. On the Joint Interest Billing page, select the JVDistributionDetails dataset.
  6. Make sure that the Diagram tab is selected and then scroll down to access the data columns.
  7. 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 code for account combination.
  8. With this column selected, click Edit Selected Data Set.
  9. On Edit Data Set – JV Distribution Details window, update the SQL query to include the following code displayed here in bold. You must add the code for both general ledger and subledger transactions.
    Note: The SQL query for subledger transactions comes after the code tag union All:
    acch.document_number as JOURNAL_REFERENCE, :P_FROM_DATE,:P_TO_DATE,:P_REPORT_DATE,
comb.code_combination_id from jv_distributions dist, jv_transactions tran,
JV_STAKEHOLDERS_VL stake, gl_code_combinations comb, JV_INVOICING_PARTNERS_VL inv,
JV_JOINT_VENTURES_VL jv, gl_je_lines gl,
  10. Click OK to save the query.
  11. If the system displays a window to enter values for lexical references in SQL Lines, enter LINES and click OK.
  12. Click the save icon.

Create a Dataset to Provide Details of Account Hierarchy

After you complete the preceding task, create a new dataset to provide details of the account hierarchy:

  1. Access the data model for Joint Interest Billing in the Custom folder. Click Edit to update this data model.
  2. On the Joint Interest Billing page, make sure that the Diagram tab is selected.
  3. To create a dataset, click the New Data Set button and select SQL Query from the drop-down list.
  4. On New Dataset SQL Query window, enter a name for the dataset and complete the following fields:
    • Data Source. Select Application DB_FSCM (Default).
    • SQL Type. Select Standard SQL.
  5. In the SQL Query field, enter the following code: 
    Select tree.parent_pk1_value parent, tree.pk1_start_value child, tree.depth,
comb.code_combination_id, seg_inst.tree_code as seginsttreecode, Case when seg.column_name
= 'SEGMENT1' then comb.SEGMENT1 when seg.column_name = 'SEGMENT2' then comb.SEGMENT2 when
seg.column_name = 'SEGMENT3' then comb.SEGMENT3 when seg.column_name = 'SEGMENT4' then
comb.SEGMENT4 when seg.column_name = 'SEGMENT5' then comb.SEGMENT5 when seg.column_name =
'SEGMENT6' then comb.SEGMENT6 when seg.column_name = 'SEGMENT7' then comb.SEGMENT7 when
seg.column_name = 'SEGMENT8' then comb.SEGMENT8 when seg.column_name = 'SEGMENT9' then
comb.SEGMENT9 when seg.column_name = 'SEGMENT10' then comb.SEGMENT10 when seg.column_name
= 'SEGMENT11' then comb.SEGMENT11 when seg.column_name = 'SEGMENT12' then comb.SEGMENT12
when seg.column_name = 'SEGMENT13' then comb.SEGMENT13 when seg.column_name = 'SEGMENT14'
then comb.SEGMENT14 when seg.column_name = 'SEGMENT15' then comb.SEGMENT15 when
seg.column_name = 'SEGMENT16' then comb.SEGMENT16 when seg.column_name = 'SEGMENT17' then
comb.SEGMENT17 when seg.column_name = 'SEGMENT18' then comb.SEGMENT18 when seg.column_name
= 'SEGMENT19' then comb.SEGMENT19 when seg.column_name = 'SEGMENT20' then comb.SEGMENT20
when seg.column_name = 'SEGMENT21' then comb.SEGMENT21 when seg.column_name = 'SEGMENT22'
then comb.SEGMENT22 when seg.column_name = 'SEGMENT23' then comb.SEGMENT23 when
seg.column_name = 'SEGMENT24' then comb.SEGMENT24 when seg.column_name = 'SEGMENT25' then
comb.SEGMENT25 when seg.column_name = 'SEGMENT26' then comb.SEGMENT26 when seg.column_name
 = 'SEGMENT27' then comb.SEGMENT27 when seg.column_name = 'SEGMENT28' then comb.SEGMENT28
 when seg.column_name = 'SEGMENT29' then comb.SEGMENT29 else comb.SEGMENT30 end as account
 ,tree.tree_version_id from fnd_kf_segment_instances seg_inst,fnd_kf_str_instances_b struc,
gl_code_combinations comb, fnd_kf_segments_b seg, fnd_kf_labeled_segments labseg ,
FND_TREE_VERSION treeversion,fnd_tree_node tree where struc.STRUCTURE_INSTANCE_NUMBER
 = comb.chart_of_accounts_id and struc.application_id = 101 and struc.key_flexfield_code
 = 'GL#' and struc.structure_id = seg.structure_id and labseg.structure_id = struc.structure_id
 and labseg.segment_code = seg.segment_code and struc.structure_instance_id = 
seg_inst.structure_instance_id and treeversion.tree_code = seg_inst.tree_code and
 treeversion.TREE_STRUCTURE_CODE ='GL_ACCT_FLEX' and TRUNC(SYSDATE) BETWEEN 
treeversion.EFFECTIVE_START_DATE AND treeversion.EFFECTIVE_END_DATE and treeversion.status =
 'ACTIVE' and labseg.segment_label_code = 'GL_ACCOUNT' and seg_inst.segment_code = 
seg.segment_code and tree.tree_structure_code = 'GL_ACCT_FLEX' and tree.tree_code =
 seg_inst.tree_code and tree.tree_version_id = treeversion.tree_version_id and 
((tree.pk1_start_value = comb.SEGMENT1) or (tree.pk1_start_value = comb.SEGMENT2)
 or (tree.pk1_start_value = comb.SEGMENT3) or (tree.pk1_start_value = comb.SEGMENT4)
 or (tree.pk1_start_value = comb.SEGMENT5) or (tree.pk1_start_value = comb.SEGMENT6)
 or (tree.pk1_start_value = comb.SEGMENT7) or (tree.pk1_start_value = comb.SEGMENT8)
 or (tree.pk1_start_value = comb.SEGMENT9) or (tree.pk1_start_value = comb.SEGMENT10)
 or (tree.pk1_start_value = comb.SEGMENT11) or (tree.pk1_start_value = comb.SEGMENT12)
 or (tree.pk1_start_value = comb.SEGMENT13) or (tree.pk1_start_value = comb.SEGMENT14)
 or (tree.pk1_start_value = comb.SEGMENT15) or (tree.pk1_start_value = comb.SEGMENT16)
 or (tree.pk1_start_value = comb.SEGMENT17) or (tree.pk1_start_value = comb.SEGMENT18)
 or (tree.pk1_start_value = comb.SEGMENT19) or (tree.pk1_start_value = comb.SEGMENT20)
 or (tree.pk1_start_value = comb.SEGMENT21) or (tree.pk1_start_value = comb.SEGMENT22)
 or (tree.pk1_start_value = comb.SEGMENT23) or (tree.pk1_start_value = comb.SEGMENT24)
 or (tree.pk1_start_value = comb.SEGMENT25) or (tree.pk1_start_value = comb.SEGMENT26)
 or (tree.pk1_start_value = comb.SEGMENT27) or (tree.pk1_start_value = comb.SEGMENT28)
 or (tree.pk1_start_value = comb.SEGMENT29) or (tree.pk1_start_value = comb.SEGMENT30))
  6. Click OK to save the query.
  7. If the system displays a window to enter values for lexical references in SQL Lines, enter LINES and click OK.
  8. Click the save icon.
    The new dataset you created appears as a new column in the Diagram tab.

Create a Parent-Child Link Between the Account and Account Hierarchy Details

  1. Access the Joint Interest Billing page in BI Publisher and make sure that the Diagram tab is selected.
  2. Scroll down to the Distribution Details dataset and select the CODE_COMBINATION_ID element.
  3. Click the icon on the right of this element to open the element action menu and select Create Link.
  4. On the Create Link window, select all the elements under the dataset for account hierarchy.
  5. Click OK.
    Notice that the link is created between the two datasets.