select fnd_flex_xml_publisher_apis.process_kff_combination_1('ar_flex_bal_seg', 'SQLGL', 'GL#', glcc_ar.CHART_OF_ACCOUNTS_ID, NULL, glcc_ar.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE') comp_code,
fnd_flex_xml_publisher_apis.process_kff_combination_1('ar_flex_acct_seg', 'SQLGL', 'GL#', glcc_ar.CHART_OF_ACCOUNTS_ID, NULL, glcc_ar.CODE_COMBINATION_ID, 'GL_ACCOUNT', 'Y', 'VALUE') clearing_acc,
fnd_flex_xml_publisher_apis.process_kff_combination_1('ar_flex_cost_seg', 'SQLGL', 'GL#', glcc_ar.CHART_OF_ACCOUNTS_ID, NULL, glcc_ar.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE') cost_center,
lu.meaning tr_type_code,
ad.asset_number||' - '||ad.description asset_number,
po_ai_in.vendor_name po_vendor_name,
ai_in.invoice_number invoice_number,
ai_in.invoice_line_number||' - '||ai_in.ap_distribution_line_number line_num,
ai_in.description description,
1,
round(sum(nvl(ai_in.payables_cost,0)) ,2) cleared_cost
from
po_vendors po_ai_in,
fa_lookups lu,
gl_code_combinations glcc_ar,
fa_additions ad,
fa_asset_history ah,
&lp_fa_asset_invoices ai_in,
fa_transaction_headers th
where
lu.lookup_code = decode(ah.asset_type, 'CIP',
'CIP ADDITION', 'ADDITION') and
lu.lookup_type = 'FAXOLTRX'
and th.date_effective between
:PERIOD1_POD and
:PERIOD1_PCD and
th.book_type_code = :P_BOOK and
th.transaction_type_code = 'TRANSFER IN'
and ad.asset_id = th.asset_id
and ah.asset_id = th.asset_id and
ah.asset_type <> 'EXPENSED' and
ah.date_effective <=
:PERIOD1_PCD and
nvl(ah.date_ineffective,sysdate) >
:PERIOD1_PCD
and ai_in.asset_id = th.asset_id and
ai_in.date_effective <=
:PERIOD1_PCD and
nvl(ai_in.date_ineffective,sysdate) >
:PERIOD1_PCD
and glcc_ar.code_combination_id = ai_in.payables_code_combination_id
and nvl(ai_in.payables_cost, 0) != 0
and po_ai_in.vendor_id (+) = ai_in.po_vendor_id
group by
fnd_flex_xml_publisher_apis.process_kff_combination_1('ar_flex_bal_seg', 'SQLGL', 'GL#', glcc_ar.CHART_OF_ACCOUNTS_ID, NULL, glcc_ar.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE'),
fnd_flex_xml_publisher_apis.process_kff_combination_1('ar_flex_acct_seg', 'SQLGL', 'GL#', glcc_ar.CHART_OF_ACCOUNTS_ID, NULL, glcc_ar.CODE_COMBINATION_ID, 'GL_ACCOUNT', 'Y', 'VALUE'),
fnd_flex_xml_publisher_apis.process_kff_combination_1('ar_flex_cost_seg', 'SQLGL', 'GL#', glcc_ar.CHART_OF_ACCOUNTS_ID, NULL, glcc_ar.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE'),
lu.meaning,
ad.asset_number||' - '||ad.description,
po_ai_in.vendor_name,
ai_in.invoice_number,
ai_in.invoice_line_number||' - '||ai_in.ap_distribution_line_number,
ai_in.description
union
select fnd_flex_xml_publisher_apis.process_kff_combination_1('ar_flex_bal_seg', 'SQLGL', 'GL#', glcc_ar.CHART_OF_ACCOUNTS_ID, NULL, glcc_ar.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE') comp_code,
fnd_flex_xml_publisher_apis.process_kff_combination_1('ar_flex_acct_seg', 'SQLGL', 'GL#', glcc_ar.CHART_OF_ACCOUNTS_ID, NULL, glcc_ar.CODE_COMBINATION_ID, 'GL_ACCOUNT', 'Y', 'VALUE') clearing_acc,
fnd_flex_xml_publisher_apis.process_kff_combination_1('ar_flex_cost_seg', 'SQLGL', 'GL#', glcc_ar.CHART_OF_ACCOUNTS_ID, NULL, glcc_ar.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE') cost_center,
lu.meaning tr_type_code,
ad.asset_number||' - '||ad.description asset_number,
po_ai_in.vendor_name po_vendor_name,
ai_in.invoice_number invoice_number,
ai_in.invoice_line_number||' - '||ai_in.ap_distribution_line_number line_num,
ai_in.description description,
2,
round(sum(nvl(ai_in.payables_cost,0)),2) cleared_cost
from po_vendors po_ai_in,
fa_lookups lu,
gl_code_combinations glcc_ar,
fa_additions ad,
fa_invoice_transactions it,
&lp_fa_asset_invoices ai_in,
fa_transaction_headers tht,
fa_transaction_headers th
where it.book_type_code = :P_BOOK and
it.invoice_transaction_id = th.invoice_transaction_id and
it.transaction_type = 'MASS ADDITION'
and lu.lookup_code = decode(th.transaction_type_code,
'ADDITION/VOID','ADDITION',
th.transaction_type_code) and
lu.lookup_type = 'FAXOLTRX'
and th.date_effective between
:PERIOD1_POD and
:PERIOD1_PCD and
th.book_type_code = :P_BOOK and
th.transaction_type_code in
('CIP ADJUSTMENT', 'ADJUSTMENT', 'ADDITION','ADDITION/VOID')
and tht.date_effective <
:PERIOD1_POD and
tht.book_type_code = :P_BOOK and
tht.asset_id = th.asset_id and
tht.transaction_type_code = 'TRANSFER IN'
and ad.asset_id = th.asset_id and
ad.asset_type <> 'EXPENSED'
and ai_in.asset_id = th.asset_id and
ai_in.payables_code_combination_id
= glcc_ar.code_combination_id
and ai_in.invoice_transaction_id_in = it.invoice_transaction_id
and ai_in.date_effective <= th.date_effective and
nvl(ai_in.date_ineffective,sysdate) > th.date_effective
and nvl(ai_in.payables_cost, 0) != 0
and ai_in.po_vendor_id = po_ai_in.vendor_id(+)
group by
fnd_flex_xml_publisher_apis.process_kff_combination_1('ar_flex_bal_seg', 'SQLGL', 'GL#', glcc_ar.CHART_OF_ACCOUNTS_ID, NULL, glcc_ar.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE'),
fnd_flex_xml_publisher_apis.process_kff_combination_1('ar_flex_acct_seg', 'SQLGL', 'GL#', glcc_ar.CHART_OF_ACCOUNTS_ID, NULL, glcc_ar.CODE_COMBINATION_ID, 'GL_ACCOUNT', 'Y', 'VALUE'),
fnd_flex_xml_publisher_apis.process_kff_combination_1('ar_flex_cost_seg', 'SQLGL', 'GL#', glcc_ar.CHART_OF_ACCOUNTS_ID, NULL, glcc_ar.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE'),
lu.meaning,
ad.asset_number||' - '||ad.description,
po_ai_in.vendor_name,
ai_in.invoice_number,
ai_in.invoice_line_number||' - '||ai_in.ap_distribution_line_number,
ai_in.description
union
select fnd_flex_xml_publisher_apis.process_kff_combination_1('fa_flex_bal_seg', 'SQLGL', 'GL#', glcc_fa.CHART_OF_ACCOUNTS_ID, NULL, glcc_fa.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE') comp_code,
fnd_flex_xml_publisher_apis.process_kff_combination_1('ar_flex_acct_seg', 'SQLGL', 'GL#', glcc_ar.CHART_OF_ACCOUNTS_ID, NULL, glcc_ar.CODE_COMBINATION_ID, 'GL_ACCOUNT', 'Y', 'VALUE') clearing_acc,
fnd_flex_xml_publisher_apis.process_kff_combination_1('fa_flex_cost_seg', 'SQLGL', 'GL#', glcc_fa.CHART_OF_ACCOUNTS_ID, NULL, glcc_fa.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE') cost_center,
lu.meaning tr_type_code,
ad.asset_number||' - '||ad.description asset_number_desc,
po_ai_in.vendor_name po_vendor_name,
ai_in.invoice_number invoice_number,
ai_in.invoice_line_number||' - '||ai_in.ap_distribution_line_number line_num,
ai_in.description description,
3,
round(sum((decode(ai_in.deleted_flag, 'YES', 0,
nvl(ai_in.fixed_assets_cost, 0)) -
nvl(ai_in.payables_cost, 0))
* (nvl (dh.units_assigned, ah.units) /
ah.units)) ,2) cleared_cost
from po_vendors po_ai_in,
fa_lookups lu,
fa_distribution_history dh,
gl_code_combinations glcc_fa,
gl_code_combinations glcc_ar,
fa_additions ad,
fa_asset_history ah,
fa_category_books cat_bk,
&lp_fa_asset_invoices ai_in,
fa_transaction_headers th
where
lu.lookup_code = decode(ah.asset_type, 'CIP',
'CIP ADDITION', 'ADDITION') and
lu.lookup_type = 'FAXOLTRX'
and th.date_effective between
:PERIOD1_POD and
:PERIOD1_PCD and
th.book_type_code = :P_BOOK and
th.transaction_type_code = 'TRANSFER IN'
and ad.asset_id = th.asset_id
and ah.asset_id = th.asset_id and
ah.date_effective <=
:PERIOD1_PCD and
nvl(ah.date_ineffective,sysdate) >
:PERIOD1_PCD and
ah.asset_type <> 'EXPENSED'
and cat_bk.book_type_code = :P_BOOK and
cat_bk.category_id = ah.category_id
and dh.book_type_code = :P_BOOK and
dh.asset_id = th.asset_id and
dh.date_effective <=
:PERIOD1_PCD and
nvl(dh.date_ineffective,sysdate) >
:PERIOD1_PCD
and glcc_fa.code_combination_id = dh.code_combination_id
and ai_in.asset_id = th.asset_id and
ai_in.date_effective <=
:PERIOD1_PCD and
nvl(ai_in.date_ineffective,sysdate) >
:PERIOD1_PCD and
nvl(ai_in.fixed_assets_cost,0) !=
nvl(ai_in.payables_cost,0)
and decode(ah.asset_type,'CIP',cat_bk.wip_clearing_account_ccid,
cat_bk.asset_clearing_account_ccid)
= glcc_ar.code_combination_id
and po_ai_in.vendor_id (+) = ai_in.po_vendor_id
group by
fnd_flex_xml_publisher_apis.process_kff_combination_1('fa_flex_bal_seg', 'SQLGL', 'GL#', glcc_fa.CHART_OF_ACCOUNTS_ID, NULL, glcc_fa.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE'),
fnd_flex_xml_publisher_apis.process_kff_combination_1('ar_flex_acct_seg', 'SQLGL', 'GL#', glcc_ar.CHART_OF_ACCOUNTS_ID, NULL, glcc_ar.CODE_COMBINATION_ID, 'GL_ACCOUNT', 'Y', 'VALUE'),
fnd_flex_xml_publisher_apis.process_kff_combination_1('fa_flex_cost_seg', 'SQLGL', 'GL#', glcc_fa.CHART_OF_ACCOUNTS_ID, NULL, glcc_fa.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE'),
lu.meaning,
ad.asset_number||' - '||ad.description,
po_ai_in.vendor_name,
ai_in.invoice_number,
ai_in.invoice_line_number||' - '||ai_in.ap_distribution_line_number,
ai_in.description
union
select fnd_flex_xml_publisher_apis.process_kff_combination_1('fa_flex_bal_seg', 'SQLGL', 'GL#', glcc_fa.CHART_OF_ACCOUNTS_ID, NULL, glcc_fa.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE') comp_code,
fnd_flex_xml_publisher_apis.process_kff_combination_1('ar_flex_acct_seg', 'SQLGL', 'GL#', glcc_ar.CHART_OF_ACCOUNTS_ID, NULL, glcc_ar.CODE_COMBINATION_ID, 'GL_ACCOUNT', 'Y', 'VALUE') clearing_acc,
fnd_flex_xml_publisher_apis.process_kff_combination_1('fa_flex_cost_seg', 'SQLGL', 'GL#', glcc_fa.CHART_OF_ACCOUNTS_ID, NULL, glcc_fa.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE') cost_center,
lu.meaning tr_type_code,
ad.asset_number||' - '||ad.description asset_number_desc,
null po_vendor_id,
null invoice_number,
null line_num,
null description,
4,
round(sum((bk_in.cost - NVL(mc.cost,nvl(bk_out.cost,0))) *
dh.units_assigned / ah.units),2) cleared_cost
from &lp_fa_books bk_in,
fa_mc_books mc,
&lp_fa_books_bas bk_out,
fa_distribution_history dh,
fa_lookups lu,
gl_code_combinations glcc_fa,
gl_code_combinations glcc_ar,
fa_additions ad,
fa_asset_history ah,
fa_category_books cat_bk,
fa_transaction_headers th
where
th.book_type_code = :P_BOOK and
th.invoice_transaction_id is null and
th.transaction_type_code in ('CIP ADDITION', 'CIP ADDITION/VOID',
'ADDITION','ADDITION/VOID', 'ADJUSTMENT', 'CIP ADJUSTMENT') and
th.date_effective between
:PERIOD1_POD and
:PERIOD1_PCD
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 = th.asset_id
and ah.asset_id = th.asset_id and
ah.date_effective <=
decode(th.transaction_type_code,
'CIP ADJUSTMENT', th.date_effective,
'ADJUSTMENT', th.date_effective,
:PERIOD1_PCD) and
nvl(ah.date_ineffective,sysdate+1) >
decode(th.transaction_type_code,
'CIP ADJUSTMENT', th.date_effective,
'ADJUSTMENT', th.date_effective,
:PERIOD1_PCD) and
ah.asset_type <> 'EXPENSED'
and bk_in.transaction_header_id_in = th.transaction_header_id
and bk_out.transaction_header_id_out(+) = th.transaction_header_id
and mc.transaction_header_id_in(+) = DECODE(:mrc_sob_type_code,'R',bk_out.transaction_header_id_in,NULL)
and mc.set_of_books_id(+) = :set_of_books_id
and dh.book_type_code = :P_BOOK
and dh.asset_id = th.asset_id
and dh.date_effective <=
decode(th.transaction_type_code,
'CIP ADJUSTMENT', th.date_effective,
'ADJUSTMENT', th.date_effective,
:PERIOD1_PCD) and
nvl(dh.date_ineffective,sysdate+1) >
decode(th.transaction_type_code,
'CIP ADJUSTMENT', th.date_effective,
'ADJUSTMENT', th.date_effective,
:PERIOD1_PCD)
and glcc_fa.code_combination_id = dh.code_combination_id
and cat_bk.category_id = ah.category_id and
cat_bk.book_type_code = :P_BOOK and
decode(ah.asset_type,'CIP',cat_bk.wip_clearing_account_ccid,
cat_bk.asset_clearing_account_ccid)
= glcc_ar.code_combination_id
and bk_in.cost != nvl(bk_out.cost,0)
group by
fnd_flex_xml_publisher_apis.process_kff_combination_1('fa_flex_bal_seg', 'SQLGL', 'GL#', glcc_fa.CHART_OF_ACCOUNTS_ID, NULL, glcc_fa.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE'),
fnd_flex_xml_publisher_apis.process_kff_combination_1('ar_flex_acct_seg', 'SQLGL', 'GL#', glcc_ar.CHART_OF_ACCOUNTS_ID, NULL, glcc_ar.CODE_COMBINATION_ID, 'GL_ACCOUNT', 'Y', 'VALUE'),
fnd_flex_xml_publisher_apis.process_kff_combination_1('fa_flex_cost_seg', 'SQLGL', 'GL#', glcc_fa.CHART_OF_ACCOUNTS_ID, NULL, glcc_fa.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE'),
lu.meaning,
ad.asset_number||' - '||ad.description
union
select fnd_flex_xml_publisher_apis.process_kff_combination_1('fa_flex_bal_seg', 'SQLGL', 'GL#', glcc_fa.CHART_OF_ACCOUNTS_ID, NULL, glcc_fa.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE') comp_code,
fnd_flex_xml_publisher_apis.process_kff_combination_1('ar_flex_acct_seg', 'SQLGL', 'GL#', glcc_ar.CHART_OF_ACCOUNTS_ID, NULL, glcc_ar.CODE_COMBINATION_ID, 'GL_ACCOUNT', 'Y', 'VALUE') clearing_acc,
fnd_flex_xml_publisher_apis.process_kff_combination_1('fa_flex_cost_seg', 'SQLGL', 'GL#', glcc_fa.CHART_OF_ACCOUNTS_ID, NULL, glcc_fa.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE') cost_center,
lu.meaning tr_type_code,
ad.asset_number||' - '||ad.description asset_number_desc,
po_ai_in.vendor_name po_vendor_name,
ai_in.invoice_number invoice_number,
ai_in.invoice_line_number||' - '||ai_in.ap_distribution_line_number line_num,
ai_in.description description,
5,
round(sum(decode(it.transaction_type,
'INVOICE ADJUSTMENT',
nvl(ai_in.fixed_assets_cost,0) -
NVL(mc.fixed_assets_cost,nvl(ai_out.fixed_assets_cost,0)),
'INVOICE DELETE',
-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_in.payables_cost, 0))
* (dh.units_assigned / ah.units)),2) cleared_cost
from fa_mc_asset_invoices mc,
&lp_fa_asset_invoices_bas ai_out,
po_vendors po_ai_in,
fa_lookups lu,
fa_distribution_history dh,
gl_code_combinations glcc_fa,
gl_code_combinations glcc_ar,
fa_additions ad,
fa_asset_history ah,
fa_category_books cat_bk,
fa_transaction_headers th,
fa_invoice_transactions it,
&lp_fa_asset_invoices ai_in
where it.book_type_code = :P_BOOK and
((it.transaction_type = 'MASS ADDITION' and
nvl(ai_in.fixed_assets_cost,0) !=
nvl(ai_in.payables_cost,0)) or
(it.transaction_type = 'INVOICE ADDITION' and
nvl(ai_in.fixed_assets_cost,0) != 0) or
(it.transaction_type = 'INVOICE ADJUSTMENT' and
nvl(ai_in.fixed_assets_cost,0) !=
nvl(ai_out.fixed_assets_cost,0)) or
(it.transaction_type = 'INVOICE DELETE' and
nvl(ai_in.fixed_assets_cost,0) != 0) or
(it.transaction_type = 'INVOICE REINSTATE' and
nvl(ai_in.fixed_assets_cost,0) != 0))
and lu.lookup_code = th.transaction_type_code and
lu.lookup_type = 'FAXOLTRX'
and th.date_effective between
:PERIOD1_POD and
:PERIOD1_PCD and
th.invoice_transaction_id = it.invoice_transaction_id and
th.transaction_type_code in ('ADJUSTMENT', 'CIP ADJUSTMENT') and
th.book_type_code = :P_BOOK
and ad.asset_id = th.asset_id and
ad.asset_id = ah.asset_id
and ah.date_effective <= th.date_effective and
nvl(ah.date_ineffective,sysdate) > th.date_effective and
ah.category_id = cat_bk.category_id and
ah.asset_type <> 'EXPENSED'
and cat_bk.book_type_code = :P_BOOK and dh.book_type_code = :P_BOOK and
dh.asset_id = th.asset_id and
dh.date_effective <= th.date_effective and
nvl(dh.date_ineffective,sysdate) > th.date_effective
and
dh.code_combination_id = glcc_fa.code_combination_id
and ai_in.invoice_transaction_id_in = th.invoice_transaction_id and
ai_in.asset_id = th.asset_id and
ai_in.date_effective <= th.date_effective and
nvl(ai_in.date_ineffective,sysdate) > th.date_effective
and decode(ah.asset_type,'CIP',cat_bk.wip_clearing_account_ccid,
cat_bk.asset_clearing_account_ccid)
= glcc_ar.code_combination_id
and ai_out.invoice_transaction_id_out (+)
= ai_in.invoice_transaction_id_in and
ai_out.asset_id (+) = ai_in.asset_id and
ai_out.asset_invoice_id (+) = ai_in.asset_invoice_id
and mc.asset_id(+) = DECODE(:mrc_sob_type_code,'R',ai_out.asset_id,NULL)
and mc.asset_invoice_id(+) = DECODE(:mrc_sob_type_code,'R',ai_out.asset_invoice_id,NULL)
and mc.invoice_transaction_id_in(+) = DECODE(:mrc_sob_type_code,'R',ai_out.invoice_transaction_id_in,NULL)
and mc.set_of_books_id(+) = :set_of_books_id
and po_ai_in.vendor_id (+) = ai_in.po_vendor_id
group by
fnd_flex_xml_publisher_apis.process_kff_combination_1('fa_flex_bal_seg', 'SQLGL', 'GL#', glcc_fa.CHART_OF_ACCOUNTS_ID, NULL, glcc_fa.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE'),
fnd_flex_xml_publisher_apis.process_kff_combination_1('ar_flex_acct_seg', 'SQLGL', 'GL#', glcc_ar.CHART_OF_ACCOUNTS_ID, NULL, glcc_ar.CODE_COMBINATION_ID, 'GL_ACCOUNT', 'Y', 'VALUE'),
fnd_flex_xml_publisher_apis.process_kff_combination_1('fa_flex_cost_seg', 'SQLGL', 'GL#', glcc_fa.CHART_OF_ACCOUNTS_ID, NULL, glcc_fa.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE'),
lu.meaning,
ad.asset_number||' - '||ad.description,
po_ai_in.vendor_name,
ai_in.invoice_number,
ai_in.invoice_line_number||' - '||ai_in.ap_distribution_line_number,
ai_in.description
union
select fnd_flex_xml_publisher_apis.process_kff_combination_1('fa_flex_bal_seg', 'SQLGL', 'GL#', glcc_fa.CHART_OF_ACCOUNTS_ID, NULL, glcc_fa.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE') comp_code,
fnd_flex_xml_publisher_apis.process_kff_combination_1('ar_flex_acct_seg', 'SQLGL', 'GL#', glcc_ar.CHART_OF_ACCOUNTS_ID, NULL, glcc_ar.CODE_COMBINATION_ID, 'GL_ACCOUNT', 'Y', 'VALUE') clearing_acc,
fnd_flex_xml_publisher_apis.process_kff_combination_1('fa_flex_cost_seg', 'SQLGL', 'GL#', glcc_fa.CHART_OF_ACCOUNTS_ID, NULL, glcc_fa.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE') cost_center,
lu.meaning tr_type_code,
ad.asset_number||' - '||ad.description asset_number_desc,
po_ai_in.vendor_name po_vendor_name,
ai_in.invoice_number invoice_number,
ai_in.invoice_line_number||' - '||ai_in.ap_distribution_line_number line_num,
ai_in.description description,
6,
round(sum(decode(th.asset_id,
ai_in.asset_id,
nvl(ai_in.fixed_assets_cost,0),
ai_out.asset_id,
-nvl(ai_out.fixed_assets_cost,0),0)
* (dh.units_assigned / ah.units)),2) cleared_cost
from &lp_fa_asset_invoices ai_out,
po_vendors po_ai_in,
fa_lookups lu,
fa_distribution_history dh,
gl_code_combinations glcc_fa,
gl_code_combinations glcc_ar,
fa_additions ad,
fa_asset_history ah,
fa_category_books cat_bk,
fa_transaction_headers th,
fa_invoice_transactions it,
&lp_fa_asset_invoices ai_in
where it.book_type_code = :P_BOOK and
it.transaction_type = 'INVOICE TRANSFER' and
nvl(ai_in.fixed_assets_cost,0) != 0
and lu.lookup_code = th.transaction_type_code and
lu.lookup_type = 'FAXOLTRX'
and th.date_effective between
:PERIOD1_POD and
:PERIOD1_PCD and
th.invoice_transaction_id = it.invoice_transaction_id and
th.transaction_type_code in ('ADJUSTMENT', 'CIP ADJUSTMENT') and
th.book_type_code = :P_BOOK
and ad.asset_id = th.asset_id and
ad.asset_id = ah.asset_id
and ah.date_effective <= th.date_effective and
nvl(ah.date_ineffective,sysdate) > th.date_effective and
ah.category_id = cat_bk.category_id and
ah.asset_type <> 'EXPENSED'
and cat_bk.book_type_code = :P_BOOK
and dh.book_type_code = :P_BOOK and
dh.asset_id = th.asset_id and
dh.date_effective <= th.date_effective and
nvl(dh.date_ineffective,sysdate) > th.date_effective
and
dh.code_combination_id = glcc_fa.code_combination_id
and ai_in.invoice_transaction_id_in = th.invoice_transaction_id and
ai_in.date_effective <= th.date_effective and
nvl(ai_in.date_ineffective,sysdate) > th.date_effective
and decode(ah.asset_type,'CIP',cat_bk.wip_clearing_account_ccid,
cat_bk.asset_clearing_account_ccid)
= glcc_ar.code_combination_id
and ai_out.invoice_transaction_id_out
= ai_in.invoice_transaction_id_in and
ai_out.asset_invoice_id = ai_in.asset_invoice_id
and ai_in.po_vendor_id = po_ai_in.vendor_id(+)
group by
fnd_flex_xml_publisher_apis.process_kff_combination_1('fa_flex_bal_seg', 'SQLGL', 'GL#', glcc_fa.CHART_OF_ACCOUNTS_ID, NULL, glcc_fa.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE'),
fnd_flex_xml_publisher_apis.process_kff_combination_1('ar_flex_acct_seg', 'SQLGL', 'GL#', glcc_ar.CHART_OF_ACCOUNTS_ID, NULL, glcc_ar.CODE_COMBINATION_ID, 'GL_ACCOUNT', 'Y', 'VALUE'),
fnd_flex_xml_publisher_apis.process_kff_combination_1('fa_flex_cost_seg', 'SQLGL', 'GL#', glcc_fa.CHART_OF_ACCOUNTS_ID, NULL, glcc_fa.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE'),
lu.meaning,
ad.asset_number||' - '||ad.description,
po_ai_in.vendor_name,
ai_in.invoice_number,
ai_in.invoice_line_number||' - '||ai_in.ap_distribution_line_number,
ai_in.description
order by
4,1,2,3,5,6,7,8,9 |
