[Home] [Help]
PACKAGE BODY: APPS.FV_COPY_YE_RECORDS_PKG
Source
1 PACKAGE BODY fv_copy_ye_records_pkg AS
2 /* $Header: FVYECPGB.pls 120.6 2005/08/15 23:28:50 dsadhukh ship $ |*/
3 g_module_name VARCHAR2(100) := 'fv.plsql.fv_copy_ye_records_pkg.';
4 PROCEDURE copy_record
5 (v_ledger_id IN gl_ledgers.ledger_id%TYPE,
6 v_old_group_id IN fv_ye_groups.group_id%TYPE,
7 v_time_frame_new IN fv_treasury_symbols.time_frame%TYPE,--modified for Bug.1575992
8 v_fund_group_code_new IN VARCHAR2,
9 v_treasury_symbol_new IN fv_treasury_symbols.treasury_symbol%TYPE)--modified for Bug.1575992
10 AS
11
12 CURSOR c_sequences
13 IS
14 SELECT *
15 FROM fv_ye_group_sequences
16 WHERE group_id = v_old_group_id
17 ORDER BY SEQUENCE;
18
19 CURSOR c_accounts(cv_sequence_id NUMBER)
20 IS
21 SELECT *
22 FROM fv_ye_sequence_accounts
23 WHERE sequence_id = cv_sequence_id
24 ORDER BY order_by_ctr;
25 l_module_name VARCHAR2(200) := g_module_name || 'copy_record';
26 l_errbuff VARCHAR2(300);
27 v_login NUMBER;
28 v_set_of_books_id NUMBER;
29 v_new_group_id fv_ye_groups.group_id%TYPE;
30 v_sequence_id fv_ye_group_sequences.sequence_id%TYPE;
31 --pkpatel:Declared a new variable to fix Bug. 1575992
32 v_treasury_symbol_id fv_treasury_symbols.treasury_symbol_id%TYPE;
33
34 BEGIN
35
36 v_login := fnd_global.user_id;
37
38 SELECT fv_ye_groups_id_s.NEXTVAL
39 INTO v_new_group_id
40 FROM dual;
41 --pkpatel:Added code to fix Bug.1575992
42
43 --Bug 2374153: Add If to check if new_treasury_symbol is null
44 IF v_treasury_symbol_new IS NOT NULL THEN
45
46 SELECT treasury_symbol_id
47 INTO v_treasury_symbol_id
48 FROM fv_treasury_symbols
49 WHERE treasury_symbol = v_treasury_symbol_new
50 AND set_of_books_id = v_ledger_id;
51 END IF;
52
53 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
54 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
55 'INSERTING TO FV_YE_GROUPS');
56 END IF;
57 -- create fv_ye_groups record for new record.
58 INSERT INTO fv_ye_groups
59 (group_id,
60 set_of_books_id,
61 fund_time_frame,
62 fund_group_code,
63 last_update_date,
64 last_updated_by,
65 last_update_login,
66 creation_date,
67 created_by,
68 treasury_symbol_id)
69 VALUES
70 (v_new_group_id,
71 v_ledger_id,
72 v_time_frame_new,
73 v_fund_group_code_new,
74 TRUNC(SYSDATE),
75 v_login,
76 v_login,
77 TRUNC(SYSDATE),
78 v_login,
79 v_treasury_symbol_id);
80
81 -- create sequences for group_id processing.
82 FOR c_sequences_rec IN c_sequences LOOP
83
84 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
85 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
86 'SEQUENCE = '||C_SEQUENCES_REC.SEQUENCE);
87 END IF;
88 SELECT fv_ye_group_sequences_s.NEXTVAL
89 INTO v_sequence_id
90 FROM dual;
91
92 INSERT INTO fv_ye_group_sequences
93 (sequence_id,
94 group_id,
95 SEQUENCE,
96 set_of_books_id,
97 last_update_date,
98 last_updated_by,
99 last_update_login,
100 creation_date,
101 created_by)
102 VALUES
103 (v_sequence_id,
104 v_new_group_id,
105 c_sequences_rec.SEQUENCE,
106 v_ledger_id,
107 TRUNC(SYSDATE),
108 v_login,
109 v_login,
110 TRUNC(SYSDATE),
111 v_login);
112
113 -- create account records for the sequence processing.
114 FOR c_accounts_rec IN c_accounts(c_sequences_rec.sequence_id) LOOP
115
116 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
117 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
118 'ACCT_ID ='||C_ACCOUNTS_REC.SEQUENCE_ACCT_ID);
119 END IF;
120 INSERT INTO fv_ye_sequence_accounts
121 (sequence_id,
122 sequence_acct_id,
123 account_flag,
124 from_account,
125 to_account,
126 order_by_ctr,
127 set_of_books_id,
128 last_update_date,
129 last_updated_by,
130 last_update_login,
131 creation_date,
132 created_by)
133 VALUES
134 (v_sequence_id,
135 fv_ye_sequence_acct_s.NEXTVAL,
136 c_accounts_rec.account_flag,
137 c_accounts_rec.from_account,
138 c_accounts_rec.to_account,
139 c_accounts_rec.order_by_ctr,
140 v_ledger_id,
141 TRUNC(SYSDATE),
142 v_login,
143 v_login,
144 TRUNC(SYSDATE),
145 v_login);
146
147 END LOOP;
148 END LOOP;
149 COMMIT;
150 EXCEPTION
151 WHEN OTHERS THEN
152 l_errbuff := SQLERRM;
153 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name,l_errbuff);
154 END copy_record;
155 END fv_copy_ye_records_pkg;