APPS.ARI_SELF_REG_CONFIG SQL Statements

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

Line: 9

 |      when the user selects the location of the customer requesting access.
 |
 | PARAMETERS
 |      p_customer_id           IN VARCHAR2
 |      p_customer_site_use_id  IN VARCHAR2 DEFAULT NULL
 |      x_verify_access         OUT ARI_SELF_REGISTRATION_PKG.VerifyAccessTable
 |      x_attempts              OUT NUMBER
 |
 | NOTES
 |      Records in ARI_SELF_REGISTRATION_PKG.VerifyAccessTable contain the variables listed below:
 |          question        VARCHAR2(2000)
 |          expected_answer VARCHAR2(2000)
 |          currency_code   VARCHAR2(15)
 |
 | MODIFICATION HISTORY
 | Date                  Author            Description of Changes
 | 09-May-2005           vnb               Created
 |
 *=======================================================================*/
PROCEDURE  verify_customer_site_access( p_customer_id          IN VARCHAR2,
                                        p_customer_site_use_id IN VARCHAR2 DEFAULT NULL,
                                        x_verify_access        OUT NOCOPY ARI_SELF_REGISTRATION_PKG.VerifyAccessTable,
                                        x_attempts             OUT NOCOPY NUMBER)
IS
    l_trx_number    VARCHAR2(255);

Line: 40

    	select organization_id org_id from hr_operating_units where mo_global.check_access(organization_id) = 'Y';

Line: 55

    	SELECT org_id INTO cust_site_org_id
    	FROM hz_cust_site_uses
    	WHERE site_use_id = p_customer_site_use_id ;

Line: 91

                SELECT trx_number
                INTO l_trx_number
                FROM ar_payment_schedules
                WHERE customer_id = p_customer_id
                AND customer_site_use_id = nvl(p_customer_site_use_id,customer_site_use_id)
                AND status like 'OP'
                AND invoice_currency_code = 'USD'
                AND amount_due_remaining > 0
                AND rownum = 1;

Line: 109

                    SELECT to_char(amount_due_remaining)
                    INTO x_verify_access(1).expected_answer
                    FROM ar_payment_schedules
                    WHERE customer_id = p_customer_id
                    AND customer_site_use_id = nvl(p_customer_site_use_id,customer_site_use_id)
                    AND status like 'OP'
                    AND invoice_currency_code = 'USD'
                    AND amount_due_remaining > 0
                    AND rownum = 1;

Line: 131

              SELECT receipt_number
              INTO l_receipt_number
              FROM ar_cash_receipts
              WHERE PAY_FROM_CUSTOMER = p_customer_id
              AND customer_site_use_id = nvl(p_customer_site_use_id,customer_site_use_id)
              AND currency_code = 'USD'
              AND rownum = 1;

Line: 155

                        SELECT amount
                        INTO l_temp
                        FROM ar_cash_receipts
                        WHERE PAY_FROM_CUSTOMER = p_customer_id
                        AND customer_site_use_id = nvl(p_customer_site_use_id,customer_site_use_id)
                        AND currency_code = 'USD'
                        AND rownum = 1;

Line: 190

 |      when the user selects the customer requesting access.
 |
 | PARAMETERS
 |      p_customer_id           IN VARCHAR2
 |      x_verify_access         OUT ARI_SELF_REGISTRATION_PKG.VerifyAccessTable
 |      x_attempts              OUT NUMBER
 |
 | NOTES
 |      Records in ARI_SELF_REGISTRATION_PKG.VerifyAccessTable contain the variables listed below:
 |          question        VARCHAR2(2000)
 |          expected_answer VARCHAR2(2000)
 |          currency_code   VARCHAR2(15)
 |
 | MODIFICATION HISTORY
 | Date                  Author            Description of Changes
 | 09-May-2005           vnb               Created
 |
 *=======================================================================*/
PROCEDURE  validate_cust_detail_access( p_customer_id          IN VARCHAR2,
                                        x_verify_access        OUT NOCOPY ARI_SELF_REGISTRATION_PKG.VerifyAccessTable,
                                        x_attempts             OUT NOCOPY NUMBER)
IS
    l_customer_name     hz_parties.party_name%type;

Line: 234

            select party_name
            into  l_customer_name
            from hz_parties party, hz_cust_accounts cust
            where cust.cust_account_id = p_customer_id
            and cust.party_id = party.party_id;

Line: 287

        SELECT cust_account_id
        INTO l_customer_id
        FROM hz_cust_accounts
        where account_number = p_search_number;

Line: 302

 * the source. So added 'distinct' in the below query to select unique customer_id.
 */
            SELECT DISTINCT customer_id
            INTO l_customer_id
            FROM ar_payment_schedules
            WHERE trx_number = p_search_number
            AND class = (CASE p_search_type
                            WHEN 'INVOICES' THEN 'INV'
            	            WHEN 'PAYMENTS' THEN 'PMT'
                            WHEN 'DEBIT_MEMOS' THEN 'DM'
                            WHEN 'CREDIT_MEMOS' THEN 'CM'
                            WHEN 'DEPOSITS' THEN 'DEP'
                	END);