Featured post

General Ledger Revaluation

General Ledger Revaluation Account balances denominated in foreign currencies are adjusted through the revaluation procedure. Revaluat...

Showing posts with label Fusion Applications. Show all posts
Showing posts with label Fusion Applications. Show all posts

Sunday, 28 July 2024

General Ledger Revaluation

General Ledger Revaluation

Account balances denominated in foreign currencies are adjusted through the revaluation procedure. Revaluation adjustments show how the dates of the original journal and the revaluation differ in terms of account balances because of variations in exchange rates.

Create a Manual Journals

1.      From General Accounting click on Journals.



2.      Enter the Journal Batch, Description, and Accounting Period in the Journal Batch section.

3.      Enter the Journal, Description, Ledger, Accounting Date, category, Conversion Rate, and currency in the Journal section.


 

4.      Enter the Account combination and amount on the Debit and Credit sides.



5.      Click on Save, Complete, and Post.


Revaluate Balances


6.      From General Accounting click on General Accounting Dashboard.



7.      From the Task list click on Revaluate Balances.


 


8.      Click on the Plus sign.



9.      Enter the Name, Description, Chart of Accounts, Currency, and Conversion Name.

10.   Enter the Unrealized Gain Account and the Unrealized Loss Account.


 


11.   Click on the Plus sign.



12.   Click on the Account Filter sign.


 


13.   Click on the Add fields.

14.   Select the Account.



15.   Select Equals and choose the Account Number.

16.   Click on OK.


 


17.   Click on the Plus sign.



18.   Click on the Account Filter sign.


 


19.   Click on the Add fields.

20.   Select the Account.

21.   Select Equals and choose the Account Number.

22.   Click on OK.


z

23.   Click on Save.



24.   Click on Generate.


 


25.   Select the Ledger, Revaluation Name, Accounting Period, Accounting Date, and

Rate Date.

26.   Click on Submit.




Scheduled processes

27.   From Tools click on Schedule Processes.



28.   The status for the request became Succeeded”.


Manage Journals


29.   From General Accounting click on Journals.



30.   Click on Manage Journals.


 


31.   In the search tab select the Accounting period.

32.   Click on Search.



33.   Click on the Journal that starts withRevaluate”.


 


34.   The Journal has been automatically created according to our setup.


Tuesday, 13 September 2022

AR Invoice Image Upload Using SOAP Request – Oracle Fusion

 During fusion implementation, Business requirement original receivable invoice image in Oracle Fusion Applications. In such cases, we can use Attachment field option in AR invoices.

The challenge here is, we would do the AR invoice date conversion using FBDI process, but FBDI template doesn’t support image files/Image URL to get uploaded to Fusion Application.

In such cases, the workaround would be to go with REST/SOAP webservices. The below example shows the sample payload of SOAP call to upload images to AR invoices.

End Point URL:
———————
https://serverurl/fscmService/ErpObjectAttachmentService?WSDL

Sample Payload:
———————-
<soapenv:Envelope xmlns:soapenv=”http://schemas.xmlsoap.org/soap/envelope/” xmlns:typ=”http://xmlns.oracle.com/apps/financials/commonModules/shared/model/erpIntegrationService/types/” xmlns:erp=”http://xmlns.oracle.com/apps/financials/commonModules/shared/model/erpIntegrationService/”>
<soapenv:Header/>
<soapenv:Body>
<typ:uploadAttachment>
<typ:entityName>RA_CUSTOMER_TRX_ALL</typ:entityName>
<typ:categoryName>CUSTOMER_TRX</typ:categoryName>
<typ:allowDuplicate>Yes</typ:allowDuplicate>
<!–Zero or more repetitions:–>
<typ:attachmentRows>
<!–Business Unit:–>
<erp:UserKeyA>BU Name</erp:UserKeyA>
<!–Transaction Number:–>
<erp:UserKeyB>1000</erp:UserKeyB>
<!–Transaction Source:–>
<erp:UserKeyC>Trx Source</erp:UserKeyC>
<!–CUSTOMER_TRX_ID:–>
<erp:UserKeyD>1</erp:UserKeyD>
<!–AttachmentType:–>
<erp:AttachmentType>FILE</erp:AttachmentType>
<!–File Name:–>
<erp:Title>TESTIMAGE.JPG</erp:Title>
<!–Content: convert the image file to base64 format and input the content below–>
<erp:Content>/9j/4AAQSkZJRgABAQEAYABgAAD/4RCcRXhpZgAATU0AKgAAAAgABAE7AAIAAAAOAAAISodpAAQAAAABAAAIWJydAUUAFFFFABRRRQB/9k=</erp:Content>
</typ:attachmentRows>
</typ:uploadAttachment>
</soapenv:Body>
</soapenv:Envelope>

Tuesday, 6 September 2022

Payment Process Request Query in Fusion

 Payment Process Request in Oracle fusion or E business suit is created to gather all the applicable invoices in P2P Cycle in order to pay the suppliers. ( Come on, they would want their money once they have delivered you the item or the services). Here is the query you can use to get the details:


SELECT aisca.CHECKRUN_ID,
aisca.checkrun_name,
tmplt.template_name ,
aisca.check_date,
aisca.period_name,
aisca.pay_thru_date,
Pmnt_mthd_tl.payment_method_name Payment_Method,
hp.party_name supplier_name,
bank_detail.party_name bank_name,
bank_brach.party_name bank_branch_name,
bank_acct.bank_account_name,
bank_acct.bank_account_num,
bank_acct.masked_account_num,
bank_acct.account_holder_name,
pmnt_doc.payment_document_name,
Pmnt_Prof.payment_profile_name Payment_Profile,
aisca.status
FROM ap_inv_selection_criteria_all aisca,
poz_suppliers supp,
hz_parties hp,
ap_payment_templates tmplt,
ce_payment_documents pmnt_doc,
IBY_ACCT_PMT_PROFILES_TL Pmnt_Prof,
ce_bank_accounts bank_Acct,
hz_parties bank_detail,
hz_parties bank_brach,
iby_payment_methods_tl Pmnt_mthd_tl
WHERE 1=1
AND supp.vendor_id(+) = aisca.vendor_id
and hp.party_id(+) = supp.party_id
AND tmplt.template_id(+) = aisca.template_id
AND pmnt_doc.payment_document_id(+) = aisca.payment_document_id
AND Pmnt_Prof.PAYMENT_PROFILE_ID(+) = aisca.PAYMENT_PROFILE_ID
AND Pmnt_prof.language = USERENV(‘LANG’)
AND bank_Acct.bank_account_id(+) = aisca.bank_account_id
AND Bank_Detail.party_id(+) = bank_Acct.BANK_ID
AND Bank_Brach.party_id(+) = bank_Acct.Bank_branch_id
AND Pmnt_mthd_tl.payment_method_code = aisca.payment_method_code
AND Pmnt_mthd_tl.LANGUAGE = USERENV(‘LANG’)
Order by aisca.creation_date desc

How To Fetch (Query) The Payment Details In Fusion

 Payment: The final leg of P2P Cycle in Oracle. Once you run the payment process Request in Fusion, depending on the criteria you have chosen Invoices will be selected for payment. Once selected, you’ll also get the option to remove the invoices you don’t want to pay. Once you confirm the final list of the invoices to be paid, and take the payment process request to the conclusion, Invoices will be paid (at least in the system. You might still need to send the checks to the vendor or EFT file to the bank)

At high level, this is how the relationship among Payment Process Request, Payment, Checks and Invoices sample looks like

Oracle Fusion Payment structure

To give the further details, every payment process request can have multiple invoices paid against. So relationship between Payment Process Request and Check is 1:N.

Again, 1 Check can be used to pay multiple invoices. At the same time one invoice can be paid by multiple checks. So relationship between review and the Invoices is M:N. Table that holds the relationship is AP_INVOICE_PAYMENTS_ALL.

Vendor Site – Invoicing & Payments Query

 Vendor Site – Invoicing & Payments Query

Vendor Site query of invoicing and payment tabs is something you can’t run away in Fusion query. in this post we’ll discuss the query to fetch the details of both the sections. So first thing first and let me give you the invoicing query. This query will get you the payment method and default flag which means you’ll be able to tell which all payment methods are available at a particular vendor site and which of them is default.


Vendor Site Invoicing Query.

SELECT ste.vendor_site_code,
pm.payment_method_code,
pm.primary_flag Default_flag
FROM poz_supplier_sites_all_m ste,
iby_ext_party_pmt_mthds pm,
iby_external_payees_all payee
WHERE 1=1
AND payee.supplier_site_id = ste.vendor_site_id
AND pm.ext_pmt_party_id = payee.ext_payee_id
AND ste.vendor_site_code =:vendor_site_code

Here :vendor_site_code is the vendor site code for which you want to fetch the details for.

Payments Query

And here is the query to fetch the bank details like bank name, branch, account number of a supplier site.

select ste.vendor_site_code,
cebranch.bank_name bank_name,
cebranch.bank_number bank_number,
cebranch.bank_branch_name branch_name,
bankacct.bank_account_num
from poz_supplier_sites_all_m ste,
iby_external_payees_all payee,
iby_pmt_instr_uses_all instr_assign,
iby_ext_bank_accounts bankacct,
ce_all_bank_branches_v cebranch
where 1=1
AND payee.supplier_site_Id = ste.VENDOR_SITE_ID
AND instr_assign.ext_pmt_party_id=payee.ext_payee_id
AND bankacct.ext_bank_account_id =instr_assign.instrument_id
AND Bankacct.branch_id=cebranch.branch_party_id(+)
AND instr_assign.instrument_type=’BANKACCOUNT’
AND instr_assign.payment_flow=’DISBURSEMENTS’
AND ste.vendor_site_code = :vendor_site_code

Supplier Site Query ( SQL) – General in Fusion

Supplier site is one of the most important set ups in Oracle Fusion Finance. if you have implemented P2P, vendor site and reports fetching the data are inevitable. in this blog, let me share the general query for supplier site. Of course there will be posts as the follow up to this post to find other important fields:

Supplier Site Query Output:

This query will return you following columns:
1. Vendor Site Code
2. Procurement Business Unit
3. Alternate Vendor Site Code
4. Customer Number
5. Purchasing Site Flag
6. Pay Site Flag
7. Procurement Site Flag
8. Primary Pay Site Flag
9. Tax Reporting Site Flag
10. Document Category
11. Site Address
12. Site Status

Supplier Site Query:

SELECT ste.vendor_site_code,
hou.name procurement_bu,
ste.vendor_site_code_alt alternate_site_name,
ste.customer_num customer_number,
ste.purchasing_site_flag,
ste.pay_site_flag,
ste.pcard_site_flag procurement_site_flag ,
ste.primary_pay_site_flag,
ste.tax_reporting_site_flag income_tax_reporting_site,
b2b_site_code b2b_supplier_site_code ,
dsc.name document_category,
hl.address1 ||’, ‘|| hl.address2||’, ‘||hl.city||’, ‘||hl.state||’, ‘|| hl.postal_code|| ‘, ‘||hl.country site_address,
CASE
WHEN NVL(ste.INACTIVE_DATE, SYSDATE +1) > SYSDATE
THEN
‘Active’
ELSE
‘Inactive’
END status
FROM poz_supplier_sites_all_m ste,
hz_party_sites hps,
hz_locations hl,
hr_operating_units hou,
fnd_doc_sequence_categories dsc
WHERE 1=1
AND hps.party_site_id = ste.party_site_id
AND hl.location_id = ste.location_id
AND hou.organization_id = ste.PRC_BU_ID
AND dsc.code(+) = ste.global_attribute1
AND ste.vendor_site_code= :vendor_site_code

Here :vendor_site_code is the input parameter. But in case you want to get all the sites of the supplier, you can add POZ_SUPPLIERS table in the above query and connect POZ_SUPPLIER_SITES_ALL_M table using vendor_id.

Supplier Details Query in Oracle Fusion


Supplier or Vendor Details is something you are bound to query if you have implemented Purchasing/ Payable modules. After all supplier would have been created to supply the material which you would consume in your business and in return you will have to pay the supplier. So most probably you will need to query the vendor from Purchasing side or Payable Side.

Base table to store the supplier information is POZ_Suppliers. Query to fetch the supplier details is given below. In case you are looking to get the details for Supplier Sites or the payment details, following posts can be helpful 

Below is the query you can use. Following are the columns query will fetch:

1. Vendor Name
2. Vendor Number
3. Vendor Type
4. Tax Organization Type
5. Business Relationship
6. DUNS Number
7. Customer Number
8. SIC
9. Registry Id
10. Year Established
11. Mission Statement
12. Taxpayer Id
13. Party Site Name
14. Party Site Address
15. Contact Person

Supplier Detail Query:


SELECT 
PS.VENDOR_NAME,
PS.SEGMENT1 SUPPLIER_NUMBER,
NVL2(PS.TAX_REPORTING_NAME, 'Secure', NULL) TAX_REPORTING_NAME,
ps.vendor_type_lookup_Code supplier_type,
ps.organization_type_lookup_code Tax_Organization_Type,
ps.business_relationship ,
ps.standard_industry_class sic,
hzl.country country,
pvs.vendor_site_spk_id, 
pvs.vendor_site_id, 
pvs.effective_end_date,
pvs.effective_start_date, 
pvs.effective_sequence,
pvs.object_version_number, 
pvs.inactive_date, 
pvs.vendor_id,
pvs.prc_bu_id, 
pvs.location_id, 
pvs.party_site_id,
pvs.vendor_site_code, 
pvs.purchasing_site_flag, 
pvs.rfq_only_site_flag,
pvs.pay_site_flag, pvs.tp_header_id, 
pvs.services_tolerance_id,
pvs.tolerance_id, pvs.terms_id, 
pvs.exclude_freight_from_discount,
pvs.bank_charge_bearer, 
pvs.pay_on_code, 
pvs.default_pay_site_id,
pvs.pay_on_receipt_summary_code, 
pvs.ece_tp_location_code,
pvs.pcard_site_flag, 
pvs.match_option, 
pvs.country_of_origin_code,
pvs.create_debit_memo_flag, 
pvs.supplier_notif_method,
pvs.email_address, 
pvs.primary_pay_site_flag, 
pvs.shipping_control,
pvs.selling_company_identifier, 
pvs.gapless_inv_num_flag,
pvs.retainage_rate, 
pvs.auto_calculate_interest_flag, 
pvs.hold_by,
pvs.hold_date, pvs.hold_flag, 
pvs.purchasing_hold_reason,
pvs.vendor_site_code_alt, 
pvs.attention_ar_flag, 
pvs.area_code,
pvs.phone, pvs.customer_num, 
pvs.ship_via_lookup_code,
pvs.freight_terms_lookup_code, 
pvs.fob_lookup_code, pvs.fax,
pvs.fax_area_code, pvs.telex, 
pvs.terms_date_basis,
pvs.pay_group_lookup_code, 
pvs.payment_priority,
pvs.invoice_amount_limit, 
pvs.pay_date_basis_lookup_code,
pvs.always_take_disc_flag, 
pvs.invoice_currency_code,
pvs.payment_currency_code, 
pvs.hold_all_payments_flag,
pvs.hold_future_payments_flag, 
pvs.hold_reason,
pvs.hold_unmatched_invoices_flag, 
pvs.payment_hold_date,
pvs.tax_reporting_site_flag, 
pvs.last_update_date, 
pvs.last_updated_by,
pvs.last_update_login, 
pvs.creation_date, 
pvs.created_by,
pvs.aging_period_days,
pvs.aging_onset_point, 
pvs.consumption_advice_frequency,
pvs.consumption_advice_summary, 
pvs.pay_on_use_flag,
pvs.mode_of_transport, 
pvs.service_level, 
hzl.address1 address_line1,
hzl.address2 address_line2, 
hzl.address3 address_line3,
hzl.address4 address_line4, 
hzl.address_style address_style,
hzl.address_lines_phonetic address_lines_alt, 
hzl.city city,
hzl.state state, 
hzl.county county, 
hzl.country country,
hzl.postal_code zip, 
hzl.province province,
hzl.location_language language_code, 
hps.duns_number_c duns_number
FROM 
poz_supplier_sites_all_m pvs, 
hz_locations hzl, 
hz_party_sites hps,
POZ_SUPPLIERS_V PS
WHERE hzl.location_id = pvs.location_id
AND hps.party_site_id = pvs.party_site_id
AND pvs.vendor_id=PS.VENDOR_ID
and PS.SEGMENT1 in ('508643','508411')

or try this

select hp.party_name Vendor_Name,
ps.segment1 supplier_number,
ps.vendor_type_lookup_Code supplier_type,
ps.organization_type_lookup_code Tax_Organization_Type,
ps.business_relationship ,
hop.DUNS_NUMBER_C DUNS_NUMBER,
ps.customer_num,
ps.standard_industry_class sic,
hop.party_number Registry_id,
hop.year_established,
hop.mission_statement,
psp.INCOME_TAX_ID Taxpayer_ID,
hps.PARTY_SITE_NAME Address_Name,
hp.address1,
hp.address2,
hp.city ,
hp.state,
hp.county,
hp_contact.person_last_name||’, ‘||hp_contact.PERSON_PRE_NAME_ADJUNCT||’ ‘||hp_contact.person_first_name Contact_Person
from poz_suppliers ps,
hz_parties hp ,
hz_organization_profiles hop,
POZ_SUPPLIERS_PII psp,
hz_party_sites hps,
hz_parties hp_contact
where hp.party_id = ps.party_id
AND hop.party_id = ps.party_id
AND psp.vendor_id(+) = ps.vendor_id
AND hps.party_site_id(+) = hp.iden_addr_party_site_id
AND hp_contact.party_id(+) = hp.preferred_contact_person_id
and hp.party_name = ‘ABC’

Wednesday, 24 August 2022

Fusion – Sample Payload For FindCustomerAccount Operation In TCA Account Web Service

SOLUTION
Below are some sample payload for findCustomerAccountOperation:
<soapenv:Envelope xmlns:soapenv=”http://schemas.xmlsoap.org/soap/envelope/” xmlns:typ=”http://xmlns.oracle.com/apps/cdm/foundation/parties/customerAccountService/applicationModule/types/” xmlns:typ1=”http://xmlns.oracle.com/adf/svc/types/”>
<soapenv:Header/>
<soapenv:Body>
<typ:findCustomerAccount>
<typ:findCriteria>
<typ1:fetchStart>0</typ1:fetchStart>
<typ1:fetchSize>1</typ1:fetchSize>
<typ1:filter>
<typ1:conjunction/>
<typ1:group>
<typ1:conjunction/>
<typ1:upperCaseCompare>false</typ1:upperCaseCompare>
<typ1:item>
<typ1:conjunction/>
<typ1:upperCaseCompare>false</typ1:upperCaseCompare>
<typ1:attribute>AccountNumber</typ1:attribute>
<typ1:operator>=</typ1:operator>
<typ1:value>GB000557</typ1:value>
</typ1:item>
</typ1:group>
<typ1:nested/>
</typ1:filter>
<typ1:excludeAttribute>false</typ1:excludeAttribute>
</typ:findCriteria>
<typ:findControl>
<typ1:retrieveAllTranslations>false</typ1:retrieveAllTranslations>
</typ:findControl>
</typ:findCustomerAccount>
</soapenv:Body>
</soapenv:Envelope>

OR

<soapenv:Envelope xmlns:soapenv=”http://schemas.xmlsoap.org/soap/envelope/” xmlns:typ=”http://xmlns.oracle.com/apps/cdm/foundation/parties/customerAccountService/applicationModule/types/” xmlns:typ1=”http://xmlns.oracle.com/adf/svc/types/”>
<soapenv:Header/>
<soapenv:Body>
<typ:findCustomerAccount>
<typ:findCriteria>
<typ1:fetchStart>0</typ1:fetchStart>
<typ1:fetchSize>1</typ1:fetchSize>
<typ1:filter>
<typ1:conjunction/>
<typ1:group>
<typ1:conjunction/>
<typ1:upperCaseCompare>false</typ1:upperCaseCompare>
<typ1:item>
<typ1:conjunction/>
<typ1:upperCaseCompare>false</typ1:upperCaseCompare>
<typ1:attribute>PartyId</typ1:attribute>
<typ1:operator>=</typ1:operator>
<typ1:value>300000001496090</typ1:value>
</typ1:item>
</typ1:group>
<typ1:nested/>
</typ1:filter>
<typ1:excludeAttribute>false</typ1:excludeAttribute>
</typ:findCriteria>
<typ:findControl>
<typ1:retrieveAllTranslations>false</typ1:retrieveAllTranslations>
</typ:findControl>
</typ:findCustomerAccount>
</soapenv:Body>
</soapenv:Envelope>

Thursday, 18 August 2022

How To active Audit For Period Status And How To Get Audit Report Oracle fusion Application GL

 How to enable Audit for Period status and how to get audit report for the same?

Please follow below steps to Enable Audit

1) Go to the task Manage Audit Policies



2) Click on Configure Business Object Attribute



3) Select Product as General Ledger

4) Select the Object "Accounting Calendars" to enable the Audit on Accounting Calendar Setup and Period Status

5) In right side the same screen select the audited attributes.

6) Save the work.




To view the Audit report:


1)From homepage > Navigator, select Audit reports under tools.

2)Select the product as 'General Ledger'

3)Business object type 'Period Status'

4)Click on search to see the audit report.

5)Select the show attribute details > All attributes to see the details.




Monday, 8 August 2022

Create analysis using Subject Area ‘General Ledger- Balances Real Time’ In Oracle Fusion

1.      Run or schedule Process “Create Balances Cube” In ESS. This process create Essbase cube.

2.      Run or schedule Process “Create Rules XML File for BI Extender Automation”

3.      Run or schedule Process “Import Oracle Fusion Data Extensions for Transactional Business Intelligence”

4.      Mandatory parameters to use the subject area as follows:-

i)        Navigate to Ledger folder and pull Ledger Name, Chart of Accounts

ii)      Navigate to Time folder and pull Name, Accounting Period Name column.

iii)     Navigate To Currency folder and pull Currency, Currency Type column

iv)     Navigate to Amount Type and pull Amount Type column

v)      Navigate to Scenario folder and pull Scenario column.

vi)     Navigate to Balance folder and pull Beginning Balance and Ending Balance

vii)   Navigate to Cost Center Segment folder and pull Cost Center Code

viii)  Navigate to Natural Account Segment folder and pull Account Code

ix)     Navigate to Balancing Segment folder and pull Balancing Segment Code

x)      Filter on Chart of Accounts

xi)     Filter on Fiscal Calendar Name

xii)   Filter on Accounting Period name for a period which has balances.

xiii)  Run the report.





Thursday, 9 June 2022

GL Cross Validation Rules in Oracle Fusion Cloud

Business Requirement : 

If Busines has Cost Center Segment values with Common for Balance sheet and this should not use for P&L Account 

Then business may require COA Restriction required for entering Cost Center as 000- Common and GL Project 0000-Common for all Expense and Revenue GL Account.


Go to System and Maintenance >  Manage Cross-Validation Rule

s
Create Rule :for Example

  • Code: CC01_BS
  • Description  Common(000)GL Costcenter Restriction for all Expense and Revenue Account
  • Effective Date from
  • Effective Date to
  • Enable : yes
  • Filter Conditions : Select where condition GL Account or any Segment on which base system should trigger cross validation rule.
  • Ex: Filter Condition Details: account Greater than or equals to 30000000 ( Make sure if this is restricted Account for which Common cost center should not allow)

account Greater than or equals to 30000000
GL_CVR_1005121_300000922647489

Then enter Validation for which system to restrict or allow the Code for any segment:

For this case we need to excluse Common Cost center should not allow for Above GL Account greather than or equal to 30000000 


*
costCenter Not equals to 000



Finally Check in journal page