1 PACKAGE pay_jp_zengin_tape AUTHID CURRENT_USER AS
2 /* $Header: pyjptpzn.pkh 120.0 2005/05/29 06:20:49 appldev noship $ */
3 /******************************************************************************
4
5 Copyright (c) Oracle Corporation (UK) Ltd 1993.
6 All Rights Reserved.
7
8 PRODUCT
9 Oracle*Payroll
10
11 NAME
12
13
14 DESCRIPTION
15 Magnetic tape format procedure.
16
17 1.0 Overview
18
19 A PL/SQL package will be written for each type of magnetic tape. The package
20 will include all cursors and procedures required for the particular magnetic
21 tape format. A stored procedure provides the top level of control flow for
22 the magnetic tape file generation. This may call other procedures dependant
23 on the state of the cursors and the input parameters.
24
25 The stored procedure will be called before each execution of a
26 formula. Parameters returned as results of the previous formula execution
27 will be passed to the procedure. The procedure must handle all context
28 cursors needed and may also set parameters required by the formula.
29
30 Using NACHA as an example, for the file header record formula, a call
31 to a cursor which fetches legal_company_id must be performed.
32
33 The interface between the 'C' process and the stored procedure will make
34 extensive use of PL/SQL tables. PL/SQL tables are single column tables which
35 are accessed by an integer index value. Items in the tables will use indexes
36 begining with 1 and increasing contiguously to the number of elements. The
37 index number will be used to match items in the name and value tables.
38
39 The first element in the value tables will always be the number of elements
40 available in the table. The elements in the tables will be of type VARCHAR2
41 any conversion necessary should be performed within the PL/SQL procedure.
42
43 The parameters returned by formula execution will be passed
44 to the stored procedure. Parameters may or may not be altered by the PL/SQL
45 procedure and will be passed back to the formula for the next execution.
46 Context tables will always be reset by the PL/SQL procedure.
47
48 The names of the tables used to interface with the PL/SQL procedure are
49 param_names type IN/OUT
50 param_values type IN/OUT
51 context_names type OUT
52 context_values type OUT
53
54 The second item in the output_parameter_value table will be the formula ID
55 of the next formula to be executed (the first item is the number of values
56 in the table).
57
58 Change List
59 -----------
60 Date Name Description
61 ----------+--------------------+---------------------------------------
62 1996/12/01 Tohru Tagawa Created.
63 1997/01/09 Tohru Tagawa Added group_by expression to the cursor
64 'zengin_ee_payment'
65 1997/07/28 Toru Tagawa Changed name column to use per_information18
66 and per_information19 not last_name,first_name.
67 Added nvl function.
68 1997/07/30 Toru Tagawa Fixed bug that bank_pay_dest_account cursor
69 fails with ORA-00979: not GROUP BY expression.
70 1998/07/30 Toru Tagawa Changed all the cursor for performance tuning.
71 Payment Formulas are also modified.
72 Alter package clause is added in the end to avoid bug
73 caused by Oracle when calling pay_magtape_generic.
74 1999/07/19 Toshihide Nanjyo Add a semicolon to the exit statement.
75 2000/02/14 Toru Tagawa Entity change by Bug.1077383 is applied.
76 2002/06/12 Toru Tagawa All procedures and functions commented out.
77 Package header:
78 ******************************************************************************/
79 --
80 -- Header cursor
81 -- ORG_PAYMENT_METHOD_ID is mandatory in Japan.
82 -- So ORG_PAYMENT_METHOD_ID is unique per PAYROLL_ACTION_ID on Magtape process.
83 CURSOR csr_source_bank IS
84 select 'P_REQUEST_ID=P', to_char(ppa.request_id),
85 'P_START_DATE=P', to_char(ppa.start_date,'DD-MON-YYYY'),
86 'P_EFFECTIVE_DATE=P', to_char(ppa.effective_date,'DD-MON-YYYY'),
87 'P_DEPOSIT_DATE=P', to_char(ppa.overriding_dd_date,'DD-MON-YYYY'),
88 'P_CLIENT_CODE=P', opm.pmeth_information1,
89 'P_CLIENT_NAME_KANA=P', opm.pmeth_information2,
90 'P_SOURCE_BANK_CODE=P', bnk.bank_code,
91 'P_SOURCE_BANK_NAME=P', bnk.bank_name,
92 'P_SOURCE_BANK_NAME_KANA=P', bnk.bank_name_kana,
93 'P_SOURCE_BRANCH_CODE=P', bch.branch_code,
94 'P_SOURCE_BRANCH_NAME=P', bch.branch_name,
95 'P_SOURCE_BRANCH_NAME_KANA=P', bch.branch_name_kana,
96 'P_SOURCE_ACCOUNT_TYPE=P', pea.segment7,
97 'P_SOURCE_ACCOUNT_NUMBER=P', pea.segment8,
98 'P_SOURCE_ACCOUNT_NAME_KANA=P', pea.segment9
99 from pay_jp_bank_branches bch,
100 pay_jp_banks bnk,
101 pay_external_accounts pea,
102 pay_org_payment_methods_f opm,
103 pay_payroll_actions ppa
104 where ppa.payroll_action_id = to_number(pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID'))
105 and opm.org_payment_method_id = ppa.org_payment_method_id
106 and ppa.effective_date
107 between opm.effective_start_date and opm.effective_end_date
108 and pea.external_account_id = opm.external_account_id
109 and bnk.bank_code = pea.segment1
110 and bch.bank_code = bnk.bank_code
111 and bch.branch_code = pea.segment4;
112 --
113 -- Destination Bank cursor
114 --
115 CURSOR csr_dest_bank IS
116 select 'P_DEST_BANK_CODE=P', bnk.bank_code,
117 'P_DEST_BANK_NAME=P', bnk.bank_name,
118 'P_DEST_BANK_NAME_KANA=P', bnk.bank_name_kana
119 from pay_jp_banks bnk,
120 pay_external_accounts pea,
121 pay_personal_payment_methods_f ppm,
122 pay_payroll_actions ppa2, -- Prepay pact
123 pay_assignment_actions paa2, -- Prepay assact
124 pay_pre_payments ppp,
125 pay_assignment_actions paa -- Magtape assact
126 where paa.payroll_action_id = to_number(pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID'))
127 and ppp.pre_payment_id = paa.pre_payment_id
128 and paa2.assignment_action_id = ppp.assignment_action_id
129 and ppa2.payroll_action_id = paa2.payroll_action_id
130 and ppm.personal_payment_method_id = ppp.personal_payment_method_id
131 and ppa2.effective_date
132 between ppm.effective_start_date and ppm.effective_end_date
133 and pea.external_account_id = ppm.external_account_id
134 and bnk.bank_code = pea.segment1
135 group by
136 bnk.bank_code,
137 bnk.bank_name,
138 bnk.bank_name_kana
139 order by 2;
140 --
141 -- Payment Cursor
142 --
143 CURSOR csr_payment IS
144 select 'P_DEST_BRANCH_CODE=P', bch.branch_code,
145 'P_DEST_BRANCH_NAME=P', bch.branch_name,
146 'P_DEST_BRANCH_NAME_KANA=P', bch.branch_name_kana,
147 'P_DEST_ACCOUNT_TYPE=P', pea.segment7,
148 'P_DEST_ACCOUNT_NUMBER=P', pea.segment8,
149 'P_DEST_ACCOUNT_NAME_KANA=P', pea.segment9,
150 'P_EMPLOYEE_NUMBER=P', min(pp.employee_number) EMPLOYEE_NUMBER,
151 'P_FULL_NAME=P', min(rpad(pp.per_information18 || ' ' || pp.per_information19,20,' ')),
152 'P_PAYMENT=P', to_char(sum(ppp.value))
153 from per_all_people_f pp,
154 per_all_assignments_f pa,
155 pay_jp_bank_branches bch,
156 pay_external_accounts pea,
157 pay_personal_payment_methods_f ppm,
158 pay_payroll_actions ppa2, -- Prepay pact
159 pay_assignment_actions paa2, -- Prepay assact
160 pay_pre_payments ppp,
161 pay_assignment_actions paa -- Magtape assact
162 where paa.payroll_action_id = to_number(pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID'))
163 and ppp.pre_payment_id = paa.pre_payment_id
164 and paa2.assignment_action_id = ppp.assignment_action_id
165 and ppa2.payroll_action_id = paa2.payroll_action_id
166 and ppm.personal_payment_method_id = ppp.personal_payment_method_id
167 and ppa2.effective_date
168 between ppm.effective_start_date and ppm.effective_end_date
169 and pea.external_account_id = ppm.external_account_id
170 and pea.segment1 = pay_magtape_generic.get_parameter_value('P_DEST_BANK_CODE')
171 and bch.bank_code = pea.segment1
172 and bch.branch_code = pea.segment4
173 and pa.assignment_id = paa.assignment_id
174 and ppa2.effective_date
175 between pa.effective_start_date and pa.effective_end_date
176 and pp.person_id=pa.person_id
177 and ppa2.effective_date
178 between pp.effective_start_date and pp.effective_end_date
179 group by
180 bch.branch_code,
181 bch.branch_name,
182 bch.branch_name_kana,
183 pea.segment7,
184 pea.segment8,
185 pea.segment9,
186 pa.person_id
187 order by 2,lpad(EMPLOYEE_NUMBER,30,' ');
188 --
189 level_cnt number;
190 --
191 /*
192 PROCEDURE new_formula;
193 --
194 FUNCTION get_process_date(p_assignment_action_id in number,
195 p_entry_date in date)
196 return date;
197 FUNCTION validate_process_date(p_assignment_action_id in number,
198 p_process_date in date)
199 return date;
200 */
201 END pay_jp_zengin_tape;
[Home] [Help]