FA Asset Transfers - draft

➜ Back to Library
Description
Categories: BI Publisher
Application: Assets
Source: Asset Transfers Report (XML) - Not Supported: Reserved For Future Use
Short Name: FAS430_XML
DB package: FA_FAS430_XMLP_PKG
Run FA Asset Transfers - draft and other Oracle EBS reports with Blitz Report™ on our demo environment
SELECT
	AD.ASSET_NUMBER || ' - ' || AD.DESCRIPTION   ASSET_NUMBER,
	TH.TRANSACTION_HEADER_ID				TRANSNUM,
	DECODE(TH.TRANSACTION_HEADER_ID,
		DH.TRANSACTION_HEADER_ID_IN,1,
		DH.TRANSACTION_HEADER_ID_OUT, 0)   TO_FROM,
/*	&ACCT_FLEX_ACCT_SEG     GL_ACCOUNT,
	&ACCT_FLEX_BAL_SEG  COMP_CODE,
	&ACCT_FLEX_COST_SEG     COST_CENTER,
    &LOC_FLEX_ALL_SEG       LOCATION,*/
	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_gl_account', 'SQLGL', 'GL#', ASCC.CHART_OF_ACCOUNTS_ID, NULL, ASCC.CODE_COMBINATION_ID, 'GL_ACCOUNT', 'Y', 'VALUE') D_GL_ACCOUNT1, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_comp_code', 'SQLGL', 'GL#', ASCC.CHART_OF_ACCOUNTS_ID, NULL, ASCC.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE') D_COMP_CODE1, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_cost_center', 'SQLGL', 'GL#', ASCC.CHART_OF_ACCOUNTS_ID, NULL, ASCC.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE') D_COST_CENTER1, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_location', 'OFA', 'LOC#', 101, NULL, LOC.LOCATION_ID, 'ALL', 'Y', 'VALUE') D_LOCATION1,
    TH.TRANSACTION_DATE_ENTERED	 START_DATE,
	dh.assigned_to  assigned_to,
	ascc.code_combination_id ccid,
	SUM(CADJ.ADJUSTMENT_AMOUNT*
		DECODE(CADJ.DEBIT_CREDIT_FLAG,'CR',-1,'DR',1)) COST,
	0				 DEPRN_RESERVE,
	SUM(distinct DECODE(TH.TRANSACTION_HEADER_ID,DH.TRANSACTION_HEADER_ID_IN,1,
		DH.TRANSACTION_HEADER_ID_OUT,-1)*DH.UNITS_ASSIGNED) UNITS,
	FA_FAS430_XMLP_PKG.D_AS_COSTFormula D_RP_DATA_FOUND
/*Unnecessary Code, Commented during DT Fix
FROM,
	&ACCT_FLEX_ACCT_SEG     GL_ACCOUNT,
	&ACCT_FLEX_BAL_SEG  COMP_CODE,
	&ACCT_FLEX_COST_SEG     COST_CENTER,
        	&LOC_FLEX_ALL_SEG       LOCATION,
        	TH.TRANSACTION_DATE_ENTERED	 START_DATE,
	dh.assigned_to  assigned_to,
	ascc.code_combination_id ccid,
	SUM(CADJ.ADJUSTMENT_AMOUNT*
		DECODE(CADJ.DEBIT_CREDIT_FLAG,'CR',-1,'DR',1)) COST,
	0				 DEPRN_RESERVE,
	SUM(distinct DECODE(TH.TRANSACTION_HEADER_ID,DH.TRANSACTION_HEADER_ID_IN,1,
		DH.TRANSACTION_HEADER_ID_OUT,-1)*DH.UNITS_ASSIGNED) UNITS
End of Comment*/
FROM
	FA_LOCATIONS		LOC,
	FA_ADDITIONS                           AD,
	GL_CODE_COMBINATIONS	ASCC,
	FA_DISTRIBUTION_HISTORY   DH,
	FA_TRANSACTION_HEADERS  TH,
	FA_ADJUSTMENTS                     CADJ
WHERE
	TH.BOOK_TYPE_CODE		=  :P_BOOK  AND
	TH.TRANSACTION_TYPE_CODE 	=  'TRANSFER'			AND
	TH.DATE_EFFECTIVE	 >=  :PERIOD1_POD  AND
	TH.DATE_EFFECTIVE	 <=  :PERIOD1_PCD  AND
	nvl(TH.MASS_REFERENCE_ID, 0)    = 
		NVL(:P_MASS_REF_ID, nvl(TH.MASS_REFERENCE_ID, 0))
AND
	(TH.TRANSACTION_HEADER_ID	=  DH.TRANSACTION_HEADER_ID_IN	OR
	TH.TRANSACTION_HEADER_ID	=  DH.TRANSACTION_HEADER_ID_OUT)
AND
	AD.ASSET_ID			=  TH.ASSET_ID			
AND
	LOC.LOCATION_ID			=  DH.LOCATION_ID
AND
	ASCC.CODE_COMBINATION_ID	=  DH.CODE_COMBINATION_ID
AND  
	CADJ.BOOK_TYPE_CODE		= :P_BOOK  AND
	CADJ.ASSET_ID			= TH.ASSET_ID AND
	CADJ.DISTRIBUTION_ID                            	= DH.DISTRIBUTION_ID AND
	CADJ.TRANSACTION_HEADER_ID	= TH.TRANSACTION_HEADER_ID AND
	CADJ.SOURCE_TYPE_CODE		= 'TRANSFER' AND
	CADJ.ADJUSTMENT_TYPE		in ('COST','CIP COST') 
GROUP BY
	TH.TRANSACTION_HEADER_ID,
	DECODE(TH.TRANSACTION_HEADER_ID,
		DH.TRANSACTION_HEADER_ID_IN,1,
		DH.TRANSACTION_HEADER_ID_OUT, 0) ,
	DH.DISTRIBUTION_ID,
--DT Fix Changes
/*	&ACCT_FLEX_ACCT_SEG,
	&ACCT_FLEX_BAL_SEG,
	&ACCT_FLEX_COST_SEG,
                  &LOC_FLEX_ALL_SEG,*/
	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_gl_account', 'SQLGL', 'GL#', ASCC.CHART_OF_ACCOUNTS_ID, NULL, ASCC.CODE_COMBINATION_ID, 'GL_ACCOUNT', 'Y', 'VALUE'), 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_comp_code', 'SQLGL', 'GL#', ASCC.CHART_OF_ACCOUNTS_ID, NULL, ASCC.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE'), 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_cost_center', 'SQLGL', 'GL#', ASCC.CHART_OF_ACCOUNTS_ID, NULL, ASCC.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE'), 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_location', 'OFA', 'LOC#', 101, NULL, LOC.LOCATION_ID, 'ALL', 'Y', 'VALUE'),
--End of DT Fix Changes
   	AD.ASSET_NUMBER,
	AD.DESCRIPTION,
	TH.TRANSACTION_DATE_ENTERED,
	DH.ASSIGNED_TO,
	ascc.code_combination_id
UNION
SELECT
	AD.ASSET_NUMBER || ' - ' || AD.DESCRIPTION   ASSET_NUMBER,
	TH.TRANSACTION_HEADER_ID				TRANSNUM,
	DECODE(TH.TRANSACTION_HEADER_ID,
		DH.TRANSACTION_HEADER_ID_IN,1,
		DH.TRANSACTION_HEADER_ID_OUT, 0)   TO_FROM,
/*	&ACCT_FLEX_ACCT_SEG     GL_ACCOUNT,
	&ACCT_FLEX_BAL_SEG  COMP_CODE,
	&ACCT_FLEX_COST_SEG     COST_CENTER,
        	&LOC_FLEX_ALL_SEG       LOCATION,*/
	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_gl_account', 'SQLGL', 'GL#', ASCC.CHART_OF_ACCOUNTS_ID, NULL, ASCC.CODE_COMBINATION_ID, 'GL_ACCOUNT', 'Y', 'VALUE') D_GL_ACCOUNT1, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_comp_code', 'SQLGL', 'GL#', ASCC.CHART_OF_ACCOUNTS_ID, NULL, ASCC.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE') D_COMP_CODE1, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_cost_center', 'SQLGL', 'GL#', ASCC.CHART_OF_ACCOUNTS_ID, NULL, ASCC.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE') D_COST_CENTER1, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_location', 'OFA', 'LOC#', 101, NULL, LOC.LOCATION_ID, 'ALL', 'Y', 'VALUE') D_LOCATION1,
    TH.TRANSACTION_DATE_ENTERED	 START_DATE,
	dh.assigned_to assigned_to,
	ascc.code_combination_id ccid,
	0				 COST,
	SUM(NVL(RADJ.ADJUSTMENT_AMOUNT,0) *
		DECODE(NVL(RADJ.DEBIT_CREDIT_FLAG,'CR'),'CR',1,'DR',-1)) DEPRN_RESERVE,
	SUM(distinct DECODE(TH.TRANSACTION_HEADER_ID,DH.TRANSACTION_HEADER_ID_IN,1,
		DH.TRANSACTION_HEADER_ID_OUT,-1)*DH.UNITS_ASSIGNED) UNITS,
	FA_FAS430_XMLP_PKG.D_AS_COSTFormula D_RP_DATA_FOUND	
/* Unnecessary Code, commented during DT Fix	
FROM,
	&ACCT_FLEX_ACCT_SEG     GL_ACCOUNT,
	&ACCT_FLEX_BAL_SEG  COMP_CODE,
	&ACCT_FLEX_COST_SEG     COST_CENTER,
        	&LOC_FLEX_ALL_SEG       LOCATION,
        	TH.TRANSACTION_DATE_ENTERED	 START_DATE,
	dh.assigned_to assigned_to,
	ascc.code_combination_id ccid,
	0				 COST,
	SUM(NVL(RADJ.ADJUSTMENT_AMOUNT,0) *
		DECODE(NVL(RADJ.DEBIT_CREDIT_FLAG,'CR'),'CR',1,'DR',-1)) DEPRN_RESERVE,
	SUM(distinct DECODE(TH.TRANSACTION_HEADER_ID,DH.TRANSACTION_HEADER_ID_IN,1,
		DH.TRANSACTION_HEADER_ID_OUT,-1)*DH.UNITS_ASSIGNED) UNITS
End of Comment*/
FROM
	FA_LOCATIONS		LOC,
	FA_ADDITIONS                           AD,
	GL_CODE_COMBINATIONS	ASCC,
	FA_DISTRIBUTION_HISTORY   DH,
	FA_TRANSACTION_HEADERS  TH,
	FA_ADJUSTMENTS                     RADJ
WHERE
	TH.BOOK_TYPE_CODE		=  :P_BOOK  AND
	TH.TRANSACTION_TYPE_CODE 	=  'TRANSFER'			AND
	TH.DATE_EFFECTIVE	 >=  :PERIOD1_POD  AND
	TH.DATE_EFFECTIVE	 <=  :PERIOD1_PCD  AND
	nvl(TH.MASS_REFERENCE_ID, 0)    = 
		NVL(:P_MASS_REF_ID, nvl(TH.MASS_REFERENCE_ID, 0))
AND
	(TH.TRANSACTION_HEADER_ID	=  DH.TRANSACTION_HEADER_ID_IN	OR
	TH.TRANSACTION_HEADER_ID	=  DH.TRANSACTION_HEADER_ID_OUT)
AND
	AD.ASSET_ID			=  TH.ASSET_ID			
AND
	LOC.LOCATION_ID			=  DH.LOCATION_ID
AND
	ASCC.CODE_COMBINATION_ID	=  DH.CODE_COMBINATION_ID
AND
	RADJ.BOOK_TYPE_CODE		= :P_BOOK  AND
	RADJ.ASSET_ID			= TH.ASSET_ID AND
	RADJ.DISTRIBUTION_ID		= DH.DISTRIBUTION_ID AND
	RADJ.SOURCE_TYPE_CODE		= 'TRANSFER' AND
	RADJ.ADJUSTMENT_TYPE		= 'RESERVE' AND
	RADJ.TRANSACTION_HEADER_ID	= TH.TRANSACTION_HEADER_ID
GROUP BY
	TH.TRANSACTION_HEADER_ID,
	DECODE(TH.TRANSACTION_HEADER_ID,
		DH.TRANSACTION_HEADER_ID_IN,1,
		DH.TRANSACTION_HEADER_ID_OUT, 0) ,
	DH.DISTRIBUTION_ID,
--DT Fix Start
/*	&ACCT_FLEX_ACCT_SEG,
	&ACCT_FLEX_BAL_SEG,
	&ACCT_FLEX_COST_SEG,
                  &LOC_FLEX_ALL_SEG,*/
	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_gl_account', 'SQLGL', 'GL#', ASCC.CHART_OF_ACCOUNTS_ID, NULL, ASCC.CODE_COMBINATION_ID, 'GL_ACCOUNT', 'Y', 'VALUE'), 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_comp_code', 'SQLGL', 'GL#', ASCC.CHART_OF_ACCOUNTS_ID, NULL, ASCC.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE'), 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_cost_center', 'SQLGL', 'GL#', ASCC.CHART_OF_ACCOUNTS_ID, NULL, ASCC.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE'), 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_location', 'OFA', 'LOC#', 101, NULL, LOC.LOCATION_ID, 'ALL', 'Y', 'VALUE'),
--End of DT Fix
    AD.ASSET_NUMBER,
	AD.DESCRIPTION,
	TH.TRANSACTION_DATE_ENTERED,
	DH.ASSIGNED_TO,
	ascc.code_combination_id
--ORDER BY 1,2,3,4,5,6,7
ORDER BY 1,2,3,4,5,6,7,8,13, 9 , 10, 12, 11
Parameter Name SQL text Validation
Book 
 
 LOV Oracle
Period 
 
 LOV Oracle
Download
Related Blitz Reports
FA Asset Additions Responsibility Report- Not Supported: Reserved For Future Use GL Account Analysis (Distributions) FA Asset Retirements FA Adjusted Form 4626 - AMT Detail Report- Not Supported: Reserved For Future Use FA Asset Additions By Cost Center Report- Not Supported: Reserved For Future Use FA Asset Reclassification Reconciliation - draft FA Asset Reclassification - draft FA Asset Retirements Report- Not Supported: Reserved For Future Use GL Account Analysis
Blitz Report™

Blitz Report™ provides multiple benefits: