[Home] [Help]
PACKAGE: APPS.PAY_IN_EFT
Source
1 PACKAGE pay_in_eft AS
2 /* $Header: pyineft.pkh 120.2 2006/11/24 10:45:52 abhjain noship $ */
3
4 level_cnt NUMBER;
5 g_start_date_param CONSTANT VARCHAR2(20) := pay_magtape_generic.get_parameter_value('START_DATE_PARAM');
6 g_eff_date_param CONSTANT VARCHAR2(20) := pay_magtape_generic.get_parameter_value('EFFECTIVE_DATE_PARAM');
7 g_pay_date_param CONSTANT VARCHAR2(20) := pay_magtape_generic.get_parameter_value('PAYMENT_DATE_PARAM');
8 g_trans_date CONSTANT VARCHAR2(20) := pay_magtape_generic.get_parameter_value('TRANSACTION_DATE');
9
10 g_cons_set_param CONSTANT pay_consolidation_sets.consolidation_set_id%TYPE :=
11 pay_magtape_generic.get_parameter_value('CONSOLIDATION_SET_PARAM');
12 g_pay_action_param CONSTANT pay_payroll_actions.payroll_action_id%TYPE :=
13 pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID');
14 g_org_pay_meth_param CONSTANT pay_org_payment_methods_f.org_payment_method_id%TYPE :=
15 pay_magtape_generic.get_parameter_value('ORG_PAYMENT_METHOD_PARAM');
16 g_reg_er_param CONSTANT hr_organization_units.organization_id%TYPE :=
17 pay_magtape_generic.get_parameter_value('REGISTERED_EMPLOYER_PARAM');
18
19 --Cursor to retrieve Header Info
20 CURSOR c_eft_header IS
21 SELECT 'COMPANY_NAME1=P'
22 , SUBSTR(company_name1, 1, 80)
23 ,'COMPANY_NAME2=P'
24 , SUBSTR(company_name1, 81, 160)
25 ,'NUMBER_OF_DATA_RECORDS=P'
26 , COUNT(*)
27 ,'TOTAL_PAYMENT_AMOUNT=P'
28 , SUM(payment_amt)
29 ,'PAYMENT_BANK_NAME=P'
30 , hr_general.decode_lookup('IN_BANK', org_bank_name)
31 ,'PAYMENT_BANK_BRANCH=P'
32 , hr_general.decode_lookup('IN_BANK_BRANCH', org_bank_branch)
33 ,'PAYMENT_BRANCH_CODE=P'
34 , org_bank_branch
35 ,'PAYMENT_BANK_ACCOUNT_NUMBER=P'
36 , bank_acc_num
37 ,'PAYMENT_DATE=P'
38 , g_pay_date_param
39 , 'SUBMIT_DATE=P'
40 , g_trans_date
41 FROM ( SELECT hoi.org_information4 company_name1
42 , NVL(ppp.value,0) payment_amt
43 , oea.segment3 org_bank_name
44 , oea.segment4 org_bank_branch
45 , oea.segment1 bank_acc_num
46 FROM
47 pay_org_payment_methods_f popm
48 , pay_external_accounts pea
49 , pay_personal_payment_methods_f pppm
50 , pay_external_accounts oea
51 , pay_pre_payments ppp
52 , pay_assignment_actions paa
53 , pay_payroll_actions ppa
54 , per_assignments_f asg
55 , per_people_f per
56 , hr_organization_units org
57 , hr_organization_information hoi
58 , hr_organization_units hou
59 , pay_payment_types ppto
60 WHERE ppa.payroll_action_id = g_pay_action_param
61 AND ppp.pre_payment_id = paa.pre_payment_id
62 AND paa.payroll_action_id = ppa.payroll_action_id
63 AND ppa.business_group_id = popm.business_group_id
64 AND oea.external_account_id = popm.external_account_id
65 AND ppa.business_group_id = org.organization_id
66 AND popm.org_payment_method_id = ppp.org_payment_method_id
67 AND pea.external_account_id = popm.external_account_id
68 AND pppm.personal_payment_method_id = ppp.personal_payment_method_id
69 AND paa.assignment_id = asg.assignment_id
70 AND asg.person_id = per.person_id
71 AND ppa.effective_date BETWEEN popm.effective_start_date AND popm.effective_end_date
72 AND ppa.effective_date BETWEEN pppm.effective_start_date AND pppm.effective_end_date
73 AND ppa.effective_date BETWEEN asg.effective_start_date AND asg.effective_end_date
74 AND ppa.effective_date BETWEEN per.effective_start_date AND per.effective_end_date
75 AND hoi.organization_id = g_reg_er_param
76 AND hoi.org_information_context = 'PER_IN_COMPANY_DF'
77 AND hou.organization_id = hoi.organization_id
78 AND hou.business_group_id = ppa.business_group_id
79 AND ppto.category = 'MT'
80 AND popm.payment_type_id = ppto.payment_type_id
81 AND popm.org_payment_method_id = g_org_pay_meth_param
82 )
83 GROUP BY
84 'COMPANY_NAME=P'
85 , company_name1
86 ,'NUMBER_OF_DATA_RECORDS=P'
87 ,'TOTAL_PAYMENT_AMOUNT=P'
88 ,'PAYMENT_BANK_NAME=P'
89 , org_bank_name
90 ,'PAYMENT_BANK_BRANCH=P'
91 , org_bank_branch
92 ,'PAYMENT_BANK_ACCOUNT_NUMBER=P'
93 , bank_acc_num
94 ,'PAYMENT_DATE=P'
95 , g_pay_date_param;
96
97 --Cursor to retrieve data records
98 CURSOR c_eft_data IS
99 SELECT 'EMPLOYEE_NUMBER=P'
100 , emp_num
101 ,'EMPLOYEE_NAME1=P'
102 , emp_name1
103 ,'EMPLOYEE_NAME2=P'
104 , emp_name2
105 ,'EMPLOYEE_NAME3=P'
106 , emp_name3
107 ,'EMPLOYEE_BANK=P'
108 , emp_bank
109 ,'EMPLOYEE_BRANCH=P'
110 , emp_branch
111 ,'EMPLOYEE_ACCOUNT_NUMBER=P'
112 , emp_acc_num
113 ,'PAYMENT_AMOUNT=P'
114 , payment_amt
115 FROM (SELECT per.employee_number emp_num
116 , SUBSTR(per.full_name,1,80) emp_name1
117 , SUBSTR(per.full_name,81,80) emp_name2
118 , SUBSTR(per.full_name,161,80) emp_name3
119 , hr_general.decode_lookup('IN_BANK',pea.segment3) emp_bank
120 , hr_general.decode_lookup('IN_BANK_BRANCH',pea.segment4) emp_branch
121 , pea.segment1 emp_acc_num
122 , NVL(ppp.value,0) payment_amt
123 FROM pay_org_payment_methods_f popm
124 , pay_external_accounts oea
125 , pay_personal_payment_methods_f pppm
126 , pay_external_accounts pea
127 , pay_pre_payments ppp
128 , pay_assignment_actions paa
129 , pay_payroll_actions ppa
130 , per_assignments_f asg
131 , per_people_f per
132 , hr_organization_units org
133 , pay_payment_types ppto
134 WHERE ppa.payroll_action_id = g_pay_action_param
135 AND ppp.pre_payment_id = paa.pre_payment_id
136 AND paa.payroll_action_id = ppa.payroll_action_id
137 AND ppa.business_group_id = popm.business_group_id
138 AND oea.external_account_id = popm.external_account_id
139 AND ppa.business_group_id = org.organization_id
140 AND popm.org_payment_method_id = ppp.org_payment_method_id
141 AND pea.external_account_id = pppm.external_account_id
142 AND pppm.personal_payment_method_id = ppp.personal_payment_method_id
143 AND paa.assignment_id = asg.assignment_id
144 AND asg.person_id = per.person_id
145 AND ppa.effective_date BETWEEN popm.effective_start_date AND popm.effective_end_date
146 AND ppa.effective_date BETWEEN pppm.effective_start_date AND pppm.effective_end_date
147 AND ppa.effective_date BETWEEN asg.effective_start_date AND asg.effective_end_date
148 AND ppa.effective_date BETWEEN per.effective_start_date AND per.effective_end_date
149 AND ppto.category = 'MT'
150 AND popm.payment_type_id = ppto.payment_type_id
151 AND popm.org_payment_method_id = g_org_pay_meth_param
152 )
153 ORDER BY emp_bank
154 , emp_branch
155 , emp_num ASC;
156
157 END pay_in_eft;