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_APPROVAL_ACTION SQL Statements

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

Line: 39


  select nvl(PRH.authorization_status,'INCOMPLETE')
  into l_authorization_status
  from PO_REQUISITION_HEADERS PRH
  where PRH.requisition_header_id = l_document_id;

Line: 57


    SELECT 'Y'
    into l_code_exist
    FROM   po_requisition_headers prh,
                       po_lookup_codes plc_clo
                WHERE  plc_clo.lookup_code = nvl(prh.closed_code, 'OPEN')
                AND    plc_clo.lookup_type = 'DOCUMENT STATE'
                AND    prh.requisition_header_id =  l_document_id;

Line: 102


    SELECT nvl(POR.authorization_status,'INCOMPLETE'),
                        nvl(POR.closed_code, 'OPEN'),
                        nvl(POR.frozen_flag, 'N'),
                        nvl(POR.hold_flag, 'N')
              	 INTO   l_authorization_status,
			l_head_closed,
                        l_frozen_flag,
                        l_user_hold_flag
              	 FROM   PO_RELEASES POR
                 WHERE  POR.po_release_id = l_document_id;

Line: 113


    SELECT nvl(POH.authorization_status,'INCOMPLETE'),
                        nvl(POH.closed_code,'OPEN'),
                        nvl(POH.frozen_flag,'N'),
                        nvl(POH.user_hold_flag,'N')
                 INTO   l_authorization_status,
			l_head_closed,
                        l_frozen_flag,
                        l_user_hold_flag
                 FROM   PO_HEADERS POH
                 WHERE  po_header_id = l_document_id;

Line: 158


      select 'Y'
      into l_code_exist
              from   po_releases por,
                     po_lookup_codes plc_sta,
                     po_lookup_codes plc_can,
                     po_lookup_codes plc_clo,
                     po_lookup_codes plc_fro,
                     po_lookup_codes plc_hld
              where  plc_sta.lookup_code =
                     decode(por.approved_flag,
                            'R', por.approved_flag,
                                 nvl(por.authorization_status,'INCOMPLETE'))
              and    plc_sta.lookup_type in ('PO APPROVAL', 'DOCUMENT STATE')
              and    plc_can.lookup_code = 'CANCELLED'
              and    plc_can.lookup_type = 'DOCUMENT STATE'
              and    plc_clo.lookup_code = nvl(por.closed_code, 'OPEN')
              and    plc_clo.lookup_type = 'DOCUMENT STATE'
              and    plc_fro.lookup_code = 'FROZEN'
              and    plc_fro.lookup_type = 'DOCUMENT STATE'
              and    plc_hld.lookup_code = 'ON HOLD'
              and    plc_hld.lookup_type = 'DOCUMENT STATE'
              and    por.po_release_id = l_document_id;

Line: 182


      select 'Y'
      into l_code_exist
                 from   po_headers poh,
                     po_lookup_codes plc_sta,
		     po_lookup_codes plc_can,
		     po_lookup_codes plc_clo,
		     po_lookup_codes plc_fro,
 		     po_lookup_codes plc_hld
              where  plc_sta.lookup_code =
                     decode(poh.approved_flag,
                            'R', poh.approved_flag,
                                 nvl(poh.authorization_status, 'INCOMPLETE'))
              and    plc_sta.lookup_type in ('PO APPROVAL', 'DOCUMENT STATE')
	      and    plc_can.lookup_code = 'CANCELLED'
              and    plc_can.lookup_type = 'DOCUMENT STATE'
              and    plc_clo.lookup_code = nvl(poh.closed_code, 'OPEN')
              and    plc_clo.lookup_type = 'DOCUMENT STATE'
              and    plc_fro.lookup_code = 'FROZEN'
              and    plc_fro.lookup_type = 'DOCUMENT STATE'
              and    plc_hld.lookup_code = 'ON HOLD'
              and    plc_hld.lookup_type = 'DOCUMENT STATE'
              and    poh.po_header_id    =  l_document_id;

Line: 224


                            p_last_update_login   in number,
                            p_document_id         in number)
is
pragma AUTONOMOUS_TRANSACTION;

Line: 231


  UPDATE po_requisition_lines_clm_v PORL
  SET PORL.last_update_date = sysdate,
      PORL.last_updated_by = p_user_id,
      PORL.last_update_login =  p_last_update_login,
      PORL.rate =
        (SELECT
           po_core_s.get_conversion_rate (FSP.set_of_books_id, PORL.currency_code, PORL.rate_date,  PORL.rate_type)
         FROM
           FINANCIALS_SYSTEM_PARAMS_ALL FSP,
           GL_SETS_OF_BOOKS SOB
         WHERE
           nvl(FSP.org_id, -9999) = nvl(PORL.org_id, -9999)      AND
           SOB.set_of_books_id    = FSP.set_of_books_id          AND
           SOB.currency_code     <> PORL.currency_code          AND
           PORL.currency_code is not null)
  WHERE   PORL.rate is NULL     AND
    PORL.requisition_header_id = p_document_id     AND
    PORL.rate_type <> 'User'
    AND     nvl(PORL.cancel_flag,'N') = 'N'     AND
    nvl(PORL.closed_code, 'OPEN') <> 'FINALLY CLOSED';

Line: 262


l_last_update_login  number;

Line: 294


  l_last_update_login := l_user_id;

Line: 304


    SELECT federal_flag INTO l_federal_flag
    FROM po_requisition_headers_all
    WHERE requisition_header_id = l_document_id;

Line: 319


    SELECT Count(*) INTO l_Cnt
    FROM po_requisition_lines_all
    WHERE requisition_header_id = l_document_id
    AND Nvl(clm_info_flag,'N') <> 'Y';

Line: 333


                    l_last_update_login,
                    l_document_id);

Line: 336


  select PO_ONLINE_REPORT_TEXT_S.nextval
  into   l_online_report_id
  from SYS.DUAL;

Line: 348


  select 'Y'
  into l_error_occur
  FROM
  PO_REQUISITION_HEADERS PRH
  WHERE  PRH.requisition_header_id = l_document_id
    AND    NOT EXISTS (SELECT 'Lines Exist'     FROM   po_requisition_lines_clm_v
  PRL     WHERE  PRL.requisition_header_id = PRH.requisition_header_id
  AND    nvl(PRL.cancel_flag,'N') = 'N');

Line: 367


    INSERT INTO po_online_report_text  (online_report_id, last_update_login,
      last_updated_by, last_update_date, created_by, creation_date, line_num,
      shipment_num, distribution_num, sequence, text_line)
    VALUES  (
      l_online_report_id, l_last_update_login,     l_user_id, sysdate,
      l_user_id, sysdate,     0, 0, 0, l_sequence, l_msg_text);

Line: 385


  SELECT 'Y'
    INTO l_error_occur
  FROM
    po_requisition_lines_clm_v PRL
  WHERE PRL.requisition_header_id = l_document_id
    AND   nvl(PRL.cancel_flag,'N') = 'N'
    AND nvl(PRL.closed_code,'OPEN') <> 'FINALLY CLOSED'
    AND   nvl(PRL.modified_by_agent_flag,'N') = 'N'
    AND   NOT EXISTS    (SELECT 'Dist Exist'    FROM PO_REQ_DISTRIBUTIONS PRD
          WHERE PRD.requisition_line_id = PRL.requisition_line_id)
    AND rownum=1;

Line: 406


    INSERT INTO po_online_report_text  (online_report_id, last_update_login,
      last_updated_by, last_update_date, created_by, creation_date, line_num,
      shipment_num, distribution_num, sequence, text_line)
    SELECT
      l_online_report_id, l_last_update_login, l_user_id,     sysdate,
      l_user_id,sysdate,PRL.line_num,0,0,l_sequence, 'Line #' || PRL.line_num ||' ' || l_msg_text
    FROM
      po_requisition_lines_clm_v PRL
    WHERE PRL.requisition_header_id = l_document_id
      AND   nvl(PRL.cancel_flag,'N') = 'N'
      AND   nvl(PRL.closed_code,'OPEN') <> 'FINALLY CLOSED'
      AND   nvl(PRL.modified_by_agent_flag,'N') = 'N'
      AND   NOT EXISTS  (SELECT 'Dist Exist'    FROM PO_REQ_DISTRIBUTIONS PRD
            WHERE PRD.requisition_line_id = PRL.requisition_line_id);

Line: 430


  SELECT 'Y'
  INTO l_error_occur
  FROM
    PO_REQ_DISTRIBUTIONS PRD,po_requisition_lines_clm_v PRL
  WHERE
    PRL.requisition_line_id = PRD.requisition_line_id     AND
    PRL.requisition_header_id = l_document_id   AND
    nvl(PRL.cancel_flag,'N') = 'N'
    AND nvl(PRL.closed_code,'OPEN') <> 'FINALLY CLOSED'     AND
    nvl(PRL.modified_by_agent_flag,'N') = 'N' AND
    PRL.quantity <>       (SELECT
           sum(PRD.req_line_quantity)
           FROM PO_REQ_DISTRIBUTIONS PRD
           WHERE
           PRD.requisition_line_id = PRL.requisition_line_id)
           AND rownum=1; */

Line: 448


SELECT 'Y'
      INTO l_error_occur
      FROM
        po_requisition_lines_clm_v PRL
      WHERE
        PRL.requisition_header_id = l_document_id   AND
        nvl(PRL.cancel_flag,'N') = 'N'
        AND nvl(PRL.closed_code,'OPEN') <> 'FINALLY CLOSED'     AND
        nvl(PRL.modified_by_agent_flag,'N') = 'N' AND
        --Start Bug 13065293
        round(PRL.quantity,15) <>       (SELECT
              round(sum(PRD.req_line_quantity),15)
        --End Bug 13065293
               FROM PO_REQ_DISTRIBUTIONS PRD
               WHERE
               PRD.requisition_line_id = PRL.requisition_line_id
               GROUP BY PRD.requisition_line_id)
        AND rownum=1;

Line: 478


    INSERT INTO po_online_report_text(online_report_id,last_update_login,
      last_updated_by,last_update_date,created_by,creation_date,line_num,
      shipment_num,distribution_num,sequence,text_line)
    SELECT l_online_report_id, l_last_update_login, l_user_id,     sysdate,
      l_user_id,sysdate,PRL.line_num,0,0,l_sequence,
      'Line #' || PRL.line_num ||' Quantity '|| to_char (PRL.quantity) || ' ' ||
      l_msg_text || ' ' || to_char(sum(PRD.req_line_quantity))
    FROM
      PO_REQ_DISTRIBUTIONS PRD,po_requisition_lines_clm_v PRL
    WHERE
      PRL.requisition_line_id = PRD.requisition_line_id     AND
      PRL.requisition_header_id = l_document_id   AND
      nvl(PRL.cancel_flag,'N') = 'N'
      AND nvl(PRL.closed_code,'OPEN') <> 'FINALLY CLOSED'     AND
      nvl(PRL.modified_by_agent_flag,'N') = 'N' AND
      PRL.quantity <>       (SELECT
           sum(PRD.req_line_quantity)
           FROM PO_REQ_DISTRIBUTIONS PRD
           WHERE
           PRD.requisition_line_id = PRL.requisition_line_id)
      GROUP BY   PRL.line_num,PRL.quantity;

Line: 508


  INSERT INTO po_online_report_text  (online_report_id, last_update_login,
  last_updated_by, last_update_date, created_by, creation_date, line_num,
  shipment_num, distribution_num, sequence, text_line) SELECT
  l_online_report_id,l_last_update_login,l_last_user_id,     sysdate,
  l_user_id,sysdate,PRL.line_num,0,0,l_sequence,
  'Line #' ||PRL.line_num||' '|| l_msg_text
  FROM po_requisition_lines_clm_v PRL
  WHERE PRL.requisition_header_id = l_document_id
  AND   PRL.source_type_code = 'INVENTORY'     AND   nvl(PRL.cancel_flag,'N')
  = 'N'     AND   nvl(PRL.closed_code, 'OPEN') <> 'FINALLY CLOSED'     AND
  1 < (select count(PRD.requisition_line_id)     FROM  PO_REQ_DISTRIBUTIONS
  PRD     WHERE PRD.requisition_line_id = PRL.requisition_line_id);

Line: 527


  INSERT INTO po_online_report_text  (online_report_id, last_update_login,
  last_updated_by, last_update_date, created_by, creation_date, line_num,
  shipment_num, distribution_num, sequence, text_line)
  SELECT
  l_online_report_id,l_last_update_login,l_user_id,     sysdate,
  l_user_id,sysdate,PRL.line_num,0,0,l_sequence,
  'Line #' ||PRL.line_num||' '||l_msg_text
  FROM po_requisition_lines_clm_v PRL, FINANCIALS_SYSTEM_PARAMETERS FSP,
  GL_SETS_OF_BOOKS SOB
  WHERE PRL.requisition_header_id = l_document_id     AND
  nvl(PRL.cancel_flag, 'N') = 'N'     AND nvl(PRL.closed_code, 'OPEN') <>
  'FINALLY CLOSED'     AND SOB.set_of_books_id = FSP.set_of_books_id     AND
  SOB.currency_code <> PRL.currency_code     AND (   PRL.rate is null
   OR PRL.rate_type is null          OR (    PRL.rate_type <> 'User'
      AND PRL.rate_date is null));

Line: 556


INSERT INTO po_online_report_text
(  online_report_id
,  last_update_login
,  last_updated_by
,  last_update_date
,  created_by
,  creation_date
,  line_num
,  shipment_num
,  distribution_num
,  sequence
,  text_line
)
SELECT
   l_online_report_id
,  l_last_update_login
,  l_user_id
,  sysdate
,  l_user_id
,  sysdate
,  PRL.line_num
,  0
,  PRD.distribution_num
,  l_sequence
,  'Line #'||PRL.line_num||' Distribution '||PRD.distribution_num||' '||l_msg_text
FROM
   FINANCIALS_SYSTEM_PARAMETERS FSP
,  PO_REQ_DISTRIBUTIONS PRD
,  po_requisition_lines_clm_v PRL
,  PO_REQUISITION_HEADERS_ALL PRH
WHERE PRD.requisition_line_id = PRL.requisition_line_id
AND   PRL.requisition_header_id = PRH.requisition_header_id
AND   PRL.requisition_header_id = l_document_id
AND   PRL.line_location_id IS NULL
AND
   (  NVL(PRH.transferred_to_oe_flag,'N') <> 'Y'
   OR NVL(PRL.source_type_code,'VENDOR') <> 'INVENTORY'
   )
AND   nvl(PRD.encumbered_flag,'N') = 'N'
AND   FSP.req_encumbrance_flag = 'Y'
AND   nvl(PRL.cancel_flag,'N') = 'N'
AND   nvl(PRL.closed_code,'OPEN') <> 'FINALLY CLOSED'
AND    Nvl(prl.modified_by_agent_flag,'N') = 'N' /*Bug 4882209*/
AND   not exists (
   select 'find if the GL date is not within Open period'
   from
      GL_PERIOD_STATUSES PS1
   ,  GL_PERIOD_STATUSES PS2
   ,  GL_SETS_OF_BOOKS GSOB
   WHERE PS1.application_id  = 101
   AND   PS1.set_of_books_id = FSP.set_of_books_id
   -- bug 5498063 
   AND ((  l_validate_gl_period IN ('Y','R') -- GL Date Project Changes R- Redefault
             and PS1.closing_status IN ('O', 'F'))
         OR
          (l_validate_gl_period = 'N'))
   -- AND   PS1.closing_status IN ('O','F')
   AND   trunc(nvl(PRD.GL_ENCUMBERED_DATE,PS1.start_date))
      BETWEEN trunc(PS1.start_date) AND trunc(PS1.end_date)
   AND   PS1.period_year <= GSOB.latest_encumbrance_year
   AND   PS1.period_name = PS2.period_name
   AND   PS2.application_id  = 201
   AND   PS2.closing_status  = 'O'
   AND   PS2.set_of_books_id = FSP.set_of_books_id
   AND   GSOB.set_of_books_id = FSP.set_of_books_id
   )
;

Line: 640


    select 'Y'
    into l_error_occur
    from po_online_report_text
    where online_report_id = l_online_report_id
    and rownum = 1;

Line: 684


select text_line from po_online_report_text where online_report_id = p_online_report_id
order by sequence;

Line: 734


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