APPS.OPI_EDW_OPMCOGS_F_C dependencies on OPI_EDW_COGS_FSTG

Line 44: OPI_EDW_COGS_FSTG

40: /* Define Missing Rate Cursor for Job Detail */
41: Cursor Missing_Rate is
42: SELECT COGS_PK,COGS_DATE,BASE_CURRENCY_FK
43: FROM
44: OPI_EDW_COGS_FSTG
45: WHERE
46: COLLECTION_STATUS in ('RATE NOT AVAILABLE','INVALID CURRENCY')
47: AND COGS_PK like '%OPM%';
48:

Line 82: SELECT count(*) into l_count from OPI_EDW_COGS_FSTG

78: BEGIN
79: /* Delete the incremental table before inserting new data */
80: DELETE OPI_EDW_OPMCOGS_INC WHERE SEQ_ID IS NOT NULL;
81: edw_log.put_line(' ');
82: SELECT count(*) into l_count from OPI_EDW_COGS_FSTG
83: WHERE COLLECTION_STATUS in ('RATE NOT AVAILABLE','INVALID CURRENCY')
84: AND COGS_PK like '%OPM%';
85: IF l_count > 0 THEN
86: edw_log.put_line(' ');

Line 101: FROM OPI_EDW_COGS_FSTG

97: OPI_EDW_OPMCOGS_FCV
98: WHERE
99: VIEW_ID=1 AND
100: COGS_PK in ( SELECT COGS_PK
101: FROM OPI_EDW_COGS_FSTG
102: WHERE COLLECTION_STATUS in ('RATE NOT AVAILABLE','INVALID CURRENCY')
103: AND COGS_PK like '%OPM%');
104:
105: edw_log.Put_line(to_char(sql%rowcount) ||' rows missing Currency Rate Conversion for View Type 1');

Line 118: FROM OPI_EDW_COGS_FSTG

114: OPI_EDW_OPMCOGS_FCV
115: WHERE
116: VIEW_ID=2 AND
117: COGS_PK in ( SELECT COGS_PK
118: FROM OPI_EDW_COGS_FSTG
119: WHERE COLLECTION_STATUS in ('RATE NOT AVAILABLE','INVALID CURRENCY')
120: AND COGS_PK like '%OPM%');
121: edw_log.Put_line(to_char(sql%rowcount) ||' rows missing Currency Rate Conversion for View Type 2');
122: edw_log.put_line(' ');

Line 132: DELETE OPI_EDW_COGS_FSTG

128: edw_log.put_line(' ');
129:
130: /*Delete all missing rows from FSTG table if source and target are on same instance*/
131: IF (LOCAL_SAME_AS_REMOTE) THEN
132: DELETE OPI_EDW_COGS_FSTG
133: WHERE COLLECTION_STATUS in ('RATE NOT AVAILABLE','INVALID CURRENCY')
134: AND COGS_PK like '%OPM%';
135: edw_log.Put_line(to_char(sql%rowcount) ||' missing Currency Rate Conversion rows deleted from Staging table');
136: END IF;

Line 166: Insert Into OPI_EDW_COGS_FSTG

162: -- all view types has completed successfully.
163: -- ------------------------------------------------
164:
165: edw_log.Put_line('Inserting Rows into Staging Table');
166: Insert Into OPI_EDW_COGS_FSTG
167: (COGS_PK
168: ,COGS_DATE
169: ,COGS_DATE_FK
170: ,ORDER_LINE_ID

Line 482: l_staging_table Varchar2(30) :='OPI_EDW_COGS_FSTG';

478: Retcode in out NOCOPY Varchar2,
479: p_from_date IN Varchar2,
480: p_to_date IN Varchar2) IS
481: l_fact_name Varchar2(30) :='OPI_EDW_COGS_F';
482: l_staging_table Varchar2(30) :='OPI_EDW_COGS_FSTG';
483: l_exception_msg Varchar2(2000):=Null;
484: l_from_date Date:=Null;
485: l_to_date Date:=Null;
486: l_seq_id1 NUMBER := -1;

Line 579: UPDATE OPI_EDW_COGS_FSTG cogs

575: -- if collection status is other than local ready
576: -- then we should not re-construct sales rep fk.
577: -- --------------------------------------------
578:
579: UPDATE OPI_EDW_COGS_FSTG cogs
580: SET PRIM_SALESRESOURCE_FK =
581: (select sr.salesrep_id||'-'||sr.org_id||'-'||cogs.instance_fk||'-SALESREP-PERS'
582: FROM RA_SALESREPS_ALL sr
583: WHERE sr.SALESREP_NUMBER = substrb(PRIM_SALESRESOURCE_FK,1,instrb(PRIM_SALESRESOURCE_FK,'-',-1,1)-1)