1 package pay_hk_ir56_magtape AS
2 /* $Header: pyhk56mt.pkh 120.0.12010000.1 2008/07/27 22:48:10 appldev ship $
3 ** Change List
4 ** ===========
5 **
6 ** Date Author Reference Description
7 ** -----------+--------+---------+-------------
8 ** 17 AUG 2001 APUNEKAR N/A Created
9 ** 23 AUG 2002 SHOSKATT 2514400 Sorted by name in ir56b_details cursor
10 ** 27 AUG 2002 SHOSKATT 2514400 Added a Date Track Check for the details
11 ** cursor
12 ** 02 DEC 2002 SRRAJAGO 2689229 Included nocopy option for the 'OUT'
13 ** parameter of the procedure 'range_code'
14 ** 04 DEC 2002 PUCHIL 2690005 Changed the cursor ir56b_details to
15 ** a) Sort the details on last_name and first_name
16 ** b) Eliminate terminated employees.
17 ** 19 DEC 2002 PUCHIL 2715305 Changed the cursor ir56b_details to
18 ** eliminate zero sheet numbers.
19 ** 23 DEC 2002 PUCHIL 2721327 Removed the changes made for Bug 2715305
20 ** 13 JAN 2003 SRRAJAGO 2746829 Modified the cursor ir56b_details to fetch
21 ** last name and first name separately instead of
22 ** the concatenation.Order by clause also modified.
23 ** 23 JAN 2003 CTREDWIN 2746829 Modified ir56b_details to include middle_name
24 ** in the ordering, as well as first name, to bring
25 ** magtape ordering fully into line with the IR56B
26 ** report
27 ** 23 JAN 2003 CTREDWIN - Syntax correction
28 ** 19 APR 2003 NANURADH 2913879 Truncated the value of archived item.
29 ** 29 MAY 2003 KAVERMA 2920731 Replaced tables per_all_assignments_f and per_all_people_f
30 ** by secured views per_assignments_f and per_people_f in ir56b_details
31 */
32
33
34 LEVEL_CNT Number;
35
36
37 /********************************************************
38 * Procedure to fetch RANGE CODE *
39 **********************************************************/
40 PROCEDURE range_code
41 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%TYPE,
42 p_sql out nocopy varchar2);
43
44 /********************************************************
45 * Procedure to fetch ASSIGNMENT ACTION CODE *
46 **********************************************************/
47
48 PROCEDURE assignment_action_code
49 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%TYPE,
50 p_start_person_id in per_all_people_f.person_id%TYPE,
51 p_end_person_id in per_all_people_f.person_id%TYPE,
52 p_chunk in number) ;
53
54 /********************************************************
55 * Cursor to create Header Record
56 **********************************************************/
57 CURSOR ir56b_header is
58 select 'ASSIGNMENT_ACTION_ID=C',
59 assign_action.assignment_action_id,
60 'SUBMISSION_DATE=P',
61 to_char(sysdate,'YYYYMMDD') submission_date,
62 'TOTAL_ASSIGNMENTS=P',
63 count(archive_count.assignment_id) total_count,
64 'TOTAL_INCOME=P',
65 sum(archive_count.amount) total_amount
66 from
67 (
68 select paa.assignment_id,
69 sum(trunc(nvl(fai.value, 0))) amount /* Bug: 2913879 */
70 from ff_archive_items fai,
71 ff_user_entities fue,
72 pay_assignment_actions paa,
73 pay_payroll_actions ppa,
74 pay_payroll_actions ppa2,
75 pay_payroll_actions ppa3
76 where ppa.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
77 and ppa2.payroll_action_id = pay_core_utils.get_parameter('REPORT_ACTION_ID', ppa.legislative_parameters)
78 and fue.user_entity_id = fai.user_entity_id
79 and fue.user_entity_name like 'X_HK_IR56_%_ASG_LE_YTD'
80 and fai.context1 = paa.assignment_action_id
81 and paa.payroll_action_id = ppa3.payroll_action_id
82 and paa.action_status = 'C'
83 and ppa3.payroll_action_id = pay_core_utils.get_parameter('ARCHIVE_ACTION_ID', ppa2.legislative_parameters)
84 and pay_hk_ir56_report.get_archive_value('X_HK_ARCHIVE_MESSAGE', paa.assignment_action_id) is null
85 group by paa.assignment_id
86 ) archive_count,
87 (
88 select paa.assignment_action_id
89 from pay_payroll_actions ppa,
90 pay_payroll_actions ppa2,
91 pay_assignment_actions paa,
92 pay_payroll_actions ppa3--archive action
93 where ppa.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
94 and ppa2.payroll_action_id = pay_core_utils.get_parameter('REPORT_ACTION_ID', ppa.legislative_parameters)
95 and ppa3.payroll_action_id = paa.payroll_action_id
96 and paa.action_status = 'C'
97 and ppa3.payroll_action_id = pay_core_utils.get_parameter('ARCHIVE_ACTION_ID', ppa2.legislative_parameters)
98
99 and pay_hk_ir56_report.get_archive_value('X_HK_ARCHIVE_MESSAGE', paa.assignment_action_id) is null
100 and rownum=1
101 ) assign_action
102 group by
103 assign_action.assignment_action_id;
104
105
106
107 /********************************************************
108 * Cursor to create Detail Record
109 **********************************************************/
110
111 /* Bug No : 2746829 - Modified cursor ir56b_details to fetch pap.last_name and pap.first_name instead of
112 the concatenation pap.last_name || pap.first_name. Order by clause modified to include last_name,
113 first_name */
114
115 /* Sorted by name - Bug 2514400 */
116 cursor ir56b_details is
117 select distinct
118 'ASSIGNMENT_ACTION_ID=C',
119 pac3.assignment_action_id,
120 pap.last_name lname, /*Changed to get proper sorted order for bug 2690005*/
121 pap.first_name||' '||pap.middle_names fname
122 from per_assignments_f paa,
123 pay_payroll_actions ppa, -- Magtape payroll action
124 pay_payroll_actions ppa2, -- Report payroll action
125 pay_payroll_actions ppa3, -- Archive payroll action
126 pay_assignment_actions pac,
127 pay_assignment_actions pac3,
128 per_people_f pap
129 where ppa.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
130 and ppa2.payroll_action_id = pay_core_utils.get_parameter('REPORT_ACTION_ID', ppa.legislative_parameters)
131 and ppa3.payroll_action_id = pay_core_utils.get_parameter('ARCHIVE_ACTION_ID', ppa2.legislative_parameters)
132 and pac.action_status = 'C'
133 and ppa3.payroll_action_id = pac.payroll_action_id
134 and paa.assignment_id = pac.assignment_id
135 and pac3.payroll_action_id = ppa3.payroll_action_id
136 and paa.assignment_id = pac3.assignment_id
137 and pap.person_id = paa.person_id
138 and pay_hk_ir56_report.get_archive_value('X_HK_ARCHIVE_MESSAGE', pac3.assignment_action_id) is null
139 and pap.effective_start_date = (select max(pap1.effective_start_date) from per_people_f pap1
140 where pap1.person_id = pap.person_id and to_date('31-03'||pay_core_utils.get_parameter('REPORTING_YEAR',
141 ppa3.legislative_parameters),'DD-MM-YYYY') between pap1.effective_start_date and pap1.effective_end_date)
142 /* Start for bug 2690005 */
143 /* Added to eliminate terminated employees */
144 and pay_hk_ir56_report.get_archive_value('X_HK_LAST_NAME', pac3.assignment_action_id) is not null
145 /* End for bug 2690005 */
146 order by lname,fname;
147
148 /********************************************************
149 * Cursor to Submit Control listing report.
150 **********************************************************/
151 --
152 -- Pass details to submit report.
153 --
154 cursor cusr_submit_reports is
155 select 'P_ARCHIVE_OR_MAGTAPE=P',
156 'MAGTAPE'
157 from dual;
158
159
160
161 end pay_hk_ir56_magtape;
[Home] [Help]