Release the Sales Order using API

Here we have used two APIs: The first API will create the delivery using the deliver detail ID and then the second API will release that delivery.

1) Create Delivery API ---  wsh_delivery_details_pub.autocreate_deliveries

2) Release that Delivery API ---  wsh_deliveries_pub.delivery_action

    DECLARE

        x_return_status                 VARCHAR2 (2);

        x_msg_count                    NUMBER;

        x_msg_data                      VARCHAR2 (2000);

        p_api_version_number     NUMBER := 1.0;

        init_msg_list                     VARCHAR2 (200);

        x_msg_details                   VARCHAR2 (3000);

        x_msg_summary              VARCHAR2 (3000);

        p_line_rows wsh_util_core.id_tab_type;

        x_del_rows wsh_util_core.id_tab_type;

        l_commit                          VARCHAR2 (30);

        p_delivery_id                   NUMBER;

        p_delivery_name             VARCHAR2 (30);

        x_trip_id                          VARCHAR2 (30);

        x_trip_name                    VARCHAR2 (30);

        api_exception                  EXCEPTION;

        lc_process_flag                VARCHAR2(5) := 'Y';

        lc_inv_period                   VARCHAR2(10);

        ln_hold_cnt                      NUMBER;

        ln_onhand_quantity         NUMBER := 0;

        lc_run_mode                    VARCHAR2(10);

        ln_request_id                   NUMBER := 0;

        lg_request_id                   NUMBER := 0;

        lc_export_phase_code     VARCHAR2(2);

        --

        ln_qoh                   NUMBER := 0;

        ln_rqoh                  NUMBER := 0;

        ln_atr                     NUMBER := 0;

        ln_att                     NUMBER := 0;

        ln_qr                      NUMBER := 0;

        ln_qs                      NUMBER := 0;

        lb_lot_control_code      BOOLEAN;

        lb_serial_control_code   BOOLEAN;

        --

        ln_delivery_detail_id    NUMBER := 0;

        ln_inv_item_id              NUMBER := 0;

        ln_organization_id        NUMBER := 0;

        ln_requested_qty           NUMBER := 0;

        ln_so_line_id                 NUMBER := 0;

        lc_subinventory             oe_order_lines_all.subinventory%TYPE;

    BEGIN

        --

        BEGIN

            SELECT wdd.delivery_detail_id, wdd.inventory_item_id, wdd.organization_id, wdd.requested_quantity,

              ola.line_id, ola.subinventory

              INTO ln_delivery_detail_id, ln_inv_item_id, ln_organization_id, ln_requested_qty, ln_so_line_id, lc_subinventory

              FROM apps.oe_order_headers_all oha,

                   apps.oe_order_lines_all ola,

                   apps.wsh_delivery_details wdd,

                   apps.mtl_system_items_b msib

              WHERE oha.header_id         = ola.header_id

                AND oha.org_id            = ola.org_id

                AND oha.header_id         = wdd.source_header_id

                AND ola.line_id           = wdd.source_line_id

                AND wdd.inventory_item_id = msib.inventory_item_id

                AND wdd.organization_id   = msib.organization_id

                AND oha.booked_flag       = 'Y'

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

                AND wdd.released_status IN ('R', 'B') -- Ready to Release, Backordered

                AND ola.flow_status_code  = 'AWAITING_SHIPPING'

                AND ola.line_number       = 1

                AND oha.order_number      = '3657782'

                AND oha.org_id            = 101;

        EXCEPTION

            WHEN no_data_found THEN

            dbms_output.put_line('  Order delivery details are not found.');

            WHEN others THEN

            dbms_output.put_line('  Error while getting order delivery details: '||SQLERRM);

        END;

        --

        x_return_status := wsh_util_core.g_ret_sts_success;

            --Check Hold applied or not on SO Line using SO_Line_ID

            BEGIN

              SELECT count(*) INTO ln_hold_cnt

              FROM oe_order_holds_all

              WHERE line_id     = ln_so_line_id

              AND released_flag = 'N'

              AND hold_release_id IS NULL;

            EXCEPTION

              WHEN no_data_found THEN

              lc_process_flag := 'Y';

              WHEN others THEN

              dbms_output.put_line( '  Error while checking on hand quantity: '||SQLERRM);

              lc_process_flag := 'N';

            END;

            --

            IF (ln_hold_cnt > 0) THEN

              dbms_output.put_line( '  Validation Error: Hold has applied on Selected SO Line');

              lc_process_flag := 'N';

            END IF;

            -- Check Inventory period is open or not

            BEGIN

              SELECT period_name INTO lc_inv_period

              FROM org_acct_periods_v 

              WHERE organization_id = ln_organization_id

              AND period_name = TO_CHAR(TRUNC(sysdate),'MON-RR')

              AND status = 'Open';

            EXCEPTION

              WHEN no_data_found THEN

              dbms_output.put_line( '  Validation Error: Current Date Inventory Period is not opened. Please alter.');

              lc_process_flag := 'N';

            END;

            -- Check On Hand quantity against SO Line item

            inv_quantity_tree_pub.query_quantities (

                        p_api_version_number    => 1.0,

                        p_init_msg_lst          => 'F',

                        x_return_status         => x_return_status,

                        x_msg_count             => x_msg_count,

                        x_msg_data              => x_msg_data,

                        p_organization_id       => ln_organization_id,

                        p_inventory_item_id     => ln_inv_item_id,

                        p_tree_mode             => apps.inv_quantity_tree_pub.g_transaction_mode,

                        p_is_revision_control   => FALSE,

                        p_is_lot_control        => lb_lot_control_code,

                        p_is_serial_control     => lb_serial_control_code,

                        p_revision              => NULL,

                        p_lot_number            => NULL,

                        p_lot_expiration_date   => SYSDATE,

                        p_subinventory_code     => lc_subinventory,

                        p_locator_id            => NULL,

                        p_onhand_source         => 3,      -- All subinventories

                        x_qoh                   => ln_qoh,

                        x_rqoh                  => ln_rqoh,

                        x_qr                    => ln_qr,

                        x_qs                    => ln_qs,

                        x_att                   => ln_att,  -- available to transact

                        x_atr                   => ln_atr   -- available to reserve

                     );

            --

            IF ( ln_att < ln_requested_quantity) OR ln_att IS NULL THEN

              dbms_output.put_line( '    Validation Error: Onhand quantity is not available.');

              dbms_output.put_line( '    -- Requested quantity : '||ln_requested_quantity);

              dbms_output.put_line( '    -- Available to transact quantity : '||ln_att);

              lc_process_flag := 'N';

            END IF;

            --

            IF (lc_process_flag = 'Y') THEN

                p_line_rows (1) := ln_delivery_detail_id;  -- we can create delivery using Delivery Detail ID

                --

                dbms_output.put_line( '  ');

                dbms_output.put_line( '  Call Auto Create Delivery API.. With Delivery Detail ID: '||ln_delivery_detail_id);

                    --1) API Call for Auto Create Deliveries

                    wsh_delivery_details_pub.autocreate_deliveries

                           (p_api_version_number => 1.0,

                            p_init_msg_list             => apps.fnd_api.g_true,

                            p_commit                      => l_commit,

                            x_return_status             => x_return_status,

                            x_msg_count                => x_msg_count,

                            x_msg_data                  => x_msg_data,

                            p_line_rows                 => p_line_rows,

                            x_del_rows                  => x_del_rows

                           );

                    IF (x_return_status <> wsh_util_core.g_ret_sts_success) THEN

                      dbms_output.put_line('  --> Failed to Auto create delivery for Sales Order');

                      dbms_output.put_line('  --> API Returned error message :'||x_msg_data);

                      RAISE api_exception;

                    ELSE

                        COMMIT;

                    END IF;

                    --

                    p_delivery_id := x_del_rows (1);

                    p_delivery_name := TO_CHAR (x_del_rows (1));

                    --

                    --2) Call API to create Pick Release.

                    dbms_output.put_line( '  API Call for Pick Release. ');

                    --

                    wsh_deliveries_pub.delivery_action

                     (p_api_version_number          => 1.0,

                            p_init_msg_list                => NULL,

                            x_return_status                => x_return_status,

                            x_msg_count                   => x_msg_count,

                            x_msg_data                     => x_msg_data,

                            p_action_code                 => 'PICK-RELEASE',

                            p_delivery_id                  => p_delivery_id,

                            p_delivery_name            => p_delivery_name,

                            p_asg_trip_id                  => NULL,

                            p_asg_trip_name            => NULL,

                            p_asg_pickup_stop_id    => NULL,

                            p_asg_pickup_loc_id      => NULL,

                            p_asg_pickup_stop_seq     => NULL,

                            p_asg_pickup_loc_code     => NULL,

                            p_asg_pickup_arr_date     => NULL,

                            p_asg_pickup_dep_date     => NULL,

                            p_asg_dropoff_stop_id     => NULL,

                            p_asg_dropoff_loc_id      => NULL,

                            p_asg_dropoff_stop_seq    => NULL,

                            p_asg_dropoff_loc_code    => NULL,

                            p_asg_dropoff_arr_date    => NULL,

                            p_asg_dropoff_dep_date    => NULL,

                            p_sc_action_flag          => 'S',

                            p_sc_intransit_flag       => 'N',

                            p_sc_close_trip_flag      => 'N',

                            p_sc_create_bol_flag      => 'N',

                            p_sc_stage_del_flag       => 'Y',

                            p_sc_trip_ship_method     => NULL,

                            p_sc_actual_dep_date      => NULL,

                            p_sc_report_set_id        => NULL,

                            p_sc_report_set_name      => NULL,

                            p_sc_defer_interface_flag => 'Y',

                            p_sc_send_945_flag        => NULL,

                            p_sc_rule_id              => NULL,

                            p_sc_rule_name            => NULL,

                            p_wv_override_flag        => 'N',

                            x_trip_id                 => x_trip_id,

                            x_trip_name               => x_trip_name

                           );

                    IF (x_return_status <> wsh_util_core.g_ret_sts_success) THEN

                      dbms_output.put_line('    Failed to Pick Release the sales order');

                      RAISE api_exception;

                    ELSE

                      COMMIT;

                      dbms_output.put_line('  ');

                      lc_api_flag := 'Y';

                    END IF;

            END IF; -- lc_process_flag

        --

    EXCEPTION

        WHEN api_exception THEN

          dbms_output.put_line( '  ');

          wsh_util_core.get_messages ('Y', x_msg_summary, x_msg_details, x_msg_count);

          IF x_msg_count > 1 THEN

            x_msg_data := x_msg_summary || x_msg_details;

            dbms_output.put_line( '  Msg Data1: '||x_msg_data);

          ELSE

            x_msg_data := x_msg_summary || x_msg_details;

            dbms_output.put_line( '  Msg Data2: '||x_msg_data);

          END IF;

    END;

    /

How to handle Log messages in stored objects (Functions, Procedure, Package Body)

Normally we handle log messages using FND_FILE.PUT_LINE(fnd_file.LOG,'Msg');

But in some cases we need to handle Log messages based on the Parameter value.

If we give the parameter value for DEBUG as Y (Yes), then the program must print a message in the log file. And if the N (No) value is entered, the program cannot print the message.

We can do it using following code logic.. (You can modify it according to your requirement and logic)

For Example -

     -- Package Specification

     CREATE OR REPLACE Package XXC_GET_DATA

     IS
         Procedure write_log(p_message IN VARCHAR2
                                        ,  p_debug_flag IN VARCHAR2 Default 'N');
         --
         Procedure employee_details(errbuf OUT VARCHAR2
                                                  , retcode OUT VARCHAR2
                                                  , p_debug_flag IN VARCHAR2
                                                  , p_emp_id IN NUMBER);

          --

    END XXC_GET_DATA;

   /

     -- Package Body

     CREATE OR REPLACE Package Body XXC_GET_DATA
     IS
         PROCEDURE write_log(p_message IN VARCHAR2
                                        ,  p_debug_flag IN VARCHAR2 Default 'N')
          IS

               lc_time VARCHAR2(20);  -- It will store log print Date with time.
          BEGIN

               SELECT to_char(SYSDATE, 'MM-DD-YYYY HH24:MI:SS') INTO lc_time

               FROM dual;

               --

               IF (p_debug_flag = 'Y') THEN
                    fnd_file.put_line(fnd_file.LOG,lc_time||'-'||p_message);

               END IF;
          EXCEPTION
               WHEN others THEN
                    fnd_file.put_line(fnd_file.LOG,'Error in Write_Log procedure: '||SQLERRM);
          END write_log;

          --
          PROCEDURE employee_details(errbuf OUT VARCHAR2
                                                  , retcode OUT VARCHAR2
                                                  , p_debug_flag IN VARCHAR2
                                                  , p_emp_id IN NUMBER)
          IS
               lc_emp_name emp.emp_name%TYPE;
               lc_address  emp.address%TYPE;
               ln_emp_num  emp.emp_number%TYPE;

          BEGIN

               BEGIN

                    SELECT emp_name, address, emp_number

                    INTO lc_emp_name, lc_address, ln_emp_num

                    FROM emp

                    WHERE emp_id = p_emp_id;

               EXCEPTION

                    WHEN no_data_found THEN

                         write_log('Employee record not found.', p_debug_flag);

                    WHEN others THEN

                         write_log('Error while getting emp details: '||SQLERRM, p_debug_flag);

               END;

               --

          EXCEPTION

               WHEN others THEN

               write_log('Error in employee_details procedure: '||SQLERRM, p_debug_flag);                   

          END employee_details;

          --

    END XXC_GET_DATA;

   /

Delete AR(Account Receivable) Transaction Using API in Oracle Apps

Delete AR Transaction Using API:

Before delete AR transaction "Allow Transaction Deletion" flag should be Y.

Allow Transaction Deletion:
           Enable the Allow transaction deletion option if you want to let users delete transactions from Oracle Receivables after the transactions have been saved.
           You will update this option at Operating Unit level.
 
   Check in database table:
 
           SELECT invoice_deletion_flag
           FROM ar_system_parameters_all ab
           WHERE org_id = 201;   -- Operating Unit org_id
 
           If value of 'invoice_deletion_flag' is Y, its mean you are enable to delete AR Transaction, otherwise you need to do setup for this.
 
   Setup:

      Navigation:  Receivable Responsibility --> Setup --> System --> System Options

  Select 'Trans and Customers' Tab:

  Click on "Allow Transaction Deletion" button and Save it.
  Check in database table.
 
   To delete AR Transaction, follow below steps:
 
   Step 1) Create a staging table to stored data related to AR transaction.

           CREATE TABLE xx_delete_ar_transaction
           AS
             SELECT customer_trx_id,
                             trx_date,
                             trx_number,
                             org_id,
                             creation_date,
                             created_by,
                             'N' record_status
                 FROM ra_customer_trx_all
              WHERE org_id = 201
                    AND trx_number IN ('123000', '124000');

   Step 2) Call API in PLSQL block to Delete AR Transactions:

           DECLARE
              CURSOR lcur_ar_trans
              IS
                 SELECT ROWID,
                                 customer_trx_id,
                                 trx_date,
                                 trx_number,
                                 org_id,
                                 creation_date,
                                 created_by
                 FROM xx_delete_ar_transaction
                 WHERE record_status = 'N';

                          lc_msg_data                VARCHAR2(4000);
                          ln_msg_count              NUMBER;
                          lc_ret_status                VARCHAR2(1) := 'S';
                          v_message_tbl             arp_trx_validate.message_tbl_type;
                          ln_user_id                    NUMBER;
                          ln_responsibility_id     NUMBER;
                          ln_application_id         NUMBER;
                          ln_msg_index              NUMBER;

           BEGIN
              --
              ln_user_id                 := 0;
              ln_application_id      := 0;
              ln_responsibility_id  := 0;

              -- Get application and responsibility id's
              BEGIN
                       SELECT application_id, responsibility_id
                       INTO ln_application_id, ln_responsibility_id
                       FROM fnd_responsibility_tl
                       WHERE responsibility_name = 'Responsibility Name';  -- Enter valid Responsibility Name
              EXCEPTION
                       WHEN others THEN
                        lc_ret_status := 'E';
                        dbms_output.put_line('Error at getting Responsibility id: '||SQLERRM);
              END;
              -- Get User ID
              BEGIN
                       SELECT user_id
                       INTO ln_user_id
                       FROM fnd_user
                       WHERE user_name = 'ABC007';   -- Enter valid User Name
              EXCEPTION
                       WHEN others THEN
                        lc_ret_status := 'E';
                        dbms_output.put_line('Error at getting User id: '||SQLERRM);
              END;
              --
              FOR lreq_ar IN lcur_ar_trans
              LOOP
              -- Variable initialization:
                  ln_msg_count := NULL;
                  lc_msg_data := NULL;
                  --
                  dbms_output.put_line ('--> TRX Number: '|| lreq_ar.trx_number);
                  -- Set Org Context for the session
                  apps.mo_global.set_policy_context ('S', lreq_ar.org_id);
                  -- Set Oracle applications context
                  apps.fnd_global.apps_initialize (ln_user_id, ln_responsibility_id, ln_application_id);

                 -- Call API
                 ar_invoice_api_pub.delete_transaction (
                            p_api_name               => 'Delete_Transaction',
                            p_api_version            => 1.0,
                            p_init_msg_list          => fnd_api.g_true,
                            p_commit                   => fnd_api.g_true,
                            p_validation_level     => fnd_api.g_valid_level_full,
                            p_customer_trx_id     => lreq_ar.customer_trx_id,
                            p_return_status          => lc_ret_status,
                            p_msg_count             => ln_msg_count,
                            p_msg_data               => lc_msg_data,
                            p_errors                     => v_message_tbl);

                IF lc_ret_status <> 'S' THEN
                    dbms_output.put_line ('--> Status: '||lc_ret_status);
             
                    BEGIN
                         UPDATE xx_delete_ar_transaction
                         SET record_status = lc_ret_status
                         WHERE ROWID = lreq_ar.ROWID;
                    EXCEPTION
                         WHEN others THEN
                         dbms_output.put_line('Error at update staging table: '||SQLERRM);
                    END;
             
                    FOR i IN 1 .. ln_msg_count
                    LOOP
                            apps.fnd_msg_pub.get (i,
                                            apps.fnd_api.g_false,
                                            lc_msg_data,
                                            ln_msg_index
                                          );
                            dbms_output.put_line ('Error: '||lc_msg_data);
                     END LOOP;
                ELSE
                     dbms_output.put_line ('AR Transaction Successfully Deleted.');
                END IF;
                dbms_output.put_line ('--------------------');
                COMMIT;
              END LOOP;
           EXCEPTION
                 WHEN OTHERS THEN
                 dbms_output.put_line ('Error in main block: ' || SQLERRM);
           END;
           / 

Cannot update price since the Purchase Agreement doest not allow changes to the Purchase Order line price - Oracle Apps R12

Actual Behavior: While updating the Standard PO Line Unit Price using the Oracle API: PO_CHANGE_API1_S.UPDATE_PO , API returned a below error message:

"Cannot update price since the Purchase Agreement doest not allow changes to the Purchase Order line price."

Problematic Record details

Standard PO Num: 27578
        Line Num: 1
Item Num: A22909
Unit Price: 67.34

User was tyring to update new Unit Price as 70.21  -- at this step got an above error.

Analysis: Standard Purchase Order has linked with Blancket Purchase Agreement, and that BPA is the Global Purchase Agreement(GBPA). That's a reason API not allow to update PO Unit Price.

Query to check the Standard PO is linked or not with Global BPA:

        SELECT pha1.segment1 standard_po_num, pla1.line_num std_line_num
      ,pha2.segment1 GBPA_num, pla2.line_num gbpa_line_num, pha2.global_agreement_flag
FROM    po_headers_all pha1, po_lines_all pla1
      ,po_headers_all pha2, po_lines_all pla2
WHERE pha1.po_header_id = pla1.po_header_id
      AND pla1.from_header_id = pha2.po_header_id
      AND pla1.from_line_id   = pla2.po_line_id
      --
      AND pha1.segment1 = '27578'
      AND pla1.line_num = 1;

Solution: Oracle API has failed to update Standard PO Line level Unit Price because the value of "Allow_Price_Override_Flag" field of Global BPA line is N (No). So, need to update it as Y (Yes).

    We can do it through Purchase Order form: 

    Navigation: PO SuperUser  --> Purchase Orders --> Purchase Orders

        --> Query (F11) --> Enter Global BPA number --> Run (Ctrl + F11)

        --> Click on Price Reference tab --> Select Line --> Click on Allow Price Override check button. And save it

Note: If new and old Unit Price values are same then API will allow to update it.

Thank you..

AP (Account Payable) Invoice Interface or Conversion in Oracle Apps

AP (Account Payable) Invoice Interface or Conversion

We will see how to create AP Invoice using Interface in Oracle R12.

Account payable is a part of Procure to Pay flow. Based on the PO receipt user create the Invoices.

-> Interface Tables: There are two tables-

I)   AP_INVOICES_INTERFACE

II)  AP_INVOICE_LINES_INTERFACE

-> Interface Error Table: AP_INTERFACE_REJECTIONS

Below are the basic steps:

Step 1) Create staging tables

        A) Header Staging table:

  CREATE TABLE xx_ap_inv_hdr_stg
     (invoice_num                 VARCHAR2 (75),
              invoice_type_lookup_code    VARCHAR2 (75),
              invoice_date                DATE,
              po_number                   VARCHAR2 (25),
              vendor_num                  VARCHAR2 (25),
              vendor_name                 VARCHAR2 (100),
              vendor_site_code            VARCHAR2 (25),
              invoice_amount              NUMBER,
              invoice_currency_code       VARCHAR2 (25),
              exchange_rate               NUMBER,
              exchange_rate_type          VARCHAR2 (25),
              exchange_date               DATE,
              terms_name                  VARCHAR2 (50),
              description                 VARCHAR2 (240),
              source                      VARCHAR2 (80),
              voucher_num                 VARCHAR2 (50),
              payment_method_lookup_code  VARCHAR2 (25),
              goods_received_date         DATE,
              invoice_received_date       DATE,
              gl_date                     DATE,
              accts_pay_code_comb_code    VARCHAR2 (50),
              creation_date               DATE,
              created_by                  NUMBER,
              last_updated_by             NUMBER,
              last_update_date            DATE,
              error_text                  VARCHAR2 (2000),
              process_flag                CHAR (1)
);

B) Line Staging table:

  CREATE TABLE xx_ap_inv_line_stg
     (invoice_number              VARCHAR2 (20),
              vendor_num                  VARCHAR2 (40),
              line_number                 VARCHAR2 (20),
              line_type_lookup_code       VARCHAR2 (20),
              quantity_invoiced           VARCHAR2 (30),
              unit_price                  VARCHAR2 (10),
              amount                      VARCHAR2 (10),
              accounting_date             DATE,
              description                 VARCHAR2 (40),
              tax_code                    VARCHAR2 (20),
              po_number                   VARCHAR2 (20),
              po_shipment_num             VARCHAR2 (40),
              dist_code_comb_code         VARCHAR2 (10),
              expenditure_type            VARCHAR2 (20),
              income_tax_region           VARCHAR2 (20),
              process_flag                CHAR (1),
              creation_date               DATE,
              created_by                  NUMBER,
              last_updated_by             NUMBER,
              last_update_date            DATE,
              error_text                  VARCHAR2 (2000)
             );

Step 2) Create Control files (.ctl) to insert data from CSV or text (.txt) files to staging tables:

Note: Here we used two Control files and two CSV files- one for header data and another for line data.

A) Header Control file:

LOAD DATA
        INFILE "D:\oracle\visappl\ap\11.5.0\data\XX_AP_INVOICE_HEADRES_DATA.txt"
        APPEND INTO TABLE xx_ap_inv_hdr_stg
        FIELDS TERMINATED BY ','
        TRAILING NULLCOLS(invoice_num,
                    invoice_type_lookup_code,
                  invoice_date,
                  po_number,
                  vendor_num,
                  vendor_name,
                  vendor_site_code,
                  invoice_amount,
                  invoice_currency_code,
                  exchange_rate,
                  exchange_rate_type,  
                    exchange_date,  
                    terms_name,  
                    description,  
                    source,
                    voucher_num,  
                    payment_method_lookup_code,  
                    goods_received_date,  
                    invoice_received_date,  
                    gl_date,  
                    accts_pay_code_comb_code,
                    creation_date "SYSDATE",
                  created_by constant "-1",
                  last_updated_by constant "-1",
                    last_update_date "SYSDATE",
                    process_flag constant "N"
)

B) Line Control file:

LOAD DATA
        INFILE "D:\ORACLE\VISAPPL\AP\11.5.0\DATA\XX_AP_INVOICE_LINES_DATA.TXT"
        APPEND INTO TABLE xx_ap_inv_line_stg
        FIELDS TERMINATED BY ','
        TRAILING NULLCOLS(invoice_number,  
                    vendor_num,  
                    line_number,
                    line_type_lookup_code,
                    quantity_invoiced,
                    unit_price,
                    amount,
                    accounting_date,
                    description,
                    tax_code,
                    po_number,
                    po_shipment_num,
                  dist_code_comb_code,
                  expenditure_type,
                  income_tax_region,
                    process_flag constant "N",
                    creation_date "SYSDATE",
                  created_by constant "-1",
                  last_updated_by constant "-1",
                    last_update_date "SYSDATE"
);

Step 3) Put the created control files and CSV files in custom application top/BIN folder.

Step 4) Create a Concurrent Executable and Concurrent Program to load data from CSV files to staging table using SQL*Loader.

Step 5) Assign the created concurrent program to Responsibility Group. And execute a program so data will insert in staging tables.

Step 6) Once a data inserted in staging table - we need to validate that data and insert in Interface tables. So, create validation package to insert data from staging tables to Interface tables:

  A) Package Specification:

CREATE OR REPLACE PACKAGE xx_ap_invoice_create_pkg
IS
            PROCEDURE xx_ap_invoice_create_proc(errbuf out VARCHAR2,
              retcode out NUMBER);
        END;
        /

B) Package Body:

CREATE OR REPLACE PACKAGE BODY xx_ap_invoice_create_pkg IS PROCEDURE xx_ap_invoice_create_proc(errbuf out VARCHAR2, retcode out NUMBER) IS ln_vendor_id NUMBER; ln_error_count NUMBER; ln_record_count NUMBER; lc_invoice_number VARCHAR2(75); ln_vendor_site_id NUMBER; lc_term_name VARCHAR2(50); lc_error_text VARCHAR2(2000):=''; ln_hdr_count NUMBER := 0; ln_line_count NUMBER := 0; BEGIN FOR lreq_hdr IN (SELECT a.rowid row_id,a.* FROM xx_ap_inv_hdr_stg a WHERE a.process_flag = 'N' ) LOOP lc_invoice_number := NULL; ln_record_count := ln_record_count + 1; -- Get Vendor Id BEGIN SELECT vendor_id INTO ln_vendor_id FROM po_vendors WHERE vendor_name = lreq_hdr.vendor_name AND segment1 = lreq_hdr.vendor_num; EXCEPTION WHEN OTHERS THEN -- here we can add other exceptions like NO_DATA_FOUND, TOO_MANY_ROWS etc. ln_error_count:=1; lc_error_text :='Vendor name does Not Exist'; END; --Primary key validation BEGIN SELECT 1 INTO lc_invoice_number FROM ap_invoices_all WHERE vendor_id = ln_vendor_id AND invoice_num = lreq_hdr.invoice_num; EXCEPTION WHEN OTHERS THEN ln_error_count:=0; END; -- IF (lc_invoice_number IS NOT NULL) THEN ln_error_count := 1; lc_error_text := lc_error_text||','||'Invoice number: '||lreq_hdr.invoice_num||' already Exist'; END IF; --To Get Vendor Site Id BEGIN SELECT vendor_site_id INTO ln_vendor_site_id FROM po_vendor_sites_all WHERE vendor_site_code = lreq_hdr.vendor_site_code AND vendor_id = ln_vendor_id AND org_id = 204; --get from PO or enter manually EXCEPTION WHEN OTHERS THEN ln_error_count:=1; lc_error_text := lc_error_text||','||'Vendor Site code does Not Exists'; END; --Term Name Exists or Nor BEGIN SELECT name INTO lc_term_name FROM ap_terms WHERE name = lreq_hdr.terms_name; EXCEPTION WHEN OTHERS THEN ln_error_count:=1; lc_error_text :=lc_error_text||','||'Term Name Not Exists'; END; IF ln_error_count <> 0 THEN UPDATE xx_ap_inv_hdr_stg SET process_flag = 'E', error_text = lc_error_text WHERE ROWID = lreq_hdr.ROW_ID; ELSE ln_hdr_count := ln_hdr_count + 1; INSERT INTO ap_invoices_interface (invoice_id, invoice_num, invoice_type_lookup_code, invoice_date, po_number, vendor_id, vendor_site_code, invoice_amount, invoice_currency_code, exchange_rate, exchange_rate_type, exchange_date, terms_name, description, source, voucher_num, payment_method_lookup_code, goods_received_date, invoice_received_date, gl_date, accts_pay_code_combination_id, org_id, creation_date, created_by, last_update_date, last_updated_by ) VALUES(ap_invoices_interface_s.nextval, lc_invoice_number, lreq_hdr.invoice_type_lookup_code, lreq_hdr.invoice_date, lreq_hdr.po_number, ln_vendor_id, lreq_hdr.vendor_site_code, lreq_hdr.invoice_amount, lreq_hdr.invoice_currency_code, lreq_hdr.exchange_rate, lreq_hdr.exchange_rate_type, lreq_hdr.exchange_date, lreq_hdr.terms_name, lreq_hdr.description, lreq_hdr.source, lreq_hdr.voucher_num, lreq_hdr.payment_method_lookup_code, lreq_hdr.goods_received_date, lreq_hdr.invoice_received_date, lreq_hdr.gl_date, lreq_hdr.accts_pay_code_comb_code, 204, sysdate, fnd_global.user_id, sysdate, fnd_global.user_id); -- Update staging table with process flag S (Successfully inserted record in stg table.) BEGIN UPDATE xx_ap_inv_hdr_stg SET process_flag = 'S' WHERE rowid = lreq_hdr.row_id; EXCEPTION WHEN others THEN lc_error_text :=lc_error_text||','||'Header Stg table updation failed -'||SQLERRM; END; -- FOR j IN (SELECT b.rowid row_id,b.* FROM xx_ap_inv_line_stg b WHERE process_flag = 'N' AND vendor_num = lreq_hdr.vendor_num AND invoice_number = lreq_hdr.invoice_num ) LOOP ln_line_count := ln_line_count + 1; INSERT INTO ap_invoice_lines_interface

(invoice_id, invoice_line_id, line_number, line_type_lookup_code, quantity_invoiced, unit_price, amount, accounting_date, description, tax_code, po_number, po_shipment_num, dist_code_combination_id, expenditure_type, income_tax_region, created_by, creation_date, last_update_date, last_updated_by) VALUES(ap_invoices_interface_s.currval, ap_invoice_lines_interface_S.nextval, J.line_number, J.line_type_lookup_code, J.quantity_invoiced, J.unit_price, J.amount, J.accounting_date, J.description, J.tax_code, J.po_number, J.po_shipment_num, J.dist_code_comb_code, J.expenditure_type,

J.income_tax_region, fnd_global.user_id, sysdate, sysdate, fnd_global.user_id); -- BEGIN UPDATE xx_ap_inv_line_stg SET process_flag = 'S' WHERE rowid = j.row_id; EXCEPTION WHEN others THEN lc_error_text :=lc_error_text||','||'Line Stg table updation failed -'||SQLERRM; END; -- END LOOP; END IF; END LOOP; -- IF ln_record_count = 0 THEN retcode := 1; END IF; fnd_file.put_line(fnd_file.log,'**********************PSNL AP Invoice Interface program started**********************'); fnd_file.put_line(fnd_file.log,'Total header records processed: '||ln_hdr_count); fnd_file.put_line(fnd_file.log,'Total line records processed: '||ln_line_count); fnd_file.put_line(fnd_file.log,'**********************PSNL AP Invoice Interface program ended**********************'); END xx_ap_invoice_create_proc; END xx_ap_invoice_create_pkg; /

Step 7) Once the data has inserted in Interface table then we need to run a standard Import program to process data from Interface tables to Base tables.

Import program is: Payables Invoice Import.

Now, we can check a processed records in Base tables.

SELECT * FROM ap_invoices_all;

SELECT * FROM ap_invoice_lines_all;

SELECT * FROM ap_invoice_distributions_all;