1 PACKAGE BODY PAY_GET_JOB_SEGMENT_PKG as
2 /* $Header: pygbjseg.pkb 115.2 2003/01/03 11:21:41 nsugavan noship $ */
3 --
4 /*===========================================================================+
5 | Copyright (c) 1993 Oracle Corporation |
6 | Redwood Shores, California, USA |
7 | All rights reserved. |
8 +============================================================================
9 Name
10 PAY_GET_JOB_SEGMENT_PKG
11 Purpose
12 Function to pass on the value of the job segment selected in Tax Details
13 References DFF or blank if no segment is selected. Function is moved here
14 to a seperate package to facilitate call to the function in oracle 8.0
15 as functions defined in the same package cannot be called in R8.0
16 --
17 REM Change List
18 REM -----------
19 REM Name Date Version Bug Text
20 REM ------------- ----------- ------- ------- --------------------------
21 REM nsugavan 12/24/2002 115.0 2657976 Initial Version
22 REM nsugavan 12/24/2002 115.1 2657976 Increased l_proc length
23 REM nsugavan 01/03/2003 115.2 2657976 Modifed Logic to use WNDS
24 and WNDS pragma restrictions
25 ============================================================================*/
26 --
27 --
28 --
29 -- Globals
30 --
31 g_package CONSTANT VARCHAR2(30) := 'PAY_GET_JOB_SEGMENT_PKG';
32 --
33 FUNCTION get_job_segment(p_organization_id in hr_organization_information.organization_id%type
34 ,p_job_definition_id in number
35 ,p_payroll_action_id in number)
36 return varchar2
37 IS
38 --
39 l_column_name varchar2(30);
40 l_segment_count integer;
41 l_enabled_segment_count integer;
42 l_job_name varchar2(60);
43 l_payroll_action_id number;
44 l_tax_ref varchar2(20);
45 l_proc CONSTANT VARCHAR2(60):= g_package||'get_job_segment';
46 --
47 cursor CSR_GET_TAX_REF(pactid NUMBER) IS
48 select
49 substr(pact.legislative_parameters, instr(pact.legislative_parameters,'TAX_REF=')+8, instr(pact.legislative_parameters||' ',' ',instr(pact.legislative_parameters,'TAX_REF=')+8) - instr(pact.legislative_parameters,'TAX_REF=')-8)
50 from pay_payroll_actions pact
51 where pact.payroll_action_id = pactid;
52
53 --
54 cursor csr_col_name(l_tax_ref VARCHAR2) is
55 select upper(job.APPLICATION_COLUMN_NAME)
56 from hr_organization_information tax
57 ,(select seg.APPLICATION_COLUMN_NAME
58 ,bus.organization_id
59 ,seg.segment_name
60 from fnd_id_flex_segments seg
61 ,fnd_id_flex_structures str
62 ,hr_organization_information bus
63 where seg.id_flex_code = 'JOB'
64 and seg.application_id = 800
65 and seg.enabled_flag = 'Y'
66 and seg.display_flag = 'Y'
67 and seg.id_flex_num = bus.org_information6
68 and seg.id_flex_num = str.id_flex_num
69 and seg.id_flex_code = str.id_flex_code
70 and upper(bus.org_information_context) = 'BUSINESS GROUP INFORMATION'
71 and bus.organization_id = p_organization_id ) job
72 where tax.organization_id = job.organization_id
73 and tax.org_information12 = job.segment_name
74 and upper(tax.org_information_context) = 'TAX DETAILS REFERENCES'
75 and tax.org_information1 = l_tax_ref;
76 --
77 --
78 --
79 cursor csr_enabled_segment_count is
80 select count(seg.segment_name)
81 from fnd_id_flex_segments seg
82 ,fnd_id_flex_structures str
83 ,hr_organization_information bus
84 where seg.id_flex_code = 'JOB'
85 and seg.application_id = 800
86 and seg.enabled_flag = 'Y'
87 and seg.display_flag = 'Y'
88 and seg.id_flex_num = bus.org_information6
89 and seg.id_flex_num = str.id_flex_num
90 and seg.id_flex_code = str.id_flex_code
91 and upper(bus.org_information_context) = 'BUSINESS GROUP INFORMATION'
92 and bus.organization_id = p_organization_id;
93 --
94 cursor csr_col_value(l_column_name VARCHAR2) IS
95 select decode(l_column_name ,
96 'SEGMENT1' , pjd.SEGMENT1,
97 'SEGMENT2' , pjd.SEGMENT2,
98 'SEGMENT3' , pjd.SEGMENT3,
99 'SEGMENT4' , pjd.SEGMENT4,
100 'SEGMENT5' , pjd.SEGMENT5,
101 'SEGMENT6' , pjd.SEGMENT6,
102 'SEGMENT7' , pjd.SEGMENT7,
103 'SEGMENT8' , pjd.SEGMENT8,
104 'SEGMENT9' , pjd.SEGMENT9,
105 'SEGMENT10' , pjd.SEGMENT10,
106 'SEGMENT11' , pjd.SEGMENT11,
107 'SEGMENT12' , pjd.SEGMENT12,
108 'SEGMENT13' , pjd.SEGMENT13,
109 'SEGMENT14' , pjd.SEGMENT14,
110 'SEGMENT15' , pjd.SEGMENT15,
111 'SEGMENT16' , pjd.SEGMENT16,
112 'SEGMENT17' , pjd.SEGMENT17,
113 'SEGMENT18' , pjd.SEGMENT18,
114 'SEGMENT19' , pjd.SEGMENT19,
115 'SEGMENT20' , pjd.SEGMENT20,
116 'SEGMENT21' , pjd.SEGMENT21,
117 'SEGMENT22' , pjd.SEGMENT22,
118 'SEGMENT23' , pjd.SEGMENT23,
119 'SEGMENT24' , pjd.SEGMENT24,
120 'SEGMENT25' , pjd.SEGMENT25,
121 'SEGMENT26' , pjd.SEGMENT26,
122 'SEGMENT27' , pjd.SEGMENT27,
123 'SEGMENT28' , pjd.SEGMENT28,
124 'SEGMENT29' , pjd.SEGMENT29,
125 'SEGMENT30' , pjd.SEGMENT30)
126 from
127 per_job_definitions pjd
128 where
129 pjd.job_definition_id = p_job_definition_id;
130 --
131 CURSOR csr_single_seg is
132 select upper(seg.APPLICATION_COLUMN_NAME)
133 from fnd_id_flex_segments seg
134 ,fnd_id_flex_structures str
135 ,hr_organization_information bus
136 where seg.id_flex_code = 'JOB'
137 and seg.application_id = 800
138 and seg.enabled_flag = 'Y'
139 and seg.display_flag = 'Y'
140 and seg.id_flex_num = bus.org_information6
141 and seg.id_flex_num = str.id_flex_num
142 and seg.id_flex_code = str.id_flex_code
143 and upper(bus.org_information_context) = 'BUSINESS GROUP INFORMATION'
144 and bus.organization_id = p_organization_id;
145 --
146 BEGIN
147 --
148 l_payroll_action_id := p_payroll_action_id ;
149 --
150 OPEN csr_get_tax_ref(l_payroll_action_id);
151 FETCH csr_get_tax_ref into l_tax_ref;
152 close csr_get_tax_ref;
153 --
154 OPEN csr_enabled_segment_count;
155 LOOP
156 FETCH csr_enabled_segment_count INTO l_segment_count;
157 EXIT WHEN csr_enabled_segment_count%NOTFOUND;
158 END LOOP;
159 close csr_enabled_segment_count;
160 --
161 if l_segment_count > 1 then
162 --
163
164 OPEN csr_col_name(l_tax_ref);
165 FETCH csr_col_name INTO l_column_name;
166 IF csr_col_name%FOUND then
167 --
168 open csr_col_value(l_column_name);
169 fetch csr_col_value into l_job_name;
170 close csr_col_value;
171 --
172 -- When there are more than one segment enabled display the Segment selected in
173 -- EDI Job Filed Segemnt(Tax Details Ref DFF) for Job name.
174 --
175 return upper(l_job_name);
176 --
177 ELSE
178 -- When there are more than one segment enabled and nothing selected in
179 -- EDI Job Filed Segemnt(Tax Details Ref DFF)
180 -- display just a blank space for Job name
181 --
182 l_job_name := (' ');
183 return l_job_name;
184 END IF;
185 CLOSE csr_col_name;
186 else
187 -- If there is just one segment enabled for Job KFF, display that
188 open csr_single_seg;
189 fetch csr_single_seg into l_column_name;
190 close csr_single_seg;
191 open csr_col_value(l_column_name);
192 fetch csr_col_value into l_job_name;
193 close csr_col_value;
194 return upper(l_job_name);
195 --
196 end if;
197 --
198 EXCEPTION
199 when others then
200 raise;
201 END get_job_segment;
202 --
203 -- EDI MES Bug 2657976
204 --
205 end pay_get_job_segment_pkg;
[Home] [Help]