eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.PO_GA_ORG_ASSIGN_DRAFT_PKG

Source


1 PACKAGE BODY PO_GA_ORG_ASSIGN_DRAFT_PKG AS
2 /* $Header: PO_GA_ORG_ASSIGN_DRAFT_PKG.plb 120.6 2006/09/28 23:02:36 bao noship $ */
3 
4 d_pkg_name CONSTANT varchar2(50) :=
5   PO_LOG.get_package_base('PO_GA_ORG_ASSIGN_DRAFT_PKG');
6 
7 -----------------------------------------------------------------------
8 --Start of Comments
9 --Name: delete_rows
10 --Pre-reqs: None
11 --Modifies:
12 --Locks:
13 --  None
14 --Function:
15 --  Deletes drafts for org assignments based on the information given
16 --  If only draft_id is provided, then all headers for the draft will be
17 --  deleted
18 --  If po_header_id and organization_id are also provided, then the one
19 --  record that has such primary key will be deleted
20 --Parameters:
21 --IN:
22 --p_draft_id
23 --  draft unique identifier
24 --p_org_assignment_id
25 --  org assignment unique identifier
26 --p_organization_id
27 --  requesting org of the assignment
28 --IN OUT:
29 --OUT:
30 --Returns:
31 --Notes:
32 --Testing:
33 --End of Comments
34 ------------------------------------------------------------------------
35 PROCEDURE delete_rows
36 ( p_draft_id IN NUMBER,
37   p_org_assignment_id IN NUMBER
38 ) IS
39 d_api_name CONSTANT VARCHAR2(30) := 'delete_rows';
40 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
41 d_position NUMBER;
42 
43 BEGIN
44   d_position := 0;
45   IF (PO_LOG.d_proc) THEN
46     PO_LOG.proc_begin(d_module);
47   END IF;
48 
49   DELETE FROM po_ga_org_assign_draft
50   WHERE draft_id = p_draft_id
51   AND org_assignment_id = NVL(p_org_assignment_id, org_assignment_id);
52 
53 EXCEPTION
54   WHEN OTHERS THEN
55     PO_MESSAGE_S.add_exc_msg
56     ( p_pkg_name => d_pkg_name,
57       p_procedure_name => d_api_name || '.' || d_position
58     );
59     RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
60 END delete_rows;
61 
62 
63 
64 -----------------------------------------------------------------------
65 --Start of Comments
69 --Locks:
66 --Name: sync_draft_from_txn
67 --Pre-reqs: None
68 --Modifies:
70 --  None
71 --Function:
72 --  Copy data from transaction table to draft table, if the corresponding
73 --  record in draft table does not exist. It also sets the delete flag of
74 --  the draft record according to the parameter.
75 --Parameters:
76 --IN:
77 --p_org_assignment_id_tbl
78 --  table of po ga org assignment unique identifier
79 --p_draft_id_tbl
80 --  table of draft ids this sync up will be done for
81 --p_delete_flag_tbl
82 --  table fo flags to indicate whether the draft record should be maked as
83 --  "to be deleted"
84 --IN OUT:
85 --OUT:
86 --x_record_already_exist_tbl
87 --  Returns whether the record was already in draft table or not
88 --Returns:
89 --Notes:
90 --Testing:
91 --End of Comments
92 ------------------------------------------------------------------------
93 PROCEDURE sync_draft_from_txn
94 ( p_org_assignment_id_tbl    IN PO_TBL_NUMBER,
95   p_draft_id_tbl             IN PO_TBL_NUMBER,
96   p_delete_flag_tbl          IN PO_TBL_VARCHAR1,
97   x_record_already_exist_tbl OUT NOCOPY PO_TBL_VARCHAR1
98 ) IS
99 
100 d_api_name CONSTANT VARCHAR2(30) := 'sync_draft_from_txn';
101 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
102 d_position NUMBER;
103 
104 l_distinct_id_list DBMS_SQL.NUMBER_TABLE;
105 l_duplicate_flag_tbl PO_TBL_VARCHAR1 := PO_TBL_VARCHAR1();
106 
107 BEGIN
108   d_position := 0;
109   IF (PO_LOG.d_proc) THEN
110     PO_LOG.proc_begin(d_module);
111   END IF;
112 
113   x_record_already_exist_tbl :=
114     PO_GA_ORG_ASSIGN_DRAFT_PVT.draft_changes_exist
115     ( p_draft_id_tbl => p_draft_id_tbl,
116       p_org_assignment_id_tbl => p_org_assignment_id_tbl
117     );
118 
119   -- bug5471513 START
120   -- If there're duplicate entries in the id table,
121   -- we do not want to insert multiple entries
122   -- Created an associative array to store what id has appeared.
123   l_duplicate_flag_tbl.EXTEND(p_org_assignment_id_tbl.COUNT);
124 
125   FOR i IN 1..p_org_assignment_id_tbl.COUNT LOOP
126     IF (x_record_already_exist_tbl(i) = FND_API.G_FALSE) THEN
127 
128       IF (l_distinct_id_list.EXISTS(p_org_assignment_id_tbl(i))) THEN
129 
130         l_duplicate_flag_tbl(i) := FND_API.G_TRUE;
131       ELSE
132         l_duplicate_flag_tbl(i) := FND_API.G_FALSE;
133 
134         l_distinct_id_list(p_org_assignment_id_tbl(i)) := 1;
135       END IF;
136 
137     ELSE
138 
139       l_duplicate_flag_tbl(i) := NULL;
140 
141     END IF;
142   END LOOP;
143   -- bug5471513 END
144 
145   d_position := 10;
146   IF (PO_LOG.d_stmt) THEN
147     PO_LOG.stmt(d_module, d_position, 'transfer records from txn to dft');
148   END IF;
149 
150   FORALL i IN 1..p_org_assignment_id_tbl.COUNT
151     INSERT INTO po_ga_org_assign_draft
152     (
153       draft_id,
154       delete_flag,
155       change_accepted_flag,
156       org_assignment_id,
157       po_header_id,
158       organization_id,
159       enabled_flag,
160       vendor_site_id,
161       last_update_date,
162       last_updated_by,
163       creation_date,
164       created_by,
165       last_update_login,
166       purchasing_org_id
167     )
168     SELECT
169       p_draft_id_tbl(i),
170       p_delete_flag_tbl(i),
171       NULL,
172       org_assignment_id,
173       po_header_id,
174       organization_id,
175       enabled_flag,
176       vendor_site_id,
177       last_update_date,
178       last_updated_by,
179       creation_date,
180       created_by,
181       last_update_login,
182       purchasing_org_id
183     FROM po_ga_org_assignments
184     WHERE org_assignment_id = p_org_assignment_id_tbl(i)
185     AND x_record_already_exist_tbl(i) = FND_API.G_FALSE
186     AND l_duplicate_flag_tbl(i) = FND_API.G_FALSE;
187 
188   d_position := 20;
189   IF (PO_LOG.d_stmt) THEN
190     PO_LOG.stmt(d_module, d_position, 'transfer count = ' || SQL%ROWCOUNT);
191   END IF;
192 
193   FORALL i IN 1..p_org_assignment_id_tbl.COUNT
194     UPDATE po_ga_org_assign_draft
195     SET    delete_flag = p_delete_flag_tbl(i)
196     WHERE  org_assignment_id = p_org_assignment_id_tbl(i)
197     AND    draft_id = p_draft_id_tbl(i)
198     AND    NVL(delete_flag, 'N') <> 'Y'  -- bug5570989
199     AND    x_record_already_exist_tbl(i) = FND_API.G_TRUE;
200 
201   d_position := 30;
202 
203   IF (PO_LOG.d_stmt) THEN
204     PO_LOG.stmt(d_module, d_position, 'update draft records that are already' ||
205                 ' in draft table. Count = ' || SQL%ROWCOUNT);
206   END IF;
207 
208   d_position := 40;
209 
210   IF (PO_LOG.d_proc) THEN
211     PO_LOG.proc_end(d_module);
212   END IF;
213 
214 EXCEPTION
215   WHEN OTHERS THEN
216     PO_MESSAGE_S.add_exc_msg
217     ( p_pkg_name => d_pkg_name,
218       p_procedure_name => d_api_name || '.' || d_position
219     );
220     RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
221 END sync_draft_from_txn;
222 
223 -----------------------------------------------------------------------
224 --Start of Comments
225 --Name: sync_draft_from_txn
226 --Pre-reqs: None
227 --Modifies:
228 --Locks:
229 --  None
230 --Function:
234 --p_org_assignment_id
231 --  Same functionality as the bulk version of this procedure
232 --Parameters:
233 --IN:
235 --  org assignment unique identifier
236 --p_draft_id
237 --  the draft this sync up will be done for
238 --p_delete_flag
239 --  flag to indicate whether the draft record should be maked as "to be
240 --  deleted"
241 --IN OUT:
242 --OUT:
243 --x_record_already_exist
244 --  Returns whether the record was already in draft table or not
245 --Returns:
246 --Notes:
247 --Testing:
248 --End of Comments
249 ------------------------------------------------------------------------
250 PROCEDURE sync_draft_from_txn
251 ( p_org_assignment_id IN NUMBER,
252   p_draft_id IN NUMBER,
253   p_delete_flag IN VARCHAR2,
254   x_record_already_exist OUT NOCOPY VARCHAR2
255 ) IS
256 
257 d_api_name CONSTANT VARCHAR2(30) := 'sync_draft_from_txn';
258 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
259 d_position NUMBER;
260 
261 l_record_already_exist_tbl PO_TBL_VARCHAR1;
262 
263 BEGIN
264   d_position := 0;
265   IF (PO_LOG.d_proc) THEN
266     PO_LOG.proc_begin(d_module);
267     PO_LOG.proc_begin(d_module, 'p_org_assignment_id', p_org_assignment_id);
268   END IF;
269 
270   sync_draft_from_txn
271   ( p_org_assignment_id_tbl    => PO_TBL_NUMBER(p_org_assignment_id),
272     p_draft_id_tbl             => PO_TBL_NUMBER(p_draft_id),
273     p_delete_flag_tbl          => PO_TBL_VARCHAR1(p_delete_flag),
274     x_record_already_exist_tbl => l_record_already_exist_tbl
275   );
276 
277   x_record_already_exist := l_record_already_exist_tbl(1);
278 
279   d_position := 10;
280   IF (PO_LOG.d_proc) THEN
281     PO_LOG.proc_end(d_module);
282     PO_LOG.proc_end(d_module, 'x_record_already_exist', x_record_already_exist);
283   END IF;
284 
285 EXCEPTION
286   WHEN OTHERS THEN
287     PO_MESSAGE_S.add_exc_msg
288     ( p_pkg_name => d_pkg_name,
289       p_procedure_name => d_api_name || '.' || d_position
290     );
291     RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
292 END sync_draft_from_txn;
293 
294 -----------------------------------------------------------------------
295 --Start of Comments
296 --Name: merge_changes
297 --Pre-reqs: None
298 --Modifies:
299 --Locks:
300 --  None
301 --Function:
302 --  Merge the records in draft table to transaction table
303 --  Either insert, update or delete will be performed on top of transaction
304 --  table, depending on the delete_flag on the draft record and whether the
305 --  record already exists in transaction table
306 --
307 --Parameters:
308 --IN:
309 --p_draft_id
310 --  draft unique identifier
311 --IN OUT:
312 --OUT:
313 --Returns:
314 --Notes:
315 --Testing:
316 --End of Comments
317 ------------------------------------------------------------------------
318 PROCEDURE merge_changes
319 ( p_draft_id IN NUMBER
320 ) IS
321 
322 d_api_name CONSTANT VARCHAR2(30) := 'merge_changes';
323 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
324 d_position NUMBER;
325 
326 BEGIN
327 
328   d_position := 0;
329   IF (PO_LOG.d_proc) THEN
330     PO_LOG.proc_begin(d_module);
331   END IF;
332 
333   -- Since putting DELETE within MERGE statement is causing database
334   -- to thrown internal error, for now we just separate the DELETE statement.
335   -- Once this is fixed we'll move the delete statement back to the merge
336   -- statement
337 
338   -- bug5187544
339   -- Delete only records that have not been rejected
340 
341   DELETE FROM po_ga_org_assignments PGOA
342   WHERE PGOA.org_assignment_id IN
343          ( SELECT PGOAD.org_assignment_id
344            FROM   po_ga_org_assign_draft PGOAD
345            WHERE  PGOAD.draft_id = p_draft_id
346            AND    PGOAD.delete_flag = 'Y'
347            AND    NVL(PGOAD.change_accepted_flag, 'Y') = 'Y' );
348 
349   -- Merge org assignments
350   -- For update case, the following columns will be skipped:
351   --PGOA.org_assignment_id
352   --PGOA.creation_date
353   --PGOA.created_by
354   MERGE INTO po_ga_org_assignments PGOA
355   USING (
356     SELECT
357       PGOAD.draft_id,
358       PGOAD.delete_flag,
359       PGOAD.change_accepted_flag,
360       PGOAD.org_assignment_id,
361       PGOAD.po_header_id,
362       PGOAD.organization_id,
363       PGOAD.enabled_flag,
364       PGOAD.vendor_site_id,
365       PGOAD.last_update_date,
366       PGOAD.last_updated_by,
367       PGOAD.creation_date,
368       PGOAD.created_by,
369       PGOAD.last_update_login,
370       PGOAD.purchasing_org_id
371     FROM po_ga_org_assign_draft PGOAD
372     WHERE PGOAD.draft_id = p_draft_id
373     AND NVL(PGOAD.change_accepted_flag, 'Y') = 'Y'
374     ) PGOADV
375   ON (PGOA.org_assignment_id = PGOADV.org_assignment_id)
376   WHEN MATCHED THEN
377     UPDATE
378     SET
379       PGOA.po_header_id = PGOADV.po_header_id,
380       PGOA.organization_id = PGOADV.organization_id,
381       PGOA.enabled_flag = PGOADV.enabled_flag,
382       PGOA.vendor_site_id = PGOADV.vendor_site_id,
383       PGOA.last_update_date = PGOADV.last_update_date,
384       PGOA.last_updated_by = PGOADV.last_updated_by,
385       PGOA.last_update_login = PGOADV.last_update_login,
386       PGOA.purchasing_org_id = PGOADV.purchasing_org_id
387   --  DELETE WHERE PGOADV.delete_flag = 'Y'
388   WHEN NOT MATCHED THEN
389     INSERT
390     (
391       PGOA.org_assignment_id,
392       PGOA.po_header_id,
393       PGOA.organization_id,
394       PGOA.enabled_flag,
395       PGOA.vendor_site_id,
396       PGOA.last_update_date,
397       PGOA.last_updated_by,
398       PGOA.creation_date,
399       PGOA.created_by,
400       PGOA.last_update_login,
401       PGOA.purchasing_org_id
402     )
403     VALUES
404     (
405       PGOADV.org_assignment_id,
406       PGOADV.po_header_id,
407       PGOADV.organization_id,
408       PGOADV.enabled_flag,
409       PGOADV.vendor_site_id,
410       PGOADV.last_update_date,
411       PGOADV.last_updated_by,
412       PGOADV.creation_date,
413       PGOADV.created_by,
414       PGOADV.last_update_login,
415       PGOADV.purchasing_org_id
416     ) WHERE NVL(PGOADV.delete_flag, 'N') <> 'Y';
417 
418   d_position := 10;
419 EXCEPTION
420   WHEN OTHERS THEN
421     PO_MESSAGE_S.add_exc_msg
422     ( p_pkg_name => d_pkg_name,
423       p_procedure_name => d_api_name || '.' || d_position
424     );
425     RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
426 END merge_changes;
427 
428 -----------------------------------------------------------------------
429 --Start of Comments
430 --Name: lock_draft_record
431 --Function:
432 --  Obtain database lock for the record in draft table
433 --Parameters:
434 --IN:
435 --p_org_assignment_id
436 --  id for po ga org assignment record
437 --p_draft_id
438 --  draft unique identifier
439 --RETURN:
440 --End of Comments
441 ------------------------------------------------------------------------
442 PROCEDURE lock_draft_record
443 ( p_org_assignment_id IN NUMBER,
444   p_draft_id        IN NUMBER
445 ) IS
446 
447 d_api_name CONSTANT VARCHAR2(30) := 'lock_draft_record';
448 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
449 d_position NUMBER;
450 
451 l_dummy NUMBER;
452 
453 BEGIN
454   d_position := 0;
455   IF (PO_LOG.d_proc) THEN
456     PO_LOG.proc_begin(d_module);
457   END IF;
458 
459   SELECT 1
460   INTO l_dummy
461   FROM po_ga_org_assign_draft
462   WHERE org_assignment_id = p_org_assignment_id
463   AND draft_id = p_draft_id
464   FOR UPDATE NOWAIT;
465 
466   IF (PO_LOG.d_proc) THEN
467     PO_LOG.proc_end(d_module);
468   END IF;
469 
470 EXCEPTION
471 WHEN NO_DATA_FOUND THEN
472   NULL;
473 END lock_draft_record;
474 
475 -----------------------------------------------------------------------
476 --Start of Comments
477 --Name: lock_transaction_record
478 --Function:
479 --  Obtain database lock for the record in transaction table
480 --Parameters:
481 --IN:
482 --p_org_assignment_id
483 --  id for po ga org assignment record
484 --RETURN:
485 --End of Comments
486 ------------------------------------------------------------------------
487 PROCEDURE lock_transaction_record
488 ( p_org_assignment_id IN NUMBER
489 ) IS
490 
491 d_api_name CONSTANT VARCHAR2(30) := 'lock_transaction_record';
492 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
493 d_position NUMBER;
494 
495 l_dummy NUMBER;
496 
497 BEGIN
498   d_position := 0;
499   IF (PO_LOG.d_proc) THEN
500     PO_LOG.proc_begin(d_module);
501   END IF;
502 
503   SELECT 1
504   INTO l_dummy
505   FROM po_ga_org_assignments
506   WHERE org_assignment_id = p_org_assignment_id
507   FOR UPDATE NOWAIT;
508 
509   IF (PO_LOG.d_proc) THEN
510     PO_LOG.proc_end(d_module);
511   END IF;
512 
513 EXCEPTION
514 WHEN NO_DATA_FOUND THEN
515   NULL;
516 END lock_transaction_record;
517 
518 END PO_GA_ORG_ASSIGN_DRAFT_PKG;