Fusion AP invoice ERROR Report (AP_INTERFACE_REJECTIONS )

------header---

 select air.Reject_lookup_code, rejection_message,aii.*

from 

AP_INTERFACE_REJECTIONS  air,

AP_INVOICES_INTERFACE  aii

where air.PARENT_ID  = aii.INVOICE_ID 

and air.PARENT_TABLE = 'AP_INVOICES_INTERFACE'

and air.created_by =fnd_global.user_name 

and air.LOAD_REQUEST_ID IN (:P_LOAD_REQUEST_ID)

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

----Lines 

select air.Reject_lookup_code, rejection_message,aii.*

from 

AP_INTERFACE_REJECTIONS  air,

AP_INVOICE_LINES_INTERFACE  aii

where air.PARENT_ID  = aii.INVOICE_LINE_ID 

and air.PARENT_TABLE = 'AP_INVOICE_LINES_INTERFACE'

and air.created_by =fnd_global.user_name 

and air.LOAD_REQUEST_ID IN (:P_LOAD_REQUEST_ID)

Fusion Internal Bank Account Query (ce_bank_accounts)

SELECT

    hou.name                "OPERATING UNIT",

    cbbv.bank_name,

    cbbv.bank_branch_name,

    cba.bank_account_name,

    hp.party_name           "LEGAL ENTITY",

    cbau.ar_use_enable_flag "RECEIVABLES_ACCOUNT_USE",

    cbau.ap_use_enable_flag "PAYABLES_ACCOUNT_USE",

    cba.bank_account_num    "ACCOUNT_NUMBER",

    cba.bank_account_type   "ACCOUNT TYPE",

    cba.iban_number,

    cba.currency_code,

    cba.multi_currency_allowed_flag,

    cba.description

FROM

    ce_bank_accounts      cba,

    ce_bank_acct_uses_all cbau,

    ce_gl_accounts_ccid   cgac,

    ce_bank_branches_v    cbbv,

    hr_operating_units    hou,

    hz_parties            hp

WHERE

        cbbv.bank_party_id = cba.bank_id

    AND cbbv.branch_party_id = cba.bank_branch_id

    AND cba.bank_account_id = cbau.bank_account_id

    AND cgac.bank_acct_use_id = cbau.bank_acct_use_id

    AND cbau.org_id = hou.organization_id

    AND hp.party_id = cba.account_owner_party_id

    AND cba.bank_account_num IN ( :p_bank_account_num )   ----Parameters

https://docs.oracle.com/en/cloud/saas/financials/24b/oedmf/cebankaccounts-4387.html

RMA Number From Inv Material transaction

select dha.ORDER_NUMBER,imt.transaction_id, (select receipt_num from rcv_shipment_headers rsh1, rcv_transactions rc where rsh1.SHIPMENT_HEADER_ID=rc.SHIPMENT_HEADER_ID and rc.TRANSACTION_ID=imt.RCV_TRANSACTION_ID)receipt_num from inv_material_txns imt, rcv_shipment_headers rsh, doo_headers_All dha where imt.transaction_id=448369 and imt.TRANSACTION_SOURCE_ID=rsh.SHIPMENT_HEADER_ID and rsh.RECEIPT_ADVICE_NUMBER=dha.HEADER_ID

Page Number In RTF Template Reset To 1 For Each Group

Issue:- “Page Number will be a part of footer, however, if the data is taken for ALL invoices at once, page number would not start with 1 again for the new invoice as all invoices would be a part of the same document”.

Expectation:- The expectation is to have the page numbering and the header styling reset for each invoice.

We have a requirement where we have to print All Invoices in one Document and Show page number in the footer but page number should reset at each invoice level.

Suppose we have one document where three invoices are there  suppose 101A invoice,102B Invoice, and 103C Invoice now when we generating a report its Showing 3 pages as 1 2 3 in the footer , But the expectation is page number will reset and  show 1 at each Invoice starting.

Solution Use this tag for starting of Your Group  <?for-each@section:G_INVOICE?>

Default Text Entry	Form Field Help Text	Description
for-each G_INVOICE	<?for-each@section:G_INVOICE?>	Begins the G_INVOICE group, and defines the element as a Section. For each occurrence of G_INVOICE, a new section is started.
<?TRX_NUMBER?>	N/A	Microsoft Word does not support form fields in the header, therefore the placeholder syntax for the TRX_NUMBER element is placed directly in the template.  Page number also repeat at invoice level
end G_INVOICE	<?end for-each?>	Closes the G_INVOICE group.

Url For Reference

https://docs.oracle.com/middleware/12212/bip/BIPRD/GUID-B9C4322A-4BBF-4D28-B8F3-435E527EE5E6.htm#BIPRD2541

xla_transaction_entities Not able to see all sources data for Some users

How To See ALL Sources Data

 xla_transaction_entities: This table Comes with Some Data security policy applied to it that the reason we are not able to see all data.In Ebiz we use xla.xla_transaction_entities  table to see all data.

Now in Cloud, we need to provide data security Policy In the data model by using the Below PLSQL block.


DECLARE
type refcursor is REF CURSOR;
xdo_cursor refcursor;

 l_roleguids FND_TABLE_OF_VARCHAR2_4000 := FND_TABLE_OF_VARCHAR2_4000();
 l_rolenames FND_TABLE_OF_VARCHAR2_4000 := FND_TABLE_OF_VARCHAR2_4000();

BEGIN

--MO_GLOBAL.Init('AP_MANAGE_PAYABLES_INVOICE_DATA');
---MO_GLOBAL.Init('AR_VIEW_RECEIVABLES_ACTIVITIES_DATA');


  l_roleguids.extend(1);
  l_rolenames.extend(1);
  l_roleguids(1) := 'A38EFCFAA623F4C538B7F93159ED3614';
  l_rolenames(1) := 'GL_GENERAL_ACCOUNTANT';

--- fnd_global.initialize_session('A38EFCFAA623F4C538B7F93159ED3614','GL_GENERAL_ACCOUNTANT', l_roleguids, l_rolenames);

  xla_security_pkg.set_security_context(602);       -----To see XLA all Sources Data 
  
  
OPEN :xdo_cursor FOR
SELECT SYSDATE RUN_DATE
from dual;


END;




Just add this to your data model to see all kinds of data.

Thank you.

How to pass all values in parameters and show same in Parameter section

pass where condition as below 

select trx_number,trx_datefrom   ra_customer_trx_allwhere (org_id in (:p_org_id ) or least(:p_org_id) is null )

now select can select all and then select null value passed as below .

and to show same in parameter use below listagg function 

SELECT

    CASE

        WHEN least(:p_business) IS NULL THEN

            'ALL'

        ELSE

            (

                SELECT

                    '['

                    ||

                        LISTAGG(business_val.description, ';') WITHIN GROUP(

                            ORDER BY

                                business_val.description

                        )

                    || ']'

                FROM

                    fnd_tree_version_vl   ftv,

                    fnd_tree_node         business_node,

                    fnd_flex_values_vl    business_val

                WHERE

                    1 = 1

                    AND ftv.tree_code = 'VP_COA_PROJECT_TREE'

                    AND ftv.status = 'ACTIVE'

                    AND ftv.tree_structure_code = 'GL_ACCT_FLEX'

                    AND business_node.tree_version_id (+) = ftv.tree_version_id

                    AND business_node.parent_tree_node_id IS NULL

                    AND business_val.flex_value = business_node.pk1_start_value

                    AND business_node.pk1_start_value IN (

                        :p_business

                    )

            )

    END v_business

from dual

How to Show Images from Database in RTF Template Report

Please find an RTF tag for inserting the Image IN RTF template.

<fo:instream-foreign-object content-type="image/jpg"><?IMAGE?></fo:instream-foreign-object>


Note:-  <?IMAGE?>  :- actual tag In Xml file  Find XML and RTF for better understanding

Click Here
Find Sample XML and RTF