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
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