APPS.OPI_DBI_INV_TURNS_PKG dependencies on OPI_INV_ITD_ORG_MV

Line 114: -- of OPI_INV_ITD_ORG_MV

110: Filling missing start date Time bucket rows
111: ********************** */
112:
113: -- We restrict the completion within the min and max trx_date
114: -- of OPI_INV_ITD_ORG_MV
115:
116: l_stmt_num := 10;
117: IF fnd_installation.get_app_info( 'OPI', l_status,
118: l_industry, l_opi_schema) THEN

Line 132: from OPI_INV_ITD_ORG_MV

128: OPEN c_range_dates FOR
129: 'select min (transaction_date)
130: from (
131: select min(TRANSACTION_DATE) transaction_date
132: from OPI_INV_ITD_ORG_MV
133: union all
134: select min (trunc (cogs_date)) transaction_date
135: from opi_dbi_cogs_f
136: where cogs_date is not null

Line 170: from OPI_INV_ITD_ORG_MV

166: (
167: select keys.ORGANIZATION_ID ORGANIZATION_ID,
168: keys.start_date TRANSACTION_DATE,
169: (select INV_BALANCE_G
170: from OPI_INV_ITD_ORG_MV
171: where
172: ORGANIZATION_ID = keys.ORGANIZATION_ID
173: and TRANSACTION_DATE =
174: (select max(TRANSACTION_DATE) max_date

Line 175: from OPI_INV_ITD_ORG_MV

171: where
172: ORGANIZATION_ID = keys.ORGANIZATION_ID
173: and TRANSACTION_DATE =
174: (select max(TRANSACTION_DATE) max_date
175: from OPI_INV_ITD_ORG_MV
176: where
177: TRANSACTION_DATE <= keys.start_date
178: and ORGANIZATION_ID = keys.ORGANIZATION_ID)
179: and rownum < 2

Line 182: from OPI_INV_ITD_ORG_MV

178: and ORGANIZATION_ID = keys.ORGANIZATION_ID)
179: and rownum < 2
180: ) INV_BALANCE_G,
181: (select INV_BALANCE_B
182: from OPI_INV_ITD_ORG_MV
183: where
184: ORGANIZATION_ID = keys.ORGANIZATION_ID
185: and TRANSACTION_DATE =
186: (select max(TRANSACTION_DATE) max_date

Line 187: from OPI_INV_ITD_ORG_MV

183: where
184: ORGANIZATION_ID = keys.ORGANIZATION_ID
185: and TRANSACTION_DATE =
186: (select max(TRANSACTION_DATE) max_date
187: from OPI_INV_ITD_ORG_MV
188: where
189: TRANSACTION_DATE <= keys.start_date
190: and ORGANIZATION_ID = keys.ORGANIZATION_ID)
191: and rownum < 2

Line 194: from OPI_INV_ITD_ORG_MV

190: and ORGANIZATION_ID = keys.ORGANIZATION_ID)
191: and rownum < 2
192: ) INV_BALANCE_B,
193: (select INV_BALANCE_SG
194: from OPI_INV_ITD_ORG_MV
195: where
196: ORGANIZATION_ID = keys.ORGANIZATION_ID
197: and TRANSACTION_DATE =
198: (select max(TRANSACTION_DATE) max_date

Line 199: from OPI_INV_ITD_ORG_MV

195: where
196: ORGANIZATION_ID = keys.ORGANIZATION_ID
197: and TRANSACTION_DATE =
198: (select max(TRANSACTION_DATE) max_date
199: from OPI_INV_ITD_ORG_MV
200: where
201: TRANSACTION_DATE <= keys.start_date
202: and ORGANIZATION_ID = keys.ORGANIZATION_ID)
203: and rownum < 2

Line 212: from OPI_INV_ITD_ORG_MV

208: sd.start_date start_date
209: FROM
210: (
211: select ORGANIZATION_ID
212: from OPI_INV_ITD_ORG_MV
213: group by ORGANIZATION_ID
214: ) ot,
215: (
216: select start_date

Line 256: FROM opi_inv_itd_org_mv) cogs_keys

252: MINUS
253: SELECT DISTINCT
254: organization_id organization_id,
255: transaction_date start_date
256: FROM opi_inv_itd_org_mv) cogs_keys
257: ) keys
258: )
259: union
260: select

Line 266: from OPI_INV_ITD_ORG_MV

262: TRANSACTION_DATE,
263: INV_BALANCE_G,
264: INV_BALANCE_B,
265: INV_BALANCE_SG
266: from OPI_INV_ITD_ORG_MV
267: );
268:
269: BIS_COLLECTION_UTILITIES.PUT_LINE('Finished Filling missing start date Time bucket rows process.');
270:

Line 293: -- opi_inv_itd_org_mv. The MV earlier had a unique key of org, date

289: -- the TURNS stg. That is the only place we compute ITD inventory
290: -- value for those COGS keys that are not part of the inventory fact.
291: -- Since turns = annualized inv/annualized cogs, make sure the cogs
292: -- value is never NULL. A corresponding fix was made the
293: -- opi_inv_itd_org_mv. The MV earlier had a unique key of org, date
294: -- and source, which caused two rows to be created for the same org/date
295: -- once CPCS was introduced with source = 3. The MV has been modified
296: -- to have a unique key of org, date.
297: INSERT /*+ append */