, ,

API – Allocate and Transact Orders (Transact Move Orders)

create or replace procedure XX_ALOCATE_TRANSACT_MOVE_ORDER (P_REQUEST_NUMBER VARCHAR2 ,P_ORGANIZATION_ID NUMBER,P_TRANSACTION_DATE  varchar2)
as
----------------- ALLOCATE MOVE ORDER API REQUIREMENTS -------------------------
l_api_version NUMBER         := 1.0;
l_init_msg_list VARCHAR2 (2) := fnd_api.g_true;
l_return_values VARCHAR2 (2) := fnd_api.g_false;
l_commit VARCHAR2 (2)        := fnd_api.g_false;
x_return_status VARCHAR2 (2);
x_msg_count NUMBER           := 0;
x_msg_data VARCHAR2 (255);
l_user_id NUMBER ;
l_resp_id NUMBER ;
l_appl_id NUMBER ;
l_row_cnt NUMBER             := 1;
l_trohdr_rec            inv_move_order_pub.trohdr_rec_type;
l_trohdr_val_rec        inv_move_order_pub.trohdr_val_rec_type;
x_trohdr_rec            inv_move_order_pub.trohdr_rec_type;
x_trohdr_val_rec        inv_move_order_pub.trohdr_val_rec_type;
l_validation_flag VARCHAR2 (2) := inv_move_order_pub.g_validation_yes;
l_trolin_tbl            inv_move_order_pub.trolin_tbl_type;
l_trolin_val_tbl        inv_move_order_pub.trolin_val_tbl_type;
x_trolin_tbl            inv_move_order_pub.trolin_tbl_type;
x_trolin_val_tbl        inv_move_order_pub.trolin_val_tbl_type;
x_number_of_rows        NUMBER ;
x_transfer_to_location  NUMBER ;
x_expiration_date       DATE;
x_transaction_temp_id   NUMBER ;
----------------- TRANSACT MOVE ORDER API REQUIREMENTS -------------------------
a_l_api_version NUMBER := 1.0;
a_l_init_msg_list VARCHAR2 (2) := fnd_api.g_true;
a_l_commit VARCHAR2 (2) := fnd_api.g_false;
a_x_return_status VARCHAR2 (2);
a_x_msg_count NUMBER := 0;
a_x_msg_data VARCHAR2 (255);
a_l_move_order_type NUMBER := 3;
a_l_transaction_mode NUMBER := 1;
a_l_trolin_tbl inv_move_order_pub.trolin_tbl_type;
a_l_mold_tbl inv_mo_line_detail_util.g_mmtt_tbl_type;
a_x_mmtt_tbl inv_mo_line_detail_util.g_mmtt_tbl_type;
a_x_trolin_tbl inv_move_order_pub.trolin_tbl_type;
a_l_transaction_date DATE := SYSDATE;
a_l_user_id NUMBER;
a_l_resp_id NUMBER;
a_l_appl_id NUMBER;
v_transaction_date date; 
CURSOR c_mo_details IS
SELECT mtrh.header_id, 
       mtrh.request_number, 
       mtrh.move_order_type,
       mtrh.organization_id,
       mtrl.line_id,
       mtrl.line_number,
       mtrl.inventory_item_id,
       mtrl.lot_number,
       mtrl.quantity,
       revision,mtrl.from_locator_id,
       (select distinct operating_unit from org_organization_definitions
        where organization_id = mtrh.organization_id) org_id
FROM   mtl_txn_request_headers mtrh, mtl_txn_request_lines mtrl
WHERE  mtrh.header_id = mtrl.header_id
AND    (mtrh.request_number  = P_REQUEST_NUMBER OR P_REQUEST_NUMBER IS NULL )--'8007'
AND    (mtrh.organization_id = P_ORGANIZATION_ID OR P_ORGANIZATION_ID IS NULL ); -- 755;
BEGIN
        FND_GLOBAL.APPS_INITIALIZE(1130, 50632, 401);
FOR i IN c_mo_details
    LOOP
            mo_global.set_policy_context ('S', i.org_id);
            inv_globals.set_org_id (NVL(i.organization_id,P_ORGANIZATION_ID));
           -- mo_global.init ('INV');

            SELECT COUNT (*)
            INTO   x_number_of_rows
            FROM   mtl_txn_request_lines
            WHERE  header_id = i.header_id;

            DBMS_OUTPUT.put_line ('Calling INV_REPLENISH_DETAIL_PUB to Allocate MO');
            -- Allocate each line of the Move Order 

            inv_replenish_detail_pub.line_details_pub(
                                                    p_line_id => i.line_id,
                                                    x_number_of_rows => x_number_of_rows,
                                                    x_detailed_qty => i.quantity,
                                                    x_return_status => x_return_status,
                                                    x_msg_count => x_msg_count,
                                                    x_msg_data => x_msg_data,
                                                    x_revision => i.revision,
                                                    x_locator_id => i.from_locator_id,
                                                    x_transfer_to_location => x_transfer_to_location,
                                                    x_lot_number => i.lot_number,
                                                    x_expiration_date => x_expiration_date,
                                                    x_transaction_temp_id => x_transaction_temp_id,
                                                    p_transaction_header_id => NULL,
                                                    p_transaction_mode => NULL,
                                                    p_move_order_type => i.move_order_type,
                                                    p_serial_flag => fnd_api.g_false,
                                                    p_plan_tasks => FALSE,
                                                    p_auto_pick_confirm => FALSE,
                                                    p_commit => FALSE
                                                  );

                    DBMS_OUTPUT.put_line('==========================================================');
                    DBMS_OUTPUT.put_line (x_return_status);
                    DBMS_OUTPUT.put_line (x_msg_data);
                    DBMS_OUTPUT.put_line (x_msg_count);

            IF (x_return_status <> fnd_api.g_ret_sts_success)  THEN
            
                   DBMS_OUTPUT.put_line (x_msg_data);
            
            END IF;

            IF (x_return_status = fnd_api.g_ret_sts_success)  THEN

                    DBMS_OUTPUT.put_line ('ALLOCATE API SUCCESSFUL WITH - Trx temp ID: ');
                    DBMS_OUTPUT.put_line (x_transaction_temp_id);
                    
            END IF;
            
            DBMS_OUTPUT.put_line('==========================================================');
            IF x_transaction_temp_id IS NOT NULL OR x_transaction_temp_id <> 0  THEN
                mo_global.set_policy_context ('S', i.org_id);
                inv_globals.set_org_id (i.organization_id);
                mo_global.init ('INV');
                a_l_trolin_tbl (1).line_id := i.line_id; 
                select to_date (P_TRANSACTION_DATE)   --('31-AUG-2010') 
                into   v_transaction_date
                from   dual;  

                FND_GLOBAL.APPS_INITIALIZE(1130, 50632, 401);
 
                inv_pick_wave_pick_confirm_pub.pick_confirm
                                                (p_api_version_number => a_l_api_version,
                                                p_init_msg_list => a_l_init_msg_list,
                                                p_commit => a_l_commit,
                                                x_return_status => a_x_return_status,
                                                x_msg_count => a_x_msg_count,
                                                x_msg_data => a_x_msg_data,
                                                p_move_order_type => i.move_order_type,
                                                p_transaction_mode => a_l_transaction_mode,
                                                p_trolin_tbl => a_l_trolin_tbl,
                                                p_mold_tbl => a_l_mold_tbl,
                                                x_mmtt_tbl => a_x_mmtt_tbl,
                                                x_trolin_tbl => a_x_trolin_tbl,
                                                p_transaction_date => v_transaction_date -- l_transaction_date
                                                );

                DBMS_OUTPUT.put_line('=======================================================');
                DBMS_OUTPUT.put_line ('Return Status - '||a_x_return_status||' '||sqlerrm);
                DBMS_OUTPUT.put_line ('Return Message - '||a_x_msg_data);
                DBMS_OUTPUT.put_line (a_x_msg_count);

                IF (a_x_return_status <> fnd_api.g_ret_sts_success) THEN
                
                    DBMS_OUTPUT.put_line (a_x_msg_data);
                
                END IF;

                 IF (a_x_return_status = fnd_api.g_ret_sts_success)  THEN

                    DBMS_OUTPUT.put_line ('TRANSACT API SUCCESSFULLY PROCESSED');
                    
                END IF;
                DBMS_OUTPUT.put_line('=======================================================');
        END IF;
    END LOOP;

    EXCEPTION
        WHEN OTHERS THEN
            DBMS_OUTPUT.put_line ('OUTER Exception Occured :');
            DBMS_OUTPUT.put_line (SQLCODE||':'||SQLERRM);
            DBMS_OUTPUT.put_line ('=======================================================');
    END;
END;

begin
XX_ALOCATE_TRANSACT_MOVE_ORDER('',763,'31-AUG-2010');
end;
/by
,

API to cancel AP Invoice

AP_CANCEL_PKG.IS_INVOICE_CANCELLABLE:
Is_Invoice_Cancellable is a Function in the AP_CANCEL_PKG package that checks that an Invoice is cancellable or not when an Invoice Cancellation process starts.
It follows the following steps and returns a Boolean value depending on the result.

  • If invoice contains distribution that does not have open GL period return FALSE.
  • If invoice has an effective payment, return FALSE.
  • If invoice is selected for payment, return FALSE.
  • If invoice is already cancelled, return FALSE.
  • If invoice is credited invoice, return FALSE.
  • If invoices have been applied against this invoice, return FALSE.
  • If invoice is matched to Finally Closed PO’s, return FALSE.
  • If project related invoices have pending adjustments, return FALSE.
  • If cancelling will cause qty_billed or amount_billed to less than 0, return FALSE.
  • If none of above, invoice is cancellable return True.

Here is a small procedure to check if an Invoice is cancellable or not.
 create or replace procedure XX_INV_CANCELLABLE (p_inv_id IN NUMBER)
is
v_boolean               BOOLEAN;
v_error_code            VARCHAR2(100);
v_debug_info            VARCHAR2(1000);
begin
v_boolean :=AP_CANCEL_PKG.IS_INVOICE_CANCELLABLE(
                P_invoice_id       => p_inv_id,
                P_error_code       => v_error_code,
                P_debug_info       => v_debug_info,
                P_calling_sequence => NULL);
IF v_boolean=TRUE
THEN
DBMS_OUTPUT.put_line (‘Invoice ‘||p_inv_id|| ‘ is cancellable’ );
ELSE
DBMS_OUTPUT.put_line (‘Invoice ‘||p_inv_id|| ‘ is not cancellable :’|| v_error_code );
END IF;
End XX_INV_CANCELLABLE;

Execute XX_INV_CANCELLABLE(12960);


AP_CANCEL_PKG.AP_CANCEL_SINGLE_INVOICE:
AP_CANCEL_SINGLE_INVOICE is a Function in the AP_CANCEL_PKG package that cancels one invoice by executing the following sequence of steps, returning TRUE if successful and FALSE otherwise.
1] Check if the invoice is cancellable. if yes, proceed otherwise return false.
2] If invoice has tax withheld, undo withholding.
3] Clear out all payment schedules.
4] Cancel all the non-discard lines.

a. reverse matching
b. fetch the maximum distribution line number
c. Set encumbered flags to ‘N’
d. Accounting event generation
e. reverse the distributions
f. update Line level Cancelled information

5] Zero out the Invoice.
6] Run AutoApproval for this invoice.
7] Check posting holds remain on this cancelled invoice.

a. if NOT exist – complete the cancellation by updating header level information set return value to TRUE.
b. if exist – no update, set the return values to FALSE, NO DATA rollback.

8] Commit the Data.
9] Populate the out parameters.
Here is a small procedure to cancel a single invoice.

create or replace procedure XX_INV_CANCEL(
                            P_xx_invoice_id IN NUMBER,
                            P_xx_last_updated_by IN  NUMBER,
                            P_xx_last_update_login IN  NUMBER,
                            P_xx_accounting_date IN  DATE)
is
v_boolean               BOOLEAN;
v_message_name          VARCHAR2(1000);
v_invoice_amount        NUMBER;
v_base_amount           NUMBER;
v_temp_cancelled_amount NUMBER;
v_cancelled_by          VARCHAR2(1000);
v_cancelled_amount      NUMBER;
v_cancelled_date        DATE;
v_last_update_date      DATE;
v_orig_prepay_amt       NUMBER;
v_pay_cur_inv_amt       NUMBER;
v_token                 VARCHAR2(100);
begin
v_boolean := AP_CANCEL_PKG.AP_CANCEL_SINGLE_INVOICE
            (p_invoice_id                 => P_xx_invoice_id,
             p_last_updated_by            => P_xx_last_updated_by,
             p_last_update_login          => P_xx_last_update_login,
             p_accounting_date            => P_xx_accounting_date,
             p_message_name               => v_message_name,
             p_invoice_amount             => v_invoice_amount,
             p_base_amount                => v_base_amount,
             p_temp_cancelled_amount      => v_temp_cancelled_amount,
             p_cancelled_by               => v_cancelled_by,
             p_cancelled_amount           => v_cancelled_amount,
             p_cancelled_date             => v_cancelled_date,
             p_last_update_date           => v_last_update_date,
             p_original_prepayment_amount => v_orig_prepay_amt,
             p_pay_curr_invoice_amount    => v_pay_cur_inv_amt,
             P_Token                      => v_token,
             p_calling_sequence           => NULL
             );

IF v_boolean
THEN
DBMS_OUTPUT.put_line (‘Successfully Cancelled the Invoice’ );
COMMIT;
ELSE
DBMS_OUTPUT.put_line (‘Failed to Cancel the Invoice’ );
ROLLBACK;
END IF;

end XX_INV_CANCEL;

Execute XX_INV_CANCEL(120573,2325,-1,SYSDATE);
 

/by