APPS.FII_AR_SALES_CREDITS_C dependencies on FII_AR_SALES_CREDITS_D_T

Line 178: UPDATE FII_AR_SALES_CREDITS_D_T

174: fii_util.put_line('');
175: end if;
176:
177: -- Mark rows updated and deleted in the AR application for processing
178: UPDATE FII_AR_SALES_CREDITS_D_T
179: SET STATUS_FLAG = 'P';
180:
181: if g_debug_flag = 'Y' then
182: fii_util.put_line('Marked '||SQL%ROWCOUNT||' rows in FII_AR_SALES_CREDITS_D_T as updated / deleted in AR');

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

178: UPDATE FII_AR_SALES_CREDITS_D_T
179: SET STATUS_FLAG = 'P';
180:
181: if g_debug_flag = 'Y' then
182: fii_util.put_line('Marked '||SQL%ROWCOUNT||' rows in FII_AR_SALES_CREDITS_D_T as updated / deleted in AR');
183: fii_util.stop_timer;
184: fii_util.print_timer('Duration');
185: fii_util.start_timer;
186: fii_util.put_line('');

Line 194: USING (SELECT sr.* FROM FII_AR_SALES_CREDITS_D_T del, RA_CUST_TRX_LINE_SALESREPS_ALL sr

190: -- (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)
191:
192: -- For non-0 rows, update existing rows and insert new rows
193: MERGE INTO FII_AR_SALES_CREDITS f
194: USING (SELECT sr.* FROM FII_AR_SALES_CREDITS_D_T del, RA_CUST_TRX_LINE_SALESREPS_ALL sr
195: WHERE nvl(revenue_percent_split, 0) <> 0
196: AND del.salescredit_pk = sr.cust_trx_line_salesrep_id
197: AND del.dml_type = 'U'
198: AND sr.customer_trx_line_id is not null) stg

Line 236: (select SALESCREDIT_PK from FII_AR_SALES_CREDITS_D_T del, RA_CUST_TRX_LINE_SALESREPS_ALL sr

232:
233: -- For rows updated in AR to have revenue_percent=0, delete them from FII
234: DELETE FROM FII_AR_SALES_CREDITS
235: WHERE SALESCREDIT_PK in
236: (select SALESCREDIT_PK from FII_AR_SALES_CREDITS_D_T del, RA_CUST_TRX_LINE_SALESREPS_ALL sr
237: where nvl(REVENUE_PERCENT_SPLIT, 0) = 0
238: and del.salescredit_pk = sr.cust_trx_line_salesrep_id
239: and del.dml_type = 'U');
240:

Line 252: (select SALESCREDIT_PK from FII_AR_SALES_CREDITS_D_T

248:
249: -- Process / Delete rows deleted from the AR application
250: DELETE FROM FII_AR_SALES_CREDITS
251: WHERE SALESCREDIT_PK in
252: (select SALESCREDIT_PK from FII_AR_SALES_CREDITS_D_T
253: where STATUS_FLAG = 'P'
254: and DML_TYPE = 'D');
255:
256: if g_debug_flag = 'Y' then

Line 265: DELETE FROM FII_AR_SALES_CREDITS_D_T

261: fii_util.put_line('');
262: end if;
263:
264: -- Delete deletions that have been processed
265: DELETE FROM FII_AR_SALES_CREDITS_D_T
266: where STATUS_FLAG = 'P';
267:
268: if g_debug_flag = 'Y' then
269: fii_util.put_line('Deleted '||SQL%ROWCOUNT||' rows in FII_AR_SALES_CREDITS_D_T as processed');

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

265: DELETE FROM FII_AR_SALES_CREDITS_D_T
266: where STATUS_FLAG = 'P';
267:
268: if g_debug_flag = 'Y' then
269: fii_util.put_line('Deleted '||SQL%ROWCOUNT||' rows in FII_AR_SALES_CREDITS_D_T as processed');
270: fii_util.stop_timer;
271: fii_util.print_timer('Duration');
272: fii_util.put_line(' ');
273: end if;

Line 574: FII_UTIL.TRUNCATE_TABLE(p_table_name => 'FII_AR_SALES_CREDITS_D_T', p_retcode => l_retcode);

570: IF g_debug_flag = 'Y' then
571: fii_util.put_line('Running Initial Load, truncate staging and base summary table.');
572: END IF;
573:
574: FII_UTIL.TRUNCATE_TABLE(p_table_name => 'FII_AR_SALES_CREDITS_D_T', p_retcode => l_retcode);
575: if l_retcode = -1 then
576: g_retcode := -2;
577: raise g_truncate_failure;
578: end if;

Line 701: insert into fii_ar_sales_credits_d_t (

697: l_pos NUMBER;
698: BEGIN
699: l_pos := instr(l_key, '_');
700: l_key := substr(l_key, 1, l_pos - 1);
701: insert into fii_ar_sales_credits_d_t (
702: SALESCREDIT_PK,
703: DML_TYPE,
704: STATUS_FLAG,
705: LAST_UPDATE_DATE,

Line 732: from fii_ar_sales_credits_d_t

728: l_key := substr(l_key, 1, l_pos - 1);
729:
730: BEGIN
731: select 'Y' into l_exists
732: from fii_ar_sales_credits_d_t
733: where salescredit_pk = to_number(l_key)
734: and dml_type = 'U';
735:
736: EXCEPTION

Line 738: insert into fii_ar_sales_credits_d_t (

734: and dml_type = 'U';
735:
736: EXCEPTION
737: WHEN NO_DATA_FOUND THEN
738: insert into fii_ar_sales_credits_d_t (
739: SALESCREDIT_PK,
740: DML_TYPE,
741: STATUS_FLAG,
742: LAST_UPDATE_DATE,