[Home] [Help]
PACKAGE: APPS.PAY_HK_EFT
Source
1 package pay_hk_eft AUTHID CURRENT_USER as
2 /* $Header: pyhkeft.pkh 115.9 2003/09/08 07:19:39 avenkatk ship $
3 **
4 ** Copyright (c) 2000 Oracle Corporation
5 ** All Rights Reserved
6 **
7 ** EFT auto pay
8 **
9 ** Change List
10 ** ===========
11 **
12 ** Date Author Reference Description
13 ** -----------+--------+---------+-------------
14 ** 14 FEB 2001 ATRIPATH N/A Amit Tripathi
15 ** 27 AUG 2002 NANURADH 2525527 Changed cursor c_hsbc_hex_data.
16 ** Second_party_identifier is set to AssignmentID instead of HKID/passport no
17 ** 11 Oct 2002 NANURADH 2600691 Added a new column 'National Identifier' in cursor c_hsbc_hex_data
18 ** 14 Nov 2002 NANURADH 2666955 Changed length of Passport Number
19 ** 16 Dec 2002 VGSRINIV 2600691 Modified cursor c_hsbc_hex_data.
20 ** Second_party_identifier is set to employee number.
21 ** Removed the National Identifier column in cursor c_hsbc_hex_data
22 ** 29 May 2003 KAVERMA 2920731 Replaced table per_all_people_f by
23 ** secured view per_people_f
24 ** 08 Sep 2003 AVENKATK 3131759 Modified cursor c_hsbc_hex_data.
25 ** Added date track check for Organizational payment method
26 */
27
28 level_cnt number ;
29
30 /********************************************************
31 * Cursor to fetch header record information *
32 ********************************************************/
33
34 cursor c_hsbc_hex_header is
35 Select
36 'AUTOPLAN_CODE=P'
37 , 'F'
38 , 'ACCOUNT_NUMBER=P'
39 , oea.segment2 ||lpad(substr(oea.SEGMENT3,1,9),9,' ')
40 , 'PAYMENT_CODE=P'
41 , NVL(popm.pmeth_information1,'NULL_VALUE')
42 , 'FIRST_PARTY_REFERENCE=P'
43 , pay_magtape_generic.get_parameter_value('FIRST_PARTY_REFERENCE')
44 , 'VALUE_DATE=P'
45 , to_char(to_date(pay_magtape_generic.get_parameter_value('TRANSACTION_DATE'),'YYYY/MM/DD'),'ddmmyy')
46 , 'INPUT_MEDIUM=P'
47 , 'K'
48 , 'FILE_NAME=P'
49 , '********'
50 , 'RECORDS_IN_BATCH=P'
51 , decode(sign(99999 - COUNT(*)),1, to_char(COUNT(*)),'NULL_VALUE')
52 , 'MONETARY_TOTAL_BATCH=P'
53 , decode(sign(9999999999 - SUM(ppp.VALUE * 100)),1,
54 to_char(SUM(ppp.VALUE * 100)),'NULL_VALUE')
55 , 'OVERFLOW_COUNT=P'
56 , decode(sign(99999 - COUNT(*)),-1, to_char(COUNT(*)),'NULL_VALUE')
57 , 'OVERFLOW_AMOUNT=P'
58 , decode(sign(9999999999 - SUM(ppp.VALUE * 100)),-1,
59 to_char(SUM(ppp.VALUE * 100)),'NULL_VALUE')
60 , 'CENTRE_CODE=P'
61 , '1'
62 , 'TRANSFER_HEAD_FLAG=P'
63 , 'Not_Printed'
64 from
65 pay_org_payment_methods_f popm
66 , pay_external_accounts oea
67 , pay_personal_payment_methods_f pppm
68 , pay_external_accounts pea
69 , pay_pre_payments ppp
70 , pay_assignment_actions paa
71 , pay_payroll_actions ppa
72 , per_assignments_f a
73 , per_people_f p
74 where
75 paa.payroll_action_id =
76 pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
77 and ppp.pre_payment_id = paa.pre_payment_id
78 and paa.payroll_action_id = ppa.payroll_action_id
79 and oea.external_account_id = popm.external_account_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
90 , oea.segment2 ||lpad(substr(oea.SEGMENT3,1,9),9,' ')
91 , popm.pmeth_information1
92 , oea.segment4
93 , to_char(to_date(pay_magtape_generic.get_parameter_value('TRANSACTION_DATE'),'YYYY/MM/DD'),'ddmmyy')
94 , nvl(popm.pmeth_information7,'NULL')
95 , to_char(to_date(pay_magtape_generic.get_parameter_value('TRANSACTION_DATE'),'YYYY/MM/DD'),'ddmmyy')
96 , popm.pmeth_information1
97 , popm.pmeth_information3
98 , popm.pmeth_information2
99 , popm.pmeth_information4
100 , popm.pmeth_information9
101 ;
102
103
104 /********************************************************
105 * Cursor to fetch data record information *
106 ********************************************************/
107
108 cursor c_hsbc_hex_data is
109 select
110 'SECOND_PARTY_IDENTIFIER=P'
111 , p.employee_number -- Bug 2600691
112 , 'BANK_ACCOUNT_NAME=P'
113 , pea.SEGMENT4
114 , 'BANK_NUMBER=P'
115 , pea.segment1
116 , 'BRANCH_NUMBER=P'
117 , pea.segment2
118 , 'ACCOUNT_NUMBER=P'
119 , substr(pea.SEGMENT3,1,9)
120 , 'AMOUNT=P'
121 , LPAD(TO_CHAR(ppp.VALUE * 100),10,'0') -- amount
122 , 'VALUE_DATE=P'
123 , 'NULL_VALUE' -- 4 blank spaces
124 , 'SECOND_PARTY_IDENTIFIER_CONTD=P'
125 , 'NULL_VALUE' -- 6 blank spaces -- Bug 2525527
126 , 'SECOND_PARTY_REFERENCE=P'
127 , 'NULL_VALUE' -- 12 blank spaces
128 , 'TRANSFER_HEAD_FLAG_P='
129 , 'Not_Printed'
130 from
131 pay_org_payment_methods_f popm
132 , pay_external_accounts oea
133 , pay_personal_payment_methods_f pppm
134 , pay_external_accounts pea
135 , pay_pre_payments ppp
136 , pay_assignment_actions paa
137 , pay_payroll_actions ppa
138 , per_assignments_f a
139 , per_people_f p
140 where
141 paa.payroll_action_id =
142 pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
143 and ppp.pre_payment_id = paa.pre_payment_id
144 and paa.payroll_action_id = ppa.payroll_action_id
145 and oea.external_account_id = popm.external_account_id
146 and popm.org_payment_method_id = ppp.org_payment_method_id
147 and pea.external_account_id = pppm.external_account_id
148 and pppm.personal_payment_method_id = ppp.personal_payment_method_id
149 and paa.assignment_id = a.assignment_id
150 and a.person_id = p.person_id
151 and paa.payroll_action_id = ppa.payroll_action_id
152 and ppa.effective_date between pppm.effective_start_date and pppm.effective_end_date
153 and ppa.effective_date between a.effective_start_date and a.effective_end_date
154 and ppa.effective_date between p.effective_start_date and p.effective_end_date
155 and ppa.effective_date between popm.effective_start_date and popm.effective_end_date
156 order by decode(pay_magtape_generic.get_parameter_value('SORT_SEQUENCE'),'N',
157 pea.SEGMENT4,'B', pea.segment2)
158 ;
159
160 end pay_hk_eft;