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

Document sequence tables in oracle apps

In this post , We will discuss about Document sequence tables in oracle apps. Document sequence helps to assign the number to Oracle transactions which occurs more than once, by numbering them you can identify the order of its generation. It acts like a chain and if a number goes missing, the link is broken and you can see that there was a deletion or removal of the transaction. Document sequence tables in oracle apps stores the unique sequence for each transaction based on the category and then after that, system refer that sequence number for generating numbers for that category transactions. In general business practice we create the document sequences for each transaction every year for audit point of view. Here below I will share the detail description of Document sequence tables in oracle apps.

 Document sequence tables in oracle apps

FND_DOCUMENT_SEQUENCES

FND_DOC_SEQUENCE_ASSIGNMENTS

FND_DOC_SEQUENCE_ASSIGNMENTS

Enter a valid business unit.(AP-810247) Create Invoices In Spreadsheet Second Line Ends In Error Business Unit ADFDI Does Not Allow Multi line Invoices

Issue steps

1.Select Business Unit field value on line 1.

2.Changed the header identifier on line 2 to "1"

3.Business Unit field value is not copied from line1 to line2

4.When double-click "Distribution Combinations" field in line2,system shows error.

Solutions

SOLUTION

Excel's Macros Setting is not "Enable all macros".

This is the reason why Business Unit field value on line 1 is not copied to Business Unit field value on line2 automatically

Please set Macros Settings in Excel "Enable all macros" by following steps;

1.Set Macros Settings in Excel "Enable all macros" and Close Excel

2.Re-open Excel and Select Business Unit field value on line 1.

3.Changed the header identifier on line 2 to "1"

4.Business Unit field value on line 1 is copied to Business Unit field value on line2 automatically

  and we can enter Distribution Combinations value without error.

Query to get list of Data Accesses of users – Oracle Fusion

    Query to get list of Data Accesses of users – Oracle Fusion

The below query extracts the list of data accesses given for any user in Oracle Cloud Application

————————————————–***************——————————————–

SELECT gl.name "Security Context Value", pr.role_name"Job Role Name", pu.username "User Name", role.ACTIVE_FLAG "Is Data Access Active"

,'DATA ACCESS SET' "Security Context"

FROM fusion.FUN_USER_ROLE_DATA_ASGNMNTS role

,fusion.gl_access_sets gl

,fusion.per_users pu

,fusion.per_roles_dn_vl pr

WHERE gl.ACCESS_SET_ID = role.ACCESS_SET_ID

AND pu.USER_GUID = role.USER_GUID

AND pr.ROLE_COMMON_NAME = role.ROLE_NAME

UNION

SELECT bu.bu_name "Security Context Value", pr.role_name"Job Role Name", pu.username "User Name", role.ACTIVE_FLAG "Is Data Access Active"

,'BUSINESS UNIT' "Security Context"

FROM fusion.FUN_ALL_BUSINESS_UNITS_V bu

,fusion.FUN_USER_ROLE_DATA_ASGNMNTS role

,fusion.per_users pu

,fusion.per_roles_dn_vl pr

WHERE role.org_id = bu.bu_id

AND pu.USER_GUID = role.USER_GUID

AND pr.ROLE_COMMON_NAME = role.ROLE_NAME

UNION

SELECT led.NAME "Security Context Value", pr.role_name "Job Role Name", pu.username "User Name", role.ACTIVE_FLAG "Is Data Access Active"

,'LEDGERS' "Security Context"

FROM fusion.GL_LEDGERS led,

fusion.FUN_USER_ROLE_DATA_ASGNMNTS role,

fusion.per_users pu,

fusion.per_roles_dn_vl pr

WHERE role.LEDGER_ID = led.LEDGER_ID

AND pu.USER_GUID = role.USER_GUID

AND pr.ROLE_COMMON_NAME = role.ROLE_NAME

UNION

SELECT book.book_type_name "Security Context Value", pr.role_name "Job Role Name", pu.username "User Name", role.ACTIVE_FLAG "Is Data Access Active"

,'ASSET BOOK' "Security Context"

FROM fusion.FUN_USER_ROLE_DATA_ASGNMNTS role,

fusion.FA_BOOK_CONTROLS book,

fusion.per_users pu,

fusion.per_roles_dn_vl pr

WHERE book.BOOK_CONTROL_ID = role.book_id

AND pu.USER_GUID = role.USER_GUID

AND pr.ROLE_COMMON_NAME = role.ROLE_NAME

UNION

SELECT interco.INTERCO_ORG_NAME "Security Context Value", pr.role_name "Job Role Name", pu.username "User Name", role.ACTIVE_FLAG "Is Data Access Active"

,'INTERCOMPANY ORGANIZATION' "Security Context"

FROM fusion.FUN_USER_ROLE_DATA_ASGNMNTS role,

fusion.FUN_INTERCO_ORGANIZATIONS interco,

fusion.per_users pu,

fusion.per_roles_dn_vl pr

WHERE interco.INTERCO_ORG_ID= role.INTERCO_ORG_ID

AND pu.USER_GUID = role.USER_GUID

AND pr.ROLE_COMMON_NAME = role.ROLE_NAME

UNION

SELECT cost.COST_ORG_NAME "Security Context Value", pr.role_name "Job Role Name", pu.username "User Name", role.ACTIVE_FLAG "Is Data Access Active"

,'COST ORGANIZATION' "Security Context"

FROM fusion.FUN_USER_ROLE_DATA_ASGNMNTS role,

fusion.CST_COST_ORGS_V cost,

fusion.per_users pu,

fusion.per_roles_dn_vl pr

WHERE cost.COST_ORG_ID= role.CST_ORGANIZATION_ID

AND pu.USER_GUID = role.USER_GUID

AND pr.ROLE_COMMON_NAME = role.ROLE_NAME

UNION

SELECT mfg.DEF_SUPPLY_SUBINV "Security Context Value", pr.role_name "Job Role Name", pu.username "User Name", role.ACTIVE_FLAG "Is Data Access Active"

,'MANUFACTURING PLANT' "Security Context"

FROM fusion.FUN_USER_ROLE_DATA_ASGNMNTS role,

fusion.RCS_MFG_PARAMETERS mfg,

fusion.per_users pu,

fusion.per_roles_dn_vl pr

WHERE mfg.ORGANIZATION_ID= role.MFG_ORGANIZATION_ID

AND pu.USER_GUID = role.USER_GUID

AND pr.ROLE_COMMON_NAME = role.ROLE_NAME

UNION

SELECT budget.NAME "Security Context Value", pr.role_name "Job Role Name", pu.username "User Name", role.ACTIVE_FLAG "Is Data Access Active"

,'CONTROL BUDGET' "Security Context"

FROM fusion.FUN_USER_ROLE_DATA_ASGNMNTS role,

fusion.XCC_CONTROL_BUDGETS budget,

fusion.per_users pu,

fusion.per_roles_dn_vl pr

WHERE budget.CONTROL_BUDGET_ID = role.CONTROL_BUDGET_ID

AND pu.USER_GUID = role.USER_GUID

AND pr.ROLE_COMMON_NAME = role.ROLE_NAME

UNION

SELECT st.SET_NAME "Security Context Value", pr.role_name "Job Role Name", pu.username "User Name", role.ACTIVE_FLAG "Is Data Access Active"

,'REFERENCE DATA SET' "Security Context"

FROM fusion.FUN_USER_ROLE_DATA_ASGNMNTS role,

fusion.FND_SETID_SETS_VL st,

fusion.per_users pu,

fusion.per_roles_dn_vl pr

WHERE st.SET_ID = role.SET_ID

AND pu.USER_GUID = role.USER_GUID

AND pr.ROLE_COMMON_NAME = role.ROLE_NAME

UNION

SELECT inv.ORGANIZATION_CODE "Security Context Value", pr.role_name "Job Role Name", pu.username "User Name", role.ACTIVE_FLAG "Is Data Access Active"

,'INVENTORY ORGANIZATION' "Security Context"

FROM fusion.FUN_USER_ROLE_DATA_ASGNMNTS role,

fusion.INV_ORG_PARAMETERS inv,

fusion.per_users pu,

fusion.per_roles_dn_vl pr

WHERE inv.ORGANIZATION_ID = role.INV_ORGANIZATION_ID

AND pu.USER_GUID = role.USER_GUID

AND pr.ROLE_COMMON_NAME = role.ROLE_NAME

UNION

SELECT hr.CLASSIFICATION_CODE "Security Context Value", pr.role_name "Job Role Name", pu.username "User Name", role.ACTIVE_FLAG "Is Data Access Active"

,'PROJECT ORGANIZATION CLASSIFICATION' "Security Context"

FROM fusion.FUN_USER_ROLE_DATA_ASGNMNTS role,

fusion.HR_ORG_UNIT_CLASSIFICATIONS_F hr,

fusion.per_users pu,

fusion.per_roles_dn_vl pr

WHERE hr.ORG_UNIT_CLASSIFICATION_ID = role.ORG_ID

AND pu.USER_GUID = role.USER_GUID

AND pr.ROLE_COMMON_NAME = role.ROLE_NAME

Unable to view Value Set Values in Oracle EBS R12.2:

 Unable to view Value Set Values in Oracle EBS R12.2:

Login as SYSADMIN and navigate to 'User Management' responsibility.

Query the user that you want to grant access to all value sets > and grant the following role
 •Role = Flexfield Value Set Security: All privileges
 •Code = UMX|FND_FLEX_VSET_ALL_PRIVS_ROLE

Note: This role provides view, insert and update privileges for the values of all independent and dependent value sets for flexfields and report parameters. This role should ONLY be assigned to users for backwards compatibility. The result of having this role is that if the user has access to the Values form (Key Flexfield, Descriptive Flexfield, and Validation) on a menu, the user can modify all values for all flexfields and report parameters.

How to enter swift code in bank setup? Oracle Application/ Fusion Application

 SWIFT Code is entered at bank branch level and it should be entered in the field "Bank Identification Code (BIC)" at Bank Branch only and not Bank Account.

Reporting in Financial Reporting Compliance

 

Reporting in Financial Reporting Compliance

In Financial Reporting Compliance provides a set of predefined reports organized into five categories.

Assessment Reports

• Assessment Details Reports - displays information about assessment conducted against selected objects.
• Control Assessment Report - lists controls and their related assessment activities in PDF format
• Control Assessment Extract - lists controls and their related assessment activities in Excel Format

Control Reports

• Control Details Report

Issue Reports

• Issue Details Report - provides information about selected issues, including the object against which each issue is raised, issue status and state, users who created or updated and when they did so, and other values.
• Issue Details Extract - provides similar information for export to an application such as Excel.

Risk Report

• Risk Control Matrix Report - lists risks, controls and related information: perspectives and other values
• Risk Control Matrix Extract - lists risks, controls and related information for export to Excel Format

Administration Reports

• Change history Report
• Pending Worklist Items Report
• Related Objects report
• Worklist Item Requiring Reassignment

Activating Email Alerts

Setup e-mail messaging in Financial Reporting Compliance users when tasks require their attention.

1. select the Enable check box in the E-Mail Alerts region
2. Select the test connection button to view a message that connectivity with your email server is established.
3. Create an email alert schedule

Overview of Receivables Invoicing Rules in Oracle Fusion Applications

 

Overview of Receivables Invoicing Rules in Oracle Fusion Applications

Invoicing Rules are used to determine when to recognize your receivable for invoices that span more than one accounting period. You can only assign one invoicing rule to an invoice. Receivables provides the following invoicing rules:

Bill In Advance: Use this rule to recognize your receivable immediately.

Bill In Arrears: Use this rule if you want to record the receivable at the end of the revenue recognition schedule.

To use Invoicing Rules create an Invoice with the field populated:

After creating your Invoice and assigning an Invoicing Rule, Run Recognize Revenue Program from the Scheduled Processes page. This will create Invoices dated in a future date depending on the Invoicing Rule you have specified.

Also, ensure that the Invoicing Rule setup in Import Information section of "Manage Transaction Sources" task is correctly configured.

Revenue Recognition and Invoicing Rules explained

Revenue recognition principle is an important accounting principle, which is the main difference between cash basis accounting and accrual basis accounting. In cash basis accounting revenues are simply recognized when cash is received no matter when and how the services were performed or goods delivered. In accrual basis accounting revenues are recognized when they are (1) realized or realizable and (2) earned no matter when cash is received.

Invoicing Rules and Accounting Rules:

In Oracle AR, the invoicing and accounting rules help create invoices that span several accounting periods. Accounting rules determine the accounting period or periods in which the revenue distributions for an invoice line are recorded. Invoicing rules determine the accounting period in which the receivable amount is recorded.

Accounting Rules:


Accounting rules determines revenue recognition schedules for invoice lines. Different accounting rules can be assigned to each invoice line. Using Accounting rules, the number of periods and the percentage of the total revenue to recognize in each period can be specified. Also accounting rules can be Fixed or Variable Duration.

Clients can also create rules that will defer revenue to an unearned revenue account. This helps in the delay of specifying the revenue recognition schedule until the exact details are known. When these details are known, clients use the Actions wizard to recognize the revenue.

Creating Accounting Entries:
• Accounting distributions are created only after the Revenue Recognition program is run.
• For Bill in Advance, the offset account to accounts receivable is Unearned Revenue.
• For Bill in Arrears, the offset account to accounts receivable is Unbilled Receivables.
• Accounting distributions are created for all periods when Revenue Recognition is run.

Running The Revenue Recognition Program:
• The Revenue Recognition program gives control over the creation of accounting entries.
• Submit the Revenue Recognition program manually through the Run Revenue Recognition window.
• The Revenue Recognition program will also be submitted when posting to Oracle GL.
• The program processes revenue by transaction, rather than by accounting period.
• Only new transactions are selected each time the process is run.
Invoicing Rules:

Invoicing rules determines when to recognize receivable for invoices that span more than one accounting period. Clients can only assign one invoicing rule to an invoice. Receivables provides the following invoicing rules:
• Bill In Advance: Use this rule to recognize your receivable immediately.
• Bill In Arrears: Use this rule if you want to record the receivable at the end of the revenue recognition schedule.

Chart of accounts table in oracle fusion

 

Chart of accounts table in oracle fusion

In this post , we will be discuss about chart of accounts table in oracle fusion. Chart of Accounts is related to the Key Flex Fields structure in Oracle fusion. Chart of accounts is uses to create the Accounting Flex fields structure in oracle fusion. Chart of Accounts is the combination of many Segments in oracle and we attach the Value sets with these Chart of Accounts segments in oracle fusion. Here below I am sharing the Chart of accounts table list in oracle fusion.

6 Important Chart of accounts table in oracle fusion

1.FND_ID_FLEX_SEGMENTS

2.FND_ID_FLEX_SEGMENTS_TL

3.FND_ID_FLEX_STRUCTURES_VL 

4.GL_COA_MAPPINGS

5.GL_COA_MAP_ACCOUNT_RULES

6.GL_COA_MAP_SEGMENT_RULES

Sample SQL Query Using Chart of accounts table in oracle fusion

select
 b.FORM_LEFT_PROMPT "Chart Of Account Segment Name",
 b.description,
 a.segment_name "CCA_VALUSET",
 c.ID_FLEX_STRUCTURE_NAME "Chart Of Account Structure Name" ,
 b.application_column_name "Internal Segment"
 from
 apps.FND_ID_FLEX_SEGMENTS a,
 apps.FND_ID_FLEX_SEGMENTS_TL b,
 apps.FND_ID_FLEX_STRUCTURES_VL c
 where
 b.language = 'US'
and c.ID_FLEX_NUM = b.ID_FLEX_NUM
 and c.enabled_flag = 'Y'
and b.application_id = a.application_id
 and b.id_flex_num = a.id_flex_num
 and b.application_column_name = a.application_column_name
 and a.application_id = 101

Enter a valid business unit.(AP-810247) Oracle Fusion AP Invoice Spreadsheet upload issue

Issue has been fixed, After Excel's Macros Setting has not been enabled "Enable all macros". Due to this Business Unit field value on line 1 is not copied to Business Unit field value on line2 automatically

 

Also Follow below steps to update Business Unit in Spreadsheet.

• Re-open Excel and Select Business Unit field value on line 1.
• Changed the header identifier on line 2 to "1"
• Business Unit field value on line 1 is copied to Business Unit field value on line2 automatically and we can enter Distribution Combinations value without error.

 

GL_BALANCES

1. Summary

GL_BALANCES contains actual, budget, and encumbrance balances for detail and summary accounts.

This table stores ledger currency, foreign currency, and statistical balances for each accounting period that has ever been opened.

ACTUAL_FLAG is either ‘A’, ‘B’, or ‘E’ for actual, budget, or encumbrance balances, respectively. If ACTUAL_FLAG is ‘E’, then ENCUMBRANCE_TYPE_ID is required.

2. Balances Calculations

GL_BALANCES stores period activity for an account in the PERIOD_NET_DR and PERIOD_NET_CR columns.

 The table stores the period beginning balances in BEGIN_BALANCE_DR and BEGIN_BALANCE_CR.

An account’s year-to-date balance is calculated as BEGIN_BALANCE_DR – BEGIN_BALANCE_CR + PERIOD_NET_DR – PERIOD_NET_CR.

Example

decode(map.account_type,’Asset’,greatest(sum(period1.begin_balance_dr + period1.period_net_dr)-sum(period1.begin_balance_cr + period1.period_net_cr),0),’Liability’,(greatest(sum(period1.begin_balance_dr + period1.period_net_dr)-sum(period1.begin_balance_cr + period1.period_net_cr),0)),q'[Ownership/Stockholder’s Equity]’,(greatest(sum(period1.begin_balance_dr + period1.period_net_dr)-sum(period1.begin_balance_cr + period1.period_net_cr),0)),null) “Ledger Debit Amount”,

decode(map.account_type,’Asset’,greatest(sum(period1.begin_balance_cr + period1.period_net_cr)-sum(period1.begin_balance_dr + period1.period_net_dr),0),’Liability’,(greatest(sum(period1.begin_balance_cr + period1.period_net_cr)-sum(period1.begin_balance_dr + period1.period_net_dr),0)),q'[Ownership/Stockholder’s Equity]’,(greatest(sum(period1.begin_balance_cr + period1.period_net_cr)-sum(period1.begin_balance_dr + period1.period_net_dr),0)),null) “Ledger Credit Amount”,

3. Foreign Currency Transactions or Journals

Detail and summary foreign currency balances that are the result of posted foreign currency journal entries have TRANSLATED_FLAG set to ‘R’, to indicate that the row is a candidate for revaluation.

For foreign currency rows, the begin balance and period net columns contain the foreign currency balance, while the begin balance and period net (_BEQ) columns contain the converted ledger currency balance.

Summary foreign currency balances that are the result of foreign currency translation have TRANSLATED_FLAG set to NULL. All summary account balances have TEMPLATE_ID not NULL.

Example

For US Company whose functional currency is USD, and if transaction (AP Invoice) is entered is in INR. Here entered Dr and Entered Cr are in INR and Accounted Dr and Cr is in USD.  (Context is for Liability account)

In GL Balances there will be two rows for this liability account, one for currency INR and other for currency USD.

A row with INR (Foreign Currency) will have TRANSLATED_FLAG set to ‘R’. For foreign currency (INR) rows, the begin balance and period net columns contain the foreign currency balance, while the begin balance and period net (_BEQ) columns contain the converted ledger currency balance (USD).

A row with USD (Functional Currency) will have TRANSLATED_FLAG set to NULL

4. Foreign Currency Translation

Detail foreign currency balances that are the result of foreign currency translation have TRANSLATED_FLAG set to ‘Y’ or ‘N’. ‘N’ indicates that the translation is out of date (i.e., the account needs to be re-translated). ‘Y’ indicates that the translation is current.

Summary foreign currency balances that are the result of foreign currency translation have TRANSLATED_FLAG set to NULL. All summary account balances have TEMPLATE_ID not NULL.

The columns that end in _ADB are not used. Also, the REVALUATION_STATUS column is not used.

Rapid implementation in Oracle Fusion-How to create

Oracle fusion provides option to create rapid implementation for financial, SCM and some other modules .Some of the basic setup you can perform very quickly with the help of predefined spreadsheet template in Oracle fusion

today we will discuss how we can perform Rapid implementation in oracle fusion financial

With Rapid implementation functionality you can create

1.A chart of accounts, hierarchies, document sequencing
2.key account assignments, a fiscal calendar, account defaulting, and financial sequencing.
3 Primary ledgers and business units with assignment of all
4.business functions to use with your subledgers.

Navigate to –

Navigate to: Setup and Maintenance > All Tasks > Search > %Rapid Implementation%
Search > Expand the Define Common Financials Configuration for Rapid Implementation

> All Tasks > Search >

Search > %Rapid Implementation%

in your search query you have to select the below option-Define Common Financial Configuration for Rapid Implementation

Once you click on this you have two option to create your rapid implementation. Two steps you need to follow here .First you need to create template and then need to upload the template to Oracle Fusion ERP

STEP 1-Create Chart of Accounts, Ledger, Legal Entities, and Business Units in Spreadsheet

Step 2-Upload Chart of Accounts

Step 3-Upload Ledger, Legal Entities, and Business Units

In the first step once you click on create option -one Rapid implementation spreadsheet template will be downloaded. In this template you to create with all your setup data and convert this to a CSV Files

The template will be look like this

you can see all the predefined tab available to decide and enter all your setup components

Perform following steps to Create Chart of Accounts, Ledger, Legal Entities, and Business Units

For complete rapid implementation process pls check the video session here

1.Enter the data into the sheets.

2.Validate the data in the sheet

3.Convert the data in to CSV Files. Two CSV Files will be create one for Chart of Accounts and another one for ledger, legal entities and business Unit

4.First upload the CSV File for chart of Account to Oracle fusion through -Upload Chart of account option

5.Run the deployment Chart Of Account Process and wait for this program to complete successfully

6.Upload the Ledger, Legal Entity and Business Units provided the first file uploaded successfully

Before preparing and uploading your data please determine the below components properly and then plan to upload

1.Design your chart of accounts segments after analyzing your client business requirement

2.Finalize segment values and hierarchies data like parent values and child value. You have to decide this after discussing with business users and be sure to capture all the accounts values in proper format.

3.Identify your legal entities, addresses, and business units .How many you need to create for your client

4.Determine your key accounts that are used to track accounting activities, for example, retained earnings,
cash, accounts receivable, accounts payable, and revenue.

5.Determine and plan your calendar start date.

6.Finalize your primary balancing segment value, legal entity, and business unit relationships..

7. Plan your document and journal sequencing requirements.

Unable to Create Supplier and its sites for business unit in Oracle Fusion Message: You can maintain sites only if you're a procurement agent with access to manage suppliers.

After Assigned Supplier Manager Abstract role to user ( enhj-test environment)

Supplier Manager ORA_POZ_SUPPLIER_MANAGER_ABSTRACT

Now User tried logged with User ID and Created New Supplier and Address as per attached screen hot, but unable to Create Site, please suggest what is next action

Message: You can maintain sites only if you're a procurement agent with access to manage suppliers.

supplier sites and still unable to see supplier site. Please see below the navigation and screen shots:

Login as krafique66 ---> Click Navigator ---> Procurement ---> Click Create Supplier

Enter Supplier Profile Detail and Click Save
Enter Address and Click Save
When I Click Sites Tab "Getting an address must exit before creating site

Solution:

The message regarding Address is misleading; User was not assigned as Procurement Agent.

1. Ensure user is setup as Procurement Agent:

1.1 Navigate: Setup and Maintenance


1.2 Search and select task: Manage Procurement Agents


1.3 Click on 'Create' (Plus sign "+" icon)

Create Agent

1.4 On Create Procurement Agent page, select a Procurement BU


1.5 Search and enter an Agent (the Agent will be the employee created for the same BU/LE).


1.6 Under Agent Access region, check that the Agent has the "Manage Suppliers" Action allowed.

Manage Supplier Privilege


1.7 Save.


2. Logout and login.

3. Navigate back to Edit Supplier page.

4. Click Sites tab.

5. Observe that the "+" icon to add a record is now available.