[Home] [Help]
PACKAGE BODY: APPS.PO_PRICE_ADJ_DRAFT_PVT
Source
1 PACKAGE BODY PO_PRICE_ADJ_DRAFT_PVT AS
2 /* $Header: PO_PRICE_ADJ_DRAFT_PVT.plb 120.0.12010000.1 2009/06/01 23:33:50 ababujan noship $ */
3
4 d_pkg_name CONSTANT varchar2(50) :=
5 PO_LOG.get_package_base('PO_PRICE_ADJ_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 + price_adjustment_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 + price_adjustment 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_price_adjustment_id_tbl
27 -- po price adjustment 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_price_adjustment_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_price_adjustments_draft ADJD
83 WHERE (ADJD.draft_id = p_draft_id_tbl(i) --this condition makes sure that, both draft_id and price_adjustment_id cannot be null at the same time
84 OR (p_draft_id_tbl(i) IS NULL
85 AND ADJD.price_adjustment_id = p_price_adjustment_id_tbl(i)))
86 AND ADJD.price_adjustment_id =
87 NVL(p_price_adjustment_id_tbl(i),
88 ADJD.price_adjustment_id)
89 AND NVL(ADJD.change_accepted_flag, 'Y') = 'Y');
90
91 d_position := 30;
92
93 -- All the num1 returned from this DELETE statement are indexes for
94 -- records that contain draft changes
95 DELETE FROM po_session_gt
96 WHERE key = l_key
97 RETURNING num1
98 BULK COLLECT INTO l_dft_exists_index_tbl;
99
100 d_position := 40;
101
102 FOR i IN 1..l_dft_exists_index_tbl.COUNT LOOP
103 l_dft_exists_tbl(l_dft_exists_index_tbl(i)) := FND_API.G_TRUE;
104 END LOOP;
105
106 IF (PO_LOG.d_stmt) THEN
107 PO_LOG.stmt(d_module, d_position, '# of records that have dft changes',
108 l_dft_exists_index_tbl.COUNT);
109 END IF;
110
111 RETURN l_dft_exists_tbl;
112
113 EXCEPTION
114 WHEN OTHERS THEN
115 PO_MESSAGE_S.add_exc_msg
116 ( p_pkg_name => d_pkg_name,
117 p_procedure_name => d_api_name || '.' || d_position
118 );
119 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
120 END draft_changes_exist;
121
122 -----------------------------------------------------------------------
123 --Start of Comments
124 --Name: draft_changes_exist
125 --Pre-reqs: None
126 --Modifies:
127 --Locks:
128 -- None
129 --Function:
130 -- Same functionality as the bulk version of draft_changes_exist
131 --Parameters:
132 --IN:
133 --p_draft_id
134 -- draft unique identifier
135 --p_price_adjustment_id
136 -- price adjustment record unique identifier
137 --IN OUT:
138 --OUT:
139 --Returns:
140 -- FND_API.G_TRUE if there are draft changes
141 -- FND_API.G_FALSE if there aren't draft changes
142 --Notes:
143 --Testing:
144 --End of Comments
145 ------------------------------------------------------------------------
146 FUNCTION draft_changes_exist
147 ( p_draft_id IN NUMBER,
148 p_price_adjustment_id IN NUMBER
149 ) RETURN VARCHAR2
150 IS
151 d_api_name CONSTANT VARCHAR2(30) := 'draft_changes_exist';
152 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
153 d_position NUMBER;
154
155 l_exists_tbl PO_TBL_VARCHAR1;
156 BEGIN
157 d_position := 0;
158 IF (PO_LOG.d_proc) THEN
159 PO_LOG.proc_begin(d_module);
160 END IF;
161
162 l_exists_tbl :=
163 draft_changes_exist
164 ( p_draft_id_tbl => PO_TBL_NUMBER(p_draft_id),
165 p_price_adjustment_id_tbl => PO_TBL_NUMBER(p_price_adjustment_id)
166 );
167
168 IF (PO_LOG.d_stmt) THEN
169 PO_LOG.stmt(d_module, d_position, 'exists', l_exists_tbl(1));
170 END IF;
171
172 RETURN l_exists_tbl(1);
173
174 EXCEPTION
175 WHEN OTHERS THEN
176 PO_MESSAGE_S.add_exc_msg
177 ( p_pkg_name => d_pkg_name,
178 p_procedure_name => d_api_name || '.' || d_position
179 );
180 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
181 END draft_changes_exist;
182
183
184 -----------------------------------------------------------------------
185 --Start of Comments
186 --Name: apply_changes
187 --Pre-reqs: None
188 --Modifies:
189 --Locks:
190 -- None
191 --Function:
192 -- Process price adjustment draft records and merge them to transaction table. It
193 -- also performs all additional work related specifically to the merge
194 -- action
195 --Parameters:
196 --IN:
197 --p_draft_info
198 -- data structure storing draft information
199 --IN OUT:
200 --OUT:
201 --Returns:
202 --Notes:
203 --Testing:
204 --End of Comments
205 ------------------------------------------------------------------------
206 PROCEDURE apply_changes
207 ( p_draft_info IN PO_DRAFTS_PVT.DRAFT_INFO_REC_TYPE
208 ) IS
209 d_api_name CONSTANT VARCHAR2(30) := 'apply_changes';
210 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
211 d_position NUMBER;
212
213 BEGIN
214 d_position := 0;
215 IF (PO_LOG.d_proc) THEN
216 PO_LOG.proc_begin(d_module);
217 END IF;
218
219 IF (p_draft_info.price_adj_changed = FND_API.G_FALSE) THEN
220 IF (PO_LOG.d_stmt) THEN
221 PO_LOG.stmt(d_module, d_position, 'no change-no need to apply');
222 END IF;
223
224 RETURN;
225 END IF;
226
227 d_position := 10;
228 -- Merge Changes
229 PO_PRICE_ADJ_DRAFT_PKG.merge_changes
230 ( p_draft_id => p_draft_info.draft_id
231 );
232
233 d_position := 20;
234 EXCEPTION
235 WHEN OTHERS THEN
236 PO_MESSAGE_S.add_exc_msg
237 ( p_pkg_name => d_pkg_name,
238 p_procedure_name => d_api_name || '.' || d_position
239 );
240 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
241 END apply_changes;
242
243 END PO_PRICE_ADJ_DRAFT_PVT;