APPS.FII_AR_SALES_CREDITS_C SQL Statements

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

Line: 109

FUNCTION POPULATE_SC_INSERT RETURN NUMBER IS
  l_row_count    NUMBER;

Line: 119

	-- Populate FII_AR_SALES_CREDITS with AR Sales Credits that have been inserted since the last Run Date
 	insert into fii_ar_sales_credits (
		SALESCREDIT_PK, INVOICE_LINE_ID,
		SALESREP_ID, SALESGROUP_ID,
		REVENUE_PERCENT_SPLIT,
		CREATED_BY, LAST_UPDATED_BY,
		LAST_UPDATE_LOGIN,
		CREATION_DATE, LAST_UPDATE_DATE)
	select 	CUST_TRX_LINE_SALESREP_ID, CUSTOMER_TRX_LINE_ID,
		SALESREP_ID, revenue_salesgroup_id,
		REVENUE_PERCENT_SPLIT,
		g_fii_user_id, g_fii_user_id,
		g_fii_login_id,
		SYSDATE, SYSDATE
	from 	ra_cust_trx_line_salesreps_all
	where	cust_trx_line_salesrep_id > g_max_salescredit_pk
	and	customer_trx_line_id is not null
	and	nvl(revenue_percent_split, 0) <> 0;

Line: 142

 		fii_util.put_line('Inserted new AR Sales Credits');

Line: 156

  Error in Procedure: POPULATE_SC_INSERT
           Message: '||sqlerrm;

Line: 160

END POPULATE_SC_INSERT;

Line: 172

   		fii_util.put_line('Processing updates and deletes');

Line: 177

	-- Mark rows updated and deleted in the AR application for processing
	UPDATE FII_AR_SALES_CREDITS_D_T
	SET STATUS_FLAG = 'P';

Line: 182

   		fii_util.put_line('Marked '||SQL%ROWCOUNT||' rows in FII_AR_SALES_CREDITS_D_T as updated / deleted in AR');

Line: 189

	-- Merge the updated Sales Credits into FII_AR_SALES_CREDITS using FII_AR_SALESCREDIT_D_T.SALESCREDIT_PK to join with RA_CUST_TRX_LINE_SALESREPS_ALL
	-- (for existing rows, delete them if the new revenue percent is 0 else update them; for new rows, insert them if the revenue percent is non-0)

Line: 192

	-- For non-0 rows, update existing rows and insert new rows
 	MERGE INTO FII_AR_SALES_CREDITS f
          USING (SELECT sr.* FROM  FII_AR_SALES_CREDITS_D_T del, RA_CUST_TRX_LINE_SALESREPS_ALL sr
                  WHERE nvl(revenue_percent_split, 0) <> 0
		  AND 	del.salescredit_pk = sr.cust_trx_line_salesrep_id
		  AND	del.dml_type = 'U'
		  AND	sr.customer_trx_line_id is not null) stg
          ON (  stg.cust_trx_line_salesrep_id = f.salescredit_pk)
   	WHEN MATCHED THEN
          UPDATE SET
		f.INVOICE_LINE_ID = stg.CUSTOMER_TRX_LINE_ID,
		f.SALESREP_ID = stg.SALESREP_ID,
		f.SALESGROUP_ID = stg.revenue_salesgroup_id,
		f.REVENUE_PERCENT_SPLIT = stg.REVENUE_PERCENT_SPLIT,
                f.LAST_UPDATED_BY =  g_fii_user_id,
                f.LAST_UPDATE_LOGIN = g_fii_login_id,
                f.LAST_UPDATE_DATE = SYSDATE
   	WHEN NOT MATCHED THEN
          INSERT (
		f.SALESCREDIT_PK, f.INVOICE_LINE_ID,
		f.SALESREP_ID, f.SALESGROUP_ID,
		f.REVENUE_PERCENT_SPLIT,
		f.CREATED_BY, f.LAST_UPDATED_BY,
		f.LAST_UPDATE_LOGIN,
		f.CREATION_DATE, f.LAST_UPDATE_DATE)
          VALUES (
		stg.CUST_TRX_LINE_SALESREP_ID, stg.CUSTOMER_TRX_LINE_ID,
		stg.SALESREP_ID, stg.revenue_salesgroup_id,
		stg.REVENUE_PERCENT_SPLIT,
		g_fii_user_id, g_fii_user_id,
		g_fii_login_id,
		SYSDATE, SYSDATE);

Line: 226

   		fii_util.put_line('Processed '||SQL%ROWCOUNT||' rows updated in AR for updation / insertion');

Line: 233

	-- For rows updated in AR to have revenue_percent=0, delete them from FII
	DELETE FROM FII_AR_SALES_CREDITS
	  WHERE SALESCREDIT_PK in
		(select SALESCREDIT_PK from FII_AR_SALES_CREDITS_D_T del, RA_CUST_TRX_LINE_SALESREPS_ALL sr
		 where nvl(REVENUE_PERCENT_SPLIT, 0) = 0
		 and   del.salescredit_pk = sr.cust_trx_line_salesrep_id
		 and   del.dml_type = 'U');

Line: 242

   		fii_util.put_line('Processed '||SQL%ROWCOUNT||' rows for deletion due to 0 / null revenue percent updates');

Line: 249

	-- Process / Delete rows deleted from the AR application
	DELETE FROM FII_AR_SALES_CREDITS
	  WHERE SALESCREDIT_PK in
		(select SALESCREDIT_PK from FII_AR_SALES_CREDITS_D_T
		 where STATUS_FLAG = 'P'
		 and DML_TYPE = 'D');

Line: 264

	-- Delete deletions that have been processed
	DELETE FROM FII_AR_SALES_CREDITS_D_T
	 where STATUS_FLAG = 'P';

Line: 269

   		fii_util.put_line('Deleted '||SQL%ROWCOUNT||' rows in FII_AR_SALES_CREDITS_D_T as processed');

Line: 294

          fii_util.put_line('Inserting dummy records for the deleted invoice lines');

Line: 298

	-- Insert dummy records based on the 0 revenue percent rows deleted in the merge phase (using the Snapshot Log for FII_AR_SALES_CREDITS)
	insert into fii_ar_sales_credits (
		SALESCREDIT_PK, INVOICE_LINE_ID,
		SALESREP_ID, SALESGROUP_ID,
		REVENUE_PERCENT_SPLIT,
		CREATED_BY, LAST_UPDATED_BY,
		LAST_UPDATE_LOGIN,
		CREATION_DATE, LAST_UPDATE_DATE)
	select 	distinct -INVOICE_LINE_ID, INVOICE_LINE_ID,
		g_default_salesrep, g_default_salesgroup,
		100,
		g_fii_user_id, g_fii_user_id,
		g_fii_login_id,
		SYSDATE, SYSDATE
	from 	mlog$_fii_ar_sales_credits sc_log
	--where	invoice_line_id not in (select distinct invoice_line_id from fii_ar_sales_credits)
	where	not exists (select 'X' from fii_ar_sales_credits where invoice_line_id = sc_log.invoice_line_id)
	and	dmltype$$ = 'D';

Line: 326

	-- Delete dummy records based on the Snapshot Log for FII_AR_SALES_CREDITS
	delete from fii_ar_sales_credits
	where	salescredit_pk in
	(select	-invoice_line_id
	from 	mlog$_fii_ar_sales_credits
	where	dmltype$$ = 'I'
	and	salescredit_pk > 0);

Line: 339

         fii_util.put_line('Inserting dummy records for new posted invoice lines without sales credits');

Line: 343

	-- Insert dummy records from the Snapshot Log for FII_AR_REVENUE_B
	insert into fii_ar_sales_credits (
		SALESCREDIT_PK, INVOICE_LINE_ID,
		SALESREP_ID, SALESGROUP_ID,
		REVENUE_PERCENT_SPLIT,
		CREATED_BY, LAST_UPDATED_BY,
		LAST_UPDATE_LOGIN,
		CREATION_DATE, LAST_UPDATE_DATE)
	select 	distinct -INVOICE_LINE_ID, INVOICE_LINE_ID,
		g_default_salesrep, g_default_salesgroup,
		100,
		g_fii_user_id, g_fii_user_id,
		g_fii_login_id,
		SYSDATE, SYSDATE
	from 	mlog$_fii_ar_revenue_b rev_log
	--where	invoice_line_id not in (select distinct invoice_line_id from fii_ar_sales_credits)
	where	not exists (select 'X' from fii_ar_sales_credits where invoice_line_id = rev_log.invoice_line_id)
	and	dmltype$$ = 'I';

Line: 392

          fii_util.put_line('start of first insert');

Line: 395

	-- Insert a dummy record into FII_AR_SALES_CREDITS for all Adjustments
	insert  into fii_ar_sales_CREDITS F (
		SALESCREDIT_PK, INVOICE_LINE_ID,
		SALESREP_ID, SALESGROUP_ID,
		REVENUE_PERCENT_SPLIT,
		CREATED_BY, LAST_UPDATED_BY,
		LAST_UPDATE_LOGIN,
		CREATION_DATE, LAST_UPDATE_DATE)
	values	(0, 0, g_default_salesrep, g_default_salesgroup, 100,
		g_fii_user_id, g_fii_user_id,
		g_fii_login_id,
		SYSDATE, SYSDATE);

Line: 411

          fii_util.put_line('start of second insert');

Line: 415

	insert /*+  APPEND PARALLEL(F) */ into fii_ar_sales_CREDITS F (
		SALESCREDIT_PK, INVOICE_LINE_ID,
		SALESREP_ID, SALESGROUP_ID,
		REVENUE_PERCENT_SPLIT,
		CREATED_BY, LAST_UPDATED_BY,
		LAST_UPDATE_LOGIN,
		CREATION_DATE, LAST_UPDATE_DATE)
	select 	/*+ PARALLEL(S) */ CUST_TRX_LINE_SALESREP_ID, CUSTOMER_TRX_LINE_ID,
		SALESREP_ID, revenue_salesgroup_id,
		REVENUE_PERCENT_SPLIT,
		g_fii_user_id, g_fii_user_id,
		g_fii_login_id,
		SYSDATE, SYSDATE
	from 	ra_cust_trx_line_salesreps_all S
	where	revenue_percent_split <> 0
	and	customer_trx_line_id is not null;

Line: 444

          fii_util.put_line('start of third insert');

Line: 448

     insert /*+ APPEND PARALLEL(F) */ into fii_ar_sales_CREDITS F (
         SALESCREDIT_PK, INVOICE_LINE_ID,
         SALESREP_ID, SALESGROUP_ID,
         REVENUE_PERCENT_SPLIT,
         CREATED_BY, LAST_UPDATED_BY,
         LAST_UPDATE_LOGIN,
         CREATION_DATE, LAST_UPDATE_DATE)
     select     /*+ parallel(rev) */ distinct -INVOICE_LINE_ID, INVOICE_LINE_ID,
         g_default_salesrep, g_default_salesgroup,
         100,
         g_fii_user_id, g_fii_user_id,
         g_fii_login_id,
         SYSDATE, SYSDATE
     from     fii_ar_revenue_b rev
     where    transaction_class <> 'ADJ'
         and invoice_line_id is not null
         and invoice_line_id not in (
        select /*+ hash_aj parallel_index(b) index_ffs(b) */
         invoice_line_id
          from fii_ar_sales_CREDITS b
         where invoice_line_id is not null);

Line: 584

       	BIS_COLLECTION_UTILITIES.DELETELOGFOROBJECT('FII_AR_SALES_CREDITS_I');

Line: 585

       	BIS_COLLECTION_UTILITIES.DELETELOGFOROBJECT('FII_AR_SALES_CREDITS_L');

Line: 604

	select max(salescredit_pk) into g_max_salescredit_pk
	from fii_ar_sales_credits;

Line: 616

	-- Insert Sales Credits records created in AR after the last run
      	l_count := POPULATE_SC_INSERT;

Line: 620

       	  fii_util.put_line('Inserted ' || l_count || ' new rows created after the last run');

Line: 623

    	  fii_util.put_line('INCREMENTAL LOAD: processing AR Sales Credits updates and deletes');

Line: 692

FUNCTION delete_salescredit_sub (
  		p_subscription_guid IN RAW,
  		p_event IN OUT NOCOPY WF_EVENT_T)
  		RETURN VARCHAR2 IS
  l_key  VARCHAR2(240) := p_event.GetEventKey();

Line: 701

  insert into fii_ar_sales_credits_d_t (
	SALESCREDIT_PK,
	DML_TYPE,
	STATUS_FLAG,
	LAST_UPDATE_DATE,
	LAST_UPDATED_BY,
	CREATION_DATE,
	CREATED_BY,
	LAST_UPDATE_LOGIN
  )
  values( to_number(l_key), 'D', null, null, null, null, null, null );

Line: 717

END delete_salescredit_sub;

Line: 719

FUNCTION update_salescredit_sub (
  		p_subscription_guid IN RAW,
  		p_event IN OUT NOCOPY WF_EVENT_T)
  		RETURN VARCHAR2 IS
  l_key  VARCHAR2(240) := p_event.GetEventKey();

Line: 731

    select 'Y' into l_exists
    from fii_ar_sales_credits_d_t
    where salescredit_pk = to_number(l_key)
    and dml_type = 'U';

Line: 738

      insert into fii_ar_sales_credits_d_t (
	SALESCREDIT_PK,
	DML_TYPE,
	STATUS_FLAG,
	LAST_UPDATE_DATE,
	LAST_UPDATED_BY,
	CREATION_DATE,
	CREATED_BY,
	LAST_UPDATE_LOGIN
      )
      values( to_number(l_key), 'U', null, null, null, null, null, null );

Line: 759

END update_salescredit_sub;