1 package PAY_NL_PAYFILE as
2 /* $Header: pynleftp.pkh 120.0.12000000.2 2007/08/02 10:52:35 summohan noship $ */
3
4 level_cnt NUMBER;
5 --
6
7 FUNCTION get_payee_details(p_assignment_id IN NUMBER
8 ,p_business_group_id IN NUMBER
9 ,p_per_pay_method_id IN NUMBER
10 ,p_date_earned IN DATE
11 ,p_payee_address OUT NOCOPY VARCHAR2
12 ) RETURN VARCHAR2;
13
14 PROCEDURE cache_formula(p_formula_name IN VARCHAR2
15 ,p_business_group_id IN NUMBER
16 ,p_effective_date IN DATE
17 ,p_formula_id IN OUT NOCOPY NUMBER
18 ,p_formula_exists IN OUT NOCOPY BOOLEAN
19 ,p_formula_cached IN OUT NOCOPY BOOLEAN
20 );
21
22 PROCEDURE run_formula(p_formula_id IN NUMBER
23 ,p_effective_date IN DATE
24 ,p_formula_name IN VARCHAR2
25 ,p_inputs IN ff_exec.inputs_t
26 ,p_outputs IN OUT NOCOPY ff_exec.outputs_t);
27
28 FUNCTION get_transaction_desc (p_assignment_id IN NUMBER
29 ,p_date_earned IN DATE
30 ,p_business_group_id IN NUMBER
31 ,p_transaction_desc IN VARCHAR2
32 ,p_prepayment_id IN VARCHAR2
33 ) RETURN VARCHAR2;
34
35 FUNCTION get_payee_address(p_payee_id IN NUMBER
36 ,p_payee_type IN VARCHAR2
37 ,p_effective_date IN DATE) RETURN VARCHAR2;
38
39
40 /********************************************************
41 * Cursor to fetch header record information *
42 ********************************************************/
43
44 CURSOR CSR_NL_PAYFILE_HEADER IS
45 SELECT 'CREATION_DATE=P'
46 ,to_char(ppa.effective_date, 'DDMMYY')
47 ,'PROCESS_DATE=P'
48 ,to_char(fnd_date.canonical_to_date(pay_nl_general.get_parameter(
49 ppa.legislative_parameters,
50 'PROCESS_DATE')), 'DDMMYY')
51 ,'FILE_ID=P'
52 , pay_nl_general.get_file_id(ppa.effective_date)
53 ,'BATCH_DESCRIPTION=P'
54 ,nvl(pay_nl_general.get_parameter(ppa.legislative_parameters, 'BATCH_DESC'),' ')
55 ,'USER_NAME=P'
56 ,pay_nl_general.get_parameter(ppa.legislative_parameters, 'USER_NAME')
57 ,'DATE_EARNED=C'
58 ,to_char(ppa.effective_date, 'YYYY/MM/DD HH24:MI:SS')
59 ,'ORG_PAY_METHOD_ID=C'
60 ,ppa.org_payment_method_id
61 FROM pay_payroll_actions ppa
62 WHERE ppa.payroll_action_id =
63 pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
64 AND EXISTS (
65 SELECT 1
66 FROM pay_assignment_actions pas
67 ,pay_pre_payments ppp
68 WHERE pas.payroll_action_id = ppa.payroll_action_id
69 AND ppp.pre_payment_id = pas.pre_payment_id
70 AND ppp.value > 0 );
71
72
73
74 /********************************************************
75 * Cursor to fetch batch/payment record information *
76 ********************************************************/
77
78 CURSOR CSR_NL_PAYFILE_BODY IS
79 SELECT 'AMOUNT=P'
80 ,sum(ppp.value*100)
81 ,'FIRST_NAME=P'
82 , substr(min(pef.first_name),1,35)
83 ,'LAST_NAME=P'
84 , substr(min(pef.last_name),1,35)
85 ,'INITIALS=P'
86 ,substr(min(pef.per_information1),1,35)
87 ,'EMP_NO=P'
88 , nvl(min(pef.employee_number),' ')
89 ,'ASG_NO=P'
90 ,decode(min(pay_nl_general.chk_multiple_assignments(ppa.effective_date,paf.person_id))
91 ,'Y',nvl(min(paf.assignment_number),' ')
92 ,'N', ' ')
93 ,'SUPPRESS_PAYEE_RECORD=P'
94 ,min(pay_nl_general.get_parameter(ppa.legislative_parameters,'SUPPRESS_PAYEE_RECORD'))
95 ,'ASSIGNMENT_ID=C'
96 , min(paf.assignment_id)
97 ,'BUSINESS_GROUP_ID=C'
98 , min(paf.business_group_id)
99 ,'PER_PAY_METHOD_ID=C'
100 ,min(ppp.personal_payment_method_id)
101 ,'DATE_EARNED=C'
102 ,to_char(min(ppa.effective_date), 'YYYY/MM/DD HH24:MI:SS')
103 ,'PRE_PAYMENT_ID=P'
104 ,min(ppp.pre_payment_id)
105 FROM per_assignments_f paf
106 ,per_people_f pef
107 ,pay_pre_payments ppp
108 ,pay_assignment_actions paa
109 ,pay_payroll_actions ppa
110 ,pay_personal_payment_methods_f ppmf
111 ,pay_external_accounts pea
112 WHERE paa.payroll_action_id =
113 pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
114 AND paa.pre_payment_id = ppp.pre_payment_id
115 AND paa.payroll_action_id = ppa.payroll_action_id
116 AND PPP.personal_payment_method_id = ppmf.personal_payment_method_id
117 AND paa.assignment_id = paf.assignment_id
118 AND paf.person_id = pef.person_id
119 AND ppp.value > 0
120 AND pea.external_account_id = ppmf.external_account_id
121 AND ppa.effective_date BETWEEN paf.effective_start_date
122 AND paf.effective_end_date
123 AND ppa.effective_date BETWEEN pef.effective_start_date
124 AND pef.effective_end_date
125 AND ppa.effective_date BETWEEN ppmf.effective_start_date
126 AND ppmf.effective_end_date
127
128 /* This is for consolidating the third-party payments having same
129 account number for an employee.This will group the payments
130 by account number and person id if the user wishes to consolidate
131 else it will group by assignment id */
132
133 Group by decode (pay_nl_general.get_parameter (legislative_parameters,
134 'ACCOUNTNO_CONSOLIDATION'),'Y', (lpad(pef.person_id,10,0) ||lpad(pea.segment2,10,0)),
135 (lpad(paf.assignment_id,10,0)||lpad(pea.segment2,10,0)))
136
137 ORDER BY decode(min(pay_nl_general.get_parameter(legislative_parameters,
138 'SORT_ORDER')),
139 'NAME', substr(min(pef.last_name) || ' ' || min(pef.first_name),1,30),
140 'NUMBER', min(pef.employee_number), null);
141 END PAY_NL_PAYFILE;
[Home] [Help]