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