APPS.FII_AP_INV_ON_HOLD_F_C dependencies on FII_AP_INV_ON_HOLD_FSTG

Line 43: DELETE FII_AP_INV_ON_HOLD_FSTG

39: PROCEDURE DELETE_STG
40: IS
41:
42: BEGIN
43: DELETE FII_AP_INV_ON_HOLD_FSTG
44: WHERE COLLECTION_STATUS = 'LOCAL READY'OR ( COLLECTION_STATUS = 'RATE NOT AVAILABLE' OR COLLECTION_STATUS = 'INVALID CURRENCY')
45: AND INSTANCE = (SELECT INSTANCE_CODE
46: FROM EDW_LOCAL_INSTANCE);
47: END;

Line 71: FROM FII_AP_INV_ON_HOLD_FSTG foh

67: TO_NUMBER(SUBSTR (INV_ON_HOLD_PK, 1, INSTR(INV_ON_HOLD_PK, '-' )-1)),
68: TO_NUMBER(SUBSTR (INV_ON_HOLD_PK, INSTR(INV_ON_HOLD_PK, '-')+1,INSTR(INV_ON_HOLD_PK,'-',1,2)-(INSTR(INV_ON_HOLD_PK,'-')+1))),
69: g_acct_or_inv_date
70:
71: FROM FII_AP_INV_ON_HOLD_FSTG foh
72:
73: WHERE
74:
75: foh.COLLECTION_STATUS = 'RATE NOT AVAILABLE'

Line 134: UPDATE FII_AP_INV_ON_HOLD_FSTG

130: IS
131:
132: BEGIN
133:
134: UPDATE FII_AP_INV_ON_HOLD_FSTG
135: SET COLLECTION_STATUS = 'READY'
136: WHERE COLLECTION_STATUS = 'LOCAL READY'
137: AND INSTANCE = (SELECT INSTANCE_CODE
138: FROM EDW_LOCAL_INSTANCE);

Line 186: Insert Into FII_AP_INV_ON_HOLD_FSTG(

182: -- Added for Currency Conversion Date Enhancement
183: -- ----------------------------------------------
184: fii_flex_mapping.init_cache('FII_AP_INV_ON_HOLD_F');
185:
186: Insert Into FII_AP_INV_ON_HOLD_FSTG(
187: CCID,
188: CREATION_DATE,
189: DUNS_FK,
190: EMPLOYEE_FK,

Line 336: Insert Into FII_AP_INV_ON_HOLD_FSTG@EDW_APPS_TO_WH(

332: IS
333:
334: BEGIN
335:
336: Insert Into FII_AP_INV_ON_HOLD_FSTG@EDW_APPS_TO_WH(
337: CCID,
338: CREATION_DATE,
339: DUNS_FK,
340: EMPLOYEE_FK,

Line 456: from FII_AP_INV_ON_HOLD_FSTG

452: USER_MEASURE4,
453: USER_MEASURE5,
454: NULL, -- OPERATION_CODE
455: 'READY'
456: from FII_AP_INV_ON_HOLD_FSTG
457: WHERE collection_status = 'LOCAL READY';
458: --ensures that only the records with collection status of local ready will be pushed to remote fstg
459: RETURN(sql%rowcount);
460:

Line 618: From FII_AP_INV_ON_HOLD_FSTG

614:
615: cursor c1 is select DISTINCT FROM_CURRENCY,
616: CREATION_DATE CONVERSION_DATE,
617: COLLECTION_STATUS
618: From FII_AP_INV_ON_HOLD_FSTG
619: where (COLLECTION_STATUS='RATE NOT AVAILABLE'
620: OR COLLECTION_STATUS = 'INVALID CURRENCY')
621: AND trunc(CREATION_DATE) <= trunc(sysdate);
622:

Line 630: From FII_AP_INV_ON_HOLD_FSTG

626: ----------------------------------------------------------------------------------------------------
627: cursor c2 is select DISTINCT FROM_CURRENCY,
628: CREATION_DATE CONVERSION_DATE,
629: COLLECTION_STATUS
630: From FII_AP_INV_ON_HOLD_FSTG
631: where (COLLECTION_STATUS='RATE NOT AVAILABLE'
632: OR COLLECTION_STATUS = 'INVALID CURRENCY' )
633: AND trunc(CREATION_DATE) > trunc(sysdate);
634:

Line 712: TRUNCATE_TABLE('FII_AP_INV_ON_HOLD_FSTG');

708: -- --------------------------------------------------------
709: edw_log.put_line(' ');
710: edw_log.put_line('Cleaning up unprocessed records left in local staging table');
711: IF (NOT LOCAL_SAME_AS_REMOTE) THEN
712: TRUNCATE_TABLE('FII_AP_INV_ON_HOLD_FSTG');
713: ELSE
714: DELETE_STG;
715: END IF;
716: select count(*) into rows1 from FII_AP_INV_ON_HOLD_FSTG;

Line 716: select count(*) into rows1 from FII_AP_INV_ON_HOLD_FSTG;

712: TRUNCATE_TABLE('FII_AP_INV_ON_HOLD_FSTG');
713: ELSE
714: DELETE_STG;
715: END IF;
716: select count(*) into rows1 from FII_AP_INV_ON_HOLD_FSTG;
717: edw_log.put_line('Number of rows in stg after truncating or deleting'||rows1 );
718: -- --------------------------------------------------------
719: -- 2. Identify Changed AP Invoice Holds record
720: -- --------------------------------------------------------

Line 876: TRUNCATE_TABLE('FII_AP_INV_ON_HOLD_FSTG');

872: edw_log.put_line(' ');
873: edw_log.put_line('Cleaning local staging table');
874:
875: fii_util.start_timer;
876: TRUNCATE_TABLE('FII_AP_INV_ON_HOLD_FSTG');
877: fii_util.stop_timer;
878: fii_util.print_timer('Duration');
879:
880: ELSE