SELECT
AD.Asset_Number || '-' || AD.Description Asset_Descr,
&FCC_Allsegs From_GL_Number,
decode( mt.to_gl_ccid, null, null, &TCC_Allsegs ) To_GL_Number,
&FLOC_Allsegs From_Location,
decode (TO_LOC.Location_ID,
null, to_char (null),
From_LOC.Location_ID, to_char (null),
&TLOC_Allsegs) To_Location,
FROM_EMP.Employee_Number From_Employee_Number,
decode (TO_EMP.Person_ID,
null, to_char (null),
FROM_EMP.Person_ID, to_char (null),
TO_EMP.Employee_Number) To_Employee_Number,
from_cc.code_combination_id from_exp_ccid,
mt.segment1, mt.segment2, mt.segment3, mt.segment4, mt.segment5,
mt.segment6, mt.segment7, mt.segment8, mt.segment9, mt.segment10,
mt.segment11, mt.segment12, mt.segment13, mt.segment14, mt.segment15,
mt.segment16, mt.segment17, mt.segment18, mt.segment19, mt.segment20,
mt.segment21, mt.segment22, mt.segment23, mt.segment24, mt.segment25,
mt.segment26, mt.segment27, mt.segment28, mt.segment29, mt.segment30,
fnd_flex_xml_publisher_apis.process_kff_combination_1('c_from_gl', 'SQLGL', 'GL#', FROM_CC.CHART_OF_ACCOUNTS_ID, NULL, FROM_CC.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') C_From_GL,
fnd_flex_xml_publisher_apis.process_kff_combination_1('c_to_gl', 'SQLGL', 'GL#', to_cc.CHART_OF_ACCOUNTS_ID, NULL, to_cc.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') C_To_GL,
fnd_flex_xml_publisher_apis.process_kff_combination_1('c_from_loc', 'OFA', 'LOC#', 101, NULL, FROM_LOC.LOCATION_ID, 'ALL', 'Y', 'VALUE') C_From_Loc,
fnd_flex_xml_publisher_apis.process_kff_combination_1('c_to_loc', 'OFA', 'LOC#', 101, NULL, TO_LOC.LOCATION_ID, 'ALL', 'Y', 'VALUE') C_To_Loc,
FA_FAS811_XMLP_PKG.CP_100_p CP_100
FROM
PER_ALL_PEOPLE_F FROM_EMP,
PER_ALL_PEOPLE_F TO_EMP,
FA_BOOKS BK,
FA_ADDITIONS AD,
GL_CODE_COMBINATIONS FROM_CC,
GL_CODE_COMBINATIONS TO_CC,
FA_LOCATIONS FROM_LOC,
FA_LOCATIONS TO_LOC,
FA_DISTRIBUTION_HISTORY DH,
FA_MASS_TRANSFERS MT
where
&From_Loc_Cond
and
&From_Emp_Cond
and
MT.Mass_Transfer_ID = :P_Mass_Transfer_ID1
and DH.Book_Type_Code= :P_Book1
and DH.Date_Ineffective is null and
DH.Transaction_Header_ID_Out is null
and
AD.Asset_ID = DH.Asset_ID and
AD.asset_category_ID = nvl(MT.category_ID, AD.asset_category_ID)
and
AD.asset_type <> 'GROUP' and
BK.Asset_ID = DH.Asset_ID and
BK.Book_Type_Code =:P_Book1
and BK.Date_Ineffective is null and
BK.Period_Counter_Fully_Retired is null
and
FROM_LOC.Location_ID = DH.Location_ID
and
FROM_CC.Code_Combination_ID = DH.Code_Combination_ID
and
FROM_EMP.PERSON_ID (+) = DH.Assigned_To
and
trunc(sysdate) between FROM_EMP.effective_start_date(+) and FROM_EMP.effective_end_date(+)
and
TO_LOC.Location_ID = nvl (:To_Location_ID, DH.Location_ID)
and
TO_CC.Code_Combination_ID = nvl (:To_GL_CCID, DH.Code_Combination_ID)
and
TO_EMP.PERSON_ID (+) = nvl (:To_Employee_ID, DH.Assigned_To)
and
trunc(sysdate) between TO_EMP.Effective_start_date(+) and TO_EMP.Effective_end_date(+)
AND ((FROM_CC.segment1 BETWEEN MT.Segment1_Low AND MT.Segment1_High)
OR
(MT.Segment1_Low IS NULL AND MT.Segment1_High IS NULL)
OR
(MT.Segment1_Low IS NULL AND FROM_CC.segment1 <= MT.Segment1_High)
OR
(MT.Segment1_High IS NULL AND FROM_CC.segment1 >= MT.Segment1_Low))
AND ((FROM_CC.segment2 BETWEEN MT.Segment2_Low AND MT.Segment2_High)
OR
(MT.Segment2_Low IS NULL AND MT.Segment2_High IS NULL)
OR
(MT.Segment2_Low IS NULL AND FROM_CC.segment2 <= MT.Segment2_High)
OR
(MT.Segment2_High IS NULL AND FROM_CC.segment2 >= MT.Segment2_Low))
AND ((FROM_CC.segment3 BETWEEN MT.Segment3_Low AND MT.Segment3_High)
OR
(MT.Segment3_Low IS NULL AND MT.Segment3_High IS NULL)
OR
(MT.Segment3_Low IS NULL AND FROM_CC.segment3 <= MT.Segment3_High)
OR
(MT.Segment3_High IS NULL AND FROM_CC.segment3 >= MT.Segment3_Low))
AND ((FROM_CC.segment4 BETWEEN MT.Segment4_Low AND MT.Segment4_High)
OR
(MT.Segment4_Low IS NULL AND MT.Segment4_High IS NULL)
OR
(MT.Segment4_Low IS NULL AND FROM_CC.segment4 <= MT.Segment4_High)
OR
(MT.Segment4_High IS NULL AND FROM_CC.segment4 >= MT.Segment4_Low))
AND ((FROM_CC.segment5 BETWEEN MT.Segment5_Low AND MT.Segment5_High)
OR
(MT.Segment5_Low IS NULL AND MT.Segment5_High IS NULL)
OR
(MT.Segment5_Low IS NULL AND FROM_CC.segment5 <= MT.Segment5_High)
OR
(MT.Segment5_High IS NULL AND FROM_CC.segment5 >= MT.Segment5_Low))
AND ((FROM_CC.segment6 BETWEEN MT.Segment6_Low AND MT.Segment6_High)
OR
(MT.Segment6_Low IS NULL AND MT.Segment6_High IS NULL)
OR
(MT.Segment6_Low IS NULL AND FROM_CC.segment6 <= MT.Segment6_High)
OR
(MT.Segment6_High IS NULL AND FROM_CC.segment6 >= MT.Segment6_Low))
AND ((FROM_CC.segment7 BETWEEN MT.Segment7_Low AND MT.Segment7_High)
OR
(MT.Segment7_Low IS NULL AND MT.Segment7_High IS NULL)
OR
(MT.Segment7_Low IS NULL AND FROM_CC.segment7 <= MT.Segment7_High)
OR
(MT.Segment7_High IS NULL AND FROM_CC.segment7 >= MT.Segment7_Low))
AND ((FROM_CC.segment8 BETWEEN MT.Segment8_Low AND MT.Segment8_High)
OR
(MT.Segment8_Low IS NULL AND MT.Segment8_High IS NULL)
OR
(MT.Segment8_Low IS NULL AND FROM_CC.segment8 <= MT.Segment8_High)
OR
(MT.Segment8_High IS NULL AND FROM_CC.segment8 >= MT.Segment8_Low))
AND ((FROM_CC.segment9 BETWEEN MT.Segment9_Low AND MT.Segment9_High)
OR
(MT.Segment9_Low IS NULL AND MT.Segment9_High IS NULL)
OR
(MT.Segment9_Low IS NULL AND FROM_CC.segment9 <= MT.Segment9_High)
OR
(MT.Segment9_High IS NULL AND FROM_CC.segment9 >= MT.Segment9_Low))
AND ((FROM_CC.segment10 BETWEEN MT.Segment10_Low AND MT.Segment10_High)
OR
(MT.Segment10_Low IS NULL AND MT.Segment10_High IS NULL)
OR
(MT.Segment10_Low IS NULL AND FROM_CC.segment10 <= MT.Segment10_High)
OR
(MT.Segment10_High IS NULL AND FROM_CC.segment10 >= MT.Segment10_Low))
AND ((FROM_CC.segment11 BETWEEN MT.Segment11_Low AND MT.Segment11_High)
OR
(MT.Segment11_Low IS NULL AND MT.Segment11_High IS NULL)
OR
(MT.Segment11_Low IS NULL AND FROM_CC.segment11 <= MT.Segment11_High)
OR
(MT.Segment11_High IS NULL AND FROM_CC.segment11 >= MT.Segment11_Low))
AND ((FROM_CC.segment12 BETWEEN MT.Segment12_Low AND MT.Segment12_High)
OR
(MT.Segment12_Low IS NULL AND MT.Segment12_High IS NULL)
OR
(MT.Segment12_Low IS NULL AND FROM_CC.segment12 <= MT.Segment12_High)
OR
(MT.Segment12_High IS NULL AND FROM_CC.segment12 >= MT.Segment12_Low))
AND ((FROM_CC.segment13 BETWEEN MT.Segment13_Low AND MT.Segment13_High)
OR
(MT.Segment13_Low IS NULL AND MT.Segment13_High IS NULL)
OR
(MT.Segment13_Low IS NULL AND FROM_CC.segment13 <= MT.Segment13_High)
OR
(MT.Segment13_High IS NULL AND FROM_CC.segment13 >= MT.Segment13_Low))
AND ((FROM_CC.segment14 BETWEEN MT.Segment14_Low AND MT.Segment14_High)
OR
(MT.Segment14_Low IS NULL AND MT.Segment14_High IS NULL)
OR
(MT.Segment14_Low IS NULL AND FROM_CC.segment14 <= MT.Segment14_High)
OR
(MT.Segment14_High IS NULL AND FROM_CC.segment14 >= MT.Segment14_Low))
AND ((FROM_CC.segment15 BETWEEN MT.Segment15_Low AND MT.Segment15_High)
OR
(MT.Segment15_Low IS NULL AND MT.Segment15_High IS NULL)
OR
(MT.Segment15_Low IS NULL AND FROM_CC.segment15 <= MT.Segment15_High)
OR
(MT.Segment15_High IS NULL AND FROM_CC.segment15 >= MT.Segment15_Low))
AND ((FROM_CC.segment16 BETWEEN MT.Segment16_Low AND MT.Segment16_High)
OR
(MT.Segment16_Low IS NULL AND MT.Segment16_High IS NULL)
OR
(MT.Segment16_Low IS NULL AND FROM_CC.segment16 <= MT.Segment16_High)
OR
(MT.Segment16_High IS NULL AND FROM_CC.segment16 >= MT.Segment16_Low))
AND ((FROM_CC.segment17 BETWEEN MT.Segment17_Low AND MT.Segment17_High)
OR
(MT.Segment17_Low IS NULL AND MT.Segment17_High IS NULL)
OR
(MT.Segment17_Low IS NULL AND FROM_CC.segment17 <= MT.Segment17_High)
OR
(MT.Segment17_High IS NULL AND FROM_CC.segment17 >= MT.Segment17_Low))
AND ((FROM_CC.segment18 BETWEEN MT.Segment18_Low AND MT.Segment18_High)
OR
(MT.Segment18_Low IS NULL AND MT.Segment18_High IS NULL)
OR
(MT.Segment18_Low IS NULL AND FROM_CC.segment18 <= MT.Segment18_High)
OR
(MT.Segment18_High IS NULL AND FROM_CC.segment18 >= MT.Segment18_Low))
AND ((FROM_CC.segment19 BETWEEN MT.Segment19_Low AND MT.Segment19_High)
OR
(MT.Segment19_Low IS NULL AND MT.Segment19_High IS NULL)
OR
(MT.Segment19_Low IS NULL AND FROM_CC.segment19 <= MT.Segment19_High)
OR
(MT.Segment19_High IS NULL AND FROM_CC.segment19 >= MT.Segment19_Low))
AND ((FROM_CC.segment20 BETWEEN MT.Segment20_Low AND MT.Segment20_High)
OR
(MT.Segment20_Low IS NULL AND MT.Segment20_High IS NULL)
OR
(MT.Segment20_Low IS NULL AND FROM_CC.segment20 <= MT.Segment20_High)
OR
(MT.Segment20_High IS NULL AND FROM_CC.segment20 >= MT.Segment20_Low))
AND ((FROM_CC.segment21 BETWEEN MT.Segment21_Low AND MT.Segment21_High)
OR
(MT.Segment21_Low IS NULL AND MT.Segment21_High IS NULL)
OR
(MT.Segment21_Low IS NULL AND FROM_CC.segment21 <= MT.Segment21_High)
OR
(MT.Segment21_High IS NULL AND FROM_CC.segment21 >= MT.Segment21_Low))
AND ((FROM_CC.segment22 BETWEEN MT.Segment22_Low AND MT.Segment22_High)
OR
(MT.Segment22_Low IS NULL AND MT.Segment22_High IS NULL)
OR
(MT.Segment22_Low IS NULL AND FROM_CC.segment22 <= MT.Segment22_High)
OR
(MT.Segment22_High IS NULL AND FROM_CC.segment22 >= MT.Segment22_Low))
AND ((FROM_CC.segment23 BETWEEN MT.Segment23_Low AND MT.Segment23_High)
OR
(MT.Segment23_Low IS NULL AND MT.Segment23_High IS NULL)
OR
(MT.Segment23_Low IS NULL AND FROM_CC.segment23 <= MT.Segment23_High)
OR
(MT.Segment23_High IS NULL AND FROM_CC.segment23 >= MT.Segment23_Low))
AND ((FROM_CC.segment24 BETWEEN MT.Segment24_Low AND MT.Segment24_High)
OR
(MT.Segment24_Low IS NULL AND MT.Segment24_High IS NULL)
OR
(MT.Segment24_Low IS NULL AND FROM_CC.segment24 <= MT.Segment24_High)
OR
(MT.Segment24_High IS NULL AND FROM_CC.segment24 >= MT.Segment24_Low))
AND ((FROM_CC.segment25 BETWEEN MT.Segment25_Low AND MT.Segment25_High)
OR
(MT.Segment25_Low IS NULL AND MT.Segment25_High IS NULL)
OR
(MT.Segment25_Low IS NULL AND FROM_CC.segment25 <= MT.Segment25_High)
OR
(MT.Segment25_High IS NULL AND FROM_CC.segment25 >= MT.Segment25_Low))
AND ((FROM_CC.segment26 BETWEEN MT.Segment26_Low AND MT.Segment26_High)
OR
(MT.Segment26_Low IS NULL AND MT.Segment26_High IS NULL)
OR
(MT.Segment26_Low IS NULL AND FROM_CC.segment26 <= MT.Segment26_High)
OR
(MT.Segment26_High IS NULL AND FROM_CC.segment26 >= MT.Segment26_Low))
AND ((FROM_CC.segment27 BETWEEN MT.Segment27_Low AND MT.Segment27_High)
OR
(MT.Segment27_Low IS NULL AND MT.Segment27_High IS NULL)
OR
(MT.Segment27_Low IS NULL AND FROM_CC.segment27 <= MT.Segment27_High)
OR
(MT.Segment27_High IS NULL AND FROM_CC.segment27 >= MT.Segment27_Low))
AND ((FROM_CC.segment28 BETWEEN MT.Segment28_Low AND MT.Segment28_High)
OR
(MT.Segment28_Low IS NULL AND MT.Segment28_High IS NULL)
OR
(MT.Segment28_Low IS NULL AND FROM_CC.segment28 <= MT.Segment28_High)
OR
(MT.Segment28_High IS NULL AND FROM_CC.segment28 >= MT.Segment28_Low))
AND ((FROM_CC.segment29 BETWEEN MT.Segment29_Low AND MT.Segment29_High)
OR
(MT.Segment29_Low IS NULL AND MT.Segment29_High IS NULL)
OR
(MT.Segment29_Low IS NULL AND FROM_CC.segment29 <= MT.Segment29_High)
OR
(MT.Segment29_High IS NULL AND FROM_CC.segment29 >= MT.Segment29_Low))
AND ((FROM_CC.segment30 BETWEEN MT.Segment30_Low AND MT.Segment30_High)
OR
(MT.Segment30_Low IS NULL AND MT.Segment30_High IS NULL)
OR
(MT.Segment30_Low IS NULL AND FROM_CC.segment30 <= MT.Segment30_High)
OR
(MT.Segment30_High IS NULL AND FROM_CC.segment30 >= MT.Segment30_Low))
and not exists (select 1 from fa_asset_hierarchy ash
where ash.asset_id = ad.asset_id and
ash.parent_hierarchy_id is not null)
order by
AD.Asset_Number,
&FCC_Allsegs,
&FLOC_Allsegs,
FROM_EMP.Employee_Number |
