eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.PO_WF_PO_NOTIFICATION SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 117


   Old tax select:
  SELECT nvl(sum(nonrecoverable_tax), 0)
    INTO l_tax_amt
    FROM po_lines pol,
         po_distributions pod
   WHERE pol.po_header_id = l_document_id
     AND pod.po_line_id = pol.po_line_id;

Line: 134


    SELECT sum( round (POD.nonrecoverable_tax *
                       decode(quantity_ordered,
                              NULL,
                              (nvl(POD.amount_ordered,0) - nvl(POD.amount_cancelled,0)) / nvl(POD.amount_ordered, 1),
                              (nvl(POD.quantity_ordered,0) - nvl(POD.quantity_cancelled,0)) / nvl(POD.quantity_ordered, 1)
                             ) / X_min_acct_unit
                       ) * X_min_acct_unit
              )
    INTO l_tax_amt
    FROM po_lines pol,
         po_distributions pod
   WHERE pol.po_header_id = l_document_id
     AND pod.po_line_id = pol.po_line_id;

Line: 148


    SELECT sum( round (POD.nonrecoverable_tax *
                       decode(quantity_ordered,
                              NULL,
                              (nvl(POD.amount_ordered,0) - nvl(POD.amount_cancelled,0)) / nvl(POD.amount_ordered, 1),
                              (nvl(POD.quantity_ordered,0) - nvl(POD.quantity_cancelled,0)) / nvl(POD.quantity_ordered, 1)
                             ),
                       X_precision
                      )
              )
    INTO l_tax_amt
    FROM po_lines pol,
         po_distributions pod
   WHERE pol.po_header_id = l_document_id
     AND pod.po_line_id = pol.po_line_id;

Line: 311


SELECT pol.line_num,
       msi.concatenated_segments,
       pol.item_revision,
       pol.item_description,
--     pol.unit_meas_lookup_code, -- bug 2401933.remove
       nvl(muom.unit_of_measure_tl, pol.unit_meas_lookup_code), -- bug 2401933.add
       pol.quantity,
       pol.unit_price,
       nvl(pol.amount, pol.quantity * pol.unit_price)
  FROM po_lines   pol,
       mtl_system_items_kfv   msi,
       mtl_units_of_measure   muom,     -- bug 2401933.add
       financials_system_parameters  fsp
 WHERE pol.po_header_id = v_document_id
   AND pol.item_id = msi.inventory_item_id(+)
   AND NVL(msi.organization_id, fsp.inventory_organization_id) =
          fsp.inventory_organization_id
/* Bug 2299484 fixed. prevented the canceled lines to be displayed
   in notifications.
*/
   AND NVL(pol.cancel_flag,'N') = 'N'
   AND muom.unit_of_measure (+) = pol.unit_meas_lookup_code  -- bug 2401933.add
 ORDER BY pol.line_num;

Line: 366


SELECT pll.shipment_num,
       msi.concatenated_segments,
       pol.item_revision,
       pol.item_description,
-- Bug 2401933.start
--     nvl(pll.unit_meas_lookup_code, pol.unit_meas_lookup_code)
--         unit_meas_lookup_code,
       nvl(muom.unit_of_measure_tl, pol.unit_meas_lookup_code),
-- Bug 2401933.end
       pll.quantity,
       nvl(pll.price_override, pol.unit_price) unit_price,
       hrl.location_code,
       ood.organization_name,
       pll.need_by_date,
       pll.promised_date,
       pll.shipment_type,
       --Bug 4950850 Added pll.amount
       --Bug 5563024 AMOUNT NOT SHOWN FOR A RELEASE SHIPMENT IN APPROVAL NOTIFICATION.
       nvl(pll.amount, nvl(pll.price_override, pol.unit_price) * pll.quantity)
  FROM po_lines   pol,
       po_line_locations pll,
       mtl_system_items_kfv msi,
       hr_locations_all hrl,
       hz_locations hz,
       org_organization_definitions ood,
       mtl_units_of_measure   muom,     -- Bug 2401933.add
       financials_system_parameters  fsp
  where  PLL.PO_RELEASE_ID = v_document_id
  and    PLL.po_line_id    = POL.po_line_id
  and    PLL.ship_to_location_id = HRL.location_id (+)
  and    PLL.ship_to_location_id = HZ.location_id (+)
  and    PLL.ship_to_organization_id = OOD.organization_id
  and    pol.item_id = msi.inventory_item_id(+)
  and    NVL(msi.organization_id, fsp.inventory_organization_id) =
          fsp.inventory_organization_id
 /* Bug 2299484 fixed. prevented the canceled shipments to be displayed
   in notifications.
*/
   AND NVL(PLL.cancel_flag,'N') = 'N'
   AND muom.unit_of_measure (+) = pol.unit_meas_lookup_code  -- Bug 2401933.add
  order by Shipment_num asc;

Line: 1003


    SELECT poh.SEQUENCE_NUM,
           per.FULL_NAME,
           polc.DISPLAYED_FIELD,
           poh.ACTION_DATE,
           poh.NOTE,
           poh.OBJECT_REVISION_NUM,
           poh.employee_id, /* bug 2788683 */
           poh.created_by /* bug 2788683 */
      from po_action_history  poh,
           per_all_people_f   per, -- Bug 3404451
           po_lookup_codes    polc
     where OBJECT_TYPE_CODE = v_doc_type_code
       and poh.action_code = polc.lookup_code
       and POLC.LOOKUP_TYPE IN ('APPROVER ACTIONS','CONTROL ACTIONS')
       and per.person_id(+) = poh.employee_id /* bug 2788683 */
       and trunc(sysdate) between per.effective_start_date(+)
                              and per.effective_end_date(+)
       and OBJECT_ID = v_document_id
    UNION ALL
    SELECT poh.SEQUENCE_NUM,
           per.FULL_NAME,
           NULL,
           poh.ACTION_DATE,
           poh.NOTE,
           poh.OBJECT_REVISION_NUM,
           poh.employee_id, /* bug 2788683 */
           poh.created_by /* bug 2788683 */
      from po_action_history  poh,
           per_all_people_f   per -- Bug 3404451
     where OBJECT_TYPE_CODE = v_doc_type_code
       and poh.action_code is null
       and per.person_id(+) = poh.employee_id /* bug 2788683 */
       and trunc(sysdate) between per.effective_start_date(+)
                              and per.effective_end_date(+)
       and OBJECT_ID = v_document_id
   order by 1 desc;

Line: 1225


      /* if action history is updated by vendor
       *    show vendor true name(vendor name)
       * else action history is updated by buyer
       *    show buyer's true name
       */
      IF l_employee_id_tbl(i) IS NULL THEN
         SELECT fu.user_name,
                hp.party_name
           INTO l_user_name,
                l_party_name
           FROM fnd_user fu,
                hz_parties hp
          WHERE hp.party_id = fu.customer_id
            AND fu.user_id = l_created_by_tbl(i);

Line: 1302


      /* if action history is updated by vendor
       *    show vendor true name(vendor name)
       * else action history is updated by buyer
       *    show buyer's true name
       */
      IF l_employee_id_tbl(i) IS NULL THEN
         SELECT fu.user_name, hp.party_name
           INTO l_user_name, l_party_name
           FROM fnd_user fu,
                hz_parties hp
          WHERE hp.party_id = fu.customer_id
            AND fu.user_id = l_created_by_tbl(i);

Line: 1364


    SELECT fu.USER_ID
      INTO l_responder_id
      FROM fnd_user fu,
           wf_notifications wfn
     WHERE wfn.notification_id = l_nid
       AND wfn.original_recipient = fu.user_name;

Line: 1379


          /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);

Line: 1386


          /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);

Line: 1414


          /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);

Line: 1434


             /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);

Line: 1528


             /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);

Line: 1583


  /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);

Line: 1594


  /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);

Line: 1606


  /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);

Line: 1621


     PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);

Line: 1632


     /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);

Line: 1719


  /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);

Line: 1761


  select type_name
  from po_document_types_tl tl, FND_LANGUAGES fl
  where fl.nls_language = p_language
  and   tl.LANGUAGE = fl.language_code
  and   tl.document_type_code = p_doc_type
  and   tl.document_subtype = p_doc_subtype;

Line: 1769


  select type_name
  from po_document_types
  where document_type_code = p_doc_type
  and   document_subtype = p_doc_subtype;

Line: 1779


  /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);

Line: 1803


   Deleted the previous code and revamped it.
  */
   --
   l_document_id := PO_WF_UTIL_PKG.GetItemAttrNumber
                                       (itemtype   => itemtype,
                                        itemkey    => itemkey,
                                        aname      => 'DOCUMENT_ID');

Line: 1813


        /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);

Line: 1826


        /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);

Line: 1836


        /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);

Line: 1851


        /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);

Line: 1858


       select message_text
       into l_msg_text
       from fnd_new_messages fm,fnd_languages fl
       where fm.message_name = 'PO_WF_NOTIF_REQUIRES_APPROVAL'
       and fm.language_code = fl.language_code
       and fl.nls_language = l_language
       and fm.application_id = 201;  -- Include application_id to better use PK index

Line: 1880


    /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);

Line: 1942


l_progress:='000'; PO_WF_DEBUG_PKG.insert_debug(p_itemtype,p_itemkey,l_log_head||':'||l_progress || 'Notification Type = ' || p_notification_type);

Line: 1953


l_progress:='010'; PO_WF_DEBUG_PKG.insert_debug(p_itemtype,p_itemkey,l_log_head||':'||l_progress||' Authorization Status = '||l_authorization_status);

Line: 1965


        PO_WF_DEBUG_PKG.insert_debug(p_itemtype,p_itemkey,l_log_head||':'||l_progress||':FALSE');

Line: 1970


    PO_WF_DEBUG_PKG.insert_debug(p_itemtype,p_itemkey,l_log_head||':'||l_progress||':TRUE');

Line: 1976


        PO_WF_DEBUG_PKG.insert_debug(p_itemtype,p_itemkey,l_log_head||':'||SQLERRM);