Asset Retirement Types in Oracle APPS

Type	Description	Rules
Unit retirement	Retire assets by unit, either all units or some units of a multiple-unit asset. The cost retired is automatically calculated for each unit retired.	Not allowed in tax books.
Cost retirement	Retire assets by cost. The units remain unchanged and the retired cost is spread evenly among the units.	Allowed in both corporate and tax books.
Source line retirement	Retire an asset that was imported as a source line by retiring the asset cost based on the source line.	Allowed for both partial and full retirements.

 

Asset Retirement Methods in Oracle APPS

Method	Description	Rules
Full retirement	Retire all the units of a multiple-unit asset Retire the entire asset cost	Full cost retirements: allowed for CIP assets. Full unit retirements: not allowed for CIP assets.
Partial retirement	Retire a specified number of units of a multiple-unit asset Retire a portion of the asset cost.	Partial cost retirements: the units remain unchanged and the retired cost is spread evenly among all assignment lines. Partial unit retirements: Oracle Fusion Assets automatically calculates the retired cost. Partial retirements of CIP assets: not allowed.

 

Asset Queue Status in Oracle APPS

Queue Name	Definition
New	New mass addition line created but not yet reviewed.
On Hold or user-defined hold queue	Mass addition line updated or put on hold.
Split	Mass addition line already split into multiple lines.
Merged	Mass addition line already merged into another line.
Cost Adjustment	Mass addition line to be added to an existing asset; ready for posting.
Post	Mass addition line ready to become an asset.
Posted	Mass addition line already posted.
Delete	Mass addition line to be deleted.

Query to extract Purchase Document Types in Oracle APPS

  SELECT haou.name operating_unit,

         flv1.meaning || ' ' || flv2.meaning document_type,

         pdtav.type_name name,

         xtv1.template_name document_types_layout,

         xtv2.template_name contract_terms_layout,

         DECODE (pdtav.can_preparer_approve_flag,  'Y', 'Yes',  'N', 'No')

            can_preparer_approve,

         INITCAP (pdtav.security_level_code) security_level,

         pdtav.document_type_code,

         pdtav.document_subtype

    FROM hr_all_organization_units haou,

         po_document_types_all_vl pdtav,

         fnd_lookup_values flv1,

         fnd_lookup_values flv2,

         xdo_templates_vl xtv1,

         xdo_templates_vl xtv2

   WHERE 1 = 1

         AND haou.organization_id = pdtav.org_id

         AND pdtav.document_type_code = flv1.lookup_code(+)

         AND pdtav.document_subtype = flv2.lookup_code(+)

         AND flv1.lookup_type(+) = 'DOCUMENT TYPE'

         AND flv2.lookup_type(+) =

                DECODE (pdtav.document_type_code,

                        'REQUISITION', 'REQUISITION TYPE',

                        'RFQ', 'RFQ SUBTYPE',

                        'QUOTATION', 'QUOTATION SUBTYPE',

                        'DOCUMENT SUBTYPE')

         AND flv1.language(+) = USERENV ('lang')

         AND flv2.language(+) = USERENV ('lang')

         AND flv1.view_application_id(+) = 201

         AND flv2.view_application_id(+) = 201

         AND flv1.security_group_id(+) = 0

         AND flv2.security_group_id(+) = 0

         AND pdtav.document_template_code = xtv1.template_code(+)

         AND pdtav.contract_template_code = xtv2.template_code(+)

ORDER BY haou.name

        ,pdtav.document_type_code

        ,pdtav.type_name

Query to fetch Elements attached to Employee in Oracle APPS

SELECT pbg.name business_group_name

      ,papf.employee_number

      ,papf.full_name

      ,petf.element_name

      ,peef.effective_start_date

  FROM per_all_people_f papf

      ,per_all_assignments_f paaf

      ,pay_element_entries_f peef

      ,pay_element_types_f petf

      ,per_business_groups pbg

 WHERE papf.person_id = paaf.person_id

   AND papf.business_group_id = pbg.business_group_id

   AND pbg.business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID')

   AND papf.current_employee_flag = 'Y'

   AND paaf.assignment_id = peef.assignment_id

   AND peef.element_type_id = petf.element_type_id

   AND TRUNC (SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date

   AND TRUNC (SYSDATE) BETWEEN paaf.effective_start_date AND paaf.effective_end_date

   AND TRUNC (SYSDATE) BETWEEN peef.effective_start_date AND peef.effective_end_date

   AND TRUNC (SYSDATE) BETWEEN petf.effective_start_date AND petf.effective_end_date

   AND petf.element_name LIKE 'PF%'

Query to extract values from the User Defined Tables in Oracle APPS

SELECT put.user_table_name

      ,pur.row_low_range_or_name

      ,pui.value

      ,puc.user_column_name

  FROM pay_user_tables put

      ,pay_user_rows_f pur

      ,pay_user_columns puc

      ,pay_user_column_instances_f pui

 WHERE 1=1

   AND pur.user_table_id = put.user_table_id

   AND pui.user_column_id = puc.user_column_id

   AND pui.user_row_id = pur.user_row_id

   AND SYSDATE BETWEEN pur.effective_start_date AND pur.effective_end_date

   AND SYSDATE BETWEEN pui.effective_start_date AND pui.effective_end_date

   --AND put.user_table_name = '<USER_DEFINED_TABLE_NAME>'

   --AND puc.user_column_name = '<USER_DEFINED_COLUMN_NAME>'

   AND puc.user_table_id = put.user_table_id

Query to get the Menu Name in Oracle APPS

SELECT frt.responsibility_name

      ,fm.menu_id

      ,fm.menu_name

  FROM fnd_menus fm

      ,fnd_responsibility fr

  ,fnd_responsibility_tl frt

 WHERE fm.menu_id = fr.menu_id

   AND fr.responsibility_id = frt.responsibility_id

   AND fr.responsibility_id = frt.responsibility_id

   AND frt.responsibility_name = 'Receivables Manager'

Query to find the Descriptive Flex Field (DFF) fields attached to a Context Code in Oracle APPS

SELECT fat.application_name

      ,desc_flex.descriptive_flexfield_name

      ,desc_flex.title

      ,desc_flex_col_usg.descriptive_flex_context_code

      ,desc_flex_col_usg.column_seq_num

      ,desc_flex_col_usg.end_user_column_name

      ,desc_flex_col_usg.application_column_name

      ,desc_flex_col_usg.form_left_prompt

      ,desc_flex_col_usg.form_above_prompt

      ,desc_flex_col_usg.flex_value_set_id value_set_id

      ,flex_vs.flex_value_set_name

      ,flex_vs.validation_type

      ,desc_flex_col_usg.enabled_flag

      ,desc_flex_col_usg.required_flag      

      ,desc_flex_col_usg.display_flag

      ,desc_flex_col_usg.display_size

  FROM fnd_descriptive_flexs_tl desc_flex

      ,fnd_descr_flex_col_usage_vl desc_flex_col_usg

      ,fnd_flex_value_sets flex_vs

      ,fnd_application_tl fat

 WHERE  1=1

   AND desc_flex.descriptive_flexfield_name = desc_flex_col_usg.descriptive_flexfield_name

   AND desc_flex.language = USERENV('LANG')

   AND desc_flex_col_usg.descriptive_flex_context_code = 'CONTRACTS'

   --AND desc_flex_col_usg.descriptive_flexfield_name = 'RA_INTERFACE_LINES'

   AND flex_vs.flex_value_set_id(+) = desc_flex_col_usg.flex_value_set_id

   AND desc_flex_col_usg.application_id = fat.application_id

   AND fat.language = USERENV('LANG')

   --AND fat.application_id = 222

   AND desc_flex.title = 'Line Transaction Flexfield'

ORDER BY desc_flex_col_usg.column_seq_num

Query to list Freight From Discount Excluded Records in Oracle APPS

SELECT supp.vendor_name

      ,supp.segment1 vendor_number

      ,supp.exclude_freight_from_discount vendor_freight_discount_excld

  ,supp_site.vendor_site_code

  ,supp_site.exclude_freight_from_discount vend_sit_freight_dis_excld

  FROM ap_suppliers supp

      ,ap_supplier_sites_all supp_site

 WHERE supp.vendor_id = supp_site.vendor_id

   AND supp.exclude_freight_from_discount  IS NULL

   AND supp_site.exclude_freight_from_discount IS NULL

ORDER BY supp.vendor_name

Query to list Geography's without Jurisdiction's in Oracle APPS EB-Tax

SELECT * 

  FROM(SELECT geography_type

             ,geography_element2_code state_code

,geography_element3_code county_code

,geography_element4_code city_code

         FROM hz_geographies geography

        WHERE geography.geography_type='STATE'

          AND SYSDATE BETWEEN geography.start_date AND geography.end_date

          AND geography_element1_code='US'

          AND NOT EXISTS (SELECT 1 

                            FROM zx_jurisdictions_b tax_juridiction

                           WHERE tax_juridiction.zone_geography_id=geography.geography_id

                             AND tax_juridiction.tax_regime_code = '<<TAX_REGIME_CODE>>'

                             AND SYSDATE BETWEEN tax_juridiction.effective_from AND NVL(tax_juridiction.effective_to,'31-DEC-4999')

                             AND tax_juridiction.tax=geography.geography_type

)

UNION

    SELECT geography_type

      ,geography_element2_code state_code

  ,geography_element3_code county_code

  ,geography_element4_code city_code

          FROM hz_geographies geography

         WHERE geography.geography_type='COUNTY'

           AND SYSDATE BETWEEN geography.start_date AND geography.end_date

           AND geography_element1_code='US'

           AND NOT EXISTS (SELECT 1 

                             FROM zx_jurisdictions_b tax_juridiction

                            WHERE tax_juridiction.zone_geography_id=geography.geography_id

                              AND tax_juridiction.tax_regime_code='<<TAX_REGIME_CODE>>'

                              AND SYSDATE BETWEEN tax_juridiction.effective_from AND NVL(tax_juridiction.effective_to,'31-DEC-4999')

                              AND tax_juridiction.tax=geography.geography_type

)

UNION

SELECT geography_type

      ,geography_element2_code state_code

  ,geography_element3_code county_code

  ,geography_element4_code city_code

  FROM hz_geographies geography

WHERE geography.geography_type='CITY'

    AND SYSDATE BETWEEN geography.start_date AND geography.end_date

    AND geography_element1_code='US'

    AND NOT EXISTS (SELECT 1 

                      FROM zx_jurisdictions_b tax_juridiction

                     WHERE tax_juridiction.zone_geography_id = geography.geography_id

                       AND tax_juridiction.tax_regime_code='_<<TAX_REGIME_CODE>>'

                       AND SYSDATE BETWEEN tax_juridiction.effective_from AND NVL(tax_juridiction.effective_to,'31-DEC-4999')

                                   AND tax_juridiction.tax=geography.geography_type

                                )

      )

ORDER BY geography_type

        ,state_code

,county_code

,city_code

Query to list Jurisdiction's for which Tax Rates Has been defined in Oracle APPS

SELECT tax

      ,tax_jurisdiction_code

  ,geography_element2_code state_code

  ,geography_element3_code county_code

  ,geography_element4_code city_code

  FROM zx_jurisdictions_b tax_juridiction

      ,hz_geographies geography

 WHERE tax_juridiction.tax_regime_code = '<<TAX_REGIME_CODE>>'

   AND SYSDATE BETWEEN tax_juridiction.effective_from AND NVL(tax_juridiction.effective_to,'31-DEC-4999')

   AND tax_juridiction.zone_geography_id=geography.geography_id

   AND tax_juridiction.tax = geography.geography_type

   AND SYSDATE BETWEEN geography.start_date AND geography.end_date

   AND NOT EXISTS (SELECT 1 

                     FROM zx_rates_b zr

                    WHERE zr.tax_regime_code='<<TAX_REGIME_CODE>>'

                      AND zr.tax_jurisdiction_code = tax_juridiction.tax_jurisdiction_code

  )

ORDER BY tax

        ,tax_jurisdiction_code

,geography_element2_code 

,geography_element3_code

,geography_element4_code

EB-Tax Setup Tables in Oracle APPS

Tax Setup	Table Name
Tax Regimes	ZX_REGIMES_B
Taxes	ZX_TAXES_B
Tax Status	ZX_STATUS_B
Tax Rates	ZX_RATES_B
Tax Jurisdictions	ZX_JURISDICTIONS_B
Tax Rules	ZX_RULES_B

 

EB-Tax Tables in Oracle APPS

TABLE_NAME

ZX_ACCOUNTS

ZX_ACCOUNT_RATES

ZX_ACCT_TX_CLS_DEFS_ALL

ZX_API_CODE_COMBINATIONS

ZX_API_OWNER_STATUSES

ZX_API_REGISTRATIONS

ZX_COMPOUND_ERRORS

ZX_COMPOUND_ERRORS_T

ZX_CONDITIONS

ZX_CONDITION_GROUPS_B

ZX_CONDITION_GROUPS_TL

ZX_CONTENT_CHOICES_TMP

ZX_CONTENT_SOURCES

ZX_DATA_UPLOAD_INTERFACE

ZX_DETAIL_TAX_LINES_GT

ZX_DETERMINING_FACTORS_B

ZX_DET_FACTORS_TL

ZX_DET_FACTOR_TEMPL_B

ZX_DET_FACTOR_TEMPL_DTL

ZX_DET_FACTOR_TEMPL_TL

ZX_ERRORS_GT

ZX_EVENT_CLASSES_B

ZX_EVENT_CLASSES_TL

ZX_EVENT_CLASS_PARAMS

ZX_EVNT_CLS_MAPPINGS

ZX_EVNT_CLS_OPTIONS

ZX_EVNT_CLS_TYPS

ZX_EVNT_TYP_MAPPINGS

ZX_EXCEPTIONS

ZX_EXEMPTIONS

ZX_FC_CODES_B

ZX_FC_CODES_CATEG_ASSOC

ZX_FC_CODES_DENORM_B

ZX_FC_CODES_TL

ZX_FC_COUNTRY_DEFAULTS

ZX_FC_TYPES_B

ZX_FC_TYPES_REG_ASSOC

ZX_FC_TYPES_TL

ZX_FORMULA_B

ZX_FORMULA_DETAILS

ZX_FORMULA_TL

ZX_ID_TCC_MAPPING_ALL

ZX_IMPORT_TAX_LINES_GT

ZX_ITM_DISTRIBUTIONS_GT

ZX_JURISDICTIONS_B

ZX_JURISDICTIONS_GT

ZX_JURISDICTIONS_TL

ZX_LINES

ZX_LINES_DET_FACTORS

ZX_LINES_SUMMARY

ZX_PARAMETERS_B

ZX_PARAMETERS_TL

ZX_PARAM_DETAILS

ZX_PARTY_TAX_PROFILE

ZX_PARTY_TYPES

ZX_PO_REC_DIST

ZX_PROCESS_RESULTS

ZX_PRODUCT_OPTIONS_ALL

ZX_PRVDR_HDR_EXTNS_GT

ZX_PRVDR_LINE_EXTNS_GT

ZX_PTNR_LOCATION_INFO_GT

ZX_PTNR_NEG_LINE_GT

ZX_PTNR_NEG_TAX_LINE_GT

ZX_PURGE_TRANSACTIONS_GT

ZX_RATES_B

ZX_RATES_TL

ZX_REGIMES_USAGES

ZX_REGIME_RELATIONS

ZX_REGISTRATIONS

ZX_REPORTING_CODES_B

ZX_REPORTING_CODES_TL

ZX_REPORTING_TYPES_B

ZX_REPORTING_TYPES_TL

ZX_REPORT_CODES_ASSOC

ZX_REPORT_TYPES_USAGES

ZX_REP_ACTG_EXT_T

ZX_REP_CONTEXT_T

ZX_REP_MATRIX_EXT_T

ZX_REP_TRX_DETAIL_T

ZX_REP_TRX_JX_EXT_T

ZX_REVERSE_DIST_GT

ZX_REVERSE_TRX_LINES_GT

ZX_RULES_B

ZX_RULES_TL

ZX_SERVICE_TYPES

ZX_SIM_CONDITIONS

ZX_SIM_PROCESS_RESULTS

ZX_SIM_PURGE

ZX_SIM_RULES_B

ZX_SIM_RULES_TL

ZX_SIM_RULE_CONDITIONS

ZX_SIM_TRX_DISTS

ZX_SRVC_SBSCRPTN_EXCLS

ZX_SRVC_SUBSCRIPTIONS

ZX_SRVC_TYP_PARAMS

ZX_STATUS_B

ZX_STATUS_TL

ZX_SUBSCRIPTION_DETAILS

ZX_SUBSCRIPTION_OPTIONS

ZX_TAXES_B

ZX_TAXES_TL

ZX_TAX_PRIORITIES_T

ZX_TAX_RELATIONS_T

ZX_TRANSACTION

ZX_TRANSACTION_LINES

ZX_TRANSACTION_LINES_GT

ZX_TRX_HEADERS_GT

ZX_TRX_LINE_APP_REGIMES

ZX_TRX_PRE_PROC_OPTIONS_GT

ZX_TRX_TAX_LINK_GT

ZX_UPDATE_CRITERIA_RESULTS

ZX_VALIDATION_ERRORS_GT