[Home] [Help]
PACKAGE BODY: APPS.PAY_SA_TRAN_IDENTIFIERS
Source
1 PACKAGE BODY pay_sa_tran_identifiers AS
2 /* $Header: pysatran.pkb 120.2 2010/05/05 05:05:36 bkeshary noship $ */
3
4
5
6 --------------------------------------------------------------------------
7 -- --
8 -- Name : GET_EFT_RECON_DATA --
9 -- Type : FUNCTION --
10 -- Access : Public --
11 -- Description : Function to identify the batch transaction --
12 -- identifiers for reconciliation --
13 -- --
14 -- Parameters : --
15 -- IN : p_effective_date DATE --
16 -- p_identifier_name VARCHAR2 --
17 -- p_payroll_action_id NUMBER --
18 -- p_payment_type_id NUMBER --
19 -- p_org_payment_method_id NUMBER --
20 -- p_personal_payment_method_id NUMBER --
21 -- p_assignment_action_id NUMBER --
22 -- p_pre_payment_id NUMBER --
23 -- p_delimiter_string VARCHAR2 --
24 -- --
25 -- OUT : N/A --
26 -- --
27 -- Change History : --
28 --------------------------------------------------------------------------
29 -- Rev# Date Userid Description --
30 --------------------------------------------------------------------------
31 -- 12.0 17-Jul-2009 bkeshary Initial Version --
32 --------------------------------------------------------------------------
33 FUNCTION get_eft_recon_data
34 (
35 p_effective_date DATE
36 , p_identifier_name VARCHAR2
37 , p_payroll_action_id NUMBER
38 , p_payment_type_id NUMBER
39 , p_org_payment_method_id NUMBER
40 , p_personal_payment_method_id NUMBER
41 , p_assignment_action_id NUMBER
42 , p_pre_payment_id NUMBER
43 , p_delimiter_string VARCHAR2
44 )
45 RETURN VARCHAR2
46 IS
47
48 CURSOR c_get_transaction_date
49 IS
50 select overriding_dd_date
51 from pay_payroll_actions
52 where payroll_action_id = p_payroll_action_id;
53
54 CURSOR c_get_func_name(p_org_payment_method_id NUMBER)
55 IS
56 select PMETH_INFORMATION4
57 from PAY_ORG_PAYMENT_METHODS_F
58 where ORG_PAYMENT_METHOD_ID = p_org_payment_method_id;
59
60
61 l_return_value VARCHAR2(80) := NULL;
62 l_trans_date Date;
63 l_func_name VARCHAR2(80);
64 user_excep EXCEPTION;
65 user_excep1 EXCEPTION;
66
67
68 BEGIN
69
70 OPEN c_get_func_name(p_org_payment_method_id);
71 FETCH c_get_func_name INTO l_func_name;
72 CLOSE c_get_func_name;
73
74
75 IF l_func_name IS NOT NULL
76 THEN
77 EXECUTE IMMEDIATE 'select '||l_func_name||'(:1,:2,:3,:4,:5,:6,:7,:8,:9) from dual'
78 INTO l_return_value
79 USING p_effective_date ,
80 p_identifier_name,
81 p_payroll_action_id,
82 p_payment_type_id,
83 p_org_payment_method_id,
84 p_personal_payment_method_id,
85 p_assignment_action_id,
86 p_pre_payment_id,
87 p_delimiter_string ;
88
89 IF UPPER(p_identifier_name) = 'TRANSACTION_DATE' THEN
90
91 BEGIN
92 l_return_value := to_char(to_date(l_return_value, 'YYYY/MM/DD'), 'YYYY/MM/DD');
93 EXCEPTION
94 WHEN others THEN
95 raise_application_error(-20100,'Transition Date must be in YYYY/MM/DD format.');
96 END;
97
98 END IF;
99
100 ELSE
101 raise user_excep;
102
103 END IF;
104
105
106 IF UPPER(p_identifier_name) = 'TRANSACTION_DATE' AND l_return_value IS NULL
107 THEN
108 OPEN c_get_transaction_date;
109 FETCH c_get_transaction_date INTO l_trans_date;
110 CLOSE c_get_transaction_date;
111
112 l_return_value := to_char(l_trans_date, 'yyyy/mm/dd');
113
114 ELSIF UPPER(p_identifier_name) = 'TRANSACTION_GROUP' AND l_return_value IS NULL
115 THEN
116 l_return_value := p_payroll_action_id;
117
118 ELSIF UPPER(p_identifier_name) = 'CONCATENATED_IDENTIFIERS' AND l_return_value IS NULL
119 THEN
120 raise user_excep1;
121
122 END IF;
123
124 RETURN l_return_value;
125
126 EXCEPTION
127
128 WHEN user_excep THEN
129
130 raise_application_error(-20101, 'Function Name must be entered in the Reconciliation Function segment of Further Payment Method Info DDF.');
131
132 WHEN user_excep1 THEN
133
134 raise_application_error(-20102, 'Ensure that the function returns a value for the identifier CONCATENATED_IDENTIFIERS.');
135
136 WHEN others THEN
137
138 raise;
139
140 END get_eft_recon_data;
141
142 END pay_sa_tran_identifiers;