[Home] [Help]
PACKAGE BODY: APPS.PAY_ZA_SOTC_PKG
Source
1 PACKAGE BODY PAY_ZA_SOTC_PKG AS
2 /* $Header: PYZASOTC.pkb 120.3 2010/09/08 06:59:42 nchinnam noship $ */
3
4 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
5 l_eff_date_sql varchar2(4000);
6 l_eff_date date;
7 l_cert_count_sql varchar2(4000);
8 l_man_cert_count_sql varchar2(4000);
9 BEGIN
10 IF (P_PAYROLL_ACTION_ID is not null) THEN
11 C_PAYROLL_ACTION_ID := 'and paa.payroll_action_id = '||P_PAYROLL_ACTION_ID;
12 ELSE
13 C_PAYROLL_ACTION_ID :=
14 ' and paa.payroll_action_id in ( '||
15 ' select ppa.payroll_action_id '||
16 'from pay_payroll_actions ppa '||
17 'where ppa.business_group_id = '||P_BUSINESS_GROUP_ID||
18 ' and ppa.action_type = ''X'' '||
19 ' and ppa.report_type = ''ZA_TYE'' '||
20 ' and ppa.action_status = ''C'' '||
21 ' and pay_za_irp5_archive_pkg.get_parameter(''CERT_TYPE'',ppa.legislative_parameters) = 1'||
22 ' and pay_za_irp5_archive_pkg.get_parameter(''LEGAL_ENTITY'',ppa.legislative_parameters) = '||P_LEGAL_ENTITY_ID||
23 ' and pay_za_irp5_archive_pkg.get_parameter(''TAX_YEAR'',ppa.legislative_parameters) = '||P_TAX_YEAR||
24 ' and NVL(pay_za_irp5_archive_pkg.get_parameter(''PERIOD_RECON'',ppa.legislative_parameters), ''02'') = NVL('||P_PERIOD_RECON||',''02'')
25 )';
26 END IF;
27
28 fnd_file.put_line(fnd_file.log,'C_PAYROLL_ACTION_ID:'||C_PAYROLL_ACTION_ID);
29
30 -- Find Effective Date
31 l_eff_date_sql := ' select max(paa.effective_date) '||
32 ' from pay_payroll_actions paa'||
33 ' where 1 = 1 '||
34 C_PAYROLL_ACTION_ID;
35 EXECUTE IMMEDIATE l_eff_date_sql INTO l_eff_date ;
36
37
38 fnd_file.put_line(fnd_file.log,'l_eff_date_sql:'||l_eff_date_sql);
39
40 -- Find Busincess group name
41 select pbg.name
42 into CP_BG_NAME
43 from per_business_groups pbg
44 where pbg.business_group_id = p_business_group_id
45 and l_eff_date between pbg.DATE_FROM and nvl(pbg.DATE_TO,to_date('31-12-4712','DD-MM-YYYY'));
46
47 fnd_file.put_line(fnd_file.log,'CP_BG_NAME:'||CP_BG_NAME);
48
49 -- Find Legal Entity , Tax Ref
50 select haou.name, hoi.org_information3
51 into CP_LE_NAME, CP_TAX_REF
52 from hr_all_organization_units haou,
53 hr_organization_information hoi
54 where haou.organization_id = P_LEGAL_ENTITY_ID
55 and hoi.organization_id = haou.organization_id
56 and hoi.org_information_context = 'ZA_LEGAL_ENTITY';
57
58 fnd_file.put_line(fnd_file.log,'CP_LE_NAME:'||CP_LE_NAME);
59 fnd_file.put_line(fnd_file.log,'CP_TAX_REF:'||CP_TAX_REF);
60
61 -- Tax Year
62 CP_TAX_YEAR := P_TAX_YEAR;
63 CP_PERIOD_RECON := P_PERIOD_RECON; -- 9877034 fix
64
65 -- Payroll Action ID
66 IF (P_PAYROLL_ACTION_ID is not null) THEN
67 CP_PAYROLL_ACTION_ID := P_PAYROLL_ACTION_ID;
68 END IF;
69
70 -- Find First Certificate,Last Certificate and Certificate Count
71 /*l_cert_count_sql := 'select min(pai.action_information1),max(pai.action_information1),count(pai.action_information1)'||
72 ' from pay_action_information pai,'||
73 ' pay_assignment_actions paa'||
74 ' where pai.action_context_id = paa.assignment_action_id'||
75 ' and pai.action_context_type = ''AAP'''||
76 ' and pai.action_information_category = ''ZATYE_EMPLOYEE_INFO'''||
77 C_PAYROLL_ACTION_ID;*/
78
79 -- Fix for Bug#10034135
80 l_cert_count_sql := 'select first_cert,last_cert,cert_cnt from ('||
81 ' select first_value(pai.action_information1) over(order by substr(pai.action_information1,17)) first_cert,'||
82 ' first_value(pai.action_information1) over(order by substr(pai.action_information1,17) DESC nulls last) last_cert,'||
83 ' count(pai.action_information1) over() cert_cnt'||
84 ' from pay_action_information pai,'||
85 ' pay_assignment_actions paa'||
86 ' where pai.action_context_id = paa.assignment_action_id'||
87 ' and pai.action_context_type = ''AAP'''||
88 ' and pai.action_information_category = ''ZATYE_EMPLOYEE_INFO'''||
89 C_PAYROLL_ACTION_ID ||
90 ' ) where rownum < 2';
91
92 fnd_file.put_line(fnd_file.log,'l_cert_count_sql:'||l_cert_count_sql);
93
94 EXECUTE IMMEDIATE l_cert_count_sql INTO CP_FIRST_CERT_NUM,CP_LAST_CERT_NUM,CP_CERT_COUNT;
95
96
97 -- Old/Manual Certificates
98 l_man_cert_count_sql := 'select count(*)'||
99 ' from pay_action_information pai,'||
100 ' pay_assignment_actions paa'||
101 ' where pai.action_context_id = paa.assignment_action_id'||
102 ' and pai.action_context_type = ''AAP'''||
103 ' and pai.action_information_category = ''ZATYE_EMPLOYEE_INFO'''||
104 C_PAYROLL_ACTION_ID||
105 ' and pai.action_information28 is not null ';
106
107 fnd_file.put_line(fnd_file.log,'l_man_cert_count_sql:'||l_man_cert_count_sql);
108
109 EXECUTE IMMEDIATE l_man_cert_count_sql INTO CP_MAN_CERT_COUNT;
110
111
112 RETURN true;
113 END BEFOREREPORT;
114
115
116 END PAY_ZA_SOTC_PKG;