APPS.AP_RETAINAGE_RELEASE_PKG SQL Statements

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

Line: 25

	Select ai.invoice_currency_code,
        --Bug6893516 Exchange rate should be 1 for base currency invoices
	       nvl(ai.exchange_rate,1) exchange_rate,
	       ai.gl_date,
	       ai.vendor_id,
	       ai.vendor_site_id,
	       ai.org_id
	  From ap_invoices_all ai
	 Where invoice_id = c_invoice_id;

Line: 63

Procedure Update_PO_Shipment_Dists
			(x_line_location_id	IN ap_invoice_lines_all.po_line_location_id%type,
			 x_released_amount	IN ap_invoice_lines_all.retained_amount%type,
			 x_released_dist_tab	IN relDistType);

Line: 247

     select
	substr(patch_level, 8, 1)
     into
	l_patch_level
     from   fnd_product_installations
     where  application_id = 200;

Line: 254

     select
	create_awt_dists_type
     into
	l_create_awt_dists_type
     from ap_system_parameters;

Line: 267

     SELECT nvl(max(line_number),0) + 1
     INTO   l_max_inv_line_num
     FROM   ap_invoice_lines_all
     WHERE  invoice_id = X_invoice_id;

Line: 361

	 x_released_lines_tab(i).last_updated_by		:= g_user_id;

Line: 362

	 x_released_lines_tab(i).last_update_date		:= sysdate;

Line: 363

	 x_released_lines_tab(i).last_update_login		:= g_login_id;

Line: 479

			INSERT INTO ap_invoice_lines_all VALUES x_released_lines_tab(i);

Line: 489

			UPDATE ap_invoice_lines
			   SET retained_amount_remaining = (abs(retained_amount_remaining) - abs(release_amount_tab(i)))
			 WHERE invoice_id  = retained_inv_id_tab(i)
			   AND line_number = retained_line_num_tab(i);

Line: 510

	select aid.*,
	       ap_invoice_distributions_s.nextval released_invoice_dist_id
          from ap_invoice_distributions aid
	 where invoice_id		= c_invoice_id
	   and invoice_line_number	= c_line_number
           and line_type_lookup_code    = 'RETAINAGE';

Line: 639

		    released_dist_tab(j).last_updated_by		:= g_user_id;

Line: 640

		    released_dist_tab(j).last_update_date		:= sysdate;

Line: 641

		    released_dist_tab(j).last_update_login		:= g_login_id;

Line: 751

		l_debug_info := 'Step 5: Update PO Shipment and Distributions';

Line: 755

		Update_PO_Shipment_Dists (x_released_lines_tab(i).po_line_location_id,
					  x_released_lines_tab(i).amount,
					  released_dist_tab);

Line: 760

		l_debug_info := 'Step 6: Insert Retainage Release Distributions: Count: '||released_dist_tab.count;

Line: 768

			INSERT INTO ap_invoice_distributions_all VALUES released_dist_tab(k);

Line: 773

		l_debug_info := 'Step 7: Update original invoice line retained amount remaining';

Line: 781

			UPDATE ap_invoice_distributions_all
			   SET retained_amount_remaining = (abs(retained_amount_remaining) - abs(release_amount_tab(i)))
			 WHERE invoice_distribution_id   = retained_inv_dist_id_tab(i);

Line: 789

		    SELECT pl.recoupment_rate
		      INTO l_recoupment_rate
		      FROM po_line_locations_all pll, po_lines_all pl
		     WHERE pll.line_location_id = x_released_lines_tab(i).po_line_location_id
		       AND pll.po_line_id = pl.po_line_id;

Line: 812

		                              P_Last_Update_Login    => g_login_id,
		                              P_Error_Message        => l_error_message,
		                              P_Calling_Sequence     => 'AP_RETAINAGE_RELEASE_PKG.CREATE_RELEASE_DISTRIBUTIONS');

Line: 839

     SELECT sum(ap_utilities_pkg.ap_round_currency(ail.retained_amount * ai.exchange_rate, ai.invoice_currency_code))
       INTO l_sum_retained_base_amount
       FROM ap_invoices      ai,
            ap_invoice_lines ail
	 WHERE ai.invoice_id = ail.invoice_id
	   AND ail.po_line_location_id	= x_line_location_id
	   AND ail.line_type_lookup_code = 'ITEM'
	   AND NVL(ail.discarded_flag,'N') <> 'Y';

Line: 848

	SELECT sum(base_amount)
	  INTO l_sum_released_base_amount
	  FROM ap_invoice_lines ail
	 WHERE (ail.retained_invoice_id, ail.retained_line_number)
	       IN
	       (Select invoice_id, line_number
		  From ap_invoice_lines ail2
		 Where ail.po_line_location_id	= x_line_location_id
		   And ail.line_type_lookup_code = 'ITEM'
		   And nvl(ail.discarded_flag,'N') <> 'Y');

Line: 861

	UPDATE ap_invoice_distributions
	   SET final_release_rounding  = l_final_release_rounding_amt
	 WHERE invoice_distribution_id = x_max_invoice_dist_id;

Line: 889

	     Update ap_invoice_lines
		Set line_selected_for_release_flag = 'Y'
	      Where invoice_id	= lock_invoice_id_tab(i)
	        And line_number	= lock_line_number_tab(i);

Line: 943

	     Update ap_invoice_lines
		Set line_selected_for_release_flag = NULL
	      Where invoice_id	= lock_invoice_id_tab(i)
	        And line_number	= lock_line_number_tab(i);

Line: 967

Procedure Update_PO_Shipment_Dists
			(x_line_location_id	IN ap_invoice_lines_all.po_line_location_id%type,
			 x_released_amount	IN ap_invoice_lines_all.retained_amount%type,
			 x_released_dist_tab	IN relDistType) As

 l_po_ap_dist_rec		PO_AP_DIST_REC_TYPE;

Line: 983

   l_api_name := 'Update_PO_Shipment_Dists';

Line: 1022

   PO_AP_INVOICE_MATCH_GRP.Update_Document_Ap_Values(
					P_Api_Version	       => 1.0,
					P_Line_Loc_Changes_Rec => l_po_ap_line_loc_rec,
					P_Dist_Changes_Rec     => l_po_ap_dist_rec,
					X_Return_Status	       => l_return_status,
					X_Msg_Data	       => l_msg_data);

Line: 1028

END Update_PO_Shipment_Dists;