[Home] [Help]
PACKAGE: APPS.PAY_SG_EFT
Source
1 package pay_sg_eft as
2 /* $Header: pysgeft.pkh 120.1.12010000.3 2008/09/12 12:19:39 lnagaraj ship $
3 **
4 ** Copyright (c) 2000 Oracle Corporation
5 ** All Rights Reserved
6 **
7 ** Singapore EFT direct credit of pay (IBG format)
8 **
9 ** Change List
10 ** ===========
11 **
12 ** Date Author Reference Description
13 ** -----------+--------+---------+-------------
14 ** 30 OCT 2000 nrobolas SGD0013 Created
15 ** 28 NOV 2000 nrobolas Added REPLACE function
16 ** 29 NOV 2000 nrobolas The advice cursor now looks up
17 ** the bank id from hr_lookups.
18 ** 29 NOV 2000 nrobolas Streamline the Control record cursor.
19 ** 19 DEC 2000 aalvarez 1530569 added where exists clause in cursor
20 ** c_ibg_control
21 ** 21 NOV 2001 shoskatt 2115345 The order by clause in the advice cursor
22 ** has been changed. Amount has been ordered by
23 ** ascending value
24 ** 04 JAN 2002 shoskatt 2168489 The Control cursor has been changed to retrieve
25 ** account name
26 ** 01 MAR 2002 shoskatt 2240758 Included the Check File Syntax
27 ** 24 APR 2002 Ragovind 2343261 Changed the cursor c_ibg_advice to get full Bank_name.
28 ** 05 JUN 2002 jkarouza 2405428 Changed the cursor c_ibg_advice to modify Account Number
29 ** as required in Bug 2405428
30 ** 30 NOB 2002 jkarouza 2689220 Changed cursor c_ibg_controlk to modify Account Number
31 ** as required in Bug 2405428 for bug 2689220.
32 ** 21 Jan 2003 apunekar 2762569 Fixed for Bank Code 7302
33 ** 07 Feb 2003 nanuradh 2788865 Added date track check for Organizational payment method
34 ** 10 Feb 2003 nanuradh 2793695 cursor c_ibg_advice is modified to fetch first 20
35 ** characters of employee_name
36 ** 11 Mar 2003 apunekar 2843503 Modified cursor c_ibg_advice and c_ibg_control for POSB bank .
37 ** 28 May 2003 nanuradh 2920732 Modified the package to use the secured views instead of base tables.
38 ** 17 DEC 2004 agore 4072941 removed function sequence_number
39 ** Instead referred pay_sg_ibg_s.nextval directly in cursor query
40 ** 14 FEB 2007 snimmala 5749324 Included ppa.payroll_action_id join in the cursor c_ibg_advice
41 ** to resolve performance issue.
42 ** 04 AUG 2008 jalin 7296560 Modified to get TRAN_CODE from parameter
43 */
44
45
46 level_cnt number ;
47
48 /*
49 ** Cursor to retrieve IBG Direct Deposit system control record info
50 */
51
52 cursor c_ibg_control is
53 Select 'RECORD_TYPE=P'
54 , '0'
55 , 'VALUE_DATE=P'
56 , pay_magtape_generic.get_parameter_value('TRANSACTION_DATE')
57 , 'O_BANK_NO=P'
58 , decode(sign((ses.effective_date)
59 - to_date('31/12/2001','dd/mm/yyyy')-1),-1,hrl.meaning,decode(lookup_code,'POSB',7171,hrl.meaning))/*bug2843503*/
60 , 'O_BRANCH_NO=P'
61 , oea.segment6
62 , 'O_ACCOUNT_NO=P'
63 , decode(hrl.meaning, '7117', replace(substr(oea.segment1,4),'-',null),
64 '7232', replace(substr(oea.segment1,4),'-',null),
65 '7339', replace(substr(oea.segment1,4),'-',null),
66 '7357', replace(substr(oea.segment1,3),'-',null),
67 '7302', replace((substr(oea.segment1,1,1) ||
68 substr(oea.segment1,5)),'-',null),/*Bug#2762569*/
69 replace(oea.segment1,'-',null))
70 , 'O_ACCOUNT_NAME=P'
71 , oea.segment2
72 , 'ORG_NAME=P'
73 , nvl(o.name,'NULL VALUE')
74 , 'SEQ_NO=P'
75 , pay_sg_ibg_s.nextval
76 , 'COMPANY_ID=P'
77 , popm.pmeth_information1
78 , 'PAY_METHOD=P'
79 , popm.org_payment_method_name
80 from pay_org_payment_methods_f popm
81 , fnd_sessions ses
82 , pay_external_accounts oea
83 , pay_payroll_actions ppa
84 , hr_organization_units o
85 , hr_lookups hrl
86 where ppa.payroll_action_id =
87 pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
88 and oea.external_account_id = popm.external_account_id
89 and session_id = userenv('sessionid')
90 and ppa.business_group_id = o.organization_id
91 and hrl.lookup_code = oea.segment4
92 and popm.org_payment_method_id = ppa.org_payment_method_id
93 and hrl.lookup_type = 'SG_BANK_CODE'
94 and ppa.effective_date between popm.effective_start_date and popm.effective_end_date
95 and exists (
96 select
97 paa.assignment_action_id
98 from
99 per_assignments_f a
100 , per_people_f p /* Bug# 2920732 */
101 , pay_external_accounts pea
102 , pay_pre_payments ppp
103 , pay_assignment_actions paa
104 , pay_personal_payment_methods_f pppm
105 where
106 ppp.pre_payment_id = paa.pre_payment_id
107 and paa.payroll_action_id = ppa.payroll_action_id
108 and pea.external_account_id = pppm.external_account_id
109 and pppm.personal_payment_method_id = ppp.personal_payment_method_id
110 and paa.assignment_id = a.assignment_id
111 and a.person_id = p.person_id
112 and ppa.effective_date between pppm.effective_start_date and pppm.effective_end_date
113 and ppa.effective_date between a.effective_start_date and a.effective_end_date
114 and ppa.effective_date between p.effective_start_date and p.effective_end_date
115 );
116
117
118 /*
119 ** Cursor to retrieve IBG Direct Deposit System payment info
120 */
121
122 cursor c_ibg_advice is
123 Select 'RECORD_TYPE=P'
124 , '1'
125 , 'R_BANK_NO=P'
126 , decode(sign((ses.effective_date)
127 - to_date('31/12/2001','dd/mm/yyyy')-1),-1,hrl.meaning,decode(lookup_code,'POSB',7171,hrl.meaning))/*Bug2843503*/
128 , 'R_BRANCH_NO=P'
129 , decode(hrl.meaning, '9812', '081',
130 pea.segment6)
131 , 'R_ACCOUNT_NO=P'
132 , decode(hrl.meaning, '7117', replace(substr(pea.segment1,4),'-',null),
133 '7232', replace(substr(pea.segment1,4),'-',null),
134 '7339', replace(substr(pea.segment1,4),'-',null),
135 '7357', replace(substr(pea.segment1,3),'-',null),
136 '7302', replace((substr(pea.segment1,1,1) ||
137 substr(pea.segment1,5)),'-',null),/*Bug#2762569*/
138 replace(pea.segment1,'-',null))
139 , 'R_ACCOUNT_NAME=P'
140 , substr(pea.segment2,1,20)
141 , 'TRAN_CODE=P'
142 , NVL(pay_magtape_generic.get_parameter_value('TRANSACTION_CODE'),'22')
143 , 'PAY_AMOUNT=P'
144 , to_char(NVL(ppp.value,0)*100) a_amount
145 , 'NRIC=P'
146 , nvl(substr(p.national_identifier,1,9),' ')
147 , 'EMPLOYEE_NAME=P'
148 , substr(p.full_name,1,20) /* Bug # 2793695 */
149 , 'EMPLOYEE_NO=P'
150 , NVL(SUBSTR(p.employee_number,1,10), 'NULL VALUE')
151 , 'BANK_NAME=P'
152 , NVL(pea.segment5, 'NULL VALUE') /* Bug#2342361 */
153 from
154 pay_org_payment_methods_f popm
155 , pay_external_accounts oea
156 , pay_personal_payment_methods_f pppm
157 , pay_external_accounts pea
158 , pay_pre_payments ppp
159 , pay_assignment_actions paa
160 , pay_payroll_actions ppa
161 , per_assignments_f a
162 , per_people_f p /* Bug# 2920732 */
163 , hr_organization_units o
164 , hr_lookups hrl
165 , fnd_sessions ses
166 where
167 paa.payroll_action_id =
168 pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
169 and ppa.payroll_action_id =
170 pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID') /* Bug#5749324 */
171 and ses.session_id = userenv('sessionid')
172 and ppp.pre_payment_id = paa.pre_payment_id
173 and paa.payroll_action_id = ppa.payroll_action_id
174 and oea.external_account_id = popm.external_account_id
175 and popm.org_payment_method_id = ppp.org_payment_method_id
176 and pea.external_account_id = pppm.external_account_id
177 and pppm.personal_payment_method_id = ppp.personal_payment_method_id
178 and paa.assignment_id = a.assignment_id
179 and a.person_id = p.person_id
180 and a.business_group_id = o.organization_id
181 and hrl.lookup_code = pea.segment4
182 and hrl.lookup_type = 'SG_BANK_CODE'
183 and o.name = pay_magtape_generic.get_parameter_value('ORG_NAME')
184 and popm.org_payment_method_name = pay_magtape_generic.get_parameter_value('PAY_METHOD')
185 and ppa.effective_date between pppm.effective_start_date and pppm.effective_end_date
186 and ppa.effective_date between a.effective_start_date and a.effective_end_date
187 and ppa.effective_date between p.effective_start_date and p.effective_end_date
188 and ppa.effective_date between popm.effective_start_date and popm.effective_end_date /* Bug : 2788865 */
189 order by decode(sign(1-nvl(ppp.value,0)),1,999999999999,ppp.value) asc, p.full_name asc;
190 End ;