1 PACKAGE arp_auto_accounting AS
2 /* $Header: ARTEAACS.pls 120.7 2004/10/26 13:13:43 mraymond ship $ */
3
4 --
5 -- global error buffer
6 --
7 g_errorbuf VARCHAR2(1000);
8 g_error_buffer VARCHAR2(1000);
9
10 -- Public user-defined exceptions
11 --
12 no_ccid EXCEPTION;
13 g_deposit_flag varchar2(1);
14
15 -- global variable
16 -- This variable is introduced to keep track from auto auccounting is getting
17 -- called. At present this variable is use to call mrc engine or not depending
18 -- on the value.
19 g_called_from VARCHAR2(30) := 'FORMS';
20
21 ----------------------------------------------------------------------------
22 --
23 -- PROCEDURE NAME: do_autoaccounting
24 --
25 -- DECSRIPTION:
26 -- Server-side entry point for autoaccounting.
27 --
28 -- ARGUMENTS:
29 -- IN:
30 -- mode: May be I(nsert), U(pdate), D(elete), or (G)et
31 -- account_class: REC, REV, FREIGHT, TAX, UNBILL, UNEARN, SUSPENSE,
32 -- CHARGES
33 -- customer_trx_id: NULL if not applicable
34 -- customer_trx_line_id: NULL if not applicable (G)
35 -- cust_trx_line_salesrep_id: NULL if not applicable
36 -- request_id: NULL if not applicable
37 -- gl_date: GL date of the account assignment
38 -- original_gl_date: Original GL date
39 -- total_trx_amount: For Receivable account only
40 -- passed_ccid: Code comination ID to use if supplied
41 -- force_account_set_no:
42 -- cust_trx_type_id (G)
43 -- primary_salesrep_id (G)
44 -- inventory_item_id (G)
45 -- memo_line_id (G)
46 --
47 -- IN/OUT:
48 -- ccid
49 -- concat_segments
50 -- failure_count
51 --
52 -- OUT:
53 --
54 -- NOTES:
55 -- If mode is not (G)et, raises the exception
56 -- arp_auto_accounting.no_ccid if autoaccounting could not derive a
57 -- valid code combination. The public variable g_error_buffer is
58 -- populated for more information. In (G)et mode, check the value
59 -- assigned to p_ccid. If it is -1, then no ccid was found.
60 --
61 -- Raises the exception NO_DATA_FOUND if no rows were selected for
62 -- processing.
63 --
64 -- Exception raised if Oracle error.
65 -- App_exception is raised for all other fatal errors and a message
66 -- is put on the AOL stack. The public variable g_error_buffer is
67 -- populated for both types of errors.
68 --
69 -- HISTORY:
70 --
71 --
72 PROCEDURE do_autoaccounting( p_mode IN VARCHAR2,
73 p_account_class IN VARCHAR2,
74 p_customer_trx_id IN NUMBER,
75 p_customer_trx_line_id IN NUMBER,
76 p_cust_trx_line_salesrep_id IN NUMBER,
77 p_request_id IN NUMBER,
78 p_gl_date IN DATE,
79 p_original_gl_date IN DATE,
80 p_total_trx_amount IN NUMBER,
81 p_passed_ccid IN NUMBER,
82 p_force_account_set_no IN VARCHAR2,
83 p_cust_trx_type_id IN NUMBER,
84 p_primary_salesrep_id IN NUMBER,
85 p_inventory_item_id IN NUMBER,
86 p_memo_line_id IN NUMBER,
87 p_ccid IN OUT NOCOPY NUMBER,
88 p_concat_segments IN OUT NOCOPY VARCHAR2,
89 p_failure_count IN OUT NOCOPY NUMBER );
90 --
91 -- PROCEDURE NAME: do_autoaccounting
92 --
93 -- DECSRIPTION:
94 -- Overloaded procedure when autoaccounting is called in G or Get mode
95 -- as warehouse id is required to be passed in and bill_to_site_use_id
96 -- is implicitly derived.
97 --
98 -- ARGUMENTS:
99 -- IN:
100 -- mode: May be (G)et only as the routine is written for the same
101 -- account_class: REC, REV, FREIGHT, TAX, UNBILL, UNEARN, SUSPENSE,
102 -- CHARGES
103 -- customer_trx_id: NULL if not applicable
104 -- customer_trx_line_id: NULL if not applicable (G)
105 -- cust_trx_line_salesrep_id: NULL if not applicable
106 -- request_id: NULL if not applicable
107 -- gl_date: GL date of the account assignment
108 -- original_gl_date: Original GL date
109 -- total_trx_amount: For Receivable account only
110 -- passed_ccid: Code comination ID to use if supplied
111 -- force_account_set_no:
112 -- cust_trx_type_id (G)
113 -- primary_salesrep_id (G)
114 -- inventory_item_id (G)
115 -- memo_line_id (G)
116 -- warehouse_id (G)
117 --
118 -- IN/OUT:
119 -- ccid
120 -- concat_segments
121 -- failure_count
122 --
123 -- OUT:
124 --
125 -- NOTES:
126 -- If mode is not (G)et, raises the exception
127 -- arp_auto_accounting.no_ccid if autoaccounting could not derive a
128 -- valid code combination. The public variable g_error_buffer is
129 -- populated for more information. In (G)et mode, check the value
130 -- assigned to p_ccid. If it is -1, then no ccid was found.
131 --
132 -- Raises the exception NO_DATA_FOUND if no rows were selected for
133 -- processing.
134 --
135 -- Exception raised if Oracle error.
136 -- App_exception is raised for all other fatal errors and a message
137 -- is put on the AOL stack. The public variable g_error_buffer is
138 -- populated for both types of errors.
139 --
140 -- Never call this routine for ALL classes as this was specifically
141 -- written to work in Get mode, but will also work in other modes
142 -- provided the account class is not ALL
143 -- HISTORY:
144 --
145 --
146 PROCEDURE do_autoaccounting( p_mode IN VARCHAR2,
147 p_account_class IN VARCHAR2,
148 p_customer_trx_id IN NUMBER,
149 p_customer_trx_line_id IN NUMBER,
150 p_cust_trx_line_salesrep_id IN NUMBER,
151 p_request_id IN NUMBER,
152 p_gl_date IN DATE,
153 p_original_gl_date IN DATE,
154 p_total_trx_amount IN NUMBER,
155 p_passed_ccid IN NUMBER,
156 p_force_account_set_no IN VARCHAR2,
157 p_cust_trx_type_id IN NUMBER,
158 p_primary_salesrep_id IN NUMBER,
159 p_inventory_item_id IN NUMBER,
160 p_memo_line_id IN NUMBER,
161 p_warehouse_id IN NUMBER,
162 p_ccid IN OUT NOCOPY NUMBER,
163 p_concat_segments IN OUT NOCOPY VARCHAR2,
164 p_failure_count IN OUT NOCOPY NUMBER );
165
166 --
167 -- Old version:
168 --
169
170 --
171 --
172 -- FUNCTION NAME: do_autoaccounting
173 --
174 -- DECSRIPTION:
175 -- Server-side entry point for autoaccounting.
176 -- This is a cover function which calls the procedure do_autoaccounting
177 -- and exists for backward compatibility. New programs should use
178 -- the procedure instead of the function.
179 --
180 -- ARGUMENTS:
181 -- IN:
182 -- mode: May be I(nsert), U(pdate), D(elete), or (G)et
183 -- account_class: REC, REV, FREIGHT, TAX, UNBILL, UNEARN, SUSPENSE,
184 -- CHARGES
185 -- customer_trx_id: NULL if not applicable
186 -- customer_trx_line_id: NULL if not applicable
187 -- cust_trx_line_salesrep_id: NULL if not applicable
188 -- request_id: NULL if not applicable
189 -- gl_date: GL date of the account assignment
190 -- original_gl_date: Original GL date
191 -- total_trx_amount: For Receivable account only
192 -- passed_ccid: Code comination ID to use if supplied
193 -- force_account_set_no:
194 -- cust_trx_type_id:
195 -- primary_salesrep_id
196 -- inventory_item_id
197 -- memo_line_id
198 -- msg_level
199 --
200 -- IN/OUT:
201 -- ccid
202 -- concat_segments
203 -- num_dist_rows_failed
204 -- errorbuf
205 --
206 -- OUT:
207 --
208 -- RETURNS:
209 -- 1 if no errors in deriving ccids and creating distributions,
210 -- 0 if one or more rows where ccid could not be found,
211 -- Exception raised if SQL error or other fatal error.
212 --
213 -- NOTES:
214 --
215 -- HISTORY:
216 --
217 FUNCTION do_autoaccounting( p_mode IN VARCHAR2,
218 p_account_class IN VARCHAR2,
219 p_customer_trx_id IN NUMBER,
220 p_customer_trx_line_id IN NUMBER,
221 p_cust_trx_line_salesrep_id IN NUMBER,
222 p_request_id IN NUMBER,
223 p_gl_date IN DATE,
224 p_original_gl_date IN DATE,
225 p_total_trx_amount IN NUMBER,
226 p_passed_ccid IN NUMBER,
227 p_force_account_set_no IN VARCHAR2,
228 p_cust_trx_type_id IN NUMBER,
229 p_primary_salesrep_id IN NUMBER,
230 p_inventory_item_id IN NUMBER,
231 p_memo_line_id IN NUMBER,
232 p_ccid IN OUT NOCOPY NUMBER,
233 p_concat_segments IN OUT NOCOPY VARCHAR2,
234 p_num_failed_dist_rows IN OUT NOCOPY NUMBER,
235 p_errorbuf IN OUT NOCOPY VARCHAR2,
236 p_msg_level IN NUMBER default null)
237 RETURN NUMBER;
238
239
240 FUNCTION query_autoacc_def( p_account_class IN VARCHAR2,
241 p_table_name IN VARCHAR2 )
242 RETURN BOOLEAN;
243
244 FUNCTION search_glcc_for_ccid( p_system_info IN
245 arp_trx_global.system_info_rec_type,
246 p_segment_table IN fnd_flex_ext.SegmentArray,
247 p_segment_cnt IN BINARY_INTEGER )
248 RETURN BINARY_INTEGER ;
249
250 FUNCTION search_glcc_for_ccid( p_system_info IN
251 arp_trx_global.system_info_rec_type,
252 p_segment_table IN fnd_flex_ext.SegmentArray,
253 p_segment_cnt IN BINARY_INTEGER,
254 p_account_class IN
255 ra_cust_trx_line_gl_dist.account_class%type,
256 p_concat_segments IN VARCHAR2 )
257 RETURN BINARY_INTEGER;
258
259 PROCEDURE test_harness;
260 PROCEDURE test_wes;
261
262 PROCEDURE test_load;
263 PROCEDURE test_query( p_account_class IN VARCHAR2,
264 p_table_name IN VARCHAR2 );
265 PROCEDURE test_find( p_trx_type_id IN NUMBER,
266 p_salesrep_id IN NUMBER,
267 p_inv_item_id IN NUMBER,
268 p_memo_line_id IN NUMBER);
269
270 PROCEDURE test_assembly;
271 PROCEDURE test_build_sql;
272 PROCEDURE test_do_autoacc;
273 --begin anuj
274 /* Multi-Org Access Control Changes for SSA;Begin;anukumar;11/01/2002*/
275 PROCEDURE init;
276 /* Multi-Org Access Control Changes for SSA;Begin;anukumar;11/01/2002*/
277 --end anuj
278
279 END ARP_AUTO_ACCOUNTING;