Protected by Copyscape Web Copyright Protection Software

Pages

Search This Blog

Thursday, June 20, 2013

Supplier Interface Script in Oracle apps Part 4




         IF ln_vendor_site_num = 0
         THEN
            BEGIN
               count_new_addl_records := NVL (count_new_addl_records, 0) + 1;

               --COMENTED BY SIVA ON 26-DEC-2012
               /*INSERT INTO JA_IN_PO_VENDOR_SITES (vendor_id,
                                                  vendor_site_id,
                                                  excise_duty_range,
                                                  excise_duty_division,
                                                  excise_duty_reg_no,
                                                  excise_duty_zone,
                                                  excise_duty_region,
                                                  excise_duty_circle,
                                                  ec_code,
                                                  cst_reg_no,
                                                  st_reg_no,
                                                  approved_invoice_flag,
                                                  vendor_type,
                                                  override_flag,
                                                  vat_reg_no,
                                                  service_tax_regno,
                                                  service_type_code,
                                                  tax_category_list,
                                                  creation_date,
                                                  created_by,
                                                  last_update_date,
                                                  last_updated_by)
                    VALUES (vendor_rec.vendor_id,
                            vendor_rec.vendor_site_id,
                            NULL,                 --vendor_rec.exc_duty_range,
                            NULL,              --vendor_rec.exc_duty_division,
                            TRIM (vendor_rec.excise_registration_no),
                            NULL,                  --vendor_rec.exc_duty_zone,
                            NULL,                --vendor_rec.exc_duty_region,
                            NULL,                --vendor_rec.exc_duty_circle,
                            NULL,                        --vendor_rec.ec_code,
                            TRIM (vendor_rec.central_sales_tax_no),
                            NULL,            --vendor_rec.lst_registration_no,
                            'Y',
                            NULL,         --trim(vendor_rec.type_of_assessee),
                            'Y',
                            TRIM (vendor_rec.value_Added_tax_no),
                            TRIM (vendor_rec.service_tax_no),
                            TRIM (lc_service_code),
                            NULL,              --vendor_rec.tax_category_list,
                            SYSDATE,
                            l_created_by,
                            SYSDATE,
                            l_created_by);*/
               --ADDED BY XXXX
               INSERT INTO JAI_CMN_VENDOR_SITES (vendor_id,
                                                 vendor_site_id,
                                                 excise_duty_range,
                                                 excise_duty_division,
                                                 excise_duty_reg_no,
                                                 excise_duty_zone,
                                                 excise_duty_region,
                                                 excise_duty_circle,
                                                 ec_code,
                                                 cst_reg_no,
                                                 st_reg_no,
                                                 approved_invoice_flag,
                                                 vendor_type,
                                                 override_flag,
                                                 vat_reg_no,
                                                 service_tax_regno,
                                                 service_type_code,
                                                 tax_category_list,
                                                 creation_date,
                                                 created_by,
                                                 last_update_date,
                                                 last_updated_by)
                    VALUES (vendor_rec.vendor_id,
                            vendor_rec.vendor_site_id,
                            NULL,                 --vendor_rec.exc_duty_range,
                            NULL,              --vendor_rec.exc_duty_division,
                            TRIM (vendor_rec.excise_registration_no),
                            NULL,                  --vendor_rec.exc_duty_zone,
                            NULL,                --vendor_rec.exc_duty_region,
                            NULL,                --vendor_rec.exc_duty_circle,
                            NULL,                        --vendor_rec.ec_code,
                            TRIM (vendor_rec.central_sales_tax_no),
                            NULL,            --vendor_rec.lst_registration_no,
                            'Y',
                            NULL,         --trim(vendor_rec.type_of_assessee),
                            'Y',
                            TRIM (vendor_rec.value_Added_tax_no),
                            TRIM (vendor_rec.service_tax_no),
                            TRIM (lc_service_code),
                            NULL,              --vendor_rec.tax_category_list,
                            SYSDATE,
                            l_created_by,
                            SYSDATE,
                            l_created_by);

               gn_uploaded_rec := NVL (gn_uploaded_rec, 0) + 1;
               count_ins_addl_records := NVL (count_ins_addl_records, 0) + 1;
               ln_insert_flag := ln_insert_flag + 1;

               UPDATE XXST_AP_SUPP_INT_STG
                  SET STATUS = 'P'
                WHERE     supplier_name = vendor_rec.supplier_name
                      AND Batch_No = vendor_rec.Batch_No
                      AND SITE_NAME = vendor_rec.SITE_NAME;

               --WHERE RECORD_ID=vendor_rec.RECORD_ID;
               COMMIT;
            EXCEPTION
               WHEN OTHERS
               THEN
                  lc_error_flag4 := 'Y';
                  lc_error_message :=
                     ' Error Occured While inserting into JA_IN_PO_VENDOR_SITES tables  ';
                  fnd_file.
                   put_line (fnd_file.LOG,
                             'ErrNo:44' || lc_error_message || SQLERRM);
                  XXST_INT_UTILITY_PKG.
                   GENERATE_ERROR_LOG_PRC (pn_batch_no,
                                           ln_conc_req_id,
                                           'XXST_AP_SUPP_INT_STG',
                                           'JA_IN_PO_VENDOR_SITES',
                                           'JA_IN_PO_VENDOR_SITES',
                                           vendor_rec.supplier_name,
                                           vendor_rec.vendor_id,
                                           lc_error_message,
                                           vendor_rec.RECORD_ID);

                  UPDATE XXST_AP_SUPP_INT_STG
                     SET status = 'E', error_message = lc_error_message
                   WHERE     supplier_name = vendor_rec.supplier_name
                         AND Batch_No = vendor_rec.Batch_No
                         AND site_name = vendor_rec.site_name;

                  fnd_file.
                   put_line (fnd_file.LOG, lc_error_message);
                  COMMIT;
            END;

            IF l_vendor_tds IS NOT NULL
            THEN
               IF vendor_rec.pan_no IS NOT NULL
                  AND LENGTH (TRIM (vendor_rec.pan_no)) = 10
               THEN
                  BEGIN
                     --COMENTED BY SIVA ON 26-DEC-2012
                     /* INSERT
                        INTO ja_in_vendor_tds_info_hdr (
                                vendor_id,
                                vendor_site_id,
                                pan_no,
                                tan_no,
                                ward_no,
                                creation_date,
                                created_by,
                                last_update_date,
                                last_updated_by,
                                last_update_login,
                                confirm_pan_flag,
                                tds_vendor_type_lookup_code)
                      VALUES (vendor_rec.vendor_id,
                              vendor_rec.vendor_site_id,
                              vendor_rec.pan_no,
                              NULL,                        --vendor_rec.tan_no,
                              NULL,                       --vendor_rec.ward_no,
                              SYSDATE,
                              l_created_by,
                              SYSDATE,
                              l_created_by,
                              l_created_by,
                              'Y',
                              l_vendor_tds);*/

                     --ADDED BY XXXXX

                     INSERT
                       INTO jai_ap_tds_vendor_hdrs (
                               vendor_id,
                               vendor_site_id,
                               pan_no,
                               tan_no,
                               ward_no,
                               creation_date,
                               created_by,
                               last_update_date,
                               last_updated_by,
                               last_update_login,
                               confirm_pan_flag,
                               tds_vendor_type_lookup_code)
                     VALUES (vendor_rec.vendor_id,
                             vendor_rec.vendor_site_id,
                             vendor_rec.pan_no,
                             NULL,                        --vendor_rec.tan_no,
                             NULL,                       --vendor_rec.ward_no,
                             SYSDATE,
                             l_created_by,
                             SYSDATE,
                             l_created_by,
                             l_created_by,
                             'Y',
                             l_vendor_tds);

                     ln_insert_flag := ln_insert_flag + 1;

                     UPDATE XXST_AP_SUPP_INT_STG
                        SET status = 'P'
                      WHERE     supplier_name = vendor_rec.supplier_name
                            AND Batch_No = vendor_rec.Batch_No
                            AND site_name = vendor_rec.site_name;

                     COMMIT;
                  EXCEPTION
                     WHEN OTHERS
                     THEN
                        UPDATE XXST_AP_SUPP_INT_STG
                           SET status = 'E', error_message = lc_error_message
                         WHERE     supplier_name = vendor_rec.supplier_name
                               AND Batch_No = vendor_rec.Batch_No
                               AND site_name = vendor_rec.site_name;

                        lc_error_flag4 := 'Y';
                        lc_error_message :=
                           'Error Occured While inserting into JA_IN_VENDOR_TDS_INFO_HDR tables ';
                        fnd_file.
                         put_line (fnd_file.LOG,
                                   'ErrNo:45' || lc_error_message);
                        COMMIT;
                  END;
               END IF;                     --IF  vendor_rec.pan_no IS NOT NULL
            END IF;                         --IF l_vendor_tds IS NOT NULL THEN
         ELSE
            BEGIN
               UPDATE XXST_AP_SUPP_INT_STG
                  SET status = 'E',
                      error_message =
                         'Supplier Additional Info Assignment is Already exists'
                WHERE     supplier_name = vendor_rec.supplier_name
                      AND Batch_No = vendor_rec.Batch_No
                      AND site_name = vendor_rec.site_name;
            --  lc_error_flag4 := 'Y';
            -- lc_error_message := 'Error Occured While inserting into JA_IN_VENDOR_TDS_INFO_HDR tables ';
            --  fnd_file.put_line(fnd_file.LOG,'ErrNo:45'||lc_error_message);
            EXCEPTION
               WHEN OTHERS
               THEN
                  fnd_file.
                   put_line (fnd_file.LOG,
                             'ErrNo:49' || lc_error_message);
            END;

            COMMIT;
         END IF;                         --IF NVL(ln_vendor_site_num,0)=0 THEN

         COMMIT;
         ln_insert_flag := 0;
      END LOOP;

      COMMIT;
      Ln_Total_Records_Processed := 0;

      BEGIN
         SELECT COUNT (*)
           INTO Ln_Total_Records_Processed
           FROM XXST_AP_SUPP_INT_STG tkapi,
                po_vendors pv,
                po_vendor_sites_all pvsa
          WHERE tkapi.supplier_name = pv.vendor_name AND tkapi.status = 'P'
                AND (pv.vendor_id = pvsa.vendor_id
                     AND pvsa.vendor_site_code = tkapi.site_name)
                AND Batch_No = p_batch_no;
      EXCEPTION
         WHEN OTHERS
         THEN
            fnd_file.
             put_line (fnd_file.LOG, 'Error while Getting Total Count');
      END;

      fnd_file.
       put_line (
         fnd_file.OUTPUT,
         'Total No of Records Processed ' || Ln_Total_Records_Processed);

      FOR j IN lcur_ja_in1
      LOOP
         EXIT WHEN lcur_ja_in1%NOTFOUND;
         Ln_vendor_id1 := NULL;
         Ln_vendor_site_id1 := NULL;

         --VENDOR ID VALIDATION
         BEGIN
            SELECT vendor_id
              INTO Ln_vendor_id1
              FROM ap_suppliers pv                             --po_vendors pv
             WHERE 1 = 1                    --pv.vendor_name = j.supplier_name
                        AND pv.vendor_name = j.supplier_name;
         EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
               UPDATE XXST_AP_SUPP_INT_STG
                  SET status = 'E',
                      error_message = 'Supplier Does Not Exists'
                WHERE     supplier_name = j.supplier_name
                      AND Batch_No = j.Batch_No
                      AND site_name = j.site_name;
            WHEN OTHERS
            THEN
               NULL;
         END;

         IF Ln_vendor_id1 IS NOT NULL
         THEN
            BEGIN
               SELECT vendor_site_id
                 INTO Ln_vendor_site_id1
                 FROM po_vendor_sites_all pvsa
                WHERE pvsa.vendor_id = Ln_vendor_id1
                      AND pvsa.vendor_site_code = j.site_name;
            EXCEPTION
               WHEN NO_DATA_FOUND
               THEN
                  UPDATE XXST_AP_SUPP_INT_STG
                     SET status = 'E',
                         error_message = 'Supplier Site Does Not Exists'
                   WHERE     supplier_name = j.supplier_name
                         AND Batch_No = j.Batch_No
                         AND site_name = j.site_name;
               WHEN OTHERS
               THEN
                  NULL;
            END;
         END IF;

         COMMIT;
      END LOOP;

      Ln_Total_Records_Errored := 0;

      BEGIN
         SELECT COUNT (*)
           INTO Ln_Total_Records_Errored
           FROM XXST_AP_SUPP_INT_STG tkapi
          WHERE tkapi.status = 'E' AND Batch_No = p_batch_no;
      EXCEPTION
         WHEN OTHERS
         THEN
            fnd_file.
             put_line (fnd_file.LOG, 'Error while Getting Total Count');
      END;

      fnd_file.
       put_line (fnd_file.OUTPUT,
                 'Total No of Records Errored ' || Ln_Total_Records_Errored);
   END ap_localization_data_creation;

   FUNCTION get_field (p_line        IN VARCHAR2,
                       p_delimiter   IN VARCHAR2,
                       p_field_no    IN NUMBER)
      RETURN VARCHAR2
   IS
      l_start        NUMBER := 0;
      l_end          NUMBER := 0;
      lc_delimiter   VARCHAR2 (10);
   BEGIN
      lc_delimiter := p_delimiter;

      /*****-- Determine start position--*****/
      IF p_field_no = 1
      THEN
         l_start := 0;
      ELSE
         l_start :=
            INSTR (p_line,
                   p_delimiter,
                   1,
                   (p_field_no - 1));

         IF l_start = 0
         THEN
            RETURN NULL;
         END IF;
      END IF;

      /*****-- Determine end position--*****/
      l_end :=
         INSTR (p_line,
                p_delimiter,
                1,
                p_field_no);

      IF l_end = 0
      THEN
         l_end := LENGTH (p_line) + 1;
      END IF;

      /*****-- Extract the field data --*****/
      IF (l_end - l_start) = 1
      THEN
         RETURN NULL;
      ELSE
         RETURN SUBSTR (p_line, (l_start + 1), ( (l_end - l_start) - 1));
      END IF;
   EXCEPTION
      WHEN OTHERS
      THEN
         RETURN NULL;
   END get_field;

   PROCEDURE ap_supplier_upload (Errbuf           OUT VARCHAR2,
                                 Retcode          OUT NUMBER,
                                 p_file_name   IN     VARCHAR2,
                                 P_LINE_NUM    IN     NUMBER)
   IS
      reqfiletype                      UTL_FILE.FILE_TYPE;
      req_line                         VARCHAR2 (3000);
      req_delim                        VARCHAR2 (1) := ',';
      rec_count                        NUMBER;
      v_File_Name                      VARCHAR2 (200) := P_File_Name;
      v_date_processed                 DATE;                            -- (Q)
      ln_user_id                       NUMBER := FND_GLOBAL.USER_ID;
      ln_resp_id                       NUMBER := FND_GLOBAL.RESP_ID;
      ln_resp_app_id                   NUMBER := FND_GLOBAL.RESP_APPL_ID;
      v_request_id                     NUMBER := 0;
      ln_batch_id                      NUMBER;
      Lc_Transaction_type              VARCHAR2 (500);
      ln_line_no                       NUMBER;
      ln_record_id                     NUMBER;
      lc_batch_no                      VARCHAR2 (50);
      lc_operating_unit                VARCHAR2 (25);
      ln_supplier_id                   NUMBER;
      lc_supplier_name                 VARCHAR2 (240);
      lc_address1                      VARCHAR2 (240);
      lc_address2                      VARCHAR2 (240);
      lc_address3                      VARCHAR2 (240);
      lc_address4                      VARCHAR2 (240);
      lc_city                          VARCHAR2 (25);
      lc_state                         VARCHAR2 (150);
      lc_pin_code                      VARCHAR2 (20);
      lc_country                       VARCHAR2 (150);
      lc_site_name                     VARCHAR2 (15);
      lc_alternate_supplier_site_any   VARCHAR2 (320);
      lc_alternate_supp_address_any    VARCHAR2 (560);
      lc_first_name                    VARCHAR2 (50);
      lc_last_name                     VARCHAR2 (50);
      lc_std_isd_tel_code              VARCHAR2 (10);
      lc_telephone_no                  VARCHAR2 (50);
      lc_std_isd_fax_code              VARCHAR2 (10);
      lc_fax_no                        VARCHAR2 (15);
      lc_mobile_no                     VARCHAR2 (15);
      lc_email_id                      VARCHAR2 (200);
      lc_url                           VARCHAR2 (2000);
      lc_central_sales_tax_no          VARCHAR2 (50);
      lc_value_added_tax_no            VARCHAR2 (50);
      lc_service_tax_no                VARCHAR2 (50);
      lc_excise_registration_no        VARCHAR2 (50);
      lc_pan_no                        VARCHAR2 (30);
      lc_edible_oil_licence_no         VARCHAR2 (150);
      lc_trade_licence_no              VARCHAR2 (150);
      lc_type_of_assessee              VARCHAR2 (150);
      lc_small_scale_industry_yn       VARCHAR2 (10);
      lc_type_of_supplier              VARCHAR2 (30);
      lc_isuplier_flag                 VARCHAR2 (5);
      lc_payment_terms_no_of_days      VARCHAR2 (50);
      lc_one_time_supplier_yn          VARCHAR2 (1);
      lc_currency_code                 VARCHAR2 (10);
      lc_supplier_bank_name            VARCHAR2 (60);
      lc_supplier_bank_branch_name     VARCHAR2 (60);
      lc_bank_branch_address1          VARCHAR2 (35);
      lc_bank_branch_address2          VARCHAR2 (35);
      lc_bank_branch_address3          VARCHAR2 (35);
      lc_bank_branch_address4          VARCHAR2 (35);
      lc_bank_city                     VARCHAR2 (50);
      lc_bank_state                    VARCHAR2 (25);
      lc_bank_country                  VARCHAR2 (25);
      lc_bank_pin_code                 VARCHAR2 (20);
      lc_ifsc_no                       VARCHAR2 (30);
      lc_supplier_bank_acctount_no     VARCHAR2 (80);
      lc_liability_account             VARCHAR2 (25);
      lc_prepayment_account            VARCHAR2 (25);
      lc_invoice_initiator_email       VARCHAR2 (150);
      lc_payment_term_email            VARCHAR2 (150);
      ln_org_id                        NUMBER;
      ln_set_of_book_id                NUMBER;
      ln_request_id                    NUMBER;
      lc_status                        VARCHAR2 (50);
   BEGIN
      v_file_name := p_file_name;
      -----------------------------------------------------------------
      Ln_batch_id := 0;
      ln_line_no := 0;

      BEGIN
         SELECT xxst_s.NEXTVAL INTO ln_batch_id FROM DUAL;
      EXCEPTION
         WHEN OTHERS
         THEN
            FND_FILE.
             PUT_LINE (FND_FILE.OUTPUT, ' Error While Getting Batch id');
      END;

      -----------------------------------------------------------------
      reqfiletype := UTL_FILE.FOPEN ('XXFTAL_AP_SUPP_DIR1', v_File_Name, 'r');

      LOOP
         ln_line_no := ln_line_no + 1;

         BEGIN
            UTL_FILE.GET_LINE (reqfiletype, req_line);
         EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
               EXIT;
         END;

         IF ln_line_no > p_line_num
         THEN
            ---------------------------------------------------------------------------------------------
            --- Mapping with csv fields
            -----------------------------------------------------------------------------------------------
            --Sl Numner
            BEGIN
               ln_record_id := TRIM (Get_Field (req_line, req_delim, 1)); -- (A)
            EXCEPTION
               WHEN OTHERS
               THEN
                  RAISE_APPLICATION_ERROR (-20057, 'Sl Number' || SQLERRM);
            END;

            -----------------------------------------------------------------------------------------------
            --Operating Unit
            BEGIN
               lc_operating_unit := TRIM (Get_Field (req_line, req_delim, 2)); -- (B)
            EXCEPTION
               WHEN OTHERS
               THEN
                  RAISE_APPLICATION_ERROR (-20057,
                                           'Operating Unit' || SQLERRM);
            END;

            -----------------------------------------------------------------
            -- Supplier Name
            BEGIN
               lc_supplier_name := TRIM (Get_Field (req_line, req_delim, 3)); -- (C)
            EXCEPTION
               WHEN OTHERS
               THEN
                  RAISE_APPLICATION_ERROR (-20057,
                                           'Supplier Name ' || SQLERRM);
            END;

            -----------------------------------------------------------------
            -- Address1
            BEGIN
               lc_address1 := TRIM (Get_Field (req_line, req_delim, 4)); -- (D)
            EXCEPTION
               WHEN OTHERS
               THEN
                  RAISE_APPLICATION_ERROR (-20057, 'Address1 ' || SQLERRM);
            END;

            -----------------------------------------------------------------
            ---- Address2
            BEGIN
               lc_address2 := TRIM (Get_Field (req_line, req_delim, 5)); -- (E)
            EXCEPTION
               WHEN OTHERS
               THEN
                  RAISE_APPLICATION_ERROR (-20057, 'Address2 ' || SQLERRM);
            END;

            ----------------------------------------------------------------
            --Address3
            BEGIN
               lc_Address3 := TRIM (Get_Field (req_line, req_delim, 6)); -- (F)
            EXCEPTION
               WHEN OTHERS
               THEN
                  RAISE_APPLICATION_ERROR (-20057, 'Address3  ' || SQLERRM);
            END;

            -----------------------------------------------------------------
            --Address4
            BEGIN
               lc_Address4 := TRIM (Get_Field (req_line, req_delim, 7)); -- (G)
            EXCEPTION
               WHEN OTHERS
               THEN
                  RAISE_APPLICATION_ERROR (-20057, 'Address4  ' || SQLERRM);
            END;

            -----------------------------------------------------------------
            -- lc_city
            BEGIN
               lc_city := TRIM (Get_Field (req_line, req_delim, 8));    -- (H)
            EXCEPTION
               WHEN OTHERS
               THEN
                  RAISE_APPLICATION_ERROR (-20057, 'City  ' || SQLERRM);
            END;

            -----------------------------------------------------------------
            --State
            BEGIN
               lc_state := TRIM (Get_Field (req_line, req_delim, 9));   -- (I)
            EXCEPTION
               WHEN OTHERS
               THEN
                  RAISE_APPLICATION_ERROR (-20057, 'State ' || SQLERRM);
            END;

            -----------------------------------------------------------------
            -- Pin Code
            BEGIN
               lc_pin_code := TRIM (Get_Field (req_line, req_delim, 10)); -- (J)
            EXCEPTION
               WHEN OTHERS
               THEN
                  RAISE_APPLICATION_ERROR (-20057, 'Pin Code  ' || SQLERRM);
            END;

            -----------------------------------------------------------------
            --lc_country
            BEGIN
               lc_country := TRIM (Get_Field (req_line, req_delim, 11)); -- (K)
            EXCEPTION
               WHEN OTHERS
               THEN
                  RAISE_APPLICATION_ERROR (-20057, 'Country ' || SQLERRM);
            END;

            -----------------------------------------------------------------
            --Site Name
            BEGIN
               lc_site_name := TRIM (Get_Field (req_line, req_delim, 12)); -- (L)
            EXCEPTION
               WHEN OTHERS
               THEN
                  RAISE_APPLICATION_ERROR (-20057, 'Site Name  ' || SQLERRM);
            END;

            -----------------------------------------------------------------
            --Alternate Supplier Site
            BEGIN
               lc_alternate_supplier_site_any :=
                  TRIM (Get_Field (req_line, req_delim, 13));           -- (M)
            EXCEPTION
               WHEN OTHERS
               THEN
                  RAISE_APPLICATION_ERROR (
                     -20057,
                     'Alternate Supplier Site' || SQLERRM);
            END;

            -----------------------------------------------------------------
            --alternate supplier address
            BEGIN
               lc_alternate_supp_address_any :=
                  TRIM (Get_Field (req_line, req_delim, 14));           -- (N)
            EXCEPTION
               WHEN OTHERS
               THEN
                  RAISE_APPLICATION_ERROR (
                     -20057,
                     'Alternate Supplier Address ' || SQLERRM);
            END;

            -----------------------------------------------------------------
            --First Name
            BEGIN
               lc_first_name := TRIM (Get_Field (req_line, req_delim, 15)); -- (O)
            EXCEPTION
               WHEN OTHERS
               THEN
                  RAISE_APPLICATION_ERROR (-20057, 'First Name  ' || SQLERRM);
            END;

            -----------------------------------------------------------------
            --Last Name
            BEGIN
               lc_last_name := TRIM (Get_Field (req_line, req_delim, 16)); -- (O)
            EXCEPTION
               WHEN OTHERS
               THEN
                  RAISE_APPLICATION_ERROR (-20057, 'Last Name  ' || SQLERRM);
            END;

            -----------------------------------------------------------------
            --STD / ISD  Tel Code
            BEGIN
               lc_std_isd_tel_code :=
                  TRIM (Get_Field (req_line, req_delim, 17));           -- (P)
            EXCEPTION
               WHEN OTHERS
               THEN
                  RAISE_APPLICATION_ERROR (-20057,
                                           'STD / ISD Tel Code ' || SQLERRM);
            END;

            -----------------------------------------------------------------
            ----Telephone No
            BEGIN
               lc_telephone_no := TRIM (Get_Field (req_line, req_delim, 18)); -- (Q)
            EXCEPTION
               WHEN OTHERS
               THEN
                  RAISE_APPLICATION_ERROR (-20057,
                                           'Telephone No ' || SQLERRM);
            END;

            -----------------------------------------------------------------
            --STD / ISD  Fax Code
            BEGIN
               lc_std_isd_fax_code :=
                  TRIM (Get_Field (req_line, req_delim, 19));           -- (P)
            EXCEPTION
               WHEN OTHERS
               THEN
                  RAISE_APPLICATION_ERROR (-20057,
                                           'STD / ISD Fax Code ' || SQLERRM);
            END;

            -----------------------------------------------------------------
            ---- Fax No
            BEGIN
               lc_fax_no := TRIM (Get_Field (req_line, req_delim, 20)); -- (R)
            EXCEPTION
               WHEN OTHERS
               THEN
                  RAISE_APPLICATION_ERROR (-20057, 'Fax No  ' || SQLERRM);
            END;

            -----------------------------------------------------------------
            ----Mobile No
            BEGIN
               lc_mobile_no := TRIM (Get_Field (req_line, req_delim, 21)); -- (S)
            EXCEPTION
               WHEN OTHERS
               THEN
                  RAISE_APPLICATION_ERROR (-20057, 'Mobile No ' || SQLERRM);
            END;

            -----------------------------------------------------------------
            ----E Mail Id
            BEGIN
               lc_email_id := TRIM (Get_Field (req_line, req_delim, 22)); -- (T)
            EXCEPTION
               WHEN OTHERS
               THEN
                  RAISE_APPLICATION_ERROR (-20057, 'E Mail Id ' || SQLERRM);
            END;

            -----------------------------------------------------------------
            -----------------------------------------------------------------
            ----URL
            BEGIN
               lc_url := TRIM (Get_Field (req_line, req_delim, 23));    -- (T)
            EXCEPTION
               WHEN OTHERS
               THEN
                  RAISE_APPLICATION_ERROR (-20057,
                                           'URL Web Address ' || SQLERRM);
            END;

            -----------------------------------------------------------------
            ---- CST Reg No
            BEGIN
               lc_central_sales_tax_no :=
                  TRIM (Get_Field (req_line, req_delim, 24));           -- (U)
            EXCEPTION
               WHEN OTHERS
               THEN
                  RAISE_APPLICATION_ERROR (-20057,
                                           'CST Reg. No  ' || SQLERRM);
            END;

            -----------------------------------------------------------------
            ---- VAT Reg No
            BEGIN
               lc_value_added_tax_no :=
                  TRIM (Get_Field (req_line, req_delim, 25));           -- (V)
            EXCEPTION
               WHEN OTHERS
               THEN
                  RAISE_APPLICATION_ERROR (-20057,
                                           'VAT Reg. No  ' || SQLERRM);
            END;

            -----------------------------------------------------------------
            ----Service Tax No
            BEGIN
               lc_service_tax_no := TRIM (Get_Field (req_line, req_delim, 26)); -- (W)
            EXCEPTION
               WHEN OTHERS
               THEN
                  RAISE_APPLICATION_ERROR (-20057,
                                           'Service Tax No  ' || SQLERRM);
            END;

            -----------------------------------------------------------------
            ----Excise Reg. No
            BEGIN
               lc_excise_registration_no :=
                  TRIM (Get_Field (req_line, req_delim, 27));           -- (X)
            EXCEPTION
               WHEN OTHERS
               THEN
                  RAISE_APPLICATION_ERROR (-20057,
                                           'Excise Reg. No  ' || SQLERRM);
            END;

            -----------------------------------------------------------------
            ----PAN No
            BEGIN
               lc_pan_no := TRIM (Get_Field (req_line, req_delim, 28)); -- (Y)
            EXCEPTION
               WHEN OTHERS
               THEN
                  RAISE_APPLICATION_ERROR (-20057, 'PAN No  ' || SQLERRM);
            END;

            -----------------------------------------------------------------
            ----EOL No
            BEGIN
               lc_edible_oil_licence_no :=
                  TRIM (Get_Field (req_line, req_delim, 29));           -- (Z)
            EXCEPTION
               WHEN OTHERS
               THEN
                  RAISE_APPLICATION_ERROR (-20057, 'EOL No  ' || SQLERRM);
            END;

            -----------------------------------------------------------------
            ---- TL No
            BEGIN
               lc_trade_licence_no :=
                  TRIM (Get_Field (req_line, req_delim, 30));          -- (AA)
            EXCEPTION
               WHEN OTHERS
               THEN
                  RAISE_APPLICATION_ERROR (-20057, 'TL No ' || SQLERRM);
            END;

            -----------------------------------------------------------------
            ---- TDS Supplier Type
            BEGIN
               lc_type_of_assessee :=
                  TRIM (Get_Field (req_line, req_delim, 31));          -- (AB)
            EXCEPTION
               WHEN OTHERS
               THEN
                  RAISE_APPLICATION_ERROR (-20057,
                                           'TDS Supplier Type  ' || SQLERRM);
            END;

            -----------------------------------------------------------------
            ---- Small Scale Industry
            BEGIN
               lc_small_scale_industry_yn :=
                  TRIM (Get_Field (req_line, req_delim, 32));          -- (AC)
            EXCEPTION
               WHEN OTHERS
               THEN
                  RAISE_APPLICATION_ERROR (
                     -20057,
                     'Small Scale Industry Y/N  ' || SQLERRM);
            END;

            -----------------------------------------------------------------
            ----  Supplier Type
            BEGIN
               lc_type_of_supplier :=
                  TRIM (Get_Field (req_line, req_delim, 33));          -- (AD)
            EXCEPTION
               WHEN OTHERS
               THEN
                  RAISE_APPLICATION_ERROR (-20057,
                                           'Supplier Type  ' || SQLERRM);
            END;

            -- Added by XXXXX
            -----------------------------------------------------------------
            ----  ISupplier Flag
            BEGIN
               lc_isuplier_flag := TRIM (Get_Field (req_line, req_delim, 34)); -- (AD)
            EXCEPTION
               WHEN OTHERS
               THEN
                  RAISE_APPLICATION_ERROR (-20057,
                                           'Supplier Type  ' || SQLERRM);
            END;

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

            -- End of Addition by Ramarao on 18-AUG-2011
            ---- Payment Terms No of Days
            BEGIN
               lc_payment_terms_no_of_days :=
                  TRIM (Get_Field (req_line, req_delim, 35));          -- (AE)
            EXCEPTION
               WHEN OTHERS
               THEN
                  RAISE_APPLICATION_ERROR (
                     -20057,
                     'Payment Terms No of Days  ' || SQLERRM);
            END;

            -----------------------------------------------------------------
            ---- One Time Supplier Yes/ No
            BEGIN
               lc_one_time_supplier_yn :=
                  TRIM (Get_Field (req_line, req_delim, 36));          -- (AF)
            EXCEPTION
               WHEN OTHERS
               THEN
                  RAISE_APPLICATION_ERROR (
                     -20057,
                     'One Time Supplier Yes/No ' || SQLERRM);
            END;

            -----------------------------------------------------------------
            ---- Currency Code
            BEGIN
               lc_currency_code := TRIM (Get_Field (req_line, req_delim, 37)); -- (AG)
            EXCEPTION
               WHEN OTHERS
               THEN
                  RAISE_APPLICATION_ERROR (-20057,
                                           'Currency Code ' || SQLERRM);
            END;

            -----------------------------------------------------------------
            ---- Supplier Bank Name
            BEGIN
               lc_supplier_bank_name :=
                  TRIM (Get_Field (req_line, req_delim, 38));          -- (AH)
            EXCEPTION
               WHEN OTHERS
               THEN
                  RAISE_APPLICATION_ERROR (-20057,
                                           'Supplier Bank Name  ' || SQLERRM);
            END;

            -----------------------------------------------------------------
            ---- Supplier Bank Branch Name
            BEGIN
               lc_supplier_bank_branch_name :=
                  TRIM (Get_Field (req_line, req_delim, 39));          -- (AI)
            EXCEPTION
               WHEN OTHERS
               THEN
                  RAISE_APPLICATION_ERROR (
                     -20057,
                     'Supplier Bank Branch Name  ' || SQLERRM);
            END;

            -----------------------------------------------------------------
            ---- Bank Branch Address1
            BEGIN
               lc_bank_branch_address1 :=
                  TRIM (Get_Field (req_line, req_delim, 40));          -- (AJ)
            EXCEPTION
               WHEN OTHERS
               THEN
                  RAISE_APPLICATION_ERROR (
                     -20057,
                     'Bank Branch Address1  ' || SQLERRM);
            END;

            -----------------------------------------------------------------
            ----Bank Branch Address2
            BEGIN
               lc_bank_branch_address2 :=
                  TRIM (Get_Field (req_line, req_delim, 41));          -- (AK)
            EXCEPTION
               WHEN OTHERS
               THEN
                  RAISE_APPLICATION_ERROR (
                     -20057,
                     'Bank Branch Address2  ' || SQLERRM);
            END;

            -----------------------------------------------------------------
            ----Bank Branch Address3
            BEGIN
               lc_bank_branch_address3 :=
                  TRIM (Get_Field (req_line, req_delim, 42));          -- (AL)
            EXCEPTION
               WHEN OTHERS
               THEN
                  RAISE_APPLICATION_ERROR (
                     -20057,
                     'Bank Branch Address3  ' || SQLERRM);
            END;

            -----------------------------------------------------------------
            ----Bank Branch Address4
            BEGIN
               lc_bank_branch_address4 :=
                  TRIM (Get_Field (req_line, req_delim, 43));          -- (AL)
            EXCEPTION
               WHEN OTHERS
               THEN
                  RAISE_APPLICATION_ERROR (
                     -20057,
                     'Bank Branch Address4  ' || SQLERRM);
            END;

            -----------------------------------------------------------------
            ---- Bank Branch City
            BEGIN
               lc_bank_city := TRIM (Get_Field (req_line, req_delim, 44)); -- (AM)
            EXCEPTION
               WHEN OTHERS
               THEN
                  RAISE_APPLICATION_ERROR (-20057,
                                           'Bank Branch City  ' || SQLERRM);
            END;

            -----------------------------------------------------------------
            ---- Bank Branch State
            BEGIN
               lc_bank_state := TRIM (Get_Field (req_line, req_delim, 45)); -- (AN)
            EXCEPTION
               WHEN OTHERS
               THEN
                  RAISE_APPLICATION_ERROR (-20057,
                                           'Bank Branch State  ' || SQLERRM);
            END;

            -----------------------------------------------------------------
            ---- Bank Branch Country
            BEGIN
               lc_bank_country := TRIM (Get_Field (req_line, req_delim, 46)); -- (AN)
            EXCEPTION
               WHEN OTHERS
               THEN
                  RAISE_APPLICATION_ERROR (
                     -20057,
                     'Bank Branch Country  ' || SQLERRM);
            END;

            ---- Bank Branch Pin Code
            BEGIN
               lc_bank_pin_code := TRIM (Get_Field (req_line, req_delim, 47)); -- (AO)
            EXCEPTION
               WHEN OTHERS
               THEN
                  RAISE_APPLICATION_ERROR (
                     -20057,
                     'Bank Branch Pin Code  ' || SQLERRM);
            END;

            -----------------------------------------------------------------
            ---- IFSC Code
            BEGIN
               lc_ifsc_no := TRIM (Get_Field (req_line, req_delim, 48)); -- (AP)
            EXCEPTION
               WHEN OTHERS
               THEN
                  RAISE_APPLICATION_ERROR (-20057, 'IFSC Code  ' || SQLERRM);
            END;

            -----------------------------------------------------------------
            ---- Supplier Bank Account No
            BEGIN
               lc_supplier_bank_acctount_no :=
                  TRIM (Get_Field (req_line, req_delim, 49));          -- (AQ)
            EXCEPTION
               WHEN OTHERS
               THEN
                  RAISE_APPLICATION_ERROR (
                     -20057,
                     'Supplier Bank Account No ' || SQLERRM);
            END;

            -----------------------------------------------------------------
            ---- Liability Account
            BEGIN
               lc_liability_account :=
                  TRIM (Get_Field (req_line, req_delim, 50));          -- (AR)
            EXCEPTION
               WHEN OTHERS
               THEN
                  RAISE_APPLICATION_ERROR (-20057,
                                           'Liability Account  ' || SQLERRM);
            END;

            -----------------------------------------------------------------
            ---- Prepayment Account
            BEGIN
               lc_prepayment_account :=
                  TRIM (Get_Field (req_line, req_delim, 51));          -- (AS)
            EXCEPTION
               WHEN OTHERS
               THEN
                  RAISE_APPLICATION_ERROR (-20057,
                                           'Prepayment Account  ' || SQLERRM);
            END;

            -----------------------------------------------------------------
            ---- Invoice Initiator Email
            BEGIN
               lc_invoice_initiator_email :=
                  TRIM (Get_Field (req_line, req_delim, 52));          -- (AT)
            EXCEPTION
               WHEN OTHERS
               THEN
                  RAISE_APPLICATION_ERROR (
                     -20057,
                     'Invoice Initiator Email  ' || SQLERRM);
            END;

            -----------------------------------------------------------------
            ---- Payment Team Member Email
            BEGIN
               lc_payment_term_email :=
                  TRIM (Get_Field (req_line, req_delim, 53));          -- (AU)
            EXCEPTION
               WHEN OTHERS
               THEN
                  RAISE_APPLICATION_ERROR (
                     -20057,
                     'Payment Team Member Email  ' || SQLERRM);
            END;

            -----------------------------------------------------------------
            ---- Payment Team Member Email
            BEGIN
               lc_status := TRIM (Get_Field (req_line, req_delim, 54)); -- (AU)
            EXCEPTION
               WHEN OTHERS
               THEN
                  RAISE_APPLICATION_ERROR (-20057, 'test ' || SQLERRM);
            END;

            -----------------------------------------------------------------
            IF lc_supplier_name IS NOT NULL
            THEN
               ln_org_id := NULL;
               ln_set_of_book_id := NULL;

               BEGIN
                  SELECT ORGANIZATION_ID, SET_OF_BOOKS_ID
                    INTO ln_org_id, ln_set_of_book_id
                    FROM HR_OPERATING_UNITS
                   WHERE NAME = lc_operating_unit;
               EXCEPTION
                  WHEN OTHERS
                  THEN
                     ln_org_id := NULL;
                     ln_set_of_book_id := NULL;
               END;

               BEGIN
                  INSERT
                    INTO XXST_AP_SUPP_INT_STG (
                            record_id,                                  -----1
                            batch_no,                                   -----2
                            operating_unit,                             -----3
                            org_id,                                    ------4
                            set_of_books_id,                           ------5
                            supplier_id,                               ------6
                            supplier_name,                             ------7
                            address1,                                  ------8
                            address2,                                  ------9
                            address3,                                 ------10
                            address4,                                 ------11
                            city,
                            state,
                            pin_code,
                            country,
                            site_name,
                            alternate_supplier_site_if_any,
                            alternate_supp_address_ifany,
                            first_name,
                            last_name,
                            std_isd_tel_code,
                            telephone_no,
                            std_isd_fax_code,
                            fax_no,
                            mobile_no,
                            email_id,
                            url,
                            central_sales_tax_no,
                            value_added_tax_no,
                            service_tax_no,
                            excise_registration_no,
                            pan_no,
                            edible_oil_licence_no,
                            trade_licence_no,
                            type_of_assessee,
                            small_scale_industry_yn,
                            type_of_supplier,
                            isupplier_flag, -- Added by XXXXX
                           
                            payment_terms_no_of_days,
                            --one_time_supplier_yn,
                            currency_code,
                            supplier_bank_name,
                            supplier_bank_branch_name,
                            bank_branch_address1,
                            bank_branch_address2,
                            bank_branch_address3,
                            bank_city,
                            bank_state,
                            bank_pin_code,
                            ifsc_no,
                            supplier_bank_acctount_no,
                            liability_account,
                            prepayment_account,
                            invoice_initiator_email,
                            payment_team_email,
                            creation_date,
                            status,
                            input_file_name,
                            error_message)
                  VALUES (TRIM (ln_record_id),
                          TRIM (ln_batch_id),
                          TRIM (lc_operating_unit),
                          TRIM (ln_org_id),
                          TRIM (ln_set_of_book_id),
                          NULL,                                 --supplier_id,
                          UPPER (TRIM (lc_supplier_name)),
                          TRIM (lc_address1),
                          TRIM (lc_address2),
                          TRIM (lc_address3),
                          TRIM (lc_address4),
                          TRIM (lc_city),
                          TRIM (lc_state),
                          TRIM (lc_pin_code),
                          TRIM (lc_country),
                          UPPER (TRIM (lc_site_name)),
                          TRIM (lc_alternate_supplier_site_any),
                          TRIM (lc_alternate_supp_address_any),
                          TRIM (lc_first_name),
                          TRIM (lc_last_name),
                          TRIM (lc_std_isd_tel_code),
                          TRIM (lc_telephone_no),
                          TRIM (lc_std_isd_fax_code),
                          TRIM (lc_fax_no),
                          TRIM (lc_mobile_no),
                          TRIM (lc_email_id),
                          TRIM (lc_url),
                          TRIM (lc_central_sales_tax_no),
                          TRIM (lc_value_added_tax_no),
                          TRIM (lc_service_tax_no),
                          TRIM (lc_excise_registration_no),
                          TRIM (lc_pan_no),
                          TRIM (lc_edible_oil_licence_no),
                          TRIM (lc_trade_licence_no),
                          TRIM (lc_type_of_assessee),
                          TRIM (lc_small_scale_industry_yn),
                          TRIM (lc_type_of_supplier),
                          TRIM (lc_isuplier_flag), -- Added by XXXXX
                          TRIM (lc_payment_terms_no_of_days),
                          --TRIM(lc_one_time_supplier_yn),
                          TRIM (lc_currency_code),
                          TRIM (lc_supplier_bank_name),
                          TRIM (lc_supplier_bank_branch_name),
                          TRIM (lc_bank_branch_address1),
                          TRIM (lc_bank_branch_address2),
                          TRIM (lc_bank_branch_address3),
                          TRIM (lc_bank_city),
                          TRIM (lc_bank_state),
                          TRIM (lc_bank_pin_code),
                          TRIM (lc_ifsc_no),
                          TRIM (lc_supplier_bank_acctount_no),
                          TRIM (lc_liability_account),
                          TRIM (lc_prepayment_account),
                          TRIM (lc_invoice_initiator_email),
                          TRIM (lc_payment_term_email),
                          TRIM (SYSDATE),                  --lc_creation_date,
                          NULL,                                   --lc_status,
                          TRIM (p_file_name),            --lc_input_file_name,
                          NULL                             --lc_error_message,
                              );
               EXCEPTION
                  WHEN OTHERS
                  THEN
                     RAISE_APPLICATION_ERROR (
                        -20057,
                        'Error While Insert into Stg Table  ' || SQLERRM);
               END;
            END IF;
         -----------------------------------------------------------------
         END IF;
      END LOOP;

      COMMIT;
      Fnd_File.
       put_line (fnd_file.LOG,
                 'File Uploaded into interface table succussfully');

      ----Calling Supplier creation procedure
      --XXFTAL_AP_SUPP_INT_PKG.ap_vendor_creation(ln_batch_id);
      BEGIN
         ln_request_id :=
            fnd_request.submit_request ('XXFTAL',
                                        'XXFTAL_SUPP_INT_CREATION',
                                        '',
                                        '',
                                        FALSE,
                                        ln_batch_id);
         COMMIT;
      END;

      UTL_FILE.FCLOSE_ALL;
   EXCEPTION
      WHEN UTL_FILE.INVALID_OPERATION
      THEN
         Fnd_File.
          put_line (
            Fnd_File.output,
            'Invalid Operation: '
            || ' File Not Exit in AP Supplier Files Directory ');
         UTL_FILE.FCLOSE_ALL;
         RAISE_APPLICATION_ERROR (
            -20051,
            'File Not Exit in AP Supplier Files Directory');
      WHEN UTL_FILE.INVALID_PATH
      THEN
         Fnd_File.put_line (Fnd_File.output, 'Invalid Path');
         UTL_FILE.FCLOSE_ALL;
         RAISE_APPLICATION_ERROR (-20052, 'Invalid Path');
      WHEN UTL_FILE.INVALID_MODE
      THEN
         Fnd_File.put_line (Fnd_File.output, 'Invalid Mode');
         UTL_FILE.FCLOSE_ALL;
         RAISE_APPLICATION_ERROR (-20053, 'Invalid Mode');
      WHEN UTL_FILE.INVALID_FILEHANDLE
      THEN
         Fnd_File.put_line (Fnd_File.output, 'Invalid File Handle ');
         UTL_FILE.FCLOSE_ALL;
         RAISE_APPLICATION_ERROR (-20054, 'Invalid File Handle');
      WHEN UTL_FILE.WRITE_ERROR
      THEN
         Fnd_File.put_line (Fnd_File.output, 'Invalid Write Error ');
         UTL_FILE.FCLOSE_ALL;
         RAISE_APPLICATION_ERROR (-20055, 'Invalid Write Error');
      WHEN UTL_FILE.READ_ERROR
      THEN
         Fnd_File.put_line (Fnd_File.output, 'Read Error ');
         UTL_FILE.FCLOSE_ALL;
         RAISE_APPLICATION_ERROR (-20056, 'Read Error');
      WHEN OTHERS
      THEN
         RAISE_APPLICATION_ERROR (-20057,
                                  'Check the File Data Format ' || SQLERRM);
         UTL_FILE.FCLOSE_ALL;
   END ap_supplier_upload;
END XXST_AP_SUPP_INT_PKG;


This is the Supplier Interface in Oracle apps R12.

Sorry for inconvenience  Due to data issues and performance loading i divided it into multiple parts don't keep in mind,

I hope this will help you..

Thanks all,
Rajashekara Reddy.

1 comment:

  1. Hi , Can you share the input file and the interface program to itsramesha@gmail.com

    ReplyDelete