FA Cost Adjustments by Source - draft
Description
Categories: BI Publisher
Application: Assets
Source: Cost Adjustments by Source Report (XML) - Not Supported: Reserved For Future Use
Short Name: FASCASR_XML
DB package: FA_FASCASR_XMLP_PKG
Source: Cost Adjustments by Source Report (XML) - Not Supported: Reserved For Future Use
Short Name: FASCASR_XML
DB package: FA_FASCASR_XMLP_PKG
Run
FA Cost Adjustments by Source - draft and other Oracle EBS reports with Blitz Report™ on our demo environment
SELECT DECODE(TH.MASS_REFERENCE_ID,NULL,0,1) SOURCE, &ACCT_FLEX_BAL_SEG COMPANY, FALU.MEANING ASSET_TYPE_DESC, DECODE(AH.ASSET_TYPE, 'CIP',CAT_BK.CIP_COST_ACCT, CAT_BK.ASSET_COST_ACCT) ACCOUNT, &ACCT_FLEX_COST_SEG COST_CENTER, AD.ASSET_NUMBER ASSET_NUM, AD.ASSET_NUMBER||' - '||AD.DESCRIPTION ASSET, AD.ASSET_ID ASSET_ID, 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)) VENDOR, nvl(AI_IN.invoice_number,AI_OUT.invoice_number) INVOICE_NO, nvl(AI_IN.INVOICE_LINE_NUMBER, AI_OUT.INVOICE_LINE_NUMBER)||' - '||NVL(AI_IN.AP_DISTRIBUTION_LINE_NUMBER, AI_OUT.AP_DISTRIBUTION_LINE_NUMBER) LINE_NO, TH.TRANSACTION_HEADER_ID THID, NVL(AI_IN.DESCRIPTION,AI_OUT.DESCRIPTION) INVOICE_DESC, ROUND(SUM((DH.UNITS_ASSIGNED/AH.UNITS) * ( decode(it.transaction_type,'INVOICE DELETE', 0-NVL(MC_IN.FIXED_ASSETS_COST,NVL(AI_IN.FIXED_ASSETS_COST,0)), 'INVOICE REINSTATE', NVL(MC_IN.FIXED_ASSETS_COST,NVL(AI_IN.FIXED_ASSETS_COST,0)), NVL(MC_IN.FIXED_ASSETS_COST,NVL(AI_IN.FIXED_ASSETS_COST,0)) - NVL(MC_OUT.FIXED_ASSETS_COST,NVL(AI_OUT.FIXED_ASSETS_COST,0)) ) )), :PRECISION) INVOICE_ADJ, ROUND(SUM((DH.UNITS_ASSIGNED/AH.UNITS) * DECODE(TH.INVOICE_TRANSACTION_ID,NULL, (NVL(MCBK_IN.COST,NVL(BK_IN.COST,0)) - NVL(MCBK_OUT.COST, NVL(BK_OUT.COST,0))), ( decode(it.transaction_type, 'INVOICE DELETE', 0-NVL(MC_IN.FIXED_ASSETS_COST,NVL(AI_IN.FIXED_ASSETS_COST,0)), 'INVOICE REINSTATE', NVL(MC_IN.FIXED_ASSETS_COST,NVL(AI_IN.FIXED_ASSETS_COST,0)), NVL(MC_IN.FIXED_ASSETS_COST,NVL(AI_IN.FIXED_ASSETS_COST,0)) -NVL(MC_OUT.FIXED_ASSETS_COST,NVL(AI_OUT.FIXED_ASSETS_COST,0)) ) ))), :PRECISION) ASSET_ADJ, DECODE(IT.TRANSACTION_TYPE,'INVOICE ADDITION','M', 'INVOICE ADJUSTMENT','A', 'INVOICE TRANSFER','T', 'INVOICE DELETE','D', 'INVOICE REINSTATE','R', NULL) FLAG, DECODE(IT.TRANSACTION_TYPE, NULL, 'NO', 'YES') IS_INV_TRANS, fnd_flex_xml_publisher_apis.process_kff_combination_1('d_comp_code', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE') D_COMP_CODE, fnd_flex_xml_publisher_apis.process_kff_combination_1('d_cost_ctr', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE') D_COST_CTR, FA_FASCASR_XMLP_PKG.out_of_balanceformula( ROUND(SUM((DH.UNITS_ASSIGNED/AH.UNITS) * DECODE(TH.INVOICE_TRANSACTION_ID,NULL, (NVL(MCBK_IN.COST,NVL(BK_IN.COST,0)) - NVL(MCBK_OUT.COST, NVL(BK_OUT.COST,0))), ( decode(it.transaction_type, 'INVOICE DELETE', 0-NVL(MC_IN.FIXED_ASSETS_COST,NVL(AI_IN.FIXED_ASSETS_COST,0)), 'INVOICE REINSTATE', NVL(MC_IN.FIXED_ASSETS_COST,NVL(AI_IN.FIXED_ASSETS_COST,0)), NVL(MC_IN.FIXED_ASSETS_COST,NVL(AI_IN.FIXED_ASSETS_COST,0)) -NVL(MC_OUT.FIXED_ASSETS_COST,NVL(AI_OUT.FIXED_ASSETS_COST,0)) ) ))), :PRECISION), ROUND(SUM((DH.UNITS_ASSIGNED/AH.UNITS) * ( decode(it.transaction_type,'INVOICE DELETE', 0-NVL(MC_IN.FIXED_ASSETS_COST,NVL(AI_IN.FIXED_ASSETS_COST,0)), 'INVOICE REINSTATE', NVL(MC_IN.FIXED_ASSETS_COST,NVL(AI_IN.FIXED_ASSETS_COST,0)), NVL(MC_IN.FIXED_ASSETS_COST,NVL(AI_IN.FIXED_ASSETS_COST,0)) - NVL(MC_OUT.FIXED_ASSETS_COST,NVL(AI_OUT.FIXED_ASSETS_COST,0)) ) )), :PRECISION) ,DECODE(IT.TRANSACTION_TYPE, NULL, 'NO', 'YES') ) OUT_OF_BALANCE FROM FA_INVOICE_TRANSACTIONS IT, FA_MC_ASSET_INVOICES MC_IN, FA_ASSET_INVOICES_BAS AI_IN, FA_MC_ASSET_INVOICES MC_OUT, FA_ASSET_INVOICES_BAS AI_OUT, FA_BOOKS_BAS BK_IN, FA_BOOKS_BAS BK_OUT, FA_MC_BOOKS MCBK_IN, FA_MC_BOOKS MCBK_OUT, FA_TRANSACTION_HEADERS TH, 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 WHERE TH.TRANSACTION_TYPE_CODE IN ('ADJUSTMENT','CIP ADJUSTMENT') AND TH.BOOK_TYPE_CODE =:P_BOOK AND TH.INVOICE_TRANSACTION_ID = IT.INVOICE_TRANSACTION_ID (+) AND TH.DATE_EFFECTIVE BETWEEN :PERIOD1_POD AND :PERIOD2_PCD 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 DH.BOOK_TYPE_CODE = :DISTRIBUTION_SOURCE_BOOK AND DH.ASSET_ID = TH.ASSET_ID AND DH.CODE_COMBINATION_ID = DHCC.CODE_COMBINATION_ID AND CAT_BK.CATEGORY_ID = AH.CATEGORY_ID AND CAT_BK.BOOK_TYPE_CODE = :P_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(+) = :P_BOOK AND BK_IN.TRANSACTION_HEADER_ID_IN(+) = TH.TRANSACTION_HEADER_ID AND MCBK_IN.ASSET_ID(+) = DECODE(:mrc_sob_type_code,'R', TH.ASSET_ID, NULL) AND MCBK_IN.TRANSACTION_HEADER_ID_IN(+) = DECODE(:mrc_sob_type_code,'R', TH.TRANSACTION_HEADER_ID,NULL) AND MCBK_IN.BOOK_TYPE_CODE(+) = DECODE(:mrc_sob_type_code,'R', :P_BOOK, NULL) AND MCBK_IN.SET_OF_BOOKS_ID(+) = :set_of_books_id AND BK_OUT.ASSET_ID(+) = TH.ASSET_ID AND BK_OUT.BOOK_TYPE_CODE(+)||'' = :P_BOOK AND BK_OUT.TRANSACTION_HEADER_ID_OUT(+) = TH.TRANSACTION_HEADER_ID AND MCBK_OUT.ASSET_ID(+) = DECODE(:mrc_sob_type_code,'R', TH.ASSET_ID, NULL) AND MCBK_OUT.TRANSACTION_HEADER_ID_OUT(+) = DECODE(:mrc_sob_type_code,'R', TH.TRANSACTION_HEADER_ID,NULL) AND MCBK_OUT.BOOK_TYPE_CODE(+) = DECODE(:mrc_sob_type_code,'R', :P_BOOK, NULL) AND MCBK_OUT.SET_OF_BOOKS_ID(+) = :set_of_books_id AND AI_IN.ASSET_ID (+) = TH.ASSET_ID AND AI_IN.INVOICE_TRANSACTION_ID_IN(+) = TH.INVOICE_TRANSACTION_ID AND MC_IN.ASSET_ID(+) = DECODE(:mrc_sob_type_code,'R',AI_IN.ASSET_ID,NULL) AND MC_IN.ASSET_INVOICE_ID(+) = DECODE(:mrc_sob_type_code,'R',AI_IN.ASSET_INVOICE_ID,NULL) AND MC_IN.INVOICE_TRANSACTION_ID_IN(+) = DECODE(:mrc_sob_type_code,'R',AI_IN.INVOICE_TRANSACTION_ID_IN,NULL) AND MC_IN.SET_OF_BOOKS_ID(+) = :set_of_books_id AND AI_OUT.ASSET_ID(+) = TH.ASSET_ID AND AI_OUT.INVOICE_TRANSACTION_ID_OUT(+) = TH.INVOICE_TRANSACTION_ID AND MC_OUT.ASSET_ID(+) = DECODE(:mrc_sob_type_code,'R',AI_OUT.ASSET_ID,NULL) AND MC_OUT.ASSET_INVOICE_ID(+) = DECODE(:mrc_sob_type_code,'R',AI_OUT.ASSET_INVOICE_ID,NULL) AND MC_OUT.INVOICE_TRANSACTION_ID_IN(+) = DECODE(:mrc_sob_type_code,'R',AI_OUT.INVOICE_TRANSACTION_ID_IN,NULL) AND MC_OUT.SET_OF_BOOKS_ID(+) = :set_of_books_id AND IT.BOOK_TYPE_CODE (+) = :P_BOOK AND AH.ASSET_ID = TH.ASSET_ID AND TH.DATE_EFFECTIVE BETWEEN AH.DATE_EFFECTIVE AND NVL(AH.DATE_INEFFECTIVE, :PERIOD2_PCD) 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 = decode(ah.asset_type,null,'ASSET TYPE','ASSET TYPE') GROUP BY DECODE(TH.MASS_REFERENCE_ID,NULL,0,1), &ACCT_FLEX_BAL_SEG, FALU.MEANING , DECODE(AH.ASSET_TYPE, 'CIP',CAT_BK.CIP_COST_ACCT, CAT_BK.ASSET_COST_ACCT) , &ACCT_FLEX_COST_SEG, AD.ASSET_NUMBER, AD.DESCRIPTION, AD.ASSET_ID, 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), 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 --ADDED ,fnd_flex_xml_publisher_apis.process_kff_combination_1('d_comp_code', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE') , fnd_flex_xml_publisher_apis.process_kff_combination_1('d_cost_ctr', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE') --ORDER BY 1,2,3,4,5,6,9,10,11,12 ORDER BY 1,D_COMP_CODE,3,4,D_COST_CTR,6,8,9,10,11,12,13,14,15,17,16 |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Book |
|
LOV Oracle | |
From Period |
|
LOV Oracle | |
To Period |
|
LOV Oracle |