APPS.PO_CALCULATEREQTOTAL_PVT SQL Statements

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

Line: 10

* Effects: Return updated distribution qty X new price
           from po_change_requests if any changes were made
*          Else return req_line_quantity X unit price
*          from po_req_distributions and po_requisition_lines
*          If line was cancelled return 0
* Returns:
*  Revised req total coming from a distribution
*  If something fails returns 0
*/
FUNCTION get_req_distribution_total(p_header_id IN NUMBER,
                                    p_line_id IN NUMBER,
                                    p_distribution_id IN NUMBER)
RETURN NUMBER
    IS
       l_matching_basis po_requisition_lines_all.matching_basis%type;

Line: 30

      SELECT prl.matching_basis, pcr.action_type,
             decode (prl.matching_basis, 'AMOUNT' , prd.req_line_amount,
             prd.req_line_quantity*prl.unit_price) +
             nvl(prd.nonrecoverable_tax,0),
             pcr.change_request_group_id
      INTO   l_matching_basis, l_action_type, l_dist_total,
             l_change_request_group_id
      FROM   po_requisition_lines_all prl,
          	 po_req_distributions_all prd,
             po_change_requests pcr
      WHERE  prl.requisition_line_id = p_line_id
      AND  	 prl.requisition_line_id = prd.requisition_line_id
      AND    prd.distribution_id = p_distribution_id
      AND  	 nvl(prl.cancel_flag,'N') = 'N'
      AND  	 nvl(prl.modified_by_agent_flag, 'N') = 'N'
      AND    pcr.document_line_id(+) = prl.requisition_line_id
      AND    pcr.document_type(+) = 'REQ'
      AND    pcr.action_type(+) <> 'DERIVED'
      AND    pcr.request_status(+) NOT IN ('ACCEPTED', 'REJECTED')
      AND    rownum =1;

Line: 80

  * Effects: Return updated distribution qty X new price
             from po_change_requests if any changes were made
  *          Else return req_line_quantity X unit price
  *          from po_req_distributions and po_requisition_lines
  *          If line was cancelled return 0
  * Returns:
  *  Revised req total coming from a distribution
  *  If something fails returns 0
  */
  FUNCTION get_req_dist_total(p_header_id IN NUMBER,
                                      p_line_id IN NUMBER,
                                      p_distribution_id IN NUMBER)
  RETURN NUMBER
      IS
         l_matching_basis po_requisition_lines_all.matching_basis%type;

Line: 100

        SELECT prl.matching_basis, pcr.action_type,
               decode (prl.matching_basis, 'AMOUNT' , prd.req_line_amount,
               prd.req_line_quantity*prl.unit_price) +
               nvl(prd.nonrecoverable_tax,0),
               pcr.change_request_group_id
        INTO   l_matching_basis, l_action_type, l_dist_total,
               l_change_request_group_id
        FROM   po_requisition_lines_all prl,
                   po_req_distributions_all prd,
               po_change_requests pcr
        WHERE  prl.requisition_line_id = p_line_id
        AND        prl.requisition_line_id = prd.requisition_line_id
        AND    prd.distribution_id = p_distribution_id
        AND        nvl(prl.cancel_flag,'N') = 'N'
        AND        nvl(prl.modified_by_agent_flag, 'N') = 'N'
        AND    pcr.document_line_id(+) = prl.requisition_line_id
        AND    pcr.document_type(+) = 'REQ'
        AND    pcr.action_type(+) <> 'DERIVED'
        AND    pcr.request_status(+) NOT IN ('ACCEPTED', 'REJECTED','SYSTEMSAVE') /* added SYSTEMSAVE condition */
        AND    rownum =1;

Line: 144

* Effects: Return updated requisition total
*          for a distribution
*          using revised values of distribution qty and line price
* Returns:
*  Revised req total coming from a distribution
*  If something fails returns 0
*/

FUNCTION get_new_distribution_total(p_header_id IN NUMBER,
                                    p_line_id IN NUMBER,
                           	        p_distribution_id IN NUMBER,
                                    p_matching_basis IN VARCHAR2,
                                    p_change_request_group_id IN NUMBER)
RETURN NUMBER
    IS
       l_nonrec_tax NUMBER := 0;

Line: 166

      SELECT prd.req_line_amount,
             nvl(pcr.new_amount, prd.req_line_amount),
             prd.nonrecoverable_tax
      INTO   l_old_dist_total, l_new_dist_total, l_nonrec_tax
      FROM   po_change_requests pcr,
             po_req_distributions_all prd
      WHERE  prd.distribution_id = p_distribution_id
      AND    pcr.document_line_id(+) = prd.requisition_line_id
      AND    pcr.document_distribution_id(+) = prd.distribution_id
      AND    pcr.request_status(+) NOT IN ('ACCEPTED','REJECTED');

Line: 180

      SELECT prd.req_line_quantity*prl.unit_price,
             nvl(pcr1.new_quantity, prd.req_line_quantity)*nvl(pcr.new_price, prl.unit_price),
             prd.nonrecoverable_tax
      INTO   l_old_dist_total, l_new_dist_total, l_nonrec_tax
      FROM   po_change_requests pcr,
             po_change_requests pcr1,
             po_requisition_lines_all prl,
             po_req_distributions_all prd
      WHERE  prd.distribution_id = p_distribution_id
      AND    pcr1.document_distribution_id(+) = prd.distribution_id
      AND    pcr1.document_line_id(+) = prd.requisition_line_id
      AND    pcr1.request_status(+) NOT IN ('ACCEPTED','REJECTED')
      AND    prd.requisition_line_id = prl.requisition_line_id
      AND    pcr.document_line_id(+) = prl.requisition_line_id
      AND    pcr.new_price(+) IS NOT NULL
      AND    pcr.old_price(+) IS NOT NULL
      AND    pcr.document_type(+) = 'REQ'
      AND    pcr.request_status(+) NOT IN ('ACCEPTED','REJECTED')
      AND    pcr.request_level(+) = 'LINE'
      AND    pcr.action_type(+) <> 'CANCELLATION'
      AND    pcr.change_request_group_id(+) = p_change_request_group_id;