[Home] [Help]
PACKAGE BODY: APPS.PO_GA_ORG_ASSIGN_DRAFT_PVT
Source
1 PACKAGE BODY PO_GA_ORG_ASSIGN_DRAFT_PVT AS
2 /* $Header: PO_GA_ORG_ASSIGN_DRAFT_PVT.plb 120.2 2005/07/26 16:21 bao noship $ */
3
4 d_pkg_name CONSTANT varchar2(50) :=
5 PO_LOG.get_package_base('PO_GA_ORG_ASSIGN_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 + org_assignment_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 + org assignment 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_org_assignment_id_tbl
27 -- po ga org assignment 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_org_assignment_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_ga_org_assign_draft PGOAD
83 WHERE PGOAD.draft_id = p_draft_id_tbl(i)
84 AND PGOAD.org_assignment_id =
85 NVL(p_org_assignment_id_tbl(i),
86 PGOAD.org_assignment_id)
87 AND NVL(PGOAD.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 -- Same functionality as the bulk version of draft_changes_exist
131 --Parameters:
132 --IN:
133 --p_draft_id
134 -- draft unique identifier
135 --p_org_assignment_id
136 -- org assignment 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_org_assignment_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_org_assignment_id_tbl => PO_TBL_NUMBER(p_org_assignment_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 org assignment 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
215 d_position := 0;
216 IF (PO_LOG.d_proc) THEN
217 PO_LOG.proc_begin(d_module);
218 END IF;
219
220 IF (p_draft_info.ga_org_assign_changed = FND_API.G_FALSE) THEN
221 IF (PO_LOG.d_stmt) THEN
222 PO_LOG.stmt(d_module, d_position, 'no change-no need to apply');
223 END IF;
224
225 RETURN;
226 END IF;
227
228 d_position := 10;
229 -- Merge Changes
230 PO_GA_ORG_ASSIGN_DRAFT_PKG.merge_changes
231 ( p_draft_id => p_draft_info.draft_id
232 );
233
234 d_position := 20;
235 EXCEPTION
236 WHEN OTHERS THEN
237 PO_MESSAGE_S.add_exc_msg
238 ( p_pkg_name => d_pkg_name,
239 p_procedure_name => d_api_name || '.' || d_position
240 );
241 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
242 END apply_changes;
243
244
245 END PO_GA_ORG_ASSIGN_DRAFT_PVT;