PACKAGE BODY: APPS.HXC_MPC_UPLOAD_PKG

Source

1 PACKAGE BODY hxc_mpc_upload_pkg AS
2 /* $Header: hxcmpcupl.pkb 115.7 2002/06/10 13:30:25 pkm ship      $ */
3 
4 FUNCTION find_bld_blk_info_type_id ( p_bld_Blk_info_type VARCHAR2 ) RETURN number IS
5 
6 CURSOR	csr_get_bld_blk_info_type_id IS
7 SELECT	bld_blk_info_type_id
8 FROM	hxc_bld_blk_info_types
9 WHERE	bld_blk_info_type	= p_bld_blk_info_type;
10 
11 l_bld_blk_info_type_id hxc_bld_blk_info_types.bld_blk_info_type_id%TYPE;
12 
13 BEGIN
14 
15 OPEN  csr_get_bld_blk_info_type_id;
16 FETCH csr_get_bld_blk_info_type_id INTO l_bld_blk_info_type_id;
17 CLOSE csr_get_bld_blk_info_type_id;
18 
19 RETURN l_bld_blk_info_type_id;
20 
21 END find_bld_blk_info_type_id;
22 
23 
24 PROCEDURE load_mapping_component_row (
25           p_name                IN VARCHAR2
26 	, p_field_name		IN VARCHAR2
27 	, p_bld_blk_info_type	IN VARCHAR2
28 	, p_segment		IN VARCHAR2
29 	, p_owner		IN VARCHAR2
30 	, p_custom_mode		IN VARCHAR2 ) IS
31 
32 l_bld_blk_info_type_id	hxc_bld_blk_info_types.bld_blk_info_type_id%TYPE;
33 l_bld_blk_info_type	hxc_bld_blk_info_types.bld_blk_info_type%TYPE;
34 l_segment		hxc_mapping_components.segment%TYPE;
35 l_field_name		hxc_mapping_components.field_name%TYPE;
36 l_mapping_component_id  hxc_mapping_components.mapping_component_id%TYPE;
37 l_ovn			hxc_mapping_components.object_version_number%TYPE;
38 l_owner			VARCHAR2(6);
39 
40 BEGIN
41 
42 l_bld_blk_info_type_id := find_bld_blk_info_type_id ( p_bld_blk_info_type );
43 
44 BEGIN
45 
46 	SELECT	mapping_component_id
47 	,	object_version_number
48 	,	DECODE( NVL(last_updated_by,-1), 1, 'SEED', 'CUSTOM')
49 	,	field_name
50 	,	bld_blk_info_type
51 	,	segment
52 	INTO	l_mapping_component_id
53 	,	l_ovn
54 	,	l_owner
55 	,	l_field_name
56 	,	l_bld_blk_info_type
57 	,	l_segment
58 	FROM	hxc_mapping_components_v
59 	WHERE	name	= P_NAME;
60 
61 /* ALLOW UPDATE TO FLAG THAT MAPPING COMPONENTS CANNOT BE UPDATED
62   THROUGH THE LOADER IF THEY ARE REFERENCED BY MAPPING */
63 
64 	IF ( p_custom_mode = 'FORCE' OR l_owner = 'SEED' )
65 	THEN
66 
67 		-- only update if the mapping component has actually changed
68 
69 		-- v115.3 - if mpc being used need to use hcdelmpc.sql first
70 
71 		IF ( ( p_segment <> l_segment ) OR ( p_field_name <> l_field_name )
72                   OR ( p_bld_blk_info_type <> l_bld_blk_info_type ) )
73 		THEN
74 
75 		hxc_mpc_upd.upd (
76 				p_mapping_component_id	=> l_mapping_component_id
77   			,	p_object_version_number => l_ovn
78   			,	p_field_name		=> p_field_name
79   			,	p_name			=> p_name
80   			,	p_bld_blk_info_type_id	=> l_bld_blk_info_type_id
81   			,	p_segment		=> p_segment );
82 
83 		END IF;
84 
85 	END IF;
86 
87 EXCEPTION WHEN NO_DATA_FOUND
88 THEN
89 
90 	hxc_mapping_component_api.create_mapping_component (
91    		p_field_name		=> p_field_name
92 	,	p_name			=> p_name
93   	,	p_bld_blk_info_type_id	=> l_bld_blk_info_type_id
94   	,	p_segment		=> p_segment
95   	,	p_mapping_component_id 	=> l_mapping_component_id
96   	,	p_object_version_number	=> l_ovn );
97 
98 END;
99 
100 END load_mapping_component_row;
101 
102 END hxc_mpc_upload_pkg;