PACKAGE BODY: APPS.HXC_TIMECARD_ATTRIBUTES_PKG

Source

1 package body hxc_timecard_attributes_pkg as
2 /* $Header: hxctcrda.pkb 115.4 2002/06/10 13:31:10 pkm ship    $ */
3 --
4 -- function get_timecard_attribute
5 --
6 --
7 -- description Returns the first found Attribute for the given Detail Building
8 --             block.
9 --
10 -- parameters
11 --        p_timecard_id		  - Detail Building block id of timecard
12 --        p_timecard_ovn          - Detail Building block ovn of the timecard
13 --        p_map                   - Map for the attribute from the mappings
14 --        p_field_name            - Field Name of the attribute from the mappings
15 --
16 -- returns 	Attribute
17 --
18 FUNCTION get_timecard_attribute(
19            p_timecard_id number,
20            p_timecard_ovn number,
21            p_map varchar2,
22            p_field_name varchar2)
23 RETURN varchar2
24 is
25 
26 cursor csr_timecard_attributes(p_context varchar2, p_segment varchar2,bb_id number,bb_ovn number)
27 is
28 SELECT
29 decode(hta.attribute_category,
30 p_context, decode(p_segment,
31 'ATTRIBUTE1' , hta.attribute1,
32 'ATTRIBUTE2' , hta.attribute2,
33 'ATTRIBUTE3' , hta.attribute3,
34 'ATTRIBUTE4' , hta.attribute4,
35 'ATTRIBUTE5' , hta.attribute5,
36 'ATTRIBUTE6' , hta.attribute6,
37 'ATTRIBUTE7' , hta.attribute7,
38 'ATTRIBUTE8' , hta.attribute8,
39 'ATTRIBUTE9' , hta.attribute9,
40 'ATTRIBUTE10' , hta.attribute10,
41 'ATTRIBUTE11' , hta.attribute11,
42 'ATTRIBUTE12' , hta.attribute12,
43 'ATTRIBUTE13' , hta.attribute13,
44 'ATTRIBUTE14' , hta.attribute14,
45 'ATTRIBUTE15' , hta.attribute15,
46 'ATTRIBUTE16' , hta.attribute16,
47 'ATTRIBUTE17' , hta.attribute17,
48 'ATTRIBUTE18' , hta.attribute18,
49 'ATTRIBUTE19' , hta.attribute19,
50 'ATTRIBUTE20' , hta.attribute20,
51 'ATTRIBUTE21' , hta.attribute21,
52 'ATTRIBUTE22' , hta.attribute22,
53 'ATTRIBUTE23' , hta.attribute23,
54 'ATTRIBUTE24' , hta.attribute24,
55 'ATTRIBUTE25' , hta.attribute25,
56 'ATTRIBUTE26' , hta.attribute26,
57 'ATTRIBUTE27' , hta.attribute27,
58 'ATTRIBUTE28' , hta.attribute28,
59 'ATTRIBUTE29' , hta.attribute29,
60 'ATTRIBUTE30' , hta.attribute30
61 ),
62 null)
63 FROM hxc_time_building_blocks htb,
64      hxc_time_attribute_usages htau,
65      hxc_time_attributes hta
66 WHERE
67     htb.time_building_block_id = bb_id
68 and htb.object_version_number = bb_ovn
69 and htb.time_building_block_id = htau.time_building_block_id
70 and htb.object_version_number = htau.time_building_block_ovn
71 and htau.time_attribute_id  = hta.time_attribute_id
72 and htb.resource_type = 'PERSON'
73 and htb.date_to = hr_general.end_of_time
74 and htb.scope = 'DETAIL';
75 
76 CURSOR csr_mapping(p_map varchar2, p_field_name varchar2)
77 is
78 SELECT hma.context,hma.segment
79 FROM hxc_mapping_attributes_v hma
80 WHERE hma.map = p_map
81 and   hma.field_name = p_field_name;
82 
83 l_mapping_row csr_mapping%rowtype;
84 l_attribute_row varchar2(500);
85 l_attribute varchar2(1000);
86 BEGIN
87    l_attribute := null;
88 
89    Open  csr_mapping(p_map,p_field_name);
90    Fetch csr_mapping into l_mapping_row;
91    Close csr_mapping;
92 
93 
94    Open csr_timecard_attributes(l_mapping_row.context,
95                                 l_mapping_row.segment,
96                                 p_timecard_id,
97                                 p_timecard_ovn);
98    LOOP
99       Fetch csr_timecard_attributes into l_attribute_row;
100 
101 
102       EXIT when csr_timecard_attributes%NOTFOUND;
103       IF (l_attribute_row is not null)
104       THEN
105           l_attribute := l_attribute_row;
106           return l_attribute;
107        END IF;
108     END LOOP;
109     Close  csr_timecard_attributes;
110 
111     return l_attribute;
112  END get_timecard_attribute;
113 
114 END hxc_timecard_attributes_pkg;