FA Additions By Source - draft

Description
Categories: BI Publisher
Application: Assets
Source: Additions By Source Report (XML) - Not Supported: Reserved For Future Use
Short Name: FASASSBS_XML
DB package: FA_FASASSBS_XMLP_PKG
SELECT	DECODE(TH.MASS_REFERENCE_ID,NULL,0,1)		SOURCE,
	&ACCT_FLEX_BAL_SEG				COMP_CODE,
	FALU.MEANING					ASSET_TYPE_DESC,
	AH.ASSET_TYPE					ASSET_TYPE,
	DECODE(AH.ASSET_TYPE, 'CIP', CB.CIP_COST_ACCT,
		CB.ASSET_COST_ACCT)			ACCOUNT,
	&ACCT_FLEX_COST_SEG				COST_CENTER,
	AD.ASSET_NUMBER||' - '||AD.DESCRIPTION		ASSET,
	AD.ASSET_NUMBER				ASSET_ORD,
	1						ORD_BY,
	NULL						VEND_NUM,
 	NULL						INV_NUM,
	TO_NUMBER(NULL)					LINE_NUM,
	NULL						DESCRIPTION,
	TO_NUMBER(NULL)					ORIG_COST,
	TO_NUMBER(NULL)					INV_COST,
	SUM(NVL(DD.ADDITION_COST_TO_CLEAR, 0))		COST,
	NULL					     	FLAG,
	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, 
	FA_FASASSBS_XMLP_PKG.c_at_unbalformula(:AT_INV_COST, :AT_ASS_COST, AH.ASSET_TYPE) C_AT_UNBAL, 
	FA_FASASSBS_XMLP_PKG.c_ac_unbalformula(:AC_INV_COST, :AC_ASS_COST, AH.ASSET_TYPE) C_AC_UNBAL, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_cost_center', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE') D_COST_CENTER, 
	FA_FASASSBS_XMLP_PKG.c_cc_unbalformula(:CC_INV_COST, :CC_ASS_COST, AH.ASSET_TYPE) C_CC_UNBAL, 
	FA_FASASSBS_XMLP_PKG.c_unbalformula(:AS_INV_COST, :AS_ASS_COST, AH.ASSET_TYPE) C_UNBAL
FROM 
     	FA_DISTRIBUTION_HISTORY 		DH,
     	FA_ASSET_HISTORY 		AH,
     	FA_CATEGORY_BOOKS 		CB,
     	FA_LOOKUPS 			FALU,
     	FA_ADDITIONS 			AD,
     	GL_CODE_COMBINATIONS 		DHCC,
    	FA_TRANSACTION_HEADERS 		TH,
	FA_DEPRN_DETAIL			DD,
     	FA_DEPRN_PERIODS		DP
WHERE
	DP.BOOK_TYPE_CODE =:P_BOOK AND
	DP.PERIOD_COUNTER >=:PERIOD1_PC 
	AND	DP.PERIOD_COUNTER <= nvl(:PERIOD2_PC, DP.PERIOD_COUNTER)	
AND
	TH.ASSET_ID			= DD.ASSET_ID			AND
       	TH.DATE_EFFECTIVE 	       >=  DP.PERIOD_OPEN_DATE		AND
	TH.DATE_EFFECTIVE		<  nvl(DP.PERIOD_CLOSE_DATE,
					       sysdate)			AND
	TH.BOOK_TYPE_CODE = :P_BOOK AND
	TH.TRANSACTION_TYPE_CODE 	in ('TRANSFER IN','TRANSFER IN/VOID')
AND
	TH.Transaction_Header_ID = 
		(SELECT min(transaction_header_id)
		 FROM fa_transaction_headers thvoid	
		 WHERE THVOID.BOOK_TYPE_CODE		= TH.BOOK_TYPE_CODE
		 AND   THVOID.TRANSACTION_TYPE_CODE	in ('TRANSFER IN/VOID', 'TRANSFER IN') 
		 AND   THVOID.ASSET_ID				= TH.ASSET_ID)
AND
	DH.BOOK_TYPE_CODE = :DISTRIBUTION_SOURCE_BOOK AND
	DH.ASSET_ID 			= DD.ASSET_ID			AND
	DHCC.CODE_COMBINATION_ID	=  DH.CODE_COMBINATION_ID
AND
	DD.BOOK_TYPE_CODE = :P_BOOK AND
	DD.DEPRN_SOURCE_CODE		=  'B'				AND
	DD.DISTRIBUTION_ID		=  DH.DISTRIBUTION_ID		AND
	DD.PERIOD_COUNTER		= DP.PERIOD_COUNTER - 1
AND
	CB.CATEGORY_ID			=  AH.CATEGORY_ID		AND
	CB.BOOK_TYPE_CODE = :P_BOOK 
	AND
	AD.ASSET_ID			=  DD.ASSET_ID		
AND
	AH.ASSET_ID			=  AD.ASSET_ID			AND
	AH.DATE_EFFECTIVE	       <=  NVL(DP.PERIOD_CLOSE_DATE, SYSDATE)	AND
	NVL(AH.DATE_INEFFECTIVE,SYSDATE+1) >  NVL(DP.PERIOD_CLOSE_DATE, SYSDATE)
AND
	AH.ASSET_TYPE			=  FALU.LOOKUP_CODE	AND
	FALU.LOOKUP_TYPE		= 'ASSET TYPE'
GROUP BY
	DECODE(TH.MASS_REFERENCE_ID,NULL,0,1),
	&ACCT_FLEX_BAL_SEG,
	FALU.MEANING,
	AH.ASSET_TYPE,
	DECODE(AH.ASSET_TYPE, 'CIP', CB.CIP_COST_ACCT,
		CB.ASSET_COST_ACCT),
	&ACCT_FLEX_COST_SEG,
	AD.ASSET_NUMBER||' - '||AD.DESCRIPTION,
	AD.ASSET_NUMBER,
	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_center', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE')
UNION ALL
SELECT	DECODE(TH.MASS_REFERENCE_ID,NULL,0,1)		SOURCE,
	&ACCT_FLEX_BAL_SEG				COMP_CODE,
	FALU.MEANING					ASSET_TYPE_DESC,
	AH.ASSET_TYPE					ASSET_TYPE,
	DECODE(AH.ASSET_TYPE, 'CIP', CB.CIP_COST_ACCT,
		CB.ASSET_COST_ACCT)			ACCOUNT,
	&ACCT_FLEX_COST_SEG				COST_CENTER,
	AD.ASSET_NUMBER||' - '||AD.DESCRIPTION		ASSET,
	AD.ASSET_NUMBER				ASSET_ORD,
	1						ORD_BY,
	NULL						VEND_NUM,
 	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,
	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, 
	FA_FASASSBS_XMLP_PKG.c_at_unbalformula(:AT_INV_COST, :AT_ASS_COST, AH.ASSET_TYPE) C_AT_UNBAL, 
	FA_FASASSBS_XMLP_PKG.c_ac_unbalformula(:AC_INV_COST, :AC_ASS_COST, AH.ASSET_TYPE) C_AC_UNBAL, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_cost_center', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE') D_COST_CENTER, 
	FA_FASASSBS_XMLP_PKG.c_cc_unbalformula(:CC_INV_COST, :CC_ASS_COST, AH.ASSET_TYPE) C_CC_UNBAL, 
	FA_FASASSBS_XMLP_PKG.c_unbalformula(:AS_INV_COST, :AS_ASS_COST, AH.ASSET_TYPE) C_UNBAL
FROM
	FA_TRANSACTION_HEADERS TH,
	FA_TRANSACTION_HEADERS	THDIS,
	FA_ADDITIONS           	AD,
	FA_ASSET_HISTORY	AH,
	FA_CATEGORY_BOOKS	CB,
	FA_DISTRIBUTION_HISTORY 	DH,
	GL_CODE_COMBINATIONS	DHCC,
	GL_CODE_COMBINATIONS	AJCC,
	FA_LOOKUPS		FALU,
	FA_ADJUSTMENTS		ADJ,
	FA_DEPRN_PERIODS	DP
WHERE
	DP.BOOK_TYPE_CODE = :P_BOOK AND
	DP.PERIOD_COUNTER >= :PERIOD1_PC AND
	DP.PERIOD_COUNTER <= nvl(:PERIOD2_PC,DP.PERIOD_COUNTER)
	AND
        	TH.DATE_EFFECTIVE 	       >=  DP.PERIOD_OPEN_DATE		AND
	TH.DATE_EFFECTIVE		<  nvl(DP.PERIOD_CLOSE_DATE,
					       sysdate)			AND
	TH.BOOK_TYPE_CODE = :P_BOOK AND
	TH.TRANSACTION_TYPE_CODE 	= 'ADDITION'
AND
	THDIS.TRANSACTION_TYPE_CODE	= 'TRANSFER IN'		AND
	THDIS.BOOK_TYPE_CODE = :P_BOOK AND
	THDIS.ASSET_ID			= TH.ASSET_ID		AND
	THDIS.DATE_EFFECTIVE 		< DP.PERIOD_OPEN_DATE
AND
	ADJ.BOOK_TYPE_CODE = :P_BOOK AND
	ADJ.ASSET_ID 			= TH.ASSET_ID			AND
	ADJ.SOURCE_TYPE_CODE 		= 'ADDITION'			AND
	ADJ.ADJUSTMENT_TYPE 		= 'COST'				AND
	ADJ.PERIOD_COUNTER_CREATED 	= DP.PERIOD_COUNTER		AND
	ADJ.CODE_COMBINATION_ID		= AJCC.CODE_COMBINATION_ID
AND
	DH.BOOK_TYPE_CODE = :P_BOOK AND
	DH.ASSET_ID			= TH.ASSET_ID			AND
	DH.DISTRIBUTION_ID		= ADJ.DISTRIBUTION_ID		AND
	DH.CODE_COMBINATION_ID		= DHCC.CODE_COMBINATION_ID
AND
	CB.CATEGORY_ID			=  AH.CATEGORY_ID		AND
	CB.BOOK_TYPE_CODE = :P_BOOK AND
	AD.ASSET_ID			=  TH.ASSET_ID		
AND
	AH.ASSET_ID			=  TH.ASSET_ID			AND
	AH.DATE_EFFECTIVE	       <=  TH.DATE_EFFECTIVE	AND
	NVL(AH.DATE_INEFFECTIVE,SYSDATE+1) >  TH.DATE_EFFECTIVE
AND
	AH.ASSET_TYPE			=  FALU.LOOKUP_CODE	AND
	FALU.LOOKUP_TYPE		= 'ASSET TYPE'	
GROUP BY
	DECODE(TH.MASS_REFERENCE_ID,NULL,0,1),
	&ACCT_FLEX_BAL_SEG,
	FALU.MEANING,
	AH.ASSET_TYPE,
	DECODE(AH.ASSET_TYPE, 'CIP', CB.CIP_COST_ACCT,
		CB.ASSET_COST_ACCT),
	&ACCT_FLEX_COST_SEG,
	AD.ASSET_NUMBER||' - '||AD.DESCRIPTION,
	AD.ASSET_NUMBER,
	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_center', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE')
UNION ALL
SELECT	DECODE(TH.MASS_REFERENCE_ID,NULL,0,1)			SOURCE,
	&ACCT_FLEX_BAL_SEG					COMP_CODE,
	FALU.MEANING						ASSET_TYPE_DESC,
	AH.ASSET_TYPE						ASSET_TYPE,
	DECODE(AH.ASSET_TYPE, 'CIP', CB.CIP_COST_ACCT,
		CB.ASSET_COST_ACCT)				ACCOUNT,
	&ACCT_FLEX_COST_SEG					COST_CENTER,
	AD.ASSET_NUMBER||' - '||AD.DESCRIPTION			ASSET,
	AD.ASSET_NUMBER					ASSET_ORD,
	2							ORD_BY,
	PO_VEND.segment1						VEND_NUM,
 	AI_IN.INVOICE_NUMBER					INV_NUM,
	AI_IN.INVOICE_LINE_NUMBER||' - '||AI_IN.AP_DISTRIBUTION_LINE_NUMBER				LINE_NUM,
	AI_IN.DESCRIPTION						DESCRIPTION,
	AI_IN.PAYABLES_COST 		ORIG_COST,
	round(SUM(DH.UNITS_ASSIGNED/AH.UNITS * AI_IN.FIXED_ASSETS_COST), :precision)	INV_COST,
	TO_NUMBER(NULL)						COST,
	DECODE(IT.TRANSACTION_TYPE,'INVOICE ADDITION','M',
				   'INVOICE ADJUSTMENT','A',
				   'INVOICE TRANSFER','T',
				   'INVOICE REINSTATE','R',NULL)      	FLAG,
				   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, 
	FA_FASASSBS_XMLP_PKG.c_at_unbalformula(:AT_INV_COST, :AT_ASS_COST, AH.ASSET_TYPE) C_AT_UNBAL, 
	FA_FASASSBS_XMLP_PKG.c_ac_unbalformula(:AC_INV_COST, :AC_ASS_COST, AH.ASSET_TYPE) C_AC_UNBAL, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_cost_center', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE') D_COST_CENTER, 
	FA_FASASSBS_XMLP_PKG.c_cc_unbalformula(:CC_INV_COST, :CC_ASS_COST, AH.ASSET_TYPE) C_CC_UNBAL, 
	FA_FASASSBS_XMLP_PKG.c_unbalformula(:AS_INV_COST, :AS_ASS_COST, AH.ASSET_TYPE) C_UNBAL
FROM 
	FA_ASSET_INVOICES 		AI_IN,
     	FA_INVOICE_TRANSACTIONS    	IT,
     	FA_TRANSACTION_HEADERS 		TH,
     	FA_DISTRIBUTION_HISTORY 		DH,
     	FA_ASSET_HISTORY 		AH,
     	FA_CATEGORY_BOOKS 		CB,
     	FA_LOOKUPS 			FALU,
     	PO_VENDORS 			PO_VEND,
     	FA_ADDITIONS 			AD,
     	GL_CODE_COMBINATIONS 		DHCC,
	FA_DEPRN_DETAIL			DD,
	FA_DEPRN_PERIODS		DP
WHERE
	DP.BOOK_TYPE_CODE = :P_BOOK AND
	DP.PERIOD_COUNTER >= :PERIOD1_PC AND
	DP.PERIOD_COUNTER <= nvl(:PERIOD2_PC,DP.PERIOD_COUNTER)	
AND
	TH.ASSET_ID			= DD.ASSET_ID			AND
       	TH.DATE_EFFECTIVE 	       >=  DP.PERIOD_OPEN_DATE		AND
	TH.DATE_EFFECTIVE		<  nvl(DP.PERIOD_CLOSE_DATE,
					       sysdate)			AND
	TH.BOOK_TYPE_CODE = :P_BOOK AND
	TH.TRANSACTION_TYPE_CODE 	in ('TRANSFER IN','TRANSFER IN/VOID')
AND
	TH.Transaction_Header_ID = 
		(SELECT min(transaction_header_id)
		 FROM fa_transaction_headers thvoid	
		 WHERE THVOID.BOOK_TYPE_CODE		= TH.BOOK_TYPE_CODE
		 AND   THVOID.TRANSACTION_TYPE_CODE	in ('TRANSFER IN/VOID', 'TRANSFER IN') 
		 AND   THVOID.ASSET_ID				= TH.ASSET_ID)
AND
	DH.BOOK_TYPE_CODE = :DISTRIBUTION_SOURCE_BOOK AND
	DH.ASSET_ID 			= DD.ASSET_ID			AND
	DHCC.CODE_COMBINATION_ID	=  DH.CODE_COMBINATION_ID
AND
	DD.BOOK_TYPE_CODE = :P_BOOK AND
	DD.DEPRN_SOURCE_CODE		=  'B'				AND
	DD.DISTRIBUTION_ID		=  DH.DISTRIBUTION_ID		AND
	DD.PERIOD_COUNTER		= DP.PERIOD_COUNTER - 1
AND
	CB.CATEGORY_ID			=  AH.CATEGORY_ID		AND
	CB.BOOK_TYPE_CODE = :P_BOOK 
	AND
	AD.ASSET_ID			=  DD.ASSET_ID		
AND
	AH.ASSET_ID			=  AD.ASSET_ID			AND
	AH.DATE_EFFECTIVE	       <=  NVL(DP.PERIOD_CLOSE_DATE, SYSDATE)	AND
	NVL(AH.DATE_INEFFECTIVE,SYSDATE+1) >  NVL(DP.PERIOD_CLOSE_DATE, SYSDATE)
AND
	AH.ASSET_TYPE			=  FALU.LOOKUP_CODE	AND
	FALU.LOOKUP_TYPE		= 'ASSET TYPE'
AND
	IT.INVOICE_TRANSACTION_ID = AI_IN.INVOICE_TRANSACTION_ID_IN
AND
	AI_IN.ASSET_ID = TH.ASSET_ID				AND
	AI_IN.DATE_EFFECTIVE <=  NVL(DP.PERIOD_CLOSE_DATE, SYSDATE)		AND
	NVL(AI_IN.DATE_INEFFECTIVE, SYSDATE+1) > NVL(DP.PERIOD_CLOSE_DATE, SYSDATE)	AND 
	AI_IN.DELETED_FLAG = 'NO'
AND
	PO_VEND.VENDOR_ID(+) = AI_IN.PO_VENDOR_ID
GROUP BY
	DECODE(TH.MASS_REFERENCE_ID,NULL,0,1),
	&ACCT_FLEX_BAL_SEG,
	FALU.MEANING,
	AH.ASSET_TYPE,
	DECODE(AH.ASSET_TYPE, 'CIP', CB.CIP_COST_ACCT,
		CB.ASSET_COST_ACCT),
	&ACCT_FLEX_COST_SEG,
	AD.ASSET_NUMBER||' - '||AD.DESCRIPTION,
	AD.ASSET_NUMBER,
	PO_VEND.segment1,
 	AI_IN.INVOICE_NUMBER,
	AI_IN.INVOICE_LINE_NUMBER||' - '||AI_IN.AP_DISTRIBUTION_LINE_NUMBER,
	AI_IN.DESCRIPTION,
	AI_IN.PAYABLES_COST ,
	DECODE(IT.TRANSACTION_TYPE,'INVOICE ADDITION','M',
				   'INVOICE ADJUSTMENT','A',
				   'INVOICE TRANSFER','T',
				   'INVOICE REINSTATE','R',NULL)
				   ,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_center', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE')
UNION ALL
SELECT	DECODE(TH.MASS_REFERENCE_ID,NULL,0,1)			SOURCE,
	&ACCT_FLEX_BAL_SEG					COMP_CODE,
	FALU.MEANING						ASSET_TYPE_DESC,
	AH.ASSET_TYPE						ASSET_TYPE,
	DECODE(AH.ASSET_TYPE, 'CIP', CB.CIP_COST_ACCT,
		CB.ASSET_COST_ACCT)				ACCOUNT,
	&ACCT_FLEX_COST_SEG					COST_CENTER,
	AD.ASSET_NUMBER||' - '||AD.DESCRIPTION			ASSET,
	AD.ASSET_NUMBER					ASSET_ORD,
	2							ORD_BY,
	PO_VEND.segment1						VEND_NUM,
 	AI_IN.INVOICE_NUMBER					INV_NUM,
	AI_IN.INVOICE_LINE_NUMBER||' - '||AI_IN.AP_DISTRIBUTION_LINE_NUMBER				LINE_NUM,
	AI_IN.DESCRIPTION						DESCRIPTION,
	AI_IN.PAYABLES_COST 		ORIG_COST,
	round(SUM(DH.UNITS_ASSIGNED/AH.UNITS * AI_IN.FIXED_ASSETS_COST),:precision)	INV_COST,
	TO_NUMBER(NULL)						COST,
	DECODE(IT.TRANSACTION_TYPE,'INVOICE ADDITION','M',
				   'INVOICE ADJUSTMENT','A',
				   'INVOICE TRANSFER','T',
				   'INVOICE REINSTATE','R',NULL)      	FLAG,
				   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, 
	FA_FASASSBS_XMLP_PKG.c_at_unbalformula(:AT_INV_COST, :AT_ASS_COST, AH.ASSET_TYPE) C_AT_UNBAL, 
	FA_FASASSBS_XMLP_PKG.c_ac_unbalformula(:AC_INV_COST, :AC_ASS_COST, AH.ASSET_TYPE) C_AC_UNBAL, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_cost_center', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE') D_COST_CENTER, 
	FA_FASASSBS_XMLP_PKG.c_cc_unbalformula(:CC_INV_COST, :CC_ASS_COST, AH.ASSET_TYPE) C_CC_UNBAL, 
	FA_FASASSBS_XMLP_PKG.c_unbalformula(:AS_INV_COST, :AS_ASS_COST, AH.ASSET_TYPE) C_UNBAL
FROM 
	FA_ASSET_INVOICES 		AI_IN,
     	FA_INVOICE_TRANSACTIONS    	IT,
     	FA_TRANSACTION_HEADERS 		THDIS,
     	FA_DISTRIBUTION_HISTORY 		DH,
     	FA_ASSET_HISTORY 		AH,
     	FA_CATEGORY_BOOKS 		CB,
     	FA_LOOKUPS 			FALU,
     	PO_VENDORS 			PO_VEND,
     	FA_ADDITIONS 			AD,
     	GL_CODE_COMBINATIONS 		DHCC,
   	FA_TRANSACTION_HEADERS 		TH,
	FA_DEPRN_PERIODS		DP
WHERE
	DP.BOOK_TYPE_CODE = :P_BOOK AND
	DP.PERIOD_COUNTER >=:PERIOD1_PC AND
	DP.PERIOD_COUNTER <= nvl(:PERIOD2_PC,DP.PERIOD_COUNTER)	
AND
        	TH.DATE_EFFECTIVE 	       >=  DP.PERIOD_OPEN_DATE		AND
	TH.DATE_EFFECTIVE		<  nvl(DP.PERIOD_CLOSE_DATE,
					       sysdate)			AND
	TH.BOOK_TYPE_CODE = :P_BOOK AND
	TH.TRANSACTION_TYPE_CODE 	= 'ADDITION'
AND
	THDIS.TRANSACTION_TYPE_CODE	= 'TRANSFER IN'		AND
	THDIS.BOOK_TYPE_CODE = :P_BOOK AND
	THDIS.ASSET_ID			= TH.ASSET_ID		AND
	THDIS.DATE_EFFECTIVE 		< DP.PERIOD_OPEN_DATE
AND
	DH.BOOK_TYPE_CODE = :P_BOOK AND
	DH.ASSET_ID			= TH.ASSET_ID			AND
	DH.CODE_COMBINATION_ID		= DHCC.CODE_COMBINATION_ID	AND
	DH.DATE_EFFECTIVE		<= TH.DATE_EFFECTIVE 		AND
	NVL(DH.DATE_INEFFECTIVE, SYSDATE)	> TH.DATE_EFFECTIVE
AND
	CB.CATEGORY_ID			=  AH.CATEGORY_ID		AND
	CB.BOOK_TYPE_CODE = :P_BOOK 
	AND
	AD.ASSET_ID			=  TH.ASSET_ID		
AND
	AH.ASSET_ID			=  TH.ASSET_ID			AND
	AH.DATE_EFFECTIVE	       <=  TH.DATE_EFFECTIVE	AND
	NVL(AH.DATE_INEFFECTIVE,SYSDATE+1) >  TH.DATE_EFFECTIVE
AND
	AH.ASSET_TYPE			=  FALU.LOOKUP_CODE	AND
	FALU.LOOKUP_TYPE		= 'ASSET TYPE'
AND
	IT.INVOICE_TRANSACTION_ID = AI_IN.INVOICE_TRANSACTION_ID_IN
AND
	AI_IN.ASSET_ID = TH.ASSET_ID				AND
	AI_IN.DATE_EFFECTIVE <=  NVL(DP.PERIOD_CLOSE_DATE, SYSDATE)		AND
	NVL(AI_IN.DATE_INEFFECTIVE, SYSDATE+1) > NVL(DP.PERIOD_CLOSE_DATE, SYSDATE)	AND 
	AI_IN.DELETED_FLAG = 'NO'
AND
	PO_VEND.VENDOR_ID(+) = AI_IN.PO_VENDOR_ID
GROUP BY
	DECODE(TH.MASS_REFERENCE_ID,NULL,0,1),
	&ACCT_FLEX_BAL_SEG,
	FALU.MEANING,
	AH.ASSET_TYPE,
	DECODE(AH.ASSET_TYPE, 'CIP', CB.CIP_COST_ACCT,
		CB.ASSET_COST_ACCT),
	&ACCT_FLEX_COST_SEG,
	AD.ASSET_NUMBER||' - '||AD.DESCRIPTION,
	AD.ASSET_NUMBER,
	PO_VEND.segment1,
 	AI_IN.INVOICE_NUMBER,
	AI_IN.INVOICE_LINE_NUMBER||' - '||AI_IN.AP_DISTRIBUTION_LINE_NUMBER,
	AI_IN.DESCRIPTION,
	AI_IN.PAYABLES_COST ,
	DECODE(IT.TRANSACTION_TYPE,'INVOICE ADDITION','M',
				   'INVOICE ADJUSTMENT','A',
				   'INVOICE TRANSFER','T',
				   'INVOICE REINSTATE','R',NULL)
				 ,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_center', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE')
--ORDER BY 1,2,3,5,6,8,9,10,11,12
order by 1,D_COMP_CODE,3,4,5,D_COST_CENTER,8,7,9,10,11,12,13,14,16,15,17
Parameter Name SQL text Validation
To Period
 
LOV Oracle
From Period
 
LOV Oracle
Book
 
LOV Oracle
Ask a question