PACKAGE BODY: APPS.PQP_COPY_EAT

Source

1 PACKAGE BODY pqp_copy_eat AS
2 /* $Header: pqpcpext.pkb 115.1 2002/03/18 12:20:21 pkm ship        $ */
3 
4 -- Package Variables
5 g_package  	VARCHAR2(33) := '  PQP_COPY_EAT.';
6 
7 -- ---------------------------------------------------------------------------+
8 -- |------------------------< COPY_EXTRACT_ATTRIBUTES >-----------------------|
9 -- ---------------------------------------------------------------------------+
10 PROCEDURE copy_extract_attributes
11 		(p_curr_ext_dfn_id 	IN NUMBER
12 		,p_new_ext_dfn_id	IN NUMBER
13 		,p_ext_prefix 		IN VARCHAR2
14 		,p_business_group_id 	IN NUMBER
15 		) IS
16 
17   CURSOR c_ext_attr IS
18   SELECT *
19   FROM pqp_extract_attributes
20   WHERE ext_dfn_id = p_curr_ext_dfn_id;
21 
22   CURSOR c_get_seq IS
23   SELECT pqp_extract_attributes_s.nextval
24   FROM dual;
25 
26   CURSOR c_new_ext_name IS
27   SELECT name
28   FROM ben_ext_dfn
29   WHERE ext_dfn_id = p_new_ext_dfn_id;
30 
31   CURSOR c_new_udt_name(p_user_table_id IN NUMBER) IS
32   SELECT user_table_name
33   FROM pay_user_tables
34   WHERE user_table_id = p_user_table_id;
35 
36   -- Local Record Variables
37   r_ext_attr		c_ext_attr%ROWTYPE;
38 
39   -- Local Variables
40   l_new_put_id		NUMBER(15);
41   l_new_seq_id		NUMBER(15);
42   l_new_ext_name	ben_ext_dfn.name%TYPE;
43   l_new_udt_name	pay_user_tables.user_table_name%TYPE;
44 
45   l_proc 		VARCHAR2(72) := g_package||'copy_extract_attributes';
46 
47 BEGIN
48 
49   hr_utility.set_location('Entering:'|| l_proc, 10);
50 
51   IF p_ext_prefix IS NULL THEN
52     fnd_message.set_name('PQP', 'PQP_230541_EXTRACT_PREFIX_NULL');
53     fnd_message.raise_error;
54   END IF;
55 
56   FOR r_ext_attr in c_ext_attr
57   LOOP -- 1 Get Extract Attributes data for p_curr_ext_dfn_id
58 
59     -- Copy User Table
60     l_new_put_id := pqp_copy_udt.copy_user_table
61     				(p_curr_udt_id		=> r_ext_attr.ext_user_table_id
62     				,p_udt_prefix		=> p_ext_prefix
63     				,p_business_group_id	=> p_business_group_id
64     				);
65 
66     -- Get the new extract name
67     OPEN c_new_ext_name;
68     FETCH c_new_ext_name INTO l_new_ext_name;
69     CLOSE c_new_ext_name;
70 
71     -- Get the new UDT name
72     OPEN c_new_udt_name(l_new_put_id);
73     FETCH c_new_udt_name INTO l_new_udt_name;
74     CLOSE c_new_udt_name;
75 
76     -- Get the new sequence number for PQP_ASSIGNMENT_ATTRIBUTES
77     OPEN c_get_seq;
78     FETCH c_get_seq INTO l_new_seq_id;
79     CLOSE c_get_seq;
80 
81     -- Insert into PQP_EXTRACT_ATTRIBUTES
82     INSERT INTO pqp_extract_attributes
83     (extract_attribute_id
84     ,ext_dfn_id
85     ,ext_dfn_name
86     ,ext_dfn_type
87     ,ext_user_table_id
88     ,ext_user_table_name
89     ,created_by
90     ,creation_date
91     ,last_updated_by
92     ,last_update_date
93     ,last_update_login
94     ,object_version_number
95     )
96     VALUES
97     (l_new_seq_id
98     ,p_new_ext_dfn_id
99     ,l_new_ext_name
100     ,r_ext_attr.ext_dfn_type
101     ,l_new_put_id
102     ,l_new_udt_name
103     ,r_ext_attr.created_by
104     ,r_ext_attr.creation_date
105     ,r_ext_attr.last_updated_by
106     ,r_ext_attr.last_update_date
107     ,r_ext_attr.last_update_login
108     ,1
109     );
110 
111   END LOOP; -- 1
112 
113   hr_utility.set_location('Leaving:'|| l_proc, 20);
114 
115 END copy_extract_attributes;
116 
117 END pqp_copy_eat;