, , , , , ,

Receipt API in Oracle Receivables

Autolock box provides a functionality to create receipts using interface method. Recent versions of oracle applications, especially 11.5 onwards, Receipt API is getting used as they are flexible to the user’s need, and customization across all kinds of interfaces, loading.
Here are the receipt APIs used in 11i applications:
ar_receipt_api_pub.Create_cash
ar_receipt_api_pub.Apply
ar_receipt_api_pub.Unapply
ar_receipt_api_pub.Reverse
ar_receipt_api_pub.Apply_on_account
ar_receipt_api_pub.Unapply_on_account
ar_receipt_api_pub.Activity_application
ar_receipt_api_pub.Activity_unapplication
ar_receipt_api_pub.Apply_other_account
ar_receipt_api_pub.Unapply_other_account
ar_receipt_api_pub.create_misc
ar_receipt_api_pub.set_profile_for_testing
ar_receipt_api_pub.Apply_Open_Receipt
ar_receipt_api_pub.Unapply_Open_Receipt

Receipt On Account & Unapplied – SQL Query

A cash receipt is an applied receipt if it is associated with a customer number and an open invoice(s).
An unapplied receipt is a cash receipt that can be applied to a customer account if it is associated with a customer number but not associated with an invoice (that is, there is no invoice for the sale or the invoice number is unknown).
An on-account receipt (cash-in-advance) is:
* recorded to the customer account
* applied against the invoice when the invoice is generated
An unidentified receipt is from an unknown source.
An Application Advice Form is required for any receipt labeled as unidentified.
Here is the simple query to find the On-Account and UnApplied amount for a customer.
 

SELECT   NVL (SUM (DECODE (
                         ara.STATUS,
                         ‘ACC’, -amount_applied,
                         0
                       )), 0),
                   NVL (SUM (DECODE (
                         ara.STATUS,
                         ‘UNAPP’, -amount_applied,
                         0
                       )), 0)
              FROM ar_receivable_applications_all ara,
                   ar_cash_receipts_all acr
             WHERE ara.cash_receipt_id = acr.cash_receipt_id
               AND acr.customer_site_use_id = ‘&&site_use_id’
               AND ARA.STATUS IN ( ‘ACC’, ‘UNAPP’ )                
               AND ara.confirmed_flag IS NULL
          GROUP BY acr.currency_code,
                   acr.customer_site_use_id;


Receipt Status – Oracle Applications

A receipt can have one of the following statuses:
Approved: This receipt has been approved for automatic receipt creation. This status is only valid for automatic receipts.
Confirmed: The customer has approved the application of this receipt and their account balances have been updated within Receivables. This status is only valid for automatic receipts.
Remitted: This receipt has been remitted. This status is valid for both automatic and manually entered receipts.
Cleared: The payment of this receipt was transferred to your bank account and the bank statement has been reconciled within Receivables. This status is valid for both automatic and manually entered receipts.
Reversed: This receipt has been reversed. You can reverse a receipt when your customer stops payment on a receipt, if a receipt comes from an account with non-sufficient funds or if you want to re-enter and reapply it in Receivables. You can reverse cash receipts and miscellaneous transactions.

/by
, , , , ,

How to delete a DFF Context

Sometime I’m quite annoyed by the typo mistake when creating a DFF context. The DFF segment screen doesn’t allow deletion of context. Fortunately, Oracle has internal API to do such thing. Following is a sample.
–*******************************************
–* Delete a descriptive flexfield
–*******************************************
SET ECHO OFF
SET FEEDBACK OFF
SET SERVEROUTPUT ON SIZE 1000000
DECLARE
l_application_id                NUMBER := 0;
l_descriptive_flexfield_name    VARCHAR2(100) :=  ‘FND_COMMON_LOOKUPS’ ;
l_descriptive_flex_context_cod  VARCHAR2(100) :=  ‘XFND_CLWW_PURGE_FOLDER’;
BEGIN
–FND_DESCRIPTIVE_FLEXS_PKG –this package is for DFF
–FND_DESCR_FLEX_CONTEXTS_PKG –this package is for DFF Context
–FND_DESCR_FLEX_COL_USAGE_PKG –this package is for DFF Column useage
–When creating a new DFF Context, it will check the DFF Column usage if the context is already used.
–so when deleting a DFF Context, both the context and column usage should be deleted.
FOR c IN (SELECT application_column_name
FROM fnd_descr_flex_column_usages
WHERE application_id = l_application_id
AND descriptive_flexfield_name = l_descriptive_flexfield_name
AND descriptive_flex_context_code = l_descriptive_flex_context_cod)
LOOP

fnd_descr_flex_col_usage_pkg.delete_row(
x_application_id                => l_application_id
,x_descriptive_flexfield_name    => l_descriptive_flexfield_name
,x_descriptive_flex_context_cod  => l_descriptive_flex_context_cod
,x_application_column_name       => c.application_column_name
);
END LOOP;
fnd_descr_flex_contexts_pkg.delete_row(
x_application_id                => l_application_id,
,x_descriptive_flexfield_name    => l_descriptive_flexfield_name
,x_descriptive_flex_context_cod  => l_descriptive_flex_context_cod
);

–commit;
end;

/by
, ,

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
, , ,

Submit the Concurrent Program from Backend

The following is the Sample Code to Submit the Concurrent Program from the backend.

Note:- This is the Concurrent Program, not the Request Set. To Submit the Request Set from the backend, We have different API.

I have already document, for submitting the request set from the backend in the Following URL.

http://oracleerpappsguide.blogspot.com/2011/09/registering-executable-concurrent.html

DECLARE
l_success NUMBER;
BEGIN
BEGIN

fnd_global.apps_initialize( user_id => 2572694, resp_id => 50407, resp_appl_id => 20003);

— If you are directly running from the database using the TOAD, SQL-NAVIGATOR or –SQL*PLUS etc. Then you need to Initialize the Apps. In this case use the above API to –Initialize the APPS. If you are using same code in some procedure and running directly
–from application then you don’t need to initalize.
–Then you can comment the above API.

l_success :=
fnd_request.submit_request
(‘XXAPP’, — Application Short name of the Concurrent Program.
‘XXPRO_RPT’, — Program Short Name.
‘Program For testing the backend Report’, — Description of the Program.
SYSDATE, — Submitted date. Always give the SYSDATE.
FALSE, — Always give the FLASE.
‘1234’ — Passing the Value to the First Parameter of the report.
);
COMMIT;

— Note:- In the above request Run, I have created the Report, which has one parameter.

IF l_success = 0
THEN
— fnd_file.put_line (fnd_file.LOG, ‘Request submission For this store FAILED’ );
DBMS_OUTPUT.PUT_LINE( ‘Request submission For this store FAILED’ );
ELSE
— fnd_file.put_line (fnd_file.LOG, ‘Request submission for this store SUCCESSFUL’);
DBMS_OUTPUT.PUT_LINE( ‘Request submission For this store SUCCESSFUL’ );
END IF;

—Note:- If you are running directly from database, use DBMS API to display. If you are 
— Running directly from Application, then Use the fnd_file API to write the message
— in the log file.

END;

/by