API Script to Cancel AP Invoices (Cancel AP Invoice API )

API Script to Cancel AP Invoices (Cancel AP Invoice API )

/* Formatted on 2017/07/08 18:15 (Formatter Plus v4.8.0) */

CREATE OR REPLACE PROCEDURE cancel_invoices (ip_operating_unit IN VARCHAR2)

AS

 l_resp_id NUMBER;

 l_appl_id NUMBER;

 l_user_id NUMBER := apps.fnd_global.user_id;

 l_org_id NUMBER := apps.fnd_global.org_id;

 l_message_name VARCHAR2 (1000);

 l_invoice_amount NUMBER;

 l_base_amount NUMBER;

 l_tax_amount NUMBER;

 l_temp_cancelled_amount NUMBER;

 l_cancelled_by VARCHAR2 (1000);

 l_cancelled_amount NUMBER;

 l_cancelled_date DATE;

 l_last_update_date DATE;

 l_original_prepayment_amount NUMBER;

 l_pay_curr_invoice_amount NUMBER;

 l_token VARCHAR2 (100);

 l_boolean BOOLEAN;

 err_msg VARCHAR2 (2000);

 CURSOR invoice_cur

 IS

 SELECT aia.invoice_id, aia.last_updated_by, aia.last_update_login,

 aia.gl_date, aia.invoice_num

 FROM xx_ap_invoices_conv_stg a,

 ap_invoices_all aia,

 ap_invoice_lines_all aila

 WHERE a.ls_inv_num = aia.invoice_num

 AND a.ls_org_id = aia.org_id

 AND aia.invoice_id = aila.invoice_id

 AND aia.org_id = aila.org_id

 AND aia.payment_status_flag = 'N'

 AND NVL (aila.cancelled_flag, 'N') <> 'Y';

--- AND aia.invoice_num = '65431';

BEGIN

 BEGIN

 SELECT DISTINCT fr.responsibility_id, frx.application_id

 INTO l_resp_id, l_appl_id

 FROM apps.fnd_responsibility frx,

 apps.fnd_responsibility_tl fr

 WHERE fr.responsibility_id = frx.responsibility_id

 AND UPPER (fr.responsibility_name) LIKE

 UPPER (DECODE (ip_operating_unit,

 'OU USA MA', 'Payables Manager',

 'OU USA WI', 'OU USA WI_Payables Manager',

 'OU Austria', 'OU AUSTRIA_Payables Manager',

 'OU China', 'OU CHINA_Payables Manager'

 )

 );

 DBMS_OUTPUT.put_line ('l_resp_id => ' || l_resp_id);

 DBMS_OUTPUT.put_line ('l_appl_id => ' || l_appl_id);

 EXCEPTION

 WHEN OTHERS

 THEN

 err_msg :=

 'Error Occured while Deriving responsibility id' || SQLERRM;

 apps.fnd_file.put_line

 (apps.fnd_file.output,

 'Error Occured while Deriving responsibility id'

 );

 END;

 mo_global.set_policy_context ('S', l_org_id);

 apps.fnd_global.apps_initialize (l_user_id, l_resp_id, l_appl_id);

 FOR l_inv_rec IN invoice_cur ----(l_org_id)

 LOOP

 DBMS_OUTPUT.put_line

 ( 'Calling API ap_cancel_pkg.ap_cancel_single_invoice to Cancel Invoice: '

 || l_inv_rec.invoice_num

 );

 DBMS_OUTPUT.put_line

 ('**************************************************************');

 l_boolean :=

 ap_cancel_pkg.ap_cancel_single_invoice

 (p_invoice_id => l_inv_rec.invoice_id,

 p_last_updated_by => l_inv_rec.last_updated_by,

 p_last_update_login => l_inv_rec.last_update_login,

 p_accounting_date => l_inv_rec.gl_date,

 p_message_name => l_message_name,

 p_invoice_amount => l_invoice_amount,

 p_base_amount => l_base_amount,

 p_temp_cancelled_amount => l_temp_cancelled_amount,

 p_cancelled_by => l_cancelled_by,

 p_cancelled_amount => l_cancelled_amount,

 p_cancelled_date => l_cancelled_date,

 p_last_update_date => l_last_update_date,

 p_original_prepayment_amount => l_original_prepayment_amount,

 p_pay_curr_invoice_amount => l_pay_curr_invoice_amount,

 p_token => l_token,

 p_calling_sequence => NULL

 );

 DBMS_OUTPUT.put_line ('l_message_name => ' || l_message_name);

 DBMS_OUTPUT.put_line ('l_invoice_amount => ' || l_invoice_amount);

 DBMS_OUTPUT.put_line ('l_base_amount => ' || l_base_amount);

 DBMS_OUTPUT.put_line ('l_tax_amount => ' || l_tax_amount);

 DBMS_OUTPUT.put_line ( 'l_temp_cancelled_amount => '

 || l_temp_cancelled_amount

 );

 DBMS_OUTPUT.put_line ('l_cancelled_by => ' || l_cancelled_by);

 DBMS_OUTPUT.put_line ('l_cancelled_amount => ' || l_cancelled_amount);

 DBMS_OUTPUT.put_line ('l_cancelled_date => ' || l_cancelled_date);

 DBMS_OUTPUT.put_line ('P_last_update_date => ' || l_last_update_date);

 DBMS_OUTPUT.put_line ( 'P_original_prepayment_amount => '

 || l_original_prepayment_amount

 );

 DBMS_OUTPUT.put_line ( 'l_pay_curr_invoice_amount => '

 || l_pay_curr_invoice_amount

 );

 IF l_boolean

 THEN

 DBMS_OUTPUT.put_line ( 'Successfully Cancelled the Invoice => '

 || l_inv_rec.invoice_num

 );

 COMMIT;

 ELSE

 DBMS_OUTPUT.put_line ( 'Failed to Cancel the Invoice => '

 || l_inv_rec.invoice_num

 );

 ROLLBACK;

 END IF;

 END LOOP;

END cancel_invoices;  

Lockbox interface

Lockbox interface

Lockbox interface

AutoLockbox lets us automatically process receipts that are sent directly to the bank instead of manually feeding them in Oracle Receivables.

AutoLockbox is a three step process:

1. Import: During this step, Lockbox reads and formats the data from your bank file into interface table AR_PAYMENTS_INTERFACE_ALL using a SQL *Loader script.
2. Validation: The validation program checks data in this interface table for compatibility with Receivables. Once validated, the data is transferred into QuickCash tables (AR_INTERIM_CASH_RECEIPTS_ALL and AR_INTERIM_CASH_RCPT_LINES_ALL).
3. Post QuickCash: This step applies the receipts and updates your customer’s balances.

Pre-Requisites:

• Banks
• Receipt Class
• Payment Method
• Receipt Source
• Lockbox
• Transmission format
• AutoCash Rule sets

Interface tables:

• AR_PAYMENTS_INTERFACE_ALL (Import data from bank file)
• AR_INTERIM_CASH_RECEIPTS_ALL
• AR_INTERIM_CASH_RCPT_LINES_ALL (Validate data in interface table and place in quick cash tables)

Base Tables:

• AR_CASH_RECEIPTS
• AR_RECEIVABLES_APPLICATIONS
• AR_ADJUSTMENTS
• AR_DISTRIBUTIONS_ALL
• AR_PAYMENT_SCHEDULES_ALL

Concurrent program:

• Lockbox

Validations:

• Check for valid record type, transmission record id.
• Validate sum of the payments within the transmission.
• Identify the lockbox number (no given by a bank to identify a lockbox).

Important Columns:

Some important columns that need to be populated in the interface tables:

AR_PAYMENTS_INTERFACE_ALL:

• STATUS
• RECORD_TYPE
• LOCKBOX_NUMBER
• BATCH_NAME
• TRANSIT_ROUTING_NUMBER
• ACCOUNT
• CHECK_NUMBER
• REMITTANCE_AMOUNT
• DEPOSIT_DATE
• ITEM_NUMBER
• CURRENCY_CODE
• DEPOSIT_TIME

GL_daily_rates_interface and its process

For Daily Currency conversion interface can be entered by 2 ways, either

1.)   to GL_INTERFACE table To import multi-currency data (Currency conversion) or

2.)   to Gl_DAILY_RATES_INTERFACE to import multi-currency data (Currency conversion)

 

1.) The following process is explained from GL_INTERFACE table , Other sample codes will get from blog spot GL Interface and Its process

 

Load multi-currency data into the GL_INTERFACE table the same way you load

Regular data.

If you want your General Ledger application to calculate your conversion,

you must enter a value in the

CURRENCY_CODE, CURRENCY_CONVERSION_DATE and USER_CURRENCY_CONVERSION_TYPE

columns of the GL_INTERFACE table.

 

If the conversion type is User, you must also enter a value in the

CURRENCY_CONVERSION_RATE column of the GL_INTERFACE table. Or, you can

directly specify the converted amounts by entering values in the ACCOUNTED_DR

and ACCOUNTED_CR columns. If you choose to enter your converted amounts, do not

specify the conversion rate, type and date.

 

2.) The following process is explained from Gl_DAILY_RATES_INTERFACE table

Procedure:

1) We populate the Staging Table

2) Performed Validation on Staging Table.

3) Populate Gl_DAILY_RATES_INTERFACE

4) Used Standard Concurrent Program : Program - Daily Rates Import and Calculation.

Interface table for GL Daily Rates are—

Gl_DAILY_RATES_INTERFACE

 

Base tables for GL Daily Rates are—

Gl_DAILY_RATES

 

Create a Staging table based on the requirement

CREATE TABLE XXGL_DRATES_STG

(

   FROM_CURRENCY          VARCHAR2 (15),

   TO_CURRENCY            VARCHAR2 (15),

   FROM_CONVERSION_DATE   DATE,

   TO_CONVERSION_DATE     DATE,

   USER_CONVERSION_TYPE   VARCHAR2 (30),

   CONVERSION_RATE        NUMBER,

   MODE_FLAG              CHAR (1)

);

 

Inserting Data into Staging Table:

Insert into XXGL_DRATES_STG Values (

'USD','INR','29-Jan-2009','31-Jan-2009','Corporate','50','I');

 

 

Moving the Data from Flat File to Base Table using SQL * LOADER:

options(skip=1)

load data

errors=5

infile '/ebs/oracle/apps/apps_st/appl/gl/12.0.0/bin/gl_daily_rates.csv'

Insert into table GL_daily_rates_stg

fields terminated by ','

optionally enclosed by '"'

Trailing nullcols

(From_currency ,To_currency, From_conversion_date, To_conversion_date,

User_conversion_type, conversion_rate, Mode_flag)

Moving the data from Staging tables to Base Tables using

Standard Interface Programs:

 

Create a Package with validations to move the data into Interface Tables

 

CREATE OR REPLACE PACKAGE XXGL_DRATES_PKG

IS

   PROCEDURE DAILY_RATES_PRC (retcode OUT NUMBER, errbuff OUT VARCHAR2);

END;

 

------------------------------------------------------------------

 

CREATE OR REPLACE PACKAGE BODY XXGL_DRATES_PKG

IS

   PROCEDURE DAILY_RATES_PRC (retcode OUT NUMBER, errbuff OUT VARCHAR2)

   IS

      CURSOR cur_drates

      IS

         SELECT FROM_CURRENCY,

                TO_CURRENCY,

                FROM_CONVERSION_DATE,

                TO_CONVERSION_DATE,

                USER_CONVERSION_TYPE,

                CONVERSION_RATE,

                MODE_FLAG

           FROM XXGL_DRATES_STG;

 

      LV_FROM_CURRENCY          VARCHAR2 (15);

      LV_TO_CURRENCY            VARCHAR2 (15);

      LV_USER_CONVERSION_TYPE   VARCHAR2 (30);

      LV_CONVERSION_RATE        NUMBER;

      LV_ERR_FLAG               VARCHAR2 (1) := 'A';

   BEGIN

      FOR i IN CUR_DRATES

      LOOP

         BEGIN

            SELECT CURRENCY_CODE

              INTO LV_FROM_CURRENCY

              FROM FND_CURRENCIES

             WHERE CURRENCY_CODE = i.FROM_CURRENCY;

         EXCEPTION

            WHEN NO_DATA_FOUND

            THEN

               lv_from_currency := NULL;

               lv_err_flag := 'E';

               FND_FILE.PUT_line (FND_FILE.LOG,

                                  'The Currency Code is not defined

/not enabled if not enabled enable it.');

         END;

 

         FND_FILE.PUT_line (

            FND_FILE.LOG,

            'The Currency Code inserting IS--' || LV_FROM_CURRENCY);

 

         BEGIN

            SELECT CURRENCY_CODE

              INTO LV_TO_CURRENCY

              FROM FND_CURRENCIES

             WHERE ENABLED_FLAG = 'Y' AND CURRENCY_CODE = i.To_CURRENCY;

         EXCEPTION

            WHEN NO_DATA_FOUND

            THEN

               lv_from_currency := NULL;

               lv_err_flag := 'E';

               FND_FILE.PUT_line (FND_FILE.LOG,

                                  'The Currency Code is not defined

/not enabled if not enabled enable it.');

         END;

 

         FND_FILE.PUT_line (

            FND_FILE.LOG,

            'The Currency Code inserting IS--' || LV_TO_CURRENCY);

 

         BEGIN

            SELECT USER_CONVERSION_TYPE

              INTO LV_USER_CONVERSION_TYPE

              FROM GL_DAILY_CONVERSION_TYPES

             WHERE USER_CONVERSION_TYPE = i.USER_CONVERSION_TYPE;

         EXCEPTION

            WHEN NO_DATA_FOUND

            THEN

               LV_USER_CONVERSION_TYPE := NULL;

               lv_err_flag := 'E';

               FND_FILE.PUT_line (FND_FILE.LOG,

                                  'The USER_CONVERSION_TYPE is not defined.');

         END;

 

         FND_FILE.PUT_line (

            FND_FILE.LOG,

               'The USER_CONVERSION_TYPE inserting IS--'

            || LV_USER_CONVERSION_TYPE);

 

         BEGIN

            SELECT USER_CONVERSION_TYPE

              INTO LV_USER_CONVERSION_TYPE

              FROM GL_DAILY_CONVERSION_TYPES

             WHERE USER_CONVERSION_TYPE = i.USER_CONVERSION_TYPE;

         EXCEPTION

            WHEN NO_DATA_FOUND

            THEN

               LV_USER_CONVERSION_TYPE := NULL;

               lv_err_flag := 'E';

               FND_FILE.PUT_line (FND_FILE.LOG,

                                  'The USER_CONVERSION_TYPE is not defined.');

         END;

 

         FND_FILE.PUT_line (

            FND_FILE.LOG,

               'The USER_CONVERSION_TYPE inserting IS--'

            || LV_USER_CONVERSION_TYPE);

 

         IF LV_ERR_FLAG = 'A'

         THEN

            INSERT INTO GL_DAILY_RATES_INTERFACE (FROM_CURRENCY,

                                                  TO_CURRENCY,

                                                  FROM_CONVERSION_DATE,

                                                  TO_CONVERSION_DATE,

                                                  USER_CONVERSION_TYPE,

                                                  CONVERSION_RATE,

                                                  MODE_FLAG)

                 VALUES (LV_FROM_CURRENCY,

                         LV_TO_CURRENCY,

                         I.FROM_CONVERSION_DATE,

                         I.TO_CONVERSION_DATE,

                         LV_USER_CONVERSION_TYPE,

                         I.CONVERSION_RATE,

                         I.MODE_FLAG);

         END IF;

      END LOOP;

 

      COMMIT;

   END;

END XXGL_DRATES_PKG;

 

Run the Standard Concurrent Program = Program - Daily Rates Import and Calculation