Fixed Asset ERP Data to Blackline Tool

 Select  '1' as KEY,

        market,

       segment4,

       segment1,

       segment2,

       Period_End_Date,

       sum(period_activity)+ sum(begin_balance) BALANCE

from 

(

with bal as (select /*+ materialize */ * FROM  (SELECT 

FTH.book_type_code book_type_code,

fab.asset_number Cloud_Asset_Number,

gcc.segment1 , 

gcc.segment2 , 

gcc.segment3 , 

'COST' Type,

fab.asset_id,

FDD.cost,

FDP.PERIOD_NAME PERIOD_NAME,

fadh.units_assigned

FROM 

fa_distribution_history fadh, 

fa_transaction_headers fth,

gl_code_combinations gcc, 

fa_additions_b fab,

FA_DEPRN_PERIODS FDP,

FA_DEPRN_DETAIL FDD

WHERE 1=1

and fth.asset_id = fadh.asset_id

and fth.book_type_code = fadh.book_type_code

and fadh.code_combination_id = gcc.code_combination_id

and fab.asset_id = fth.asset_id

and fadh.transaction_header_id_in = fth.transaction_header_id

and fadh.book_type_code = fth.book_type_code

AND FDD.ASSET_ID = FTH.ASSET_ID

AND FDD.DISTRIBUTION_ID = FADH.DISTRIBUTION_ID

AND FDD.PERIOD_COUNTER = (SELECT MAX(FDP1.PERIOD_COUNTER)

FROM FA_DEPRN_PERIODS FDP1

,FA_DEPRN_DETAIL FDS1

WHERE FDS1.BOOK_TYPE_CODE = FDD.BOOK_TYPE_CODE

AND FDS1.ASSET_ID = FDD.ASSET_ID

AND FDS1.DISTRIBUTION_ID = FDD.DISTRIBUTION_ID

AND FDP1.BOOK_TYPE_CODE = FDD.BOOK_TYPE_CODE

AND FDS1.PERIOD_COUNTER = FDP1.PERIOD_COUNTER

AND FDP1.CALENDAR_PERIOD_OPEN_DATE <= fnd_date.String_to_date(:P_PERIOD_NUM||'-'||:P_PERIOD_YEAR,'MM-YY')

)

AND FDD.PERIOD_COUNTER = FDP.PERIOD_COUNTER

AND FDD.BOOK_TYPE_CODE = FDP.BOOK_TYPE_CODE

UNION ALL

SELECT 

FADH.book_type_code book_type_code,

fab.asset_number Cloud_Asset_Number,

gcc.segment1 , 

gcc.segment2 , 

gcc.segment3 , 

'DEP_RES' Type,

fab.asset_id,

fds.deprn_reserve COST,

FDP.PERIOD_NAME PERIOD_NAME,

fadh.units_assigned

FROM 

FA_DEPRN_DETAIL fds, 

fa_distribution_history fadh, 

gl_code_combinations gcc, 

fa_additions_b fab, 

FA_DEPRN_PERIODS FDP

WHERE 1=1

and fadh.asset_id = fds.asset_id

and fadh.DISTRIBUTION_ID = fds.DISTRIBUTION_ID

and fadh.book_type_code = fds.book_type_code

and fadh.code_combination_id = gcc.code_combination_id

and fab.asset_id = fadh.asset_id

AND FDS.PERIOD_COUNTER = FDP.PERIOD_COUNTER

AND FDP.PERIOD_COUNTER = (SELECT MAX(FDP1.PERIOD_COUNTER)

FROM FA_DEPRN_PERIODS FDP1

,FA_DEPRN_DETAIL FDS1

WHERE FDS1.BOOK_TYPE_CODE = FDS.BOOK_TYPE_CODE

AND FDS1.ASSET_ID = FDS.ASSET_ID

AND FDS1.DISTRIBUTION_ID = FDS.DISTRIBUTION_ID

AND FDP1.BOOK_TYPE_CODE = FDP.BOOK_TYPE_CODE

AND FDS1.PERIOD_COUNTER = FDP1.PERIOD_COUNTER

AND FDP1.CALENDAR_PERIOD_OPEN_DATE <= fnd_date.String_to_date(:P_PERIOD_NUM||'-'||:P_PERIOD_YEAR,'MM-YY')

) 

AND FDS.BOOK_TYPE_CODE = FDP.BOOK_TYPE_CODE))

,CURRENT_UNITS AS (select /*+ materialize */ * FROM  (SELECT FAH.ASSET_ID , FAH.UNITS , FAH.BOOK_TYPE_CODE , GCC1.SEGMENT4 COST_SEGMENT4, GCC2.SEGMENT4 RESERVE_SEGMENT4

FROM 

FA_ASSET_HISTORY FAH

,FA_CATEGORY_BOOKS FCB

,GL_CODE_COMBINATIONS GCC1

,GL_CODE_COMBINATIONS GCC2

,(SELECT MAX(FTH.TRANSACTION_HEADER_ID) TRANSACTION_HEADER_ID, FTH.ASSET_ID , FTH.BOOK_TYPE_CODE

FROM  FA_TRANSACTION_HEADERS FTH

,XLA_EVENTS XE

,FA_ASSET_HISTORY FAH1

WHERE 1=1

AND FTH.EVENT_ID = XE.EVENT_ID

AND XE.EVENT_DATE  <= LAST_DAY(fnd_date.String_to_date(:P_PERIOD_NUM||'-'||:P_PERIOD_YEAR,'MM-YY'))

AND FTH.TRANSACTION_HEADER_ID = FAH1.TRANSACTION_HEADER_ID_IN

AND FTH.BOOK_TYPE_CODE = FAH1.BOOK_TYPE_CODE

AND FTH.ASSET_ID = FAH1.ASSET_ID

GROUP BY FTH.ASSET_ID , FTH.BOOK_TYPE_CODE) ASSET_HISTORY

WHERE 1=1

AND ASSET_HISTORY.ASSET_ID = FAH.ASSET_ID

AND ASSET_HISTORY.TRANSACTION_HEADER_ID = FAH.TRANSACTION_HEADER_ID_IN

AND ASSET_HISTORY.BOOK_TYPE_CODE = FAH.BOOK_TYPE_CODE

AND FAH.CATEGORY_ID = FCB.CATEGORY_ID

AND FAH.BOOK_TYPE_CODE = FCB.BOOK_TYPE_CODE

AND FCB.ASSET_COST_ACCOUNT_CCID = GCC1.CODE_COMBINATION_ID

AND FCB.RESERVE_ACCOUNT_CCID = GCC2.CODE_COMBINATION_ID))

SELECT rf.ancestor_pk1_value           

                        || Chr(9)            market,

                         (CASE WHEN Type = 'DEP_RES' THEN CU.RESERVE_SEGMENT4

ELSE CU.COST_SEGMENT4 END)

                        || Chr(9)            segment4,

                        bal.segment1

                        || Chr(9)            segment1,

                        bal.segment2

                        || Chr(9)

                        || Chr(9)

                        || Chr(9)

                        || Chr(9)

                        || Chr(9)

                        || Chr(9)

                        || Chr(9)            segment2,

To_char(Last_day(fnd_date.String_to_date(gp.period_name,

                 'Mon-YY')),

'MM/DD/YYYY'

|| Chr(9)

|| Chr(9)

|| Chr(9))           Period_End_Date,

(CASE WHEN Type = 'DEP_RES' THEN To_char((round(bal.COST,2))*(-1),'fm999999999990.00')

ELSE  To_char(round(bal.cost,2),'fm999999999990.00') END) period_activity ,

TO_CHAR(nvl(null,0) ,'fm999999999990.00') begin_balance

from 

bal,

CURRENT_UNITS CU,

gl_ledgers GLG,

(SELECT DISTINCT tree_code,ancestor_pk1_value,distance,pk1_value FROM gl_seg_val_hier_rf) rf,

gl_periods GP,

fnd_lookup_values  flv,

fa_book_controls fbc

where 1=1

and GLG.ledger_category_code = 'PRIMARY'

AND gp.period_num = :P_PERIOD_NUM

AND gp.period_year = :P_PERIOD_YEAR

AND GLG.name = nvl(:P_LEDGER,GLG.name)

AND fnd_date.String_to_date(bal.period_name,'Mon-YY') <= fnd_date.String_to_date(gp.period_name,'Mon-YY')

AND fbc.book_type_code = bal.book_type_code

and fnd_date.String_to_date(bal.period_name,'Mon-YY') <=NVL(flv.end_Date_active,SYSDATE+1)

AND rf.pk1_value = bal.segment1

AND rf.tree_code = flv.description

AND flv.lookup_type = 'xxxxxxxxxxxx'

AND rf.distance = flv.tag

and flv.language='US'

and flv.lookup_code = 'xxxxxxxxxxxx' -- RICE ID for the given object

AND fbc.book_class = 'CORPORATE'

AND GLG.LEDGER_ID = fbc.set_of_books_id

AND CU.ASSET_ID = BAL.ASSET_ID

AND CU.BOOK_TYPE_CODE = BAL.BOOK_TYPE_CODE

)

group by 

market,

       segment4,

       segment1,

       segment2,

       Period_End_Date

order by 

market,

       segment4,

       segment1,

       segment2

Accessing Oracle BIP Reports via a URL using Oracle Sandbox or Page Composer

• • server:port - is the name of the server and port number where BI Publisher is running
  • xmlpserver - is a required static string (the name of the application)
  • ReportDirectory - is the folder path to the report. When the report is under Shared Folders, do not include "Shared Folders" in the path. If the report is under My Folders, include the ~username as the first node in the path. See the examples following.
  • ReportName.xdo - is the name of the report with the .xdo extension. If the name contains spaces, replace the space with a "+" character.

  Examples:

  The following URL launches the North America Sales report. The report resides in the catalog under Shared Folders/Samples/Sales. Note that Shared Folders is not included in the path.

  http://example.com:7001/xmlpserver/Samples/Sales/North+America+Sales.xdo

  The following URL launches the North America Sales report that resides in the catalog under My Folders/Samples/Sales. Note that the user name in this case is weblogic, therefore the first node in the path is ~weblogic.

  http://example.com:7001/xmlpserver/~weblogic/Samples/Sales/North+America+Sales.xdo

  Specifying Parameters in the URL

  The preceding examples render the complete report inside the BI Publisher report viewer with all the report controls.

  The default layout, default output format, and default parameters are used to render the report. You can add parameters to the URL to specify how the report renders.

  When constructing the URL, note the following standard URL syntax:? - denotes the first parameter& - denotes each additional parameter

  The following table describes more parameters you can add to the URL.

  Parameter	Definition	Example Usage
  _xpt	Specifies whether to render the report in the BI Publisher report viewer or export the document to a new window appropriate for the output type. For example, if the output type specified is html, the report document (only) will render in a browser window; if the output type is PDF you will be prompted to save or open the PDF document. When this parameter is not specified, the report renders in the BI Publisher report viewer. Valid values are: 0 - renders the report in the BI Publisher report viewer 1- exports the document to appropriate application window	_xpt=0
  _xdo	(Optional) Provides the path to the report.	_xdo=%2FSamples%2FSalary+Report.xdo
  _xt	Specifies the layout to use. Enter the name of the layout as defined in the report definition. If an invalid name is entered, the default layout is used.	_xt=Manager+Summary
  _xf	Specifies the output format. If no value is specified, the default output format is used. If an invalid value is specified, or, if a value is specified that is not enabled for the layout, the report does not render. Valid values are: analyze - use for Interactive output rtf docx pdf html pptx - use for PowerPoint 2007 ppt - use for PowerPoint xml - use for Data excel - use for Excel excel2000 - use for Excel 2000 xslx - use for Excel 2007 csv	_xf=pdf
  Report parameters as named in the data model	Specify name-value pairs for the parameters specific to the report. You must use the parameter name as defined in the data model.	dept=10
  _xmode	Specifies the report viewer mode. If not specified, defaults to view in the full report viewer. Valid values are: 0 - view in the full report viewer. 1 - hide BI Publisher banner, hide parameters, can change layout, other actions: export only. 2 - hide BI Publisher banner. (No Header) 3 - hide BI Publisher banner, hide parameters (No Parameters) 4 - report document only: hide BI Publisher banner, hide parameters, hide other actions, hide layouts. (Document Only)	_xmode=1

  Example:

  http://example.com:7001/xmlpserver/Samples/Salary+Report.xdo?_xpt=0&_xdo=%2FSamples%2FSalary%20Report.xdo&_xmode=4&dept=10&_xt=Simple&_xf=html

  This URL runs the report "Salary Report" located under Shared Folders/Samples. Note the following:

  _xpt=0 renders the document in the report viewer

  _xdo=%2FSamples%2FSalary%20Report.xdo defines the report path

  _xmode=4 renders the document only

  dept=10 sets the report-specific parameter "dept" to "10"

  _xt=Simple uses the layout called "Simple"

  _xf=html sets the output format to html

   

   

   

  Document URL:

  https://docs.oracle.com/cd/E80149_01/bip/BIPRD/GUID-F788B306-99F6-432E-BCD5-F45046D31684.htm#BIPRD3324

   

  Reference parameter Screenshots :

  click on link and select Edit Components:

  
  

• 
  

•