1 Reply Latest reply on Mar 20, 2017 9:02 AM by Kirushna

    Alert that send notification more than 1 time and it is supposed to send it only one time

    2680100

      Dears,

      I have a request to send a notification when the approved releases amount reach 60% of the total amount of the PO but the problem is that the alert sent more than 1 notification at a time sometimes 2 sometimes 3 sometimes 5 notifications sent ,.. kindly find the below steps

       

      1.    Alert on the "On update level" on PO_RELEASES_ALL :

      Alert Name: “XXTE_BLANKET_PO_PERCENTEGE”

      select poh.segment1 as PO_Number,

             poh.blanket_total_amount as po_amount,

             poh.blanket_total_amount *
      (
      60 / 100) Percentage,

             Sum(loc.price_override *
      loc.quantity) Total_release

        INTO &Po_no, &Po_Amount,
      &Percentage, &Total_release

        from po_headers_all        poh,

             po_lines_all          pol,

             PO_RELEASES_ALL       R,

             PO_LINE_LOCATIONS_ALL loc

      where poh.po_header_id =
      pol.po_header_id

         and poh.po_header_id = r.po_header_id

         and poh.po_header_id =
      loc.po_header_id

         and R.PO_RELEASE_ID =
      loc.po_release_id

         and pol.po_line_id =
      loc.po_line_id

         and poh.type_lookup_code = 'BLANKET'

         and poh.authorization_status = 'APPROVED'

         and R.Authorization_Status = 'APPROVED'

         and loc.line_location_id in (select l.location_id

                                        from hr_locations_all l

                                       where l.inactive_dateis null

                                        and l.attribute1 = 50998 --responsibility 'TEData_PR_Customer
      Operation'

                                      )

         and (select Sum(loc.price_override *
      loc.quantity) Total_release

                from po_headers_all        po,

                     po_lines_all          pol,

                   
      PO_RELEASES_ALL       R,

                   
      PO_LINE_LOCATIONS_ALL loc

               where po.po_header_id
      = pol.po_header_id

                 and po.po_header_id =
      r.po_header_id

                 and po.po_header_id =
      loc.po_header_id

                 and R.PO_RELEASE_ID =
      loc.po_release_id

                 and pol.po_line_id =
      loc.po_line_id

                 and po.type_lookup_code = 'BLANKET'

                 and po.authorization_status = 'APPROVED'

                 and R.Authorization_Status = 'APPROVED'

                 and poh.currency_code <> 'EGP'

                 and po.po_header_id = poh.po_header_id---53681 --55481

                 and loc.line_location_id in (select l.location_id

                                               from hr_locations_all l

                                              where l.inactive_date is null

                                                 and l.attribute1 = 50998 --responsibility 'TEData_PR_Customer Operation'

                                            
      )) >=

             (poh.blanket_total_amount *
      (
      60 / 100))

         and R.rowid = :rowid

      Group by poh.segment1,

                poh.blanket_total_amount,

                poh.blanket_total_amount
      * (
      60 / 100)