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 |