[Home] [Help]
PACKAGE BODY: APPS.CN_LEDGER_JOURNAL_ENTRIES_API
Source
1 PACKAGE BODY CN_LEDGER_JOURNAL_ENTRIES_API as
2 /* $Header: cnsbjeb.pls 115.4 2001/10/29 17:12:38 pkm ship $ */
3
4 /*
5 Date Name Description
6 ---------------------------------------------------------------------------+
7 29-DEC-94 A. Lower Created package
8 06-23-95 A. Erickson cn_periods.period_name column name update
9 MAR-02-99 H. Chen code changes for MLS
10
11 Name : CN_LEDGER_JOURNAL_ENTRIES_API
12 Purpose : Holds functions for accessing and procedures for setting
13 properties of journal entries.
14
15 Notes :
16
17 */
18
19 --+
20 -- Procedure name
21 -- New_JE
22 -- Purpose
23 -- An API function which returns the journal entry ID of a newly created
24 -- entry.
25 --+
26
27 FUNCTION New_JE (X_batch_id NUMBER,
28 X_salesrep_id NUMBER,
29 X_period_id NUMBER,
30 X_account NUMBER,
31 X_credit NUMBER,
32 X_debit NUMBER,
33 X_date DATE) return NUMBER IS
34 JE_Return NUMBER;
35
36 BEGIN
37
38 INSERT INTO cn_ledger_journal_entries
39 (batch_id, srp_period_id, balance_id, credit, debit, je_date,
40 ledger_je_id, reason)
41 (SELECT X_batch_id, srp_per.srp_period_id, X_account,
42 X_credit, X_debit, X_date,
43 cn_ledger_journal_entries_s.nextval,
44 batch.reason
45 FROM cn_srp_periods srp_per,
46 cn_ledger_je_batches batch
47 WHERE salesrep_id = X_salesrep_id
48 AND period_id = X_period_id
49 AND batch.batch_id = X_batch_id);
50
51 SELECT cn_ledger_journal_entries_s.currval INTO JE_Return FROM dual;
52
53 RETURN JE_Return;
54
55 END New_JE;
56
57
58 --+
59 -- Procedure name
60 -- New_JE
61 -- Purpose
62 -- An API function which returns the journal entry ID of a newly created
63 -- entry.
64 --+
65
66 FUNCTION New_JE (X_batch_id NUMBER,
67 X_salesrep_id NUMBER,
68 X_period_id NUMBER,
69 x_role_id NUMBER,
70 x_credit_type_id NUMBER,
71 X_account NUMBER,
72 X_credit NUMBER,
73 X_debit NUMBER,
74 X_date DATE) return NUMBER IS
75 JE_Return NUMBER;
76
77 BEGIN
78
79 --dbms_output.put_line('Salesrep: ' || x_salesrep_id);
80 --dbms_output.put_line('Period: ' || x_period_id);
81 --dbms_output.put_line('Credit: ' || x_credit_type_id);
82 --dbms_output.put_line('Role: ' || x_role_id);
83 --dbms_output.put_line('Batch: ' || x_batch_id);
84
85 INSERT INTO cn_ledger_journal_entries
86 (batch_id,
87 srp_period_id,
88 balance_id,
89 credit,
90 debit,
91 je_date,
92 ledger_je_id,
93 reason)
94 (SELECT X_batch_id,
95 srp_per.srp_period_id,
96 X_account,
97 X_credit,
98 X_debit,
99 X_date,
100 cn_ledger_journal_entries_s.nextval,
101 batch.reason
102 FROM cn_srp_periods srp_per,
103 cn_ledger_je_batches batch
104 WHERE salesrep_id = X_salesrep_id
105 AND period_id = x_period_id
106 AND credit_type_id = x_credit_type_id
107 AND role_id = x_role_id
108 AND batch.batch_id = X_batch_id);
109
110 --dbms_output.put_line('Insert ...' || SQL%rowcount);
111
112 SELECT cn_ledger_journal_entries_s.CURRVAL INTO JE_Return FROM dual;
113
114 RETURN JE_Return;
115
116 END New_JE;
117
118
119 --+
120 -- Procedure name
121 -- New_JE
122 -- Purpose
123 -- An API function which returns the journal entry ID of a newly created
124 -- entry.
125 --+
126
127 FUNCTION New_JE (X_batch_id NUMBER,
128 X_salesrep_id NUMBER,
129 X_period_id NUMBER,
130 X_account NUMBER,
131 X_credit NUMBER,
132 X_debit NUMBER,
133 X_date DATE,
134 X_reason VARCHAR2) return NUMBER IS
135 JE_Return NUMBER;
136
137 BEGIN
138
139 INSERT INTO cn_ledger_journal_entries
140 (batch_id, srp_period_id, balance_id, credit, debit, je_date,
141 ledger_je_id, reason)
142 (SELECT X_batch_id, srp_per.srp_period_id, X_account,
143 X_credit, X_debit, X_date,
144 cn_ledger_journal_entries_s.nextval, X_reason
145 FROM cn_srp_periods srp_per
146 WHERE salesrep_id = X_salesrep_id
147 AND period_id = X_period_id);
148
149 SELECT cn_ledger_journal_entries_s.currval INTO JE_Return FROM dual;
150
151 RETURN JE_Return;
152
153 END New_JE;
154
155
156 PROCEDURE Names_From_IDs (X_batch_id NUMBER,
157 X_who_name IN OUT VARCHAR2,
158 X_reason IN OUT VARCHAR2,
159 X_posted IN OUT VARCHAR2,
160 X_srp_period_id NUMBER,
161 X_balance_id NUMBER,
162 X_salesrep_name IN OUT VARCHAR2,
163 X_account_name IN OUT VARCHAR2,
164 X_period_name IN OUT VARCHAR2,
165 X_salesrep_id IN OUT NUMBER,
166 X_period_id IN OUT NUMBER) IS
167
168 Dummy NUMBER(15);
169 BEGIN
170
171 BEGIN
172
173 SELECT who, reason, decode(status, 'POSTED', 'Y', 'N')
174 INTO X_who_name, X_reason, X_posted
175 FROM cn_ledger_je_batches
176 WHERE batch_id = X_batch_id;
177
178 EXCEPTION
179
180 WHEN NO_DATA_FOUND THEN
181
182 Dummy := 1;
183
184 END;
185
186 BEGIN
187
188 SELECT rep.name, rep.salesrep_id
189 INTO X_salesrep_name, X_salesrep_id
190 FROM cn_salesreps rep,
191 cn_srp_periods per
192 WHERE per.srp_period_id = X_srp_period_id
193 AND rep.salesrep_id = per.salesrep_id;
194
195 SELECT per.period_name, per.period_ID
196 INTO X_period_name, X_period_id
197 FROM cn_periods per,
198 cn_srp_periods srp
199 WHERE srp.period_id = per.period_id
200 AND srp.srp_period_id = X_srp_period_id;
201
202 -- MLS Change
203 -- replace cn_subledger_balance_types with cn_ledger_bal_types
204 -- SELECT balance_name INTO X_account_name
205 -- FROM cn_subledger_balance_types
206 -- WHERE balance_id = X_balance_id;
207 SELECT balance_name INTO X_account_name
208 FROM cn_ledger_bal_types
209 WHERE balance_id = X_balance_id;
210
211 EXCEPTION
212
213 WHEN NO_DATA_FOUND THEN
214
215 Dummy := 1;
216
217 END;
218
219 END Names_From_IDs;
220
221
222 END CN_LEDGER_JOURNAL_ENTRIES_API;