APPS.AS_ATA_NEW_PUB SQL Statements

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

Line: 31

G_DEL_CHNG               VARCHAR2(50) := 'DELETE CHANGED ENTITY';

Line: 55

PROCEDURE DELETE_CHANGED_ENTITY(
 p_entity  IN VARCHAR2,
 x_errbuf  OUT NOCOPY VARCHAR2,
 x_retcode OUT NOCOPY VARCHAR2,
 x_return_status OUT NOCOPY VARCHAR2);

Line: 88

   SELECT 'X'
   FROM  jty_changed_terrs
   WHERE source_id = -1001
     AND tap_request_id IS NULL ;

Line: 141

       INSERT /*+ append parallel(AS_TERR_RESOURCES_TMP) */
          INTO AS_TERR_RESOURCES_TMP
        (RESOURCE_ID,
         RESOURCE_TYPE,
         PARTY_ID,
         TERR_ID)
        ( SELECT DISTINCT USERS.EMPLOYEE_ID,
                          VAL.PROFILE_OPTION_VALUE,-1,-1
          FROM  FND_PROFILE_OPTION_VALUES VAL,
                FND_PROFILE_OPTIONS OPTIONS,
                FND_USER USERS
          WHERE VAL.LEVEL_ID = 10004
          AND USERS.EMPLOYEE_ID is not null
          AND VAL.PROFILE_OPTION_VALUE is not null
          AND USERS.USER_ID = VAL.LEVEL_VALUE
          AND VAL.PROFILE_OPTION_VALUE is not null
          AND OPTIONS.PROFILE_OPTION_ID = VAL.PROFILE_OPTION_ID
          AND OPTIONS.APPLICATION_ID = VAL.APPLICATION_ID
          AND OPTIONS.PROFILE_OPTION_NAME = 'AS_DEF_CUST_ST_ROLE'
         );

Line: 301

		DELETE_CHANGED_ENTITY('ALL_SALES',
		x_errbuf  =>l_errbuf
		,x_retcode =>l_retcode
		,x_return_status =>l_return_status);

Line: 314

		DELETE_CHANGED_ENTITY('ALL_QUOTES',
		x_errbuf  =>l_errbuf
		,x_retcode =>l_retcode
		,x_return_status =>l_return_status);

Line: 326

		DELETE_CHANGED_ENTITY('ALL_PROPOSALS',
		x_errbuf  =>l_errbuf
		,x_retcode =>l_retcode
		,x_return_status =>l_return_status);

Line: 340

		DELETE_CHANGED_ENTITY('ACCOUNT',
		x_errbuf  =>l_errbuf
		,x_retcode =>l_retcode
		,x_return_status =>l_return_status);

Line: 353

		DELETE_CHANGED_ENTITY('LEAD',
		x_errbuf  =>l_errbuf
		,x_retcode =>l_retcode
		,x_return_status =>l_return_status);

Line: 366

		DELETE_CHANGED_ENTITY('OPPTY',
		x_errbuf  =>l_errbuf
		,x_retcode =>l_retcode
		,x_return_status =>l_return_status);

Line: 379

		DELETE_CHANGED_ENTITY('QUOTE',
		x_errbuf  =>l_errbuf
		,x_retcode =>l_retcode
		,x_return_status =>l_return_status);

Line: 391

		DELETE_CHANGED_ENTITY('PROPOSAL',
		x_errbuf  =>l_errbuf
		,x_retcode =>l_retcode
		,x_return_status =>l_return_status);

Line: 543

    AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || G_PREATA || 'UPDATE REQUEST ID INTO AS_CHANGED_ACCOUNTS TABLE');

Line: 544

    UPDATE AS_CHANGED_ACCOUNTS
    SET REQUEST_ID = FND_GLOBAL.Conc_Request_Id
    where REQUEST_ID IS NULL;

Line: 550

    AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || G_PREATA || 'UPDATE REQUEST ID INTO ASO_CHANGED_QUOTES TABLE');

Line: 551

    update ASO_CHANGED_QUOTES
    set CONC_REQUEST_ID = FND_GLOBAL.Conc_Request_Id
    where CONC_REQUEST_ID IS NULL;

Line: 557

    AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || G_PREATA || 'UPDATE REQUEST ID INTO ASO_CHANGED_QUOTES TABLE');

Line: 558

    update PRP_CHANGED_PROPOSALS
    set REQUEST_ID = FND_GLOBAL.Conc_Request_Id
    where REQUEST_ID IS NULL;

Line: 572

PROCEDURE DELETE_CHANGED_ENTITY(p_entity IN VARCHAR2,
				x_errbuf  OUT NOCOPY VARCHAR2,
				x_retcode OUT NOCOPY VARCHAR2,
				x_return_status OUT NOCOPY VARCHAR2)IS

	TYPE entity_id_list    is TABLE of NUMBER INDEX BY BINARY_INTEGER;

Line: 581

	   SELECT  distinct customer_id
	   FROM AS_CHANGED_ACCOUNTS_ALL CHNG
	   WHERE CHNG.lead_id IS NULL
	   AND CHNG.sales_lead_id IS NULL
	   AND EXISTS (SELECT 'X' FROM JTF_TAE_1001_ACCOUNT_NM_TRANS TRANS
	                WHERE TRANS.TRANS_OBJECT_ID = CHNG.CUSTOMER_ID);

Line: 589

	   SELECT  distinct sales_lead_id
	   FROM AS_CHANGED_ACCOUNTS_ALL CHNG
	   WHERE lead_id IS NULL
	   AND sales_lead_id IS NOT NULL
   	   AND EXISTS (SELECT 'X' FROM JTF_TAE_1001_LEAD_NM_TRANS TRANS
	                WHERE TRANS.TRANS_OBJECT_ID = CHNG.SALES_LEAD_ID);

Line: 597

	   SELECT  distinct lead_id
	   FROM AS_CHANGED_ACCOUNTS_ALL  CHNG
	   WHERE lead_id IS NOT NULL
	   AND sales_lead_id IS NULL
	   AND EXISTS (SELECT 'X' FROM JTF_TAE_1001_OPPOR_NM_TRANS TRANS
	                WHERE TRANS.TRANS_OBJECT_ID = CHNG.LEAD_ID);

Line: 605

	   SELECT  distinct proposal_id
	   FROM PRP_CHANGED_PROPOSALS  CHNG
	   WHERE EXISTS (SELECT 'X' FROM JTF_TAE_1001_PROP_NM_TRANS TRANS
	                WHERE trans.trans_object_id = chng.proposal_id);

Line: 611

	   SELECT  distinct quote_number
	   FROM ASO_CHANGED_QUOTES  CHNG
	   WHERE EXISTS (SELECT 'X' FROM JTF_TAE_1001_QUOTE_NM_TRANS TRANS
	                WHERE trans.trans_object_id = chng.quote_number);

Line: 617

	   SELECT  distinct customer_id
	   FROM AS_CHANGED_ACCOUNTS_ALL;

Line: 621

	   SELECT  distinct quote_number
	   FROM ASO_CHANGED_QUOTES;

Line: 625

	   SELECT  distinct proposal_id
	   FROM PRP_CHANGED_PROPOSALS;

Line: 652

			AS_GAR.LOG(G_ENTITY || 'DELETE FROM CHANGED ENTITY::' || p_entity || ': LOOPCOUNT :- ' ||l_loop_count);

Line: 698

				AS_GAR.LOG(G_ENTITY || 'DELETE FROM CHANGED ENTITY::' || p_entity || AS_GAR.G_N_ROWS_PROCESSED ||
								 p_entity_id.FIRST || '-' ||
								 p_entity_id.LAST);

Line: 710

									DELETE FROM AS_CHANGED_ACCOUNTS_ALL ACC
									WHERE ACC.customer_id=p_entity_id(i)
									AND ACC.lead_id IS NULL
									AND ACC.sales_lead_id IS NULL;

Line: 717

									DELETE FROM AS_CHANGED_ACCOUNTS_ALL ACC
									WHERE ACC.sales_lead_id=p_entity_id(i)
									AND ACC.lead_id IS NULL;

Line: 723

									DELETE FROM AS_CHANGED_ACCOUNTS_ALL ACC
									WHERE ACC.lead_id=p_entity_id(i)
									AND ACC.change_type = 'OPPORTUNITY';

Line: 730

									DELETE FROM ASO_CHANGED_QUOTES ACC
									WHERE ACC.quote_number=p_entity_id(i);

Line: 735

									DELETE FROM PRP_CHANGED_PROPOSALS ACC
									WHERE ACC.proposal_id=p_entity_id(i);

Line: 740

									DELETE FROM AS_CHANGED_ACCOUNTS_ALL ACC
									WHERE ACC.customer_id=p_entity_id(i);

Line: 745

									DELETE FROM ASO_CHANGED_QUOTES ACC
									WHERE ACC.quote_number=p_entity_id(i);

Line: 750

									DELETE FROM PRP_CHANGED_PROPOSALS ACC
									WHERE ACC.proposal_id=p_entity_id(i);

Line: 761

									AS_GAR.LOG('processing Individual Delete');

Line: 767

													DELETE FROM AS_CHANGED_ACCOUNTS_ALL ACC
													WHERE ACC.customer_id=p_entity_id(i)
													AND ACC.lead_id IS NULL
													AND ACC.sales_lead_id IS NULL;

Line: 772

													DELETE FROM AS_CHANGED_ACCOUNTS_ALL ACC
													WHERE ACC.sales_lead_id=p_entity_id(i)
													AND ACC.lead_id IS NULL;

Line: 776

													DELETE FROM AS_CHANGED_ACCOUNTS_ALL ACC
													WHERE ACC.lead_id=p_entity_id(i)
													AND ACC.change_type = 'OPPORTUNITY';

Line: 781

													DELETE FROM ASO_CHANGED_QUOTES ACC
													WHERE ACC.quote_number=p_entity_id(i);

Line: 784

													DELETE FROM PRP_CHANGED_PROPOSALS ACC
													WHERE ACC.proposal_id=p_entity_id(i);

Line: 787

													DELETE FROM AS_CHANGED_ACCOUNTS_ALL ACC
													WHERE ACC.customer_id=p_entity_id(i);

Line: 790

													DELETE FROM ASO_CHANGED_QUOTES ACC
													WHERE ACC.quote_number=p_entity_id(i);

Line: 793

													DELETE FROM PRP_CHANGED_PROPOSALS ACC
													WHERE ACC.proposal_id=p_entity_id(i);

Line: 799

													AS_GAR.LOG(G_ENTITY || 'DELETE FROM CHANGED ENTITY::' || p_entity || AS_GAR.G_IND_DEL || AS_GAR.G_GENERAL_EXCEPTION);

Line: 807

								AS_GAR.LOG_EXCEPTION(G_ENTITY || 'DELETE FROM CHANGED ENTITY::' || p_entity || AS_GAR.G_GENERAL_EXCEPTION, SQLERRM, TO_CHAR(SQLCODE));

Line: 821

						AS_GAR.LOG(G_ENTITY || 'DELETE FROM CHANGED ENTITY::' || p_entity || AS_GAR.G_N_ROWS_PROCESSED || l_first || '-' || l_last);

Line: 830

			AS_GAR.LOG(G_ENTITY ||'DELETE FROM CHANGED ENTITY::' || AS_GAR.G_END);

Line: 834

				AS_GAR.LOG_EXCEPTION(G_ENTITY || 'DELETE FROM CHANGED ENTITY::' || p_entity || AS_GAR.G_GENERAL_EXCEPTION, SQLERRM, TO_CHAR(SQLCODE));

Line: 859

      AS_GAR.LOG_EXCEPTION(G_ENTITY || 'DELETE FROM CHANGED ENTITY::' || p_entity , SQLERRM, TO_CHAR(SQLCODE));

Line: 861

END DELETE_CHANGED_ENTITY;

Line: 871

    AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || G_PREATA || 'UPDATE REQUEST ID INTO AS_CHANGED_ACCOUNTS TABLE');

Line: 872

    UPDATE AS_CHANGED_ACCOUNTS
    SET REQUEST_ID = NULL
    WHERE REQUEST_ID = FND_GLOBAL.Conc_Request_Id;

Line: 878

    AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || G_PREATA || 'UPDATE REQUEST ID INTO ASO_CHANGED_QUOTES TABLE');

Line: 879

    update ASO_CHANGED_QUOTES
    set CONC_REQUEST_ID = NULL
    where CONC_REQUEST_ID =FND_GLOBAL.Conc_Request_Id;

Line: 885

    AS_GAR.LOG(G_ENTITY || AS_GAR.G_PROCESS || G_PREATA || 'UPDATE REQUEST ID INTO ASO_CHANGED_QUOTES TABLE');

Line: 886

    update PRP_CHANGED_PROPOSALS
    set REQUEST_ID = NULL
    where REQUEST_ID = FND_GLOBAL.Conc_Request_Id;