JL Colombian Magnetic Media Literal Configuration Verification - draft

Description
Categories: BI Publisher
Application: Latin America Localizations
Source: Colombian Magnetic Media Literal Configuration Verification Report (XML) - Not Supported: Reserved For Future Use
Short Name: JLCOGLCM_XML
DB package: JL_JLCOGLCM_XMLP_PKG
SELECT   TO_CHAR(l.literal_code) 			literal_code,
                c.reported_value,
                nvl(c.reported_value,'0') NVL_reported_value,
               DECODE(c.reported_value, NULL, l. description, c.description) 	
						description, 
               DECODE(c.reported_value, NULL, l.threshold_value, c.threshold_value) 
						threshold_value,
               DECODE(c.config_id_parent, NULL, ' ', sl.literal_code||'-'||p.reported_value) 
						parent,
               DECODE(c.literal_id, NULL, ' ', f.description) 		movement_type,
               DECODE(r.range_id, NULL, ' ', &C_ACCOUNT_START) 	account_start ,
               DECODE(r.range_id, NULL, ' ', &C_ACCOUNT_END)    	account_end,
               f1.meaning   					status
FROM    jl_co_gl_mg_ranges 				r ,
              jl_co_gl_mg_configs 				c ,
              jl_co_gl_mg_configs 				p,
              fnd_lookups 					f,
              fnd_lookups 					f1,
              jl_co_gl_mg_literals 				sl,
              jl_co_gl_mg_literals 				l           
WHERE  TO_CHAR(l.literal_code) BETWEEN NVL(:p_literal_from, (to_char(l.literal_code)))
                                                        AND NVL(:p_literal_to, (to_char(l.literal_code)))
AND      l.reported_year 	= NVL(:p_report_year, l.reported_year)
AND      l.set_of_books_id 	= :P_SET_OF_BOOKS_ID
AND      l.processed_flag 	= NVL(:P_MAG_MEDIA_STATUS, l.processed_flag)
AND      c.literal_id(+) 		= l.literal_id
AND      f.lookup_type(+)  	= 'JLCO_MG_MOVEMENT_TYPE'
AND      f.lookup_code(+)  	= c.movement_type
AND      f1.lookup_type 	=  'JLCO_MG_LITERAL_PROCESS_FLAG'
AND      f1.lookup_code 	= l.processed_flag
AND      p.config_id(+) 	= c.config_id_parent
AND      sl.literal_id(+) 	= p.literal_id
AND      r.config_id(+)  	= c.config_id
UNION
SELECT  to_char(subl.literal_code) 	literal_code,
               '0'			reported_value,
               '0' NVL_reported_value,
               subl.description 		description, 
               subl.threshold_value  		threshold_value,
               null  			parent,
               null  			movement_type,
               null			account_start ,
               null			account_end,
               f3.meaning			status
FROM  fnd_lookups f3,
            jl_co_gl_mg_literals subl
WHERE  to_char(subl.literal_code) between nvl(:p_literal_from,(to_char(subl.literal_code)))
                                                  AND nvl(:p_literal_to, (to_char(subl.literal_code)))
AND      subl.reported_year = nvl(:p_report_year,subl.reported_year)
AND      subl.set_of_books_id = :P_SET_OF_BOOKS_ID
AND      subl.processed_flag = NVL(:P_MAG_MEDIA_STATUS,subl.processed_flag)
AND      exists (select literal_id 
	   from jl_co_gl_mg_configs  a
                     where a.literal_id = subl.literal_id)  
AND      f3.lookup_type  =  'JLCO_MG_LITERAL_PROCESS_FLAG'
AND      f3.lookup_code  =  subl.processed_flag
ORDER BY 1,2,7
Parameter Name SQL text Validation
LEDGER
 
Number
Magnetic Media Status
 
LOV Oracle
Ending Literal Code
 
LOV Oracle
Starting Literal Code
 
LOV Oracle
Report Year
 
LOV Oracle