Friday 19 December 2014

Barcode Generation using the data outbound TSV (Tab Separated Value) file-Display row values as column wise




There are so many ways to convert oracle rows to columns.
Those are
  • PIVOT operator
  • XMLAGG function
  • SYS_CONNECT_BY_PATH operator
  • CROSS JOIN
  • STRAGG function
  • LAG OVER function
  • CASE Operator
  • DECODE using RANK Operator and may be more…

I have tried only few among the above and am going to give an example using DECODE and RANK/ROW_NUMBER functions.

Example:
a) Retrieve the data of expenses raised by an employee (row wise display).

SELECT report_header_id, employee_id, invoice_num
  --,(rank() over (partition by employee_id order by report_header_id)) seq,
  -- (row_number() over (partition by employee_id order by report_header_id))
FROM   ap_expense_report_headers_all
 WHERE employee_id = 11923







b) Retrieve the same data in column wise.

SELECT   employee_id, MAX (DECODE (seq, 1, invoice_num, NULL)) expense_1,
         MAX (DECODE (seq, 2, invoice_num, NULL)) expense_2,
         MAX (DECODE (seq, 3, invoice_num, NULL)) expense_3,
         MAX (DECODE (seq, 4, invoice_num, NULL)) expense_4,
         MAX (DECODE (seq, 5, invoice_num, NULL)) expense_5,
         MAX (DECODE (seq, 6, invoice_num, NULL)) expense_6,
         MAX (DECODE (seq, 7, invoice_num, NULL)) expense_7,
         MAX (DECODE (seq, 8, invoice_num, NULL)) expense_8,
         MAX (DECODE (seq, 9, invoice_num, NULL)) expense9
    FROM (SELECT employee_id, invoice_num,
                 ROW_NUMBER () OVER (PARTITION BY employee_id ORDER BY report_header_id)
                                                                          seq
            FROM ap_expense_report_headers_all
           WHERE employee_id = 11923)
GROUP BY employee_id;







Same logic applied and generated the tsv(Tab Separated Value) file using UTL_FILE utility in pl/sql code (i.e. outbound from oracle database).

Sample Code:

CREATE OR REPLACE PROCEDURE xx_barcode_generation_prc (
   errbuff              OUT      VARCHAR2, -- Mandatory parameter for concurrent program executable registration
   retcode              OUT      VARCHAR2, -- Mandatory parameter for concurrent program executable registration
   p_invoice_no         IN       VARCHAR2, -- Parameter1 in concurrent program
   p_transporter_name   IN       VARCHAR2, -- Parameter2 in concurrent program
   p_form38             IN       VARCHAR2  -- Parameter3 in concurrent program
)
IS

   -- Getting row values in column wise
   CURSOR cur_main (p_invoice_no VARCHAR2)
   IS
      SELECT DISTINCT a.po_number, a.invoice_no, a.invoice_date,
                      MAX (DECODE (seq, 1, a.part_no, NULL)) partno_1,
                      MAX (DECODE (seq, 1, a.quantity, NULL)) qty_1,
                      MAX (DECODE (seq, 2, a.part_no, NULL)) partno_2,
                      MAX (DECODE (seq, 2, a.quantity, NULL)) qty_2,
                      MAX (DECODE (seq, 3, a.part_no, NULL)) partno_3,
                      MAX (DECODE (seq, 3, a.quantity, NULL)) qty_3,
                      MAX (DECODE (seq, 4, a.part_no, NULL)) partno_4,
                      MAX (DECODE (seq, 4, a.quantity, NULL)) qty_4,
                      MAX (DECODE (seq, 5, a.part_no, NULL)) partno_5,
                      MAX (DECODE (seq, 5, a.quantity, NULL)) qty_5,
                      MAX (DECODE (seq, 6, a.part_no, NULL)) partno_6,
                      MAX (DECODE (seq, 6, a.quantity, NULL)) qty_6,
                      MAX (DECODE (seq, 7, a.part_no, NULL)) partno_7,
                      MAX (DECODE (seq, 7, a.quantity, NULL)) qty_7,
                      MAX (DECODE (seq, 8, a.part_no, NULL)) partno_8,
                      MAX (DECODE (seq, 8, a.quantity, NULL)) qty_8,
                      MAX (DECODE (seq, 9, a.part_no, NULL)) partno_9,
                      MAX (DECODE (seq, 9, a.quantity, NULL)) qty_9
                 FROM (SELECT po_number, invoice_no, invoice_date, part_no,
                              order_line_id, quantity,
                              RANK () OVER (PARTITION BY invoice_no ORDER BY order_line_id)
                                                                          seq
                         FROM (SELECT   (CASE
                                            WHEN UPPER (oeh.order_type) LIKE
                                                                  '%INTERNAL%'
                                               THEN TO_CHAR (wsh.delivery_id)
                                            ELSE oeh.cust_po_number
                                         END
                                        ) po_number,
                                        jisp.excise_invoice_no invoice_no,
                                        TO_CHAR
                                           (jisp.excise_invoice_date,
                                            'DD-MON-YYYY'
                                           ) invoice_date,
                                        mtl.segment1 part_no,
                                        jisp.quantity quantity,
                                        (  NVL (jisp.selling_price, 0)
                                         * NVL (jisp.quantity, 0)
                                        ) invoice_value,
                                        jisp.order_line_id
                                   FROM jai_om_wsh_lines_all jisp,
                                        wsh_new_deliveries_v wsh,
                                        oe_order_headers_v oeh,
                                        mtl_system_items mtl,
                                        oe_order_lines_v oel
                                  WHERE jisp.order_header_id = oeh.header_id
                                    AND jisp.delivery_id = wsh.delivery_id
                                    AND jisp.order_header_id = oel.header_id
                                    AND jisp.order_line_id = oel.line_id
                                    AND jisp.inventory_item_id =
                                                         mtl.inventory_item_id
                                    AND mtl.organization_id =
                                           (SELECT inventory_organization_id
                                              FROM financials_system_parameters)
                                    --AND OEH.ORG_ID=NVL(:P_ORG_ID,OEH.ORG_ID)
                                    AND jisp.excise_invoice_no =
                                           NVL (p_invoice_no,
                                                jisp.excise_invoice_no
                                               )
                               ORDER BY jisp.order_line_id)) a
             GROUP BY po_number, invoice_no, invoice_date;

   l_file_name      VARCHAR2 (100);
   l_file           UTL_FILE.FILE_TYPE; -- Data type of utl_file
   l_org_id         NUMBER;
   l_sysdate        DATE;
   l_po_number      VARCHAR2 (240);
   l_invoice_no     VARCHAR2 (240);
   l_invoice_date   DATE;
   l_inv_value      NUMBER;
   l_partno1        VARCHAR2 (240);
   l_qty1           NUMBER;
   l_partno2        VARCHAR2 (240);
   l_qty2           NUMBER;
   l_partno3        VARCHAR2 (240);
   l_qty3           NUMBER;
   l_partno4        VARCHAR2 (240);
   l_qty4           NUMBER;
   l_partno5        VARCHAR2 (240);
   l_qty5           NUMBER;
   l_partno6        VARCHAR2 (240);
   l_qty6           NUMBER;
   l_partno7        VARCHAR2 (240);
   l_qty7           NUMBER;
   l_partno8        VARCHAR2 (240);
   l_qty8           NUMBER;
   l_partno9        VARCHAR2 (240);
   l_qty9           NUMBER;
   l_chr            VARCHAR2 (20);
   l_error          VARCHAR2 (400);
BEGIN
   -- Initialize the apps and set org context
--    mo_global.init;
   l_org_id := fnd_profile.VALUE ('ORG_ID');
   mo_global.set_policy_context ('S', l_org_id);
   COMMIT;

   -- Generating the Dynamic File Name using Invoice Number and system date
   l_file_name :=
               'XX' || '_BARCODE' || l_invoice_no || l_sysdate || '.tsv';

   -- Opening file using the utility utl_file in write mode
   l_file :=
      UTL_FILE.FOPEN
            ('/data/erp/db/tech_st/11.1.0/appsutil/outbound/ERPTEST',
             l_file_name,
             'W'
            );

   OPEN cur_main (p_invoice_no);

   LOOP
      FETCH cur_main
       INTO l_po_number, l_invoice_no, l_invoice_date, l_partno1, l_qty1,
            l_partno2, l_qty2, l_partno3, l_qty3, l_partno4, l_qty4,
            l_partno5, l_qty5, l_partno6, l_qty6, l_partno7, l_qty7,
            l_partno8, l_qty8, l_partno9, l_qty9;

      EXIT WHEN cur_main%NOTFOUND;
      fnd_file.put_line (fnd_file.LOG, 'Starting...Inside cursor');

      SELECT SYSDATE
        INTO l_sysdate
        FROM DUAL;

        -- To get the TAB character; ascii value of TAB is 9
      SELECT CHR (9)
        INTO l_chr
        FROM DUAL;

      SELECT   ROUND (SUM ((NVL (selling_price, 0) * NVL (quantity, 0))), 2)
          INTO l_inv_value
          FROM jai_om_wsh_lines_all
         WHERE excise_invoice_no = l_invoice_no AND org_id = l_org_id
      GROUP BY excise_invoice_no;

      fnd_file.put_line
         (fnd_file.LOG,
          '***********************************************************************'
         );
      fnd_file.put_line (fnd_file.LOG, 'Opening File : ' || l_file_name);

        -- Writing the file using the data
      UTL_FILE.PUT_LINE (l_file,
                            l_po_number
                         || l_chr
                         || l_invoice_no
                         || l_chr
                         || l_invoice_date
                         || l_chr
                         || p_form38
                         || l_chr
                         || p_transporter_name
                         || l_chr
                         || l_inv_value
                         || l_chr
                         || l_partno1
                         || l_chr
                         || l_qty1
                         || l_partno2
                         || l_chr
                         || l_qty2
                         || l_partno3
                         || l_chr
                         || l_qty3
                         || l_partno4
                         || l_chr
                         || l_qty4
                         || l_partno5
                         || l_chr
                         || l_qty5
                         || l_partno6
                         || l_chr
                         || l_qty6
                         || l_partno7
                         || l_chr
                         || l_qty7
                         || l_partno8
                         || l_chr
                         || l_qty8
                         || l_partno9
                         || l_chr
                         || l_qty9
                        );
      fnd_file.put_line (fnd_file.LOG, 'Ending File : ' || l_file_name);
      fnd_file.put_line
         (fnd_file.LOG,
          '***********************************************************************'
         );
   END LOOP;
   UTL_FILE.FCLOSE(l_file);
EXCEPTION
   WHEN OTHERS
   THEN
      l_error := SUBSTR (SQLERRM, 1, 300);
      fnd_file.put_line (fnd_file.LOG,
                         'Other error in XX_BARCODE_GENERATION_PRC: ' || l_error
                        );
END xx_barcode_generation_prc;
/






Thanks,
Rajesh