Wednesday 27 April 2016

PO Match Option Update in oracle apps r12

/* Formatted on 4/27/2016 11:43:46 AM (QP5 v5.114.809.3010) */
CREATE OR REPLACE PROCEDURE XX_PO_MATCH_OPTION_UPDATE (
   ERRBUF           OUT VARCHAR2,
   RETCODE          OUT NUMBER,
   p_header_id   IN     NUMBER
)
AS
   n_count1   NUMBER := 0;
   n_count2   NUMBER := 0;
   n_count3   NUMBER := 0;

   CURSOR c1
   IS
      SELECT   pll.po_header_id, pll.line_location_id
        FROM   po_headers_all poh, po_line_locations_all pll
       WHERE       poh.po_header_id = pll.po_header_id
               AND poh.org_id = 101
               AND poh.org_id = pll.org_id
               AND pll.receipt_required_flag = 'Y'
               AND pll.inspection_required_flag = 'N'
               AND match_option = 'P'
               AND poh.po_header_id = p_header_id;

   CURSOR c2
   IS
      SELECT   pll.po_header_id, pll.line_location_id
        FROM   po_headers_all poh, po_line_locations_all pll
       WHERE       poh.po_header_id = pll.po_header_id
               AND poh.org_id = 101
               AND poh.org_id = pll.org_id
               AND pll.receipt_required_flag = 'Y'
               AND pll.inspection_required_flag = 'N'
               AND match_option = 'R'
               AND pll.accrue_on_receipt_flag = 'N'
               AND poh.po_header_id = p_header_id;

   CURSOR c3
   IS
      SELECT   pll.po_header_id, pll.line_location_id
        FROM   po_headers_all poh, po_line_locations_all pll
       WHERE       poh.po_header_id = pll.po_header_id
               AND poh.org_id = 101
               AND poh.org_id = pll.org_id
               AND pll.receipt_required_flag = 'Y'
               AND pll.inspection_required_flag = 'N'
               AND match_option = 'P'
               AND pll.accrue_on_receipt_flag = 'N'
               AND poh.po_header_id = p_header_id;
BEGIN
   --CASE 1
   FOR i1 IN c1
   LOOP
      BEGIN
         UPDATE   po_line_locations_all
            SET   match_option = 'R'
          WHERE   line_location_id = i1.line_location_id;

         DBMS_OUTPUT.put_line(   ' PO_HEADER_ID '
                              || i1.po_header_id
                              || '  - '
                              || ' PO_LINE_LOCATION_ID '
                              || i1.line_location_id
                              || ' IS  UPDATED');
         fnd_file.put_line (
            fnd_file.output,
               ' PO_HEADER_ID '
            || i1.po_header_id
            || '  - '
            || ' PO_LINE_LOCATION_ID '
            || i1.line_location_id
            || ' IS  UPDATED'
         );
         n_count1 := n_count1 + 1;
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line(   ' PO_HEADER_ID '
                                 || i1.po_header_id
                                 || '  - '
                                 || ' PO_LINE_LOCATION_ID '
                                 || i1.line_location_id
                                 || ' IS NOT UPDATED');
            fnd_file.put_line (
               fnd_file.LOG,
                  ' PO_HEADER_ID '
               || i1.po_header_id
               || '  - '
               || ' PO_LINE_LOCATION_ID '
               || i1.line_location_id
               || ' IS NOT UPDATED'
            );
      END;
   END LOOP;

   DBMS_OUTPUT.put_line (' NO OF RECORDS UPDATED IN CASE 1 ' || n_count1);
   fnd_file.put_line (fnd_file.output,
                      ' NO OF RECORDS UPDATED IN CASE 1 ' || n_count1);

   --CASE 2
   FOR i2 IN c2
   LOOP
      BEGIN
         UPDATE   po_line_locations_all
            SET   accrue_on_receipt_flag = 'Y'
          WHERE   line_location_id = i2.line_location_id;

         DBMS_OUTPUT.put_line(   ' PO_HEADER_ID '
                              || i2.po_header_id
                              || '  - '
                              || ' PO_LINE_LOCATION_ID '
                              || i2.line_location_id
                              || ' IS  UPDATED');
         fnd_file.put_line (
            fnd_file.output,
               ' PO_HEADER_ID '
            || i2.po_header_id
            || '  - '
            || ' PO_LINE_LOCATION_ID '
            || i2.line_location_id
            || ' IS  UPDATED'
         );
         n_count2 := n_count2 + 1;
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line(   ' PO_HEADER_ID '
                                 || i2.po_header_id
                                 || '  - '
                                 || ' PO_LINE_LOCATION_ID '
                                 || i2.line_location_id
                                 || ' IS NOT UPDATED');
            fnd_file.put_line (
               fnd_file.LOG,
                  ' PO_HEADER_ID '
               || i2.po_header_id
               || '  - '
               || ' PO_LINE_LOCATION_ID '
               || i2.line_location_id
               || ' IS NOT UPDATED'
            );
      END;
   END LOOP;

   DBMS_OUTPUT.put_line (' NO OF RECORDS UPDATED IN CASE 2 ' || n_count2);
   fnd_file.put_line (fnd_file.output,
                      ' NO OF RECORDS UPDATED IN CASE 2 ' || n_count2);

   --CASE 3
   FOR i3 IN c3
   LOOP
      BEGIN
         UPDATE   po_line_locations_all
            SET   accrue_on_receipt_flag = 'Y', match_option = 'R'
          WHERE   line_location_id = i3.line_location_id;

         DBMS_OUTPUT.put_line(   ' PO_HEADER_ID '
                              || i3.po_header_id
                              || '  - '
                              || ' PO_LINE_LOCATION_ID '
                              || i3.line_location_id
                              || ' IS  UPDATED');
         fnd_file.put_line (
            fnd_file.output,
               ' PO_HEADER_ID '
            || i3.po_header_id
            || '  - '
            || ' PO_LINE_LOCATION_ID '
            || i3.line_location_id
            || ' IS  UPDATED'
         );
         n_count3 := n_count3 + 1;
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line(   ' PO_HEADER_ID '
                                 || i3.po_header_id
                                 || '  - '
                                 || ' PO_LINE_LOCATION_ID '
                                 || i3.line_location_id
                                 || ' IS NOT UPDATED');
            fnd_file.put_line (
               fnd_file.LOG,
                  ' PO_HEADER_ID '
               || i3.po_header_id
               || '  - '
               || ' PO_LINE_LOCATION_ID '
               || i3.line_location_id
               || ' IS NOT UPDATED'
            );
      END;
   END LOOP;

   DBMS_OUTPUT.put_line (' NO OF RECORDS UPDATED IN CASE 3 ' || n_count3);
   fnd_file.put_line (fnd_file.output,
                      ' NO OF RECORDS UPDATED IN CASE 3 ' || n_count3);
END xx_po_match_option_update;

                 *********************************************************
exec po_match_option_update( 50428);

No comments:

Post a Comment