FA Cost Adjustments by Source - draft

➜ Back to Library
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
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
Download
Related Blitz Reports
FA Cost Clearing Reconciliation - draft FA CIP Assets - draft FA Capital Spending - draft FA Mass Additions Invoice Merge - draft FA Additions By Source FA CIP Statistics for the Central Statistical Office (KSH) (Hungary) FA Asset Disposals Responsibility Report- Not Supported: Reserved For Future Use FA Recoverable Cost - draft FA Asset Book Details
Blitz Report™

Blitz Report™ provides multiple benefits: