1 package body pyzamcrp as
2 /* $Header: pyzamcrp.pkb 120.2 2005/06/28 00:07:13 kapalani noship $ */
3
4 -----------------------------------------------------------------------------
5 -- NAME
6 -- manual_ct_cheque
7 -- PURPOSE
8 -- Issues Manual Credit Transfer Cheques.
9 -- ARGUMENTS
10 -- p_errmsg - Returned error message.
11 -- p_errcode - Returned error code.
12 -- p_payroll_action_id - The Payroll action to process.
13 -- p_payroll_name - The Payroll name.
14 -- p_branch_code - The Clearing number of the bank to process.
15 -- p_start_cheque - The cheque number of the cheque to produce.
16 -- USES
17 -- NOTES
18 --
19 -----------------------------------------------------------------------------
20 procedure manual_ct_cheque
21 (
22 p_errmsg out nocopy varchar2,
23 p_errcode out nocopy number,
24 p_payroll_action_id number,
25 p_payroll_name varchar2,
26 p_branch_code varchar2,
27 p_start_cheque number
28 ) is
29
30 -- Cursor used to update cheque numbers.
31 cursor cheque_csr is
32 select
33 paf.payroll_id,
34 ppa.effective_date,
35 cdv.branch_code
36 from
37 pay_payroll_actions ppa,
38 pay_assignment_actions paa,
39 pay_za_branch_cdv_details cdv,
40 pay_pre_payments ppp,
41 pay_personal_payment_methods_f ppm,
42 pay_external_accounts pea,
43 per_assignments_f paf,
44 pay_payrolls_f ppf
45 where ppa.payroll_action_id = p_payroll_action_id
46 and ppf.payroll_name = p_payroll_name
47 and cdv.branch_code = p_branch_code
48 and paa.serial_number is null
49 and ppa.payroll_action_id = paa.payroll_action_id
50 and paa.action_status not in ('V', 'E')
51 and paa.pre_payment_id = ppp.pre_payment_id
52 and paa.assignment_id = paf.assignment_id
53 and ppa.effective_date between paf.effective_start_date and paf.effective_end_date
54 and ppa.effective_date between ppm.effective_start_date and ppm.effective_end_date
55 and ppa.effective_date between ppf.effective_start_date and ppf.effective_end_date
56 and ppm.personal_payment_method_id = ppp.personal_payment_method_id
57 and ppm.external_account_id = pea.external_account_id
58 and cdv.branch_code = pea.segment1
59 and paf.payroll_id = ppf.payroll_id
60 order by ppf.payroll_name, ppa.effective_date, cdv.bank_name, cdv.branch_name,
61 cdv.branch_code
62 for update of paa.serial_number;
63
64 l_flag boolean := FALSE;
65 v_cheq cheque_csr%ROWTYPE;
66
67 begin
68
69 hr_utility.trace('Entering pyzamcrp.manual_ct_cheque');
70
71 -- Create the cheque.
72 for v_cheq in cheque_csr loop
73
74 -- Indicate that we did create a cheque.
75 l_flag := TRUE;
76
77 -- Update the assignment action to the current cheque number.
78 update pay_assignment_actions
79 set serial_number = to_char(p_start_cheque)
80 where current of cheque_csr;
81
82 end loop;
83
84 -- Check whether cheque was produced.
85 if not l_flag then
86 -- Raise an error.
87 p_errmsg := 'Cheque already issued or no such clearing number on Credit Transfer.';
88 p_errcode := 2; -- Error
89 return;
90 end if;
91
92 -- Commit the cheque.
93 commit;
94
95 hr_utility.trace('Exiting pyzamcrp.manual_ct_cheque');
96 exception
97 when others then
98 p_errmsg := null;
99 p_errcode := null;
100 end manual_ct_cheque;
101
102 end pyzamcrp;
[Home] [Help]