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 |