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