Create_Ap_Invoice

CREATE OR REPLACE PROCEDURE create_invoice (errcode   OUT VARCHAR2,
                                            errmsg    OUT VARCHAR2)
IS
   l_err_msg                    VARCHAR2 (1000);
   l_err_code                   VARCHAR2 (1000);
   l_invoice_num                VARCHAR2 (100);
   l_invoice_type_lookup_code   VARCHAR2 (100);
   l_vendor_id                  NUMBER;
   l_vendor_site_id             NUMBER;
   l_invoice_amount             NUMBER;
   l_invoice_currency_code      VARCHAR2 (10);
   l_source                     VARCHAR2 (240);
   l_org_id                     NUMBER;
   l_orgn_id                    NUMBER;
   l_entry_date                 DATE;
   l_form_number                NUMBER;
   l_group_id                   NUMBER;
   l_goods_received_date        DATE;
   l_currency_code              VARCHAR2 (10);
   l_po_number                  VARCHAR2 (40);
   l_amount                     NUMBER;
   counter                      NUMBER;
   l_ship_to_location_id        NUMBER;
   l_code_combination_id        NUMBER;
   l_term_id                    NUMBER;
   l_dist_code_comb_id          NUMBER;
   v_count                      NUMBER;
   l_error_flag                 VARCHAR2 (1);
   l_error_details              VARCHAR2 (250);
   CURSOR headers
   IS
      SELECT   * FROM xx_temp_inv_hdr;
   CURSOR lines (p_invoice_id NUMBER)
   IS
      SELECT   *
        FROM   xx_temp_inv_line
       WHERE   invoice_number = p_invoice_id;
BEGIN
   -- mo_global.init('PO');
   -- mo_global.SET_POLICY_CONTEXT('',88);
   l_err_msg := NULL;
   l_amount := 0;
   v_count := 0;
   FOR hdr_rec IN headers
   LOOP
      BEGIN
         SELECT   COUNT ( * )
           INTO   v_count
           FROM   ap_invoices_all
          WHERE   invoice_num = hdr_rec.invoice_number;
         IF v_count > 0
         THEN
            DBMS_OUTPUT.put_line('Invoice No already exists -->'
                                 || v_count
                                 || SQLERRM);
         ELSE
            BEGIN
               SELECT   vendor_id
                 INTO   l_vendor_id
                 FROM   ap_suppliers
                WHERE   vendor_name = hdr_rec.supplier_name; --'LOGWELL FORGE LTD.';
            EXCEPTION
               WHEN NO_DATA_FOUND
               THEN
                  DBMS_OUTPUT.put_line ('Vendor id is not found' || SQLERRM);
            END;
            IF l_vendor_id IS NOT NULL
            THEN
               DBMS_OUTPUT.put_line ('Vendor id is not found' || SQLERRM);
               BEGIN
                  SELECT   vendor_site_id
                    INTO   l_vendor_site_id
                    FROM   ap_supplier_sites_all
                   WHERE   vendor_site_code = hdr_rec.supplier_site_name
                           AND vendor_id = l_vendor_id;
                  IF l_vendor_site_id IS NOT NULL
                  THEN
                     DBMS_OUTPUT.put_line (
                        'Vendor id is not found' || SQLERRM
                     );
                     BEGIN
                        SELECT   code_combination_id
                          INTO   l_code_combination_id
                          FROM   gl_code_combinations_kfv
                         WHERE   concatenated_segments =
                                    hdr_rec.liability_account;
                        IF l_code_combination_id IS NOT NULL
                        THEN
                           DBMS_OUTPUT.put_line (
                              'Vendor id is not found' || SQLERRM
                           );
                           BEGIN
                              SELECT   term_id
                                INTO   l_term_id
                                FROM   ap_terms_tl
                               WHERE   NAME =
                                          '60 DAYS AFTER RECEIPT OF MATERIAL';
                              IF l_term_id IS NOT NULL
                              THEN
                                 DBMS_OUTPUT.put_line (
                                    'Vendor id is not found' || SQLERRM
                                 );
                                 DBMS_OUTPUT.put_line (
                                    'start header loop found'
                                 );
                                 INSERT INTO ap_invoices_interface (
                                                                       invoice_id,
                                                                       invoice_type_lookup_code,
                                                                       invoice_num,
                                                                       invoice_date,
                                                                       gl_date,
                                                                       vendor_id,
                                                                       vendor_num,
                                                                       vendor_site_id,
                                                                       invoice_amount,
                                                                       invoice_currency_code,
                                                                       description,
                                                                       terms_id,
                                                                       payment_method_code,
                                                                       doc_category_code,
                                                                       accts_pay_code_combination_id,
                                                                       goods_received_date,
                                                                       org_id,
                                                                       SOURCE,
                                                                       last_update_date,
                                                                       last_updated_by,
                                                                       creation_date,
                                                                       created_by
                                            )
                                   VALUES   (ap_invoices_interface_s.NEXTVAL, -- invoice_id
                                             hdr_rec.invoice_type,
                                             -- type
                                             hdr_rec.invoice_number,
                                             hdr_rec.invoice_date,
                                             hdr_rec.gl_date,
                                             l_vendor_id,
                                             hdr_rec.supplier_number,
                                             l_vendor_site_id,
                                             hdr_rec.invoice_amount,
                                             hdr_rec.invoice_currency,
                                             hdr_rec.invoice_description,
                                             l_term_id,
                                             hdr_rec.payment_method,
                                             hdr_rec.document_category,
                                             l_code_combination_id,
                                             hdr_rec.good_receiving_date,
                                             246,
                                             'OPEN BALANCE',
                                             SYSDATE,
                                             fnd_profile.VALUE ('USER_ID'),
                                             SYSDATE,
                                             fnd_profile.VALUE ('USER_ID'));
                                 fnd_file.put_line (
                                    fnd_file.LOG,
                                    'Invoice Num ' || hdr_rec.invoice_number
                                 );
                                 counter := 1;
                                 DBMS_OUTPUT.put_line (
                                    'end header loop found'
                                 );
                                 FOR rec IN lines (hdr_rec.invoice_number)
                                 LOOP
                                    DBMS_OUTPUT.put_line (
                                       'start line loop found' || SQLERRM
                                    );
                                    BEGIN
                                       SELECT   code_combination_id
                                         INTO   l_dist_code_comb_id
                                         FROM   gl_code_combinations_kfv
                                        WHERE   concatenated_segments =
                                                   rec.ACCOUNT;
                                    EXCEPTION
                                       WHEN NO_DATA_FOUND
                                       THEN
                                          DBMS_OUTPUT.put_line('dist code combination_id is not found'
                                                               || SQLERRM);
                                    END;
                                    DBMS_OUTPUT.put_line('start insert line loop found'
                                                         || SQLERRM);
                                    INSERT INTO ap_invoice_lines_interface (
                                                                               invoice_id,
                                                                               invoice_line_id,
                                                                               line_number,
                                                                               line_type_lookup_code,
                                                                               amount,
                                                                               dist_code_combination_id,
                                                                               description,
                                                                               org_id,
                                                                               last_update_date,
                                                                               last_updated_by,
                                                                               creation_date,
                                                                               created_by
                                               )
                                      VALUES   (
                                                   ap_invoices_interface_s.CURRVAL,
                                                   ap_invoice_lines_interface_s.NEXTVAL,
                                                   rec.line_number,
                                                   rec.TYPE,
                                                   rec.amount,
                                                   l_dist_code_comb_id,
                                                   rec.description,
                                                   246,
                                                   SYSDATE,
                                                   fnd_profile.VALUE (
                                                      'USER_ID'
                                                   ),
                                                   SYSDATE,
                                                   fnd_profile.VALUE (
                                                      'USER_ID'
                                                   )
                                               );
                                    DBMS_OUTPUT.put_line (
                                       'end line loop found' || SQLERRM
                                    );
                                    DBMS_OUTPUT.put_line('Invoice Line Num '
                                                         || rec.line_number
                                                         || ' Line Amount: '
                                                         || rec.amount);
                                    fnd_file.put_line (
                                       fnd_file.LOG,
                                          'Invoice Line Num '
                                       || rec.line_number
                                       || ' Line Amount: '
                                       || rec.amount
                                    );
                                    counter := counter + 1;
                                 END LOOP;
                              ELSE
                                 DBMS_OUTPUT.put_line (
                                    'Vendor id is not found' || SQLERRM
                                 );
                              END IF;
                           EXCEPTION
                              WHEN NO_DATA_FOUND
                              THEN
                                 DBMS_OUTPUT.put_line (
                                    'Term name is not found' || SQLERRM
                                 );
                           END;
                        ELSE
                           DBMS_OUTPUT.put_line (
                              'Vendor id is not found' || SQLERRM
                           );
                        END IF;
                     --'01-SSC-001-24106-0000-000-0000-000';
                     EXCEPTION
                        WHEN NO_DATA_FOUND
                        THEN
                           DBMS_OUTPUT.put_line (
                              'Account Number is not found' || SQLERRM
                           );
                     END;
                  ELSE
                     DBMS_OUTPUT.put_line (
                        'Vendor id is not found' || SQLERRM
                     );
                  END IF;
               EXCEPTION
                  WHEN NO_DATA_FOUND
                  THEN
                     DBMS_OUTPUT.put_line (
                        'Vendor site id is not found' || SQLERRM
                     );
               END;
            ELSE
               DBMS_OUTPUT.put_line ('Vendor id is not found' || SQLERRM);
            END IF;
         END IF;
      END;
   --DBMS_OUTPUT.put_line ('INVOICE NUM v_count' || v_count);
   END LOOP;
   fnd_file.put_line (fnd_file.LOG, lines%ROWCOUNT || ' Rows Inserted');
   fnd_file.put_line (fnd_file.output, 'ap invoice lines are valid');
   fnd_file.put_line (fnd_file.LOG, headers%ROWCOUNT || ' Rows Inserted');
   fnd_file.put_line (fnd_file.output, 'ap invoice headers are valid');

   COMMIT;
END create_invoice;
/