[Home] [Help]
PACKAGE BODY: APPS.PO_ATTR_VALUES_DRAFT_PVT
Source
1 PACKAGE BODY PO_ATTR_VALUES_DRAFT_PVT AS
2 /* $Header: PO_ATTR_VALUES_DRAFT_PVT.plb 120.2 2005/07/26 16:22 bao noship $ */
3
4 d_pkg_name CONSTANT varchar2(50) :=
5 PO_LOG.get_package_base('PO_ATTR_VALUES_DRAFT_PVT');
6
7 -----------------------------------------------------------------------
8 --Start of Comments
9 --Name: draft_changes_exist
10 --Pre-reqs: None
11 --Modifies:
12 --Locks:
13 -- None
14 --Function:
15 -- Checks whether there is any draft changes in the draft table
16 -- given the draft_id or draft_id + attribute_values_id
17 -- If only draft_id is provided, this program returns FND_API.G_TRUE for
18 -- any draft changes in this table for the draft
19 -- If the whole primary key is provided (draft_id + attribut_values id), then
20 -- it return true if there is draft for this particular record in
21 -- the draft table
22 --Parameters:
23 --IN:
24 --p_draft_id_tbl
25 -- draft unique identifier
26 --p_attribute_values_id_tbl
27 -- po attribute values unique identifier
28 --IN OUT:
29 --OUT:
30 --Returns:
31 -- Array of flags indicating whether draft changes exist for the corresponding
32 -- entry in the input parameter. For each entry in the returning array:
33 -- FND_API.G_TRUE if there are draft changes
34 -- FND_API.G_FALSE if there aren't draft changes
35 --Notes:
36 --Testing:
37 --End of Comments
38 ------------------------------------------------------------------------
39 FUNCTION draft_changes_exist
40 ( p_draft_id_tbl IN PO_TBL_NUMBER,
41 p_attribute_values_id_tbl IN PO_TBL_NUMBER
42 ) RETURN PO_TBL_VARCHAR1
43 IS
44 d_api_name CONSTANT VARCHAR2(30) := 'draft_changes_exist';
45 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
46 d_position NUMBER;
47
48 l_key NUMBER;
49 l_index_tbl PO_TBL_NUMBER := PO_TBL_NUMBER();
50 l_dft_exists_tbl PO_TBL_VARCHAR1 := PO_TBL_VARCHAR1();
51 l_dft_exists_index_tbl PO_TBL_NUMBER := PO_TBL_NUMBER();
52
53 BEGIN
54 d_position := 0;
55 IF (PO_LOG.d_proc) THEN
56 PO_LOG.proc_begin(d_module);
57 END IF;
58
59 l_index_tbl.extend(p_draft_id_tbl.COUNT);
60 l_dft_exists_tbl.extend(p_draft_id_tbl.COUNT);
61
62 FOR i IN 1..l_index_tbl.COUNT LOOP
63 l_index_tbl(i) := i;
64 l_dft_exists_tbl(i) := FND_API.G_FALSE;
65 END LOOP;
66
67 d_position := 10;
68
69 l_key := PO_CORE_S.get_session_gt_nextval;
70
71 d_position := 20;
72
73 FORALL i IN 1..p_draft_id_tbl.COUNT
74 INSERT INTO po_session_gt
75 ( key,
76 num1
77 )
78 SELECT l_key,
79 l_index_tbl(i)
80 FROM DUAL
81 WHERE EXISTS (SELECT 1
82 FROM po_attribute_values_draft PAVD
83 WHERE PAVD.draft_id = p_draft_id_tbl(i)
84 AND PAVD.attribute_values_id =
85 NVL(p_attribute_values_id_tbl(i),
86 PAVD.attribute_values_id)
87 AND NVL(PAVD.change_accepted_flag, 'Y') = 'Y');
88
89
90 d_position := 30;
91
92 -- All the num1 returned from this DELETE statement are indexes for
93 -- records that contain draft changes
94 DELETE FROM po_session_gt
95 WHERE key = l_key
96 RETURNING num1
97 BULK COLLECT INTO l_dft_exists_index_tbl;
98
99 d_position := 40;
100
101 FOR i IN 1..l_dft_exists_index_tbl.COUNT LOOP
102 l_dft_exists_tbl(l_dft_exists_index_tbl(i)) := FND_API.G_TRUE;
103 END LOOP;
104
105 IF (PO_LOG.d_stmt) THEN
106 PO_LOG.stmt(d_module, d_position, '# of records that have dft changes',
107 l_dft_exists_index_tbl.COUNT);
108 END IF;
109
110 RETURN l_dft_exists_tbl;
111
112 EXCEPTION
113 WHEN OTHERS THEN
114 PO_MESSAGE_S.add_exc_msg
115 ( p_pkg_name => d_pkg_name,
116 p_procedure_name => d_api_name || '.' || d_position
117 );
118 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
119 END draft_changes_exist;
120
121 -----------------------------------------------------------------------
122 --Start of Comments
123 --Name: draft_changes_exist
124 --Pre-reqs: None
125 --Modifies:
126 --Locks:
127 -- None
128 --Function:
129 -- Same functionality as the bulk version of draft_changes_exist
130 --Parameters:
131 --IN:
132 --p_draft_id
133 -- draft unique identifier
134 --p_attribute_values_id
135 -- attribute values record unique identifier
136 --IN OUT:
137 --OUT:
138 --Returns:
139 -- FND_API.G_TRUE if there are draft changes
140 -- FND_API.G_FALSE if there aren't draft changes
141 --Notes:
142 --Testing:
143 --End of Comments
144 ------------------------------------------------------------------------
145 FUNCTION draft_changes_exist
146 ( p_draft_id IN NUMBER,
147 p_attribute_values_id IN NUMBER
148 ) RETURN VARCHAR2
149 IS
150 d_api_name CONSTANT VARCHAR2(30) := 'draft_changes_exist';
151 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
152 d_position NUMBER;
153
154 l_exists_tbl PO_TBL_VARCHAR1;
155 BEGIN
156 d_position := 0;
157 IF (PO_LOG.d_proc) THEN
158 PO_LOG.proc_begin(d_module);
159 END IF;
160
161 l_exists_tbl :=
162 draft_changes_exist
163 ( p_draft_id_tbl => PO_TBL_NUMBER(p_draft_id),
164 p_attribute_values_id_tbl => PO_TBL_NUMBER(p_attribute_values_id)
165 );
166
167 IF (PO_LOG.d_stmt) THEN
168 PO_LOG.stmt(d_module, d_position, 'exists', l_exists_tbl(1));
169 END IF;
170
171 RETURN l_exists_tbl(1);
172
173 EXCEPTION
174 WHEN OTHERS THEN
175 PO_MESSAGE_S.add_exc_msg
176 ( p_pkg_name => d_pkg_name,
177 p_procedure_name => d_api_name || '.' || d_position
178 );
179 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
180 END draft_changes_exist;
181
182
183 -----------------------------------------------------------------------
184 --Start of Comments
185 --Name: apply_changes
186 --Pre-reqs: None
187 --Modifies:
188 --Locks:
189 -- None
190 --Function:
191 -- Process attr val draft records and merge them to transaction table. It
192 -- also performs all additional work related specifically to the merge
193 -- action
194 --Parameters:
195 --IN:
196 --p_draft_info
197 -- data structure storing draft information
198 --IN OUT:
199 --OUT:
200 --Returns:
201 --Notes:
202 --Testing:
203 --End of Comments
204 ------------------------------------------------------------------------
205 PROCEDURE apply_changes
206 ( p_draft_info IN PO_DRAFTS_PVT.DRAFT_INFO_REC_TYPE
207 ) IS
208 d_api_name CONSTANT VARCHAR2(30) := 'apply_changes';
209 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
210 d_position NUMBER;
211
212 BEGIN
213 d_position := 0;
214 IF (PO_LOG.d_proc) THEN
215 PO_LOG.proc_begin(d_module);
216 END IF;
217
218 IF (p_draft_info.attr_values_changed = FND_API.G_FALSE) THEN
219 IF (PO_LOG.d_stmt) THEN
220 PO_LOG.stmt(d_module, d_position, 'no change-no need to apply');
221 END IF;
222
223 RETURN;
224 END IF;
225
226 d_position := 10;
227 -- Merge Changes
228 PO_ATTR_VALUES_DRAFT_PKG.merge_changes
229 ( p_draft_id => p_draft_info.draft_id
230 );
231
232 d_position := 20;
233 EXCEPTION
234 WHEN OTHERS THEN
235 PO_MESSAGE_S.add_exc_msg
236 ( p_pkg_name => d_pkg_name,
237 p_procedure_name => d_api_name || '.' || d_position
238 );
239 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
240 END apply_changes;
241
242 END PO_ATTR_VALUES_DRAFT_PVT;