[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
ad.asset_number, ad.description,
ad.serial_number, ad.tag_number, ad.inventorial,
cb.deprn_reserve_acct,
DECODE(AH.ASSET_TYPE, 'CIP', CB.CIP_COST_ACCT,
CB.ASSET_COST_ACCT) ,
-- dd.ytd_deprn, dd.deprn_reserve,
TH.TRANSACTION_HEADER_ID, th.mass_reference_id,
tdh.distribution_id,
tascc.code_combination_id, fascc.code_combination_id,
tloc.location_id, floc.location_id,
temp.full_name, temp.employee_number,
femp.full_name, femp.employee_number,
TH.TRANSACTION_DATE_ENTERED,
ad.asset_type,
SUM(CADJ.ADJUSTMENT_AMOUNT*
DECODE(CADJ.DEBIT_CREDIT_FLAG,'CR',-1,'DR',1)),
SUM(TDH.UNITS_ASSIGNED)
FROM
fa_category_books cb,
fa_asset_history ah,
FA_LOCATIONS TLOC, FA_LOCATIONS FLOC,
PER_ALL_PEOPLE_F Temp,
PER_ALL_PEOPLE_F FEMP,
FA_ADDITIONS AD,
GL_CODE_COMBINATIONS TASCC, GL_CODE_COMBINATIONS FASCC,
-- fa_deprn_detail dd,
FA_DISTRIBUTION_HISTORY TDH, FA_DISTRIBUTION_HISTORY FDH,
FA_TRANSACTION_HEADERS TH,
FA_ADJUSTMENTS CADJ
WHERE
AH.ASSET_ID = TH.ASSET_ID AND
TH.DATE_EFFECTIVE >= AH.DATE_EFFECTIVE AND
TH.DATE_EFFECTIVE < NVL(AH.DATE_INEFFECTIVE,SYSDATE)
AND
CB.CATEGORY_ID = AH.CATEGORY_ID AND
CB.BOOK_TYPE_CODE = TH.BOOK_TYPE_CODE
AND
TH.BOOK_TYPE_CODE = h_book AND
TH.TRANSACTION_TYPE_CODE = 'TRANSFER' AND
TH.DATE_EFFECTIVE >= H_PERIOD1_POD AND
TH.DATE_EFFECTIVE <= nvl(H_PERIOD2_PCD,sysdate)
AND
TH.TRANSACTION_HEADER_ID = TDH.TRANSACTION_HEADER_ID_IN AND
TH.TRANSACTION_HEADER_ID = FDH.TRANSACTION_HEADER_ID_OUT
AND
AD.ASSET_ID = TH.ASSET_ID
--AND dd.asset_id = dh.asset_id and
-- dd.book_type_code = dh.book_type_code and
-- dd.distribution_id = dh.distribution_id and
-- dd.period_counter = (select max(dd1.period_counter) from
-- fa_deprn_detail dd1 where
-- dd1.distribution_id = dd.distribution_id
-- and dd1.book_type_code = dd.book_type_code
-- and dd1.asset_id = dd.asset_id)
AND
FLOC.LOCATION_ID = FDH.LOCATION_ID AND
TLOC.LOCATION_ID = TDH.LOCATION_ID
AND
Temp.person_id (+) = Tdh.assigned_to
AND TRUNC(SYSDATE) BETWEEN TEMP.EFFECTIVE_START_DATE(+) AND TEMP.EFFECTIVE_END_DATE(+)
AND FEMP.PERSON_ID (+) = FDH.ASSIGNED_TO
AND TRUNC(SYSDATE) BETWEEN FEMP.EFFECTIVE_START_DATE(+) AND FEMP.EFFECTIVE_END_DATE(+)
AND
TASCC.CODE_COMBINATION_ID = TDH.CODE_COMBINATION_ID AND
FASCC.CODE_COMBINATION_ID = FDH.CODE_COMBINATION_ID
AND
CADJ.BOOK_TYPE_CODE = h_book AND
CADJ.ASSET_ID = TH.ASSET_ID AND
CADJ.DISTRIBUTION_ID = TDH.DISTRIBUTION_ID AND
CADJ.TRANSACTION_HEADER_ID = TH.TRANSACTION_HEADER_ID AND
CADJ.SOURCE_TYPE_CODE = 'TRANSFER' AND
CADJ.ADJUSTMENT_TYPE in ('COST','CIP COST')
GROUP BY
ad.asset_number, ad.description,
ad.serial_number, ad.tag_number, ad.inventorial,
cb.deprn_reserve_acct,
DECODE(AH.ASSET_TYPE, 'CIP', CB.CIP_COST_ACCT,
CB.ASSET_COST_ACCT) ,
-- dd.ytd_deprn, dd.deprn_reserve,
TH.TRANSACTION_HEADER_ID,th.mass_reference_id,
TDH.DISTRIBUTION_ID,
tascc.code_combination_id, fascc.code_combination_id,
tloc.location_id, floc.location_id,
temp.full_name, temp.employee_number,
femp.full_name, femp.employee_number,
AD.ASSET_NUMBER, ad.description, ad.serial_number, ad.tag_number,
TH.TRANSACTION_DATE_ENTERED,
AD.asset_type;
SELECT
ad.asset_number,
cb.deprn_reserve_acct,
SUM(NVL(RADJ.ADJUSTMENT_AMOUNT,0) *
DECODE(NVL(RADJ.DEBIT_CREDIT_FLAG,'CR'),'CR',1,'DR',-1) )
FROM
fa_category_books cb,
FA_ADDITIONS AD,
FA_ASSET_HISTORY AH,
FA_TRANSACTION_HEADERS TH,
FA_ADJUSTMENTS RADJ
WHERE
AD.ASSET_ID = TH.ASSET_ID AND
TH.ASSET_ID = AH.ASSET_ID
AND
TH.DATE_EFFECTIVE >= AH.DATE_EFFECTIVE AND
TH.DATE_EFFECTIVE < NVL(AH.DATE_INEFFECTIVE,SYSDATE)
AND
CB.CATEGORY_ID = AH.CATEGORY_ID AND
CB.BOOK_TYPE_CODE = TH.BOOK_TYPE_CODE
AND
TH.BOOK_TYPE_CODE = h_book AND
TH.TRANSACTION_TYPE_CODE = 'TRANSFER' AND
TH.DATE_EFFECTIVE >= H_PERIOD1_POD AND
TH.DATE_EFFECTIVE <= nvl(H_PERIOD2_PCD,sysdate)
AND
RADJ.BOOK_TYPE_CODE = h_book AND
RADJ.ASSET_ID = TH.ASSET_ID AND
radj.distribution_id = h_distid and
RADJ.TRANSACTION_HEADER_ID = TH.TRANSACTION_HEADER_ID AND
RADJ.SOURCE_TYPE_CODE = 'TRANSFER' AND
RADJ.ADJUSTMENT_TYPE = 'RESERVE'
GROUP BY
ad.asset_number,
cb.deprn_reserve_acct;
select fcr.last_update_login into h_login_id
from fnd_concurrent_requests fcr
where fcr.request_id = h_request_id;
select period_open_date
into h_period1_pod
from fa_deprn_periods
where book_type_code = h_book and period_name = begin_period;
select count(*) into h_count
from fa_deprn_periods where period_name = end_period
and book_type_code = h_book;
select period_close_date
into h_period2_pcd
from fa_deprn_periods
where book_type_code = h_book and period_name = end_period;
select location_flex_structure
into h_loc_structure
from fa_system_controls;
select accounting_flex_structure
into h_acct_structure
from fa_book_controls
where book_type_code = h_book;
insert into fa_transfer_rep_itf (
request_id, asset_number, description, serial_number, tag_number,
to_company, to_cost_center, to_expense_acct,
from_company, from_cost_center, from_expense_acct,
reserve_acct, cost_acct, inventorial,
transaction_header_id, mass_transfer_id, to_location, from_location,
transaction_date, to_employee_name, to_employee_number,
from_employee_name, from_employee_number,
cost_transferred, reserve_transferred, units_transferred,
created_by, creation_date, last_updated_by, last_update_date,
last_update_login, asset_type)
values (request_id, h_asset_number, h_description, h_serial_number,
h_tag_number,
h_to_acct_segs(h_bal_seg), h_to_acct_segs(h_cc_seg),
h_to_acct_segs(h_acct_seg), h_from_acct_segs(h_bal_seg),
h_from_acct_segs(h_cc_seg), h_from_acct_segs(h_acct_seg),
h_reserve_acct, h_cost_acct, h_inventorial, h_thid, h_mass_ref_id,
h_concat_to_loc, h_concat_from_loc, h_trx_date,
h_to_emp_name, h_to_emp_number, h_from_emp_name, h_from_emp_number,
h_tfr_cost, h_tfr_reserve, h_tfr_units,
user_id, sysdate, user_id, sysdate, h_login_id, h_asset_type);
if h_mesg_name = 'FA_SHARED_INSERT_FAIL' then
fnd_message.set_token('TABLE','FA_TRANSFER_REP_ITF',FALSE);