[Home] [Help]
PACKAGE BODY: APPS.PAY_SS_REP_CATG_METADATA
Source
1 PACKAGE BODY pay_ss_rep_catg_metadata AS
2 /* $Header: pyssrcmd.pkb 120.0.12000000.1 2007/07/09 12:28:34 vbattu noship $ */
3 --
4 --------------------------------------------------------------------------------
5 -- CREATE_PAYSLIP_RECORDS
6 --------------------------------------------------------------------------------
7 PROCEDURE create_payslip_records(p_business_group_id NUMBER ) IS
8 --
9 CURSOR get_template_code IS
10 SELECT DISTINCT org_information4 template_code
11 FROM hr_organization_information
12 WHERE org_information_context = 'HR_SELF_SERVICE_BG_PREFERENCE'
13 AND organization_id = p_business_group_id
14 AND org_information1 = 'PAYSLIP'
15 AND org_information3 = 'Y';
16 --
17 CURSOR get_legislation_code IS
18 SELECT legislation_code
19 FROM per_business_groups
20 WHERE business_group_id = p_business_group_id;
21 --
22 CURSOR get_report_group_id IS
23 SELECT report_group_id
24 FROM pay_report_groups
25 WHERE short_name = 'PAYSLIP_REPORT'
26 AND report_group_name = 'Generic Payslip Report';
27 --
28 CURSOR get_report_category_id(c_leg_code VARCHAR2) IS
29 SELECT report_category_id
30 FROM pay_report_categories
31 WHERE category_name = c_leg_code||' Payslip Report'
32 AND short_name = c_leg_code||'_PAYSLIP_REPORT'
33 AND business_group_id = p_business_group_id;
34 --
35 CURSOR get_report_variables IS
36 SELECT report_variable_id
37 ,report_definition_id
38 FROM pay_report_variables
39 WHERE business_group_id = p_business_group_id;
40 --
41 l_legislation_code per_business_groups.legislation_code%TYPE;
42 l_report_category_id pay_report_categories.report_category_id%TYPE;
43 l_report_group_id pay_report_groups.report_group_id%TYPE;
44 --
45 BEGIN
46 --
47 OPEN get_legislation_code;
48 FETCH get_legislation_code INTO l_legislation_code;
49 CLOSE get_legislation_code;
50 --
51 OPEN get_report_category_id(l_legislation_code);
52 FETCH get_report_category_id INTO l_report_category_id;
53 CLOSE get_report_category_id;
54 --
55 IF l_report_category_id IS NULL THEN
56 --
57 INSERT INTO pay_report_categories
58 ( REPORT_CATEGORY_ID,
59 REPORT_GROUP_ID,
60 CATEGORY_NAME,
61 --LEGISLATION_CODE,
62 SHORT_NAME,
63 BUSINESS_GROUP_ID)
64 SELECT
65 PAY_REPORT_CATEGORIES_S.nextval,
66 report_group_id,
67 l_legislation_code||' Payslip Report',
68 --'DK',
69 l_legislation_code||'_PAYSLIP_REPORT',
70 p_business_group_id
71 FROM pay_report_groups prg
72 WHERE short_name = 'PAYSLIP_REPORT'
73 AND report_group_name = 'Generic Payslip Report'
74 AND NOT EXISTS
75 (SELECT NULL
76 FROM pay_report_categories
77 WHERE report_group_id = prg.report_group_id
78 AND short_name = l_legislation_code||'_PAYSLIP_REPORT'
79 AND category_name = l_legislation_code||' Payslip Report');
80 --
81 --l_report_category_id := PAY_REPORT_CATEGORIES_S.currval;
82 --
83 END IF;
84 --
85 DELETE FROM pay_report_variables
86 WHERE business_group_id = p_business_group_id
87 AND report_definition_id IN ( SELECT report_definition_id
88 from pay_report_definitions
89 where report_name = 'Generic Payslip Report (pdf)' );
90 --
91 DELETE FROM pay_report_category_components
92 WHERE business_group_id = p_business_group_id
93 AND report_category_id IN ( SELECT report_category_id
94 FROM pay_report_categories
95 WHERE short_name = l_legislation_code||'_PAYSLIP_REPORT');
96 --
97 OPEN get_report_group_id;
98 FETCH get_report_group_id INTO l_report_group_id;
99 CLOSE get_report_group_id;
100 --
101 FOR csr_template_code IN get_template_code LOOP
102 --
103 INSERT INTO pay_report_variables
104 ( REPORT_VARIABLE_ID,
105 REPORT_DEFINITION_ID,
106 DEFINITION_TYPE,
107 NAME,
108 VALUE,
109 --LEGISLATION_CODE,
110 BUSINESS_GROUP_ID)
111 SELECT
112 PAY_REPORT_VARIABLES_S.nextval,
113 report_definition_id,
114 'SS',
115 l_legislation_code||' Payslip Template',
116 csr_template_code.template_code,
117 --'DK',
118 p_business_group_id
119 FROM pay_report_definitions prd
120 WHERE report_name = 'Generic Payslip Report (pdf)'
121 AND report_group_id = l_report_group_id
122 AND NOT EXISTS
123 (SELECT NULL
124 FROM pay_report_variables
125 WHERE value = csr_template_code.template_code
126 AND name = l_legislation_code||' Payslip Template'
127 AND report_definition_id = prd.report_definition_id);
128 --
129 END LOOP;
130 --
131 FOR csr_report_varables IN get_report_variables LOOP
132 --
133 INSERT INTO pay_report_category_components
134 (REPORT_CATEGORY_COMP_ID,
135 REPORT_CATEGORY_ID,
136 REPORT_DEFINITION_ID,
137 STYLE_SHEET_VARIABLE_ID,
138 --LEGISLATION_CODE,
139 BUSINESS_GROUP_ID)
140 SELECT
141 PAY_REPORT_CATEGORY_COMP_S.nextval,
142 report_category_id,
143 csr_report_varables.report_definition_id,
144 csr_report_varables.report_variable_id,
145 --'DK',
146 p_business_group_id
147 FROM pay_report_categories prc
148 WHERE short_name = l_legislation_code||'_PAYSLIP_REPORT'
149 AND category_name = l_legislation_code||' Payslip Report'
150 AND report_group_id = l_report_group_id
151 AND NOT EXISTS
152 (SELECT NULL
153 FROM pay_report_category_components
154 WHERE report_category_id = prc.report_category_id
155 AND report_definition_id = csr_report_varables.report_definition_id
156 AND style_sheet_variable_id = csr_report_varables.report_variable_id);
157 --
158 END LOOP;
159 --
160 END create_payslip_records;
161 --------------------------------------------------------------------------------
162 -- create_rep_catg_metadata
163 --------------------------------------------------------------------------------
164 PROCEDURE create_rep_catg_metadata(errbuf OUT NOCOPY VARCHAR2
165 ,retcode OUT NOCOPY VARCHAR2
166 ,p_business_group_id NUMBER
167 ,p_document_type VARCHAR2 ) IS
168 BEGIN
169 --
170 IF p_document_type = 'PAYSLIP' THEN
171 create_payslip_records(p_business_group_id);
172 END IF;
173 --
174 END create_rep_catg_metadata;
175 --
176 END pay_ss_rep_catg_metadata;