[Home] [Help]
PACKAGE: APPS.PAY_CN_EFT
Source
1 PACKAGE pay_cn_eft AS
2 /* $Header: pycneft.pkh 120.4 2006/12/13 12:38:56 sukukuma 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_cons_set_param CONSTANT pay_consolidation_sets.consolidation_set_id%TYPE :=
8 pay_magtape_generic.get_parameter_value('CONSOLIDATION_SET_PARAM');
9 g_pay_action_param CONSTANT pay_payroll_actions.payroll_action_id%TYPE :=
10 pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID');
11 g_org_pay_meth_param CONSTANT pay_org_payment_methods_f.org_payment_method_id%TYPE :=
12 pay_magtape_generic.get_parameter_value('ORG_PAYMENT_METHOD_PARAM');
13 g_legal_er_param CONSTANT hr_organization_units.organization_id%TYPE :=
14 pay_magtape_generic.get_parameter_value('LEGAL_EMPLOYER_PARAM');
15
16
17
18 --Cursor to retrieve CCBS Header Info
19 CURSOR c_ccbs_header IS
20 SELECT 'COMPANY_NAME=P'
21 , gre_name /* 3592894, replaced gre_name with action_information18 */
22 ,'NUMBER_OF_DATA_RECORDS=P'
23 , count(*)
24 ,'TOTAL_PAYMENT_AMOUNT=P'
25 , sum(payment_amount)*100
26 ,'PAYMENT_BANK_NAME=P'
27 , org_bank_name
28 ,'PAYMENT_PERIOD=P' /* Bug 3260333, replaced payment date with payment period*/
29 , TO_CHAR(effective_date,'YYYY/MM') /* 3592894, replaced payment date with effective date*/
30 ,'PAYMENT_METHOD=P'
31 , personal_payment_method_name
32 ,'CURRENCY=P'
33 , currency_code
34 FROM ( SELECT hou.name gre_name
35 , ppp.value payment_amount
36 , pea.segment1 org_bank_name
37 , ppa.effective_date effective_date
38 ,opm.org_payment_method_name personal_payment_method_name
39 ,opm.currency_code currency_code
40 FROM pay_pre_payments ppp
41 ,pay_payroll_actions ppa
42 ,pay_assignment_actions paa
43 ,pay_org_payment_methods_f opm
44 ,pay_personal_payment_methods_f ppm
45 ,pay_external_accounts pea
46 ,hr_organization_units hou
47 WHERE ppa.payroll_action_id= g_pay_action_param
48 AND paa.payroll_action_id = ppa.payroll_action_id
49 AND ppp.pre_payment_id = paa.pre_payment_id
50 AND opm.org_payment_method_id = ppp.org_payment_method_id
51 AND ppm.personal_payment_method_id =ppp.personal_payment_method_id
52 AND pea.external_account_id = opm.external_account_id
53 AND hou.organization_id = g_legal_er_param
54 AND ppa.effective_date BETWEEN opm.effective_start_date AND opm.effective_end_date
55 AND ppa.effective_date BETWEEN ppm.effective_start_date AND ppm.effective_end_date
56 )
57 GROUP BY
58 'COMPANY_NAME=P'
59 , gre_name
60 ,'NUMBER_OF_DATA_RECORDS=P'
61 ,'TOTAL_PAYMENT_AMOUNT=P'
62 ,'PAYMENT_BANK_NAME=P'
63 , org_bank_name
64 ,'PAYMENT_PERIOD=P'
65 ,TO_CHAR(effective_date,'YYYY/MM')
66 ,'PAYMENT_METHOD=P'
67 , personal_payment_method_name
68 ,'CURRENCY=P'
69 , currency_code;
70
71 --Cursor to retrieve CCBS data
72 CURSOR c_ccbs_data IS
73 SELECT 'ORGANIZATION_NAME=P'
74 , organization_name
75 ,'EMPLOYEE_NUMBER=P'
76 , employee_number
77 ,'PAYEE_NAME=P'
78 , payee_name
79 ,'EMPLOYEE_ACCOUNT_NUMBER=P'
80 , personal_bank_account_number
81 ,'PAYMENT_AMOUNT=P'
82 , TO_CHAR(payment_amount*100 )
83 FROM ( SELECT hou.name organization_name
84 ,ppf.employee_number employee_number
85 ,ppf.full_name payee_name
86 ,pea.segment3 personal_bank_account_number
87 ,ppp.value payment_amount
88 FROM pay_pre_payments ppp
89 ,pay_payroll_actions ppa
90 ,pay_assignment_actions paa
91 ,pay_org_payment_methods_f opm
92 ,pay_personal_payment_methods_f ppm
93 ,pay_external_accounts pea
94 ,per_assignments_f paf
95 ,hr_organization_units hou
96 ,per_people_f ppf
97 WHERE ppa.payroll_action_id= g_pay_action_param
98 AND paa.payroll_action_id = ppa.payroll_action_id
99 AND ppp.pre_payment_id = paa.pre_payment_id
100 AND opm.org_payment_method_id = ppp.org_payment_method_id
101 AND ppm.personal_payment_method_id =ppp.personal_payment_method_id
102 AND pea.external_account_id = ppm.external_account_id
103 AND paa.assignment_id = paf.assignment_id
104 AND hou.organization_id = paf.organization_id
105 AND ppf.person_id = paf.person_id
106 AND ppa.effective_date BETWEEN opm.effective_start_date AND opm.effective_end_date
107 AND ppa.effective_date BETWEEN ppm.effective_start_date AND ppm.effective_end_date
108 AND ppa.effective_date BETWEEN paf.effective_start_date AND paf.effective_end_date
109 AND ppa.effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date)
110 ORDER BY organization_name,employee_number ASC;
111
112 END pay_cn_eft;