[Home] [Help]
PACKAGE: APPS.PAY_NZ_EFT
Source
1 package pay_nz_eft as
2 /* $Header: pynzeft.pkh 120.0.12010000.3 2008/10/23 09:09:21 pmatamsr ship $
3 **
4 ** Copyright (c) 1999 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 ** 1 NOV 1999 ATOPOL N/A Big bang
15 ** 9 AUG 2001 APUNEKAR 1876803 New Cursor Added.
16 ** 26 SEP 2001 APUNEKAR 1998102 Validated delimiters such as commas.
17 ** 30 May 2003 PUCHIL 2920728 Changed both cursors to use secured views.
18 ** Removed check_sql errors.
19 ** 24 Jun 2003 PUCHIL 3719858 Changed both cursors to improve
20 ** performance.
21 ** 07 Oct 2008 PMATAMSR 6891410 As part of NewZealand Direct Credit Enhancement
22 ** /AVENKATK procedure add_custom_xml is added to the
23 ** package.This procedure will be called by the
24 ** XML generation process and adds required XML tags
25 ** in XML generated for each assignment.
26 */
27
28 level_cnt number ;
29
30
31 /*
32 ** Cursor to retrieve ASB Bank Fastnet Office Direct Credit CSV Import File
33 ** header records
34 */
35 /*
36 ** Bug 2920728 - Replaced per_all_people_f with per_people_f
37 */
38
39 cursor c_asb_csv_header is
40 Select
41 'RECORD_TYPE=P'
42 ,'12'
43 ,'REGISTRATION_NUMBER=P'
44 ,oea.segment1
45 ||oea.segment2
46 ||oea.segment3 -- account_no
47 ,'CLIENT_SHORT_NAME=P' -- BG Name
48 ,nvl(replace(o.name,','),'NULL VALUE')
49 ,'DUE_DATE=P'
50 ,to_char(to_date(pay_magtape_generic.get_parameter_value('TRANSACTION_DATE'),'YYYY/MM/DD'),'ddmmyy')
51 ,'HASH_TOTAL=P'
52 ,to_char(SUM(pea.segment2) + SUM(substr(pea.segment1,3,4)))
53 ,'BATCH_COUNT=P'
54 ,to_char(COUNT(*))
55 ,'BATCH_TOTAL=P'
56 ,substr(to_char(SUM(nvl(ppp.VALUE,0) * 100)),1,11)
57 ,'TRANSFER_HEAD_FLAG=P'
58 ,'Not_Printed'
59 from
60 pay_org_payment_methods_f popm
61 , pay_external_accounts oea
62 , pay_personal_payment_methods_f pppm
63 , pay_external_accounts pea
64 , pay_pre_payments ppp
65 , pay_assignment_actions paa
66 , pay_payroll_actions ppa
67 , per_assignments_f a
68 , per_people_f p
69 , hr_organization_units o
70
71 where
72 ppa.payroll_action_id =
73 pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
74 and
75 ppp.pre_payment_id = paa.pre_payment_id
76 and paa.payroll_action_id = ppa.payroll_action_id
77 and ppa.business_group_id = popm.business_group_id --Bug 3719858
78 and oea.external_account_id = popm.external_account_id
79 and ppa.business_group_id = o.organization_id
80 and popm.org_payment_method_id = ppp.org_payment_method_id
81 and pea.external_account_id = pppm.external_account_id
82 and pppm.personal_payment_method_id = ppp.personal_payment_method_id
83 and paa.assignment_id = a.assignment_id
84 and a.person_id = p.person_id
85 and ppa.effective_date between popm.effective_start_date and popm.effective_end_date
86 and ppa.effective_date between pppm.effective_start_date and pppm.effective_end_date
87 and ppa.effective_date between a.effective_start_date and a.effective_end_date
88 and ppa.effective_date between p.effective_start_date and p.effective_end_date
89 group by oea.segment1||oea.segment2||oea.segment3,o.name;
90
91
92 /*
93 ** Cursor to retrieve ASB Bank Fastnet Office Direct Credit CSV Import File
94 ** detail records
95 */
96 /*
97 ** Bug 2920728 - Replaced per_all_people_f with per_people_f
98 */
99
100
101 cursor c_asb_csv_detail is
102 select
103 'ACCOUNT_NO=P'
104 , lpad(substr(pea.segment1,1,2),2,0)||'-'
105 ||lpad(substr(pea.segment1,3,6),4,0)||'-'
106 ||pea.segment2||'-'
107 ||pea.segment3 -- account_no
108 , 'TRANSACTION_CODE=P' --transaction code
109 , '052'
110 , 'AMOUNT=P'
111 , to_char(ppp.value*100) -- amount
112 , 'THIS_PARTY_PARTICULARS=P'
113 , 'Salary/Wages' -- this_party_particulars
114 , 'THIS_PARTY_CODE=P'
115 --
116 -- The NULL value was not recognised by the fast formula using the
117 -- default for ...
118 -- if ... was defaulted ...
119 -- therefore the string NULL VALUE is passed
120 --
121 , nvl(replace(pea.segment5,','), 'NULL VALUE') -- this_party_code
122 , 'THIS_PARTY_ALPHA_REFERENCE=P'
123 , nvl(replace(pea.segment4,','), 'NULL VALUE') -- this_party_alpha_reference
124 , 'THIS_PARTY_NUM_REF=P'
125 , '000000000000' -- this_party_num_reference
126 , 'OTHER_PARTY_PARTICULARS=P'
127 , 'Salary/Wages' -- other_party_particulars
128 , 'OTHER_PARTY_CODE=P'
129 , nvl(replace(oea.segment5,','), 'NULL VALUE') -- other_party_code
130 , 'OTHER_PARTY_ALPHA_REF=P'
131 , nvl(replace(oea.segment4,','), 'NULL VALUE') -- other_party_reference
132 , 'OTHER_PARTY_NAME=P'
133 , nvl(replace(o.name,','),'NULL VALUE') -- other_party_name ie payee_name
134 , 'THIS_PARTY_NAME=P'
135 , substr
136 (replace(p.first_name,',') ||' '||replace(p.last_name,','), 1, 20) -- this_party_name ie payee_name
137 from
138 pay_org_payment_methods_f popm
139 , pay_external_accounts oea
140 , pay_personal_payment_methods_f pppm
141 , pay_external_accounts pea
142 , pay_pre_payments ppp
143 , pay_assignment_actions paa
144 , pay_payroll_actions ppa
145 , per_assignments_f a
146 , per_people_f p
147 , hr_organization_units o
148 where
149 ppa.payroll_action_id =
150 pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
151 and ppp.pre_payment_id = paa.pre_payment_id
152 and paa.payroll_action_id = ppa.payroll_action_id
153 and ppa.business_group_id = popm.business_group_id --Bug 3719858
154 and oea.external_account_id = popm.external_account_id
155 and ppa.business_group_id = o.organization_id
156 and popm.org_payment_method_id = ppp.org_payment_method_id
157 and pea.external_account_id = pppm.external_account_id
158 and pppm.personal_payment_method_id = ppp.personal_payment_method_id
159 and paa.assignment_id = a.assignment_id
160 and a.person_id = p.person_id
161 and ppa.effective_date between popm.effective_start_date and popm.effective_end_date
162 and ppa.effective_date between pppm.effective_start_date and pppm.effective_end_date
163 and ppa.effective_date between a.effective_start_date and a.effective_end_date
164 and ppa.effective_date between p.effective_start_date and p.effective_end_date
165 ;
166
167
168 /* Bug# 6891410 --This Procedure will be called by the XML generation process.This will
169 * add additional required tags in the XML generated for each assignment */
170
171 PROCEDURE add_custom_xml;
172
173 end pay_nz_eft ;