DBA Data[Home] [Help]

APPS.IEX_PROMISES_PUB SQL Statements

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

Line: 119

	-- filter passed promises; we need only BROKEN_PROMISE promises for update
Line: 123

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

            		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: 199

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

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

		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: 276

        select strategy_level
	from iex_strategies
	where party_id= p_party_id
	and status_code in ('OPEN', 'ONHOLD');
Line: 317

	/*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: 331

	select decode(preference_value, 'CUSTOMER', 10, 'ACCOUNT', 20, 'BILL_TO', 30, 'DELINQUENCY', 40,  40)
	into l_DefaultStrategyLevel
	from iex_app_preferences_b
	where  preference_name = 'COLLECTIONS STRATEGY LEVEL'
	and enabled_flag = 'Y'
	and org_id is null;  -- changed for bug 8708271 pnaveenk
Line: 491

		select IEX_PTP_WF_S.NEXTVAL from dual;
Line: 593

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

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

		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: 770

        	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: 836

        	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: 868

		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: 915

        	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: 948

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

        	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: 1028

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

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

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: 1094

        	select IEX_PROMISE_DETAILS_S.NEXTVAL from dual;
Line: 1097

		select org_id
		from iex_delinquencies_all
		where delinquency_id = p_del_id;
Line: 1106

    	SAVEPOINT INSERT_PROMISE_PVT;
Line: 1131

	VALIDATE_INSERT_INPUT(P_PROMISE_REC);
Line: 1173

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

    	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: 1274

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

	      iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Calling update_del_stage_level');
Line: 1282

    update_del_stage_level (
		p_promise_id		=> l_promise_id,
		X_RETURN_STATUS		=> l_return_status,
		X_MSG_COUNT             => l_msg_count,
		X_MSG_DATA	    	=> l_msg_data);
Line: 1289

	      iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': After call to update_del_stage_level');
Line: 1295

		     iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': update_del_stage_level failed');
Line: 1301

	-- 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: 1406

      ROLLBACK TO INSERT_PROMISE_PVT;
Line: 1410

      ROLLBACK TO INSERT_PROMISE_PVT;
Line: 1414

      ROLLBACK TO INSERT_PROMISE_PVT;
Line: 1424

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

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

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

	l_procedure := 'VALIDATE_UPDATE_INPUT';
Line: 1469

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

	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: 1642

        l_SQL := l_str_select || l_where_clause || l_str_cond;
Line: 1645

       	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: 1680

		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: 1708

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: 1744

    	SAVEPOINT UPDATE_PROMISE_PVT;
Line: 1768

	VALIDATE_UPDATE_INPUT(P_PROMISE_REC);
Line: 1789

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

	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: 1829

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

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

	      iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Calling update_del_stage_level');
Line: 1846

  update_del_stage_level (
		p_promise_id		=> P_PROMISE_REC.PROMISE_ID,
		X_RETURN_STATUS		=> l_return_status,
		X_MSG_COUNT             => l_msg_count,
		X_MSG_DATA	    	=> l_msg_data);
Line: 1853

	      iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': After call to update_del_stage_level');
Line: 1859

		     iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': update_del_stage_level failed');
Line: 1865

	-- 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: 1884

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

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

      ROLLBACK TO UPDATE_PROMISE_PVT;
Line: 1982

      ROLLBACK TO UPDATE_PROMISE_PVT;
Line: 1986

      ROLLBACK TO UPDATE_PROMISE_PVT;
Line: 2117

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

	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,
	ATTRIBUTE_CATEGORY = P_PROMISE_REC.ATTRIBUTES.ATTRIBUTE_CATEGORY, -- start added by snuthala for bug 10373547 12/16/2010
	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    -- end added by snuthala for bug 10373547 12/16/2010
	where promise_detail_id = P_PROMISE_REC.PROMISE_ID;
Line: 2149

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

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

	      iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Calling update_del_stage_level');
Line: 2165

   update_del_stage_level (
		p_promise_id		=> P_PROMISE_REC.PROMISE_ID,
		X_RETURN_STATUS		=> l_return_status,
		X_MSG_COUNT             => l_msg_count,
		X_MSG_DATA	    	=> l_msg_data);
Line: 2172

	      iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': After call to update_del_stage_level');
Line: 2178

		     iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': update_del_stage_level failed');
Line: 2184

	-- 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: 2202

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

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

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

		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: 2496

        	select IEX_PROMISE_DETAILS_S.NEXTVAL from dual;
Line: 2500

		select org_id
		from iex_delinquencies_all
		where delinquency_id = p_del_id;
Line: 2563

                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: 2580

		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: 2622

			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: 2639

			      iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Calling update_del_stage_level');
Line: 2641

		 update_del_stage_level (
				p_promise_id		=> l_promise_id,
				X_RETURN_STATUS		=> l_return_status,
				X_MSG_COUNT             => l_msg_count,
				X_MSG_DATA	    	=> l_msg_data);
Line: 2647

			      iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': After call to update_del_stage_level');
Line: 2654

				     iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': update_del_stage_level failed');
Line: 2663

		-- 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: 2664

                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: 2692

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

    		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: 2789

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

		      iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Calling update_del_stage_level');
Line: 2796

	  update_del_stage_level (
			p_promise_id		=> l_promise_id,
			X_RETURN_STATUS		=> l_return_status,
			X_MSG_COUNT             => l_msg_count,
			X_MSG_DATA	    	=> l_msg_data);
Line: 2802

		      iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': After call to update_del_stage_level');
Line: 2809

			     iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': update_del_stage_level failed');
Line: 2833

	-- 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: 2946

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

Procedure update_del_stage_level (
		p_promise_id		IN	       NUMBER,
		X_RETURN_STATUS		OUT NOCOPY     VARCHAR2,
		X_MSG_COUNT             OUT NOCOPY     NUMBER,
		X_MSG_DATA	    	OUT NOCOPY     VARCHAR2)
is
	l_api_name			CONSTANT VARCHAR2(30) := 'update_del_stage_level';
Line: 3004

    	SAVEPOINT UPDATE_DEL_STAGE_PVT;
Line: 3012

	   select delinquency_id into l_promised_delinquency_id
	   from iex_promise_details
	   where promise_detail_id = p_promise_id;
Line: 3016

	   SELECT  sum(promise_amount) into l_total_already_pro_amt
	   from iex_promise_details where delinquency_id = l_promised_delinquency_id
	   and status = 'COLLECTABLE'
	   and state = 'PROMISE';
Line: 3028

	    SELECT  amount_due_remaining into l_amt_due_remaining
	   from ar_payment_schedules pay,
		iex_delinquencies del
	   where
	   del.payment_schedule_id = pay.payment_schedule_id
	   and del.delinquency_id = l_promised_delinquency_id;
Line: 3042

		update iex_delinquencies_all
		set staged_dunning_level = 0
		where delinquency_id = l_promised_delinquency_id;
Line: 3047

			iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || 'Updated the staged dunning level to 0 for delinquency id: ' || l_promised_delinquency_id);
Line: 3052

		update iex_delinquencies_all
		set staged_dunning_level = l_stage_number
		where delinquency_id = l_promised_delinquency_id
		and staged_dunning_level=0; --added for bug 14318148
Line: 3057

			iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || 'Updated the staged dunning level to ' || l_stage_number || ' for delinquency id: ' || l_promised_delinquency_id);
Line: 3071

      ROLLBACK TO UPDATE_DEL_STAGE_PVT;
Line: 3075

      ROLLBACK TO UPDATE_DEL_STAGE_PVT;
Line: 3079

      ROLLBACK TO UPDATE_DEL_STAGE_PVT;
Line: 3086

end update_del_stage_level;