[Home] [Help]
PACKAGE BODY: APPS.ICX_LOAD_REQ_INTERFACE
Source
1 PACKAGE BODY ICX_LOAD_REQ_INTERFACE as
2 /* $Header: ICXLREQB.pls 115.3 99/07/17 03:18:29 porting ship $ */
3
4 Procedure Load_ShopCart_To_Interface(l_cart_id IN NUMBER) as
5
6 l_org_id number;
7 l_dist_seq_id number;
8 l_trans_id number;
9
10 cursor get_cart_line_id(l_cart_id number,l_org_id number) is
11 select cart_line_id
12 from icx_shopping_cart_lines
13 where cart_id = l_cart_id
14 and nvl(org_id,-9999) = nvl(l_org_id,-9999);
15
16 Begin
17
18 -- Check if session is valid
19 if (icx_sec.validatesession('ICX_REQS')) then
20
21 l_org_id := icx_sec.getId(icx_sec.PV_ORG_ID);
22 -- fnd_client_info.set_org_context(to_char(l_org_id));
23
24 for prec in get_cart_line_id(l_cart_id,l_org_id) loop
25
26 select po_ri_dist_sequence_s.nextval into l_dist_seq_id from dual;
27 select po_requisitions_interface_s.nextval into l_trans_id from dual;
28
29 insert into po_requisitions_interface
30 (TRANSACTION_ID ,
31 INTERFACE_SOURCE_CODE ,
32 SOURCE_TYPE_CODE ,
33 REQUISITION_TYPE ,
34 DESTINATION_TYPE_CODE ,
35 ITEM_DESCRIPTION ,
36 QUANTITY ,
37 UNIT_PRICE ,
38 AUTHORIZATION_STATUS ,
39 APPROVER_ID ,
40 NOTE_TO_APPROVER ,
41 PREPARER_ID ,
42 AUTOSOURCE_FLAG ,
43 REQ_NUMBER_SEGMENT1 ,
44 NOTE_TO_BUYER ,
45 ITEM_ID ,
46 ITEM_REVISION ,
47 CATEGORY_ID ,
48 UNIT_OF_MEASURE ,
49 LINE_TYPE_ID ,
50 DESTINATION_ORGANIZATION_ID ,
51 DELIVER_TO_LOCATION_ID ,
52 DELIVER_TO_REQUESTOR_ID ,
53 SUGGESTED_BUYER_ID,
54 SUGGESTED_VENDOR_NAME ,
55 SUGGESTED_VENDOR_SITE ,
56 SUGGESTED_VENDOR_CONTACT,
57 SUGGESTED_VENDOR_PHONE,
58 SUGGESTED_VENDOR_ITEM_NUM ,
59 NEED_BY_DATE ,
60 AUTOSOURCE_DOC_HEADER_ID ,
61 AUTOSOURCE_DOC_LINE_NUM ,
62 DOCUMENT_TYPE_CODE ,
63 HEADER_DESCRIPTION,
64 HEADER_ATTRIBUTE_CATEGORY,
65 HEADER_ATTRIBUTE1,
66 HEADER_ATTRIBUTE2,
67 HEADER_ATTRIBUTE3,
68 HEADER_ATTRIBUTE4,
69 HEADER_ATTRIBUTE5,
70 HEADER_ATTRIBUTE6,
71 HEADER_ATTRIBUTE7,
72 HEADER_ATTRIBUTE8,
73 HEADER_ATTRIBUTE9,
74 HEADER_ATTRIBUTE10,
75 HEADER_ATTRIBUTE11,
76 HEADER_ATTRIBUTE12,
77 HEADER_ATTRIBUTE13,
78 HEADER_ATTRIBUTE14,
79 HEADER_ATTRIBUTE15,
80 LINE_ATTRIBUTE_CATEGORY,
81 LINE_ATTRIBUTE1,
82 LINE_ATTRIBUTE2,
83 LINE_ATTRIBUTE3,
84 LINE_ATTRIBUTE4,
85 LINE_ATTRIBUTE5,
86 LINE_ATTRIBUTE6,
87 LINE_ATTRIBUTE7,
88 LINE_ATTRIBUTE8,
89 LINE_ATTRIBUTE9,
90 LINE_ATTRIBUTE10,
91 LINE_ATTRIBUTE11,
92 LINE_ATTRIBUTE12,
93 LINE_ATTRIBUTE13,
94 LINE_ATTRIBUTE14,
95 LINE_ATTRIBUTE15,
96 MULTI_DISTRIBUTIONS,
97 REQ_DIST_SEQUENCE_ID,
98 ORG_ID,
99 REQUISITION_HEADER_ID,
100 REQUISITION_LINE_ID,
101 EMERGENCY_PO_NUM)
102 select
103 l_trans_id,
104 'ICX',
105 'VENDOR',
106 'PURCHASE',
107 rtrim(isc.DESTINATION_TYPE_CODE, ' '),
108 rtrim(ici.ITEM_DESCRIPTION, ' ') ,
109 ici.QUANTITY ,
110 round(ici.UNIT_PRICE,5) ,
111 'INCOMPLETE',
112 isc.APPROVER_ID,
113 rtrim(isc.NOTE_TO_APPROVER, ' ') ,
114 hrev.employee_id,
115 'N',
116 rtrim(isc.REQ_NUMBER_SEGMENT1, ' '),
117 rtrim(isc.NOTE_TO_BUYER, ' ') ,
118 ici.ITEM_ID ,
119 rtrim(ici.ITEM_REVISION, ' ') ,
120 -- nvl(ici.acct_id,nvl(msi.expense_account,hrev.default_code_combination_id)),
121 ici.CATEGORY_ID ,
122 rtrim(ici.UNIT_OF_MEASURE, ' ') ,
123 ici.LINE_TYPE_ID ,
124 ici.DESTINATION_ORGANIZATION_ID,
125 ici.DELIVER_TO_LOCATION_ID,
126 isc.DELIVER_TO_REQUESTOR_ID,
127 nvl(ici.suggested_buyer_id,nvl(msi.buyer_id,poh.agent_id)),
128 rtrim(ici.SUGGESTED_VENDOR_NAME, ' '),
129 rtrim(ici.SUGGESTED_VENDOR_SITE, ' '),
130 rtrim(ici.SUGGESTED_VENDOR_CONTACT, ' '),
131 rtrim(ici.SUGGESTED_VENDOR_PHONE, ' '),
135 -- updated with the line level need by date. Sai 8/6/97.
132 rtrim(ici.SUGGESTED_VENDOR_ITEM_NUM, ' '),
133 -- nvl(isc.need_by_date,sysdate),
134 -- The above is commented. The need by date at the line level need to
136 nvl(trunc(ici.need_by_date),trunc(sysdate)),
137 ici.autosource_doc_header_id,
138 ici.autosource_doc_line_num,
139 poh.type_lookup_code,
140 rtrim(isc.header_description, ' '),
141 rtrim(isc.HEADER_ATTRIBUTE_CATEGORY, ' '),
142 rtrim(isc.HEADER_ATTRIBUTE1, ' '),
143 rtrim(isc.HEADER_ATTRIBUTE2, ' '),
144 rtrim(isc.HEADER_ATTRIBUTE3, ' '),
145 rtrim(isc.HEADER_ATTRIBUTE4, ' '),
146 rtrim(isc.HEADER_ATTRIBUTE5, ' '),
147 rtrim(isc.HEADER_ATTRIBUTE6, ' '),
148 -- isc.HEADER_ATTRIBUTE7,
149 -- The following has been commedted. The Header attribute 7 will be updated
150 -- with reserved_po_num if the customer has emergency po or with header
151 -- attribute 7 if there is no emergency PO. Sai 8/6/97.
152 -- isc.RESERVED_PO_NUM,
153 NVL(rtrim(isc.RESERVED_PO_NUM, ' '), rtrim(isc.HEADER_ATTRIBUTE7, ' ')),
154 rtrim(isc.HEADER_ATTRIBUTE8, ' '),
155 rtrim(isc.HEADER_ATTRIBUTE9, ' '),
156 rtrim(isc.HEADER_ATTRIBUTE10, ' '),
157 rtrim(isc.HEADER_ATTRIBUTE11, ' '),
158 rtrim(isc.HEADER_ATTRIBUTE12, ' '),
159 rtrim(isc.HEADER_ATTRIBUTE13, ' '),
160 rtrim(isc.HEADER_ATTRIBUTE14, ' '),
161 rtrim(isc.HEADER_ATTRIBUTE15, ' '),
162 rtrim(ici.LINE_ATTRIBUTE_CATEGORY, ' '),
163 rtrim(ici.LINE_ATTRIBUTE1, ' '),
164 rtrim(ici.LINE_ATTRIBUTE2, ' '),
165 rtrim(ici.LINE_ATTRIBUTE3, ' '),
166 rtrim(ici.LINE_ATTRIBUTE4, ' '),
167 rtrim(ici.LINE_ATTRIBUTE5, ' '),
168 rtrim(ici.LINE_ATTRIBUTE6, ' '),
169 rtrim(ici.LINE_ATTRIBUTE7, ' '),
170 rtrim(ici.LINE_ATTRIBUTE8, ' '),
171 rtrim(ici.LINE_ATTRIBUTE9, ' '),
172 rtrim(ici.LINE_ATTRIBUTE10, ' '),
173 rtrim(ici.LINE_ATTRIBUTE11, ' '),
174 rtrim(ici.LINE_ATTRIBUTE12, ' '),
175 rtrim(ici.LINE_ATTRIBUTE13, ' '),
176 rtrim(ici.LINE_ATTRIBUTE14, ' '),
177 rtrim(ici.LINE_ATTRIBUTE15, ' '),
178 'Y',
179 l_dist_seq_id,
180 l_org_id,
181 isc.cart_id,
182 ici.cart_line_id,
183 rtrim(isc.RESERVED_PO_NUM, ' ')
184 from icx_shopping_carts isc,
185 icx_shopping_cart_lines ici,
186 mtl_system_items msi,
187 po_headers poh,
188 hr_employees_current_v hrev,
189 FND_USER fwu
190 where isc.shopper_id = fwu.user_id
191 and isc.saved_flag = '0'
192 and ici.cart_id = isc.cart_id
193 and ici.autosource_doc_header_id = poh.po_header_id (+)
194 and ici.item_id = msi.inventory_item_id (+)
195 and ici.destination_organization_id = msi.organization_id (+)
196 and fwu.employee_id = hrev.employee_id
197 and isc.cart_id = l_cart_id
198 and ici.cart_line_id = prec.cart_line_id
199 and nvl(isc.org_id, -9999) = nvl(l_org_id, -9999);
200
201
202 insert into po_req_dist_interface (
203 -- ? DIST_ATTRIBUTE_CATEGORY,
204 TRANSACTION_ID,
205 CHARGE_ACCOUNT_ID,
206 CHARGE_ACCOUNT_SEGMENT1,
207 CHARGE_ACCOUNT_SEGMENT2,
208 CHARGE_ACCOUNT_SEGMENT3,
209 CHARGE_ACCOUNT_SEGMENT4,
210 CHARGE_ACCOUNT_SEGMENT5,
211 REQ_NUMBER_SEGMENT1,
212 -- ? EXPENDITURE_TYPE,
213 DESTINATION_ORGANIZATION_ID,
214 DISTRIBUTION_ATTRIBUTE1,
215 DISTRIBUTION_ATTRIBUTE2,
216 DISTRIBUTION_ATTRIBUTE3,
217 DISTRIBUTION_ATTRIBUTE4,
218 DISTRIBUTION_ATTRIBUTE5,
219 DISTRIBUTION_ATTRIBUTE6,
220 DISTRIBUTION_ATTRIBUTE7,
221 DISTRIBUTION_ATTRIBUTE8,
222 DISTRIBUTION_ATTRIBUTE9,
223 DISTRIBUTION_ATTRIBUTE10,
224 DISTRIBUTION_ATTRIBUTE11,
225 DISTRIBUTION_ATTRIBUTE12,
226 DISTRIBUTION_ATTRIBUTE13,
227 DISTRIBUTION_ATTRIBUTE14,
228 DISTRIBUTION_ATTRIBUTE15,
229 ACCRUAL_ACCOUNT_ID,
230 VARIANCE_ACCOUNT_ID,
231 BUDGET_ACCOUNT_ID,
232 -- ? PROCESS_FLAG,
233 LAST_UPDATED_BY,
234 LAST_UPDATE_DATE,
235 LAST_UPDATE_LOGIN,
236 CREATION_DATE,
237 CREATED_BY,
238 INTERFACE_SOURCE_CODE,
239 -- ? INTERFACE_SOURCE_LINE_ID,
240 -- REQ_DISTRIBUTION_ID,
241 DESTINATION_TYPE_CODE,
242 -- ? QUANTITY,
243 CHARGE_ACCOUNT_SEGMENT6,
244 CHARGE_ACCOUNT_SEGMENT7,
245 CHARGE_ACCOUNT_SEGMENT8,
246 CHARGE_ACCOUNT_SEGMENT9,
247 CHARGE_ACCOUNT_SEGMENT10,
248 CHARGE_ACCOUNT_SEGMENT11,
249 CHARGE_ACCOUNT_SEGMENT12,
250 CHARGE_ACCOUNT_SEGMENT13,
251 CHARGE_ACCOUNT_SEGMENT14,
252 CHARGE_ACCOUNT_SEGMENT15,
253 CHARGE_ACCOUNT_SEGMENT16,
254 CHARGE_ACCOUNT_SEGMENT17,
255 CHARGE_ACCOUNT_SEGMENT18,
256 CHARGE_ACCOUNT_SEGMENT19,
257 CHARGE_ACCOUNT_SEGMENT20,
258 CHARGE_ACCOUNT_SEGMENT21,
259 CHARGE_ACCOUNT_SEGMENT22,
260 CHARGE_ACCOUNT_SEGMENT23,
261 CHARGE_ACCOUNT_SEGMENT24,
262 CHARGE_ACCOUNT_SEGMENT25,
263 CHARGE_ACCOUNT_SEGMENT26,
264 CHARGE_ACCOUNT_SEGMENT27,
265 CHARGE_ACCOUNT_SEGMENT28,
266 CHARGE_ACCOUNT_SEGMENT29,
267 CHARGE_ACCOUNT_SEGMENT30,
268 ORG_ID,
269 DIST_SEQUENCE_ID,
270 ITEM_ID,
271 ALLOCATION_TYPE,
272 ALLOCATION_VALUE,
273 DISTRIBUTION_NUMBER)
274 SELECT
275 po_req_dist_interface_s.nextval,
276 icd.CHARGE_ACCOUNT_ID,
277 rtrim(icd.CHARGE_ACCOUNT_SEGMENT1, ' '),
278 rtrim(icd.CHARGE_ACCOUNT_SEGMENT2, ' '),
279 rtrim(icd.CHARGE_ACCOUNT_SEGMENT3, ' '),
280 rtrim(icd.CHARGE_ACCOUNT_SEGMENT4, ' '),
281 rtrim(icd.CHARGE_ACCOUNT_SEGMENT5, ' '),
282 rtrim(isc.REQ_NUMBER_SEGMENT1, ' '),
283 ici.DESTINATION_ORGANIZATION_ID,
284 rtrim(icd.DISTRIBUTION_ATTRIBUTE1, ' '),
285 rtrim(icd.DISTRIBUTION_ATTRIBUTE2, ' '),
286 rtrim(icd.DISTRIBUTION_ATTRIBUTE3, ' '),
287 rtrim(icd.DISTRIBUTION_ATTRIBUTE4, ' '),
288 rtrim(icd.DISTRIBUTION_ATTRIBUTE5, ' '),
289 rtrim(icd.DISTRIBUTION_ATTRIBUTE6, ' '),
290 rtrim(icd.DISTRIBUTION_ATTRIBUTE7, ' '),
291 rtrim(icd.DISTRIBUTION_ATTRIBUTE8, ' '),
292 rtrim(icd.DISTRIBUTION_ATTRIBUTE9, ' '),
293 rtrim(icd.DISTRIBUTION_ATTRIBUTE10, ' '),
294 rtrim(icd.DISTRIBUTION_ATTRIBUTE11, ' '),
295 rtrim(icd.DISTRIBUTION_ATTRIBUTE12, ' '),
296 rtrim(icd.DISTRIBUTION_ATTRIBUTE13, ' '),
297 rtrim(icd.DISTRIBUTION_ATTRIBUTE14, ' '),
298 rtrim(icd.DISTRIBUTION_ATTRIBUTE15, ' '),
299 icd.ACCRUAL_ACCOUNT_ID,
300 icd.VARIANCE_ACCOUNT_ID,
301 icd.BUDGET_ACCOUNT_ID,
302 icd.LAST_UPDATED_BY,
303 icd.LAST_UPDATE_DATE,
304 icd.LAST_UPDATE_LOGIN,
305 icd.CREATION_DATE,
306 icd.CREATED_BY,
307 'ICX',
308 -- icd.DISTRIBUTION_ID,
309 rtrim(isc.DESTINATION_TYPE_CODE, ' '),
310 rtrim(icd.CHARGE_ACCOUNT_SEGMENT6, ' '),
311 rtrim(icd.CHARGE_ACCOUNT_SEGMENT7, ' '),
312 rtrim(icd.CHARGE_ACCOUNT_SEGMENT8, ' '),
313 rtrim(icd.CHARGE_ACCOUNT_SEGMENT9, ' '),
314 rtrim(icd.CHARGE_ACCOUNT_SEGMENT10, ' '),
315 rtrim(icd.CHARGE_ACCOUNT_SEGMENT11, ' '),
316 rtrim(icd.CHARGE_ACCOUNT_SEGMENT12, ' '),
317 rtrim(icd.CHARGE_ACCOUNT_SEGMENT13, ' '),
318 rtrim(icd.CHARGE_ACCOUNT_SEGMENT14, ' '),
319 rtrim(icd.CHARGE_ACCOUNT_SEGMENT15, ' '),
320 rtrim(icd.CHARGE_ACCOUNT_SEGMENT16, ' '),
321 rtrim(icd.CHARGE_ACCOUNT_SEGMENT17, ' '),
322 rtrim(icd.CHARGE_ACCOUNT_SEGMENT18, ' '),
323 rtrim(icd.CHARGE_ACCOUNT_SEGMENT19, ' '),
324 rtrim(icd.CHARGE_ACCOUNT_SEGMENT20, ' '),
325 rtrim(icd.CHARGE_ACCOUNT_SEGMENT21, ' '),
326 rtrim(icd.CHARGE_ACCOUNT_SEGMENT22, ' '),
327 rtrim(icd.CHARGE_ACCOUNT_SEGMENT23, ' '),
328 rtrim(icd.CHARGE_ACCOUNT_SEGMENT24, ' '),
329 rtrim(icd.CHARGE_ACCOUNT_SEGMENT25, ' '),
330 rtrim(icd.CHARGE_ACCOUNT_SEGMENT26, ' '),
331 rtrim(icd.CHARGE_ACCOUNT_SEGMENT27, ' '),
332 rtrim(icd.CHARGE_ACCOUNT_SEGMENT28, ' '),
333 rtrim(icd.CHARGE_ACCOUNT_SEGMENT29, ' '),
334 rtrim(icd.CHARGE_ACCOUNT_SEGMENT30, ' '),
335 icd.ORG_ID,
336 l_dist_seq_id,
337 ici.ITEM_ID,
338 icd.ALLOCATION_TYPE,
339 icd.ALLOCATION_VALUE,
340 icd.DISTRIBUTION_NUM
341 from icx_shopping_carts isc,
342 icx_shopping_cart_lines ici,
343 icx_cart_line_distributions icd
344 where isc.saved_flag = '0'
345 and isc.cart_id = ici.cart_id
346 and ici.cart_id = icd.cart_id
347 and ici.cart_line_id = icd.cart_line_id
348 and isc.cart_id = l_cart_id
349 and ici.cart_line_id = prec.cart_line_id
350 and nvl(isc.org_id,-9999) = nvl(l_org_id,-9999);
351
352 end loop;
353
354 end if;
355
356 EXCEPTION
357 When Others Then
358 po_message_s.sql_error('Load_ShopCart_To_Interface', 1, sqlcode);
359 RAISE;
360
361 End Load_ShopCart_TO_Interface;
362
363 End ICX_LOAD_REQ_INTERFACE;