FA CIP Statistics for the Central Statistical Office (KSH) (Hungary) (1)
Description
- Categories
- BI Publisher
- Application
- Assets
- Source
- FA CIP Statistics for the Central Statistical Office (KSH) (Hungary)
- Short Name
- FAWIPSTAT
- DB package
- FA_CIP_STAT_PKG
Imported from BI Publisher
Shows Fixed Asset CIP Statistics information in a period range.
Shows Fixed Asset CIP Statistics information in a period range.
SQL 371 lines
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 |
Parameters 3
| Parameter | Type | List of values SQL |
|---|---|---|
| Book | LOV Oracle | |
| From Period | LOV Oracle | |
| To Period | LOV Oracle |