[Home] [Help]
PACKAGE: APPS.PAY_ZA_ACB_TAPE
Source
1 package pay_za_acb_tape AUTHID CURRENT_USER as
2 /* $Header: pyzaacb.pkh 120.3 2006/05/17 05:38:05 rpahune ship $ */
3
4 function get_acb_user_gen_num
5 (
6 p_payroll_action_id in number,
7 p_user_code in varchar2
8 ) return number;
9
10 function get_acb_inst_gen_num
11 (
12 p_payroll_action_id in number,
13 p_acb_user_type in varchar2,
14 p_acb_inst_code in varchar2
15 ) return number;
16
17 -- Declare public variables
18 level_cnt number;
19 user_gen number(30);
20 inst_gen number(30);
21
22 -- ACB Cursors
23 -- ACB Installation Header Cursor
24 cursor acb_inst_header is
25 select 'TRANSFER_PAYROLL_ACTION_ID=P', ppa.payroll_action_id,
26 'TRANSFER_INST_ID_CODE_FROM=P', substr(hoi.org_information1, 1, 80),
27 'TRANSFER_ACB_USER_TYPE=P', substr(hoi.org_information2, 1, 80),
28 'TRANSFER_INST_NAME=P', nvl(substr(hoi.org_information3, 1, 80), substr(hou.name, 1, 80)),
29 'TRANSFER_CREATION_DATE=P', to_char(sysdate, 'YYMMDD'),
30 'TRANSFER_PURGE_DATE=P', to_char(sysdate + 30, 'YYMMDD'),
31 'TRANSFER_START_DATE=P', to_char(ppa.start_date, 'YYMMDD'),
32 'TRANSFER_END_DATE=P', to_char(ppa.effective_date, 'YYMMDD'),
33 'PAYROLL_ACTION_ID=C', ppa.payroll_action_id
34 from pay_payroll_actions ppa,
35 hr_organization_information hoi,
36 hr_organization_units hou
37 where ppa.payroll_action_id = pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
38 and hou.organization_id = ppa.business_group_id
39 and hoi.organization_id = hou.organization_id
40 and hoi.org_information_context = 'ZA_ACB_INFORMATION';
41
42 -- ACB User Header Cursor
43 --
44 cursor acb_user_header is
45 select /*+ ORDERED
46 INDEX (paa PAY_ASSIGNMENT_ACTIONS_N50)
47 INDEX (ppp PAY_PRE_PAYMENTS_PK)
48 INDEX (paa2 PAY_ASSIGNMENT_ACTIONS_PK)
49 INDEX (ppa2 PAY_PAYROLL_ACTIONS_PK)
50 INDEX (ppf PAY_PAYROLLS_F_PK)
51 INDEX (scl HR_SOFT_CODING_KEYFLEX_PK)
52 INDEX (ppa PAY_PAYROLL_ACTIONS_PK)
53 INDEX (ptp PER_TIME_PERIODS_PK)
54 */
55 'TRANSFER_ACB_USER_CODE=P', substr(scl.segment2, 1, 80),
56 'TRANSFER_FIRST_ACTION_DATE=P', to_char(min(nvl(ppa.overriding_dd_date, nvl(ptp.default_dd_date, ptp.end_date))), 'YYYYMMDD'),
57 'TRANSFER_LAST_ACTION_DATE=P', to_char(max(nvl(ppa.overriding_dd_date, nvl(ptp.default_dd_date, ptp.end_date))), 'YYYYMMDD'),
58 'TRANSFER_SERVICE_TYPE=P', min(substr(scl.segment3, 1, 80)),
59 'TRANSFER_USER_REFERENCE=P', min(rpad(upper(scl.segment4),10,' ') || rpad(upper(scl.segment5), 20, ' ')),
60 'TRANSFER_AGGREGATE_LIMIT=P', nvl(min(substr(scl.segment6, 1, 80)), '0'),
61 'TRANSFER_ITEM_LIMIT=P', nvl(min(substr(scl.segment7, 1, 80)), '0')
62 from
63 pay_assignment_actions paa
64 , pay_pre_payments ppp
65 , pay_assignment_actions paa2
66 , pay_payroll_actions ppa2
67 , pay_payrolls_f ppf
68 , hr_soft_coding_keyflex scl
69 , pay_payroll_actions ppa
70 , per_time_periods ptp
71 where
72 paa.payroll_action_id = pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
73 and paa.pre_payment_id = ppp.pre_payment_id
74 and paa2.assignment_action_id =
75 (
76 select max(locked_action_id)
77 from pay_action_interlocks pai
78 where pai.locking_action_id = ppp.assignment_action_id
79 )
80 and paa2.payroll_action_id = ppa2.payroll_action_id
81 and ppa2.payroll_id = ppf.payroll_id
82 and ppa2.effective_date between ppf.effective_start_date and ppf.effective_end_date
83 and ppf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
84 and scl.id_flex_num = (select rule_mode from pay_legislation_rules where legislation_code = 'ZA' and rule_type = 'S')
85 and scl.enabled_flag = 'Y'
86 and paa.payroll_action_id = ppa.payroll_action_id
87 and ppa2.time_period_id = ptp.time_period_id
88 group by substr(scl.segment2, 1, 80)
89 order by substr(scl.segment2, 1, 80);
90
91 -- ACB Contra-Transaction Header Cursor
92 --
93 cursor acb_trans_header is
94 select /*+ INDEX (ppa PAY_PAYROLL_ACTIONS_PK)
95 INDEX (opm PAY_ORG_PAYMENT_METHODS_F_PK)
96 INDEX (pea PAY_EXTERNAL_ACCOUNTS_PK)
97 INDEX (paa PAY_ASSIGNMENT_ACTIONS_N50)
98 INDEX (ppp PAY_PRE_PAYMENTS_PK)
99 INDEX (ppm PAY_PERSONAL_PAYMENT_METHO_PK)
100 INDEX (pea2 PAY_EXTERNAL_ACCOUNTS_PK)
101 INDEX (paa2 PAY_ASSIGNMENT_ACTIONS_PK)
102 INDEX (ppa2 PAY_PAYROLL_ACTIONS_PK)
103 INDEX (ppf PAY_PAYROLLS_F_PK)
104 INDEX (scl HR_SOFT_CODING_KEYFLEX_PK)
105 INDEX (ptp PER_TIME_PERIODS_PK)
106 */
107 distinct 'TRANSFER_ORG_PAY_METHOD=P', ppp.org_payment_method_id,
108 'TRANSFER_USER_BRANCH=P', substr(pea.segment1, 1, 80),
109 'TRANSFER_ENTRY_CLASS=P', decode(pea2.segment2, '4', '64', '61'),
110 'TRANSFER_USER_ACCOUNT_NO=P', substr(pea.segment3, 1, 80),
111 'TRANSFER_USER_ACC_NAME=P', substr(pea.segment4, 1, 80),
112 'TRANSFER_ACTION_DATE=P', to_char(min(nvl(ppa.overriding_dd_date, nvl(ptp.default_dd_date, ptp.end_date))), 'YYYYMMDD'),
113 'TRANSFER_PAYROLL_NAME=P', substr(ppf.payroll_name, 1, 80),
114 'TRANSFER_PAYROLL_ID=P', ppf.payroll_id,
115 'TRANSFER_PAY_METHOD_NAME=P', substr(opm.org_payment_method_name, 1, 80)
116 from pay_payroll_actions ppa,
117 pay_assignment_actions paa,
118 pay_pre_payments ppp,
119 pay_org_payment_methods_f opm,
120 pay_external_accounts pea,
121 pay_personal_payment_methods_f ppm,
122 pay_external_accounts pea2,
123 pay_assignment_actions paa2,
124 pay_payroll_actions ppa2,
125 pay_all_payrolls_f ppf,
126 hr_soft_coding_keyflex scl,
127 per_time_periods ptp
128 where
129 ppa.payroll_action_id = pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
130 and ppa.payroll_action_id = paa.payroll_action_id
131 and paa.pre_payment_id = ppp.pre_payment_id
132 and ppp.org_payment_method_id = opm.org_payment_method_id
133 and ppa.effective_date between opm.effective_start_date
134 and opm.effective_end_date
135 and opm.external_account_id = pea.external_account_id
136 and pea.id_flex_num = (Select rule_mode from pay_legislation_rules WHERE LEGISLATION_CODE = 'ZA' and rule_type = 'E')
137 and pea.enabled_flag = 'Y'
138
139 and ppp.personal_payment_method_id = ppm.personal_payment_method_id
140 and ppa.effective_date between ppm.effective_start_date
141 and ppm.effective_end_date
142 and ppm.external_account_id = pea2.external_account_id
143 and paa2.assignment_action_id =
144 (
145 select max(locked_action_id)
146 from pay_action_interlocks pai
147 where pai.locking_action_id = ppp.assignment_action_id
148 )
149 and paa2.payroll_action_id = ppa2.payroll_action_id
150 and ppa2.time_period_id = ptp.time_period_id
151 and ppa2.effective_date between ppf.effective_start_date
152 and ppf.effective_end_date
153 and ppa2.business_group_id = ppf.business_group_id
154 and ppa2.payroll_id = ppf.payroll_id
155 and ppf.soft_coding_keyflex_id + 0 = scl.soft_coding_keyflex_id
156 and scl.id_flex_num = (SELECT rule_mode FROM pay_legislation_rules WHERE LEGISLATION_CODE = 'ZA' and rule_type = 'S')
157 and scl.enabled_flag = 'Y'
158 and scl.segment2 = pay_magtape_generic.get_parameter_value('TRANSFER_ACB_USER_CODE')
159 group by ppp.org_payment_method_id,
160 pea.segment1,
161 decode(pea2.segment2, '4', '64', '61'),
162 pea.segment3,
163 pea.segment4,
164 ppf.payroll_name,
165 ppf.payroll_id,
166 opm.org_payment_method_name
167 order by decode(pea2.segment2, '4', '64', '61'),
168 substr(ppf.payroll_name, 1, 80),
169 substr(opm.org_payment_method_name, 1, 80),
170 substr(pea.segment4, 1, 80);
171
172 -- ACB Transactions Cursor
173 --
174 cursor acb_transactions is
175 select 'ASSIGNMENT_ACTION_ID=P', paa.assignment_action_id,
176 'TRANSFER_ASSIGN_NO=P', substr(paf.assignment_number, 1, 80),
177 'TRANSFER_HOMING_BRANCH=P', substr(pea.segment1, 1, 80),
178 'TRANSFER_HOMING_ACC_NO=P', substr(pea.segment3, 1, 80),
179 'TRANSFER_ACC_TYPE=P', substr(pea.segment2, 1, 80),
180 'TRANSFER_TRANS_AMOUNT=P', ppp.value * 100,
181 'TRANSFER_HOMING_ACC_NAME=P', nvl(upper(substr(pea.segment4, 1, 80)), upper(substr(ppf.last_name, 1, 80)) || ' ' || substr(upper(ppf.first_name), 1, 1))
182 from per_all_people ppf,
183 pay_external_accounts pea,
184 pay_personal_payment_methods ppm,
185 pay_pre_payments ppp,
186 per_all_assignments paf,
187 pay_assignment_actions paa,
188 pay_legislation_rules plr
189 where paa.payroll_action_id = pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
190 and paa.assignment_id = paf.assignment_id
191 and paf.payroll_id + 0 = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ID')
192 and paf.person_id = ppf.person_id
193 and ppp.org_payment_method_id + 0 = pay_magtape_generic.get_parameter_value('TRANSFER_ORG_PAY_METHOD')
194 and ppp.pre_payment_id = paa.pre_payment_id
195 and ppm.personal_payment_method_id = ppp.personal_payment_method_id
196 and decode(pea.segment2, '4', '64', '61') = pay_magtape_generic.get_parameter_value('TRANSFER_ENTRY_CLASS')
197 and pea.external_account_id = ppm.external_account_id
198 and pea.id_flex_num = plr.rule_mode
199 and plr.LEGISLATION_CODE = 'ZA'
200 and plr.rule_type = 'E'
201 and pea.enabled_flag = 'Y'
202 order by substr(paf.assignment_number, 1, 80),
203 substr(pea.segment4, 1, 80);
204
205 end pay_za_acb_tape;