[Home] [Help]
PACKAGE BODY: APPS.PO_ATTR_VALUES_TLP_DRAFT_PVT
Source
1 PACKAGE BODY PO_ATTR_VALUES_TLP_DRAFT_PVT AS
2 /* $Header: PO_ATTR_VALUES_TLP_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_TLP_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_tlp_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 + attribute_values_tlp id),
20 -- then it returns 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_tlp_id_tbl
27 -- po attribute_values_tlp 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_tlp_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_tlp_draft PAVTD
83 WHERE PAVTD.draft_id = p_draft_id_tbl(i)
84 AND PAVTD.attribute_values_tlp_id =
85 NVL(p_attribute_values_tlp_id_tbl(i),
86 PAVTD.attribute_values_tlp_id)
87 AND NVL(PAVTD.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 -----------------------------------------------------------------------
123 --Start of Comments
124 --Name: draft_changes_exist
125 --Pre-reqs: None
126 --Modifies:
127 --Locks:
128 -- None
129 --Function:
130 -- Checks whether there is any draft changes in the draft table
131 -- given the primary key or part of the primary key
132 -- If only draft_id is provided, this program returns FND_API.G_TRUE for
133 -- any draft changes in this table for the draft
134 -- If the whole primary key is provided (draft_id+attribute_values_tlp_id),
135 -- then it return true if there is draft for this particular record in
136 -- the draft table
137 --Parameters:
138 --IN:
139 --p_draft_id
140 -- draft unique identifier
141 --p_attribute_values_tlp_id
142 -- attribute values tlp record unique identifier
143 --IN OUT:
144 --OUT:
145 --Returns:
146 -- FND_API.G_TRUE if there are draft changes
147 -- FND_API.G_FALSE if there aren't draft changes
148 --Notes:
149 --Testing:
150 --End of Comments
151 ------------------------------------------------------------------------
152 FUNCTION draft_changes_exist
153 ( p_draft_id IN NUMBER,
154 p_attribute_values_tlp_id IN NUMBER
155 ) RETURN VARCHAR2
156 IS
157 d_api_name CONSTANT VARCHAR2(30) := 'draft_changes_exist';
158 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
159 d_position NUMBER;
160
161 l_exists_tbl PO_TBL_VARCHAR1;
162 BEGIN
163 d_position := 0;
164 IF (PO_LOG.d_proc) THEN
165 PO_LOG.proc_begin(d_module);
166 END IF;
167
168 l_exists_tbl :=
169 draft_changes_exist
170 ( p_draft_id_tbl => PO_TBL_NUMBER(p_draft_id),
171 p_attribute_values_tlp_id_tbl => PO_TBL_NUMBER(p_attribute_values_tlp_id)
172 );
173
174 IF (PO_LOG.d_stmt) THEN
175 PO_LOG.stmt(d_module, d_position, 'exists', l_exists_tbl(1));
176 END IF;
177
178 RETURN l_exists_tbl(1);
179
180 EXCEPTION
181 WHEN OTHERS THEN
182 PO_MESSAGE_S.add_exc_msg
183 ( p_pkg_name => d_pkg_name,
184 p_procedure_name => d_api_name || '.' || d_position
185 );
186 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
187 END draft_changes_exist;
188
189
190 -----------------------------------------------------------------------
191 --Start of Comments
192 --Name: apply_changes
193 --Pre-reqs: None
194 --Modifies:
195 --Locks:
196 -- None
197 --Function:
198 -- Process attr val draft records and merge them to transaction table. It
199 -- also performs all additional work related specifically to the merge
200 -- action
201 --Parameters:
202 --IN:
203 --p_draft_info
204 -- data structure storing draft information
205 --IN OUT:
206 --OUT:
207 --Returns:
208 --Notes:
209 --Testing:
210 --End of Comments
211 ------------------------------------------------------------------------
212 PROCEDURE apply_changes
213 ( p_draft_info IN PO_DRAFTS_PVT.DRAFT_INFO_REC_TYPE
214 ) IS
215 d_api_name CONSTANT VARCHAR2(30) := 'apply_changes';
216 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
217 d_position NUMBER;
218
219 BEGIN
220 d_position := 0;
221 IF (PO_LOG.d_proc) THEN
222 PO_LOG.proc_begin(d_module);
223 END IF;
224
225 IF (p_draft_info.attr_values_tlp_changed = FND_API.G_FALSE) THEN
226 IF (PO_LOG.d_stmt) THEN
227 PO_LOG.stmt(d_module, d_position, 'no change-no need to apply');
228 END IF;
229
230 RETURN;
231 END IF;
232
233 d_position := 10;
234 -- Merge Changes
235 PO_ATTR_VALUES_TLP_DRAFT_PKG.merge_changes
236 ( p_draft_id => p_draft_info.draft_id
237 );
238
239 d_position := 20;
240 EXCEPTION
241 WHEN OTHERS THEN
242 PO_MESSAGE_S.add_exc_msg
243 ( p_pkg_name => d_pkg_name,
244 p_procedure_name => d_api_name || '.' || d_position
245 );
246 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
247 END apply_changes;
248
249 END PO_ATTR_VALUES_TLP_DRAFT_PVT;