[Home] [Help]
PACKAGE BODY: APPS.CN_COLLECTION_AIA_PUB
Source
4 G_FILE_NAME CONSTANT VARCHAR2(12) := 'CNPCLTRB.pls';
1 PACKAGE BODY CN_COLLECTION_AIA_PUB AS
2 /* $Header: CNPCLTRB.pls 120.4 2010/12/13 04:41:26 sseshaiy noship $*/
3 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CN_COLLECTION_AIA_PUB';
5 g_cn_debug VARCHAR2(1) := fnd_profile.value('CN_DEBUG');
6 g_error_msg VARCHAR2(100) := ' is a required field. Please enter the value for it.';
7
8
9 PROCEDURE debugmsg
10 (
11 msg VARCHAR2)
12 IS
13 BEGIN
14 IF g_cn_debug = 'Y' THEN
15 cn_message_pkg.debug(SUBSTR(msg,1,254));
16 fnd_file.put_line(fnd_file.Log, msg); -- Bug fix 5125980
17 END IF;
18 -- comment out dbms_output before checking in file
19 -- dbms_output.put_line(substr(msg,1,254));
20 END debugmsg;
21
22 -- API name : loadrow_om
23 -- Type : public.
24 -- Pre-reqs :
25 -- Usage : This particular api will be called by above load row api for collection of siebel order management records.
26
27 PROCEDURE loadrow_om
28 (
29 p_api_version IN NUMBER,
30 p_init_msg_list IN VARCHAR2:= FND_API.G_FALSE,
31 p_commit IN VARCHAR2:= FND_API.G_FALSE,
32 p_aia_rec_tbl IN CN_COLLECTION_AIA_PUB.aia_rec_tbl_type,
33 p_org_id IN NUMBER,
34 x_msg_count OUT nocopy NUMBER,
35 x_msg_data OUT nocopy VARCHAR2,
36 x_return_status OUT nocopy VARCHAR2 )
37 IS
38 l_api_name CONSTANT VARCHAR2(30) := 'loadrow';
39 l_api_version CONSTANT NUMBER :=1.0;
40 l_error_msg VARCHAR(240);
41 --x_org_id NUMBER;
45 l_salesrep_id NUMBER := -1;
42 l_update_flag VARCHAR2(10) := 'N';
43 l_salesrep_number VARCHAR2(30) := '_';
44 l_employee_number VARCHAR2(30) := '_';
46 l_trans_seq_num NUMBER := 0;
47 l_update_check NUMBER := -1;
48 l_counter NUMBER;
49 l_invoice_date DATE := sysdate;
50
51
52 CURSOR get_res_num_from_src_num(p_employee_number varchar2)
53 IS
54 SELECT employee_number
55 FROM cn_salesreps
56 WHERE resource_id IN
57 (SELECT resource_id
58 FROM jtf_rs_resource_extns
59 WHERE source_number = p_employee_number
60 );
61
62 CURSOR get_salesrep_from_res_num(p_employee_number varchar2)
63 IS
64 SELECT salesrep_id
65 FROM cn_salesreps
66 WHERE resource_id IN
67 (SELECT resource_id
68 FROM jtf_rs_resource_extns
69 WHERE source_number = p_employee_number
70 );
71
72 CURSOR get_res_num_from_salesrep(p_salesrep_id number)
73 IS
74 SELECT employee_number FROM cn_salesreps WHERE salesrep_id = p_salesrep_id;
75
76 CURSOR get_src_num_from_salesrep(p_salesrep_id number)
77 IS
78 SELECT source_number
79 FROM jtf_rs_resource_extns
80 WHERE resource_id IN
81 ( SELECT resource_id FROM cn_salesreps WHERE salesrep_id = p_salesrep_id
82 ) ;
83
84 CURSOR check_order_number(p_order_number varchar2)
85 IS
86 SELECT 'Y' FLAG,
87 TRANS_SEQ_ID
88 FROM CN_COLLECTION_AIA
89 WHERE ORDER_NUMBER = p_order_number
90 AND rownum = 1;
91
92 CURSOR check_not_trx(l_trans_seq_num number,p_org_id number)
93 IS
94 SELECT 1 FLAG
95 FROM cn_not_trx
96 WHERE source_trx_line_id = l_trans_seq_num --*** Line.Primary_Key
97 AND event_id = -1020
98 AND org_id = p_org_id;
99
100 l_resnum_srcnum_cr get_res_num_from_src_num%ROWTYPE;
101 l_salesrep_resnum_cr get_salesrep_from_res_num%ROWTYPE;
102 l_resnum_salesrep_cr get_res_num_from_salesrep%ROWTYPE;
103 l_srcnum_salesrep_cr get_src_num_from_salesrep%ROWTYPE;
104 l_order_num_cr check_order_number%ROWTYPE;
105 l_not_trx_cr check_not_trx%ROWTYPE;
106
107 BEGIN
108 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api: start ');
109 x_msg_data := '_';
110 x_msg_count := 0;
111 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api: l_api_version: ' || l_api_version);
112 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api: l_api_name: ' || l_api_name);
113 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api: G_PKG_NAME: ' || G_PKG_NAME);
114 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api:p_org_id: ' || p_org_id);
115 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api: p_api_version: ' || p_api_version);
116
117 -- Initialize API return status to success
118 x_return_status := FND_API.G_RET_STS_SUCCESS;
119
120 -- Standard call to check for call compatibility.
121 IF NOT FND_API.Compatible_API_Call ( l_api_version , p_api_version , l_api_name , G_PKG_NAME ) THEN
122 x_return_status := 'F';
123 x_msg_count := 1;
124 l_error_msg := 'p_api_version' || g_error_msg;
125 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api: l_error_msg: ' || l_error_msg);
126 RAISE CN_AIA_REQ_FIELD_NOT_SET_ERROR;
127 END IF;
128
129 IF(p_org_id IS NULL) THEN
130 x_return_status := 'F';
131 x_msg_count := 1;
132 l_error_msg := 'p_org_id' || g_error_msg;
133 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api: l_error_msg: ' || l_error_msg);
134 RAISE CN_AIA_REQ_FIELD_NOT_SET_ERROR;
135 END IF;
136
137 -- Codes start here
138 --x_org_id := mo_global.get_current_org_id;
139 --DBMS_OUTPUT.put_line('p_org_id ' || p_org_id);
140 mo_global.set_policy_context('S',p_org_id);
141
142 -- Initialize message list if p_init_msg_list is set to TRUE.
143 IF FND_API.to_Boolean( p_init_msg_list ) THEN
144 FND_MSG_PUB.initialize;
145 END IF;
146
147 IF p_aia_rec_tbl.COUNT = 0
148 THEN
149 x_return_status := 'F';
150 x_msg_count := 1;
151 l_error_msg := 'p_aia_rec_tbl' || g_error_msg;
152 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api: l_error_msg: ' || l_error_msg);
153 RAISE CN_AIA_REQ_FIELD_NOT_SET_ERROR;
154 RETURN;
155 END IF;
156
157 FOR l_counter IN 1 .. p_aia_rec_tbl.COUNT
158 LOOP
159 begin
160 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api: p_salesrep_id: ' || p_aia_rec_tbl(l_counter).SALESREP_ID);
161 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api: p_employee_number: ' || p_aia_rec_tbl(l_counter).EMPLOYEE_NUMBER);
162 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api: p_processed_date: ' || p_aia_rec_tbl(l_counter).PROCESSED_DATE);
163 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api: p_invoice_number: ' || p_aia_rec_tbl(l_counter).INVOICE_NUMBER);
164 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api: p_invoice_date: ' || p_aia_rec_tbl(l_counter).INVOICE_DATE);
165 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api: p_transaction_amount: ' || p_aia_rec_tbl(l_counter).TRANSACTION_AMOUNT);
166 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api: p_transaction_currency_code: ' || p_aia_rec_tbl(l_counter).TRANSACTION_CURRENCY_CODE);
167 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api: p_trx_type: ' || p_aia_rec_tbl(l_counter).TRX_TYPE);
168 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api: p_revenue_type: ' || p_aia_rec_tbl(l_counter).REVENUE_TYPE);
169 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api: p_adjust_comments: ' || p_aia_rec_tbl(l_counter).ADJUST_COMMENTS);
170 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api: p_source_doc_id: ' || p_aia_rec_tbl(l_counter).SOURCE_DOC_ID);
171
172
176 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api: ATTRIBUTE_CATEGORY: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE_CATEGORY);
173 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api: SALES_CHANNEL: ' || p_aia_rec_tbl(l_counter).SALES_CHANNEL);
174 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api: LINE_NUMBER: ' || p_aia_rec_tbl(l_counter).LINE_NUMBER);
175 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api: REASON_CODE: ' || p_aia_rec_tbl(l_counter).REASON_CODE);
177 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api: ADJUST_DATE: ' || p_aia_rec_tbl(l_counter).ADJUST_DATE);
178 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api: ADJUSTED_BY: ' || p_aia_rec_tbl(l_counter).ADJUSTED_BY);
179 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api: BILL_TO_ADDRESS_ID: ' || p_aia_rec_tbl(l_counter).BILL_TO_ADDRESS_ID);
180 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api: SHIP_TO_ADDRESS_ID: ' || p_aia_rec_tbl(l_counter).SHIP_TO_ADDRESS_ID);
181 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api: BILL_TO_CONTACT_ID: ' || p_aia_rec_tbl(l_counter).BILL_TO_CONTACT_ID);
182 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api: SHIP_TO_CONTACT_ID: ' || p_aia_rec_tbl(l_counter).SHIP_TO_CONTACT_ID);
183 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api: CUSTOMER_ID: ' || p_aia_rec_tbl(l_counter).CUSTOMER_ID);
184 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api: INVENTORY_ITEM_ID: ' || p_aia_rec_tbl(l_counter).INVENTORY_ITEM_ID);
185 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api: ORDER_NUMBER: ' || p_aia_rec_tbl(l_counter).ORDER_NUMBER);
186 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api: BOOKED_DATE: ' || p_aia_rec_tbl(l_counter).BOOKED_DATE);
187 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api: SOURCE_TRX_NUMBER: ' || p_aia_rec_tbl(l_counter).SOURCE_TRX_NUMBER);
188 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api: DISCOUNT_PERCENTAGE: ' || p_aia_rec_tbl(l_counter).DISCOUNT_PERCENTAGE);
189 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api: MARGIN_PERCENTAGE: ' || p_aia_rec_tbl(l_counter).MARGIN_PERCENTAGE);
190 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api: EXCHANGE_RATE: ' || p_aia_rec_tbl(l_counter).EXCHANGE_RATE);
191 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api: TYPE: ' || p_aia_rec_tbl(l_counter).TYPE);
192 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api: SOURCE_TRX_SALES_LINE_ID: ' || p_aia_rec_tbl(l_counter).SOURCE_TRX_SALES_LINE_ID);
193
194
195 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api:p_attribute1 : ' || p_aia_rec_tbl(l_counter).ATTRIBUTE1 );
196 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api:p_attribute2 : ' || p_aia_rec_tbl(l_counter).ATTRIBUTE2 );
197 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api:p_attribute3 : ' || p_aia_rec_tbl(l_counter).ATTRIBUTE3 );
198 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api:p_attribute4 : ' || p_aia_rec_tbl(l_counter).ATTRIBUTE4 );
199 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api:p_attribute5 : ' || p_aia_rec_tbl(l_counter).ATTRIBUTE5 );
200 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api:p_attribute6 : ' || p_aia_rec_tbl(l_counter).ATTRIBUTE6 );
201 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api:p_attribute7 : ' || p_aia_rec_tbl(l_counter).ATTRIBUTE7 );
202 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api:p_attribute8 : ' || p_aia_rec_tbl(l_counter).ATTRIBUTE8 );
203 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api:p_attribute9 : ' || p_aia_rec_tbl(l_counter).ATTRIBUTE9 );
204 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api:p_attribute10: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE10 );
205 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api:p_attribute11: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE11 );
206 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api:p_attribute12: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE12 );
207 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api:p_attribute13: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE13 );
208 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api:p_attribute14: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE14 );
209 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api:p_attribute15: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE15 );
210 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api:p_attribute16: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE16 );
211 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api:p_attribute17: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE17 );
212 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api:p_attribute18: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE18 );
213 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api:p_attribute19: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE19 );
214 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api:p_attribute20: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE20 );
215 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api:p_attribute21: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE21 );
216 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api:p_attribute22: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE22 );
217 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api:p_attribute23: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE23 );
218 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api:p_attribute24: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE24 );
219 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api:p_attribute25: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE25 );
220 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api:p_attribute26: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE26 );
221 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api:p_attribute27: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE27 );
222 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api:p_attribute28: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE28 );
223 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api:p_attribute29: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE29 );
224 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api:p_attribute30: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE30 );
225 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api:p_attribute31: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE31 );
226 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api:p_attribute32: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE32 );
227 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api:p_attribute33: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE33 );
228 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api:p_attribute34: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE34 );
229 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api:p_attribute35: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE35 );
233 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api:p_attribute39: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE39 );
230 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api:p_attribute36: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE36 );
231 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api:p_attribute37: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE37 );
232 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api:p_attribute38: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE38 );
234 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api:p_attribute40: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE40 );
235 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api:p_attribute41: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE41 );
236 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api:p_attribute42: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE42 );
237 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api:p_attribute43: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE43 );
238 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api:p_attribute44: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE44 );
239 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api:p_attribute45: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE45 );
240 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api:p_attribute46: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE46 );
241 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api:p_attribute47: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE47 );
242 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api:p_attribute48: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE48 );
243 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api:p_attribute49: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE49 );
244 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api:p_attribute50: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE50 );
245 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api:p_attribute51: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE51 );
246 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api:p_attribute52: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE52 );
247 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api:p_attribute53: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE53 );
248 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api:p_attribute54: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE54 );
249 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api:p_attribute55: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE55 );
250 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api:p_attribute56: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE56 );
251 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api:p_attribute57: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE57 );
252 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api:p_attribute58: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE58 );
253 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api:p_attribute59: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE59 );
254 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api:p_attribute60: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE60 );
255 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api:p_attribute61: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE61 );
256 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api:p_attribute62: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE62 );
257 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api:p_attribute63: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE63 );
258 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api:p_attribute64: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE64 );
259 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api:p_attribute65: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE65 );
260 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api:p_attribute66: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE66 );
261 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api:p_attribute67: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE67 );
262 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api:p_attribute68: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE68 );
263 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api:p_attribute69: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE69 );
264 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api:p_attribute70: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE70 );
265 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api:p_attribute71: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE71 );
266 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api:p_attribute72: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE72 );
267 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api:p_attribute73: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE73 );
268 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api:p_attribute74: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE74 );
269 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api:p_attribute75: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE75 );
270 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api:p_attribute76: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE76 );
271 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api:p_attribute77: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE77 );
272 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api:p_attribute78: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE78 );
273 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api:p_attribute79: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE79 );
274 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api:p_attribute80: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE80 );
275 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api:p_attribute81: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE81 );
276 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api:p_attribute82: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE82 );
277 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api:p_attribute83: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE83 );
278 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api:p_attribute84: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE84 );
279 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api:p_attribute85: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE85 );
280 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api:p_attribute86: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE86 );
281 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api:p_attribute87: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE87 );
282 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api:p_attribute88: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE88 );
283 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api:p_attribute89: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE89 );
284 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api:p_attribute90: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE90 );
285 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api:p_attribute91: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE91 );
289 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api:p_attribute95: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE95 );
286 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api:p_attribute92: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE92 );
287 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api:p_attribute93: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE93 );
288 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api:p_attribute94: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE94 );
290 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api:p_attribute96: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE96 );
291 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api:p_attribute97: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE97 );
292 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api:p_attribute98: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE98 );
293 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api:p_attribute99: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE99 );
294 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api:p_attribute100: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE100);
295 EXCEPTION
296 WHEN OTHERS THEN
297 --x_return_status := 'F';
298 --DBMS_OUTPUT.put_line('CN_COLLECTION_AIA_PUB.loadrow_om:exception in debug messages others: ' || '[ ' || SQLERRM(SQLCODE()) || ' ]');
299 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om:exception in debug messages others: ' || SQLERRM(SQLCODE()) );
300 end;
301
302 x_msg_data := p_aia_rec_tbl(l_counter).ORDER_NUMBER ;
303 l_update_check := -1;
304 l_update_flag := 'N';
305 l_salesrep_number := '_';
306 l_employee_number := '_';
307 l_salesrep_id := -1;
308 l_trans_seq_num := 0;
309
310
311 --Mandatory fields validation starts here
312 IF(p_aia_rec_tbl(l_counter).PROCESSED_DATE IS NULL or
313 p_aia_rec_tbl(l_counter).PROCESSED_DATE = FND_API.G_MISS_CHAR) THEN
314 x_return_status := 'F';
315 x_msg_count := 1;
316 l_error_msg := 'p_processed_date' || g_error_msg;
317 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api: l_error_msg: ' || l_error_msg);
318 RAISE CN_AIA_REQ_FIELD_NOT_SET_ERROR;
319 END IF;
320
321 IF(p_aia_rec_tbl(l_counter).ORDER_NUMBER IS NULL or
322 p_aia_rec_tbl(l_counter).ORDER_NUMBER = G_LOC_MISS_NUM) THEN
323 x_return_status := 'F';
324 x_msg_count := 1;
325 l_error_msg := 'p_order_number' || g_error_msg;
326 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api: l_error_msg: ' || l_error_msg);
327 RAISE CN_AIA_REQ_FIELD_NOT_SET_ERROR;
328 END IF;
329
330 IF(p_aia_rec_tbl(l_counter).BOOKED_DATE IS NULL or
331 p_aia_rec_tbl(l_counter).BOOKED_DATE = FND_API.G_MISS_CHAR) THEN
332 x_return_status := 'F';
333 x_msg_count := 1;
334 l_error_msg := 'p_booked_date' || g_error_msg;
335 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api: l_error_msg: ' || l_error_msg);
336 RAISE CN_AIA_REQ_FIELD_NOT_SET_ERROR;
337 END IF;
338
339 IF(p_aia_rec_tbl(l_counter).TRANSACTION_AMOUNT IS NULL or
340 p_aia_rec_tbl(l_counter).TRANSACTION_AMOUNT = G_LOC_MISS_NUM) THEN
341 x_return_status := 'F';
342 x_msg_count := 1;
343 l_error_msg := 'p_transaction_amount' || g_error_msg;
344 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api: l_error_msg: ' || l_error_msg);
345 RAISE CN_AIA_REQ_FIELD_NOT_SET_ERROR;
346 END IF;
347
348 IF(p_aia_rec_tbl(l_counter).SALESREP_ID IS NULL AND p_aia_rec_tbl(l_counter).EMPLOYEE_NUMBER IS NULL
349 AND p_aia_rec_tbl(l_counter).SALESREP_ID = G_LOC_MISS_NUM AND p_aia_rec_tbl(l_counter).EMPLOYEE_NUMBER = FND_API.G_MISS_CHAR) THEN
350 x_return_status := 'F';
351 x_msg_count := 1;
352 l_error_msg := 'Any one of p_salesrep_id or p_employee_number' || g_error_msg;
353 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api: l_error_msg: ' || l_error_msg);
354 RAISE CN_AIA_REQ_FIELD_NOT_SET_ERROR;
355 END IF;
356
357
358
359 IF(p_aia_rec_tbl(l_counter).EMPLOYEE_NUMBER IS NOT NULL and p_aia_rec_tbl(l_counter).EMPLOYEE_NUMBER <> FND_API.G_MISS_CHAR) THEN
360 FOR l_resnum_srcnum_cr IN get_res_num_from_src_num(p_aia_rec_tbl(l_counter).EMPLOYEE_NUMBER)
361 LOOP
362 l_salesrep_number := l_resnum_srcnum_cr.employee_number;
363 END LOOP;
364 --DBMS_OUTPUT.put_line('l_salesrep_number ' || l_salesrep_number);
365 FOR l_salesrep_resnum_cr IN get_salesrep_from_res_num(p_aia_rec_tbl(l_counter).EMPLOYEE_NUMBER)
366 LOOP
367 l_salesrep_id := l_salesrep_resnum_cr.salesrep_id;
368 END LOOP;
369 --DBMS_OUTPUT.put_line('l_salesrep_id ' || l_salesrep_id);
370 l_employee_number := p_aia_rec_tbl(l_counter).EMPLOYEE_NUMBER;
371 IF(l_salesrep_id = -1) THEN
372 l_salesrep_number := '_';
373 l_employee_number := '_';
374 FOR l_resnum_salesrep_cr IN get_res_num_from_salesrep(p_aia_rec_tbl(l_counter).SALESREP_ID)
375 LOOP
376 l_salesrep_number := l_resnum_salesrep_cr.employee_number;
377 END LOOP;
378 FOR l_srcnum_salesrep_cr IN get_src_num_from_salesrep(p_aia_rec_tbl(l_counter).SALESREP_ID)
379 LOOP
380 l_employee_number := l_srcnum_salesrep_cr.source_number;
381 END LOOP;
382 IF(l_employee_number <> '_') THEN
383 l_salesrep_id := p_aia_rec_tbl(l_counter).SALESREP_ID;
384 END IF;
385 END IF;
386 ELSE
387 FOR l_resnum_salesrep_cr IN get_res_num_from_salesrep(p_aia_rec_tbl(l_counter).SALESREP_ID)
388 LOOP
389 l_salesrep_number := l_resnum_salesrep_cr.employee_number;
390 END LOOP;
391 FOR l_srcnum_salesrep_cr IN get_src_num_from_salesrep(p_aia_rec_tbl(l_counter).SALESREP_ID)
392 LOOP
396 l_salesrep_id := p_aia_rec_tbl(l_counter).SALESREP_ID;
393 l_employee_number := l_srcnum_salesrep_cr.source_number;
394 END LOOP;
395 IF(l_employee_number <> '_') THEN
397 END IF;
398 END IF;
399
400 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api: l_salesrep_number: ' || l_salesrep_number);
401 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api: l_salesrep_id: ' || l_salesrep_id);
402 IF(l_salesrep_id = -1) THEN
403 x_return_status := 'F';
404 x_msg_count := 1;
405 l_error_msg := 'Please enter valid value for either of p_salesrep_id or p_employee_number ';
406 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api: l_error_msg: ' || l_error_msg);
407 RAISE CN_AIA_REQ_FIELD_NOT_SET_ERROR;
408 END IF;
409
410 FOR l_order_num_cr IN check_order_number(p_aia_rec_tbl(l_counter).ORDER_NUMBER)
411 LOOP
412 l_update_flag := l_order_num_cr.FLAG;
413 l_trans_seq_num := l_order_num_cr.TRANS_SEQ_ID;
414 END LOOP;
415 --DBMS_OUTPUT.put_line('CN_COLLECTION_AIA_PUB.loadrow_om api: l_update_flag: ' || l_update_flag);
416 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api: l_update_flag: ' || l_update_flag);
417 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api: l_trans_seq_num: ' || l_trans_seq_num);
418
419 IF(l_update_flag = 'N') THEN
420 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api: insert start ');
421 INSERT
422 INTO CN_COLLECTION_AIA
423 (
424 TRANS_SEQ_ID ,
425 SALESREP_ID ,
426 EMPLOYEE_NUMBER ,
427 PROCESSED_DATE ,
428 INVOICE_NUMBER ,
429 INVOICE_DATE ,
430 TRANSACTION_AMOUNT ,
431 TRANSACTION_CURRENCY_CODE,
432 TRX_TYPE ,
433 REVENUE_TYPE ,
434 SALESREP_NUMBER ,
435 ADJUST_COMMENTS ,
436 ADJUST_STATUS ,
437 SOURCE_DOC_ID ,
438 UPDATE_FLAG ,
439 SALES_CHANNEL ,
440 LINE_NUMBER ,
441 REASON_CODE ,
442 ATTRIBUTE_CATEGORY ,
443 ADJUST_DATE ,
444 ADJUSTED_BY ,
445 BILL_TO_ADDRESS_ID ,
446 SHIP_TO_ADDRESS_ID ,
447 BILL_TO_CONTACT_ID ,
448 SHIP_TO_CONTACT_ID ,
449 CUSTOMER_ID ,
450 INVENTORY_ITEM_ID ,
451 ORDER_NUMBER ,
452 BOOKED_DATE ,
453 SOURCE_TRX_NUMBER ,
454 DISCOUNT_PERCENTAGE ,
455 MARGIN_PERCENTAGE ,
456 EXCHANGE_RATE ,
457 TYPE ,
458 SOURCE_TRX_SALES_LINE_ID ,
459 ORG_ID ,
460 ATTRIBUTE1 ,
461 ATTRIBUTE2 ,
462 ATTRIBUTE3 ,
463 ATTRIBUTE4 ,
464 ATTRIBUTE5 ,
465 ATTRIBUTE6 ,
466 ATTRIBUTE7 ,
467 ATTRIBUTE8 ,
468 ATTRIBUTE9 ,
469 ATTRIBUTE10 ,
470 ATTRIBUTE11 ,
471 ATTRIBUTE12 ,
472 ATTRIBUTE13 ,
473 ATTRIBUTE14 ,
474 ATTRIBUTE15 ,
475 ATTRIBUTE16 ,
476 ATTRIBUTE17 ,
477 ATTRIBUTE18 ,
478 ATTRIBUTE19 ,
479 ATTRIBUTE20 ,
480 ATTRIBUTE21 ,
481 ATTRIBUTE22 ,
482 ATTRIBUTE23 ,
483 ATTRIBUTE24 ,
484 ATTRIBUTE25 ,
485 ATTRIBUTE26 ,
486 ATTRIBUTE27 ,
487 ATTRIBUTE28 ,
488 ATTRIBUTE29 ,
489 ATTRIBUTE30 ,
490 ATTRIBUTE31 ,
491 ATTRIBUTE32 ,
492 ATTRIBUTE33 ,
493 ATTRIBUTE34 ,
494 ATTRIBUTE35 ,
495 ATTRIBUTE36 ,
496 ATTRIBUTE37 ,
497 ATTRIBUTE38 ,
498 ATTRIBUTE39 ,
499 ATTRIBUTE40 ,
500 ATTRIBUTE41 ,
501 ATTRIBUTE42 ,
502 ATTRIBUTE43 ,
503 ATTRIBUTE44 ,
504 ATTRIBUTE45 ,
505 ATTRIBUTE46 ,
506 ATTRIBUTE47 ,
507 ATTRIBUTE48 ,
508 ATTRIBUTE49 ,
509 ATTRIBUTE50 ,
510 ATTRIBUTE51 ,
511 ATTRIBUTE52 ,
512 ATTRIBUTE53 ,
513 ATTRIBUTE54 ,
514 ATTRIBUTE55 ,
515 ATTRIBUTE56 ,
516 ATTRIBUTE57 ,
517 ATTRIBUTE58 ,
518 ATTRIBUTE59 ,
519 ATTRIBUTE60 ,
520 ATTRIBUTE61 ,
521 ATTRIBUTE62 ,
522 ATTRIBUTE63 ,
526 ATTRIBUTE67 ,
523 ATTRIBUTE64 ,
524 ATTRIBUTE65 ,
525 ATTRIBUTE66 ,
527 ATTRIBUTE68 ,
528 ATTRIBUTE69 ,
529 ATTRIBUTE70 ,
530 ATTRIBUTE71 ,
531 ATTRIBUTE72 ,
532 ATTRIBUTE73 ,
533 ATTRIBUTE74 ,
534 ATTRIBUTE75 ,
535 ATTRIBUTE76 ,
536 ATTRIBUTE77 ,
537 ATTRIBUTE78 ,
538 ATTRIBUTE79 ,
539 ATTRIBUTE80 ,
540 ATTRIBUTE81 ,
541 ATTRIBUTE82 ,
542 ATTRIBUTE83 ,
543 ATTRIBUTE84 ,
544 ATTRIBUTE85 ,
545 ATTRIBUTE86 ,
546 ATTRIBUTE87 ,
547 ATTRIBUTE88 ,
548 ATTRIBUTE89 ,
549 ATTRIBUTE90 ,
550 ATTRIBUTE91 ,
551 ATTRIBUTE92 ,
552 ATTRIBUTE93 ,
553 ATTRIBUTE94 ,
554 ATTRIBUTE95 ,
555 ATTRIBUTE96 ,
556 ATTRIBUTE97 ,
557 ATTRIBUTE98 ,
558 ATTRIBUTE99 ,
559 ATTRIBUTE100
560 )
561 VALUES
562 (
563 CN_COLLECTION_AIA_S.NextVal ,
564 l_salesrep_id ,
565 l_employee_number ,
566 to_date(p_aia_rec_tbl(l_counter).PROCESSED_DATE, 'dd/mm/yyyy hh24:mi:ss'),
567 p_aia_rec_tbl(l_counter).INVOICE_NUMBER ,
568 decode(p_aia_rec_tbl(l_counter).INVOICE_DATE, FND_API.G_MISS_CHAR, null, to_date(p_aia_rec_tbl(l_counter).INVOICE_DATE, 'dd/mm/yyyy hh24:mi:ss')),
569 p_aia_rec_tbl(l_counter).TRANSACTION_AMOUNT ,
570 p_aia_rec_tbl(l_counter).TRANSACTION_CURRENCY_CODE ,
571 'AIA_OM' ,
572 decode(p_aia_rec_tbl(l_counter).REVENUE_TYPE, FND_API.G_MISS_CHAR, 'REVENUE', p_aia_rec_tbl(l_counter).REVENUE_TYPE),
573 l_salesrep_number ,
574 p_aia_rec_tbl(l_counter).ADJUST_COMMENTS ,
575 'MANUAL' ,
576 p_aia_rec_tbl(l_counter).SOURCE_DOC_ID ,
577 l_update_flag ,
578 p_aia_rec_tbl(l_counter).SALES_CHANNEL ,
579 p_aia_rec_tbl(l_counter).LINE_NUMBER ,
580 p_aia_rec_tbl(l_counter).REASON_CODE ,
581 p_aia_rec_tbl(l_counter).ATTRIBUTE_CATEGORY ,
582 decode(p_aia_rec_tbl(l_counter).ADJUST_DATE, FND_API.G_MISS_CHAR, null, to_date(p_aia_rec_tbl(l_counter).ADJUST_DATE, 'dd/mm/yyyy hh24:mi:ss')),
583 p_aia_rec_tbl(l_counter).ADJUSTED_BY ,
584 p_aia_rec_tbl(l_counter).BILL_TO_ADDRESS_ID ,
585 p_aia_rec_tbl(l_counter).SHIP_TO_ADDRESS_ID ,
586 p_aia_rec_tbl(l_counter).BILL_TO_CONTACT_ID ,
587 p_aia_rec_tbl(l_counter).SHIP_TO_CONTACT_ID ,
588 p_aia_rec_tbl(l_counter).CUSTOMER_ID ,
589 p_aia_rec_tbl(l_counter).INVENTORY_ITEM_ID ,
590 p_aia_rec_tbl(l_counter).ORDER_NUMBER ,
591 to_date(p_aia_rec_tbl(l_counter).BOOKED_DATE, 'dd/mm/yyyy hh24:mi:ss'),
592 p_aia_rec_tbl(l_counter).SOURCE_TRX_NUMBER ,
593 p_aia_rec_tbl(l_counter).DISCOUNT_PERCENTAGE ,
594 p_aia_rec_tbl(l_counter).MARGIN_PERCENTAGE ,
595 p_aia_rec_tbl(l_counter).EXCHANGE_RATE ,
596 p_aia_rec_tbl(l_counter).TYPE ,
597 p_aia_rec_tbl(l_counter).SOURCE_TRX_SALES_LINE_ID ,
598 p_org_id ,
599 p_aia_rec_tbl(l_counter).ATTRIBUTE1 ,
600 p_aia_rec_tbl(l_counter).ATTRIBUTE2 ,
601 p_aia_rec_tbl(l_counter).ATTRIBUTE3 ,
602 p_aia_rec_tbl(l_counter).ATTRIBUTE4 ,
603 p_aia_rec_tbl(l_counter).ATTRIBUTE5 ,
604 p_aia_rec_tbl(l_counter).ATTRIBUTE6 ,
605 p_aia_rec_tbl(l_counter).ATTRIBUTE7 ,
606 p_aia_rec_tbl(l_counter).ATTRIBUTE8 ,
607 p_aia_rec_tbl(l_counter).ATTRIBUTE9 ,
608 p_aia_rec_tbl(l_counter).ATTRIBUTE10 ,
609 p_aia_rec_tbl(l_counter).ATTRIBUTE11 ,
610 p_aia_rec_tbl(l_counter).ATTRIBUTE12 ,
611 p_aia_rec_tbl(l_counter).ATTRIBUTE13 ,
612 p_aia_rec_tbl(l_counter).ATTRIBUTE14 ,
613 p_aia_rec_tbl(l_counter).ATTRIBUTE15 ,
614 p_aia_rec_tbl(l_counter).ATTRIBUTE16 ,
615 p_aia_rec_tbl(l_counter).ATTRIBUTE17 ,
616 p_aia_rec_tbl(l_counter).ATTRIBUTE18 ,
617 p_aia_rec_tbl(l_counter).ATTRIBUTE19 ,
618 p_aia_rec_tbl(l_counter).ATTRIBUTE20 ,
619 p_aia_rec_tbl(l_counter).ATTRIBUTE21 ,
620 p_aia_rec_tbl(l_counter).ATTRIBUTE22 ,
621 p_aia_rec_tbl(l_counter).ATTRIBUTE23 ,
622 p_aia_rec_tbl(l_counter).ATTRIBUTE24 ,
623 p_aia_rec_tbl(l_counter).ATTRIBUTE25 ,
627 p_aia_rec_tbl(l_counter).ATTRIBUTE29 ,
624 p_aia_rec_tbl(l_counter).ATTRIBUTE26 ,
625 p_aia_rec_tbl(l_counter).ATTRIBUTE27 ,
626 p_aia_rec_tbl(l_counter).ATTRIBUTE28 ,
628 p_aia_rec_tbl(l_counter).ATTRIBUTE30 ,
629 p_aia_rec_tbl(l_counter).ATTRIBUTE31 ,
630 p_aia_rec_tbl(l_counter).ATTRIBUTE32 ,
631 p_aia_rec_tbl(l_counter).ATTRIBUTE33 ,
632 p_aia_rec_tbl(l_counter).ATTRIBUTE34 ,
633 p_aia_rec_tbl(l_counter).ATTRIBUTE35 ,
634 p_aia_rec_tbl(l_counter).ATTRIBUTE36 ,
635 p_aia_rec_tbl(l_counter).ATTRIBUTE37 ,
636 p_aia_rec_tbl(l_counter).ATTRIBUTE38 ,
637 p_aia_rec_tbl(l_counter).ATTRIBUTE39 ,
638 p_aia_rec_tbl(l_counter).ATTRIBUTE40 ,
639 p_aia_rec_tbl(l_counter).ATTRIBUTE41 ,
640 p_aia_rec_tbl(l_counter).ATTRIBUTE42 ,
641 p_aia_rec_tbl(l_counter).ATTRIBUTE43 ,
642 p_aia_rec_tbl(l_counter).ATTRIBUTE44 ,
643 p_aia_rec_tbl(l_counter).ATTRIBUTE45 ,
644 p_aia_rec_tbl(l_counter).ATTRIBUTE46 ,
645 p_aia_rec_tbl(l_counter).ATTRIBUTE47 ,
646 p_aia_rec_tbl(l_counter).ATTRIBUTE48 ,
647 p_aia_rec_tbl(l_counter).ATTRIBUTE49 ,
648 p_aia_rec_tbl(l_counter).ATTRIBUTE50 ,
649 p_aia_rec_tbl(l_counter).ATTRIBUTE51 ,
650 p_aia_rec_tbl(l_counter).ATTRIBUTE52 ,
651 p_aia_rec_tbl(l_counter).ATTRIBUTE53 ,
652 p_aia_rec_tbl(l_counter).ATTRIBUTE54 ,
653 p_aia_rec_tbl(l_counter).ATTRIBUTE55 ,
654 p_aia_rec_tbl(l_counter).ATTRIBUTE56 ,
655 p_aia_rec_tbl(l_counter).ATTRIBUTE57 ,
656 p_aia_rec_tbl(l_counter).ATTRIBUTE58 ,
657 p_aia_rec_tbl(l_counter).ATTRIBUTE59 ,
658 p_aia_rec_tbl(l_counter).ATTRIBUTE60 ,
659 p_aia_rec_tbl(l_counter).ATTRIBUTE61 ,
660 p_aia_rec_tbl(l_counter).ATTRIBUTE62 ,
661 p_aia_rec_tbl(l_counter).ATTRIBUTE63 ,
662 p_aia_rec_tbl(l_counter).ATTRIBUTE64 ,
663 p_aia_rec_tbl(l_counter).ATTRIBUTE65 ,
664 p_aia_rec_tbl(l_counter).ATTRIBUTE66 ,
665 p_aia_rec_tbl(l_counter).ATTRIBUTE67 ,
666 p_aia_rec_tbl(l_counter).ATTRIBUTE68 ,
667 p_aia_rec_tbl(l_counter).ATTRIBUTE69 ,
668 p_aia_rec_tbl(l_counter).ATTRIBUTE70 ,
669 p_aia_rec_tbl(l_counter).ATTRIBUTE71 ,
670 p_aia_rec_tbl(l_counter).ATTRIBUTE72 ,
671 p_aia_rec_tbl(l_counter).ATTRIBUTE73 ,
672 p_aia_rec_tbl(l_counter).ATTRIBUTE74 ,
673 p_aia_rec_tbl(l_counter).ATTRIBUTE75 ,
674 p_aia_rec_tbl(l_counter).ATTRIBUTE76 ,
675 p_aia_rec_tbl(l_counter).ATTRIBUTE77 ,
676 p_aia_rec_tbl(l_counter).ATTRIBUTE78 ,
677 p_aia_rec_tbl(l_counter).ATTRIBUTE79 ,
678 p_aia_rec_tbl(l_counter).ATTRIBUTE80 ,
679 p_aia_rec_tbl(l_counter).ATTRIBUTE81 ,
680 p_aia_rec_tbl(l_counter).ATTRIBUTE82 ,
681 p_aia_rec_tbl(l_counter).ATTRIBUTE83 ,
682 p_aia_rec_tbl(l_counter).ATTRIBUTE84 ,
683 p_aia_rec_tbl(l_counter).ATTRIBUTE85 ,
684 p_aia_rec_tbl(l_counter).ATTRIBUTE86 ,
685 p_aia_rec_tbl(l_counter).ATTRIBUTE87 ,
686 p_aia_rec_tbl(l_counter).ATTRIBUTE88 ,
687 p_aia_rec_tbl(l_counter).ATTRIBUTE89 ,
688 p_aia_rec_tbl(l_counter).ATTRIBUTE90 ,
689 p_aia_rec_tbl(l_counter).ATTRIBUTE91 ,
690 p_aia_rec_tbl(l_counter).ATTRIBUTE92 ,
691 p_aia_rec_tbl(l_counter).ATTRIBUTE93 ,
692 p_aia_rec_tbl(l_counter).ATTRIBUTE94 ,
693 p_aia_rec_tbl(l_counter).ATTRIBUTE95 ,
694 p_aia_rec_tbl(l_counter).ATTRIBUTE96 ,
695 p_aia_rec_tbl(l_counter).ATTRIBUTE97 ,
696 p_aia_rec_tbl(l_counter).ATTRIBUTE98 ,
697 p_aia_rec_tbl(l_counter).ATTRIBUTE99 ,
698 p_aia_rec_tbl(l_counter).ATTRIBUTE100
699 );
700
701 IF fnd_api.to_boolean
702 (
703 p_commit
704 )
705 THEN
706 COMMIT;
707 END IF;
708
709 x_msg_count := 1;
710
711 debugmsg
712 (
713 'CN_COLLECTION_AIA_PUB.loadrow_om api: ' || 'INSERTED ORDER NUMBER' || ' '
714 || p_aia_rec_tbl(l_counter).ORDER_NUMBER
715 )
716 ;
717 ELSE
718 -- This step will check that if this transaction has already been collected then update flag should be 'Y'
719 -- else it should be 'N'. Here l_update_check is initialized to -1. If order number exists then it will go
720 -- to this else part and here it will check wether this has been collected. If yes then l_update_check = 'Y'
721 FOR l_not_trx_cr IN check_not_trx(l_trans_seq_num, p_org_id)
722 LOOP
723 l_update_check := l_order_num_cr.FLAG;
724 END LOOP;
725
726 debugmsg
730 ;
727 (
728 'CN_COLLECTION_AIA_PUB.loadrow_om api: l_update_check(in table cn_not_trx): ' || l_update_check
729 )
731 -- Since this order number has not been collected. so l_update_check = 'Y'
732 IF
733 (
734 l_update_check = -1
735 )
736 THEN
737 l_update_flag := 'N';
738 END IF;
739 debugmsg
740 (
741 'CN_COLLECTION_AIA_PUB.loadrow_om api: l_update_flag: ' || l_update_flag
742 )
743 ;
744 debugmsg
745 (
746 'CN_COLLECTION_AIA_PUB.loadrow_om api: update process start '
747 )
748 ;
749 UPDATE CN_COLLECTION_AIA
750 SET SALESREP_ID =l_salesrep_id ,
751 EMPLOYEE_NUMBER =l_employee_number ,
752 PROCESSED_DATE =to_date(p_aia_rec_tbl(l_counter).PROCESSED_DATE, 'dd/mm/yyyy hh24:mi:ss'),
753 INVOICE_DATE =decode(p_aia_rec_tbl(l_counter).INVOICE_DATE, FND_API.G_MISS_CHAR, null, to_date(p_aia_rec_tbl(l_counter).INVOICE_DATE, 'dd/mm/yyyy hh24:mi:ss')),
754 TRANSACTION_AMOUNT =p_aia_rec_tbl(l_counter).TRANSACTION_AMOUNT ,
755 TRANSACTION_CURRENCY_CODE =p_aia_rec_tbl(l_counter).TRANSACTION_CURRENCY_CODE ,
756 TRX_TYPE ='AIA_OM' ,
757 REVENUE_TYPE =decode(p_aia_rec_tbl(l_counter).REVENUE_TYPE, FND_API.G_MISS_CHAR, 'REVENUE',
758 p_aia_rec_tbl(l_counter).REVENUE_TYPE) ,
759 SALESREP_NUMBER =l_salesrep_number ,
760 ADJUST_COMMENTS =p_aia_rec_tbl(l_counter).ADJUST_COMMENTS ,
761 ADJUST_STATUS ='MANUAL' ,
762 SOURCE_DOC_ID =p_aia_rec_tbl(l_counter).SOURCE_DOC_ID ,
763 UPDATE_FLAG =l_update_flag ,
764 SALES_CHANNEL =p_aia_rec_tbl(l_counter).SALES_CHANNEL ,
765 LINE_NUMBER =p_aia_rec_tbl(l_counter).LINE_NUMBER ,
766 REASON_CODE =p_aia_rec_tbl(l_counter).REASON_CODE ,
767 ATTRIBUTE_CATEGORY =p_aia_rec_tbl(l_counter).ATTRIBUTE_CATEGORY ,
768 ADJUST_DATE =decode(p_aia_rec_tbl(l_counter).ADJUST_DATE, FND_API.G_MISS_CHAR, null, to_date(p_aia_rec_tbl(l_counter).ADJUST_DATE, 'dd/mm/yyyy hh24:mi:ss')),
769 ADJUSTED_BY =p_aia_rec_tbl(l_counter).ADJUSTED_BY ,
770 BILL_TO_ADDRESS_ID =p_aia_rec_tbl(l_counter).BILL_TO_ADDRESS_ID ,
771 SHIP_TO_ADDRESS_ID =p_aia_rec_tbl(l_counter).SHIP_TO_ADDRESS_ID ,
772 BILL_TO_CONTACT_ID =p_aia_rec_tbl(l_counter).BILL_TO_CONTACT_ID ,
773 SHIP_TO_CONTACT_ID =p_aia_rec_tbl(l_counter).SHIP_TO_CONTACT_ID ,
774 CUSTOMER_ID =p_aia_rec_tbl(l_counter).CUSTOMER_ID ,
775 INVENTORY_ITEM_ID =p_aia_rec_tbl(l_counter).INVENTORY_ITEM_ID ,
776 ORDER_NUMBER =p_aia_rec_tbl(l_counter).ORDER_NUMBER ,
777 BOOKED_DATE =to_date(p_aia_rec_tbl(l_counter).BOOKED_DATE, 'dd/mm/yyyy hh24:mi:ss'),
778 SOURCE_TRX_NUMBER =p_aia_rec_tbl(l_counter).SOURCE_TRX_NUMBER ,
779 DISCOUNT_PERCENTAGE =p_aia_rec_tbl(l_counter).DISCOUNT_PERCENTAGE ,
780 MARGIN_PERCENTAGE =p_aia_rec_tbl(l_counter).MARGIN_PERCENTAGE ,
781 EXCHANGE_RATE =p_aia_rec_tbl(l_counter).EXCHANGE_RATE ,
782 TYPE =p_aia_rec_tbl(l_counter).TYPE ,
783 SOURCE_TRX_SALES_LINE_ID =p_aia_rec_tbl(l_counter).SOURCE_TRX_SALES_LINE_ID ,
784 ORG_ID =p_org_id ,
785 ATTRIBUTE1 =p_aia_rec_tbl(l_counter).ATTRIBUTE1 ,
786 ATTRIBUTE2 =p_aia_rec_tbl(l_counter).ATTRIBUTE2 ,
787 ATTRIBUTE3 =p_aia_rec_tbl(l_counter).ATTRIBUTE3 ,
788 ATTRIBUTE4 =p_aia_rec_tbl(l_counter).ATTRIBUTE4 ,
789 ATTRIBUTE5 =p_aia_rec_tbl(l_counter).ATTRIBUTE5 ,
790 ATTRIBUTE6 =p_aia_rec_tbl(l_counter).ATTRIBUTE6 ,
791 ATTRIBUTE7 =p_aia_rec_tbl(l_counter).ATTRIBUTE7 ,
792 ATTRIBUTE8 =p_aia_rec_tbl(l_counter).ATTRIBUTE8 ,
793 ATTRIBUTE9 =p_aia_rec_tbl(l_counter).ATTRIBUTE9 ,
794 ATTRIBUTE10 =p_aia_rec_tbl(l_counter).ATTRIBUTE10 ,
795 ATTRIBUTE11 =p_aia_rec_tbl(l_counter).ATTRIBUTE11 ,
796 ATTRIBUTE12 =p_aia_rec_tbl(l_counter).ATTRIBUTE12 ,
797 ATTRIBUTE13 =p_aia_rec_tbl(l_counter).ATTRIBUTE13 ,
798 ATTRIBUTE14 =p_aia_rec_tbl(l_counter).ATTRIBUTE14 ,
799 ATTRIBUTE15 =p_aia_rec_tbl(l_counter).ATTRIBUTE15 ,
800 ATTRIBUTE16 =p_aia_rec_tbl(l_counter).ATTRIBUTE16 ,
801 ATTRIBUTE17 =p_aia_rec_tbl(l_counter).ATTRIBUTE17 ,
802 ATTRIBUTE18 =p_aia_rec_tbl(l_counter).ATTRIBUTE18 ,
803 ATTRIBUTE19 =p_aia_rec_tbl(l_counter).ATTRIBUTE19 ,
804 ATTRIBUTE20 =p_aia_rec_tbl(l_counter).ATTRIBUTE20 ,
805 ATTRIBUTE21 =p_aia_rec_tbl(l_counter).ATTRIBUTE21 ,
806 ATTRIBUTE22 =p_aia_rec_tbl(l_counter).ATTRIBUTE22 ,
807 ATTRIBUTE23 =p_aia_rec_tbl(l_counter).ATTRIBUTE23 ,
811 ATTRIBUTE27 =p_aia_rec_tbl(l_counter).ATTRIBUTE27 ,
808 ATTRIBUTE24 =p_aia_rec_tbl(l_counter).ATTRIBUTE24 ,
809 ATTRIBUTE25 =p_aia_rec_tbl(l_counter).ATTRIBUTE25 ,
810 ATTRIBUTE26 =p_aia_rec_tbl(l_counter).ATTRIBUTE26 ,
812 ATTRIBUTE28 =p_aia_rec_tbl(l_counter).ATTRIBUTE28 ,
813 ATTRIBUTE29 =p_aia_rec_tbl(l_counter).ATTRIBUTE29 ,
814 ATTRIBUTE30 =p_aia_rec_tbl(l_counter).ATTRIBUTE30 ,
815 ATTRIBUTE31 =p_aia_rec_tbl(l_counter).ATTRIBUTE31 ,
816 ATTRIBUTE32 =p_aia_rec_tbl(l_counter).ATTRIBUTE32 ,
817 ATTRIBUTE33 =p_aia_rec_tbl(l_counter).ATTRIBUTE33 ,
818 ATTRIBUTE34 =p_aia_rec_tbl(l_counter).ATTRIBUTE34 ,
819 ATTRIBUTE35 =p_aia_rec_tbl(l_counter).ATTRIBUTE35 ,
820 ATTRIBUTE36 =p_aia_rec_tbl(l_counter).ATTRIBUTE36 ,
821 ATTRIBUTE37 =p_aia_rec_tbl(l_counter).ATTRIBUTE37 ,
822 ATTRIBUTE38 =p_aia_rec_tbl(l_counter).ATTRIBUTE38 ,
823 ATTRIBUTE39 =p_aia_rec_tbl(l_counter).ATTRIBUTE39 ,
824 ATTRIBUTE40 =p_aia_rec_tbl(l_counter).ATTRIBUTE40 ,
825 ATTRIBUTE41 =p_aia_rec_tbl(l_counter).ATTRIBUTE41 ,
826 ATTRIBUTE42 =p_aia_rec_tbl(l_counter).ATTRIBUTE42 ,
827 ATTRIBUTE43 =p_aia_rec_tbl(l_counter).ATTRIBUTE43 ,
828 ATTRIBUTE44 =p_aia_rec_tbl(l_counter).ATTRIBUTE44 ,
829 ATTRIBUTE45 =p_aia_rec_tbl(l_counter).ATTRIBUTE45 ,
830 ATTRIBUTE46 =p_aia_rec_tbl(l_counter).ATTRIBUTE46 ,
831 ATTRIBUTE47 =p_aia_rec_tbl(l_counter).ATTRIBUTE47 ,
832 ATTRIBUTE48 =p_aia_rec_tbl(l_counter).ATTRIBUTE48 ,
833 ATTRIBUTE49 =p_aia_rec_tbl(l_counter).ATTRIBUTE49 ,
834 ATTRIBUTE50 =p_aia_rec_tbl(l_counter).ATTRIBUTE50 ,
835 ATTRIBUTE51 =p_aia_rec_tbl(l_counter).ATTRIBUTE51 ,
836 ATTRIBUTE52 =p_aia_rec_tbl(l_counter).ATTRIBUTE52 ,
837 ATTRIBUTE53 =p_aia_rec_tbl(l_counter).ATTRIBUTE53 ,
838 ATTRIBUTE54 =p_aia_rec_tbl(l_counter).ATTRIBUTE54 ,
839 ATTRIBUTE55 =p_aia_rec_tbl(l_counter).ATTRIBUTE55 ,
840 ATTRIBUTE56 =p_aia_rec_tbl(l_counter).ATTRIBUTE56 ,
841 ATTRIBUTE57 =p_aia_rec_tbl(l_counter).ATTRIBUTE57 ,
842 ATTRIBUTE58 =p_aia_rec_tbl(l_counter).ATTRIBUTE58 ,
843 ATTRIBUTE59 =p_aia_rec_tbl(l_counter).ATTRIBUTE59 ,
844 ATTRIBUTE60 =p_aia_rec_tbl(l_counter).ATTRIBUTE60 ,
845 ATTRIBUTE61 =p_aia_rec_tbl(l_counter).ATTRIBUTE61 ,
846 ATTRIBUTE62 =p_aia_rec_tbl(l_counter).ATTRIBUTE62 ,
847 ATTRIBUTE63 =p_aia_rec_tbl(l_counter).ATTRIBUTE63 ,
848 ATTRIBUTE64 =p_aia_rec_tbl(l_counter).ATTRIBUTE64 ,
849 ATTRIBUTE65 =p_aia_rec_tbl(l_counter).ATTRIBUTE65 ,
850 ATTRIBUTE66 =p_aia_rec_tbl(l_counter).ATTRIBUTE66 ,
851 ATTRIBUTE67 =p_aia_rec_tbl(l_counter).ATTRIBUTE67 ,
852 ATTRIBUTE68 =p_aia_rec_tbl(l_counter).ATTRIBUTE68 ,
853 ATTRIBUTE69 =p_aia_rec_tbl(l_counter).ATTRIBUTE69 ,
854 ATTRIBUTE70 =p_aia_rec_tbl(l_counter).ATTRIBUTE70 ,
855 ATTRIBUTE71 =p_aia_rec_tbl(l_counter).ATTRIBUTE71 ,
856 ATTRIBUTE72 =p_aia_rec_tbl(l_counter).ATTRIBUTE72 ,
857 ATTRIBUTE73 =p_aia_rec_tbl(l_counter).ATTRIBUTE73 ,
858 ATTRIBUTE74 =p_aia_rec_tbl(l_counter).ATTRIBUTE74 ,
859 ATTRIBUTE75 =p_aia_rec_tbl(l_counter).ATTRIBUTE75 ,
860 ATTRIBUTE76 =p_aia_rec_tbl(l_counter).ATTRIBUTE76 ,
861 ATTRIBUTE77 =p_aia_rec_tbl(l_counter).ATTRIBUTE77 ,
862 ATTRIBUTE78 =p_aia_rec_tbl(l_counter).ATTRIBUTE78 ,
863 ATTRIBUTE79 =p_aia_rec_tbl(l_counter).ATTRIBUTE79 ,
864 ATTRIBUTE80 =p_aia_rec_tbl(l_counter).ATTRIBUTE80 ,
865 ATTRIBUTE81 =p_aia_rec_tbl(l_counter).ATTRIBUTE81 ,
866 ATTRIBUTE82 =p_aia_rec_tbl(l_counter).ATTRIBUTE82 ,
867 ATTRIBUTE83 =p_aia_rec_tbl(l_counter).ATTRIBUTE83 ,
868 ATTRIBUTE84 =p_aia_rec_tbl(l_counter).ATTRIBUTE84 ,
869 ATTRIBUTE85 =p_aia_rec_tbl(l_counter).ATTRIBUTE85 ,
870 ATTRIBUTE86 =p_aia_rec_tbl(l_counter).ATTRIBUTE86 ,
871 ATTRIBUTE87 =p_aia_rec_tbl(l_counter).ATTRIBUTE87 ,
872 ATTRIBUTE88 =p_aia_rec_tbl(l_counter).ATTRIBUTE88 ,
873 ATTRIBUTE89 =p_aia_rec_tbl(l_counter).ATTRIBUTE89 ,
874 ATTRIBUTE90 =p_aia_rec_tbl(l_counter).ATTRIBUTE90 ,
875 ATTRIBUTE91 =p_aia_rec_tbl(l_counter).ATTRIBUTE91 ,
876 ATTRIBUTE92 =p_aia_rec_tbl(l_counter).ATTRIBUTE92 ,
880 ATTRIBUTE96 =p_aia_rec_tbl(l_counter).ATTRIBUTE96 ,
877 ATTRIBUTE93 =p_aia_rec_tbl(l_counter).ATTRIBUTE93 ,
878 ATTRIBUTE94 =p_aia_rec_tbl(l_counter).ATTRIBUTE94 ,
879 ATTRIBUTE95 =p_aia_rec_tbl(l_counter).ATTRIBUTE95 ,
881 ATTRIBUTE97 =p_aia_rec_tbl(l_counter).ATTRIBUTE97 ,
882 ATTRIBUTE98 =p_aia_rec_tbl(l_counter).ATTRIBUTE98 ,
883 ATTRIBUTE99 =p_aia_rec_tbl(l_counter).ATTRIBUTE99 ,
884 ATTRIBUTE100 =p_aia_rec_tbl(l_counter).ATTRIBUTE100
885 WHERE ORDER_NUMBER = p_aia_rec_tbl(l_counter).ORDER_NUMBER;
886
887 IF fnd_api.to_boolean(p_commit) THEN
888 COMMIT;
889 END IF;
890
891 x_msg_count := 1;
892 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api: ' || 'UPDATED ORDER NUMBER' || ' '
893 || p_aia_rec_tbl(l_counter).ORDER_NUMBER );
894 END IF;
895 END LOOP;
896
897 IF(x_msg_data = '_') THEN
898 x_return_status := 'F';
899 END IF;
900
901 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api: x_return_status: ' || x_return_status);
902 EXCEPTION
903 WHEN CN_AIA_REQ_FIELD_NOT_SET_ERROR THEN
904 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api:exception: CN_AIA_REQ_FIELD_NOT_SET_ERROR: ');
905 raise_application_error (-20001,l_error_msg);
906 WHEN OTHERS THEN
907 x_return_status := 'F';
908 x_msg_count := 1;
909 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api:exception others: ' || SQLERRM(SQLCODE()) );
910 raise_application_error (-20002,SQLERRM(SQLCODE()));
911 -- DBMS_OUTPUT.put_line('[ ' || SQLERRM(SQLCODE()) || ' ]');
912 END loadrow_om;
913
914
915 -- API name : loadrow
916 -- Type : Public.
917 -- Pre-reqs :
918 -- Usage :
919 --+
920 -- Desc :
921 --
922 --
923 --+
924 -- Parameters :
925 -- IN : p_api_version NUMBER Require
926 -- p_init_msg_list VARCHAR2 Optional (FND_API.G_FALSE)
927 -- p_commit VARCHAR2 Optional (FND_API.G_FALSE)
928 --
929 -- OUT : x_return_status VARCHAR2(1)
930 -- x_msg_count NUMBER
931 -- x_msg_data VARCHAR2(2000)
932 --
933 --
934 --
935 -- +
936 --+
937 -- Version : Current version 1.0
938 -- Initial version 1.0
939 --+
940 -- Notes :
941 --+
942 -- End of comments
943 PROCEDURE loadrow
944 (
945 p_api_version IN NUMBER,
946 p_init_msg_list IN VARCHAR2:= FND_API.G_FALSE,
947 p_commit IN VARCHAR2:= FND_API.G_FALSE,
948 p_aia_rec_tbl IN CN_COLLECTION_AIA_PUB.aia_rec_tbl_type,
949 p_org_id IN NUMBER,
950 p_aia_error_rec_tbl OUT nocopy CN_COLLECTION_AIA_PUB.aia_error_rec_tbl_type,
951 x_msg_count OUT nocopy NUMBER,
952 x_msg_data OUT nocopy VARCHAR2,
953 x_return_status OUT nocopy VARCHAR2 )
954 IS
955 l_api_name CONSTANT VARCHAR2(30) := 'loadrow';
956 l_api_version CONSTANT NUMBER :=1.0;
957 l_error_msg VARCHAR(240);
958 --x_org_id NUMBER;
959 l_update_flag VARCHAR2(10) := 'N';
960 l_salesrep_number VARCHAR2(30) := '_';
961 l_employee_number VARCHAR2(30) := '_';
962 l_salesrep_id NUMBER := -1;
963 l_trans_seq_num NUMBER := 0;
964 l_update_check NUMBER := -1;
965 l_counter NUMBER;
966 l_invoice_date DATE := sysdate;
967 l_trx_type VARCHAR2(30) := '_';
968 l_error_count NUMBER := 1;
969 l_tot_error_count NUMBER := 0;
970
971
972 CURSOR get_res_num_from_src_num(p_employee_number varchar2)
973 IS
974 SELECT employee_number
975 FROM cn_salesreps
976 WHERE resource_id IN
977 (SELECT resource_id
978 FROM jtf_rs_resource_extns
979 WHERE source_number = p_employee_number
980 );
981
982 CURSOR get_salesrep_from_res_num(p_employee_number varchar2)
983 IS
984 SELECT salesrep_id
985 FROM cn_salesreps
986 WHERE resource_id IN
987 (SELECT resource_id
988 FROM jtf_rs_resource_extns
989 WHERE source_number = p_employee_number
990 );
991
992 CURSOR get_res_num_from_salesrep(p_salesrep_id number)
993 IS
994 SELECT employee_number FROM cn_salesreps WHERE salesrep_id = p_salesrep_id;
995
996 CURSOR get_src_num_from_salesrep(p_salesrep_id number)
997 IS
998 SELECT source_number
999 FROM jtf_rs_resource_extns
1000 WHERE resource_id IN
1001 ( SELECT resource_id FROM cn_salesreps WHERE salesrep_id = p_salesrep_id
1002 ) ;
1003
1004 CURSOR check_invoice_number(p_invoice_number varchar2)
1005 IS
1006 SELECT 'Y' FLAG,
1007 TRANS_SEQ_ID
1008 FROM CN_COLLECTION_AIA
1009 WHERE INVOICE_NUMBER = p_invoice_number
1010 AND rownum = 1;
1011
1012 CURSOR check_not_trx(l_trans_seq_num number,p_org_id number)
1013 IS
1014 SELECT 1 FLAG
1015 FROM cn_not_trx
1016 WHERE source_trx_line_id = l_trans_seq_num --*** Line.Primary_Key
1017 AND event_id = -1020
1018 AND org_id = p_org_id;
1019
1020 l_resnum_srcnum_cr get_res_num_from_src_num%ROWTYPE;
1021 l_salesrep_resnum_cr get_salesrep_from_res_num%ROWTYPE;
1022 l_resnum_salesrep_cr get_res_num_from_salesrep%ROWTYPE;
1023 l_srcnum_salesrep_cr get_src_num_from_salesrep%ROWTYPE;
1024 l_invoice_num_cr check_invoice_number%ROWTYPE;
1028 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api: start ');
1025 l_not_trx_cr check_not_trx%ROWTYPE;
1026
1027 BEGIN
1029 x_msg_data := '_';
1030 x_msg_count := 0;
1031 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api: l_api_version: ' || l_api_version);
1032 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api: l_api_name: ' || l_api_name);
1033 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api: G_PKG_NAME: ' || G_PKG_NAME);
1034 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api:p_org_id: ' || p_org_id);
1035 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api: p_api_version: ' || p_api_version);
1036
1037 -- Initialize API return status to success
1038 x_return_status := FND_API.G_RET_STS_SUCCESS;
1039
1040 -- Standard call to check for call compatibility.
1041 /*IF NOT FND_API.Compatible_API_Call ( l_api_version , p_api_version , l_api_name , G_PKG_NAME ) THEN
1042 x_return_status := 'F';
1043 x_msg_count := 1;
1044 l_error_msg := 'p_api_version' || g_error_msg;
1045 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api: l_error_msg: ' || l_error_msg);
1046 END IF; */
1047
1048 IF(p_org_id IS NULL) THEN
1049 x_return_status := 'F';
1050 x_msg_count := 1;
1051 --l_error_msg := 'p_org_id' || g_error_msg;
1052 fnd_message.set_name('CN', 'CN_AIA_REQ_FIELD_ERROR_MSG');
1053 fnd_message.set_token('FIELD','p_org_id');
1054 l_error_msg := fnd_message.get;
1055 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api: l_error_msg: ' || l_error_msg);
1056 RAISE CN_AIA_REQ_FIELD_NOT_SET_ERROR;
1057 END IF;
1058
1059 -- Codes start here
1060 --x_org_id := mo_global.get_current_org_id;
1061 --DBMS_OUTPUT.put_line('p_org_id ' || p_org_id);
1062 mo_global.set_policy_context('S',p_org_id);
1063
1064 -- Initialize message list if p_init_msg_list is set to TRUE.
1065 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1066 FND_MSG_PUB.initialize;
1067 END IF;
1068
1069 IF p_aia_rec_tbl.COUNT = 0
1070 THEN
1071 x_return_status := 'F';
1072 x_msg_count := 1;
1073 --l_error_msg := 'p_aia_rec_tbl' || g_error_msg;
1074 fnd_message.set_name('CN', 'CN_AIA_REQ_FIELD_ERROR_MSG');
1075 fnd_message.set_token('FIELD','p_aia_rec_tbl');
1076 l_error_msg := fnd_message.get;
1077 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api: l_error_msg: ' || l_error_msg);
1078 --RAISE CN_AIA_REQ_FIELD_NOT_SET_ERROR;
1079 RETURN;
1080 END IF;
1081
1082 l_trx_type := p_aia_rec_tbl(1).TRX_TYPE;
1083 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api:l_trx_type: ' || l_trx_type);
1084
1085 IF(l_trx_type = 'AIA_OM') THEN
1086 loadrow_om
1087 (
1088 p_api_version => p_api_version ,
1089 p_init_msg_list => p_init_msg_list,
1090 p_commit => p_commit,
1091 p_aia_rec_tbl => p_aia_rec_tbl,
1092 p_org_id => p_org_id,
1093 x_msg_count => x_msg_count,
1094 x_msg_data => x_msg_data,
1095 x_return_status => x_return_status
1096 );
1097 ELSE
1098 FOR l_counter IN 1 .. p_aia_rec_tbl.COUNT
1099 LOOP
1100 begin
1101 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api: p_salesrep_id: ' || p_aia_rec_tbl(l_counter).SALESREP_ID);
1102 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api: p_employee_number: ' || p_aia_rec_tbl(l_counter).EMPLOYEE_NUMBER);
1103 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api: p_processed_date: ' || p_aia_rec_tbl(l_counter).PROCESSED_DATE);
1104 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api: p_invoice_number: ' || p_aia_rec_tbl(l_counter).INVOICE_NUMBER);
1105 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api: p_invoice_date: ' || p_aia_rec_tbl(l_counter).INVOICE_DATE);
1106 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api: p_transaction_amount: ' || p_aia_rec_tbl(l_counter).TRANSACTION_AMOUNT);
1107 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api: p_transaction_currency_code: ' || p_aia_rec_tbl(l_counter).TRANSACTION_CURRENCY_CODE);
1108 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api: p_trx_type: ' || p_aia_rec_tbl(l_counter).TRX_TYPE);
1109 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api: p_revenue_type: ' || p_aia_rec_tbl(l_counter).REVENUE_TYPE);
1110 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api: p_adjust_comments: ' || p_aia_rec_tbl(l_counter).ADJUST_COMMENTS);
1111 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api: p_source_doc_id: ' || p_aia_rec_tbl(l_counter).SOURCE_DOC_ID);
1112 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api:p_attribute1 : ' || p_aia_rec_tbl(l_counter).ATTRIBUTE1 );
1113 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api:p_attribute2 : ' || p_aia_rec_tbl(l_counter).ATTRIBUTE2 );
1114 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api:p_attribute3 : ' || p_aia_rec_tbl(l_counter).ATTRIBUTE3 );
1115 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api:p_attribute4 : ' || p_aia_rec_tbl(l_counter).ATTRIBUTE4 );
1116 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api:p_attribute5 : ' || p_aia_rec_tbl(l_counter).ATTRIBUTE5 );
1117 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api:p_attribute6 : ' || p_aia_rec_tbl(l_counter).ATTRIBUTE6 );
1118 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api:p_attribute7 : ' || p_aia_rec_tbl(l_counter).ATTRIBUTE7 );
1119 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api:p_attribute8 : ' || p_aia_rec_tbl(l_counter).ATTRIBUTE8 );
1120 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api:p_attribute9 : ' || p_aia_rec_tbl(l_counter).ATTRIBUTE9 );
1121 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api:p_attribute10: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE10 );
1122 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api:p_attribute11: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE11 );
1123 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api:p_attribute12: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE12 );
1124 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api:p_attribute13: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE13 );
1125 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api:p_attribute14: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE14 );
1126 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api:p_attribute15: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE15 );
1130 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api:p_attribute19: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE19 );
1127 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api:p_attribute16: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE16 );
1128 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api:p_attribute17: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE17 );
1129 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api:p_attribute18: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE18 );
1131 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api:p_attribute20: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE20 );
1132 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api:p_attribute21: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE21 );
1133 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api:p_attribute22: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE22 );
1134 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api:p_attribute23: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE23 );
1135 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api:p_attribute24: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE24 );
1136 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api:p_attribute25: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE25 );
1137 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api:p_attribute26: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE26 );
1138 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api:p_attribute27: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE27 );
1139 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api:p_attribute28: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE28 );
1140 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api:p_attribute29: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE29 );
1141 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api:p_attribute30: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE30 );
1142 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api:p_attribute31: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE31 );
1143 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api:p_attribute32: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE32 );
1144 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api:p_attribute33: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE33 );
1145 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api:p_attribute34: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE34 );
1146 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api:p_attribute35: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE35 );
1147 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api:p_attribute36: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE36 );
1148 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api:p_attribute37: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE37 );
1149 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api:p_attribute38: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE38 );
1150 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api:p_attribute39: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE39 );
1151 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api:p_attribute40: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE40 );
1152 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api:p_attribute41: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE41 );
1153 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api:p_attribute42: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE42 );
1154 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api:p_attribute43: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE43 );
1155 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api:p_attribute44: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE44 );
1156 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api:p_attribute45: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE45 );
1157 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api:p_attribute46: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE46 );
1158 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api:p_attribute47: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE47 );
1159 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api:p_attribute48: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE48 );
1160 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api:p_attribute49: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE49 );
1161 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api:p_attribute50: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE50 );
1162 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api:p_attribute51: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE51 );
1163 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api:p_attribute52: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE52 );
1164 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api:p_attribute53: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE53 );
1165 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api:p_attribute54: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE54 );
1166 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api:p_attribute55: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE55 );
1167 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api:p_attribute56: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE56 );
1168 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api:p_attribute57: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE57 );
1169 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api:p_attribute58: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE58 );
1170 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api:p_attribute59: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE59 );
1171 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api:p_attribute60: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE60 );
1172 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api:p_attribute61: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE61 );
1173 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api:p_attribute62: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE62 );
1174 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api:p_attribute63: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE63 );
1175 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api:p_attribute64: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE64 );
1176 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api:p_attribute65: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE65 );
1177 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api:p_attribute66: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE66 );
1178 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api:p_attribute67: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE67 );
1179 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api:p_attribute68: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE68 );
1180 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api:p_attribute69: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE69 );
1181 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api:p_attribute70: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE70 );
1182 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api:p_attribute71: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE71 );
1183 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api:p_attribute72: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE72 );
1184 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api:p_attribute73: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE73 );
1188 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api:p_attribute77: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE77 );
1185 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api:p_attribute74: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE74 );
1186 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api:p_attribute75: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE75 );
1187 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api:p_attribute76: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE76 );
1189 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api:p_attribute78: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE78 );
1190 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api:p_attribute79: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE79 );
1191 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api:p_attribute80: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE80 );
1192 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api:p_attribute81: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE81 );
1193 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api:p_attribute82: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE82 );
1194 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api:p_attribute83: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE83 );
1195 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api:p_attribute84: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE84 );
1196 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api:p_attribute85: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE85 );
1197 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api:p_attribute86: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE86 );
1198 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api:p_attribute87: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE87 );
1199 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api:p_attribute88: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE88 );
1200 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api:p_attribute89: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE89 );
1201 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api:p_attribute90: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE90 );
1202 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api:p_attribute91: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE91 );
1203 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api:p_attribute92: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE92 );
1204 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api:p_attribute93: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE93 );
1205 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api:p_attribute94: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE94 );
1206 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api:p_attribute95: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE95 );
1207 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api:p_attribute96: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE96 );
1211 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api:p_attribute100: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE100);
1208 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api:p_attribute97: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE97 );
1209 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api:p_attribute98: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE98 );
1210 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api:p_attribute99: ' || p_aia_rec_tbl(l_counter).ATTRIBUTE99 );
1212 EXCEPTION
1213 WHEN OTHERS THEN
1214 --x_return_status := 'F';
1215 --DBMS_OUTPUT.put_line('CN_COLLECTION_AIA_PUB.loadrow:exception in debug messages others: ' || '[ ' || SQLERRM(SQLCODE()) || ' ]');
1216 debugmsg('CN_COLLECTION_AIA_PUB.loadrow:exception in debug messages others: ' || SQLERRM(SQLCODE()) );
1217 end;
1218
1219 x_msg_data := p_aia_rec_tbl(l_counter).INVOICE_NUMBER ;
1220 l_update_check := -1;
1221 l_update_flag := 'N';
1222 l_salesrep_number := '_';
1223 l_employee_number := '_';
1224 l_salesrep_id := -1;
1225 l_trans_seq_num := 0;
1226 l_error_count := 1;
1227
1228
1229 --Mandatory fields validation starts here
1230 IF(p_aia_rec_tbl(l_counter).PROCESSED_DATE IS NULL or
1231 p_aia_rec_tbl(l_counter).PROCESSED_DATE = FND_API.G_MISS_CHAR) THEN
1232 x_return_status := 'F';
1233 x_msg_count := 1;
1234 l_tot_error_count := l_tot_error_count + 1;
1235 --l_error_msg := 'p_processed_date' || g_error_msg;
1236 fnd_message.set_name('CN', 'CN_AIA_REQ_FIELD_ERROR_MSG');
1237 fnd_message.set_token('FIELD','p_processed_date');
1241 p_aia_error_rec_tbl(l_tot_error_count).error_desc := l_error_msg;
1238 l_error_msg := fnd_message.get;
1239 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api: l_error_msg: ' || l_error_msg);
1240 p_aia_error_rec_tbl(l_tot_error_count).invoice_number := x_msg_data;
1242 l_error_count := l_error_count + 1;
1243 END IF;
1244
1245 IF(p_aia_rec_tbl(l_counter).INVOICE_NUMBER IS NULL or
1246 p_aia_rec_tbl(l_counter).INVOICE_NUMBER = FND_API.G_MISS_CHAR) THEN
1247 x_return_status := 'F';
1248 x_msg_count := 1;
1249 l_tot_error_count := l_tot_error_count + 1;
1250 --l_error_msg := 'p_invoice_number' || g_error_msg;
1251 fnd_message.set_name('CN', 'CN_AIA_REQ_FIELD_ERROR_MSG');
1252 fnd_message.set_token('FIELD','p_invoice_number');
1253 l_error_msg := fnd_message.get;
1254 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api: l_error_msg: ' || l_error_msg);
1255 p_aia_error_rec_tbl(l_tot_error_count).invoice_number := x_msg_data;
1256 p_aia_error_rec_tbl(l_tot_error_count).error_desc := l_error_msg;
1257 l_error_count := l_error_count +1;
1258 END IF;
1259
1260 IF(p_aia_rec_tbl(l_counter).INVOICE_DATE IS NULL or
1261 p_aia_rec_tbl(l_counter).INVOICE_DATE = FND_API.G_MISS_CHAR) THEN
1262 x_return_status := 'F';
1263 x_msg_count := 1;
1264 l_tot_error_count := l_tot_error_count + 1;
1265 --l_error_msg := 'p_invoice_date' || g_error_msg;
1266 fnd_message.set_name('CN', 'CN_AIA_REQ_FIELD_ERROR_MSG');
1267 fnd_message.set_token('FIELD','p_invoice_date');
1268 l_error_msg := fnd_message.get;
1269 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api: l_error_msg: ' || l_error_msg);
1270 p_aia_error_rec_tbl(l_tot_error_count).invoice_number := x_msg_data;
1271 p_aia_error_rec_tbl(l_tot_error_count).error_desc := l_error_msg;
1272 l_error_count := l_error_count +1;
1273 END IF;
1274
1275 IF(p_aia_rec_tbl(l_counter).TRANSACTION_AMOUNT IS NULL or
1276 p_aia_rec_tbl(l_counter).TRANSACTION_AMOUNT = G_LOC_MISS_NUM) THEN
1277 x_return_status := 'F';
1278 x_msg_count := 1;
1279 l_tot_error_count := l_tot_error_count + 1;
1280 --l_error_msg := 'p_transaction_amount' || g_error_msg;
1281 fnd_message.set_name('CN', 'CN_AIA_REQ_FIELD_ERROR_MSG');
1282 fnd_message.set_token('FIELD','p_transaction_amount');
1283 l_error_msg := fnd_message.get;
1284 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api: l_error_msg: ' || l_error_msg);
1285 p_aia_error_rec_tbl(l_tot_error_count).invoice_number := x_msg_data;
1286 p_aia_error_rec_tbl(l_tot_error_count).error_desc := l_error_msg;
1287 l_error_count := l_error_count +1;
1288 END IF;
1289
1290 IF(p_aia_rec_tbl(l_counter).SALESREP_ID IS NULL AND p_aia_rec_tbl(l_counter).EMPLOYEE_NUMBER IS NULL
1291 AND p_aia_rec_tbl(l_counter).SALESREP_ID = G_LOC_MISS_NUM AND p_aia_rec_tbl(l_counter).EMPLOYEE_NUMBER = FND_API.G_MISS_CHAR) THEN
1292 x_return_status := 'F';
1293 x_msg_count := 1;
1294 l_tot_error_count := l_tot_error_count + 1;
1295 --l_error_msg := 'Any one of p_salesrep_id or p_employee_number' || g_error_msg;
1296 fnd_message.set_name('CN', 'CN_AIA_SR_EMP_FIELD_ERROR_MSG');
1297 l_error_msg := fnd_message.get;
1298 debugmsg('CN_COLLECTION_AIA_PUB.loadrow_om api: l_error_msg: ' || l_error_msg);
1299 p_aia_error_rec_tbl(l_tot_error_count).invoice_number := x_msg_data;
1300 p_aia_error_rec_tbl(l_tot_error_count).error_desc := l_error_msg;
1301 l_error_count := l_error_count +1;
1302 END IF;
1303
1304
1305
1306 IF(p_aia_rec_tbl(l_counter).EMPLOYEE_NUMBER IS NOT NULL and p_aia_rec_tbl(l_counter).EMPLOYEE_NUMBER <> FND_API.G_MISS_CHAR) THEN
1307 FOR l_resnum_srcnum_cr IN get_res_num_from_src_num(p_aia_rec_tbl(l_counter).EMPLOYEE_NUMBER)
1308 LOOP
1309 l_salesrep_number := l_resnum_srcnum_cr.employee_number;
1310 END LOOP;
1311 --DBMS_OUTPUT.put_line('l_salesrep_number ' || l_salesrep_number);
1312 FOR l_salesrep_resnum_cr IN get_salesrep_from_res_num(p_aia_rec_tbl(l_counter).EMPLOYEE_NUMBER)
1313 LOOP
1314 l_salesrep_id := l_salesrep_resnum_cr.salesrep_id;
1315 END LOOP;
1316 --DBMS_OUTPUT.put_line('l_salesrep_id ' || l_salesrep_id);
1317 l_employee_number := p_aia_rec_tbl(l_counter).EMPLOYEE_NUMBER;
1318 IF(l_salesrep_id = -1) THEN
1319 l_salesrep_number := '_';
1320 l_employee_number := '_';
1321 FOR l_resnum_salesrep_cr IN get_res_num_from_salesrep(p_aia_rec_tbl(l_counter).SALESREP_ID)
1322 LOOP
1323 l_salesrep_number := l_resnum_salesrep_cr.employee_number;
1324 END LOOP;
1325 FOR l_srcnum_salesrep_cr IN get_src_num_from_salesrep(p_aia_rec_tbl(l_counter).SALESREP_ID)
1326 LOOP
1327 l_employee_number := l_srcnum_salesrep_cr.source_number;
1328 END LOOP;
1329 IF(l_employee_number <> '_') THEN
1330 l_salesrep_id := p_aia_rec_tbl(l_counter).SALESREP_ID;
1331 END IF;
1332 END IF;
1333 ELSE
1334 FOR l_resnum_salesrep_cr IN get_res_num_from_salesrep(p_aia_rec_tbl(l_counter).SALESREP_ID)
1335 LOOP
1336 l_salesrep_number := l_resnum_salesrep_cr.employee_number;
1337 END LOOP;
1338 FOR l_srcnum_salesrep_cr IN get_src_num_from_salesrep(p_aia_rec_tbl(l_counter).SALESREP_ID)
1339 LOOP
1340 l_employee_number := l_srcnum_salesrep_cr.source_number;
1341 END LOOP;
1342 IF(l_employee_number <> '_') THEN
1343 l_salesrep_id := p_aia_rec_tbl(l_counter).SALESREP_ID;
1344 END IF;
1345 END IF;
1346
1350 x_return_status := 'F';
1347 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api: l_salesrep_number: ' || l_salesrep_number);
1348 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api: l_salesrep_id: ' || l_salesrep_id);
1349 IF(l_salesrep_id = -1) THEN
1351 x_msg_count := 1;
1352 l_tot_error_count := l_tot_error_count + 1;
1353 --l_error_msg := 'Please enter valid value for either of p_salesrep_id or p_employee_number. ';
1354 fnd_message.set_name('CN', 'CN_AIA_SR_EMP_FIELD_ERROR_MSG');
1355 l_error_msg := fnd_message.get;
1356 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api: l_error_msg: ' || l_error_msg);
1357 p_aia_error_rec_tbl(l_tot_error_count).invoice_number := x_msg_data;
1358 p_aia_error_rec_tbl(l_tot_error_count).error_desc := l_error_msg;
1359 l_error_count := l_error_count +1;
1360 END IF;
1361
1362 if(l_error_count <= 1) Then
1363 --CONTINUE;
1364 --End If;
1365
1366 FOR l_invoice_num_cr IN check_invoice_number(p_aia_rec_tbl(l_counter).INVOICE_NUMBER)
1367 LOOP
1368 l_update_flag := l_invoice_num_cr.FLAG;
1369 l_trans_seq_num := l_invoice_num_cr.TRANS_SEQ_ID;
1370 END LOOP;
1371 --DBMS_OUTPUT.put_line('CN_COLLECTION_AIA_PUB.loadrow api: l_update_flag: ' || l_update_flag);
1372 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api: l_update_flag: ' || l_update_flag);
1373 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api: l_trans_seq_num: ' || l_trans_seq_num);
1374
1375 IF(l_update_flag = 'N') THEN
1376 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api: insert start ');
1377 INSERT
1378 INTO CN_COLLECTION_AIA
1379 (
1380 TRANS_SEQ_ID ,
1381 SALESREP_ID ,
1382 EMPLOYEE_NUMBER ,
1383 PROCESSED_DATE ,
1384 INVOICE_NUMBER ,
1385 INVOICE_DATE ,
1386 TRANSACTION_AMOUNT ,
1387 TRANSACTION_CURRENCY_CODE,
1388 TRX_TYPE ,
1389 REVENUE_TYPE ,
1390 SALESREP_NUMBER ,
1391 ADJUST_COMMENTS ,
1392 ADJUST_STATUS ,
1393 SOURCE_DOC_ID ,
1394 UPDATE_FLAG ,
1395 ORG_ID ,
1396 ATTRIBUTE1 ,
1397 ATTRIBUTE2 ,
1398 ATTRIBUTE3 ,
1399 ATTRIBUTE4 ,
1400 ATTRIBUTE5 ,
1401 ATTRIBUTE6 ,
1402 ATTRIBUTE7 ,
1403 ATTRIBUTE8 ,
1404 ATTRIBUTE9 ,
1405 ATTRIBUTE10 ,
1406 ATTRIBUTE11 ,
1407 ATTRIBUTE12 ,
1408 ATTRIBUTE13 ,
1409 ATTRIBUTE14 ,
1410 ATTRIBUTE15 ,
1411 ATTRIBUTE16 ,
1412 ATTRIBUTE17 ,
1413 ATTRIBUTE18 ,
1414 ATTRIBUTE19 ,
1415 ATTRIBUTE20 ,
1416 ATTRIBUTE21 ,
1417 ATTRIBUTE22 ,
1418 ATTRIBUTE23 ,
1419 ATTRIBUTE24 ,
1420 ATTRIBUTE25 ,
1421 ATTRIBUTE26 ,
1422 ATTRIBUTE27 ,
1423 ATTRIBUTE28 ,
1424 ATTRIBUTE29 ,
1425 ATTRIBUTE30 ,
1426 ATTRIBUTE31 ,
1427 ATTRIBUTE32 ,
1428 ATTRIBUTE33 ,
1429 ATTRIBUTE34 ,
1430 ATTRIBUTE35 ,
1431 ATTRIBUTE36 ,
1432 ATTRIBUTE37 ,
1433 ATTRIBUTE38 ,
1434 ATTRIBUTE39 ,
1435 ATTRIBUTE40 ,
1436 ATTRIBUTE41 ,
1437 ATTRIBUTE42 ,
1438 ATTRIBUTE43 ,
1439 ATTRIBUTE44 ,
1440 ATTRIBUTE45 ,
1441 ATTRIBUTE46 ,
1442 ATTRIBUTE47 ,
1443 ATTRIBUTE48 ,
1444 ATTRIBUTE49 ,
1445 ATTRIBUTE50 ,
1446 ATTRIBUTE51 ,
1447 ATTRIBUTE52 ,
1448 ATTRIBUTE53 ,
1449 ATTRIBUTE54 ,
1450 ATTRIBUTE55 ,
1451 ATTRIBUTE56 ,
1452 ATTRIBUTE57 ,
1453 ATTRIBUTE58 ,
1454 ATTRIBUTE59 ,
1455 ATTRIBUTE60 ,
1456 ATTRIBUTE61 ,
1457 ATTRIBUTE62 ,
1458 ATTRIBUTE63 ,
1459 ATTRIBUTE64 ,
1460 ATTRIBUTE65 ,
1461 ATTRIBUTE66 ,
1462 ATTRIBUTE67 ,
1463 ATTRIBUTE68 ,
1464 ATTRIBUTE69 ,
1465 ATTRIBUTE70 ,
1466 ATTRIBUTE71 ,
1467 ATTRIBUTE72 ,
1471 ATTRIBUTE76 ,
1468 ATTRIBUTE73 ,
1469 ATTRIBUTE74 ,
1470 ATTRIBUTE75 ,
1472 ATTRIBUTE77 ,
1473 ATTRIBUTE78 ,
1474 ATTRIBUTE79 ,
1475 ATTRIBUTE80 ,
1476 ATTRIBUTE81 ,
1477 ATTRIBUTE82 ,
1478 ATTRIBUTE83 ,
1479 ATTRIBUTE84 ,
1480 ATTRIBUTE85 ,
1481 ATTRIBUTE86 ,
1482 ATTRIBUTE87 ,
1483 ATTRIBUTE88 ,
1484 ATTRIBUTE89 ,
1485 ATTRIBUTE90 ,
1486 ATTRIBUTE91 ,
1487 ATTRIBUTE92 ,
1488 ATTRIBUTE93 ,
1489 ATTRIBUTE94 ,
1490 ATTRIBUTE95 ,
1491 ATTRIBUTE96 ,
1492 ATTRIBUTE97 ,
1493 ATTRIBUTE98 ,
1494 ATTRIBUTE99 ,
1495 ATTRIBUTE100
1496 )
1497 VALUES
1498 (
1499 CN_COLLECTION_AIA_S.NextVal ,
1500 l_salesrep_id ,
1501 l_employee_number ,
1502 to_date(p_aia_rec_tbl(l_counter).PROCESSED_DATE, 'dd/mm/yyyy hh24:mi:ss'),
1503 p_aia_rec_tbl(l_counter).INVOICE_NUMBER ,
1504 to_date(p_aia_rec_tbl(l_counter).INVOICE_DATE, 'dd/mm/yyyy hh24:mi:ss'),
1505 p_aia_rec_tbl(l_counter).TRANSACTION_AMOUNT ,
1506 p_aia_rec_tbl(l_counter).TRANSACTION_CURRENCY_CODE ,
1507 'AIA' ,
1508 decode(p_aia_rec_tbl(l_counter).REVENUE_TYPE, FND_API.G_MISS_CHAR, 'REVENUE', p_aia_rec_tbl(l_counter).REVENUE_TYPE),
1509 l_salesrep_number ,
1510 p_aia_rec_tbl(l_counter).ADJUST_COMMENTS ,
1511 'MANUAL' ,
1512 p_aia_rec_tbl(l_counter).SOURCE_DOC_ID ,
1513 l_update_flag ,
1514 p_org_id ,
1515 p_aia_rec_tbl(l_counter).ATTRIBUTE1 ,
1516 p_aia_rec_tbl(l_counter).ATTRIBUTE2 ,
1517 p_aia_rec_tbl(l_counter).ATTRIBUTE3 ,
1518 p_aia_rec_tbl(l_counter).ATTRIBUTE4 ,
1519 p_aia_rec_tbl(l_counter).ATTRIBUTE5 ,
1520 p_aia_rec_tbl(l_counter).ATTRIBUTE6 ,
1521 p_aia_rec_tbl(l_counter).ATTRIBUTE7 ,
1522 p_aia_rec_tbl(l_counter).ATTRIBUTE8 ,
1523 p_aia_rec_tbl(l_counter).ATTRIBUTE9 ,
1524 p_aia_rec_tbl(l_counter).ATTRIBUTE10 ,
1525 p_aia_rec_tbl(l_counter).ATTRIBUTE11 ,
1526 p_aia_rec_tbl(l_counter).ATTRIBUTE12 ,
1527 p_aia_rec_tbl(l_counter).ATTRIBUTE13 ,
1528 p_aia_rec_tbl(l_counter).ATTRIBUTE14 ,
1529 p_aia_rec_tbl(l_counter).ATTRIBUTE15 ,
1530 p_aia_rec_tbl(l_counter).ATTRIBUTE16 ,
1531 p_aia_rec_tbl(l_counter).ATTRIBUTE17 ,
1532 p_aia_rec_tbl(l_counter).ATTRIBUTE18 ,
1533 p_aia_rec_tbl(l_counter).ATTRIBUTE19 ,
1534 p_aia_rec_tbl(l_counter).ATTRIBUTE20 ,
1535 p_aia_rec_tbl(l_counter).ATTRIBUTE21 ,
1536 p_aia_rec_tbl(l_counter).ATTRIBUTE22 ,
1537 p_aia_rec_tbl(l_counter).ATTRIBUTE23 ,
1538 p_aia_rec_tbl(l_counter).ATTRIBUTE24 ,
1539 p_aia_rec_tbl(l_counter).ATTRIBUTE25 ,
1540 p_aia_rec_tbl(l_counter).ATTRIBUTE26 ,
1541 p_aia_rec_tbl(l_counter).ATTRIBUTE27 ,
1542 p_aia_rec_tbl(l_counter).ATTRIBUTE28 ,
1543 p_aia_rec_tbl(l_counter).ATTRIBUTE29 ,
1544 p_aia_rec_tbl(l_counter).ATTRIBUTE30 ,
1545 p_aia_rec_tbl(l_counter).ATTRIBUTE31 ,
1546 p_aia_rec_tbl(l_counter).ATTRIBUTE32 ,
1547 p_aia_rec_tbl(l_counter).ATTRIBUTE33 ,
1548 p_aia_rec_tbl(l_counter).ATTRIBUTE34 ,
1549 p_aia_rec_tbl(l_counter).ATTRIBUTE35 ,
1550 p_aia_rec_tbl(l_counter).ATTRIBUTE36 ,
1551 p_aia_rec_tbl(l_counter).ATTRIBUTE37 ,
1552 p_aia_rec_tbl(l_counter).ATTRIBUTE38 ,
1553 p_aia_rec_tbl(l_counter).ATTRIBUTE39 ,
1554 p_aia_rec_tbl(l_counter).ATTRIBUTE40 ,
1555 p_aia_rec_tbl(l_counter).ATTRIBUTE41 ,
1556 p_aia_rec_tbl(l_counter).ATTRIBUTE42 ,
1557 p_aia_rec_tbl(l_counter).ATTRIBUTE43 ,
1558 p_aia_rec_tbl(l_counter).ATTRIBUTE44 ,
1559 p_aia_rec_tbl(l_counter).ATTRIBUTE45 ,
1560 p_aia_rec_tbl(l_counter).ATTRIBUTE46 ,
1561 p_aia_rec_tbl(l_counter).ATTRIBUTE47 ,
1562 p_aia_rec_tbl(l_counter).ATTRIBUTE48 ,
1563 p_aia_rec_tbl(l_counter).ATTRIBUTE49 ,
1564 p_aia_rec_tbl(l_counter).ATTRIBUTE50 ,
1565 p_aia_rec_tbl(l_counter).ATTRIBUTE51 ,
1569 p_aia_rec_tbl(l_counter).ATTRIBUTE55 ,
1566 p_aia_rec_tbl(l_counter).ATTRIBUTE52 ,
1567 p_aia_rec_tbl(l_counter).ATTRIBUTE53 ,
1568 p_aia_rec_tbl(l_counter).ATTRIBUTE54 ,
1570 p_aia_rec_tbl(l_counter).ATTRIBUTE56 ,
1571 p_aia_rec_tbl(l_counter).ATTRIBUTE57 ,
1572 p_aia_rec_tbl(l_counter).ATTRIBUTE58 ,
1573 p_aia_rec_tbl(l_counter).ATTRIBUTE59 ,
1574 p_aia_rec_tbl(l_counter).ATTRIBUTE60 ,
1575 p_aia_rec_tbl(l_counter).ATTRIBUTE61 ,
1576 p_aia_rec_tbl(l_counter).ATTRIBUTE62 ,
1577 p_aia_rec_tbl(l_counter).ATTRIBUTE63 ,
1578 p_aia_rec_tbl(l_counter).ATTRIBUTE64 ,
1579 p_aia_rec_tbl(l_counter).ATTRIBUTE65 ,
1580 p_aia_rec_tbl(l_counter).ATTRIBUTE66 ,
1581 p_aia_rec_tbl(l_counter).ATTRIBUTE67 ,
1582 p_aia_rec_tbl(l_counter).ATTRIBUTE68 ,
1583 p_aia_rec_tbl(l_counter).ATTRIBUTE69 ,
1584 p_aia_rec_tbl(l_counter).ATTRIBUTE70 ,
1585 p_aia_rec_tbl(l_counter).ATTRIBUTE71 ,
1586 p_aia_rec_tbl(l_counter).ATTRIBUTE72 ,
1587 p_aia_rec_tbl(l_counter).ATTRIBUTE73 ,
1588 p_aia_rec_tbl(l_counter).ATTRIBUTE74 ,
1589 p_aia_rec_tbl(l_counter).ATTRIBUTE75 ,
1590 p_aia_rec_tbl(l_counter).ATTRIBUTE76 ,
1591 p_aia_rec_tbl(l_counter).ATTRIBUTE77 ,
1592 p_aia_rec_tbl(l_counter).ATTRIBUTE78 ,
1593 p_aia_rec_tbl(l_counter).ATTRIBUTE79 ,
1594 p_aia_rec_tbl(l_counter).ATTRIBUTE80 ,
1595 p_aia_rec_tbl(l_counter).ATTRIBUTE81 ,
1596 p_aia_rec_tbl(l_counter).ATTRIBUTE82 ,
1597 p_aia_rec_tbl(l_counter).ATTRIBUTE83 ,
1598 p_aia_rec_tbl(l_counter).ATTRIBUTE84 ,
1599 p_aia_rec_tbl(l_counter).ATTRIBUTE85 ,
1600 p_aia_rec_tbl(l_counter).ATTRIBUTE86 ,
1601 p_aia_rec_tbl(l_counter).ATTRIBUTE87 ,
1602 p_aia_rec_tbl(l_counter).ATTRIBUTE88 ,
1603 p_aia_rec_tbl(l_counter).ATTRIBUTE89 ,
1604 p_aia_rec_tbl(l_counter).ATTRIBUTE90 ,
1605 p_aia_rec_tbl(l_counter).ATTRIBUTE91 ,
1606 p_aia_rec_tbl(l_counter).ATTRIBUTE92 ,
1607 p_aia_rec_tbl(l_counter).ATTRIBUTE93 ,
1608 p_aia_rec_tbl(l_counter).ATTRIBUTE94 ,
1609 p_aia_rec_tbl(l_counter).ATTRIBUTE95 ,
1610 p_aia_rec_tbl(l_counter).ATTRIBUTE96 ,
1611 p_aia_rec_tbl(l_counter).ATTRIBUTE97 ,
1612 p_aia_rec_tbl(l_counter).ATTRIBUTE98 ,
1613 p_aia_rec_tbl(l_counter).ATTRIBUTE99 ,
1614 p_aia_rec_tbl(l_counter).ATTRIBUTE100
1615 );
1616
1617 IF fnd_api.to_boolean
1618 (
1619 p_commit
1620 )
1621 THEN
1622 COMMIT;
1623 END IF;
1624
1625 x_msg_count := 1;
1626
1627 debugmsg
1628 (
1629 'CN_COLLECTION_AIA_PUB.loadrow api: ' || 'INSERTED INVOICE NUMBER' || ' '
1630 || p_aia_rec_tbl(l_counter).INVOICE_NUMBER
1631 )
1632 ;
1633 ELSE
1634 -- This step will check that if this transaction has already been collected then update flag should be 'Y'
1635 -- else it should be 'N'. Here l_update_check is initialized to -1. If invoice number exists then it will go
1636 -- to this else part and here it will check wether this has been collected. If yes then l_update_check = 'Y'
1637 FOR l_not_trx_cr IN check_not_trx(l_trans_seq_num, p_org_id)
1638 LOOP
1639 l_update_check := l_invoice_num_cr.FLAG;
1640 END LOOP;
1641
1642 debugmsg
1643 (
1644 'CN_COLLECTION_AIA_PUB.loadrow api: l_update_check(in table cn_not_trx): ' || l_update_check
1645 )
1646 ;
1647 -- Since this invoice number has not been collected. so l_update_check = 'Y'
1648 IF
1649 (
1650 l_update_check = -1
1651 )
1652 THEN
1653 l_update_flag := 'N';
1654 END IF;
1655 debugmsg
1656 (
1657 'CN_COLLECTION_AIA_PUB.loadrow api: l_update_flag: ' || l_update_flag
1658 )
1659 ;
1660 debugmsg
1661 (
1662 'CN_COLLECTION_AIA_PUB.loadrow api: update process start '
1663 )
1664 ;
1665 UPDATE CN_COLLECTION_AIA
1666 SET SALESREP_ID =l_salesrep_id ,
1667 EMPLOYEE_NUMBER =l_employee_number ,
1668 PROCESSED_DATE =to_date(p_aia_rec_tbl(l_counter).PROCESSED_DATE, 'dd/mm/yyyy hh24:mi:ss'),
1669 INVOICE_DATE =to_date(p_aia_rec_tbl(l_counter).INVOICE_DATE, 'dd/mm/yyyy hh24:mi:ss'),
1670 TRANSACTION_AMOUNT =p_aia_rec_tbl(l_counter).TRANSACTION_AMOUNT ,
1671 TRANSACTION_CURRENCY_CODE =p_aia_rec_tbl(l_counter).TRANSACTION_CURRENCY_CODE ,
1672 TRX_TYPE ='AIA' ,
1673 REVENUE_TYPE =decode(p_aia_rec_tbl(l_counter).REVENUE_TYPE, FND_API.G_MISS_CHAR, 'REVENUE',
1677 ADJUST_STATUS ='MANUAL' ,
1674 p_aia_rec_tbl(l_counter).REVENUE_TYPE) ,
1675 SALESREP_NUMBER =l_salesrep_number ,
1676 ADJUST_COMMENTS =p_aia_rec_tbl(l_counter).ADJUST_COMMENTS ,
1678 SOURCE_DOC_ID =p_aia_rec_tbl(l_counter).SOURCE_DOC_ID ,
1679 UPDATE_FLAG =l_update_flag ,
1680 ORG_ID =p_org_id ,
1681 ATTRIBUTE1 =p_aia_rec_tbl(l_counter).ATTRIBUTE1 ,
1682 ATTRIBUTE2 =p_aia_rec_tbl(l_counter).ATTRIBUTE2 ,
1683 ATTRIBUTE3 =p_aia_rec_tbl(l_counter).ATTRIBUTE3 ,
1684 ATTRIBUTE4 =p_aia_rec_tbl(l_counter).ATTRIBUTE4 ,
1685 ATTRIBUTE5 =p_aia_rec_tbl(l_counter).ATTRIBUTE5 ,
1686 ATTRIBUTE6 =p_aia_rec_tbl(l_counter).ATTRIBUTE6 ,
1687 ATTRIBUTE7 =p_aia_rec_tbl(l_counter).ATTRIBUTE7 ,
1688 ATTRIBUTE8 =p_aia_rec_tbl(l_counter).ATTRIBUTE8 ,
1689 ATTRIBUTE9 =p_aia_rec_tbl(l_counter).ATTRIBUTE9 ,
1690 ATTRIBUTE10 =p_aia_rec_tbl(l_counter).ATTRIBUTE10 ,
1691 ATTRIBUTE11 =p_aia_rec_tbl(l_counter).ATTRIBUTE11 ,
1692 ATTRIBUTE12 =p_aia_rec_tbl(l_counter).ATTRIBUTE12 ,
1693 ATTRIBUTE13 =p_aia_rec_tbl(l_counter).ATTRIBUTE13 ,
1694 ATTRIBUTE14 =p_aia_rec_tbl(l_counter).ATTRIBUTE14 ,
1695 ATTRIBUTE15 =p_aia_rec_tbl(l_counter).ATTRIBUTE15 ,
1696 ATTRIBUTE16 =p_aia_rec_tbl(l_counter).ATTRIBUTE16 ,
1697 ATTRIBUTE17 =p_aia_rec_tbl(l_counter).ATTRIBUTE17 ,
1698 ATTRIBUTE18 =p_aia_rec_tbl(l_counter).ATTRIBUTE18 ,
1699 ATTRIBUTE19 =p_aia_rec_tbl(l_counter).ATTRIBUTE19 ,
1700 ATTRIBUTE20 =p_aia_rec_tbl(l_counter).ATTRIBUTE20 ,
1701 ATTRIBUTE21 =p_aia_rec_tbl(l_counter).ATTRIBUTE21 ,
1702 ATTRIBUTE22 =p_aia_rec_tbl(l_counter).ATTRIBUTE22 ,
1703 ATTRIBUTE23 =p_aia_rec_tbl(l_counter).ATTRIBUTE23 ,
1704 ATTRIBUTE24 =p_aia_rec_tbl(l_counter).ATTRIBUTE24 ,
1705 ATTRIBUTE25 =p_aia_rec_tbl(l_counter).ATTRIBUTE25 ,
1706 ATTRIBUTE26 =p_aia_rec_tbl(l_counter).ATTRIBUTE26 ,
1707 ATTRIBUTE27 =p_aia_rec_tbl(l_counter).ATTRIBUTE27 ,
1708 ATTRIBUTE28 =p_aia_rec_tbl(l_counter).ATTRIBUTE28 ,
1709 ATTRIBUTE29 =p_aia_rec_tbl(l_counter).ATTRIBUTE29 ,
1710 ATTRIBUTE30 =p_aia_rec_tbl(l_counter).ATTRIBUTE30 ,
1711 ATTRIBUTE31 =p_aia_rec_tbl(l_counter).ATTRIBUTE31 ,
1712 ATTRIBUTE32 =p_aia_rec_tbl(l_counter).ATTRIBUTE32 ,
1713 ATTRIBUTE33 =p_aia_rec_tbl(l_counter).ATTRIBUTE33 ,
1714 ATTRIBUTE34 =p_aia_rec_tbl(l_counter).ATTRIBUTE34 ,
1715 ATTRIBUTE35 =p_aia_rec_tbl(l_counter).ATTRIBUTE35 ,
1716 ATTRIBUTE36 =p_aia_rec_tbl(l_counter).ATTRIBUTE36 ,
1717 ATTRIBUTE37 =p_aia_rec_tbl(l_counter).ATTRIBUTE37 ,
1718 ATTRIBUTE38 =p_aia_rec_tbl(l_counter).ATTRIBUTE38 ,
1719 ATTRIBUTE39 =p_aia_rec_tbl(l_counter).ATTRIBUTE39 ,
1720 ATTRIBUTE40 =p_aia_rec_tbl(l_counter).ATTRIBUTE40 ,
1721 ATTRIBUTE41 =p_aia_rec_tbl(l_counter).ATTRIBUTE41 ,
1722 ATTRIBUTE42 =p_aia_rec_tbl(l_counter).ATTRIBUTE42 ,
1723 ATTRIBUTE43 =p_aia_rec_tbl(l_counter).ATTRIBUTE43 ,
1724 ATTRIBUTE44 =p_aia_rec_tbl(l_counter).ATTRIBUTE44 ,
1725 ATTRIBUTE45 =p_aia_rec_tbl(l_counter).ATTRIBUTE45 ,
1726 ATTRIBUTE46 =p_aia_rec_tbl(l_counter).ATTRIBUTE46 ,
1727 ATTRIBUTE47 =p_aia_rec_tbl(l_counter).ATTRIBUTE47 ,
1728 ATTRIBUTE48 =p_aia_rec_tbl(l_counter).ATTRIBUTE48 ,
1729 ATTRIBUTE49 =p_aia_rec_tbl(l_counter).ATTRIBUTE49 ,
1730 ATTRIBUTE50 =p_aia_rec_tbl(l_counter).ATTRIBUTE50 ,
1731 ATTRIBUTE51 =p_aia_rec_tbl(l_counter).ATTRIBUTE51 ,
1732 ATTRIBUTE52 =p_aia_rec_tbl(l_counter).ATTRIBUTE52 ,
1733 ATTRIBUTE53 =p_aia_rec_tbl(l_counter).ATTRIBUTE53 ,
1734 ATTRIBUTE54 =p_aia_rec_tbl(l_counter).ATTRIBUTE54 ,
1735 ATTRIBUTE55 =p_aia_rec_tbl(l_counter).ATTRIBUTE55 ,
1736 ATTRIBUTE56 =p_aia_rec_tbl(l_counter).ATTRIBUTE56 ,
1737 ATTRIBUTE57 =p_aia_rec_tbl(l_counter).ATTRIBUTE57 ,
1738 ATTRIBUTE58 =p_aia_rec_tbl(l_counter).ATTRIBUTE58 ,
1739 ATTRIBUTE59 =p_aia_rec_tbl(l_counter).ATTRIBUTE59 ,
1740 ATTRIBUTE60 =p_aia_rec_tbl(l_counter).ATTRIBUTE60 ,
1741 ATTRIBUTE61 =p_aia_rec_tbl(l_counter).ATTRIBUTE61 ,
1745 ATTRIBUTE65 =p_aia_rec_tbl(l_counter).ATTRIBUTE65 ,
1742 ATTRIBUTE62 =p_aia_rec_tbl(l_counter).ATTRIBUTE62 ,
1743 ATTRIBUTE63 =p_aia_rec_tbl(l_counter).ATTRIBUTE63 ,
1744 ATTRIBUTE64 =p_aia_rec_tbl(l_counter).ATTRIBUTE64 ,
1746 ATTRIBUTE66 =p_aia_rec_tbl(l_counter).ATTRIBUTE66 ,
1747 ATTRIBUTE67 =p_aia_rec_tbl(l_counter).ATTRIBUTE67 ,
1748 ATTRIBUTE68 =p_aia_rec_tbl(l_counter).ATTRIBUTE68 ,
1749 ATTRIBUTE69 =p_aia_rec_tbl(l_counter).ATTRIBUTE69 ,
1750 ATTRIBUTE70 =p_aia_rec_tbl(l_counter).ATTRIBUTE70 ,
1751 ATTRIBUTE71 =p_aia_rec_tbl(l_counter).ATTRIBUTE71 ,
1752 ATTRIBUTE72 =p_aia_rec_tbl(l_counter).ATTRIBUTE72 ,
1753 ATTRIBUTE73 =p_aia_rec_tbl(l_counter).ATTRIBUTE73 ,
1754 ATTRIBUTE74 =p_aia_rec_tbl(l_counter).ATTRIBUTE74 ,
1755 ATTRIBUTE75 =p_aia_rec_tbl(l_counter).ATTRIBUTE75 ,
1756 ATTRIBUTE76 =p_aia_rec_tbl(l_counter).ATTRIBUTE76 ,
1757 ATTRIBUTE77 =p_aia_rec_tbl(l_counter).ATTRIBUTE77 ,
1758 ATTRIBUTE78 =p_aia_rec_tbl(l_counter).ATTRIBUTE78 ,
1759 ATTRIBUTE79 =p_aia_rec_tbl(l_counter).ATTRIBUTE79 ,
1760 ATTRIBUTE80 =p_aia_rec_tbl(l_counter).ATTRIBUTE80 ,
1761 ATTRIBUTE81 =p_aia_rec_tbl(l_counter).ATTRIBUTE81 ,
1762 ATTRIBUTE82 =p_aia_rec_tbl(l_counter).ATTRIBUTE82 ,
1763 ATTRIBUTE83 =p_aia_rec_tbl(l_counter).ATTRIBUTE83 ,
1764 ATTRIBUTE84 =p_aia_rec_tbl(l_counter).ATTRIBUTE84 ,
1765 ATTRIBUTE85 =p_aia_rec_tbl(l_counter).ATTRIBUTE85 ,
1766 ATTRIBUTE86 =p_aia_rec_tbl(l_counter).ATTRIBUTE86 ,
1767 ATTRIBUTE87 =p_aia_rec_tbl(l_counter).ATTRIBUTE87 ,
1768 ATTRIBUTE88 =p_aia_rec_tbl(l_counter).ATTRIBUTE88 ,
1769 ATTRIBUTE89 =p_aia_rec_tbl(l_counter).ATTRIBUTE89 ,
1770 ATTRIBUTE90 =p_aia_rec_tbl(l_counter).ATTRIBUTE90 ,
1771 ATTRIBUTE91 =p_aia_rec_tbl(l_counter).ATTRIBUTE91 ,
1772 ATTRIBUTE92 =p_aia_rec_tbl(l_counter).ATTRIBUTE92 ,
1773 ATTRIBUTE93 =p_aia_rec_tbl(l_counter).ATTRIBUTE93 ,
1774 ATTRIBUTE94 =p_aia_rec_tbl(l_counter).ATTRIBUTE94 ,
1775 ATTRIBUTE95 =p_aia_rec_tbl(l_counter).ATTRIBUTE95 ,
1776 ATTRIBUTE96 =p_aia_rec_tbl(l_counter).ATTRIBUTE96 ,
1777 ATTRIBUTE97 =p_aia_rec_tbl(l_counter).ATTRIBUTE97 ,
1778 ATTRIBUTE98 =p_aia_rec_tbl(l_counter).ATTRIBUTE98 ,
1779 ATTRIBUTE99 =p_aia_rec_tbl(l_counter).ATTRIBUTE99 ,
1780 ATTRIBUTE100 =p_aia_rec_tbl(l_counter).ATTRIBUTE100
1781 WHERE INVOICE_NUMBER = p_aia_rec_tbl(l_counter).INVOICE_NUMBER;
1782
1783 IF fnd_api.to_boolean(p_commit) THEN
1784 COMMIT;
1785 END IF;
1786
1787 x_msg_count := 1;
1788 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api: ' || 'UPDATED INVOICE NUMBER' || ' '
1789 || p_aia_rec_tbl(l_counter).INVOICE_NUMBER );
1790 END IF;
1791 End If;
1792 END LOOP;
1793 END IF;
1794
1795 IF(x_msg_data = '_') THEN
1796 x_return_status := 'F';
1797 END IF;
1798
1799 x_msg_data := 'p_aia_error_rec_tbl_count = ' || p_aia_error_rec_tbl.count;
1800
1801 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api: x_return_status: ' || x_return_status);
1802 EXCEPTION
1803 WHEN CN_AIA_REQ_FIELD_NOT_SET_ERROR THEN
1804 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api:exception: CN_AIA_REQ_FIELD_NOT_SET_ERROR: ');
1805 raise_application_error (-20001,l_error_msg);
1806 WHEN OTHERS THEN
1807 x_return_status := 'F';
1808 x_msg_count := 1;
1809 debugmsg('CN_COLLECTION_AIA_PUB.loadrow api:exception others: ' || SQLERRM(SQLCODE()) );
1810 raise_application_error (-20002,SQLERRM(SQLCODE()));
1811 -- DBMS_OUTPUT.put_line('[ ' || SQLERRM(SQLCODE()) || ' ]');
1812 END loadrow;
1813
1814
1815 -- API name : updaterow_comm_api
1816 -- Type : public.
1817 -- Pre-reqs :
1818 -- Usage : This particular api will be called via trigger when we submit collect aia program and their are transactions in
1819 -- cn_collection_aia table whose update_flag status is 'Y' (this means that these transactions has been
1820 -- updated after collection). Once these have been updated in cn_comm_line_api table, update_flag will again be set
1821 -- to 'N' in cn_collection_aia table, so that re-submission of program does not call the update api again based on flag.
1822 --+
1823 -- Desc :
1824 --
1825 --
1826 --+
1827 -- Parameters :
1828 -- IN
1829 --
1830 -- OUT : x_return_status VARCHAR2(1)
1831 --
1832 --
1833 --
1834 --
1835 -- +
1836 --+
1837 -- Version : Current version 1.0
1838 -- Initial version 1.0
1839 --+
1840 -- Notes :
1841 --+
1842 -- End of comments
1843 PROCEDURE updaterow_comm_api
1844 (
1845 x_return_status OUT nocopy VARCHAR2,
1846 x_start_period_name IN VARCHAR2,
1847 x_end_period_name IN VARCHAR2 )
1848 IS
1849 CURSOR c_update_records(l_start_date DATE,l_end_date DATE)
1853 WHERE update_flag = 'Y'
1850 IS
1851 SELECT *
1852 FROM CN_COLLECTION_AIA
1854 AND TRUNC(processed_date) BETWEEN to_date(l_start_date, 'dd-mm-rr') AND to_date(l_end_date, 'dd-mm-rr');
1855
1856 TYPE collect_aia_tbl_type IS TABLE OF c_update_records%ROWTYPE
1857 INDEX BY PLS_INTEGER;
1858
1859 l_collect_aia_tbl collect_aia_tbl_type;
1860
1861
1862 CURSOR get_start_period_id(l_org_id NUMBER)
1863 IS
1864 SELECT period_id
1865 FROM cn_periods
1866 WHERE period_name = x_start_period_name
1867 AND org_id = l_org_id ;
1868
1869 CURSOR get_end_period_id(l_org_id NUMBER)
1870 IS
1871 SELECT period_id
1872 FROM cn_periods
1873 WHERE period_name = x_end_period_name
1874 AND org_id = l_org_id ;
1875
1876
1877 l_comm_lines_id NUMBER;
1878 l_adj_rec_type cn_get_tx_data_pub.adj_rec_type;
1879 l_api_id NUMBER;
1880 l_return_status VARCHAR2(100);
1881 l_msg_count NUMBER;
1882 l_msg_data VARCHAR2(2000);
1883 l_loading_status VARCHAR2(100);
1884 l_salesrep_id NUMBER;
1885 x_org_id NUMBER;
1886 l_start_period_id NUMBER;
1887 l_end_period_id NUMBER;
1888 l_collect_aia_tbl_count NUMBER;
1889 x_start_date DATE;
1890 x_end_date DATE;
1891
1892 FUNCTION get_comm_line_id
1893 (
1894 p_invoice_number VARCHAR2,
1895 l_invoice_date DATE,
1896 l_employee_number VARCHAR2)
1897 RETURN NUMBER
1898 IS
1899 CURSOR c_get_comm_line_id
1900 IS
1901 SELECT max(comm_lines_api_id)
1902 FROM cn_comm_lines_api_all
1903 WHERE invoice_number = p_invoice_number
1904 AND employee_number = l_employee_number
1905 AND NVL(adjust_status,'N') <> 'FROZEN';
1906
1907 l_comm_line_id NUMBER;
1908 BEGIN
1909 OPEN c_get_comm_line_id;
1910 FETCH c_get_comm_line_id INTO l_comm_line_id;
1911
1912 CLOSE c_get_comm_line_id;
1913 RETURN l_comm_line_id;
1914 END;
1915
1916 FUNCTION get_comm_line_id_for_om
1917 (
1918 p_order_number NUMBER,
1919 l_booked_date DATE,
1920 l_employee_number VARCHAR2)
1921 RETURN NUMBER
1922 IS
1923 CURSOR c_get_comm_line_id
1924 IS
1925 SELECT comm_lines_api_id
1926 FROM cn_comm_lines_api_all
1927 WHERE order_number = p_order_number
1928 AND employee_number = l_employee_number
1929 AND NVL(adjust_status,'N') <> 'FROZEN';
1930
1931 l_comm_line_id NUMBER;
1932 BEGIN
1933 OPEN c_get_comm_line_id;
1934 FETCH c_get_comm_line_id INTO l_comm_line_id;
1935
1936 CLOSE c_get_comm_line_id;
1937 RETURN l_comm_line_id;
1938 END;
1939 BEGIN
1940 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: start: ');
1941
1942 -- DBMS_OUTPUT.put_line('Start update');
1943 x_org_id := mo_global.get_current_org_id;
1944
1945 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: x_org_id: ' || x_org_id);
1946 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: x_start_period_name: ' || x_start_period_name);
1947 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: x_end_period_name: ' || x_end_period_name);
1948 --DBMS_OUTPUT.put_line('x_org_id ' || x_org_id);
1949 --DBMS_OUTPUT.put_line('x_start_period_name ' || x_start_period_name);
1950 --DBMS_OUTPUT.put_line('x_end_period_name ' || x_end_period_name);
1951
1952 mo_global.set_policy_context('S',x_org_id);
1953
1954 OPEN get_start_period_id(x_org_id); -- open the cursor
1955 FETCH get_start_period_id INTO l_start_period_id; -- fetch data into local variables
1956 CLOSE get_start_period_id;
1957
1958 OPEN get_end_period_id(x_org_id); -- open the cursor
1959 FETCH get_end_period_id INTO l_end_period_id; -- fetch data into local variables
1960 CLOSE get_end_period_id;
1961
1962 cn_periods_api.set_dates(l_start_period_id, l_end_period_id, x_org_id, x_start_date, x_end_date);
1963
1964 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: x_start_date: ' || x_start_date);
1965 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: x_end_date: ' || x_end_date);
1966 --DBMS_OUTPUT.put_line('x_start_date ' || x_start_date);
1967 --DBMS_OUTPUT.put_line('x_end_date ' || x_end_date);
1968
1969 OPEN c_update_records(x_start_date,x_end_date);
1970 FETCH c_update_records
1971 BULK COLLECT INTO l_collect_aia_tbl;
1972 CLOSE c_update_records;
1973
1974 l_collect_aia_tbl_count :=l_collect_aia_tbl.COUNT;
1975 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: No. of records to be updated : ' || l_collect_aia_tbl_count);
1976
1977 IF (l_collect_aia_tbl_count <= 0) THEN
1978 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api : No transactions to Update.');
1979 ELSE
1980 FOR i IN l_collect_aia_tbl.FIRST .. l_collect_aia_tbl.LAST LOOP
1981 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: l_collect_aia_tbl(i).trx_type: ' || l_collect_aia_tbl(i).trx_type);
1982 IF(l_collect_aia_tbl(i).trx_type = 'AIA_OM') THEN
1983 l_comm_lines_id := get_comm_line_id_for_om(l_collect_aia_tbl(i).order_number, l_collect_aia_tbl(i).booked_date, l_collect_aia_tbl(i).salesrep_number);
1984 ELSE
1985 l_comm_lines_id := get_comm_line_id(l_collect_aia_tbl(i).invoice_number, l_collect_aia_tbl(i).invoice_date, l_collect_aia_tbl(i).salesrep_number);
1986 END IF;
1987 --debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: l_comm_lines_id: ' || l_comm_lines_id);
1988 --DBMS_OUTPUT.put_line('l_comm_lines_id ' || l_comm_lines_id);
1989 l_adj_rec_type.comm_lines_api_id := l_comm_lines_id;
1990 l_adj_rec_type.invoice_number := l_collect_aia_tbl(i).invoice_number;
1991 l_adj_rec_type.invoice_date := l_collect_aia_tbl(i).invoice_date;
1995 l_adj_rec_type.attribute1 := l_collect_aia_tbl(i).attribute1;
1992 l_adj_rec_type.direct_salesrep_number := l_collect_aia_tbl(i).salesrep_number;
1993 l_adj_rec_type.transaction_amount := l_collect_aia_tbl(i).transaction_amount;
1994 l_adj_rec_type.transaction_amount_orig := l_collect_aia_tbl(i).transaction_amount;
1996 l_adj_rec_type.revenue_type := l_collect_aia_tbl(i).revenue_type;
1997 l_adj_rec_type.processed_date := l_collect_aia_tbl(i).processed_date;
1998 l_adj_rec_type.trx_type := l_collect_aia_tbl(i).trx_type;
1999 l_adj_rec_type.orig_currency_code := l_collect_aia_tbl(i).transaction_currency_code;
2000 l_adj_rec_type.direct_salesrep_id := l_collect_aia_tbl(i).salesrep_id;
2001 l_adj_rec_type.adjust_comments := l_collect_aia_tbl(i).adjust_comments;
2002 l_adj_rec_type.attribute2 := l_collect_aia_tbl(i).attribute2 ;
2003 l_adj_rec_type.attribute3 := l_collect_aia_tbl(i).attribute3 ;
2004 l_adj_rec_type.attribute4 := l_collect_aia_tbl(i).attribute4 ;
2005 l_adj_rec_type.attribute5 := l_collect_aia_tbl(i).attribute5 ;
2006 l_adj_rec_type.attribute6 := l_collect_aia_tbl(i).attribute6 ;
2007 l_adj_rec_type.attribute7 := l_collect_aia_tbl(i).attribute7 ;
2008 l_adj_rec_type.attribute8 := l_collect_aia_tbl(i).attribute8 ;
2009 l_adj_rec_type.attribute9 := l_collect_aia_tbl(i).attribute9 ;
2010 l_adj_rec_type.attribute10 := l_collect_aia_tbl(i).attribute10 ;
2011 l_adj_rec_type.attribute11 := l_collect_aia_tbl(i).attribute11 ;
2012 l_adj_rec_type.attribute12 := l_collect_aia_tbl(i).attribute12 ;
2013 l_adj_rec_type.attribute13 := l_collect_aia_tbl(i).attribute13 ;
2014 l_adj_rec_type.attribute14 := l_collect_aia_tbl(i).attribute14 ;
2015 l_adj_rec_type.attribute15 := l_collect_aia_tbl(i).attribute15 ;
2016 l_adj_rec_type.attribute16 := l_collect_aia_tbl(i).attribute16 ;
2017 l_adj_rec_type.attribute17 := l_collect_aia_tbl(i).attribute17 ;
2018 l_adj_rec_type.attribute18 := l_collect_aia_tbl(i).attribute18 ;
2019 l_adj_rec_type.attribute19 := l_collect_aia_tbl(i).attribute19 ;
2020 l_adj_rec_type.attribute20 := l_collect_aia_tbl(i).attribute20 ;
2021 l_adj_rec_type.attribute21 := l_collect_aia_tbl(i).attribute21 ;
2022 l_adj_rec_type.attribute22 := l_collect_aia_tbl(i).attribute22 ;
2023 l_adj_rec_type.attribute23 := l_collect_aia_tbl(i).attribute23 ;
2024 l_adj_rec_type.attribute24 := l_collect_aia_tbl(i).attribute24 ;
2025 l_adj_rec_type.attribute25 := l_collect_aia_tbl(i).attribute25 ;
2026 l_adj_rec_type.attribute26 := l_collect_aia_tbl(i).attribute26 ;
2027 l_adj_rec_type.attribute27 := l_collect_aia_tbl(i).attribute27 ;
2028 l_adj_rec_type.attribute28 := l_collect_aia_tbl(i).attribute28 ;
2029 l_adj_rec_type.attribute29 := l_collect_aia_tbl(i).attribute29 ;
2030 l_adj_rec_type.attribute30 := l_collect_aia_tbl(i).attribute30 ;
2031 l_adj_rec_type.attribute31 := l_collect_aia_tbl(i).attribute31 ;
2032 l_adj_rec_type.attribute32 := l_collect_aia_tbl(i).attribute32 ;
2033 l_adj_rec_type.attribute33 := l_collect_aia_tbl(i).attribute33 ;
2034 l_adj_rec_type.attribute34 := l_collect_aia_tbl(i).attribute34 ;
2035 l_adj_rec_type.attribute35 := l_collect_aia_tbl(i).attribute35 ;
2036 l_adj_rec_type.attribute36 := l_collect_aia_tbl(i).attribute36 ;
2037 l_adj_rec_type.attribute37 := l_collect_aia_tbl(i).attribute37 ;
2038 l_adj_rec_type.attribute38 := l_collect_aia_tbl(i).attribute38 ;
2039 l_adj_rec_type.attribute39 := l_collect_aia_tbl(i).attribute39 ;
2040 l_adj_rec_type.attribute40 := l_collect_aia_tbl(i).attribute40 ;
2041 l_adj_rec_type.attribute41 := l_collect_aia_tbl(i).attribute41 ;
2042 l_adj_rec_type.attribute42 := l_collect_aia_tbl(i).attribute42 ;
2043 l_adj_rec_type.attribute43 := l_collect_aia_tbl(i).attribute43 ;
2044 l_adj_rec_type.attribute44 := l_collect_aia_tbl(i).attribute44 ;
2045 l_adj_rec_type.attribute45 := l_collect_aia_tbl(i).attribute45 ;
2046 l_adj_rec_type.attribute46 := l_collect_aia_tbl(i).attribute46 ;
2047 l_adj_rec_type.attribute47 := l_collect_aia_tbl(i).attribute47 ;
2048 l_adj_rec_type.attribute48 := l_collect_aia_tbl(i).attribute48 ;
2049 l_adj_rec_type.attribute49 := l_collect_aia_tbl(i).attribute49 ;
2050 l_adj_rec_type.attribute50 := l_collect_aia_tbl(i).attribute50 ;
2051 l_adj_rec_type.attribute51 := l_collect_aia_tbl(i).attribute51 ;
2052 l_adj_rec_type.attribute52 := l_collect_aia_tbl(i).attribute52 ;
2053 l_adj_rec_type.attribute53 := l_collect_aia_tbl(i).attribute53 ;
2054 l_adj_rec_type.attribute54 := l_collect_aia_tbl(i).attribute54 ;
2055 l_adj_rec_type.attribute55 := l_collect_aia_tbl(i).attribute55 ;
2056 l_adj_rec_type.attribute56 := l_collect_aia_tbl(i).attribute56 ;
2057 l_adj_rec_type.attribute57 := l_collect_aia_tbl(i).attribute57 ;
2058 l_adj_rec_type.attribute58 := l_collect_aia_tbl(i).attribute58 ;
2059 l_adj_rec_type.attribute59 := l_collect_aia_tbl(i).attribute59 ;
2060 l_adj_rec_type.attribute60 := l_collect_aia_tbl(i).attribute60 ;
2061 l_adj_rec_type.attribute61 := l_collect_aia_tbl(i).attribute61 ;
2062 l_adj_rec_type.attribute62 := l_collect_aia_tbl(i).attribute62 ;
2063 l_adj_rec_type.attribute63 := l_collect_aia_tbl(i).attribute63 ;
2067 l_adj_rec_type.attribute67 := l_collect_aia_tbl(i).attribute67 ;
2064 l_adj_rec_type.attribute64 := l_collect_aia_tbl(i).attribute64 ;
2065 l_adj_rec_type.attribute65 := l_collect_aia_tbl(i).attribute65 ;
2066 l_adj_rec_type.attribute66 := l_collect_aia_tbl(i).attribute66 ;
2068 l_adj_rec_type.attribute68 := l_collect_aia_tbl(i).attribute68 ;
2069 l_adj_rec_type.attribute69 := l_collect_aia_tbl(i).attribute69 ;
2070 l_adj_rec_type.attribute70 := l_collect_aia_tbl(i).attribute70 ;
2071 l_adj_rec_type.attribute71 := l_collect_aia_tbl(i).attribute71 ;
2072 l_adj_rec_type.attribute72 := l_collect_aia_tbl(i).attribute72 ;
2073 l_adj_rec_type.attribute73 := l_collect_aia_tbl(i).attribute73 ;
2074 l_adj_rec_type.attribute74 := l_collect_aia_tbl(i).attribute74 ;
2075 l_adj_rec_type.attribute75 := l_collect_aia_tbl(i).attribute75 ;
2076 l_adj_rec_type.attribute76 := l_collect_aia_tbl(i).attribute76 ;
2077 l_adj_rec_type.attribute77 := l_collect_aia_tbl(i).attribute77 ;
2078 l_adj_rec_type.attribute78 := l_collect_aia_tbl(i).attribute78 ;
2079 l_adj_rec_type.attribute79 := l_collect_aia_tbl(i).attribute79 ;
2080 l_adj_rec_type.attribute80 := l_collect_aia_tbl(i).attribute80 ;
2081 l_adj_rec_type.attribute81 := l_collect_aia_tbl(i).attribute81 ;
2082 l_adj_rec_type.attribute82 := l_collect_aia_tbl(i).attribute82 ;
2083 l_adj_rec_type.attribute83 := l_collect_aia_tbl(i).attribute83 ;
2084 l_adj_rec_type.attribute84 := l_collect_aia_tbl(i).attribute84 ;
2085 l_adj_rec_type.attribute85 := l_collect_aia_tbl(i).attribute85 ;
2086 l_adj_rec_type.attribute86 := l_collect_aia_tbl(i).attribute86 ;
2087 l_adj_rec_type.attribute87 := l_collect_aia_tbl(i).attribute87 ;
2088 l_adj_rec_type.attribute88 := l_collect_aia_tbl(i).attribute88 ;
2089 l_adj_rec_type.attribute89 := l_collect_aia_tbl(i).attribute89 ;
2090 l_adj_rec_type.attribute90 := l_collect_aia_tbl(i).attribute90 ;
2091 l_adj_rec_type.attribute91 := l_collect_aia_tbl(i).attribute91 ;
2092 l_adj_rec_type.attribute92 := l_collect_aia_tbl(i).attribute92 ;
2093 l_adj_rec_type.attribute93 := l_collect_aia_tbl(i).attribute93 ;
2094 l_adj_rec_type.attribute94 := l_collect_aia_tbl(i).attribute94 ;
2095 l_adj_rec_type.attribute95 := l_collect_aia_tbl(i).attribute95 ;
2096 l_adj_rec_type.attribute96 := l_collect_aia_tbl(i).attribute96 ;
2097 l_adj_rec_type.attribute97 := l_collect_aia_tbl(i).attribute97 ;
2098 l_adj_rec_type.attribute98 := l_collect_aia_tbl(i).attribute98 ;
2099 l_adj_rec_type.attribute99 := l_collect_aia_tbl(i).attribute99 ;
2100 l_adj_rec_type.attribute100 := l_collect_aia_tbl(i).attribute100;
2101 begin
2102 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: l_comm_lines_id: ' || l_comm_lines_id);
2103 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: l_collect_aia_tbl(i).invoice_number: ' || l_adj_rec_type.invoice_number);
2104 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: l_collect_aia_tbl(i).invoice_date: ' || l_adj_rec_type.invoice_date);
2105 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: l_collect_aia_tbl(i).transaction_amount: ' || l_adj_rec_type.transaction_amount);
2106 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: l_collect_aia_tbl(i).revenue_type: ' || l_adj_rec_type.revenue_type);
2107 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: l_collect_aia_tbl(i).processed_date: ' || l_adj_rec_type.processed_date);
2108 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: l_collect_aia_tbl(i).trx_type: ' || l_adj_rec_type.trx_type);
2109 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: l_collect_aia_tbl(i).transaction_currency_code: ' || l_adj_rec_type.orig_currency_code);
2110 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: l_collect_aia_tbl(i).salesrep_id: ' || l_adj_rec_type.direct_salesrep_id);
2111 /*debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: l_collect_aia_tbl(i).attribute1: ' || l_adj_rec_type.attribute1 );
2112 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: l_collect_aia_tbl(i).adjust_comments: ' || l_adj_rec_type.adjust_comments);
2113 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: l_collect_aia_tbl(i).attribute2 : ' || l_adj_rec_type.attribute2 );
2114 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: l_collect_aia_tbl(i).attribute3 : ' || l_adj_rec_type.attribute3 );
2115 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: l_collect_aia_tbl.attribute4 : ' || l_adj_rec_type.attribute4 );
2116 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: l_collect_aia_tbl(i).attribute5 : ' || l_adj_rec_type.attribute5 );
2117 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: l_collect_aia_tbl(i).attribute6 : ' || l_adj_rec_type.attribute6 );
2118 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: l_collect_aia_tbl(i).attribute7 : ' || l_adj_rec_type.attribute7 );
2119 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: l_collect_aia_tbl(i).attribute8 : ' || l_adj_rec_type.attribute8 );
2120 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: l_collect_aia_tbl(i).attribute9 : ' || l_adj_rec_type.attribute9 );
2121 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: l_collect_aia_tbl(i).attribute10 : ' || l_adj_rec_type.attribute10 );
2122 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: l_collect_aia_tbl(i).attribute11 : ' || l_adj_rec_type.attribute11 );
2123 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: l_collect_aia_tbl(i).attribute12 : ' || l_adj_rec_type.attribute12 );
2124 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: l_collect_aia_tbl(i).attribute13 : ' || l_adj_rec_type.attribute13 );
2125 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: l_collect_aia_tbl(i).attribute14 : ' || l_adj_rec_type.attribute14 );
2129 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: l_collect_aia_tbl(i).attribute18 : ' || l_adj_rec_type.attribute18 );
2126 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: l_collect_aia_tbl(i).attribute15 : ' || l_adj_rec_type.attribute15 );
2127 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: l_collect_aia_tbl(i).attribute16 : ' || l_adj_rec_type.attribute16 );
2128 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: l_collect_aia_tbl(i).attribute17 : ' || l_adj_rec_type.attribute17 );
2130 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: l_collect_aia_tbl(i).attribute19 : ' || l_adj_rec_type.attribute19 );
2131 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: l_collect_aia_tbl(i).attribute20 : ' || l_adj_rec_type.attribute20 );
2132 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: l_collect_aia_tbl(i).attribute21 : ' || l_adj_rec_type.attribute21 );
2133 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: l_collect_aia_tbl(i).attribute22 : ' || l_adj_rec_type.attribute22 );
2134 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: l_collect_aia_tbl(i).attribute23 : ' || l_adj_rec_type.attribute23 );
2135 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: l_collect_aia_tbl(i).attribute24 : ' || l_adj_rec_type.attribute24 );
2136 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: l_collect_aia_tbl(i).attribute25 : ' || l_adj_rec_type.attribute25 );
2137 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: l_collect_aia_tbl(i).attribute26 : ' || l_adj_rec_type.attribute26 );
2138 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: l_collect_aia_tbl(i).attribute27 : ' || l_adj_rec_type.attribute27 );
2139 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: l_collect_aia_tbl(i).attribute28 : ' || l_adj_rec_type.attribute28 );
2140 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: l_collect_aia_tbl(i).attribute29 : ' || l_adj_rec_type.attribute29 );
2141 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: l_collect_aia_tbl(i).attribute30 : ' || l_adj_rec_type.attribute30 );
2142 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: l_collect_aia_tbl(i).attribute31 : ' || l_adj_rec_type.attribute31 );
2143 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: l_collect_aia_tbl(i).attribute32 : ' || l_adj_rec_type.attribute32 );
2144 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: l_collect_aia_tbl(i).attribute33 : ' || l_adj_rec_type.attribute33 );
2145 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: l_collect_aia_tbl(i).attribute34 : ' || l_adj_rec_type.attribute34 );
2146 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: l_collect_aia_tbl(i).attribute35 : ' || l_adj_rec_type.attribute35 );
2147 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: l_collect_aia_tbl(i).attribute36 : ' || l_adj_rec_type.attribute36 );
2148 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: l_collect_aia_tbl(i).attribute37 : ' || l_adj_rec_type.attribute37 );
2149 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: l_collect_aia_tbl(i).attribute38 : ' || l_adj_rec_type.attribute38 );
2150 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: l_collect_aia_tbl(i).attribute39 : ' || l_adj_rec_type.attribute39 );
2151 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: l_collect_aia_tbl(i).attribute40 : ' || l_adj_rec_type.attribute40 );
2152 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: l_collect_aia_tbl(i).attribute41 : ' || l_adj_rec_type.attribute41 );
2153 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: l_collect_aia_tbl(i).attribute42 : ' || l_adj_rec_type.attribute42 );
2154 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: l_collect_aia_tbl(i).attribute43 : ' || l_adj_rec_type.attribute43 );
2155 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: l_collect_aia_tbl(i).attribute44 : ' || l_adj_rec_type.attribute44 );
2156 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: l_collect_aia_tbl(i).attribute45 : ' || l_adj_rec_type.attribute45 );
2157 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: l_collect_aia_tbl(i).attribute46 : ' || l_adj_rec_type.attribute46 );
2158 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: l_collect_aia_tbl(i).attribute47 : ' || l_adj_rec_type.attribute47 );
2159 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: l_collect_aia_tbl(i).attribute48 : ' || l_adj_rec_type.attribute48 );
2160 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: l_collect_aia_tbl(i).attribute49 : ' || l_adj_rec_type.attribute49 );
2161 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: l_collect_aia_tbl(i).attribute50 : ' || l_adj_rec_type.attribute50 );
2162 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: l_collect_aia_tbl(i).attribute51 : ' || l_adj_rec_type.attribute51 );
2163 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: l_collect_aia_tbl(i).attribute52 : ' || l_adj_rec_type.attribute52 );
2164 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: l_collect_aia_tbl(i).attribute53 : ' || l_adj_rec_type.attribute53 );
2165 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: l_collect_aia_tbl(i).attribute54 : ' || l_adj_rec_type.attribute54 );
2166 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: l_collect_aia_tbl(i).attribute55 : ' || l_adj_rec_type.attribute55 );
2167 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: l_collect_aia_tbl(i).attribute56 : ' || l_adj_rec_type.attribute56 );
2168 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: l_collect_aia_tbl(i).attribute57 : ' || l_adj_rec_type.attribute57 );
2169 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: l_collect_aia_tbl(i).attribute58 : ' || l_adj_rec_type.attribute58 );
2170 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: l_collect_aia_tbl(i).attribute59 : ' || l_adj_rec_type.attribute59 );
2171 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: l_collect_aia_tbl(i).attribute60 : ' || l_adj_rec_type.attribute60 );
2172 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: l_collect_aia_tbl(i).attribute61 : ' || l_adj_rec_type.attribute61 );
2173 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: l_collect_aia_tbl(i).attribute62 : ' || l_adj_rec_type.attribute62 );
2174 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: l_collect_aia_tbl(i).attribute63 : ' || l_adj_rec_type.attribute63 );
2175 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: l_collect_aia_tbl(i).attribute64 : ' || l_adj_rec_type.attribute64 );
2179 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: l_collect_aia_tbl(i).attribute68 : ' || l_adj_rec_type.attribute68 );
2176 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: l_collect_aia_tbl(i).attribute65 : ' || l_adj_rec_type.attribute65 );
2177 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: l_collect_aia_tbl(i).attribute66 : ' || l_adj_rec_type.attribute66 );
2178 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: l_collect_aia_tbl(i).attribute67 : ' || l_adj_rec_type.attribute67 );
2180 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: l_collect_aia_tbl(i).attribute69 : ' || l_adj_rec_type.attribute69 );
2181 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: l_collect_aia_tbl(i).attribute70 : ' || l_adj_rec_type.attribute70 );
2182 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: l_collect_aia_tbl(i).attribute71 : ' || l_adj_rec_type.attribute71 );
2183 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: l_collect_aia_tbl(i).attribute72 : ' || l_adj_rec_type.attribute72 );
2184 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: l_collect_aia_tbl(i).attribute73 : ' || l_adj_rec_type.attribute73 );
2185 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: l_collect_aia_tbl(i).attribute74 : ' || l_adj_rec_type.attribute74 );
2186 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: l_collect_aia_tbl(i).attribute75 : ' || l_adj_rec_type.attribute75 );
2187 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: l_collect_aia_tbl(i).attribute76 : ' || l_adj_rec_type.attribute76 );
2188 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: l_collect_aia_tbl(i).attribute77 : ' || l_adj_rec_type.attribute77 );
2189 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: l_collect_aia_tbl(i).attribute78 : ' || l_adj_rec_type.attribute78 );
2190 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: l_collect_aia_tbl(i).attribute79 : ' || l_adj_rec_type.attribute79 );
2191 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: l_collect_aia_tbl(i).attribute80 : ' || l_adj_rec_type.attribute80 );
2192 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: l_collect_aia_tbl(i).attribute81 : ' || l_adj_rec_type.attribute81 );
2193 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: l_collect_aia_tbl(i).attribute82 : ' || l_adj_rec_type.attribute82 );
2194 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: l_collect_aia_tbl(i).attribute83 : ' || l_adj_rec_type.attribute83 );
2195 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: l_collect_aia_tbl(i).attribute84 : ' || l_adj_rec_type.attribute84 );
2196 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: l_collect_aia_tbl(i).attribute85 : ' || l_adj_rec_type.attribute85 );
2197 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: l_collect_aia_tbl(i).attribute86 : ' || l_adj_rec_type.attribute86 );
2198 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: l_collect_aia_tbl(i).attribute87 : ' || l_adj_rec_type.attribute87 );
2199 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: l_collect_aia_tbl(i).attribute88 : ' || l_adj_rec_type.attribute88 );
2200 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: l_collect_aia_tbl(i).attribute89 : ' || l_adj_rec_type.attribute89 );
2201 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: l_collect_aia_tbl(i).attribute90 : ' || l_adj_rec_type.attribute90 );
2202 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: l_collect_aia_tbl(i).attribute91 : ' || l_adj_rec_type.attribute91 );
2203 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: l_collect_aia_tbl(i).attribute92 : ' || l_adj_rec_type.attribute92 );
2204 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: l_collect_aia_tbl(i).attribute93 : ' || l_adj_rec_type.attribute93 );
2205 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: l_collect_aia_tbl(i).attribute94 : ' || l_adj_rec_type.attribute94 );
2206 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: l_collect_aia_tbl(i).attribute95 : ' || l_adj_rec_type.attribute95 );
2207 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: l_collect_aia_tbl(i).attribute96 : ' || l_adj_rec_type.attribute96 );
2208 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: l_collect_aia_tbl(i).attribute97 : ' || l_adj_rec_type.attribute97 );
2209 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: l_collect_aia_tbl(i).attribute98 : ' || l_adj_rec_type.attribute98 );
2210 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: l_collect_aia_tbl(i).attribute99 : ' || l_adj_rec_type.attribute99 );
2211 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: l_collect_aia_tbl(i).attribute100: ' || l_adj_rec_type.attribute100);*/
2212 EXCEPTION
2213 WHEN OTHERS THEN
2214 --x_return_status := 'F';
2215 --DBMS_OUTPUT.put_line('CN_COLLECTION_AIA_PUB.updaterow_comm_api:exception in debug messages others: ' || '[ ' || SQLERRM(SQLCODE()) || ' ]');
2216 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api:exception in debug messages others: ' || SQLERRM(SQLCODE()) );
2217 end;
2218 cn_get_tx_data_pub.update_api_record ( p_api_version => 1.0, p_newtx_rec => l_adj_rec_type, x_api_id => l_api_id, x_return_status => l_return_status , x_msg_count => l_msg_count , x_msg_data => l_msg_data, x_loading_status => l_loading_status);
2219 -- DBMS_OUTPUT.put_line('Start update' || l_collect_aia_tbl(i).invoice_number);
2220 -- DBMS_OUTPUT.put_line('Start update' || l_comm_lines_id);
2221 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: updating update_flag to N of CN_COLLECTION_AIA : ' );
2222 UPDATE CN_COLLECTION_AIA
2223 SET update_flag = 'N'
2224 WHERE invoice_number = l_collect_aia_tbl(i).invoice_number;
2225
2226 x_return_status := 'S';
2227 END LOOP;
2228 END IF;
2229
2230
2231 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api: x_return_status: ' || x_return_status);
2232 EXCEPTION
2233 WHEN OTHERS THEN
2234 x_return_status := 'F';
2235 debugmsg('CN_COLLECTION_AIA_PUB.updaterow_comm_api:exception others: ' || SQLERRM(SQLCODE()) );
2236 -- DBMS_OUTPUT.put_line('[ ' || SQLERRM(SQLCODE()) || ' ]');
2237 END updaterow_comm_api;
2238 END CN_COLLECTION_AIA_PUB;