GL - Oracle Fusion Accounting Scripts

Query to fetch the Sub-Ledger Application and its mapping set in Oracle Cloud Fusion

  SELECT xst.application_name,

         xst.application_id,

         xst.description,

         xmsb.mapping_set_code,

         xmsb.mapping_set_id,

         xmsb.amb_context_code,

         xmsb.enabled_flag ms_enabled_flag,

         xmsb.updated_flag,

         xmsb.last_updated_by,

         xmsb.last_update_date,

         xmsv.mapping_set_value_id,

         xmsv.input_value_constant1,

         xmsv.value_constant,

         xmsv.effective_start_date,

         xmsv.effective_end_date,

         xmsv.enabled_flag

    FROM xla_subledgers_tl    xst,

         xla_mapping_sets_b   xmsb,

         xla_mapping_set_values xmsv

   WHERE     1 = 1

         AND xst.application_id = xmsb.application_id

         AND xmsb.application_id = xmsv.application_id

         AND xmsb.mapping_set_code = xmsv.mapping_set_code

         AND xmsb.amb_context_code = xmsv.amb_context_code

         AND xst.application_name = '<SUB_LEDGER_APPLICATION>'

         AND xst.language = 'US'

ORDER BY xst.application_name, xmsb.mapping_set_code

Query to fetch the GL Accounting Flex Fields Hierarchy Structure. Provided the example for the Company Hierarchy

SELECT tree.tree_structure_code,

       tree.tree_code,

       tree.tree_node_id,

       tree.tree.tree_version_id,

       tree.parent_pk1_value parent,

       tree.pk1_start_value child,

       tree.creation_date,

       tree.created_by,

       tree.last_updated_by,

       tree.last_update_date

  FROM fnd_tree_node tree

 WHERE     tree.tree_structure_code = 'GL_ACCT_FLEX' -- <Provide GL Account Structure Code>

       AND tree.tree_code = 'Company Hierarchy' --<Provide Hierarchy either it can be Company/Account/Dept/Cost_Center>

       AND tree.tree_version_id =

              (SELECT tree_version_id

                 FROM fnd_tree_version_vl

                WHERE tree_code = 'Company Hierarchy'

                      AND SYSDATE BETWEEN effective_start_date AND effective_end_date)

START WITH tree.parent_tree_node_id IS NULL

CONNECT BY PRIOR tree_node_id = parent_tree_node_id

Query to fetch the Accounting Period Status for all applications

SELECT fa.application_short_name,

       fa.application_id,

       fat.application_name,

       gps.set_of_books_id,

       gps.period_name,

       gps.ledger_id,

       gl.name,

       gps.created_by,

       gps.creation_date,

       gps.period_num,

       gps.period_year,

       gps.closing_status,

       DECODE (gps.closing_status,

               'O', 'Open',

               'C', 'Closed',

               'F', 'Future',

               'N', 'Never',

               gps.closing_status)

          gl_status,

       gps.start_date,

       gps.end_date,

       gps.last_updated_by,

       gps.last_update_date

  FROM fnd_application    fa,

       fnd_application_tl fat,

       gl_period_statuses gps,

       gl_ledgers         gl

 WHERE     1 = 1

       AND fat.language = 'US'

       AND fa.application_id = fat.application_id

       AND fa.application_id = gps.application_id

       AND gps.ledger_id = gl.ledger_id

AR - Oracle Fusion AR Customer Tables and Scripts

Base Tables Descriptions

HZ_PARTIES Foundational part of Oracle’s Trading Community

Architecture (TCA). Trading Partner can be on 3 types

•  Person (e.g., Jane Doe)

•  Organization (e.g., Oracle Corporation)

•  Group (e.g., World Wide Web Consortium)

HZ_PARTY_SITES Location details like the Address related to a party

HZ_CUST_ACCOUNTS contains details about customer accounts or business relationships that the deploying company has formed with parties classified as either Organizations or Individuals

HZ_LOCATIONS Postal Address, physical location of party site.

HZ_ORIG_SYSTEMS_B Source systems are systems that load data into the

HZ_ORIG_SYSTEMS_TL TCA Registry

HZ_ORIG_SYS_REFERENCES source system references details stored.

HZ_RELATIONSHIPS Relationship between the party and the contact person details.

HZ_CUST_ACCT_SITES_ALL Store Customer Site against the Oracle Business Unit.

HZ_CUST_SITE_USES_ALL Purpose of the Site either Bill-To , Ship-To , Dunning Letter.

HZ_CUST_ACCOUNT_ROLES A role or function that a party performs for a customer account.

HZ_CONTACT_POINTS Communication to the contact person either by Phone, Website URL, or Email.

HZ_GEOGRAPHIES Stores Geography detail and its Address format.

1. Customer Account and Addresses - Query to fetch Customer details at Account, Address, Sites, and Site Uses.

SELECT hca.cust_account_id,

       hca.party_id                    cust_acct_party_id,

       hca.account_number,

       hca.orig_system_reference       acct_orig_sys_ref,

       hp.party_number,

       hp.party_name,

       hp.party_type,

       hp.orig_system_reference        party_orig_sys_ref,

       hcas.cust_acct_site_id,

       hcas.party_site_id,

       hcas.orig_system_reference      acct_site_orig_sys_ref,

       hcas.status                     acct_site_status,

       hcas.set_id                     acct_site_set_id,

       hps.location_id,

       hps.party_id,

       hps.party_site_number,

       hl.orig_system_reference        loc_orig_sys_ref,

       hl.country,

       hl.address1,

       hl.address2,

       hl.address3,

       hl.address4,

       hl.city,

       hl.state,

       hl.postal_code,

       hcsu.site_use_id,

       hcsu.site_use_code,

       hcsu.status                     site_use_status,

       hcsu.location,

       hcsu.set_id                     site_user_set_id,

       acct_osr.orig_system            acct_osr_system,

       acct_osr.orig_system_reference  acct_osr_value,

       party_osr.orig_system           party_osr_system,

       party_osr.orig_system_reference party_osr_value,

       site_osr.orig_system            site_osr_system,

       site_osr.orig_system_reference  site_osr_value,

       loc_osr.orig_system             loc_osr_system,

       loc_osr.orig_system_reference   loc_osr_value

  FROM hz_cust_accounts       hca,

       hz_parties             hp,

       hz_cust_acct_sites_all hcas,

       hz_party_sites         hps,

       hz_locations           hl,

       hz_cust_site_uses_all  hcsu,

       hz_orig_sys_references acct_osr,

       hz_orig_sys_references party_osr,

       hz_orig_sys_references site_osr,

       hz_orig_sys_references loc_osr

 WHERE     hca.party_id = hp.party_id

       AND hca.cust_account_id = hcas.cust_account_id

       AND hcas.party_site_id = hps.party_site_id

       AND hps.location_id = hl.location_id

       AND hcas.cust_acct_site_id = hcsu.cust_acct_site_id

       AND acct_osr.owner_table_name = 'HZ_CUST_ACCOUNTS'

       AND acct_osr.owner_table_id = hca.cust_account_id

       AND party_osr.owner_table_name = 'HZ_PARTIES'

       AND party_osr.owner_table_id = hp.party_id

       AND site_osr.owner_table_name = 'HZ_CUST_ACCT_SITES_ALL'

       AND site_osr.owner_table_id = hcas.cust_acct_site_id

       AND loc_osr.owner_table_name = 'HZ_LOCATIONS'

       AND loc_osr.owner_table_id = hps.location_id

       AND hps.party_site_number = :p_site_number -- Query for Particular Customer Site

       AND hca.account_number = :p_acct_number -- Query for particular Customer Account Number

2.Customer Account and Contact Details - Query to fetch customer details in the account and the contact person details.

SELECT hca.CUST_ACCOUNT_ID,

       hca.PARTY_ID                    cust_acct_party_id,

       hca.ACCOUNT_NUMBER,

       hcas.CUST_ACCT_SITE_ID,

       hcas.PARTY_SITE_ID,

       hps.PARTY_SITE_NUMBER,

       hp.PARTY_NUMBER,

       hp.PARTY_NAME,

       hp.PARTY_TYPE,

       hp.party_id,

       hp.PERSON_FIRST_NAME,

       hp.PERSON_MIDDLE_NAME,

       hp.PERSON_LAST_NAME,

       party_osr.ORIG_SYSTEM           party_osr_system,

       party_osr.ORIG_SYSTEM_REFERENCE party_osr_value

  FROM hz_cust_accounts       hca,

       hz_cust_acct_sites_all hcas,

       hz_party_sites         hps,

       HZ_CUST_ACCOUNT_ROLES  HCAR,

       HZ_RELATIONSHIPS       HR,

       HZ_PARTIES             HP,

       HZ_ORIG_SYS_REFERENCES party_osr

 WHERE     1 = 1

       AND hca.CUST_ACCOUNT_ID = hcas.CUST_ACCOUNT_ID

       AND hcas.PARTY_SITE_ID = hps.PARTY_SITE_ID

       AND hca.cust_account_id = hcar.cust_account_id

       AND hcas.cust_acct_site_id = hcar.cust_acct_site_id

       AND HCAR.RELATIONSHIP_ID = HR.RELATIONSHIP_ID

       AND HP.party_id = HR.Object_id

       AND HR.object_type = 'PERSON'

       AND party_osr.OWNER_TABLE_NAME = 'HZ_PARTIES'

       AND party_osr.OWNER_TABLE_ID = hp.PARTY_ID

       AND hps.PARTY_SITE_NUMBER = :P_SITE_NUMBER

       AND hca.account_number = :P_ACCT_NUMBER -- Query for Customer Account detail

       AND hp.PARTY_NAME = :P_PARTY_NAME    -- Query for Contact Person detail

3. Customer Account and Contact Point Details - Query to fetch customer details in the account, contact person and contact points details

SELECT hca.cust_account_id,

       hca.party_id           cust_acct_party_id,

       hca.account_number,

       hcas.cust_acct_site_id,

       hcas.party_site_id,

       hps.party_site_number,

       hp.party_number,

       hp.party_name,

       hp.party_type,

       hp.party_id,

       hp.person_first_name,

       hp.person_middle_name,

       hp.person_last_name,

       party_osr.orig_system  party_osr_system,

       party_osr.orig_system_reference                     party_osr_value,

       hcp_phone.contact_point_type                        phone_cp_type,

       hcp_phone.contact_point_id                          phone_cp_id,

       hcp_phone.status       phone_status,

       hcp_phone.primary_flag                              phone_primary_flag,

       hcp_phone.phone_area_code || hcp_phone.phone_number phone_no,

       hcp_email.contact_point_type                        email_cp_type,

       hcp_email.contact_point_id                          email_cp_id,

       hcp_email.status       email_status,

       hcp_email.primary_flag                              email_primary_type,

       hcp_email.email_address,

       phone_osr.orig_system  phone_osr_system,

       phone_osr.orig_system_reference                     phone_osr_value,

       email_osr.orig_system  email_osr_system,

       email_osr.orig_system_reference                     email_osr_value,

       cust_osr.orig_system   cust_osr_system,

       cust_osr.orig_system_reference                      cust_osr_value,

       site_osr.orig_system   site_osr_system,

       site_osr.orig_system_reference                      site_osr_value

  FROM hz_cust_accounts       hca,

       hz_cust_acct_sites_all hcas,

       hz_party_sites         hps,

       hz_cust_account_roles  hcar,

       hz_relationships       hr,

       hz_parties             hp,

       hz_contact_points      hcp_email,

       hz_contact_points      hcp_phone,

       hz_orig_sys_references party_osr,

       hz_orig_sys_references phone_osr,

       hz_orig_sys_references email_osr,

       hz_orig_sys_references cust_osr,

       hz_orig_sys_references site_osr

 WHERE     1 = 1

       AND hca.cust_account_id = hcas.cust_account_id

       AND hcas.party_site_id = hps.party_site_id

       AND hca.cust_account_id = hcar.cust_account_id

       AND hcas.cust_acct_site_id = hcar.cust_acct_site_id

       AND hcar.relationship_id = hr.relationship_id

       AND hp.party_id = hr.object_id

       AND hr.object_type = 'PERSON'

       AND hcp_email.owner_table_name(+) = 'HZ_PARTIES'

       AND hcp_email.owner_table_id(+) = hp.party_id

       AND hcp_email.contact_point_type(+) = 'EMAIL'

       AND hcp_phone.owner_table_name(+) = 'HZ_PARTIES'

       AND hcp_phone.owner_table_id(+) = hp.party_id

       AND hcp_phone.contact_point_type(+) = 'PHONE'

       AND party_osr.owner_table_name = 'HZ_PARTIES'

       AND party_osr.owner_table_id = hp.party_id

       AND phone_osr.owner_table_name(+) = 'HZ_CONTACT_POINTS'

       AND phone_osr.owner_table_id(+) = hcp_phone.contact_point_id

       AND email_osr.owner_table_name(+) = 'HZ_CONTACT_POINTS'

       AND email_osr.owner_table_id(+) = hcp_email.contact_point_id

       AND cust_osr.owner_table_name = 'HZ_PARTIES'

       AND cust_osr.owner_table_id = hca.party_id

       AND site_osr.owner_table_name = 'HZ_PARTY_SITES'

       AND site_osr.owner_table_id = hps.party_site_id

       AND hps.party_site_number = :p_site_number

       AND hca.account_number = :p_acct_number

       AND hp.party_name = :p_party_name