CREATE OR REPLACE PACKAGE BODY APPS.xxbsi_isg_order_import_api AS /***************************************************************************** Program Name: XXBSI_ISG_ORDER_IMPORT_API Program Type: Data base procedure Program file: XXBSI_ISG_ORDER_IMPORT_API.sql Author: SRISYS Program Description: This package is used to import Orders from external system to Oracle E-Business Suite Change History: ----------------------------------------------------------------------------- Date Name Version Change Description ----------------------------------------------------------------------------- 23-Aug-2018 S.Balakrishnan 1.0 Initial Version ------------------------------------------------------------------------------ *****************************************************************************/ PROCEDURE PROCESS_ORDER (p_header_id IN NUMBER, p_order_status OUT VARCHAR2, p_message OUT VARCHAR2) IS CURSOR cur_order IS SELECT * FROM xxbsi_order_header_stg WHERE processed_flag ='N' AND header_id = p_header_id; -- CURSOR cur_line (lv_header_id NUMBER) IS SELECT * FROM xxbsi_order_line_stg WHERE processed_flag ='N'--IS NOT NULL AND header_id = lv_header_id; l_line_no NUMBER :=0; l_line_id NUMBER; l_sold_to_org_id NUMBER; l_party_id NUMBER; l_account_number NUMBER; l_address VARCHAR2 (960); l_invoice_to_org_id NUMBER; l_ship_to_org_id NUMBER; l_ship_location_id NUMBER; l_transactional_curr_code VARCHAR2 (5) := 'USD'; l_order_type_id NUMBER; l_line_type_id NUMBER; l_payment_term_id NUMBER; l_invoice_payment_term_id NUMBER; lv_payment_term_id NUMBER; l_ship_payment_term_id NUMBER; l_shipping_method_code VARCHAR2 (50) ; l_freight_term_code VARCHAR2 (5); l_price_list_id NUMBER; l_fob_point VARCHAR2 (20); l_inventory_item_id NUMBER; l_primary_uom_code VARCHAR2 (3); l_org_id NUMBER; l_return_status VARCHAR2 (2000); l_msg_count NUMBER; l_msg_data VARCHAR2 (2000); v_sts VARCHAR2 (10); l_order_source_id NUMBER; l_orig_sys_document_ref VARCHAR2 (100); l_header_id NUMBER; -- Order process API parameters lv_debug_level NUMBER := 1; l_debug_file VARCHAR2 (200); l_api_version_number NUMBER := 1; l_header_rec oe_order_pub.header_rec_type; l_line_tbl oe_order_pub.line_tbl_type; l_action_request_tbl oe_order_pub.request_tbl_type; l_header_rec_out oe_order_pub.header_rec_type; l_header_val_rec_out oe_order_pub.header_val_rec_type; l_header_adj_tbl_out oe_order_pub.header_adj_tbl_type; l_header_adj_val_tbl_out oe_order_pub.header_adj_val_tbl_type; l_header_price_att_tbl_out oe_order_pub.header_price_att_tbl_type; l_header_adj_att_tbl_out oe_order_pub.header_adj_att_tbl_type; l_header_adj_assoc_tbl_out oe_order_pub.header_adj_assoc_tbl_type; l_header_scredit_tbl_out oe_order_pub.header_scredit_tbl_type; l_header_scredit_val_tbl_out oe_order_pub.header_scredit_val_tbl_type; l_line_tbl_out oe_order_pub.line_tbl_type; l_line_val_tbl_out oe_order_pub.line_val_tbl_type; l_line_adj_tbl_out oe_order_pub.line_adj_tbl_type; l_line_adj_val_tbl_out oe_order_pub.line_adj_val_tbl_type; l_line_price_att_tbl_out oe_order_pub.line_price_att_tbl_type; l_line_adj_att_tbl_out oe_order_pub.line_adj_att_tbl_type; l_line_adj_assoc_tbl_out oe_order_pub.line_adj_assoc_tbl_type; l_line_scredit_tbl_out oe_order_pub.line_scredit_tbl_type; l_line_scredit_val_tbl_out oe_order_pub.line_scredit_val_tbl_type; l_lot_serial_tbl_out oe_order_pub.lot_serial_tbl_type; l_lot_serial_val_tbl_out oe_order_pub.lot_serial_val_tbl_type; l_action_request_tbl_out oe_order_pub.request_tbl_type; v_message VARCHAR2(10000); v_message_index VARCHAR2(10000); lv_return_status VARCHAR2(2000); lv_error_msg VARCHAR2(10000); l_error_flag VARCHAR2(1) := 'S'; l_error_message VARCHAR2(2000); l_location_id NUMBER; l_party_site_id NUMBER; l_cust_acct_site_id NUMBER; l_organization_id NUMBER; l_ship_via VARCHAR2(150); l_freight_term VARCHAR2(50); l_customername VARCHAR2(150); -- BEGIN l_debug_level := xxbsi_debug.set_debug('XXBSI_ISG_ORDER_IMPORT_API'); -- dbms_output.put_line('BLUESTAR ORDER IMPORT FROM LEGACY SYSTEM PROGRAM V1.1 '|| SYSDATE); xxbsi_debug.start_proc('XXBSI_ISG_ORDER_IMPORT_API',l_debug_level); -- FOR order_rec IN cur_order LOOP Customer_Validation(order_rec.account_number ,l_party_id ,l_customername ,l_sold_to_org_id ,l_org_id ,l_invoice_to_org_id ,l_error_message); -- mo_global.init('ONT'); mo_global.set_policy_context ('S',l_org_id); -- IF l_sold_to_org_id IS NOT NULL THEN -- IF order_rec.record_status = 'A' AND order_rec.ship_cust is not null THEN ------------------------------------------------------------------------------------------------------ -- Ship to Address Validation ------------------------------------------------------------------------------------------------------ -- Get Ship to Loaction Id based on Bill to Location Id SELECT order_rec.ship_cust||','||order_rec.ship_address1||','||order_rec.ship_address2||','||order_rec.ship_address3 INTO l_address FROM DUAL; -- Get Ship to Location Id l_ship_to_org_id:= Address_Validation (order_rec.account_number , l_address , order_rec.ship_city , order_rec.ship_state_province , order_rec.ship_postal_code , order_rec.ship_territory , l_org_id); -- IF l_ship_to_org_id > 0 THEN dbms_output.put_line('Ship To Location ID :' || l_ship_to_org_id); IF l_debug_level >=1 THEN xxbsi_debug.debug_msg('XXBSI_ISG_ORDER_IMPORT_API','- Ship To Location ID :' || l_ship_to_org_id,1); END IF; ELSE ------------------------------------------------------------------------------------------------------ --CREATE NEW BILL TO LOCATION ------------------------------------------------------------------------------------------------------ CREATE_NEW_LOCATION (order_rec.ship_cust ,order_rec.ship_address1 ,order_rec.ship_address2 ,order_rec.ship_address3 ,order_rec.ship_city ,order_rec.ship_state_province ,order_rec.ship_postal_code ,order_rec.ship_territory ,l_location_id ,lv_return_status ,lv_error_msg); -- IF lv_return_status = 'SUCCESS' THEN l_error_flag := 'S'; ELSE l_error_flag := 'E'; END IF; l_error_message := lv_error_msg; -- IF l_location_id IS NOT NULL THEN ------------------------------------------------------------------------------------------------------ --CREATE NEW PARTY SITE ------------------------------------------------------------------------------------------------------ CREATE_PARTY_SITE (l_party_id , l_location_id , l_party_site_id , lv_return_status , lv_error_msg); -- IF lv_return_status = 'SUCCESS' THEN l_error_flag := 'S'; ELSE l_error_flag := 'E'; END IF; l_error_message := lv_error_msg; -- IF l_party_site_id IS NOT NULL THEN ------------------------------------------------------------------------------------------------------ --CREATE NEW CUSTOMER ACCOUNT SITE ------------------------------------------------------------------------------------------------------ CREATE_CUST_ACCT_SITE ( l_sold_to_org_id , l_party_site_id , l_cust_acct_site_id , lv_return_status , lv_error_msg); -- IF lv_return_status = 'SUCCESS' THEN l_error_flag := 'S'; ELSE l_error_flag := 'E'; END IF; l_error_message := lv_error_msg; -- IF l_cust_acct_site_id IS NOT NULL THEN ------------------------------------------------------------------------------------------------------ --CREATE NEW CUSTOMER ACCOUNT SITE USE ------------------------------------------------------------------------------------------------------ CREATE_CUST_SITE_USE ( l_cust_acct_site_id , l_ship_to_org_id , lv_return_status , lv_error_msg); -- IF lv_return_status = 'SUCCESS' THEN l_error_flag := 'S'; ELSE l_error_flag := 'E'; END IF; l_error_message := lv_error_msg; -- END IF; END IF; END IF; END IF; ------------------------------------------------------------------------------------------------------ -- Get ORDER_TYPE_ID using OE_TRANSACTION_TYPES_TL table ------------------------------------------------------------------------------------------------------ l_order_type_id := Get_OrderType(order_rec.account_number); -- IF l_order_type_id = 0 THEN l_error_flag := 'E'; l_error_message := l_error_message||' Order Type Not Assigned to Customer'; END IF; -- dbms_output.put_line('Order type ID for customer :'|| l_order_type_id); IF l_debug_level >=1 THEN xxbsi_debug.debug_msg('XXBSI_ISG_ORDER_IMPORT_API','- Order type ID for customer :'|| l_order_type_id,1); END IF; ------------------------------------------------------------------------------------------------------ -- payment term -- based on customer ------------------------------------------------------------------------------------------------------ IF l_ship_to_org_id > 0 THEN l_payment_term_id := PaymentTerms_Validation(order_rec.account_number,l_ship_to_org_id,l_org_id); IF l_payment_term_id = 0 AND l_ship_to_org_id > 0 THEN l_payment_term_id := PaymentTerms_Validation(order_rec.account_number,l_invoice_to_org_id,l_org_id); ELSIF l_payment_term_id = 0 AND l_invoice_to_org_id > 0 THEN l_payment_term_id := PaymentTerms_Validation(order_rec.account_number,'',l_org_id); END IF; END IF; -- lv_payment_term_id := l_payment_term_id; ------------------------------------------------------------------------------------------------------ -- Shipping Method ------------------------------------------------------------------------------------------------------ IF l_ship_to_org_id > 0 THEN l_ship_via := ShipmentMethod_Validation(order_rec.account_number,l_ship_to_org_id,l_org_id); IF l_ship_via IS NULL AND l_ship_to_org_id > 0 THEN l_ship_via := ShipmentMethod_Validation(order_rec.account_number,l_invoice_to_org_id,l_org_id); ELSIF l_ship_via IS NULL AND l_invoice_to_org_id > 0 THEN l_ship_via := ShipmentMethod_Validation(order_rec.account_number,'',l_org_id); END IF; END IF; -- -------------------------------------------------------------------------------------------- -- Freight Term ------------------------------------------------------------------------------------------------------ IF l_ship_to_org_id > 0 THEN l_freight_term := FreightTerm_Validation(order_rec.account_number,l_ship_to_org_id,l_org_id); IF l_freight_term IS NULL AND l_ship_to_org_id > 0 THEN l_freight_term := FreightTerm_Validation(order_rec.account_number,l_invoice_to_org_id,l_org_id); ELSIF l_freight_term IS NULL AND l_invoice_to_org_id > 0 THEN l_freight_term := FreightTerm_Validation(order_rec.account_number,'',l_org_id); END IF; END IF; -- ------------------------------------------------------------------------------------------------------ -- Get Price List ID -- based on order_type ------------------------------------------------------------------------------------------------------ l_price_list_id:= GET_PRICELIST(l_order_type_id); IF l_price_list_id =0 THEN dbms_output.put_line('Pricelist is not Assigned to Order Type'); IF l_debug_level >=1 THEN xxbsi_debug.debug_msg('XXBSI_ISG_ORDER_IMPORT_API','- Pricelist is not Assigned to Order Type',1); END IF; l_error_flag :='E'; l_error_message := l_error_message||' Pricelist is not Assigned'; END IF; -- ------------------------------------------------------------------------------------------------------ -- Get Warehouse ID -- based on ship to & order_type ------------------------------------------------------------------------------------------------------ l_organization_id := GetDefaultWarehouse (order_rec.account_number,l_invoice_to_org_id,l_order_type_id,l_org_id); -- IF l_organization_id =0 THEN dbms_output.put_line('Warhouse is not Assigned Customer'); IF l_debug_level >=1 THEN xxbsi_debug.debug_msg('XXBSI_ISG_ORDER_IMPORT_API','- Warhouse is not Assigned Customer',1); END IF; l_error_flag :='E'; l_error_message := l_error_message||' Warehouse is not Assigned'; END IF; ------------------------------------------------------------------------------------------------------ -- Get FOB point from lookup table ------------------------------------------------------------------------------------------------------ BEGIN SELECT lookup_code INTO l_fob_point FROM fnd_lookup_values WHERE lookup_type = 'FOB' AND meaning = 'Shipping Point' AND view_application_id = 222; EXCEPTION WHEN OTHERS THEN --fnd_file.put_line(fnd_file.LOG,'Error while retrieving FOB point for customer '); dbms_output.put_line('Error while retrieving FOB point for customer '); IF l_debug_level >=3 THEN xxbsi_debug.debug_msg('XXBSI_ISG_ORDER_IMPORT_API','- Error while retrieving FOB point for customer ',1); END IF; l_error_flag :='E'; l_error_message := l_error_message||' Shipping Point Not Assigned'; END; dbms_output.put_line('FOB point for customer :'|| l_fob_point); -- IF l_debug_level >=1 THEN xxbsi_debug.debug_msg('XXBSI_ISG_ORDER_IMPORT_API','- FOB point for customer :'|| l_fob_point,1); END IF; ------------------------------------------------------------------------------------------------------ -- Get Order Souce ID for Online ------------------------------------------------------------------------------------------------------ BEGIN SELECT order_source_id INTO l_order_source_id FROM oe_order_sources WHERE NAME = 'Online'; EXCEPTION WHEN OTHERS THEN --fnd_file.put_line(fnd_file.LOG,'Error while retrieving Order source for customer '|| order_rec.cust_name); dbms_output.put_line('Error while retrieving Order source for customer '); IF l_debug_level >=3 THEN xxbsi_debug.debug_msg('XXBSI_ISG_ORDER_IMPORT_API','- Error while retrieving Order source for customer ',1); END IF; l_error_flag :='E'; l_error_message := l_error_message||' Invalid Order Source'; END; dbms_output.put_line('Order source ID is ' || l_order_source_id); IF l_debug_level >=1 THEN xxbsi_debug.debug_msg('XXBSI_ISG_ORDER_IMPORT_API','- Order source ID is ' || l_order_source_id,1); END IF; -- start of line cursor for an order l_line_no :=0; IF l_error_flag <> 'E' THEN FOR line_rec IN cur_line (order_rec.header_id) LOOP dbms_output.put_line( 'Processing Line Items '); IF l_debug_level >=1 THEN xxbsi_debug.debug_msg('XXBSI_ISG_ORDER_IMPORT_API','- Processing Line Items ',1); END IF; ------------------------------------------------------------------------------------------------------ -- Get inventory_item_id and primary uom code of an item_number ------------------------------------------------------------------------------------------------------ BEGIN SELECT inventory_item_id, primary_uom_code INTO l_inventory_item_id, l_primary_uom_code FROM mtl_system_items_b WHERE segment1 = line_rec.item AND organization_id = l_organization_id; --> 101 > BlueStar Master (BSI) -- 206-Bluestar(BGL) EXCEPTION WHEN NO_DATA_FOUND THEN dbms_output.put_line('Item Number not found '|| line_rec.item); IF l_debug_level >=3 THEN xxbsi_debug.debug_msg('XXBSI_ISG_ORDER_IMPORT_API','- Item Number not found '|| line_rec.item,1); END IF; l_error_flag :='E'; l_error_message := l_error_message||' Item Not Found'; WHEN OTHERS THEN --fnd_file.put_line(fnd_file.LOG,'Error while retrieving inventory_item_id, Primary uom code of an item '|| line_rec.item); dbms_output.put_line('Error while retrieving inventory_item_id, Primary uom code of an item '|| line_rec.item); IF l_debug_level >=3 THEN xxbsi_debug.debug_msg('XXBSI_ISG_ORDER_IMPORT_API','- Error while retrieving inventory_item_id, Primary uom code of an item '|| line_rec.item,1); END IF; l_error_flag :='E'; l_error_message := l_error_message||' Invalid Item'; END; dbms_output.put_line('Item ID and UOM '|| l_inventory_item_id|| ' And '|| l_primary_uom_code); IF l_debug_level >=1 THEN xxbsi_debug.debug_msg('XXBSI_ISG_ORDER_IMPORT_API','- Item ID and UOM '|| l_inventory_item_id|| ' And '|| l_primary_uom_code,1); END IF; ------------------------------------------------------------------------------------------------------ -- Get LINE_TYPE_ID using OE_TRANSACTION_TYPES_TL table ------------------------------------------------------------------------------------------------------ BEGIN SELECT default_outbound_line_type_id INTO l_line_type_id FROM oe_transaction_types_all WHERE transaction_type_id = l_order_type_id; EXCEPTION WHEN OTHERS THEN --fnd_file.put_line(fnd_file.LOG,'Error while retrieving LINE_TYPE_ID for an item '|| line_rec.item); dbms_output.put_line('Error while retrieving LINE_TYPE_ID for an item '|| line_rec.item); IF l_debug_level >=3 THEN xxbsi_debug.debug_msg('XXBSI_ISG_ORDER_IMPORT_API','- Error while retrieving LINE_TYPE_ID for an item '|| line_rec.item,1); END IF; l_error_flag :='E'; l_error_message := l_error_message||' Invalid Line Type'; END; dbms_output.put_line('Line Type ID ' || l_line_type_id); IF l_debug_level >=1 THEN xxbsi_debug.debug_msg('XXBSI_ISG_ORDER_IMPORT_API','- Line Type ID ' || l_line_type_id,1); END IF; -- IF line_rec.quantity <= 0 THEN l_error_flag :='E'; l_error_message := l_error_message||' Quantity should be greater than 0'; dbms_output.put_line('Quantity should be greater than 0'); IF l_debug_level >=1 THEN xxbsi_debug.debug_msg('XXBSI_ISG_ORDER_IMPORT_API','- Quantity should be greater than 0 ',1); END IF; END IF; l_line_no := l_line_no + 1; l_line_tbl (l_line_no) := oe_order_pub.g_miss_line_rec; l_line_tbl (l_line_no).orig_sys_document_ref := l_customername||'-'||order_rec.reference_number; l_line_tbl (l_line_no).orig_sys_line_ref := l_customername||'-'||line_rec.line_reference_number; l_line_tbl (l_line_no).ordered_quantity := line_rec.QUANTITY; l_line_tbl (l_line_no).order_quantity_uom := l_primary_uom_code; l_line_tbl (l_line_no).ship_from_org_id := l_organization_id; l_line_tbl (l_line_no).last_updated_by := fnd_global.user_id; l_line_tbl (l_line_no).last_update_date := SYSDATE; l_line_tbl (l_line_no).inventory_item_id := l_inventory_item_id; l_line_tbl (l_line_no).calculate_price_flag := 'N'; l_line_tbl (l_line_no).unit_selling_price := line_rec.SELLING_PRICE; l_line_tbl (l_line_no).request_date := SYSDATE; l_line_tbl (l_line_no).operation := oe_globals.g_opr_create; l_line_tbl (l_line_no).shipping_instructions := line_rec.shipping_instructions; l_line_tbl (l_line_no).packing_instructions := line_rec.packing_instructions; -- Update Order line Stg table UPDATE xxbsi_order_line_stg SET processed_flag = l_error_flag , error_message = l_error_message , last_update_date=sysdate , last_updated_by = fnd_global.user_id WHERE header_id = line_rec.header_id AND line_id = line_rec.line_id; COMMIT; END LOOP; END IF; -- End of Line Cursor of the variable -- IF (lv_debug_level > 0) THEN l_debug_file := oe_debug_pub.set_debug_mode ('FILE'); oe_debug_pub.initialize; oe_debug_pub.setdebuglevel (lv_debug_level); oe_msg_pub.initialize; END IF; -- Set header variable to l_header_rec record type object l_header_rec := oe_order_pub.g_miss_header_rec; l_header_rec.operation := oe_globals.g_opr_create; l_header_rec.order_type_id := l_order_type_id; l_header_rec.sold_to_org_id := l_sold_to_org_id; l_header_rec.ship_to_org_id := l_ship_to_org_id; l_header_rec.invoice_to_org_id := l_invoice_to_org_id; l_header_rec.price_list_id := l_price_list_id; l_header_rec.pricing_date := SYSDATE; l_header_rec.transactional_curr_code := order_rec.currency_code; l_header_rec.cust_po_number := order_rec.cust_po_number; l_header_rec.payment_term_id := lv_payment_term_id; l_header_rec.fob_point_code := l_fob_point; l_header_rec.shipping_method_code := l_ship_via; l_header_rec.freight_terms_code := l_freight_term; l_header_rec.org_id := l_org_id; l_header_rec.ship_from_org_id := l_organization_id; l_header_rec.order_source_id := l_order_source_id; l_header_rec.orig_sys_document_ref := l_customername||'-'||order_rec.reference_number; l_header_rec.shipping_instructions := order_rec.shipping_instructions; l_header_rec.packing_instructions := order_rec.packing_instructions; l_header_rec.ordered_date := SYSDATE; l_action_request_tbl (1) := oe_order_pub.g_miss_request_rec; ELSIF order_rec.record_status = 'U' THEN l_line_no :=0; FOR line_rec IN cur_line (order_rec.header_id) LOOP BEGIN SELECT ooh.header_id,ool.line_id INTO l_header_id, l_line_id FROM oe_order_headers_all ooh , oe_order_lines_all ool WHERE ooh.header_id = ool.header_id AND ooh.orig_sys_document_ref= l_customername||'-'||order_rec.reference_number AND ool.orig_sys_line_ref = l_customername||'-'||line_rec.line_reference_number AND ooh.org_id=l_org_id; EXCEPTION WHEN NO_DATA_FOUND THEN dbms_output.put_line('Order Not Existed in Oracle :' || order_rec.reference_number); l_error_flag :='E'; l_error_message := l_error_message||'Order Not Existed in Oracle :' || order_rec.reference_number; IF l_debug_level >=3 THEN xxbsi_debug.debug_msg('XXBSI_ISG_ORDER_IMPORT_API','- Order Not Existed in Oracle :' || order_rec.reference_number,1); END IF; END; -- IF l_header_id IS NOT NULL THEN --dbms_output.put_line('Order Line :'||l_header_id||'-'||l_line_id ); l_line_no := l_line_no + 1; l_line_tbl (l_line_no) := oe_order_pub.g_miss_line_rec; l_line_tbl (l_line_no).last_update_date := SYSDATE; l_line_tbl (l_line_no).last_updated_by := fnd_global.user_id; l_line_tbl (l_line_no).last_update_login := fnd_global.login_id; l_line_tbl (l_line_no).header_id := l_header_id; l_line_tbl (l_line_no).line_id := l_line_id; l_line_tbl (l_line_no).ordered_quantity := line_rec.QUANTITY; l_line_tbl (l_line_no).operation := oe_globals.G_OPR_UPDATE; l_line_tbl (l_line_no).change_reason := 'MANUAL'; l_line_tbl (l_line_no).request_date := SYSDATE; -- Update Order line Stg table UPDATE xxbsi_order_line_stg SET processed_flag = l_error_flag , error_message = l_error_message , last_update_date=sysdate , last_updated_by = fnd_global.user_id WHERE header_id = line_rec.header_id AND line_id = line_rec.line_id; -- END IF; END LOOP; COMMIT; -- Calling Cancel API ElSIF order_rec.record_status = 'D' THEN BEGIN SELECT header_id INTO l_header_id FROM oe_order_headers_all WHERE orig_sys_document_ref= l_customername||'-'||order_rec.reference_number AND org_id=l_org_id; EXCEPTION WHEN NO_DATA_FOUND THEN dbms_output.put_line('Order Not Existed in Oracle :' || order_rec.reference_number); l_error_flag :='E'; l_error_message := l_error_message||'Order Not Existed in Oracle :' || order_rec.reference_number; IF l_debug_level >=3 THEN xxbsi_debug.debug_msg('XXBSI_ISG_ORDER_IMPORT_API','- Order Not Existed in Oracle :' || order_rec.reference_number,1); END IF; END; -- IF l_header_id IS NOT NULL THEN dbms_output.put_line('Order Header ID :' ||l_header_id); l_header_rec := oe_order_pub.g_miss_header_rec; l_header_rec.operation := oe_globals.g_opr_update; l_header_rec.header_id := l_header_id; l_header_rec.cancelled_flag := 'Y'; l_header_rec.change_reason := 'Not provided'; END IF; END IF; --#### End of --> Printing Variable Values - Testing ####-- IF l_error_flag <> 'E' THEN ------------------------------------------------------------------------------------------------------ -- Calling OE_ORDER_PUB.process_order API ------------------------------------------------------------------------------------------------------ dbms_output.put_line('Calling OE_ORDER_PUB.process_order API '); IF l_debug_level >=1 THEN xxbsi_debug.debug_msg('XXBSI_ISG_ORDER_IMPORT_API','- Calling OE_ORDER_PUB.process_order API',1); END IF; oe_order_pub.process_order (p_org_id => l_org_id, -- p_operating_unit => fnd_profile.VALUE('OPERATING_UNIT'), p_api_version_number => l_api_version_number, p_init_msg_list => fnd_api.g_true, p_header_rec => l_header_rec, p_line_tbl => l_line_tbl, p_action_request_tbl => l_action_request_tbl, x_header_rec => l_header_rec_out, x_header_val_rec => l_header_val_rec_out, x_header_adj_tbl => l_header_adj_tbl_out, x_header_adj_val_tbl => l_header_adj_val_tbl_out, x_header_price_att_tbl => l_header_price_att_tbl_out, x_header_adj_att_tbl => l_header_adj_att_tbl_out, x_header_adj_assoc_tbl => l_header_adj_assoc_tbl_out, x_header_scredit_tbl => l_header_scredit_tbl_out, x_header_scredit_val_tbl => l_header_scredit_val_tbl_out, x_line_tbl => l_line_tbl_out, x_line_val_tbl => l_line_val_tbl_out, x_line_adj_tbl => l_line_adj_tbl_out, x_line_adj_val_tbl => l_line_adj_val_tbl_out, x_line_price_att_tbl => l_line_price_att_tbl_out, x_line_adj_att_tbl => l_line_adj_att_tbl_out, x_line_adj_assoc_tbl => l_line_adj_assoc_tbl_out, x_line_scredit_tbl => l_line_scredit_tbl_out, x_line_scredit_val_tbl => l_line_scredit_val_tbl_out, x_lot_serial_tbl => l_lot_serial_tbl_out, x_lot_serial_val_tbl => l_lot_serial_val_tbl_out, x_action_request_tbl => l_action_request_tbl_out, x_return_status => l_return_status, x_msg_count => l_msg_count, x_msg_data => l_msg_data ); BEGIN IF l_return_status = 'E' THEN v_sts := 'ERROR'; l_error_flag :=l_return_status; ELSIF l_return_status = 'S' THEN v_sts := 'SUCCESS'; l_error_flag :=l_return_status; -- Update Order Header Stg table ELSIF l_return_status = 'W' THEN v_sts := 'WARNING'; ELSE v_sts := l_return_status; END IF; dbms_output.put_line('Creation of Order status is.... ' || v_sts); IF l_debug_level >=1 THEN xxbsi_debug.debug_msg('XXBSI_ISG_ORDER_IMPORT_API','- Creation of Order status is.... ' || v_sts,1); END IF; END; IF (l_return_status = fnd_api.g_ret_sts_success) THEN dbms_output.put_line('Creation of Order is successful.... ' || TO_CHAR (l_header_rec_out.order_number)); IF l_debug_level >=1 THEN xxbsi_debug.debug_msg('XXBSI_ISG_ORDER_IMPORT_API','- Creation of Order is successful.... ' || TO_CHAR (l_header_rec_out.order_number),1); END IF; COMMIT; ---v_message :=v_sts; l_return_status := 'S'; p_order_status := 'S'; p_message := 'Order has been successfully created :'||TO_CHAR (l_header_rec_out.order_number); ELSE l_return_status := 'E'; l_error_flag := l_return_status; BEGIN IF l_msg_count > 0 THEN FOR x_index in 1..l_msg_count LOOP oe_msg_pub.get( p_msg_index => x_index ,p_encoded => Fnd_Api.G_FALSE ,p_data => v_message ,p_msg_index_out => v_message_index); dbms_output.put_line('API error while creating an order '||v_message); IF l_debug_level >=3 THEN xxbsi_debug.debug_msg('XXBSI_ISG_ORDER_IMPORT_API','- API error while creating an order '||v_message,1); END IF; l_error_message :=l_error_message||';'||v_message; END LOOP; dbms_output.put_line('API error while creating an order '||SQLERRM); IF l_debug_level >=3 THEN xxbsi_debug.debug_msg('XXBSI_ISG_ORDER_IMPORT_API','- API error while creating an order '||SQLERRM,1); END IF; END IF; END; -- END IF; END IF; ELSE l_error_flag := 'E'; -- UPDATE xxbsi_order_header_stg SET processed_flag = l_error_flag , error_message = l_error_message , last_update_date=sysdate , last_updated_by = fnd_global.user_id WHERE header_id = order_rec.header_id; END IF; -- IF l_error_flag ='E' THEN p_order_status := l_error_flag; p_message := l_error_message; END IF; -- UPDATE xxbsi_order_header_stg SET processed_flag = l_error_flag , error_message = l_error_message , last_update_date=sysdate , last_updated_by = fnd_global.user_id WHERE header_id = order_rec.header_id; l_error_flag :=''; END LOOP; -- xxbsi_debug.end_proc('XXBSI_ISG_ORDER_IMPORT_API',l_debug_level); EXCEPTION WHEN OTHERS THEN dbms_output.put_line('Un-handled exception while creating an order '|| SQLERRM); IF l_debug_level >=3 THEN xxbsi_debug.debug_msg('XXBSI_ISG_ORDER_IMPORT_API','- Un-handled exception while creating an order '|| SQLERRM,1); END IF; END process_order; -- PROCEDURE Customer_Validation (p_customer IN VARCHAR2, l_party_id OUT NUMBER, l_customer_name OUT VARCHAR2, l_sold_to_org_id OUT NUMBER, l_org_id OUT NUMBER, l_invoice_to_org_id OUT NUMBER, l_error_message OUT VARCHAR2) IS -- BEGIN l_debug_level := xxbsi_debug.set_debug('XXBSI_ISG_ORDER_IMPORT_API'); BEGIN SELECT hca.party_id,hp.party_name,hca.cust_account_id,hzcsu.org_id,hzcsu.site_use_id INTO l_party_id,l_customer_name,l_sold_to_org_id,l_org_id,l_invoice_to_org_id FROM hz_party_sites hzp, hz_locations hl, hz_parties hp, hz_cust_accounts_all hca, hz_cust_acct_sites_all hzcau, hz_cust_site_uses_all hzcsu WHERE hzp.party_site_id = hzcau.party_site_id AND hzp.location_id = hl.location_id AND hzcau.cust_acct_site_id = hzcsu.cust_acct_site_id AND hca.party_id =hp.party_id AND hzcau.cust_account_id = hca.cust_account_id AND hzcsu.primary_flag='Y' AND hca.account_number = p_customer AND hzcsu.site_use_code = 'BILL_TO' AND hzcsu.org_id <> 101 AND hzcsu.status ='A'; EXCEPTION WHEN NO_DATA_FOUND THEN l_party_id := ''; l_sold_to_org_id := ''; l_org_id := ''; l_invoice_to_org_id :=''; l_error_message := 'Invalid Customer'; dbms_output.put_line('Invalid Customer'); IF l_debug_level >=3 THEN xxbsi_debug.debug_msg('XXBSI_ISG_ORDER_IMPORT_API','- Invalid Customer',1); END IF; END; -- IF l_debug_level >=1 THEN xxbsi_debug.debug_msg('XXBSI_ISG_ORDER_IMPORT_API','- Party ID :'||l_party_id,1); xxbsi_debug.debug_msg('XXBSI_ISG_ORDER_IMPORT_API','- Party Name :'||l_customer_name,1); xxbsi_debug.debug_msg('XXBSI_ISG_ORDER_IMPORT_API','- Sold to Org ID :'||l_sold_to_org_id,1); xxbsi_debug.debug_msg('XXBSI_ISG_ORDER_IMPORT_API','- Org ID :'||l_org_id,1); xxbsi_debug.debug_msg('XXBSI_ISG_ORDER_IMPORT_API','- Invocie to Org ID :'||l_invoice_to_org_id,1); END IF; END Customer_Validation; -- FUNCTION Address_Validation (p_customer_number IN NUMBER, p_address IN VARCHAR2, p_city IN VARCHAR2, p_state IN VARCHAR2, p_postal_code IN VARCHAR2, p_territory IN VARCHAR2, p_org_id IN NUMBER) RETURN NUMBER IS l_site_use_id NUMBER; BEGIN l_debug_level := xxbsi_debug.set_debug('XXBSI_ISG_ORDER_IMPORT_API'); BEGIN SELECT hzcsu.site_use_id INTO l_site_use_id FROM hz_party_sites hzp, hz_locations hl, hz_cust_accounts_all hca, hz_cust_acct_sites_all hzcau, hz_cust_site_uses_all hzcsu WHERE hzp.party_site_id = hzcau.party_site_id AND hzp.location_id = hl.location_id AND hzcau.cust_acct_site_id = hzcsu.cust_acct_site_id AND hzcau.cust_account_id = hca.cust_account_id AND hca.account_number = p_customer_number AND hzcsu.site_use_code = 'SHIP_TO' AND hl.address1||','||hl.address2||','||hl.address3||','||hl.address4 = p_address AND hl.city = p_city AND hl.state = p_state AND hl.postal_code = p_postal_code AND hl.country = p_territory AND hzcsu.org_id = p_org_id; EXCEPTION WHEN OTHERS THEN dbms_output.put_line('Invalid Ship To'); -- IF l_debug_level >=3 THEN xxbsi_debug.debug_msg('XXBSI_ISG_ORDER_IMPORT_API','- Invalid Ship To',1); END IF; l_site_use_id:= 0; END; -- IF l_debug_level >=1 THEN xxbsi_debug.debug_msg('XXBSI_ISG_ORDER_IMPORT_API','- Ship to Org ID :'||l_site_use_id,1); END IF; RETURN l_site_use_id; END Address_Validation; -- FUNCTION PaymentTerms_Validation (p_customer_number IN NUMBER, p_site_use_id IN NUMBER, p_org_id IN NUMBER) RETURN NUMBER IS l_cust_term_id NUMBER; l_bill_term_id NUMBER; l_ship_term_id NUMBER; BEGIN l_debug_level := xxbsi_debug.set_debug('XXBSI_ISG_ORDER_IMPORT_API'); BEGIN SELECT hzcsu.payment_term_id INTO l_ship_term_id FROM hz_party_sites hzp, hz_locations hl, hz_cust_accounts_all hca, hz_cust_acct_sites_all hzcau, hz_cust_site_uses_all hzcsu WHERE hzp.party_site_id = hzcau.party_site_id AND hzp.location_id = hl.location_id AND hzcau.cust_acct_site_id = hzcsu.cust_acct_site_id AND hzcau.cust_account_id = hca.cust_account_id AND hca.account_number = p_customer_number AND hzcsu.site_use_code ='SHIP_TO' AND hzcsu.site_use_id = p_site_use_id AND hzcsu.org_id = p_org_id; -- --fnd_file.put_line(fnd_file.LOG,'Payment ship Term Id : '||l_ship_term_id); dbms_output.put_line('Payment Ship Term Id : '||l_ship_term_id); -- IF l_debug_level >=1 THEN xxbsi_debug.debug_msg('XXBSI_ISG_ORDER_IMPORT_API','- Payment Ship Term Id : '||l_ship_term_id,1); END IF; -- RETURN l_ship_term_id; EXCEPTION WHEN NO_DATA_FOUND THEN l_ship_term_id := 0; IF l_debug_level >=3 THEN xxbsi_debug.debug_msg('XXBSI_ISG_ORDER_IMPORT_API','- Payment Ship Term Id : '||l_ship_term_id,1); END IF; END; -- IF l_ship_term_id = 0 THEN BEGIN SELECT hzcsu.payment_term_id INTO l_bill_term_id FROM hz_party_sites hzp, hz_locations hl, hz_cust_accounts_all hca, hz_cust_acct_sites_all hzcau, hz_cust_site_uses_all hzcsu WHERE hzp.party_site_id = hzcau.party_site_id AND hzp.location_id = hl.location_id AND hzcau.cust_acct_site_id = hzcsu.cust_acct_site_id AND hzcau.cust_account_id = hca.cust_account_id AND hca.account_number = p_customer_number AND hzcsu.site_use_code ='BILL_TO' AND hzcsu.site_use_id = p_site_use_id AND hzcsu.org_id = p_org_id; -- --fnd_file.put_line(fnd_file.LOG,'Payment Bill Term Id : '||l_bill_term_id); dbms_output.put_line('Payment ship Term Id : '||l_bill_term_id); -- IF l_debug_level >=1 THEN xxbsi_debug.debug_msg('XXBSI_ISG_ORDER_IMPORT_API','- Payment Bill Term Id : '||l_bill_term_id,1); END IF; RETURN l_bill_term_id; EXCEPTION WHEN NO_DATA_FOUND THEN l_bill_term_id := 0; -- IF l_debug_level >=3 THEN xxbsi_debug.debug_msg('XXBSI_ISG_ORDER_IMPORT_API','- Payment Bill Term Id : '||l_bill_term_id,1); END IF; END; -- IF l_bill_term_id = 0 THEN BEGIN SELECT payment_term_id INTO l_cust_term_id FROM hz_cust_accounts_all WHERE account_number = p_customer_number; -- --fnd_file.put_line(fnd_file.LOG,'Payment Cust Term Id : '||l_cust_term_id); dbms_output.put_line('Payment Cust Term Id : '||l_cust_term_id); -- IF l_debug_level >=1 THEN xxbsi_debug.debug_msg('XXBSI_ISG_ORDER_IMPORT_API','- Payment Cust Term Id : '||l_cust_term_id,1); END IF; RETURN l_cust_term_id; EXCEPTION WHEN NO_DATA_FOUND THEN IF l_debug_level >=3 THEN xxbsi_debug.debug_msg('XXBSI_ISG_ORDER_IMPORT_API','- Payment Term Id : '||l_cust_term_id,1); END IF; RETURN 0; END; END IF; END IF; END PaymentTerms_Validation; -- FUNCTION ShipmentMethod_Validation (p_customer_number IN NUMBER, p_site_use_id IN NUMBER, p_org_id IN NUMBER) RETURN VARCHAR IS l_cust_ship VARCHAR2(150); l_bill_ship VARCHAR2(150); l_ship VARCHAR2(150); BEGIN l_debug_level := xxbsi_debug.set_debug('XXBSI_ISG_ORDER_IMPORT_API'); BEGIN SELECT hzcsu.ship_via INTO l_ship FROM hz_party_sites hzp, hz_locations hl, hz_cust_accounts_all hca, hz_cust_acct_sites_all hzcau, hz_cust_site_uses_all hzcsu WHERE hzp.party_site_id = hzcau.party_site_id AND hzp.location_id = hl.location_id AND hzcau.cust_acct_site_id = hzcsu.cust_acct_site_id AND hzcau.cust_account_id = hca.cust_account_id AND hca.account_number = p_customer_number AND hzcsu.site_use_code ='SHIP_TO' AND hzcsu.site_use_id = p_site_use_id AND hzcsu.org_id = p_org_id; -- dbms_output.put_line('Shipping Method : '||l_ship); -- IF l_debug_level >=1 THEN xxbsi_debug.debug_msg('XXBSI_ISG_ORDER_IMPORT_API','- Shipping Method Ship to Level: '||l_ship,1); END IF; RETURN l_ship; EXCEPTION WHEN NO_DATA_FOUND THEN l_ship :=''; -- IF l_debug_level >=3 THEN xxbsi_debug.debug_msg('XXBSI_ISG_ORDER_IMPORT_API','- Shipping Method Ship to Level: '||l_ship,1); END IF; END; -- IF l_ship IS NULL THEN BEGIN SELECT hzcsu.ship_via INTO l_bill_ship FROM hz_party_sites hzp, hz_locations hl, hz_cust_accounts_all hca, hz_cust_acct_sites_all hzcau, hz_cust_site_uses_all hzcsu WHERE hzp.party_site_id = hzcau.party_site_id AND hzp.location_id = hl.location_id AND hzcau.cust_acct_site_id = hzcsu.cust_acct_site_id AND hzcau.cust_account_id = hca.cust_account_id AND hca.account_number = p_customer_number AND hzcsu.site_use_code ='BILL_TO' AND hzcsu.site_use_id = p_site_use_id AND hzcsu.org_id = p_org_id; -- dbms_output.put_line('Shipping Method : '||l_bill_ship); -- IF l_debug_level >=1 THEN xxbsi_debug.debug_msg('XXBSI_ISG_ORDER_IMPORT_API','- Shipping Method Bill to Level : '||l_bill_ship,1); END IF; RETURN l_bill_ship; EXCEPTION WHEN NO_DATA_FOUND THEN l_bill_ship := ''; IF l_debug_level >=3 THEN xxbsi_debug.debug_msg('XXBSI_ISG_ORDER_IMPORT_API','- Shipping Method Bill to Level : '||l_bill_ship,1); END IF; END; -- IF l_bill_ship IS NULL THEN BEGIN SELECT ship_via INTO l_cust_ship FROM hz_cust_accounts_all WHERE account_number = p_customer_number; -- dbms_output.put_line('Shipping Method : '||l_cust_ship); IF l_debug_level >=1 THEN xxbsi_debug.debug_msg('XXBSI_ISG_ORDER_IMPORT_API','- Shipping Method Customer Level : '||l_cust_ship,1); END IF; RETURN l_cust_ship; EXCEPTION WHEN NO_DATA_FOUND THEN IF l_debug_level >=3 THEN xxbsi_debug.debug_msg('XXBSI_ISG_ORDER_IMPORT_API','- Shipping Method is Not Assigned ',1); END IF; RETURN NULL; END; END IF; END IF; END ShipmentMethod_Validation; -- FUNCTION FreightTerm_Validation (p_customer_number IN NUMBER, p_site_use_id IN NUMBER, p_org_id IN NUMBER) RETURN VARCHAR IS l_cust_freight VARCHAR2(150); l_bill_freight VARCHAR2(150); l_ship_freight VARCHAR2(150); BEGIN l_debug_level := xxbsi_debug.set_debug('XXBSI_ISG_ORDER_IMPORT_API'); BEGIN SELECT hzcsu.freight_term INTO l_ship_freight FROM hz_party_sites hzp, hz_locations hl, hz_cust_accounts_all hca, hz_cust_acct_sites_all hzcau, hz_cust_site_uses_all hzcsu WHERE hzp.party_site_id = hzcau.party_site_id AND hzp.location_id = hl.location_id AND hzcau.cust_acct_site_id = hzcsu.cust_acct_site_id AND hzcau.cust_account_id = hca.cust_account_id AND hca.account_number = p_customer_number AND hzcsu.site_use_code ='SHIP_TO' AND hzcsu.site_use_id = p_site_use_id AND hzcsu.org_id = p_org_id; -- dbms_output.put_line('Freight Term : '||l_ship_freight); IF l_debug_level >=1 THEN xxbsi_debug.debug_msg('XXBSI_ISG_ORDER_IMPORT_API','- Freight Term at Ship to Level : '||l_ship_freight,1); END IF; RETURN l_ship_freight; EXCEPTION WHEN NO_DATA_FOUND THEN l_ship_freight := ''; IF l_debug_level >=1 THEN xxbsi_debug.debug_msg('XXBSI_ISG_ORDER_IMPORT_API','- Freight Term at Ship to Level: '||l_ship_freight,1); END IF; END; -- IF l_ship_freight IS NULL THEN BEGIN SELECT hzcsu.freight_term INTO l_bill_freight FROM hz_party_sites hzp, hz_locations hl, hz_cust_accounts_all hca, hz_cust_acct_sites_all hzcau, hz_cust_site_uses_all hzcsu WHERE hzp.party_site_id = hzcau.party_site_id AND hzp.location_id = hl.location_id AND hzcau.cust_acct_site_id = hzcsu.cust_acct_site_id AND hzcau.cust_account_id = hca.cust_account_id AND hca.account_number = p_customer_number AND hzcsu.site_use_code ='BILL_TO' AND hzcsu.site_use_id = p_site_use_id AND hzcsu.org_id = p_org_id; -- dbms_output.put_line('Freight Term : '||l_bill_freight); IF l_debug_level >=1 THEN xxbsi_debug.debug_msg('XXBSI_ISG_ORDER_IMPORT_API','- Freight Term at Bill to Level: '||l_bill_freight,1); END IF; RETURN l_bill_freight; EXCEPTION WHEN NO_DATA_FOUND THEN l_bill_freight := ''; IF l_debug_level >=3 THEN xxbsi_debug.debug_msg('XXBSI_ISG_ORDER_IMPORT_API','- Freight Term at Bill to Level: '||l_bill_freight,1); END IF; END; -- IF l_bill_freight IS NOT NULL THEN BEGIN SELECT freight_term INTO l_cust_freight FROM hz_cust_accounts_all WHERE account_number = p_customer_number; -- dbms_output.put_line('Freight Term : '||l_cust_freight); IF l_debug_level >=1 THEN xxbsi_debug.debug_msg('XXBSI_ISG_ORDER_IMPORT_API','- Freight Term at Customer Level: '||l_cust_freight,1); END IF; RETURN l_cust_freight; EXCEPTION WHEN NO_DATA_FOUND THEN IF l_debug_level >=3 THEN xxbsi_debug.debug_msg('XXBSI_ISG_ORDER_IMPORT_API','- Freight Term is not Assigned ',1); END IF; RETURN NULL; END; END IF; END IF; END FreightTerm_Validation; FUNCTION GET_PRICELIST (p_order_type_id IN NUMBER) RETURN NUMBER IS l_list_header_id NUMBER; l_pricelist_name VARCHAR2(150); BEGIN l_debug_level := xxbsi_debug.set_debug('XXBSI_ISG_ORDER_IMPORT_API'); BEGIN SELECT qlt.list_header_id, qlt.name INTO l_list_header_id,l_pricelist_name FROM oe_transaction_types_all ott , qp_list_headers qlt WHERE ott.price_list_id = qlt.list_header_id AND ott.transaction_type_id = p_order_type_id; dbms_output.put_line('Price List :'|| l_pricelist_name); IF l_debug_level >=1 THEN xxbsi_debug.debug_msg('XXBSI_ISG_ORDER_IMPORT_API','- Price List :'|| l_pricelist_name,1); END IF; RETURN l_list_header_id; EXCEPTION WHEN OTHERS THEN dbms_output.put_line('Price List not assigned to Order Type '); IF l_debug_level >=3 THEN xxbsi_debug.debug_msg('XXBSI_ISG_ORDER_IMPORT_API','- Price List not assigned to Order Type ',1); END IF; RETURN 0; END; END GET_PRICELIST; -- FUNCTION GetDefaultWarehouse (p_customer_number IN NUMBER, p_site_use_id IN NUMBER, p_order_type_id IN VARCHAR2, p_org_id IN NUMBER) RETURN NUMBER IS l_warehouse_id NUMBER; BEGIN l_debug_level := xxbsi_debug.set_debug('XXBSI_ISG_ORDER_IMPORT_API'); BEGIN SELECT trim(hzcsu.warehouse_id) INTO l_warehouse_id FROM hz_party_sites hzp, hz_locations hl, hz_cust_accounts_all hca, hz_cust_acct_sites_all hzcau, hz_cust_site_uses_all hzcsu WHERE hzp.party_site_id = hzcau.party_site_id AND hzp.location_id = hl.location_id AND hzcau.cust_acct_site_id = hzcsu.cust_acct_site_id AND hzcau.cust_account_id = hca.cust_account_id AND hca.account_number = p_customer_number AND hzcsu.site_use_code ='BILL_TO' AND hzcsu.site_use_id = p_site_use_id AND hzcsu.org_id = p_org_id;--fnd_profile.value('ORG_ID'); dbms_output.put_line('Warehouse Id : '||l_warehouse_id); IF l_debug_level >=1 THEN xxbsi_debug.debug_msg('XXBSI_ISG_ORDER_IMPORT_API','- Warehouse Id at Bill to Level: '||l_warehouse_id,1); END IF; EXCEPTION WHEN NO_DATA_FOUND THEN l_warehouse_id := ''; IF l_debug_level >=3 THEN xxbsi_debug.debug_msg('XXBSI_ISG_ORDER_IMPORT_API','- Warehouse Id at Bill to Level : '||l_warehouse_id,1); END IF; END; -- IF l_warehouse_id is null THEN BEGIN SELECT ott.warehouse_id INTO l_warehouse_id FROM oe_transaction_types_all ott , oe_transaction_types_tl ott1 WHERE ott.transaction_type_id =ott1.transaction_type_id AND ott.transaction_type_id = p_order_type_id AND ott1.language ='US'; dbms_output.put_line('Warehouse Id : '||l_warehouse_id); IF l_debug_level >=1 THEN xxbsi_debug.debug_msg('XXBSI_ISG_ORDER_IMPORT_API','- Warehouse Id at Order Type: '||l_warehouse_id,1); END IF; RETURN l_warehouse_id; EXCEPTION WHEN OTHERS THEN dbms_output.put_line('Warehouse Not Assigned'); IF l_debug_level >=3 THEN xxbsi_debug.debug_msg('XXBSI_ISG_ORDER_IMPORT_API','- Warehouse Not Assigned ',1); END IF; RETURN 0; END; ELSE RETURN l_warehouse_id; END IF; END GetDefaultWarehouse; -- FUNCTION Get_OrderType (p_customer IN NUMBER) RETURN NUMBER IS l_order_type_id NUMBER; BEGIN l_debug_level := xxbsi_debug.set_debug('XXBSI_ISG_ORDER_IMPORT_API'); BEGIN SELECT hzcsu.order_type_id INTO l_order_type_id FROM hz_party_sites hzp, hz_locations hl, hz_parties hp, hz_cust_accounts_all hca, hz_cust_acct_sites_all hzcau, hz_cust_site_uses_all hzcsu WHERE hzp.party_site_id = hzcau.party_site_id AND hzp.location_id = hl.location_id AND hzcau.cust_acct_site_id = hzcsu.cust_acct_site_id AND hca.party_id =hp.party_id AND hzcau.cust_account_id = hca.cust_account_id AND hzcsu.primary_flag='Y' AND hca.account_number = p_customer AND hzcsu.site_use_code = 'BILL_TO' AND hzcsu.org_id <> 101 AND hzcsu.status ='A'; -- IF l_debug_level >=1 THEN xxbsi_debug.debug_msg('XXBSI_ISG_ORDER_IMPORT_API','- Order Type : '||l_order_type_id,1); END IF; EXCEPTION WHEN OTHERS THEN dbms_output.put_line('Order Type is not assgined to Customer'); IF l_debug_level >=3 THEN xxbsi_debug.debug_msg('XXBSI_ISG_ORDER_IMPORT_API','- Order Type is not assgined to Customer ',1); END IF; l_order_type_id :=0; END; RETURN l_order_type_id; END Get_OrderType; -- PROCEDURE CREATE_NEW_LOCATION ( address1 VARCHAR2, address2 VARCHAR2, address3 VARCHAR2, address4 VARCHAR2, city VARCHAR2, state_province VARCHAR2, postal_code VARCHAR2, country VARCHAR2, p_location_id OUT NUMBER, p_return_status OUT VARCHAR2, p_error_msg OUT VARCHAR2) IS p_location_rec hz_location_v2pub.location_rec_type; l_return_status VARCHAR2 (200); l_msg_count NUMBER; l_msg_data VARCHAR2 (200); l_location_id NUMBER; l_territory_code VARCHAR2 (20); BEGIN l_debug_level := xxbsi_debug.set_debug('XXBSI_ISG_ORDER_IMPORT_API'); IF l_debug_level >=1 THEN xxbsi_debug.debug_msg('XXBSI_ISG_ORDER_IMPORT_API','- Location Creation ',1); END IF; -- p_location_rec.address1 := address1; p_location_rec.address2 := address2; p_location_rec.address3 := address3; p_location_rec.address4 := address4; p_location_rec.city := city; p_location_rec.state := state_province; p_location_rec.country := country; -- p_location_rec.county := l_county; p_location_rec.postal_code := postal_code; p_location_rec.created_by_module := 'TCA_FORM_WRAPPER'; -- call API to create a location hz_location_v2pub.create_location (p_init_msg_list => FND_API.G_TRUE, p_location_rec => p_location_rec, x_location_id => l_location_id, x_return_status => l_return_status, x_msg_count => l_msg_count, x_msg_data => l_msg_data); DECLARE v_sts VARCHAR2 (10); BEGIN IF l_return_status = 'E' THEN v_sts := 'ERROR'; ELSIF l_return_status = 'S' THEN v_sts := 'SUCCESS'; p_location_id := l_location_id; ELSIF l_return_status = 'W' THEN v_sts := 'WARNING'; ELSE v_sts := l_return_status; END IF; p_return_status := v_sts; END; IF (l_return_status = fnd_api.g_ret_sts_success) THEN dbms_output.put_line('Creation of Customer Location is Successful'); dbms_output.put_line('Customer Location ID is ' || p_location_id); IF l_debug_level >=1 THEN xxbsi_debug.debug_msg('XXBSI_ISG_ORDER_IMPORT_API','- Creation of Customer Location is Successful',1); xxbsi_debug.debug_msg('XXBSI_ISG_ORDER_IMPORT_API','- Customer Location ID is ' || p_location_id,1); END IF; COMMIT; ELSE DECLARE v_message VARCHAR2 (10000); v_message_index VARCHAR2 (10000); BEGIN IF l_msg_count > 0 THEN FOR x_index IN 1 .. l_msg_count LOOP FND_MSG_PUB.GET (p_msg_index => x_index, p_encoded => FND_API.G_FALSE, p_data => v_message, p_msg_index_out => v_message_index); dbms_output.put_line('API error while creating location for customer '|| v_message); IF l_debug_level >=1 THEN xxbsi_debug.debug_msg('XXBSI_ISG_ORDER_IMPORT_API','- API error while creating location for customer '|| v_message,1); END IF; END LOOP; END IF; p_error_msg := v_message; ROLLBACK; END; END IF; EXCEPTION WHEN OTHERS THEN p_error_msg := SQLERRM; dbms_output.put_line('Error while creating location for customer ' || SQLERRM); IF l_debug_level >=1 THEN xxbsi_debug.debug_msg('XXBSI_ISG_ORDER_IMPORT_API','- Error while creating location for customer ' || SQLERRM,1); END IF; END CREATE_NEW_LOCATION; PROCEDURE CREATE_PARTY_SITE (p_party_id NUMBER, p_location_id NUMBER, p_party_site_id OUT NUMBER, p_return_status OUT VARCHAR2, p_error_message OUT VARCHAR2) IS l_party_site_rec hz_party_site_v2pub.party_site_rec_type; l_party_site_number VARCHAR2 (2000); l_party_site_id NUMBER; l_return_status VARCHAR2 (2000); l_msg_count NUMBER; l_msg_data VARCHAR2 (2000); BEGIN -- l_debug_level := xxbsi_debug.set_debug('XXBSI_ISG_ORDER_IMPORT_API'); dbms_output.put_line('Party Site'); IF l_debug_level >=1 THEN xxbsi_debug.debug_msg('XXBSI_ISG_ORDER_IMPORT_API','- Party Site Creation ',1); END IF; l_party_site_rec.party_id := p_party_id; l_party_site_rec.location_id := p_location_id; -- l_party_site_rec.identifying_address_flag := 'Y'; l_party_site_rec.created_by_module := 'TCA_FORM_WRAPPER'; hz_party_site_v2pub.create_party_site ( p_init_msg_list => FND_API.G_TRUE, p_party_site_rec => l_party_site_rec, x_party_site_id => l_party_site_id, x_party_site_number => l_party_site_number, x_return_status => l_return_status, x_msg_count => l_msg_count, x_msg_data => l_msg_data); DECLARE v_sts VARCHAR2 (10); BEGIN IF l_return_status = 'E' THEN v_sts := 'ERROR'; ELSIF l_return_status = 'S' THEN v_sts := 'SUCCESS'; p_party_site_id := l_party_site_id; ELSIF l_return_status = 'W' THEN v_sts := 'WARNING'; ELSE v_sts := l_return_status; END IF; p_return_status := v_sts; END; IF (l_return_status = fnd_api.g_ret_sts_success) THEN dbms_output.put_line('Creation of Customer Party Site is Successful'); IF l_debug_level >=1 THEN xxbsi_debug.debug_msg('XXBSI_ISG_ORDER_IMPORT_API','- Creation of Customer Party Site is Successful',1); xxbsi_debug.debug_msg('XXBSI_ISG_ORDER_IMPORT_API','- Customer Party Site ID is ' || p_party_site_id,1); END IF; COMMIT; ELSE DECLARE v_message VARCHAR2 (10000); v_message_index VARCHAR2 (10000); BEGIN IF l_msg_count > 0 THEN FOR x_index IN 1 .. l_msg_count LOOP FND_MSG_PUB.GET (p_msg_index => x_index, p_encoded => FND_API.G_FALSE, p_data => v_message, p_msg_index_out => v_message_index); dbms_output.put_line('API error while creating Customer Party Site '|| v_message); IF l_debug_level >=1 THEN xxbsi_debug.debug_msg('XXBSI_ISG_ORDER_IMPORT_API','- API error while creating Customer Party Site '|| v_message,1); END IF; END LOOP; END IF; p_error_message := v_message; ROLLBACK; END; END IF; EXCEPTION WHEN OTHERS THEN p_error_message := SQLERRM; dbms_output.put_line('Error while creating Customer party site ' || SQLERRM); IF l_debug_level >=3 THEN xxbsi_debug.debug_msg('XXBSI_ISG_ORDER_IMPORT_API','- Error while creating Customer party site ' || SQLERRM,1); END IF; END CREATE_PARTY_SITE; PROCEDURE CREATE_CUST_ACCT_SITE (p_cust_account_id NUMBER, p_party_site_id NUMBER, p_cust_acct_site_id OUT NUMBER, p_return_status OUT VARCHAR2, p_error_message OUT VARCHAR2) IS p_customer_acount_site_record hz_cust_account_site_v2pub.cust_acct_site_rec_type; l_cust_acct_site_id NUMBER; l_return_status VARCHAR2 (200); l_msg_count NUMBER; l_msg_data VARCHAR2 (200); BEGIN -- l_debug_level := xxbsi_debug.set_debug('XXBSI_ISG_ORDER_IMPORT_API'); IF l_debug_level >=1 THEN xxbsi_debug.debug_msg('XXBSI_ISG_ORDER_IMPORT_API','- Customer Account Site Creation ',1); END IF; -- p_customer_acount_site_record.cust_account_id := p_cust_account_id; p_customer_acount_site_record.party_site_id := p_party_site_id; p_customer_acount_site_record.created_by_module := 'TCA_FORM_WRAPPER'; hz_cust_account_site_v2pub.CREATE_CUST_ACCT_SITE ( p_init_msg_list => FND_API.G_TRUE, p_cust_acct_site_rec => p_customer_acount_site_record, x_cust_acct_site_id => l_cust_acct_site_id, x_return_status => l_return_status, x_msg_count => l_msg_count, x_msg_data => l_msg_data); DECLARE v_sts VARCHAR2 (10); BEGIN IF l_return_status = 'E' THEN v_sts := 'ERROR'; ELSIF l_return_status = 'S' THEN v_sts := 'SUCCESS'; p_cust_acct_site_id := l_cust_acct_site_id; ELSIF l_return_status = 'W' THEN v_sts := 'WARNING'; ELSE v_sts := l_return_status; END IF; p_return_status := v_sts; END; IF (l_return_status = fnd_api.g_ret_sts_success) THEN dbms_output.put_line('Creation of Customer Account Site is Successful'); IF l_debug_level >=1 THEN xxbsi_debug.debug_msg('XXBSI_ISG_ORDER_IMPORT_API','- Creation of Customer Account Site is Successful',1); xxbsi_debug.debug_msg('XXBSI_ISG_ORDER_IMPORT_API','- Customer Account Site ID is ' || p_cust_acct_site_id,1); END IF; COMMIT; ELSE DECLARE v_message VARCHAR2 (10000); v_message_index VARCHAR2 (10000); BEGIN IF l_msg_count > 0 THEN FOR x_index IN 1 .. l_msg_count LOOP FND_MSG_PUB.GET (p_msg_index => x_index, p_encoded => FND_API.G_FALSE, p_data => v_message, p_msg_index_out => v_message_index); dbms_output.put_line('API error while creating Customer Account Site '|| v_message); IF l_debug_level >=1 THEN xxbsi_debug.debug_msg('XXBSI_ISG_ORDER_IMPORT_API','- API error while creating Customer Account Site '|| v_message,1); END IF; END LOOP; END IF; p_error_message := v_message; ROLLBACK; END; END IF; EXCEPTION WHEN OTHERS THEN p_error_message := SQLERRM; dbms_output.put_line('Error while creating Customer Account Site ' || SQLERRM); IF l_debug_level >=3 THEN xxbsi_debug.debug_msg('XXBSI_ISG_ORDER_IMPORT_API','- Error while creating Customer Account Site ' || SQLERRM,1); END IF; END CREATE_CUST_ACCT_SITE; PROCEDURE CREATE_CUST_SITE_USE (p_cust_acct_site_id NUMBER, p_site_use_id OUT NUMBER, p_return_status OUT VARCHAR2, p_error_message OUT VARCHAR2) IS p_cust_site_use_rec HZ_CUST_ACCOUNT_SITE_V2PUB.CUST_SITE_USE_REC_TYPE; p_customer_profile_rec HZ_CUSTOMER_PROFILE_V2PUB.CUSTOMER_PROFILE_REC_TYPE; l_site_use_id NUMBER; l_return_status VARCHAR2 (200); l_msg_count NUMBER; l_msg_data VARCHAR2 (200); l_location NUMBER; l_order_type_id NUMBER; l_fob_point VARCHAR2 (15); l_price_list_id NUMBER; l_payment_term_id NUMBER; BEGIN l_debug_level := xxbsi_debug.set_debug('XXBSI_ISG_ORDER_IMPORT_API'); -- IF l_debug_level >=1 THEN xxbsi_debug.debug_msg('XXBSI_ISG_ORDER_IMPORT_API','- Customer Account Site Use Creation ' || SQLERRM,1); END IF; -- Get location value from sequence SELECT hz_cust_site_uses_s.NEXTVAL INTO l_location FROM DUAL; -- p_cust_site_use_rec.cust_acct_site_id := p_cust_acct_site_id; p_cust_site_use_rec.site_use_code := 'SHIP_TO'; p_cust_site_use_rec.created_by_module := 'TCA_FORM_WRAPPER'; p_cust_site_use_rec.location := l_location; HZ_CUST_ACCOUNT_SITE_V2PUB.CREATE_CUST_SITE_USE ( p_init_msg_list => FND_API.G_TRUE, p_cust_site_use_rec => p_cust_site_use_rec, p_customer_profile_rec => p_customer_profile_rec, p_create_profile => FND_API.G_TRUE, p_create_profile_amt => FND_API.G_TRUE, x_site_use_id => l_site_use_id, x_return_status => l_return_status, x_msg_count => l_msg_count, x_msg_data => l_msg_data); DECLARE v_sts VARCHAR2 (10); BEGIN IF l_return_status = 'E' THEN v_sts := 'ERROR'; ELSIF l_return_status = 'S' THEN v_sts := 'SUCCESS'; p_site_use_id := l_site_use_id; ELSIF l_return_status = 'W' THEN v_sts := 'WARNING'; ELSE v_sts := l_return_status; END IF; p_return_status := v_sts; END; IF (l_return_status = fnd_api.g_ret_sts_success) THEN dbms_output.put_line('Creation of Customer Account Site Use is Successful'); IF l_debug_level >=1 THEN xxbsi_debug.debug_msg('XXBSI_ISG_ORDER_IMPORT_API','- Creation of Customer Account Site Use is Successful',1); xxbsi_debug.debug_msg('XXBSI_ISG_ORDER_IMPORT_API','- Customer Account Site Use ID is ' || p_site_use_id ,1); END IF; COMMIT; ELSE DECLARE v_message VARCHAR2 (10000); v_message_index VARCHAR2 (10000); BEGIN IF l_msg_count > 0 THEN FOR x_index IN 1 .. l_msg_count LOOP FND_MSG_PUB.GET (p_msg_index => x_index, p_encoded => FND_API.G_FALSE, p_data => v_message, p_msg_index_out => v_message_index); dbms_output.put_line('API error while creating Customer Account Site Use '|| v_message); IF l_debug_level >=1 THEN xxbsi_debug.debug_msg('XXBSI_ISG_ORDER_IMPORT_API','- API error while creating Customer Account Site Use '|| v_message,1); END IF; END LOOP; END IF; p_error_message := v_message; ROLLBACK; END; END IF; EXCEPTION WHEN OTHERS THEN p_error_message := SQLERRM; dbms_output.put_line('Error while creating Customer Account Site Use ' || SQLERRM); IF l_debug_level >=1 THEN xxbsi_debug.debug_msg('XXBSI_ISG_ORDER_IMPORT_API','- Error while creating Customer Account Site Use ' || SQLERRM,1); END IF; END CREATE_CUST_SITE_USE; END xxbsi_isg_order_import_api; /