[Home] [Help]
PACKAGE BODY: APPS.GMDSURG
Source
1 PACKAGE BODY GMDSURG AS
2 /* $Header: GMDPSURB.pls 120.3 2006/01/16 10:55:22 txdaniel noship $ */
3
4 /* ================================================================= */
5 /* FUNCTION: */
6 /* get_surrogate DYNAMIC SQL INSIDE */
7 /* */
8 /* DESCRIPTION: */
9 /* This PL/SQL function is responsible for */
10 /* retrieving a surrogate key unique number */
11 /* based on the passed in surrogate key. */
12 /* */
13 /* This function concatinates GEM5_ + passed */
14 /* surrogate key name + _s to determine the proper */
15 /* named sequence name to retrieve the next */
16 /* unique sequence number for the surrogate key. */
17 /* */
18 /* SURROGATE KEYS: */
19 /* trans_id lot_id line_id */
20 /* batch_id text_code contact_id */
21 /* addr_id cust_vend doc_id */
22 /* vendor_id journal_id session_id */
23 /* interface_id subled_id batch_event_id */
24 /* subledger_id actrans_id item_cost_id */
25 /* cmpnt_cost_id apint_trans_id batchstepline_id */
26 /* */
27 /* */
28 /* SYNOPSIS: */
29 /* iret := GMDSURG.get_surrogate(psurrogate_name); */
30 /* */
31 /* psurrogate_name the surrogate key name that */
32 /* you need a unique sequence number for. */
33 /* */
34 /* RETURNS: */
35 /* > 0 Success */
36 /* < 0 RDBMS error */
37 /* =============================================================== */
38 FUNCTION get_surrogate(psurrogate VARCHAR2)
39 RETURN NUMBER IS
40
41 /* Local variables. */
42 /* ================ MBER */
43 l_surrogate VARCHAR2(2000);
44 l_sqlstatement varchar2(1000) := NULL;
45 l_name VARCHAR2(80);
46
47 -- REF cursor defenition
48
49 TYPE REF_CUR is REF CURSOR;
50 l_ref_cur REF_CUR;
51 /* =================================== */
52 BEGIN
53 l_name := 'GEM5_'||psurrogate||'_s.nextval';
54 /* Create dynamic SQL statement to retrieve next */
55 /* sequence value for the surrogate key. */
56 /* ============================================ */
57 l_sqlstatement := 'SELECT '||l_name||' from SYS.DUAL';
58
59
60 OPEN l_ref_cur FOR l_sqlstatement;
61 FETCH l_ref_cur INTO l_surrogate;
62 CLOSE l_ref_cur;
63
64 RETURN l_surrogate;
65
66 EXCEPTION
67 WHEN OTHERS THEN
68 RETURN SQLCODE;
69 END get_surrogate;
70 END;