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; /