Query to get Dependent Value Set Values for given Independent Value Set in Oracle APPS

  SELECT ffvs_major.flex_value_set_id   major_flex_value_set_id,

         ffvs_major.flex_value_set_name major_flex_value_set_name,

         ffvs_major.description         major_set_description,

         ffvs_major.validation_type     major_validation_type,

         ffv_major.flex_value           major_flex_value,

         ffvt_major.description         major_value_description,

         ffv_major.enabled_flag         major_enabled_flag,

         ffv_major.last_update_date     major_last_update_date,

         ffv_major.last_updated_by      major_last_updated_by,

         ffv_major.attribute1           major_attribute1,

         ffv_major.attribute2           major_attribute2,

         ffv_major.attribute3           major_attribute3

        ,ffvs_minor.flex_value_set_id   minor_flex_value_set_id,

         ffvs_minor.flex_value_set_name minor_flex_value_set_name,

         ffvs_minor.description minor_set_description,

         ffvs_minor.validation_type minor_validation_type,

         ffv_major.flex_value minor_flex_value,

         ffvt_minor.description minor_value_description,

         ffv_minor.enabled_flag minor_enabled_flag,

         ffv_minor.last_update_date minor_last_update_date,

         ffv_minor.last_updated_by minor_last_updated_by,

         ffv_minor.attribute1 minor_attribute1,

         ffv_minor.attribute2 minor_attribute2,

         ffv_minor.attribute3 minor_attribute3

    FROM fnd_flex_value_sets ffvs_major

        ,fnd_flex_values ffv_major

        ,fnd_flex_values_tl ffvt_major

        ,fnd_flex_value_sets ffvs_minor

        ,fnd_flex_values ffv_minor

        ,fnd_flex_values_tl ffvt_minor

   WHERE ffvs_major.flex_value_set_id = ffv_major.flex_value_set_id

     AND ffv_major.flex_value_id = ffvt_major.flex_value_id

     AND ffvt_major.language = USERENV ('LANG')

     AND UPPER(ffvs_major.flex_value_set_name) = 'XX_MAJOR_CATEGORY_VS'

     AND ffvs_major.flex_value_set_id = ffvs_minor.parent_flex_value_set_id

     AND ffv_major.flex_value = 'XX'

     AND ffv_major.flex_value = ffv_minor.parent_flex_value_low

     AND ffvs_minor.flex_value_set_id = ffv_minor.flex_value_set_id

     AND ffv_minor.flex_value_id = ffvt_minor.flex_value_id

     AND ffvt_minor.language = USERENV ('LANG')

ORDER BY ffv_major.flex_value ASC

Query to get Customer Details in Oracle APPS R12

SELECT DISTINCT ou.name ou_name 

      ,ps.customer_id

  ,party.party_name customer_name

      ,hzca.account_number customer_number

  ,hzca.status customer_status

  ,hcsu.location

  ,hcsu.site_use_code

  ,hcsu.status location_status

  ,ps.class

  ,hcsu.site_use_id

  ,hcpc.name customer_profile_name

  ,hzl.address1 address_line1

  ,hzl.address2 address_line2

  ,hzl.address3 address_line3

  ,hzl.city

  ,hzl.state

  ,hzl.postal_code  

  ,ps.customer_site_use_id

  ,party_site.identifying_address_flag

  ,ps.trx_date

  FROM apps.hz_parties party

      ,apps.hz_party_sites party_site

  ,apps.hz_locations hzl

  ,apps.hz_cust_accounts hzca

  ,apps.hz_cust_acct_sites hcas

  ,apps.hz_cust_site_uses hcsu

  ,apps.hz_customer_profiles cust_profile

  ,apps.hz_cust_profile_classes hcpc

  ,apps.ar_payment_schedules_all ps

  ,apps.hr_operating_units ou 

 WHERE party.party_id = hzca.party_id(+) 

   AND party.party_id = cust_profile.party_id 

   AND party.party_id = party_site.party_id 

   AND party_site.party_site_id = hcas.party_site_id 

   AND party_site.location_id = hzl.location_id 

   AND hzca.cust_account_id = hcas.cust_account_id 

   AND hcas.cust_acct_site_id = hcsu.cust_acct_site_id 

   AND hzca.cust_account_id = cust_profile.cust_account_id 

   AND hzca.cust_account_id = ps.customer_id 

   AND cust_profile.profile_class_id = hcpc.profile_class_id 

   AND ps.customer_site_use_id = hcsu.site_use_id 

   AND hcsu.org_id = ou.organization_id

Query to get Supplier Site Level Bank Account in Oracle APPS

SELECT bank_account_name

              ,bank_account_num

  FROM iby_ext_bank_accounts

 WHERE ext_bank_account_id IN ( SELECT ext_bank_account_id

                                  FROM iby_account_owners

                                 WHERE account_owner_party_id

                                    IN ( SELECT party_id

      FROM hz_party_sites

   WHERE party_site_name = 'SUPPLIER_SITE_NAME'

)

)

POS_SUPPLIER_MAPPINGS

POS_SUPPLIER_MAPPINGS table holds the mapping between the AP_SUPPLIERS.VENDOR_ID and HZ_PARTIES.PARTY_ID. This is useful in case of two vendors effectively belong to the same HZ_PARTY Record.

SELECT psm.* 

  FROM pos_supplier_mappings  psm

 WHERE psm.party_id = 123456

IBY_EXTERNAL_PAYEES_ALL

This table captures Payment related details of the Supplier. For Example: The Supplier's remittance advice, the default Payment method Code for this supplier or the bank charges in case of SWIFT payments, etc. This information can be setup at Supplier Level or  Supplier Site Level.

SELECT iepa.* 

  FROM iby_external_payees_all iepa 

 WHERE iepa.payee_party_id = 123456

HZ_ORGANIZATION_PROFILES

This table captures additional Supplier information.

Examples are like credit scoring details of Supplier or the No. of Employees working in Supplier Organization.

SELECT hop.* 

  FROM hz_organization_profiles hop

 WHERE hop.party_id = 123456

HZ_PARTY_USG_ASSIGNMENTS

This table stores the Party Usages, for example, party_id of the type SUPPLIER.

SELECT party_id 

      ,party_usg_assignment_id

  ,party_usage_code 

  FROM hz_party_usg_assignments 

 WHERE party_id = 123456;

Supplier Type Employee not showing any List Of values while creating in Oracle APPS

This is from Oracle Note Doc ID 2395382.1:

The contingent workers will not be directly visible in the suppliers form.

The functional reason is that Contingent Worker (CW) Employees are different from Company Employees. In case of Contingent workers you generally work with the company that has provided the CW. Also there are many restrictions for the Employee type Suppliers (like we don’t show the address, most of the fields are non editable etc.) that may not apply to CW. As such the way to do this would be to establish a Supplier and then relate this Supplier to the Contingent worker in the HR form.

If the customers requirement is to create Employee type suppliers, the such CW need to be made as a employees and not CW.

Unable to create Employee Type Supplier in Oracle APPS R12

While creating Employee Type Supplier, Supplier Type not showing "Supplier used to process expense payments to internal employees" from list of values and showing only "Standard supplier".

Please follow Oracle Support Note "R12:AP: Suppliers Form: Unable To Create Or View An Employee Type Supplier (Doc ID 1371295.1)" to resolve the issue.

Pricing Data Bulk Loader in Oracle APPS

The pricing data bulk loader imports data from the following interface tables:

• QP_INTERFACE_LIST_HEADERS
• QP_INTERFACE_LIST_LINES
• QP_INTERFACE_QUALIFIERS 
• QP_INTERFACE_PRICING_ATTRIBS 
• QP_INTERFACE_ERRORS

QP_INTERFACE_LIST_HEADERS: This table captures the Price List header data.

QP_INTERFACE_LIST_LINES: Price list line data is captured in this interface table.

QP_INTERFACE_QUALIFIERS: This table contains the header qualifiers associated with the price lists to be imported. 

QP_INTERFACE_PRICING_ATTRIBS: The product and pricing attributes data is captured in this table. 

How To Find the XML Publisher Temporary Directory Via SQL in Oracle APPS?

Navigate: XML Publisher -> Administrator Properties -> General -> Temporary Directory

SELECT value

FROM   XDO_CONFIG_VALUES

WHERE  property_code = 'SYSTEM_TEMP_DIR';

Steps to define Auto Lockbox in Oracle APPS

• Define the Bank, Bank Branch and Bank Account 
• Define Receipt Class for Auto Lockbox 
• Define Receipt Source
• Define Autocash Ruleset
• Define Customer, attach the receipt method and create the customer bank account (Branch number and account number combination decides the MICR number) 
• Define Lockbox 
• Navigation: Setup --> Receipts --> Lockboxes --> Lockboxes
• Define Transmission Formats 
• Navigation: Setup --> Receipts --> Lockboxes --> Transmission Formats
• Control file changes
• Run Autolockbox 
• Submit Validation 
• Submit Post Quickcash