Tuesday 1 November 2011

xx_tax_load_backup

/* Formatted on 12-08-2011 00:36:38 (QP5 v5.115.810.9015) */
 /* CREATE TABLE xx_r12tax_load
(
   Operating_unit   VARCHAR2 (75),
   Po_no            VARCHAR2 (25),
   po_line_no       NUMBER,
   tax_name         VARCHAR2 (50),
   tax_type         VARCHAR2 (50),
   tax_line_no      NUMBER,
   precedence_1     NUMBER,
   precedence_2     NUMBER,
   precedence_3     NUMBER,
   precedence_4     NUMBER,
   precedence_5     NUMBER,
   tax_rate         NUMBER,
   tax_amount       NUMBER,
   Loaded_status    varchar2(4)  
);
DROP TABLE xx_r12tax_load; */
CREATE OR REPLACE PROCEDURE APPS.XX_TAX_LOAD (retcode   OUT VARCHAR2,
                                              errbuf    OUT VARCHAR2)
IS
   CURSOR c1
   IS
      SELECT   * FROM xx_r12tax_load;
   CURSOR c2 (po_header_id IN NUMBER)
   IS
        SELECT   *
          FROM   po_lines_all
         WHERE   po_header_id = po_header_id
      ORDER BY   po_header_id, po_line_id, LINE_NUM;
   CURSOR c3 (po_no IN NUMBER, po_line_no NUMBER)
   IS
        SELECT   OPERATING_UNIT,
                 TO_NUMBER (PO_NO),
                 PO_LINE_NO,
                 TAX_NAME,
                 TAX_TYPE,
                 TAX_LINE_NO,
                 PRECEDENCE_1,
                 PRECEDENCE_2,
                 PRECEDENCE_3,
                 PRECEDENCE_4,
                 PRECEDENCE_5,
                 TAX_RATE,
                 TAX_AMOUNT
          FROM   xx_r12tax_load
         WHERE   PO_NO = po_no AND PO_LINE_NO = po_line_no
      ORDER BY   TO_NUMBER (PO_NO), PO_LINE_NO, TAX_LINE_NO;
   v_rec_count        NUMBER;
   v_operating_unit   VARCHAR2 (50);
   v_po_count         NUMBER;
   v_po_header_id     NUMBER;
   v_po_line_cnt      NUMBER;
   v_po_line_id       NUMBER;
   v_po_tax_count     NUMBER;
   v_po_tax_lne_cnt   NUMBER;
   v_tax_id           VARCHAR2 (50);
   v_po_line_locid    NUMBER;
   v_po_ln_focid      NUMBER;
BEGIN
   FOR rec1 IN c1
   LOOP
      DBMS_OUTPUT.put_line(   '*************************************'
                           || rec1.PO_NO
                           || '********************************');
      --------------------------------------------------------------------------------
      fnd_file.put_line (
         fnd_file.LOG,
            '*************************************'
         || rec1.PO_NO
         || '********************************'
      );
      BEGIN
         SELECT   COUNT ( * )
           INTO   v_rec_count
           FROM   hr_operating_units
          WHERE   name = rec1.OPERATING_UNIT;
      END;
      IF v_rec_count > 0
      THEN
         BEGIN
              SELECT   COUNT ( * ), po_header_id
                --------------------------------------------------------------------------------
                INTO   v_po_count, v_po_header_id
                FROM   po_headers_all
               WHERE   segment1 = rec1.Po_no
            GROUP BY   po_header_id;
            IF v_po_count > 0
            THEN
               DBMS_OUTPUT.put_line('*************************************'
                                    || 'Purchase Order --> '
                                    || rec1.PO_NO
                                    || '********************************');
               --------------------------------------------------------------------------------
               fnd_file.put_line (
                  fnd_file.LOG,
                     '*************************************'
                  || 'Purchase Order --> '
                  || rec1.PO_NO
                  || '********************************'
               );
               BEGIN
                  --                  FOR rec3 IN c3 (v_po_header_id)
                  --                  LOOP
                  --                     DBMS_OUTPUT.put_line ('Test');
                  --                  END LOOP;
                  --                  SELECT   COUNT ( * ), po_line_id
                  --                    INTO   v_po_line_cnt, v_po_line_id
                  --                    FROM   po_lines_all
                  --                   WHERE   po_header_id = v_po_header_id;
                  --                  IF v_po_line_cnt > 0
                  --                  THEN
                  BEGIN
                     FOR rec2 IN c2 (v_po_header_id)
                     LOOP
                        DBMS_OUTPUT.put_line('*************************************'
                                             || 'Purchase Order Line Details --> '
                                             || 'PO HEADER ID --> '
                                             || rec2.po_header_id
                                             || 'PO LINE ID --> '
                                             || rec2.po_line_id
                                             || ' PO LINE NO --> '
                                             || rec2.LINE_NUM
                                             || '********************************');
                        --------------------------------------------------------------------------------
                        fnd_file.put_line (
                           fnd_file.LOG,
                              '*************************************'
                           || 'Purchase Order Line Details --> '
                           || 'PO HEADER ID --> '
                           || rec2.po_header_id
                           || 'PO LINE ID --> '
                           || rec2.po_line_id
                           || ' PO LINE NO --> '
                           || rec2.LINE_NUM
                           || '********************************'
                        );
                        /* BEGIN
                            SELECT   line_location_id, line_focus_id
                              INTO   v_po_line_locid, v_po_ln_focid
                              FROM   jai_po_line_locations
                             WHERE   po_header_id = rec2.po_header_id
                                     AND po_line_id = rec2.po_line_id;
                            DBMS_OUTPUT.put_line(' line_location_id, line_focus_id ' --------------------------------------------------------------------------------
                                                 || v_po_line_locid
                                                 || ' , '
                                                 || v_po_ln_focid);
                            fnd_file.put_line (
                               fnd_file.LOG,
                                  ' line_location_id, line_focus_id '
                               || v_po_line_locid
                               || ' , '
                               || v_po_ln_focid
                            );
                         END; */
                        BEGIN
                           FOR rec3 IN c3 (rec1.Po_no, rec2.LINE_NUM)
                           LOOP
                              DBMS_OUTPUT.put_line('*************************************'
                                                   || 'Purchase Order Tax Line Details --> '
                                                   --                                                   || 'PO NO --> '
                                                   --                                                   || rec3.PO_NO
                                                   --                                                   || 'PO LINE ID --> '
                                                   --                                                   || rec3.PO_LINE_NO
                                                   --                                                   || 'PO LINE NO --> '
                                                   --                                                   || rec3.TAX_LINE_NO
                                                   --                                                   || 'PO TAX NAME --> '
                                                   --                                                   || rec3.TAX_NAME
                                                   || '********************************');
                              --------------------------------------------------------------------------------
                              fnd_file.put_line (
                                 fnd_file.LOG,
                                    '*************************************'
                                 || 'Purchase Order Tax Line Details --> '
                                 --                                 || 'PO NO --> '
                                 --                                 || rec3.PO_NO
                                 --                                 || 'PO LINE ID --> '
                                 --                                 || rec3.PO_LINE_NO
                                 --                                 || 'PO LINE NO --> '
                                 --                                 || rec3.TAX_LINE_NO
                                 --                                 || 'PO TAX NAME --> '
                                 --                                 || rec3.TAX_NAME
                                 || '********************************'
                              );
                              BEGIN
                                 SELECT   COUNT ( * )
                                   --------------------------------------------------------------------------------
                                   INTO   v_po_tax_count
                                   FROM   JAI_PO_TAXES
                                  WHERE       PO_HEADER_ID = v_po_header_id
                                          AND po_line_id = rec2.po_line_id
                                          AND TAX_LINE_NO = rec3.TAX_LINE_NO;
                                 IF v_po_tax_count > 0
                                 THEN
                                    BEGIN
                                       SELECT   tax_id
                                         INTO   v_tax_id
                                         FROM   jai_cmn_taxes_all
                                        WHERE   UPPER (tax_name) =
                                                   UPPER (rec3.TAX_NAME);
                                       DBMS_OUTPUT.put_line('*************************************'
                                                            || ' Tax ID --> '
                                                            || v_tax_id
                                                            || ' Tax Name --> '
                                                            || UPPER(rec3.TAX_NAME)
                                                            || '********************************');
                                       --------------------------------------------------------------------------------
                                       fnd_file.put_line (
                                          fnd_file.LOG,
                                          '*************************************'
                                          || ' Tax ID --> '
                                          || v_tax_id
                                          || ' Tax Name --> '
                                          || UPPER (rec3.TAX_NAME)
                                          || '********************************'
                                       );
                                    END;
                                 ELSE
                                    IF rec3.TAX_NAME LIKE '%'
                                    THEN
                                       DBMS_OUTPUT.put_line('*************************************'
                                                            || 'Purchase Order Precedence Tax Insert Block --> '
                                                            || '********************************');
                                       --------------------------------------------------------------------------------
                                       fnd_file.put_line (
                                          fnd_file.LOG,
                                          '*************************************'
                                          || 'Purchase Order Precedence Tax Insert Block --> '
                                          || '********************************'
                                       );
                                       INSERT INTO jai_po_taxes (
                                                                    TAX_ID,
                                                                    PRECEDENCE_1,
                                                                    PRECEDENCE_2,
                                                                    PRECEDENCE_3,
                                                                    PRECEDENCE_4,
                                                                    PRECEDENCE_5,
                                                                    PO_HEADER_ID,
                                                                    po_line_id,
                                                                    TAX_LINE_NO,
                                                                    TAX_TYPE,
                                                                    TAX_AMOUNT,
                                                                    LINE_FOCUS_ID,
                                                                    LINE_LOCATION_ID
                                                  )
                                         VALUES   (v_tax_id,
                                                   rec3.PRECEDENCE_1,
                                                   rec3.PRECEDENCE_2,
                                                   rec3.PRECEDENCE_3,
                                                   rec3.PRECEDENCE_4,
                                                   rec3.PRECEDENCE_5,
                                                   v_po_header_id,
                                                   rec2.po_line_id,
                                                   rec3.TAX_LINE_NO,
                                                   rec3.TAX_TYPE,
                                                   rec3.TAX_AMOUNT,
                                                   13620,
                                                   167001);
                                       COMMIT;
                                    ELSE
                                       BEGIN
                                          DBMS_OUTPUT.put_line('*************************************'
                                                               || 'Purchase Order Lumpsum Tax Insert Block --> '
                                                               || '********************************');
                                          --------------------------------------------------------------------------------
                                          fnd_file.put_line (
                                             fnd_file.LOG,
                                             '*************************************'
                                             || 'Purchase Order Lumpsum Tax Insert Block --> '
                                             || '********************************'
                                          );
                                          INSERT INTO jai_po_taxes (
                                                                       TAX_ID,
                                                                       PO_HEADER_ID,
                                                                       po_line_id,
                                                                       TAX_LINE_NO,
                                                                       TAX_AMOUNT,
                                                                       TAX_TYPE
                                                     )
                                            VALUES   (v_tax_id,
                                                      v_po_header_id,
                                                      rec2.po_line_id,
                                                      rec3.TAX_LINE_NO,
                                                      rec3.TAX_AMOUNT,
                                                      rec3.TAX_TYPE);
                                          COMMIT;
                                       END;
                                    END IF;
                                 END IF;
                              END;
                           END LOOP;
                        END;
                     END LOOP;
                  END;
               --                  ELSE
               --                     DBMS_OUTPUT.put_line ('Test');
               --                  END IF;
               END;
            ELSE
               DBMS_OUTPUT.put_line('PO Not Exists In Oracle Base tb :'
                                    || rec1.PO_NO);
               fnd_file.put_line --------------------------------------------------------------------------------
                                 (
                  fnd_file.LOG,
                  'PO Not Exists In Oracle Base tb :'
                  || rec1.PO_NO
               );
            END IF;
            DBMS_OUTPUT.put_line ('Before end of the loop');
         END;
      ELSE
         DBMS_OUTPUT.put_line ('End of the Loop ');
      END IF;
   END LOOP;
END;

No comments:

Post a Comment