APPS.IEX_PROMISES_PUB SQL Statements

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

Line: 120

	-- filter passed promises; we need only BROKEN_PROMISE promises for update

Line: 124

	l_SQL := 'SELECT state ' ||
			'FROM IEX_PROMISE_DETAILS ' ||
			'WHERE ' ||
			'PROMISE_DETAIL_ID = :P_PROMISE_ID';

Line: 190

            		update iex_promise_details
            		set UWQ_STATUS = P_STATUS,
                		UWQ_ACTIVE_DATE = l_uwq_active_date,
                		UWQ_COMPLETE_DATE = l_uwq_complete_date,
                		last_update_date = sysdate,
                		last_updated_by = G_USER_ID
            		where
                		promise_detail_id = l_broken_promises(i);

Line: 200

		iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': nothing to update');

Line: 258

		SELECT delinquency_id, cust_account_id, CNSLD_INVOICE_ID, CONTRACT_ID
		FROM IEX_PROMISE_DETAILS
		WHERE PROMISE_DETAIL_ID = P_PROMISE_ID;*/

Line: 267

		SELECT del.party_cust_id,del.cust_account_id,del.CUSTOMER_SITE_USE_ID,
		del.delinquency_id,  prd.CNSLD_INVOICE_ID, prd.CONTRACT_ID
		FROM IEX_PROMISE_DETAILS prd,
		IEX_DELINQUENCIES_ALL del
		WHERE prd.PROMISE_DETAIL_ID = P_PROMISE_ID
		and prd.delinquency_id = del.delinquency_id;

Line: 310

	/*l_SQL := 'SELECT delinquency_id, cust_account_id, CNSLD_INVOICE_ID, CONTRACT_ID ' ||
			'FROM IEX_PROMISE_DETAILS ' ||
			'WHERE ' ||
			'PROMISE_DETAIL_ID = :P_PROMISE_ID';

Line: 323

	select decode(preference_value, 'CUSTOMER', 10, 'ACCOUNT', 20, 'BILL_TO', 30, 'DELINQUENCY', 40,  40)
	into l_DefaultStrategyLevel
	from iex_app_preferences_vl
	where  preference_name = 'COLLECTIONS STRATEGY LEVEL'
	and enabled_flag = 'Y' ;

Line: 476

		select IEX_PTP_WF_S.NEXTVAL from dual;

Line: 578

PROCEDURE VALIDATE_INSERT_INPUT(P_PROMISE_REC IN IEX_PROMISES_PUB.PRO_INSRT_REC_TYPE)
IS
    	Type refCur is Ref Cursor;

Line: 582

    	l_api_name		CONSTANT VARCHAR2(30) := 'VALIDATE_INSERT_INPUT';

Line: 702

		l_SQL := 'SELECT ''X'' ' ||
				'FROM IEX_LOOKUPS_V ' ||
				'WHERE ' ||
				'LOOKUP_TYPE = ''IEX_PAYMENT_TYPES'' AND LOOKUP_CODE = :P_PAYMENT_METHOD AND ' ||
				'ENABLED_FLAG = ''Y''';

Line: 755

        	l_SQL := 'SELECT COUNT(1) ' ||
			'FROM IEX_PROMISE_DETAILS ' ||
			'WHERE ' ||
			'DELINQUENCY_ID IS NULL AND CNSLD_INVOICE_ID IS NULL AND CONTRACT_ID IS NULL AND ' ||
			'CUST_ACCOUNT_ID IS NOT NULL AND CUST_ACCOUNT_ID = :P_CUST_ACCOUNT_ID AND ' ||
			'PROMISE_DATE = :P_PROMISE_DATE AND ' ||
			'STATUS in (''COLLECTABLE'', ''PENDING'')';

Line: 821

        	l_SQL := 'SELECT COUNT(1) ' ||
				'FROM IEX_PROMISE_DETAILS ' ||
				'WHERE ' ||
				'CNSLD_INVOICE_ID IS NULL AND CONTRACT_ID IS NULL AND ' ||
				'CUST_ACCOUNT_ID IS NOT NULL AND CUST_ACCOUNT_ID = :P_CUST_ACCOUNT_ID AND ' ||
				'DELINQUENCY_ID IS NOT NULL AND DELINQUENCY_ID = :P_DELINQUENCY_ID AND ' ||
				'PROMISE_DATE = :P_PROMISE_DATE AND ' ||
				'STATUS in (''COLLECTABLE'', ''PENDING'')';

Line: 853

		l_SQL := 'SELECT PSA.INVOICE_CURRENCY_CODE ' ||
				'FROM AR_PAYMENT_SCHEDULES PSA, IEX_DELINQUENCIES DEL ' ||
				'WHERE ' ||
				'DEL.DELINQUENCY_ID = :P_DELINQUENCY_ID AND ' ||
				'DEL.PAYMENT_SCHEDULE_ID = PSA.PAYMENT_SCHEDULE_ID';

Line: 900

        	l_SQL := 'SELECT COUNT(1) ' ||
				'FROM IEX_PROMISE_DETAILS ' ||
				'WHERE ' ||
				'DELINQUENCY_ID IS NULL AND CONTRACT_ID IS NULL AND ' ||
				'CUST_ACCOUNT_ID IS NOT NULL AND CUST_ACCOUNT_ID = :P_CUST_ACCOUNT_ID AND ' ||
				'CNSLD_INVOICE_ID IS NOT NULL AND CNSLD_INVOICE_ID = :P_CNSLD_INVOICE_ID AND ' ||
				'PROMISE_DATE = :P_PROMISE_DATE AND ' ||
				'STATUS in (''COLLECTABLE'', ''PENDING'')';

Line: 933

		l_SQL := 'SELECT CNSLD.CURRENCY_CODE ' ||
				'FROM IEX_BPD_CNSLD_INV_REMAINING_V CNSLD ' ||
				'WHERE ' ||
				'CNSLD.CONSOLIDATED_INVOICE_ID = :P_CNSLD_ID';

Line: 979

        	l_SQL := 'SELECT COUNT(1) ' ||
				'FROM IEX_PROMISE_DETAILS ' ||
				'WHERE ' ||
				'DELINQUENCY_ID IS NULL AND CNSLD_INVOICE_ID IS NULL AND ' ||
				'CUST_ACCOUNT_ID IS NOT NULL AND CUST_ACCOUNT_ID = :P_CUST_ACCOUNT_ID AND ' ||
				'CONTRACT_ID IS NOT NULL AND CONTRACT_ID = :P_CONTRACT_ID AND ' ||
				'PROMISE_DATE = :P_PROMISE_DATE AND ' ||
				'STATUS in (''COLLECTABLE'', ''PENDING'')';

Line: 1013

		l_SQL := 'SELECT cntr.CURRENCY_CODE ' ||
				'FROM iex_pay_okl_contracts_v cntr ' ||
				'WHERE ' ||
				'cntr.CONTRACT_ID = :P_CONTRACT_ID';     */

Line: 1018

		l_SQL := 'SELECT CURRENCY_CODE ' ||
				'FROM OKC_K_HEADERS_B ' ||
				'WHERE ' ||
				'ID = :P_CONTRACT_ID';

Line: 1043

PROCEDURE INSERT_PROMISE(
    	P_API_VERSION		IN      NUMBER,
    	P_INIT_MSG_LIST		IN      VARCHAR2, --  DEFAULT FND_API.G_FALSE,
    	P_COMMIT                IN      VARCHAR2, -- DEFAULT FND_API.G_FALSE,
    	P_VALIDATION_LEVEL	IN      NUMBER, -- DEFAULT FND_API.G_VALID_LEVEL_FULL,
    	X_RETURN_STATUS		OUT NOCOPY     VARCHAR2,
    	X_MSG_COUNT             OUT NOCOPY     NUMBER,
    	X_MSG_DATA	    	OUT NOCOPY     VARCHAR2,
    	P_PROMISE_REC           IN	IEX_PROMISES_PUB.PRO_INSRT_REC_TYPE,
    	X_PRORESP_REC		OUT NOCOPY	IEX_PROMISES_PUB.PRO_RESP_REC_TYPE)
IS
    	l_api_name			CONSTANT VARCHAR2(30) := 'INSERT_PROMISE';

Line: 1079

        	select IEX_PROMISE_DETAILS_S.NEXTVAL from dual;

Line: 1082

		select org_id
		from iex_delinquencies_all
		where delinquency_id = p_del_id;

Line: 1091

    	SAVEPOINT INSERT_PROMISE_PVT;

Line: 1116

	VALIDATE_INSERT_INPUT(P_PROMISE_REC);

Line: 1158

	   iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Doing insert...');

Line: 1161

    	INSERT INTO IEX_PROMISE_DETAILS
	(
		PROMISE_DETAIL_ID,
		OBJECT_VERSION_NUMBER,
		PROGRAM_ID,
		LAST_UPDATE_DATE,
		LAST_UPDATED_BY,
		LAST_UPDATE_LOGIN,
		CREATION_DATE,
		CREATED_BY,
		PROMISE_DATE,
		PROMISE_AMOUNT,
		PROMISE_PAYMENT_METHOD,
		STATUS,
		ACCOUNT,
		PROMISE_ITEM_NUMBER,
		CURRENCY_CODE,
		CAMPAIGN_SCHED_ID,
		DELINQUENCY_ID,
		RESOURCE_ID,
		PROMISE_MADE_BY,
		CUST_ACCOUNT_ID,
		ATTRIBUTE_CATEGORY,
		ATTRIBUTE1,
		ATTRIBUTE2,
		ATTRIBUTE3,
		ATTRIBUTE4,
		ATTRIBUTE5,
		ATTRIBUTE6,
		ATTRIBUTE7,
		ATTRIBUTE8,
		ATTRIBUTE9,
		ATTRIBUTE10,
		ATTRIBUTE11,
		ATTRIBUTE12,
		ATTRIBUTE13,
		ATTRIBUTE14,
		ATTRIBUTE15,
		CNSLD_INVOICE_ID,
		CONTRACT_ID,
		BROKEN_ON_DATE,
		AMOUNT_DUE_REMAINING,
		STATE,
		ORG_ID
	)
	VALUES
	(
		l_promise_id,
		1.0,
		G_APP_ID,
		sysdate,
		G_USER_ID,
		G_LOGIN_ID,
		sysdate,
		G_USER_ID,
		P_PROMISE_REC.PROMISE_DATE,
		P_PROMISE_REC.PROMISE_AMOUNT,
		P_PROMISE_REC.PROMISE_PAYMENT_METHOD,
		l_promise_status,
		P_PROMISE_REC.ACCOUNT,
		P_PROMISE_REC.PROMISE_ITEM_NUMBER,
		P_PROMISE_REC.CURRENCY_CODE,
		P_PROMISE_REC.CAMPAIGN_SCHED_ID,
		P_PROMISE_REC.DELINQUENCY_ID,
		P_PROMISE_REC.TAKEN_BY_RESOURCE_ID,
		P_PROMISE_REC.PROMISED_BY_PARTY_PER_ID,
		P_PROMISE_REC.CUST_ACCOUNT_ID,
		P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE_CATEGORY,
		P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE1,
		P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE2,
		P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE3,
		P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE4,
		P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE5,
		P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE6,
		P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE7,
		P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE8,
		P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE9,
		P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE10,
		P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE11,
		P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE12,
		P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE13,
		P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE14,
		P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE15,
		P_PROMISE_REC.CNSLD_INVOICE_ID,
		P_PROMISE_REC.CONTRACT_ID,
		l_broken_on_date,
		P_PROMISE_REC.PROMISE_AMOUNT,
		l_promise_state,
		l_org_id
	);

Line: 1259

	   iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Insert OK');

Line: 1262

	-- inserting a note
	if P_PROMISE_REC.NOTE is not null then

                IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
		   iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Going to build context for note...');

Line: 1367

      ROLLBACK TO INSERT_PROMISE_PVT;

Line: 1371

      ROLLBACK TO INSERT_PROMISE_PVT;

Line: 1375

      ROLLBACK TO INSERT_PROMISE_PVT;

Line: 1385

PROCEDURE VALIDATE_UPDATE_INPUT(P_PROMISE_REC IN IEX_PROMISES_PUB.PRO_UPDT_REC_TYPE)
IS
    	Type refCur is Ref Cursor;

Line: 1398

	l_procedure			varchar2(50); --  := 'VALIDATE_UPDATE_INPUT';

Line: 1414

        l_str_select varchar2(1000); -- := 'SELECT COUNT(1) ' ||

Line: 1423

	l_procedure := 'VALIDATE_UPDATE_INPUT';

Line: 1430

        l_str_select := 'SELECT COUNT(1) ' ||
			'FROM IEX_PROMISE_DETAILS ' ||
			'WHERE ';

Line: 1492

	l_SQL := 'SELECT CUST_ACCOUNT_ID, DELINQUENCY_ID, CNSLD_INVOICE_ID, CONTRACT_ID, STATUS, STATE, PROMISE_AMOUNT, AMOUNT_DUE_REMAINING ' ||
			'FROM IEX_PROMISE_DETAILS ' ||
			'WHERE ' ||
			'PROMISE_DETAIL_ID = :P_PROMISE_ID';

Line: 1603

        l_SQL := l_str_select || l_where_clause || l_str_cond;

Line: 1606

       	l_SQL := 'SELECT COUNT(1) ' ||
			'FROM IEX_PROMISE_DETAILS ' ||
			'WHERE ' || l_where_clause || ' AND ' ||
			'promise_detail_id <> :P_PROMISE_ID AND ' ||
			'PROMISE_DATE = :P_PROMISE_DATE AND ' ||
			'STATUS in (''COLLECTABLE'', ''PENDING'')';

Line: 1641

		l_SQL := 'SELECT ''X'' ' ||
				'FROM IEX_LOOKUPS_V ' ||
				'WHERE ' ||
				'LOOKUP_TYPE = ''IEX_PAYMENT_TYPES'' AND LOOKUP_CODE = :P_PAYMENT_METHOD AND ' ||
				'ENABLED_FLAG = ''Y''';

Line: 1669

PROCEDURE UPDATE_PROMISE(
    	P_API_VERSION			IN      NUMBER,
    	P_INIT_MSG_LIST			IN      VARCHAR2, -- DEFAULT FND_API.G_FALSE,
    	P_COMMIT                    	IN      VARCHAR2, -- DEFAULT FND_API.G_FALSE,
    	P_VALIDATION_LEVEL	    	IN      NUMBER, --  DEFAULT FND_API.G_VALID_LEVEL_FULL,
    	X_RETURN_STATUS			OUT NOCOPY     VARCHAR2,
    	X_MSG_COUNT                 	OUT NOCOPY     NUMBER,
    	X_MSG_DATA	    	    	OUT NOCOPY     VARCHAR2,
    	P_PROMISE_REC               	IN	IEX_PROMISES_PUB.PRO_UPDT_REC_TYPE,
    	X_PRORESP_REC			OUT NOCOPY	IEX_PROMISES_PUB.PRO_RESP_REC_TYPE)
IS
    	l_api_name			CONSTANT VARCHAR2(30) := 'UPDATE_PROMISE';

Line: 1705

    	SAVEPOINT UPDATE_PROMISE_PVT;

Line: 1729

	VALIDATE_UPDATE_INPUT(P_PROMISE_REC);

Line: 1750

	/* do update */
        IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
	   iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Doing update...');

Line: 1755

	UPDATE iex_promise_details
	SET PROMISE_AMOUNT = P_PROMISE_REC.PROMISE_AMOUNT,
	AMOUNT_DUE_REMAINING = P_PROMISE_REC.PROMISE_AMOUNT,
	PROMISE_DATE = P_PROMISE_REC.PROMISE_DATE,
	BROKEN_ON_DATE = l_broken_on_date,
	STATUS = l_promise_status,
	PROMISE_PAYMENT_METHOD = P_PROMISE_REC.PROMISE_PAYMENT_METHOD,
	ACCOUNT = P_PROMISE_REC.ACCOUNT,
	PROMISE_ITEM_NUMBER = P_PROMISE_REC.PROMISE_ITEM_NUMBER,
	CAMPAIGN_SCHED_ID = P_PROMISE_REC.CAMPAIGN_SCHED_ID,
	ATTRIBUTE_CATEGORY = P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE_CATEGORY,
	ATTRIBUTE1 = P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE1,
	ATTRIBUTE2 = P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE2,
	ATTRIBUTE3 = P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE3,
	ATTRIBUTE4 = P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE4,
	ATTRIBUTE5 = P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE5,
	ATTRIBUTE6 = P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE6,
	ATTRIBUTE7 = P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE7,
	ATTRIBUTE8 = P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE8,
	ATTRIBUTE9 = P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE9,
	ATTRIBUTE10 = P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE10,
	ATTRIBUTE11 = P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE11,
	ATTRIBUTE12 = P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE12,
	ATTRIBUTE13 = P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE13,
	ATTRIBUTE14 = P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE14,
	ATTRIBUTE15 = P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE15,
	RESOURCE_ID = P_PROMISE_REC.TAKEN_BY_RESOURCE_ID,
	PROMISE_MADE_BY = P_PROMISE_REC.PROMISED_BY_PARTY_PER_ID,
	PROGRAM_ID = G_APP_ID,
	last_update_date = sysdate,
	last_updated_by = G_USER_ID,
	LAST_UPDATE_LOGIN = G_LOGIN_ID
	where promise_detail_id = P_PROMISE_REC.PROMISE_ID;

Line: 1790

	   iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Update OK');

Line: 1797

	l_SQL := 'SELECT state FROM IEX_PROMISE_DETAILS WHERE PROMISE_DETAIL_ID = :P_PROMISE_ID';

Line: 1803

	-- inserting a note
	if P_PROMISE_REC.NOTE is not null then

                IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
	      	   iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Going to build context for note...');

Line: 1822

		l_SQL := 'SELECT CUST_ACCOUNT_ID ' ||
				'FROM IEX_PROMISE_DETAILS ' ||
				'WHERE ' ||
				'PROMISE_DETAIL_ID = :P_PROMISE_ID';

Line: 1893

    	-- commit if promise updated successfully
    	IF FND_API.To_Boolean( p_commit ) THEN
        	COMMIT WORK;

Line: 1916

      ROLLBACK TO UPDATE_PROMISE_PVT;

Line: 1920

      ROLLBACK TO UPDATE_PROMISE_PVT;

Line: 1924

      ROLLBACK TO UPDATE_PROMISE_PVT;

Line: 2054

	/* do update */
IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
	iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Doing update...');

Line: 2059

	UPDATE iex_promise_details
	SET STATUS = l_promise_status,
	RESOURCE_ID = P_PROMISE_REC.TAKEN_BY_RESOURCE_ID,
	PROMISE_MADE_BY = P_PROMISE_REC.PROMISED_BY_PARTY_PER_ID,
	PROGRAM_ID = G_APP_ID,
	last_update_date = sysdate,
	last_updated_by = G_USER_ID,
	LAST_UPDATE_LOGIN = G_LOGIN_ID
	where promise_detail_id = P_PROMISE_REC.PROMISE_ID;

Line: 2070

	iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Update OK');

Line: 2076

	l_SQL := 'SELECT state FROM IEX_PROMISE_DETAILS WHERE PROMISE_DETAIL_ID = :P_PROMISE_ID';

Line: 2083

	-- inserting a note
	if P_PROMISE_REC.NOTE is not null then

IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
		iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Going to build context for note...');

Line: 2101

		l_SQL := 'SELECT CUST_ACCOUNT_ID ' ||
				'FROM IEX_PROMISE_DETAILS ' ||
				'WHERE ' ||
				'PROMISE_DETAIL_ID = :P_PROMISE_ID';

Line: 2164

    	-- commit if promise updated successfully
    	IF FND_API.To_Boolean( p_commit ) THEN
        	COMMIT WORK;

Line: 2249

	l_SQL := 'SELECT count(distinct CUST_ACCOUNT_ID) from iex_delinquencies where delinquency_id in (';

Line: 2318

		l_SQL := 'SELECT ''X'' ' ||
				'FROM IEX_LOOKUPS_V ' ||
				'WHERE ' ||
				'LOOKUP_TYPE = ''IEX_PAYMENT_TYPES'' AND LOOKUP_CODE = :P_PAYMENT_METHOD AND ' ||
				'ENABLED_FLAG = ''Y''';

Line: 2395

        	select IEX_PROMISE_DETAILS_S.NEXTVAL from dual;

Line: 2399

		select org_id
		from iex_delinquencies_all
		where delinquency_id = p_del_id;

Line: 2462

                l_SQL := 'SELECT psa.CUSTOMER_ID, psa.CUSTOMER_SITE_USE_ID, del.payment_schedule_id ' ||
                         ' FROM iex_delinquencies del, ar_payment_schedules psa ' ||
                         ' WHERE psa.payment_schedule_id = del.payment_schedule_id and psa.status = ''OP'' and ' ||
                         ' psa.AMOUNT_DUE_REMAINING > 0 and del.DELINQUENCY_ID = :P_DEL_ID';

Line: 2479

		l_SQL := 'SELECT PROMISE_DETAIL_ID, PROMISE_AMOUNT, CURRENCY_CODE, STATUS, STATE, AMOUNT_DUE_REMAINING ' ||
		         'FROM IEX_PROMISE_DETAILS ' ||
		         'WHERE DELINQUENCY_ID = :P_DEL_ID AND STATUS in (''COLLECTABLE'', ''PENDING'') ' ||
			 'UNION ' ||
			 'SELECT PROMISE_DETAIL_ID, PROMISE_AMOUNT, CURRENCY_CODE, STATUS, STATE, AMOUNT_DUE_REMAINING ' ||
		         'FROM IEX_PROMISE_DETAILS ' ||
		         'WHERE CUST_ACCOUNT_ID = :P_CUST_ACCOUNT_ID AND ' ||
		         'DELINQUENCY_ID IS NULL AND CNSLD_INVOICE_ID IS NULL AND CONTRACT_ID IS NULL AND ' ||
		         'TRUNC(promise_date) = TRUNC(:P_PROMISE_DATE) AND ' ||
		         'STATUS in (''COLLECTABLE'', ''PENDING'')';

Line: 2521

			UPDATE iex_promise_details
			SET STATUS = 'CANCELLED',
			RESOURCE_ID = P_MASS_PROMISE_REC.TAKEN_BY_RESOURCE_ID,
			PROMISE_MADE_BY = P_MASS_PROMISE_REC.PROMISED_BY_PARTY_PER_ID,
			PROGRAM_ID = G_APP_ID,
			last_update_date = sysdate,
			last_updated_by = G_USER_ID,
			LAST_UPDATE_LOGIN = G_LOGIN_ID
			where
			PROMISE_DETAIL_ID = l_promise_id;

Line: 2537

		-- fixed a perf bug 4930381  l_SQL := 'SELECT CUSTOMER_ID, AMOUNT_DUE_REMAINING, INVOICE_CURRENCY_CODE, CUSTOMER_SITE_USE_ID FROM iex_pay_invoices_v WHERE DELINQUENCY_ID = :P_DEL_ID';

Line: 2538

                l_SQL := 'SELECT psa.CUSTOMER_ID, psa.AMOUNT_DUE_REMAINING, psa.INVOICE_CURRENCY_CODE, psa.CUSTOMER_SITE_USE_ID '||
                         '  FROM iex_delinquencies del, ar_payment_schedules psa ' ||
                         '  WHERE psa.payment_schedule_id = del.payment_schedule_id and psa.status = ''OP'' and ' ||
                         '  psa.AMOUNT_DUE_REMAINING > 0 and del.DELINQUENCY_ID = :P_DEL_ID';

Line: 2566

		iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Inserting new promise...');

Line: 2568

    		INSERT INTO IEX_PROMISE_DETAILS
		(
			PROMISE_DETAIL_ID,
			OBJECT_VERSION_NUMBER,
			PROGRAM_ID,
			LAST_UPDATE_DATE,
			LAST_UPDATED_BY,
			LAST_UPDATE_LOGIN,
			CREATION_DATE,
			CREATED_BY,
			PROMISE_DATE,
			PROMISE_AMOUNT,
			PROMISE_PAYMENT_METHOD,
			STATUS,
			ACCOUNT,
			PROMISE_ITEM_NUMBER,
			CURRENCY_CODE,
			CAMPAIGN_SCHED_ID,
			DELINQUENCY_ID,
			RESOURCE_ID,
			PROMISE_MADE_BY,
			CUST_ACCOUNT_ID,
			ATTRIBUTE_CATEGORY,
			ATTRIBUTE1,
			ATTRIBUTE2,
			ATTRIBUTE3,
			ATTRIBUTE4,
			ATTRIBUTE5,
			ATTRIBUTE6,
			ATTRIBUTE7,
			ATTRIBUTE8,
			ATTRIBUTE9,
			ATTRIBUTE10,
			ATTRIBUTE11,
			ATTRIBUTE12,
			ATTRIBUTE13,
			ATTRIBUTE14,
			ATTRIBUTE15,
			CNSLD_INVOICE_ID,
			CONTRACT_ID,
			BROKEN_ON_DATE,
			AMOUNT_DUE_REMAINING,
			STATE,
			ORG_ID
		)
		VALUES
		(
			l_promise_id,
			1.0,
			G_APP_ID,
			sysdate,
			G_USER_ID,
			G_LOGIN_ID,
			sysdate,
			G_USER_ID,
			P_MASS_PROMISE_REC.PROMISE_DATE,
			l_remaining_amount,
			P_MASS_PROMISE_REC.PROMISE_PAYMENT_METHOD,
			l_status,
			P_MASS_PROMISE_REC.ACCOUNT,
			P_MASS_PROMISE_REC.PROMISE_ITEM_NUMBER,
			l_currency,
			P_MASS_PROMISE_REC.CAMPAIGN_SCHED_ID,
			P_MASS_IDS_TBL(i),
			P_MASS_PROMISE_REC.TAKEN_BY_RESOURCE_ID,
			P_MASS_PROMISE_REC.PROMISED_BY_PARTY_PER_ID,
			l_cust_account_id,
			P_MASS_PROMISE_REC.ATTRIBUTES.ATTRIBUTE_CATEGORY,
			P_MASS_PROMISE_REC.ATTRIBUTES.ATTRIBUTE1,
			P_MASS_PROMISE_REC.ATTRIBUTES.ATTRIBUTE2,
			P_MASS_PROMISE_REC.ATTRIBUTES.ATTRIBUTE3,
			P_MASS_PROMISE_REC.ATTRIBUTES.ATTRIBUTE4,
			P_MASS_PROMISE_REC.ATTRIBUTES.ATTRIBUTE5,
			P_MASS_PROMISE_REC.ATTRIBUTES.ATTRIBUTE6,
			P_MASS_PROMISE_REC.ATTRIBUTES.ATTRIBUTE7,
			P_MASS_PROMISE_REC.ATTRIBUTES.ATTRIBUTE8,
			P_MASS_PROMISE_REC.ATTRIBUTES.ATTRIBUTE9,
			P_MASS_PROMISE_REC.ATTRIBUTES.ATTRIBUTE10,
			P_MASS_PROMISE_REC.ATTRIBUTES.ATTRIBUTE11,
			P_MASS_PROMISE_REC.ATTRIBUTES.ATTRIBUTE12,
			P_MASS_PROMISE_REC.ATTRIBUTES.ATTRIBUTE13,
			P_MASS_PROMISE_REC.ATTRIBUTES.ATTRIBUTE14,
			P_MASS_PROMISE_REC.ATTRIBUTES.ATTRIBUTE15,
			null,
			null,
			l_broken_on_date,
			l_remaining_amount,
			'PROMISE',
			l_org_id
		);

Line: 2663

		iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Insert OK');

Line: 2684

	-- inserting a note
	if P_MASS_PROMISE_REC.NOTE is not null and l_note_type is not null then

IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
		iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Going to build context for note...');

Line: 2797

    	-- commit if promise updated successfully
    	IF FND_API.To_Boolean( p_commit ) THEN
        	COMMIT WORK;