e-Invoicing Process in Oracle ERP Cloud View Larger Image e-Invoicing Process in Oracle ERP Cloud

Oracle ERP Cloud provides an integrated Collaboration Messaging Framework to establish business-to-business (B2B) message exchanging capabilities with customers or suppliers. In this blog we will describe how to process an e-Invoice for a customer in Oracle Fusion ERP.

Electronic invoicing (e-Invoicing) is the exchange of the invoice document between a supplier and a buyer and it can be defined as structured invoice data in electronic data interchange (EDI) or XML formats issued, transmitted, received, processed and stored electronically.

Below are the high-level steps that defines the process of generating an AR Invoice in Oracle Fusion ERP.

1. Configuring the customer account for XML Invoicing
2. Create a B2B Trading Partner in Collaboration Messaging
3. Configure the Customer Account for the Trading Partner in Collaboration Messaging
4. Run the process to generate the XML

Describing below the configuration and the process required for each step.

1. Configuring the customer account for XML Invoicing
   Setting the preferred delivery method as XML and select “Enable for XML Invoicing” options.
   Navigation: Customer Account -> Profile History
   • Click on Customer Account
     
   • Navigate to Profile History
     Actions -> Correct Record (If customer account already exist)
     
   • Update Preferred Delivery Method as “XML”
     Select All options in “Enable for XML Invoicing”
     Save and Close
     

2. Create a B2B Trading Partner in Collaboration Messaging
   Navigation: Tools -> Collaboration Messaging
   • Click on Task Lists -> Trading Partners -> Manage B2B Trading Partner
     
   • Enter Service Provider, Trading Partner ID and Partner ID Type.
     Trading Partner ID is same as Registry ID
     Save and Close
     
   • Setup Delivery Methods
     Name: Can be any value
     Delivery Method: UCM File Export
     UCM File Export Document Account : scm/CMKOutbundMessageQueue/export (automatically selected)
     
   • Click on next tab: Outbound Collaboration Messages
     Name: Can be any value
     Collaboration Message Definition: UBL_2.1_INVOICE_OUT (Please note this message definition can be duplicated and customized as per the requirements if needed)
     Delivery Method: Same as created in last step
     Save and Close
     

3. Configure the Customer Account for the Trading Partner in Collaboration Messaging
   Navigation: Tools -> Collaboration Messaging
   Click on Task List -> Trading Partners -> Manage Customer Account Collaboration Configuration
   • Search with customer name and click on Edit Collaboration Configuration
     
   • Service Provider: Selected in previous step (None)
     Trading Partner ID: Select the party id
   • Collaboration Documents for Service Provider
     Document: PROCESS_INVOICE_OUT
     Association Status: Enabled
     

4. Run the process to generate the XML
   • Run the Scheduled Process: Generate and Transfer XML Transactions. This process places the files in UCM as the delivery method selected as UCM File Export.
     
   • How to find the file transmitted to UCM path
     Navigation: Tools -> Fie Import and Export
     
     Search with Account: scm/CMKOutbundMessageQueue/export (this is the standard path used at the time of Delivery Method setup)
     

Query To Get Payable Payment Details In Oracle Fusion ERP Application

 select     "AP_INVOICES_ALL"."INVOICE_ID" as "INVOICE_ID",

      "AP_INVOICES_ALL"."INVOICE_NUM" as "INVOICE_NUM",

      "AP_INVOICES_ALL"."INVOICE_AMOUNT" as "INVOICE_AMOUNT",

      "AP_INVOICES_ALL"."INVOICE_CURRENCY_CODE" as "INVOICE_CURRENCY_CODE",

      "AP_INVOICES_ALL"."AMOUNT_PAID" as "AMOUNT_PAID",

      "AP_INVOICES_ALL"."INVOICE_DATE" as "INVOICE_DATE",

      "AP_INVOICES_ALL"."INVOICE_TYPE_LOOKUP_CODE" as "INVOICE_TYPE_LOOKUP_CODE",

      "AP_INVOICES_ALL"."DESCRIPTION" as "DESCRIPTION",

      "AP_INVOICES_ALL"."PAYMENT_STATUS_FLAG" as "PAYMENT_STATUS_FLAG",

      "AP_INVOICE_PAYMENTS_ALL"."AMOUNT" as "AMOUNT",

      "AP_INVOICE_PAYMENTS_ALL"."ACCOUNTING_DATE" as "ACCOUNTING_DATE",

      "AP_INVOICE_PAYMENTS_ALL"."BANK_ACCOUNT_NUM" as "BANK_ACCOUNT_NUM",

      "AP_INVOICE_PAYMENTS_ALL"."BANK_ACCOUNT_TYPE" as "BANK_ACCOUNT_TYPE",

      "AP_INVOICE_PAYMENTS_ALL"."BANK_NUM" as "BANK_NUM",

      "AP_INVOICE_PAYMENTS_ALL"."CHECK_ID" as "CHECK_ID",

      "AP_CHECKS_ALL"."AMOUNT" as "AMOUNT_1",

      "AP_CHECKS_ALL"."BANK_ACCOUNT_NAME" as "BANK_ACCOUNT_NAME",

      "AP_CHECKS_ALL"."CHECK_DATE" as "CHECK_DATE",

      "AP_CHECKS_ALL"."CHECK_NUMBER" as "CHECK_NUMBER",

      "AP_CHECKS_ALL"."PAYMENT_METHOD_LOOKUP_CODE" as "PAYMENT_METHOD_LOOKUP_CODE",

      "AP_CHECKS_ALL"."PAYMENT_TYPE_FLAG" as "PAYMENT_TYPE_FLAG",

      "AP_CHECKS_ALL"."BANK_ACCOUNT_NUM" as "BANK_ACCOUNT_NUM_1",

      "AP_CHECKS_ALL"."BANK_NUM" as "BANK_NUM_1",

      "AP_CHECKS_ALL"."BANK_ACCOUNT_TYPE" as "BANK_ACCOUNT_TYPE_1",

      "AP_CHECKS_ALL"."DESCRIPTION" as "DESCRIPTION_1",

      "AP_INVOICE_PAYMENTS_ALL"."PAYMENT_NUM" as "PAYMENT_NUM",

      "AP_INVOICE_DISTRIBUTIONS_ALL"."DISTRIBUTION_LINE_NUMBER" as "DISTRIBUTION_LINE_NUMBER",

      "AP_INVOICE_DISTRIBUTIONS_ALL"."AMOUNT" as "AMOUNT_1_1",

      "AP_INVOICE_DISTRIBUTIONS_ALL"."DESCRIPTION" as "DESCRIPTION_1_1",

      "AP_INVOICE_DISTRIBUTIONS_ALL"."UNIT_PRICE" as "UNIT_PRICE",

      "AP_INVOICE_PAYMENTS_ALL"."INVOICING_PARTY_ID" as "INVOICING_PARTY_ID",

      "HZ_PARTIES"."PARTY_NUMBER" as "PARTY_NUMBER",

      "HZ_PARTIES"."PARTY_NAME" as "PARTY_NAME",

      "HZ_PARTIES"."PARTY_TYPE" as "PARTY_TYPE",

      "HZ_PARTIES"."ADDRESS1" as "ADDRESS1",

      "HZ_PARTIES"."ADDRESS2" as "ADDRESS2",

      "HZ_PARTIES"."CITY" as "CITY",

      "HZ_PARTIES"."COUNTRY" as "COUNTRY",

      "HZ_PARTIES"."STATE" as "STATE",

      "HZ_PARTIES"."PROVINCE" as "PROVINCE",

      "AP_INVOICES_ALL"."PARTY_ID" as "PARTY_ID",

      "AP_INVOICES_ALL"."PARTY_SITE_ID" as "PARTY_SITE_ID",

      "HZ_PARTY_SITES"."PARTY_SITE_NAME" as "PARTY_SITE_NAME" 

  from    "FUSION"."HZ_PARTY_SITES" "HZ_PARTY_SITES",

     "FUSION"."HZ_PARTIES" "HZ_PARTIES",

     "FUSION"."AP_INVOICE_DISTRIBUTIONS_ALL" "AP_INVOICE_DISTRIBUTIONS_ALL",

     "FUSION"."AP_CHECKS_ALL" "AP_CHECKS_ALL",

     "FUSION"."AP_INVOICES_ALL" "AP_INVOICES_ALL",

     "FUSION"."AP_INVOICE_PAYMENTS_ALL" "AP_INVOICE_PAYMENTS_ALL" 

  where   "AP_INVOICES_ALL"."INVOICE_ID"="AP_INVOICE_PAYMENTS_ALL"."INVOICE_ID"

  and     "AP_INVOICE_PAYMENTS_ALL"."CHECK_ID"="AP_CHECKS_ALL"."CHECK_ID"

  and     "AP_INVOICES_ALL"."INVOICE_ID"="AP_INVOICE_DISTRIBUTIONS_ALL"."INVOICE_ID"

  and     "AP_INVOICES_ALL"."PARTY_ID"="HZ_PARTIES"."PARTY_ID"(+)

  and     "AP_INVOICES_ALL"."PARTY_SITE_ID"="HZ_PARTY_SITES"."PARTY_SITE_ID"(+)

 order by "AP_INVOICES_ALL"."INVOICE_NUM" ASC

Query To Get Payable Invoice Details In Oracle Fusion ERP Application

 select     "AP_INVOICES_ALL"."INVOICE_ID" as "INVOICE_ID",

      "AP_INVOICES_ALL"."INVOICE_NUM" as "INVOICE_NUM",

      "AP_INVOICES_ALL"."INVOICE_AMOUNT" as "INVOICE_AMOUNT",

      "AP_INVOICES_ALL"."SOURCE" as "SOURCE",

      "AP_INVOICES_ALL"."AMOUNT_PAID" as "AMOUNT_PAID",

      "AP_INVOICES_ALL"."INVOICE_CURRENCY_CODE" as "INVOICE_CURRENCY_CODE",

      "AP_TERMS_TL_1"."NAME" as "PAYMENT_TERM",

      "AP_INVOICES_ALL"."INVOICE_TYPE_LOOKUP_CODE" as "INVOICE_TYPE_LOOKUP_CODE",

      "HR_ALL_ORGANIZATION_UNITS"."NAME" as "BUSINESS_UNIT",

      "AP_INVOICES_ALL"."INVOICE_DATE" as "INVOICE_DATE",

      "HZ_PARTIES_2"."PARTY_NAME" as "SUPPLIER",

      "HZ_PARTIES_3"."PARTY_NAME" as "SUPPLIER_SITE",

      "AP_INVOICE_LINES_ALL"."LINE_NUMBER" as "LINE_NUMBER",

      "AP_INVOICE_LINES_ALL"."DESCRIPTION" as "DESCRIPTION",

      "AP_INVOICE_LINES_ALL"."LINE_SOURCE" as "LINE_SOURCE",

      "AP_INVOICE_LINES_ALL"."ITEM_DESCRIPTION" as "ITEM_DESCRIPTION",

      "AP_INVOICE_LINES_ALL"."DISTRIBUTION_SET_ID" as "DISTRIBUTION_SET_ID",

      "AP_INVOICE_LINES_ALL"."AMOUNT" as "AMOUNT",

      "AP_INVOICE_LINES_ALL"."ACCOUNT_SEGMENT" as "ACCOUNT_SEGMENT",

      "AP_INVOICE_LINES_ALL"."BALANCING_SEGMENT" as "BALANCING_SEGMENT",

      "AP_INVOICE_LINES_ALL"."COST_CENTER_SEGMENT" as "COST_CENTER_SEGMENT",

      "AP_INVOICE_LINES_ALL"."ACCOUNTING_DATE" as "ACCOUNTING_DATE",

      "AP_INVOICE_LINES_ALL"."QUANTITY_INVOICED" as "QUANTITY_INVOICED",

      "AP_INVOICE_LINES_ALL"."UNIT_PRICE" as "UNIT_PRICE",

      "AP_INVOICE_LINES_ALL"."SHIP_TO_LOCATION_ID" as "SHIP_TO_LOCATION_ID" 

  from    "FUSION"."AP_INVOICE_LINES_ALL" "AP_INVOICE_LINES_ALL",

     "FUSION"."HZ_PARTIES" "HZ_PARTIES_3",

     "FUSION"."HZ_PARTY_SITES" "HZ_PARTY_SITES_1",

     "FUSION"."HZ_PARTIES" "HZ_PARTIES_2",

     "FUSION"."HR_ALL_ORGANIZATION_UNITS" "HR_ALL_ORGANIZATION_UNITS",

     "FUSION"."AP_TERMS_TL" "AP_TERMS_TL_1",

     "FUSION"."AP_INVOICES_ALL" "AP_INVOICES_ALL" 

  where   "AP_INVOICES_ALL"."TERMS_ID"="AP_TERMS_TL_1"."TERM_ID"

  and     "AP_INVOICES_ALL"."ORG_ID"="HR_ALL_ORGANIZATION_UNITS"."ORGANIZATION_ID"

  and     "AP_INVOICES_ALL"."PARTY_ID"="HZ_PARTIES_2"."PARTY_ID"

  and     "AP_INVOICES_ALL"."PARTY_SITE_ID"="HZ_PARTY_SITES_1"."PARTY_SITE_ID"

  and     "HZ_PARTY_SITES_1"."PARTY_ID"="HZ_PARTIES_3"."PARTY_ID"

  and     "AP_INVOICES_ALL"."INVOICE_ID"="AP_INVOICE_LINES_ALL"."INVOICE_ID"

  -- and      "AP_INVOICES_ALL"."INVOICE_NUM" ='Test Inv 1' 

    and     "AP_TERMS_TL_1"."LANGUAGE" ='US'

Supplier tables in oracle apps r12.supplier master tables in oracle r12

 Supplier tables in oracle apps r12.supplier master tables in oracle r12

In this post , I am sharing the Suppliers table which stores Supplier Information in the Oracle apps r12. Here below you will find the suppliers Table with Detail InformationAP_SUPPLIERS

This table store Supplier Header Information's in Oracle apps r12.

AP_SUPPLIER_SITES_ALL

This Table stores Supplier sites Information in the Oracle apps R12.

AP_SUPPLIER_CONTACT

This Table store the Supplier Communications information in Oracle apps R12.

PO_VENDORS

This is the View in the Oracle apps which stores Suppliers Information.

To get the Supplier Banking Information in Oracle Apps r12 ,you can refer these below tables.

select VENDOR_NAME,vendor_site_code,IEBA.BANK_ACCOUNT_NUM,IEBA.IBAN,IEBA.BANK_ACCOUNT_NAME,cbv.BANK_NAME,cbv.ADDRESS_LINE1 BANK_ADDRESS_1,CBV.COUNTRY BANK_COUNTRY,

CBV.CITY BANK_CITY,

CBBV.BANK_BRANCH_NAME,CBBV.ADDRESS_LINE1 BRANCH_ADDRESS_1,

CBBV.CITY BRANCH_CITY,CBBV.COUNTRY BRANCH_COUNTRY,CBBV.BRANCH_NUMBER,CBBV.EFT_SWIFT_CODE BIC ,FOREIGN_PAYMENT_USE_FLAG  from apps.AP_SUPPLIERS APS,apps.AP_SUPPLIER_SITES_ALL ass,apps.IBY_EXTERNAL_PAYEES_ALL IEPA,

apps.IBY_PMT_INSTR_USES_ALL IPIUA,APPS.IBY_EXT_BANK_ACCOUNTS IEBA

,apps.ce_banks_v cbv, apps.ce_bank_BRANCHES_V CBBV

where aps.VENDOR_ID=ass.VENDOR_ID

and ass.ORG_ID=:P_ORG_ID

AND IEPA.PAYEE_PARTY_ID=APS.PARTY_ID

and nvl(iepa.PARTY_SITE_ID,supplier_SITE_ID)=nvl(ass.PARTY_SITE_ID,VENDOR_SITE_ID)

--and  iepa.PARTY_SITE_ID IS not NULL

AND IPIUA.EXT_PMT_PARTY_ID(+)=IEPA.EXT_PAYEE_ID

AND IEBA.EXT_BANK_ACCOUNT_ID(+)=IPIUA.INSTRUMENT_ID

AND IEBA.BANK_ID=cbv.BANK_PARTY_ID(+)

AND IEBA.BRANCH_ID=CBBV.BRANCH_PARTY_ID(+)

group by VENDOR_NAME,vendor_site_code, IEBA.BANK_ACCOUNT_NUM,IEBA.IBAN,IEBA.BANK_ACCOUNT_NAME,cbv.BANK_NAME,cbv.ADDRESS_LINE1,CBV.COUNTRY,

CBV.CITY ,

CBBV.BANK_BRANCH_NAME,CBBV.ADDRESS_LINE1 ,CBBV.CITY ,CBBV.COUNTRY ,CBBV.BRANCH_NUMBER,CBBV.EFT_SWIFT_CODE  ,FOREIGN_PAYMENT_USE_FLAG,

IEBA.ATTRIBUTE2,IEBA.ATTRIBUTE3,IEBA.ATTRIBUTE8,IEBA.ATTRIBUTE9,IEBA.ATTRIBUTE10

 

Supplier Site Table in Oracle Fusion

 

Types of Supplier Details in Oracle Fusion

In Oracle Fusion, Supplier details are divided into four parts.

1. Supplier Header

2.Supplier Addresses

3.Supplier Sites

4.Supplier Site Assignment

4 Important Supplier Tables in Oracle Fusion

1.POZ_SUPPLIER_SITES_ALL_M

2.POZ_SUPPLIERS_V

3.POZ_SUPPLIER_CONTACTS_V

4.POZ_SITE_ASSIGNMENTS_ALL_M

SQL Query to Extract Supplier detail Using Supplier Site Table in Oracle Fusion

SELECT PS.VENDOR_NAME,

PS.SEGMENT1 SUPPLIER_NUMBER,

NVL2(PS.TAX_REPORTING_NAME, 'Secure', NULL) TAX_REPORTING_NAME,

PS.EMPLOYEE_ID,

pvs.vendor_site_spk_id, pvs.vendor_site_id, pvs.effective_end_date,

pvs.vendor_site_code, pvs.purchasing_site_flag,pvs.rfq_only_site_flag,

pvs.pay_site_flag,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.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.fax_area_code,pvs.payment_priority,

pvs.invoice_amount_limit, 

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,

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

DISPLAY QR CODE IN CLOUD ERP BI PUBLISHER REPORT

 

It is very easy to display QR code in a Fusion BI Publisher report. To achieve that you need to add the below tag in the RTF template of your BIP report

<?qrcode: <DATA>; <SIZE>[; <CHARSET>]?>

for example: <?qrcode: ITEM_NUMBER; 100?>

adding qrcode tag in the RTF template

Sample Query: select item_number,description from egp_system_items_vl

BIP Data model

Report output with QR Code

For more information please refer
https://docs.oracle.com/en/cloud/paas/analytics-cloud/acpmr/2d-barcode-functions.html#GUID-E0D697B1-0B13-46A3-8578-1FE933F1A86D
and
Fusion Application – BI Publisher – How to implement QR Code (Doc ID 2382839.1)

Business Units in Oracle Fusion Financials

In the Oracle Fusion Clouds, a Business Unit (BU) is a system organization that:

1. Performs business functions – assign business functions to business units and associated business units with a ledger in order to process and partition your business transactions.

2. Partitions transactions – business transactions generated in a particular business unit is stored separately from transactions processed in other business units.

3. Secures transactions – assign data roles associated with your business units to your users to secure access to your business transactions.

4. Shares reference data – reference data objects can be shared across multiple business units, thus significantly reducing setup efforts.

You can use BUs to store data on behalf of a Legal Entity. As compliance with transaction tax auditing legislation is built into transaction types and transaction types are stored by a BU, this is an effective way to manage transaction compliance. The real world legal entity's compliance obligations are administered by:

• BU on subledger transactions and

• Ledger for balancing, closing, and reporting rules. Depending on the nature of the regulation with which you must comply, various combinations of real-world company and system legal entity, BU, and Ledger are possible. In a worldwide deployment, one would expect to see all combinations in place in different situations.

• One legal entity accounts for itself in one Ledger storing subledger data in one BU. This is a normal setup for a country or region that closely regulates subledger data by legal entity.

• Several legal entities account for themselves in one Ledger storing subledger data in one BU, and use the Chart of Accounts primary balancing segment to produce financial statements for each legal entity.

This is a normal setup for a country or region that regulates a group of companies as a whole.

• A legal entity or group of legal entities account for themselves in one Ledger storing subledger data in several BUs. This is a normal setup for a group doing business in highly regulated industries in a given country or region.

• A part of a legal entity accounts for itself in a ledger using one or several BUs. This is a normal situation for a large corporation using several instances or Enterprise Resource Planning (ERP) systems.

System legal entities, Ledgers, and BUs are defined in relation to one another. A legal entity accounts for itself in the Primary Ledger and optionally in other ledgers, and stores its subledger data in one or more BUs.

BUs are often identified with security. In Oracle Fusion Applications, users are given access to the data they handle through "data roles". A data role is associated with a specific BU. A user can be granted access to several BUs through the assignment of multiple data roles. By securing subledger data in this way users can access and process transaction information only for the particular business unit or set of business units to which they have been granted access. They view only what they need and have the authorization to view.

This is very fundamental security. Oracle Fusion Applications incorporate many other security models specific to circumstances that match the usage and deployment requirement needs that you have for those circumstances to provide comprehensive and appropriate security.

BUs can be used to model autonomous organizational units that create financial transactions. You create, process, and report on subledger financial data within the context of a BU.

• Use a BU when you need to keep the data of one organization distinct - at arm’s length - from the data of another organization. You might have the right to prevent a state's transaction tax auditor from viewing the transactions of a neighboring state; consider storing each state's transactions in separate BUs. This right often exists when the states are independent nations, but seldom when they are federated.

• Use a BU when you need to comply with transaction tax law that is substantially different (more than just the tax rates) to similar laws in the neighboring state. You can use product "transaction types" to create similar transactions that follow different documentation and processing practices.

• Use a BU when you wish to keep data of an operation private from the management of another operation. For example, within a financial institution division, you may want to keep the transactions and data of the lending operation separate from that of the brokerage operations.

BUs divide the subledger document data in Oracle Fusion Financials into distinct segments. Standard reports and processes run within BUs, and 'special' reports and processes run across them. You can deploy BUs to provide barriers that require special access, reporting, and processing to cross.

Subledger Accounting and Business Units

While subledger transaction rules are governed by BU, subledger accounting rules are not BU dependent.

BUs store transaction types and rules that govern the transaction document such as invoices, and the business rules (for example, credit terms) that you want to be applied to those documents.

Subledger accounting rules are not stored by BU. They are stored centrally but can refer to any data (including BU identifiers) associated with the transaction to derive the appropriate accounting.

 Define Business Units

Setup and Maintenance > Financials > Define Common Applications Configuration for Financials.

Business Unit Definition Considerations

1.  How will you structure your ledgers, legal entities, and business unit rollups?

2.  Are there financial reports that need to be generated by business unit?

3.  How can you set up your departments or business unit accounts to achieve departmental hierarchies that report accurately on your lines of business?

4.  What reporting do you need to support the managers of your business

5.  units, and the executives who measure them?

6.  How often are business unit results aggregated?

7.  What level of reporting detail is required across business units?

Manage Business Units

·        A business unit represents a unit of an enterprise that performs one or many business functions and can be consolidated in both a managerial and legal hierarchy.

·        A business unit can process procurement transactions on behalf of many legal entities.

·        A business unit is used to partition your sub-ledgers.

·        A business unit is similar to the Operating Unit in prior versions of Oracle, with additional functionality.

Oracle Fusion Financials facilitates independent balance sheet rollups for legal and management reporting by providing the ability to use up to three balancing segments. Hierarchies created using the management segment can provide the divisional results.

For example, it is possible to define management segment values to correspond to business units and arrange them in a hierarchy where the higher nodes correspond to divisions and subdivisions.

Use business units in the following ways:

·        Management reporting

·        Processing of transactions

·        Security of transactional data

·        Reference data definition and sharing

Reference Data Sharing

Share your reference data (setup data) by grouping them into reference data sets and associating them with one or more business units. Being able to share reference data across business units enables faster and easier definition and maintenance of setup data, eliminates duplication and ensures a single source of truth for setups. For example, an organization has several business units and each business unit has a set of payment terms that can be applied to a customer invoice. Instead of each business unit creating and maintaining multiple sets of the same payment terms, you can define the payment terms reference data at the organization level and stripe that data into partitions (create reference data sets) so that the different payment terms can be assigned to each business unit.

If your enterprise has country-specific requirements that affect your reference data, consider creating separate reference data sets and business units to meet these local requirements.

Shared Services

The setup of business units provides a powerful security construct in the applications by creating a tight relationship between the functions a user can perform and the data that a user can process. This security model is appropriate in a business environment where local business units are solely responsible for managing all aspects of the finance and administration functions.

In a worldwide deployment, this tight relationship provides an internal control over inappropriate processing.

For example, in traditional local operations, an invoice of one BU (a system representation of, perhaps, a company in a country) cannot be paid by a payment from another (a system representation of, perhaps, a different company in a different country). This would amount to tax fraud.

Oracle Fusion Financials; hashtag#Oracle, hashtag#Business Unit, hashtag#Enterprise Structure

By contrast, in a Shared Service Center environment, processes that allow one company to perform services for others, with appropriate intercompany accounting, require that users access the data of different companies, each complying with different local requirements. There are two types of relationships:

·        Service/Provider Model - You can define relationships between business units to outline which business unit provides services to the other business units. Your business units that have the requisitioning business function can define relationships with business units that have the procurement business function enabled. If you operate in this model, consider minimizing the number of business units assigned to your primary ledgers because security considerations might not be as prominent in the case of a shared service center.

·        Non Service/Provider Model – Each business unit performs all business functions throughout the transaction lifecycle (business units do not provide services to other business units). A user in the shared service center could manage more than one of these business units and is assigned access to multiple business units for which he is responsible for transaction processing.

You can isolate your data by BU for security and local level compliance and also enable certain users and processes to work across them. Consider an environment where the orders are taken in several different BUs each representing different registered companies. These BUs segregate the orders and data appropriately. However, all of these orders can be managed from a "shared service" order desk in an outsourcing environment through a single job role.

Correct Journal Import Data

  Period Error Codes

EP01 This date is not in any open or future enterable period.

EP03 This date is not within any period in an open encumbrance year.

EP04 This date is not a business day.

EP05 There are no business days in this period.

Unbalanced Journal Error Codes

WU01 This journal entry is unbalanced. It is processed because suspense posting is allowed in this ledger.

EU02 This journal entry is unbalanced and suspense posting is not allowed in this ledger.

EU03 This encumbrance journal entry is unbalanced and the Reserve for Encumbrance account is not defined.

Flexfield Error Codes

EF01 This account is inactive for this accounting date.

EF02 Detail posting not allowed for this account.

EF03 Disabled account.

EF04 This is an invalid account. Check your cross-validation rules and segment values.

EF05 There is no account with this Code Combination ID.

EF06 The alternate account is invalid.

WF01 An alternate account was used instead of the original account.

WF02 A suspense account was used instead of the original account.

Foreign Currency Error Codes

EC01 A conversion rate must be entered when using the User conversion rate type.

EC02 There is no conversion date supplied.

EC03 A conversion rate type or an accounted amount must be supplied when entering foreign currency journal lines.

EC06 There is no conversion rate for this currency,  conversion rate type,  and conversion date.

EC08 Invalid currency.

EC09 No currencies are enabled.

EC10 Encumbrance journals cannot be created in a foreign currency.

EC11 Invalid conversion rate type.

EC12 The entered amount must equal the accounted amount in a ledger or statistical currency journal line.

EC13 Amount is too large.

ECW1 Converted amounts could not be validated because the conversion rate type is not specified.

Encumbrance Error Codes.

EE01 An encumbrance type is required for encumbrance lines.

EE02 Invalid or disabled encumbrance type.

EE03 Encumbrance journals cannot be created in the STAT currency.

EE04 The BUDGET_VERSION_ID column must be null for encumbrance lines.

EE05 Average journals cannot be created for encumbrances.

EE06 Originating company information cannot be specified for encumbrances.

Reversal Error Codes

ER01 A reversal period name must be provided.

ER02 This reversal period name is invalid. Check your calendar for valid periods.

ER03 The reversal date must be provided

ER04 This reversal date is not in a valid period.

ER05 This reversal date is not in your database date format.

ER06 Your reversal date must be the same as or after your effective date.

ER07 This reversal date is not a business day.

ER08 There are no business days in your reversal period.

ER09 Default reversal information could not be determined.

Descriptive Flexfield Error Codes

ED01 The context and attribute values do not form a valid descriptive flexfield for Journals - Journal Entry Lines.

ED02 The context and attribute values do not form a valid descriptive flexfield for Journals - Captured Information.

ED03 The context and attribute values do not form a valid descriptive flexfield for Value Added Tax.

Miscellaneous Error Codes

EM01 Invalid journal entry category.

EM02 There are no journal entry categories defined.

EM05 The ENCUMBRANCE_TYPE_ID column must be null for actual journals.

EM06 The budget_version_id column must be null for actual journals.

EM07 The statistical amount belongs in the entered_dr or entered_cr column when entering a STAT currency journal line.

EM09 There is no Transaction Code defined.

EM10 Invalid Transaction Code.

EM12 An error occurred when generating sequential numbering.

EM13 The assigned sequence is inactive.

EM14 There is a sequential numbering setup error resulting from a missing grant or synonym.

EM17 Sequential numbering is always used and there is no assignment for this ledger and journal entry category.

EM18 Manual document sequences cannot be used with Journal Import.

EM19 Value Added Tax data is only valid in conjunction with actual journals.

EM24 Average journals can only be imported into consolidation ledgers.

EM25 Invalid average journal column value.  Valid values are {Y_CODE,  {N_CODE,  and null.

EM26 Invalid originating company.

EM27 Originating company information can only be specified when intercompany balancing is enabled.

EM29 You do not have access to this ledger and account combination.

EM30 This primary balancing segment value is not valid for this ledger.

EM31 This management segment value is not valid for this ledger.

EM32 This primary balancing segment value is not valid for the legal entity.

How to Identify Whether the Transfer to Cash Management Process Has Completed

Payroll Cannot Transferred To Cash Management

STEPS

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

The issue can be reproduced at will with the following steps:

1-Calculate Payroll

2-Calculate Prepayments

3-Archive Periodic Payroll Results

4-Make EFT Payments

5-Generate Payslips

6-Calculate Costing of Payments -1

7-Transfer Payments Information to Cash Management

8-Transfer to Subledger Accounting

9-Calculate Costing of Payments -2

10-Create Accounting (draft)

Below query to identify unprocessed payroll action id

For the last Transfer to Cash Management executed, this will show number of employees successfully processed.

select count(*)

from pay_payroll_rel_actions ra

where ra.source_action_id is null

and ra.action_status <> 'U'

and ra.payroll_action_id in

(select max(pa.payroll_action_id)

from pay_payroll_actions pa

where pa.action_type = 'TC');

The following will show number awaiting processing:

select count(*)

from pay_payroll_rel_actions ra

where ra.source_action_id is null

and ra.action_status = 'U'

and ra.payroll_action_id in

(select max(pa.payroll_action_id)

from pay_payroll_actions pa

where pa.action_type = 'TC');

Supplier Bank Assignment Query SQL Oracle Fusion

 Select * from (SELECT PaymentInstrumentUsesEO.INSTRUMENT_PAYMENT_USE_ID,

  PaymentInstrumentUsesEO.EXT_PMT_PARTY_ID,

  PaymentInstrumentUsesEO.INSTRUMENT_TYPE,

  PaymentInstrumentUsesEO.INSTRUMENT_ID,

  PaymentInstrumentUsesEO.PAYMENT_FUNCTION,

  PaymentInstrumentUsesEO.ORDER_OF_PREFERENCE,

  PaymentInstrumentUsesEO.DEBIT_AUTH_FLAG,

  PaymentInstrumentUsesEO.DEBIT_AUTH_METHOD,

  PaymentInstrumentUsesEO.DEBIT_AUTH_REFERENCE,

  PaymentInstrumentUsesEO.DEBIT_AUTH_BEGIN,

  PaymentInstrumentUsesEO.DEBIT_AUTH_END,

  PaymentInstrumentUsesEO.START_DATE,

  PaymentInstrumentUsesEO.END_DATE,

  ibyextbankaccts.bank_account_id,

  ibyextbankaccts.bank_account_name,

  ibyextbankaccts.bank_account_number,

  ibyextbankaccts.currency_code,

  ibyextbankaccts.bank_name,

  ibyextbankaccts.bank_number,

  ibyextbankaccts.bank_branch_name,

  ibyextbankaccts.bank_account_type,

  ibyextbankaccts.branch_number,

  ibyextbankaccts.iban_number,

  ibyextbankaccts.eft_swift_code,

  'N' detailView,

  PaymentInstrumentUsesEO.PAYMENT_FLOW,

  supplier.vendor_name party_name,

  supplier.vendor_name_alt alt_party_name,

  supplier.segment1 party_number,

  NULL site_name,

  NULL alt_site_name,

  address.party_site_name,

  NULL organization_name,

  DECODE(Payee.supplier_site_id, NULL, DECODE(Payee.org_id, NULL, DECODE(Payee.party_site_id, NULL,

  (SELECT MEANING

  FROM fnd_lookup_values_vl

  WHERE LOOKUP_TYPE = 'IBY_EXT_BNK_ACCT_ASSGN_SRCH'

  AND LOOKUP_CODE   ='SUPPLIER'

  ),

  (SELECT MEANING

  FROM fnd_lookup_values_vl

  WHERE LOOKUP_TYPE = 'IBY_EXT_BNK_ACCT_ASSGN_SRCH'

  AND LOOKUP_CODE   ='ADDRESS'

  )),

  (SELECT MEANING

  FROM fnd_lookup_values_vl

  WHERE LOOKUP_TYPE = 'IBY_EXT_BNK_ACCT_ASSGN_SRCH'

  AND LOOKUP_CODE   ='ADDRESS_OPERATING'

  )),

  (SELECT MEANING

  FROM fnd_lookup_values_vl

  WHERE LOOKUP_TYPE = 'IBY_EXT_BNK_ACCT_ASSGN_SRCH'

  AND LOOKUP_CODE   ='SITE'

  )) assignment_level,

  ibyextbankaccts.ext_bank_account_id ext_bank_account_id,

  ibyextbankaccts.bank_party_id bank_party_id,

  ibyextbankaccts.branch_party_id branch_party_id,

  DECODE(ibyextbankaccts.FOREIGN_PAYMENT_USE_FLAG, 'Y',

  (SELECT meaning

  FROM fnd_lookup_values_vl

  WHERE LOOKUP_TYPE = 'IBY_YES_NO'

  AND lookup_code   = 'Y'

  ),

  (SELECT meaning

  FROM fnd_lookup_values_vl

  WHERE LOOKUP_TYPE = 'IBY_YES_NO'

  AND lookup_code   = 'N'

  )) FOREIGN_PAYMENT_USE_FLAG,

  DECODE(ibyextbankaccts.PRIMARY_ACCT_OWNER_PARTY_ID, Payee.payee_party_id,

  (SELECT meaning

  FROM fnd_lookup_values_vl

  WHERE LOOKUP_TYPE = 'IBY_YES_NO'

  AND lookup_code   = 'Y'

  ),

  (SELECT meaning

  FROM fnd_lookup_values_vl

  WHERE LOOKUP_TYPE = 'IBY_YES_NO'

  AND lookup_code   = 'N'

  )) primary_flag,

  Payee.payee_party_id party_id,

  (SELECT MEANING

  FROM fnd_lookup_values_vl

  WHERE LOOKUP_TYPE = 'IBY_EXT_BNK_ACCT_ASSGN_SRCH'

  AND LOOKUP_CODE   ='SUPPLIER'

  ) party_type

FROM IBY_PMT_INSTR_USES_ALL PaymentInstrumentUsesEO,

  IBY_EXT_BANK_ACCOUNTS_V ibyextbankaccts,

  IBY_EXTERNAL_PAYEES_ALL Payee,

  poz_suppliers_v supplier,

  hz_party_sites address

WHERE PaymentInstrumentUsesEO.instrument_id  = ibyextbankaccts.bank_account_id

AND PaymentInstrumentUsesEO.instrument_type  = 'BANKACCOUNT'

AND PaymentInstrumentUsesEO.payment_function = 'PAYABLES_DISB'

AND PaymentInstrumentUsesEO.EXT_PMT_PARTY_ID = Payee.ext_payee_id

AND Payee.payee_party_id                     = supplier.party_id

AND Payee.party_site_id                      = address.party_site_id(+)

AND Payee.org_id                            IS NULL

AND Payee.org_type                          IS NULL

UNION

SELECT PaymentInstrumentUsesEO.INSTRUMENT_PAYMENT_USE_ID,

  PaymentInstrumentUsesEO.EXT_PMT_PARTY_ID,

  PaymentInstrumentUsesEO.INSTRUMENT_TYPE,

  PaymentInstrumentUsesEO.INSTRUMENT_ID,

  PaymentInstrumentUsesEO.PAYMENT_FUNCTION,

  PaymentInstrumentUsesEO.ORDER_OF_PREFERENCE,

  PaymentInstrumentUsesEO.DEBIT_AUTH_FLAG,

  PaymentInstrumentUsesEO.DEBIT_AUTH_METHOD,

  PaymentInstrumentUsesEO.DEBIT_AUTH_REFERENCE,

  PaymentInstrumentUsesEO.DEBIT_AUTH_BEGIN,

  PaymentInstrumentUsesEO.DEBIT_AUTH_END,

  PaymentInstrumentUsesEO.START_DATE,

  PaymentInstrumentUsesEO.END_DATE,

  ibyextbankaccts.bank_account_id,

  ibyextbankaccts.bank_account_name,

  ibyextbankaccts.bank_account_number,

  ibyextbankaccts.currency_code,

  ibyextbankaccts.bank_name,

  ibyextbankaccts.bank_number,

  ibyextbankaccts.bank_branch_name,

  ibyextbankaccts.bank_account_type,

  ibyextbankaccts.branch_number,

  ibyextbankaccts.iban_number,

  ibyextbankaccts.eft_swift_code,

  'N' detailView,

  PaymentInstrumentUsesEO.PAYMENT_FLOW,

  supplier.vendor_name party_name,

  supplier.vendor_name_alt alt_party_name,

  supplier.segment1 party_number,

  DECODE(supplier.vendor_type_lookup_code, 'EMPLOYEE', NVL(

  (SELECT NVL(ALC.displayed_field, site.VENDOR_SITE_CODE)

  FROM AP_LOOKUP_CODES ALC

  WHERE ALC.lookup_type(+) = 'VENDOR_SITE_CODE'

  AND ALC.lookup_code(+)   = site.VENDOR_SITE_CODE

  ), site.VENDOR_SITE_CODE), site.VENDOR_SITE_CODE) site_name,

  site.vendor_site_code_alt alt_site_name,

  address.party_site_name,

  ou.NAME organization_name,

  DECODE(Payee.supplier_site_id, NULL, DECODE(Payee.org_id, NULL, DECODE(Payee.party_site_id, NULL,

  (SELECT MEANING

  FROM fnd_lookup_values_vl

  WHERE LOOKUP_TYPE = 'IBY_EXT_BNK_ACCT_ASSGN_SRCH'

  AND LOOKUP_CODE   ='SUPPLIER'

  ),

  (SELECT MEANING

  FROM fnd_lookup_values_vl

  WHERE LOOKUP_TYPE = 'IBY_EXT_BNK_ACCT_ASSGN_SRCH'

  AND LOOKUP_CODE   ='ADDRESS'

  )),

  (SELECT MEANING

  FROM fnd_lookup_values_vl

  WHERE LOOKUP_TYPE = 'IBY_EXT_BNK_ACCT_ASSGN_SRCH'

  AND LOOKUP_CODE   ='ADDRESS_OPERATING'

  )),

  (SELECT MEANING

  FROM fnd_lookup_values_vl

  WHERE LOOKUP_TYPE = 'IBY_EXT_BNK_ACCT_ASSGN_SRCH'

  AND LOOKUP_CODE   ='SITE'

  )) assignment_level,

  ibyextbankaccts.ext_bank_account_id ext_bank_account_id,

  ibyextbankaccts.bank_party_id bank_party_id,

  ibyextbankaccts.branch_party_id branch_party_id,

  DECODE(ibyextbankaccts.FOREIGN_PAYMENT_USE_FLAG, 'Y',

  (SELECT meaning

  FROM fnd_lookup_values_vl

  WHERE LOOKUP_TYPE = 'IBY_YES_NO'

  AND lookup_code   = 'Y'

  ),

  (SELECT meaning

  FROM fnd_lookup_values_vl

  WHERE LOOKUP_TYPE = 'IBY_YES_NO'

  AND lookup_code   = 'N'

  )) FOREIGN_PAYMENT_USE_FLAG,

  DECODE(ibyextbankaccts.PRIMARY_ACCT_OWNER_PARTY_ID, Payee.payee_party_id,

  (SELECT meaning

  FROM fnd_lookup_values_vl

  WHERE LOOKUP_TYPE = 'IBY_YES_NO'

  AND lookup_code   = 'Y'

  ),

  (SELECT meaning

  FROM fnd_lookup_values_vl

  WHERE LOOKUP_TYPE = 'IBY_YES_NO'

  AND lookup_code   = 'N'

  )) primary_flag,

  Payee.payee_party_id party_id,

  (SELECT MEANING

  FROM fnd_lookup_values_vl

  WHERE LOOKUP_TYPE = 'IBY_EXT_BNK_ACCT_ASSGN_SRCH'

  AND LOOKUP_CODE   ='SUPPLIER'

  ) party_type

FROM IBY_PMT_INSTR_USES_ALL PaymentInstrumentUsesEO,

  IBY_EXT_BANK_ACCOUNTS_V ibyextbankaccts,

  IBY_EXTERNAL_PAYEES_ALL Payee,

  poz_suppliers_v supplier,

  POZ_SUPPLIER_SITES_V site,

  hz_party_sites address,

  hr_operating_units ou

WHERE PaymentInstrumentUsesEO.instrument_id  = ibyextbankaccts.bank_account_id

AND PaymentInstrumentUsesEO.instrument_type  = 'BANKACCOUNT'

AND PaymentInstrumentUsesEO.payment_function = 'PAYABLES_DISB'

AND PaymentInstrumentUsesEO.EXT_PMT_PARTY_ID = Payee.ext_payee_id

AND Payee.payee_party_id                     = supplier.party_id

AND Payee.supplier_site_id                   = site.vendor_site_id(+)

AND Payee.party_site_id                      = address.party_site_id(+)

AND Payee.org_id                             = ou.organization_id (+)

AND Payee.org_id                            IS NOT NULL

AND Payee.org_type                          IS NOT NULL

/*AND (Payee.org_id, Payee.org_type)          IN

(SELECT uo.organization_id,

uo.organization_type

FROM ce_security_profiles_v uo

)*/

UNION

SELECT PaymentInstrumentUsesEO.INSTRUMENT_PAYMENT_USE_ID,

  PaymentInstrumentUsesEO.EXT_PMT_PARTY_ID,

  PaymentInstrumentUsesEO.INSTRUMENT_TYPE,

  PaymentInstrumentUsesEO.INSTRUMENT_ID,

  PaymentInstrumentUsesEO.PAYMENT_FUNCTION,

  PaymentInstrumentUsesEO.ORDER_OF_PREFERENCE,

  PaymentInstrumentUsesEO.DEBIT_AUTH_FLAG,

  PaymentInstrumentUsesEO.DEBIT_AUTH_METHOD,

  PaymentInstrumentUsesEO.DEBIT_AUTH_REFERENCE,

  PaymentInstrumentUsesEO.DEBIT_AUTH_BEGIN,

  PaymentInstrumentUsesEO.DEBIT_AUTH_END,

  PaymentInstrumentUsesEO.START_DATE,

  PaymentInstrumentUsesEO.END_DATE,

  ibyextbankaccts.bank_account_id,

  ibyextbankaccts.bank_account_name,

  ibyextbankaccts.bank_account_number,

  ibyextbankaccts.currency_code,

  ibyextbankaccts.bank_name,

  ibyextbankaccts.bank_number,

  ibyextbankaccts.bank_branch_name,

  ibyextbankaccts.bank_account_type,

  ibyextbankaccts.branch_number,

  ibyextbankaccts.iban_number,

  ibyextbankaccts.eft_swift_code,

  'N' detailView,

  PaymentInstrumentUsesEO.PAYMENT_FLOW,

  hp.party_name party_name,

  NULL alt_party_name,

  Cust.ACCOUNT_NUMBER party_number,

  NULL site_name,

  NULL alt_site_name,

  NULL party_site_name,

  NULL organization_name,

  DECODE(Payer.ACCT_SITE_USE_ID,NULL,(

  (SELECT MEANING

  FROM fnd_lookup_values_vl

  WHERE LOOKUP_TYPE = 'IBY_EXT_BNK_ACCT_ASSGN_SRCH'

  AND LOOKUP_CODE   ='CUSTOMER'

  )),(

  (SELECT MEANING

  FROM fnd_lookup_values_vl

  WHERE LOOKUP_TYPE = 'IBY_EXT_BNK_ACCT_ASSGN_SRCH'

  AND LOOKUP_CODE   ='SITE'

  ))) assignment_level,

  ibyextbankaccts.ext_bank_account_id ext_bank_account_id,

  ibyextbankaccts.bank_party_id bank_party_id,

  ibyextbankaccts.branch_party_id branch_party_id,

  DECODE(ibyextbankaccts.FOREIGN_PAYMENT_USE_FLAG, 'Y',

  (SELECT meaning

  FROM fnd_lookup_values_vl

  WHERE LOOKUP_TYPE = 'IBY_YES_NO'

  AND lookup_code   = 'Y'

  ),

  (SELECT meaning

  FROM fnd_lookup_values_vl

  WHERE LOOKUP_TYPE = 'IBY_YES_NO'

  AND lookup_code   = 'N'

  )) FOREIGN_PAYMENT_USE_FLAG,

  DECODE(ibyextbankaccts.PRIMARY_ACCT_OWNER_PARTY_ID, Payer.party_id,

  (SELECT meaning

  FROM fnd_lookup_values_vl

  WHERE LOOKUP_TYPE = 'IBY_YES_NO'

  AND lookup_code   = 'Y'

  ),

  (SELECT meaning

  FROM fnd_lookup_values_vl

  WHERE LOOKUP_TYPE = 'IBY_YES_NO'

  AND lookup_code   = 'N'

  )) primary_flag,

  Payer.party_id party_id,

  (SELECT MEANING

  FROM fnd_lookup_values_vl

  WHERE LOOKUP_TYPE = 'IBY_EXT_BNK_ACCT_ASSGN_SRCH'

  AND LOOKUP_CODE   ='CUSTOMER'

  ) party_type

FROM IBY_PMT_INSTR_USES_ALL PaymentInstrumentUsesEO,

  IBY_EXT_BANK_ACCOUNTS_V ibyextbankaccts,

  IBY_EXTERNAL_PAYERS_ALL Payer,

  HZ_CUST_ACCOUNTS Cust,

  hz_parties hp

WHERE PaymentInstrumentUsesEO.instrument_id  = ibyextbankaccts.bank_account_id

AND PaymentInstrumentUsesEO.instrument_type  = 'BANKACCOUNT'

AND PaymentInstrumentUsesEO.payment_function = 'CUSTOMER_PAYMENT'

AND PaymentInstrumentUsesEO.EXT_PMT_PARTY_ID = Payer.ext_payer_id

AND Payer.party_id                           = Cust.party_id

AND Payer.CUST_ACCOUNT_ID                    = Cust.CUST_ACCOUNT_ID

AND Cust.party_id                            = hp.party_id

AND Payer.org_id                            IS NULL

AND Payer.org_type                          IS NULL

UNION

SELECT PaymentInstrumentUsesEO.INSTRUMENT_PAYMENT_USE_ID,

  PaymentInstrumentUsesEO.EXT_PMT_PARTY_ID,

  PaymentInstrumentUsesEO.INSTRUMENT_TYPE,

  PaymentInstrumentUsesEO.INSTRUMENT_ID,

  PaymentInstrumentUsesEO.PAYMENT_FUNCTION,

  PaymentInstrumentUsesEO.ORDER_OF_PREFERENCE,

  PaymentInstrumentUsesEO.DEBIT_AUTH_FLAG,

  PaymentInstrumentUsesEO.DEBIT_AUTH_METHOD,

  PaymentInstrumentUsesEO.DEBIT_AUTH_REFERENCE,

  PaymentInstrumentUsesEO.DEBIT_AUTH_BEGIN,

  PaymentInstrumentUsesEO.DEBIT_AUTH_END,

  PaymentInstrumentUsesEO.START_DATE,

  PaymentInstrumentUsesEO.END_DATE,

  ibyextbankaccts.bank_account_id,

  ibyextbankaccts.bank_account_name,

  ibyextbankaccts.bank_account_number,

  ibyextbankaccts.currency_code,

  ibyextbankaccts.bank_name,

  ibyextbankaccts.bank_number,

  ibyextbankaccts.bank_branch_name,

  ibyextbankaccts.bank_account_type,

  ibyextbankaccts.branch_number,

  ibyextbankaccts.iban_number,

  ibyextbankaccts.eft_swift_code,

  'N' detailView,

  PaymentInstrumentUsesEO.PAYMENT_FLOW,

  hp.PARTY_NAME party_name,

  NULL alt_party_name,

  Cust.ACCOUNT_NUMBER party_number,

  address.party_site_name site_name,

  NULL alt_site_name,

  address.party_site_name,

  ou.NAME organization_name,

  DECODE(Payer.ACCT_SITE_USE_ID,NULL,(

  (SELECT MEANING

  FROM fnd_lookup_values_vl

  WHERE LOOKUP_TYPE = 'IBY_EXT_BNK_ACCT_ASSGN_SRCH'

  AND LOOKUP_CODE   ='CUSTOMER'

  )),(

  (SELECT MEANING

  FROM fnd_lookup_values_vl

  WHERE LOOKUP_TYPE = 'IBY_EXT_BNK_ACCT_ASSGN_SRCH'

  AND LOOKUP_CODE   ='SITE'

  ))) assignment_level,

  ibyextbankaccts.ext_bank_account_id ext_bank_account_id,

  ibyextbankaccts.bank_party_id bank_party_id,

  ibyextbankaccts.branch_party_id branch_party_id,

  DECODE(ibyextbankaccts.FOREIGN_PAYMENT_USE_FLAG, 'Y',

  (SELECT meaning

  FROM fnd_lookup_values_vl

  WHERE LOOKUP_TYPE = 'IBY_YES_NO'

  AND lookup_code   = 'Y'

  ),

  (SELECT meaning

  FROM fnd_lookup_values_vl

  WHERE LOOKUP_TYPE = 'IBY_YES_NO'

  AND lookup_code   = 'N'

  )) FOREIGN_PAYMENT_USE_FLAG,

  DECODE(ibyextbankaccts.PRIMARY_ACCT_OWNER_PARTY_ID, Payer.party_id,

  (SELECT meaning

  FROM fnd_lookup_values_vl

  WHERE LOOKUP_TYPE = 'IBY_YES_NO'

  AND lookup_code   = 'Y'

  ),

  (SELECT meaning

  FROM fnd_lookup_values_vl

  WHERE LOOKUP_TYPE = 'IBY_YES_NO'

  AND lookup_code   = 'N'

  )) primary_flag,

  Payer.party_id party_id,

  (SELECT MEANING

  FROM fnd_lookup_values_vl

  WHERE LOOKUP_TYPE = 'IBY_EXT_BNK_ACCT_ASSGN_SRCH'

  AND LOOKUP_CODE   ='CUSTOMER'

  ) party_type

FROM IBY_PMT_INSTR_USES_ALL PaymentInstrumentUsesEO,

  IBY_EXT_BANK_ACCOUNTS_V ibyextbankaccts,

  IBY_EXTERNAL_PAYERS_ALL Payer,

  HZ_CUST_ACCOUNTS Cust,

  HZ_CUST_ACCT_SITES_ALL site,

  HZ_CUST_SITE_USES_ALL site_use,

  hz_party_sites address,

  hr_operating_units ou,

  hz_parties hp

WHERE PaymentInstrumentUsesEO.instrument_id  = ibyextbankaccts.bank_account_id

AND PaymentInstrumentUsesEO.instrument_type  = 'BANKACCOUNT'

AND PaymentInstrumentUsesEO.payment_function = 'CUSTOMER_PAYMENT'

AND PaymentInstrumentUsesEO.EXT_PMT_PARTY_ID = Payer.ext_payer_id

AND Payer.party_id                           = Cust.party_id

AND Payer.CUST_ACCOUNT_ID                    = Cust.CUST_ACCOUNT_ID

AND Cust.party_id                            = hp.party_id

AND Payer.ACCT_SITE_USE_ID                   = site_use.site_use_id(+)

AND site_use.cust_acct_site_id               = site.cust_acct_site_id(+)

AND site.party_site_id                       = address.party_site_id(+)

AND Payer.org_id                             = ou.organization_id (+)

AND Payer.org_id                            IS NOT NULL

AND Payer.org_type                          IS NOT NULL

  /*AND (Payer.org_id, Payer.org_type)          IN

  (SELECT uo.organization_id,

  uo.organization_type

  FROM ce_security_profiles_v uo

  )*/

) where

PARTY_NAME = :bind_partyName

or bank_account_name = :bind_bankAccountName

or bank_account_number = :bind_bankAccountNumber

or party_number = :bind_partyNumber