UPDATE REPORT QUERY

/* Formatted on 6/1/2012 1:33:20 PM (QP5 v5.115.810.9015) */
CREATE OR REPLACE PROCEDURE APPS.elango_edi_po_clear_prc (
   errbuf        OUT VARCHAR2,
   retcode       OUT VARCHAR2,
   P_PO_NUM   IN     VARCHAR2
)
AS
   l_node             VARCHAR2 (32767);
   l_txt              VARCHAR2 (32767);
   l_indx             NUMBER;
   l_count            NUMBER;
   l_non_removed_po   VARCHAR (32767);
BEGIN
   l_txt := P_PO_NUM || ',';

   fnd_file.put_line (fnd_file.LOG,'Purchase Order numbers Given as input : ' || P_PO_NUM);
   fnd_file.put_line (fnd_file.LOG, '=== Removed Purchase Orders ===');

   LOOP
      l_indx := INSTR (l_txt, ',');
      EXIT WHEN NVL (l_indx, 0) = 0;
      l_node := TRIM (SUBSTR (l_txt, 1, l_indx - 1));
      l_txt := SUBSTR (l_txt, l_indx + 1);


      UPDATE   mastec.mas_edi_850_po_headers
         SET   attribute7 = 'E'
       WHERE   attribute7 IS NULL AND po_number = l_node;

      COMMIT;

      SELECT   COUNT (po_number)
        INTO   l_count
        FROM   mastec.mas_edi_850_po_headers
       WHERE   po_number = l_node AND attribute7 = 'E';


      IF l_count > 0
      THEN
         fnd_file.put_line (fnd_file.LOG, l_node);
      ELSIF l_count = 0
      THEN
         l_non_removed_po := l_node || CHR (13) || l_non_removed_po;
      END IF;
   END LOOP;

   fnd_file.put_line (fnd_file.LOG, '=== Removed Purchase Orders ===');

   IF l_non_removed_po IS NOT NULL
   THEN
      fnd_file.put_line (fnd_file.LOG, '=== Non Removed Purchase Orders ===');
      fnd_file.put_line (fnd_file.LOG, l_non_removed_po);
      fnd_file.put_line (fnd_file.LOG, '=== Non Removed Purchase Orders ===');
   END IF;
END;