Oracle Order Management: Updating Order Line -Technical Over View

Some pointers on Order Cancellation

Query to find out the cancelled orders

/* Formatted on 21-Aug-15 3:11:51 PM (QP5 v5.115.810.9015) */
SELECT   flt.meaning,
         oer.reason_type,
         oer.reason_code,
         flv.meaning,
         oer.comments,
         oer.creation_date,
         oer.reason_id,
         oer.entity_id,
         oer.entity_code,
         oer.version_number,
         oer.header_id
  FROM   oe_reasons oer, fnd_lookup_values flv, fnd_lookup_types_vl flt
 WHERE       flv.LANGUAGE = USERENV ('LANG')
         AND flv.view_application_id = 660
         AND flt.view_application_id = 660
         AND flt.security_group_id =
               fnd_global.lookup_security_group (FLT.LOOKUP_TYPE,
                                                 FLT.VIEW_APPLICATION_ID)
         AND FLV.SECURITY_GROUP_ID =
               fnd_global.lookup_security_group (flv.lookup_type,
                                                 flv.view_application_id)
         AND flv.lookup_type = flt.lookup_type
         AND oer.reason_type = flv.lookup_type
         AND oer.reason_code = flv.lookup_code;

Cancel reason appears on the sales order : Actions : Additional Order Information : tab 'Quantity change history'.

Information displayed there are stored in tables/fields I listed above.

If you can not see your cancel reason here, you probably have an issue with your sales order screen

/* Formatted on 21-Aug-15 2:56:22 PM (QP5 v5.115.810.9015) */ DECLARE    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);    p_header_rec oe_order_pub.header_rec_type          := oe_order_pub.g_miss_header_rec ;    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 ;    x_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_msg_index_out            NUMBER (10);    l_line_tbl_index           NUMBER;    p_order_number             NUMBER;    l_header_id                NUMBER;    l_line_id                  NUMBER; BEGIN    DBMS_OUTPUT.ENABLE (1000000);    fnd_global.Apps_initialize (1318, 21623, 660);    -- pass in user_id, responsibility_id, and application_id    --tables used for user_id ->fund_user,    --USE BELOW CODE    /*SELECT   user_id, responsibility_application_id, responsibility_id   FROM   fnd_user_resp_groups_all  WHERE   responsibility_application_id =             (SELECT   application_id                FROM   fnd_allications               WHERE   application_short_name = 'ONT')*/    oe_msg_pub.initialize;    oe_debug_pub.initialize;    mo_global.Init ('ONT');                                 -- Required for R12    mo_global.Set_org_context (204, NULL, 'ONT');    fnd_global.Set_nls_context ('AMERICAN');    mo_global.Set_policy_context ('S', 204);                -- Required for R12    --TO GET HEADER_ID,LINE_ID    SELECT   ooha.header_id, oola.line_id      INTO   l_header_id, l_line_id      FROM   oe_order_headers_all ooha, oe_order_lines_all oola     WHERE   ooha.header_id = oola.header_id             AND ooha.order_number = p_order_number;    --X_DEBUG_FILE := OE_DEBUG_PUB.Set_Debug_Mode('FILE');    --oe_debug_pub.SetDebugLevel(5);    --dbms_output.put_line('START OF NEW DEBUG');    --This is to update a line to an existing order    l_line_tbl_index := 1;    -- Changed attributes    l_line_tbl (l_line_tbl_index) := OE_ORDER_PUB.G_MISS_LINE_REC;    --l_line_tbl(l_line_tbl_index).invoice_to_org_id := 322;--org_organization_definitions    l_line_tbl (l_line_tbl_index).ordered_quantity := 2;    -- Primary key of the entity i.e. the order line    l_line_tbl (l_line_tbl_index).line_id := l_line_id;    l_line_tbl (l_line_tbl_index).change_reason := 'Not provided';    -- Indicates to process order that this is an update operation    l_line_tbl (l_line_tbl_index).operation := OE_GLOBALS.G_OPR_UPDATE;    -- CALL TO PROCESS ORDER    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                 => x_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);    -- Retrieve messages    DBMS_OUTPUT.Put_line (       'Line Id: ' || x_line_tbl (l_line_tbl_index).line_id    );    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);    END LOOP;    -- Check the return status    IF l_return_status = fnd_api.g_ret_sts_success    THEN       DBMS_OUTPUT.put_line ('Line Quantity Update Sucessful');    ELSE       DBMS_OUTPUT.put_line ('Line Quantity update Failed');    END IF; END; / COMMIT;