1 Package cn_invoice_changes_pvt AUTHID CURRENT_USER AS
2 -- $Header: cnvinvs.pls 120.2 2005/08/10 03:48:02 hithanki noship $
3 -- +======================================================================+
4 -- | Copyright (c) 1994 Oracle Corporation |
5 -- | Redwood Shores, California, USA |
6 -- | All rights reserved. |
7 -- +======================================================================+
8
9 --
10 -- Package Name
11 -- cn_get_tx_data_pub
12 -- Purpose
13 -- Package Specification for Sun Enhancements (JSP Version)
14 -- History
15 -- 08/07/01 Rao.Chenna Created
16 --
17 TYPE invoice_rec IS RECORD(
18 invoice_change_id number(15),
19 salesrep_id number(15),
20 invoice_number varchar2(20),
21 line_number number,
22 revenue_type varchar2(15),
23 split_pct number,
24 direct_salesrep_number varchar2(30),
25 comm_lines_api_id number(15),
26 attribute_category varchar2(30),
27 attribute1 varchar2(150),
28 attribute2 varchar2(150),
29 attribute3 varchar2(150),
30 attribute4 varchar2(150),
31 attribute5 varchar2(150),
32 attribute6 varchar2(150),
33 attribute7 varchar2(150),
34 attribute8 varchar2(150),
35 attribute9 varchar2(150),
36 attribute10 varchar2(150),
37 attribute11 varchar2(150),
38 attribute12 varchar2(150),
39 attribute13 varchar2(150),
40 attribute14 varchar2(150),
41 attribute15 varchar2(150),
42 creation_date date,
43 created_by number(15),
44 last_update_date date,
45 last_updated_by number(15),
46 last_update_login number(15),
47 object_version_number number,
48 org_id number(15));
49 --
50 TYPE invoice_tbl IS
51 TABLE OF invoice_rec
52 INDEX BY BINARY_INTEGER ;
53 --
54 TYPE deal_data_rec IS RECORD(
55 comm_lines_api_id number(15),
56 invoice_number varchar2(20),
57 line_number number);
58 --
59 TYPE deal_data_tbl IS
60 TABLE OF deal_data_rec
61 INDEX BY BINARY_INTEGER ;
62 /*--------------------------------------------------------------------------
63 API name : convert_adj_to_api
64 Type : Private
65 Pre-reqs :
66 Usage :
67 Desc : most of the code for this module is based on cn_adjustment_v
68 view and adj_rec_type is a record corresponding to this view.
69 So we have to convert this record type to table handler
70 record type before we call the table handler.
71 Parameters
72 IN : p_adj_rec - PL/SQL record corresponding to cn_adjustment_v
73 OUT NOCOPY : x_api_rec - PL/SQL record corresponding to cn_comm_lines_api
74 table
75 Notes :
76 --------------------------------------------------------------------------*/
77 PROCEDURE convert_adj_to_api(
78 p_adj_rec IN cn_get_tx_data_pub.adj_rec_type,
79 x_api_rec OUT NOCOPY cn_comm_lines_api_pkg.comm_lines_api_rec_type);
80 /*--------------------------------------------------------------------------
81 API name : prepare_api_record
82 Type : Private
83 Pre-reqs :
84 Usage :
85 Desc : In this module, so many places we have to compare the
86 transaction's old data with new data from the JSPs. Where
87 ever new data is not available, we have to take the data from
88 the old record. This API will do this task.
89 Parameters
90 IN : p_newtx_rec - PL/SQL record corresponding to new data coming
91 from JSP
92 : p_old_adj_tbl - PL/SQL table contain old information.
93 OUT NOCOPY : x_final_trx_rec - PL/SQL record filled with old and new data.
94 Notes :
95 --------------------------------------------------------------------------*/
96 PROCEDURE prepare_api_record(
97 p_newtx_rec IN cn_get_tx_data_pub.adj_rec_type,
98 p_old_adj_tbl IN cn_get_tx_data_pub.adj_tbl_type,
99 x_final_trx_rec OUT NOCOPY cn_get_tx_data_pub.adj_rec_type,
100 x_return_status OUT NOCOPY VARCHAR2);
101 /*--------------------------------------------------------------------------
102 API name : update_invoice_changes
103 Type : Private
104 Pre-reqs :
105 Usage :
106 Desc : Sun Enhancements. This API is being used to create and delete
107 the records from cn_invoice_changes table that will track the
108 invoice transactions. Where ever we use this API, we have to
109 use twice. First to delete the records from cn_invoice_changes
110 table. After creating new records in cn_comm_lines_api table
111 call this API again to create records with new API_IDs
112 Parameters
113 IN : p_existing_data - PL/SQL table correspond to data-to-be-deleted
114 from cn_invoice_changes table.
115 : p_new_data - PL/SQL table correspond to data-to-be-created
116 in cn_invoice_changes table.
117 : p_exist_data_check - Used for first call
118 : p_new_data_check - Used for the second call
119 OUT NOCOPY :
120 Notes :
121 --------------------------------------------------------------------------*/
122 PROCEDURE update_invoice_changes(
123 p_api_version IN NUMBER,
124 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
125 p_validation_level IN VARCHAR2 := FND_API.G_VALID_LEVEL_FULL,
126 p_existing_data IN invoice_tbl,
127 p_new_data IN invoice_tbl,
128 p_exist_data_check IN VARCHAR2 DEFAULT NULL,
129 p_new_data_check IN VARCHAR2 DEFAULT NULL,
130 x_return_status OUT NOCOPY VARCHAR2,
131 x_msg_count OUT NOCOPY NUMBER,
132 x_msg_data OUT NOCOPY VARCHAR2,
133 x_loading_status OUT NOCOPY VARCHAR2);
134 /*--------------------------------------------------------------------------
135 API name : update_credit_memo
136 Type : Private
137 Pre-reqs :
138 Usage :
139 Desc : Sun Enhancements. This API is being used to delete existing
140 credit memos and payments and recreate with new split %s.
141 Parameters
142 IN : p_existing_data - PL/SQL table correspond to data-to-be-negated
143 in cn_comm_lines_api and cn_commission_headers tables.
144 : p_new_data - PL/SQL table correspond to data-to-be-created in
145 cn_comm_lines_api and cn_commission_headers tables.
146 : p_to_salesrep_id - It is being used when called from Move Credits.
147 : p_to_salesrep_number - It is being used when called from Move
148 Credits
149 OUT NOCOPY :
150 Notes :
151 --------------------------------------------------------------------------*/
152 PROCEDURE update_credit_memo(
153 p_api_version IN NUMBER,
154 p_init_msg_list IN VARCHAR2:= FND_API.G_TRUE,
155 p_validation_level IN VARCHAR2:= FND_API.G_VALID_LEVEL_FULL,
156 p_existing_data IN invoice_tbl,
157 p_new_data IN invoice_tbl,
158 p_to_salesrep_id IN NUMBER := FND_API.G_MISS_NUM,
159 p_to_salesrep_number IN VARCHAR2:= FND_API.G_MISS_CHAR,
160 p_called_from IN VARCHAR2,
161 p_adjust_status IN VARCHAR2,
162 x_return_status OUT NOCOPY VARCHAR2,
163 x_msg_count OUT NOCOPY NUMBER,
164 x_msg_data OUT NOCOPY VARCHAR2,
165 x_loading_status OUT NOCOPY VARCHAR2);
166 /*--------------------------------------------------------------------------
167 API name : update_mass_invoices
168 Type : Private
169 Pre-reqs :
170 Usage :
171 Desc : Sun Enhancements. This API is being to identify the unique
172 invoices based on the search criteria and delete the records
173 from cn_invoice_changes table.
174 Parameters
175 IN : p_salesrep_id - From the transaction summary search page.
176 : p_pr_date_to - From the transaction summary search page.
177 : p_pr_date_from - From the transaction summary search page.
178 : p_calc_status - From the transaction summary search page.
179 : p_order_num - From the transaction summary search page.
180 : p_srch_attr_rec - This record type stores the attribute
181 columns from the advanced search option.
182 OUT NOCOPY :
183 Notes :
184 --------------------------------------------------------------------------*/
185 PROCEDURE update_mass_invoices (
186 p_api_version IN NUMBER,
187 p_init_msg_list IN VARCHAR2:= FND_API.G_TRUE,
188 p_validation_level IN VARCHAR2:= FND_API.G_VALID_LEVEL_FULL,
189 p_salesrep_id IN NUMBER := FND_API.G_MISS_NUM,
190 p_pr_date_to IN DATE := FND_API.G_MISS_DATE,
191 p_pr_date_from IN DATE := FND_API.G_MISS_DATE,
192 p_calc_status IN VARCHAR2:= FND_API.G_MISS_CHAR,
193 p_invoice_num IN VARCHAR2:= FND_API.G_MISS_CHAR,
194 p_order_num IN NUMBER := FND_API.G_MISS_NUM,
195 p_srch_attr_rec IN cn_get_tx_data_pub.adj_rec_type,
196 p_to_salesrep_id IN NUMBER := FND_API.G_MISS_NUM,
197 p_to_salesrep_number IN VARCHAR2:= FND_API.G_MISS_CHAR,
198 x_return_status OUT NOCOPY VARCHAR2,
199 x_msg_count OUT NOCOPY NUMBER,
200 x_msg_data OUT NOCOPY VARCHAR2,
201 x_loading_status OUT NOCOPY VARCHAR2,
202 x_existing_data OUT NOCOPY invoice_tbl);
203 /*--------------------------------------------------------------------------
204 API name : capture_deal_invoice
205 Type : Private
206 Pre-reqs :
207 Usage :
208 Desc : Sun Enhancements. This API is being used to collect the
209 distinct records for a given invoice number from api/header
210 table.
211 Parameters
212 IN : p_trx_type - INVOICE/CM/PAYMENT
213 : p_invoice_number - Invoice Number
214 : p_split_data_tbl - Split Information from the JSP
215 : x_deal_data_tbl - PL/SQL table contain information to create
216 records in cn_comm_lines_api table
217 OUT NOCOPY :
218 Notes :
219 --------------------------------------------------------------------------*/
220 PROCEDURE capture_deal_invoice(
221 p_api_version IN NUMBER,
222 p_init_msg_list IN VARCHAR2:= FND_API.G_TRUE,
223 p_validation_level IN VARCHAR2:= FND_API.G_VALID_LEVEL_FULL,
224 p_trx_type IN VARCHAR2,
225 p_split_nonrevenue_line IN VARCHAR2, p_invoice_number IN VARCHAR2,
226 p_org_id IN NUMBER,
227 p_split_data_tbl IN cn_get_tx_data_pub.split_data_tbl,
228 x_deal_data_tbl OUT NOCOPY cn_invoice_changes_pvt.deal_data_tbl,
229 x_return_status OUT NOCOPY VARCHAR2,
230 x_msg_count OUT NOCOPY NUMBER,
231 x_msg_data OUT NOCOPY VARCHAR2,
232 x_loading_status OUT NOCOPY VARCHAR2);
233 /*--------------------------------------------------------------------------
234 API name : invoice_split_batch
235 Type : Private
236 Pre-reqs :
237 Usage :
238 Desc : Sun Enhancements. This API should be run after COLLECTIONS.
239 This API collects all new INVOICE transactions information
240 and store them in CN_INVOICE_CHANGES table. Same changes are
241 applied to credit memo(CM) and payments (PMT).
242 Parameters
243 IN :
244 OUT NOCOPY :
245 Notes :
246 --------------------------------------------------------------------------*/
247 PROCEDURE invoice_split_batch(
248 x_errbuf OUT NOCOPY VARCHAR2,
249 x_retcode OUT NOCOPY NUMBER);
250 --
251 END; -- Package spec