APPS.OPI_EDW_OPM_JOB_RSRC_F_C dependencies on OPI_EDW_JOB_RSRC_FSTG

Line 43: OPI_EDW_JOB_RSRC_FSTG

39: /* Define Missing Rate Cursor for Job Detail */
40: Cursor Missing_Rate is
41: SELECT JOB_RSRC_PK,TRX_DATE_FK,SOB_CURRENCY_FK
42: FROM
43: OPI_EDW_JOB_RSRC_FSTG
44: WHERE
45: COLLECTION_STATUS in ('RATE NOT AVAILABLE','INVALID CURRENCY')
46: AND JOB_RSRC_PK like '%OPM'
47: AND SUBSTRB(JOB_RSRC_PK,instrB(JOB_RSRC_PK,'-',-1,3)+1,

Line 91: SELECT count(*) into l_count from OPI_EDW_JOB_RSRC_FSTG where

87: DELETE OPI_EDW_OPM_JOB_RSRC_INC;
88: edw_log.put_line(' ');
89: edw_log.Put_line('Identifying Missing Rate Rows ');
90: edw_log.put_line(' ');
91: SELECT count(*) into l_count from OPI_EDW_JOB_RSRC_FSTG where
92: COLLECTION_STATUS in ('RATE NOT AVAILABLE','INVALID CURRENCY')
93: AND JOB_RSRC_PK like '%OPM';
94: IF l_count > 0 THEN
95:

Line 109: from OPI_EDW_JOB_RSRC_FSTG

105: WHERE
106: BATCHSTEPLINE_ID
107: in ( SELECT SUBSTRB(JOB_RSRC_PK,instrB(JOB_RSRC_PK,'-',-1,3)+1,
108: instrB(JOB_RSRC_PK,'-',-1,2)-1-instrB(JOB_RSRC_PK,'-',-1,3))
109: from OPI_EDW_JOB_RSRC_FSTG
110: WHERE COLLECTION_STATUS in ('RATE NOT AVAILABLE','INVALID CURRENCY')
111: AND JOB_RSRC_PK like '%OPM');
112: /* The above Sub selcet has a reverse string traverse as the batchlineid is 3 from right in PK KEY */
113: edw_log.Put_line(to_char(sql%rowcount) ||' rows missing Currency Rate Conversion');

Line 124: DELETE OPI_EDW_JOB_RSRC_FSTG

120: edw_log.put_line(' ');
121:
122: /*Delete all missing rows from FSTG table if source and target are on same instance*/
123: IF (LOCAL_SAME_AS_REMOTE) THEN
124: DELETE OPI_EDW_JOB_RSRC_FSTG
125: WHERE COLLECTION_STATUS in ('RATE NOT AVAILABLE','INVALID CURRENCY')
126: AND JOB_RSRC_PK like '%OPM';
127: edw_log.Put_line(to_char(sql%rowcount) ||' missing Currency Rate Conversion rows deleted from Staging table');
128: END IF;

Line 155: INSERT INTO OPI_EDW_JOB_RSRC_FSTG(

151: -- In our case, we consider the records to be in
152: -- progress until the push_to_local procedure for
153: -- all view types has completed successfully.
154: -- ------------------------------------------------
155: INSERT INTO OPI_EDW_JOB_RSRC_FSTG(
156: ACTIVITY_FK,
157: ACT_CMPL_DATE ,
158: ACT_RSRC_COUNT,
159: ACT_RSRC_QTY ,

Line 364: l_staging_table Varchar2(30) :='OPI_EDW_JOB_RSRC_FSTG';

360: Retcode in out NOCOPY Varchar2,
361: p_from_date IN Varchar2,
362: p_to_date IN Varchar2) IS
363: l_fact_name Varchar2(30) :='OPI_EDW_JOB_RSRC_F';
364: l_staging_table Varchar2(30) :='OPI_EDW_JOB_RSRC_FSTG';
365: l_exception_msg Varchar2(2000):=Null;
366: l_from_date Date:=Null;
367: l_to_date Date:=Null;
368: l_seq_id NUMBER := -1;