FA Mass Transfers Preview - draft
Description
Categories: BI Publisher
Application: Assets
Source: Mass Transfers Preview Report (XML) - Not Supported: Reserved For Future Use
Short Name: FAS811_XML
DB package: FA_FAS811_XMLP_PKG
Source: Mass Transfers Preview Report (XML) - Not Supported: Reserved For Future Use
Short Name: FAS811_XML
DB package: FA_FAS811_XMLP_PKG
Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS
Contact us to schedule a demo or if you need help with the installation
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 |
