Order import via API

CREATE OR REPLACE PACKAGE BODY APPS.ksom_order_import_r12_pkg AS /****************************************************************************** NAME: ksom_order_import_pkg PURPOSE: REVISIONS: Ver Date Author Description --------- ---------- --------------- ------------------------------------ 1.0 2008/5/29 1. Created this package body. ******************************************************************************/ CURSOR C_ORDER_header (cin_row in number ) IS SELECT DL.ROWID ROW_ID, DL.* FROM KSOM_DRAFT_LINES_ALL DL WHERE DL.PROCESS_STATUS = 'C' AND DL.STATUS = 'A' AND NVL(DL.SOURCE, 'Z') <> 'R' and rownum < nvl(cin_row,100); -- 201412 vicky 可指定筆數 -- AND DL.SOURCE=P_SOURCE; PROCEDURE MAIN_PROCESS(rtncode out varchar2, rtnmsg out varchar2, p_process in varchar2, p_source in varchar2, in_row in number default 100) is begin if p_process = 'A' then for r_order_header in c_order_header (in_row) loop create_order(p_source, r_order_header.line_id); end loop; end if; end; procedure create_order(p_source in varchar2, p_line_id in number) is 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_adj_tbl OE_ORDER_PUB.Header_Adj_Tbl_Type; l_line_adj_tbl OE_ORDER_PUB.line_adj_tbl_Type; l_header_scr_tbl OE_ORDER_PUB.Header_Scredit_Tbl_Type; l_line_scredit_tbl OE_ORDER_PUB.Line_Scredit_Tbl_Type; l_request_rec OE_ORDER_PUB.Request_Rec_Type ; l_return_status VARCHAR2 ( 1000 ) ; l_msg_count NUMBER; l_msg_data VARCHAR2 ( 1000 ) ; p_api_version_number NUMBER :=1.0; p_init_msg_list VARCHAR2 ( 10 ) := FND_API.G_FALSE; p_return_values VARCHAR2 ( 10 ) := FND_API.G_FALSE; p_action_commit VARCHAR2 ( 10 ) := FND_API.G_FALSE; x_return_status VARCHAR2 ( 1 ) ; x_msg_count NUMBER; x_msg_data VARCHAR2 ( 100 ) ; x_header_rec OE_ORDER_PUB.Header_Rec_Type := OE_ORDER_PUB.G_MISS_HEADER_REC; p_old_header_rec OE_ORDER_PUB.Header_Rec_Type := OE_ORDER_PUB.G_MISS_HEADER_REC; p_header_val_rec OE_ORDER_PUB.Header_Val_Rec_Type := OE_ORDER_PUB.G_MISS_HEADER_VAL_REC; p_old_header_val_rec OE_ORDER_PUB.Header_Val_Rec_Type := OE_ORDER_PUB.G_MISS_HEADER_VAL_REC; p_Header_Adj_tbl OE_ORDER_PUB.Header_Adj_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_ADJ_TBL; p_old_Header_Adj_tbl OE_ORDER_PUB.Header_Adj_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_ADJ_TBL; p_Header_Adj_val_tbl OE_ORDER_PUB.Header_Adj_Val_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_ADJ_VAL_TBL; p_old_Header_Adj_val_tbl OE_ORDER_PUB.Header_Adj_Val_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_ADJ_VAL_TBL; p_Header_price_Att_tbl OE_ORDER_PUB.Header_Price_Att_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_PRICE_ATT_TBL; p_old_Header_Price_Att_tbl OE_ORDER_PUB.Header_Price_Att_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_PRICE_ATT_TBL; p_Header_Adj_Att_tbl OE_ORDER_PUB.Header_Adj_Att_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_ADJ_ATT_TBL; p_old_Header_Adj_Att_tbl OE_ORDER_PUB.Header_Adj_Att_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_ADJ_ATT_TBL; p_Header_Adj_Assoc_tbl OE_ORDER_PUB.Header_Adj_Assoc_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_ADJ_ASSOC_TBL; p_old_Header_Adj_Assoc_tbl OE_ORDER_PUB.Header_Adj_Assoc_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_ADJ_ASSOC_TBL; p_Header_Scredit_tbl OE_ORDER_PUB.Header_Scredit_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_SCREDIT_TBL; p_old_Header_Scredit_tbl OE_ORDER_PUB.Header_Scredit_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_SCREDIT_TBL; p_Header_Scredit_val_tbl OE_ORDER_PUB.Header_Scredit_Val_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_SCREDIT_VAL_TBL; p_old_Header_Scredit_val_tbl OE_ORDER_PUB.Header_Scredit_Val_Tbl_Type := OE_ORDER_PUB.G_MISS_HEADER_SCREDIT_VAL_TBL; p_line_tbl OE_ORDER_PUB.Line_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_TBL; p_old_line_tbl OE_ORDER_PUB.Line_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_TBL; p_line_val_tbl OE_ORDER_PUB.Line_Val_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_VAL_TBL; p_old_line_val_tbl OE_ORDER_PUB.Line_Val_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_VAL_TBL; p_Line_Adj_tbl OE_ORDER_PUB.Line_Adj_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_ADJ_TBL; p_old_Line_Adj_tbl OE_ORDER_PUB.Line_Adj_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_ADJ_TBL; p_Line_Adj_val_tbl OE_ORDER_PUB.Line_Adj_Val_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_ADJ_VAL_TBL; p_old_Line_Adj_val_tbl OE_ORDER_PUB.Line_Adj_Val_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_ADJ_VAL_TBL; p_Line_price_Att_tbl OE_ORDER_PUB.Line_Price_Att_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_PRICE_ATT_TBL; p_old_Line_Price_Att_tbl OE_ORDER_PUB.Line_Price_Att_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_PRICE_ATT_TBL; p_Line_Adj_Att_tbl OE_ORDER_PUB.Line_Adj_Att_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_ADJ_ATT_TBL; p_old_Line_Adj_Att_tbl OE_ORDER_PUB.Line_Adj_Att_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_ADJ_ATT_TBL; p_Line_Adj_Assoc_tbl OE_ORDER_PUB.Line_Adj_Assoc_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_ADJ_ASSOC_TBL; p_old_Line_Adj_Assoc_tbl OE_ORDER_PUB.Line_Adj_Assoc_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_ADJ_ASSOC_TBL; p_Line_Scredit_tbl OE_ORDER_PUB.Line_Scredit_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_SCREDIT_TBL; p_old_Line_Scredit_tbl OE_ORDER_PUB.Line_Scredit_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_SCREDIT_TBL; p_Line_Scredit_val_tbl OE_ORDER_PUB.Line_Scredit_Val_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_SCREDIT_VAL_TBL; p_old_Line_Scredit_val_tbl OE_ORDER_PUB.Line_Scredit_Val_Tbl_Type := OE_ORDER_PUB.G_MISS_LINE_SCREDIT_VAL_TBL; p_Lot_Serial_tbl OE_ORDER_PUB.Lot_Serial_Tbl_Type := OE_ORDER_PUB.G_MISS_LOT_SERIAL_TBL; p_old_Lot_Serial_tbl OE_ORDER_PUB.Lot_Serial_Tbl_Type := OE_ORDER_PUB.G_MISS_LOT_SERIAL_TBL; p_Lot_Serial_val_tbl OE_ORDER_PUB.Lot_Serial_Val_Tbl_Type := OE_ORDER_PUB.G_MISS_LOT_SERIAL_VAL_TBL; p_old_Lot_Serial_val_tbl OE_ORDER_PUB.Lot_Serial_Val_Tbl_Type := OE_ORDER_PUB.G_MISS_LOT_SERIAL_VAL_TBL; p_action_request_tbl OE_ORDER_PUB.Request_Tbl_Type := OE_ORDER_PUB.G_MISS_REQUEST_TBL; x_header_val_rec OE_ORDER_PUB.Header_Val_Rec_Type; x_Header_Adj_tbl OE_ORDER_PUB.Header_Adj_Tbl_Type; x_Header_Adj_val_tbl OE_ORDER_PUB.Header_Adj_Val_Tbl_Type; x_Header_price_Att_tbl OE_ORDER_PUB.Header_Price_Att_Tbl_Type; x_Header_Adj_Att_tbl OE_ORDER_PUB.Header_Adj_Att_Tbl_Type; x_Header_Adj_Assoc_tbl OE_ORDER_PUB.Header_Adj_Assoc_Tbl_Type; x_Header_Scredit_tbl OE_ORDER_PUB.Header_Scredit_Tbl_Type; x_Header_Scredit_val_tbl OE_ORDER_PUB.Header_Scredit_Val_Tbl_Type; x_line_val_tbl OE_ORDER_PUB.Line_Val_Tbl_Type; x_Line_Adj_tbl OE_ORDER_PUB.Line_Adj_Tbl_Type; x_Line_Adj_val_tbl OE_ORDER_PUB.Line_Adj_Val_Tbl_Type; x_Line_price_Att_tbl OE_ORDER_PUB.Line_Price_Att_Tbl_Type; x_Line_Adj_Att_tbl OE_ORDER_PUB.Line_Adj_Att_Tbl_Type; x_Line_Adj_Assoc_tbl OE_ORDER_PUB.Line_Adj_Assoc_Tbl_Type; x_Line_Scredit_tbl OE_ORDER_PUB.Line_Scredit_Tbl_Type; x_Line_Scredit_val_tbl OE_ORDER_PUB.Line_Scredit_Val_Tbl_Type; x_Lot_Serial_tbl OE_ORDER_PUB.Lot_Serial_Tbl_Type; x_Lot_Serial_val_tbl OE_ORDER_PUB.Lot_Serial_Val_Tbl_Type; x_action_request_tbl OE_ORDER_PUB.Request_Tbl_Type; X_DEBUG_FILE VARCHAR2 ( 100 ) ; l_line_tbl_index NUMBER; l_msg_index_out NUMBER ( 10 ) ; i NUMBER; vautoposo_flag VARCHAR2(10); x_inventory_item_id KSOM_DRAFT_LINES_ALL.Inventory_Item_Id%TYPE; x_terms PO_HEADERS_INTERFACE.TERMS_ID%TYPE; --x_ship_to_location_id PO_HEADERS_INTERFACE.SHIP_TO_LOCATION_ID%TYPE; x_fob PO_HEADERS_INTERFACE.FOB%TYPE; x_agent_id PO_HEADERS_INTERFACE.agent_id%TYPE; x_tax_code_id PO_LINES_INTERFACE.TAX_CODE_ID%TYPE; x_tax_name PO_LINES_INTERFACE.TAX_NAME%TYPE; --20200605 Jason x_item_desc varchar2(240); x_bill_to PO_HEADERS_INTERFACE.Bill_To_Location_Id%TYPE; x_vendor_id PO_HEADERS_INTERFACE.Vendor_Id%TYPE; x_vendor_site_id PO_HEADERS_INTERFACE.Vendor_Site_Id%TYPE; x_err_log VARCHAR2(4000); CURSOR C_ORDER IS SELECT DL.ROWID ROW_ID, DL.* FROM KSOM_DRAFT_LINES_ALL DL WHERE DL.PROCESS_STATUS = 'C' and dl.line_id = p_line_id -- AND DL.STATUS='A'; -- AND DL.SOURCE=P_SOURCE; ; CURSOR C_LINES IS SELECT dl.*, dl.rowid row_id, nvl(ito.commit_due_date, dl.shipping_date) SCHEDULE_SHIP_DATE, DECODE(upper(dl.uom), -- 20181106 by marklin follow 前製程委統碩寫法 'PNL', nvl(ito.pnl_qty, dl.purchase_qty), nvl(ito.pcs_qty, dl.PURCHASE_QTY)) ordered_quantity, nvl(ito.customer_ship_date, dl.shipping_date) customer_ship_date, nvl(ito.reply_customer_date,ito.customer_ship_date) reply_customer_date, --202010 X01 EDI Jason WO_FACT_NO FROM KSOM_DRAFT_LINES_ALL DL, Ksom_item_type_order ito where dl.serial_no = ito.serial_no(+) and dl.line_id = p_line_id; BEGIN dbms_output.enable ( 1000000 ) ; fnd_global.apps_initialize ( fnd_global.user_id, 21623, 660 ) ; -- pass in user_id, responsibility_id, and application_id MO_GLOBAL.INIT ( 'ONT' ) ; -- Required for R12 mo_global.set_org_context ( 2, NULL, 'ONT' ) ; fnd_global.set_nls_context ( 'AMERICAN' ) ; MO_GLOBAL.SET_POLICY_CONTEXT ( 'S', 2 ) ; -- Required for R12 oe_msg_pub.initialize; --oe_debug_pub.initialize; --X_DEBUG_FILE := OE_DEBUG_PUB.Set_Debug_Mode ( 'FILE' ) ; --oe_debug_pub.SetDebugLevel ( 5 ) ; -- Use 5 for the most debuging output, I warn you its a lot of data dbms_output.put_line ( 'START OF NEW DEBUG' ) ; --This is to CREATE an order header and an order line --Create Header record --Initialize header record to missing FOR R_ORDER IN C_ORDER LOOP l_header_rec := OE_ORDER_PUB.G_MISS_HEADER_REC; l_header_rec.order_type_id := r_order.order_type_id; l_header_rec.price_list_id := r_order.price_list_id; l_header_rec.salesrep_id := r_order.PRIMARY_SALESREP_ID; l_header_rec.ship_from_org_id := r_order.ship_from_org_id; l_header_rec.sold_from_org_id := fnd_profile.value('ORG_ID'); l_header_rec.cust_po_number := r_order.po_no; l_header_rec.invoice_to_org_id := r_order.bill_to_org_id; l_header_rec.ship_to_org_id := r_order.ship_to_org_id; l_header_rec.sold_to_org_id := r_order.customer_id; l_header_rec.payment_term_id := r_order.payment_term_id; l_header_rec.fob_point_code := r_order.fob_point; l_header_rec.attribute1 := r_order.header_attribute1; l_header_rec.attribute2 := r_order.header_attribute2; --to_char(r_order.SHIPPING_DATE,'DD-MON-YYYY'); l_header_rec.ATTRIBUTE3 := r_order.header_attribute3; --to_char(r_order.ALLOCATION_DATE,'DD-MON-YYYY'); l_header_rec.attribute4 := r_order.header_attribute4; l_header_rec.attribute5 := r_order.header_attribute5; l_header_rec.ATTRIBUTE6 := r_order.header_attribute6; l_header_rec.attribute7 := r_order.header_attribute7; l_header_rec.attribute8 := r_order.header_attribute8; l_header_rec.ATTRIBUTE9 := r_order.header_attribute9; l_header_rec.attribute10 := r_order.header_attribute10; l_header_rec.attribute11 := r_order.header_attribute11; l_header_rec.ATTRIBUTE12 := r_order.header_attribute12; l_header_rec.ATTRIBUTE13 := r_order.serial_no; --r_order.header_attribute13; l_header_rec.attribute14 := r_order.header_attribute14; l_header_rec.attribute15 := r_order.header_attribute15; l_header_rec.ATTRIBUTE16 := r_order.header_attribute16; l_header_rec.attribute17 := r_order.header_attribute17; l_header_rec.attribute18 := r_order.header_attribute18; l_header_rec.ATTRIBUTE19 := r_order.header_attribute19; l_header_rec.attribute20 := r_order.header_attribute20; l_header_rec.ordered_date := nvl(r_order.ordered_date,sysdate); l_header_rec.operation := OE_GLOBALS.G_OPR_CREATE; l_line_tbl_index :=1; fnd_file.put_line(fnd_file.LOG, R_ORDER.line_id); i := 0; for r_lines in c_lines loop i := i + 1; fnd_file.put_line(fnd_file.LOG, i); l_line_tbl(i) := OE_ORDER_PUB.G_MISS_LINE_REC; l_line_tbl(i).inventory_item_id := r_lines.inventory_item_id; l_line_tbl(i).ordered_quantity := r_lines.ordered_quantity; l_line_tbl(i).UNIT_SELLING_PRICE := to_number(r_lines.up); l_line_tbl(i).UNIT_LIST_PRICE := to_number(r_lines.up); l_line_tbl(i).CALCULATE_PRICE_FLAG := 'N'; l_line_tbl(i).CONTEXT := 'Shipping Information'; l_line_tbl(i).SCHEDULE_SHIP_DATE := R_lines.SCHEDULE_SHIP_DATE; l_line_tbl(i).PROMISE_DATE := R_lines.SCHEDULE_SHIP_DATE; l_line_tbl(i).fob_point_code := r_lines.fob_point; l_line_tbl(i).tax_code := r_lines.tax_code; --l_line_tbl(i).context := 'STANDARD'; l_line_tbl(i).attribute1 := r_lines.line_attribute1; l_line_tbl(i).attribute2 := r_lines.line_attribute2; l_line_tbl(i).attribute3 := r_lines.line_attribute3; l_line_tbl(i).attribute4 := r_lines.line_attribute4; l_line_tbl(i).attribute5 := r_lines.line_attribute5; l_line_tbl(i).attribute6 := r_lines.line_attribute6; --l_line_tbl(i).attribute7 := r_lines.line_attribute7; -- 20180726 add 原本的PHASEOUT 改成WO_FACT_NO l_line_tbl(i).attribute7 := r_lines.wo_fact_no; l_line_tbl(i).attribute8 := r_lines.line_attribute8; l_line_tbl(i).attribute9 := r_lines.line_attribute9; l_line_tbl(i).attribute10 := TO_CHAR(r_lines.CUSTOMER_SHIP_DATE, 'DD-MON-YYYY'); --r_lines.line_attribute10; l_line_tbl(i).attribute11 := r_lines.line_attribute11; l_line_tbl(i).attribute12 := TO_CHAR(r_lines.CUSTOMER_SHIP_DATE, 'DD-MON-YYYY'); --r_lines.line_attribute12 ; l_line_tbl(i).attribute13 := TO_CHAR(r_lines.reply_customer_date, 'DD-MON-YYYY');-- r_lines.line_attribute13; 202010 X01 EDI Jason l_line_tbl(i).customer_line_number := r_lines.line;-- 202010 X01 EDI Jason l_line_tbl(i).attribute14 := r_lines.line_attribute14; l_line_tbl(i).attribute15 := r_lines.line_attribute15; l_line_tbl(i).attribute16 := r_lines.line_attribute16; l_line_tbl(i).attribute17 := r_lines.line_attribute17; l_line_tbl(i).attribute18 := r_lines.line_attribute18; l_line_tbl(i).attribute19 := r_lines.line_attribute19; l_line_tbl(i).attribute20 := r_lines.line_attribute20; l_line_tbl(i).salesrep_id := r_lines.primary_salesrep_id; --l_line_tbl(i).packing_instructions := r_lines.packing_instructions; l_line_tbl(i).ORDER_QUANTITY_UOM := r_lines.UOM; l_line_tbl(i).operation := OE_GLOBALS.G_OPR_CREATE; --tbl_sz_line_id(i) := rl.sz_order_line_id; l_line_tbl(i).shipping_instructions := r_lines.shipping_instructions; l_line_tbl(i).ship_to_contact_id := r_lines.ship_to_contact_id; l_line_tbl(i).invoice_to_contact_id := r_lines.invoice_to_contact_id; -- 20180523 add by marklin end loop; l_action_request_tbl(l_line_tbl_index).request_type := oe_globals.g_book_order; l_action_request_tbl(l_line_tbl_index).entity_code := oe_globals.g_entity_header; -- l_action_request_tbl(l_line_tbl_index).entity_id := 190383; -- CALL TO PROCESS ORDER Check the return status and then commit. OE_ORDER_PUB.process_order ( p_api_version_number => 1.0, p_init_msg_list => fnd_api.g_false, p_return_values => fnd_api.g_false, p_action_commit => fnd_api.g_false, x_return_status => l_return_status, x_msg_count => l_msg_count, x_msg_data => l_msg_data, p_header_rec => l_header_rec, p_line_tbl => l_line_tbl, p_action_request_tbl => l_action_request_tbl -- OUT PARAMETERS , x_header_rec => x_header_rec , x_header_val_rec => x_header_val_rec , x_Header_Adj_tbl => x_Header_Adj_tbl , x_Header_Adj_val_tbl => x_Header_Adj_val_tbl , x_Header_price_Att_tbl => x_Header_price_Att_tbl , x_Header_Adj_Att_tbl => x_Header_Adj_Att_tbl , x_Header_Adj_Assoc_tbl => x_Header_Adj_Assoc_tbl , x_Header_Scredit_tbl => x_Header_Scredit_tbl , x_Header_Scredit_val_tbl => x_Header_Scredit_val_tbl , x_line_tbl => p_line_tbl , x_line_val_tbl => x_line_val_tbl , x_Line_Adj_tbl => x_Line_Adj_tbl , x_Line_Adj_val_tbl => x_Line_Adj_val_tbl , x_Line_price_Att_tbl => x_Line_price_Att_tbl , x_Line_Adj_Att_tbl => x_Line_Adj_Att_tbl , x_Line_Adj_Assoc_tbl => x_Line_Adj_Assoc_tbl , x_Line_Scredit_tbl => x_Line_Scredit_tbl , x_Line_Scredit_val_tbl => x_Line_Scredit_val_tbl , x_Lot_Serial_tbl => x_Lot_Serial_tbl , x_Lot_Serial_val_tbl => x_Lot_Serial_val_tbl , x_action_request_tbl => x_action_request_tbl ) ; --dbms_output.put_line ( 'OM Debug file: ' ||oe_debug_pub.G_DIR||'/'||oe_debug_pub.G_FILE ) ; dbms_output.put_line ('Header_ID : '||x_header_rec.header_id); dbms_output.put_line('status: ' || x_header_rec.flow_status_code); -- Retrieve messages fnd_file.put_line(fnd_file.log, 'header_id='||x_header_rec.header_id ); x_err_log := null; FOR i IN 1 .. l_msg_count LOOP Oe_Msg_Pub.get ( p_msg_index => i, p_encoded => Fnd_Api.G_FALSE, p_data => l_msg_data, p_msg_index_out => l_msg_index_out ) ; DBMS_OUTPUT.PUT_LINE ( 'message is: ' || l_msg_data ) ; DBMS_OUTPUT.PUT_LINE ( 'message index is: ' || l_msg_index_out ) ; l_msg_data := oe_msg_pub.get(p_msg_index => i,p_encoded => 'F'); fnd_file.put_line(fnd_file.LOG, i || '=>' || l_msg_data); x_err_log := x_err_log || l_msg_data; END LOOP; -- Check the return status IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN dbms_output.put_line ( 'Process Order Sucess' ) ; ELSE dbms_output.put_line ( 'Failed' ) ; END IF; -- [S] Evelyn 2012/8/27 Insert into PO header Interface --run PO request : KSPO 請購單轉採購單資料轉入作業 Set (Report Set) -- : Import Standard Purchase Orders (, STANDARD, , Y, , APPROVED, , , ,) --依據Auto PO/SO Ruler設定之幣別, 匯率… fnd_file.put_line(fnd_file.log, 'Customer Code:' ||r_order.customer_code); if r_order.customer_code='T99' then vautoposo_flag :='Y'; else vautoposo_flag :='N'; end if; if vautoposo_flag ='Y' then --AP Payment Terms, Tax_Code_id begin -- 20190118 by marklin R12 新抓法 select s.terms_id, nvl(ktc.TAX_RATE_ID, lc.key2), ktc.tax_rate_code, lc.key1, s.fob_lookup_code, s.bill_to_location_id, s.vendor_id, s.vendor_site_id into x_terms, x_tax_code_id, x_tax_name, --20200605 Jason x_agent_id, x_fob, x_bill_to, x_vendor_id, x_vendor_site_id from AP.AP_SUPPLIER_SITES_ALL s, kszx_tax_codes_v ktc, ksfnd_lookup_codes lc where lc.lookup_type = 'KSOM_AUTOPOSO_PROFILES' and lc.key3 = 'PO_DEFAULT' and s.vendor_id = lc.key4 and s.vendor_site_id = lc.key5 and nvl(s.vat_code,'0%VAT') = ktc.tax_rate_code -- 20190609 by marklin 先應急處理 and s.org_id = ktc.org_id and lc.VALUECODE=128 ; /* select terms_id, nvl(TAX_ID, lc.key2), lc.key1, s.fob_lookup_code, s.bill_to_location_id, s.vendor_id, s.vendor_site_id into x_terms, x_tax_code_id, x_agent_id, x_fob, x_bill_to, x_vendor_id, x_vendor_site_id from Po_Vendor_Sites_All s, ap_tax_codes_all t, ksfnd_lookup_codes lc where lc.lookup_type = 'KSOM_AUTOPOSO_PROFILES' and lc.key3 = 'PO_DEFAULT' and vendor_id = lc.key4 and vendor_site_id = lc.key5 and VALUECODE=128 -- 統碩委景碩, PO 下在統碩 and s.vat_code = t.name(+); */ exception when others then fnd_file.put_line(fnd_file.log, 'ERROR when Select Payment Terms, Tax_Code_id, FOB... '); end; INSERT INTO po.po_headers_interface (interface_header_id, --batch_id, process_code, action, org_id, document_type_code, currency_code, agent_id, vendor_id, vendor_site_id, bill_to_location_id, ship_to_location_id, reference_num, pay_on_code, COMMENTS, creation_date, --rate, --rate_date, --rate_type_code, TERMS_ID, --FREIGHT_TERMS, FOB ---- FREIGHT_CARRIER --,vendor_contact_id ) VALUES (apps.po_headers_interface_s.NEXTVAL, --x_batch_id,---ks_item_rec.SEGMENT1, 'PENDING', 'ORIGINAL', 128, ---2, -- Kinsus 'STANDARD', r_order.currency_code, ---'USD', -- Your currency code x_agent_id, --x_agent_id, ---25, -- Your buyer id x_vendor_id, --Vendor ID x_vendor_site_id, --Vendor Site ID x_bill_to, --r_order.bill_to_org_id, 11357, --x_ship_to_location_id, ' ', '', r_order.po_no, sysdate, --x_rate, --x_rate_date, --x_rate_type, x_terms, --term_id --x_FREIGHT_TERMS, x_fob ---- x_FREIGHT_CARRIER --,x_vendor_contact_id ); -- Any reference num --x_PO_I_H_ID := po_headers_interface_s.nextval; -- [E] Evelyn 2012/8/27 Insert into PO header Interface end if; fnd_file.put_line(fnd_file.log, R_ORDER.line_id); i := 0; for r_lines in c_lines loop i := i + 1; fnd_file.put_line(fnd_file.log, i); l_line_tbl(i) := OE_ORDER_PUB.G_MISS_LINE_REC; l_line_tbl(i).inventory_item_id := r_lines.inventory_item_id; l_line_tbl(i).ordered_quantity := r_lines.ordered_quantity; l_line_tbl(i).UNIT_SELLING_PRICE := to_number(r_lines.up); l_line_tbl(i).UNIT_LIST_PRICE := to_number(r_lines.up); l_line_tbl(i).CALCULATE_PRICE_FLAG := 'N'; l_line_tbl(i).CONTEXT := 'Shipping Information'; l_line_tbl(i).SCHEDULE_SHIP_DATE := R_lines.SCHEDULE_SHIP_DATE; l_line_tbl(i).PROMISE_DATE := R_lines.SCHEDULE_SHIP_DATE; l_line_tbl(i).fob_point_code := r_lines.fob_point; l_line_tbl(i).tax_code := r_lines.tax_code; --l_line_tbl(i).context := 'STANDARD'; l_line_tbl(i).attribute1 := r_lines.line_attribute1; l_line_tbl(i).attribute2 := r_lines.line_attribute2; l_line_tbl(i).attribute3 := r_lines.line_attribute3; l_line_tbl(i).attribute4 := r_lines.line_attribute4; l_line_tbl(i).attribute5 := r_lines.line_attribute5; l_line_tbl(i).attribute6 := r_lines.line_attribute6; l_line_tbl(i).attribute7 := r_lines.line_attribute7; l_line_tbl(i).attribute8 := r_lines.line_attribute8; l_line_tbl(i).attribute9 := r_lines.line_attribute9; l_line_tbl(i).attribute10 := TO_CHAR(r_lines.CUSTOMER_SHIP_DATE, 'DD-MON-YYYY'); --r_lines.line_attribute10; l_line_tbl(i).attribute11 := r_lines.line_attribute11; l_line_tbl(i).attribute12 := TO_CHAR(r_lines.CUSTOMER_SHIP_DATE, 'DD-MON-YYYY'); --r_lines.line_attribute12 ; l_line_tbl(i).attribute13 := r_lines.line_attribute13; l_line_tbl(i).attribute14 := r_lines.line_attribute14; l_line_tbl(i).attribute15 := r_lines.line_attribute15; l_line_tbl(i).attribute16 := r_lines.line_attribute16; l_line_tbl(i).attribute17 := r_lines.line_attribute17; l_line_tbl(i).attribute18 := r_lines.line_attribute18; l_line_tbl(i).attribute19 := r_lines.line_attribute19; l_line_tbl(i).attribute20 := r_lines.line_attribute20; l_line_tbl(i).salesrep_id := r_lines.primary_salesrep_id; --l_line_tbl(i).packing_instructions := r_lines.packing_instructions; l_line_tbl(i).ORDER_QUANTITY_UOM := r_lines.UOM; l_line_tbl(i).operation := OE_GLOBALS.G_OPR_CREATE; --tbl_sz_line_id(i) := rl.sz_order_line_id; l_line_tbl(i).shipping_instructions := r_lines.shipping_instructions; -- [S] Evelyn 2012/8/27 Insert into PO Lines Interface if vautoposo_flag ='Y' then fnd_file.put_line(fnd_file.log, 'Start Insert PO Line Interface ' || r_lines.line_id); IF r_order.MTL_CATEGORY = 'MATERIAL' THEN -- MTL_CATEGORY = 'MATERIAL' 為前製程委統碩，景碩以原料料號開PO select INVENTORY_ITEM_ID INTO x_inventory_item_id from mtl_system_items_b where segment1 = r_order.MTL_NO_O and organization_id = 130 and enabled_flag = 'Y' and rownum = 1; ELSE -- 全製程委統碩，景碩原訂單之料號別 SELECT inventory_item_id --,mtl_no||'|'|| r_lines.po_no INTO x_inventory_item_id --,x_item_desc FROM KSOM_DRAFT_LINES_ALL WHERE SZ_LINE_ID = r_lines.line_id; SELECT DESCRIPTION INTO x_item_desc FROM MTL_SYSTEM_ITEMS_B WHERE INVENTORY_ITEM_ID = x_inventory_item_id AND organization_id = 130; END IF; fnd_file.put_line(fnd_file.log, x_item_desc); INSERT INTO po_lines_interface (interface_line_id, interface_Header_id, item_id, item_revision, line_num, shipment_num, line_type_id, Category_ID, item_description, unit_of_measure, quantity, unit_price, --from_header_id, --from_line_id, need_by_date, creation_date, organization_id, --tax_code_id, tax_name, --20200605 Jason receipt_required_flag, inspection_required_flag) VALUES (po_lines_interface_s.nextval, po_headers_interface_s.currval, x_inventory_item_id, '00', i, 1, 1, 45, x_item_desc, upper(r_lines.UOM), r_lines.ordered_quantity, to_number(r_lines.up), --l_header_rec.header_id, --l_line_tbl(i).line_id, r_lines.CUSTOMER_SHIP_DATE, sysdate, 130, --x_tax_code_id, x_tax_name, --20200605 Jason 'Y', 'N'); INSERT INTO po.po_distributions_interface aa (interface_header_id, interface_line_id, interface_distribution_id, quantity_ordered, DELIVER_TO_LOCATION_ID, creation_date) VALUES (po_headers_interface_s.CURRVAL, po_lines_interface_s.CURRVAL, po_distributions_interface_s.NEXTVAL, r_lines.ordered_quantity, 11357, --x_ship_to_location_id, sysdate); -- [E] Evelyn 2012/8/27 Insert into PO Lines Interface end if; end loop; fnd_file.put_line(fnd_file.log, l_return_status); if vautoposo_flag ='Y' then update ksom_draft_lines_all set PROCESS_STATUS = l_return_status, OM_SO = x_header_rec.header_id, PR_NUMBER = po_headers_interface_s.currval where rowid = r_order.row_id; update po.po_headers_interface set ATTRIBUTE4 = x_header_rec.header_id where COMMENTS = r_order.po_no; else update ksom_draft_lines_all set PROCESS_STATUS = l_return_status, om_so = x_header_rec.header_id, err_log = decode(x_err_log, null, err_log, substr(err_log || ' *' || substr(x_err_log, 1, 300), 1, 900)) where rowid = r_order.row_id; end if; if substr(r_order.mtl_no, 1, 4) = '7S11' and nvl(r_order.line_attribute4, '~') = 'FA' then insert into KSOM_S11_REQUESTOR (CUSTOMER_PO, ITEM_NO, REQUESTOR, INVENTORY_ITEM_ID, STATUS, USER_NAME, CREATION_DATE, LAST_UPDATE_DATE, LAST_UPDATED_NAME) values (r_order.po_no, r_order.mtl_no, r_order.requester, r_order.inventory_item_id, 'A', ks_global.EMPLOYEE_ID_NUMBER(r_order.last_updated_by), sysdate, sysdate, ks_global.EMPLOYEE_ID_NUMBER(r_order.last_updated_by)); end if; END LOOP; --Add by Carol 20220311 SO Cust PO Line Number拋入Shipping Transactions的reference_line_number _ITPR2201006 update wsh_delivery_details b set b.reference_line_number = (select CUSTOMER_LINE_NUMBER from oe_order_lines_all where line_id = b.source_line_id) where 1=1 and b.customer_id = 1736 --X01 -- and b.reference_line_number is null --Modify by Carol 20221213 取消已有Cust PO line Number，不更新條件。_Mantis AR#0000688 and substr(b.cust_po_number,1,2) <> 'K-' -- and b.creation_date >= sysdate - 2; --to_date('20210401','yyyymmdd'); and b.source_line_id in (select line_id from oe_order_lines_all where last_update_date >= sysdate -1) --Modify by Carol 20230109_X01轉正式訂單後SO輸入cust PO line number_Mantis AR#0000688 and (b.reference_line_number <> (select CUSTOMER_LINE_NUMBER from oe_order_lines_all where line_id = b.source_line_id) or b.reference_line_number is null); --Modify by Carol 20230109_排除cust PO line number已同步的資料_Mantis AR#0000688 END; END ksom_order_import_r12_pkg; /

GET_DATA_FM_DB

FUNCTION GET_DATA_FM_DB RETURN BOOLEAN is x_blob blob; x_clob clob; x_line_data varchar2(3000); x_line_total_num number; x_end_char varchar2(30):=chr('10'); --分行符號 x_separator varchar2(10) :=chr('9'); -- TAB x_start_line_num number:=2; --excel上開始的行數 x_substitute_count number; x_name_in varchar2(100); --x_item_count NUMBER; --x_lot_number varchar2(30); --x_revision varchar2(10); --x_serial_nmumber varchar2(30); --x_subinventory_name varchar2(30); --x_locator_count number; --x_inventory_item_id number; --X_ADJ_QTY number; --v-_locator_count number; v_item_count number; v_category_code varchar2(20); v_count number; v_count_routing number; BEGIN go_block('IMPORT_DATA'); first_record; APPS.BlobToClob (:control.file_id); --Modify by Carol_Her 20140122 Oracle 11g不支援D2K使用clob，改以Function取得匯入資料。 /* begin select FILE_DATA into x_clob from KSMM.KSMM_UPLOAD_DATA_GT where file_id=:control.file_id; exception when others then fnd_message.debug('找不到CLOB檔'); RAISE FORM_TRIGGER_FAILURE; END; */ begin --Modify by Carol_Her 20140122 Oracle 11g不支援D2K使用clob，改以Function取得匯入資料。 --Select get_clob_line_num (x_clob,x_end_char),fl.FILE_NAME Select get_clob_line_num(get_clob_function(:control.file_id),x_end_char),fl.FILE_NAME into x_line_total_num,:control.file_name From fnd_lobs fl Where fl.file_id =:control.file_id ; exception when others then :control.file_name:=''; end; --從第x_start_line_num行開始取資料 for i in x_start_line_num .. x_line_total_num loop --取出資料，將值指定給x_line_data begin --Modify by Carol_Her 20140122 Oracle 11g不支援D2K使用clob，改以Function取得匯入資料。 --select get_clob_line_data(x_clob,i,x_end_char) select get_clob_line_data(get_clob_function(:control.file_id),i,x_end_char) into x_line_data from dual; exception when others then fnd_message.debug(SQLERRM); end ; :import_data.line_num:=i-(x_start_line_num-1); -- read data start --x_name_in:='assembly_item'; --copy(get_column_data(x_line_data,1,x_separator), ':import_data.'||x_name_in); :import_data.year:=get_column_data(x_line_data,1,x_separator); :import_data.seq_no:=get_column_data(x_line_data,2,x_separator); :import_data.prod_type:=get_column_data(x_line_data,3,x_separator); :import_data.item_h:=get_column_data(x_line_data,4,x_separator); :import_data.q1_Yield:=get_column_data(x_line_data,5,x_separator); :import_data.q2_Yield:=get_column_data(x_line_data,6,x_separator); :import_data.q3_Yield:=get_column_data(x_line_data,7,x_separator); :import_data.q4_Yield:=get_column_data(x_line_data,8,x_separator); /*:import_data.work_time:=nvl(round(get_column_data(x_line_data,7,x_separator),2),0); :import_data.depreciation:=nvl(round(get_column_data(x_line_data,8,x_separator),2),0); :import_data.material:=nvl(round(get_column_data(x_line_data,9,x_separator),2),0); :import_data.make_expen:=nvl(round(get_column_data(x_line_data,10,x_separator),2),0); :import_data.bom:=nvl(round(get_column_data(x_line_data,11,x_separator),2),0); :import_data.top_item_no:=get_column_data(x_line_data,12,x_separator); :import_data.top_revision:=get_column_data(x_line_data,13,x_separator); */ -- read data end --如果到了最後一行，將資料刪除 if i = x_line_total_num then delete fnd_lobs fl Where fl.file_id =:control.file_id ; forms_ddl('COMMIT'); else next_record; end if; end loop; return (true); exception when others then -- fnd_message.debug('異常停止'); delete fnd_lobs fl Where fl.file_id =:control.file_id ; forms_ddl('COMMIT'); return (true); END;

upload csv to from - find file

--WEB.SHOW_DOCUMENT('http://XXX.html', '_blank'); Declare access_id Number; button_choice Integer; l_file_id Varchar2(100); l_gfm_id Integer; l_server_url VARCHAR2(255); l_url VARCHAR2(255); import boolean ; Begin --fnd_message.debug(:parameter.org_id); :control.file_id:=''; :control.ERROR_MESSAGE:=''; :control.file_name:=''; access_id := fnd_gfm.authorize(Null); fnd_profile.get('APPS_WEB_AGENT',l_server_url); l_url := RTRIM(l_server_url, '/') || '/fnd_file_upload.displayGFMform?access_id=' || to_char(access_id) || chr(38) || 'l_server_url=' || l_server_url; IF (l_url IS NULL) THEN RAISE FORM_TRIGGER_FAILURE; RETURN; END IF; fnd_utilities.open_url(l_url); fnd_message.set_name('FND', 'ATCHMT-FILE-UPLOAD-COMPLETE'); button_choice := fnd_message.question(button1 => 'YES' ,button2 => Null ,button3 => 'NO' ,default_btn => 1 ,cancel_btn => 3 ,icon => 'question'); If (button_choice = 3) Then Null; Elsif (button_choice = 1) Then -- clear_block(no_commit); --先將control block 清空 l_gfm_id := fnd_gfm.get_file_id(access_id); :control.file_id:=l_gfm_id; if :control.file_id>0 then -- import_action start go_block('IMPORT_DATA'); clear_block(no_commit); /* select INV.KSINV_PHYSICAL_TAG_DATA_S.NEXTVAL INTO :CONTROL.HEADER_ID FROM DUAL;*/ import :=get_data_fm_db; -- import_action end else fnd_message.debug('沒找到對應檔案'); end if; End If; End;