[Home] [Help]
PACKAGE: APPS.PAY_AU_EFT
Source
1 package pay_au_eft as
2 /* $Header: pyaueft.pkh 120.0 2005/05/29 01:54:33 appldev noship $
3 **
4 ** Copyright (c) 2000 Oracle Corporation
5 ** All Rights Reserved
6 **
7 ** EFT direct credit of pay stuff
8 **
9 ** Change List
10 ** ===========
11 **
12 ** Date Author Reference Description
13 ** -----------+--------+---------+-------------
14 ** 04 JUL 2000 ABAJPAI N/A Ashlesh Bajpai
15 ** 07 NOV 2000 ABAJPAI N/A Bug No 1485599
16 ** 06 DEC 2000 ABAJPAI N/A Bug No 1523311
17 ** 07 Jun 2001 ATRIPATH Changes made for handling
18 ** debit record entry
19 ** 25 Jun 2001 ATRIPATH Changed detail cursor to filter out
20 ** duplicate records
21 ** 9 Nov 2001 Ragovind 1845869 Changed the Order by clause
22 ** 18 Jun 2002 shoskatt 2421215 For the remitters name, account
23 ** name from the Bank Details Flexfield
24 ** used in the Detail Cursor
25 ** 17 Apr 2003 atripath 2900104 Tuned the c_aba_details cursor
26 ** 29 May 2003 apunekar 2920725 Corrected base tables to support security model
27 ** 25 Jun 2004 srrajago 3603495 Cursor 'c_aba_msg' modified - Performance Fix.
28 ** 06 Jun 2004 srrajago 3603495 Cursor 'c_aba_msg' modified - Performance Fix.
29 */
30
31 level_cnt number ;
32
33 /*
34 ** Cursor to retrieve Westpac Direct Entry system Header info
35 */
36
37 /*Bug2920725 Corrected base tables to support security model*/
38
39 cursor c_aba_header is
40 Select
41 'RECORD_TYPE=P'
42 , '0'
43 , 'ACCOUNT_HOLDER_NAME=P'
44 , oea.segment3
45 , 'BSB_NUMBER=P'
46 , oea.segment1
47 , 'ACCOUNT_NUMBER=P'
48 , oea.segment2
49 , 'TRANSACTION_CODE=P'
50 , popm.pmeth_information8
51 , 'INDICATOR=P'
52 , nvl(popm.pmeth_information7,'NULL')
53 , 'TRANSACTION_DATE=P'
54 , to_char(to_date(pay_magtape_generic.get_parameter_value('TRANSACTION_DATE'),'YYYY/MM/DD'),'ddmmyy')
55 -- Transaction Date
56 , 'BANK_MNEMONIC_CODE=P'
57 , popm.pmeth_information1
58 , 'REEL_SEQUENCE_NUMBER=P'
59 , '01'
60 , 'EFT_USER_NAME=P'
61 , popm.pmeth_information3
62 , 'EFT_USER_ID=P'
63 , popm.pmeth_information2
64 , 'FILE_DESCRIPTION=P'
65 , popm.pmeth_information4
66 , 'INCLUDE_SUMMARY=P'
67 , popm.pmeth_information9
68 , 'DEBIT_ITEM_AUTHORITY=P'
69 , NVL(popm.pmeth_information5,'N')
70 , 'PAYROLL_NAME=P'
71 , NVL(ppf.payroll_name,'Salary/Wages')
72 from
73 pay_org_payment_methods_f popm
74 , pay_external_accounts oea
75 , pay_payroll_actions ppa
76 , pay_payrolls_f ppf
77 where
78 ppa.payroll_action_id =
79 pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
80 and oea.external_account_id = popm.external_account_id
81 and popm.org_payment_method_id = ppa.org_payment_method_id
82 and ppa.effective_date between popm.effective_start_date and popm.effective_end_date
83 and ppa.effective_date between ppf.effective_start_date(+) and ppf.effective_end_date(+)
84 and ppa.payroll_id = ppf.payroll_id(+)
85 and exists (
86 select
87 paa.assignment_action_id
88 from
89 per_assignments_f a
90 , per_people_f p
91 , pay_external_accounts pea
92 , pay_pre_payments ppp
93 , pay_assignment_actions paa
94 , pay_personal_payment_methods_f pppm
95 where
96 ppp.pre_payment_id = paa.pre_payment_id
97 and paa.payroll_action_id = ppa.payroll_action_id
98 and pea.external_account_id = pppm.external_account_id
99 and pppm.personal_payment_method_id = ppp.personal_payment_method_id
100 and paa.assignment_id = a.assignment_id
101 and a.person_id = p.person_id
102 and ppa.effective_date between pppm.effective_start_date and pppm.effective_end_date
103 and ppa.effective_date between a.effective_start_date and a.effective_end_date
104 and ppa.effective_date between p.effective_start_date and p.effective_end_date
105 );
106
107
108 /*
109 ** Cursor to retrieve Westpac Direct Entry system Details info
110 */
111 /* Bug #2421215 - For the remitters name, account name of the Bank Details FF has been used instead of Organization Name */
112 /*Bug2920725 Corrected base tables to support security model*/
113 cursor c_aba_detail is
114 select
115 'RECORD_TYPE=P'
116 , '1'
117 , 'ACCOUNT_HOLDER_NAME=P'
118 , pea.segment3
119 , 'BSB_NUMBER=P'
120 , pea.segment1
121 , 'ACCOUNT_NUMBER=P'
122 , pea.segment2
123 , 'USER_BSB_NUMBER=P'
124 , oea.segment1
125 , 'USER_ACCOUNT_NUMBER=P'
126 , oea.segment2
127 , 'TRANSACTION_CODE=P'
128 , popm.pmeth_information8
129 , 'INDICATOR=P'
130 , decode(popm.pmeth_information7,Null,' ','O',' ',popm.pmeth_information7)
131 , 'AMOUNT=P'
132 , to_char(ppp.value*100) -- amount
133 , 'LODGEMENT_REFERENCE=P'
134 , a.assignment_number
135 , 'REMITTER_NAME=P'
136 , oea.segment3
137 , 'WITHHOLDING_TAX_AMOUNT=P'
138 , '0'
139 , 'ASSIGNMENT_ACTION_ID=P'
140 , paa.ASSIGNMENT_ACTION_ID
141 from
142 pay_org_payment_methods_f popm
143 , pay_external_accounts oea
144 , pay_personal_payment_methods_f pppm
145 , pay_external_accounts pea
146 , pay_pre_payments ppp
147 , pay_assignment_actions paa
148 , pay_payroll_actions ppa
149 , per_assignments_f a /*bug 2900104*/
150 , per_people_f p
151 where ppa.payroll_action_id = pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID') /*bug 2900104*/
152 and paa.payroll_action_id = ppa.payroll_action_id
153 and ppp.pre_payment_id = paa.pre_payment_id
154 and popm.org_payment_method_id = ppp.org_payment_method_id
155 and oea.external_account_id = popm.external_account_id
156 and pppm.personal_payment_method_id = ppp.personal_payment_method_id
157 and pea.external_account_id = pppm.external_account_id
158 and paa.assignment_id = a.assignment_id
159 and pppm.org_payment_method_id = ppp.org_payment_method_id
160 and a.person_id = p.person_id
161 and ppa.effective_date between pppm.effective_start_date and pppm.effective_end_date
162 and ppa.effective_date between a.effective_start_date and a.effective_end_date
163 and ppa.effective_date between p.effective_start_date and p.effective_end_date
164 and ppa.effective_date between popm.effective_start_date and popm.effective_end_date
165 order by decode(pay_magtape_generic.get_parameter_value('SORT_SEQUENCE'),'N',nvl(p.order_name,p.full_name),'B', pea.segment1 )
166 ;
167 /*
168 ** Cursor to retrieve Westpac Direct Entry system control info
169 */
170 /* Bug No: 3603495 - Performance fix in the following cursor. Introduced pay_payroll_actions and its join */
171 cursor c_aba_msg is
172 select
173 'SOURCE_ID=P'
174 , source_id
175 , 'SOURCE_TYPE=P'
176 , source_type
177 , 'LINE_TEXT=P'
178 , line_text
179 , 'TRANSFER_HEAD_FLAG=P'
180 , decode(rownum,1,'Not_Printed','Printed')
181 from
182 pay_message_lines m,
183 pay_assignment_actions a,
184 pay_payroll_actions ppa,
185 (SELECT pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID') payroll_action_id FROM dual WHERE rownum=1) ppas
186 where
187 ppa.payroll_action_id = ppas.payroll_action_id
188 and a.payroll_action_id = ppa.payroll_action_id
189 and
190 a.assignment_action_id = m.source_id
191 and
192 m.source_type = 'A';
193 end ;