/* Formatted on 20-02-2014 04:08:22 PM (QP5 v5.163.1008.3004) */
SELECT fp.period_name,
adt.asset_number,
adt.asset_type,
adt.current_units,
adt.tag_number,
-- LTRIM (RTRIM (cat.segment1))
-- || '-'
-- || LTRIM (RTRIM (cat.segment2))
-- || '-'
-- || LTRIM (RTRIM (cat.segment3))
-- category,
LTRIM (RTRIM (cat.segment1)) major,
LTRIM (RTRIM (cat.segment2)) miner,
LTRIM (RTRIM (cat.segment3)) sub_category,
/* (SELECT x2.description
FROM fnd_flex_values_vl x2, fa_categories_b y2
WHERE x2.flex_value_set_id = 1015080
AND x2.flex_value = y2.segment3
AND y2.category_id = cat.category_id)
Asset_Category_Description,*/
xx_fa_desc (cat.segment3) Asset_Category_Description,
bks.date_placed_in_service,
bks.life_in_months,
bks.original_cost,
adt.description,
dh.location_id,
fl.segment1,
adt.attribute1 property_tax_code,
bks.deprn_method_code,
ROUND (
SUM (
DECODE (bks.period_counter_fully_retired, '', bks.cost, 0)
* dh.units_assigned
/ ah.units),
2)
COST,
bks.original_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,
-- ROUND (SUM (NVL (dn.deprn_amount, 0) * dh.units_assigned / ah.units),
-- 2)
-- - (NVL (dn.deprn_adjustment_amount, 0)
-- - NVL (dn.bonus_deprn_adjustment_amount, 0))
-- - NVL (dn.bonus_deprn_amount, 0) ptd,
ROUND (
SUM (
dn.deprn_amount
- (NVL (dn.deprn_adjustment_amount, 0)
- NVL (dn.bonus_deprn_adjustment_amount, 0))
- NVL (dn.bonus_deprn_amount, 0)),
2)
ptd,
bks.date_placed_in_service,
--fp.period_name,
fcb.asset_cost_acct asset_cost_acct,
fcb.deprn_expense_acct deprn_acct,
fcb.deprn_reserve_acct acc_deprn_acct,
fbc.attribute1,
hou.name,
dhcc.segment4,
apps.gl_flexfields_pkg.get_description_sql (dhcc.chart_of_accounts_id,
4,
dhcc.segment4)
segment3_des,
NULL loc_seg2_desc,
NULL loc_seg3_desc
FROM fa_distribution_history dh,
fa_asset_history ah,
fa_additions adt,
fa_categories_b cat,
fa_category_books fcb,
fa_books bks,
gl_code_combinations dhcc,
fa_deprn_summary dn,
fa_deprn_periods fp,
fa_book_controls fbc,
fa_locations fl,
hr_operating_units hou
WHERE fp.book_type_code = 'PIUDCL_NAG_FAB'
AND dn.book_type_code = 'PIUDCL_NAG_FAB'
AND dn.period_counter =
(SELECT dp.period_counter
FROM fa_deprn_periods dp
WHERE dp.book_type_code = 'PIUDCL_NAG_FAB'
AND dp.period_counter =
(SELECT MAX (dpz.period_counter)
FROM fa_deprn_summary dsz,
fa_deprn_periods dpz
WHERE dpz.book_type_code = 'PIUDCL_NAG_FAB'
AND dpz.period_counter <=
fp.period_counter
AND dsz.book_type_code =
'PIUDCL_NAG_FAB'
AND dsz.period_counter =
dpz.period_counter
AND dsz.asset_id = dn.asset_id))
AND bks.book_type_code = 'PIUDCL_NAG_FAB'
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 = 'PIUDCL_NAG_FAB'
AND dpy.fiscal_year = fp.fiscal_year)
AND adt.asset_id = dn.asset_id
AND adt.ASSET_CATEGORY_ID = cat.category_id
AND cat.category_id = fcb.category_id
AND adt.asset_id = dh.asset_id
AND fcb.book_type_code = 'PIUDCL_NAG_FAB'
AND dh.book_type_code = 'PIUDCL_NAG_FAB'
--AND fp.period_name = :from_period
AND dh.location_id = fl.location_id
--and fl.segment1 not in('None')
AND bks.book_type_code = fbc.book_type_code
AND fbc.book_type_code = 'PIUDCL_NAG_FAB'
--and :to_period
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')
AND fbc.attribute1 = hou.name
AND dh.date_ineffective IS NULL
AND bks.date_ineffective IS NULL
--and hou.name=:p_operating_name
-- and bks.book_type_code=:p_book_type_code
--AND bks.date_placed_in_service between nvl(:p_from_date,(bks.date_placed_in_service)) and nvl(:p_to_date,(bks.date_placed_in_service))
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,
fcb.asset_cost_acct,
fcb.deprn_reserve_acct,
fbc.attribute1,
fl.segment1,
dn.deprn_adjustment_amount,
dn.bonus_deprn_adjustment_amount,
dn.bonus_deprn_amount,
bks.deprn_method_code,
cat.category_id,
dn.deprn_amount,
dhcc.chart_of_accounts_id,
fcb.deprn_expense_acct,
adt.asset_type,
adt.current_units,
cat.segment1,
cat.segment2,
cat.segment3,
bks.original_cost,
bks.life_in_months,
hou.name
--fp.period_name
ORDER BY asset_number;
Function Query
================
CREATE OR REPLACE FUNCTION APPS.xx_fa_desc (
p_segment3 varchar2
)
RETURN varchar2
IS
v_desc varchar2(150);
BEGIN
dbms_output.put_line (p_segment3 || ' SEG3');
BEGIN
SELECT RTRIM
(XMLAGG (XMLELEMENT (E, DESCRIPTION || ',')).EXTRACT
('//text()'),
','
)DESCRIPTION into v_desc
FROM (
SELECT distinct x2.description
FROM fnd_flex_values_vl x2 , fa_categories_b y2
WHERE x2.flex_value_set_id = 1015080
and x2.flex_value = y2.segment3
and x2.flex_value = p_segment3);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
v_desc := null;
END;
RETURN v_desc;
END;
/
SELECT fp.period_name,
adt.asset_number,
adt.asset_type,
adt.current_units,
adt.tag_number,
-- LTRIM (RTRIM (cat.segment1))
-- || '-'
-- || LTRIM (RTRIM (cat.segment2))
-- || '-'
-- || LTRIM (RTRIM (cat.segment3))
-- category,
LTRIM (RTRIM (cat.segment1)) major,
LTRIM (RTRIM (cat.segment2)) miner,
LTRIM (RTRIM (cat.segment3)) sub_category,
/* (SELECT x2.description
FROM fnd_flex_values_vl x2, fa_categories_b y2
WHERE x2.flex_value_set_id = 1015080
AND x2.flex_value = y2.segment3
AND y2.category_id = cat.category_id)
Asset_Category_Description,*/
xx_fa_desc (cat.segment3) Asset_Category_Description,
bks.date_placed_in_service,
bks.life_in_months,
bks.original_cost,
adt.description,
dh.location_id,
fl.segment1,
adt.attribute1 property_tax_code,
bks.deprn_method_code,
ROUND (
SUM (
DECODE (bks.period_counter_fully_retired, '', bks.cost, 0)
* dh.units_assigned
/ ah.units),
2)
COST,
bks.original_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,
-- ROUND (SUM (NVL (dn.deprn_amount, 0) * dh.units_assigned / ah.units),
-- 2)
-- - (NVL (dn.deprn_adjustment_amount, 0)
-- - NVL (dn.bonus_deprn_adjustment_amount, 0))
-- - NVL (dn.bonus_deprn_amount, 0) ptd,
ROUND (
SUM (
dn.deprn_amount
- (NVL (dn.deprn_adjustment_amount, 0)
- NVL (dn.bonus_deprn_adjustment_amount, 0))
- NVL (dn.bonus_deprn_amount, 0)),
2)
ptd,
bks.date_placed_in_service,
--fp.period_name,
fcb.asset_cost_acct asset_cost_acct,
fcb.deprn_expense_acct deprn_acct,
fcb.deprn_reserve_acct acc_deprn_acct,
fbc.attribute1,
hou.name,
dhcc.segment4,
apps.gl_flexfields_pkg.get_description_sql (dhcc.chart_of_accounts_id,
4,
dhcc.segment4)
segment3_des,
NULL loc_seg2_desc,
NULL loc_seg3_desc
FROM fa_distribution_history dh,
fa_asset_history ah,
fa_additions adt,
fa_categories_b cat,
fa_category_books fcb,
fa_books bks,
gl_code_combinations dhcc,
fa_deprn_summary dn,
fa_deprn_periods fp,
fa_book_controls fbc,
fa_locations fl,
hr_operating_units hou
WHERE fp.book_type_code = 'PIUDCL_NAG_FAB'
AND dn.book_type_code = 'PIUDCL_NAG_FAB'
AND dn.period_counter =
(SELECT dp.period_counter
FROM fa_deprn_periods dp
WHERE dp.book_type_code = 'PIUDCL_NAG_FAB'
AND dp.period_counter =
(SELECT MAX (dpz.period_counter)
FROM fa_deprn_summary dsz,
fa_deprn_periods dpz
WHERE dpz.book_type_code = 'PIUDCL_NAG_FAB'
AND dpz.period_counter <=
fp.period_counter
AND dsz.book_type_code =
'PIUDCL_NAG_FAB'
AND dsz.period_counter =
dpz.period_counter
AND dsz.asset_id = dn.asset_id))
AND bks.book_type_code = 'PIUDCL_NAG_FAB'
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 = 'PIUDCL_NAG_FAB'
AND dpy.fiscal_year = fp.fiscal_year)
AND adt.asset_id = dn.asset_id
AND adt.ASSET_CATEGORY_ID = cat.category_id
AND cat.category_id = fcb.category_id
AND adt.asset_id = dh.asset_id
AND fcb.book_type_code = 'PIUDCL_NAG_FAB'
AND dh.book_type_code = 'PIUDCL_NAG_FAB'
--AND fp.period_name = :from_period
AND dh.location_id = fl.location_id
--and fl.segment1 not in('None')
AND bks.book_type_code = fbc.book_type_code
AND fbc.book_type_code = 'PIUDCL_NAG_FAB'
--and :to_period
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')
AND fbc.attribute1 = hou.name
AND dh.date_ineffective IS NULL
AND bks.date_ineffective IS NULL
--and hou.name=:p_operating_name
-- and bks.book_type_code=:p_book_type_code
--AND bks.date_placed_in_service between nvl(:p_from_date,(bks.date_placed_in_service)) and nvl(:p_to_date,(bks.date_placed_in_service))
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,
fcb.asset_cost_acct,
fcb.deprn_reserve_acct,
fbc.attribute1,
fl.segment1,
dn.deprn_adjustment_amount,
dn.bonus_deprn_adjustment_amount,
dn.bonus_deprn_amount,
bks.deprn_method_code,
cat.category_id,
dn.deprn_amount,
dhcc.chart_of_accounts_id,
fcb.deprn_expense_acct,
adt.asset_type,
adt.current_units,
cat.segment1,
cat.segment2,
cat.segment3,
bks.original_cost,
bks.life_in_months,
hou.name
--fp.period_name
ORDER BY asset_number;
Function Query
================
CREATE OR REPLACE FUNCTION APPS.xx_fa_desc (
p_segment3 varchar2
)
RETURN varchar2
IS
v_desc varchar2(150);
BEGIN
dbms_output.put_line (p_segment3 || ' SEG3');
BEGIN
SELECT RTRIM
(XMLAGG (XMLELEMENT (E, DESCRIPTION || ',')).EXTRACT
('//text()'),
','
)DESCRIPTION into v_desc
FROM (
SELECT distinct x2.description
FROM fnd_flex_values_vl x2 , fa_categories_b y2
WHERE x2.flex_value_set_id = 1015080
and x2.flex_value = y2.segment3
and x2.flex_value = p_segment3);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
v_desc := null;
END;
RETURN v_desc;
END;
/
No comments:
Post a Comment