=============================
SELECT logfile_name, outfile_name
FROM fnd_concurrent_requests
WHERE request_id = 449842
=============================
Monday, December 29, 2008
Query to know all the menus/ Responsibilities name where one given form is refered
Query to know all the menus/ Responsibilities name where one given form is refered
============================================================
SELECT RESPONSIBILITY_NAME
FROM fnd_responsibility_vl
WHERE MENU_ID IN (SELECT menu_id
FROM fnd_menu_entries_vl
WHERE FUNCTION_ID IN ( SELECT FUNCTION_ID
FROM fnd_menu_entries_vl
WHERE PROMPT = 'Define'))
UNION
SELECT RESPONSIBILITY_NAME
FROM fnd_responsibility_vl
WHERE MENU_ID IN (SELECT MENU_ID
FROM fnd_menu_entries_vl
WHERE SUB_MENU_ID IN(SELECT menu_id
FROM fnd_menu_entries_vl
WHERE FUNCTION_ID IN
( SELECT FUNCTION_ID
FROM fnd_menu_entries_vl
WHERE PROMPT = 'Define')));
==============================================================
============================================================
SELECT RESPONSIBILITY_NAME
FROM fnd_responsibility_vl
WHERE MENU_ID IN (SELECT menu_id
FROM fnd_menu_entries_vl
WHERE FUNCTION_ID IN ( SELECT FUNCTION_ID
FROM fnd_menu_entries_vl
WHERE PROMPT = 'Define'))
UNION
SELECT RESPONSIBILITY_NAME
FROM fnd_responsibility_vl
WHERE MENU_ID IN (SELECT MENU_ID
FROM fnd_menu_entries_vl
WHERE SUB_MENU_ID IN(SELECT menu_id
FROM fnd_menu_entries_vl
WHERE FUNCTION_ID IN
( SELECT FUNCTION_ID
FROM fnd_menu_entries_vl
WHERE PROMPT = 'Define')));
==============================================================
Friday, December 19, 2008
Creation of Dependable value set
:$FLEX$.XX_ORG_NAME
useful queries for the same this type of value set-
(SELECT DISTINCT forecast_designator,ORGANIZATION_ID,forecast_set,ROW_NUMBER () OVER (PARTITION BY forecast_designator ORDER BY forecast_designator) rn FROM mrp_forecast_designators_v)
WHERE organization_id = NVL(:$FLEX$.XX_ORG_NAME, organization_id) AND forecast_set = 'KANBAN' AND rn =1
useful queries for the same this type of value set-
(SELECT DISTINCT forecast_designator,ORGANIZATION_ID,forecast_set,ROW_NUMBER () OVER (PARTITION BY forecast_designator ORDER BY forecast_designator) rn FROM mrp_forecast_designators_v)
WHERE organization_id = NVL(:$FLEX$.XX_ORG_NAME, organization_id) AND forecast_set = 'KANBAN' AND rn =1
Monday, December 15, 2008
FNDLOAD for Request set
===========================================================
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct GEN_ASCP_MSCPDX_DEF.ldt REQ_SET REQUEST_SET_NAME="GEN_ASCP_MSCPDX"
--------------------------------------------------------------------------------------
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct XX_ASCP_MSCPDX_LINK.ldt REQ_SET_LINKS REQUEST_SET_NAME="XX_ASCP_MSCPDX"
--------------------------------------------------------------------------------------
FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afcprset.lct XX_ASCP_MSCPDX_DEF.ldt
--------------------------------------------------------------------------------------
FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afcprset.lct XX_ASCP_MSCPDX_LINK.ldt
============================================
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct GEN_ASCP_MSCPDX_DEF.ldt REQ_SET REQUEST_SET_NAME="GEN_ASCP_MSCPDX"
--------------------------------------------------------------------------------------
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct XX_ASCP_MSCPDX_LINK.ldt REQ_SET_LINKS REQUEST_SET_NAME="XX_ASCP_MSCPDX"
--------------------------------------------------------------------------------------
FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afcprset.lct XX_ASCP_MSCPDX_DEF.ldt
--------------------------------------------------------------------------------------
FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afcprset.lct XX_ASCP_MSCPDX_LINK.ldt
============================================
PLSQL Block to attach concurrent program to request group
Run anonyms plsql block to attach this concurrent program to request group.
BEGIN
fnd_program.add_to_group ('XX_PO_NOTIFY_RECEIVE','PO','All Reports','PO');
END;
BEGIN
fnd_program.add_to_group ('XX_PO_NOTIFY_RECEIVE','PO','All Reports','PO');
END;
FNDLOAD for Concurrent Program
===================================================
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct PO_NOTIFY_RECEIVE.ldt PROGRAM APPLICATION_SHORT_NAME="PO" CONCURRENT_PROGRAM_NAME="PO_NOTIFY_RECEIVE"
---------------------------------------------------------------------
FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct PO_NOTIFY_RECEIVE.ldt PROGRAM APPLICATION_SHORT_NAME="PO" CONCURRENT_PROGRAM_NAME="PO_NOTIFY_RECEIVE"
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct PO_NOTIFY_RECEIVE.ldt PROGRAM APPLICATION_SHORT_NAME="PO" CONCURRENT_PROGRAM_NAME="PO_NOTIFY_RECEIVE"
---------------------------------------------------------------------
FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct PO_NOTIFY_RECEIVE.ldt PROGRAM APPLICATION_SHORT_NAME="PO" CONCURRENT_PROGRAM_NAME="PO_NOTIFY_RECEIVE"
FNDLOAD for Alert
=========================================================
FNDLOAD apps/apps 0 Y DOWNLOAD $ALR_TOP/patch/115/import/alr.lct Email_to_requestes_for_PO_Receipt.ldt ALR_ALERTS APPLICATION_SHORT_NAME='PO' ALERT_NAME=' Email to requestes for PO Receipt'
-----------------------------------------------------------
FNDLOAD apps/apps 0 Y UPLOAD $ALR_TOP/patch/115/import/alr.lct Email_to_requestes_for_PO_Receipt.ldt
FNDLOAD apps/apps 0 Y DOWNLOAD $ALR_TOP/patch/115/import/alr.lct Email_to_requestes_for_PO_Receipt.ldt ALR_ALERTS APPLICATION_SHORT_NAME='PO' ALERT_NAME=' Email to requestes for PO Receipt'
-----------------------------------------------------------
FNDLOAD apps/apps 0 Y UPLOAD $ALR_TOP/patch/115/import/alr.lct Email_to_requestes_for_PO_Receipt.ldt
Wednesday, December 10, 2008
Query to get Application Short Name, Base Path, Application Id If you know Application Name
SELECT fa.application_short_name, fat.application_id, fa.basepath,
fa.product_code
FROM fnd_application_tl fat, fnd_application fa
WHERE fat.application_id = fa.application_id
AND fat.application_name = 'Purchasing'
fa.product_code
FROM fnd_application_tl fat, fnd_application fa
WHERE fat.application_id = fa.application_id
AND fat.application_name = 'Purchasing'
Query to get the request group of a concurrent program
======================================================
SELECT REQUEST_GROUP_NAME
FROM FND_REQUEST_GROUPS where REQUEST_GROUP_ID in
(SELECT REQUEST_GROUP_ID
FROM FND_REQUEST_GROUP_UNITS WHERE (request_unit_id=820) )
----------------------------------------------------------
SELECT CONCURRENT_PROGRAM_ID
FROM FND_CONCURRENT_PROGRAMS_VL
WHERE queue_control_flag = 'N'
and USER_CONCURRENT_PROGRAM_NAME = 'TK Electronic Customs Interface - Set'
order by application_id, user_concurrent_program_name
/*if you do not find this request group directly attached to in any of the responsibilkity then go to the function and query in parameter field
with this request group , then you will get the function name - get the function id ,
--through this function id you will get to know in which menu this is attached (through below query ),
through menu name you will get to know the tresponsibility name.*/
SELECT entry_sequence, prompt, description, grant_flag, menu_id,
sub_menu_id, function_id, last_update_date, last_updated_by,
created_by, creation_date, last_update_login, row_id
FROM fnd_menu_entries_vl
WHERE function_id = 26045
SELECT menu_name, user_menu_name, TYPE, description, menu_id,
last_update_date, last_updated_by, last_update_login, creation_date,
created_by, row_id
FROM fnd_menus_vl
where menu_id = 79018
---------------------------------
Request set
SELECT request_set_id, user_request_set_name, request_set_name, description,
start_date_active, end_date_active, print_together_flag,
allow_constraints_flag,application_id,
concurrent_program_id, owner, created_by, creation_date,
last_update_date, last_updated_by, last_update_login, icon_name,
printer, start_stage
FROM fnd_request_sets_vl
WHERE ('SYS' = 'SYS' OR owner = 1304)
AND (user_request_set_name = 'TK Electronic Customs Interface - Set')
ORDER BY application_id, request_set_name
========================================================
SELECT REQUEST_GROUP_NAME
FROM FND_REQUEST_GROUPS where REQUEST_GROUP_ID in
(SELECT REQUEST_GROUP_ID
FROM FND_REQUEST_GROUP_UNITS WHERE (request_unit_id=820) )
----------------------------------------------------------
SELECT CONCURRENT_PROGRAM_ID
FROM FND_CONCURRENT_PROGRAMS_VL
WHERE queue_control_flag = 'N'
and USER_CONCURRENT_PROGRAM_NAME = 'TK Electronic Customs Interface - Set'
order by application_id, user_concurrent_program_name
/*if you do not find this request group directly attached to in any of the responsibilkity then go to the function and query in parameter field
with this request group , then you will get the function name - get the function id ,
--through this function id you will get to know in which menu this is attached (through below query ),
through menu name you will get to know the tresponsibility name.*/
SELECT entry_sequence, prompt, description, grant_flag, menu_id,
sub_menu_id, function_id, last_update_date, last_updated_by,
created_by, creation_date, last_update_login, row_id
FROM fnd_menu_entries_vl
WHERE function_id = 26045
SELECT menu_name, user_menu_name, TYPE, description, menu_id,
last_update_date, last_updated_by, last_update_login, creation_date,
created_by, row_id
FROM fnd_menus_vl
where menu_id = 79018
---------------------------------
Request set
SELECT request_set_id, user_request_set_name, request_set_name, description,
start_date_active, end_date_active, print_together_flag,
allow_constraints_flag,application_id,
concurrent_program_id, owner, created_by, creation_date,
last_update_date, last_updated_by, last_update_login, icon_name,
printer, start_stage
FROM fnd_request_sets_vl
WHERE ('SYS' = 'SYS' OR owner = 1304)
AND (user_request_set_name = 'TK Electronic Customs Interface - Set')
ORDER BY application_id, request_set_name
========================================================
Selecting Database instance through SQL Query
============================
Select name from v$database;
============================
Select name from v$database;
============================
Tuesday, December 9, 2008
Tables of Fixed Assets
Tables of Fixed Assets
=================
1- FA_DEPRN_PERIODS
2- FA_DEPRN_SUMMARY
3- FA_ADDITIONS_B
4- FA_BOOKS
5- FA_CATEGORIES_B
6- FA_DEPRN_DETAIL
FA_DEPRN_PERIODS contains information about your depreciation periods. Oracle Assets uses this table to determine when each period in FA_CALENDARS was open for a depreciation book. PERIOD_OPEN_DATE and PERIOD_CLOSE_DATE are the dates when you opened and closed each book’s depreciation period. Each time you run the depreciation program, it closes the current period by setting PERIOD_CLOSE_DATE to the system date. It also opens the next period by inserting a new row into this table in which PERIOD_CLOSE_DATE is NULL and PERIOD_OPEN_DATE equals the PERIOD_CLOSE_DATE of the old row. CALENDAR_PERIOD_OPEN_DATE and CALENDAR_PERIOD_CLOSE_DATE correspond to your calendar as defined by the START_DATE and END_DATE columns in FA_CALENDAR_PERIODS.
FA_DEPRN_SUMMARY contains depreciation information for your assets. Each time you run the depreciation program, it inserts one row into thistable for each asset. PERIOD_COUNTER is the period for which you ran the depreciation program. DEPRN_AMOUNT is the depreciation expense for an asset in a depreciation period. It is the sum of DEPRN_AMOUNT in all the rows of FA_DEPRN_DETAIL for the asset and period. YTD_DEPRN is the accumulated depreciation of an asset for the current fiscal year as of the end of this period. DEPRN_RESERVE is the total accumulated depreciation for this asset. DEPRN_SOURCE_CODE tells you what program created the row BOOKS Created by the Depreciation Books form, Quick Additions form, or the post mass additions program when you enter a new asset. DEPRN Created by the depreciation program when you run depreciation. ADJUSTED_COST is the depreciable basis the depreciation program uses to calculate depreciation for an asset in a depreciation period. This value is the same as the asset’s recoverable cost, except for assets that use a diminishing value depreciation method, assets to which you have made an amortized adjustment, and assets you have revalued.
For assets that use a diminishing value method, the ADJUSTED_COST is the beginning of year net book value, which the depreciation program updates at the start of each fiscal year. When you perform an amortized adjustment on an asset or revalue it, the ADJUSTED_COST becomes the asset’s net book value at the time of the adjustment or revaluation. BONUS_RATE is the bonus rate that Oracle Assets adds to the adjusted rate to give you the flat rate for the fiscal year. The depreciation program uses this rate to calculate depreciation for an asset. This only applies to assets that use both a flat–rate depreciation method and bonus depreciation.
FA_ADDITIONS_B contains descriptive information to help you identify your assets. Oracle Assets does not use this table to calculate depreciation.When you add an asset, Oracle Assets inserts a row into this table and into FA_ASSET_HISTORY. When you change the asset information stored in this table, Oracle Assets updates it in this table. It also creates a new row in FA_ASSET_HISTORY. When you perform a unit retirement, Oracle Assets reduces the CURRENT_UNITS by the units retired. UNIT_ADJUSTMENT_FLAG is set to YES by the Additions form if you change the number of units for an asset. The Transfers form resets it to NO after you reassign the remaining units. FA_ADJUSTMENTS stores information that Oracle Assets needs to create journal entries for transactions. The posting program creates journal entries for regular depreciation expense from information in FA_DEPRN_DETAIL. Oracle Assets inserts a row in this table for the debit and credit sides of a financial transaction. All the rows for the same transaction have the same value in the TRANSACTION_HEADER_ID column. The SOURCE_TYPE_CODE column tells you which program created the adjustment:
- ADDITION Depreciation program
- ADJUSTMENT Expensed or Amortized Adjustment User Exit
- CIP ADDITION Depreciation program
- CIP ADJUSTMENT Expensed or Amortized Adjustment User Exit
- CIP RETIREMENT Gain/loss program
- DEPRECIATION Depreciation program (Retroactive transactions andexpensed depreciation adjustments)
- RETIREMENT Gain/loss program
- RECLASS Reclassification user exit
- TRANSFER Transfers form
- TAX Reserve Adjustments form
- REVALUATION Mass revaluation program
The ADJUSTMENT_TYPE column tells you which type of account Oracle Assets adjusts. DEBIT_CREDIT_FLAG is DR if the amount is a debit and CR if the amount is a credit. ADJUSTMENT_AMOUNT is the amount debited or credited to the account. ANNUALIZED_ADJUSTMENT is the adjustment amount for a period times the number of periods in a fiscal year. The depreciation program uses it to calculate the depreciation adjustment for an asset when you perform multiple retroactive transactions on the asset. Oracle Assets calculates ADJUSTMENT_PER_PERIOD by dividing the ADJUSTMENT_AMOUNT for a retroactive transaction by the numberof periods between the period you entered the transaction and the period that it was effective. For current period transactions, this columnis zero. PERIOD_COUNTER_CREATED IS the period that you entered the adjustment into Oracle Assets. PERIOD_COUNTER_ADJUSTED is the period to which the adjustment applies. It is the same as PERIOD_COUNTER_CREATED, unless you enter a reserve adjustment, in which case PERIOD_COUNTER_ADJUSTED is the last period of the fiscal year to which the adjustment applies. CODE_COMBINATION_ID indicates the Accounting Flexfield combination Oracle Assets debits or credits for all transactions except reclassifications and intercompany transfers. This CODE_COMBINATION_ID is generated using the Account Generator, and the posting program does not perform any further processing.
FA_BOOKS contains the information that Oracle Assets needs to calculate depreciation. When you initially add an asset, Oracle Assets inserts one row into the table. This becomes the ”active” row for the asset. Whenever you use the Depreciation Books form to change the asset’s depreciation information, or if you retire or reinstate it, Oracle Assets inserts another row into the table, which then becomes the new ”active” row, and marks the previous row as obsolete.
At any point in time, there is only one ”active” row in the table for an asset in any given depreciation book. Generally, Oracle Assets uses the active row, but if you run a report for a prior accounting period, Oracle Assets selects the row that was active during that period. You can identify the active row for anasset in a book because it is the only one whose DATE_INEFFECTIVE and TRANSACTION_HEADER_ID_OUT are NULL. When Oracle Assets terminates a row, the DATE_INEFFECTIVE and TRANSACTION_HEADER_OUT are set to the DATE_EFFECTIVE and TRANSACTION_HEADER_IN of the new row, respectively. This means that you can easily identify rows affected by the same transaction because they have the same DATE_EFFECTIVE / DATE_INEFFECTIVE and TRANSACTION_HEADER_ID_IN / TRANSACTION_HEADER_ID_OUT pairs.When Oracle Assets creates the new row, the value used for the TRANSACTION_HEADER_ID_IN column is the same as the TRANSACTION_HEADER_ID in the row inserted into FA_TRANSACTION_HEADERS, and the DATE_EFFECTIVE is the system date. When you retire an asset, Oracle Assets inserts a new row to reduce the COST by the amount retired. When you reinstate an asset, Oracle Assets inserts a new row to increase the COST by the COST_RETIRED in the corresponding row in FA_RETIREMENTS.RATE_ADJUSTMENT_FACTOR is originally 1. It is used to spread depreciation over the remaining life of an asset after an amortization or revaluation. If you perform a revaluation or an amortized adjustment, Oracle Assets resets the Rate Adjustment Factor to prorate the remaining recoverable net book value over the remaining life. This fraction is calculated as [Recoverable Cost – what Depreciation Reserve would be]/Recoverable Cost. The depreciation program uses this value to adjust the depreciation rate for an asset.
FA_CATEGORIES_B stores information about your asset categories. This table provides default information when you add an asset. The depreciation program does not use this information to calculate depreciation.The Asset Categories form inserts one row in this table for each asset category you define. The Application Object Library table
FND_ID_FLEX_SEGMENTS stores information about which column in this table is used for each segment.
FA_DEPRN_DETAIL contains the depreciation amounts that the depreciation program charges to the depreciation expense account in each distribution line.
Oracle Assets uses this information to create depreciation expense journal entries for your general ledger.The depreciation program inserts one row per distribution line for an asset each time you run depreciation.
For example, if you assign an asset to two different cost centers, the depreciation program inserts two rows in this table for the asset. DEPRN_AMOUNT is the amount of depreciation expense calculated forthis distribution line.YTD_DEPRN is the year–to–date depreciation allocated to thisdistribution line.When you add an asset, Oracle Assets inserts a row into this table for the period before the current period. This row has the asset cost in the ADDITION_COST_TO_CLEAR column and a DEPRN_SOURCE_CODE of ’B’. This column is used for reporting on new assets. When you run depreciation, Oracle Assets transfers the cost to the COST column in the current period row, this row has a DEPRN_SOURCE_CODE of ’D’.
=================
1- FA_DEPRN_PERIODS
2- FA_DEPRN_SUMMARY
3- FA_ADDITIONS_B
4- FA_BOOKS
5- FA_CATEGORIES_B
6- FA_DEPRN_DETAIL
FA_DEPRN_PERIODS contains information about your depreciation periods. Oracle Assets uses this table to determine when each period in FA_CALENDARS was open for a depreciation book. PERIOD_OPEN_DATE and PERIOD_CLOSE_DATE are the dates when you opened and closed each book’s depreciation period. Each time you run the depreciation program, it closes the current period by setting PERIOD_CLOSE_DATE to the system date. It also opens the next period by inserting a new row into this table in which PERIOD_CLOSE_DATE is NULL and PERIOD_OPEN_DATE equals the PERIOD_CLOSE_DATE of the old row. CALENDAR_PERIOD_OPEN_DATE and CALENDAR_PERIOD_CLOSE_DATE correspond to your calendar as defined by the START_DATE and END_DATE columns in FA_CALENDAR_PERIODS.
FA_DEPRN_SUMMARY contains depreciation information for your assets. Each time you run the depreciation program, it inserts one row into thistable for each asset. PERIOD_COUNTER is the period for which you ran the depreciation program. DEPRN_AMOUNT is the depreciation expense for an asset in a depreciation period. It is the sum of DEPRN_AMOUNT in all the rows of FA_DEPRN_DETAIL for the asset and period. YTD_DEPRN is the accumulated depreciation of an asset for the current fiscal year as of the end of this period. DEPRN_RESERVE is the total accumulated depreciation for this asset. DEPRN_SOURCE_CODE tells you what program created the row BOOKS Created by the Depreciation Books form, Quick Additions form, or the post mass additions program when you enter a new asset. DEPRN Created by the depreciation program when you run depreciation. ADJUSTED_COST is the depreciable basis the depreciation program uses to calculate depreciation for an asset in a depreciation period. This value is the same as the asset’s recoverable cost, except for assets that use a diminishing value depreciation method, assets to which you have made an amortized adjustment, and assets you have revalued.
For assets that use a diminishing value method, the ADJUSTED_COST is the beginning of year net book value, which the depreciation program updates at the start of each fiscal year. When you perform an amortized adjustment on an asset or revalue it, the ADJUSTED_COST becomes the asset’s net book value at the time of the adjustment or revaluation. BONUS_RATE is the bonus rate that Oracle Assets adds to the adjusted rate to give you the flat rate for the fiscal year. The depreciation program uses this rate to calculate depreciation for an asset. This only applies to assets that use both a flat–rate depreciation method and bonus depreciation.
FA_ADDITIONS_B contains descriptive information to help you identify your assets. Oracle Assets does not use this table to calculate depreciation.When you add an asset, Oracle Assets inserts a row into this table and into FA_ASSET_HISTORY. When you change the asset information stored in this table, Oracle Assets updates it in this table. It also creates a new row in FA_ASSET_HISTORY. When you perform a unit retirement, Oracle Assets reduces the CURRENT_UNITS by the units retired. UNIT_ADJUSTMENT_FLAG is set to YES by the Additions form if you change the number of units for an asset. The Transfers form resets it to NO after you reassign the remaining units. FA_ADJUSTMENTS stores information that Oracle Assets needs to create journal entries for transactions. The posting program creates journal entries for regular depreciation expense from information in FA_DEPRN_DETAIL. Oracle Assets inserts a row in this table for the debit and credit sides of a financial transaction. All the rows for the same transaction have the same value in the TRANSACTION_HEADER_ID column. The SOURCE_TYPE_CODE column tells you which program created the adjustment:
- ADDITION Depreciation program
- ADJUSTMENT Expensed or Amortized Adjustment User Exit
- CIP ADDITION Depreciation program
- CIP ADJUSTMENT Expensed or Amortized Adjustment User Exit
- CIP RETIREMENT Gain/loss program
- DEPRECIATION Depreciation program (Retroactive transactions andexpensed depreciation adjustments)
- RETIREMENT Gain/loss program
- RECLASS Reclassification user exit
- TRANSFER Transfers form
- TAX Reserve Adjustments form
- REVALUATION Mass revaluation program
The ADJUSTMENT_TYPE column tells you which type of account Oracle Assets adjusts. DEBIT_CREDIT_FLAG is DR if the amount is a debit and CR if the amount is a credit. ADJUSTMENT_AMOUNT is the amount debited or credited to the account. ANNUALIZED_ADJUSTMENT is the adjustment amount for a period times the number of periods in a fiscal year. The depreciation program uses it to calculate the depreciation adjustment for an asset when you perform multiple retroactive transactions on the asset. Oracle Assets calculates ADJUSTMENT_PER_PERIOD by dividing the ADJUSTMENT_AMOUNT for a retroactive transaction by the numberof periods between the period you entered the transaction and the period that it was effective. For current period transactions, this columnis zero. PERIOD_COUNTER_CREATED IS the period that you entered the adjustment into Oracle Assets. PERIOD_COUNTER_ADJUSTED is the period to which the adjustment applies. It is the same as PERIOD_COUNTER_CREATED, unless you enter a reserve adjustment, in which case PERIOD_COUNTER_ADJUSTED is the last period of the fiscal year to which the adjustment applies. CODE_COMBINATION_ID indicates the Accounting Flexfield combination Oracle Assets debits or credits for all transactions except reclassifications and intercompany transfers. This CODE_COMBINATION_ID is generated using the Account Generator, and the posting program does not perform any further processing.
FA_BOOKS contains the information that Oracle Assets needs to calculate depreciation. When you initially add an asset, Oracle Assets inserts one row into the table. This becomes the ”active” row for the asset. Whenever you use the Depreciation Books form to change the asset’s depreciation information, or if you retire or reinstate it, Oracle Assets inserts another row into the table, which then becomes the new ”active” row, and marks the previous row as obsolete.
At any point in time, there is only one ”active” row in the table for an asset in any given depreciation book. Generally, Oracle Assets uses the active row, but if you run a report for a prior accounting period, Oracle Assets selects the row that was active during that period. You can identify the active row for anasset in a book because it is the only one whose DATE_INEFFECTIVE and TRANSACTION_HEADER_ID_OUT are NULL. When Oracle Assets terminates a row, the DATE_INEFFECTIVE and TRANSACTION_HEADER_OUT are set to the DATE_EFFECTIVE and TRANSACTION_HEADER_IN of the new row, respectively. This means that you can easily identify rows affected by the same transaction because they have the same DATE_EFFECTIVE / DATE_INEFFECTIVE and TRANSACTION_HEADER_ID_IN / TRANSACTION_HEADER_ID_OUT pairs.When Oracle Assets creates the new row, the value used for the TRANSACTION_HEADER_ID_IN column is the same as the TRANSACTION_HEADER_ID in the row inserted into FA_TRANSACTION_HEADERS, and the DATE_EFFECTIVE is the system date. When you retire an asset, Oracle Assets inserts a new row to reduce the COST by the amount retired. When you reinstate an asset, Oracle Assets inserts a new row to increase the COST by the COST_RETIRED in the corresponding row in FA_RETIREMENTS.RATE_ADJUSTMENT_FACTOR is originally 1. It is used to spread depreciation over the remaining life of an asset after an amortization or revaluation. If you perform a revaluation or an amortized adjustment, Oracle Assets resets the Rate Adjustment Factor to prorate the remaining recoverable net book value over the remaining life. This fraction is calculated as [Recoverable Cost – what Depreciation Reserve would be]/Recoverable Cost. The depreciation program uses this value to adjust the depreciation rate for an asset.
FA_CATEGORIES_B stores information about your asset categories. This table provides default information when you add an asset. The depreciation program does not use this information to calculate depreciation.The Asset Categories form inserts one row in this table for each asset category you define. The Application Object Library table
FND_ID_FLEX_SEGMENTS stores information about which column in this table is used for each segment.
FA_DEPRN_DETAIL contains the depreciation amounts that the depreciation program charges to the depreciation expense account in each distribution line.
Oracle Assets uses this information to create depreciation expense journal entries for your general ledger.The depreciation program inserts one row per distribution line for an asset each time you run depreciation.
For example, if you assign an asset to two different cost centers, the depreciation program inserts two rows in this table for the asset. DEPRN_AMOUNT is the amount of depreciation expense calculated forthis distribution line.YTD_DEPRN is the year–to–date depreciation allocated to thisdistribution line.When you add an asset, Oracle Assets inserts a row into this table for the period before the current period. This row has the asset cost in the ADDITION_COST_TO_CLEAR column and a DEPRN_SOURCE_CODE of ’B’. This column is used for reporting on new assets. When you run depreciation, Oracle Assets transfers the cost to the COST column in the current period row, this row has a DEPRN_SOURCE_CODE of ’D’.
Monday, December 8, 2008
Sunday, December 7, 2008
Tables of Category
Important Table related to catagory
===========================
Category
Mtl_item_categories
Mtl_categories_b
Others
Mtl_system_items_b
Links Between table
===============
Mtl_item_categories /Mtl_system_items_b --> inventory_item_id,organization_id/inventory_item_id,organization_id
Mtl_item_categories/Mtl_categories_b --> category_id/category_id
===========================
Category
Mtl_item_categories
Mtl_categories_b
Others
Mtl_system_items_b
Links Between table
===============
Mtl_item_categories /Mtl_system_items_b --> inventory_item_id,organization_id/inventory_item_id,organization_id
Mtl_item_categories/Mtl_categories_b --> category_id/category_id
Friday, December 5, 2008
Anaylitical Function Use
SELECT inventory_item_id, forecast_designator, organization_id,forecast_date, bucket_type,
quantity, request_id, rn,
MAX (quantity) OVER (PARTITION BY inventory_item_id ORDER BY inventory_item_id)maxq
FROM (
SELECT 'Y' fl, inventory_item_id, forecast_designator,organization_id, forecast_date, bucket_type,quantity, request_id,
ROW_NUMBER () OVER (PARTITION BY inventory_item_id ORDER BY forecast_date)rn
FROM xxgenascp_forecast_stg
ORDER BY inventory_item_id, forecast_date)
WHERE rn <= p_max_forecastweeks UNION SELECT inventory_item_id, forecast_designator, organization_id,forecast_date, bucket_type, quantity, request_id, rn, MAX (quantity) OVER (PARTITION BY inventory_item_id ORDER BY inventory_item_id)maxq FROM (SELECT 'N' fl, inventory_item_id, forecast_designator,organization_id, forecast_date, bucket_type,quantity, request_id, ROW_NUMBER () OVER (PARTITION BY inventory_item_id ORDER BY forecast_date)rn FROM xxgenascp_forecast_stgORDER BY inventory_item_id, forecast_date) WHERE rn > p_max_forecastweeks
quantity, request_id, rn,
MAX (quantity) OVER (PARTITION BY inventory_item_id ORDER BY inventory_item_id)maxq
FROM (
SELECT 'Y' fl, inventory_item_id, forecast_designator,organization_id, forecast_date, bucket_type,quantity, request_id,
ROW_NUMBER () OVER (PARTITION BY inventory_item_id ORDER BY forecast_date)rn
FROM xxgenascp_forecast_stg
ORDER BY inventory_item_id, forecast_date)
WHERE rn <= p_max_forecastweeks UNION SELECT inventory_item_id, forecast_designator, organization_id,forecast_date, bucket_type, quantity, request_id, rn, MAX (quantity) OVER (PARTITION BY inventory_item_id ORDER BY inventory_item_id)maxq FROM (SELECT 'N' fl, inventory_item_id, forecast_designator,organization_id, forecast_date, bucket_type,quantity, request_id, ROW_NUMBER () OVER (PARTITION BY inventory_item_id ORDER BY forecast_date)rn FROM xxgenascp_forecast_stgORDER BY inventory_item_id, forecast_date) WHERE rn > p_max_forecastweeks
Getting current concurrent request number in plsql fired by concurrent request
select FND_GLOBAL.CONC_REQUEST_ID from dual;
To get the Organization Name if you know its Id
select * from org_organization_definition where organization_id = 121;
Wednesday, December 3, 2008
Important Table in Purchasing Module of Oracle apps
=========================================
Purchase Order
Po_headers_all
Po_lines_all
Po_line_locations_all
Po_distributions_all
Buyers
Po_agents
Document and line types
Po_document_types_all_b
Po_line_types_b
Releases
Po_realeases_all
Requisitions
Po_requisition_headers_all
Po_requisition_lines_all
Po_req_distributions_all
Vendors
Po_vendors
Po_vendor_sites_all
Po_vendor_contacts
Receiving
Rcv_transactions
Others
Mtl_system_items_b
Mtl_categories_b
Links between Oracle Apps PO Tables
============================
Po_headers_all/Po_lines_all --> po_header_id/po_header_id
Po_headers_all/Po_vendor_sites_all --> vendor_site_id/vendor_site_id
Po_headers_all/Po_vendors --> vendor_id/vendor_id
Po_headers_all/Po_line_locations_all--> po_header_id/po_header_id
Po_lines_all/Po_line_locations_all --> po_line_id/po_line_id
Po_lines_all/mtl_system_items_b --> item_id /inventory_item_id ( get for master org)
Po_lines_all/Mtl_categories_b --> category_id/ category_id
Po_line_locations_all/Rcv_transactions -->line_location_id/po_line_location_id
Po_vendor_sites_all/Po_vendors --> vendor_id/ vendor_id
po_distributions_all/po_req_distributions_all --> req_distribution_id/distribution_id
=========================================
Purchase Order
Po_headers_all
Po_lines_all
Po_line_locations_all
Po_distributions_all
Buyers
Po_agents
Document and line types
Po_document_types_all_b
Po_line_types_b
Releases
Po_realeases_all
Requisitions
Po_requisition_headers_all
Po_requisition_lines_all
Po_req_distributions_all
Vendors
Po_vendors
Po_vendor_sites_all
Po_vendor_contacts
Receiving
Rcv_transactions
Others
Mtl_system_items_b
Mtl_categories_b
Links between Oracle Apps PO Tables
============================
Po_headers_all/Po_lines_all --> po_header_id/po_header_id
Po_headers_all/Po_vendor_sites_all --> vendor_site_id/vendor_site_id
Po_headers_all/Po_vendors --> vendor_id/vendor_id
Po_headers_all/Po_line_locations_all--> po_header_id/po_header_id
Po_lines_all/Po_line_locations_all --> po_line_id/po_line_id
Po_lines_all/mtl_system_items_b --> item_id /inventory_item_id ( get for master org)
Po_lines_all/Mtl_categories_b --> category_id/ category_id
Po_line_locations_all/Rcv_transactions -->line_location_id/po_line_location_id
Po_vendor_sites_all/Po_vendors --> vendor_id/ vendor_id
po_distributions_all/po_req_distributions_all --> req_distribution_id/distribution_id
Tuesday, December 2, 2008
General Introduction of Oracle Apps
Oracle E business suite consists of nearly 135 different modules which are based various business processes prevailing in trade and Industry.Some of them are technical modules.
The financial modules are:
a) General Ledger,
b) Accounts Receivables,
c) Accounts Payable,
d) Fixed Assets,
e)Cash Management,
f)) iPayment,
g)iAssets,
h)Oracle risk Management,
The HR modules are:
a) Oracle HRMS,
b) Payroll,
c)Advanced benefits,
d)iRecruitment,
e)Learning Management,
f)Time and Labor,
g) Self-service Human resources.
The technical modules or core Technology modules are :
a) AK - Common Modules
b) Oracle AOL( Application Object Library)
c) Oracle eCommerce Gateway
d) Oracle iSetup
e) Trading Community Architecture( TCA)
f) Oracle Workflow g) Oracle XML Gateway
The financial modules are:
a) General Ledger,
b) Accounts Receivables,
c) Accounts Payable,
d) Fixed Assets,
e)Cash Management,
f)) iPayment,
g)iAssets,
h)Oracle risk Management,
The HR modules are:
a) Oracle HRMS,
b) Payroll,
c)Advanced benefits,
d)iRecruitment,
e)Learning Management,
f)Time and Labor,
g) Self-service Human resources.
The technical modules or core Technology modules are :
a) AK - Common Modules
b) Oracle AOL( Application Object Library)
c) Oracle eCommerce Gateway
d) Oracle iSetup
e) Trading Community Architecture( TCA)
f) Oracle Workflow g) Oracle XML Gateway
Monday, December 1, 2008
Canonical Date Function
passing character from parameter - fnd_standard_date
select fnd_date.canonical_to_date('2008/01/12') from dual;
alternate solution :
lv_start_date := TRUNC(TO_DATE(p_start_date,'YYYY/MM/DD HH24:MI:SS'))
select fnd_date.canonical_to_date('2008/01/12') from dual;
alternate solution :
lv_start_date := TRUNC(TO_DATE(p_start_date,'YYYY/MM/DD HH24:MI:SS'))
Thursday, October 16, 2008
Initializing Oracle Applications environment in Oracle Release 12i
Initializing Oracle Applications environment in Oracle Release 12i
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
Problem Description : Not able to see data from views synonyms even after setting the apps envirenment in Oracle applications release 12i
----------------------------------------------
We need to set the mo global parameter to see the data in 12i , If
R12 Multi-Org Access Control (MOAC) exists in setup.
begin
fnd_global.apps_initialize(1210, 20678, 222);
mo_global.init('AR');
end;
The parameters used here are:
1. User_ID
2. Responsibility_ID
3. Responsibility_Application_ID
Link for more details
http://www.ltsolutions.eu/r12_pdf/R12_Multi-Org_Access_Control.pdf
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
Problem Description : Not able to see data from views synonyms even after setting the apps envirenment in Oracle applications release 12i
----------------------------------------------
We need to set the mo global parameter to see the data in 12i , If
R12 Multi-Org Access Control (MOAC) exists in setup.
begin
fnd_global.apps_initialize(1210, 20678, 222);
mo_global.init('AR');
end;
The parameters used here are:
1. User_ID
2. Responsibility_ID
3. Responsibility_Application_ID
Link for more details
http://www.ltsolutions.eu/r12_pdf/R12_Multi-Org_Access_Control.pdf
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
Method to call a report (Concurrent Program) from a form or from plsql block in oracle applications
Method to call a report (Concurrent Program) from a form or from plsql block in oracle applications
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
v_req:=FND_REQUEST.SUBMIT_REQUEST
(
'ONT',
'TWOEXP01',
'',
to_char(SYSDATE,'DD-MON-YYYY HH24:MI'),
FALSE,
:g_invoice_no,
:g_mail_code,
:g_org_id,
:g_ou_id,
chr(0),
'','','','','', '','','','','','','','','','', '','','','','','','','','','', '','','','','','','','','','', '','','','','','','','','','', '','','','','','','','','','', '','','','','','','','','','', '','','','','','','','','','', '','','','','','','','','','', '','','','','','','','','','');-- 100 ARGUMENTS FROM THE BEGINNING if v_req<=0 then
dbms_output.put_line('!!!ERROR!!! SUBMIT REQUEST ERROR.');
:g_debug:='error';
end if;
COMMIT;
dbms_output.put_line(' the req id is 'to_char(v_req));
1. where 'ONT' is the application short name
2. where 'TWOEXP01' is the concurrent short name
3. :g_invoice_no,:g_mail_code,:g_org_id,:g_ou_id are the arguments, and chr(0) must be the last argument.
4. 'Commit;' is required!!
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
v_req:=FND_REQUEST.SUBMIT_REQUEST
(
'ONT',
'TWOEXP01',
'',
to_char(SYSDATE,'DD-MON-YYYY HH24:MI'),
FALSE,
:g_invoice_no,
:g_mail_code,
:g_org_id,
:g_ou_id,
chr(0),
'','','','','', '','','','','','','','','','', '','','','','','','','','','', '','','','','','','','','','', '','','','','','','','','','', '','','','','','','','','','', '','','','','','','','','','', '','','','','','','','','','', '','','','','','','','','','', '','','','','','','','','','');-- 100 ARGUMENTS FROM THE BEGINNING if v_req<=0 then
dbms_output.put_line('!!!ERROR!!! SUBMIT REQUEST ERROR.');
:g_debug:='error';
end if;
COMMIT;
dbms_output.put_line(' the req id is 'to_char(v_req));
1. where 'ONT' is the application short name
2. where 'TWOEXP01' is the concurrent short name
3. :g_invoice_no,:g_mail_code,:g_org_id,:g_ou_id are the arguments, and chr(0) must be the last argument.
4. 'Commit;' is required!!
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
Oracle Applications SQL Query to know all the menus Responsibilities name where one given form is refered
Oracle Applications SQL Query to know all the menus Responsibilities name where one given form is refered
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
SELECT
RESPONSIBILITY_NAME FROM fnd_responsibility_vl
WHERE
MENU_ID IN
(SELECT menu_id
FROM fnd_menu_entries_vl
WHERE
FUNCTION_ID IN
( SELECT
FUNCTION_ID
FROM
fnd_menu_entries_vl
WHERE
PROMPT = 'Define'))
UNION
SELECT
RESPONSIBILITY_NAME
FROM fnd_responsibility_vl
WHERE
MENU_ID IN
(SELECT
MENU_ID
FROM
fnd_menu_entries_vl
WHERE
SUB_MENU_ID IN
(SELECT
menu_id
FROM
fnd_menu_entries_vl
WHERE
FUNCTION_ID IN
( SELECT
FUNCTION_ID
FROM
fnd_menu_entries_vl
WHERE
PROMPT = 'Define')))
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
SELECT
RESPONSIBILITY_NAME FROM fnd_responsibility_vl
WHERE
MENU_ID IN
(SELECT menu_id
FROM fnd_menu_entries_vl
WHERE
FUNCTION_ID IN
( SELECT
FUNCTION_ID
FROM
fnd_menu_entries_vl
WHERE
PROMPT = 'Define'))
UNION
SELECT
RESPONSIBILITY_NAME
FROM fnd_responsibility_vl
WHERE
MENU_ID IN
(SELECT
MENU_ID
FROM
fnd_menu_entries_vl
WHERE
SUB_MENU_ID IN
(SELECT
menu_id
FROM
fnd_menu_entries_vl
WHERE
FUNCTION_ID IN
( SELECT
FUNCTION_ID
FROM
fnd_menu_entries_vl
WHERE
PROMPT = 'Define')))
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
Oracle Applications Query to get the current users of oracle apps and forms or concurrent programms opened by them (Through front end and back end
Oracle Applications Query to get the current users of oracle apps and forms or concurrent programms opened by them (Through front end and back end
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
Front End ---> System Administration - Security - User - Monitor
Back End Query --- SELECT
USER_NAME, RESPONSIBILITY_NAME, USER_FORM_NAME, TIME, PID, USER_ID, RESP_APPL_ID, RESPONSIBILITY_ID, FORM_ID, FORM_APPL_ID
FROM
FND_SIGNON_AUDIT_VIEW
where
RESPONSIBILITY_NAME IS NOT NULL
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
Front End ---> System Administration - Security - User - Monitor
Back End Query --- SELECT
USER_NAME, RESPONSIBILITY_NAME, USER_FORM_NAME, TIME, PID, USER_ID, RESP_APPL_ID, RESPONSIBILITY_ID, FORM_ID, FORM_APPL_ID
FROM
FND_SIGNON_AUDIT_VIEW
where
RESPONSIBILITY_NAME IS NOT NULL
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
Oracle Applications SQL Query to get apps user data in just plain sql using something like userenv or srw to extract say their org_id, etc.?
Oracle Applications SQL Query to get apps user data in just plain sql using something like userenv or srw to extract say their org_id, etc.?
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
select
fnd_global.user_id,
fnd_global.resp_id,
fnd_global.resp_appl_id,
fnd_global.security_group_id,
fnd_global.user_name,
fnd_global.resp_name,
fnd_global.application_name,
fnd_global.application_short_name,
fnd_global.login_id,
fnd_global.conc_login_id,
fnd_global.prog_appl_id,
fnd_global.conc_program_id,
fnd_global.conc_request_id,
fnd_global.current_language,
fnd_global.base_language,
fnd_global.org_id,
fnd_global.org_name
from dual;
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
select
fnd_global.user_id,
fnd_global.resp_id,
fnd_global.resp_appl_id,
fnd_global.security_group_id,
fnd_global.user_name,
fnd_global.resp_name,
fnd_global.application_name,
fnd_global.application_short_name,
fnd_global.login_id,
fnd_global.conc_login_id,
fnd_global.prog_appl_id,
fnd_global.conc_program_id,
fnd_global.conc_request_id,
fnd_global.current_language,
fnd_global.base_language,
fnd_global.org_id,
fnd_global.org_name
from dual;
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
Oracle Applications Query to know the fmb name if you know the form name and the path for its fmx and fmb as well
Oracle Applications Query to know the fmb name if you know the form name and the path for its fmx and fmb as well
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
SELECT
ffv.form_name, ffv.user_form_name, ffv.description,
(SELECT application_name FROM fnd_application_tl WHERE application_id = ffv.application_id) application_name,
(SELECT basepath FROM fnd_application WHERE application_id = ffv.application_id) basepath,
form_id,
audit_enabled_flag
FROM
fnd_form_vl ffv
WHERE
(form_id >= 0) AND UPPER (user_form_name) LIKE '%MASS CHANGE%'
-----Path we have to derive from BASEPATH variable displayed in query
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
SELECT
ffv.form_name, ffv.user_form_name, ffv.description,
(SELECT application_name FROM fnd_application_tl WHERE application_id = ffv.application_id) application_name,
(SELECT basepath FROM fnd_application WHERE application_id = ffv.application_id) basepath,
form_id,
audit_enabled_flag
FROM
fnd_form_vl ffv
WHERE
(form_id >= 0) AND UPPER (user_form_name) LIKE '%MASS CHANGE%'
-----Path we have to derive from BASEPATH variable displayed in query
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
Oracle Applications Query to get the actual concurrent program file executable if we know the concurrent program name
Oracle Applications Query to get the actual concurrent program file executable if we know the concurrent program name
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
SELECT
B.CONCURRENT_PROGRAM_NAME , A.USER_EXECUTABLE_NAME ,
DECODE( A.EXECUTION_METHOD_CODE , 'I' , 'PL/SQL Stored Procedure' , 'H' ,'Host' , 'S' ,'Immediate' , 'J' ,'Java Stored Procedure' , 'K', 'Java concurrent program' , 'M', 'Multi Language Function' , 'P', 'Oracle reports' , 'B', 'Request Set Stage Function' , 'A', 'Spawned' , 'L', 'SQL*Loader' , 'Q', 'SQL*Plus' , 'E', 'Pearl concurrent Programm' , 'Unkown Type') ,
A.EXECUTION_FILE_NAME , A.EXECUTION_FILE_PATH
FROM
FND_EXECUTABLES_FORM_V A , FND_CONCURRENT_PROGRAMS_VL B
WHERE
A.EXECUTABLE_ID = B.EXECUTABLE_ID AND A.APPLICATION_ID = B.APPLICATION_ID AND A.executable_id > 4 AND upper(B.USER_CONCURRENT_PROGRAM_NAME) LIKE 'XX INVENTORY CONVERSION%'
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
SELECT
B.CONCURRENT_PROGRAM_NAME , A.USER_EXECUTABLE_NAME ,
DECODE( A.EXECUTION_METHOD_CODE , 'I' , 'PL/SQL Stored Procedure' , 'H' ,'Host' , 'S' ,'Immediate' , 'J' ,'Java Stored Procedure' , 'K', 'Java concurrent program' , 'M', 'Multi Language Function' , 'P', 'Oracle reports' , 'B', 'Request Set Stage Function' , 'A', 'Spawned' , 'L', 'SQL*Loader' , 'Q', 'SQL*Plus' , 'E', 'Pearl concurrent Programm' , 'Unkown Type') ,
A.EXECUTION_FILE_NAME , A.EXECUTION_FILE_PATH
FROM
FND_EXECUTABLES_FORM_V A , FND_CONCURRENT_PROGRAMS_VL B
WHERE
A.EXECUTABLE_ID = B.EXECUTABLE_ID AND A.APPLICATION_ID = B.APPLICATION_ID AND A.executable_id > 4 AND upper(B.USER_CONCURRENT_PROGRAM_NAME) LIKE 'XX INVENTORY CONVERSION%'
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
Oracle Applications Query to get all the oracle apps responsibilities responsibility having by one oracle apps user
Oracle Applications Query to get all the oracle apps responsibilities responsibility having by one oracle apps user
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
SELECT
FRT.RESPONSIBILITY_NAME, FAT.APPLICATION_NAME, FURGD.START_DATE, FURGD.END_DATE
FROM
FND_USER_RESP_GROUPS_DIRECT FURGD, FND_RESPONSIBILITY_TL FRT, FND_RESPONSIBILITY FT, FND_APPLICATION_TL FAT, FND_USER FU
WHERE
FURGD.RESPONSIBILITY_ID = FRT.RESPONSIBILITY_ID AND FURGD.RESPONSIBILITY_APPLICATION_ID = FRT.APPLICATION_ID AND FURGD.RESPONSIBILITY_APPLICATION_ID = FAT.APPLICATION_ID AND FURGD.RESPONSIBILITY_ID = FT.RESPONSIBILITY_ID AND FURGD.RESPONSIBILITY_APPLICATION_ID = FT.APPLICATION_ID AND FURGD.USER_ID =FU.USER_ID AND FT.VERSION IN ('4','W','M','H') AND FU.USER_NAME= 'PGARG'
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
SELECT
FRT.RESPONSIBILITY_NAME, FAT.APPLICATION_NAME, FURGD.START_DATE, FURGD.END_DATE
FROM
FND_USER_RESP_GROUPS_DIRECT FURGD, FND_RESPONSIBILITY_TL FRT, FND_RESPONSIBILITY FT, FND_APPLICATION_TL FAT, FND_USER FU
WHERE
FURGD.RESPONSIBILITY_ID = FRT.RESPONSIBILITY_ID AND FURGD.RESPONSIBILITY_APPLICATION_ID = FRT.APPLICATION_ID AND FURGD.RESPONSIBILITY_APPLICATION_ID = FAT.APPLICATION_ID AND FURGD.RESPONSIBILITY_ID = FT.RESPONSIBILITY_ID AND FURGD.RESPONSIBILITY_APPLICATION_ID = FT.APPLICATION_ID AND FURGD.USER_ID =FU.USER_ID AND FT.VERSION IN ('4','W','M','H') AND FU.USER_NAME= 'PGARG'
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
Oracle Applications SQL Query to get all the apps users from FND_USER having particular Responsibility
Oracle Applications SQL Query to get all the apps users from FND_USER having particular Responsibility
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
SELECT
fu.user_name, frt.responsibility_name, fat.application_name, furgd.start_date, furgd.end_date
FROM
fnd_user_resp_groups_direct furgd, fnd_responsibility_tl frt, fnd_responsibility ft, fnd_application_tl fat, fnd_user fu
WHERE
furgd.responsibility_id = frt.responsibility_id AND furgd.responsibility_application_id = frt.application_id AND furgd.responsibility_application_id = fat.application_id AND furgd.responsibility_id = ft.responsibility_id AND furgd.responsibility_application_id = ft.application_id AND furgd.user_id = fu.user_id AND ft.VERSION IN ('4', 'W', 'M', 'H') AND frt.responsibility_name = 'Application Developer'
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
SELECT
fu.user_name, frt.responsibility_name, fat.application_name, furgd.start_date, furgd.end_date
FROM
fnd_user_resp_groups_direct furgd, fnd_responsibility_tl frt, fnd_responsibility ft, fnd_application_tl fat, fnd_user fu
WHERE
furgd.responsibility_id = frt.responsibility_id AND furgd.responsibility_application_id = frt.application_id AND furgd.responsibility_application_id = fat.application_id AND furgd.responsibility_id = ft.responsibility_id AND furgd.responsibility_application_id = ft.application_id AND furgd.user_id = fu.user_id AND ft.VERSION IN ('4', 'W', 'M', 'H') AND frt.responsibility_name = 'Application Developer'
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
Thursday, September 25, 2008
Oracle applications SQL Query to select all the functions Submenu comprises by a menu if you know its responsibility name
Oracle applications SQL Query to select all the functions Submenu comprises by a menu if you know its responsibility name
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
SELECT
0 num,
RPAD ('Hirarchy for ' '-' user_menu_name,60,'-') '(' menu_id ')--(Main Menu)' menu_hirarchy
FROM fnd_menus_vl
WHERE user_menu_name = 'ABM Intelligence'
UNION
SELECT
ROWNUM num,
RPAD ( LPAD (' ', 7 * LEVEL) entry_sequence '-' NVL (prompt, function_id),60,'-')
'(' NVL (sub_menu_id, function_id) ')--(' DECODE (sub_menu_id, NULL, 'Function', 'Sub Menu') ')'
menu_hirarchy
FROM fnd_menu_entries_vl
START WITH menu_id = (SELECT menu_id FROM fnd_menus_vl WHERE menu_id =
(SELECT menu_id FROM fnd_responsibility_vl WHERE UPPER (responsibility_name) =
'APPLICATION DEVELOPER'))
CONNECT BY PRIOR sub_menu_id = menu_id
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
SELECT
0 num,
RPAD ('Hirarchy for ' '-' user_menu_name,60,'-') '(' menu_id ')--(Main Menu)' menu_hirarchy
FROM fnd_menus_vl
WHERE user_menu_name = 'ABM Intelligence'
UNION
SELECT
ROWNUM num,
RPAD ( LPAD (' ', 7 * LEVEL) entry_sequence '-' NVL (prompt, function_id),60,'-')
'(' NVL (sub_menu_id, function_id) ')--(' DECODE (sub_menu_id, NULL, 'Function', 'Sub Menu') ')'
menu_hirarchy
FROM fnd_menu_entries_vl
START WITH menu_id = (SELECT menu_id FROM fnd_menus_vl WHERE menu_id =
(SELECT menu_id FROM fnd_responsibility_vl WHERE UPPER (responsibility_name) =
'APPLICATION DEVELOPER'))
CONNECT BY PRIOR sub_menu_id = menu_id
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
Subscribe to:
Posts (Atom)
