CREATE OR REPLACE PACKAGE BODY ap_im_int_pkg
AS

   PROCEDURE ap_im_int_start (
      errbuf    OUT NOCOPY   VARCHAR2,
      retcode   OUT NOCOPY   NUMBER
   )
   IS
      l_job   NUMBER;
   BEGIN
      -- 1. insert Table Temporary.
      Gr2ORA_IM;


      DBMS_JOB.submit (l_job, 'FillAndSend_IM1;');
      DBMS_JOB.submit (l_job, 'FillAndSend_IM2;');
      DBMS_JOB.submit (l_job, 'FillAndSend_IM3;');
      DBMS_JOB.submit (l_job, 'FillAndSend_IM4;');
      
      -- 3. Delete All If flag status 'P' in Temp.Table
      deltempflag_im;
      -- 4. Commit
      COMMIT;
   END;

-- *********************************************
   PROCEDURE Gr2ora_im
   IS
   BEGIN
      INSERT INTO ap_im_int_ora
                  (im_interface_id, invoice_num, invoice_type_lookup_code,
                   invoice_date, vendor_num, vendor_site_code, amount,
                   invoice_currency_code, terms_name, description,
                   pay_group_lookup_code, gl_date, terms_date, Gr_store,
                   accounting_date, type_code, sales_chanel, division,
                   inventory, dept)
         SELECT   im_interface_id, invoice_num, invoice_type_lookup_code,
                  invoice_date, vendor_num, vendor_site_code, amount,
                  invoice_currency_code, terms_name, description,
                  pay_group_lookup_code, gl_date, terms_date, Gr_store,
                  accounting_date, type_code, sales_chanel, division,
                  inventory, DECODE (sales_chanel, '6', 'CAFETARIA', dept)
                 FROM     ap_im_int@link2prd.head.com
         ORDER BY invoice_num, type_code;

      IF TRUNC (SYSDATE) <= TO_DATE ('01/04/2011', 'dd/mm/yyyy')
      THEN
         UPDATE ap_im_int_ora
            SET gl_date = TO_DATE ('01/04/2011', 'dd/mm/yyyy'),
                accounting_date = TO_DATE ('01/04/2011', 'dd/mm/yyyy')
          WHERE flag IS NULL AND SUBSTR (Gr_store, 3, 3) = '227';
      END IF;

      UPDATE ap_im_int_ora
         SET gl_date = TRUNC (SYSDATE - 1),
             accounting_date = TRUNC (SYSDATE - 1)
       WHERE TO_CHAR (gl_date, 'yyyymm') <
                                       TO_CHAR (TRUNC (SYSDATE - 6), 'yyyymm')
         AND flag IS NULL;
   END;

-- *********************************************
   PROCEDURE fillandsend_im1
   IS
      CURSOR Gr_data
      IS
         SELECT   *
             FROM ap_im_int_ora
            WHERE flag IS NULL
              AND SUBSTR (Gr_store, 3, 3) IN (
                                          SELECT store_code
                                            FROM parameter_ora
                                           WHERE module = 'MATC'
                                                 AND status = 1)
              AND amount <> 0
              AND type_code = '8'
              AND SUBSTR (Gr_store, 3, 3) BETWEEN '100' AND '140'
         ORDER BY SUBSTR (Gr_store, 3, 3),
                  vendor_num,
                  vendor_site_code,
                  invoice_num;

      rGr                 Gr_data%ROWTYPE;
      cnewseq             ap_invoices_interface.invoice_id%TYPE;
      ctermsid            ap_invoices_interface.terms_id%TYPE;
      nno                 NUMBER;
      cvendor_num         rGr.vendor_num%TYPE;
      cvendor_site_code   rGr.vendor_site_code%TYPE;
      cinvoice_num        rGr.invoice_num%TYPE;
      cinv_num            rGr.invoice_num%TYPE;
      ccancel             CHAR (1);
      vreturn             NUMBER;
      vstorenew           VARCHAR2 (3);
      vsubstore           VARCHAR2 (3);
      vsite_disp          VARCHAR2 (10)                           := 1;
      dummy               VARCHAR2 (100);
      lcreate             BOOLEAN                                 := TRUE;
      vattribute4         VARCHAR2 (3);
      cinvoicenum         rGr.invoice_num%TYPE;
      cGrstored         rGr.Gr_store%TYPE;
   BEGIN
      cvendor_num := 'X';
      cvendor_site_code := 'X';
      cinvoice_num := 'X';

      OPEN Gr_data;

      LOOP
         FETCH Gr_data
          INTO rGr;

         EXIT WHEN Gr_data%NOTFOUND;
         vreturn := 1;
         dummy := NULL;                                     -- RESET 05092011
         ctermsid := supp_int_pkg.findtermid (rGr.terms_name);
         lcreate := TRUE;

         IF     SUBSTR (rGr.invoice_num,
                        INSTR (rGr.invoice_num, 'CL', 1),
                        2
                       ) = 'CL'
            AND ctermsid <> 0
         THEN
            vreturn := checkinvnumb (SUBSTR (rGr.invoice_num, 1, 13));
            ctermsid := vreturn;
         END IF;

         IF ctermsid <> 0
         THEN
            ctermsid := supp_int_pkg.findtermid (rGr.terms_name);
            ccancel := 'N';

            IF checkdescr (rGr.description, ROUND (rGr.amount, 2)) = 1
            THEN
               createcancelim (rGr.description);
               ccancel := 'Y';
            ELSE
             
               IF SUBSTR (rGr.invoice_num, -1) = 'R'
               THEN
                  BEGIN
                     SELECT   invoice_num
                         INTO dummy
                         FROM ap_invoices_all
                        WHERE description = rGr.description
                          AND invoice_type_lookup_code = 'STANDARD'
                          AND invoice_amount = ROUND (rGr.amount, 2)
                          AND invoice_num =
                                 SUBSTR (rGr.invoice_num,
                                         1,
                                         LENGTH (rGr.invoice_num) - 1
                                        )
                          AND ROWNUM = 1
                     ORDER BY creation_date DESC, invoice_id DESC;


                     createcancelim (rGr.description);
                     ccancel := 'Y';
                     lcreate := TRUE;
                  EXCEPTION
                     WHEN NO_DATA_FOUND
                     THEN
                        lcreate := TRUE;
                  END;
               ELSE
        
                  BEGIN
                     SELECT   1
                         INTO dummy
                         FROM ap_invoices_all
                        WHERE description = rGr.description
                          AND invoice_type_lookup_code = 'STANDARD'
                          AND invoice_amount = ROUND (rGr.amount, 2)
                          AND invoice_num = rGr.invoice_num
                          AND ROWNUM = 1
                     ORDER BY creation_date DESC, invoice_id DESC;

                     lcreate := FALSE;
                  EXCEPTION
                     WHEN NO_DATA_FOUND
                     THEN
           
                        BEGIN
                           SELECT   invoice_num
                               INTO dummy
                               FROM ap_invoices_all
                              WHERE description = rGr.description
                                AND invoice_type_lookup_code = 'STANDARD'
                                AND invoice_amount = ROUND (rGr.amount, 2)
                                AND ROWNUM = 1
                           ORDER BY creation_date DESC, invoice_id DESC;

                           createcancelim (rGr.description);
                           ccancel := 'Y';
                           lcreate := TRUE;
                        EXCEPTION
                           WHEN NO_DATA_FOUND
                           THEN
                              lcreate := TRUE;
                        END;
                  END;
               END IF;
            END IF;

            cinv_num := rGr.invoice_num;
            cnewseq :=
               TO_NUMBER (   TO_CHAR (SYSTIMESTAMP, 'yymm')
                          || TO_CHAR (supp_int_pkg.getnewseq ())
                         );

            IF     (   SUBSTR (rGr.Gr_store, 3, 3) = '300'
                    OR SUBSTR (rGr.Gr_store, 3, 3) = '199'
                   )
               AND rGr.sales_chanel = 3
            THEN
               vstorenew := '201';
               vsubstore := '201';
            ELSE
               -- Branch
               IF supp_int_pkg.func_Branchmart
                                                   (SUBSTR (rGr.Gr_store,
                                                            3,
                                                            3
                                                           )
                                                   ) = '201'
               THEN
                  vstorenew := '201';
                  vsubstore := '201';
               ELSE
                  vstorenew := SUBSTR (rGr.Gr_store, 3, 3);

                  IF SUBSTR (rGr.Gr_store, 3, 3) IN ('199', '300')
                  THEN
                     vsubstore := '301';
                  ELSE
                     vsubstore := SUBSTR (rGr.Gr_store, 3, 3);
                  END IF;
               END IF;
            END IF;

            vattribute4 := vsubstore;

           
            IF lcreate
            THEN
               IF SUBSTR (rGr.vendor_site_code, 1, 1) <> 'C'
                  AND (rGr.vendor_num <> cvendor_num)
                  OR (rGr.vendor_site_code <> cvendor_site_code)
                  OR (rGr.invoice_num <> cinvoice_num)
               THEN
                  temp2orawksh (cnewseq,
                                cinv_num,
                                rGr.invoice_type_lookup_code,
                                rGr.invoice_date,
                                rGr.vendor_num,
                                rGr.vendor_site_code,
                                ROUND (rGr.amount, 2),
                                rGr.invoice_currency_code,
                                ctermsid,
                                rGr.terms_name,
                                rGr.description,
                                rGr.pay_group_lookup_code,
                                rGr.gl_date,
                                rGr.terms_date,
                                rGr.dept,
                                supp_int_pkg.findorg_id (vstorenew),
                                rGr.inventory,
                                vattribute4
                               );
                  cvendor_num := rGr.vendor_num;
                  cvendor_site_code := rGr.vendor_site_code;
                  cinvoice_num := rGr.invoice_num;
               END IF;
            END IF;

            nno := TO_CHAR (supp_int_pkg.getnewseq ());

            IF lcreate
            THEN                                        
               IF vsubstore IN ('300', '199')
               THEN
                  vsubstore := '301';
               END IF;

               temp2orawksd (cnewseq,
                             nno,
                             ROUND (rGr.amount, 2),
                             rGr.accounting_date,
                             rGr.description,
                              supp_int_pkg.func_Branchmart (vstorenew)
                             || '.000.9922001.000.'
                             || vsubstore
                             || '.00',
                             rGr.dept,
                             supp_int_pkg.findorg_id (vstorenew),
                             rGr.inventory,
                             vattribute4
                            );

               UPDATE    ap_im_int_ora
                     SET flag = 'P'
                   WHERE im_interface_id = rGr.im_interface_id
               RETURNING invoice_num, Gr_store
                    INTO cinvoicenum, cGrstored;

               UPDATE ap_im_int_ora
                  SET flag = 'P'
                WHERE invoice_num = cinvoicenum
                  AND Gr_store = cGrstored
                  AND flag IS NULL;

               DBMS_OUTPUT.put_line ('Flag = P');
               DBMS_OUTPUT.put_line ('  ');
            END IF;

         END IF;

         IF SUBSTR (rGr.Gr_store, 3, 3) <> vsite_disp
         THEN
            fnd_file.put_line
                       (fnd_file.LOG,
                           'Invoice Matching Interface for Operating Unit : '
                        || SUBSTR (rGr.Gr_store, 3, 3)
                       );
            vsite_disp := SUBSTR (rGr.Gr_store, 3, 3);
         END IF;
      END LOOP;
      
   END;

-- *********************************************
   PROCEDURE temp2orawksh (
      vinvoice_id                 ap_invoices_interface.invoice_id%TYPE,
      vinvoice_num                ap_invoices_interface.invoice_num%TYPE,
      vinvoice_type_lookup_code   ap_invoices_interface.invoice_type_lookup_code%TYPE,
      vinvoice_date               ap_invoices_interface.invoice_date%TYPE,
      vvendor_num                 ap_invoices_interface.vendor_num%TYPE,
      vvendor_site_code           ap_invoices_interface.vendor_site_code%TYPE,
      vinvoice_amount             ap_invoices_interface.invoice_amount%TYPE,
      vinvoice_currency_code      ap_invoices_interface.invoice_currency_code%TYPE,
      vterms_id                   ap_invoices_interface.terms_id%TYPE,
      vterms_name                 ap_invoices_interface.terms_name%TYPE,
      vdescription                ap_invoices_interface.description%TYPE,
      vpay_group_lookup_code      ap_invoices_interface.pay_group_lookup_code%TYPE,
      vgl_date                    ap_invoices_interface.gl_date%TYPE,
      vterms_date                 ap_invoices_interface.terms_date%TYPE,
      vattribute2                 ap_invoices_interface.attribute2%TYPE,
      vorg_id                     ap_invoices_interface.org_id%TYPE,
      vattribute3                 VARCHAR2,
      vattribute4                 VARCHAR2
   )
   IS
   BEGIN

      INSERT INTO ap_invoices_interface
                  (invoice_id, invoice_num, invoice_type_lookup_code,
                   invoice_date, vendor_num, vendor_site_code,
                   invoice_amount, invoice_currency_code, terms_id,
                   terms_name, description, creation_date, created_by,
                   SOURCE, doc_category_code, payment_method_lookup_code,
                   pay_group_lookup_code, gl_date, terms_date,
                   attribute2, org_id, attribute3, attribute4
                  )
           VALUES (vinvoice_id, vinvoice_num, vinvoice_type_lookup_code,
                   vinvoice_date, vvendor_num, vvendor_site_code,
                   vinvoice_amount, vinvoice_currency_code, vterms_id,
                   vterms_name, vdescription, SYSDATE, '3091',
                   'G.O.L.D', 'STD INV', 'CHECK',
                   vpay_group_lookup_code, vgl_date, vterms_date,
                   vattribute2, vorg_id, vattribute3, vattribute4
                  );
   EXCEPTION
      WHEN DUP_VAL_ON_INDEX
      THEN
         DBMS_OUTPUT.put_line ('ERROR : Invoice ID : ' || vinvoice_id);
      WHEN OTHERS
      THEN
         fnd_file.put_line
                (fnd_file.LOG,
                    'ERROR Insert Into AP_INVOICES_INTERFACE, Invoice num : '
                 || vinvoice_num
                 || ' Attribute 3 :'
                 || vattribute3
                );
   END;

-- *********************************************\
   PROCEDURE temp2orawksd (
      vinvoice_id               ap_invoice_lines_interface.invoice_id%TYPE,
      vinvoice_line_id          ap_invoice_lines_interface.invoice_line_id%TYPE,
      vamount                   ap_invoice_lines_interface.amount%TYPE,
      vaccounting_date          ap_invoice_lines_interface.accounting_date%TYPE,
      vdescription              ap_invoice_lines_interface.description%TYPE,
      vdist_code_concatenated   ap_invoice_lines_interface.dist_code_concatenated%TYPE,
      vattribute1               ap_invoice_lines_interface.attribute1%TYPE,
      vorg_id                   ap_invoice_lines_interface.org_id%TYPE,
      vattribute3               VARCHAR2,
      vattribute4               VARCHAR2
   )
   IS
   BEGIN
      DBMS_OUTPUT.put_line ('Masuk ke Detail');
      DBMS_OUTPUT.put_line ('Invoice ID : ' || vinvoice_id);
      DBMS_OUTPUT.put_line ('Invoice Line_ID' || vinvoice_line_id);
      DBMS_OUTPUT.put_line (' ');

      INSERT INTO ap_invoice_lines_interface
                  (invoice_id, invoice_line_id, line_number,
                   line_type_lookup_code, amount, accounting_date,
                   description, dist_code_concatenated, created_by,
                   creation_date, attribute1, org_id, attribute3, attribute4
                  )
           VALUES (vinvoice_id, vinvoice_line_id, 1,
                   'ITEM', vamount, vaccounting_date,
                   vdescription, vdist_code_concatenated, '1091',
                   SYSDATE, vattribute1, vorg_id, vattribute3, vattribute4
                  );
   EXCEPTION
      WHEN DUP_VAL_ON_INDEX
      THEN
         fnd_file.put_line
             (fnd_file.LOG,('ERROR : Invoice ID : ' || vinvoice_id);
         fnd_file.put_line
             (fnd_file.LOG, ('Invoice Line_ID' || vinvoice_line_id);
      WHEN OTHERS
      THEN
         fnd_file.put_line
             (fnd_file.LOG,
                 'ERROR insert into AP_INVOICE_LINES_INTERFACE, Invoice ID: '
              || vinvoice_id
              || ' Attribute 3 :'
              || vattribute3
             );
   END;

-- *********************************************
   PROCEDURE deltempflag_im
   IS
   BEGIN
      DELETE      ap_im_int_ora
            WHERE flag = 'P' AND TRUNC (date_from_Gr) <
                                                         TRUNC (SYSDATE)
                                                         - 120;

      UPDATE ap_im_int_ora
         SET flag = 'X'
       WHERE flag IS NULL
         AND invoice_num LIKE '%CL'
         AND TRUNC (date_from_Gr) = TRUNC (SYSDATE) - 2;
   END;

-- *********************************************
   FUNCTION checkdescr (descr IN VARCHAR2, inv_amount IN NUMBER)
      RETURN NUMBER
   IS
      nreturn   NUMBER        := 0;
      cdesc     VARCHAR2 (80);
      camt      NUMBER;
   BEGIN

      SELECT   description, invoice_amount
          INTO cdesc, camt
          FROM ap_invoices_all
         WHERE description = descr
           AND invoice_type_lookup_code = 'STANDARD'
           AND ROWNUM = 1
      ORDER BY creation_date DESC;

      IF cdesc = descr AND NVL (camt, 0) <> inv_amount
      THEN
         nreturn := 1;
      ELSE
         nreturn := 0;
      END IF;

      RETURN (nreturn);
   EXCEPTION
      WHEN OTHERS
      THEN
         RETURN nreturn;
   END;

-- *********************************************
   PROCEDURE createcancelim (descr IN VARCHAR2)
   IS
      cnewseq                     ap_invoices_interface.invoice_id%TYPE;
      vinvoice_id                 ap_invoices_all.invoice_id%TYPE;
      vinvoice_num                ap_invoices_all.invoice_num%TYPE;
      vinvoice_type_lookup_code   ap_invoices_all.invoice_type_lookup_code%TYPE;
      vinvoice_date               ap_invoices_all.invoice_date%TYPE;
      vvendor_num                 ap_invoices_interface.vendor_num%TYPE;
      vvendor_site_code           ap_invoices_interface.vendor_site_code%TYPE;
      vvendor_id                  ap_invoices_all.vendor_id%TYPE;
      vvendor_site_id             ap_invoices_all.vendor_site_id%TYPE;
      vinvoice_amount             ap_invoices_all.invoice_currency_code%TYPE;
      vinvoice_currency_code      ap_invoices_all.invoice_currency_code%TYPE;
      vterms_id                   ap_invoices_all.terms_id%TYPE;
      vdescription                ap_invoices_all.description%TYPE;
      vpay_group_lookup_code      ap_invoices_all.pay_group_lookup_code%TYPE;
      vgl_date                    ap_invoices_all.gl_date%TYPE;
      vterms_date                 ap_invoices_all.terms_date%TYPE;
      vattribute2                 ap_invoices_all.attribute2%TYPE;
      vattribute4                 ap_invoices_all.attribute4%TYPE;
      vorg_id                     ap_invoices_all.org_id%TYPE;

      CURSOR detail_data
      IS
         SELECT *
           FROM ap_invoice_distributions_all
          WHERE invoice_id = vinvoice_id;

      rdetail                     detail_data%ROWTYPE;
      nno                         NUMBER;
   BEGIN
     
      SELECT invoice_id, invoice_num, invoice_type_lookup_code,
             invoice_date, vendor_id, vendor_site_id, invoice_amount,
             invoice_currency_code, terms_id, description,
             pay_group_lookup_code, gl_date, terms_date, attribute2,
             org_id, attribute4
        INTO vinvoice_id, vinvoice_num, vinvoice_type_lookup_code,
             vinvoice_date, vvendor_id, vvendor_site_id, vinvoice_amount,
             vinvoice_currency_code, vterms_id, vdescription,
             vpay_group_lookup_code, vgl_date, vterms_date, vattribute2,
             vorg_id, vattribute4
        FROM (SELECT   *
                  FROM ap_invoices_all
                 WHERE description = descr
                   AND invoice_type_lookup_code = 'STANDARD'
              ORDER BY creation_date DESC, invoice_id DESC)
       WHERE ROWNUM = 1;


      SELECT segment1
        INTO vvendor_num
        FROM po_vendors
       WHERE vendor_id = vvendor_id;

      SELECT vendor_site_code
        INTO vvendor_site_code
        FROM po_vendor_sites_all
       WHERE vendor_id = vvendor_id AND vendor_site_id = vvendor_site_id;

      cnewseq :=
         TO_NUMBER (   TO_CHAR (SYSTIMESTAMP, 'yymm')
                    || TO_CHAR (supp_int_pkg.getnewseq ())
                   );

      IF INSTR (vinvoice_num, '(N', 1, 1) != 0
      THEN
         vinvoice_num :=  SUBSTR (vinvoice_num, 1, INSTR (vinvoice_num, '(N', 1, 1) - 1);
      END IF;

      ap_ret_int_pkg.temp2orawksh
                           (cnewseq,
                               vinvoice_num
                            || ' (C-'
                            || TO_CHAR (SYSTIMESTAMP, 'yyyymmdd')
                            || ')',
                            'CREDIT',
                            vinvoice_date,
                            vvendor_num,
                            vvendor_site_code,
                            ROUND (vinvoice_amount, 2) * -1,
                            vinvoice_currency_code,
                            vterms_id,
                            NULL,         
                            vdescription,
                            vpay_group_lookup_code,
                            SYSDATE - 1,
                            vterms_date,
                            vattribute2,
                            vorg_id,
                            vattribute4
                           );

      -- ********* DETAIL **********
      OPEN detail_data;

      LOOP
         FETCH detail_data
          INTO rdetail;

         EXIT WHEN detail_data%NOTFOUND;
         nno := TO_CHAR (supp_int_pkg.getnewseq ());
         ap_ret_int_pkg.temp2orawksd
                         (cnewseq,
                          nno,
                          rdetail.distribution_line_number,
                          rdetail.line_type_lookup_code,
                          ROUND (rdetail.amount, 2) * -1,
                          SYSDATE - 1,
                          rdetail.description,
                          rdetail.amount_includes_tax_flag,
                          NULL,
                          rdetail.dist_code_combination_id,
                          rdetail.attribute1,
                          rdetail.org_id,
                          rdetail.tax_code_override_flag,
                          rdetail.tax_code_id,
                          NULL,
                          rdetail.tax_code_override_flag,
                          vattribute4
                         );
      END LOOP;
   END;

   FUNCTION checkinvnumb (v_invoice IN VARCHAR2)
      RETURN NUMBER
   IS
      nreturn   NUMBER;

   BEGIN
      SELECT   1
          INTO nreturn
          FROM ap_invoices_all
         WHERE invoice_num = v_invoice AND ROWNUM = 1
      ORDER BY ROWID DESC;

      RETURN (nreturn);
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         RETURN (0);     
      WHEN OTHERS
      THEN
         RETURN (1);
   END;
END ap_im_int_pkg;
/