[Home] [Help]
PACKAGE: APPS.PAY_KR_MAGTAPE_PKG
Source
1 package pay_kr_magtape_pkg AUTHID CURRENT_USER as
2 /* $Header: pykrmgtp.pkh 115.5 2003/05/30 07:38:17 nnaresh ship $ */
3 --
4 level_cnt number;
5 --
6 cursor csr_header(p_payroll_action_id number default to_number(pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID'))) is
7 select
8 'BUSINESS_GROUP_ID=C', to_char(ppa2.business_group_id),
9 'PAYROLL_ID=C', to_char(ppa2.payroll_id),
10 'DATE_EARNED=C', fnd_date.date_to_canonical(ppa2.effective_date),
11 'OVERRIDING_DD_DATE=P', fnd_date.date_to_canonical(ppa2.overriding_dd_date),
12 'START_DATE=P', fnd_date.date_to_canonical(ppa2.start_date),
13 'EFFECTIVE_DATE=P', fnd_date.date_to_canonical(ppa2.effective_date),
14 'ORG_PAYMENT_METHOD_NAME=P', opmtl.org_payment_method_name,
15 'GIRO_COMPANY_NAME=P', opm.pmeth_information1,
16 'GIRO_COMPANY_CODE=P', opm.pmeth_information2,
17 'SOURCE_BANK_CODE=P', pea.segment1,
18 'SOURCE_BANK_NAME=P', hrl.meaning,
19 'SOURCE_BANK_ACCOUNT_TYPE=P', pea.segment2,
20 'SOURCE_BANK_ACCOUNT_NAME=P', pea.segment3,
21 'SOURCE_BANK_ACCOUNT_NUMBER=P', pea.segment4,
22 'ORG_PAY_METHOD_ID=C', to_char(opm.org_payment_method_id),
23 'ORG_PAY_METHOD_ID=P', to_char(opm.org_payment_method_id)
24 from hr_lookups hrl,
25 pay_external_accounts pea,
26 pay_org_payment_methods_f_tl opmtl,
27 pay_org_payment_methods_f opm,
28 pay_payroll_actions ppa2,
29 (
30 select ppp.org_payment_method_id
31 from pay_pre_payments ppp,
32 pay_assignment_actions paa,
33 pay_payroll_actions ppa
34 where ppa.payroll_action_id = p_payroll_action_id
35 and paa.payroll_action_id = ppa.payroll_action_id
36 and ppp.pre_payment_id = paa.pre_payment_id
37 group by ppp.org_payment_method_id
38 ) v
39 where ppa2.payroll_action_id = p_payroll_action_id
40 and opm.org_payment_method_id = v.org_payment_method_id
41 and ppa2.effective_date
42 between opm.effective_start_date and opm.effective_end_date
43 and pea.external_account_id = opm.external_account_id
44 and hrl.lookup_type = 'KR_BANK'
45 and hrl.lookup_code = pea.segment1
46 and opmtl.org_payment_method_id = opm.org_payment_method_id
47 and opmtl.language = userenv('LANG')
48 order by
49 hrl.meaning,
50 opmtl.org_payment_method_name;
51 --
52 cursor csr_data(
53 p_payroll_action_id NUMBER DEFAULT to_number(pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')),
54 p_org_payment_method_id NUMBER DEFAULT to_number(pay_magtape_generic.get_parameter_value('ORG_PAY_METHOD_ID')),
55 p_effective_date DATE DEFAULT fnd_date.canonical_to_date(pay_magtape_generic.get_parameter_value('EFFECTIVE_DATE'))) is
56 select
57 'LAST_NAME=P', pp.last_name,
58 'FIRST_NAME=P', pp.first_name,
59 'NATIONAL_IDENTIFIER=P', nvl(pp.national_identifier, ' '),
60 'EMPLOYEE_NUMBER=P', pp.employee_number,
61 'ASSIGNMENT_NUMBER=P', pa.assignment_number,
62 'BANK_CODE=P', pea.segment1,
63 'BANK_NAME=P', hrl.meaning,
64 'BANK_ACCOUNT_TYPE=P', pea.segment2,
65 'BANK_ACCOUNT_NUMBER=P', pea.segment3,
66 'BANK_ACCOUNT_NAME=P', pea.segment4,
67 'PAYMENT=P', to_char(ppp.value),
68 'ASSIGNMENT_ACTION_ID=C', to_char(paa.assignment_action_id),
69 'ASSIGNMENT_ACTION_ID=P', to_char(paa.assignment_action_id),
70 'ASSIGNMENT_ID=C', to_char(pa.assignment_id),
71 'PER_PAY_METHOD_ID=C', to_char(ppm.personal_payment_method_id),
72 'ORGANIZATION_ID=C', to_char(pa.organization_id),
73 'TAX_UNIT_ID=C', to_char(pa.establishment_id)
74 from hr_lookups hrl,
75 per_people_f pp,
76 per_assignments_f pa,
77 pay_external_accounts pea,
78 pay_personal_payment_methods_f ppm,
79 pay_payroll_actions ppa2, -- PrePayment pact
80 pay_assignment_actions paa2, -- PrePayment assact
81 pay_pre_payments ppp,
82 pay_assignment_actions paa -- Magtape assact
83 where paa.payroll_action_id = p_payroll_action_id
84 and ppp.pre_payment_id = paa.pre_payment_id
85 and ppp.org_payment_method_id + 0 = p_org_payment_method_id
86 and paa2.assignment_action_id = ppp.assignment_action_id
87 and ppa2.payroll_action_id = paa2.payroll_action_id
88 and ppm.personal_payment_method_id = ppp.personal_payment_method_id
89 /* Must use effective_date of PrePayments for PPM */
90 and ppa2.effective_date
91 between ppm.effective_start_date and ppm.effective_end_date
92 and pea.external_account_id = ppm.external_account_id
93 and pa.assignment_id = paa.assignment_id
94 and p_effective_date
95 between pa.effective_start_date and pa.effective_end_date
96 and pp.person_id = pa.person_id
97 and p_effective_date
98 between pp.effective_start_date and pp.effective_end_date
99 /* Do not remove the following NVL function for performance purpose. */
100 and hrl.lookup_type = nvl('KR_BANK', pp.last_name)
101 and hrl.lookup_code = pea.segment1
102 order by
103 pp.last_name,
104 pp.first_name,
105 pp.person_id,
106 ppa2.effective_date,
107 ppa2.action_sequence,
108 hrl.meaning;
109 --
110 end pay_kr_magtape_pkg;