Customer Conversion

Customer Conversion

 CREATE OR REPLACE PACKAGE BODY APPS.XX_TP_DMIG_LEAD_PROFILES_PKG1
 AS
    error_message   VARCHAR2 (30000);
    PROCEDURE xx_tp_ar_cust_val_proc1;
    PROCEDURE xx_tp_ar_cust_process_proc1;
    /* Cursor to display the Status Report of all the records*/
    CURSOR cur_sum
    IS
         SELECT   COUNT (1) num,
                  err_status,
                  NVL (err_msg, 'Validated Records') err_msg
           FROM   XXCUS.xx_tp_dmig_lead_profiles_stg1 stg
       GROUP BY   err_status, err_msg;
    CURSOR cur_sum1
    IS
         SELECT   COUNT (1) num, err_status
           -- , NVL (err_msg, 'Processed Records') err_msg
           FROM   XXCUS.xx_tp_dmig_lead_profiles_stg1 stg
       GROUP BY   err_status;
    --                , err_msg;
    /* Replacing the Fnd_File.out */
    PROCEDURE PRINT (p_in IN VARCHAR2);
    PROCEDURE PRINT (p_in IN VARCHAR2)
    IS
    BEGIN
       fnd_file.put_line (fnd_file.output, p_in);
       DBMS_OUTPUT.put_line (p_in);
    END;
    PROCEDURE main (retbuf            OUT VARCHAR2,
                    retcode           OUT NUMBER,
                    process_flag   IN     VARCHAR2)
    IS
    BEGIN
       /*==============================================
        *   Purpose : Updating the err status to  V   in
                      XX_TP_DMIG_LEAD_PROFILES_STG once the records
              are validated.
        * =============================================*/
       IF process_flag = 'V'
       THEN
          --
          --
          fnd_file.put_line (fnd_file.LOG, ' Validation started ');
          xx_tp_ar_cust_val_proc1;
       /*=============================================
         *   Purpose : Updating the err status to S  in
                   XX_TP_DMIG_LEAD_PROFILES_STG  once the
               records are Processed
         * =============================================*/
       ELSIF process_flag = 'P'
       THEN
          fnd_file.put_line (fnd_file.LOG, ' PROCESSING DATA  started ');
          --
          -- Process the data
          --
          xx_tp_ar_cust_process_proc1;
          fnd_file.put_line (fnd_file.LOG, ' END PROCESSING DATA');
       ELSE
          --
          -- Validatiopn and Process
          --
          NULL;
       END IF;
    EXCEPTION
       WHEN OTHERS
       THEN
          fnd_file.put_line (fnd_file.LOG,
                             'Error in main due to :' || SQLERRM);
    END main;                                               -- End for the main
    /*=============================================
    * Procedure : XX_TP_AR_CUST_VAL_PROC
    * Purpose : This procedure validates the data in staging
                Table XX_TP_DMIG_LEAD_PROFILES_STG and updates the
                data err_status and err_msg with 'E'(Errored)
                or V(once successfully validated) with respective
                error message.
     * =============================================*/
    PROCEDURE xx_tp_ar_cust_val_proc1
    IS
       x_cntry_code    fnd_territories.territory_code%TYPE;
       x_site_code     ar_lookups.lookup_code%TYPE;
       x_stat_code     ar_lookups.lookup_code%TYPE;
       x_credit_rate   ar_lookups.lookup_code%TYPE;
       CURSOR cur_validate
       IS
          SELECT   stg.ROWID, stg.*
            FROM   XXCUS.xx_tp_dmig_lead_profiles_stg1 stg
           WHERE   NVL (err_status, 'X') <> 'S';
    BEGIN
       PRINT (' ********************************  ');
       PRINT (' Validation Stage for CUSTOMER ');
       PRINT (' ********************************  ');
       PRINT ('   ');
       PRINT ('ERROR DETAILS: ');
       PRINT ('************* ');
       PRINT ('   ');
       FOR rec_validate IN cur_validate
       LOOP
          error_message := NULL;
          rec_validate.err_status := NULL;
          rec_validate.err_status := 'V';
          /* =============================================
            *   Purpose : Verify wherther the Party name is
                             defined or not
           * =============================================*/
          IF rec_validate.original_name IS NULL
          THEN
             error_message := 'Party name is mandatory';
             rec_validate.err_status := 'E';
          END IF;
          /* =============================================
            *   Purpose : Verify whether the Country is
                        defined in Fnd_Territories or not.
           * =============================================*/
          BEGIN
             SELECT   territory_code
               INTO   x_cntry_code
               FROM   fnd_territories
              WHERE   territory_code = rec_validate.country_code;
          EXCEPTION
             WHEN NO_DATA_FOUND
             THEN
                error_message :=
                   error_message || ',' || 'Invalid territory code';
                rec_validate.err_status := 'E';
                PRINT (
                   rec_validate.first_name || ' :  Invalid territory code'
                );
             WHEN OTHERS
             THEN
                error_message :=
                      error_message
                   || ','
                   || 'Error while validating the territory code due to : '
                   || SQLERRM;
                rec_validate.err_status := 'E';
          END;
          /* =============================================
                * Purpose : Updating the Error Status and
                  Error Message in the Staging table.
           * =============================================*/
          UPDATE   XXCUS.XX_TP_DMIG_LEAD_PROFILES_STG1 x
             SET   err_msg = error_message,
                   err_status = rec_validate.err_status
           WHERE   x.ROWID = rec_validate.ROWID;
       END LOOP;
       COMMIT;
       PRINT ('   ');
       PRINT ('SUMMARY OF VALIDATION ');
       PRINT ('********************* ');
       PRINT ('   ');
       PRINT ('COUNT       STATUS      ERROR MESSAGE');
       PRINT ('----        ------      -------------');
       FOR rec_sum IN cur_sum
       LOOP
          PRINT(   RPAD (TO_CHAR (rec_sum.num), 12, ' ')
                || RPAD (rec_sum.err_status, 12, ' ')
                || rec_sum.err_msg);
       END LOOP;
       PRINT ('**** END OF THE REPORT *****');
    EXCEPTION
       WHEN OTHERS
       THEN
          fnd_file.put_line (
             fnd_file.output,
             'Error while validating the data due to: ' || SQLERRM
          );
    END xx_tp_ar_cust_val_proc1;                        /* End of validation */
    --
    -- Processing the Valid data
    /* =============================================
     * Procedure : XXCSC_AR_CUST_VAL_PROC
     * Purpose : This procedure processses the data into R12 base
                 Tables from staging table and updates the err_status
             to 'S' once it is processed into hz base tables.
             if any error occurs updates err_status and err_msg
             with 'E'(Errored) with respective error message.
      * =============================================*/
    PROCEDURE xx_tp_ar_cust_process_proc1
    IS
       p_cust_account_rec       hz_cust_account_v2pub.cust_account_rec_type;
       p_person_rec             hz_party_v2pub.person_rec_type;
       p_customer_profile_rec   hz_customer_profile_v2pub.customer_profile_rec_type;
       p_organization_rec       hz_party_v2pub.organization_rec_type;
       p_location_rec           hz_location_v2pub.location_rec_type;
       p_cust_site_use_rec      hz_cust_account_site_v2pub.cust_site_use_rec_type;
       p_cust_acct_site_rec     hz_cust_account_site_v2pub.cust_acct_site_rec_type;
       p_party_site_rec         hz_party_site_v2pub.party_site_rec_type;
       x_cust_account_id        NUMBER;
       x_account_number         VARCHAR2 (2000);
       x_party_id               NUMBER;
       x_party_number           VARCHAR2 (2000);
       x_profile_id             NUMBER;
       x_msg_count              NUMBER;
       x_msg_data               VARCHAR2 (2000);
       x_party_site_id          NUMBER;
       x_party_site_number      VARCHAR2 (2000);
       x_location_id            NUMBER;
       x_site_use_id            NUMBER;
       x_return_status          VARCHAR2 (2000);
       x_cust_acct_site_id      NUMBER;
       x_success_rec            NUMBER;
       x_err_count              NUMBER;
       CURSOR cur_customer (x_status VARCHAR2)
       IS
          SELECT   xcs.ROWID, xcs.*
            FROM   XXCUS.xx_tp_dmig_lead_profiles_stg1 xcs
           WHERE   err_status = x_status;
    BEGIN
       /*      PRINT ('               **********PROCESSING OF RECORDS**********                     ');
             fnd_file.put_line(fnd_file.output,'SUMMARY OF PROCESSED RECORDS');
             PRINT(RPAD(' Party ID',20,' ') || RPAD(' Cust Account Id',20,' ' )|| RPAD('Party Site Id',20,'
             PRINT(RPAD('-',20,'-') || RPAD('-',20,'-' )|| RPAD('-',20,'-'));
             PRINT( '  ');
       */
       FOR rec_cust IN cur_customer ('V')
       LOOP
          error_message := NULL;
          /* =============================================
               *Purpose : This API take the customer info'n from the staging
            table as input, and outputs the unique id's (party_id,cust_account_id,
            account_number) and pushes the whole   data into r12 hz tables.
              * =============================================*/
          p_cust_account_rec.account_name := rec_cust.original_name;
          p_cust_account_rec.created_by_module := 'TCA_V2_API';
          p_person_rec.person_first_name := rec_cust.first_name;
          hz_cust_account_v2pub.create_cust_account ('T',
                                                     p_cust_account_rec,
                                                     p_person_rec,
                                                     p_customer_profile_rec,
                                                     'F'                  --'T'
                                                        ,
                                                     x_cust_account_id,
                                                     x_account_number,
                                                     x_party_id,
                                                     x_party_number,
                                                     x_profile_id,
                                                     x_return_status,
                                                     x_msg_count,
                                                     x_msg_data);
          IF x_return_status <> 'S'
          THEN
             error_message := x_msg_data;
          END IF;
          /* =============================================
          *Purpose : This API take the Location info'n from the staging
       table as input, and outputs the unique id's (location_id)
       and pushes the whole   data into r12 hz tables.
         * =============================================*/
          IF x_return_status = 'S'
          THEN
             -- Initialization of the default values to create the  location
             p_location_rec.country := rec_cust.country_code;           ---'US'
             p_location_rec.address1 := rec_cust.address1;
             p_location_rec.city := rec_cust.city;
             p_location_rec.county := rec_cust.country_code;
             p_location_rec.postal_code := rec_cust.postal_code;
             ----TO_NUMBER (rec_cust.postal_code);
             p_location_rec.state := rec_cust.state;
             p_location_rec.created_by_module := 'TCA_V2_API';
             hz_location_v2pub.create_location ('T',
                                                p_location_rec,
                                                x_location_id,
                                                x_return_status,
                                                x_msg_count,
                                                x_msg_data);
             IF x_return_status <> 'S'
             THEN
                error_message := x_msg_data;
             END IF;
          END IF;
          /* =============================================
          *Purpose : This API take the Party Site info'n from the staging
       table as input, and outputs the party_site_id, party_site_number
       and pushes the whole data into r12 hz tables.
         * =============================================*/
          --
          IF x_return_status = 'S'
          THEN
             p_party_site_rec.party_id := x_party_id;      --rec_cust.party_id;
             p_party_site_rec.location_id := x_location_id;
             --rec_cust.location_id;
             p_party_site_rec.identifying_address_flag := 'Y';
             p_party_site_rec.created_by_module := 'TCA_V2_API';
             hz_party_site_v2pub.create_party_site ('T',
                                                    p_party_site_rec,
                                                    x_party_site_id,
                                                    x_party_site_number,
                                                    x_return_status,
                                                    x_msg_count,
                                                    x_msg_data);
             COMMIT;
             IF x_return_status <> 'S'
             THEN
                error_message := x_msg_data;
             END IF;
          END IF;
          /* =============================================
           *Purpose : This API take the Customer Account Site info'n
        from the staging table as input, and outputs the cust_acct_site_id
        and pushes the whole data into r12 hz tables.
          * =============================================*/
          IF x_return_status = 'S'
          THEN
             /*            PRINT(RPAD(x_party_id,20, ' ')||RPAD(x_cust_account_id,20, ' ') || RPAD(x_party_site
             */
             p_cust_acct_site_rec.cust_account_id := x_cust_account_id;
             p_cust_acct_site_rec.party_site_id := x_party_site_id;
             p_cust_acct_site_rec.LANGUAGE := rec_cust.LANGUAGE_code;
             p_cust_acct_site_rec.created_by_module := 'TCA_V2_API';
             hz_cust_account_site_v2pub.create_cust_acct_site (
                'T',
                p_cust_acct_site_rec,
                x_cust_acct_site_id,
                x_return_status,
                x_msg_count,
                x_msg_data
             );
             IF x_return_status <> 'S'
             THEN
                error_message := x_msg_data;
             END IF;
          END IF;
          /* =============================================
           *Purpose : This API take the Customer Site use info'n
        from the staging table as input, and outputs the site_use_id
        and pushes the whole data into r12 hz tables.
          * =============================================*/
          IF x_return_status = 'S'
          THEN
             p_cust_site_use_rec.cust_acct_site_id := x_cust_acct_site_id;
             p_cust_site_use_rec.site_use_code := rec_cust.site_use_code;
             --rec_cust.SITE_USE_code;
             p_cust_site_use_rec.created_by_module := 'TCA_V2_API';
             hz_cust_account_site_v2pub.create_cust_site_use (
                'T',
                p_cust_site_use_rec,
                p_customer_profile_rec,
                '',
                '',
                x_site_use_id,
                x_return_status,
                x_msg_count,
                x_msg_data
             );
             IF x_return_status <> 'S'
             THEN
                error_message := x_msg_data;
             END IF;
          END IF;
          /* =============================================
          Purpose: Running Customer profile
          * =============================================*/
          /*    IF x_return_status = 'S'
                   THEN
                         p_customer_profile_rec.cust_account_id := x_cust_account_id;
                         p_customer_profile_rec.statement_cycle_id := rec_cust.statment_cycle_id;
                         p_customer_profile_rec.created_by_module := 'TCA_V2_API';
                          hz_customer_profile_v2pub.create_customer_profile (
                          p_customer_profile_rec    ,
                          p_create_profile_amt      => FND_API.G_TRUE,
                          x_cust_account_profile_id => l_cust_account_profile_id,
                          x_return_status           => x_return_status,
                          x_msg_count               => x_msg_count,
                          x_msg_data                => x_msg_data
                      IF x_return_status <> 'S'
                      THEN
                         error_message              := x_msg_data;
                      END IF;
                   END IF;
          */
          /* =============================================
            *Purpose : Updates the data into Staging table with
        respective id's whcih are returned by the API's. Also
        updates the ERR_STATUS and ERR_MSG respectively
           * =============================================*/
          UPDATE   XXCUS.xx_tp_dmig_lead_profiles_stg1 xcst
             SET   err_status = x_return_status,
                   err_msg = error_message,
                   party_id = x_party_id,
                   location_id = x_location_id,
                   cust_account_id = x_cust_account_id,
                   party_site_id = x_party_site_id,
                   cust_acct_site_id = x_cust_acct_site_id,
                   site_use_id = x_site_use_id,
                   request_id = APPS.FND_GLOBAL.CONC_REQUEST_ID,
                   created_by = APPS.FND_GLOBAL.USER_ID,
                   last_updated_by = APPS.FND_GLOBAL.USER_ID
           WHERE   ROWID = rec_cust.ROWID;
          COMMIT;
       END LOOP;
       -- ERROR REPORT
       PRINT ('  ');
       PRINT (RPAD ('*', 60, '*'));
       PRINT (
          RPAD (' ', 25, ' ') || 'SUMMARY OF RECORDS ' || RPAD (' ', 25, ' ')
       );
       PRINT (RPAD ('*', 60, '*'));
       PRINT ('  ');
       SELECT   COUNT (ORIGINAL_name)
         INTO   x_err_count
         FROM   XXCUS.xx_tp_dmig_lead_profiles_stg1
        WHERE   err_status = 'E';
       PRINT('TOTAL NUMBER OF RECORDS ERROERED OUT WHILE PROCESSING     :'
             || X_ERR_COUNT);
       SELECT   COUNT (1)
         INTO   x_err_count
         FROM   XXCUS.xx_tp_dmig_lead_profiles_stg1
        WHERE   err_status = 'S';
       PRINT('TOTAL NUMBER OF RECORDS INSERTED INTO HZ BASE TABLES      :'
             || X_ERR_COUNT);
       PRINT ('  ');
       PRINT (RPAD ('*', 60, '*'));
       PRINT (RPAD (' ', 28, ' ') || 'ERROR DATA' || RPAD (' ', 28, ' '));
       PRINT (RPAD ('*', 60, '*'));
       PRINT(   RPAD (' PARTY NAME', 30, ' ')
             || RPAD (' ', 8, ' ')
             || RPAD ('ERROR MESSAGE', 100, ' '));
       PRINT (
          RPAD ('-', 30, '-') || RPAD (' ', 8, ' ') || RPAD ('-', 100, '-')
       );
       FOR rec_cust IN cur_customer ('E')
       LOOP
          PRINT(RPAD (SUBSTR (rec_cust.ORIGINAL_name, 1, 40), 40, ' ')
                || RPAD (rec_cust.err_msg, 100, ' '));
       END LOOP;
       PRINT ('  ');
       PRINT (RPAD ('*', 60, '*'));
       PRINT(   RPAD (' ', 28, ' ')
             || 'PROCESSED RECORD DETAILS'
             || RPAD (' ', 28, ' '));
       PRINT (RPAD ('*', 60, '*'));
       PRINT ('  ');
       PRINT(   RPAD (' PARTY NAME', 40, ' ')
             || RPAD ('ACCOUNT NAME', 50, ' ')
             || RPAD ('PARTY SITE ID', 20, ' '));
       PRINT (
          RPAD ('-', 40, '-') || RPAD ('-', 50, '-') || RPAD ('-', 20, '-')
       );
       PRINT ('  ');
       FOR rec_cust IN cur_customer ('S')
       LOOP
          PRINT(   RPAD (rec_cust.ORIGINAL_name, 40, ' ')
                || RPAD (SUBSTR (rec_cust.FIRST_name, 1, 50), 50, ' ')
                || RPAD (rec_cust.party_site_id, 20, ' '));
       END LOOP;
       PRINT ('   ');
       PRINT (RPAD ('-', 60, '-'));
       PRINT (RPAD (' ', 28, ' ') || 'END REPORT' || RPAD (' ', 28, ' '));
       PRINT (RPAD ('-', 60, '-'));
       COMMIT;
       /* Report to display the Record Status*/
       --BEGIN
       /*      PRINT ('   ');
             PRINT ('   ');
             PRINT ('SUMMARY OF RECORDS ');
             PRINT ('******************* ');
       */
       /*      PRINT ('   ');
       /*      PRINT ('COUNT       STATUS     ');
             PRINT ('-----       -------      ');
            FOR rec_sum IN cur_sum1
             LOOP
             if(rec_sum.err_status='S')
             then
                PRINT ('TOTAL NUMBER OF RECORDS INSERTED INTO HZ BASE TABLES      :'||X_ERR_COUNT);
              else
                    PRINT ('TOTAL NUMBER OF RECORDS errored out      :'||X_ERR_COUNT);
           end if;
                PRINT (   RPAD (TO_CHAR (rec_sum.num)
                              , 12
                              , ' '
                               )
                       || RPAD (rec_sum.err_status
                              , 12
                              , ' '
                               ));
       --                || rec_sum.err_msg);*/
       BEGIN
          NULL;
       /*select count(*) into x_err_count from xxcsc_ar_cust_conv_stg where err_status='E';
       PRINT ('TOTAL NUMBER OF RECORDS ERROERED OUT WHILE PROCESSING     :'||X_ERR_COUNT);
       select count(*) into x_err_count from xxcsc_ar_cust_conv_stg where err_status='S';
       PRINT ('TOTAL NUMBER OF RECORDS INSERTED INTO HZ BASE TABLES      :'||X_ERR_COUNT);
       */
       EXCEPTION
          WHEN NO_DATA_FOUND
          THEN
             PRINT('NO DATA FOUND  WHILE PRINTING THE COUNT AND STATUS OF RECORDS');
          WHEN OTHERS
          THEN
             PRINT ('ERROR WHILE PRINTING THE COUNT AND STATUS OF RECORDS');
       END;
    -- END LOOP;
    EXCEPTION
       WHEN OTHERS
       THEN
          fnd_file.put_line (
             fnd_file.LOG,
             'Error while processing records due to :' || SQLERRM
          );
    END xx_tp_ar_cust_process_proc1;
 END XX_TP_DMIG_LEAD_PROFILES_PKG1;

Instructions for GL interface

Instructions for GL interface

1)Once we get the data into the stage table we have to transfer from stage table into
 Interface table by using PL/SQL Program
 Inside this program we write
 1)Cursor   (to select data from stage table)
 2)Validate Data
 3)Insert Statement(To Insert Into Interface table.
Note:1) If Record is valid record then we will insert into interface table other wise
        we will insert into Error tables.
     2)Before Inserting the Data into Interface tables first we have to understand the
       Interface table structure and we should know waht data is valid and what data
      is not valid for the all Mandatroy columns.
2)Once the data is Inserted into the Interface table . Then we will submit concurrent
  program to transfer the data from interface table to Base Tables.
In this Interface Interface table is GL_INTERFACE
Mandatroy Columns:
==================
1)STATUS
2)ACCOUNTING_DATE
3)CURRENCY_CODE
4)CREATED_BY
5)CREATION_DATE
6)USER_JE_SOURCE_NAME
7)USER_JE_CATEGORY_NAME
8)ACTUAL_FLAG
9)ENTERED_DR
10)ENTERED_CR
11)GROUP_ID
12)PERIOD_NAME
13)SET_OF_BOOKS_ID

1)Status Column will accept any Data. but we will insert always standard string called
         "NEW". It means that we are bringing new data into Oracle Applications.
2)ACCOUNTING_DATE  : Column will accept valid acc_date as per the accounting Periods
3)CURRENCY_CODE    : from FND_CURRENCIES table we can find out wether CUrrency Code is
            valid or Not if Currecny code is available in the Table and enabled it is
            valid.otherwise Invalid
4)CREATED_BY  : IS nothing but UserID we have to find wether  USerID is valid or not
               By using FND_USER table we can find out wether it is valid or not.
5)CREATION_DATE : Should be valid date
                   date should be <= SYSDATE
6)USER_JE_SOURCE_NAME: Valid Source name
                 By using GL_JE_SOURCES table  we can find wether valid source or not.
7)USER_JE_CATEGORY_NAME : Will accept valid cvategory name
        By using GL_JE_CATEGORIES table we can find out wether valid category or Not.
8)ACTUL_FLAG    : This Column will accept single Character
                  Either 'A' 'B' 'E'
                  A=Actuval Amounts
                  B=Budeget Amounts
                  E=Encumbrance Amounts
9)ENTERED_DR
10)ENTERED_CR : Both Columns we accept Numbers Only but same number we have to insert
                into both the columns.
        Always ENTERED_CR = ENTERED_DR . Otherwise Suspense Account will be
               Created.
11)GROUP_ID   : Will accept any Number
12)period_name : Will Accept valid period and that period should be in the Open status
                By using GL_PERIODS we can find wether Period is there or not
                         GL_PERIOD_STATUSES table we can find wether it is in "Open"
                status or Not.
13)SET_OF_BOOKS_ID  : This column will accept valid set of Books ID. By using
              GL_SETS_OF_BOOKS table we can fnd out wether valuid set_of_books_id or
              not.

Procedure for GL Interface

Procedure for GL Interface

CREATE OR REPLACE PROCEDURE GE_Pro30(Errbuf  OUT VARCHAR2,
                                      Retcode OUT VARCHAR2) IS
  -- cursor declaration
  CURSOR gl_cur IS
    SELECT
        status    ,
        set_of_books_id   ,
        accounting_date   ,
        currency   ,
        date_created   ,
        created_by    ,
        actual_flag    ,
        category       ,
        source     ,
        curr_conversion     ,
          segment1     ,
          segment2        ,
          segment3        ,
          segment4        ,
          segment5        ,
          entered_dr      ,
          entered_cr      ,
          accounted_dr    ,
          accounted_cr    ,
          group_id
          FROM XX_TEMP;
l_currencycode    VARCHAR2(25);
l_set_of_books_id NUMBER(3);
l_flag            VARCHAR2(2);
l_error_msg       VARCHAR2(100);
l_err_flag        VARCHAR2(10);
l_category        VARCHAR2(100);
L_USERID          NUMBER(10);
l_count           NUMBER(9) default 0;
BEGIN
 DELETE FROM  gl_interface;
 COMMIT;
FOR rec_cur IN gl_cur LOOP
l_count:=l_count+1;
l_flag    :='A';
--Category Column Validation
BEGIN
 SELECT USER_JE_CATEGORY_NAME
 INTO   l_CATEGORY
 FROM   GL_JE_CATEGORIES
 WHERE  USER_JE_CATEGORY_NAME = REC_CUR.Category;
 EXCEPTION
     WHEN OTHERS THEN
            l_flag:='E';
            l_error_msg:='Category does not exist ';
Fnd_File.put_line (Fnd_File.LOG,'Inserting data into the Interface TABLE'||'-'||l_count||' '||l_error_msg);
END;
 --End Category Column Validation
 --User ID column validation
BEGIN
SELECT USER_ID
INTO   L_USERID
FROM   FND_USER
WHERE  USER_ID = REC_CUR.created_by;
EXCEPTION
WHEN OTHERS THEN
            l_flag:='E';
            l_error_msg:='User ID does not exist ';
Fnd_File.put_line (Fnd_File.LOG,'Inserting data into the Interface TABLE'||'-'||l_count||' '||l_error_msg);
END;
 --End of Created_by OR UserID column Validation
 --Set of  books Validation
 BEGIN
     SELECT set_of_books_id
     INTO   l_set_of_books_id
     FROM   GL_SETS_OF_BOOKS
     WHERE set_of_books_id=rec_cur.set_of_books_id;
     EXCEPTION
     WHEN OTHERS THEN
            l_flag:='E';
            l_error_msg:='set of Books ID does not exist ';
Fnd_File.put_line (Fnd_File.LOG,'Inserting data into the Interface TABLE'||'-'||l_count||' '||l_error_msg);
 END;
--Cuurency Code Validation
 BEGIN
     SELECT currency_code
     INTO   l_currencycode
     FROM   fnd_currencies
     WHERE currency_code=rec_cur.currency
     AND currency_code='USD';
     EXCEPTION
      WHEN OTHERS THEN
            l_flag:='E';
            l_error_msg:='currency code does not exists';
Fnd_File.put_line (Fnd_File.LOG,'Inserting data into the Interface TABLE'||'-'||l_count||' '||l_error_msg);
 END;
IF   l_flag!='E' THEN
Fnd_File.put_line (Fnd_File.LOG,'Inserting data into the Interface TABLE');
INSERT INTO gl_interface(status,
      set_of_books_id,
      accounting_date,
      currency_code,
      date_created,
      created_by,
      actual_flag,
      user_je_category_name,
      user_je_source_name,
      user_currency_conversion_type,
      segment1,
      segment2,
      segment3,
      segment4,
      segment5,
      entered_dr,
      entered_cr,
      accounted_dr,
      accounted_cr,
      group_id)
      VALUES
      (rec_cur.status    ,
       rec_cur.set_of_books_id   ,
       rec_cur.accounting_date   ,
       rec_cur.currency    ,
       rec_cur.date_created   ,
       rec_cur.created_by    ,
       rec_cur.actual_flag    ,
       rec_cur.category       ,
       rec_cur.source     ,
       rec_cur.curr_conversion                ,
       rec_cur.segment1  ,
       rec_cur.segment2  ,
       rec_cur.segment3  ,
       rec_cur.segment4  ,
       rec_cur.segment5  ,
       rec_cur.entered_dr   ,
       rec_cur.entered_cr   ,
       rec_cur.accounted_dr  ,
       rec_cur.accounted_cr  ,
       rec_cur.group_id);
 END IF;
 l_flag:=NULL;
 l_error_msg:=NULL;
  END LOOP;
COMMIT;
END  GE_Pro30;
/

Inventory Outbound Interface

Inventory Outbound Interface

CREATE OR REPLACE procedure MAHAMAD_INV_Out1(Errbuf OUT varchar2,
                                       Retcode ouT varchar2,
            f_id    in number,
            t_id    in varchar2) as
cursor c1 is select
    msi.segment1 item,
    msi.inventory_item_id Itemid,
    msi.description  itemdesc,
    msi.primary_uom_code Uom,
    ood.organization_name name,
    ood.organization_id   id,
    mc . segment1||','||mc.segment2 Category
    from
    mtl_system_items_b           msi,
    org_organization_definitions ood,
    mtl_item_categories          mic,
    mtl_categories               mc
    where
    msi.organization_id       = ood.organization_id
    and msi.inventory_item_id = mic.inventory_item_id
    and msi.organization_id   = mic.organization_id
    and mic.category_id       = mc.category_id
    and msi.purchasing_item_flag = 'Y'
    and msi.organization_id between f_id and t_id;
x_id     utl_file.file_type;
l_count  number(5) default 0;
begin
          x_id:=utl_file.fopen('d:\oracle\proddb\8.1.7\plsql\temp','invoutdata.dat','W');
           --select * from v$parameter where name like '%utl_file%'
for x1 in c1 loop
l_count:=l_count+1;
utl_file.put_line(x_id,x1.item    ||'-'||
        x1.itemid  ||'-'||
        x1.itemdesc||'-'||
        x1.uom   ||'-'||
        x1.name   ||'-'||
        x1.id   ||'-'||
        x1.category   );
end loop;
utl_file.fclose(x_id);
Fnd_file.Put_line(Fnd_file.output,'No of Records transfered to the data file :'||l_count);
Fnd_File.Put_line(fnd_File.Output,' ');
Fnd_File.Put_line(fnd_File.Output,'Submitted User name  '||Fnd_Profile.Value('USERNAME'));
Fnd_File.Put_line(fnd_File.Output,' ');
Fnd_File.Put_line(fnd_File.Output,'Submitted Responsibility name '||Fnd_profile.value('RESP_NAME'));
Fnd_File.Put_line(fnd_File.Output,' ');
Fnd_File.Put_line(fnd_File.Output,'Submission Date :'|| SYSDATE);
Exception
WHEN utl_file.invalid_operation THEN
  fnd_file.put_line(fnd_File.log,'invalid operation');
  utl_file.fclose_all;
WHEN utl_file.invalid_path THEN
  fnd_file.put_line(fnd_File.log,'invalid path');
  utl_file.fclose_all;
WHEN utl_file.invalid_mode THEN
  fnd_file.put_line(fnd_File.log,'invalid mode');
  utl_file.fclose_all;
WHEN utl_file.invalid_filehandle THEN
  fnd_file.put_line(fnd_File.log,'invalid filehandle');
  utl_file.fclose_all;
WHEN utl_file.read_error THEN
  fnd_file.put_line(fnd_File.log,'read error');
  utl_file.fclose_all;
WHEN utl_file.internal_error THEN
  fnd_file.put_line(fnd_File.log,'internal error');
  utl_file.fclose_all;
WHEN OTHERS THEN
  fnd_file.put_line(fnd_File.log,'other error');
  utl_file.fclose_all;
End MAHAMAD_INV_Out1;

PO Conversion

PO Conversion

CREATE OR REPLACE PROCEDURE PO_Int12(Errbuf  OUT VARCHAR2,
                                      Retcode OUT VARCHAR2) AS
CURSOR c1 IS SELECT * FROM XX_TEMP_HEADERS;
CURSOR c2 IS SELECT * FROM XX_TEMP_LINES;
l_vendor_id   number(10);
l_item        varchar2(150);
l_flag        varchar2(4) default 'A';
l_msg         varchar2(200);
l_site_code     varchar2(100);
l_curr_code     varchar2(10);
l_org_id     number(6);
BEGIN
DELETE FROM PO_HEADERS_INTERFACE;
DELETE FROM PO_LINES_INTERFACE;
COMMIT;
FOR x1 IN c1 LOOP
 BEGIN
   SELECT vendor_id
   INTO   l_vendor_id
   FROM   po_vendors
   WHERE  vendor_name = x1.VENDOR_NAME;
  EXCEPTION
   WHEN OTHERS THEN
    l_flag        := 'E';
    l_msg       := 'Vendor id is Not in SYSTEM';
Fnd_FIle.Put_line(Fnd_File.Log,'Error Occured'||l_msg);
 END;
--Vendor Site code  Validation
 begin
    select  vendor_site_code
    into    l_site_code
    from    po_vendor_sites_all
    where   vendor_site_code = x1.vendor_site_code;
    EXCEPTION
       WHEN OTHERS THEN
        l_flag        := 'E';
        l_msg         := 'Vendor Site Code is Not in SYSTEM';
Fnd_FIle.Put_line(Fnd_File.log,'Error Occured'||l_msg);
    END;
--End of Site Code Validation
--Currency Code Validation
  Begin
   select currency_code
   into   l_curr_code
   from   fnd_currencies
   where  currency_code = x1.CURRENCY_CODE;
EXCEPTION
      WHEN OTHERS THEN
        l_flag        := 'E';
        l_msg         := 'Currency Code is Invalid';
Fnd_FIle.Put_line(Fnd_File.Log,'Error Occured'||l_msg);
    END;
--End of te Currency Validation
--Operating Unit ID Validation
Begin
   select organization_id
   into   l_org_id
   from   hr_operating_units
   where  organization_id = x1.org_id;
   EXCEPTION
      WHEN OTHERS THEN
        l_flag        := 'E';
        l_msg         := 'Invalid Organization ID';
Fnd_FIle.Put_line(Fnd_File.Log,'Error Occured'||l_msg);
    END;
--End of the ORG ID Validation
  IF l_flag != 'E' THEN
     INSERT INTO po_headers_interface
   (
   INTERFACE_HEADER_ID
  ,BATCH_ID
  ,ACTION
   ,ORG_ID
  ,DOCUMENT_TYPE_CODE
  ,CURRENCY_CODE
  ,AGENT_NAME
  ,VENDOR_NAME
  ,VENDOR_SITE_CODE
  ,SHIP_TO_LOCATION
  ,BILL_TO_LOCATION
  ,creation_date
  ,APPROVAL_STATUS
  ,APPROVED_DATE
  ,FREIGHT_TERMS
)
VALUES
(
   x1.INTERFACE_HEADER_ID
  ,x1.batch_id
  ,x1.action
  ,x1.org_id
  ,x1.document_type_code
  ,x1.CURRENCY_CODE
  ,x1.AGENT_NAME
  ,x1.VENDOR_NAME
  ,x1.VENDOR_SITE_CODE
  ,x1.SHIP_TO_LOCATION
  ,x1.BILL_TO_LOCATION
  ,SYSDATE-10
  ,x1.APPROVAL_STATUS
  ,SYSDATE
 ,x1.FREIGHT_TERMS
 );
end if;
END LOOP;
FOR x2 IN c2  LOOP
l_flag := 'A';
--Item Validation
begin
  select segment1
  into   l_item
  from   mtl_system_items_b
     where  segment1        = x2.item
     AND    ORGANIZATION_ID = fnd_profile.value('ORG_ID');
exception
when others then
    l_flag        := 'E';
    l_msg       := 'Item is not valid Item';
Fnd_FIle.Put_line(Fnd_File.Log,'Error Occured'||l_msg);
 END;
--End of the Item Validation
 if  l_flag != 'E' then
 INSERT INTO PO_LINES_INTERFACE
 (
  INTERFACE_LINE_ID
  ,INTERFACE_HEADER_ID
  ,LINE_NUM
  ,SHIPMENT_NUM
  ,LINE_TYPE
  ,ITEM
  ,ITEM_DESCRIPTION
  ,item_id
  ,UOM_CODE
  ,QUANTITY
  ,UNIT_PRICE
  ,SHIP_TO_ORGANIZATION_CODE
  ,SHIP_TO_LOCATION
  ,NEED_BY_DATE
  ,PROMISED_DATE
  ,list_price_per_unit
)
VALUES
(
   x2.INTERFACE_LINE_ID
  ,x2.INTERFACE_HEADER_ID
  ,x2.LINE_NUM
  ,x2.SHIPMENT_NUM
  ,x2.LINE_TYPE
  ,x2.ITEM
  ,x2.ITEM_DESCRIPTION
  ,x2.item_id
  ,x2.UOM_CODE
  ,x2.QUANTITY,
   X2.UNIT_PRICE,
  X2.SHIP_TO_ORGANIZATION_CODE,
  X2.SHIP_TO_LOCATION,
  sysdate,
  sysdate,
  X2.LIST_PRICE_PER_UNIT);
END IF;
END LOOP;
COMMIT;
END PO_INT12;
/

PO Conversion

PO Conversion

CREATE OR REPLACE PROCEDURE PO_Int12(Errbuf  OUT VARCHAR2,
                                      Retcode OUT VARCHAR2) AS
CURSOR c1 IS SELECT * FROM XX_TEMP_HEADERS;
CURSOR c2 IS SELECT * FROM XX_TEMP_LINES;
l_vendor_id   number(10);
l_item        varchar2(150);
l_flag        varchar2(4) default 'A';
l_msg         varchar2(200);
l_site_code     varchar2(100);
l_curr_code     varchar2(10);
l_org_id     number(6);
BEGIN
DELETE FROM PO_HEADERS_INTERFACE;
DELETE FROM PO_LINES_INTERFACE;
COMMIT;
FOR x1 IN c1 LOOP
 BEGIN
   SELECT vendor_id
   INTO   l_vendor_id
   FROM   po_vendors
   WHERE  vendor_name = x1.VENDOR_NAME;
  EXCEPTION
   WHEN OTHERS THEN
    l_flag        := 'E';
    l_msg       := 'Vendor id is Not in SYSTEM';
Fnd_FIle.Put_line(Fnd_File.Log,'Error Occured'||l_msg);
 END;
--Vendor Site code  Validation
 begin
    select  vendor_site_code
    into    l_site_code
    from    po_vendor_sites_all
    where   vendor_site_code = x1.vendor_site_code;
    EXCEPTION
       WHEN OTHERS THEN
        l_flag        := 'E';
        l_msg         := 'Vendor Site Code is Not in SYSTEM';
Fnd_FIle.Put_line(Fnd_File.log,'Error Occured'||l_msg);
    END;
--End of Site Code Validation
--Currency Code Validation
  Begin
   select currency_code
   into   l_curr_code
   from   fnd_currencies
   where  currency_code = x1.CURRENCY_CODE;
EXCEPTION
      WHEN OTHERS THEN
        l_flag        := 'E';
        l_msg         := 'Currency Code is Invalid';
Fnd_FIle.Put_line(Fnd_File.Log,'Error Occured'||l_msg);
    END;
--End of te Currency Validation
--Operating Unit ID Validation
Begin
   select organization_id
   into   l_org_id
   from   hr_operating_units
   where  organization_id = x1.org_id;
   EXCEPTION
      WHEN OTHERS THEN
        l_flag        := 'E';
        l_msg         := 'Invalid Organization ID';
Fnd_FIle.Put_line(Fnd_File.Log,'Error Occured'||l_msg);
    END;
--End of the ORG ID Validation
  IF l_flag != 'E' THEN
     INSERT INTO po_headers_interface
   (
   INTERFACE_HEADER_ID
  ,BATCH_ID
  ,ACTION
   ,ORG_ID
  ,DOCUMENT_TYPE_CODE
  ,CURRENCY_CODE
  ,AGENT_NAME
  ,VENDOR_NAME
  ,VENDOR_SITE_CODE
  ,SHIP_TO_LOCATION
  ,BILL_TO_LOCATION
  ,creation_date
  ,APPROVAL_STATUS
  ,APPROVED_DATE
  ,FREIGHT_TERMS
)
VALUES
(
   x1.INTERFACE_HEADER_ID
  ,x1.batch_id
  ,x1.action
  ,x1.org_id
  ,x1.document_type_code
  ,x1.CURRENCY_CODE
  ,x1.AGENT_NAME
  ,x1.VENDOR_NAME
  ,x1.VENDOR_SITE_CODE
  ,x1.SHIP_TO_LOCATION
  ,x1.BILL_TO_LOCATION
  ,SYSDATE-10
  ,x1.APPROVAL_STATUS
  ,SYSDATE
 ,x1.FREIGHT_TERMS
 );
end if;
END LOOP;
FOR x2 IN c2  LOOP
l_flag := 'A';
--Item Validation
begin
  select segment1
  into   l_item
  from   mtl_system_items_b
     where  segment1        = x2.item
     AND    ORGANIZATION_ID = fnd_profile.value('ORG_ID');
exception
when others then
    l_flag        := 'E';
    l_msg       := 'Item is not valid Item';
Fnd_FIle.Put_line(Fnd_File.Log,'Error Occured'||l_msg);
 END;
--End of the Item Validation
 if  l_flag != 'E' then
 INSERT INTO PO_LINES_INTERFACE
 (
  INTERFACE_LINE_ID
  ,INTERFACE_HEADER_ID
  ,LINE_NUM
  ,SHIPMENT_NUM
  ,LINE_TYPE
  ,ITEM
  ,ITEM_DESCRIPTION
  ,item_id
  ,UOM_CODE
  ,QUANTITY
  ,UNIT_PRICE
  ,SHIP_TO_ORGANIZATION_CODE
  ,SHIP_TO_LOCATION
  ,NEED_BY_DATE
  ,PROMISED_DATE
  ,list_price_per_unit
)
VALUES
(
   x2.INTERFACE_LINE_ID
  ,x2.INTERFACE_HEADER_ID
  ,x2.LINE_NUM
  ,x2.SHIPMENT_NUM
  ,x2.LINE_TYPE
  ,x2.ITEM
  ,x2.ITEM_DESCRIPTION
  ,x2.item_id
  ,x2.UOM_CODE
  ,x2.QUANTITY,
   X2.UNIT_PRICE,
  X2.SHIP_TO_ORGANIZATION_CODE,
  X2.SHIP_TO_LOCATION,
  sysdate,
  sysdate,
  X2.LIST_PRICE_PER_UNIT);
END IF;
END LOOP;
COMMIT;
END PO_INT12;
/

SQL Loader Program

SQL Loader Program

SQL Loader

SQL LOADER is an Oracle utility used to load data into table given a datafile which has the records that need to be loaded. SQL*Loader takes data file, as well as a control file, to insert data into the table. When a Control file is executed, it can create Three (3) files called a

 log file, bad file or reject file, discard file.

• Log file tells you the state of the tables and indexes and the number of logical records already read from the input datafile. This information can be used to resume the load where it left off.
• Bad file or reject file gives you the records that were rejected because of formatting errors or because they caused Oracle errors.
• Discard file specifies the records that do not meet any of the loading criteria like when any of the WHEN clauses specified in the control file. These records differ from rejected records.

Structure of the data file:

The data file can be in fixed record format or variable record format.

Fixed Record Format would look like the below. In this case you give a specific position where the Control file can expect a data field:

7369 SMITH      CLERK        7902  12/17/1980         800                  

7499 ALLEN      SALESMAN  7698  2/20/1981           1600    

7521 WARD      SALESMAN  7698  2/22/1981           1250    

7566 JONES      MANAGER   7839  4/2/1981             2975             

7654 MARTIN    SALESMAN  7698  9/28/1981           1250    

7698 BLAKE      MANAGER   7839  5/1/1981             2850             

7782 CLARK      MANAGER   7839  6/9/1981             2450             

7788 SCOTT      ANALYST    7566  12/9/1982           3000             

7839 KING        PRESIDENT          11/17/1981         5000             

7844 TURNER    SALESMAN  7698  9/8/1981            1500           

7876 ADAMS     CLERK         7788  1/12/1983          1100             

7900 JAMES      CLERK         7698  12/3/1981          950              

7902 FORD        ANALYST     7566  12/3/1981          3000            

7934 MILLER     CLERK         7782  1/23/1982          1300            

Variable Record Format would like below where the data fields are separated by a delimiter.

Note: The Delimiter can be anything you like. In this case it is "|"

1196700|9|0|692.64

1378901|2|3900|488.62

1418700|2|2320|467.92

1418702|14|8740|4056.36

1499100|1|0|3.68

1632800|3|0|1866.66

1632900|1|70|12.64

1637600|50|0|755.5

Structure of a Control file:

Sample CTL file for loading a Variable record data file:

OPTIONS (SKIP = 1)   --The first row in the data file is skipped without loading

LOAD DATA

INFILE '$FILE'             -- Specify the data file path and name

APPEND                       -- type of loading (INSERT, APPEND, REPLACE, TRUNCATE

INTO TABLE "APPS"."BUDGET"   -- the table to be loaded into

FIELDS TERMINATED BY '|'           -- Specify the delimiter if variable format datafile

 OPTIONALLY ENCLOSED BY '"'   --the values of the data fields may be enclosed in "

TRAILING NULLCOLS     -- columns that are not present in the record treated as null

  (ITEM_NUMBER    "TRIM(:ITEM_NUMBER)", -- Can use all SQL functions on columns

  QTY                 DECIMAL EXTERNAL,

  REVENUE             DECIMAL EXTERNAL,

  EXT_COST            DECIMAL EXTERNAL TERMINATED BY WHITESPACE "(TRIM(:EXT_COST))"  ,

  MONTH           "to_char(LAST_DAY(ADD_MONTHS(SYSDATE,-1)),'DD-MON-YY')" ,

DIVISION_CODE    CONSTANT "AUD"  -- Can specify constant value instead of

                                                                          Getting value from datafile

   )

OPTION statement precedes the LOAD DATA statement. The OPTIONS parameter allows you to specify runtime arguments in the control file, rather than on the command line. The following arguments can be specified using the OPTIONS parameter.

SKIP = n -- Number of logical records to skip (Default 0)

LOAD = n -- Number of logical records to load (Default all)

ERRORS = n -- Number of errors to allow (Default 50)

ROWS = n   -- Number of rows in conventional path bind array or between direct path data saves (Default: Conventional Path 64, Direct path all)

BINDSIZE = n -- Size of conventional path bind array in bytes (System-dependent default)

SILENT = {FEEDBACK | ERRORS | DISCARDS | ALL} -- Suppress messages during run

                (header, feedback, errors, discards, partitions, all)

DIRECT = {TRUE | FALSE} --Use direct path (Default FALSE)

PARALLEL = {TRUE | FALSE} -- Perform parallel load (Default FALSE)

LOAD DATA statement is required at the beginning of the control file.

INFILE: INFILE keyword is used to specify location of the datafile or datafiles.

INFILE * specifies that the data is found in the control file and not in an external file. INFILE '$FILE', can be used to send the filepath and filename as a parameter when registered as a concurrent program.

INFILE   '/home/vision/kap/import2.csv' specifies the filepath and the filename.

Example where datafile is an external file:

LOAD DATA

INFILE   '/home/vision/kap/import2.csv'

INTO TABLE kap_emp

FIELDS TERMINATED BY ","

( emp_num, emp_name, department_num, department_name )

Example where datafile is in the Control file:

LOAD DATA

INFILE *

INTO TABLE kap_emp

FIELDS TERMINATED BY ","              

( emp_num, emp_name, department_num, department_name )

BEGINDATA

7369,SMITH,7902,Accounting

7499,ALLEN,7698,Sales

7521,WARD,7698,Accounting

7566,JONES,7839,Sales

7654,MARTIN,7698,Accounting

Example where file name and path is sent as a parameter when registered as a concurrent program

LOAD DATA

INFILE '$FILE'

INTO TABLE kap_emp

FIELDS TERMINATED BY ","              

( emp_num, emp_name, department_num, department_name )

TYPE OF LOADING:

INSERT   -- If the table you are loading is empty, INSERT can be used.

APPEND  -- If data already exists in the table, SQL*Loader appends the new rows to it. If data doesn't already exist, the new rows are simply loaded.

REPLACE -- All rows in the table are deleted and the new data is loaded

TRUNCATE -- SQL*Loader uses the SQL TRUNCATE command.

INTO TABLE is required to identify the table to be loaded into. In the above example INTO TABLE "APPS"."BUDGET", APPS refers to the Schema and BUDGET is the Table name.

FIELDS TERMINATED BY specifies how the data fields are terminated in the datafile.(If the file is Comma delimited or Pipe delimited etc)

OPTIONALLY ENCLOSED BY '"' specifies that data fields may also be enclosed by quotation marks.

TRAILING NULLCOLS clause tells SQL*Loader to treat any relatively positioned columns that are not present in the record as null columns.

Loading a fixed format data file:

LOAD DATA

INFILE 'sample.dat'

INTO TABLE emp

(      empno         POSITION(01:04)   INTEGER EXTERNAL,

       ename          POSITION(06:15)   CHAR,

       job            POSITION(17:25)   CHAR,

       mgr            POSITION(27:30)   INTEGER EXTERNAL,

       sal            POSITION(32:39)   DECIMAL EXTERNAL,

       comm           POSITION(41:48)   DECIMAL EXTERNAL,

       deptno         POSITION(50:51)   INTEGER EXTERNAL)

Steps to Run the SQL* LOADER from UNIX:

At the prompt, invoke SQL*Loader as follows:

      sqlldr USERID=scott/tiger CONTROL= LOG=

      name>

SQL*Loader loads the tables, creates the log file, and returns you to the system prompt. You can check the log file to see the results of running the case study.

Register as concurrent Program:

Place the Control file in $CUSTOM_TOP/bin.

Define the Executable. Give the Execution Method as SQL*LOADER.

Define the Program. Add the Parameter for FILENAME.

 Skip columns:

You can skip columns using the 'FILLER' option.

Load Data
--
--
--
TRAILING  NULLCOLS
(
name Filler,
Empno ,
sal
)

here the column name will be skipped.