Functionality of Segment Value Inheritance ESS Process

 

The Segment Value Inheritance process simplifies the maintenance of the chart of accounts. When the characteristics of values in the value sets are updated then all previously created account code combinations that referenced such values are not automatically updated by these changes.

This process is run to automatically propagate the following attributes of a segment value to all account code combinations which contain that segment value.

- Enable/disable flag
- Effective dates
- Allow budgeting flag
- Allow posting flag

The Segment Value Inheritance process allows you to run a controlled process to update such existing account code combinations. This process maintains and corrects the current attribute settings for those account code combinations that contain the account values that were changed.

For account code combinations where the present settings need to be retained and not impacted by account attribute changes, activate the flag to preserve the account code combination's attribute. Activating the flag prevents those account code combination's attributes from being update when the Segment Value Inheritance process is run.

Note:

• Use the Segment Value Inheritance Exception Report to view the account combinations that have been changed.
• Individual segment value attributes override account combination attributes.
• Conflicting settings for Enabled, Allow Posting, From Date, or To Date are resolved by using the most restrictive of the settings of the individual segment values.
• Disabling a segment value disables the combinations containing that value, even if the Preserve Attributes check box is selected

How to remove access for "Complete" action for AR Transactions for few end users Sandbox

 1) Remove "Complete" action button on AR Transactions for few user on the basis of assigned Roles

2) Provide access to few users to "complete" action for  AR Transactions .

================

I have used below Edit Page component through Sandbox >>EL but "Complete" Button is getting disabled for all users  

#{securityContext.userInRole['AZM_LOG_COM_ACTION']}

AZM_LOG_COM_ACTION - this is dummy role assigned to 2 users only.

Fusion Role Types

 Oracle FAW provides four role types:

Duty roles => Define the duties of a job as an entitlement to perform a particular action, such as access to an AP Transactions subject area.

Data roles => Provide access to the transactional data in the tables. Data roles group the users based on the functional access they have through a particular job role and a particular dimension of data. For example, a group of users based on invoices relevant only to their business unit.

System roles => Provide a set of privileges that allows users to perform system tasks after signing into Oracle Fusion Analytics Warehouse, such as administering system settings, performing functional setup, managing security, and modeling data.

Job roles => Inherit duty roles, data roles, and application roles that are assigned to users. A job role defines a user business function such as Vice President of Sales, Human Resources Analyst, and Procurement Buyer. Job roles and users are synchronized from Oracle Applications Cloud to Oracle Identity Cloud Service. The job roles are mapped as groups in Oracle Identity Cloud Service. You can also create custom job roles based on your business requirements

User Cannot Manually Create Tax For A Credit Memo Yet They Are Able To Create Tax For An Invoice

To implement the solution, please execute the following steps:

1. Navigate to Setup and Maintenance > Setup: Financials > Functional Area: Transaction Tax

2. Task "Manage Configuration Owner Tax Options"

3. Search by the Configuration Owner (Business Unit or Legal Entity depending on your setup) and Event Class as "Credit Memo". If not present create a new entry for the Configuration Owner/Event Class.

4. Verify if the checkbox "Allow entry of manual tax lines" is checked. If not, user will need to end date the current record and create a new one with this checkbox as checked.

5. Retest the issue

How to Default Input VAT tax or any tax from Receivable Transaction Fusion Cloud

How to Default Input VAT tax or any tax from Receivable Transaction 

 

The Manage Application Tax Option is set for product Receivables, for this specific BU, to default the tax classification code S4, based on Receivable system options.

But the setup is not complete: in Manage Receivables System options the Item Validation Organization is snot set.

However, the Tax Classification information is changed and it is trying to default a new one when the transaction line Item/Memo Line/ Description is changed, because the used Transaction Type is checked the Default Tax Classification Code option.

If the business requirement is to not default the Tax Classification code for transaction line, then please uncheck this option for used transaction type:

1. Got to Setup and Maintenance > Financials Setup > Customer billing

2. Manage transaction types task > Search by issue transaction type name

3. Ensure the flag - Default tax classification code is disabled

4. Retest the issue

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>

Detail SQL Query Using Workflow/BPM tables in oracle fusion

Query to extract information related to the PR Purchase requisition BPM approval rules.

select par.rule_id,

par.rule_number,

par.rule_name,

par.DISPLAY_RULE_NAME,

par.description,

par.CONDITIONS_STRING,

par.PRIORITY,

par.active_flag,

pat.LOOKUP_CODE pat_LOOKUP_CODE,

pap.PARTICIPANT_KEY,

paa.ACTION_CODE,

paa.APPROVAL_GROUP_NAME,

pac.condition_number,

pac.attribute_key cond_key,

pac.dimension_key,

pac.operator,

pac.condition_string

from POR_AMX_RULES PAR,

POR_AMX_PARTICIPANTS pap,

POR_AMX_DIMENSIONS   PAD,

POR_AMX_TASKS PAT,

(select RULE_NAME max_rul_name, max(OBJECT_VERSION_NUMBER) max_obj from POR_AMX_RULES group by RULE_NAME) rul_max,

POR_AMX_STAGES   PAS,

POR_AMX_ACTIONS PAA,

POR_AMX_CONDITIONS PAC

where par.active_flag = 'Y'

and par.PARTICIPANT_ID = pap.PARTICIPANT_ID

and par.task_id = pap.task_id

and pas.STAGE_ID = pap.STAGE_ID

and pas.task_id = pap.task_id

and pas.stage_id = pap.stage_id

--and pap.DIMENSION_ID = pad.DIMENSION_ID

and PAT.task_id = pad.task_id

and rul_max.max_obj = par.OBJECT_VERSION_NUMBER

and rul_max.max_rul_name = par.rule_name

and paa.rule_id = par.rule_id

and paa.task_id = par.task_id

and pac.rule_id = paa.rule_id

and pac.task_id = paa.task_id

and pat.task_key = 'ReqApproval'

and pad.TABLE_NAME  = 'POR_REQUISITION_HEADERS_ALL'

BPM Approval (Workflow) Table list in Oracle Fusion

BPM(Workflow) Table list in Oracle Fusion

1. POR_AMX_RULES
2. POR_AMX_PARTICIPANTS
3. POR_AMX_DIMENSIONS
4. POR_AMX_TASKS
5. POR_AMX_STAGES
6. POR_AMX_ACTIONS
7. POR_AMX_CONDITIONS

SQL Query

The sql query below extracts information about the BPM approval rules for Purchase requisitions data

SELECT par.rule_id,
       par.rule_number,
       par.rule_name,
       par.DISPLAY_RULE_NAME,
       par.description,
       par.CONDITIONS_STRING,
       par.PRIORITY,
       par.active_flag,
       pat.LOOKUP_CODE pat_LOOKUP_CODE,
       pap.PARTICIPANT_KEY,
       paa.ACTION_CODE,
       paa.APPROVAL_GROUP_NAME,
       pac.condition_number,
       pac.attribute_key cond_key,
       pac.dimension_key,
       pac.operator,
       pac.condition_string
FROM   POR_AMX_RULES PAR,
       POR_AMX_PARTICIPANTS pap,
       POR_AMX_DIMENSIONS   PAD,
       POR_AMX_TASKS PAT,
       (select RULE_NAME max_rul_name, max(OBJECT_VERSION_NUMBER) max_obj 
        from POR_AMX_RULES 
        group by RULE_NAME) rul_max,
       POR_AMX_STAGES   PAS,
       POR_AMX_ACTIONS PAA,
       POR_AMX_CONDITIONS PAC
WHERE  par.active_flag = 'Y'
   and par.PARTICIPANT_ID = pap.PARTICIPANT_ID
   and par.task_id = pap.task_id
   and pas.STAGE_ID = pap.STAGE_ID
   and pas.task_id = pap.task_id
   and pas.stage_id = pap.stage_id
   and PAT.task_id = pad.task_id
   and rul_max.max_obj = par.OBJECT_VERSION_NUMBER
   and rul_max.max_rul_name = par.rule_name
   and paa.rule_id = par.rule_id
   and paa.task_id = par.task_id
   and pac.rule_id = paa.rule_id
   and pac.task_id = paa.task_id
   and pat.task_key = 'ReqApproval'
   and pad.TABLE_NAME  = 'POR_REQUISITION_HEADERS_ALL';

SQL query to extract data Pending approval transaction for employee transactions data

 select

txnd.transaction_id,

txnd.status,

txnd.state,

txnd.created_by,

txnd.creation_date,

txnd.last_update_date,

txnd.last_updated_by,

txnh.module_identifier,

txnh.subject,

txnh.subject_id,

txnh.object,

txnh.object_id,

txne.Fault_Date,

txne.Error_Details,

wft.TITLE,

wft.OUTCOME as WF_OUTCOME,

wft.STATE as WF_STATE,

Wft.Assignees,

wft.Assigneddate,

wft.Approvers,

wft.compositeinstanceid

from

Fusion.Hrc_Txn_Header Txnh, Fusion.Hrc_Txn_Data Txnd,

fa_fusion_soainfra.wftask wft,

fusion.hrc_txn_error txne

Where txnh.Transaction_Id = txnd.Transaction_Id

and txnh.transaction_id = txne.transaction_id(+)

And To_Char(Txnh.Transaction_Id) = Wft.Identificationkey(+)

and ((txnh.subject_id in (select distinct person_id from per_all_people_f

where person_number in ('1234')

))

or (txnh.subject_id in (select assignment_id from per_all_assignments_m

where assignment_type ='E' and person_id in

(select distinct person_id from per_all_people_f where person_number in

('1234')

)))

or (txnh.subject_id in (select period_of_service_id from

per_periods_of_service where person_id in

(select distinct person_id from per_all_people_f where person_number in

('1234')

))))

SQL query to get current approver and future or next approver for resignation transactions

 SELECT wt.ASSIGNEESDISPLAYNAME next_approver,--hrops_name

wt.FROMUSERDISPLAYNAME current_approver,

txnh.object_id assignment_id,

(

SELECT DISTINCT papf.person_number

FROM per_all_people_f papf,

per_all_assignments_f paaf

WHERE 1 = 1

AND papf.person_id = paaf.person_id

AND sysdate BETWEEN paaf.effective_start_date

AND paaf.effective_end_date

AND paaf.assignment_id = txnh.object_id

) person_number,

(

SELECT to_date(EXTRACTVALUE(xmltype.createxml(DATA_CACHE), '/TRANSACTION/BUSINESSDATA/AM/TXN/EO/PeriodOfServiceEORow/NotifiedTerminationDate/DATA'), 'rrrr-mm-dd')

FROM hrc_txn_data txnd

WHERE 1 = 1

AND LENGTHB(TO_CHAR(SUBSTR(DATA_CACHE, 1, 4000))) <> 0

AND txnd.transaction_id = wt.identificationkey

) NTD,

(

SELECT to_date(EXTRACTVALUE(xmltype.createxml(DATA_CACHE), '/TRANSACTION/BUSINESSDATA/AM/TXN/EO/PeriodOfServiceEORow/ActualTerminationDate/DATA'), 'rrrr-mm-dd')

FROM hrc_txn_data txnd

WHERE 1 = 1

AND LENGTHB(TO_CHAR(SUBSTR(DATA_CACHE, 1, 4000))) <> 0

AND txnd.transaction_id = wt.identificationkey

) ATD,

(

SELECT DISTINCT ppnf.display_name

FROM per_person_names_f ppnf,

per_all_people_f papf,

per_all_assignments_f paaf

WHERE 1 = 1

AND ppnf.person_id = papf.person_id

AND sysdate BETWEEN ppnf.effective_start_date

AND ppnf.effective_end_date

AND ppnf.NAME_TYPE = 'GLOBAL'

AND papf.person_id = paaf.person_id

AND sysdate BETWEEN paaf.effective_start_date

AND paaf.effective_end_date

AND paaf.assignment_id = txnh.object_id

) DISPLAY_NAME,

trunc(CREATEDDATE) trx_date

FROM fa_fusion_soainfra.wftask wt,

hrc_txn_header txnh

WHERE 1 = 1

AND wt.identificationkey = txnh.transaction_id

AND ((ASSIGNMENTCONTEXT LIKE '%Above%'

AND wt.PUSHBACKSEQUENCE <> 'INITIAL_ASSIGNEES;1')

OR (ASSIGNMENTCONTEXT LIKE '%Below%'))

and wt.TASKDEFINITIONNAME = 'ResignationApproval'

AND Txnh.OBJECT = 'PER_ALL_ASSIGNMENTS_M'

AND Txnh.module_identifier IN ('Resignation','Terminations')

AND wt.COMPONENTNAME IN ('ResignationApproval','TerminationsApproval')

AND trunc(wt.UPDATEDDATE) = trunc(sysdate)

AND wt.ENDDATE IS NULL

SQL query to extract from data_cache field on HRC_TRX_DATA with transaction status

 

SELECT

person.person_number,

EXTRACTVALUE (

xmltype ('' || txnD.data_cache || ''),

'TRANSACTION/TransCtx/PreRowAsgCalcAttributeHash/Department')

Department_old,

EXTRACTVALUE (xmltype ('' || txnD.data_cache || ''),

'TRANSACTION/TransCtx/asgCalcAttributeHash/Department')

Department_new,

INITCAP (names.display_name) display_name,

EXTRACTVALUE (

xmltype ('' || txnD.data_cache || ''),

'TRANSACTION/TransCtx/PreRowAsgCalcAttributeHash/BusinessUnit')

BusinessUnit_old,

EXTRACTVALUE (

xmltype ('' || txnD.data_cache || ''),

'TRANSACTION/TransCtx/asgCalcAttributeHash/BusinessUnit')

BusinessUnit_new,

EXTRACTVALUE (

xmltype ('' || txnD.data_cache || ''),

'TRANSACTION/TransCtx/PreRowAsgCalcAttributeHash/Location')

Location_old,

EXTRACTVALUE (xmltype ('' || txnD.data_cache || ''),

'TRANSACTION/TransCtx/asgCalcAttributeHash/Location')

Location_new,

'InProgress' STATUS,

(SELECT per_all_people_f.person_number||'-'||INITCAP (per_person_names_f.display_name)

FROM PER_ASSIGNMENT_SUPERVISORS_F,

per_person_names_f,

per_all_people_f

WHERE PER_ASSIGNMENT_SUPERVISORS_F.manager_id =

per_person_names_f.person_id

AND per_person_names_f.person_id =

per_all_people_f.person_id

AND PER_ASSIGNMENT_SUPERVISORS_F.MANAGER_TYPE =

'LINE_MANAGER'

AND TRUNC (SYSDATE) BETWEEN TRUNC (

PER_ASSIGNMENT_SUPERVISORS_F.EFFECTIVE_START_DATE)

AND TRUNC (

PER_ASSIGNMENT_SUPERVISORS_F.EFFECTIVE_END_DATE)

AND TRUNC (SYSDATE) BETWEEN TRUNC (

per_person_names_f.EFFECTIVE_START_DATE)

AND TRUNC (

per_person_names_f.EFFECTIVE_END_DATE)

AND TRUNC (SYSDATE) BETWEEN TRUNC (

per_all_people_f.EFFECTIVE_START_DATE)

AND TRUNC (

per_all_people_f.EFFECTIVE_END_DATE)

and PER_ASSIGNMENT_SUPERVISORS_F.assignment_id=asg.assignment_id

AND per_person_names_f.name_type = 'GLOBAL'

)curr_reporting,

/* EXTRACTVALUE (xmltype ('' || replace(txnD.data_cache,',','') || ''),

'TRANSACTION/BUSINESSDATA/AM/TXN/EO/PeriodOfServiceEORow/CEO/EO/EmployeeTermsDEORow/CEO/EO/EmployeeAssignmentDEORow/CEO/EO/AssignmentSupervisorDEORow/ManagerName/DATA[1]')

*/null prapose_mgr,

asg.effective_start_date doj,

EXTRACTVALUE (xmltype ('' || txnD.data_cache || ''),

'TRANSACTION/TransCtx/basicInfoAttributes/ReasonName')REASON

FROM hrc_txn_header txnH,

per_all_people_f person,

per_all_assignments_m asg,

per_person_names_f names,

hrc_txn_data txnD,

FA_FUSION_SOAINFRA.WFTASK wft,

per_persons ppos

WHERE UPPER (txnH.Object) IN ('PER_ALL_ASSIGNMENTS_M')

AND txnH.object_id = asg.assignment_id

AND SYSDATE BETWEEN asg.effective_start_date

AND asg.effective_end_date

AND asg.person_id = person.person_id

AND SYSDATE BETWEEN person.effective_start_date

AND person.effective_end_date

AND names.person_id = person.person_id

AND person.person_id = ppos.person_id

AND TO_CHAR (txnh.transaction_id) = wft.identificationkey(+)

AND SYSDATE BETWEEN names.effective_start_date

AND names.effective_end_date

AND names.name_type = 'GLOBAL'

AND txnH.transaction_id = txnD.transaction_id

AND txnD.status = 'PENDING'

AND txnH.MODULE_IDENTIFIER = 'Transfers'

AND person.person_number =nvl(:L_PERSON_NUM,person.person_number)

and (EXTRACTVALUE (

xmltype ('' || txnD.data_cache || ''),

'TRANSACTION/TransCtx/PreRowAsgCalcAttributeHash/BusinessUnit')) =nvl(:L_BU_NAME,(EXTRACTVALUE (

xmltype ('' || txnD.data_cache || ''),

'TRANSACTION/TransCtx/PreRowAsgCalcAttributeHash/BusinessUnit')))

and wft.ASSIGNEESDISPLAYNAME is not null

SQL query to search for Pending Transaction of Employee Assignments changes

 

SELECT
person.person_number||'-'||INITCAP (names.display_name) pp,person.person_number
FROM hrc_txn_header txnH,
per_all_people_f person,
per_all_assignments_m asg,
per_person_names_f names,
hrc_txn_data txnD,
FA_FUSION_SOAINFRA.WFTASK wft,
per_persons ppos
WHERE UPPER (txnH.Object) IN ('PER_ALL_ASSIGNMENTS_M')
AND txnH.object_id = asg.assignment_id
AND SYSDATE BETWEEN asg.effective_start_date
AND asg.effective_end_date
AND asg.person_id = person.person_id
AND SYSDATE BETWEEN person.effective_start_date
AND person.effective_end_date
AND names.person_id = person.person_id
AND person.person_id = ppos.person_id
AND TO_CHAR (txnh.transaction_id) = wft.identificationkey(+)
AND SYSDATE BETWEEN names.effective_start_date
AND names.effective_end_date
AND names.name_type = 'GLOBAL'
AND txnH.transaction_id = txnD.transaction_id
AND txnD.status = 'PENDING'
AND txnH.MODULE_IDENTIFIER = 'Transfers'
and wft.ASSIGNEESDISPLAYNAME is not null

Link Tables Between Workflow Table (FA_FUSION_SOAINFRA.WFTASK) and Projects Table (PJF_PROJECTS_ALL_B)

 1. PROJECT STATUS NOTIFICATION APPROVAL: 

select * from FA_FUSION_SOAINFRA.WFTASK where componentname ='ProjectStatusNotificationApproval'
and identificationkey = (select project_id from pjf_projects_all_b where segment1 = <Project Number>);

2. FORECAST APPROVAL: 

select * from FA_FUSION_SOAINFRA.WFTASK where componentname ='ApprovePlanVersion'
and identificationkey = (select plan_version_id from PJO_PLAN_VERSIONS_B where project_id = (select project_id from pjf_projects_all_b where segment1 = <Project Number>));

 

3. CONTRACT APPROVAL: 

select * from FA_FUSION_SOAINFRA.WFTASK where componentname ='ContractsApproval' and taskid in (select task_id from OKC_K_HEADERS_ALL_B where contract_number = <Contract Number>); 

 

To join the contracts table with the projects table, you need the following SQL:

SELECT OKH.CONTRACT_NUMBER
,OKHT.COGNOMEN AWARD_NAME
,PPAB.SEGMENT1 PROJ_NUM
FROM fusion.OKC_K_HEADERS_ALL_B OKH
, fusion.OKC_K_HEADERS_TL OKHT
, fusion.GMS_AWARD_HEADERS_B GAH
, fusion.GMS_AWARD_PROJECTS GAP
, fusion.PJF_PROJECTS_ALL_B PPAB
WHERE GAH.ID = OKH.ID
AND OKH.ID = OKHT.ID
AND OKH.ID = GAP.AWARD_ID
AND GAP.PROJECT_ID = PPAB.PROJECT_ID
AND OKH.CONTRACT_NUMBER = <Contract Number>;

 

Oracle ERP Cloud SQL Query to find Bank Accounts Associated with a Supplier

/******************************************************************* *PURPOSE: ERP Cloud SQL Query to find Bank Accounts Associated with a Supplier * *AUTHOR: Shailender Thallam * *******************************************************************/ SELECT ps.segment1 AS vendor_num , hzp.party_name AS vendor_name , pssm.attribute20 AS legacy_vendor_number , pssm.vendor_site_code , bank.party_name bank_name , branch.party_name branch_name , ieb.bank_account_name , ieb.bank_account_num , ieb.currency_code , ieb.Bank_Account_Name_alt AS TRANSIT_NUMBER , ieb.Secondary_Account_Reference AS ROUTING_NUMBER FROM poz_suppliers ps , poz_supplier_sites_all_m pssm , iby_external_payees_all payee , iby_pmt_instr_uses_all uses , iby_ext_bank_accounts ieb , hz_parties bank , hz_parties branch , HZ_PARTIES HZP WHERE ps.vendor_id = pssm.vendor_id AND ps.party_id = payee.payee_party_id AND payee.supplier_site_id = pssm.vendor_site_id AND uses.instrument_type = 'BANKACCOUNT' AND payee.ext_payee_id = uses.ext_pmt_party_id AND uses.payment_function = 'PAYABLES_DISB' AND uses.instrument_id = ieb.ext_bank_account_id AND ieb.bank_id = bank.party_id(+) AND ieb.branch_id = branch.party_id(+) AND hzp.party_id = ps.party_id AND SYSDATE BETWEEN NVL(uses.start_date,SYSDATE) AND NVL(uses.end_date,SYSDATE) AND SYSDATE BETWEEN NVL(ieb.start_date,SYSDATE) AND NVL(ieb.end_date,SYSDATE) AND NVL(ps.end_date_active,SYSDATE+1) > TRUNC (SYSDATE) AND NVL(pssm.inactive_date,SYSDATE+1) > TRUNC (SYSDATE)

Supplier bank account query in fusion

 Supplier bank account query in fusion

 This SQL query will help to extract the complete Supplier Banking information's in oracle fusion. 

 Top 10 Important Tables used by Supplier bank account query in fusion

1.POZ_SUPPLIERS_V

2.POZ_SUPPLIER_SITES_V

3.IBY_EXTERNAL_PAYEES_ALL

4.IBY_PMT_INSTR_USES_ALL

5.IBY_EXT_BANK_ACCOUNTS

6.CE_BANK_BRANCHES_V

7.IBY_EXTERNAL_PAYEES_ALL

8.POZ_SUPPLIER_ADDRESS_V

9.POZ_SITE_ASSIGNMENTS_ALL_M

10.IBY_EXT_PARTY_PMT_MTHDS

Detail Supplier bank account query in fusion

SELECT PSV.VENDOR_NAME ,

  PSV.SEGMENT1 ,

  PSV.VENDOR_TYPE_LOOKUP_CODE ,

  PSV.ORGANIZATION_TYPE_LOOKUP_CODE " Organization Type" ,

  TAX_PRF.COUNTRY_CODE "TAXPAYER_COUNTRY",

  PSP.INCOME_TAX_ID "TAXPAYER_ID",

  PSV.FEDERAL_REPORTABLE_FLAG,

  PSV.TYPE_1099 "FEDERAL_INCOME_TAX_TYPE",

  PSV.STATE_REPORTABLE_FLAG STATE_REPORTABLE ,

  TAX_PRF.TAX_CLASSIFICATION_CODE "TAX_CLASSIFICATION_CODE",

  ZXR.TAX_REGIME_CODE "TAX_REGIME_CODE",

  ZXR.TAX "TAX",

  TAX_PRF.REP_REGISTRATION_NUMBER "REGISTRATION_NUMBER",

  ZXR.TAX_JURISDICTION_CODE "TAX_JURISDICTION_CODE",

  PSV.TAX_REPORTING_NAME,

  PSAV.PARTY_SITE_NAME "Supplier Address Name",

  PSAV.ADDRESS1 ,

  PSAV.ADDRESS2 ,

  PSAV.ADDRESS3 ,

  PSAV.ADDRESS4 ,

  PSAV.CITY ,

  PSAV.STATE ,

  PSAV.COUNTY ,

  PSAV.POSTAL_CODE ,

  PSAV.COUNTRY ,

  PSAV.EMAIL_ADDRESS ,

  PSSV.VENDOR_SITE_CODE ,

  TERMS.NAME "PAYMENT_TERMS",

  PSSV.INVOICE_CURRENCY_CODE ,

  PSSV.PAYMENT_CURRENCY_CODE ,

  PSSV.TAX_REPORTING_SITE_FLAG ,

  HP.PARTY_NAME BANK_PARTY_NAME,

  IEBA.COUNTRY_CODE BANK_COUNTRY,

  IEBA.FOREIGN_PAYMENT_USE_FLAG ALLOW_INT,

 IEBA.BANK_ACCOUNT_NUM BANK_ACCOUNT_NUM,

  IEBA.BANK_ACCOUNT_NAME BANK_ACCOUNT_NAME,

  IEBA.CURRENCY_CODE BANK_ACCT_CURRENCY_CODE,

  IEBA.IBAN ,

  EBB.BANK_BRANCH_NAME ,

  EBB.BRANCH_NUMBER 

FROM POZ_SUPPLIERS_V PSV ,

  POZ_SUPPLIERS_PII PSP,

  ZX_PARTY_TAX_PROFILE TAX_PRF,

  FND_LOOKUP_VALUES_TL REGT,

  HZ_CODE_ASSIGNMENTS HCA,

  FND_LOOKUP_VALUES_TL HCAVAL,

  ZX_REGISTRATIONS ZXR,

  POZ_SUPPLIER_SITES_V PSSV,

  POZ_ALL_SUPPLIER_CONTACTS_V PSC,

  HZ_RELATIONSHIPS hr,

  HZ_ORG_CONTACTS hoc,

  IBY_EXTERNAL_PAYEES_ALL IEPA,

  IBY_EXT_PARTY_PMT_MTHDS IEPPM,

  IBY_PMT_INSTR_USES_ALL IPIUA ,

  IBY_EXT_BANK_ACCOUNTS IEBA,

  CE_BANK_BRANCHES_V EBB,

  HZ_PARTIES HP,

  IBY_EXTERNAL_PAYEES_ALL BANK,

  POZ_SUPPLIER_ADDRESS_V PSAV,

  AP_TERMS_TL TERMS,

  POZ_SITE_ASSIGNMENTS_ALL_M PSAA,

  HR_OPERATING_UNITS HRO,

  HR_OPERATING_UNITS HRO1,

  IBY_EXT_PARTY_PMT_MTHDS PAY_METHOD

WHERE PSP.VENDOR_ID(+)               =PSV.VENDOR_ID

AND TAX_PRF.PARTY_ID(+)                =PSV.PARTY_ID

AND TAX_PRF.PARTY_TAX_PROFILE_ID       = HCA.OWNER_TABLE_ID(+)

AND (HCA.OWNER_TABLE_NAME          = 'ZX_PARTY_TAX_PROFILE'

OR HCA.OWNER_TABLE_NAME           IS NULL)

AND HCAVAL.LOOKUP_CODE(+)          =HCA.CLASS_CODE

AND (REGT.LOOKUP_TYPE              ='ZX_REGISTRATIONS_TYPE'

OR REGT.LOOKUP_TYPE               IS NULL)

AND TAX_PRF.REGISTRATION_TYPE_CODE     =REGT.LOOKUP_CODE(+)

AND TAX_PRF.PARTY_TAX_PROFILE_ID       =ZXR.PARTY_TAX_PROFILE_ID(+)

AND PSSV.VENDOR_ID(+)              =PSV.VENDOR_ID

AND PSSV.VENDOR_SITE_ID            = IEPA.SUPPLIER_SITE_ID(+)

AND PSC.SUP_PARTY_ID(+)            = PSV.PARTY_ID

AND hr.subject_id(+)               =PSV.PARTY_ID

AND( hr.relationship_code          = 'CONTACT'

OR hr.relationship_code           IS NULL)

AND (hr.object_table_name          = 'HZ_PARTIES'

OR hr.object_table_name           IS NULL)

AND hoc.party_relationship_id(+)   = hr.relationship_id

AND IEPA.EXT_PAYEE_ID              = IEPPM.EXT_PMT_PARTY_ID(+)

AND ((IEPPM.INACTIVE_DATE         IS NULL)

OR (IEPPM.INACTIVE_DATE            > SYSDATE) )

AND IPIUA.EXT_PMT_PARTY_ID (+)     = IEPA.EXT_PAYEE_ID

AND ( IPIUA.INSTRUMENT_TYPE        = 'BANKACCOUNT'

OR IPIUA.INSTRUMENT_TYPE          IS NULL)

AND ( IPIUA.PAYMENT_FLOW           = 'DISBURSEMENTS'

OR IPIUA.PAYMENT_FLOW             IS NULL)

AND ( IPIUA.ORDER_OF_PREFERENCE    = 1

OR IPIUA.ORDER_OF_PREFERENCE      IS NULL)

AND IPIUA.INSTRUMENT_ID            = IEBA.EXT_BANK_ACCOUNT_ID(+)

AND HP.PARTY_ID (+)                = IEBA.BANK_ID

AND IEBA.BRANCH_ID                 = EBB.BRANCH_PARTY_ID(+)

AND BANK.EXT_PAYEE_ID(+)           =PSV.VENDOR_ID

AND PSAV.LOCATION_ID(+)            =PSSV.LOCATION_ID

AND TERMS.TERM_ID(+)               =PSSV.TERMS_ID

AND PSAA.VENDOR_SITE_ID(+)         =PSSV.VENDOR_SITE_ID

AND HRO.ORGANIZATION_ID(+)         =PSAA.BU_ID

AND HRO1.ORGANIZATION_ID(+)        =PSAA.BILL_TO_BU_ID

AND PAY_METHOD.EXT_PMT_PARTY_ID(+) =BANK.EXT_PAYEE_ID

AND BANK.EXT_PAYEE_ID              =PAY_METHOD.EXT_PMT_PARTY_ID(+)