[Home] [Help]
PACKAGE BODY: APPS.HRFRBSOC
Source
1 PACKAGE BODY hrfrbsoc AS
2 /* $Header: hrfrbsoc.pkb 115.5 2003/05/19 15:12:41 jheer noship $ */
3 --
4 PROCEDURE run_bs (errbuf OUT NOCOPY VARCHAR2
5 ,retcode OUT NOCOPY NUMBER
6 ,p_business_group_id IN NUMBER
7 ,p_template_id IN NUMBER
8 ,p_year IN NUMBER
9 ,p_company_id IN NUMBER DEFAULT NULL
10 ,p_establishment_id IN NUMBER DEFAULT NULL
11 ,p_process_name IN VARCHAR2
12 ,p_debug IN VARCHAR2) IS
13 --
14 l_prmrec hr_summary_util.prmTabType;
15 l_stmt VARCHAR2(32000);
16 l_est_string VARCHAR2(4000):=NULL;
17 l_select_string VARCHAR2(4000):=NULL;
18 l_dummy number;
19 --
20 /* Bug 2217457 Changed to use hr_fr_establishments_v */
21 CURSOR csr_get_est IS
22 SELECT organization_id organization_id
23 FROM hr_fr_establishments_v
24 WHERE company_org_id = to_char(p_company_id)
25 OR organization_id = p_company_id;
26 --
27 BEGIN
28 --
29 IF p_establishment_id IS NOT NULL THEN
30 l_select_string := '(select '||p_establishment_id||' establishment_id from dual) v';
31 l_est_string := '('||p_establishment_id||')';
32 ELSE
33 /* Bug 2217457 - Changed to use views instead of union select from dual */
34 l_select_string := '(SELECT organization_id establishment_id FROM hr_fr_establishments_v WHERE company_org_id = ';
35 l_select_string := l_select_string || to_char(p_company_id) || ' OR organization_id = ';
36 l_select_string := l_select_string || to_char(p_company_id) || ') v ';
37 FOR l_rec IN csr_get_est LOOP
38 IF csr_get_est%ROWCOUNT=1 THEN
39 l_est_string := '('||l_rec.organization_id;
40 ELSE
41 l_est_string := l_est_string||','||l_rec.organization_id;
42 END IF;
43 END LOOP;
44 IF l_est_string IS NOT NULL THEN
45 l_est_string := l_est_string||')';
46 END IF;
47 END IF;
48 --
49 l_prmrec(1).name := 'P_BUSINESS_GROUP_ID';
50 l_prmrec(1).value := p_business_group_id;
51 --
52 l_prmrec(2).name := 'P_END_OF_YEAR';
53 l_prmrec(2).value := 'to_date('''||p_year||'1231'',''YYYYMMDD'')';
54 --
55 l_prmrec(3).name := 'P_YEAR';
56 l_prmrec(3).value := ''''||p_year||'''';
57 --
58 l_prmrec(4).name := 'P_ESTABLISHMENT_ID';
59 l_prmrec(4).value := p_establishment_id;
60 --
61 l_prmrec(5).name := 'P_ESTABLISHMENT_TABLE';
62 l_prmrec(5).value := l_select_string;
63 --
64 l_prmrec(6).name := 'P_COMPANY_ID';
65 l_prmrec(6).value := p_company_id;
66 --
67 l_prmrec(7).name := 'P_START_OF_YEAR';
68 l_prmrec(7).value := 'to_date('''||p_year||'0101'',''YYYYMMDD'')';
69 --
70 l_prmrec(8).name := 'P_ESTABLISHMENT_LIST';
71 l_prmrec(8).value := NVL(l_est_string,'(NULL)');
72 --
76 ,p_process_name => p_process_name
73 hrsumrep.process_run(p_business_group_id => p_business_group_id
74 ,p_process_type => 'BILAN SOCIAL'
75 ,p_template_id => p_template_id
77 ,p_parameters => l_prmrec
78 ,p_store_data => TRUE
79 ,p_statement => l_stmt
80 ,p_retcode => retcode
81 ,p_debug => p_debug);
82 --
83 EXCEPTION WHEN OTHERS THEN
84 retcode := 2; /*critical error*/
85 errbuf := sqlerrm;
86 END run_bs;
87 --
88 PROCEDURE delete_gsp(errbuf OUT NOCOPY VARCHAR2
89 ,retcode OUT NOCOPY NUMBER
90 ,p_process_run_id IN NUMBER) IS
91 --
92 BEGIN
93 --
94 hrsumrep.delete_process_data(p_process_run_id);
95 --
96 EXCEPTION WHEN OTHERS THEN
97 retcode := 2; /*critical error*/
98 errbuf := sqlerrm;
99 END delete_gsp;
100 --
101 END hrfrbsoc;