SELECT DECODE(FTH.mass_reference_id,NULL,'Manual Transactions','Mass Additions') source
,fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_bal_seg', 'SQLGL', 'GL#', GLCC.CHART_OF_ACCOUNTS_ID, NULL, GLCC.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE') comp_code
,FALU.meaning asset_type_desc
,FAH.asset_type asset_type
,DECODE(FAH.asset_type, 'CIP', FCB.cip_cost_acct,FCB.asset_cost_acct) account
,fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_cost_seg', 'SQLGL', 'GL#', GLCC.CHART_OF_ACCOUNTS_ID, NULL, GLCC.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE') cost_center
,FAD.asset_number||' - '||FAD.description asset
,FAD.asset_number asset_ord
,1 ord_by
,NULL vend_num
,NULL vend_name
,NULL inv_num
,TO_NUMBER(NULL) line_num
,NULL description
,TO_NUMBER(NULL) orig_cost
,TO_NUMBER(NULL) inv_cost
,SUM(NVL(FDD.addition_cost_to_clear, 0)) cost
,NULL flag
,NULL transaction_date
,fnd_flex_xml_publisher_apis.process_kff_combination_1('addition_type', 'OFA', 'KEY#', 101, NULL, fak.CODE_COMBINATION_ID, 'ADDITION_TYPE', 'N', 'VALUE') addition_type
,fnd_flex_xml_publisher_apis.process_kff_combination_1('industry_code', 'OFA', 'KEY#', 101, NULL, fak.CODE_COMBINATION_ID, 'INDUSTRY_CODE', 'N', 'VALUE') industry_code
,fnd_flex_xml_publisher_apis.process_kff_combination_1('statistical_code', 'OFA', 'KEY#', 101, NULL, fak.CODE_COMBINATION_ID, 'STATISTICAL_CODE', 'N', 'VALUE') statistical_code
,fnd_flex_xml_publisher_apis.process_kff_combination_1('tariff_num', 'OFA', 'KEY#', 101, NULL, fak.CODE_COMBINATION_ID, 'TARIFF_NUM', 'N', 'VALUE') tariff_num
,fnd_flex_xml_publisher_apis.process_kff_combination_1('item_number', 'OFA', 'KEY#', 101, NULL, fak.CODE_COMBINATION_ID, 'ITEM_NUMBER', 'N', 'VALUE') item_number
,fnd_flex_xml_publisher_apis.process_kff_combination_1('reserve', 'OFA', 'KEY#', 101, NULL, fak.CODE_COMBINATION_ID, 'RESERVE', 'N', 'VALUE') reserve
FROM
fa_distribution_history FDH
,fa_asset_history FAH
,fa_category_books FCB
,fa_lookups FALU
,fa_additions FAD
,gl_code_combinations GLCC
,fa_transaction_headers FTH
,fa_deprn_detail FDD
,fa_deprn_periods FDP
,fa_asset_keywords FAK
WHERE FDP.book_type_code = :P_BOOK
AND FDP.period_counter >= &gn_period1_pc
AND FDP.period_counter <= NVL(&gn_period2_pc,FDP.period_counter)
AND FTH.asset_id = FDD.asset_id
AND FTH.date_effective >= FDP.period_open_date
AND FTH.date_effective < NVL(FDP.period_close_date,sysdate)
AND FTH.book_type_code = :P_BOOK
AND FTH.transaction_type_code in ('TRANSFER IN','TRANSFER IN/VOID')
AND FTH.transaction_header_id = (SELECT MIN(THVOID.transaction_header_id)
FROM fa_transaction_headers THVOID
WHERE THVOID.book_type_code = FTH.book_type_code
AND THVOID.transaction_type_code IN ('TRANSFER IN/VOID'
, 'TRANSFER IN')
AND THVOID.asset_id = FTH.asset_id)
AND FDH.book_type_code = :gc_distribution_source_book
AND FDH.asset_id = FDD.asset_id
AND GLCC.code_combination_id = FDH.code_combination_id
AND FDD.book_type_code = :P_BOOK
AND FDD.deprn_source_code = 'B'
AND FDD.distribution_id = FDH.distribution_id
AND FDD.period_counter = FDP.period_counter - 1
AND FCB.category_id = FAH.category_id
AND FCB.book_type_code = :P_BOOK
AND FAD.asset_id = FDD.asset_id
AND FAH.asset_id = FAD.asset_id
AND FAH.date_effective <= NVL(FDP.period_close_date, SYSDATE)
AND NVL(FAH.date_ineffective,SYSDATE+1) > NVL(FDP.period_close_date, SYSDATE)
AND FAH.asset_type = FALU.lookup_code
AND FALU.lookup_type = 'ASSET TYPE'
AND FAK.code_combination_id = FAD.asset_key_ccid
GROUP BY
DECODE(FTH.mass_reference_id,NULL,'Manual Transactions','Mass Additions')
,fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_bal_seg', 'SQLGL', 'GL#', GLCC.CHART_OF_ACCOUNTS_ID, NULL, GLCC.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE')
,FALU.meaning
,FAH.asset_type
,DECODE(FAH.asset_type, 'CIP', FCB.cip_cost_acct,FCB.asset_cost_acct)
,fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_cost_seg', 'SQLGL', 'GL#', GLCC.CHART_OF_ACCOUNTS_ID, NULL, GLCC.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE')
,FAD.asset_number||' - '||FAD.description
,FAD.asset_number
,fnd_flex_xml_publisher_apis.process_kff_combination_1('addition_type', 'OFA', 'KEY#', 101, NULL, fak.CODE_COMBINATION_ID, 'ADDITION_TYPE', 'N', 'VALUE')
,fnd_flex_xml_publisher_apis.process_kff_combination_1('industry_code', 'OFA', 'KEY#', 101, NULL, fak.CODE_COMBINATION_ID, 'INDUSTRY_CODE', 'N', 'VALUE')
,fnd_flex_xml_publisher_apis.process_kff_combination_1('statistical_code', 'OFA', 'KEY#', 101, NULL, fak.CODE_COMBINATION_ID, 'STATISTICAL_CODE', 'N', 'VALUE')
,fnd_flex_xml_publisher_apis.process_kff_combination_1('tariff_num', 'OFA', 'KEY#', 101, NULL, fak.CODE_COMBINATION_ID, 'TARIFF_NUM', 'N', 'VALUE')
,fnd_flex_xml_publisher_apis.process_kff_combination_1('item_number', 'OFA', 'KEY#', 101, NULL, fak.CODE_COMBINATION_ID, 'ITEM_NUMBER', 'N', 'VALUE')
,fnd_flex_xml_publisher_apis.process_kff_combination_1('reserve', 'OFA', 'KEY#', 101, NULL, fak.CODE_COMBINATION_ID, 'RESERVE', 'N', 'VALUE')
UNION ALL
SELECT DECODE(FTH.mass_reference_id,NULL,'Manual Transactions','Mass Additions') source
,fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_bal_seg', 'SQLGL', 'GL#', GLCC.CHART_OF_ACCOUNTS_ID, NULL, GLCC.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE') comp_code
,FALU.MEANING asset_type_desc
,FAH.ASSET_TYPE asset_type
,DECODE(FAH.ASSET_TYPE, 'CIP', FCB.CIP_COST_ACCT,FCB.ASSET_COST_ACCT) account
,fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_cost_seg', 'SQLGL', 'GL#', GLCC.CHART_OF_ACCOUNTS_ID, NULL, GLCC.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE') cost_center
,FAD.ASSET_NUMBER||' - '||FAD.DESCRIPTION asset
,FAD.ASSET_NUMBER asset_ord
,1 ord_by
,NULL vend_num
,NULL vend_name
,NULL inv_num
,TO_NUMBER(NULL) line_num
,NULL description
,TO_NUMBER(NULL) orig_cost
,TO_NUMBER(NULL) inv_cost
,SUM(DECODE(ADJ.debit_credit_flag, 'DR',1,-1) * ADJ.adjustment_amount) cost
,NULL flag
,NULL transaction_date
,fnd_flex_xml_publisher_apis.process_kff_combination_1('addition_type', 'OFA', 'KEY#', 101, NULL, fak.CODE_COMBINATION_ID, 'ADDITION_TYPE', 'N', 'VALUE') addition_type
,fnd_flex_xml_publisher_apis.process_kff_combination_1('industry_code', 'OFA', 'KEY#', 101, NULL, fak.CODE_COMBINATION_ID, 'INDUSTRY_CODE', 'N', 'VALUE') industry_code
,fnd_flex_xml_publisher_apis.process_kff_combination_1('statistical_code', 'OFA', 'KEY#', 101, NULL, fak.CODE_COMBINATION_ID, 'STATISTICAL_CODE', 'N', 'VALUE') statistical_code
,fnd_flex_xml_publisher_apis.process_kff_combination_1('tariff_num', 'OFA', 'KEY#', 101, NULL, fak.CODE_COMBINATION_ID, 'TARIFF_NUM', 'N', 'VALUE') tariff_num
,fnd_flex_xml_publisher_apis.process_kff_combination_1('item_number', 'OFA', 'KEY#', 101, NULL, fak.CODE_COMBINATION_ID, 'ITEM_NUMBER', 'N', 'VALUE') item_number
,fnd_flex_xml_publisher_apis.process_kff_combination_1('reserve', 'OFA', 'KEY#', 101, NULL, fak.CODE_COMBINATION_ID, 'RESERVE', 'N', 'VALUE') reserve
FROM
fa_transaction_headers FTH
,fa_transaction_headers FTH1
,fa_additions FAD
,fa_asset_history FAH
,fa_category_books FCB
,fa_distribution_history FDH
,gl_code_combinations GLCC
,gl_code_combinations AJCC
,fa_lookups FALU
,fa_adjustments ADJ
,fa_deprn_periods FDP
,fa_asset_keywords FAK
WHERE FDP.book_type_code = :P_BOOK
AND FDP.period_counter >= &gn_period1_pc
AND FDP.period_counter <= NVL(&gn_period2_pc,FDP.period_counter)
AND FTH.date_effective >= FDP.PERIOD_OPEN_DATE
AND FTH.date_effective < NVL(FDP.period_close_date,sysdate)
AND FTH.book_type_code = :P_BOOK
AND FTH.transaction_type_code = 'ADDITION'
AND FTH1.transaction_type_code = 'TRANSFER IN'
AND FTH1.book_type_code = :P_BOOK
AND FTH1.asset_id = FTH.asset_id
AND FTH1.date_effective < FDP.period_open_date
AND ADJ.book_type_code = :P_BOOK
AND ADJ.asset_id = FTH.asset_id
AND ADJ.source_type_code = 'ADDITION'
AND ADJ.adjustment_type = 'COST'
AND ADJ.period_counter_created = FDP.period_counter
AND ADJ.code_combination_id = AJCC.code_combination_id
AND FDH.book_type_code = :P_BOOK
AND FDH.asset_id = FTH.asset_id
AND FDH.distribution_id = ADJ.distribution_id
AND FDH.code_combination_id = GLCC.code_combination_id
AND FCB.category_id = FAH.category_id
AND FCB.book_type_code = :P_BOOK
AND FAD.asset_id = FTH.asset_id
AND FAH.asset_id = FTH.asset_id
AND FAH.date_effective <= FTH.date_effective
AND NVL(FAH.date_ineffective,SYSDATE+1) > FTH.date_effective
AND FAH.asset_type = FALU.lookup_code
AND FALU.lookup_type = 'ASSET TYPE'
AND FAK.code_combination_id = FAD.asset_key_ccid
GROUP BY
DECODE(FTH.mass_reference_id,NULL,'Manual Transactions','Mass Additions')
,fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_bal_seg', 'SQLGL', 'GL#', GLCC.CHART_OF_ACCOUNTS_ID, NULL, GLCC.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE')
,FALU.meaning
,FAH.asset_type
,DECODE(FAH.asset_type, 'CIP', FCB.cip_cost_acct,FCB.asset_cost_acct)
,fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_cost_seg', 'SQLGL', 'GL#', GLCC.CHART_OF_ACCOUNTS_ID, NULL, GLCC.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE')
,FAD.asset_number||' - '||FAD.description
,FAD.asset_number
,fnd_flex_xml_publisher_apis.process_kff_combination_1('addition_type', 'OFA', 'KEY#', 101, NULL, fak.CODE_COMBINATION_ID, 'ADDITION_TYPE', 'N', 'VALUE')
,fnd_flex_xml_publisher_apis.process_kff_combination_1('industry_code', 'OFA', 'KEY#', 101, NULL, fak.CODE_COMBINATION_ID, 'INDUSTRY_CODE', 'N', 'VALUE')
,fnd_flex_xml_publisher_apis.process_kff_combination_1('statistical_code', 'OFA', 'KEY#', 101, NULL, fak.CODE_COMBINATION_ID, 'STATISTICAL_CODE', 'N', 'VALUE')
,fnd_flex_xml_publisher_apis.process_kff_combination_1('tariff_num', 'OFA', 'KEY#', 101, NULL, fak.CODE_COMBINATION_ID, 'TARIFF_NUM', 'N', 'VALUE')
,fnd_flex_xml_publisher_apis.process_kff_combination_1('item_number', 'OFA', 'KEY#', 101, NULL, fak.CODE_COMBINATION_ID, 'ITEM_NUMBER', 'N', 'VALUE')
,fnd_flex_xml_publisher_apis.process_kff_combination_1('reserve', 'OFA', 'KEY#', 101, NULL, fak.CODE_COMBINATION_ID, 'RESERVE', 'N', 'VALUE')
UNION ALL
SELECT DECODE(FTH.mass_reference_id,NULL,'Manual Transactions','Mass Additions') source
,fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_bal_seg', 'SQLGL', 'GL#', GLCC.CHART_OF_ACCOUNTS_ID, NULL, GLCC.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE') comp_code
,FALU.meaning asset_type_desc
,FAH.asset_type asset_type
,DECODE(FAH.asset_type, 'CIP', FCB.CIP_COST_ACCT,FCB.asset_cost_acct) account
,fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_cost_seg', 'SQLGL', 'GL#', GLCC.CHART_OF_ACCOUNTS_ID, NULL, GLCC.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE') cost_center
,FAD.asset_number||' - '||FAD.description asset
,FAD.asset_number asset_ord
,2 ord_by
,PV.segment1 vend_num
,PV.vendor_name vend_name
,FAI.invoice_number inv_num
,FAI.ap_distribution_line_number line_num
,FAI.description description
,FAI.payables_cost orig_cost
,round(SUM(FDH.units_assigned/FAH.UNITS * FAI.fixed_assets_cost), &gn_precision) inv_cost
,TO_NUMBER(NULL) cost
,DECODE(FIT.transaction_type,'INVOICE ADDITION','M',
'INVOICE ADJUSTMENT','A',
'INVOICE TRANSFER','T',
'INVOICE REINSTATE','R',NULL) flag
,FTH.transaction_date_entered transaction_date
,fnd_flex_xml_publisher_apis.process_kff_combination_1('addition_type', 'OFA', 'KEY#', 101, NULL, fak.CODE_COMBINATION_ID, 'ADDITION_TYPE', 'N', 'VALUE') addition_type
,fnd_flex_xml_publisher_apis.process_kff_combination_1('industry_code', 'OFA', 'KEY#', 101, NULL, fak.CODE_COMBINATION_ID, 'INDUSTRY_CODE', 'N', 'VALUE') industry_code
,fnd_flex_xml_publisher_apis.process_kff_combination_1('statistical_code', 'OFA', 'KEY#', 101, NULL, fak.CODE_COMBINATION_ID, 'STATISTICAL_CODE', 'N', 'VALUE') statistical_code
,fnd_flex_xml_publisher_apis.process_kff_combination_1('tariff_num', 'OFA', 'KEY#', 101, NULL, fak.CODE_COMBINATION_ID, 'TARIFF_NUM', 'N', 'VALUE') tariff_num
,fnd_flex_xml_publisher_apis.process_kff_combination_1('item_number', 'OFA', 'KEY#', 101, NULL, fak.CODE_COMBINATION_ID, 'ITEM_NUMBER', 'N', 'VALUE') item_number
,fnd_flex_xml_publisher_apis.process_kff_combination_1('reserve', 'OFA', 'KEY#', 101, NULL, fak.CODE_COMBINATION_ID, 'RESERVE', 'N', 'VALUE') reserve
FROM
fa_asset_invoices FAI
,fa_invoice_transactions FIT
,fa_transaction_headers FTH
,fa_distribution_history FDH
,fa_asset_history FAH
,fa_category_books FCB
,fa_lookups FALU
,po_vendors PV
,fa_additions FAD
,gl_code_combinations GLCC
,fa_deprn_detail FDD
,fa_deprn_periods FDP
,fa_asset_keywords FAK
WHERE FDP.book_type_code = :P_BOOK
AND FDP.period_counter >= &gn_period1_pc
AND FDP.period_counter <= NVL(&gn_period2_pc,FDP.period_counter)
AND FTH.asset_id = FDD.asset_id
AND FTH.date_effective >= FDP.period_open_date
AND FTH.date_effective < NVL(FDP.period_close_date,sysdate)
AND FTH.book_type_code = :P_BOOK
AND FTH.transaction_type_code in ('TRANSFER IN','TRANSFER IN/VOID')
AND FTH.transaction_header_id = (SELECT MIN(THVOID.transaction_header_id)
FROM fa_transaction_headers THVOID
WHERE THVOID.book_type_code = FTH.book_type_code
AND THVOID.transaction_type_code IN ('TRANSFER IN/VOID'
, 'TRANSFER IN')
AND THVOID.asset_id = FTH.asset_id)
AND FDH.book_type_code = :gc_distribution_source_book
AND FDH.asset_id = FDD.asset_id
AND GLCC.code_combination_id = FDH.code_combination_id
AND FDD.book_type_code = :P_BOOK
AND FDD.deprn_source_code = 'B'
AND FDD.distribution_id = FDH.distribution_id
AND FDD.period_counter = FDP.period_counter - 1
AND FCB.category_id = FAH.category_id
AND FCB.book_type_code = :P_BOOK
AND FAD.asset_id = FDD.asset_id
AND FAH.asset_id = FAD.asset_id
AND FAH.date_effective <= NVL(FDP.period_close_date, SYSDATE)
AND NVL(FAH.date_ineffective,SYSDATE+1) > NVL(FDP.period_close_date, SYSDATE)
AND FAH.asset_type = FALU.lookup_code
AND FALU.lookup_type = 'ASSET TYPE'
AND FIT.invoice_transaction_id = FAI.invoice_transaction_id_in
AND FAI.asset_id = FTH.asset_id
AND FAI.date_effective <= NVL(FDP.period_close_date, SYSDATE)
AND NVL(FAI.date_ineffective, SYSDATE+1) > NVL(FDP.period_close_date, SYSDATE)
AND FAI.deleted_flag = 'NO'
AND PV.vendor_id(+) = FAI.po_vendor_id
AND FAK.code_combination_id = FAD.asset_key_ccid
GROUP BY
DECODE(FTH.mass_reference_id,NULL,'Manual Transactions','Mass Additions')
,fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_bal_seg', 'SQLGL', 'GL#', GLCC.CHART_OF_ACCOUNTS_ID, NULL, GLCC.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE')
,FALU.meaning
,FAH.asset_type
,DECODE(FAH.asset_type, 'CIP', FCB.cip_cost_acct,FCB.asset_cost_acct)
,fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_cost_seg', 'SQLGL', 'GL#', GLCC.CHART_OF_ACCOUNTS_ID, NULL, GLCC.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE')
,FAD.asset_number||' - '||FAD.description
,FAD.asset_number
,PV.segment1
,PV.vendor_name
,FAI.invoice_number
,FAI.ap_distribution_line_number
,FAI.description
,FAI.payables_cost
,FTH.transaction_date_entered
,DECODE(FIT.transaction_type,'INVOICE ADDITION','M',
'INVOICE ADJUSTMENT','A',
'INVOICE TRANSFER','T',
'INVOICE REINSTATE','R',NULL)
,fnd_flex_xml_publisher_apis.process_kff_combination_1('addition_type', 'OFA', 'KEY#', 101, NULL, fak.CODE_COMBINATION_ID, 'ADDITION_TYPE', 'N', 'VALUE')
,fnd_flex_xml_publisher_apis.process_kff_combination_1('industry_code', 'OFA', 'KEY#', 101, NULL, fak.CODE_COMBINATION_ID, 'INDUSTRY_CODE', 'N', 'VALUE')
,fnd_flex_xml_publisher_apis.process_kff_combination_1('statistical_code', 'OFA', 'KEY#', 101, NULL, fak.CODE_COMBINATION_ID, 'STATISTICAL_CODE', 'N', 'VALUE')
,fnd_flex_xml_publisher_apis.process_kff_combination_1('tariff_num', 'OFA', 'KEY#', 101, NULL, fak.CODE_COMBINATION_ID, 'TARIFF_NUM', 'N', 'VALUE')
,fnd_flex_xml_publisher_apis.process_kff_combination_1('item_number', 'OFA', 'KEY#', 101, NULL, fak.CODE_COMBINATION_ID, 'ITEM_NUMBER', 'N', 'VALUE')
,fnd_flex_xml_publisher_apis.process_kff_combination_1('reserve', 'OFA', 'KEY#', 101, NULL, fak.CODE_COMBINATION_ID, 'RESERVE', 'N', 'VALUE')
UNION ALL
SELECT DECODE(FTH.mass_reference_id,NULL,'Manual Transactions','Mass Additions') source
,fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_bal_seg', 'SQLGL', 'GL#', GLCC.CHART_OF_ACCOUNTS_ID, NULL, GLCC.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE') comp_code
,FALU.meaning asset_type_desc
,FAH.asset_type asset_type
,DECODE(FAH.asset_type, 'CIP', FCB.cip_cost_acct,FCB.asset_cost_acct) account
,fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_cost_seg', 'SQLGL', 'GL#', GLCC.CHART_OF_ACCOUNTS_ID, NULL, GLCC.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE') cost_center
,FAD.ASSET_NUMBER||' - '||FAD.DESCRIPTION asset
,FAD.ASSET_NUMBER asset_ord
,2 ord_by
,PV.segment1 vend_num
,PV.vendor_name vend_name
,FAI.invoice_number inv_num
,FAI.ap_distribution_line_number line_num
,FAI.description description
,FAI.payables_cost orig_cost
,round(SUM(FDH.units_assigned/FAH.units * FAI.fixed_assets_cost),&gn_precision) inv_cost
,TO_NUMBER(NULL) cost
,DECODE(FIT.TRANSACTION_TYPE,'INVOICE ADDITION','M',
'INVOICE ADJUSTMENT','A',
'INVOICE TRANSFER','T',
'INVOICE REINSTATE','R',NULL) flag
,FTH.transaction_date_entered transaction_date
,fnd_flex_xml_publisher_apis.process_kff_combination_1('addition_type', 'OFA', 'KEY#', 101, NULL, fak.CODE_COMBINATION_ID, 'ADDITION_TYPE', 'N', 'VALUE') addition_type
,fnd_flex_xml_publisher_apis.process_kff_combination_1('industry_code', 'OFA', 'KEY#', 101, NULL, fak.CODE_COMBINATION_ID, 'INDUSTRY_CODE', 'N', 'VALUE') industry_code
,fnd_flex_xml_publisher_apis.process_kff_combination_1('statistical_code', 'OFA', 'KEY#', 101, NULL, fak.CODE_COMBINATION_ID, 'STATISTICAL_CODE', 'N', 'VALUE') statistical_code
,fnd_flex_xml_publisher_apis.process_kff_combination_1('tariff_num', 'OFA', 'KEY#', 101, NULL, fak.CODE_COMBINATION_ID, 'TARIFF_NUM', 'N', 'VALUE') tariff_num
,fnd_flex_xml_publisher_apis.process_kff_combination_1('item_number', 'OFA', 'KEY#', 101, NULL, fak.CODE_COMBINATION_ID, 'ITEM_NUMBER', 'N', 'VALUE') item_number
,fnd_flex_xml_publisher_apis.process_kff_combination_1('reserve', 'OFA', 'KEY#', 101, NULL, fak.CODE_COMBINATION_ID, 'RESERVE', 'N', 'VALUE') reserve
FROM
fa_asset_invoices FAI
,fa_invoice_transactions FIT
,fa_transaction_headers FTH1
,fa_distribution_history FDH
,fa_asset_history FAH
,fa_category_books FCB
,fa_lookups FALU
,po_vendors PV
,fa_additions FAD
,gl_code_combinations GLCC
,fa_transaction_headers FTH
,fa_deprn_periods FDP
,fa_asset_keywords FAK
WHERE FDP.book_type_code = :P_BOOK
AND FDP.period_counter >= &gn_period1_pc
AND FDP.period_counter <= NVL(&gn_period2_pc,FDP.period_counter)
AND FTH.date_effective >= FDP.period_open_date
AND FTH.date_effective < NVL(FDP.period_close_date,SYSDATE)
AND FTH.book_type_code = :P_BOOK
AND FTH.transaction_type_code = 'ADDITION'
AND FTH1.transaction_type_code = 'TRANSFER IN'
AND FTH1.book_type_code = :P_BOOK
AND FTH1.asset_id = FTH.asset_id
AND FTH1.date_effective < FDP.period_open_date
AND FDH.book_type_code = :P_BOOK
AND FDH.asset_id = FTH.asset_id
AND FDH.code_combination_id = GLCC.code_combination_id
AND FDH.date_effective <= FTH.date_effective
AND NVL(FDH.date_ineffective, SYSDATE) > FTH.date_effective
AND FCB.category_id = FAH.category_id
AND FCB.book_type_code = :P_BOOK
AND FAD.asset_id = FTH.asset_id
AND FAH.asset_id = FTH.asset_id
AND FAH.date_effective <= FTH.date_effective
AND NVL(FAH.date_ineffective,SYSDATE+1) > FTH.date_effective
AND FAH.asset_type = FALU.lookup_code
AND FALU.lookup_type = 'ASSET TYPE'
AND FIT.invoice_transaction_id = FAI.invoice_transaction_id_in
AND FAI.asset_id = FTH.asset_id
AND FAI.date_effective <= NVL(FDP.period_close_date, SYSDATE)
AND NVL(FAI.date_ineffective, SYSDATE+1) > NVL(FDP.period_close_date, SYSDATE)
AND FAI.deleted_flag = 'NO'
AND PV.vendor_id(+) = FAI.po_vendor_id
AND FAK.code_combination_id = FAD.asset_key_ccid
GROUP BY
DECODE(FTH.mass_reference_id,NULL,'Manual Transactions','Mass Additions')
,fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_bal_seg', 'SQLGL', 'GL#', GLCC.CHART_OF_ACCOUNTS_ID, NULL, GLCC.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE')
,FALU.meaning
,FAH.asset_type
,DECODE(FAH.asset_type, 'CIP', FCB.cip_cost_acct,FCB.asset_cost_acct)
,fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_cost_seg', 'SQLGL', 'GL#', GLCC.CHART_OF_ACCOUNTS_ID, NULL, GLCC.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE')
,FAD.asset_number||' - '||FAD.description
,FAD.asset_number
,PV.segment1
,PV.vendor_name
,FAI.invoice_number
,FAI.ap_distribution_line_number
,FAI.description
,FAI.payables_cost
,FTH.transaction_date_entered
,DECODE(FIT.transaction_type,'INVOICE ADDITION','M',
'INVOICE ADJUSTMENT','A',
'INVOICE TRANSFER','T',
'INVOICE REINSTATE','R',NULL)
,fnd_flex_xml_publisher_apis.process_kff_combination_1('addition_type', 'OFA', 'KEY#', 101, NULL, fak.CODE_COMBINATION_ID, 'ADDITION_TYPE', 'N', 'VALUE')
,fnd_flex_xml_publisher_apis.process_kff_combination_1('industry_code', 'OFA', 'KEY#', 101, NULL, fak.CODE_COMBINATION_ID, 'INDUSTRY_CODE', 'N', 'VALUE')
,fnd_flex_xml_publisher_apis.process_kff_combination_1('statistical_code', 'OFA', 'KEY#', 101, NULL, fak.CODE_COMBINATION_ID, 'STATISTICAL_CODE', 'N', 'VALUE')
,fnd_flex_xml_publisher_apis.process_kff_combination_1('tariff_num', 'OFA', 'KEY#', 101, NULL, fak.CODE_COMBINATION_ID, 'TARIFF_NUM', 'N', 'VALUE')
,fnd_flex_xml_publisher_apis.process_kff_combination_1('item_number', 'OFA', 'KEY#', 101, NULL, fak.CODE_COMBINATION_ID, 'ITEM_NUMBER', 'N', 'VALUE')
,fnd_flex_xml_publisher_apis.process_kff_combination_1('reserve', 'OFA', 'KEY#', 101, NULL, fak.CODE_COMBINATION_ID, 'RESERVE', 'N', 'VALUE')
ORDER BY 1,2,3,5,6,8,9,10,11,12 |
