[Home] [Help]
1: PACKAGE BODY PON_SOURCING_OPENAPI_GRP AS
2: /* $Header: PONRNBAB.pls 120.27 2007/07/23 17:08:41 tarkumar ship $ */
3:
4: g_call_purge BOOLEAN := false;
5:
47: BEGIN
48:
49: if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
50: fnd_log.string(fnd_log.level_statement,
51: 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface',
52: 'Entered with input -- '||
53: 'p_interface_id : '|| p_interface_id);
54: end if;
55:
67: );
68:
69: if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
70: fnd_log.string(fnd_log.level_statement,
71: 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface',
72: 'returning with output -- '||
73: 'x_document_number : '|| x_document_number ||
74: 'x_document_url : ' || x_document_url ||
75: 'x_request_id : ' || l_request_id ||
111: BEGIN
112:
113: if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
114: fnd_log.string(fnd_log.level_statement,
115: 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface',
116: 'Entered with input -- '||
117: 'p_interface_id : '|| p_interface_id);
118: end if;
119:
140: end if;
141:
142: if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
143: fnd_log.string(fnd_log.level_statement,
144: 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface',
145: 'returning with output -- '||
146: 'x_document_number : '|| x_document_number ||
147: 'x_document_url : ' || x_document_url ||
148: 'x_request_id : ' || x_request_id ||
262: IF (p_is_concurrent_call = 'Y') THEN
263:
264: if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
265: fnd_log.string(fnd_log.level_statement,
266: 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
267: 'This is a concurrent call');
268: end if;
269:
270: x_document_number := p_document_number;
293: WHERE interface_auction_header_id = p_interface_id;
294:
295: if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
296: fnd_log.string(fnd_log.level_statement,
297: 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
298: x_error_message);
299: end if;
300:
301: RETURN;
317: WHERE interface_auction_header_id = p_interface_id;
318:
319: if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
320: fnd_log.string(fnd_log.level_statement,
321: 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
322: x_error_message);
323: end if;
324:
325: RETURN;
326: END IF;
327:
328: if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
329: fnd_log.string(fnd_log.level_statement,
330: 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
331: 'Counting the number of lines for p_interface_id : ' || p_interface_id);
332: end if;
333:
334: SELECT count(p_interface_id)
337: WHERE interface_auction_header_id = p_interface_id;
338:
339: if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
340: fnd_log.string(fnd_log.level_statement,
341: 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
342: 'l_number_of_lines (number of lines to copy into PON tables, which may not be the same as the value in number_of_lines field in PON_AUCTION_HEADERS_ALL for this auction) : '||
343: l_number_of_lines ||';Cheking if l_number_of_lines > Threshold for p_party_id: '|| v_site_id);
344: end if;
345:
405: IF (p_is_concurrent_call = 'N') THEN
406:
407: if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
408: fnd_log.string(fnd_log.level_statement,
409: 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
410: 'This is not a concurrent call; Handling Header information now; Validating the header');
411: end if;
412:
413: -- Validate data in the PON_AUC_HEADERS_INTERFACE table
426: WHERE interface_auction_header_id = p_interface_id;
427:
428: if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
429: fnd_log.string(fnd_log.level_statement,
430: 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
431: 'Error in validating the header; error mesg : ' ||x_error_message);
432: end if;
433:
434: RETURN;
440:
441:
442: if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
443: fnd_log.string(fnd_log.level_statement,
444: 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
445: 'preparing to insert a row in the transaction table');
446: end if;
447:
448: -- Get the document_number from sequence and store it so that it can
449: -- be returned
450:
451: if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
452: fnd_log.string(fnd_log.level_statement,
453: 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
454: 'Getting the document_number from sequence');
455: end if;
456:
457: SELECT pon_auction_headers_all_s.NEXTVAL INTO x_document_number
458: FROM dual;
459:
460: if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
461: fnd_log.string(fnd_log.level_statement,
462: 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
463: 'New document_number is (x_document_number) : ' ||x_document_number);
464: end if;
465:
466: --Also get the document url and store it
487: when others then
488: v_auc_contact_id := null;
489: if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
490: fnd_log.string(fnd_log.level_statement,
491: 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface',
492: 'Could not determine contact_id for fnd_user_id ' || fnd_global.user_id());
493: end if;
494: end;
495:
496: v_contracts_doctype := pon_conterms_utl_pvt.get_negotiation_doc_type(v_doctype_id);
497:
498: if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
499: fnd_log.string(fnd_log.level_statement,
500: 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
501: 'Calling okc_terms_copy_grp.copy_doc with parameters -- '||
502: 'v_contracts_doctype : ' || v_contracts_doctype ||
503: 'x_document_number : ' || x_document_number ||
504: 'v_auc_contact_id : '|| v_auc_contact_id
511: l_old_policy := mo_global.get_access_mode();
512: l_old_org_id := mo_global.get_current_org_id();
513: if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
514: fnd_log.string(fnd_log.level_statement,
515: 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
516: 'Getting current policy -- '||
517: 'old policy : ' || l_old_policy ||
518: 'old org_id : ' || l_old_org_id
519: );
562:
563:
564: if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
565: fnd_log.string(fnd_log.level_statement,
566: 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
567: 'Executed copy_doc() ; returned with status : ' ||v_return_status);
568: end if;
569:
570: if (v_return_status <> fnd_api.g_ret_sts_success) then
569:
570: if (v_return_status <> fnd_api.g_ret_sts_success) then
571: if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
572: fnd_log.string(fnd_log.level_statement,
573: 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
574: 'Call to copy_doc() failed for source_id=' || g_header_rec.source_doc_id || ' target_id=' || x_document_number);
575: end if;
576:
577: x_result := 'FAILURE';
610: -- Default style allows quantity based price tiers passing Y as p_qty_price_tiers_enabled.
611:
612: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN --{
613: fnd_log.string(fnd_log.level_statement,
614: 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
615: 'Calling the PON_AUCTION_PKG.GET_DEFAULT_TIERS_INDICATOR API to get the' ||
616: ' default price tiers indicator value.');
617: END IF;
618:
625: x_price_tiers_indicator => v_price_tiers_indicator);
626:
627: if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
628: fnd_log.string(fnd_log.level_statement,
629: 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
630: 'Inserting row into PON_AUCTION_HEADERS_ALL with the following filed values -- ' ||
631: 'auction_header_id (x_document_number) : '|| x_document_number ||
632: '; document_number (x_document_number) : ' || x_document_number ||
633: '; amendment_number : ' || 0 ||
791: );
792:
793: if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
794: fnd_log.string(fnd_log.level_statement,
795: 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
796: 'Inserted recore into pon_auction_headers_all');
797: end if;
798:
799: END IF; --end of IF (p_is_concurrent_call = 'N')
810: --{
811: -- price break line setting, this should be called after header is inserted
812: if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
813: fnd_log.string(fnd_log.level_statement,
814: 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
815: 'calling PON_AUCTION_PKG.get_default_pb_settings with x_document_number : ' || x_document_number);
816: end if;
817:
818: PON_AUCTION_PKG.get_default_pb_settings (x_document_number,
820: v_price_break_neg_flag);
821:
822: if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
823: fnd_log.string(fnd_log.level_statement,
824: 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
825: 'v_price_break_type : ' || v_price_break_type || '; v_price_break_neg_flag : ' || v_price_break_neg_flag);
826: end if;
827:
828:
831: v_debug_status := 'VALIDATE_ITEM' ;
832:
833: if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
834: fnd_log.string(fnd_log.level_statement,
835: 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
836: 'validating PON_AUCTION_ITEM_PRICES_INTERFACE; calling val_auc_items_interface with p_interface_id : ' || p_interface_id);
837: end if;
838:
839: val_auc_items_interface(p_interface_id, v_error_code, v_error_message);
851: WHERE interface_auction_header_id = p_interface_id;
852:
853: if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
854: fnd_log.string(fnd_log.level_statement,
855: 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
856: 'error when validating PON_AUCTION_ITEM_PRICES_INTERFACE; x_error_message : ' || x_error_message);
857: end if;
858:
859: RETURN;
862: -- Validate data in the PON_AUCTION_SHIPMENTS_INTERFACE table
863:
864: if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
865: fnd_log.string(fnd_log.level_statement,
866: 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
867: 'validating PON_AUCTION_SHIPMENTS_INTERFACE; calling val_auc_shipments_interface with p_interface_id : ' || p_interface_id);
868: end if;
869:
870: val_auc_shipments_interface(p_interface_id,v_error_code, v_error_message);
881: WHERE interface_auction_header_id = p_interface_id;
882:
883: if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
884: fnd_log.string(fnd_log.level_statement,
885: 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
886: 'error when validating PON_AUCTION_SHIPMENTS_INTERFACE; x_error_message : ' || x_error_message);
887: end if;
888:
889: RETURN;
891: END IF; -- end if (g_header_rec.origination_code <> 'CONTRACT')
892:
893: if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
894: fnd_log.string(fnd_log.level_statement,
895: 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
896: 'validating PON_ATTACHMENTS_INTERFACE; calling val_attachments_interface with p_interface_id : ' || p_interface_id);
897: end if;
898:
899: -- Validate data in the PON_ATTACHMENTS_INTERFACE table
911: WHERE interface_auction_header_id = p_interface_id;
912:
913: if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
914: fnd_log.string(fnd_log.level_statement,
915: 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
916: 'error when validating PON_ATTACHMENTS_INTERFACE; x_error_message : ' || x_error_message);
917: end if;
918:
919: RETURN;
920: END IF;
921:
922: if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
923: fnd_log.string(fnd_log.level_statement,
924: 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
925: 'getting category id for VENDOR attachments');
926: end if;
927:
928: -- get category id for VENDOR attachments
938: X_ERROR_CODE := 'CREATE_DRAFT:ATTACHMENT_CATEGORY_ID';
939: X_ERROR_MESSAGE := 'The attachment category id for name=VENDOR could not be found';
940: if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
941: fnd_log.string(fnd_log.level_statement,
942: 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
943: 'error when getting category id for VENDOR attachments; x_error_message : ' || X_ERROR_MESSAGE);
944: end if;
945:
946: RETURN;
947: END;
948:
949: if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
950: fnd_log.string(fnd_log.level_statement,
951: 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
952: 'getting UOM for amount based line types');
953: end if;
954:
955: -- Get UOM for amount based line types
990: WHERE interface_auction_header_id = p_interface_id;
991:
992: if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
993: fnd_log.string(fnd_log.level_statement,
994: 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
995: 'Max lines to copy (l_max_line_number) : ' || l_max_line_number);
996: end if;
997:
998:
1004: l_batch_size := PON_LARGE_AUCTION_UTIL_PKG.BATCH_SIZE;
1005:
1006: if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
1007: fnd_log.string(fnd_log.level_statement,
1008: 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
1009: 'Starting batching with batchsize (l_batch_size) : ' || l_batch_size);
1010: end if;
1011:
1012: -- for testing purpose
1024: WHILE (l_batch_start <= l_max_line_number) LOOP
1025:
1026: if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
1027: fnd_log.string(fnd_log.level_statement,
1028: 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
1029: 'Batching the line_numbers in the range '|| l_batch_start ||' to '|| l_batch_end ||' (inclusive)');
1030: end if;
1031:
1032:
1037: v_debug_status := 'INSERT_PON_AUC_ITEMS';
1038:
1039: if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
1040: fnd_log.string(fnd_log.level_statement,
1041: 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
1042: 'Handling Item level data; updating the pon_auc_items_interface table with the correct order_type_lookup_code values');
1043: end if;
1044:
1045:
1055: AND interface_line_number <= l_batch_end;
1056:
1057: if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
1058: fnd_log.string(fnd_log.level_statement,
1059: 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
1060: 'inserting into pon_auction_item_prices_all for auction_header_id (x_document_number) : ' || x_document_number);
1061: end if;
1062:
1063: -- Then insert into the items transaction table.
1167: -- Update the shipments flag based on if their are shipments
1168:
1169: if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
1170: fnd_log.string(fnd_log.level_statement,
1171: 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
1172: ' Updating the shipments flag based on if there are shipments');
1173: end if;
1174:
1175: UPDATE pon_auction_item_prices_all
1185: -- ADDED FOR SERVICES PROCUREMENT PROJECT
1186: -- Update the price differntials flag based on if their are price differentials at item level
1187: if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
1188: fnd_log.string(fnd_log.level_statement,
1189: 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
1190: ' Updating the price differntials flag based on if their are price differentials at item level');
1191: end if;
1192:
1193: UPDATE pon_auction_item_prices_all
1216: -- AND interface_line_number IS NOT NULL);
1217:
1218: if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
1219: fnd_log.string(fnd_log.level_statement,
1220: 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
1221: ' CAlling Add_Catalog_Descriptors with x_document_number : ' || x_document_number || '; p_interface_id : ' || p_interface_id);
1222: end if;
1223:
1224: Add_Catalog_Descriptors (x_document_number, p_interface_id,l_batch_start,l_batch_end);
1233: -- clause
1234:
1235: if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
1236: fnd_log.string(fnd_log.level_statement,
1237: 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
1238: ' copying price breaks and price break level price differentials' );
1239: end if;
1240:
1241: INSERT INTO pon_auction_shipments_all
1297: v_debug_status := 'INSERT_PRICE_DIFFERENTIALS_ITEM';
1298:
1299: if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
1300: fnd_log.string(fnd_log.level_statement,
1301: 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
1302: 'Inserting price differentials information into the transaction table at item level' );
1303: end if;
1304:
1305: INSERT INTO pon_price_differentials
1345: -- at shipment level
1346:
1347: if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
1348: fnd_log.string(fnd_log.level_statement,
1349: 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
1350: 'Inserting price differentials information into the transaction table at shipment level' );
1351: end if;
1352:
1353: INSERT INTO pon_price_differentials
1387: v_debug_status := 'INSERT_LINE_ATTACHMENTS';
1388:
1389: if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
1390: fnd_log.string(fnd_log.level_statement,
1391: 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
1392: 'Copying over the (Line) attachments from the PON_ATTACHMENTS_INTERFACE table' );
1393: end if;
1394:
1395: INSERT INTO fnd_attached_documents
1436: --table for all the items that have attachments.
1437:
1438: if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
1439: fnd_log.string(fnd_log.level_statement,
1440: 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
1441: 'Updating the attachment_flag in pon_auction_item_prices_all table for all the items that have attachments.' );
1442: end if;
1443:
1444: UPDATE pon_auction_item_prices_all
1459:
1460: IF (p_is_concurrent_call = 'Y') THEN
1461: if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
1462: fnd_log.string(fnd_log.level_statement,
1463: 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
1464: 'This being a concurrent call, the batch is being committed( from line numbers ' || l_batch_start || ' to ' || l_batch_end);
1465: end if;
1466:
1467: COMMIT;
1477: END IF;
1478:
1479: if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
1480: fnd_log.string(fnd_log.level_statement,
1481: 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
1482: 'Computed the window for next batch to be ' || l_batch_start || ' to ' || l_batch_end || ' (inclusive) ' );
1483: end if;
1484:
1485: END LOOP;
1504: v_debug_status := 'INSERT_HEADER_ATTACHMENTS';
1505:
1506: if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
1507: fnd_log.string(fnd_log.level_statement,
1508: 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
1509: 'Copying over the (header)attachments from the PON_ATTACHMENTS_INTERFACE table' );
1510: end if;
1511:
1512: INSERT INTO fnd_attached_documents
1556: IF (v_set_as_bidders_curr = 'Y' AND v_blanket_bidders_curr = 'Y') THEN
1557:
1558: if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
1559: fnd_log.string(fnd_log.level_statement,
1560: 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
1561: 'Copying over the currency into PON_AUC_CURR_INFO table' );
1562: end if;
1563:
1564: INSERT INTO pon_auction_currency_rates
1593: v_debug_status := 'INSERT_SUPPLIER_INFO';
1594:
1595: if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
1596: fnd_log.string(fnd_log.level_statement,
1597: 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
1598: 'Addding the supplier information on the blanket to the PON_BIDDING_PARTIES; selectiong supplier_site_id' );
1599: end if;
1600:
1601: select supplier_site_id
1621:
1622: --lxchen
1623: if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
1624: fnd_log.string(fnd_log.level_statement,
1625: 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
1626: 'Getting supplier information' );
1627: end if;
1628:
1629: -- Get the supplier information
1646: WHERE interface_auction_header_id = p_interface_id;
1647:
1648: if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
1649: fnd_log.string(fnd_log.level_statement,
1650: 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
1651: 'Error in retrieving supplier information for supplier_id : ' || g_header_rec.supplier_id );
1652: end if;
1653:
1654: RETURN;
1655: END IF;
1656:
1657: if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
1658: fnd_log.string(fnd_log.level_statement,
1659: 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
1660: 'Inserting record into PON_BIDDING_PARTIES' );
1661: end if;
1662:
1663:
1706: --to handle the children
1707: --
1708: if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
1709: fnd_log.string(fnd_log.level_statement,
1710: 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
1711: 'Is this auction super large? (l_is_super_large_neg) : ' || l_is_super_large_neg);
1712: end if;
1713:
1714: IF (l_is_super_large_neg = 'Y') THEN
1713:
1714: IF (l_is_super_large_neg = 'Y') THEN
1715: if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
1716: fnd_log.string(fnd_log.level_statement,
1717: 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
1718: 'Raising a concurrent request here');
1719: end if;
1720:
1721: --RAISE CONCURRENT REQUEST HERE
1731: );
1732:
1733: if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
1734: fnd_log.string(fnd_log.level_statement,
1735: 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
1736: 'Concurrent request successfully raised; Request id : ' || l_request_id ||' ; setting the request information into pon_auction_headers_all');
1737: end if;
1738:
1739: update pon_auction_headers_all set
1755: -----------------------------------------------------------------------------
1756: -- Set the result to success and unset any variables required.
1757: if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
1758: fnd_log.string(fnd_log.level_statement,
1759: 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
1760: 'Setting the result to success');
1761: end if;
1762:
1763: x_result := 'SUCCESS';
1774: IF (l_is_super_large_neg = 'N') OR (l_is_super_large_neg = 'Y' AND p_is_concurrent_call = 'Y') THEN
1775:
1776: if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
1777: fnd_log.string(fnd_log.level_statement,
1778: 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
1779: 'updating the process_status in pon_auc_headers_interface to ACCEPTED');
1780: end if;
1781:
1782: UPDATE pon_auc_headers_interface
1784: WHERE interface_auction_header_id = p_interface_id;
1785:
1786: if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
1787: fnd_log.string(fnd_log.level_statement,
1788: 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
1789: 'updating the complete_flag in pon_auc_headers_all to Y');
1790: end if;
1791:
1792:
1810: --COMMIT;
1811:
1812: if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
1813: fnd_log.string(fnd_log.level_statement,
1814: 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
1815: 'returning with output -- '||
1816: 'x_document_number : '|| x_document_number ||
1817: 'x_document_url : ' || x_document_url ||
1818: 'x_result : ' || x_result ||
1822:
1823: EXCEPTION
1824: WHEN others THEN
1825: fnd_message.set_name('PON','PON_AUC_PLSQL_ERR');
1826: fnd_message.set_token('PACKAGE','PON_SOURCING_OPENAPI_GRP');
1827: fnd_message.set_token('PROCEDURE', 'create_draft_neg_interface');
1828: fnd_message.set_token('ERROR',v_debug_status || '[' || SQLERRM || ']');
1829:
1830: ROLLBACK TO SAVEPOINT pon_before_insert;
1939:
1940: EXCEPTION
1941: WHEN others THEN
1942: fnd_message.set_name('PON','PON_AUC_PLSQL_ERR');
1943: fnd_message.set_token('PACKAGE','PON_SOURCING_OPENAPI_GRP');
1944: fnd_message.set_token('PROCEDURE', 'val_auc_headers_interface');
1945: fnd_message.set_token('ERROR',v_debug_status || '[' || SQLERRM || ']');
1946: app_exception.raise_exception;
1947:
1972: v_debug_status := 'VALIDATING_ITEMS';
1973:
1974: if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
1975: fnd_log.string(fnd_log.level_statement,
1976: 'pon.plsql.pon_sourcing_openapi_grp.val_auc_items_interface',
1977: 'Validating the lines in the range ; validating item org_id');
1978: end if;
1979:
1980: -- validate item org_id is the same as that in the header
1987: IF (v_invalid_item_recs IS NOT NULL ) THEN
1988:
1989: if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
1990: fnd_log.string(fnd_log.level_statement,
1991: 'pon.plsql.pon_sourcing_openapi_grp.val_auc_items_interface',
1992: 'Error in validating item org_id');
1993: end if;
1994:
1995: x_error_code := 'VALIDATE_ITEMS:INCORRECT_ORG_ID';
2001: -- Validate origination_code is BLANKET
2002:
2003: if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
2004: fnd_log.string(fnd_log.level_statement,
2005: 'pon.plsql.pon_sourcing_openapi_grp.val_auc_items_interface',
2006: 'Validating origination_code is BLANKET');
2007: end if;
2008:
2009: v_debug_status := 'VALIDATE_LINE_ORG_CODE';
2016: IF (v_invalid_item_recs IS NOT NULL ) THEN
2017:
2018: if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
2019: fnd_log.string(fnd_log.level_statement,
2020: 'pon.plsql.pon_sourcing_openapi_grp.val_auc_items_interface',
2021: 'Error in validating origination_code is BLANKET');
2022: end if;
2023:
2024: x_error_code := 'VALIDATE_ITEMS:INVALID_LINE_ORIGINATION_CODE';
2030: -- Validate that price_break_type values are CUMULATIVE, NON-CUMMULATIVE or null
2031:
2032: if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
2033: fnd_log.string(fnd_log.level_statement,
2034: 'pon.plsql.pon_sourcing_openapi_grp.val_auc_items_interface',
2035: 'Validating that price_break_type values are CUMULATIVE, NON-CUMMULATIVE or null');
2036: end if;
2037:
2038: v_debug_status := 'VALIDATE_PRICE_BREAK_TYPE';
2045: IF (v_invalid_item_recs IS NOT NULL ) THEN
2046:
2047: if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
2048: fnd_log.string(fnd_log.level_statement,
2049: 'pon.plsql.pon_sourcing_openapi_grp.val_auc_items_interface',
2050: 'Error in validating that price_break_type values are CUMULATIVE, NON-CUMMULATIVE or null');
2051: end if;
2052:
2053: x_error_code := 'VALIDATE_ITEMS:INVALID_PRICE_BREAK_TYPE';
2057: END if;
2058:
2059: if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
2060: fnd_log.string(fnd_log.level_statement,
2061: 'pon.plsql.pon_sourcing_openapi_grp.val_auc_items_interface',
2062: 'Returning with x_error_code : ' || x_error_code || '; x_error_message : ' || x_error_message);
2063: end if;
2064:
2065: EXCEPTION
2064:
2065: EXCEPTION
2066: WHEN others THEN
2067: fnd_message.set_name('PON','PON_AUC_PLSQL_ERR');
2068: fnd_message.set_token('PACKAGE','PON_SOURCING_OPENAPI_GRP');
2069: fnd_message.set_token('PROCEDURE', 'val_auc_items_interface');
2070: fnd_message.set_token('ERROR',v_debug_status || '[' || SQLERRM || ']');
2071: app_exception.raise_exception;
2072:
2095: BEGIN
2096:
2097: if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
2098: fnd_log.string(fnd_log.level_statement,
2099: 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
2100: 'Entered the procedure');
2101: end if;
2102:
2103: -- validate item org_id is the same as that in the header
2111: IF (v_shipment_rec IS NOT null) THEN
2112:
2113: if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
2114: fnd_log.string(fnd_log.level_statement,
2115: 'pon.plsql.pon_sourcing_openapi_grp.val_auc_items_interface',
2116: 'Error in validating item org_id');
2117: end if;
2118:
2119: x_error_code := 'VALIDATE_SHIPMENTS:INCORRECT_ORG_ID';
2125: -- Validate the shipment type columns have correct values
2126:
2127: if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
2128: fnd_log.string(fnd_log.level_statement,
2129: 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
2130: 'Validating the shipment type columns have correct values');
2131: end if;
2132:
2133: v_debug_status := 'VALIDATE_SHIPMENT_TYPE';
2147:
2148: EXCEPTION
2149: WHEN others THEN
2150: fnd_message.set_name('PON','PON_AUC_PLSQL_ERR');
2151: fnd_message.set_token('PACKAGE','PON_SOURCING_OPENAPI_GRP');
2152: fnd_message.set_token('PROCEDURE', 'val_auc_shipments_interface');
2153: fnd_message.set_token('ERROR',v_debug_status || '[' || SQLERRM || ']');
2154: app_exception.raise_exception;
2155:
2181:
2182: EXCEPTION
2183: WHEN others THEN
2184: fnd_message.set_name('PON','PON_AUC_PLSQL_ERR');
2185: fnd_message.set_token('PACKAGE','PON_SOURCING_OPENAPI_GRP');
2186: fnd_message.set_token('PROCEDURE', 'val_attachments_interface');
2187: fnd_message.set_token('ERROR',v_debug_status || '[' || SQLERRM || ']');
2188: app_exception.raise_exception;
2189:
2277:
2278: EXCEPTION
2279: WHEN others THEN
2280: fnd_message.set_name('PON','PON_AUC_PLSQL_ERR');
2281: fnd_message.set_token('PACKAGE','PON_SOURCING_OPENAPI_GRP');
2282: fnd_message.set_token('PROCEDURE', 'get_trading_partner_info');
2283: fnd_message.set_token('ERROR',v_debug_status || '[' || SQLERRM || ']');
2284: app_exception.raise_exception;
2285:
2359:
2360: EXCEPTION
2361: WHEN others THEN
2362: fnd_message.set_name('PON','PON_AUC_PLSQL_ERR');
2363: fnd_message.set_token('PACKAGE','PON_SOURCING_OPENAPI_GRP');
2364: fnd_message.set_token('PROCEDURE', 'purge_interface_table');
2365: fnd_message.set_token('ERROR','[' || SQLERRM || ']');
2366: app_exception.raise_exception;
2367: END purge_interface_table;
2408: ,x_cpa_enabled OUT NOCOPY VARCHAR2) IS
2409:
2410: -- declare local variables
2411: l_api_name CONSTANT VARCHAR2(30) := 'IS_CPA_INTEGRATION_ENABLED';
2412: l_pkg_name CONSTANT VARCHAR2(30) := 'PON_SOURCING_OPENAPI_GRP';
2413: l_api_version CONSTANT VARCHAR2(5) := '1.0';
2414:
2415: BEGIN
2416:
2477: x_return_status OUT NOCOPY VARCHAR2,
2478: x_msg_count OUT NOCOPY NUMBER,
2479: x_msg_data OUT NOCOPY VARCHAR2) IS
2480:
2481: l_pkg_name CONSTANT VARCHAR2(30) := 'PON_SOURCING_OPENAPI_GRP';
2482: l_api_name CONSTANT VARCHAR2(30) := 'get_display_line_number';
2483: l_api_version CONSTANT NUMBER := 1.0;
2484:
2485: begin
2694: BEGIN
2695:
2696: if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
2697: fnd_log.string(fnd_log.level_statement,
2698: 'pon.plsql.pon_sourcing_openapi_grp.initialise_globals',
2699: 'Entered the procedure; initialising g_header_rec');
2700: end if;
2701:
2702: -- Read data into header record for convenience
2705: WHERE interface_auction_header_id = p_interface_id;
2706:
2707: if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
2708: fnd_log.string(fnd_log.level_statement,
2709: 'pon.plsql.pon_sourcing_openapi_grp.initialise_globals',
2710: 'initialised g_header_rec; Entered the procedure');
2711: end if;
2712:
2713: END INITIALISE_GLOBALS;
2738:
2739: BEGIN
2740: if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
2741: fnd_log.string(fnd_log.level_statement,
2742: 'pon.plsql.pon_sourcing_openapi_grp.pon_reneg_super_large_neg',
2743: 'This is a concurrent program; Entered the procedure');
2744: end if;
2745:
2746: --set the message_suffix
2755: l_request_id := FND_GLOBAL.CONC_REQUEST_ID;
2756:
2757: if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
2758: fnd_log.string(fnd_log.level_statement,
2759: 'pon.plsql.pon_sourcing_openapi_grp.pon_reneg_super_large_neg',
2760: 'The request Id of this concurrent process is ' || l_request_id ||'; Now calling create_draft_neg_interface_pvt');
2761: end if;
2762:
2763: create_draft_neg_interface_pvt (
2775: RETCODE := '2' ;
2776:
2777: if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
2778: fnd_log.string(fnd_log.level_statement,
2779: 'pon.plsql.pon_sourcing_openapi_grp.pon_reneg_super_large_neg',
2780: l_error_message);
2781: end if;
2782:
2783: ELSE
2783: ELSE
2784:
2785: if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
2786: fnd_log.string(fnd_log.level_statement,
2787: 'pon.plsql.pon_sourcing_openapi_grp.pon_reneg_super_large_neg',
2788: 'purging the tables; calling PON_SOURCING_OPENAPI_GRP.PURGE_INTERFACE_TABLE()');
2789: end if;
2790:
2791: g_call_purge := true;
2784:
2785: if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
2786: fnd_log.string(fnd_log.level_statement,
2787: 'pon.plsql.pon_sourcing_openapi_grp.pon_reneg_super_large_neg',
2788: 'purging the tables; calling PON_SOURCING_OPENAPI_GRP.PURGE_INTERFACE_TABLE()');
2789: end if;
2790:
2791: g_call_purge := true;
2792: PURGE_INTERFACE_TABLE (
2799: RETCODE := '2' ;
2800:
2801: if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
2802: fnd_log.string(fnd_log.level_statement,
2803: 'pon.plsql.pon_sourcing_openapi_grp.pon_reneg_super_large_neg',
2804: l_error_message);
2805: end if;
2806:
2807: END IF;
2807: END IF;
2808:
2809: if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
2810: fnd_log.string(fnd_log.level_statement,
2811: 'pon.plsql.pon_sourcing_openapi_grp.pon_reneg_super_large_neg',
2812: 'Notifying SUCCESS');
2813: end if;
2814:
2815: PON_WF_UTL_PKG.ReportConcProgramStatus (
2830: --
2831:
2832: /* if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
2833: fnd_log.string(fnd_log.level_statement,
2834: 'pon.plsql.pon_sourcing_openapi_grp.pon_reneg_super_large_neg',
2835: 'Clearing request_id in pon_auction_headers_all');
2836: end if;
2837:
2838: update pon_auction_headers_all
2842: RETCODE := '0';
2843:
2844: if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
2845: fnd_log.string(fnd_log.level_statement,
2846: 'pon.plsql.pon_sourcing_openapi_grp.pon_reneg_super_large_neg',
2847: 'Cleared request_id in pon_auction_headers_all for auction_header_id : ' || p_auction_header_id ||' ; returning');
2848: end if;
2849:
2850: Commit;
2862: p_DestinationPageCode => 'PON_MANAGE_DRAFT_NEG',
2863: p_bid_number => NULL);
2864: if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
2865: fnd_log.string(fnd_log.level_statement,
2866: 'pon.plsql.pon_sourcing_openapi_grp.pon_reneg_super_large_neg',
2867: 'Notifying FAILURE');
2868: end if;
2869:
2870: END IF;
2927: sysdate + 7);
2928:
2929: if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
2930: fnd_log.string(fnd_log.level_statement,
2931: 'pon.plsql.pon_sourcing_openapi_grp.pon_reneg_super_large_neg',
2932: 'Notifying FAILURE');
2933: end if;
2934:
2935: --set the return code
2940:
2941: END;
2942:
2943:
2944: END PON_SOURCING_OPENAPI_GRP;