APPS.POS_SCO_TOLERANCE_PVT SQL Statements

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

Line: 38

SELECT DISTINCT document_type
FROM   po_change_requests
WHERE  change_request_group_id = p_change_request_grp_id_csr;

Line: 43

SELECT type_lookup_code
FROM   po_headers_all poha
WHERE  poha.po_header_id = p_po_header_id_csr;

Line: 48

SELECT distinct(release_type)
FROM   po_releases_all pora
WHERE  pora.po_header_id = p_po_header_id_csr
       AND pora.po_release_id = p_po_release_id_csr;

Line: 54

SELECT org_id
FROM po_headers_all
WHERE po_header_id = p_po_header_id_csr;

Line: 493

         SELECT nvl(pcr.old_promised_date,pcr.old_need_by_date) old_promise_date,
                pcr.new_promised_date, pll.promised_date,pll.need_by_date
         FROM   po_change_requests pcr,
	        po_line_locations_all pll
         WHERE  pcr.document_header_id=p_po_header_id_csr
	        AND pcr.document_line_location_id = pll.line_location_id
                AND pcr.CHANGE_REQUEST_GROUP_ID=p_change_group_id_csr
                AND pcr.request_level = 'SHIPMENT'
                AND pcr.action_type = 'MODIFICATION'
                AND pcr.request_status = 'PENDING'
                AND pcr.initiator='SUPPLIER'
                AND ( (pcr.new_promised_date <> old_promised_date)  OR
	              (nvl(pcr.old_promised_date,nvl(pcr.old_need_by_date,pcr.new_promised_date - 1))<>pcr.new_promised_date)
                    );

Line: 632

	   SELECT old_price,new_price
	   FROM   po_change_requests
	   WHERE  document_header_id=p_po_header_id_csr
	          AND CHANGE_REQUEST_GROUP_ID=p_change_group_id_csr
	          AND request_level = 'LINE'
	          AND new_price IS NOT NULL
	          AND action_type = 'MODIFICATION'
	          AND request_status = 'PENDING'
	          AND initiator='SUPPLIER';

Line: 645

          SELECT  plla.price_override,nvl(pcr.new_price,pcr.old_price)
	  	   FROM   po_change_requests  pcr,
	  	          po_line_locations_all plla
	  	   WHERE  pcr.po_release_id= p_po_release_id_csr
	  	          AND pcr.CHANGE_REQUEST_GROUP_ID=p_change_group_id_csr
	  	          AND pcr.request_level = 'SHIPMENT'
	  	          AND pcr.new_price IS NOT NULL
	  	          AND pcr.action_type = 'MODIFICATION'
	  	          AND pcr.request_status = 'PENDING'
	  	          AND pcr.initiator='SUPPLIER'
			      AND pcr.document_line_location_id = plla.line_location_id;

Line: 659

	           SELECT pl.unit_price,pcr.new_price
	  	  	   FROM   po_change_requests  pcr,
	  	  	          po_lines_all pl
	  	  	   WHERE  pcr.document_header_id= p_po_header_id
	  	  	          AND pcr.CHANGE_REQUEST_GROUP_ID=p_change_group_id_csr
	  	  	          AND pcr.request_level = 'LINE'
	  	  	          AND pcr.new_price IS NOT NULL
	  	  	          AND pcr.action_type = 'MODIFICATION'
	  	  	          AND pcr.request_status = 'PENDING'
	  	  	          AND pcr.initiator='SUPPLIER'
			          AND pcr.document_line_id = pl.po_line_id;

Line: 832

	 SELECT pcr.old_quantity,
	        pcr.new_quantity
	 FROM   po_change_requests pcr
	 WHERE  pcr.change_request_group_id=p_change_group_id_csr
	       AND pcr.new_quantity IS NOT NULL
	       AND pcr.action_type='MODIFICATION'
	       AND pcr.request_status= 'PENDING'
	       AND pcr.request_level= 'SHIPMENT'
	       AND pcr.initiator= 'SUPPLIER';

Line: 955

	 SELECT  sum(decode(pl.matching_basis, 'AMOUNT', (pll.amount - nvl(pll.amount_cancelled,0)),(pl.unit_price * (pll.quantity - nvl(pll.quantity_cancelled,0)))))
         FROM 	 po_lines_all pl,
                 po_line_locations_all pll
         WHERE   pl.po_header_id = p_po_header_id_csr
	 	 AND pll.po_line_id = pl.po_line_id;

Line: 962

	 SELECT  sum(decode(pl.matching_basis, 'AMOUNT', (pll.amount - nvl(pll.amount_cancelled,0)),(pll.price_override * (pll.quantity - nvl(pll.quantity_cancelled,0)))))
         FROM 	 po_lines_all pl,
                 po_line_locations_all pll
         WHERE   pll.po_release_id = p_po_release_id_csr
                 AND pll.po_header_id = p_po_header_id_csr
	 	 AND pll.po_line_id = pl.po_line_id;

Line: 971

        SELECT SUM(DECODE(pl.matching_basis,'QUANTITY',
                                          (pll.quantity - NVL(pll.quantity_cancelled,0))* (pll.price_override),
                                             'AMOUNT',
                   DECODE(pll.payment_type,  'LUMPSUM',
                                              (pll.amount - NVL(pll.amount_cancelled,0)),
                                              'MILESTONE',
                                     	      (pll.amount - NVL(pll.amount_cancelled,0)),
                                              'RATE',
                                              (pll.quantity - NVL(pll.quantity_cancelled,0))*(pll.price_override))))
        FROM 	 po_lines_all pl,
                 po_line_locations_all pll
        WHERE    pl.po_header_id = p_po_header_id_csr
	         AND pll.po_line_id = pl.po_line_id;

Line: 988

        SELECT SUM(DECODE(pl.matching_basis,'QUANTITY',
	                                    (pl.quantity*pl.unit_price),
					    'AMOUNT',
					    (pl.amount)))
              FROM po_lines_all pl
	      WHERE pl.po_header_id = p_po_header_id_csr;

Line: 1162

	 SELECT  nvl(sum(decode(pl.matching_basis, 'AMOUNT',(nvl(pcr1.new_amount,pll.amount) - nvl(pll.amount_cancelled,0)),
	          (nvl(pcr.new_price,pl.unit_price) *
	          (nvl(pcr1.new_quantity,pll.quantity) - nvl(pll.quantity_cancelled,0))))),0)
	          FROM    po_change_requests pcr, --line amount/price change
	 	 	 po_change_requests pcr1, --shipment quantity change
	 	 	 po_lines_all pl,
	 	 	 po_line_locations_all pll
	          WHERE   pl.po_header_id = p_po_header_id_csr
	 	 	 AND pll.po_line_id = pl.po_line_id
	                 AND pcr1.document_header_id (+) = p_po_header_id_csr
	                 AND pcr1.document_line_location_id(+) = pll.line_location_id
	 	         AND pcr1.action_type(+) = 'MODIFICATION'
	 	         AND pcr1.request_status(+) = 'PENDING'
	 	         AND pcr1.request_level (+) = 'SHIPMENT'
	 	         AND pcr1.initiator(+) = 'SUPPLIER'
	                 AND pcr.document_line_id(+) = pl.po_line_id
	 	         AND pcr.action_type(+) = 'MODIFICATION'
	 	         AND pcr.request_status(+) = 'PENDING'
	 	         AND pcr.request_level (+) = 'LINE'
	 	         AND pcr.initiator(+) = 'SUPPLIER'
	 	UNION ALL
	   -- for splitted shipments
	  SELECT   nvl(sum(decode(pl.matching_basis, 'AMOUNT', nvl(pcr2.new_amount,pll.amount),
	           (nvl(pcr.new_price,pl.unit_price) * pcr2.new_quantity))),0)
	          FROM    po_change_requests pcr, --line amount/price change
	 		 po_change_requests pcr2, --for split shipments
	 	 	 po_lines_all pl,
	 	 	 po_line_locations_all pll
	          WHERE   pl.po_header_id = p_po_header_id_csr
	 	 	 AND pll.po_line_id = pl.po_line_id
	                 AND pcr2.document_header_id = p_po_header_id_csr
	                 AND pcr2.parent_line_location_id = pll.line_location_id
	 	         AND pcr2.action_type = 'MODIFICATION'
	 	         AND pcr2.request_status = 'PENDING'
	 	         AND pcr2.request_level  = 'SHIPMENT'
	 	         AND pcr2.initiator = 'SUPPLIER'
	                 AND pcr.document_line_id(+) = pl.po_line_id
	 	         AND pcr.action_type(+) = 'MODIFICATION'
	 	         AND pcr.request_status(+) = 'PENDING'
	 	         AND pcr.request_level (+) = 'LINE'
	                 AND pcr.initiator(+) = 'SUPPLIER';

Line: 1207

  SELECT  nvl(sum(decode(pl.matching_basis, 'AMOUNT', (nvl(pcr.new_amount, pll.amount) - nvl(pll.amount_cancelled,0)), (nvl(nvl(pcr.new_price,pcr.old_price),pll.price_override) * (nvl(pcr.new_quantity,pll.quantity) - nvl(pll.quantity_cancelled,0))))),0)
  	FROM    po_change_requests pcr,
  		po_lines_all pl,
  		po_line_locations_all pll
  	WHERE  pll.po_header_id = p_po_header_id_csr
               AND pll.po_release_id = p_po_release_id_csr
  	       AND pll.po_line_id = pl.po_line_id
  	       AND pcr.po_release_id(+) = p_po_release_id_csr
  	       AND pcr.document_header_id(+) = p_po_header_id_csr
  	       --AND pcr.document_line_id = pl.po_line_id
  	       AND pcr.action_type(+) = 'MODIFICATION'
  	       AND pcr.request_status(+) = 'PENDING'
  	       AND pcr.request_level (+) = 'SHIPMENT'
  	       AND pcr.initiator(+) = 'SUPPLIER'
  	       AND pcr.document_line_location_id(+) = pll.line_location_id
  	 UNION ALL
  SELECT  nvl(sum(decode(pl.matching_basis, 'AMOUNT', nvl(pcr2.new_amount, pll.amount), (nvl(pcr2.new_price,pll.price_override) * nvl(pcr2.new_quantity,pll.quantity)))),0)
  	FROM    po_change_requests pcr2, -- for splitted shipments
  		po_lines_all pl,
  		po_line_locations_all pll
  	WHERE  pll.po_header_id = p_po_header_id_csr
  	       AND pll.po_line_id = pl.po_line_id
  	       AND pcr2.po_release_id(+) = p_po_release_id_csr
  	       AND pcr2.document_header_id(+) = p_po_header_id_csr
  	       AND pcr2.document_line_id(+) = pl.po_line_id
  	       AND pcr2.action_type(+) = 'MODIFICATION'
  	       AND pcr2.request_status(+) = 'PENDING'
  	       AND pcr2.request_level (+) = 'SHIPMENT'
  	       AND pcr2.initiator(+) = 'SUPPLIER'
	       AND pcr2.parent_line_location_id = pll.line_location_id;

Line: 1241

 SELECT NVL(SUM(DECODE(pl.matching_basis,'QUANTITY',
                                         (pll.quantity - NVL(pll.quantity_cancelled,0))*(nvl(pcr.new_price,pll.price_override)),
                                        'AMOUNT',
               DECODE(pll.payment_type, 'LUMPSUM',
                                         (nvl(pcr.new_amount,pll.amount) -  NVL(pll.amount_cancelled,0)),
                                        'MILESTONE',
                                         (nvl(pcr.new_amount,pll.amount) -  NVL(pll.amount_cancelled,0)),
                                        'RATE',
                                         (nvl(pcr.new_quantity,pll.quantity) - NVL(pll.quantity_cancelled,0))*(nvl(pcr.new_price,pll.price_override))))),0)
 FROM
    po_change_requests pcr, --shipment changes
    po_lines_all pl,
    po_line_locations_all pll
 WHERE
    pl.po_header_id = p_po_header_id_csr
    AND pll.po_line_id = pl.po_line_id
    AND pcr.document_header_id (+) = p_po_header_id_csr
    AND pcr.document_line_location_id(+) = pll.line_location_id
    AND pcr.action_type(+) = 'MODIFICATION'
    AND pcr.request_status(+) = 'PENDING'
    AND pcr.request_level (+) = 'SHIPMENT'
    AND pcr.initiator(+) = 'SUPPLIER'
UNION ALL
 -- for split shipment changes
SELECT NVL(SUM(DECODE(pl.matching_basis,'QUANTITY',
                                         (pll.quantity - NVL(pll.quantity_cancelled,0))*(nvl(pcr.new_price,pll.price_override)),
                                        'AMOUNT',
               DECODE(nvl(pcr.new_progress_type,pll.payment_type), 'LUMPSUM',
                                                                  (nvl(pcr.new_amount,pll.amount) -  NVL(pll.amount_cancelled,0)),
                                                                  'MILESTONE',
                                                                  (nvl(pcr.new_amount,pll.amount) -  NVL(pll.amount_cancelled,0)),
                                                                  'RATE',
                                                                  (nvl(pcr.new_quantity,pll.quantity) - NVL(pll.quantity_cancelled,0))*(nvl(pcr.new_price,pll.price_override))))),0)
FROM
    po_change_requests pcr, --shipment changes
    po_lines_all pl,
    po_line_locations_all pll
WHERE
    pl.po_header_id = p_po_header_id_csr
    AND pll.po_line_id = pl.po_line_id
    AND pcr.document_header_id  = p_po_header_id_csr
    AND pcr.parent_line_location_id = pll.line_location_id
    AND pcr.action_type = 'MODIFICATION'
    AND pcr.request_status = 'PENDING'
    AND pcr.request_level  = 'SHIPMENT'
    AND pcr.initiator = 'SUPPLIER';

Line: 1291

  SELECT NVL(SUM(DECODE(pl.matching_basis,'QUANTITY',(pl.quantity*nvl(pcr.new_price,pl.unit_price)),
                                          'AMOUNT',nvl(pcr.new_amount,pl.amount))),0)
  FROM
      po_lines_all pl,
      po_change_requests pcr
  WHERE
      pl.po_header_id = p_po_header_id_csr
      AND pcr.document_header_id (+) = p_po_header_id_csr
      AND pcr.document_line_id(+) = pl.po_line_id
      AND pcr.action_type(+) = 'MODIFICATION'
      AND pcr.request_status(+) = 'PENDING'
      AND pcr.request_level (+) = 'LINE'
      AND pcr.initiator(+) = 'SUPPLIER';

Line: 1392

         select sum(decode(pol.matching_basis, 'AMOUNT', (pll.amount - nvl(pll.amount_cancelled,0)), (pol.unit_price * (pll.quantity - nvl(pll.quantity_cancelled,0))))),pll.po_line_id
         from po_lines_all pol,
              po_line_locations_all pll
         where pll.po_header_id = p_po_header_id_csr
         AND   pll.po_line_id = pol.po_line_id
         GROUP BY pll.po_line_id;

Line: 1400

       select sum(decode(pol.matching_basis, 'AMOUNT', (pll.amount - nvl(pll.amount_cancelled,0)), (pol.unit_price * (pll.quantity - nvl(pll.quantity_cancelled,0))))),pll.po_line_id
       from po_lines_all pol,
            po_line_locations_all pll
       where pll.po_release_id =p_po_release_id_csr
       AND   pll.po_header_id = p_po_header_id_csr
       AND   pll.po_line_id = pol.po_line_id
       GROUP BY pll.po_line_id;

Line: 1411

        SELECT SUM(DECODE(pl.matching_basis,'QUANTITY',pl.unit_price*pl.quantity,'AMOUNT',pl.amount)),pl.po_line_id
        FROM po_lines_all pl
	WHERE pl.po_header_id =  p_po_header_id_csr
	GROUP BY pl.po_line_id;

Line: 1418

       SELECT SUM(DECODE(pl.matching_basis,'QUANTITY',
                                          (pll.quantity - NVL(pll.quantity_cancelled,0))* (pll.price_override),
                                           'AMOUNT',
                  DECODE(pll.payment_type, 'LUMPSUM',
                                           (pll.amount - NVL(pll.amount_cancelled,0)),
                                           'MILESTONE',
                                     	   (pll.amount - NVL(pll.amount_cancelled,0)),
                                           'RATE',
                                           (pll.quantity - NVL(pll.quantity_cancelled,0))*(pll.price_override)))),pll.po_line_id
        FROM 	 po_lines_all pl,
                 po_line_locations_all pll
        WHERE    pl.po_header_id = p_po_header_id_csr
	         AND pll.po_line_id = pl.po_line_id
                 GROUP BY pll.po_line_id;

Line: 1643

  SELECT  nvl(sum(decode(pl.matching_basis, 'AMOUNT', (nvl(pcr1.new_amount,pll.amount) - nvl(pll.amount_cancelled,0)),
           (nvl(pcr.new_price,pl.unit_price) *
           (nvl(pcr1.new_quantity,pll.quantity) - nvl(pll.quantity_cancelled,0))))),0)
           FROM    po_change_requests pcr, --line amount/price change
  	 	 po_change_requests pcr1, --shipment quantity change
  	 	 po_lines_all pl,
  	 	 po_line_locations_all pll
           WHERE   pl.po_header_id = p_po_header_id_csr
                   AND pl.po_line_id = p_temp_po_line_id_csr
  	 	   AND pll.po_line_id = pl.po_line_id
                   AND pcr1.document_header_id (+) = p_po_header_id_csr
                   AND pcr1.document_line_location_id(+) = pll.line_location_id
  	           AND pcr1.action_type(+) = 'MODIFICATION'
  	           AND pcr1.request_status(+) = req_status
  	           AND pcr1.request_level (+) = 'SHIPMENT'
  	           AND pcr1.initiator(+) = req_initiator
                   AND pcr.document_line_id(+) = pl.po_line_id
  	           AND pcr.action_type(+) = 'MODIFICATION'
  	           AND pcr.request_status(+) = req_status
  	           AND pcr.request_level (+) = 'LINE'
  	           AND pcr.initiator(+) = req_initiator
  	UNION ALL
    -- for splitted shipments
   SELECT   nvl(sum(decode(pl.matching_basis, 'AMOUNT', nvl(pcr2.new_amount,pll.amount),
            (nvl(pcr.new_price,pl.unit_price) * pcr2.new_quantity))),0)
           FROM    po_change_requests pcr, --line amount/price change
  		   po_change_requests pcr2, --for split shipments
  	 	   po_lines_all pl,
  	 	   po_line_locations_all pll
           WHERE   pl.po_header_id = p_po_header_id_csr
                   AND pl.po_line_id = p_temp_po_line_id_csr
  	 	   AND pll.po_line_id = pl.po_line_id
                   AND pcr2.document_header_id = p_po_header_id_csr
                   AND pcr2.parent_line_location_id = pll.line_location_id
  	           AND pcr2.action_type = 'MODIFICATION'
  	           AND pcr2.request_status in req_status
  	           AND pcr2.request_level  = 'SHIPMENT'
  	           AND pcr2.initiator = req_initiator
                   AND pcr.document_line_id(+) = pl.po_line_id
  	           AND pcr.action_type(+) = 'MODIFICATION'
  	           AND pcr.request_status in req_status
  	           AND pcr.request_level (+) = 'LINE'
	           AND pcr.initiator(+) = req_initiator;

Line: 1691

   SELECT  nvl(sum(decode(pl.matching_basis, 'AMOUNT', (nvl(pcr.new_amount, pll.amount) - nvl(pll.amount_cancelled,0)),
			  (nvl(nvl(pcr.new_price,pcr.old_price),pll.price_override) *
			   (nvl(pcr.new_quantity,pll.quantity) - nvl(pll.quantity_cancelled,0))))),0)
     	FROM    po_change_requests pcr,
     		po_lines_all pl,
     		po_line_locations_all pll
     	WHERE  pll.po_header_id = p_po_header_id_csr
     	       AND pll.po_line_id = p_temp_po_line_id_csr
     	       AND pll.po_line_id = pl.po_line_id
     	       AND pll.po_release_id = p_po_release_id_csr
	       AND pcr.po_release_id (+) = p_po_release_id_csr
     	       AND pcr.document_header_id(+) = p_po_header_id_csr
     	       --AND pcr.document_line_id = pl.po_line_id
     	       AND pcr.action_type(+) = 'MODIFICATION'
     	       AND pcr.request_status (+) = req_status
     	       AND pcr.request_level (+) = 'SHIPMENT'
     	       AND pcr.initiator(+) = req_initiator
     	       AND pcr.document_line_location_id(+) = pll.line_location_id
     	 UNION ALL
     SELECT  nvl(sum(decode(pl.matching_basis, 'AMOUNT', nvl(pcr2.new_amount, pll.amount), (nvl(nvl(pcr2.new_price,pcr2.old_price),pll.price_override) * nvl(pcr2.new_quantity,pll.quantity)))),0)
     	FROM    po_change_requests pcr2, -- for splitted shipments
     		po_lines_all pl,
     		po_line_locations_all pll
     	WHERE  pll.po_header_id = p_po_header_id_csr
     	       AND pll.po_line_id = p_temp_po_line_id_csr
     	       AND pll.po_line_id = pl.po_line_id
     	       AND pcr2.po_release_id = p_po_release_id_csr
     	       AND pcr2.document_header_id(+) = p_po_header_id_csr
     	       AND pcr2.document_line_id(+) = pl.po_line_id
     	       AND pcr2.action_type(+) = 'MODIFICATION'
     	       AND pcr2.request_status in req_status
     	       AND pcr2.request_level (+) = 'SHIPMENT'
     	       AND pcr2.initiator(+) = req_initiator
	       AND pcr2.parent_line_location_id = pll.line_location_id;

Line: 1729

   select NVL(SUM(DECODE(pl.matching_basis,'QUANTITY',
                                         (pll.quantity - NVL(pll.quantity_cancelled,0))*(nvl(pcr.new_price,pll.price_override)),
                                        'AMOUNT',
                  DECODE(pll.payment_type, 'LUMPSUM',
                                         (nvl(pcr.new_amount,pll.amount) -  NVL(pll.amount_cancelled,0)),
                                        'MILESTONE',
                                         (nvl(pcr.new_amount,pll.amount) -  NVL(pll.amount_cancelled,0)),
                                        'RATE',
                                         (nvl(pcr.new_quantity,pll.quantity) - NVL(pll.quantity_cancelled,0))*(nvl(pcr.new_price,pll.price_override))))),0)
 FROM
    po_change_requests pcr, --shipment quantity changes
    po_lines_all pl,
    po_line_locations_all pll
 WHERE
    pl.po_header_id = p_po_header_id_csr
    AND pl.po_line_id = p_temp_po_line_id_csr
    AND pll.po_line_id = pl.po_line_id
    AND pcr.document_header_id (+) = p_po_header_id_csr
    AND pcr.document_line_location_id(+) = pll.line_location_id
    AND pcr.action_type(+) = 'MODIFICATION'
    AND pcr.request_status(+)=req_status
    AND pcr.request_level (+) = 'SHIPMENT'
    AND pcr.initiator(+) = req_initiator
 UNION ALL
 -- for split shipment changes
 select NVL(SUM(DECODE(pl.matching_basis,'QUANTITY',
                                         (pll.quantity - NVL(pll.quantity_cancelled,0))*(nvl(pcr.new_price,pll.price_override)),
                                        'AMOUNT',
                DECODE(nvl(pcr.new_progress_type,pll.payment_type), 'LUMPSUM',
                                                                  (nvl(pcr.new_amount,pll.amount) -  NVL(pll.amount_cancelled,0)),
                                                                  'MILESTONE',
                                                                  (nvl(pcr.new_amount,pll.amount) -  NVL(pll.amount_cancelled,0)),
                                                                  'RATE',
                                                                  (nvl(pcr.new_quantity,pll.quantity) - NVL(pll.quantity_cancelled,0))*(nvl(pcr.new_price,pll.price_override))))),0)
 FROM
    po_change_requests pcr, --shipment quantity changes
    po_lines_all pl,
    po_line_locations_all pll
 WHERE
    pl.po_header_id = p_po_header_id_csr
    AND pl.po_line_id = p_temp_po_line_id_csr
    AND pll.po_line_id = pl.po_line_id
    AND pcr.document_header_id  = p_po_header_id_csr
    AND pcr.parent_line_location_id = pll.line_location_id
    AND pcr.action_type = 'MODIFICATION'
    AND pcr.request_status(+)=req_status
    AND pcr.request_level  = 'SHIPMENT'
    AND pcr.initiator = req_initiator;

Line: 1781

  SELECT NVL(SUM(DECODE(pl.matching_basis,'QUANTITY',(pl.quantity*nvl(pcr.new_price,pl.unit_price)),
                                          'AMOUNT',nvl(pcr.new_amount,pl.amount))),0)
  FROM
      po_lines_all pl,
      po_change_requests pcr
  WHERE
      pl.po_header_id = p_po_header_id_csr
      AND pl.po_line_id = p_temp_po_line_id_csr
      AND pcr.document_header_id (+) = p_po_header_id_csr
      AND pcr.document_line_id(+) = pl.po_line_id
      AND pcr.action_type(+) = 'MODIFICATION'
      AND pcr.request_status(+)=req_status
      AND pcr.request_level (+) = 'LINE'
      AND pcr.initiator(+) = req_initiator;

Line: 1808

        select distinct request_status ,initiator into req_status,req_initiator from po_change_requests
        where document_header_id=p_po_header_id AND document_type = 'PO'
              and change_active_flag='Y'
              and rownum=1;

Line: 1813

        select distinct request_status ,initiator into req_status,req_initiator from po_change_requests
        where document_header_id=p_po_header_id AND document_type = 'RELEASE'
              and po_release_id = p_po_release_id
              and change_active_flag='Y'
              and rownum=1;

Line: 1896

        select sum(decode(pol.matching_basis,'AMOUNT',(nvl(pll.amount,0) - nvl(pll.amount_cancelled,0)),(pol.unit_price *(pll.quantity-nvl(pll.quantity_cancelled,0))))),pll.line_location_id
	from    po_line_locations_all pll,
	        po_lines_all pol
	where pll.po_header_id = p_po_header_id_csr
	      AND pll.po_line_id = pol.po_line_id
	      GROUP BY pll.line_location_id;

Line: 1905

        SELECT  sum(decode(pl.matching_basis, 'AMOUNT', (pll.amount - nvl(pll.amount_cancelled,0)),(pll.price_override * (pll.quantity - nvl(pll.quantity_cancelled,0))))),
                pll.line_location_id
	FROM 	po_lines_all pl,
	        po_line_locations_all pll
	WHERE   pll.po_release_id = p_po_release_id_csr
                AND pll.po_header_id = p_po_header_id_csr
		AND pll.po_line_id = pl.po_line_id
		GROUP BY pll.line_location_id;

Line: 1916

        SELECT SUM(DECODE(pl.matching_basis,'QUANTITY',
                                          (pll.quantity - NVL(pll.quantity_cancelled,0))* (pll.price_override),
                                            'AMOUNT',
                   DECODE(pll.payment_type, 'LUMPSUM',
                                            (pll.amount - NVL(pll.amount_cancelled,0)),
                                            'MILESTONE',
                                     	    (pll.amount - NVL(pll.amount_cancelled,0)),
                                            'RATE',
                                            (pll.quantity - NVL(pll.quantity_cancelled,0))*(pll.price_override)))),pll.line_location_id
         FROM 	 po_lines_all pl,
                 po_line_locations_all pll
         WHERE   pl.po_header_id = p_po_header_id_csr
	         AND pll.po_line_id = pl.po_line_id
                 GROUP BY pll.line_location_id;

Line: 2158

    SELECT  nvl(sum(decode(pl.matching_basis, 'AMOUNT', (nvl(pcr1.new_amount,pll.amount) - nvl(pll.amount_cancelled,0)),
            (nvl(pcr.new_price,pl.unit_price) *
            (nvl(pcr1.new_quantity,pll.quantity) - nvl(pll.quantity_cancelled,0))))),0)
            FROM    po_change_requests pcr, --line amount/price change
   	 	 po_change_requests pcr1, --shipment quantity change
   	 	 po_lines_all pl,
   	 	 po_line_locations_all pll
            WHERE   pl.po_header_id = p_po_header_id_csr
                 AND pll.line_location_id = p_line_location_id_csr
   	 	 AND pll.po_line_id = pl.po_line_id
                    AND pcr1.document_header_id (+) = p_po_header_id_csr
                    AND pcr1.document_line_location_id(+) = pll.line_location_id
   	         AND pcr1.action_type(+) = 'MODIFICATION'
   	         AND pcr1.request_status(+) = req_status
   	         AND pcr1.request_level (+) = 'SHIPMENT'
   	         AND pcr1.initiator(+) = req_initiator
                    AND pcr.document_line_id(+) = pl.po_line_id
   	         AND pcr.action_type(+) = 'MODIFICATION'
   	         AND pcr.request_status(+) = req_status
   	         AND pcr.request_level (+) = 'LINE'
   	         AND pcr.initiator(+) =  req_initiator;

Line: 2182

    SELECT   nvl(sum(decode(pl.matching_basis, 'AMOUNT', nvl(pcr2.new_amount,pll.amount),
             (nvl(pcr.new_price,pl.unit_price) * pcr2.new_quantity))),0)
            FROM    po_change_requests pcr, --line amount/price change
   		 po_change_requests pcr2, --for split shipments
   	 	 po_lines_all pl,
   	 	 po_line_locations_all pll
            WHERE   pl.po_header_id = p_po_header_id_csr
                 AND pll.line_location_id = p_line_location_id_csr
   	 	 AND pll.po_line_id = pl.po_line_id
                    AND pcr2.document_header_id = p_po_header_id_csr
                    AND pcr2.parent_line_location_id = pll.line_location_id
   	         AND pcr2.action_type = 'MODIFICATION'
   	         AND pcr2.request_status = req_status
   	         AND pcr2.request_level  = 'SHIPMENT'
   	         AND pcr2.initiator =  req_initiator
                 AND pcr2.document_shipment_number = p_po_shipment_num
                    AND pcr.document_line_id(+) = pl.po_line_id
   	         AND pcr.action_type(+) = 'MODIFICATION'
   	         AND pcr.request_status(+) =  req_status
   	         AND pcr.request_level (+) =  'LINE'
	         AND pcr.initiator(+) =  req_initiator;

Line: 2207

   SELECT  nvl(sum(decode(pl.matching_basis, 'AMOUNT', (nvl(pcr.new_amount, pll.amount) - nvl(pll.amount_cancelled,0)),
			  (nvl(nvl(pcr.new_price,pcr.old_price),pll.price_override) *
			   (nvl(pcr.new_quantity,pll.quantity) - nvl(pll.quantity_cancelled,0))))),0)
    	FROM    po_change_requests pcr,
    		po_lines_all pl,
    		po_line_locations_all pll
    	WHERE  pll.po_header_id = p_po_header_id_csr
    	       AND pll.line_location_id = p_line_location_id_csr
	       AND pll.po_release_id = p_po_release_id_csr
    	       AND pll.po_line_id = pl.po_line_id
    	      -- AND pcr.po_release_id = p_po_release_id_csr
    	       AND pcr.document_header_id(+) = p_po_header_id_csr
    	       AND pcr.document_line_id = pl.po_line_id
    	       AND pcr.action_type(+) = 'MODIFICATION'
    	       AND pcr.request_status(+) = req_status
    	       AND pcr.request_level (+) = 'SHIPMENT'
    	       AND pcr.initiator(+) = req_initiator
    	       AND pcr.document_line_location_id(+) = pll.line_location_id;

Line: 2227

    SELECT  nvl(sum(decode(pl.matching_basis, 'AMOUNT', nvl(pcr2.new_amount, pll.amount), (nvl(nvl(pcr2.new_price,pcr2.old_price),pll.price_override) * nvl(pcr2.new_quantity,pll.quantity)))),0)
    	FROM    po_change_requests pcr2, -- for splitted shipments
    		po_lines_all pl,
    		po_line_locations_all pll
    	WHERE  pll.po_header_id = p_po_header_id_csr
    	       AND pll.line_location_id = p_line_location_id_csr
    	       AND pll.po_line_id = pl.po_line_id
    	       AND pcr2.po_release_id = p_po_release_id_csr
    	       AND pcr2.document_header_id(+) = p_po_header_id_csr
    	       AND pcr2.document_line_id(+) = pl.po_line_id
    	       AND pcr2.action_type(+) = 'MODIFICATION'
    	       AND pcr2.request_status(+)=  req_status
    	       AND pcr2.request_level (+) = 'SHIPMENT'
    	       AND pcr2.initiator(+) = req_initiator
    	       AND pcr2.parent_line_location_id = pll.line_location_id
               AND pcr2.document_shipment_number = p_po_shipment_num;

Line: 2246

    select NVL(SUM(DECODE(pl.matching_basis,'QUANTITY',
                                         (pll.quantity - NVL(pll.quantity_cancelled,0))*(nvl(pcr.new_price,pll.price_override)),
                                        'AMOUNT',
                   DECODE(pll.payment_type, 'LUMPSUM',
                                         (nvl(pcr.new_amount,pll.amount) -  NVL(pll.amount_cancelled,0)),
                                        'MILESTONE',
                                         (nvl(pcr.new_amount,pll.amount) -  NVL(pll.amount_cancelled,0)),
                                        'RATE',
                                         (nvl(pcr.new_quantity,pll.quantity) - NVL(pll.quantity_cancelled,0))*(nvl(pcr.new_price,pll.price_override))))),0)
    FROM
        po_change_requests pcr, --shipment quantity changes
        po_lines_all pl,
        po_line_locations_all pll
    WHERE
        pl.po_header_id = p_po_header_id_csr
        AND pll.line_location_id = p_line_location_id_csr
        AND pll.po_line_id = pl.po_line_id
        AND pcr.document_header_id (+) = p_po_header_id_csr
        AND pcr.document_line_location_id(+) = pll.line_location_id
        AND pcr.action_type(+) = 'MODIFICATION'
        AND pcr.request_status(+) = 'PENDING'
        AND pcr.request_level (+) = 'SHIPMENT'
        AND pcr.initiator(+) = 'SUPPLIER';

Line: 2272

    select NVL(SUM(DECODE(pl.matching_basis,'QUANTITY',
                                            (pll.quantity - NVL(pll.quantity_cancelled,0))*(nvl(pcr.new_price,pll.price_override)),
                                            'AMOUNT',
                   DECODE(nvl(pcr.new_progress_type,pll.payment_type), 'LUMPSUM',
                                                                       (nvl(pcr.new_amount,pll.amount) -  NVL(pll.amount_cancelled,0)),
                                                                       'MILESTONE',
                                                                       (nvl(pcr.new_amount,pll.amount) -  NVL(pll.amount_cancelled,0)),
                                                                       'RATE',
                                                                       (nvl(pcr.new_quantity,pll.quantity) - NVL(pll.quantity_cancelled,0))*(nvl(pcr.new_price,pll.price_override))))),0)
    FROM
       po_change_requests pcr, --shipment quantity changes
       po_lines_all pl,
       po_line_locations_all pll
    WHERE
       pl.po_header_id = p_po_header_id_csr
       AND pll.line_location_id = p_line_location_id_csr
       AND pll.po_line_id = pl.po_line_id
       AND pcr.document_header_id  = p_po_header_id_csr
       AND pcr.parent_line_location_id = pll.line_location_id
       AND pcr.action_type = 'MODIFICATION'
       AND pcr.request_status = 'PENDING'
       AND pcr.request_level  = 'SHIPMENT'
       AND pcr.initiator = 'SUPPLIER'
       AND pcr.document_shipment_number = p_po_shipment_num;

Line: 2308

        select distinct request_status ,initiator into req_status,req_initiator from po_change_requests
        where document_header_id=p_po_header_id AND document_type = 'PO'
              and change_active_flag='Y'
              and rownum=1;

Line: 2313

        select distinct request_status ,initiator into req_status,req_initiator from po_change_requests
        where document_header_id=p_po_header_id AND document_type = 'RELEASE'
              and po_release_id = p_po_release_id
              and change_active_flag='Y'
              and rownum=1;

Line: 2621

       select count(1) from po_change_requests pcr
       where pcr.document_header_id=p_po_header_id_csr_pd
	     AND pcr.change_request_group_id=p_change_group_id_csr_pd
	     AND pcr.new_promised_date IS NOT NULL
	     AND pcr.action_type='MODIFICATION'
	     AND pcr.request_status= 'PENDING'
	     AND pcr.request_level= 'SHIPMENT'
	     AND pcr.initiator= 'SUPPLIER';

Line: 2633

       select count(1) from po_change_requests pcr
       where  pcr.document_header_id=p_po_header_id_csr
	      AND pcr.change_request_group_id=p_change_group_id_csr
	      AND pcr.new_quantity IS NOT NULL
	      AND pcr.action_type='MODIFICATION'
	      AND pcr.request_status= 'PENDING'
	      AND pcr.request_level= 'SHIPMENT'
	      AND pcr.initiator= 'SUPPLIER';

Line: 2644

select count(1) from po_change_requests pcr
where  pcr.document_header_id=p_po_header_id_csr
       AND pcr.change_request_group_id=p_change_group_id_csr
       AND nvl(pcr.new_price,pcr.new_amount) IS NOT NULL
       AND pcr.action_type='MODIFICATION'
       AND pcr.request_status= 'PENDING'
       AND pcr.request_level IN ('LINE','SHIPMENT')
       AND pcr.initiator= 'SUPPLIER';

Line: 2654

select count(1) from po_change_requests pcr
where  pcr.document_header_id = p_po_header_id_csr
       AND pcr.change_request_group_id=p_change_group_id_csr
       AND nvl(pcr.new_price,pcr.new_amount) IS NOT NULL
       AND pcr.action_type='MODIFICATION'
       AND pcr.request_status= 'PENDING'
       AND pcr.request_level= 'SHIPMENT'
       AND pcr.initiator= 'SUPPLIER';

Line: 2820

        select distinct porh.requisition_header_id
        from   po_requisition_headers_all porh,
               po_requisition_lines_all porl,
               po_headers_all poh,
               po_line_locations_all poll
        where  porh.requisition_header_id = porl.requisition_header_id AND
               porl.line_location_id = poll.line_location_id  AND
               poh.po_header_id = poll.po_header_id AND
               poh.po_header_id = p_po_header_id_csr;

Line: 2831

        select distinct porh.requisition_header_id
        from   po_requisition_headers_all porh,
               po_requisition_lines_all porl,
               po_headers_all poh,
               po_line_locations_all poll
        where  porh.requisition_header_id = porl.requisition_header_id AND
               porl.line_location_id = poll.line_location_id  AND
               poh.po_header_id = poll.po_header_id AND
               poh.po_header_id = p_po_header_id_csr AND
               poll.po_release_id = p_po_release_id_csr;

Line: 2901

          update po_requisition_headers_all
          set change_pending_flag = 'Y'
          where requisition_header_id = l_req_hdr_id;

Line: 2916

          update po_requisition_headers_all
          set change_pending_flag = 'Y'
          where requisition_header_id = l_req_hdr_id;

Line: 3050

  SELECT count(distinct porl.requisition_header_id)
  FROM   po_requisition_lines_all porl,  -- 
         po_line_locations_all poll
  WHERE  poll.line_location_id = porl.line_location_id AND
         NVL(poll.cancel_flag, 'N') = 'N' AND
         NVL(poll.CLOSEd_code, 'OPEN') <> 'FINALLY CLOSED' AND
         poll.shipment_type IN('STANDARD', 'BLANKET')
         AND poll.po_header_id=p_po_header_id_csr;

Line: 3062

  SELECT count(distinct porl.requisition_header_id)
  FROM   po_requisition_lines_all porl,  -- 
         po_line_locations_all poll
  WHERE  poll.line_location_id = porl.line_location_id AND
         NVL(poll.cancel_flag, 'N') = 'N' AND
         NVL(poll.CLOSEd_code, 'OPEN') <> 'FINALLY CLOSED' AND
         poll.shipment_type IN('STANDARD', 'BLANKET')
         AND poll.po_header_id=p_po_header_id_csr
         AND poll.po_release_id = p_po_release_id_csr;

Line: 3076

 SELECT count(1)
 FROM   po_line_locations_all plla
 WHERE  plla.po_header_id = p_po_header_id_csr
        AND plla.line_location_id NOT IN (SELECT plla2.line_location_id
                                          FROM   po_requisition_lines_all  porla,
                                                 po_line_locations_all plla2
                                          WHERE  plla2.po_header_id = p_po_header_id_csr
                                                 AND porla.line_location_id = plla2.line_location_id);

Line: 3088

  SELECT count(1)
  FROM   po_line_locations_all plla
  WHERE  plla.po_header_id = p_po_header_id_csr
         AND plla.po_release_id = p_po_release_id_csr
         AND plla.line_location_id NOT IN (   SELECT plla2.line_location_id
                                              FROM   po_requisition_lines_all  porla,
                                                     po_line_locations_all plla2
                                              WHERE  plla2.po_header_id = p_po_header_id_csr
                                              AND    plla2.po_release_id = p_po_release_id_csr
                                              AND    porla.line_location_id = plla2.line_location_id);

Line: 3103

        select count(1)
        from   po_change_requests pcr,
	       po_requisition_lines_all porl1,
	       po_line_locations_all pll
        where  pcr.document_header_id = p_po_header_id_csr
               AND pcr.REQUEST_LEVEL = 'LINE'
               AND pcr.new_price is not NULL
               AND pcr.request_status = 'PENDING'
	       AND porl1.line_location_id = pll.line_location_id
	       AND pcr.document_line_id = pll.po_line_id
	       AND porl1.item_id is not null;

Line: 3118

        select count(1)
        from   po_change_requests pcr,
	       po_requisition_lines_all porl1,
	       po_line_locations_all pll
        where  pcr.document_header_id = p_po_header_id_csr
	       AND pcr.po_release_id = p_po_release_id_csr
               AND pcr.REQUEST_LEVEL = 'SHIPMENT'
               AND nvl(pcr.new_price,pcr.new_amount) is not NULL   -- NEW_AMOUNT in case of FPS
               AND pcr.request_status = 'PENDING'
	       AND pcr.document_line_location_id = porl1.line_location_id
	       AND porl1.line_location_id = pll.line_location_id
	       AND porl1.item_id is NOT NULL;

Line: 3133

    select count(1)
        from   po_change_requests pcr,
	       po_requisition_lines_all porl1,
	       po_line_locations_all pll
	where  pcr.document_header_id = p_po_header_id_csr
	       AND pcr.REQUEST_LEVEL = 'SHIPMENT'
               AND pcr.new_amount is not NULL
               AND pcr.request_status = 'PENDING'
  	       AND pcr.document_line_location_id = porl1.line_location_id
	       AND porl1.line_location_id = pll.line_location_id
	       AND porl1.item_id IS NOT NULL;

Line: 3149

        select count(1)
        from   po_requisition_headers_all porh,
               po_requisition_lines_all porl,
               po_headers_all poh,
               po_line_locations_all poll
        where  porh.requisition_header_id = porl.requisition_header_id AND
               porl.line_location_id = poll.line_location_id  AND
               poh.po_header_id = poll.po_header_id AND
               poh.po_header_id = p_po_header_id_csr AND
               porh.change_pENDing_flag = 'Y' ;

Line: 3164

        select count(1)
        from   po_requisition_headers_all porh,
               po_requisition_lines_all porl,
               po_headers_all poh,
               po_line_locations_all poll
        where  porh.requisition_header_id = porl.requisition_header_id AND
               porl.line_location_id = poll.line_location_id  AND
               poh.po_header_id = poll.po_header_id AND
               poh.po_header_id = p_po_header_id_csr AND
               poll.po_release_id = p_po_release_id_csr AND
               porh.change_pENDing_flag = 'Y' ;

Line: 3179

  select UNIQUE(pda.deliver_to_person_id)
  from
  	po_change_requests pcr,
  	po_distributions_all pda
  where pcr.change_request_group_id = c_grp_id_csr
  AND pcr.request_level = 'LINE'
  AND pcr.document_line_id = pda.po_line_id
  and pda.deliver_to_person_id is not null
  union
  select UNIQUE(pda.deliver_to_person_id)
  from
  	po_change_requests pcr,
  	po_distributions_all pda
  where pcr.change_request_group_id = c_grp_id_csr
  AND pcr.request_level = 'SHIPMENT'
  AND pcr.document_line_location_id = pda.line_location_id
  and pda.deliver_to_person_id is not null;

Line: 3412

         select count(1)
         from   po_change_requests
         where  parent_line_location_id is not null
         AND    action_type = 'MODIFICATION'
         AND    document_header_id = p_po_header_id_csr
         AND    request_level = 'SHIPMENT'
         AND    request_status = 'PENDING';

Line: 3423

           select count(1)
           from   po_change_requests
           where  parent_line_location_id is not null
           AND    action_type = 'MODIFICATION'
           AND    document_header_id = p_po_header_id_csr
           AND    po_release_id      = p_po_release_id_csr
           AND    request_level = 'SHIPMENT'
           AND    request_status = 'PENDING';

Line: 3435

         SELECT count(1)
         from   po_change_requests
         where  action_type = 'CANCELLATION'
         AND    request_status = 'PENDING'
         AND    document_header_id = p_po_header_id_csr;

Line: 3444

           SELECT count(1)
           from   po_change_requests
           where  action_type = 'CANCELLATION'
           AND    request_status = 'PENDING'
           AND    document_header_id = p_po_header_id_csr
           AND    po_release_id    =   p_po_release_id_csr;

Line: 3455

        select count(1)
        from   po_change_requests
        where  action_type = 'MODIFICATION'
        AND    ADDITIONAL_CHANGES is not NULL
        AND    request_status = 'PENDING'
        AND    document_header_id = p_po_header_id_csr;

Line: 3464

        select count(1)
        from   po_change_requests
        where  action_type = 'MODIFICATION'
        AND    ADDITIONAL_CHANGES is not NULL
        AND    request_level = 'HEADER'
        AND    request_status = 'PENDING'
        AND    document_header_id = p_po_header_id_csr
        AND    po_release_id    =   p_po_release_id_csr;

Line: 3479

  SELECT acceptance_required_flag,revision_num
  FROM   po_headers_all
  WHERE  po_header_id = p_po_header_id_csr;

Line: 3485

  SELECT count(1)
  FROM   po_acceptances pa,
         po_lines_archive_all pla,
         po_line_locations_archive_all plla
  WHERE  plla.po_header_id = p_po_header_id_csr
         AND pa.accepted_flag = 'N'
         AND plla.po_line_id = pla.po_line_id
         AND pa.po_line_location_id = plla.line_location_id
         AND pa.revision_num = c_revision_num
	 AND plla.revision_num = (SELECT max(plla2.revision_num)
	                          FROM   po_line_locations_archive_all plla2
	                          WHERE  plla2.line_location_id = plla.line_location_id
                                  AND    plla.revision_num <= c_revision_num);

Line: 3503

     SELECT acceptance_required_flag,revision_num
     FROM   po_releases_all
     WHERE  po_header_id = p_po_header_id_csr
     AND    po_release_id = p_po_release_id_csr;

Line: 3510

  SELECT count(1)
  FROM   po_acceptances pa,
         po_lines_archive_all pla,
         po_line_locations_archive_all plla
  WHERE  plla.po_header_id = p_po_header_id_csr
         AND pa.po_release_id = p_po_release_id_csr
         AND pa.accepted_flag = 'N'
         AND plla.po_release_id = pa.po_release_id
         AND plla.po_line_id = pla.po_line_id
         AND pa.po_line_location_id = plla.line_location_id
         AND pa.revision_num = c_revision_num
	 AND plla.revision_num = (SELECT max(plla2.revision_num)
	                          FROM   po_line_locations_archive_all plla2
	                          WHERE  plla2.line_location_id = plla.line_location_id
                                  AND    plla.revision_num <= c_revision_num);

Line: 3529

  SELECT acceptance_required_flag
  FROM   po_headers_all
  WHERE  po_header_id = p_po_header_id_csr;

Line: 3534

  SELECT count(1)
  FROM   po_acceptances
  WHERE  po_header_id = p_po_header_id_csr
  and revision_num=c_revision_num
  and signature_flag='Y';

Line: 3541

SELECT count(1)
  FROM   po_acceptances
  WHERE  po_release_id =p_po_release_id_csr
  and revision_num=c_revision_num
  and signature_flag='Y';

Line: 3549

   SELECT count(1)
   FROM   po_change_requests
   WHERE  action_type = 'MODIFICATION'
   AND    request_status = 'PENDING'
   AND    request_level = 'LINE'
   AND    new_supplier_part_number is not null
   AND    document_header_id = p_po_header_id_csr;

Line: 3828

       select  OLD_QUANTITY,
               NEW_QUANTITY,
               OLD_PROMISED_DATE,
               NEW_PROMISED_DATE,
               OLD_PRICE,
               NEW_PRICE,
	       OLD_AMOUNT,
	       NEW_AMOUNT
       from    po_change_requests
       where   initiator='SUPPLIER'
	       AND action_type='MODIFICATION'
	       AND request_level='SHIPMENT'
               AND change_request_group_id = l_change_group_id_csr
               AND request_status IN('PENDING','BUYER_APP')
               AND document_header_id=l_po_header_id
               AND  ( (nvl(new_promised_date,sysdate) <> nvl(old_promised_date,sysdate-1)) OR
	              (nvl(new_promised_date,old_promised_date-1)<>old_promised_date) OR
	              (nvl(old_promised_date,new_promised_date-1)<>new_promised_date)
                    );

Line: 3849

        select  count(1)
        from   po_change_requests
        where  initiator='SUPPLIER'
	       AND action_type='MODIFICATION'
               AND change_request_group_id = l_change_group_id_csr
	       AND request_level='LINE'
               AND request_status IN('PENDING','BUYER_APP')
               AND document_header_id=l_po_header_id;

Line: 4000

SELECT  pcr.change_request_group_id,
	pcr.new_price new_price,
	pcr.new_quantity,
	pcr.new_start_date,
	pcr.new_expiration_date,
	pcr.new_amount,
	pcr.request_level,
        --pcr.new_promised_date,
	nvl(pcr.new_need_by_date,decode(prla.need_by_date,plla.need_by_date,null,plla.need_by_date)),
        pcr.request_reason,
	prla.requisition_line_id,
	prla.requisition_header_id,
	prla.line_location_id,
	prda.distribution_id
FROM    po_change_requests pcr,
        po_requisition_lines_all prla,
	po_req_distributions_all prda,
	po_line_locations_all plla
WHERE   document_header_id= p_po_header_id_csr
        AND request_status='BUYER_APP'
        AND change_active_flag='Y'
        AND initiator='SUPPLIER'
      --AND pcr.document_line_location_id = prla.line_location_id(+)
        AND prda.requisition_line_id=prla.requisition_line_id
        AND pcr.request_level='SHIPMENT'
        AND plla.line_location_id = prla.line_location_id
	AND plla.po_line_id=pcr.document_line_id
	AND plla.po_header_id=pcr.document_header_id
	AND (   pcr.new_price    is not null    --New change JAI
	     OR pcr.new_quantity is not null
	     OR pcr.new_start_date is not null
	     OR pcr.new_expiration_date is not null
	     OR pcr.new_amount is not null
	    )
UNION
SELECT  pcr.change_request_group_id,
	pcr.new_price,
	null new_quantity,
	null new_start_date ,
	null new_expiration_date,
	null new_amount,
	pcr.request_level,
      --null new_promised_date,
	null new_need_by_date,
        pcr.request_reason,
	prla.requisition_line_id,
	prla.requisition_header_id,
	prla.line_location_id,
	null distribution_id
FROM    po_change_requests pcr,
	po_line_locations_all plla,
	po_requisition_lines_all prla
WHERE   document_header_id=p_po_header_id_csr
        AND request_status='BUYER_APP'
        AND change_active_flag='Y'
        AND initiator='SUPPLIER'
	AND pcr.request_level='LINE'
	AND plla.line_location_id = prla.line_location_id
	AND plla.po_line_id=pcr.document_line_id
	AND plla.po_header_id=pcr.document_header_id;

Line: 4064

SELECT  pcr.change_request_group_id,
	pcr.new_price new_price,
	pcr.new_quantity,
	pcr.new_start_date,
	pcr.new_expiration_date,
	pcr.new_amount,
	pcr.request_level,
        --pcr.new_promised_date,
	nvl(pcr.new_need_by_date,decode(prla.need_by_date,plla.need_by_date,null,plla.need_by_date)),
        pcr.request_reason,
	prla.requisition_line_id,
	prla.requisition_header_id,
	prla.line_location_id,
	prda.distribution_id
FROM    po_change_requests pcr,
        po_requisition_lines_all prla,
	po_req_distributions_all prda,
	po_line_locations_all plla
WHERE   pcr.po_release_id= p_po_release_id_csr
        AND request_status='BUYER_APP'
        AND change_active_flag='Y'
        AND initiator='SUPPLIER'
      --AND pcr.document_line_location_id = prla.line_location_id(+)
        AND prda.requisition_line_id=prla.requisition_line_id
        AND pcr.request_level='SHIPMENT'
        AND plla.line_location_id = prla.line_location_id
	AND plla.po_line_id=pcr.document_line_id
	AND plla.po_header_id=pcr.document_header_id
    AND plla.po_release_id =pcr.po_release_id
	AND (   pcr.new_price    is not null    --New change JAI
         OR pcr.new_quantity is not null
	     OR pcr.new_start_date is not null
	     OR pcr.new_expiration_date is not null
	     OR pcr.new_amount is not null
	    )
UNION
SELECT  pcr.change_request_group_id,
	pcr.new_price,
	null new_quantity,
	null new_start_date ,
	null new_expiration_date,
	null new_amount,
	pcr.request_level,
      --null new_promised_date,
	null new_need_by_date,
        pcr.request_reason,
	prla.requisition_line_id,
	prla.requisition_header_id,
	prla.line_location_id,
	null distribution_id
FROM    po_change_requests pcr,
	po_line_locations_all plla,
	po_requisition_lines_all prla
WHERE   pcr.po_release_id=p_po_release_id_csr
        AND request_status='BUYER_APP'
        AND change_active_flag='Y'
        AND initiator='SUPPLIER'
	AND pcr.request_level='LINE'
	AND plla.line_location_id = prla.line_location_id
	AND plla.po_line_id=pcr.document_line_id
	AND plla.po_header_id=pcr.document_header_id;

Line: 4323

   SELECT ame_transaction_type
   INTO   l_ame_transaction_type
   FROM   po_document_types
   WHERE  document_type_code = 'CHANGE_REQUEST' and
          document_subtype = 'REQUISITION';

Line: 4344

     update PO_CHANGE_REQUESTS
     set Parent_change_request_id = x_change_group_id
     where change_request_group_id= l_change_group_id;

Line: 4495

  select UNIQUE(porh.PREPARER_ID)
  from   po_requisition_headers_all porh,
         po_requisition_lines_all porl,
         po_headers_all poh,
         po_line_locations_all poll
  where  porh.requisition_header_id = porl.requisition_header_id AND
         porl.line_location_id = poll.line_location_id  AND
         poh.po_header_id = poll.po_header_id AND
         poh.po_header_id = c_po_header_id;

Line: 4507

  select pda.deliver_to_person_id
  from
  	po_change_requests pcr,
  	po_distributions_all pda
  where pcr.change_request_group_id = c_grp_id_csr
  AND pcr.request_level = 'LINE'
  AND pcr.document_line_id = pda.po_line_id
  union
  select pda.deliver_to_person_id
  from
  	po_change_requests pcr,
  	po_distributions_all pda
  where pcr.change_request_group_id = c_grp_id_csr
  AND pcr.request_level = 'SHIPMENT'
  AND pcr.document_line_location_id = pda.line_location_id;

Line: 4608

       update po_change_requests
        set request_status ='REQ_APP',
            responded_by = fnd_global.user_id,
            response_date = sysdate
        where change_request_group_id = l_change_group_id
              AND request_status = 'PENDING';