System version:
rdbms:9.2.0.6.0
Oracle Application Products: 11.5.10.2
Due to the expiration of the finished product encoding, the system prompts as follows:
There are two scenarios for this tip:
1. Check that the sales order line status for the finished product has been "pending shipment". Sweat........... More than three years of sales orders have not been shipped has not been canceled. No wonder it can't fail. Through the following SQL
Select Oeh. Order_number, ott.name, oeh. Creation_date, cust. Customer_name, oel. Line_number | | '.' || Oel. Shipment_number line, msi. SEGMENT1, msi. DESCRIPTION, oel. Ordered_quantity, oel. line_id from Oe_order_headers_all Oeh, oe_order_lines_all OEL, &nbs P mtl_system_items_b MSI, ar_customers &NB Sp CUST oe_transaction_types_v OTT where Oeh. header_id = Oel. header_id and Oel. ship_from_org_id = msi.organization_id and oel. inventory_item_id = MSI. inventory_item_id and Oeh. sold_to_org_id = CUST. customer_id and Oeh. order_type_id = OTT. transaction_type_id &NBsp and Oeh. Ordered_date < To_date (' 2018-01-01 ', ' yyyy-mm-dd ') and Oeh. Flow_status_code = ' Booked ' and Oel. Flow_status_code = ' awaiting_shipping ' and not Exists (Select 1 to Wsh_d Elivery_details WDD where WDD. source_line_id = Oel. line_id and WDD. source_header_id = Oel. header_id and WDD. Released_status = ' Y ') order by Oeh. Creation_date, Oeh. sold_to_org_id;
2. Although the sales order lines that are encoded in the finished product are all closed, they are also prompted because the sales header process issues cause the sales order header status to not be closed.
Inspection found that before 2018 there are 4K multiple lines "Waiting to ship" order lines, so many sales order lines are not canceled, now manual cancellation is not realistic AH. Only use the system's API to cancel. View the system standard form source code, the main calling API package such as: Oe_oe_form_cancel_line. Process_cancel_quantity
Declare l_return_status Varchar2 (): = null;
L_msg_count number: = 0;
L_msg_data VARCHAR2: = null;
L_mc_err_handling_flag number: = Null; ---cancellation type: F is completely canceled, p represents partial cancellation.
When partially canceled, the cancellation quantity cannot be empty L_cancel_type varchar2 (2): = ' F ';
--l_cancelation_reason_code varchar2 (m): = name_in (' Audit_qty.reasons_code '); L_linecount number: = 1; --Cancel the number of records--Cancel order line Oe_order_lines_all.
line_id separated by commas, line_id can be from different sales orders l_linelist varchar2 (32000): = ' 320564,12432324 ';
--Cancel Quantity l_cancel_quantity number;
--Cancellation Memo l_comments VARCHAR2 (200): = ' 20180414 bulk cancel operation ';
--Take the reason code l_reasonscode number: = 1;
L_error_count number;
L_userid number: = 1110;
L_respid number: = 50247;
L_respapplid number: = 660;
L_msg_index_out number (10); Begin--Initialize fnd_global.apps_initialize (user_id => L_userid, resp_id => 50 247, resp_appl_id => l_respapplid);
--Invoke API Oe_oe_form_cancel_line.
Process_cancel_quantity (p_num_of_records => l_linecount,--oe_cancel_lines.g_line_count,
P_record_ids => l_linelist,--oe_cancel_lines.g_line_list,
P_cancel_to_quantity => l_cancel_quantity,
P_cancellation_comments => l_comments,--name_in (' audit_qty.comments '),
P_reason_code => L_reasonscode,--l_cancelation_reason_code, P_cancellation_type => L_cancel_type, P_mc_err_handling_flag
=> L_mc_err_handling_flag, X_msg_count => L_msg_count,
X_msg_data => L_msg_data, X_return_status => L_return_status,
X_error_count => L_error_count); If L_return_status <> ' S ' then 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 | | ',' || ' Message index is: ' | |
L_msg_index_out);
End LOOP;
Rollback;
else commit;
End If; End;