[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT TH.MASS_REFERENCE_ID,
dhcc.code_combination_id,
cat_bk.category_id, dh.location_id,
emp.name, emp.employee_number,
DECODE(AH.ASSET_TYPE, 'CIP',CAT_BK.CIP_COST_ACCT,
CAT_BK.ASSET_COST_ACCT),
AD.ASSET_NUMBER,
ad.description, ad.tag_number, ad.serial_number, ad.inventorial,
bk_out.cost, bk_in.cost,
DECODE(NVL(PO_IN.SEGMENT1,PO_OUT.SEGMENT1),NULL,NULL,
NVL(PO_IN.SEGMENT1,PO_OUT.SEGMENT1)||' - '||
NVL(PO_IN.VENDOR_NAME,PO_OUT.VENDOR_NAME)),
nvl(AI_IN.invoice_number,AI_OUT.invoice_number) ,
/* bug#9166346 */
nvl(AI_IN.INVOICE_LINE_NUMBER, AI_OUT.INVOICE_LINE_NUMBER),
NVL(AI_IN.AP_DISTRIBUTION_LINE_NUMBER, AI_OUT.AP_DISTRIBUTION_LINE_NUMBER),
TH.TRANSACTION_HEADER_ID,
NVL(AI_IN.DESCRIPTION,AI_OUT.DESCRIPTION),
ROUND(SUM((DH.UNITS_ASSIGNED/AH.UNITS) *
(
decode(it.transaction_type,'INVOICE DELETE',
0-NVL(AI_IN.FIXED_ASSETS_COST,0),
'INVOICE REINSTATE',
NVL(AI_IN.FIXED_ASSETS_COST,0),
NVL(AI_IN.FIXED_ASSETS_COST,0)-NVL(AI_OUT.FIXED_ASSETS_COST,0)
)
)), h_precision),
ROUND(SUM((DH.UNITS_ASSIGNED/AH.UNITS) *
DECODE(TH.INVOICE_TRANSACTION_ID,NULL,
(NVL(BK_IN.COST,0) - NVL(BK_OUT.COST,0)),
(
decode(it.transaction_type,'INVOICE DELETE',
0-NVL(AI_IN.FIXED_ASSETS_COST,0),
'INVOICE REINSTATE',
NVL(AI_IN.FIXED_ASSETS_COST,0),
NVL(AI_IN.FIXED_ASSETS_COST,0)-NVL(AI_OUT.FIXED_ASSETS_COST,0)
)
))), h_precision),
DECODE(IT.TRANSACTION_TYPE,'INVOICE ADDITION','M',
'INVOICE ADJUSTMENT','A',
'INVOICE TRANSFER','T',
'INVOICE DELETE','D',
'INVOICE REINSTATE','R',
NULL),
DECODE(IT.TRANSACTION_TYPE, NULL, 'NO', 'YES'),
GAD.ASSET_NUMBER GROUP_ASSET_NUMBER
FROM FA_INVOICE_TRANSACTIONS IT,
FA_ASSET_INVOICES AI_IN,
FA_ASSET_INVOICES AI_OUT,
FA_BOOKS BK_IN,
FA_BOOKS BK_OUT,
FA_TRANSACTION_HEADERS TH,
( select full_name name, employee_number, person_id employee_id
from per_people_f
where TRUNC(SYSDATE) BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE
) EMP,
FA_DISTRIBUTION_HISTORY DH,
FA_ASSET_HISTORY AH,
FA_CATEGORY_BOOKS CAT_BK,
FA_LOOKUPS FALU,
PO_VENDORS PO_IN,
PO_VENDORS PO_OUT,
FA_ADDITIONS AD,
GL_CODE_COMBINATIONS DHCC,
FA_BOOKS ACTIVE_BK,
FA_ADDITIONS_B GAD
WHERE
TH.TRANSACTION_TYPE_CODE IN ('ADJUSTMENT','CIP ADJUSTMENT') AND
TH.BOOK_TYPE_CODE = h_book AND
TH.INVOICE_TRANSACTION_ID = IT.INVOICE_TRANSACTION_ID (+) AND
TH.DATE_EFFECTIVE BETWEEN
h_period1_pod AND
nvl(h_period2_pcd,sysdate)
-- added to get the active group asset
-- in respect to the group active at end of last period
AND ACTIVE_BK.book_type_code = h_book AND
ACTIVE_BK.ASSET_ID = TH.ASSET_ID AND
ACTIVE_BK.date_effective <= nvl(h_period2_pcd,sysdate) AND
NVL(ACTIVE_BK.date_ineffective, sysdate) >= nvl(h_period2_pcd,sysdate) AND
ACTIVE_BK.group_asset_id = gad.asset_id (+)
AND
DH.TRANSACTION_HEADER_ID_IN <= TH.TRANSACTION_HEADER_ID AND
NVL(DH.TRANSACTION_HEADER_ID_OUT, TH.TRANSACTION_HEADER_ID +1)
> TH.TRANSACTION_HEADER_ID AND
/*fix for bug no.3803578 */
DH.BOOK_TYPE_CODE = h_distribution_source_book AND
DH.ASSET_ID = TH.ASSET_ID AND
DH.CODE_COMBINATION_ID = DHCC.CODE_COMBINATION_ID
AND
emp.employee_id (+) = dh.assigned_to
AND
CAT_BK.CATEGORY_ID = AH.CATEGORY_ID AND
CAT_BK.BOOK_TYPE_CODE = h_book
AND
BK_IN.COST <> BK_OUT.COST
AND
AD.ASSET_ID = TH.ASSET_ID
AND
BK_IN.ASSET_ID(+) = TH.ASSET_ID AND
BK_IN.BOOK_TYPE_CODE(+) = h_book AND
BK_IN.TRANSACTION_HEADER_ID_IN(+) = TH.TRANSACTION_HEADER_ID
AND
BK_OUT.ASSET_ID(+) = TH.ASSET_ID AND
BK_OUT.BOOK_TYPE_CODE(+)||'' = h_book AND
BK_OUT.TRANSACTION_HEADER_ID_OUT(+) = TH.TRANSACTION_HEADER_ID
AND
AI_IN.ASSET_ID (+) = TH.ASSET_ID AND
AI_IN.INVOICE_TRANSACTION_ID_IN(+) = TH.INVOICE_TRANSACTION_ID
AND
AI_OUT.ASSET_ID(+) = TH.ASSET_ID AND
AI_OUT.INVOICE_TRANSACTION_ID_OUT(+) = TH.INVOICE_TRANSACTION_ID
AND
IT.BOOK_TYPE_CODE (+) = h_book
AND
AH.ASSET_ID = TH.ASSET_ID AND
TH.DATE_EFFECTIVE BETWEEN AH.DATE_EFFECTIVE AND
NVL(AH.DATE_INEFFECTIVE,
nvl(h_period2_pcd,sysdate))
AND
PO_IN.VENDOR_ID(+) = AI_IN.po_vendor_id AND
PO_OUT.VENDOR_ID(+) = AI_OUT.PO_VENDOR_ID
AND
FALU.LOOKUP_CODE = AH.ASSET_TYPE AND
FALU.LOOKUP_TYPE = 'ASSET TYPE'
GROUP BY
TH.MASS_REFERENCE_ID,
dhcc.code_combination_id,
FALU.MEANING,cat_bk.category_id, dh.location_id,
emp.name, emp.employee_number,
DECODE(AH.ASSET_TYPE, 'CIP',CAT_BK.CIP_COST_ACCT,
CAT_BK.ASSET_COST_ACCT) ,
AD.ASSET_NUMBER,
AD.DESCRIPTION, ad.tag_number, ad.serial_number, ad.inventorial,
bk_out.cost, bk_in.cost,
DECODE(NVL(PO_IN.SEGMENT1,PO_OUT.SEGMENT1),NULL,NULL,
NVL(PO_IN.SEGMENT1,PO_OUT.SEGMENT1)||' - '||
NVL(PO_IN.VENDOR_NAME,PO_OUT.VENDOR_NAME)),
nvl(AI_IN.invoice_number,AI_OUT.invoice_number),
/* Bug#9166346 */
nvl(AI_IN.INVOICE_LINE_NUMBER, AI_OUT.INVOICE_LINE_NUMBER),
NVL(AI_IN.AP_DISTRIBUTION_LINE_NUMBER, AI_OUT.AP_DISTRIBUTION_LINE_NUMBER),
TH.TRANSACTION_HEADER_ID,
NVL(AI_IN.DESCRIPTION,AI_OUT.DESCRIPTION),
IT.TRANSACTION_TYPE,
GAD.ASSET_NUMBER;
select fcr.last_update_login into h_login_id
from fnd_concurrent_requests fcr
where fcr.request_id = h_request_id;
select distribution_source_book into h_distribution_source_book
from fa_book_controls
where book_type_code=h_book;
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 accounting_flex_structure
into h_acct_struct
from fa_book_controls
where book_type_code = h_book;
select location_flex_structure, category_flex_structure
into h_loc_struct, h_cat_struct
from fa_system_controls;
select cur.precision into h_precision
from fa_book_controls bc, gl_sets_of_books sob, fnd_currencies cur
where bc.book_type_code = h_book
and sob.set_of_books_id = bc.set_of_books_id
and sob.currency_code = cur.currency_code;
h_mesg_name := 'FA_SHARED_INSERT_FAILED';
insert into fa_adjust_rep_itf (
request_id, mass_ref_id, company, cost_center,
expense_Acct, cost_acct, employee_name, employee_number,
location, category,
asset_number, description, tag_number, serial_number, inventorial,
before_cost, after_cost, vendor_name, invoice_number,
line_number,distribution_line_number, invoice_description, transaction_header_id,
invoice_adjustment, asset_adjustment, inv_trx_flag,
is_inv_adj_flag, created_by, creation_date,
last_updated_by, last_update_date, last_update_login, group_asset_number)
values (request_id, h_mass_ref_id, h_acct_segs(h_bal_seg),
h_acct_segs(h_cc_seg), h_acct_segs(h_acct_seg),
h_cost_acct, h_emp_name, h_emp_number,
h_concat_loc, h_concat_cat, h_asset_number,
h_description, h_tag_number, h_serial_number, h_inventorial,
h_before_cost, h_after_cost, h_vendor_name,
h_invoice_number, h_line_number,h_distribution_line_number, h_invoice_descr, h_thid,
h_invoice_adjust, h_asset_adjust,
h_inv_flag, h_is_inv_adj,
user_id, sysdate, user_id, sysdate, h_login_id, h_group_asset_number);
if h_mesg_name = 'FA_SHARED_INSERT_FAIL' then
fnd_message.set_token('TABLE','FA_ADJUST_REP_ITF',FALSE);
select code_combination_id ,
code_combination_id,
tr_type_code,
asset_number,
asset_desc,
tag_number,
serial_number,
inventorial,
po_vendor_name,
invoice_number,
invoice_line_number,
line_num,
description,
sum(cleared_cost)
from (
select distinct
adj1.adjustment_line_id,
glcc_ar.code_combination_id code_combination_id,
lu.meaning tr_type_code,
ad.asset_number asset_number,
ad.description asset_desc ,
ad.tag_number tag_number,
ad.serial_number serial_number,
ad.inventorial inventorial,
po_ai_in.vendor_name po_vendor_name,
ai_in.invoice_number invoice_number,
ai_in.invoice_line_number invoice_line_number,
ai_in.ap_distribution_line_number line_num,
ai_in.description description,
decode(adj1.debit_credit_flag
, 'CR', adj1.adjustment_amount
, -adj1.adjustment_amount) cleared_cost
FROM
fa_lookups lu ,
fa_additions ad ,
fa_asset_history ah ,
fa_transaction_headers th ,
fa_adjustments adj1 ,
po_vendors po_ai_in ,
fa_asset_invoices ai_in ,
gl_code_combinations glcc_ar ,
XLA_AE_HEADERS HEADERS ,
XLA_AE_LINES LINES ,
XLA_DISTRIBUTION_LINKS LINKS ,
fa_book_controls BC
WHERE bc.book_type_code = h_book
AND th.book_type_code = bc.book_type_code AND
th.date_effective BETWEEN h_period1_pod AND nvl(h_period1_pcd,sysdate)
AND adj1.book_type_code = th.book_type_code AND
adj1.transaction_header_id = th.transaction_header_id AND
adj1.adjustment_type = 'COST CLEARING'
AND lu.lookup_code = DECODE ( ah.asset_type ,
'CIP' , DECODE ( th.transaction_type_code ,
'CIP ADDITION/VOID' , 'CIP ADDITION' ,
'ADDITION/VOID' , 'CIP ADDITION' ,
th.transaction_type_code )
, DECODE ( th.transaction_type_code ,
'CIP ADDITION/VOID' , 'ADDITION' ,
'ADDITION/VOID' , 'ADDITION' ,
th.transaction_type_code ) )
AND lu.lookup_type = 'FAXOLTRX'
AND ad.asset_id = adj1.asset_id
AND ah.asset_id = th.asset_id
AND th.date_effective between ah.date_effective and nvl(ah.date_ineffective, sysdate)
AND ah.asset_type <> 'EXPENSED'
AND ai_in.asset_id (+) = adj1.asset_id
and ai_in.source_line_id (+) = adj1.source_line_id
AND po_ai_in.vendor_id (+) = ai_in.po_vendor_id
AND LINKS.SOURCE_DISTRIBUTION_ID_NUM_1 = ADJ1.TRANSACTION_HEADER_ID
AND LINKS.SOURCE_DISTRIBUTION_ID_NUM_2 = ADJ1.ADJUSTMENT_LINE_ID
AND LINKS.APPLICATION_ID = 140
AND LINKS.SOURCE_DISTRIBUTION_TYPE = 'TRX'
AND HEADERS.AE_HEADER_ID = LINKS.AE_HEADER_ID
AND HEADERS.LEDGER_ID = BC.SET_OF_BOOKS_ID
AND HEADERS.APPLICATION_ID = 140
AND LINES.AE_HEADER_ID = LINKS.AE_HEADER_ID
AND LINES.AE_LINE_NUM = LINKS.AE_LINE_NUM
AND LINES.APPLICATION_ID = 140
AND glcc_ar.code_combination_id = LINES.CODE_COMBINATION_ID)
group by
code_combination_id,
tr_type_code,
asset_number,
asset_desc,
tag_number,
serial_number,
inventorial,
po_vendor_name,
invoice_number,
invoice_line_number,
line_num,
description;
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, period_close_date
into h_period1_pod, h_period1_pcd
from fa_deprn_periods
where book_type_code = h_book and period_name = period;
select accounting_flex_structure
into h_acct_struct
from fa_book_controls
where book_type_code = h_book;
h_mesg_name := 'FA_SHARED_INSERT_FAILED';
insert into fa_costclear_rep_itf (
request_id, company, cost_Center, account, transaction_type,
asset_number, description, tag_number, serial_number,
vendor_name, invoice_number, line_number,distribution_line_number, inventorial,
inv_description, payables_cost, created_by,
creation_date, last_updated_by, last_update_date,
last_update_login) values (request_id,
h_ar_acct_segs(h_bal_seg), h_ar_acct_segs(h_cc_seg),
h_ar_acct_segs(h_acct_seg), h_thcode, h_asset_number, h_description,
h_tag_number, h_serial_number, h_vendor_name,
h_invoice_number, h_line_number,h_distribution_line_number, h_inventorial, h_inv_description,
h_payables_cost, user_id, sysdate, user_id, sysdate, h_login_id);
if h_mesg_name = 'FA_SHARED_INSERT_FAIL' then
fnd_message.set_token('TABLE','FA_COSTCLEAR_REP_ITF',FALSE);