Caused by: oracle.xdo.parser.v2.XPathException: Variable not defined: '_MR'.

Noticed that this issue is caused when you insert a graphical object like Text Box in rtf file, try to remove and this error should go away.

Dynamic Header and Footer in XML Publisher rtf Template

Code highlight in yellow is important.

--place below text in header

<?call@inlines:HeaderLogo?><?with-param:orgName;.//DocumentPayable/InvoiceBusinessUnit/InvoiceBusinessUnitName?><?end call?>

Place below in rtf main body

<?start-body?>

<?for-each-group@section:OutboundPayment;.//DocumentPayable/InvoiceBusinessUnit/InvoiceBusinessUnitName?>

---write desired code/tablees here ---

<?end for-each-group?>

<?end-body?>

--place below text in footer

<?call@inlines:footer_content?><?with-param:orgName; .//DocumentPayable/InvoiceBusinessUnit/InvoiceBusinessUnitName?><?end call?>

Subtemplates used :

<?template:HeaderLogo?>

<?param: orgName;’zz’?>

<?choose:?>

<?when: $orgName = ‘ABC Company’?>ABC LTD (You can attach Image here as well)<?end when?>

<?when: $orgName =’XYZ Company’?>XYZ LTD (You can attach Image here as well)<?end when?>

<?otherwise:?>

none

<?end otherwise?>

<?end choose?>

<?end template?>

                                                                       

<?template: footer_content?>

<?param: orgName;’tt’?>

<?choose:?>

<?when: $orgName = ‘ABC Company’?>ABC LTD (You can attach Image here as well)<?end when?>

<?when: $orgName =’XYZ Company’?>XYZ LTD (You can attach Image here as well)<?end when?>

<?end otherwise?>

none

<?end choose?>

<?end template?>

Fusion FA Asset Register Report : SQL

SELECT 
 FA.ASSET_NUMBER
,FAT.DESCRIPTION
,FB.ORIGINAL_COST
,FB.DATE_PLACED_IN_SERVICE
,FA.ATTRIBUTE_CATEGORY_CODE
,FCB.SEGMENT1 MAJOR_CATEGORY
,FCB.SEGMENT2 MINOR_CATEGORY
,NVL(FDS.DEPRN_RESERVE,0) DEPRN_RESERVE
,NVL(FDS.YTD_DEPRN,0) YTD_DEPRN
,(select FL.SEGMENT1 ||' ,'|| FL.SEGMENT2 ||' ,'|| FL.SEGMENT3 from fa_locations FL where FL.location_id = FDH.LOCATION_ID) location
,(SELECT FULL_NAME FROM PER_PERSON_NAMES_F WHERE PERSON_ID= FDH.ASSIGNED_TO AND NAME_TYPE ='GLOBAL') EMP_NAME
, (SELECT TO_CHAR(TRUNC(METH.LIFE_IN_MONTHS/12) +
             (MOD(METH.LIFE_IN_MONTHS,12)/100), '9000D00')
                                LIFE_IN_YR_MONTHS
FROM FA_METHODS METH
WHERE 1=1
AND METH.METHOD_ID = FB.METHOD_ID
AND ROWNUM =1
   ) LIFE_IN_YR_MONTHS
,1/(SELECT (METH.LIFE_IN_MONTHS/12) LIFE_IN_YR
FROM FA_METHODS METH
WHERE 1=1
AND METH.METHOD_ID = FB.METHOD_ID
AND ROWNUM =1
   ) DEPRN_RATIO
--,FCATB.ASSET_COST_ACCOUNT_CCID COST_ACCT_CCID
--,FCATB.RESERVE_ACCOUNT_CCID ACCUMULATED_DEPRN_CCID
--,FDH.CODE_COMBINATION_ID EXPENSE_CCID
--, (SELECT SEGMENT4 FROM GL_CODE_COMBINATIONS WHERE CODE_COMBINATION_ID = FCATB.ASSET_COST_ACCOUNT_CCID ) COST_ACCT
--, (SELECT SEGMENT4 FROM GL_CODE_COMBINATIONS WHERE CODE_COMBINATION_ID = FCATB.RESERVE_ACCOUNT_CCID    ) ACCDEP_ACCT
--, (SELECT SEGMENT4 FROM GL_CODE_COMBINATIONS WHERE CODE_COMBINATION_ID = FDH.CODE_COMBINATION_ID       ) EXP_ACCT
--,FDS_P.DEPRN_RESERVE DEPRN_RESERVE_PREVIOUS
--,FDS_P.DEPRN_AMOUNT
--,FDH.ASSIGNED_TO
--,FDH.LOCATION_ID
--,(SELECT PERIOD_NAME FROM FA_DEPRN_PERIODS WHERE PERIOD_COUNTER = FDP_C.PERIOD_COUNTER and BOOK_TYPE_CODE = NVL(:P_BOOK_TYPE_CODE, '-1') ) CURRENT_PERIOD
--,FDP_C.PERIOD_COUNTER CURRENT_PERIOD_COUNTER
--,FDP_P.PERIOD_COUNTER PREVIOUS_PERIOD_COUNTER
--,(SELECT PERIOD_NAME FROM FA_DEPRN_PERIODS WHERE PERIOD_COUNTER = FDP_P.PERIOD_COUNTER and BOOK_TYPE_CODE = NVL(:P_BOOK_TYPE_CODE, '-1')) PREVIOUS_PERIOD
--ROUND(SYSDATE - FB.DATE_PLACED_IN_SERVICE,0) DAYS_IN_SERVICE
FROM
  FA_ADDITIONS_B FA
, FA_ADDITIONS_TL FAT
, FA_BOOKS FB
, FA_CATEGORIES_B FCB
, FA_CATEGORY_BOOKS FCATB
, FA_DEPRN_SUMMARY FDS
--, FA_DEPRN_SUMMARY FDS_P
, FA_DISTRIBUTION_HISTORY FDH
, (SELECT MAX(PERIOD_COUNTER) AS PERIOD_COUNTER FROM FA_DEPRN_PERIODS WHERE PERIOD_CLOSE_DATE IS NULL AND BOOK_TYPE_CODE =  NVL(:P_BOOK_TYPE_CODE, '-1') ) FDP_C
--, (SELECT MAX(PERIOD_COUNTER) AS PERIOD_COUNTER FROM FA_DEPRN_PERIODS WHERE PERIOD_CLOSE_DATE IS NOT NULL AND BOOK_TYPE_CODE =  NVL(:P_BOOK_TYPE_CODE, '-1') ) FDP_P
, FA_BOOK_CONTROLS FBC
WHERE 1=1
AND FAT.ASSET_ID = FA.ASSET_ID
AND ( userenv('LANG') ) = FAT.language
------
AND FA.ASSET_ID = FB.ASSET_ID 
AND FCB.CATEGORY_ID = FA.ASSET_CATEGORY_ID
AND FCATB.CATEGORY_ID = FCB.CATEGORY_ID
AND FB.BOOK_TYPE_CODE = FCATB.BOOK_TYPE_CODE
AND FB.DATE_INEFFECTIVE IS NULL
AND FDS.ASSET_ID(+) = FA.ASSET_ID
AND FDS.BOOK_TYPE_CODE(+) = FB.BOOK_TYPE_CODE
AND FDS.PERIOD_COUNTER(+) = FDP_C.PERIOD_COUNTER
--AND FDS_P.ASSET_ID = FA.ASSET_ID
--AND FDS_P.BOOK_TYPE_CODE = FB.BOOK_TYPE_CODE
--AND FDS_P.PERIOD_COUNTER = FDP_P.PERIOD_COUNTER
AND FDH.TRANSACTION_HEADER_ID_OUT IS NULL
AND FDH.ASSET_ID = FA.ASSET_ID
AND FDH.BOOK_TYPE_CODE = FB.BOOK_TYPE_CODE
AND FBC.BOOK_TYPE_CODE = FB.BOOK_TYPE_CODE
AND FBC.SET_OF_BOOKS_ID = :P_LEDGER_ID
--AND NVL(FDH.ASSIGNED_TO,-1) = NVL(:P_EMPLOYEE_ID,NVL(FDH.ASSIGNED_TO,-1))
and (NVL(FDH.ASSIGNED_TO,-1) IN (:P_EMPLOYEE_ID) OR LEAST(:P_EMPLOYEE_ID) IS NULL)
and (FCB.SEGMENT1 IN (:P_MAJOR_CATEGORY_NAME) OR LEAST(:P_MAJOR_CATEGORY_NAME) IS NULL)
and (FCB.SEGMENT2 IN (:P_MINOR_CATEGORY_NAME) OR LEAST(:P_MINOR_CATEGORY_NAME) IS NULL)
AND NVL(FDH.LOCATION_ID,-1) BETWEEN NVL(:P_FROM_LOCATION_ID, NVL(FDH.LOCATION_ID,-1)) AND NVL(:P_TO_LOCATION_ID, NVL(FDH.LOCATION_ID,-1))
AND FA.ASSET_NUMBER BETWEEN NVL(:P_FROM_ASSET_NO, FA.ASSET_NUMBER) AND NVL(:P_TO_ASSET_NO, NVL(:P_FROM_ASSET_NO, FA.ASSET_NUMBER))
AND FB.BOOK_TYPE_CODE = NVL(:P_BOOK_TYPE_CODE, '-1')
AND EXISTS (select 'x' from FA_CATEGORY_BOOKS where book_type_code = :p_book_type_code and category_id = fcb.category_id)

Receipt Accounting SQL in Fusion Receivables and Transaction Accounting SQL in Fusion Receivables

SELECT
 xte.entity_code, 
xah.ae_header_id,
    xte.source_id_int_1,
    xal.ae_line_num,
    xal.displayed_line_number,
    xal.description,
    xal.party_id,
    xal.party_site_id,
    xal.party_type_code,
    xal.accounting_class_code,
    (select meaning from FND_LOOKUP_VALUES_TL
     where lookup_type='XLA_ACCOUNTING_CLASS'
     and lookup_code = xal.accounting_class_code
     and language='US'
     and source_lang='US') ACCOUNTING_CLASS_NAME,
    xal.code_combination_id,
    xal.accounted_dr,
    xal.accounted_cr,
    --gcc.code_combination_id AS code_combination_id,
    gcc.segment1
    || '.'
    || gcc.segment2
    || '.'
    || gcc.segment3
    || '.'
    || gcc.segment4
    || '.'
    || gcc.segment5
    || '.'
    || gcc.segment6
    || '.'
    || gcc.segment7 AS account_segments,
    gled.chart_of_accounts_id,
gl_flexfields_pkg.get_concat_description(gled.chart_of_accounts_id,gcc.code_combination_id) account_description,
gled.ledger_id
FROM
xla_transaction_entities xte,
xla_ae_headers xah,
xla_ae_lines xal,
gl_code_combinations gcc,
gl_ledgers gled
WHERE 1=1
    AND xah.entity_id = xte.entity_id
    AND xal.ae_header_id = xah.ae_header_id
    AND xal.application_id = 222
    AND xah.application_id = 222
    AND xte.application_id = 222
    AND xte.ledger_id = gled.ledger_id
    AND xte.ledger_id = xah.ledger_id
    AND xah.ledger_id = xal.ledger_id
    AND gcc.code_combination_id = xal.code_combination_id
    and xte.entity_code ='RECEIPTS'
and gled.ledger_id= :LEDGER_ID
and  xte.source_id_int_1 = :m_cash_receipt_id
order by  xal.accounted_dr,     xal.displayed_line_number

###################################################
Receivables Invoice or Transaction Accounting SQL
###################################################

SELECT
 xte.entity_code, 
xah.ae_header_id,
    xte.source_id_int_1,
    xal.ae_line_num,
    xal.displayed_line_number,
    xal.description,
    xal.party_id,
    xal.party_site_id,
    xal.party_type_code,
    xal.accounting_class_code,
    (select meaning from FND_LOOKUP_VALUES_TL
     where lookup_type='XLA_ACCOUNTING_CLASS'
     and lookup_code = xal.accounting_class_code
     and language='US'
     and source_lang='US') ACCOUNTING_CLASS_NAME,
    xal.code_combination_id,
    xal.accounted_dr,
    xal.accounted_cr,
    --gcc.code_combination_id AS code_combination_id,
    gcc.segment1
    || '.'
    || gcc.segment2
    || '.'
    || gcc.segment3
    || '.'
    || gcc.segment4
    || '.'
    || gcc.segment5
    || '.'
    || gcc.segment6
    || '.'
    || gcc.segment7 AS account_segments,
    gled.chart_of_accounts_id,
gl_flexfields_pkg.get_concat_description(gled.chart_of_accounts_id,gcc.code_combination_id) account_description,
gled.ledger_id
FROM
xla_transaction_entities xte,
xla_ae_headers xah,
xla_ae_lines xal,
gl_code_combinations gcc,
gl_ledgers gled
WHERE 1=1
    AND xah.entity_id = xte.entity_id
    AND xal.ae_header_id = xah.ae_header_id
    AND xal.application_id = 222
    AND xah.application_id = 222
    AND xte.application_id = 222
    AND xte.ledger_id = gled.ledger_id
    AND xte.ledger_id = xah.ledger_id
    AND xah.ledger_id = xal.ledger_id
    AND gcc.code_combination_id = xal.code_combination_id
    and xte.entity_code ='TRANSACTIONS'
and gled.ledger_id= :LEDGER_ID
and  xte.source_id_int_1 = :RA_CUSTOMER_TRX_ALL.CUSTOMER_TRX_ID
order by  xal.accounted_dr,     xal.displayed_line_number

##########################
select primary_ledger_id from fun_fin_business_units_v where bu_name like 'XX%'

Payment Accounting SQL: Oracle Fusion R13

Payment Accounting SQL

SELECT
    xah.ae_header_id,
    xte.source_id_int_1,
    xal.ae_line_num,
    xal.displayed_line_number,
    xal.description,
    xal.party_id,
    xal.party_site_id,
    xal.party_type_code,
    xal.accounting_class_code,
    (select meaning from FND_LOOKUP_VALUES_TL
     where lookup_type='XLA_ACCOUNTING_CLASS'
     and lookup_code = xal.accounting_class_code
     and language='US'
     and source_lang='US') ACCOUNTING_CLASS_NAME,
    xal.code_combination_id,
    xal.accounted_dr,
    xal.accounted_cr,
    --gcc.code_combination_id AS code_combination_id,
    gcc.segment1
    || '.'
    || gcc.segment2
    || '.'
    || gcc.segment3
    || '.'
    || gcc.segment4
    || '.'
    || gcc.segment5
    || '.'
    || gcc.segment6
    || '.'
    || gcc.segment7 AS account_segments,
    gled.chart_of_accounts_id,
gl_flexfields_pkg.get_concat_description(gled.chart_of_accounts_id,gcc.code_combination_id) account_description,
gled.ledger_id
FROM
xla_transaction_entities xte,
xla_ae_headers xah,
xla_ae_lines xal,
gl_code_combinations gcc,
gl_ledgers gled
WHERE 1=1
    AND xah.entity_id = xte.entity_id
    AND xal.ae_header_id = xah.ae_header_id
    AND xal.application_id = 200
    AND xah.application_id = 200
    AND xte.application_id = 200
    AND xte.ledger_id = gled.ledger_id
    AND xte.ledger_id = xah.ledger_id
    AND xah.ledger_id = xal.ledger_id
    AND gcc.code_combination_id = xal.code_combination_id
    and xte.entity_code ='AP_PAYMENTS'
order by  xal.accounted_dr,     xal.displayed_line_number