1 PACKAGE BODY OKC_CREATE_PO_FROM_K_PVT AS
2 /* $Header: OKCRKPOB.pls 120.0 2005/05/26 09:30:43 appldev noship $ */
3
4 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
5
6
7 -- Local private procedure
8 ------------------------------------------------------------------------------
9 ---------------- Procedure: my_debug -------------------------
10 ------------------------------------------------------------------------------
11 -- Purpose: Serves to log debug messages in the log file for the
12 -- concurrent request. Can be changed to print out dbms_output
13 -- statements if testing locally
14 --
15 -- Parameters: As specified below. Currently the level and module are
16 -- not used
17 --
18 -- Out Parameters: None
19 --
20 -------------------------------------------------------------------------------
21 PROCEDURE my_debug( p_msg IN VARCHAR2,
22 p_level IN NUMBER DEFAULT 1,
23 p_module IN VARCHAR2 DEFAULT 'OKC');
24
25
26
27 ----------------------------------------------------------------------------
28 -- Global Constants--------------------------------------------------------
29 ----------------------------------------------------------------------------
30 -- Standard API Constants
31
32 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(30) := 'OKC_CONTRACTS_UNEXP_ERROR';
33 G_SQLCODE_TOKEN CONSTANT VARCHAR2(07) := 'SQLCODE';
34 G_SQLERRM_TOKEN CONSTANT VARCHAR2(07) := 'SQLERRM';
35 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OKC_CREATE_PO_FROM_K_PVT';
36 G_PKG_FILENAME CONSTANT VARCHAR2(12) := 'OKCRKPOB.pls';
37 G_APP_NAME CONSTANT VARCHAR2(03) := OKC_API.G_APP_NAME;
38 G_APP_NAME1 CONSTANT VARCHAR2(03) := 'OKC';
39
40 G_API_TYPE CONSTANT VARCHAR2(08) := '_PROCESS';
41 G_SCOPE CONSTANT VARCHAR2(04) := '_PVT';
42
43
44 -- Constants used for defining rules/rule groups
45 -- Rule group Billing is not being used for Phase I. Rules inside it
46 -- are placed in PAYMENT. Need to modify for phase II
47
48 g_rg_billing CONSTANT VARCHAR2(12) := 'PAYMENT';
49 g_rg_shipping CONSTANT VARCHAR2(12) := 'OKPSHIPPING';
50 g_rg_payment CONSTANT VARCHAR2(12) := 'PAYMENT';
51 g_rg_currency CONSTANT VARCHAR2(12) := 'CURRENCY';
52 --g_ru_billto CONSTANT VARCHAR2(03) := 'BTO'; --currently not used
53 g_ru_billto CONSTANT VARCHAR2(06) := 'OKPBTO';
54 g_ru_shipto CONSTANT VARCHAR2(06) := 'BUYSTO';
55 g_ru_convert CONSTANT VARCHAR2(03) := 'CVN';
56 g_ru_payto CONSTANT VARCHAR2(03) := 'PTO';
57 g_ru_payterms CONSTANT VARCHAR2(03) := 'PTR';
58
59 -- Other Constants
60
61 g_po_hdr_process_code CONSTANT VARCHAR2(12) := 'PENDING';
62 g_po_hdr_action CONSTANT VARCHAR2(12) := 'ORIGINAL';
63 g_po_hdr_document_type_code CONSTANT VARCHAR2(12) := 'STANDARD';
64 g_po_hdr_approval_status CONSTANT VARCHAR2(12) := 'APPROVED';
65 g_po_hdr_interface_source_code CONSTANT VARCHAR2(03) := 'OKP';
66 g_po_hdr_accept_required_flag CONSTANT VARCHAR2(01) := 'N';
67 g_po_hdr_frozen_flag CONSTANT VARCHAR2(01) := 'N';
68 g_po_hdr_approv_required_flag CONSTANT VARCHAR2(01) := 'N';
69 g_po_ln_shipment_type CONSTANT VARCHAR2(08) := 'STANDARD';
70 g_po_dis_destination_type_code CONSTANT VARCHAR2(10) := 'INVENTORY';
71
72 g_sts_signed CONSTANT VARCHAR2(06) := 'SIGNED';
73 g_sts_active CONSTANT VARCHAR2(06) := 'ACTIVE';
74
75 -- Related objects constants
76
77 g_crj_rty_code CONSTANT VARCHAR2(20) := 'CONTRACTCREATESPO';
78 g_crj_chr_jtot_object_code CONSTANT VARCHAR2(20) := 'OKX_PO_HEADERS';
79 g_crj_cle_jtot_object_code CONSTANT VARCHAR2(20) := 'OKX_PO_LINES';
80 ----------------------------------------------------------------------------
81 -- Global Cursors--------------------------------------------------------
82 ----------------------------------------------------------------------------
83
84 -- cursor to get contract header information
85
86 CURSOR c_chr (b_chr_id NUMBER) IS SELECT
87 authoring_org_id
88 ,currency_code
89 ,date_signed approved_date
90 ,po_headers_interface_s.NEXTVAL po_interface_header_id
91 ,DECODE(contract_number_modifier, null,
92 contract_number,
93 contract_number || ' - ' || contract_number_modifier)
94 contract_number_print -- used to form the
95 -- comments in the PO Header
96 -- ,po_headers_s.NEXTVAL po_header_id
97 FROM okc_k_headers_b
98 WHERE id = b_chr_id;
99
100
101 -- cursor to get line information for the top lines related to the contract
102
103 CURSOR c_top_cle(b_chr_id NUMBER) IS
104 SELECT
105 cle.id source_cle_id -- source for the PO line
106 ,cim.object1_id1 -- item_id
107 ,cim.uom_code -- uom_code
108 ,cim.number_of_items qty -- quantity
109 ,cle.price_negotiated/cim.number_of_items -- unit_price
110 ,cle.price_unit -- list_price_per_unit
111 ,trunc(greatest(cle.start_date, sysdate)) need_by_date
112 -- need by date cannot be earlier than sysdate according to a check
113 -- performed in PDOI. Logged bug 2166158 for this
114 FROM
115 okc_k_lines_b cle,
116 okc_statuses_b sts,
117 okc_k_items cim
118 -- jtf_objects_b jot -- (Sangeeta) not necessary to check the source
119 WHERE
120 cim.cle_id = cle.id
121 --AND jot.object_code = cim.jtot_object1_code
122 AND sts.code = cle.sts_code
123 AND cle.dnz_chr_id = b_chr_id
124 AND sts.code in (g_sts_signed, g_sts_active) -- only active and signed lines
125 AND cle.cle_id IS NULL -- only top lines
126 AND cle.price_level_ind = 'Y' -- indicates this is a priced line
127 AND cle.item_to_price_yn = 'Y' -- indicates the item comes from inventory
128 ORDER BY cle.display_sequence;
129 ----------------------------------------------------------------------------
130 -- Global Variables--------------------------------------------------------
131 ----------------------------------------------------------------------------
132
133 G_USER_ID NUMBER;
134 G_LAST_UPDATE_LOGIN NUMBER;
135 G_PROGRAM_ID NUMBER;
136 G_PROGRAM_APPLICATION_ID NUMBER;
137 G_REQUEST_ID NUMBER;
138 g_chr c_chr%ROWTYPE;
139 g_unexp_error exception;
140 g_error exception;
141
142
143 ----------------------------------------------------------------------------
144 -- Global Type Declarations------------------------------------------------
145 ----------------------------------------------------------------------------
146
147 -------------------------------------------------------------------------
148 -- rule_rec_typ holds the header and lines rules associated to a contract (Aida change typ to type)
149 -------------------------------------------------------------------------
150
151 TYPE rule_rec_typ IS RECORD
152 (
153 chr_id okc_k_headers_b.id%TYPE
154 ,cle_id okc_k_lines_b.id%TYPE
155 ,object1_id1 okc_rules_b.object1_id1%TYPE
156 ,object1_id2 okc_rules_b.object1_id2%TYPE
157 ,jtot_object1_code okc_rules_b.jtot_object1_code%TYPE
158 ,object2_id1 okc_rules_b.object2_id1%TYPE
159 ,object2_id2 okc_rules_b.object2_id2%TYPE
160 ,jtot_object2_code okc_rules_b.jtot_object2_code%TYPE
161 ,rule_information_category okc_rules_b.rule_information_category%TYPE
162 ,rule_information1 okc_rules_b.rule_information1%TYPE
163 );
164
165 -------------------------------------------------------------------------
166 -- po_header_rec_type holds the values that will be inserted in
167 -- po_headers_interface table
168 -- interface_header_id - Interface header unique identifier
169 -- document_type_code - Document type to be created. In our case Standard PO
170 -- document_num - number used to uniquely identify the PO in forms and reports
171 -- po_header_id - primary key in po_headers_all table
172 -- agent_id - buyer id
173 -------------------------------------------------------------------------
174
175 TYPE po_header_rec_type IS RECORD(
176 interface_header_id po_headers_interface.interface_header_id%type,
177 org_id okc_k_headers_b.authoring_org_id%type,
178 document_type_code po_headers_interface.document_type_code%type,
179 document_num po_headers_interface.document_num%type,
180 po_header_id po_headers_interface.po_header_id%type,
181 currency_code okc_k_headers_b.currency_code%type,
182 rate_type_code okc_conversion_attribs_v.conversion_type%type,
183 rate_date okc_conversion_attribs_v.conversion_date%type,
184 rate okc_conversion_attribs_v.conversion_rate%type,
185 agent_id okx_buyers_v.id1%type,
186 vendor_id okx_vendors_v.id1%type,
187 vendor_site_id okx_vendor_sites_v.id1%type,
188 vendor_contact_id okc_contacts.object1_id1%type,
189 ship_to_location_id okc_rules_b.object1_id1%type,
190 bill_to_location_id okc_rules_b.object1_id1%type,
191 terms_id okc_rules_b.object1_id1%type,
192 freight_terms okc_rules_b.object1_id1%type,
193 approved_date okc_k_headers_b.date_signed%type,
194 ship_to_organization_id okc_rules_b.object2_id1%type,
195 comments varchar2(100) -- used to form the comments
196 -- with the contract number
197 );
198
199 -------------------------------------------------------------------------
200 -- po_lines_rec_type holds the values that will be inserted in
201 -- po_lines_interface table
202 -- item_id - Item unique identifier
203 -- unit_price - Unit price for the line
204 -- list_price_per_unit - List price for the item on the line
205 -- need_by_date - Date the goods are needed by
206 -- interface_line_id - Interface line unique identifier
207 -- po_line_id - primary key in po_lines_all table
208 -------------------------------------------------------------------------
209
210 TYPE po_lines_rec_type IS RECORD(
211 source_cle_id okc_k_lines_b.id%type,
212 item_id okc_k_items.object1_id1%type,
213 uom_code okc_k_items.uom_code%type,
214 quantity okc_k_items.number_of_items%type,
215 unit_price okc_k_lines_b.price_negotiated%type,
216 list_price_per_unit okc_k_lines_b.price_unit%type,
217 need_by_date okc_k_lines_b.start_date%type,
218 interface_line_id po_lines_interface.interface_line_id%type,
219 po_line_id po_lines_interface.po_line_id%type,
220 interface_header_id po_headers_interface.interface_header_id%type,
221 ship_to_organization_id okc_rules_b.object1_id1%type,
222 ship_to_location_id okc_rules_b.object1_id1%type,
223 terms_id okc_rules_b.object1_id1%type,
224 freight_terms okc_rules_b.object1_id1%type
225 );
226
227 -------------------------------------------------------------------------
228 -- po_distributions_rec_type holds the values that will be inserted in
229 -- po_distributions_interface table
230 -- interface_distribution_id - Interface line unique identifier
231 -- interface_line_id - Unit price for the line
232 -------------------------------------------------------------------------
233
234 TYPE po_distributions_rec_type IS RECORD(
235 interface_header_id po_headers_interface.interface_header_id%type,
236 interface_line_id po_lines_interface.interface_line_id%type,
237 interface_distribution_id po_distributions_interface.interface_distribution_id%type,
238 org_id okc_k_headers_b.authoring_org_id%type,
239 quantity_ordered okc_k_items.number_of_items%type,
240 charge_account_id po_distributions_interface.charge_account_id%TYPE
241 );
242
243
244 ----------------------------------------------------------------------------
245 -- -- TABLE TYPES-----------------------------------------------------------
246 ----------------------------------------------------------------------------
247
248 TYPE po_lines_tab IS TABLE OF po_lines_rec_type INDEX BY BINARY_INTEGER;
249
250 TYPE po_distributions_tab IS TABLE OF po_distributions_rec_type INDEX BY BINARY_INTEGER;
251
252 TYPE NumberTabTyp IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
253
254 TYPE DateTabTyp IS TABLE OF DATE INDEX BY BINARY_INTEGER;
255
256 TYPE Char30TabTyp IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
257
258 TYPE Char3TabTyp IS TABLE OF VARCHAR2(3) INDEX BY BINARY_INTEGER;
259
260
261 ------------------------------------------------------------------------------
262 ---------------- Procedure: my_debug -------------------------
263 ------------------------------------------------------------------------------
264 -- Purpose: Serves to log debug messages in the log file for the
265 -- concurrent request. Can be changed to print out dbms_output
266 -- statements if testing locally
267 --
268 -- Parameters: As specified below. Currently the level and module are
269 -- not used
270 --
271 -- Out Parameters: None
272 --
273 -------------------------------------------------------------------------------
274
275 PROCEDURE my_debug( p_msg IN VARCHAR2,
276 p_level IN NUMBER DEFAULT 1,
277 p_module IN VARCHAR2 DEFAULT 'OKC') IS
278 BEGIN
279
280 fnd_file.put_line(fnd_file.log, g_pkg_filename ||':'||p_msg);
281 -- okc_debug.Log(p_msg,p_level,p_module);
282 -- dbms_output.put_line(substr(p_msg,1,240));
283
284 END my_debug;
285
286
287 --------------------------------------------------------------------------------
288 -- Private Procedures ---------------------------------------------------------
289 --------------------------------------------------------------------------------
290
291 --------------------------------------------------------------------------------
292 -------------- Procedure: set_notification_msg --------------------------------
293 --------------------------------------------------------------------------------
294 -- Purpose: Sets the notification messages on the stack
295 --
296 -- Parameters: See specification below. No special parameters.
297 --
298 --------------------------------------------------------------------------------
299 PROCEDURE set_notification_msg (p_api_version IN NUMBER
300 ,p_init_msg_list IN VARCHAR2
301 ,p_application_name IN VARCHAR2
302 ,p_message_subject IN FND_NEW_MESSAGES.MESSAGE_NAME%TYPE
303 ,p_message_body IN FND_NEW_MESSAGES.MESSAGE_NAME%TYPE
304 ,p_message_body_token1 IN VARCHAR2
305 ,p_message_body_token1_value IN VARCHAR2
306 ,p_message_body_token2 IN VARCHAR2
307 ,p_message_body_token2_value IN VARCHAR2
308 ,p_message_body_token3 IN VARCHAR2
309 ,p_message_body_token3_value IN VARCHAR2
310 ,x_return_status OUT NOCOPY VARCHAR2) IS
311
312 l_init_msg_count NUMBER:=0;
313
317
314 BEGIN
315
316 my_debug('4000: Entered set_notification_msg', 2);
318 my_debug('4010: Initialize Error Message List', 2);
319
320 okc_api.init_msg_list(p_init_msg_list => p_init_msg_list);
321
322 l_init_msg_count:=fnd_msg_pub.count_msg;
323
324 my_debug('4020: Set Notification Messages on the stack', 2);
325
326
327 -- Checking on subject and body message codes not null
328
329 IF NVL(p_message_body,OKC_API.G_MISS_CHAR) = OKC_API.G_MISS_CHAR OR
330 NVL(p_message_subject,OKC_API.G_MISS_CHAR) = OKC_API.G_MISS_CHAR
331 THEN
332 OKC_API.set_message(p_app_name => p_application_name,
333 p_msg_name => 'OKC_K_NO_MSG_NOTIF');
334
335 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
336
337 ELSE
338 IF NVL(p_message_subject,OKC_API.G_MISS_CHAR) <> OKC_API.G_MISS_CHAR
339 THEN
340 my_debug('4030: Setting subject message on the stack', 2);
341
342 OKC_API.set_message(p_app_name => p_application_name
343 ,p_msg_name => p_message_subject
344 ,p_token1 => p_message_body_token1
345 ,p_token1_value => p_message_body_token1_value
346 ,p_token2 => p_message_body_token2
347 ,p_token2_value => p_message_body_token2_value
348 ,p_token3 => p_message_body_token3
349 ,p_token3_value => p_message_body_token3_value
350 );
351 END IF;
352
353 IF NVL(p_message_body,OKC_API.G_MISS_CHAR) <> OKC_API.G_MISS_CHAR
354 THEN
355 my_debug('4040: Setting body message on the stack', 2);
356
357 OKC_API.set_message(p_app_name => p_application_name
358 ,p_msg_name => p_message_body
359 ,p_token1 => p_message_body_token1
360 ,p_token1_value => p_message_body_token1_value
361 ,p_token2 => p_message_body_token2
362 ,p_token2_value => p_message_body_token2_value
363 ,p_token3 => p_message_body_token3
364 ,p_token3_value => p_message_body_token3_value
365 );
366 END IF;
367 END IF;
368
369 x_return_status := OKC_API.G_RET_STS_SUCCESS;
370
371 my_debug('4040: Exiting set_notification_msg', 2);
372
373
374 EXCEPTION
375 WHEN OKC_API.G_EXCEPTION_ERROR THEN
376 x_return_status := OKC_API.G_RET_STS_ERROR;
377
378 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
379 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
380
381 WHEN OTHERS THEN
382 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
383 OKC_API.set_message(G_APP_NAME
384 ,G_UNEXPECTED_ERROR
385 ,G_SQLCODE_TOKEN
386 ,SQLCODE
387 ,G_SQLERRM_TOKEN
388 ,SQLERRM);
389 END set_notification_msg;
390
391 --------------------------------------------------------------------------------
392 -------------- Procedure: notify_buyer ------------------------------
393 --------------------------------------------------------------------------------
394 -- Purpose: Notify the buyer of a purchase order creation
395 --
396 -- Parameters: See specification below. No special parameters.
397 --
398 --------------------------------------------------------------------------------
399
400 PROCEDURE notify_buyer(p_api_version IN NUMBER
401 ,p_init_msg_list IN VARCHAR2
402 ,p_application_name IN VARCHAR2
403 ,p_message_subject IN FND_NEW_MESSAGES.MESSAGE_NAME%TYPE
404 ,p_message_body IN FND_NEW_MESSAGES.MESSAGE_NAME%TYPE
405 ,p_message_body_token1 IN VARCHAR2
406 ,p_message_body_token1_value IN VARCHAR2
407 ,p_message_body_token2 IN VARCHAR2
408 ,p_message_body_token2_value IN VARCHAR2
409 ,p_message_body_token3 IN VARCHAR2
410 ,p_message_body_token3_value IN VARCHAR2
411 ,p_chr_id IN OKC_K_HEADERS_B.ID%TYPE
412 ,x_k_buyer_name OUT NOCOPY VARCHAR2
413 ,x_return_status OUT NOCOPY VARCHAR2
414 ,x_msg_count OUT NOCOPY NUMBER
415 ,x_msg_data OUT NOCOPY VARCHAR2) IS
416
417 -- standard api variables
418 l_api_version CONSTANT NUMBER := 1;
419 l_api_name CONSTANT VARCHAR2(30) := 'notify_buyer';
420 lx_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
421 lx_msg_count NUMBER := 0;
422 lx_msg_data VARCHAR2(2000);
423 l_init_msg_count NUMBER:=0;
424
425 l_notif_flag VARCHAR2(1):=OKC_API.g_false;
426 l_send_notif VARCHAR2(1):=OKC_API.g_false;
427 l_notification_string VARCHAR2(4000);
428 l_k_buyer_name fnd_user.user_name%TYPE;
429
430
431
432 BEGIN
433
434 my_debug('5010: Entered notify_buyer', 2);
435
436 my_debug('5010: Initialize error message list', 2);
437
438 okc_api.init_msg_list(p_init_msg_list => p_init_msg_list);
439
443 -- Notify the buyer of a po creation
440 l_init_msg_count:=fnd_msg_pub.count_msg;
441
442
444 -- Call the workflow notification alert */
445
446 SELECT user_name
447 INTO l_k_buyer_name
448 FROM FND_USER
449 WHERE EMPLOYEE_ID = (SELECT cco.object1_id1 agent_id
450 FROM okc_contacts cco
451 WHERE cco.cro_code = 'BUYER'
452 AND cco.jtot_object1_code = 'OKX_BUYER'
453 AND cco.dnz_chr_id = p_chr_id
454 AND rownum = 1)
455 AND rownum = 1;
456
457
458 IF SQL%FOUND AND NVL(l_k_buyer_name, okc_api.g_miss_char) <> okc_api.g_miss_char THEN
459
460 my_debug('5020: Buyer name' || l_k_buyer_name , 2);
461
462 l_send_notif := OKC_API.G_TRUE;
463
464 x_k_buyer_name := l_k_buyer_name;
465
466 ELSE
467
468 l_send_notif := OKC_API.G_FALSE;
469
470 OKC_API.set_message(p_app_name => g_app_name, --OKC
471 p_msg_name => 'OKC_K_NOKBUYER',
472 p_token1 => 'KNUMBER',
473 p_token1_value => p_chr_id);
474
475 END IF;
476
477
478 IF l_send_notif = OKC_API.g_true THEN
479
480 -- Prepare the procedure to be executed through the
481 -- workflow alert system
482
483 -- The construction of the string is based on the parameters passed
484
485 l_notification_string := 'BEGIN OKC_CREATE_PO_FROM_K_PVT.set_notification_msg(' ||
486 'p_api_version =>1'||
487 ',p_init_msg_list =>'||''''||p_init_msg_list||''''||
488 ',p_application_name =>'||''''||p_application_name||''''||
489 ',p_message_subject =>'||''''||p_message_subject ||''''||
490 ',p_message_body =>'||''''||p_message_body||'''' ;
491
492
493 IF NVL(p_message_body_token1, OKC_API.G_MISS_CHAR) <>
494 OKC_API.G_MISS_CHAR AND
495 NVL(p_message_body_token1_value, OKC_API.G_MISS_CHAR) <>
496 OKC_API.G_MISS_CHAR THEN
497 l_notification_string := l_notification_string||',p_message_body_token1 =>' ||''''|| p_message_body_token1 ||'''';
498 l_notification_string := l_notification_string||',p_message_body_token1_value =>' ||''''|| p_message_body_token1_value ||'''';
499 END IF;
500 IF NVL(p_message_body_token2, OKC_API.G_MISS_CHAR) <>
501 OKC_API.G_MISS_CHAR AND
502 NVL(p_message_body_token2_value, OKC_API.G_MISS_CHAR) <>
503 OKC_API.G_MISS_CHAR THEN
504 l_notification_string := l_notification_string||',p_message_body_token2 =>' ||''''|| p_message_body_token2 ||'''';
505 l_notification_string := l_notification_string||',p_message_body_token2_value =>' ||''''|| p_message_body_token2_value ||'''';
506 END IF;
507 IF NVL(p_message_body_token3, OKC_API.G_MISS_CHAR) <>
508 OKC_API.G_MISS_CHAR AND
509 NVL(p_message_body_token3_value, OKC_API.G_MISS_CHAR) <>
510 OKC_API.G_MISS_CHAR THEN
511 l_notification_string := l_notification_string||',p_message_body_token3 =>' ||''''|| p_message_body_token3 ||'''';
512 l_notification_string := l_notification_string||',p_message_body_token3_value =>' ||''''|| p_message_body_token3_value ||'''';
513 END IF;
514
515 l_notification_string := l_notification_string||',x_return_status => :1); END;';
516
517
518 -- Submit the procedure to the workflow alert system once to notify the buyer
519
520
521 IF NVL(l_k_buyer_name, okc_api.g_miss_char) <> okc_api.g_miss_char THEN
522
523
524 my_debug('5030: Notify the buyer' , 2);
525
526 my_debug('5040: Call OKC_ASYNC_PUB' , 2);
527
528 my_debug('5050: Notification string = ' || l_notification_string , 2);
529
530
531 OKC_ASYNC_PUB.loop_call(p_api_version => 1,
532 p_proc => l_notification_string,
533 p_s_recipient => l_k_buyer_name,
534 p_e_recipient => l_k_buyer_name,
535 p_contract_id => p_chr_id,
536 x_return_status => lx_return_status,
537 x_msg_count => lx_msg_count,
538 x_msg_data => lx_msg_data
539 );
540
541
542 my_debug('5060: End OKC_ASYNC_PUB' , 2);
543
544 my_debug('5070: Return status: ' || lx_return_status, 2);
545
546
547 IF (lx_return_status = OKC_API.G_RET_STS_UNEXP_ERROR OR
548 lx_return_status = OKC_API.G_RET_STS_ERROR) THEN
549
550 okc_api.set_message(p_app_name => g_app_name,
551 p_msg_name => 'OKC_K_BUYER_NOTIFFAILURE',
552 p_token1 => 'KBUYER',
553 p_token1_value => l_k_buyer_name);
554
555 END IF;
556
557 IF (lx_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
558
559 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
560
561 ELSIF (lx_return_status = OKC_API.G_RET_STS_ERROR) THEN
562
566
563 RAISE OKC_API.G_EXCEPTION_ERROR;
564
565 END IF;
567 END IF; -- IF NVL(l_k_buyer_name, okc_api.g_miss_char) <> okc_api.g_miss_char
568
569
570 END IF; -- IF l_send_notif = OKC_API.g_true THEN
571
572 my_debug('5080: INITIALIZE ERROR MESSAGE OUT NOCOPY VARIABLES' , 2);
573
574 FND_MSG_PUB.Count_And_Get (
575 p_count => x_msg_count,
576 p_data => x_msg_data);
577
578 x_msg_count:=x_msg_count - l_init_msg_count;
579
580 x_return_status := OKC_API.G_RET_STS_SUCCESS;
581
582
583
584 EXCEPTION
585 WHEN OKC_API.G_EXCEPTION_ERROR THEN
586
587 x_return_status := OKC_API.G_RET_STS_ERROR;
588
589 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
590 fnd_msg_pub.add_exc_msg(p_pkg_name => g_pkg_name
591 ,p_procedure_name => l_api_name
592 ,p_error_text => 'Encountered error condition'
593 );
594 END IF;
595
596 FND_MSG_PUB.Count_And_Get (
597 p_count => x_msg_count,
598 p_data => x_msg_data);
599 x_msg_count:=x_msg_count - l_init_msg_count;
600
601 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
602
603 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
604
605 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
606 fnd_msg_pub.add_exc_msg(p_pkg_name => g_pkg_name
607 ,p_procedure_name => l_api_name
608 ,p_error_text => 'Encountered unexpected error'
609 );
610 END IF;
611
612 FND_MSG_PUB.Count_And_Get (
613 p_count => x_msg_count,
614 p_data => x_msg_data);
615 x_msg_count:=x_msg_count - l_init_msg_count;
616
617 WHEN OTHERS THEN
618 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
619 OKC_API.set_message(G_APP_NAME
620 ,G_UNEXPECTED_ERROR
621 ,G_SQLCODE_TOKEN
622 ,SQLCODE
623 ,G_SQLERRM_TOKEN
624 ,SQLERRM);
625
626 FND_MSG_PUB.Count_And_Get (
627 p_count => x_msg_count,
628 p_data => x_msg_data);
629 x_msg_count:=x_msg_count - l_init_msg_count;
630
631 END notify_buyer;
632
633 ------------------------------------------------------------------------------
634 ------------------- Procedure: get_k_rules --------------------------
635 ------------------------------------------------------------------------------
636 -- Purpose: Gets the header and lines rules associated to a contract
637 --
638 -- In Parameters: p_chr_id Contract header id
639 -- p_cle_id Topline id
640 --
641 -- In Out Parameters: px_po_header_rec Record to hold po header interface data
642 -- px_po_lines_rec Record to hold po line interface data
643 --
644 -- Out Parameters: x_return_status Standard return status
645 --
646 -- Note: QA check must ensure that the occurrence of any rule does not happen
647 -- more than once per header or line.
648 -------------------------------------------------------------------------------
649
650 PROCEDURE get_k_rules(p_chr_id IN okc_k_headers_b.ID%TYPE,
651 p_cle_id IN okc_k_lines_v.id%TYPE,
652 px_po_header_rec IN OUT NOCOPY po_header_rec_type,
653 px_po_lines_rec IN OUT NOCOPY po_lines_rec_type,
654 x_return_status OUT NOCOPY VARCHAR2 ) IS
655
656 -- cursor to get rule information related to the Contract
657
658 CURSOR c_rules (b_chr_id NUMBER,
659 b_cle_id NUMBER) IS SELECT
660 rgp.chr_id
661 ,rgp.cle_id
662 ,rul.object1_id1
663 ,rul.object1_id2
664 ,rul.jtot_object1_code
665 ,rul.object2_id1
666 ,rul.object2_id2
667 ,rul.jtot_object2_code
668 ,rul.rule_information_category
669 ,rul.rule_information1
670 FROM okc_rule_groups_b rgp
671 ,okc_rules_b rul
672 WHERE
673 rgp.dnz_chr_id = b_chr_id
674 AND rul.rgp_id = rgp.id
675 AND ((rgp.cle_id IS NULL AND b_cle_id IS NULL) OR
676 (b_cle_id IS NOT NULL AND rgp.cle_id = b_cle_id))
677 AND ((rgp.rgd_code = g_rg_billing and rul.rule_information_category = g_ru_billto) or
678 (rgp.rgd_code = g_rg_shipping and rul.rule_information_category = g_ru_shipto) or
679 (rgp.rgd_code = g_rg_payment and rul.rule_information_category = g_ru_payto) or
680 (rgp.rgd_code = g_rg_payment and rul.rule_information_category = g_ru_payterms) or
681 (rgp.rgd_code = g_rg_currency and rul.rule_information_category = g_ru_convert));
682
683
684 -- cursor to get exchange rate information
685
686 CURSOR c_conv_type (b_id1 VARCHAR2) IS
687 SELECT conversion_type,
688 conversion_rate,
689 conversion_date
690 FROM okc_conversion_attribs_v
691 WHERE conversion_type = b_id1
692 AND dnz_chr_id = p_chr_id;
693
694
695
696 l_ru_h_nb NUMBER; -- count number of rules at header level
697 l_ru_l_nb NUMBER; -- count number of rules at line level
698
699
700
704 l_ru_l_nb := 0;
701 BEGIN
702
703 l_ru_h_nb := 0;
705
706 IF (l_debug = 'Y') THEN
707 okc_debug.Set_Indentation('get_k_rules');
708 END IF;
709
710 my_debug('20: Entering get_k_rules', 2);
711
712 x_return_status := OKC_API.G_RET_STS_SUCCESS;
713
714
715 -- rule selection
716
717 FOR r_rule IN c_rules(p_chr_id, p_cle_id) LOOP
718
719 EXIT WHEN c_rules%NOTFOUND;
720
721
722 -- rule at header level only
723
724 IF p_cle_id IS NULL THEN
725
726 IF r_rule.rule_information_category = g_ru_payto THEN
727
728 -- get vendor site id
729
730 px_po_header_rec.vendor_site_id := r_rule.object1_id1;
731
732 my_debug('40: Rule selected: '||g_ru_payto, 1);
733
734 my_debug('60: Vendor Site Id: '||r_rule.object1_id1, 1);
735
736 l_ru_h_nb:=l_ru_h_nb + 1;
737
738 END IF;
739
740 END IF;
741
742
743 -- rule at header level only
744
745 IF p_cle_id IS NULL THEN
746
747 IF r_rule.rule_information_category = g_ru_billto THEN
748
749 -- get bill to location id
750
751 px_po_header_rec.bill_to_location_id := r_rule.object1_id1;
752
753 my_debug('80: Rule selected: '|| g_ru_billto, 1);
754
755 my_debug('100: Bill To Location Id: '||r_rule.object1_id1, 1);
756
757 l_ru_h_nb:=l_ru_h_nb + 1;
758
759 END IF;
760
761 END IF;
762
763
764 -- rule at header level only
765
766 IF p_cle_id IS NULL THEN
767
768 IF r_rule.rule_information_category = g_ru_convert THEN
769
770 -- get rate, rate_type, rate_type_code and rate_date
771
772 OPEN c_conv_type(r_rule.object1_id1);
773 FETCH c_conv_type INTO px_po_header_rec.rate_type_code,
774 px_po_header_rec.rate,
775 px_po_header_rec.rate_date;
776 CLOSE c_conv_type;
777
778 my_debug('120: Rule selected: '|| g_ru_convert, 1);
779
780 my_debug('140: Rate type Code: '|| px_po_header_rec.rate_type_code, 1);
781
782 my_debug('160: Rate: '|| px_po_header_rec.rate, 1);
783
784 my_debug('180: Rate date: '|| px_po_header_rec.rate_date, 1);
785
786 l_ru_h_nb:=l_ru_h_nb + 1;
787
788 END IF;
789
790 END IF;
791
792
793 -- rule at both header and line level
794
795 IF r_rule.rule_information_category = g_ru_payterms THEN
796
797 IF p_cle_id IS NULL THEN -- get header rule
798
799 -- get terms id
800
801 px_po_header_rec.terms_id := r_rule.object1_id1;
802
803 my_debug('200: Rule selected: '|| g_ru_payterms, 1);
804
805 my_debug('220: Terms Id: '|| px_po_header_rec.terms_id, 1);
806
807 l_ru_h_nb:=l_ru_h_nb + 1;
808
809 ELSE
810
811 -- get terms id
812
813 px_po_lines_rec.terms_id := nvl(r_rule.object1_id1, px_po_header_rec.terms_id);
814
815 my_debug('240: Rule selected: '|| g_ru_payterms, 1);
816
817 my_debug('260: Terms Id: '|| px_po_lines_rec.terms_id, 1);
818
819 l_ru_l_nb:=l_ru_l_nb + 1;
820
821 END IF;
822
823 END IF;
824
825
826 -- rule at both header and line level
827
828 IF r_rule.rule_information_category = g_ru_shipto THEN
829
830 IF p_cle_id IS NULL THEN -- get header rule
831
832 -- get ship to location id
833
834 px_po_header_rec.ship_to_location_id := r_rule.object1_id1;
835
836 px_po_header_rec.ship_to_organization_id := r_rule.object2_id1;
837
838 my_debug('280: Rule selected: '|| g_ru_shipto, 1);
839
840 my_debug('300: Ship To Location Id : '|| px_po_header_rec.ship_to_location_id, 1);
841
842 l_ru_h_nb:=l_ru_h_nb + 1;
843
844 ELSE
845
846 -- get ship to location id and ship to organization id
847
848 px_po_lines_rec.ship_to_location_id := nvl(r_rule.object1_id1,px_po_header_rec.ship_to_location_id);
849
850 px_po_lines_rec.ship_to_organization_id := nvl(r_rule.object2_id1,px_po_header_rec.ship_to_organization_id);
851
852 my_debug('320: Rule selected: '|| g_ru_shipto, 1);
853
854 my_debug('340: Ship To Location Id : '|| px_po_lines_rec.ship_to_location_id, 1);
855
856 my_debug('360: Ship To Organization Id : '|| px_po_lines_rec.ship_to_organization_id, 1);
857
858 l_ru_l_nb:=l_ru_l_nb + 1;
859
860
861 END IF;
862
863 END IF;
864
865
866 END LOOP;
867
868
869 IF p_cle_id IS NULL THEN
870 my_debug('380: Rules selection: '||l_ru_h_nb||' rule(s) selected at header level', 1);
871 ELSE
872 my_debug('400: Rules selection: '||l_ru_l_nb||' rule(s) selected at line level', 1);
873 END IF;
877 IF (l_debug = 'Y') THEN
874
875 my_debug('420: Exiting get_k_rules', 2);
876
878 okc_debug.Reset_Indentation;
879 END IF;
880
881 EXCEPTION
882 WHEN OTHERS THEN
883 my_debug('440: error'||substr(sqlerrm,1,240));
884 OKC_API.set_message(p_app_name => g_app_name,
885 p_msg_name => g_unexpected_error,
886 p_token1 => g_sqlcode_token,
887 p_token1_value => sqlcode,
888 p_token2 => g_sqlerrm_token,
889 p_token2_value => sqlerrm);
890 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
891 my_debug('460: Exiting get_k_rules', 2);
892 IF (l_debug = 'Y') THEN
893 okc_debug.Reset_Indentation;
894 END IF;
895
896 IF c_rules%ISOPEN THEN
897 CLOSE c_rules;
898 END IF;
899
900 IF c_conv_type%ISOPEN THEN
901 CLOSE c_conv_type;
902 END IF;
903
904 END get_k_rules;
905
906 --------------------------------------------------------------------------------
907 -------------- Procedure: get_k_info ----------------------------
908 ------------------------------------------------------------------------------------
909 -- Purpose: Gets the relevant contract info at header and line
910 -- level to populate po headers record, po lines table and po distributions table
911 --
912 -- In Parameters: p_chr_id Contract header id
913 --
914 -- Out Parameters: x_return_status Standard return status
915 -- x_po_header_rec Record to hold po header interface data
916 -- x_po_lines_tab Table to hold po lines interface data
917 -- x_po_dist_tab Table to hold po distributions interface data
918 --------------------------------------------------------------------------------
919
920 PROCEDURE get_k_info (p_chr_id IN okc_k_headers_b.ID%TYPE
921 ,x_return_status OUT NOCOPY VARCHAR2
922 ,x_po_header_rec OUT NOCOPY po_header_rec_type
923 ,x_po_lines_tab OUT NOCOPY po_lines_tab
924 ,x_po_dist_tab OUT NOCOPY po_distributions_tab) IS
925
926
927
928 contract_not_found exception;
929 e_exit exception;
930 l_idx pls_integer :=0; -- generic table index
931 po_lines_rec po_lines_rec_type; -- record to hold po line interface data
932 po_header_rec po_header_rec_type; -- record to hold po header interface data
933 l_return_status VARCHAR2(100); -- standard return status
934 l_cpr_id NUMBER; -- to hold k_party_roles id
935
936 BEGIN
937
938
939 -- get contract information
940
941 -- get contract header information
942
943 IF (l_debug = 'Y') THEN
944 okc_debug.Set_Indentation('get_k_info');
945 END IF;
946
947 my_debug('480: Entering get_k_info', 2);
948
949 x_return_status := OKC_API.G_RET_STS_SUCCESS;
950
951 OPEN c_chr(p_chr_id);
952
953 FETCH c_chr INTO g_chr;
954
955 IF c_chr%NOTFOUND THEN
956 -- no header is a fatal error
957 raise contract_not_found;
958 CLOSE c_chr;
959
960 END IF;
961
962 CLOSE c_chr;
963
964
965 x_po_header_rec.org_id := g_chr.authoring_org_id;
966 x_po_header_rec.currency_code := g_chr.currency_code;
967 x_po_header_rec.approved_date := g_chr.approved_date;
968 x_po_header_rec.interface_header_id := g_chr.po_interface_header_id;
969
970 -- The contract number is to be displayed in the comments column of
971 -- the PO Header. Format it using a message
972
973 fnd_message.set_name('OKC','OKC_PO_CREATED_FROM_K_COMMENT');
974 fnd_message.set_token('CONTRACT_NUMBER_PRINT',g_chr.contract_number_print);
975 x_po_header_rec.comments := fnd_message.get;
976
977 my_debug('500: Interface Header Id: '||g_chr.po_interface_header_id, 1);
978 my_debug('520: Contract org id:'||g_chr.authoring_org_id, 1);
979 my_debug('540: Contract currency code:'||g_chr.currency_code, 1);
980 my_debug('560: Contract approved date:'||g_chr.approved_date, 1);
981
982
983 -- select to get agent_id
984 -- This will probably change for phase II when we derive the buyer from the item
985
986 BEGIN
987
988 SELECT cco.object1_id1 agent_id
989 INTO x_po_header_rec.agent_id
990 FROM okc_contacts cco
991 WHERE cco.cro_code = 'BUYER'
992 AND cco.jtot_object1_code = 'OKX_BUYER'
993 AND cco.dnz_chr_id = p_chr_id
994 AND rownum = 1; -- added - just in case there is more than one contact defined
995
996 EXCEPTION
997 WHEN no_data_found THEN
998 my_debug('580: buyer not found', 1);
999 raise g_unexp_error;
1000 END;
1001
1002
1003 -- select to get vendor information. QA check will guarantee there will be only one vendor for a contact
1004
1005 BEGIN
1006
1007 SELECT cpr.object1_id1 vendor_id,
1008 cpr.id
1012 WHERE
1009 INTO x_po_header_rec.vendor_id,
1010 l_cpr_id
1011 FROM okc_k_party_roles_b cpr
1013 cpr.rle_code = 'VENDOR'
1014 AND cpr.jtot_object1_code = 'OKX_VENDOR'
1015 AND cpr.cle_id IS NULL -- header level vendors only
1016 AND cpr.dnz_chr_id = p_chr_id;
1017
1018 EXCEPTION
1019 WHEN no_data_found THEN
1020 my_debug('600: vendor not found', 1);
1021 raise g_unexp_error;
1022 END;
1023
1024
1025 -- select to get vendor_contact information
1026
1027 BEGIN
1028
1029 SELECT cco.object1_id1 vendor_contact_id
1030 INTO x_po_header_rec.vendor_contact_id
1031 FROM okc_contacts cco
1032 WHERE cco.cpl_id = l_cpr_id
1033 AND cco.dnz_chr_id = p_chr_id
1034 AND cco.jtot_object1_code = 'OKX_VCONTACT'
1035 AND rownum = 1; -- added - just in case there is more than one contact defined
1036 EXCEPTION
1037 WHEN no_data_found THEN
1038 my_debug('620: vendor contact not found', 1);
1039 END;
1040
1041
1042 -- call get_k_rules to get rules related to this K at the header level
1043
1044
1045 my_debug('640: Before calling get_k_rules at header level', 1);
1046
1047 get_k_rules (p_chr_id => p_chr_id,
1048 p_cle_id => NULL,
1049 px_po_header_rec => x_po_header_rec,
1050 px_po_lines_rec => po_lines_rec,
1051 x_return_status => l_return_status );
1052
1053 my_debug('660: after calling get_k_rules at header level. Status :'||l_return_status, 1);
1054
1055
1056 IF l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
1057 Raise g_unexp_error;
1058 ELSIF l_return_status = OKC_API.G_RET_STS_ERROR THEN
1059 Raise g_error;
1060 END IF;
1061
1062
1063 -- get contract top lines information
1064
1065
1066 OPEN c_top_cle(p_chr_id);
1067 LOOP
1068 l_idx := l_idx + 1;
1069
1070 FETCH c_top_cle
1071 INTO x_po_lines_tab(l_idx).source_cle_id,
1072 x_po_lines_tab(l_idx).item_id,
1073 x_po_lines_tab(l_idx).uom_code,
1074 x_po_lines_tab(l_idx).quantity,
1075 x_po_lines_tab(l_idx).unit_price,
1076 x_po_lines_tab(l_idx).list_price_per_unit,
1077 x_po_lines_tab(l_idx).need_by_date;
1078
1079 IF c_top_cle%NOTFOUND THEN
1080 IF l_idx = 1 THEN
1081 my_debug('680: contract has no lines', 1);
1082 END IF;
1083 EXIT;
1084 END IF;
1085
1086
1087 x_po_lines_tab(l_idx).interface_header_id := x_po_header_rec.interface_header_id;
1088
1089 -- making this select separately as I got an error when selecting po_lines_interface_s.NEXTVAL in the c_top_cle cursor above
1090
1091 SELECT po_lines_interface_s.NEXTVAL
1092 INTO x_po_lines_tab(l_idx).interface_line_id
1093 FROM dual;
1094
1095 x_po_dist_tab(l_idx).interface_header_id := x_po_header_rec.interface_header_id;
1096
1097 x_po_dist_tab(l_idx).interface_line_id := x_po_lines_tab(l_idx).interface_line_id;
1098
1099 x_po_dist_tab(l_idx).org_id := x_po_header_rec.org_id;
1100
1101 x_po_dist_tab(l_idx).quantity_ordered := x_po_lines_tab(l_idx).quantity;
1102
1103
1104 -- Select the charge account from Inventory. This is a temporary fix
1105 -- because PDOI currently forces you to give a charge account. Ideally
1106 -- this should be derived by PDOI. Log bug <bugNo>
1107
1108 BEGIN
1109
1110 SELECT expense_account
1111 INTO x_po_dist_tab(l_idx).charge_account_id
1112 FROM okx_system_items_v
1113 WHERE inventory_item_id = x_po_lines_tab(l_idx).interface_line_id
1114 AND organization_id = SYS_CONTEXT('OKC_CONTEXT','ORGANIZATION_ID');
1115
1116 EXCEPTION
1117 WHEN no_data_found
1118 THEN
1119 -- This is highly temporary for the demo. Ideally the exception should
1120 -- be handled.
1121 x_po_dist_tab(l_idx).charge_account_id := 13401;
1122 END;
1123
1124
1125 my_debug('700: Contract Line Info:', 1);
1126
1127 my_debug('720: contract line id:'|| x_po_lines_tab(l_idx).source_cle_id, 1);
1128
1129 my_debug('740: item id:'|| x_po_lines_tab(l_idx).item_id, 1);
1130
1131 my_debug('760: uom code:'|| x_po_lines_tab(l_idx).uom_code, 1);
1132
1133 my_debug('780: quantity:'|| x_po_lines_tab(l_idx).quantity, 1);
1134
1135 my_debug('800: unit price:'|| x_po_lines_tab(l_idx).unit_price, 1);
1136
1137 my_debug('820: list price per unit:'|| x_po_lines_tab(l_idx).list_price_per_unit, 1);
1138
1139 my_debug('840: need by date:'|| x_po_lines_tab(l_idx).need_by_date, 1);
1140
1141 my_debug('860: Before calling get_k_rules at line level', 1);
1142
1143
1144 -- call get_k_rules to to get rules related to this K at the lines level
1145
1146
1147 get_k_rules(p_chr_id => p_chr_id,
1148 p_cle_id => x_po_lines_tab(l_idx).source_cle_id,
1149 px_po_header_rec => po_header_rec,
1150 px_po_lines_rec => x_po_lines_tab(l_idx),
1151 x_return_status => l_return_status );
1152
1156
1153
1154
1155 my_debug('880: after calling get_k_rules at line level. Status :'||l_return_status, 1);
1157 IF l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR THEN
1158 Raise g_unexp_error;
1159 ELSIF l_return_status = OKC_API.G_RET_STS_ERROR THEN
1160 Raise g_error;
1161 END IF;
1162
1163
1164 END LOOP;
1165 CLOSE c_top_cle;
1166
1167
1168 my_debug('900: Exiting get_k_info', 2);
1169 IF (l_debug = 'Y') THEN
1170 okc_debug.Reset_Indentation;
1171 END IF;
1172
1173 EXCEPTION
1174 WHEN contract_not_found THEN
1175
1176 x_return_status := OKC_API.G_RET_STS_ERROR;
1177 okc_api.set_message(OKC_API.G_APP_NAME,'OKC_K2O_NOKHDR');
1178 my_debug('920: Exiting get_k_info', 2);
1179 IF (l_debug = 'Y') THEN
1180 okc_debug.Reset_Indentation;
1181 END IF;
1182
1183 WHEN g_error THEN
1184
1185 x_return_status := OKC_API.G_RET_STS_ERROR;
1186
1187 IF c_chr%ISOPEN THEN
1188 CLOSE c_chr;
1189 END IF;
1190
1191 IF c_top_cle%ISOPEN THEN
1192 CLOSE c_top_cle;
1193 END IF;
1194
1195 my_debug('940: Exiting get_k_info', 2);
1196 IF (l_debug = 'Y') THEN
1197 okc_debug.Reset_Indentation;
1198 END IF;
1199
1200 WHEN g_unexp_error THEN
1201
1202 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1203
1204 IF c_chr%ISOPEN THEN
1205 CLOSE c_chr;
1206 END IF;
1207
1208 IF c_top_cle%ISOPEN THEN
1209 CLOSE c_top_cle;
1210 END IF;
1211
1212 my_debug('960: Exiting get_k_info', 2);
1213 IF (l_debug = 'Y') THEN
1214 okc_debug.Reset_Indentation;
1215 END IF;
1216
1217 WHEN OTHERS THEN
1218 my_debug('980: error'||substr(sqlerrm,1,240));
1219 OKC_API.set_message(p_app_name => g_app_name,
1220 p_msg_name => g_unexpected_error,
1221 p_token1 => g_sqlcode_token,
1222 p_token1_value => sqlcode,
1223 p_token2 => g_sqlerrm_token,
1224 p_token2_value => sqlerrm);
1225 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1226
1227 IF c_chr%ISOPEN THEN
1228 CLOSE c_chr;
1229 END IF;
1230
1231 IF c_top_cle%ISOPEN THEN
1232 CLOSE c_top_cle;
1233 END IF;
1234
1235 my_debug('1000: Exiting get_k_info', 2);
1236 IF (l_debug = 'Y') THEN
1237 okc_debug.Reset_Indentation;
1238 END IF;
1239
1240 END get_k_info;
1241
1242 --------------------------------------------------------------------------------
1243 ------------- Procedure: insert_po_hdr ---------------------
1244 --------------------------------------------------------------------------------
1245 -- Purpose: Populate purchase order headers interface table and create
1246 -- related objects for the header
1247 --
1248 -- In Parameters:
1249 -- p_batch_id Batch id of records to be inserted
1250 -- p_po_header_rec Record to hold po header interface data
1251 --
1252 -- Out Parameters: x_return_status Standard return status
1253 -- x_po_header_id This is a temporary parameter
1254 -- that needs to be here because of a
1255 -- bug in PO (PDOI does not accept
1256 -- po_line_id). Hence the related objects
1257 -- for lines is created with po_header_id
1258 -- and line_num. The po_header_id generated
1259 -- in this procedure is passed to the
1260 -- insert_po_lines procedure
1261 -----------------------------------------------------------------------------------
1262
1263 PROCEDURE insert_po_hdr(
1264 p_chr_id IN NUMBER
1265 ,p_batch_id IN NUMBER
1266 ,p_po_header_rec IN po_header_rec_type
1267 ,x_return_status OUT NOCOPY VARCHAR2
1268 ,x_po_header_id OUT NOCOPY po_headers_all.po_header_id%TYPE) IS
1269
1270
1271 l_po_null_rec po_header_rec_type; -- initialize to null
1272
1273 l_po_header_id number; -- Identifier of the PO Header being created
1274
1275 BEGIN
1276
1277 IF (l_debug = 'Y') THEN
1278 okc_debug.Set_Indentation('insert_po_hdr');
1279 END IF;
1280
1281 my_debug('1020: Entering insert_po_hdr', 2);
1282
1283 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1284
1285 my_debug('1040: Batch id = ' || p_batch_id, 2);
1286
1287 -- Select the PO Header id so that it can be populated in related
1288 -- objects and can be logged
1289 SELECT po_headers_s.nextval
1290 INTO l_po_header_id
1291 FROM dual;
1292
1293 my_debug('1060: po_header_id : ' || l_po_header_id,2);
1294 x_po_header_id := l_po_header_id;
1295
1299 interface_header_id,
1296 INSERT INTO po_headers_interface
1297 (
1298 batch_id,
1300 interface_source_code,
1301 process_code,
1302 action,
1303 org_id,
1304 document_type_code,
1305 -- document_num, -- will not be providing for phase I as the setup in demo env. will be set to manual and numbering is numeric
1306 po_header_id,
1307 currency_code,
1308 rate_type_code,
1309 rate_date,
1310 rate,
1311 agent_id,
1312 vendor_id,
1313 vendor_site_id,
1314 vendor_contact_id,
1315 ship_to_location_id,
1316 bill_to_location_id,
1317 terms_id,
1318 freight_terms,
1319 approval_status,
1320 approved_date,
1321 acceptance_required_flag,
1322 frozen_flag,
1323 approval_required_flag,
1324 creation_date,
1325 created_by,
1326 last_update_date,
1327 last_updated_by,
1328 last_update_login,
1329 request_id,
1330 program_application_id,
1331 program_id,
1332 program_update_date,
1333 comments
1334 )
1335 VALUES
1336 (
1337 p_batch_id, -- batch_id
1338 p_po_header_rec.interface_header_id, -- interface_header_id
1339 g_po_hdr_interface_source_code, -- interface_source_code
1340 g_po_hdr_process_code, -- process_code
1341 g_po_hdr_action, -- action
1342 p_po_header_rec.org_id, -- org_id
1343 g_po_hdr_document_type_code, -- document_type_code
1344 -- p_po_header_rec.document_num, -- document_num
1345 l_po_header_id, -- po_header_id
1346 p_po_header_rec.currency_code, -- currency_code
1347 p_po_header_rec.rate_type_code, -- rate_type_code
1348 p_po_header_rec.rate_date, -- rate_date
1349 p_po_header_rec.rate, -- rate
1350 p_po_header_rec.agent_id, -- agent_id
1351 p_po_header_rec.vendor_id, -- vendor_id
1352 p_po_header_rec.vendor_site_id, -- vendor_site_id
1353 p_po_header_rec.vendor_contact_id, -- vendor_contact_id
1354 p_po_header_rec.ship_to_location_id, -- ship_to_location_id
1355 p_po_header_rec.bill_to_location_id, -- bill_to_location_id
1356 p_po_header_rec.terms_id, -- terms_id
1357 p_po_header_rec.freight_terms, -- freight_terms
1358 g_po_hdr_approval_status, -- approval_status
1359 p_po_header_rec.approved_date, -- approved_date
1360 g_po_hdr_accept_required_flag, -- acceptance_required_flag
1361 g_po_hdr_frozen_flag, -- frozen_flag
1362 g_po_hdr_approv_required_flag, -- approval_required_flag
1363 sysdate, -- creation_date
1364 G_USER_ID, -- created_by
1365 sysdate, -- last_update_date
1366 G_USER_ID, -- last_updated_by
1367 G_LAST_UPDATE_LOGIN, -- last_update_login
1368 G_REQUEST_ID, -- request_id
1369 G_PROGRAM_APPLICATION_ID, -- program_application_id
1370 G_PROGRAM_ID, -- program_id
1371 sysdate, -- program_update_date
1372 p_po_header_rec.comments -- comments
1373 );
1374
1375
1376
1377 -- here create the relationship from the po to the contract
1378 -- Insert record into OKC_K_REL_OBJS to record the link between the
1379 -- Contract header and PO header. Currently, this is a direct insert.
1380
1381 INSERT INTO OKC_K_REL_OBJS
1382 ( id,
1383 cle_id,
1384 chr_id,
1385 rty_code,
1386 object1_id1,
1387 object1_id2,
1388 jtot_object1_code,
1389 object_version_number,
1390 created_by,
1391 creation_date,
1392 last_updated_by,
1393 last_update_date,
1394 last_update_login
1395 )
1396 VALUES
1397 (
1398 okc_p_util.raw_to_number(sys_guid()) -- id
1399 ,null -- cle_id
1400 ,p_chr_id -- chr_id
1401 ,g_crj_rty_code -- rty_code
1402 ,l_po_header_id -- object1_id1
1403 ,'#' -- object1_id2
1404 ,g_crj_chr_jtot_object_code -- jtot_object1_code
1405 ,1 -- object_version_number
1406 ,G_USER_ID -- created_by
1407 ,sysdate -- creation_date
1408 ,G_USER_ID -- last_updated_by
1409 ,sysdate -- last_update_date
1410 ,g_last_update_login -- last_update_login
1411 );
1412
1413
1414 my_debug('1080: Inserted rows into OKC_K_REL_OBJS for Header: ' || sql%ROWCOUNT , 4);
1415
1416 -- cleanup
1417
1418 -- p_po_header_rec := l_po_null_rec; -- uncomment this line when create relationship from po to contract above is done
1419
1420 my_debug('1100: Exiting insert_po_hdr', 2);
1421
1422 IF (l_debug = 'Y') THEN
1426 EXCEPTION
1423 okc_debug.Reset_Indentation;
1424 END IF;
1425
1427 WHEN OTHERS THEN
1428 my_debug('1120: error'||substr(sqlerrm,1,240));
1429 OKC_API.set_message(p_app_name => g_app_name,
1430 p_msg_name => g_unexpected_error,
1431 p_token1 => g_sqlcode_token,
1432 p_token1_value => sqlcode,
1433 p_token2 => g_sqlerrm_token,
1434 p_token2_value => sqlerrm);
1435 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1436 my_debug('1140: Exiting insert_po_hdr', 4);
1437 IF (l_debug = 'Y') THEN
1438 okc_debug.Reset_Indentation;
1439 END IF;
1440
1441
1442 END insert_po_hdr;
1443
1444 --------------------------------------------------------------------------------
1445 ------------- Procedure: insert_po_lines ---------------------
1446 --------------------------------------------------------------------------------
1447 -- Purpose: Populate purchase order lines interface table and create
1448 -- related objects for lines
1449 --
1450 -- In Parameters: p_po_lines_tab Table to hold po lines interface data
1451 --
1452 -- Out Parameters: x_return_status Standard return status
1453 ----------------------------------------------------------------------------
1454
1455 PROCEDURE insert_po_lines(p_chr_id IN okc_k_headers_b.id%TYPE
1456 ,p_po_lines_tab IN po_lines_tab
1457 ,p_po_header_id IN po_lines_all.po_header_id%TYPE
1458 ,x_return_status OUT NOCOPY VARCHAR2) IS
1459
1460
1461 -- Declaration of individual elements to avoid ORA-3113 error because
1462 -- FORALL does not allow insert of elements of %rowtype
1463
1464 ls_freight_terms Char30TabTyp;
1465 ls_interface_header_id NumberTabTyp;
1466 ls_interface_line_id NumberTabTyp;
1467 ls_item_id NumberTabTyp;
1468 ls_line_num NumberTabTyp;
1469 ls_list_price_per_unit NumberTabTyp;
1470 ls_need_by_date DateTabTyp;
1471 ls_po_line_id NumberTabTyp;
1472 ls_quantity NumberTabTyp;
1473 ls_ship_to_location_id NumberTabTyp;
1474 ls_ship_to_organization_id NumberTabTyp;
1475 ls_source_cle_id NumberTabTyp;
1476 ls_unit_price NumberTabTyp;
1477 ls_uom_code Char3TabTyp;
1478
1479
1480
1481 BEGIN
1482
1483 IF (l_debug = 'Y') THEN
1484 okc_debug.Set_Indentation('insert_po_lines');
1485 END IF;
1486
1487 my_debug('1160: Entering insert_po_lines', 2);
1488
1489 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1490
1491 -- Map all values into single table arrays to avoid Oracle errors
1492 -- caused by using rec(i).field
1493
1494 my_debug('1180: p_po_lines_tab.first = ' || p_po_lines_tab.first, 2);
1495 my_debug('1200: p_po_lines_tab.last = ' || p_po_lines_tab.last , 2);
1496
1497 FOR i IN p_po_lines_tab.first..p_po_lines_tab.last
1498 LOOP
1499 ls_interface_header_id(i) := p_po_lines_tab(i).interface_header_id;
1500 ls_interface_line_id(i) := p_po_lines_tab(i).interface_line_id;
1501 -- ls_po_line_id(i) := p_po_lines_tab(i).po_line_id; -- uncomment if we are able to generate po_line_id upfront
1502
1503 -- Line number set to sequential number for all records
1504 -- Need to change this when we introduce multiple shipments for a line
1505 ls_line_num(i) := i;
1506
1507 ls_freight_terms(i) := p_po_lines_tab(i).freight_terms;
1508 ls_item_id(i) := p_po_lines_tab(i).item_id;
1509 ls_list_price_per_unit(i) := p_po_lines_tab(i).list_price_per_unit;
1510 ls_need_by_date(i) := p_po_lines_tab(i).need_by_date;
1511 ls_quantity(i) := p_po_lines_tab(i).quantity;
1512 ls_ship_to_location_id(i) := p_po_lines_tab(i).ship_to_location_id;
1513 ls_ship_to_organization_id(i) := p_po_lines_tab(i).ship_to_organization_id;
1514 ls_source_cle_id(i) := p_po_lines_tab(i).source_cle_id;
1515 ls_unit_price(i) := p_po_lines_tab(i).unit_price;
1516 ls_uom_code(i) := p_po_lines_tab(i).uom_code;
1517
1518 my_debug('1220: i = ' || i,2);
1519 my_debug('1240: ls_interface_header_id(i) = ' || ls_interface_header_id(i),2);
1520 my_debug('1260: ls_interface_line_id(i) = ' || ls_interface_line_id(i),2);
1521 my_debug('1280: ls_line_num(i) = ' || ls_line_num(i),2);
1522 my_debug('1300: g_po_ln_shipment_type = ' || g_po_ln_shipment_type,2);
1523 my_debug('1320: ls_item_id(i) = ' || ls_item_id(i),2);
1524 my_debug('1340: ls_uom_code(i) = ' || ls_uom_code(i),2);
1525 my_debug('1360: ls_quantity(i) = ' || ls_quantity(i),2);
1526 my_debug('1380: ls_unit_price(i) = ' || ls_unit_price(i),2);
1527 my_debug('1400: ls_list_price_per_unit(i) = ' || ls_list_price_per_unit(i),2);
1528 my_debug('1420: ls_ship_to_organization_id(i) = ' || ls_ship_to_organization_id(i),2);
1529 my_debug('1440: ls_ship_to_location_id(i) = ' || ls_ship_to_location_id(i),2);
1530 my_debug('1460: ls_need_by_date(i) = ' || ls_need_by_date(i),2);
1531 my_debug('1480: ls_freight_terms(i) = ' || ls_freight_terms(i),2);
1535 my_debug('1560: G_PROGRAM_APPLICATION_ID = ' || G_PROGRAM_APPLICATION_ID,2);
1532 my_debug('1500: G_LAST_UPDATE_LOGIN = ' || G_LAST_UPDATE_LOGIN,2);
1533 my_debug('1520: G_USER_ID = ' || G_USER_ID,2);
1534 my_debug('1540: G_REQUEST_ID = ' || G_REQUEST_ID,2);
1536 my_debug('1580: G_PROGRAM_ID = ' || G_PROGRAM_ID,2);
1537
1538 END LOOP;
1539
1540
1541 IF p_po_lines_tab.first is not NULL
1542 THEN
1543 FORALL i IN p_po_lines_tab.first..p_po_lines_tab.last
1544
1545 INSERT INTO PO_LINES_INTERFACE
1546 (
1547 interface_line_id,
1548 interface_header_id,
1549 line_num,
1550 -- po_line_id, -- uncomment if we are able to pass po_line_id
1551 shipment_type,
1552 item_id,
1553 uom_code,
1554 quantity,
1555 unit_price,
1556 list_price_per_unit,
1557 ship_to_organization_id,
1558 ship_to_location_id,
1559 need_by_date,
1560 freight_terms,
1561 last_update_date,
1562 last_updated_by,
1563 last_update_login,
1564 creation_date,
1565 created_by,
1566 request_id,
1567 program_application_id,
1568 program_id,
1569 program_update_date
1570 )
1571 VALUES
1572 (
1573 ls_interface_line_id(i), -- interface_line_id
1574 ls_interface_header_id(i), -- interface_header_id
1575 ls_line_num(i), -- line_num
1576 --null, -- po_line_id
1577 g_po_ln_shipment_type, -- shipment_type
1578 ls_item_id(i), -- item_id
1579 ls_uom_code(i), -- uom_code
1580 ls_quantity(i), -- quantity
1581 ls_unit_price(i), -- unit_price
1582 ls_list_price_per_unit(i), -- list_price_per_unit
1583 ls_ship_to_organization_id(i), -- ship_to_organization_id
1584 ls_ship_to_location_id(i), -- ship_to_location_id
1585 ls_need_by_date(i), -- need_by_date
1586 ls_freight_terms(i), -- freight_terms
1587 sysdate, -- last_update_date
1588 G_USER_ID, -- last_updated_by
1589 G_LAST_UPDATE_LOGIN, -- last_update_login
1590 sysdate, -- creation_date
1591 G_USER_ID, -- created_by
1592 G_REQUEST_ID, -- request_id
1593 G_PROGRAM_APPLICATION_ID, -- program_application_id
1594 G_PROGRAM_ID, -- program_id
1595 sysdate -- program_update_date
1596 );
1597
1598
1599 -- Here create the relationship from the po line to the contract line
1600 -- Insert record into OKC_K_REL_OBJS to record the link between the
1601 -- Contract header and PO header. Currently, this is a direct insert.
1602 -- Need to modify this call to pass the entire table once
1603 -- the related objects API has a bulk insert API without validations
1604
1605 -- Normally, we would insert the relationship between cle_id and the
1606 -- po_line_id. However, because of a current bug, PDOI does not accept
1607 -- po_line_id. Hence, the relationship table is populated with
1608 -- po_header_id and line_num and is updated during the final call to
1609 -- this program with the correct po_line_id (after the PO has been
1610 -- created)
1611
1612 FORALL i IN p_po_lines_tab.first..p_po_lines_tab.last
1613 INSERT INTO OKC_K_REL_OBJS
1614 ( id,
1615 cle_id,
1616 chr_id,
1617 rty_code,
1618 object1_id1,
1619 object1_id2,
1620 jtot_object1_code,
1621 object_version_number,
1622 created_by,
1623 creation_date,
1624 last_updated_by,
1625 last_update_date,
1626 last_update_login
1627 )
1628 VALUES
1629 (
1630 okc_p_util.raw_to_number(sys_guid()) -- id
1631 ,ls_source_cle_id(i) -- cle_id
1632 ,p_chr_id -- chr_id
1633 ,g_crj_rty_code -- rty_code
1634 ,p_po_header_id -- object1_id1 -- see note above
1635 ,ls_line_num(i) -- object1_id2 -- see note above
1636 ,g_crj_cle_jtot_object_code -- jtot_object1_code
1637 ,1 -- object_version_number
1638 ,G_USER_ID -- created_by
1639 ,sysdate -- creation_date
1640 ,G_USER_ID -- last_updated_by
1641 ,sysdate -- last_update_date
1642 ,g_last_update_login -- last_update_login
1643 );
1644 -- cleanup
1645
1646 ls_interface_line_id.delete;
1647 ls_interface_header_id.delete;
1648 ls_line_num.delete;
1649 ls_po_line_id.delete;
1650 ls_item_id.delete;
1651 ls_uom_code.delete;
1652 ls_quantity.delete;
1653 ls_unit_price.delete;
1654 ls_list_price_per_unit.delete;
1655 ls_ship_to_organization_id.delete;
1656 ls_ship_to_location_id.delete;
1657 ls_need_by_date.delete;
1658 ls_freight_terms.delete;
1662 --p_po_lines_tab.delete; -- uncomment this line when create relationship from po to contract above is done
1659 ls_source_cle_id.delete;
1660
1661
1663
1664 END IF;
1665
1666 my_debug('1560: Exiting insert_po_lines', 2);
1667
1668 IF (l_debug = 'Y') THEN
1669 okc_debug.Reset_Indentation;
1670 END IF;
1671
1672 EXCEPTION
1673 WHEN OTHERS THEN
1674 my_debug('1580: error'||substr(sqlerrm,1,240));
1675 OKC_API.set_message(p_app_name => g_app_name,
1676 p_msg_name => g_unexpected_error,
1677 p_token1 => g_sqlcode_token,
1678 p_token1_value => sqlcode,
1679 p_token2 => g_sqlerrm_token,
1680 p_token2_value => sqlerrm);
1681 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1682 my_debug('1600: Exiting insert_po_lines', 4);
1683 IF (l_debug = 'Y') THEN
1684 okc_debug.Reset_Indentation;
1685 END IF;
1686
1687
1688 END insert_po_lines;
1689
1690 --------------------------------------------------------------------------------
1691 ------------- Procedure: insert_po_distributions ---------------------
1692 --------------------------------------------------------------------------------
1693 -- Purpose: Populate purchase order distributions interface table
1694
1695 -- In Parameters: p_po_dist_tab Table to hold po distributions interface data
1696
1697 -- Out Parameters: x_return_status Standard return status
1698 ------------------------------------------------------------------------------------
1699
1700 PROCEDURE insert_po_distributions(p_po_dist_tab IN po_distributions_tab
1701 ,x_return_status OUT NOCOPY VARCHAR2 ) IS
1702
1703 -- Declaration of individual elements to avoid ORA-3113 error because
1704 -- FORALL does not allow insert of elements of %rowtype
1705
1706 ls_interface_header_id NumberTabTyp;
1707 ls_interface_line_id NumberTabTyp;
1708 ls_interface_distribution_id NumberTabTyp;
1709 ls_org_id NumberTabTyp;
1710 ls_quantity_ordered NumberTabTyp;
1711 ls_charge_account_id NumberTabTyp;
1712
1713
1714 BEGIN
1715
1716 IF (l_debug = 'Y') THEN
1717 okc_debug.Set_Indentation('insert_po_distributions');
1718 END IF;
1719
1720 my_debug('1620: Entering insert_po_distributions', 2);
1721
1722 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1723
1724
1725 -- Map all values into single table arrays to avoid Oracle errors
1726 -- caused by using rec(i).field
1727
1728 FOR i IN p_po_dist_tab.first..p_po_dist_tab.last
1729 LOOP
1730 ls_interface_header_id(i) := p_po_dist_tab(i).interface_header_id;
1731 ls_interface_line_id(i) := p_po_dist_tab(i).interface_line_id;
1732 ls_interface_distribution_id(i) := p_po_dist_tab(i).interface_distribution_id;
1733 ls_org_id(i) := p_po_dist_tab(i).org_id;
1734 ls_quantity_ordered(i) := p_po_dist_tab(i).quantity_ordered;
1735 ls_charge_account_id(i) := p_po_dist_tab(i).charge_account_id;
1736
1737 my_debug('1640: PO distribution org id:'|| p_po_dist_tab(i).org_id, 1);
1738
1739 my_debug('1660: PO distribution quantity ordered:'|| p_po_dist_tab(i).quantity_ordered, 1);
1740 my_debug('1680: PO charge_Account_id :'|| p_po_dist_tab(i).charge_account_id, 1);
1741
1742 END LOOP;
1743
1744 IF p_po_dist_tab.first is not NULL THEN
1745 FORALL i IN p_po_dist_tab.first..p_po_dist_tab.last
1746
1747 INSERT INTO PO_DISTRIBUTIONS_INTERFACE
1748 (
1749 interface_header_id,
1750 interface_line_id,
1751 interface_distribution_id,
1752 distribution_num,
1753 org_id,
1754 quantity_ordered,
1755 destination_type_code,
1756 charge_account_id
1757 )
1758 VALUES
1759 (
1760 ls_interface_header_id(i), -- interface_header_id
1761 ls_interface_line_id(i), -- interface_line_id
1762 po_distributions_interface_s.NEXTVAL, -- interface_distribution_id
1763 1, -- distribution_num
1764 ls_org_id(i), -- org_id
1765 ls_quantity_ordered(i), -- quantity_ordered
1766 g_po_dis_destination_type_code, -- destination_type_code
1767 ls_charge_account_id(i) -- charge_account_id
1768 );
1769
1770
1771
1772 -- cleanup
1773
1774 -- ls_interface_header_id.delete;
1775 -- ls_interface_line_id.delete;
1776 -- ls_interface_distribution_id.delete;
1777 -- ls_distribution_num.delete;
1778
1779 commit;
1780 ls_org_id.delete;
1781 ls_quantity_ordered.delete;
1782
1783
1784 -- p_po_dist_tab.delete;
1785
1786 my_debug('1700: Exiting insert_po_distributions', 2);
1787
1788 IF (l_debug = 'Y') THEN
1789 okc_debug.Reset_Indentation;
1790 END IF;
1791
1792 END IF;
1793
1797 OKC_API.set_message(p_app_name => g_app_name,
1794 EXCEPTION
1795 WHEN OTHERS THEN
1796 my_debug('1720: error'||substr(sqlerrm,1,240));
1798 p_msg_name => g_unexpected_error,
1799 p_token1 => g_sqlcode_token,
1800 p_token1_value => sqlcode,
1801 p_token2 => g_sqlerrm_token,
1802 p_token2_value => sqlerrm);
1803 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1804 my_debug('1740: Exiting insert_po_distributions', 4);
1805 IF (l_debug = 'Y') THEN
1806 okc_debug.Reset_Indentation;
1807 END IF;
1808
1809 END insert_po_distributions;
1810
1811
1812 ----------------------------------------------------------------------------
1813 -- Public Procedure -------------------------------------------------------
1814 ----------------------------------------------------------------------------
1815
1816 --------------------------------------------------------------------------------
1817 ------------- Procedure: create_po_from_k ---------------------
1818 --------------------------------------------------------------------------------
1819 -- Purpose: Create a purchase order from a contract by populating the po
1820 -- interface tables
1821 --
1822 -- In Parameters: p_chr_id Contract header id
1823 -- p_batch_id Batch id
1824 --
1825 -- Out Parameters: x_return_status Standard return status
1826 ----------------------------------------------------------------------------------
1827
1828 PROCEDURE create_po_from_k(p_api_version IN NUMBER
1829 ,p_init_msg_list IN VARCHAR2
1830 ,p_chr_id IN okc_k_headers_b.ID%TYPE
1831 ,x_return_status OUT NOCOPY VARCHAR2
1832 ,x_msg_count OUT NOCOPY NUMBER
1833 ,x_msg_data OUT NOCOPY VARCHAR2) IS
1834
1835 -- standard api variables
1836
1837 l_api_version CONSTANT NUMBER := 1;
1838 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_PO_FROM_K';
1839 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1840 l_msg_count NUMBER := 0;
1841 l_msg_data VARCHAR2(1000);
1842 l_po_header_rec po_header_rec_type;
1843 l_po_lines_tab po_lines_tab;
1844 l_po_dist_tab po_distributions_tab;
1845
1846
1847 -- Batch id for all records created in the interface table
1848 l_batch_id number;
1849 l_request_id number;
1850
1851 -- Temporarily required...see notes elsewhere
1852 l_po_header_id po_headers_all.po_header_id%TYPE;
1853
1854 BEGIN
1855
1856 G_USER_ID := FND_GLOBAL.USER_ID;
1857 G_LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
1858 G_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
1859 G_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID;
1860 G_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
1861
1862 -- Generate the batch id for the run. All records are inserted into
1863 -- the interface table with this batch_id
1864 -- Max will not return an exception but NULL if no records are there;
1865 -- hence the nvl
1866
1867 SELECT NVL(MAX(batch_id),0) + 1
1868 INTO l_batch_id
1869 FROM po_headers_interface;
1870
1871 my_debug('1760: Batch Id: ' || l_batch_id, 4);
1872
1873 my_debug('1760: Contract Id: ' || p_chr_id, 4);
1874
1875 -- include here call to procedure that will validate the contract
1876
1877 -- call procedure to fetch the contract data
1878
1879 get_k_info(p_chr_id => p_chr_id
1880 ,x_return_status => l_return_status
1881 ,x_po_header_rec => l_po_header_rec
1882 ,x_po_lines_tab => l_po_lines_tab
1883 ,x_po_dist_tab => l_po_dist_tab);
1884
1885 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1886 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1887 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1888 RAISE OKC_API.G_EXCEPTION_ERROR;
1889 END IF;
1890
1891 -- call procedure to populate purchase order header
1892
1893 insert_po_hdr(
1894 p_chr_id => p_chr_id
1895 ,p_batch_id => l_batch_id
1896 ,p_po_header_rec => l_po_header_rec
1897 ,x_return_status => l_return_status
1898 ,x_po_header_id => l_po_header_id);
1899
1900 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1901 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1902 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1903 RAISE OKC_API.G_EXCEPTION_ERROR;
1904 END IF;
1905
1906
1907 OPEN c_top_cle(p_chr_id);
1908
1909 FETCH c_top_cle
1910 INTO l_po_lines_tab(1).source_cle_id,
1911 l_po_lines_tab(1).item_id,
1912 l_po_lines_tab(1).uom_code,
1913 l_po_lines_tab(1).quantity,
1914 l_po_lines_tab(1).unit_price,
1915 l_po_lines_tab(1).list_price_per_unit,
1916 l_po_lines_tab(1).need_by_date;
1917
1918
1919 -- call procedure to populate purchase order lines just if the contract has lines
1920
1921 IF c_top_cle%FOUND THEN
1922
1923
1924 insert_po_lines(p_chr_id => p_chr_id
1925 ,p_po_lines_tab => l_po_lines_tab
1926 ,p_po_header_id => l_po_header_id
1927 ,x_return_status => l_return_status);
1928
1929
1930 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1931 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1932 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1933 RAISE OKC_API.G_EXCEPTION_ERROR;
1934 END IF;
1935
1936
1937 -- call procedure to populate purchase order distributions
1938
1939
1940 insert_po_distributions(p_po_dist_tab => l_po_dist_tab
1941 ,x_return_status => l_return_status);
1942
1943 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1944 my_debug('1780: Unexpected error from insert_po_distributions',4);
1945 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1946 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1947 my_debug('1800: Error from insert_po_distributions',4);
1948 RAISE OKC_API.G_EXCEPTION_ERROR;
1949 END IF;
1950
1951 END IF;
1952
1953 my_debug('1820: Calling submit_request for PDOI',4);
1954
1955 -- Submit the request to import the records from the interface table.
1956 -- The batch_id generated is passed in to ensure that only those
1957 -- records which were inserted during the earlier phase are imported.
1958
1959 l_request_id := fnd_request.submit_request(
1960 application => 'PO'
1961 ,program => 'POXPOPDOI'
1962 ,sub_request => TRUE -- Indicates that this is a child
1963 -- of the parent request
1964 ,argument1 => null -- Default buyer
1965 ,argument2 => 'Standard' -- Document type
1966 ,argument3 => null -- Document sub type
1967 ,argument4 => 'N' -- Create or update items
1968 ,argument5 => 'N' -- Create sourcing rules
1969 ,argument6 => null -- Approval status
1970 ,argument7 => null -- Release generation method
1971 ,argument8 => l_batch_id -- Batch Id
1972 ,argument9 => null -- Operating unit
1973 );
1974
1975 my_debug('1840: Finished the call with request id: ' || l_request_id,4);
1979 IF l_request_id = 0
1976 -- If the request could not be submitted for some reason, then the
1977 -- request_id is set to 0. Exit with an error if this is the case
1978
1980 THEN
1981 my_debug('1860: Error submitting request for PDOI',4);
1982 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1983 END IF;
1984
1985 -- If no errors, set the status of the current request to PAUSED since
1986 -- it is not running at present and so that it frees up resources
1987 -- Also, set request_data to stage2 to indicate that this is the final run
1988
1989 fnd_conc_global.set_req_globals(conc_status => 'PAUSED',
1990 request_data => 'STAGE2');
1991
1992 EXCEPTION
1993 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1994 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1995 (l_api_name,
1996 G_PKG_NAME,
1997 'OKC_API.G_RET_STS_ERROR',
1998 x_msg_count,
1999 x_msg_data,
2000 '_PROCESS');
2001 my_debug('1880: Exiting create_po_from_k', 4);
2002 IF (l_debug = 'Y') THEN
2003 okc_debug.Reset_Indentation;
2004 END IF;
2005
2006 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2007 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2008 (l_api_name,
2009 G_PKG_NAME,
2010 'OKC_API.G_RET_STS_UNEXP_ERROR',
2011 x_msg_count,
2012 x_msg_data,
2013 '_PROCESS');
2014 my_debug('1900: Exiting create_po_from_k', 4);
2015 IF (l_debug = 'Y') THEN
2016 okc_debug.Reset_Indentation;
2017 END IF;
2018
2019 WHEN OTHERS THEN
2020 my_debug('1920: error'||substr(sqlerrm,1,240));
2021 OKC_API.set_message(p_app_name => g_app_name,
2022 p_msg_name => g_unexpected_error,
2023 p_token1 => g_sqlcode_token,
2024 p_token1_value => sqlcode,
2025 p_token2 => g_sqlerrm_token,
2026 p_token2_value => sqlerrm);
2027 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2028 my_debug('1940: Exiting create_po_from_k', 4);
2029 IF (l_debug = 'Y') THEN
2030 okc_debug.Reset_Indentation;
2031 END IF;
2032
2033 END create_po_from_k;
2034
2035 --------------------------------------------------------------------------------
2036 -------------- Procedure: tieback_related_objs_from_po ------------------------
2037 --------------------------------------------------------------------------------
2038 -- Purpose: See specifications (avoid dual maintenance of comments)
2039 --
2040 -- In Parameters: p_chr_id Contract header id
2041 --
2042 -- Out Parameters: Standard
2043 --------------------------------------------------------------------------------
2044
2045 PROCEDURE tieback_related_objs_from_po(
2046 p_api_version IN NUMBER
2047 ,p_init_msg_list IN VARCHAR2
2048 ,p_chr_id IN okc_k_headers_b.id%TYPE
2049 ,x_po_number OUT NOCOPY VARCHAR2
2050 ,x_return_status OUT NOCOPY VARCHAR2
2051 ,x_msg_count OUT NOCOPY NUMBER
2052 ,x_msg_data OUT NOCOPY VARCHAR2
2053 ) IS
2054
2055 l_msg_data VARCHAR2(200);
2056 l_po_number po_headers_all.segment1%TYPE;
2057
2058 -- Keep track of number of rows affected. It is essential to store the
2059 -- SQL*ROWCOUNT value locally after each SQL operation as if we use
2060 -- the variable SQL%ROWCOUNT, there is a chance that it might get
2061 -- overwritten by intermediate function calls. For e.g. if we use
2062 -- sql%rowcount in a debug statement and then examine it, it is
2063 -- possible that the function which prints the debug message has a SQL
2064 -- operation causing the value of SQL%ROWCOUNT to be overwritten
2065
2066 l_sql_rowcount pls_integer;
2067
2068
2069 BEGIN
2070
2071 x_return_status := OKC_API.G_RET_STS_SUCCESS;
2072
2073 IF (l_debug = 'Y') THEN
2074 okc_debug.Set_Indentation('tieback_related_objs_from_po');
2075 END IF;
2076
2077
2078 -- Remove the relationship between the contract header and PO header
2079 -- from related objects if no PO header was created
2080
2081 my_debug('1960: Attempting delete of hanging header related objects', 4);
2082
2083 DELETE okc_k_rel_objs rel
2084 WHERE rel.chr_id = p_chr_id -- for the current contract
2085 AND rel.cle_id IS NULL -- related obj pertains to header
2086 AND rel.rty_code = g_crj_rty_code -- for PO creation
2087 AND rel.jtot_object1_code = g_crj_chr_jtot_object_code -- correct jtot object
2088 -- for contract header
2089 AND NOT EXISTS (
2090 SELECT null
2091 FROM po_headers_all poh
2092 WHERE rel.object1_id1 = poh.po_header_id
2093 );
2094
2095 l_sql_rowcount := SQL%ROWCOUNT;
2096
2097 my_debug('1980: Related objects hanging headers deleted: ' || l_sql_rowcount, 4);
2098
2099 -- Check the number of related objects deleted by the above statement.
2100 -- If any is deleted (that means no related object was created for the
2101 -- header), then this indicates that the whole PDOI has failed and we
2102 -- need to set the concurrent program status accordingly. Also, print
2103 -- the message in the out file so that the user is made aware of this
2104
2105 IF l_sql_rowcount > 0
2106 THEN
2107 my_debug('2000: *** Fatal Error. No PO was created **',1);
2108 fnd_message.set_name('OKC','OKC_PO_NOT_CREATED_BY_PDOI');
2109
2110 -- Write message to out file
2111 fnd_file.put_line(fnd_file.output, fnd_message.get);
2112
2113 -- Set return values
2114 x_msg_count := 1;
2115 x_msg_data := fnd_message.get;
2116
2117 -- Clean up all lines since the header was not interfaced
2118
2119 my_debug('2020: Deleting related object lines', 4);
2120
2121 DELETE FROM okc_k_rel_objs
2122 WHERE chr_id = p_chr_id;
2123
2124 my_debug('2040: Deleted related object lines, count = ' || SQL%ROWCOUNT, 4);
2125
2126
2127 -- Raise exception to skip further processing and mark the program in
2128 -- error
2129 RAISE g_unexp_error;
2130
2131 END IF; -- if sql%rowcount > 0
2132
2133 -- If there is no header record deleted, this means that the PO was
2134 -- successfully created. Log the PO number in the out file so that the
2135 -- user can easily identify the PO
2136
2137 SELECT po.segment1
2138 INTO l_po_number
2139 FROM po_headers_all po
2140 WHERE po.po_header_id =
2141 ( SELECT object1_id1
2142 FROM okc_k_rel_objs rel
2143 WHERE rel.chr_id = p_chr_id -- for the current contract
2144 AND rel.cle_id IS NULL -- related obj pertains to header
2145 AND rel.rty_code = g_crj_rty_code -- for PO creation
2146 AND rel.jtot_object1_code = g_crj_chr_jtot_object_code
2147 -- correct jtot object
2148 );
2149
2150 -- Get the PO number into a translated message and write this into the
2151 -- out file
2152
2153 my_debug('2060: PO number created: ' || l_po_number, 4);
2154
2155 fnd_message.set_name('OKC','OKC_LOG_PO_CREATED_DETAILS');
2156 fnd_message.set_token('PO_NUMBER', l_po_number);
2157 fnd_file.put_line(fnd_file.output, fnd_message.get);
2158 x_po_number := l_po_number;
2159
2160 -- setting the message twice so Events Engine can get it
2161
2162 fnd_message.set_name('OKC','OKC_LOG_PO_CREATED_DETAILS');
2163 fnd_message.set_token('PO_NUMBER', l_po_number);
2164
2165 my_debug('2080: Attempting delete of hanging line related objects', 1);
2166
2167 -- Remove the relationship between the contract lines and
2168 -- corresponding PO lines from related objects if they were not
2169 -- created. This can happen if there is some error during PDOI
2170
2171 DELETE okc_k_rel_objs rel
2172 WHERE rel.chr_id = p_chr_id -- for the current contract
2173 AND rel.cle_id IS NOT NULL -- for line records
2174 AND rel.rty_code = g_crj_rty_code -- for K-PO records
2175 AND rel.jtot_object1_code = g_crj_cle_jtot_object_code -- correct jtot object
2176 AND NOT EXISTS (
2177 SELECT null
2178 FROM po_lines_all pol
2179 WHERE rel.object1_id1 = pol.po_header_id
2180 AND rel.object1_id2 = pol.line_num);
2181
2182 l_sql_rowcount := SQL%ROWCOUNT;
2183
2184 my_debug('2100: Related objects hanging lines deleted: ' || l_sql_rowcount, 4);
2185
2186 -- If some rows were deleted, this means that all lines were not
2187 -- transferred over. The user will have to manually correct these
2188 -- lines. Indicate to the main procedure that this is a Warning so
2189 -- that the return status of the Concurrent program can be set to
2190 -- Warning and the user sees this in the View Requests window
2191
2192 IF l_sql_rowcount > 0
2193 THEN
2194 fnd_message.set_name('OKC','OKC_LOG_PO_SOME_LINES_NOT_XFRD');
2195 l_msg_data := fnd_message.get;
2196 x_return_status := OKC_API.G_RET_STS_WARNING;
2197 x_msg_count := 1;
2198 x_msg_data := l_msg_data;
2199
2200 fnd_file.put_line(fnd_file.output, l_msg_data);
2201 END IF;
2202
2203
2204 -- Now that the hanging related objects have been deleted, update the
2205 -- related objects table to reflect the relationship between contract
2206 -- line id and po line id. Currently, during the first pass, the
2207 -- relationship is established between the contract line id and the
2208 -- po_header_id + line_num. Will change in the future if the bug is
2209 -- fixed in PDOI
2210
2211 my_debug('2110: Updating related objects replacing line num with line id');
2212
2213 UPDATE okc_k_rel_objs rel
2214 SET (rel.object1_id1, rel.object1_id2) =
2215 ( SELECT pol.po_line_id, '#'
2216 FROM po_lines_all pol
2217 WHERE pol.po_header_id = rel.object1_id1
2218 AND pol.line_num = rel.object1_id2 )
2219 WHERE chr_id = p_chr_id
2220 AND rel.rty_code = g_crj_rty_code
2221 AND rel.cle_id IS NOT NULL;
2222
2223 l_sql_rowcount := SQL%ROWCOUNT;
2224 my_debug('2120: Related objects lines updated: ' || l_sql_rowcount, 4);
2225
2226 IF (l_debug = 'Y') THEN
2227 okc_debug.Reset_Indentation;
2228 END IF;
2229
2230 EXCEPTION
2231 WHEN g_unexp_error
2232 THEN
2233 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2234 IF (l_debug = 'Y') THEN
2235 okc_debug.Reset_Indentation;
2236 END IF;
2237
2238 END tieback_related_objs_from_po;
2239
2240 END OKC_CREATE_PO_FROM_K_PVT;
[Home] [Help]