How To Upload Supplier Additional Information




--------------------------------1. Data Template for Supplier Additional Info :---------------------------


SL_NO
VENDOR_NAME
VENDOR_SITE_CODE
EXCISE_DUTY_REG_NO
EXCISE_DUTY_RANGE
EXCISE_DUTY_DIVISION
EXCISE_DUTY_COMM
EC_CODE
TAX_NAME
ST_REG_NO
CST_REG_NO
VAT_REG_NO
SERVICE_TAX_REGNO
PAN_NO
TAN_NO
WARD_NO
SECTION_CODE
TDS_VENDOR_TYPE_LOOKUP_CODE
VERIFY_FLAG
NVL_SITE_VERIFY_FLAG
ERROR_MESSAGE



---------------------------2. Create staging table :------------------------------------


CREATE TABLE XX_SUPPLIERS_ADDINFO
(
  SL_NO                        NUMBER           NOT NULL,
  VENDOR_NAME                  VARCHAR2(240 BYTE) NOT NULL,
  VENDOR_SITE_CODE             VARCHAR2(15 BYTE) NOT NULL,
  EXCISE_DUTY_REG_NO           VARCHAR2(50 BYTE),
  EXCISE_DUTY_RANGE            VARCHAR2(50 BYTE),
  EXCISE_DUTY_DIVISION         VARCHAR2(50 BYTE),
  EXCISE_DUTY_COMM             VARCHAR2(50 BYTE),
  EC_CODE                      VARCHAR2(50 BYTE),
  TAX_NAME                     VARCHAR2(50 BYTE),
  ST_REG_NO                    VARCHAR2(50 BYTE),
  CST_REG_NO                   VARCHAR2(50 BYTE),
  VAT_REG_NO                   VARCHAR2(50 BYTE),
  SERVICE_TAX_REGNO            VARCHAR2(50 BYTE),
  PAN_NO                       VARCHAR2(30 BYTE),
  TAN_NO                       VARCHAR2(30 BYTE),
  WARD_NO                      VARCHAR2(30 BYTE),
  SECTION_CODE                 VARCHAR2(30 BYTE),
  TDS_VENDOR_TYPE_LOOKUP_CODE  VARCHAR2(30 BYTE),
  VERIFY_FLAG                  VARCHAR2(1 BYTE),
  NVL_SITE_VERIFY_FLAG         VARCHAR2(1 BYTE),
  ERROR_MESSAGE                VARCHAR2(100 BYTE)
)




---------------------------3. Create Procedure :------------------------------------

CREATE OR REPLACE PROCEDURE APPS.XXCONA_SUPPLER_ADDINFO(
    errbuf OUT VARCHAR2,
    retcode OUT NUMBER,
    P_ORG_ID IN NUMBER
    )
AS

/********************Suppliers Additional Information ***********************/
 
    L_VERIFY_FLAG       VARCHAR2(1);
    L_ERROR_MESSAGE     VARCHAR2(2500);
    L_VENDOR_ID         NUMBER(10);
    L_VENDOR_SITE_ID     NUMBER(10);
    L_ORG_ID             NUMBER(10);
    L_USER_ID         NUMBER(10);
    L_VENDOR_TYPE     VARCHAR2(50);
    L_TAX_ID         NUMBER(10);
    L_SECTION_TYPE VARCHAR2(25);
    L_SECTION_CODE VARCHAR2(30);
 
    L_CNT_NVL_SITE NUMBER(3);
    L_CNT_SITE NUMBER(3);


  CURSOR C_ADDINFO IS
        SELECT *
        FROM    XX_SUPPLIERS_ADDINFO
        WHERE   VERIFY_FLAG = 'N' ;

   BEGIN

     DBMS_OUTPUT.PUT_LINE ('Upload Program Start');
     FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Checking Start-----');
     FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Operating Unit-----'||P_ORG_ID);
        FOR c1 IN C_ADDINFO
        LOOP

        l_verify_flag := 'Y';
        l_error_message := null;
        l_cnt_nvl_site := 0;
        l_cnt_site := 0;


         BEGIN
            SELECT ORGANIZATION_ID INTO l_org_id
            FROM HR_OPERATING_UNITS
            WHERE ORGANIZATION_ID = P_ORG_ID; -- NAME like P_ORG_CODE;
         EXCEPTION
            WHEN OTHERS THEN
                l_verify_flag := 'N';
                l_error_message := l_error_message||' Operating Unit is not valid...';
          DBMS_OUTPUT.PUT_LINE ('Operating Unit is '||l_org_id);
          FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Operating Unit is '||l_org_id);
         END;

        l_user_id := APPS.FND_PROFILE.VALUE ('user_id');

         BEGIN
            SELECT VENDOR_ID,VENDOR_TYPE_LOOKUP_CODE
            INTO l_vendor_id,L_VENDOR_TYPE
            FROM APPS.AP_SUPPLIERS
            WHERE TRIM(upper(vendor_name)) = trim(upper(c1.vendor_name));
         EXCEPTION
            WHEN OTHERS Then
            l_verify_flag := 'N';
            l_error_message := l_error_message||'Vendor Name is not valid...';
         DBMS_OUTPUT.PUT_LINE ('Vendor ID '||l_vendor_id ||' Vendor Type '||l_vendor_type);
         FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Vendor ID '||l_vendor_id ||' Vendor Type '||l_vendor_type);
         END;


         BEGIN
            SELECT VENDOR_SITE_ID  INTO l_vendor_site_id
            FROM AP_SUPPLIER_SITES_ALL
            WHERE TRIM(upper(vendor_site_code)) = trim(upper(c1.vendor_site_code))
            AND   VENDOR_ID = l_vendor_id
            AND   ORG_ID = l_org_id;
         EXCEPTION
            WHEN OTHERS Then
            l_verify_flag := 'N';
            l_error_message := l_error_message||'Vendor Site not valid...';
         DBMS_OUTPUT.PUT_LINE ('Vendor Site ID '||l_vendor_site_id );
         FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Vendor Site ID '||l_vendor_site_id);
         END;


         BEGIN
           SELECT TAX_ID   INTO l_tax_id
           FROM JAI_CMN_TAXES_ALL
          WHERE upper(tax_name) = trim(upper(c1.tax_name))
           AND org_id = l_org_id
           AND nvl(end_date,sysdate) >= sysdate;
          EXCEPTION
                 WHEN NO_DATA_FOUND THEN
                    null;
                 WHEN OTHERS THEN
                    l_verify_flag := 'N';
                    l_error_message := l_error_message||'TDS Tax is not Valid...';
          DBMS_OUTPUT.PUT_LINE ('TAX ID '||l_tax_id );
          FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'TAX ID '||l_tax_id);
          END;

         BEGIN
            SELECT DISTINCT SECTION_TYPE, SECTION_CODE, VENDOR_TYPE_LOOKUP_CODE
            INTO l_section_type,     l_section_code,    l_vendor_type
            FROM JAI_AP_TDS_THHOLD_HDRS
           WHERE TRIM(UPPER(SECTION_CODE)) = UPPER(TRIM(C1.SECTION_CODE))
            AND TRIM(UPPER(VENDOR_TYPE_LOOKUP_CODE)) = UPPER(TRIM(C1.TDS_VENDOR_TYPE_LOOKUP_CODE))
            AND EXCEPTION_SETUP_FLAG = 'N';
         EXCEPTION
             WHEN NO_DATA_FOUND THEN
                null;
             WHEN OTHERS THEN
                l_verify_flag := 'N';
                l_error_message := l_error_message||'Tds Section or TDS Vendor Type is not valid...';
          DBMS_OUTPUT.PUT_LINE ('VENDOR_TYPE_LOOKUP_CODE '||l_vendor_type );
          FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'VENDOR_TYPE_LOOKUP_CODE '||l_vendor_type);
         END;


         BEGIN
                SELECT COUNT(*) INTO l_cnt_site
                FROM JAI_CMN_VENDOR_SITES
                WHERE vendor_site_id = l_vendor_site_id
                AND vendor_id = l_vendor_id ;
            IF l_cnt_site > 0 then
            l_verify_flag := 'N' ;
            l_error_message := l_error_message||'Site already existing in JAI_CMN_VENDOR_SITES...';
            l_cnt_site := 0;
            END IF;
         EXCEPTION
                 WHEN OTHERS THEN
                 l_cnt_site := 0;
          DBMS_OUTPUT.PUT_LINE ('NO of Site already existing '||l_cnt_site||l_error_message );
          FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'NO of Site already existing '||l_cnt_site||l_error_message );
         END;

         BEGIN
                SELECT COUNT(*) INTO L_CNT_SITE
                FROM JAI_AP_TDS_VENDOR_HDRS
                WHERE VENDOR_SITE_ID = l_vendor_site_id
                AND VENDOR_ID = l_vendor_id ;
            IF L_CNT_SITE > 0 THEN
            L_VERIFY_FLAG := 'N' ;
            l_error_message := l_error_message||'Site already existing in JAI_AP_TDS_VENDOR_HDRS...';
            END IF;
         EXCEPTION
                WHEN OTHERS THEN
                l_cnt_site := 0;
          DBMS_OUTPUT.PUT_LINE ('NO of Site already existing '||l_cnt_site||l_error_message  );
          FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'NO of Site already existing '||l_cnt_site||l_error_message );
         END;


          IF l_verify_flag <> 'N' THEN

                BEGIN
                    SELECT COUNT(*) INTO L_CNT_NVL_SITE
                    FROM JAI_CMN_VENDOR_SITES
                    WHERE VENDOR_SITE_ID = 0
                    AND VENDOR_ID = l_vendor_id ;
                EXCEPTION
                WHEN OTHERS THEN
                l_cnt_nvl_site := 0;
               
                END;
                DBMS_OUTPUT.PUT_LINE ('NO of Supplier upload '||L_CNT_NVL_SITE );
                FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'NO of Supplier upload '||L_CNT_NVL_SITE);

            IF l_cnt_nvl_site = 0 then

            BEGIN
                DBMS_OUTPUT.PUT_LINE ('Null Supplier site insert JAI_CMN_VENDOR_SITES ');
                FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Null Supplier site insert JAI_CMN_VENDOR_SITES');
            INSERT INTO JAI_CMN_VENDOR_SITES
            (
             VENDOR_ID
            ,VENDOR_SITE_ID
            ,creation_date
            ,created_by
            ,last_update_date
            ,last_updated_by
            ,last_update_login
            ,approved_invoice_flag
            ,EXCISE_DUTY_RANGE
            ,EXCISE_DUTY_DIVISION
            ,EXCISE_DUTY_REG_NO
            ,cst_reg_no
            ,st_reg_no
            ,vat_reg_no
            ,SERVICE_TAX_REGNO
            ,EXCISE_DUTY_COMM
            ,EC_CODE
            )
            VALUES
            (
            l_vendor_id
            ,0
            ,sysdate
            ,l_user_id
            ,sysdate
            ,l_user_id
            ,-1
            ,'Y'
            ,trim(c1.excise_duty_range)
            ,trim(c1.excise_duty_division)
            ,trim(c1.excise_duty_reg_no)
            ,trim(c1.CST_REG_NO)
            ,trim(c1.ST_REG_NO)
            ,trim(c1.VAT_REG_NO)
            ,trim(c1.SERVICE_TAX_REGNO)
            ,trim(c1.EXCISE_DUTY_COMM)
            ,NULL
            ) ;
            UPDATE XX_SUPPLIERS_ADDINFO
            SET nvl_site_verify_flag = 'Y'
            WHERE vendor_name = c1.vendor_name
            AND   VENDOR_SITE_CODE = c1.VENDOR_SITE_CODE;
            EXCEPTION
                WHEN OTHERS THEN
                    l_error_message := SQLERRM ;
                    l_verify_flag := 'N';
            DBMS_OUTPUT.PUT_LINE ('Error is '||l_error_message);
            FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Error is  '||l_error_message);
            UPDATE XX_SUPPLIERS_ADDINFO
            SET  nvl_site_verify_flag = 'N',
                 error_message = l_error_message
            WHERE vendor_name = c1.vendor_name
            AND   vendor_site_code = c1.vendor_site_code;
            END;

             IF l_verify_flag <> 'N' THEN

             BEGIN

             INSERT INTO JAI_AP_TDS_VENDOR_HDRS
                (
                    vendor_id
                    ,vendor_site_id
                    ,pan_no
                    ,creation_date
                    ,created_by
                    ,last_update_date
                    ,last_updated_by
                    ,last_update_login
                    ,section_type
                    ,section_code
                    ,confirm_pan_flag
                    ,tds_vendor_type_lookup_code
                    ,TAN_NO
                )
                VALUES
                (
                    l_vendor_id
                    ,0
                    ,trim(c1.pan_no)
                    ,sysdate
                    ,l_user_id
                    ,sysdate
                    ,l_user_id
                    ,-1
                    ,''
                    ,''
                    ,'Y'
                    ,l_vendor_type
                    ,trim(c1.TAN_NO)
                ) ;

             UPDATE XX_SUPPLIERS_ADDINFO
                    set nvl_site_verify_flag = 'Y'
                    where vendor_name = c1.vendor_name
                    and vendor_site_code = c1.vendor_site_code;
             EXCEPTION
                WHEN OTHERS THEN
                    l_error_message := SQLERRM;
                    l_verify_flag := 'N';
             DBMS_OUTPUT.PUT_LINE ('Error is '||l_error_message);
             FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Error is  '||l_error_message);
             UPDATE XX_SUPPLIERS_ADDINFO
                    set nvl_site_verify_flag = 'N',
                    error_message = l_error_message
                    where vendor_name = c1.vendor_name
                    and vendor_site_code = c1.vendor_site_code;

             END;

            END IF;

          END IF;



          BEGIN

            INSERT INTO JAI_CMN_VENDOR_SITES J
            (
            vendor_id
            ,vendor_site_id
            ,creation_date
            ,created_by
            ,last_update_date
            ,last_updated_by
            ,last_update_login
            ,approved_invoice_flag
            ,excise_duty_range
            ,excise_duty_division
            ,excise_duty_reg_no
            ,cst_reg_no
            ,st_reg_no
            ,vat_reg_no
            ,service_tax_regno
            ,EXCISE_DUTY_COMM
            ,EC_CODE
            )
            values
            (
             l_vendor_id
            ,l_vendor_site_id
            ,sysdate
            ,l_user_id
            ,sysdate
            ,l_user_id
            ,-1
            ,'Y'
            ,trim(c1.excise_duty_range)
            ,trim(c1.excise_duty_division)
            ,trim(c1.excise_duty_reg_no)
            ,trim(c1.cst_reg_no)
            ,trim(c1.st_reg_no)
            ,trim(c1.vat_reg_no)
            ,trim(c1.service_tax_regno)
            ,trim(c1.EXCISE_DUTY_COMM)
            ,trim(c1.EC_CODE)
            ) ;

            UPDATE XX_SUPPLIERS_ADDINFO
                set verify_flag = 'Y'
                where vendor_name = c1.vendor_name
                and vendor_site_code = c1.vendor_site_code;
            EXCEPTION
                WHEN OTHERS THEN
                l_error_message := SQLERRM;
                l_verify_flag := 'N';
            DBMS_OUTPUT.PUT_LINE ('Error is '||l_error_message);
            FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Error is  '||l_error_message);
            UPDATE XX_SUPPLIERS_ADDINFO
                set verify_flag = 'N',
                error_message = l_error_message
                where vendor_name = c1.vendor_name
                and vendor_site_code = c1.vendor_site_code;

          END;


          BEGIN

            insert into JAI_AP_TDS_VENDOR_HDRS
            (
            tax_id
            ,vendor_id
            ,vendor_site_id
            ,pan_no
            ,creation_date
            ,created_by
            ,last_update_date
            ,last_updated_by
            ,last_update_login
            ,section_type
            ,section_code
            ,confirm_pan_flag
            ,tds_vendor_type_lookup_code
            ,TAN_NO
            )
            values
            (
            l_tax_id
            ,l_vendor_id
            ,l_vendor_site_id
            ,trim(c1.pan_no)
            ,sysdate
            ,l_user_id
            ,sysdate
            ,l_user_id
            ,-1
            ,l_section_type
            ,l_section_code
            ,'Y'
            ,l_vendor_type
            ,trim(c1.TAN_NO)
            ) ;

            UPDATE XX_SUPPLIERS_ADDINFO
                set verify_flag = 'Y'
                where vendor_name = c1.vendor_name
                and vendor_site_code = c1.vendor_site_code;
            EXCEPTION
                WHEN OTHERS THEN
                l_error_message := SQLERRM;
                l_verify_flag := 'N';
            DBMS_OUTPUT.PUT_LINE ('Error is '||l_error_message);
            FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Error is  '||l_error_message);
            UPDATE XX_SUPPLIERS_ADDINFO
                set verify_flag = 'N',
                error_message = l_error_message
                where vendor_name = c1.vendor_name
                and vendor_site_code = c1.vendor_site_code;

          END;


         ELSE

            DBMS_OUTPUT.PUT_LINE ('NO of Supplier upload '||L_CNT_NVL_SITE );
            UPDATE XX_SUPPLIERS_ADDINFO
            set verify_flag = 'N',
            error_message = l_error_message
            where vendor_name = c1.vendor_name
            and vendor_site_code = c1.vendor_site_code;


         END IF;

        Commit;

        END LOOP;
        DBMS_OUTPUT.PUT_LINE ('Upload Program End');

    END XXCONA_SUPPLER_ADDINFO;
/