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.