Understand Loops in BI Publisher: for-each vs for-each-group vs for-each: current-group

xml grouping

SUPPLIERS

INVOICES

Required:

SUPPLIERS

CURRENCY

INVOICES

‘for-each-group’ to regroup to show the invoices by currenc, [<?for-each-group:G_INVOICE_NUM;INVOICE_CURRENCY_CODE?>]

• This is specifying that we want to create a new group ‘INVOICE_CURRENCY_CODE’ based on the original ‘G_INVOICE_NUM’ group.
• We now have ‘for-each:current-group()’  to refer to the new INVOICE_CURRENCY_CODE group we have just created.
• Note: we cannot refer to the group as INVOICE_CURRENCY_CODE as this group is only created at runtime.
• sum (current-group()/ENT_AMT) for the entered amount, again we use the current-group() tag to refer to the new ‘INVOICE_CURRENCY_CODE’ group.

I

<?for-each:G_VENDOR_NAME?>

<?for-each-group:G_INVOICE_NUM;INVOICE_CURRENCY_CODE?> Currency: USD Invoice Number Type Invoice Date GL Date Entered Amount Accounted Amount <?for-each:current-group()?> <?INVOICE_NUM?> <?INV_TYPE?> <?INVOICE_DATE?> <?GL_DATE?> <?ENT_AMT?> <?ACCTD_AMT?> <?end for-each?> Total for <?INVOICE_CURRENCY_CODE?> <? sum(current-group()/ENT_AMT)?> <?format-number(sum(current-group()/ACCTD_AMT),"#,###.00","Euro")?> <?end for-each-group?>

<?split-by-page-break:?>

<?end for-each?>

-------------
Output
-------------

Currency: EUR Invoice Number Type Invoice Date GL Date Entered Amount Accounted Amount 981110 Standard 10-NOV-07 10-NOV-07 122 122 Total for EUR 122 122.00 Currency: FIM Invoice Number Type Invoice Date GL Date Entered Amount Accounted Amount 100000 Standard 28-MAY-07 28-MAY-07 122 20.33 100001 Standard 28-MAY-07 28-MAY-07 200 33.33 1 Standard 03-JUN-07 03-JUN-07 400 66.67 100004 Standard 28-MAY-07 28-MAY-07 100 16.67 00s Standard 07-JUN-07 07-JUN-07 100 16.67 FI1009 Standard 10-MAY-07 10-MAY-07 1220 203.33 Total for FIM 2142 357.00

bi publisher tips and tricks

<?xml version = '1.0' encoding = 'utf-8'?>
<data>
<RequisitioningBU>bu name</RequisitioningBU>
</data>


-> working code for boilerplate (xdofx with upper), does not work in non-boilerplate type

<?xdofx:if upper(RequisitioningBU) ='BU NAME' then 'A' else if upper(RequisitioningBU) ='BU2 NAME' then 'B' else  'ORACLE' end if?>


-> working code for tags (xdofx with upper)

<?choose:?>
<?when:substring(RequisitioningBU,1,7)= "BU NAME"?> stuff <?end when?>
<?when:substring(RequisitioningBU,1,8)= "BU2 NAME"?> stuff <?end when?>
<?end choose?>


Adding IF condition in rtf with OR

<?if: not(substring(ReportNumber,1,2) = "PG" or substring(ReportNumber,1,2)= "NA") ?>


Define a xsl variable [xsl definition works under bi form elements only]

define:

<xsl:variable name="no_of_lines_per_page" select="number(26)"/>

print variables

<?$no_of_lines_per_page?>

Access tags a a particular level: relative retrival

./ mean at same level there should be a tag Payee, and under that Name tag should be present

<?./Payee/Name?>


Access tags a a particular level: absolute retrival

You can get absolute value using below logic

<?/OutboundPaymentInstruction/OutboundPayment/Beneficiary/Name?>




Test a condition with absolute path data 

Data:

<?xml version = '1.0' encoding = 'utf-8'?>

<A>

               <B ID="12345">

                              <C>data</C>

               </B>

</A>

Below syntax will 

1. create a variable v1

2. select value of A/B/@ID into v1

3. checks if value of v1 12345, if true prints SUCCESS

4. to use it within rtf, we need to create a rtf element [ex. xsl_var_if_test], and insert below syntax into that. 

<xsl:variable name="v1" select="A/B/@ID"/>

<xsl:if test="$v1 = '12345'">SUCCESS</xsl:if>

Combining XSL with for-each
May be you need to extract values from levels under a repeating element, and print them

Data:

<?xml version="1.0" encoding="utf-8"?>
<A>
  <B ID="12345">
    <C>data</C>
  </B>
  <B ID="123451">
    <C>data-1</C>
  </B>
  <B ID="123452">
    <C>data-2</C>
  </B>
  <B ID="123453">
    <C>data-3</C>
  </B>
  <B ID="123454">
    <C>data-4</C>
  </B>
  <B ID="123455">
    <C>data-5</C>
  </B>
</A>

Below syntax will 

1. create a variables v<>

2. select value of ./@ID into v<>

3. checks if value of v1 12345, if true prints SUCCESS

write in rtf like below:

<?for-each: B?>

xsl_var_if_test2

<?end for-each?>

where xsl_var_if_test2 is a element with boilerplate text as below

<xsl:variable name="v1" select="./@ID"/><xsl:if test="$v1 = '12345'">SUCCESS-0</xsl:if>
<xsl:variable name="v2" select="./@ID"/><xsl:if test="$v2 = '123451'">SUCCESS-1</xsl:if>
<xsl:variable name="v3" select="./@ID"/><xsl:if test="$v3 = '123452'">SUCCESS-2</xsl:if>
<xsl:variable name="v4" select="./@ID"/><xsl:if test="$v4 = '123453'">SUCCESS-3</xsl:if>
<xsl:variable name="v5" select="./@ID"/><xsl:if test="$v5 = '123454'">SUCCESS-4</xsl:if>

prints:

SUCCESS-1
SUCCESS-2
SUCCESS-3
SUCCESS-4

Subtraction in rtf:

<?(ACCTED_DR)-xdoxslt:sum(ACTUAL_DR)?>

---------------------------

ELSE IF

<?xdofx:if COMM!='' then SAL+COMM else SAL end if?>

DECODE TAG:
<?xdofx:decode(COMM,’’,’sry’,COMM)?>
<?xdofx:decode(COMM,’’,ENAME,COMM||’, ’||ENAME)?>

CHOOSE WHEN
<?choose:?> <?when: x == y?> ... <?end when?> <?otherwise:?> ... <?end otherwise?> <?end choose?>

How to get Active bank accounts for a customer, Supplier

-----
Case: You have account number

hz_cust_accounts  (pass account_number = '<1234>') (get CUST_ACCOUNT_ID, PARTY_ID)
HZ_CUST_ACCT_SITES_ALL  (cust_account_id=hz_cust_accounts.cust_account_id) (get cust_account_site_id)
HZ_CUST_SITE_USES_ALL   (pass CUST_ACCT_SITE_ID) (get SITE_USE_ID -> which are active)

IBY_EXTERNAL_PAYERS_ALL (pass party_id= hz_cust_accounts.party_id, ACCT_SITE_USE_ID-> join with HZ_CUST_SITE_USES_ALL.SITE_USE_ID) (get EXT_PAYER_ID)
IBY_PMT_INSTR_USES_ALL  (pass EXT_PMT_PARTY_ID  = IBY_EXTERNAL_PAYERS_ALL.EXT_PAYER_ID)
IBY_EXT_BANK_ACCOUNTS   (get EXT_BANK_ACCOUNT_ID)
---
HZ_ACCOUNT_OWNERS (pass account_owner_party_id= hz_cust_accounts.party_id) (get EXT_BANK_ACCOUNT_ID)


SELECT ca.account_number      "Customer Account Number"
      , null                  "Site address"
      , piu.start_date        "Bank Account Use Start Date"
      , piu.end_date          "Bank Account Use End Date"
      , eba.bank_account_num  "Bank Account Number"
      , eba.bank_account_name "Bank Account Name"
      , eba.start_date        "Bank Account Start Date"
      , eba.end_date          "Bank Account End Date"
  FROM hz_cust_accounts        ca
     , iby_external_payers_all epa
     , iby_pmt_instr_uses_all  piu
     , iby_ext_bank_accounts   eba
 WHERE ca.cust_account_id      = epa.cust_account_id
   AND piu.ext_pmt_party_id    = epa.ext_payer_id
   AND piu.payment_function    = 'CUSTOMER_PAYMENT'
   AND eba.ext_bank_account_id = piu.instrument_id
   AND ca.account_number       = 'SKM-PERSON-01'
   AND epa.acct_site_use_id    is null



-----------------

(
                SELECT DISTINCT
                    bank.party_name bank_name,
                    branch.party_name branch_name,
                    account.bank_account_num bank_account_num,
                    account.iban iban,
                    account.eft_swift_code swift_code,
                    acc_owner.account_owner_party_id account_owner_party_id,
                    account.ext_bank_account_id
                FROM
                    hz_parties bank,
                    hz_relationships rel,
                    hz_parties branch,
                    iby_ext_bank_accounts account,
                    iby_account_owners acc_owner
                WHERE
                    1 = 1
                    AND bank.party_id = rel.object_id
                    AND bank.party_type = rel.object_type
                    AND rel.object_table_name = 'HZ_PARTIES'
                    AND rel.relationship_code = 'BRANCH_OF'
                    AND rel.subject_id = branch.party_id
                    AND rel.subject_type = branch.party_type
                    AND rel.subject_table_name = 'HZ_PARTIES'
                    AND bank.party_id = account.bank_id
                    AND branch.party_id = account.branch_id (+)
                    AND account.ext_bank_account_id = acc_owner.ext_bank_account_id (+)
                    AND SYSDATE BETWEEN nvl(rel.start_date,SYSDATE - 1) AND nvl(rel.end_date,SYSDATE + 1)
                    AND bank.status = 'A'
                    AND branch.status = 'A'
            ) supplier_bank

Fusion Financials : How to link GL data to the subledger data

How to link GL data to the subledger data

---------------

1. ap_invoices_all (pass invoice num) (get invoice_id)
2. ap_invoice_payments_all (pass invoice_id) (get_check_id)
3. ap_checks_all (pass check_id) (get payment_id)

-> iby_payments_all (pass payment_id) ( get  other vendor, bank info)

1. xla_entity_id_mappings (pass entity_code='AP_PAYMENTS') get (TRANSACTION_ID_COL_NAME_1 -> CHECK_ID, SOURCE_ID_COL_NAME_1 -> SOURCE_ID_INT_1)
2. xla_transaction_entities  (pass SOURCE_ID_INT_1 = <check_id>) (get Entity_id, applicaiton_id)
3. xla_events (pass entity_id) (get event_id)
4. xla_ae_headers (pass event_id) (get ae_header_id)
5. xla_ae_lines (pass ae_header_id) (get gl_sl_link_id, gl_sl_link_table)
6. gl_import_references (pass gl_sl_link_id, gl_sl_link_table) (get je_header_id)

--------

• gl_je_lines (je_header_id, je_line_num)                -> gl_import_references (je_header_id, je_line_num)
• gl_import_references (gl_sl_link_table, gl_sl_link_id) -> xla_ae_lines (gl_sl_link_table, gl_sl_link_id)
• xla_ae_lines (applicaiton_id, ae_header_id)            -> xla_ae_headers (application_id, ae_header_id)
• xla_ae_headers (application_id, event_id)              -> xla_events (application_id, event_id)
• xla_events (application_id, entity_id)                 -> xla.xla_transaction_entities (application_id, entity_id)

• xla.xla_transaction_entities (source_id_int_1, etc) after filtering by application_id, entity_code and ledger_id     -> subledger's table(its key columns mentioned in xla_entity_id_mappings) for that ledger_id
• For Ex:
• xla.xla_transaction_entities (source_id_int_1) filtered by application_id 200, entity_code AP_INVOICES and ledger_id -> ap_invoices_all (invoice_id) for that set_of_books_id.
• xla.xla_transaction_entities (source_id_int_1) filtered by application_id 200, entity_code AP_PAYMENTS and ledger_id -> ap_checks_all (check_id) for that set_of_books_id.
• xla.xla_transaction_entities (source_id_int_1) filtered by application_id 222, entity_code TRANSACTIONS and ledger_id -> ra_customer_trx_all (customer_trx_id) for that set_of_books_id.

-----
helpful sql joining ap to gl links

select
'X'
     --  aca.check_number,
     --  aipa.invoice_payment_id,
     --  aca.payment_id,
     --  aipa.invoice_id,
     --  aipa.payment_num,
     --  aia.invoice_num,
     --  aia.gl_date,
     --  aia.invoice_amount,
     --  xal.code_combination_id xla_ccid,
     --  xal.ae_header_id,
     --  xal.ae_line_num,
     --  ipa.document_sequence_value
FROM
  ap_invoices_all aia
, ap_checks_all aca
, ap_invoice_payments_all aipa
, iby_payments_all ipa
, xla_transaction_entities xte
, xla_events xe
, xla_ae_headers xah
, xla_ae_lines xal
where 1=1
and aia.invoice_id = aipa.invoice_id
and aipa.check_id  = aca.check_id
and ipa.payment_id = aca.payment_id
and xte.entity_code ='AP_PAYMENTS'
and xte.source_id_int_1 = aca.check_id
and xte.SECURITY_ID_INT_1 = :p_business_unit
and xte.ledger_id = :p_ledger_id
and xte.source_application_id =200
and xte.entity_id = xe.entity_id
and xe.event_id = xah.event_id
and xal.ae_header_id = xah.ae_header_id
and xal.gl_sl_link_table = 'XLAJEL'
and xal.gl_sl_link_id = GIR.GL_SL_LINK_ID
--and aca.check_number='6'

AP(Payables) invoice related important table

 AP(Payables) invoice related important table

Invoice

AP_BATCHES_ALL

SELECT
  *
FROM
  AP_BATCHES_ALL
WHERE
  batch_id IN
  (
    SELECT
      batch_id
    FROM
      AP_INVOICES_ALL
    WHERE
      invoice_id = :P_INVOICE_ID
  );

AP_INVOICES_ALL

SELECT DISTINCT
  ai.*,
  fnd_flex_ext.get_segs('SQLGL','GL#', '101' , ai.accts_pay_code_combination_id
  ) "Accts Pay Account"
FROM
  AP_INVOICES_ALL ai
WHERE
  invoice_id = :P_INVOICE_ID  
ORDER BY
  ai.invoice_id ASC;

AP_INVOICE_LINES_ALL
          
SELECT
  *
FROM
  AP_INVOICE_LINES_ALL
WHERE
  invoice_id = :P_INVOICE_ID;


AP_INVOICE_DISTRIBUTIONS_ALL

SELECT
  aid.*,
  fnd_flex_ext.get_segs('SQLGL','GL#', '101' ,
  aid.accts_pay_code_combination_id) "Accts Pay Account",
  fnd_flex_ext.get_segs('SQLGL','GL#', '101' , aid.dist_code_combination_id)
  "Dist Account"
FROM
  AP_INVOICE_DISTRIBUTIONS_ALL aid
WHERE
  aid.invoice_id = :P_INVOICE_ID
ORDER BY
  aid.invoice_id,
  aid.invoice_line_number,
  aid.distribution_line_number ASC;

ZX_LINES 
         
SELECT
  *
FROM
  ZX_LINES
WHERE
  application_id      = 200
AND entity_code       = 'AP_INVOICES'
AND trx_id            = :P_INVOICE_ID
AND event_class_code IN ('STANDARD INVOICES' , 'PREPAYMENT INVOICES',
  'EXPENSE REPORTS');

zx_lines_summary 
         
SELECT
  *
FROM
  zx_lines_summary
WHERE
  application_id      = 200
AND entity_code       = 'AP_INVOICES'
AND trx_id            = :P_INVOICE_ID
AND event_class_code IN ('STANDARD INVOICES' , 'PREPAYMENT INVOICES',
  'EXPENSE REPORTS');


zx_rec_nrec_dist
          
SELECT
  *
FROM
  zx_rec_nrec_dist
WHERE
  application_id      = 200
AND entity_code       = 'AP_INVOICES'
AND trx_id            = :P_INVOICE_ID
AND event_class_code IN ('STANDARD INVOICES' , 'PREPAYMENT INVOICES',
  'EXPENSE REPORTS');

zx_lines_det_factors  
        
SELECT
  *
FROM
  zx_lines_det_factors
WHERE
  application_id      = 200
AND entity_code       = 'AP_INVOICES'
AND trx_id            = :P_INVOICE_ID
AND event_class_code IN ('STANDARD INVOICES' , 'PREPAYMENT INVOICES',
  'EXPENSE REPORTS');


AP_PAYMENT_SCHEDULES_ALL 
         
SELECT
  *
FROM
  AP_PAYMENT_SCHEDULES_ALL
WHERE
  invoice_id = :P_INVOICE_ID;

ap_recurring_payments_all
 
SELECT DISTINCT
  arp.*
FROM
  ap_recurring_payments_all arp,
  ap_invoices_all ai
WHERE
  ai.recurring_payment_id = arp.recurring_payment_id
AND ai.invoice_id         = :P_INVOICE_ID;


AP_HOLDS_ALL 
         
SELECT
  *
FROM
  AP_HOLDS_ALL
WHERE
  invoice_id = :P_INVOICE_ID;

AP_HOLD_CODES
          
SELECT
  *
FROM
  AP_HOLD_CODES
WHERE
  hold_lookup_code IN
  (
    SELECT
      hold_lookup_code
    FROM
      AP_HOLDS_ALL
    WHERE
      invoice_id = :P_INVOICE_ID
  );


AP_TERMS
          
SELECT
  *
FROM
  AP_TERMS
WHERE
  term_id IN
  (
    SELECT DISTINCT
      terms_id
    FROM
      AP_INVOICES_ALL
    WHERE
      invoice_id = :P_INVOICE_ID
  );



AP_TERMS_LINES
          
SELECT
  *
FROM
  AP_TERMS_LINES
WHERE
  term_id IN
  (
    SELECT DISTINCT
      terms_id
    FROM
      AP_INVOICES_ALL
    WHERE
      invoice_id = :P_INVOICE_ID
  );


AP_INVOICE_PREPAYS_ALL 
         
SELECT
  *
FROM
  AP_INVOICE_PREPAYS_ALL
WHERE
  invoice_id = :P_INVOICE_ID;


AP_CHRG_ALLOCATIONS_ALL 

SELECT DISTINCT
  aca.*
FROM
  AP_CHRG_ALLOCATIONS_ALL aca,
  AP_INVOICE_DISTRIBUTIONS_ALL aid
WHERE
  (
    aca.charge_dist_id = aid.invoice_distribution_id
  OR aca.item_dist_id  = aid.invoice_distribution_id
  )
AND aid.invoice_id = :P_INVOICE_ID
ORDER BY
  aca.charge_dist_id;

AP_INV_APRVL_HIST_ALL
          
SELECT
  *
FROM
  AP_INV_APRVL_HIST_ALL
WHERE
  invoice_id = :P_INVOICE_ID
ORDER BY
  1;


AP_ALLOCATION_RULES 
         
SELECT
  *
FROM
  AP_ALLOCATION_RULES
WHERE
  invoice_id = :P_INVOICE_ID;

AP_ALLOCATION_RULE_LINES 
         
SELECT
  *
FROM
  AP_ALLOCATION_RULE_LINES
WHERE
  invoice_id = :P_INVOICE_ID;

AP_SELF_ASSESSED_TAX_DIST_ALL
          
SELECT
  *
FROM
  AP_SELF_ASSESSED_TAX_DIST_ALL
WHERE
  invoice_id = :P_INVOICE_ID;


AP_INVOICE_RELATIONSHIPS
           
SELECT
  *
FROM
  AP_INVOICE_RELATIONSHIPS
WHERE
  original_invoice_id = :P_INVOICE_ID
OR related_invoice_id = :P_INVOICE_ID;

Oracle AP Tax Joins

 AP Table Relation Oracle Apps

ORACLE PAYABLE TABLE RELATION
Source Table	Dependent Table	Condition
AP_INVOICE_LINES_ALL AIL	ZX_LINES_SUMMARY ZLS	AIL.invoice_id=ZLS.trx_id and ZLS.application_id = 200 and ZLS.entity_code = 'AP_INVOICES' and ZLS.event_class_code in ('STANDARD INVOICES', 'PREPAYMENT INVOICES', 'EXPENSE REPORTS') and AIL.summary_tax_line_id =ZLS.summary_tax_line_id
AP_INVOICE_LINES_ALL AIL	ZX_LINES ZL	AIL.invoice_id=ZL.trx_id and ZL.application_id = 200 and ZL.entity_code = 'AP_INVOICES' and ZL.event_class_code in ('STANDARD INVOICES', 'PREPAYMENT INVOICES', 'EXPENSE REPORTS') and AIL.line_number=ZL.trx_line_number
AP_INVOICE_DISTRIBUTIONS_ALL AID	ZX_REC_NREC_DIST ZD	AID.invoice_id=ZD.trx_id and ZD.application_id = 200 and ZD.entity_code = 'AP_INVOICES' and ZD.event_class_code in ('STANDARD INVOICES', 'PREPAYMENT INVOICES', 'EXPENSE REPORTS') and AID.summary_tax_line_id = ZD.summary_tax_line_id and AID.detail_tax_dist_id = ZD.rec_nrec_tax_dist_id
AP_SELF_ASSESSED_TAX_DIST_ALL ASTD	ZX_REC_NREC_DIST ZD	ASTD.invoice_id=ZD.trx_id and ZD.application_id = 200 and ZD.entity_code = 'AP_INVOICES' and ZD.event_class_code in ('STANDARD INVOICES', 'PREPAYMENT INVOICES', 'EXPENSE REPORTS') and ASTD.summary_tax_line_id = ZD.summary_tax_line_id and ASTD.detail_tax_dist_id =ZD.rec_nrec_tax_dist_id and ZD.self_assessed_flag='Y'
AP_INVOICE_LINES_ALL AIL	ZX_LINES_DET_FACTORS ZLDF	AIL.invoice_id=ZLDF.trx_id and ZLDF.application_id = 200 and ZLDF.entity_code = 'AP_INVOICES' and ZLDF.event_class_code in ('STANDARD INVOICES', 'PREPAYMENT INVOICES', 'EXPENSE REPORTS') and AIL.line_number=ZLDF.trx_line_number
AP_INVOICE_DISTRIBUTIONS_ALL or AP_SELF_ASSESSED_TAX_DIST_ALL AID	XLA_EVENTS XE	Actual Event: -------------- xe.event_id = aid.accounting_event_id and xe.application_id = 200 Budgetary Event: ----------------- xe.event_id = aid.bc_event_id and xe.application_id = 200
AP_INVOICE_DISTRIBUTIONS_ALL or AP_SELF_ASSESSED_TAX_DIST_ALL AID	RCV_TRANSACTIONS RT	aid.rcv_transaction_id = rt.transaction_id
AP_INVOICE_DISTRIBUTIONS_ALL or AP_SELF_ASSESSED_TAX_DIST_ALL AID	GL_CODE_COMBINATIONS GCC	aid.dist_code_combination_id =gcc.code_combination_id
AP_PAYMENT_HISTORY_ALL APH	XLA_EVENTS XE	aph.accounting_event_id = xe.event_id    and xe.application_id = 200
AP_INVOICE_PAYMENTS_ALL AIP	XLA_EVENTS XE	aip.accounting_event_id = xe.event_id and xe.application_id = 200
AP_INVOICE_DISTRIBUTIONS_ALL or AP_SELF_ASSESSED_TAX_DIST_ALL AID	PO_DISTRIBUTIONS_ALL	PD aid.po_distribution_id = pd.po_distribution_id
AP_INVOICES_ALL AI	PO_HEADERS_ALL PH	ai.po_header_id = ph.po_header_id
AP_SUPPLIER_SITES_ALL ASS	HZ_PARTY_SITES HPS	ass.party_site_id = hps.party_site_id
AP_SUPPLIERS AS	HZ_PARTIES HP	as.party_id = hp.party_id
AP_PRERPAY_APP_DISTS or AP_PAYMENT_HIST_DISTS APA	XLA_EVENTS XE	apa.accounting_event_id = xe.event_id xe.application_id = 200
AP_PREPAY_HISTORY_ALL APH	XLA_EVENTS XE	aph.accounting_event_id = xe.event_id and xe.application_id = 200
AP_INVOICES_ALL AI	XLA.XLA_TRANSACTION_ENTITIES XTE	ai.invoice_id = nvl(xte.source_id_int_1, -99) and xte.entity_code = 'AP_INVOICES' and xte.application_id = 200
AP_CHECKS_ALL AC	XLA.XLA_TRANSACTION_ENTITIES XTE	ac.check_id = nvl(xte.source_id_int_1, -99) and xte.entity_code = 'AP_PAYMENTS' and xte.application_id =200