FA Cost Clearing Reconciliation - draft
Description
Categories: BI Publisher
Application: Assets
Source: Cost Clearing Reconciliation Report (XML) - Not Supported: Reserved For Future Use
Short Name: FASCC_XML
DB package: FA_FASCC_XMLP_PKG
Source: Cost Clearing Reconciliation Report (XML) - Not Supported: Reserved For Future Use
Short Name: FASCC_XML
DB package: FA_FASCC_XMLP_PKG
Run
FA Cost Clearing Reconciliation - draft and other Oracle EBS reports with Blitz Reportâ„¢ on our demo environment
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 |