How to Enable Personalization link for OAF Pages(Self Service Pages)

Set the values of following profiles to enable Personalization Page link in OAF Pages



Profile Name                                                      Value

FND: Personalization Region Link Enabled            Yes

Personalize Self-Service Defn                                Yes

Disable Self-Service Personal                                 No


After Setting the Values of above profiles






Personalization link does not appear despite setting profile

There is a slight undocumented feature in OA Framework Personaliziations.

Requirement
You need to enable a personalization link against a user, by setting
profile option “Personalize Self-Service Defn”.


Action taken by you
You went into the System Administrator responsibility and assigned a value of Yes to
profile option “Personalize Self-Service Defn” for desired user.
However, user reports that they still can not see Personalization links.
Also, you notice that it is quite random, as in some cases, you do notice that
personalization link appears straightaway after setting that profile option.


Reason behind this
We know that profile options are cached. Sometimes old value of profile value is retrieved
from the cache. Hence you might be required to bounce the Apache server or
Global Cache for this profile option change to take effect.
This can be quite frustrating.


Solution
Instead of using System administrator, use responsibility named “Functional Administrator”.
It is noticed that if you change profile option value for “Personalize Self-Service Defn”
from this screen, then change made to this profile option value takes
effect immediately.



Steps are
1. Firstly ensure that your username has Functional Administrator responsibility



2. Search for the Profile Option, via Tab Core Services, and SubTab “Profiles”



3. Update the value of Profile Option to Yes for User Level or for the site level.

FA DEPRECIATION QUERY

FA DEPRECIATION QUERY



Data can come into Oracle Assets via the FA_MASS_ADDITIONS table from the following sources: external sources (i.e. legacy systems), Project Accounting (PA), Accounts Payable (AP) and indirectly from Purchasing (PO).

Once the data is posted in Oracle Assets, it is then considered to be a true asset. If an asset is setup as a capitalized asset, it can be depreciated. When Depreciation has ran successfully and the period has closed, you can then submit the Create Journal Entries program (FAPOST).

The Create Journal Entries program creates the journals for General Ledger (GL) and inserts that data directly into the GL tables, GL_JE_HEADERS, GL_JE_BATCHES, and GL_JE_LINES. Therefore, no journal import from within GL is necessary. If the Create Journals program was successful, the unposted journals can be viewed immediately in GL

SELECT
fp.period_name,
dhcc.segment2 business_unit,
dhcc.segment3 account,
dhcc.segment4 dept,
adt.asset_number,
adt.tag_number,
ltrim(rtrim(cat.segment1)) ||'-'|| ltrim(rtrim(cat.segment2)) ||'-'|| ltrim(rtrim(cat.segment3)) category,
bks.date_placed_in_service acquistion_date,
bks.original_cost,
adt.description,
dh.location_id,
adt.context subject_to_property_tax,
adt.attribute1 property_tax_code,
round(sum(decode(bks.period_counter_fully_retired, '',bks.cost, 0) * dh.units_assigned / ah.units), 2) COST, round(sum(nvl(dn.deprn_amount,0) * dh.units_assigned/ ah.units), 2) deprn,
round(sum(nvl(dn.deprn_reserve,0) * dh.units_assigned/ ah.units), 2) deprn_reserve,
round(sum(nvl(dn.ytd_deprn,0) * dh.units_assigned/ ah.units), 2) YTD_DEP,
round(sum(decode(bks.period_counter_fully_retired, '', (bks.cost - dn.deprn_reserve), 0) * dh.units_assigned/ ah.units), 2) c_nbv
FROM
fa_distribution_history dh,
fa_asset_history ah,
fa_additions adt,
fa_categories_b cat,
fa_books bks,
gl_code_combinations dhcc,
fa_deprn_summary dn,
fa_deprn_periods fp
WHERE
fp.book_type_code = 'XXX'
and dn.book_type_code = 'XXXX'
and dn.period_counter =
( select dp.period_counter from fa_deprn_periods dp where dp.book_type_code = 'XXXX'
and dp.period_counter =
( select max(dpz.period_counter) from fa_deprn_summary dsz, fa_deprn_periods dpz
where dpz.book_type_code = 'XXXX'
and dpz.period_counter <= fp.period_counter
and dsz.book_type_code = 'XXXX'
and dsz.period_counter = dpz.period_counter
and dsz.asset_id = dn.asset_id ))
and bks.book_type_code = 'XXXX'
and bks.asset_id = dn.asset_id
and nvl(bks.date_ineffective,sysdate) > to_date(to_char(nvl(fp.period_close_date, sysdate),'DD-MM-YYYY HH24:MI:SS'),'DD-MM-YYYY HH24:MI:SS')
and bks.date_effective < to_date(to_char(nvl(fp.period_close_date, sysdate),'DD-MM-YYYY HH24:MI:SS'),'DD-MM-YYYY HH24:MI:SS')
and nvl(bks.period_counter_fully_retired, fp.period_counter) in
( select dpy.period_counter
from fa_deprn_periods dpy
where dpy.book_type_code = 'XXXX'
and dpy.fiscal_year = fp.fiscal_year)
and adt.asset_id = dn.asset_id
and adt.ASSET_CATEGORY_ID = cat.category_id
and adt.asset_id = dh.asset_id
and dh.book_type_code = 'XXXX'
and nvl(dh.date_ineffective,sysdate) > to_date(to_char(nvl(fp.period_close_date, sysdate),'DD-MM-YYYY HH24:MI:SS'),'DD-MM-YYYY HH24:MI:SS')
and dh.date_effective < to_date(to_char(nvl(fp.period_close_date, sysdate),'DD-MM-YYYY
HH24:MI:SS'),'DD-MM-YYYY HH24:MI:SS')
and dhcc.code_combination_id(+) = dh.code_combination_id
and ah.asset_id = adt.asset_id
and nvl(ah.date_ineffective,sysdate) > to_date(to_char(nvl(fp.period_close_date, sysdate),'DD-MM-YYYY
HH24:MI:SS'),'DD-MM-YYYY HH24:MI:SS')
and ah.date_effective < to_date(to_char(nvl(fp.period_close_date, sysdate),'DD-MM-YYYY
HH24:MI:SS'),'DD-MM-YYYY HH24:MI:SS')
GROUP BY
fp.period_name,
dhcc.segment2,
dhcc.segment4,
dhcc.segment3,
adt.asset_number,
adt.tag_number,
ltrim(rtrim(cat.segment1))||'-'||ltrim(rtrim(cat.segment2))||'-'||ltrim(rtrim(cat.segment3)),
adt.description,
bks.date_placed_in_service,
bks.original_cost,
dh.location_id,
adt.context,
adt.attribute1

FA For getting YTD Depreciation..etc...

FA For getting YTD Depreciation..etc...
--CREATE OR REPLACE VIEW MVL_FA_LISTING_V
(BOOK_TYPE_CODE, ASSET_NUMBER, ASSET_ID, TAG_NUMBER, DATE_PLACED_IN_SERVICE,
DESCRIPTION, ASSET_CATEGORY, ASSET_CATEGORY1, ASSET_COST, ORIGINAL_ASSET_COST,
ASSET_LIFE, ACCUM_DEPRECIATION, YTD_DEPRECIATION, DEP_THIS_RUN, DEPARTMENT_NO,
CUSTODIAN, EMPLOYEE_NUMBER, PO_NUMBER, INVOICE_NUMBER, VENDOR_NUMBER,
VENDOR_NAME, SERIAL_NUMBER, LOCATION_FLEXFIELD, TAX_MAJOR_CATEGORY, TAX_MINOR_CATEGORY,
RETIREMENT_TYPE, ASSET_KEY, PERIOD_NAME, ACQUISITION_DATE, MANUFACTURER_NAME,
RETIREMENT_PENDING_FLAG)
AS
SELECT fbv.book_type_code
,fab.asset_number,fab.asset_id
,fab.tag_number
,fbv.date_placed_in_service
,fab.description
,fcb.segment1||'.'||fcb.segment2 asset_category
,fcb.segment1||'.'||fcb.segment2 asset_category1
,fbv.cost asset_cost
,fbv.original_cost original_asset_cost
,fbv.life_in_months asset_life
,mvl_discoverer_fin_support.get_depreciation_dtls(fab.asset_id
,fbv.book_type_code
,fdh.distribution_id
,fdp.period_counter
,'ACCUM') accum_depreciation
,mvl_discoverer_fin_support.get_depreciation_dtls(fab.asset_id
,fbv.book_type_code
,fdh.distribution_id
,fdp.period_counter
,'YTD') ytd_depreciation
,mvl_discoverer_fin_support.get_depreciation_dtls(fab.asset_id
,fbv.book_type_code
,fdh.distribution_id
,fdp.period_counter
,'RUN') dep_this_run
,gcc.segment2 department_no
,(SELECT ppx.full_name FROM per_people_x ppx WHERE ppx.person_id = fdh.assigned_to AND ROWNUM = 1) custodian
,(SELECT ppx.employee_number FROM per_people_x ppx WHERE ppx.person_id = fdh.assigned_to AND ROWNUM = 1) employee_number
,(SELECT po_number FROM fa_invoice_details_v WHERE asset_id = fbv.asset_id AND ROWNUM = 1) po_number
,(SELECT invoice_number FROM fa_invoice_details_v WHERE asset_id = fbv.asset_id AND ROWNUM = 1) invoice_number
,(SELECT vendor_number FROM fa_invoice_details_v WHERE asset_id = fbv.asset_id AND ROWNUM = 1) vendor_number
,(SELECT vendor_name FROM fa_invoice_details_v WHERE asset_id = fbv.asset_id AND ROWNUM = 1) vendor_name
,fab.serial_number
,fl.segment3||'.'||fl.segment4 location_flexfield
,fab.attribute1 tax_major_category
,fab.attribute2 tax_minor_category
,(SELECT retirement_type_code FROM fa_retirements WHERE retirement_id = fdh.retirement_id AND ROWNUM = 1) retirement_type
,(SELECT segment1||'.'||segment2 FROM fa_asset_keywords WHERE code_combination_id = fab.asset_key_ccid AND ROWNUM = 1) asset_key
,fdp.period_name
,fab.attribute3 acquisition_date
,fab.manufacturer_name
,fbv.retirement_pending_flag
FROM apps.fa_additions_v fab
,apps.fa_books_v fbv
,apps.fa_categories_b fcb
,apps.fa_deprn_periods fdp
,apps.fa_distribution_history fdh
,apps.gl_code_combinations gcc
,apps.fa_locations fl
WHERE 1=1
AND fab.asset_id = fbv.asset_id
AND fcb.category_id = fab.asset_category_id
AND fbv.transaction_header_id_out IS NULL
AND fdp.book_type_code = fbv.book_type_code
AND fdh.asset_id = fbv.asset_id
AND fdh.code_combination_id = gcc.code_combination_id
AND fdh.location_id=fl.location_id
AND fbv.transaction_header_id_out IS NULL
AND fdh.transaction_header_id_out IS NULL
/

***************************************PACKAGE Body***********************************

CREATE OR REPLACE PACKAGE BODY APPS.mvl_discoverer_fin_support IS

FUNCTION get_po_category(p_po_line_id NUMBER
,p_invoice_id NUMBER) RETURN VARCHAR2 IS

l_category_name VARCHAR2(200):= NULL;

BEGIN
SELECT segment1||'.'||segment2
INTO l_category_name
FROM mtl_categories_b mcb
,po_lines_all pla
WHERE mcb.category_id = pla.category_id
AND pla.po_line_id = p_po_line_id;

RETURN l_category_name;

EXCEPTION
WHEN NO_DATA_FOUND THEN
SELECT segment1||'.'||segment2
INTO l_category_name
FROM ap_invoice_distributions_all aid
,po_distributions_all pd
,po_lines_all pl
,mtl_categories_b mcb
WHERE aid.invoice_id = p_invoice_id
AND aid.po_distribution_id = pd.po_distribution_id
AND pd.po_line_id = pl.po_line_id
AND mcb.category_id = pl.category_id
AND ROWNUM = 1;

RETURN l_category_name;

WHEN OTHERS THEN
RETURN NULL;
END get_po_category;

--/* ------------------------------------------------------------------- */
-- FUNCTION get_depreciation_dtls
--/* ------------------------------------------------------------------- */

FUNCTION get_depreciation_dtls(p_asset_id NUMBER
,p_booktype_code VARCHAR2
,p_distribution_id NUMBER
,p_period_counter NUMBER
,p_type VARCHAR2) RETURN NUMBER IS

l_deprn_reserve NUMBER;

BEGIN
SELECT DECODE(p_type,'ACCUM',deprn_reserve,'YTD',ytd_deprn,'RUN',deprn_amount)
INTO l_deprn_reserve
FROM apps.fa_deprn_summary fdd
WHERE fdd.asset_id = p_asset_id
AND fdd.book_type_code = p_booktype_code
AND fdd.period_counter = p_period_counter;
RETURN l_deprn_reserve;

EXCEPTION

WHEN NO_DATA_FOUND THEN
SELECT DECODE(p_type,'ACCUM',deprn_reserve,'YTD',ytd_deprn,'RUN',0)
INTO l_deprn_reserve
FROM apps.fa_deprn_summary fdd
WHERE fdd.asset_id = p_asset_id
AND fdd.book_type_code = p_booktype_code
AND fdd.period_counter = (SELECT MAX(period_counter)
FROM apps.fa_deprn_detail fdd2
WHERE fdd2.asset_id = p_asset_id
AND fdd2.book_type_code = p_booktype_code);
RETURN l_deprn_reserve;

WHEN OTHERS THEN
RETURN 0;

END get_depreciation_dtls;

--/* ------------------------------------------------------------------- */
-- SCR#12634
-- FUNCTION get_invpo_match_rqstr_id
--/* ------------------------------------------------------------------- */
FUNCTION get_invpo_match_rqstr_id(p_invoice_id NUMBER) RETURN VARCHAR2 IS

ln_requestor_id PO_DISTRIBUTIONS_ALL.deliver_to_person_id%TYPE;

BEGIN
SELECT DISTINCT PDA2.deliver_to_person_id
INTO ln_requestor_id
FROM po_distributions_all PDA2
,ap_invoice_distributions_all AIDA2
WHERE AIDA2.po_distribution_id = PDA2.po_distribution_id
AND AIDA2.invoice_id = p_invoice_id
AND PDA2.deliver_to_person_id IS NOT NULL;

RETURN(ln_requestor_id);

EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN(NULL);
END get_invpo_match_rqstr_id;

--/* ------------------------------------------------------------------- */
-- SCR#12634
-- FUNCTION get_invpo_lnematch_rqstr_id
--/* ------------------------------------------------------------------- */
FUNCTION get_invpo_lnematch_rqstr_id(p_invoice_id NUMBER) RETURN VARCHAR2 IS

ln_po_header_id PO_HEADERS_ALL.po_header_id%TYPE;
ln_requestor_id PO_DISTRIBUTIONS_ALL.deliver_to_person_id%TYPE;

BEGIN

BEGIN
SELECT DISTINCT POD.po_header_id
INTO ln_po_header_id
FROM ap_invoice_distributions_all APID
,po_distributions_all POD
WHERE APID.po_distribution_id = POD.po_distribution_id
AND APID.invoice_id = p_invoice_id
AND rownum = 1;
EXCEPTION
WHEN NO_DATA_FOUND THEN
ln_requestor_id := NULL;
END;

IF ln_po_header_id IS NOT NULL THEN

BEGIN
SELECT DISTINCT deliver_to_person_id
INTO ln_requestor_id
FROM po_distributions_all
WHERE po_header_id = ln_po_header_id
AND deliver_to_person_id IS NOT NULL
AND rownum = 1;
EXCEPTION
WHEN NO_DATA_FOUND THEN
ln_requestor_id := NULL;
END;
END IF;

RETURN(ln_requestor_id);

END get_invpo_lnematch_rqstr_id;


FUNCTION get_wfr_rqstr(p_po_header_id PO_HEADERS_ALL.po_header_id%TYPE) RETURN VARCHAR2 IS

CURSOR cur_wfr_rqstr(p_po_header_id PO_HEADERS_ALL.po_header_id%TYPE) IS
SELECT DISTINCT POD.creation_date, POD.deliver_to_person_id, POA.agent_name
FROM po_distributions_all POD
,po_agents_v POA
WHERE POD.deliver_to_person_id = POA.agent_id(+)
AND POD.po_header_id = p_po_header_id
ORDER BY POD.creation_date;
ld_creation_date PO_DISTRIBUTIONS_ALL.creation_date%TYPE;
ln_deliver_to_person_id PO_DISTRIBUTIONS_ALL.deliver_to_person_id%TYPE;
lc_agent_name PO_AGENTS_V.agent_name%TYPE;
ln_ctr NUMBER:=0;
BEGIN

BEGIN
SELECT DISTINCT POD.deliver_to_person_id, POA.agent_name
INTO ln_deliver_to_person_id, lc_agent_name
FROM po_distributions_all POD
,po_agents_v POA
WHERE POD.deliver_to_person_id = POA.agent_id(+)
AND POD.po_header_id = p_po_header_id;
RETURN(lc_agent_name);
EXCEPTION
WHEN TOO_MANY_ROWS THEN
FOR rec_wfr_rqstr IN cur_wfr_rqstr(p_po_header_id) LOOP
ln_ctr := ln_ctr + 1;
lc_agent_name := rec_wfr_rqstr.agent_name;

IF ln_ctr = 1 THEN
EXIT;
END IF;
END LOOP;
RETURN(lc_agent_name);
END;

END get_wfr_rqstr;

END mvl_discoverer_fin_support;
/

*****************************************Package Header*********************

CREATE OR REPLACE PACKAGE APPS.mvl_discoverer_fin_support IS


FUNCTION get_po_category(p_po_line_id NUMBER,p_invoice_id NUMBER) RETURN VARCHAR2;

FUNCTION get_depreciation_dtls(p_asset_id NUMBER
,p_booktype_code VARCHAR2
,p_distribution_id NUMBER
,p_period_counter NUMBER
,p_type VARCHAR2) RETURN NUMBER;


FUNCTION get_invpo_match_rqstr_id(p_invoice_id NUMBER) RETURN VARCHAR2;


FUNCTION get_invpo_lnematch_rqstr_id(p_invoice_id NUMBER) RETURN VARCHAR2;


FUNCTION get_wfr_rqstr(p_po_header_id PO_HEADERS_ALL.po_header_id%TYPE) RETURN VARCHAR2;

END mvl_discoverer_fin_support;
/

Calculate Asset YTD Query

Calculate Asset YTD Query


select fb.book_type_code,
asset_number,
fat.description Asset_Desc,
fb.date_placed_in_service,
(fb.LIFE_IN_MONTHS/12) LIFE_IN_YEARS,
-- null invoice_number,
-- null invoice_desc,
-- null invoice_cost,
fb.cost,
fb.original_cost,
fds.deprn_amount,
fds.DEPRN_RESERVE ACC_DEPRN,
fds.ytd_deprn,
fb.deprn_method_code,
DECODE(fb.cost,0,0, (fb.COST - fds.DEPRN_RESERVE)) NET_BOOK_VALUE,
fdp.period_name,
-- null vendor_name,
fc.segment2 Major,
fc.segment3 Minor
from fa_additions_b fa,
fa_additions_tl fat,
fa_books fb,
fa_book_controls fbc,
fa_categories fc,
fa_deprn_summary fds,
fa_deprn_periods fdp
where fdp.period_name =:period_name
and fa.asset_id = fb.asset_id
and fa.asset_id = fat.asset_id(+)
and fa.asset_category_id = fc.category_id
and fb.book_type_code = fbc.book_type_code
and fb.BOOK_TYPE_CODE = fdp.BOOK_TYPE_CODE
and fds.ASSET_ID = fb.ASSET_ID
and fdp.PERIOD_COUNTER = DECODE(fbc.INITIAL_PERIOD_COUNTER,fds.PERIOD_COUNT ER,fds.PERIOD_COUNTER + 1 ,fds.PERIOD_COUNTER)
and fb.date_ineffective is NULL





Asset Opening

select substr(c.asset_number,6,1) asset_number,sum(a.deprn_reserve) nbt_depr_op_bal
from fa_deprn_summary a,fa_deprn_periods b,fa_additions c
where a.period_counter = b.period_counter
and b.calendar_period_close_date < pf_date
AND b.book_type_code = nbt_book_type
and a.asset_id = c.asset_id
and substr(c.asset_number,1,3) = p_orgid
and a.period_counter = (select max(d.period_counter) from fa_deprn_summary d,fa_deprn_periods e
where a.asset_id = d.asset_id
and d.period_counter = e.period_counter
and e.calendar_period_close_date < pf_date)
group by substr(c.asset_number,6,1);

Depreciation for the year

select sum(a.deprn_amount) into nbt_year_total
from fa_deprn_summary a,fa_deprn_periods b,fa_additions c
where a.period_counter = b.period_counter
and b.calendar_period_close_date between pf_date and pt_date
AND b.book_type_code = nbt_book_type
and a.asset_id = c.asset_id
and substr(c.asset_number,1,3) = p_orgid
and substr(c.asset_number,6,1) = c2_rec.asset_number;
exception when no_data_found then nbt_year_total := 0;
end;

Depreciation for the month

select sum(a.deprn_amount) into nbt_month_total
from fa_deprn_summary a,fa_deprn_periods b,fa_additions c
where a.period_counter = b.period_counter
AND to_char(b.calendar_period_close_date,'MONYYYY') = TO_CHAR(pt_date,'MONYYYY')
AND b.book_type_code = nbt_book_type
and a.asset_id = c.asset_id
and substr(c.asset_number,1,3) = p_orgid
and substr(c.asset_number,6,1) = c2_rec.asset_number;
exception when no_data_found then nbt_year_total := 0;
end;

FIXED ASSETS

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’.

ASSET  CATEGORIES

SELECT DISTINCT a.segment1||'.'||a.segment2 CATEGORY,a.segment1 MAJOR_CATEGORY
,  a.segment2 MINOR_CATEGORY
,  gl1.segment1||'.'||gl1.SEGMENT2||'.'||gl1.SEGMENT3||'.'||gl1.SEGMENT4||'.'||gl1.SEGMENT5 ASSET_COST
,  gl2.segment1||'.'||gl2.SEGMENT2||'.'||gl2.SEGMENT3||'.'||gl2.SEGMENT4||'.'||gl2.SEGMENT5 ASSET_COST_CLEARING
,  c.DEPRN_EXPENSE_ACCT
,  gl3.segment1||'.'||gl3.SEGMENT2||'.'||gl3.SEGMENT3||'.'||gl3.SEGMENT4||'.'||gl3.SEGMENT5 DEPRN_RESERVE_ACCOUNT
,  deprn_method
,  life_in_months
,  (life_in_months/12) lIFE
,  prorate_convention_code
FROM fa_categories a
, FA_CATEGORY_BOOK_DEFAULTS b
, fa_category_books c
, gl_code_combinations gl1
, gl_code_combinations gl2
, gl_code_combinations gl3
WHERE a.category_id = b.category_id
AND c.category_id = b.category_id
AND c.category_id = a.category_id
AND a.ENABLED_FLAG = 'Y'
AND c.ASSET_COST_ACCOUNT_CCID = gl1.CODE_COMBINATION_ID
AND c.ASSET_CLEARING_ACCOUNT_CCID = gl2.CODE_COMBINATION_ID
AND c.RESERVE_ACCOUNT_CCID = gl3.CODE_COMBINATION_ID
--AND c.WIP_COST_ACCOUNT_CCID = gl4.CODE_COMBINATION_ID
--AND c.WIP_CLEARING_ACCOUNT_CCID = gl5.CODE_COMBINATION_ID
AND b.book_type_code = c.book_type_code
AND c.book_type_code = 'CORP BOOK'

FA YTD Depreciation

FA For getting YTD Depreciation..etc...
--CREATE OR REPLACE VIEW MVL_FA_LISTING_V
(BOOK_TYPE_CODE, ASSET_NUMBER, ASSET_ID, TAG_NUMBER, DATE_PLACED_IN_SERVICE,
DESCRIPTION, ASSET_CATEGORY, ASSET_CATEGORY1, ASSET_COST, ORIGINAL_ASSET_COST,
ASSET_LIFE, ACCUM_DEPRECIATION, YTD_DEPRECIATION, DEP_THIS_RUN, DEPARTMENT_NO,
CUSTODIAN, EMPLOYEE_NUMBER, PO_NUMBER, INVOICE_NUMBER, VENDOR_NUMBER,
VENDOR_NAME, SERIAL_NUMBER, LOCATION_FLEXFIELD, TAX_MAJOR_CATEGORY, TAX_MINOR_CATEGORY,
RETIREMENT_TYPE, ASSET_KEY, PERIOD_NAME, ACQUISITION_DATE, MANUFACTURER_NAME,
RETIREMENT_PENDING_FLAG)
AS
SELECT fbv.book_type_code
,fab.asset_number,fab.asset_id
,fab.tag_number
,fbv.date_placed_in_service
,fab.description
,fcb.segment1||'.'||fcb.segment2 asset_category
,fcb.segment1||'.'||fcb.segment2 asset_category1
,fbv.cost asset_cost
,fbv.original_cost original_asset_cost
,fbv.life_in_months asset_life
,mvl_discoverer_fin_support.get_depreciation_dtls(fab.asset_id
,fbv.book_type_code
,fdh.distribution_id
,fdp.period_counter
,'ACCUM') accum_depreciation
,mvl_discoverer_fin_support.get_depreciation_dtls(fab.asset_id
,fbv.book_type_code
,fdh.distribution_id
,fdp.period_counter
,'YTD') ytd_depreciation
,mvl_discoverer_fin_support.get_depreciation_dtls(fab.asset_id
,fbv.book_type_code
,fdh.distribution_id
,fdp.period_counter
,'RUN') dep_this_run
,gcc.segment2 department_no
,(SELECT ppx.full_name FROM per_people_x ppx WHERE ppx.person_id = fdh.assigned_to AND ROWNUM = 1) custodian
,(SELECT ppx.employee_number FROM per_people_x ppx WHERE ppx.person_id = fdh.assigned_to AND ROWNUM = 1) employee_number
,(SELECT po_number FROM fa_invoice_details_v WHERE asset_id = fbv.asset_id AND ROWNUM = 1) po_number
,(SELECT invoice_number FROM fa_invoice_details_v WHERE asset_id = fbv.asset_id AND ROWNUM = 1) invoice_number
,(SELECT vendor_number FROM fa_invoice_details_v WHERE asset_id = fbv.asset_id AND ROWNUM = 1) vendor_number
,(SELECT vendor_name FROM fa_invoice_details_v WHERE asset_id = fbv.asset_id AND ROWNUM = 1) vendor_name
,fab.serial_number
,fl.segment3||'.'||fl.segment4 location_flexfield
,fab.attribute1 tax_major_category
,fab.attribute2 tax_minor_category
,(SELECT retirement_type_code FROM fa_retirements WHERE retirement_id = fdh.retirement_id AND ROWNUM = 1) retirement_type
,(SELECT segment1||'.'||segment2 FROM fa_asset_keywords WHERE code_combination_id = fab.asset_key_ccid AND ROWNUM = 1) asset_key
,fdp.period_name
,fab.attribute3 acquisition_date
,fab.manufacturer_name
,fbv.retirement_pending_flag
FROM apps.fa_additions_v fab
,apps.fa_books_v fbv
,apps.fa_categories_b fcb
,apps.fa_deprn_periods fdp
,apps.fa_distribution_history fdh
,apps.gl_code_combinations gcc
,apps.fa_locations fl
WHERE 1=1
AND fab.asset_id = fbv.asset_id
AND fcb.category_id = fab.asset_category_id
AND fbv.transaction_header_id_out IS NULL
AND fdp.book_type_code = fbv.book_type_code
AND fdh.asset_id = fbv.asset_id
AND fdh.code_combination_id = gcc.code_combination_id
AND fdh.location_id=fl.location_id
AND fbv.transaction_header_id_out IS NULL
AND fdh.transaction_header_id_out IS NULL
/
SELECT fnd_id_flex_segments.application_column_name,
fnd_id_flex_segments.segment_name, fnd_id_flex_segments.segment_num,
fnd_flex_values.flex_value_set_id,
fnd_flex_values.flex_value AS SEGMENT,
fnd_flex_values_tl.description, fnd_id_flex_segments.application_id, fnd_flex_values.PARENT_FLEX_VALUE_LOW as parent
FROM applsys.fnd_flex_values fnd_flex_values,
applsys.fnd_flex_values_tl fnd_flex_values_tl,
applsys.fnd_id_flex_segments fnd_id_flex_segments
WHERE fnd_flex_values.flex_value_id = fnd_flex_values_tl.flex_value_id
AND fnd_flex_values.flex_value_set_id =
fnd_id_flex_segments.flex_value_set_id
AND fnd_id_flex_segments.flex_value_set_id = 1008035
--      AND fnd_flex_values.flex_value <> 'T'
AND fnd_id_flex_segments.application_id = '140'
AND fnd_id_flex_segments.segment_num = 3
/
SELECT fa_additions.asset_id, disc_gl_set_of_books.set_of_books_id,
disc_gl_set_of_books.description AS set_of_books,
disc_gl_set_of_books.set_of_book_currency,
fa_additions.asset_number, fa_additions.tag_number,
fa_additions.description, fa_additions.manufacturer_name,
fa_additions.serial_number, fa_additions.model_number,
fa_categories_b.segment1, fa_categories_b.segment2,
fa_categories_b.segment3, fa_books.book_type_code,
ROUND (  fa_books.COST
* fa_distribution_history.units_assigned
/ fa_additions.current_units,
0
) COST,
fa_books.date_effective, fa_books.date_ineffective,
fa_books.date_placed_in_service, fa_books.deprn_start_date,
fa_books.original_cost, fa_additions.current_units,
fa_distribution_history.units_assigned, fa_books.life_in_months,
fa_employees.employee_number, fa_employees.NAME employee_name,
fa_locations.segment1 "STATE", fa_locations.segment2 "CITY",
fa_locations.segment3 "SITE", fa_locations.segment4 "LOCATOR",
fa_category_books.asset_cost_acct "ASSET_ACCOUNT_ID",
segment2.description AS "ASSET_ACCOUNT",
fa_book_controls.book_class, disc_ccid_dsc_mv.account_type,
disc_ccid_dsc_mv.code_combination_id, disc_ccid_dsc_mv.gl_seg1,
disc_ccid_dsc_mv.gl_seg2, disc_ccid_dsc_mv.gl_seg3,
disc_ccid_dsc_mv.gl_seg4, disc_ccid_dsc_mv.gl_seg5,
disc_ccid_dsc_mv.gl_seg6, disc_ccid_dsc_mv.gl_seg7,
disc_ccid_dsc_mv.gl_seg8, disc_ccid_dsc_mv.gl_seg9,
disc_ccid_dsc_mv.gl_seg10, disc_ccid_dsc_mv.gl_seg11,
disc_ccid_dsc_mv.gl_name_seg1, disc_ccid_dsc_mv.gl_name_seg2,
disc_ccid_dsc_mv.gl_name_seg3, disc_ccid_dsc_mv.gl_name_seg4,
disc_ccid_dsc_mv.gl_name_seg5, disc_ccid_dsc_mv.gl_name_seg6,
disc_ccid_dsc_mv.gl_name_seg7, disc_ccid_dsc_mv.gl_name_seg8,
disc_ccid_dsc_mv.gl_name_seg9, disc_ccid_dsc_mv.gl_name_seg10,
disc_ccid_dsc_mv.gl_name_seg11,
fa_books.date_placed_in_service "DATE_IN",
--TO_DATE(FA_DISTRIBUTION_HISTORY.DATE_INEFFECTIVE,'DD-MON-RRRR') "DATE_INEFFECTIVE",
--FA_BOOKS.DATE_INEFFECTIVE
NULL "DATE_IN_MONTH", fa_deprn_summary.deprn_amount,
fa_deprn_summary.ytd_deprn, fa_deprn_summary.deprn_reserve,
fa_deprn_summary.deprn_source_code,
fa_deprn_summary.addition_cost_to_clear adjusted_cost,
fa_deprn_periods.period_name
--      disc_fa_invoice_details.vendor_name,
--          disc_fa_invoice_details.invoice_number,
--          disc_fa_invoice_details.invoice_date,
--          disc_fa_invoice_details.fixed_assets_cost AS fa_cost_by_invoice
FROM   apps.fa_additions fa_additions,
fa.fa_book_controls fa_book_controls,
fa.fa_books fa_books,
fa.fa_categories_b fa_categories_b,
fa.fa_category_books fa_category_books,
fa.fa_distribution_history fa_distribution_history,
gl.gl_code_combinations gl_code_combinations,
apps.fa_employees fa_employees,
fa.fa_locations fa_locations,
apps.disc_ccid_dsc_mv disc_ccid_dsc_mv,
apps.disc_segment2 segment2,
apps.disc_gl_set_of_books disc_gl_set_of_books,
--          disc_gl_month in_month,
fa.fa_deprn_detail fa_deprn_summary,
fa.fa_deprn_periods fa_deprn_periods
--          disc_fa_invoice_details disc_fa_invoice_details
WHERE  fa_books.book_type_code = fa_book_controls.book_type_code
AND fa_books.asset_id = fa_additions.asset_id
AND fa_books.date_ineffective IS NULL
AND fa_distribution_history.book_type_code = fa_books.book_type_code
AND fa_distribution_history.asset_id = fa_additions.asset_id
AND fa_distribution_history.date_ineffective IS NULL
AND fa_categories_b.category_id = fa_additions.asset_category_id
AND fa_category_books.book_type_code =
fa_distribution_history.book_type_code
AND fa_category_books.category_id = fa_additions.asset_category_id
AND gl_code_combinations.code_combination_id =
fa_distribution_history.code_combination_id
AND fa_employees.employee_id(+) = fa_distribution_history.assigned_to
AND fa_locations.location_id = fa_distribution_history.location_id
AND gl_code_combinations.code_combination_id =
disc_ccid_dsc_mv.code_combination_id
AND segment2.SEGMENT = fa_category_books.asset_cost_acct
AND disc_gl_set_of_books.set_of_books_id =
fa_book_controls.set_of_books_id
--      AND fa_books.date_placed_in_service BETWEEN in_month.start_date AND in_month.end_date
AND fa_deprn_summary.asset_id = fa_distribution_history.asset_id
AND (    fa_deprn_periods.period_counter =
fa_deprn_summary.period_counter
AND fa_deprn_periods.book_type_code =
fa_deprn_summary.book_type_code
)
AND fa_deprn_summary.distribution_id =
fa_distribution_history.distribution_id
--      AND fa_books.asset_id = disc_fa_invoice_details.asset_id(+)
--      AND fa_deprn_summary.deprn_source_code = 'D'
--and fa_additions.asset_id = '10000767';
/
Posted by DILLI SUBRAMANI at 05/06/2013 03:54:00 pm 

account receivable(ar)important tables in oracle apps

account receivable(ar)important tables in oracle apps

--ACCOUNT RECIEVABLES TABLES

--TRANSACTIONS(INVOICES)

Select * from   RA_CUSTOMER_TRX_ALL

Select * from   RA_CUSTOMER_TRX_LINES_ALL

Select * from   RA_CUST_TRX_LINE_GL_DIST_ALL

Select * from   RA_CUST_TRX_LINE_SALESREPS_ALL

--CASH RECIEPTS

Select * from   AR_CASH_RECEIPTS_ALL

--INVOICE TYPES

Select * from   RA_CUST_TRX_TYPES_ALL

--SOURCE

Select * from   RA_BATCH_SOURCES_ALL

--SALES REPS

Select * from   RA_SALESREPS_ALL

--PAYMENT TERMS

Select * from   RA_TERMS_TL

Select * from   RA_TERMS_LINES

--RECEIPT PAYMENT METHOD

Select * from   AR_RECEIPT_METHODS

HOW DO SUBMIT A CONCURRENT REQUEST FROM PL/SQL?

FND_REQUEST.SUBMIT_REQUEST is an API used to submit a concurrent program in ORACLE Applications.

Parameters:

Application : Short name of the application associated with the concurrent request to be submitted.
Program - Short name of the concurrent program (not the executable) for which the request should be submitted.
Description - Description of the request that is displayed in the Concurrent Requests form (Optional.)
Start_time - Time at which the request should start running, formatted as HH24:MI or HH24:MI:SS (Optional.)
Sub_request - Set to TRUE if the request is submitted from another request and should be treated as a sub-request.
Argument1...100 - Arguments for the concurrent request; up to 100 arguments are permitted. If submitted from Oracle Forms, you must specify all 100 arguments.

Example: Submission of Standard Order Import Program


SET SERVEROUTPUT ON;
DECLARE
     v_request_id                        NUMBER           DEFAULT 0;
   
    --Order Import Parameters
    p_operating_unit                     VARCHAR2(20)    := NULL;
    p_order_source                       VARCHAR2(20)    := 'XYZ';
    p_orig_sys_document_ref              VARCHAR2(20)    := NULL;
    p_operation_code                     VARCHAR2(20)    := NULL;
    p_validate_only                      VARCHAR2(20)    := 'N';
    p_debug_level                        VARCHAR2(20)    := '1';
    p_num_instances                      VARCHAR2(20)    := '4';
    p_sold_to_org_id                     VARCHAR2(20)    := NULL;
    p_sold_to_org                        VARCHAR2(20)    := NULL;
    p_change_sequence                    VARCHAR2(20)    := NULL;
    p_perf_param                         VARCHAR2(20)    := 'Y';
    p_rtrim_data                         VARCHAR2(20)    := 'N';
    p_pro_ord_with_null_flag             VARCHAR2(20)    := 'Y';
    p_default_org_id                     VARCHAR2(20)    := '83';
    p_validate_desc_flex                 VARCHAR2(20)    := 'N';

    -- End of Parameters -----

    v_context varchar2(100);


    FUNCTION set_context( i_user_name    IN  VARCHAR2
                         ,i_resp_name    IN  VARCHAR2
                         ,i_org_id       IN  NUMBER)
    RETURN VARCHAR2
    IS
        /* Inorder to reduce the content of the post I moved the implementation part of this function to another post and it is available here */
    END set_context;


BEGIN
      -- Setting the context ----
      v_context := set_context('&V_USER_NAME','&V_RESPONSIBILITY',82);
      IF v_context = 'F'
      THEN
        DBMS_OUTPUT.PUT_LINE('Error while setting the context');      
      END IF;

      DBMS_OUTPUT.PUT_LINE('Submit Order Import Concurrent Program');

      v_request_id:=  FND_REQUEST.SUBMIT_REQUEST (
               application  =>  'ONT'
              ,program      =>  'OEOIMP'
              ,description  =>  'Order Import'
              ,start_time   =>  SYSDATE
              ,sub_request  =>  NULL
              ,argument1    =>  p_operating_unit
              ,argument2    =>  p_order_source
              ,argument3    =>  p_orig_sys_document_ref
              ,argument4    =>  p_operation_code
              ,argument5    =>  p_validate_only
              ,argument6    =>  p_debug_level
              ,argument7    =>  p_num_instances
              ,argument8    =>  p_sold_to_org_id
              ,argument9    =>  p_sold_to_org
              ,argument10   =>  p_change_sequence
              ,argument11   =>  p_perf_param
              ,argument12   =>  p_rtrim_data
              ,argument13   =>  p_pro_ord_with_null_flag
              ,argument14   =>  p_default_org_id
              ,argument15   =>  p_validate_desc_flex
             );

       COMMIT;

       DBMS_OUTPUT.PUT_LINE('Request_id: '||v_request_id);

EXCEPTION WHEN OTHERS THEN      
       DBMS_OUTPUT.PUT_LINE(SQLCODE||' Error :'||SQLERRM);
END;