select
&ACCT_FLEX_BAL_SEG COMP_CODE,
FACB.CIP_COST_ACCT ASSET_ACCOUNT,
&ACCT_FLEX_COST_SEG COST_CENTER,
FADD.ASSET_NUMBER ASSET_NUMBER,
POV.SEGMENT1 || DECODE(POV.SEGMENT1, NULL, ' ',' - ') ||
POV.VENDOR_NAME VEND_NUM_NAME,
INV.INVOICE_NUMBER INV_NUM,
INV.INVOICE_LINE_NUMBER||' - '||INV.AP_DISTRIBUTION_LINE_NUMBER LINE_NUM,
FADD.ASSET_ID ASSET_ID,
FADD.ASSET_NUMBER||' - '||FADD.DESCRIPTION ASSET_NUM_DESC,
INV.DESCRIPTION DESCRIPTION,
ROUND (NVL(MC.FIXED_ASSETS_COST,INV.FIXED_ASSETS_COST) *
SUM (NVL (FADH.UNITS_ASSIGNED, FAH.UNITS) / FAH.UNITS),
:PRECISION) COST,
FAH.CATEGORY_ID ASSET_CAT,
fnd_flex_xml_publisher_apis.process_kff_combination_1('comp_code_dsp', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE') COMP_CODE_DSP,
fnd_flex_xml_publisher_apis.process_kff_combination_1('cost_center_dsp', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE') COST_CENTER_DSP
from
GL_CODE_COMBINATIONS DHCC,
PO_VENDORS POV,
FA_CATEGORY_BOOKS FACB,
FA_MC_ASSET_INVOICES MC,
FA_ASSET_INVOICES_BAS INV,
FA_ADDITIONS FADD,
FA_ASSET_HISTORY FAH,
FA_DISTRIBUTION_HISTORY FADH,
FA_BOOKS FAB
where
FAH.ASSET_TYPE = 'CIP' and
:PERIOD1_PCD between
FAH.DATE_EFFECTIVE AND
NVL (FAH.DATE_INEFFECTIVE, :PERIOD1_PCD)
and
FAB.ASSET_ID = FAH.ASSET_ID and
FAB.BOOK_TYPE_CODE = :P_BOOK and
:PERIOD1_PCD between
FAB.DATE_EFFECTIVE AND
NVL (FAB.DATE_INEFFECTIVE, :PERIOD1_PCD) and
NVL(FAB.PERIOD_COUNTER_FULLY_RETIRED,(:PERIOD1_PC + 1)) >:PERIOD1_PC
and
FADD.ASSET_ID = FAB.ASSET_ID
and
INV.ASSET_ID(+) = FADD.ASSET_ID and
MC.ASSET_ID(+) = INV.ASSET_ID and
MC.ASSET_INVOICE_ID(+) = INV.ASSET_INVOICE_ID and
MC.INVOICE_TRANSACTION_ID_IN(+) = INV.INVOICE_TRANSACTION_ID_IN and
MC.SET_OF_BOOKS_ID(+) = :set_of_books_id and
INV.DELETED_FLAG(+) = 'NO' and :PERIOD1_PCD
between
INV.DATE_EFFECTIVE(+) AND
NVL (INV.DATE_INEFFECTIVE(+), :PERIOD1_PCD)
and
POV.VENDOR_ID(+) = INV.PO_VENDOR_ID
and
FACB.CATEGORY_ID = FAH.CATEGORY_ID and
FACB.BOOK_TYPE_CODE = :P_BOOK
and
FADH.BOOK_TYPE_CODE = :P_BOOK and
FADH.ASSET_ID = FADD.ASSET_ID and
:PERIOD1_PCD BETWEEN
FADH.DATE_EFFECTIVE AND
NVL (FADH.DATE_INEFFECTIVE,:PERIOD1_PCD)
and
DHCC.CODE_COMBINATION_ID = FADH.CODE_COMBINATION_ID
group by
FADD.ASSET_ID,
FADD.ASSET_NUMBER,
FAH.CATEGORY_ID,
FADD.DESCRIPTION,
POV.SEGMENT1,
POV.VENDOR_NAME,
INV.INVOICE_NUMBER,
INV.INVOICE_LINE_NUMBER||' - '||INV.AP_DISTRIBUTION_LINE_NUMBER,
INV.DESCRIPTION,
NVL(MC.FIXED_ASSETS_COST,INV.FIXED_ASSETS_COST),
FAH.UNITS,
&ACCT_FLEX_COST_SEG,
FACB.CIP_COST_ACCT,
&ACCT_FLEX_BAL_SEG
--added
,fnd_flex_xml_publisher_apis.process_kff_combination_1('comp_code_dsp', '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('cost_center_dsp', '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,7
ORDER BY COST_CENTER_DSP |