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.AP_AUTOMATIC_PROPAGATION_PKG SQL Statements

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

Line: 16


      SELECT COUNT(DISTINCT ai.invoice_id)
      INTO   l_affected_invoices
      FROM   ap_invoices_all ai, ap_payment_schedules_all aps
      WHERE  aps.external_bank_account_id  = p_external_bank_account_id
         AND    ai.invoice_id                 = aps.invoice_id
      -- AND    ai.payment_status_flag        IN ('N','P') /* Bug 11783234 Exclude 'partial paid payment schedules' status */
         AND    ai.payment_status_flag        = 'N'
         AND    ai.cancelled_date             IS NULL
         AND    ai.vendor_id                = p_vendor_id
         AND    ai.org_id                   = p_org_id
         AND    (ai.party_site_id           = p_party_site_id
	         or ( ai.party_site_id is null
		      and  ai.vendor_site_id in (select vendor_site_id
		                                 from ap_supplier_sites ass
		                                 where ass.party_site_id = p_party_site_id
					           and ass.vendor_id   = p_vendor_id)
                    )
		 )
         AND    aps.checkrun_id		      IS NULL; /* Bug 11783234 Added condition to remove selected invoices from this API */

Line: 38


      SELECT COUNT(DISTINCT ai.invoice_id)
      INTO   l_affected_invoices
      FROM   ap_invoices_all ai, ap_payment_schedules_all aps
      WHERE  aps.external_bank_account_id  = p_external_bank_account_id
         AND    ai.invoice_id                 = aps.invoice_id
      -- AND    ai.payment_status_flag        IN ('N','P') /* Bug 11783234 Exclude 'partial paid payment schedules' status */
	 AND    ai.payment_status_flag        = 'N'
         AND    ai.cancelled_date             IS NULL
         AND    ai.vendor_id                = p_vendor_id
         AND    (ai.party_site_id           = p_party_site_id
	         or ( ai.party_site_id is null
		      and  ai.vendor_site_id in (select vendor_site_id
		                                 from ap_supplier_sites ass
		                                 where ass.party_site_id = p_party_site_id
					           and ass.vendor_id   = p_vendor_id)
                    )
		 )
          AND   aps.checkrun_id			IS NULL; /* Bug 11783234 Added condition to remove selected invoices from this API */

Line: 59


      SELECT COUNT(DISTINCT ai.invoice_id)
      INTO   l_affected_invoices
      FROM   ap_invoices_all ai, ap_payment_schedules_all aps
      WHERE  aps.external_bank_account_id  = p_external_bank_account_id
        AND    ai.invoice_id                 = aps.invoice_id
    --  AND    ai.payment_status_flag        IN ('N','P') /* Bug 11783234 Exclude 'partial paid payment schedules' status */
        AND    ai.payment_status_flag        = 'N'
        AND    ai.cancelled_date             IS NULL
        AND    ai.vendor_id                  = p_vendor_id
        AND    ai.vendor_site_id             = p_vendor_site_id
	AND    aps.checkrun_id		     IS NULL; /* Bug 11783234 Added condition to remove selected invoices from this API */

Line: 72


      SELECT COUNT(DISTINCT ai.invoice_id)
      INTO   l_affected_invoices
      FROM   ap_invoices_all ai, ap_payment_schedules_all aps
      WHERE  aps.external_bank_account_id  = p_external_bank_account_id
        AND    ai.invoice_id                 = aps.invoice_id
     -- AND    ai.payment_status_flag        IN ('N','P') /* Bug 11783234 Exclude 'partial paid payment schedules' status */
        AND    ai.payment_status_flag        = 'N'
        AND    ai.cancelled_date             IS NULL
        AND    ai.vendor_id                  = p_vendor_id
	AND    aps.checkrun_id	             IS  NULL; /* Bug 11783234 Added condition to remove selected invoices from this API */

Line: 91


/* Bug 10634847  Added API to return selected invoices for payments count */
FUNCTION Get_Selected_Invoices_Count(
   P_external_bank_account_id iby_ext_bank_accounts.ext_bank_account_id%TYPE,
   P_vendor_id                ap_suppliers.vendor_id%TYPE,
   P_vendor_site_id           ap_supplier_sites.vendor_site_id%TYPE DEFAULT NULL,
   P_party_Site_Id            ap_supplier_sites.party_site_id%TYPE  DEFAULT NULL,
   P_org_id                   ap_invoices.org_id%TYPE  DEFAULT NULL
  ) RETURN NUMBER IS
  l_affected_invoices NUMBER := 0;

Line: 105


l_api_name              VARCHAR2(100) := 'Get_Selected_Invoices_Count';

Line: 110


    l_debug_info := 'ENTER Get_Selected_Invoices_Count';

Line: 134


      SELECT COUNT(DISTINCT ai.invoice_id)
      INTO   l_affected_invoices
      FROM   ap_invoices_all ai, ap_payment_schedules_all aps
      WHERE  aps.external_bank_account_id  = p_external_bank_account_id
         AND    ai.invoice_id                 = aps.invoice_id
         AND    ai.payment_status_flag        IN ('N','P')
         AND    ai.cancelled_date             IS NULL
         AND    ai.vendor_id                = p_vendor_id
         AND    ai.org_id                   = p_org_id
         AND    (ai.party_site_id           = p_party_site_id
	         or ( ai.party_site_id is null
		      and  ai.vendor_site_id in (select vendor_site_id
		                                 from ap_supplier_sites ass
		                                 where ass.party_site_id = p_party_site_id
					           and ass.vendor_id   = p_vendor_id)
                    )
		 )
	AND aps.checkrun_id			IS NOT NULL;

Line: 155


      SELECT COUNT(DISTINCT ai.invoice_id)
      INTO   l_affected_invoices
      FROM   ap_invoices_all ai, ap_payment_schedules_all aps
      WHERE  aps.external_bank_account_id  = p_external_bank_account_id
         AND    ai.invoice_id                 = aps.invoice_id
         AND    ai.payment_status_flag        IN ('N','P')
         AND    ai.cancelled_date             IS NULL
         AND    ai.vendor_id                = p_vendor_id
         AND    (ai.party_site_id           = p_party_site_id
	         or ( ai.party_site_id is null
		      and  ai.vendor_site_id in (select vendor_site_id
		                                 from ap_supplier_sites ass
		                                 where ass.party_site_id = p_party_site_id
					           and ass.vendor_id   = p_vendor_id)
                    )
		 )
	AND aps.checkrun_id			IS NOT NULL;

Line: 175


      SELECT COUNT(DISTINCT ai.invoice_id)
      INTO   l_affected_invoices
      FROM   ap_invoices_all ai, ap_payment_schedules_all aps
      WHERE  aps.external_bank_account_id  = p_external_bank_account_id
        AND    ai.invoice_id                 = aps.invoice_id
        AND    ai.payment_status_flag        IN ('N','P')
        AND    ai.cancelled_date             IS NULL
        AND    ai.vendor_id                  = p_vendor_id
        AND    ai.vendor_site_id             = p_vendor_site_id
	AND aps.checkrun_id	         	IS NOT NULL;

Line: 187


      SELECT COUNT(DISTINCT ai.invoice_id)
      INTO   l_affected_invoices
      FROM   ap_invoices_all ai, ap_payment_schedules_all aps
      WHERE  aps.external_bank_account_id  = p_external_bank_account_id
        AND    ai.invoice_id                 = aps.invoice_id
        AND    ai.payment_status_flag        IN ('N','P')
        AND    ai.cancelled_date             IS NULL
        AND    ai.vendor_id                  = p_vendor_id
	AND aps.checkrun_id			IS NOT NULL;

Line: 201


      l_debug_info := 'Exception occured in Get_Selected_Invoices_Count  :'||SQLERRM;

Line: 207


END Get_Selected_Invoices_Count;

Line: 209


PROCEDURE Update_Payment_Schedules (
    p_from_bank_account_id iby_ext_bank_accounts.ext_bank_account_id%TYPE,
    p_to_bank_account_id   iby_ext_bank_accounts.ext_bank_account_id%TYPE,
    p_vendor_id            ap_suppliers.vendor_id%TYPE,
    P_vendor_site_id       ap_supplier_sites.vendor_site_id%TYPE DEFAULT NULL,
    P_party_Site_Id        ap_supplier_sites.party_site_id%TYPE  DEFAULT NULL,
    P_org_id               ap_invoices.org_id%TYPE  DEFAULT NULL,
    P_party_id             ap_suppliers.party_id%TYPE	DEFAULT NULL			-- Added for bug 9410719
   ) IS

-- Added for bug 9410719
G_LEVEL_STATEMENT       CONSTANT NUMBER       := FND_LOG.LEVEL_STATEMENT;

Line: 224


l_api_name              VARCHAR2(100) := 'Update_Payment_Schedules';

Line: 233


    l_debug_info := 'ENTER Update_Payment_Schedules';

Line: 272


	FOR x IN (SELECT aps.invoice_id, aps.payment_num, ai.payment_currency_code, ai.vendor_site_id
                  FROM   ap_payment_schedules_all aps,ap_invoices_all ai
                  WHERE  aps.invoice_id = ai.invoice_id
                  AND    aps.external_bank_account_id = p_from_bank_account_id
		   -- Added the below two condition for the bug 10401158
                  AND    aps.checkrun_id is NULL
                  AND    nvl(aps.payment_status_flag , 'N') = 'N'
                  AND    ai.payment_status_flag        IN ('N','P')
                  AND    ai.cancelled_date             IS NULL
                  AND    ai.vendor_id                  = p_vendor_id
                  AND    ai.org_id                     = p_org_id
                  AND   (ai.party_site_id              = p_party_site_id
                         OR   (ai.party_site_id is NULL
                               AND  ai.vendor_site_id in (SELECT vendor_site_id
                                                          FROM ap_supplier_sites ass
                                                          WHERE ass.party_site_id = p_party_site_id
                                                          AND ass.vendor_id   = p_vendor_id)
                              )
                        )
                  )
          LOOP
                BEGIN
			IBY_DISBURSE_UI_API_PUB_PKG.get_default_bank_acct(x.payment_currency_code,
				                                          P_party_id,
					                                  P_party_Site_Id,
						                          x.vendor_site_id,
							                  P_org_id,
								          P_org_type,
									  P_pmt_function,
									  p_from_bank_account_id,
	                                                                  l_to_bank_account_id
		                                                         );

Line: 311


			UPDATE ap_payment_schedules_all aps
			SET aps.external_bank_account_id = l_to_bank_account_id,
		            last_update_date  = SYSDATE,
			    last_updated_by   = FND_GLOBAL.user_id,
		            last_update_login = FND_GLOBAL.login_id
		        WHERE aps.invoice_id = x.invoice_id -- Added and commented below code for bug 9410719
			AND   aps.payment_num = x.payment_num;

Line: 319


			--Bug10065192: Update the bank at invoice header level, so recalculation of
			--payment schedules does not reinstate the inactive bank
			UPDATE ap_invoices_all ai
			SET ai.external_bank_account_id = l_to_bank_account_id,
		        ai.last_update_date  = SYSDATE,
			    ai.last_updated_by   = FND_GLOBAL.user_id,
		        ai.last_update_login = FND_GLOBAL.login_id
		    WHERE ai.invoice_id = x.invoice_id;

Line: 329


           (SELECT DISTINCT ai.invoice_id
            FROM   ap_invoices_all ai, ap_payment_schedules_all aps
            WHERE  aps.external_bank_account_id  = p_from_bank_account_id
            AND    ai.invoice_id                 = aps.invoice_id
            AND    ai.payment_status_flag        IN ('N','P')
            AND    ai.cancelled_date             IS NULL
            AND    ai.vendor_id                  = p_vendor_id
            AND    ai.org_id                     = p_org_id
            AND   (ai.party_site_id           = p_party_site_id
	           or ( ai.party_site_id is null
		      and  ai.vendor_site_id in (select vendor_site_id
		                                 from ap_supplier_sites ass
		                                 where ass.party_site_id = p_party_site_id
					           and ass.vendor_id   = p_vendor_id)
                     )
		   )
	    );

Line: 359


	FOR x IN (SELECT aps.invoice_id, aps.payment_num, ai.payment_currency_code, ai.vendor_site_id, ai.org_id
                  FROM ap_payment_schedules_all aps,ap_invoices_all ai
                  WHERE aps.invoice_id		    = ai.invoice_id
                  AND aps.external_bank_account_id  = p_from_bank_account_id
		  -- Added the below two condition for the bug 10401158
                  AND    aps.checkrun_id is NULL
                  AND    nvl(aps.payment_status_flag , 'N') = 'N'
                  AND    ai.payment_status_flag     IN ('N','P')
                  AND    ai.cancelled_date          IS NULL
                  AND    ai.vendor_id               = p_vendor_id
                  AND   (ai.party_site_id           = p_party_site_id
                          OR (ai.party_site_id IS NULL
                              AND  ai.vendor_site_id IN (SELECT vendor_site_id
                                                         FROM ap_supplier_sites ass
                                                         WHERE ass.party_site_id = p_party_site_id
                                                         AND ass.vendor_id   = p_vendor_id)
                              )
                          )
                  )
          LOOP
                BEGIN
			IBY_DISBURSE_UI_API_PUB_PKG.get_default_bank_acct(x.payment_currency_code,
				                                          P_party_id,
					                                  P_party_Site_Id,
						                          x.vendor_site_id,
							                  x.org_id,
								          P_org_type,
									  P_pmt_function,
									  p_from_bank_account_id,
	                                                                  l_to_bank_account_id
		                                                          );

Line: 396


			UPDATE ap_payment_schedules_all aps
		        SET aps.external_bank_account_id = l_to_bank_account_id,
		            last_update_date  = SYSDATE,
		            last_updated_by   = FND_GLOBAL.user_id,
		            last_update_login = FND_GLOBAL.login_id
		        WHERE aps.invoice_id = x.invoice_id -- Added and commented below code for bug 9410719
			AND   aps.payment_num = x.payment_num;

Line: 404


			--Bug10065192: Update the bank at invoice header level, so recalculation of
			--payment schedules does not reinstate the inactive bank
			UPDATE ap_invoices_all ai
			SET ai.external_bank_account_id = l_to_bank_account_id,
		        ai.last_update_date  = SYSDATE,
			    ai.last_updated_by   = FND_GLOBAL.user_id,
		        ai.last_update_login = FND_GLOBAL.login_id
		    WHERE ai.invoice_id = x.invoice_id;

Line: 414


           (SELECT DISTINCT ai.invoice_id
            FROM   ap_invoices_all ai, ap_payment_schedules_all aps
            WHERE  aps.external_bank_account_id  = p_from_bank_account_id
            AND    ai.invoice_id                 = aps.invoice_id
            AND    ai.payment_status_flag        IN ('N','P')
            AND    ai.cancelled_date             IS NULL
            AND    ai.vendor_id                  = p_vendor_id
            AND   (ai.party_site_id           = p_party_site_id
	           or ( ai.party_site_id is null
		      and  ai.vendor_site_id in (select vendor_site_id
		                                 from ap_supplier_sites ass
		                                 where ass.party_site_id = p_party_site_id
					           and ass.vendor_id   = p_vendor_id)
                     )
		   )
	    );

Line: 443


	FOR x in (SELECT aps.invoice_id, aps.payment_num, ai.payment_currency_code, ai.party_site_id, ai.org_id
                  FROM ap_payment_schedules_all aps,ap_invoices_all ai
                  WHERE aps.invoice_id = ai.invoice_id
                  AND aps.external_bank_account_id     = p_from_bank_account_id
		  -- Added the below two condition for the bug 10401158
                  AND    aps.checkrun_id is NULL
                  AND    nvl(aps.payment_status_flag , 'N') = 'N'
                  AND    ai.payment_status_flag        IN ('N','P')
	          AND    ai.cancelled_date             IS NULL
		  AND    ai.vendor_id                  = p_vendor_id
                  AND    ai.vendor_site_id             = p_vendor_site_id
                 )
        LOOP
            BEGIN
		IBY_DISBURSE_UI_API_PUB_PKG.get_default_bank_acct(x.payment_currency_code,
                                                                  P_party_id,
                                                                  x.party_Site_Id,
                                                                  P_vendor_site_id,
                                                                  x.org_id,
                                                                  P_org_type,
                                                                  P_pmt_function,
								  p_from_bank_account_id,
                                                                  l_to_bank_account_id
                                                                  );

Line: 473


	       UPDATE ap_payment_schedules_all aps
	       SET aps.external_bank_account_id = l_to_bank_account_id,
	            last_update_date  = SYSDATE,
		    last_updated_by   = FND_GLOBAL.user_id,
	            last_update_login = FND_GLOBAL.login_id
		WHERE aps.invoice_id = x.invoice_id -- Added and commented below code for bug 9410719
		AND   aps.payment_num = x.payment_num;

Line: 481


		--Bug10065192: Update the bank at invoice header level, so recalculation of
		--payment schedules does not reinstate the inactive bank
		UPDATE ap_invoices_all ai
		SET ai.external_bank_account_id = l_to_bank_account_id,
			ai.last_update_date  = SYSDATE,
			ai.last_updated_by   = FND_GLOBAL.user_id,
			ai.last_update_login = FND_GLOBAL.login_id
		WHERE ai.invoice_id = x.invoice_id;

Line: 491


           (SELECT DISTINCT ai.invoice_id
            FROM   ap_invoices_all ai, ap_payment_schedules_all aps
            WHERE  aps.external_bank_account_id  = p_from_bank_account_id
            AND    ai.invoice_id                 = aps.invoice_id
            AND    ai.payment_status_flag        IN ('N','P')
            AND    ai.cancelled_date             IS NULL
            AND    ai.vendor_id                  = p_vendor_id
            AND    ai.vendor_site_id             = p_vendor_site_id);

Line: 512


	FOR x in (SELECT aps.invoice_id, aps.payment_num, ai.payment_currency_code, ai.party_site_id, ai.org_id, ai.vendor_site_id
                  FROM ap_payment_schedules_all aps,ap_invoices_all ai
                  WHERE aps.invoice_id		       = ai.invoice_id
                  AND aps.external_bank_account_id     = p_from_bank_account_id
		  -- Added the below two condition for the bug 10401158
                  AND    aps.checkrun_id is NULL
                  AND    nvl(aps.payment_status_flag , 'N') = 'N'
                  AND    ai.payment_status_flag        IN ('N','P')
                  AND    ai.cancelled_date             IS NULL
                  AND    ai.vendor_id                  = p_vendor_id
                  )
        LOOP
            BEGIN
                IBY_DISBURSE_UI_API_PUB_PKG.get_default_bank_acct(x.payment_currency_code,
                                                                  P_party_id,
                                                                  x.party_Site_Id,
                                                                  x.vendor_site_id,
                                                                  x.org_id,
                                                                  P_org_type,
                                                                  P_pmt_function,
								  p_from_bank_account_id,
                                                                  l_to_bank_account_id
                                                                  );

Line: 542


		UPDATE ap_payment_schedules_all aps
		SET aps.external_bank_account_id = l_to_bank_account_id,
	            last_update_date  = SYSDATE,
	            last_updated_by   = FND_GLOBAL.user_id,
	            last_update_login = FND_GLOBAL.login_id
		WHERE aps.invoice_id = x.invoice_id -- Added and commented below code for bug 9410719
		AND   aps.payment_num = x.payment_num;

Line: 550


		--Bug10065192: Update the bank at invoice header level, so recalculation of
		--payment schedules does not reinstate the inactive bank
		UPDATE ap_invoices_all ai
		SET ai.external_bank_account_id = l_to_bank_account_id,
			ai.last_update_date  = SYSDATE,
			ai.last_updated_by   = FND_GLOBAL.user_id,
			ai.last_update_login = FND_GLOBAL.login_id
		WHERE ai.invoice_id = x.invoice_id;

Line: 560


           (SELECT DISTINCT ai.invoice_id
            FROM   ap_invoices_all ai, ap_payment_schedules_all aps
            WHERE  aps.external_bank_account_id  = p_from_bank_account_id
            AND    ai.invoice_id                 = aps.invoice_id
            AND    ai.payment_status_flag        IN ('N','P')
            AND    ai.cancelled_date             IS NULL
            AND    ai.vendor_id                  = p_vendor_id)
*/
	    EXCEPTION
                WHEN OTHERS THEN
                     l_debug_info := 'Exception occured when vendor_id is not null :'||SQLERRM;

Line: 583


END Update_Payment_Schedules;