eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.OPI_EDW_COGS_FCV

Source

View Text - Preformatted


SELECT 1 VIEW_ID, inc.seq_id SEQ_ID, 'INV' || '-' || to_char(mmt.transaction_id) || '-' || to_char(nvl(mta.cost_element_id,999)) || '-' || to_char(l.line_id) || '-' || inst.instance_code COGS_PK, inst.instance_code INSTANCE_FK, pl.inventory_item_id||'-'||nvl(pl.ship_from_org_id, l.ship_from_org_id) ||'-'||inst.instance_code||'-IORG' TOP_MODEL_ITEM_FK, l.inventory_item_id||'-'||mmt.organization_id ||'-'||inst.instance_code||'-IORG' ITEM_ORG_FK, ood.operating_unit ||'-'||inst.instance_code OPERATING_UNIT_FK, mmt.organization_id ||'-'||inst.instance_code INV_ORG_FK, decode(h.sold_to_org_id, null, 'NA_EDW',h.sold_to_org_id ||'-'|| inst.instance_code ||'-CUST_ACCT-TPRT') CUSTOMER_FK, decode(h.sales_channel_code,'', 'NA_EDW',h.sales_channel_code ||'-'||inst.instance_code) SALES_CHANNEL_FK, h.salesrep_id||'-'|| h.org_id||'-'||inst.instance_code ||'-SALESREP-PERS' PRIM_SALES_REP_FK, h.salesrep_id||'-'|| h.org_id||'-'||inst.instance_code ||'-SALESREP-PERS' PRIM_SALESRESOURCE_FK, EDW_GEOGRAPHY_PKG.Customer_Site_Location_fk( l.invoice_to_org_id, inst.instance_code) BILL_TO_LOC_FK, EDW_GEOGRAPHY_PKG.Customer_Site_Location_fk( l.ship_to_org_id,inst.instance_code) SHIP_TO_LOC_FK, decode(pl.project_id,'', 'NA_EDW', pl.project_id ||'-' || inst.instance_code|| '-PJ-PRJ') PROJECT_FK, decode(l.task_id, '', 'NA_EDW', l.task_id ||'-' || inst.instance_code ) TASK_FK, EDW_UTIL.get_edw_base_uom(mmt.transaction_uom, l.inventory_item_id) BASE_UOM_FK, nvl(mmt.currency_code, EDW_UTIL.get_base_currency( mmt.organization_id)) TRX_CURRENCY_FK, EDW_UTIL.get_base_currency(mmt.organization_id) BASE_CURRENCY_FK, EDW_LOOKUP_PKG.Lookup_Code_FK('SOL','ORDER_CATEGORY', upper(h.order_category_code) ) ORDER_CATEGORY_FK, EDW_LOOKUP_PKG.Lookup_Code_FK('SOO','ORDER_TYPE', upper(ordtyp.name) ) ORDER_TYPE_FK, decode(l.invoice_to_org_id, '', 'NA_EDW', l.invoice_to_org_id||'-' ||inst.instance_code||'-CUST_SITE_USE') BILL_TO_SITE_FK, decode(l.ship_to_org_id, '', 'NA_EDW', l.ship_to_org_id||'-' ||inst.instance_code||'-CUST_SITE_USE') SHIP_TO_SITE_FK, EDW_TIME_PKG.CAL_DAY_FK(h.BOOKED_DATE, fspa.SET_OF_BOOKS_ID,inst.instance_code) MONTH_BOOKED_FK, EDW_TIME_PKG.CAL_DAY_FK(h.BOOKED_DATE, fspa.SET_OF_BOOKS_ID,inst.instance_code) DATE_BOOKED_FK, EDW_TIME_PKG.CAL_DAY_FK(l.promise_date, fspa.SET_OF_BOOKS_ID,inst.instance_code) DATE_PROMISED_FK, EDW_TIME_PKG.CAL_DAY_FK(l.REQUEST_DATE, fspa.SET_OF_BOOKS_ID,inst.instance_code) DATE_REQUESTED_FK, EDW_TIME_PKG.CAL_DAY_FK(l.schedule_ship_date, fspa.SET_OF_BOOKS_ID,inst.instance_code) DATE_SCHEDULED_FK, EDW_TIME_PKG.CAL_DAY_FK(l.ACTUAL_SHIPMENT_DATE, fspa.SET_OF_BOOKS_ID,inst.instance_code) DATE_SHIPPED_FK, decode( mmt.locator_id, NULL, decode(mmt.subinventory_code, NULL, mp.organization_code ||'-'||inst.instance_code||'-PLNT', mmt.subinventory_code || '-'||mp.organization_code||'-'|| inst.instance_code||'-SUBI'), mmt.locator_id||'-'||mp.organization_code||'-'||inst.instance_code) LOCATOR_FK, EDW_LOOKUP_PKG.Lookup_Code_FK('SOS','ORDER_SOURCE', upper(pl.source_type_code) ) ORDER_SOURCE_FK, to_char(fspa.set_of_books_id)||'-'||inst.instance_code SET_OF_BOOKS_FK, 'NA_EDW' CAMPAIGN_INIT_FK, 'NA_EDW' CAMPAIGN_ACTL_FK, 'NA_EDW' MEDCHN_INIT_FK, 'NA_EDW' MEDCHN_ACTL_FK, 'NA_EDW' OFFER_HDR_FK, 'NA_EDW' OFFER_LINE_FK, 'NA_EDW' MARKET_SEGMENT_FK, 'NA_EDW' TARGET_SEGMENT_INIT_FK, 'NA_EDW' TARGET_SEGMENT_ACTL_FK, 'NA_EDW' CAMPAIGN_STATUS_ACTL_FK, 'NA_EDW' CAMPAIGN_STATUS_INIT_FK, EDW_TIME_PKG.CAL_DAY_FK(mta.transaction_date, fspa.SET_OF_BOOKS_ID,inst.instance_code) COGS_DATE_FK, pl.line_id || '-' || inst.instance_code ORDER_LINE_ID, mp.organization_code||'-'||inst.instance_code||'-PLNT' SHIP_INV_LOCATOR_FK, mta.transaction_date COGS_DATE, h.ordered_date ORDER_DATE, decode( decode(sign(l.promise_date - nvl( l.ACTUAL_SHIPMENT_DATE, l.promise_date +1)),1,1,0,1, 0), 1,decode(nvl(mta.cost_element_id, -1),1,1,-1,1,0) / EDW_UTIL.get_line_detail_count(l.line_Id), 0) PROM_EARLY_COUNT, decode(sign(l.promise_date - nvl(l.ACTUAL_SHIPMENT_DATE,l.promise_date +1)), -1,decode(nvl(mta.cost_element_id, -1),1,1,-1,1,0) / EDW_UTIL.get_line_detail_count(l.line_Id), 0) PROM_LATE_COUNT, decode( decode(sign(l.request_date - nvl(l.ACTUAL_SHIPMENT_DATE, l.request_date +1)),1,1,0,1,0), 1,decode(nvl(mta.cost_element_id, -1),1,1,-1,1,0) / EDW_UTIL.get_line_detail_count(l.line_Id), 0) REQ_EARLY_COUNT, decode(sign(l.request_date-nvl(l.ACTUAL_SHIPMENT_DATE,l.request_date +1)), -1,decode(nvl(mta.cost_element_id, -1),1,1,-1,1,0) / EDW_UTIL.get_line_detail_count(l.line_Id), 0) REQ_LATE_COUNT, decode(sign(l.promise_date-nvl(l.ACTUAL_SHIPMENT_DATE,l.promise_date+1)), 1, mta.base_transaction_value * (-1), 0, mta.base_transaction_value * (-1), 0) PROM_EARLY_VAL_G, decode(sign(l.promise_date-nvl(l.ACTUAL_SHIPMENT_DATE,l.promise_date +1)), -1,mta.base_transaction_value * (-1), 0) PROM_LATE_VAL_G, decode(sign(l.request_date-nvl(l.ACTUAL_SHIPMENT_DATE,l.request_date+1)), 1,mta.base_transaction_value * (-1), 0,mta.base_transaction_value * (-1), 0) REQ_EARLY_VAL_G, decode(sign(l.request_date-nvl(l.ACTUAL_SHIPMENT_DATE,l.request_date+1)), -1,mta.base_transaction_value * (-1), 0) REQ_LATE_VAL_G, decode( decode(nvl(mta.cost_element_id, -1),1,1,-1,1,0), 1, months_between(l.ACTUAL_SHIPMENT_DATE, l.request_date) * 31, 0)/ EDW_UTIL.get_line_detail_count(l.line_Id) REQUEST_LEAD_TIME, decode( decode(nvl(mta.cost_element_id, -1),1,1,-1,1,0), 1,months_between(l.ACTUAL_SHIPMENT_DATE, l.promise_date) * 31, 0)/ EDW_UTIL.get_line_detail_count(l.line_Id) PROMISE_LEAD_TIME, decode( decode(nvl(mta.cost_element_id, -1),1,1,-1,1,0), 1,months_between(l.ACTUAL_SHIPMENT_DATE, h.booked_date) * 31, 0)/EDW_UTIL.get_line_detail_count(l.line_Id) ORDER_LEAD_TIME, decode(decode(pl.ato_line_id, NULL, 'N', 'Y'), 'N', decode(mmt.inventory_item_id, pl.inventory_item_id, decode(nvl(mta.cost_element_id, -1), 1, abs(mmt.primary_quantity) * /* UOM based on the reporting OU, not the OE OU */ EDW_UTIL.get_uom_conv_rate(mmt.transaction_uom, mmt.inventory_item_id), -1, abs(mmt.primary_quantity) * EDW_UTIL.get_uom_conv_rate(mmt.transaction_uom, mmt.inventory_item_id), 0), decode(pl.item_type_code, 'MODEL', decode(nvl(mta.cost_element_id, 1), 1, nvl(pl.shipped_quantity,0) / EDW_UTIL.get_pto_mmt_count(pl.line_id) * EDW_UTIL.get_uom_conv_rate(pl.order_quantity_uom, pl.inventory_item_id), 0), 'KIT', decode(nvl(mta.cost_element_id, 1), 1, nvl(pl.shipped_quantity,0) / EDW_UTIL.get_pto_mmt_count(pl.line_id) * EDW_UTIL.get_uom_conv_rate(pl.order_quantity_uom, pl.inventory_item_id), 0), 0) ), 'Y', decode(pl.item_type_code, 'MODEL', /* case of ATO model */ decode(nvl(mta.cost_element_id, -1), 1, abs(mmt.primary_quantity) * EDW_UTIL.get_uom_conv_rate(mmt.transaction_uom, mmt.inventory_item_id), -1, abs(mmt.primary_quantity) * EDW_UTIL.get_uom_conv_rate(mmt.transaction_uom, mmt.inventory_item_id), 0), decode(mmt.inventory_item_id, pl.inventory_item_id, decode(nvl(mta.cost_element_id, -1), 1, abs(mmt.primary_quantity) * EDW_UTIL.get_uom_conv_rate(mmt.transaction_uom, mmt.inventory_item_id), -1, abs(mmt.primary_quantity) * EDW_UTIL.get_uom_conv_rate(mmt.transaction_uom, mmt.inventory_item_id), 0) ,0) ), decode(mmt.inventory_item_id, pl.inventory_item_id, decode(nvl(mta.cost_element_id, -1), 1, abs(mmt.primary_quantity) * EDW_UTIL.get_uom_conv_rate(mmt.transaction_uom, mmt.inventory_item_id), -1, abs(mmt.primary_quantity) * EDW_UTIL.get_uom_conv_rate(mmt.transaction_uom, mmt.inventory_item_id), 0), 0) ) SHIPPED_QTY_B, to_number(NULL) RMA_QTY_B, to_number(NULL) ICAP_QTY_B, nvl(mta.transaction_value,mta.base_transaction_value) COGS_T, (mta.base_transaction_value) COGS_B, 0 COGS_G, 0 RMA_VAL_T, 0 RMA_VAL_G, null LAST_UPDATE_DATE, mta.cost_element_id COST_ELEMENT, mta.reference_account ACCOUNT, h.order_number ORDER_NUMBER, wnd.waybill WAYBILL_NUMBER, wdd.lot_number LOT, wdd.revision REVISION, wdd.serial_number SERIAL_NUMBER, Null USER_ATTRIBUTE1, Null USER_ATTRIBUTE2, Null USER_ATTRIBUTE3, Null USER_ATTRIBUTE4, Null USER_ATTRIBUTE5, Null USER_ATTRIBUTE6, Null USER_ATTRIBUTE7, Null USER_ATTRIBUTE8, Null USER_ATTRIBUTE9, Null USER_ATTRIBUTE10, Null USER_ATTRIBUTE11, Null USER_ATTRIBUTE12, Null USER_ATTRIBUTE13, Null USER_ATTRIBUTE14, Null USER_ATTRIBUTE15, to_number(NULL) USER_MEASURE1, to_number(NULL) USER_MEASURE2, to_number(NULL) USER_MEASURE3, to_number(NULL) USER_MEASURE4, to_number(NULL) USER_MEASURE5, Null USER_FK1, Null USER_FK2, Null USER_FK3, Null USER_FK4, Null USER_FK5, Null OPERATION_CODE, wda.DELIVERY_ID DELIVERY_ID, edw_currency.get_rate( EDW_UTIL.get_base_currency(mmt.organization_id), nvl(mta.transaction_date, mta.creation_date)) global_currency_rate from opi_edw_cogs_inc inc, mtl_material_transactions mmt, mtl_transaction_accounts mta, oe_order_lines_all l, oe_order_lines_all pl, oe_order_headers_all h, wsh_delivery_details wdd, wsh_delivery_assignments wda, wsh_new_deliveries wnd, edw_local_instance inst, so_order_types_all ordtyp, financials_system_params_all fspa, (SELECT HOU.ORGANIZATION_ID ORGANIZATION_ID, DECODE(FPG.MULTI_ORG_FLAG, 'Y', DECODE(HOI2.ORG_INFORMATION_CONTEXT, 'Accounting Information', TO_NUMBER(HOI2.ORG_INFORMATION3), TO_NUMBER(NULL)), TO_NUMBER(NULL)) OPERATING_UNIT FROM HR_ORGANIZATION_UNITS HOU, HR_ORGANIZATION_INFORMATION HOI2, FND_PRODUCT_GROUPS FPG WHERE HOU.ORGANIZATION_ID = HOI2.ORGANIZATION_ID AND ( HOI2.ORG_INFORMATION_CONTEXT || '') ='Accounting Information' ) ood, mtl_parameters mp WHERE mmt.transaction_id = inc.primary_key1 and ( (mmt.transaction_source_type_id = 2 and mta.transaction_source_type_id = 2) or (mmt.transaction_source_type_id = 13 and mmt.transaction_action_id = 9 and mta.transaction_source_type_id = 13) ) and mmt.transaction_id = mta.transaction_id and mta.accounting_line_type in (2, 35) and ordtyp.order_type_id = h.order_type_id and pl.org_id = l.org_id and h.org_id = l.org_id and l.line_id = mmt.trx_source_line_id and l.line_category_code = 'ORDER' and pl.line_category_code = 'ORDER' and pl.line_id = nvl(l.top_model_line_id, l.line_id) and h.header_id = l.header_id and h.header_id = pl.header_id and mp.organization_id = mmt.organization_id and ood.organization_id = mmt.organization_id and fspa.org_id = ood.operating_unit and wdd.delivery_detail_id(+) = mmt.picking_line_id and wda.DELIVERY_ID = wnd.DELIVERY_ID (+) and wda.DELIVERY_DETAIL_ID (+) = wdd.delivery_detail_id UNION ALL Select 2 VIEW_ID, inc.seq_id SEQ_ID, 'RMA' || '-' || to_char(mmt.transaction_id) || '-' || to_char(nvl(mta.cost_element_id,999)) || '-' || inst.instance_code COGS_PK, inst.instance_code INSTANCE_FK, nvl(pl.inventory_item_id,nvl(cl.inventory_item_id, l.inventory_item_id))||'-'|| nvl(pl.ship_from_org_id, nvl(cl.ship_from_org_id,l.ship_from_org_id)) ||'-'||inst.instance_code||'-IORG' TOP_MODEL_ITEM_FK, nvl(cl.inventory_item_id,l.inventory_item_id)||'-'|| nvl(cl.ship_from_org_id,l.ship_from_org_id) ||'-'||inst.instance_code||'-IORG' ITEM_ORG_FK, ood.operating_unit ||'-'||inst.instance_code OPERATING_UNIT_FK, mmt.organization_id ||'-'||inst.instance_code INV_ORG_FK, decode(h.sold_to_org_id, null, 'NA_EDW',h.sold_to_org_id ||'-'|| inst.instance_code ||'-CUST_ACCT-TPRT') CUSTOMER_FK, decode(h.sales_channel_code, '', 'NA_EDW',h.sales_channel_code ||'-'||inst.instance_code) SALES_CHANNEL_FK, h.salesrep_id||'-'|| h.org_id||'-'||inst.instance_code ||'-SALESREP-PERS' PRIM_SALES_REP_FK, h.salesrep_id||'-'|| h.org_id||'-'||inst.instance_code ||'-SALESREP-PERS' PRIM_SALESRESOURCE_FK, decode(l.link_to_line_id, '',decode(l.invoice_to_org_id, '','NA_EDW', EDW_GEOGRAPHY_PKG.Customer_Site_Location_fk( l.invoice_to_org_id,inst.instance_code)), decode(pl.invoice_to_org_id, '',decode(cl.invoice_to_org_id, '',decode(l.invoice_to_org_id, '','NA_EDW', EDW_GEOGRAPHY_PKG.Customer_Site_Location_fk( l.invoice_to_org_id,inst.instance_code)), EDW_GEOGRAPHY_PKG.Customer_Site_Location_fk( l.invoice_to_org_id,inst.instance_code)), EDW_GEOGRAPHY_PKG.Customer_Site_Location_fk(l.invoice_to_org_id, inst.instance_code)) ) BILL_TO_LOC_FK, decode(l.link_to_line_id, '',decode(l.SHIP_TO_ORG_ID, '','NA_EDW',EDW_GEOGRAPHY_PKG.Customer_Site_Location_fk( l.SHIP_TO_ORG_ID,inst.instance_code)), decode(pl.SHIP_TO_ORG_ID, '',decode(cl.SHIP_TO_ORG_ID, '',decode(l.SHIP_TO_ORG_ID, '','NA_EDW', EDW_GEOGRAPHY_PKG.Customer_Site_Location_fk( l.SHIP_TO_ORG_ID, inst.instance_code)), EDW_GEOGRAPHY_PKG.Customer_Site_Location_fk( cl.SHIP_TO_ORG_ID,inst.instance_code)), EDW_GEOGRAPHY_PKG.Customer_Site_Location_fk(pl.SHIP_TO_ORG_ID, inst.instance_code)) ) SHIP_TO_LOC_FK, decode(l.link_to_line_id, '',decode(l.project_id, '','NA_EDW', l.project_id||'-'|| inst.instance_code|| '-PJ-PRJ'), decode(pl.project_id, '',decode(cl.project_id, '',decode(l.project_id, '','NA_EDW', l.project_id||'-'||inst.instance_code|| '-PJ-PRJ'), cl.project_id||'-'|| inst.instance_code|| '-PJ-PRJ'), pl.project_id||'-'|| inst.instance_code|| '-PJ-PRJ')) PROJECT_FK, decode(l.link_to_line_id, '',decode(l.task_id, '','NA_EDW', l.task_id ||'-'||inst.instance_code), decode(pl.task_id, '',decode(cl.task_id, '',decode(l.task_id, '','NA_EDW', l.task_id||'-'||inst.instance_code ), cl.task_id ||'-'|| inst.instance_code), pl.task_id|| '-'|| inst.instance_code)) TASK_FK, EDW_UTIL.get_edw_base_uom(mmt.transaction_uom, l.inventory_item_id) BASE_UOM_FK, nvl(mmt.currency_code, EDW_UTIL.get_base_currency( mmt.organization_id)) TRX_CURRENCY_FK, EDW_UTIL.get_base_currency(mmt.organization_id) BASE_CURRENCY_FK, EDW_LOOKUP_PKG.Lookup_Code_FK('SOL','ORDER_CATEGORY', upper(h.order_category_code) ) ORDER_CATEGORY_FK, EDW_LOOKUP_PKG.Lookup_Code_FK('SOO','ORDER_TYPE', upper(ordtyp.name) ) ORDER_TYPE_FK, decode(l.invoice_to_org_id, '', 'NA_EDW', l.invoice_to_org_id||'-' ||inst.instance_code||'-CUST_SITE_USE') BILL_TO_SITE_FK, decode(l.link_to_line_id, '',decode(l.SHIP_TO_ORG_ID, '','NA_EDW', l.ship_to_org_id||'-'||inst.instance_code||'-CUST_SITE_USE'), decode(pl.SHIP_TO_ORG_ID, '',decode(cl.SHIP_TO_ORG_ID, '',decode(l.SHIP_TO_ORG_ID, '','NA_EDW', l.ship_to_org_id ||'-' ||inst.instance_code||'-CUST_SITE_USE'), cl.ship_to_org_id||'-'||inst.instance_code||'-CUST_SITE_USE'), pl.ship_to_org_id||'-'||inst.instance_code||'-CUST_SITE_USE')) SHIP_TO_SITE_FK, EDW_TIME_PKG.CAL_DAY_FK(h.BOOKED_DATE, fspa.SET_OF_BOOKS_ID,inst.instance_code) MONTH_BOOKED_FK, EDW_TIME_PKG.CAL_DAY_FK(h.BOOKED_DATE, fspa.SET_OF_BOOKS_ID,inst.instance_code) DATE_BOOKED_FK, EDW_TIME_PKG.CAL_DAY_FK(l.promise_date, fspa.SET_OF_BOOKS_ID,inst.instance_code) DATE_PROMISED_FK, EDW_TIME_PKG.CAL_DAY_FK(l.request_date, fspa.SET_OF_BOOKS_ID,inst.instance_code) DATE_REQUESTED_FK, EDW_TIME_PKG.CAL_DAY_FK(NULL, fspa.SET_OF_BOOKS_ID,inst.instance_code) DATE_SCHEDULED_FK, EDW_TIME_PKG.CAL_DAY_FK(NULL, fspa.SET_OF_BOOKS_ID,inst.instance_code) DATE_SHIPPED_FK, decode( mmt.locator_id, NULL, decode(mmt.subinventory_code, NULL, mp.organization_code ||'-'||inst.instance_code||'-PLNT', mmt.subinventory_code ||'-'|| mp.organization_code||'-'|| inst.instance_code||'-SUBI'), mmt.locator_id||'-'||mp.organization_code||'-'||inst.instance_code) LOCATOR_FK, EDW_LOOKUP_PKG.Lookup_Code_FK('SOS','ORDER_SOURCE', upper(pl.source_type_code) ) ORDER_SOURCE_FK, to_char(fspa.set_of_books_id)||'-'||inst.instance_code SET_OF_BOOKS_FK, 'NA_EDW' CAMPAIGN_INIT_FK, 'NA_EDW' CAMPAIGN_ACTL_FK, 'NA_EDW' MEDCHN_INIT_FK, 'NA_EDW' MEDCHN_ACTL_FK, 'NA_EDW' OFFER_HDR_FK, 'NA_EDW' OFFER_LINE_FK, 'NA_EDW' MARKET_SEGMENT_FK, 'NA_EDW' TARGET_SEGMENT_INIT_FK, 'NA_EDW' TARGET_SEGMENT_ACTL_FK, 'NA_EDW' CAMPAIGN_STATUS_ACTL_FK, 'NA_EDW' CAMPAIGN_STATUS_INIT_FK, EDW_TIME_PKG.CAL_DAY_FK(mta.transaction_date, fspa.SET_OF_BOOKS_ID,inst.instance_code) COGS_DATE_FK, pl.line_id ||'-' || inst.instance_code ORDER_LINE_ID, mp.organization_code||'-'||inst.instance_code ||'-PLNT' SHIP_INV_LOCATOR_FK, mta.transaction_date COGS_DATE, h.ordered_date ORDER_DATE, to_number(NULL) PROM_EARLY_COUNT, to_number(NULL) PROM_LATE_COUNT, to_number(NULL) REQ_EARLY_COUNT, to_number(NULL) REQ_LATE_COUNT, to_number(NULL) PROM_EARLY_VAL_G, to_number(NULL) PROM_LATE_VAL_G, to_number(NULL) REQ_EARLY_VAL_G, to_number(NULL) REQ_LATE_VAL_G, to_number(NULL) REQUEST_LEAD_TIME, to_number(NULL) PROMISE_LEAD_TIME, to_number(NULL) ORDER_LEAD_TIME, to_number(NULL) SHIPPED_QTY_B, decode(pl.inventory_item_id, '', decode(nvl(mta.cost_element_id, -1), 1, mmt.transaction_quantity * EDW_UTIL.get_uom_conv_rate(mmt.transaction_uom, mmt.inventory_item_id), -1, mmt.transaction_quantity * EDW_UTIL.get_uom_conv_rate(mmt.transaction_uom, mmt.inventory_item_id), 0) * (-1), decode(decode(pl.ato_line_id, NULL, 'N', 'Y'), 'N', decode(mmt.inventory_item_id, pl.inventory_item_id, decode(nvl(mta.cost_element_id, -1), 1, mmt.transaction_quantity * EDW_UTIL.get_uom_conv_rate(mmt.transaction_uom, mmt.inventory_item_id), -1, mmt.transaction_quantity * EDW_UTIL.get_uom_conv_rate(mmt.transaction_uom, mmt.inventory_item_id), 0) * (-1), decode(pl.item_type_code, 'MODEL', decode(pl.inventory_item_id, cl.inventory_item_id, decode(nvl(mta.cost_element_id, 1), 1, mmt.transaction_quantity * EDW_UTIL.get_uom_conv_rate(mmt.transaction_uom, mmt.inventory_item_id), 0), 0), 'KIT', decode(pl.inventory_item_id, cl.inventory_item_id, decode(nvl(mta.cost_element_id, 1), 1, mmt.transaction_quantity * EDW_UTIL.get_uom_conv_rate(mmt.transaction_uom, mmt.inventory_item_id), 0), 0), 0) ), 'Y', decode(pl.item_type_code, 'MODEL', decode(nvl(mta.cost_element_id, -1), 1, mmt.transaction_quantity * EDW_UTIL.get_uom_conv_rate(mmt.transaction_uom, mmt.inventory_item_id), -1, mmt.transaction_quantity * EDW_UTIL.get_uom_conv_rate(mmt.transaction_uom, mmt.inventory_item_id), 0) * (-1), decode(mmt.inventory_item_id, pl.inventory_item_id, decode(nvl(mta.cost_element_id, -1), 1, mmt.transaction_quantity * EDW_UTIL.get_uom_conv_rate(mmt.transaction_uom, mmt.inventory_item_id), -1, mmt.transaction_quantity * EDW_UTIL.get_uom_conv_rate(mmt.transaction_uom, mmt.inventory_item_id), 0) * (-1), 0) ), decode(mmt.inventory_item_id, pl.inventory_item_id, decode(nvl(mta.cost_element_id, -1), 1, mmt.transaction_quantity * EDW_UTIL.get_uom_conv_rate(mmt.transaction_uom, mmt.inventory_item_id), -1, mmt.transaction_quantity * EDW_UTIL.get_uom_conv_rate(mmt.transaction_uom, mmt.inventory_item_id), 0) * (-1), 0) ) ) RMA_QTY_B, to_number(NULL) ICAP_QTY_B, nvl(mta.transaction_value,mta.base_transaction_value) COGS_T, mta.base_transaction_value COGS_B, 0 COGS_G, nvl(mta.transaction_value,mta.base_transaction_value) RMA_VAL_T, 0 RMA_VAL_G, null LAST_UPDATE_DATE, mta.cost_element_id COST_ELEMENT, mta.reference_account ACCOUNT, h.order_number ORDER_NUMBER, NULL WAYBILL_NUMBER, NULL LOT, NULL REVISION, NULL SERIAL_NUMBER, Null USER_ATTRIBUTE1, Null USER_ATTRIBUTE2, Null USER_ATTRIBUTE3, Null USER_ATTRIBUTE4, Null USER_ATTRIBUTE5, Null USER_ATTRIBUTE6, Null USER_ATTRIBUTE7, Null USER_ATTRIBUTE8, Null USER_ATTRIBUTE9, Null USER_ATTRIBUTE10, Null USER_ATTRIBUTE11, Null USER_ATTRIBUTE12, Null USER_ATTRIBUTE13, Null USER_ATTRIBUTE14, Null USER_ATTRIBUTE15, to_number(NULL) USER_MEASURE1, to_number(NULL) USER_MEASURE2, to_number(NULL) USER_MEASURE3, to_number(NULL) USER_MEASURE4, to_number(NULL) USER_MEASURE5, Null USER_FK1, Null USER_FK2, Null USER_FK3, Null USER_FK4, Null USER_FK5, Null OPERATION_CODE, to_number(NULL) delivery_id, edw_currency.get_rate( EDW_UTIL.get_base_currency(mmt.organization_id), nvl(mta.transaction_date, mta.creation_date)) global_currency_rate from opi_edw_cogs_inc inc, mtl_material_transactions mmt, mtl_transaction_accounts mta, oe_order_lines_all l, oe_order_lines_all cl, oe_order_lines_all pl, oe_order_headers_all h, edw_local_instance inst, so_order_types_all ordtyp, financials_system_params_all fspa, (SELECT HOU.ORGANIZATION_ID ORGANIZATION_ID, DECODE(FPG.MULTI_ORG_FLAG, 'Y', DECODE(HOI2.ORG_INFORMATION_CONTEXT, 'Accounting Information', TO_NUMBER(HOI2.ORG_INFORMATION3), TO_NUMBER(NULL)), TO_NUMBER(NULL)) OPERATING_UNIT FROM HR_ORGANIZATION_UNITS HOU, HR_ORGANIZATION_INFORMATION HOI2, FND_PRODUCT_GROUPS FPG WHERE HOU.ORGANIZATION_ID = HOI2.ORGANIZATION_ID AND ( HOI2.ORG_INFORMATION_CONTEXT || '') ='Accounting Information' ) ood, mtl_parameters mp where mmt.transaction_id = inc.primary_key1 and ( (mmt.transaction_source_type_id = 12 and mta.transaction_source_type_id = 12) or (mmt.transaction_source_type_id = 13 and mmt.transaction_action_id = 14 and mta.transaction_source_type_id = 13) ) and mmt.transaction_id = mta.transaction_id and mta.accounting_line_type in (2, 35) and ordtyp.order_type_id = h.order_type_id and h.org_id = l.org_id and l.line_id = mmt.trx_source_line_id and l.line_category_code = 'RETURN' and cl.line_id (+) = l.link_to_line_id and pl.line_id (+) = nvl(cl.top_model_line_id, cl.line_id) and h.header_id = l.header_id and mp.organization_id = mmt.organization_id and ood.organization_id = mmt.organization_id and fspa.org_id = ood.operating_unit UNION ALL Select 3 VIEW_ID, inc.seq_id SEQ_ID, 'ICI' || '-' || to_char(aid.distribution_line_number) || '-' || to_char(aid.invoice_id) || '-' || inst.instance_code COGS_PK, inst.instance_code INSTANCE_FK, pl.inventory_item_id||'-'||nvl(pl.ship_from_org_id, l.ship_from_org_id) ||'-'||inst.instance_code||'-IORG' TOP_MODEL_ITEM_FK, l.inventory_item_id||'-'||l.ship_from_org_id ||'-'||inst.instance_code||'-IORG' ITEM_ORG_FK, aid.org_id ||'-'||inst.instance_code OPERATING_UNIT_FK, decode(rcl.interface_line_attribute3, '', 'NA_EDW', rcl.interface_line_attribute3 ||'-' ||inst.instance_code) INV_ORG_FK, decode(h.sold_to_org_id, null, 'NA_EDW',h.sold_to_org_id ||'-'|| inst.instance_code ||'-CUST_ACCT-TPRT') CUSTOMER_FK, decode(h.sales_channel_code,'','NA_EDW', h.sales_channel_code ||'-'||inst.instance_code) SALES_CHANNEL_FK, h.salesrep_id||'-'|| h.org_id||'-'||inst.instance_code ||'-SALESREP-PERS' PRIM_SALES_REP_FK, h.salesrep_id||'-'|| h.org_id||'-'||inst.instance_code ||'-SALESREP-PERS' PRIM_SALESRESOURCE_FK, EDW_GEOGRAPHY_PKG.Customer_Site_Location_fk( l.invoice_to_org_id, inst.instance_code) BILL_TO_LOC_FK, EDW_GEOGRAPHY_PKG.Customer_Site_Location_fk( l.ship_to_org_id,inst.instance_code) SHIP_TO_LOC_FK, decode(pl.project_id,'', 'NA_EDW', pl.project_id ||'-' || inst.instance_code|| '-PJ-PRJ') PROJECT_FK, decode(l.task_id, '', 'NA_EDW', l.task_id ||'-' || inst.instance_code ) TASK_FK, EDW_UTIL.get_edw_base_uom(rcl.uom_code,l.inventory_item_id) BASE_UOM_FK, nvl(ai.INVOICE_CURRENCY_CODE, nvl(h.TRANSACTIONAL_CURR_CODE, 'NA_EDW')) TRX_CURRENCY_FK, sob.currency_code BASE_CURRENCY_FK, EDW_LOOKUP_PKG.Lookup_Code_FK('SOL','ORDER_CATEGORY', upper(h.order_category_code) ) ORDER_CATEGORY_FK, EDW_LOOKUP_PKG.Lookup_Code_FK('SOO','ORDER_TYPE', upper(ordtyp.name) ) ORDER_TYPE_FK, decode(l.invoice_to_org_id, '', 'NA_EDW', l.invoice_to_org_id||'-' ||inst.instance_code||'-CUST_SITE_USE') BILL_TO_SITE_FK, decode(l.ship_to_org_id, '', 'NA_EDW', l.ship_to_org_id||'-' ||inst.instance_code||'-CUST_SITE_USE') SHIP_TO_SITE_FK, EDW_TIME_PKG.CAL_DAY_FK(h.BOOKED_DATE, fspa.SET_OF_BOOKS_ID,inst.instance_code) MONTH_BOOKED_FK, EDW_TIME_PKG.CAL_DAY_FK(h.BOOKED_DATE, fspa.SET_OF_BOOKS_ID,inst.instance_code) DATE_BOOKED_FK, EDW_TIME_PKG.CAL_DAY_FK(l.promise_date, fspa.SET_OF_BOOKS_ID,inst.instance_code) DATE_PROMISED_FK, EDW_TIME_PKG.CAL_DAY_FK(l.REQUEST_DATE, fspa.SET_OF_BOOKS_ID,inst.instance_code) DATE_REQUESTED_FK, EDW_TIME_PKG.CAL_DAY_FK(null, fspa.SET_OF_BOOKS_ID,inst.instance_code) DATE_SCHEDULED_FK, EDW_TIME_PKG.CAL_DAY_FK(null, fspa.SET_OF_BOOKS_ID,inst.instance_code) DATE_SHIPPED_FK, 'NA_EDW' LOCATOR_FK, EDW_LOOKUP_PKG.Lookup_Code_FK('SOS','ORDER_SOURCE', upper(pl.source_type_code) ) ORDER_SOURCE_FK, to_char(fspa.set_of_books_id)||'-'||inst.instance_code SET_OF_BOOKS_FK, 'NA_EDW' CAMPAIGN_INIT_FK, 'NA_EDW' CAMPAIGN_ACTL_FK, 'NA_EDW' MEDCHN_INIT_FK, 'NA_EDW' MEDCHN_ACTL_FK, 'NA_EDW' OFFER_HDR_FK, 'NA_EDW' OFFER_LINE_FK, 'NA_EDW' MARKET_SEGMENT_FK, 'NA_EDW' TARGET_SEGMENT_INIT_FK, 'NA_EDW' TARGET_SEGMENT_ACTL_FK, 'NA_EDW' CAMPAIGN_STATUS_ACTL_FK, 'NA_EDW' CAMPAIGN_STATUS_INIT_FK, EDW_TIME_PKG.CAL_DAY_FK(aid.accounting_date, fspa.SET_OF_BOOKS_ID,inst.instance_code) COGS_DATE_FK, pl.line_id ||'-'||inst.instance_code ORDER_LINE_ID, decode(rcl.interface_line_attribute3, '', 'NA_EDW',mp.organization_code ||'-'||inst.instance_code||'-PLNT' ) SHIP_INV_LOCATOR_FK, aid.accounting_date COGS_DATE, h.ordered_date ORDER_DATE, to_number(NULL) PROM_EARLY_COUNT, to_number(NULL) PROM_LATE_COUNT, to_number(NULL) REQ_EARLY_COUNT, to_number(NULL) REQ_LATE_COUNT, to_number(NULL) PROM_EARLY_VAL_G, to_number(NULL) PROM_LATE_VAL_G, to_number(NULL) REQ_EARLY_VAL_G, to_number(NULL) REQ_LATE_VAL_G, to_number(NULL) REQUEST_LEAD_TIME, to_number(NULL) PROMISE_LEAD_TIME, to_number(NULL) ORDER_LEAD_TIME, to_number(NULL) SHIPPED_QTY_B, to_number(NULL) RMA_QTY_B, nvl(rcl.quantity_invoiced,0) ICAP_QTY_B, nvl(aid.amount, 0) COGS_T, nvl(aid.base_amount, nvl(aid.amount,0)) COGS_B, 0 COGS_G, 0 RMA_VAL_T, 0 RMA_VAL_G, null LAST_UPDATE_DATE, to_number(NULL) COST_ELEMENT, aid.accts_pay_code_combination_id ACCOUNT, h.order_number ORDER_NUMBER, NULL WAYBILL_NUMBER, NULL LOT, NULL REVISION, NULL SERIAL_NUMBER, Null USER_ATTRIBUTE1, Null USER_ATTRIBUTE2, Null USER_ATTRIBUTE3, Null USER_ATTRIBUTE4, Null USER_ATTRIBUTE5, Null USER_ATTRIBUTE6, Null USER_ATTRIBUTE7, Null USER_ATTRIBUTE8, Null USER_ATTRIBUTE9, Null USER_ATTRIBUTE10, Null USER_ATTRIBUTE11, Null USER_ATTRIBUTE12, Null USER_ATTRIBUTE13, Null USER_ATTRIBUTE14, Null USER_ATTRIBUTE15, to_number(NULL) USER_MEASURE1, to_number(NULL) USER_MEASURE2, to_number(NULL) USER_MEASURE3, to_number(NULL) USER_MEASURE4, to_number(NULL) USER_MEASURE5, Null USER_FK1, Null USER_FK2, Null USER_FK3, Null USER_FK4, Null USER_FK5, Null OPERATION_CODE, to_number(NULL) DELIVERY_ID, edw_currency.get_rate(sob.currency_code, nvl(aid.accounting_date, aid.creation_date)) global_currency_rate from opi_edw_cogs_inc inc, ap_invoice_distributions_all aid, ap_invoices_all ai, ra_customer_trx_lines_all rcl, oe_order_lines_all l, oe_order_lines_all pl, oe_order_headers_all h, edw_local_instance inst, financials_system_params_all fspa, gl_sets_of_books sob, so_order_types_all ordtyp, mtl_parameters mp, mtl_material_transactions mmt where aid.invoice_id = inc.primary_key1 and aid.distribution_line_number = inc.primary_key2 and ai.invoice_id = aid.invoice_id and translate( lower(aid.REFERENCE_1), 'abcdefghijklmnopqrstuvwxyz_ -+0123456789', 'abcdefghijklmnopqrstuvwxyz_ -+') is null and ai.source = 'Intercompany' and ai.org_id = aid.org_id and rcl.CUSTOMER_TRX_LINE_ID = to_number(aid.REFERENCE_1) and aid.line_type_lookup_code = 'ITEM' and l.line_id = rcl.interface_line_attribute6 and pl.line_id = nvl(l.top_model_line_id, l.line_id) and pl.org_id = l.org_id and h.org_id = l.org_id and h.header_id = l.header_id and h.header_id = pl.header_id and l.line_category_code = 'ORDER' and pl.line_category_code = 'ORDER' and ordtyp.order_type_id = h.order_type_id and fspa.org_id = h.org_id and fspa.SET_OF_BOOKS_ID = sob.SET_OF_BOOKS_ID and mmt.transaction_id = rcl.interface_line_attribute7 and nvl(mmt.logical_transaction,-99) <> 1 and mp.organization_id(+) = rcl.interface_line_attribute3 and mp.process_enabled_flag <> 'Y'

View Text - HTML Formatted

SELECT 1 VIEW_ID
, INC.SEQ_ID SEQ_ID
, 'INV' || '-' || TO_CHAR(MMT.TRANSACTION_ID) || '-' || TO_CHAR(NVL(MTA.COST_ELEMENT_ID
, 999)) || '-' || TO_CHAR(L.LINE_ID) || '-' || INST.INSTANCE_CODE COGS_PK
, INST.INSTANCE_CODE INSTANCE_FK
, PL.INVENTORY_ITEM_ID||'-'||NVL(PL.SHIP_FROM_ORG_ID
, L.SHIP_FROM_ORG_ID) ||'-'||INST.INSTANCE_CODE||'-IORG' TOP_MODEL_ITEM_FK
, L.INVENTORY_ITEM_ID||'-'||MMT.ORGANIZATION_ID ||'-'||INST.INSTANCE_CODE||'-IORG' ITEM_ORG_FK
, OOD.OPERATING_UNIT ||'-'||INST.INSTANCE_CODE OPERATING_UNIT_FK
, MMT.ORGANIZATION_ID ||'-'||INST.INSTANCE_CODE INV_ORG_FK
, DECODE(H.SOLD_TO_ORG_ID
, NULL
, 'NA_EDW'
, H.SOLD_TO_ORG_ID ||'-'|| INST.INSTANCE_CODE ||'-CUST_ACCT-TPRT') CUSTOMER_FK
, DECODE(H.SALES_CHANNEL_CODE
, ''
, 'NA_EDW'
, H.SALES_CHANNEL_CODE ||'-'||INST.INSTANCE_CODE) SALES_CHANNEL_FK
, H.SALESREP_ID||'-'|| H.ORG_ID||'-'||INST.INSTANCE_CODE ||'-SALESREP-PERS' PRIM_SALES_REP_FK
, H.SALESREP_ID||'-'|| H.ORG_ID||'-'||INST.INSTANCE_CODE ||'-SALESREP-PERS' PRIM_SALESRESOURCE_FK
, EDW_GEOGRAPHY_PKG.CUSTOMER_SITE_LOCATION_FK( L.INVOICE_TO_ORG_ID
, INST.INSTANCE_CODE) BILL_TO_LOC_FK
, EDW_GEOGRAPHY_PKG.CUSTOMER_SITE_LOCATION_FK( L.SHIP_TO_ORG_ID
, INST.INSTANCE_CODE) SHIP_TO_LOC_FK
, DECODE(PL.PROJECT_ID
, ''
, 'NA_EDW'
, PL.PROJECT_ID ||'-' || INST.INSTANCE_CODE|| '-PJ-PRJ') PROJECT_FK
, DECODE(L.TASK_ID
, ''
, 'NA_EDW'
, L.TASK_ID ||'-' || INST.INSTANCE_CODE ) TASK_FK
, EDW_UTIL.GET_EDW_BASE_UOM(MMT.TRANSACTION_UOM
, L.INVENTORY_ITEM_ID) BASE_UOM_FK
, NVL(MMT.CURRENCY_CODE
, EDW_UTIL.GET_BASE_CURRENCY( MMT.ORGANIZATION_ID)) TRX_CURRENCY_FK
, EDW_UTIL.GET_BASE_CURRENCY(MMT.ORGANIZATION_ID) BASE_CURRENCY_FK
, EDW_LOOKUP_PKG.LOOKUP_CODE_FK('SOL'
, 'ORDER_CATEGORY'
, UPPER(H.ORDER_CATEGORY_CODE) ) ORDER_CATEGORY_FK
, EDW_LOOKUP_PKG.LOOKUP_CODE_FK('SOO'
, 'ORDER_TYPE'
, UPPER(ORDTYP.NAME) ) ORDER_TYPE_FK
, DECODE(L.INVOICE_TO_ORG_ID
, ''
, 'NA_EDW'
, L.INVOICE_TO_ORG_ID||'-' ||INST.INSTANCE_CODE||'-CUST_SITE_USE') BILL_TO_SITE_FK
, DECODE(L.SHIP_TO_ORG_ID
, ''
, 'NA_EDW'
, L.SHIP_TO_ORG_ID||'-' ||INST.INSTANCE_CODE||'-CUST_SITE_USE') SHIP_TO_SITE_FK
, EDW_TIME_PKG.CAL_DAY_FK(H.BOOKED_DATE
, FSPA.SET_OF_BOOKS_ID
, INST.INSTANCE_CODE) MONTH_BOOKED_FK
, EDW_TIME_PKG.CAL_DAY_FK(H.BOOKED_DATE
, FSPA.SET_OF_BOOKS_ID
, INST.INSTANCE_CODE) DATE_BOOKED_FK
, EDW_TIME_PKG.CAL_DAY_FK(L.PROMISE_DATE
, FSPA.SET_OF_BOOKS_ID
, INST.INSTANCE_CODE) DATE_PROMISED_FK
, EDW_TIME_PKG.CAL_DAY_FK(L.REQUEST_DATE
, FSPA.SET_OF_BOOKS_ID
, INST.INSTANCE_CODE) DATE_REQUESTED_FK
, EDW_TIME_PKG.CAL_DAY_FK(L.SCHEDULE_SHIP_DATE
, FSPA.SET_OF_BOOKS_ID
, INST.INSTANCE_CODE) DATE_SCHEDULED_FK
, EDW_TIME_PKG.CAL_DAY_FK(L.ACTUAL_SHIPMENT_DATE
, FSPA.SET_OF_BOOKS_ID
, INST.INSTANCE_CODE) DATE_SHIPPED_FK
, DECODE( MMT.LOCATOR_ID
, NULL
, DECODE(MMT.SUBINVENTORY_CODE
, NULL
, MP.ORGANIZATION_CODE ||'-'||INST.INSTANCE_CODE||'-PLNT'
, MMT.SUBINVENTORY_CODE || '-'||MP.ORGANIZATION_CODE||'-'|| INST.INSTANCE_CODE||'-SUBI')
, MMT.LOCATOR_ID||'-'||MP.ORGANIZATION_CODE||'-'||INST.INSTANCE_CODE) LOCATOR_FK
, EDW_LOOKUP_PKG.LOOKUP_CODE_FK('SOS'
, 'ORDER_SOURCE'
, UPPER(PL.SOURCE_TYPE_CODE) ) ORDER_SOURCE_FK
, TO_CHAR(FSPA.SET_OF_BOOKS_ID)||'-'||INST.INSTANCE_CODE SET_OF_BOOKS_FK
, 'NA_EDW' CAMPAIGN_INIT_FK
, 'NA_EDW' CAMPAIGN_ACTL_FK
, 'NA_EDW' MEDCHN_INIT_FK
, 'NA_EDW' MEDCHN_ACTL_FK
, 'NA_EDW' OFFER_HDR_FK
, 'NA_EDW' OFFER_LINE_FK
, 'NA_EDW' MARKET_SEGMENT_FK
, 'NA_EDW' TARGET_SEGMENT_INIT_FK
, 'NA_EDW' TARGET_SEGMENT_ACTL_FK
, 'NA_EDW' CAMPAIGN_STATUS_ACTL_FK
, 'NA_EDW' CAMPAIGN_STATUS_INIT_FK
, EDW_TIME_PKG.CAL_DAY_FK(MTA.TRANSACTION_DATE
, FSPA.SET_OF_BOOKS_ID
, INST.INSTANCE_CODE) COGS_DATE_FK
, PL.LINE_ID || '-' || INST.INSTANCE_CODE ORDER_LINE_ID
, MP.ORGANIZATION_CODE||'-'||INST.INSTANCE_CODE||'-PLNT' SHIP_INV_LOCATOR_FK
, MTA.TRANSACTION_DATE COGS_DATE
, H.ORDERED_DATE ORDER_DATE
, DECODE( DECODE(SIGN(L.PROMISE_DATE - NVL( L.ACTUAL_SHIPMENT_DATE
, L.PROMISE_DATE +1))
, 1
, 1
, 0
, 1
, 0)
, 1
, DECODE(NVL(MTA.COST_ELEMENT_ID
, -1)
, 1
, 1
, -1
, 1
, 0) / EDW_UTIL.GET_LINE_DETAIL_COUNT(L.LINE_ID)
, 0) PROM_EARLY_COUNT
, DECODE(SIGN(L.PROMISE_DATE - NVL(L.ACTUAL_SHIPMENT_DATE
, L.PROMISE_DATE +1))
, -1
, DECODE(NVL(MTA.COST_ELEMENT_ID
, -1)
, 1
, 1
, -1
, 1
, 0) / EDW_UTIL.GET_LINE_DETAIL_COUNT(L.LINE_ID)
, 0) PROM_LATE_COUNT
, DECODE( DECODE(SIGN(L.REQUEST_DATE - NVL(L.ACTUAL_SHIPMENT_DATE
, L.REQUEST_DATE +1))
, 1
, 1
, 0
, 1
, 0)
, 1
, DECODE(NVL(MTA.COST_ELEMENT_ID
, -1)
, 1
, 1
, -1
, 1
, 0) / EDW_UTIL.GET_LINE_DETAIL_COUNT(L.LINE_ID)
, 0) REQ_EARLY_COUNT
, DECODE(SIGN(L.REQUEST_DATE-NVL(L.ACTUAL_SHIPMENT_DATE
, L.REQUEST_DATE +1))
, -1
, DECODE(NVL(MTA.COST_ELEMENT_ID
, -1)
, 1
, 1
, -1
, 1
, 0) / EDW_UTIL.GET_LINE_DETAIL_COUNT(L.LINE_ID)
, 0) REQ_LATE_COUNT
, DECODE(SIGN(L.PROMISE_DATE-NVL(L.ACTUAL_SHIPMENT_DATE
, L.PROMISE_DATE+1))
, 1
, MTA.BASE_TRANSACTION_VALUE * (-1)
, 0
, MTA.BASE_TRANSACTION_VALUE * (-1)
, 0) PROM_EARLY_VAL_G
, DECODE(SIGN(L.PROMISE_DATE-NVL(L.ACTUAL_SHIPMENT_DATE
, L.PROMISE_DATE +1))
, -1
, MTA.BASE_TRANSACTION_VALUE * (-1)
, 0) PROM_LATE_VAL_G
, DECODE(SIGN(L.REQUEST_DATE-NVL(L.ACTUAL_SHIPMENT_DATE
, L.REQUEST_DATE+1))
, 1
, MTA.BASE_TRANSACTION_VALUE * (-1)
, 0
, MTA.BASE_TRANSACTION_VALUE * (-1)
, 0) REQ_EARLY_VAL_G
, DECODE(SIGN(L.REQUEST_DATE-NVL(L.ACTUAL_SHIPMENT_DATE
, L.REQUEST_DATE+1))
, -1
, MTA.BASE_TRANSACTION_VALUE * (-1)
, 0) REQ_LATE_VAL_G
, DECODE( DECODE(NVL(MTA.COST_ELEMENT_ID
, -1)
, 1
, 1
, -1
, 1
, 0)
, 1
, MONTHS_BETWEEN(L.ACTUAL_SHIPMENT_DATE
, L.REQUEST_DATE) * 31
, 0)/ EDW_UTIL.GET_LINE_DETAIL_COUNT(L.LINE_ID) REQUEST_LEAD_TIME
, DECODE( DECODE(NVL(MTA.COST_ELEMENT_ID
, -1)
, 1
, 1
, -1
, 1
, 0)
, 1
, MONTHS_BETWEEN(L.ACTUAL_SHIPMENT_DATE
, L.PROMISE_DATE) * 31
, 0)/ EDW_UTIL.GET_LINE_DETAIL_COUNT(L.LINE_ID) PROMISE_LEAD_TIME
, DECODE( DECODE(NVL(MTA.COST_ELEMENT_ID
, -1)
, 1
, 1
, -1
, 1
, 0)
, 1
, MONTHS_BETWEEN(L.ACTUAL_SHIPMENT_DATE
, H.BOOKED_DATE) * 31
, 0)/EDW_UTIL.GET_LINE_DETAIL_COUNT(L.LINE_ID) ORDER_LEAD_TIME
, DECODE(DECODE(PL.ATO_LINE_ID
, NULL
, 'N'
, 'Y')
, 'N'
, DECODE(MMT.INVENTORY_ITEM_ID
, PL.INVENTORY_ITEM_ID
, DECODE(NVL(MTA.COST_ELEMENT_ID
, -1)
, 1
, ABS(MMT.PRIMARY_QUANTITY) * /* UOM BASED ON THE REPORTING OU
, NOT THE OE OU */ EDW_UTIL.GET_UOM_CONV_RATE(MMT.TRANSACTION_UOM
, MMT.INVENTORY_ITEM_ID)
, -1
, ABS(MMT.PRIMARY_QUANTITY) * EDW_UTIL.GET_UOM_CONV_RATE(MMT.TRANSACTION_UOM
, MMT.INVENTORY_ITEM_ID)
, 0)
, DECODE(PL.ITEM_TYPE_CODE
, 'MODEL'
, DECODE(NVL(MTA.COST_ELEMENT_ID
, 1)
, 1
, NVL(PL.SHIPPED_QUANTITY
, 0) / EDW_UTIL.GET_PTO_MMT_COUNT(PL.LINE_ID) * EDW_UTIL.GET_UOM_CONV_RATE(PL.ORDER_QUANTITY_UOM
, PL.INVENTORY_ITEM_ID)
, 0)
, 'KIT'
, DECODE(NVL(MTA.COST_ELEMENT_ID
, 1)
, 1
, NVL(PL.SHIPPED_QUANTITY
, 0) / EDW_UTIL.GET_PTO_MMT_COUNT(PL.LINE_ID) * EDW_UTIL.GET_UOM_CONV_RATE(PL.ORDER_QUANTITY_UOM
, PL.INVENTORY_ITEM_ID)
, 0)
, 0) )
, 'Y'
, DECODE(PL.ITEM_TYPE_CODE
, 'MODEL'
, /* CASE OF ATO MODEL */ DECODE(NVL(MTA.COST_ELEMENT_ID
, -1)
, 1
, ABS(MMT.PRIMARY_QUANTITY) * EDW_UTIL.GET_UOM_CONV_RATE(MMT.TRANSACTION_UOM
, MMT.INVENTORY_ITEM_ID)
, -1
, ABS(MMT.PRIMARY_QUANTITY) * EDW_UTIL.GET_UOM_CONV_RATE(MMT.TRANSACTION_UOM
, MMT.INVENTORY_ITEM_ID)
, 0)
, DECODE(MMT.INVENTORY_ITEM_ID
, PL.INVENTORY_ITEM_ID
, DECODE(NVL(MTA.COST_ELEMENT_ID
, -1)
, 1
, ABS(MMT.PRIMARY_QUANTITY) * EDW_UTIL.GET_UOM_CONV_RATE(MMT.TRANSACTION_UOM
, MMT.INVENTORY_ITEM_ID)
, -1
, ABS(MMT.PRIMARY_QUANTITY) * EDW_UTIL.GET_UOM_CONV_RATE(MMT.TRANSACTION_UOM
, MMT.INVENTORY_ITEM_ID)
, 0)
, 0) )
, DECODE(MMT.INVENTORY_ITEM_ID
, PL.INVENTORY_ITEM_ID
, DECODE(NVL(MTA.COST_ELEMENT_ID
, -1)
, 1
, ABS(MMT.PRIMARY_QUANTITY) * EDW_UTIL.GET_UOM_CONV_RATE(MMT.TRANSACTION_UOM
, MMT.INVENTORY_ITEM_ID)
, -1
, ABS(MMT.PRIMARY_QUANTITY) * EDW_UTIL.GET_UOM_CONV_RATE(MMT.TRANSACTION_UOM
, MMT.INVENTORY_ITEM_ID)
, 0)
, 0) ) SHIPPED_QTY_B
, TO_NUMBER(NULL) RMA_QTY_B
, TO_NUMBER(NULL) ICAP_QTY_B
, NVL(MTA.TRANSACTION_VALUE
, MTA.BASE_TRANSACTION_VALUE) COGS_T
, (MTA.BASE_TRANSACTION_VALUE) COGS_B
, 0 COGS_G
, 0 RMA_VAL_T
, 0 RMA_VAL_G
, NULL LAST_UPDATE_DATE
, MTA.COST_ELEMENT_ID COST_ELEMENT
, MTA.REFERENCE_ACCOUNT ACCOUNT
, H.ORDER_NUMBER ORDER_NUMBER
, WND.WAYBILL WAYBILL_NUMBER
, WDD.LOT_NUMBER LOT
, WDD.REVISION REVISION
, WDD.SERIAL_NUMBER SERIAL_NUMBER
, NULL USER_ATTRIBUTE1
, NULL USER_ATTRIBUTE2
, NULL USER_ATTRIBUTE3
, NULL USER_ATTRIBUTE4
, NULL USER_ATTRIBUTE5
, NULL USER_ATTRIBUTE6
, NULL USER_ATTRIBUTE7
, NULL USER_ATTRIBUTE8
, NULL USER_ATTRIBUTE9
, NULL USER_ATTRIBUTE10
, NULL USER_ATTRIBUTE11
, NULL USER_ATTRIBUTE12
, NULL USER_ATTRIBUTE13
, NULL USER_ATTRIBUTE14
, NULL USER_ATTRIBUTE15
, TO_NUMBER(NULL) USER_MEASURE1
, TO_NUMBER(NULL) USER_MEASURE2
, TO_NUMBER(NULL) USER_MEASURE3
, TO_NUMBER(NULL) USER_MEASURE4
, TO_NUMBER(NULL) USER_MEASURE5
, NULL USER_FK1
, NULL USER_FK2
, NULL USER_FK3
, NULL USER_FK4
, NULL USER_FK5
, NULL OPERATION_CODE
, WDA.DELIVERY_ID DELIVERY_ID
, EDW_CURRENCY.GET_RATE( EDW_UTIL.GET_BASE_CURRENCY(MMT.ORGANIZATION_ID)
, NVL(MTA.TRANSACTION_DATE
, MTA.CREATION_DATE)) GLOBAL_CURRENCY_RATE
FROM OPI_EDW_COGS_INC INC
, MTL_MATERIAL_TRANSACTIONS MMT
, MTL_TRANSACTION_ACCOUNTS MTA
, OE_ORDER_LINES_ALL L
, OE_ORDER_LINES_ALL PL
, OE_ORDER_HEADERS_ALL H
, WSH_DELIVERY_DETAILS WDD
, WSH_DELIVERY_ASSIGNMENTS WDA
, WSH_NEW_DELIVERIES WND
, EDW_LOCAL_INSTANCE INST
, SO_ORDER_TYPES_ALL ORDTYP
, FINANCIALS_SYSTEM_PARAMS_ALL FSPA
, (SELECT HOU.ORGANIZATION_ID ORGANIZATION_ID
, DECODE(FPG.MULTI_ORG_FLAG
, 'Y'
, DECODE(HOI2.ORG_INFORMATION_CONTEXT
, 'ACCOUNTING INFORMATION'
, TO_NUMBER(HOI2.ORG_INFORMATION3)
, TO_NUMBER(NULL))
, TO_NUMBER(NULL)) OPERATING_UNIT
FROM HR_ORGANIZATION_UNITS HOU
, HR_ORGANIZATION_INFORMATION HOI2
, FND_PRODUCT_GROUPS FPG
WHERE HOU.ORGANIZATION_ID = HOI2.ORGANIZATION_ID
AND ( HOI2.ORG_INFORMATION_CONTEXT || '') ='ACCOUNTING INFORMATION' ) OOD
, MTL_PARAMETERS MP
WHERE MMT.TRANSACTION_ID = INC.PRIMARY_KEY1
AND ( (MMT.TRANSACTION_SOURCE_TYPE_ID = 2
AND MTA.TRANSACTION_SOURCE_TYPE_ID = 2) OR (MMT.TRANSACTION_SOURCE_TYPE_ID = 13
AND MMT.TRANSACTION_ACTION_ID = 9
AND MTA.TRANSACTION_SOURCE_TYPE_ID = 13) )
AND MMT.TRANSACTION_ID = MTA.TRANSACTION_ID
AND MTA.ACCOUNTING_LINE_TYPE IN (2
, 35)
AND ORDTYP.ORDER_TYPE_ID = H.ORDER_TYPE_ID
AND PL.ORG_ID = L.ORG_ID
AND H.ORG_ID = L.ORG_ID
AND L.LINE_ID = MMT.TRX_SOURCE_LINE_ID
AND L.LINE_CATEGORY_CODE = 'ORDER'
AND PL.LINE_CATEGORY_CODE = 'ORDER'
AND PL.LINE_ID = NVL(L.TOP_MODEL_LINE_ID
, L.LINE_ID)
AND H.HEADER_ID = L.HEADER_ID
AND H.HEADER_ID = PL.HEADER_ID
AND MP.ORGANIZATION_ID = MMT.ORGANIZATION_ID
AND OOD.ORGANIZATION_ID = MMT.ORGANIZATION_ID
AND FSPA.ORG_ID = OOD.OPERATING_UNIT
AND WDD.DELIVERY_DETAIL_ID(+) = MMT.PICKING_LINE_ID
AND WDA.DELIVERY_ID = WND.DELIVERY_ID (+)
AND WDA.DELIVERY_DETAIL_ID (+) = WDD.DELIVERY_DETAIL_ID UNION ALL SELECT 2 VIEW_ID
, INC.SEQ_ID SEQ_ID
, 'RMA' || '-' || TO_CHAR(MMT.TRANSACTION_ID) || '-' || TO_CHAR(NVL(MTA.COST_ELEMENT_ID
, 999)) || '-' || INST.INSTANCE_CODE COGS_PK
, INST.INSTANCE_CODE INSTANCE_FK
, NVL(PL.INVENTORY_ITEM_ID
, NVL(CL.INVENTORY_ITEM_ID
, L.INVENTORY_ITEM_ID))||'-'|| NVL(PL.SHIP_FROM_ORG_ID
, NVL(CL.SHIP_FROM_ORG_ID
, L.SHIP_FROM_ORG_ID)) ||'-'||INST.INSTANCE_CODE||'-IORG' TOP_MODEL_ITEM_FK
, NVL(CL.INVENTORY_ITEM_ID
, L.INVENTORY_ITEM_ID)||'-'|| NVL(CL.SHIP_FROM_ORG_ID
, L.SHIP_FROM_ORG_ID) ||'-'||INST.INSTANCE_CODE||'-IORG' ITEM_ORG_FK
, OOD.OPERATING_UNIT ||'-'||INST.INSTANCE_CODE OPERATING_UNIT_FK
, MMT.ORGANIZATION_ID ||'-'||INST.INSTANCE_CODE INV_ORG_FK
, DECODE(H.SOLD_TO_ORG_ID
, NULL
, 'NA_EDW'
, H.SOLD_TO_ORG_ID ||'-'|| INST.INSTANCE_CODE ||'-CUST_ACCT-TPRT') CUSTOMER_FK
, DECODE(H.SALES_CHANNEL_CODE
, ''
, 'NA_EDW'
, H.SALES_CHANNEL_CODE ||'-'||INST.INSTANCE_CODE) SALES_CHANNEL_FK
, H.SALESREP_ID||'-'|| H.ORG_ID||'-'||INST.INSTANCE_CODE ||'-SALESREP-PERS' PRIM_SALES_REP_FK
, H.SALESREP_ID||'-'|| H.ORG_ID||'-'||INST.INSTANCE_CODE ||'-SALESREP-PERS' PRIM_SALESRESOURCE_FK
, DECODE(L.LINK_TO_LINE_ID
, ''
, DECODE(L.INVOICE_TO_ORG_ID
, ''
, 'NA_EDW'
, EDW_GEOGRAPHY_PKG.CUSTOMER_SITE_LOCATION_FK( L.INVOICE_TO_ORG_ID
, INST.INSTANCE_CODE))
, DECODE(PL.INVOICE_TO_ORG_ID
, ''
, DECODE(CL.INVOICE_TO_ORG_ID
, ''
, DECODE(L.INVOICE_TO_ORG_ID
, ''
, 'NA_EDW'
, EDW_GEOGRAPHY_PKG.CUSTOMER_SITE_LOCATION_FK( L.INVOICE_TO_ORG_ID
, INST.INSTANCE_CODE))
, EDW_GEOGRAPHY_PKG.CUSTOMER_SITE_LOCATION_FK( L.INVOICE_TO_ORG_ID
, INST.INSTANCE_CODE))
, EDW_GEOGRAPHY_PKG.CUSTOMER_SITE_LOCATION_FK(L.INVOICE_TO_ORG_ID
, INST.INSTANCE_CODE)) ) BILL_TO_LOC_FK
, DECODE(L.LINK_TO_LINE_ID
, ''
, DECODE(L.SHIP_TO_ORG_ID
, ''
, 'NA_EDW'
, EDW_GEOGRAPHY_PKG.CUSTOMER_SITE_LOCATION_FK( L.SHIP_TO_ORG_ID
, INST.INSTANCE_CODE))
, DECODE(PL.SHIP_TO_ORG_ID
, ''
, DECODE(CL.SHIP_TO_ORG_ID
, ''
, DECODE(L.SHIP_TO_ORG_ID
, ''
, 'NA_EDW'
, EDW_GEOGRAPHY_PKG.CUSTOMER_SITE_LOCATION_FK( L.SHIP_TO_ORG_ID
, INST.INSTANCE_CODE))
, EDW_GEOGRAPHY_PKG.CUSTOMER_SITE_LOCATION_FK( CL.SHIP_TO_ORG_ID
, INST.INSTANCE_CODE))
, EDW_GEOGRAPHY_PKG.CUSTOMER_SITE_LOCATION_FK(PL.SHIP_TO_ORG_ID
, INST.INSTANCE_CODE)) ) SHIP_TO_LOC_FK
, DECODE(L.LINK_TO_LINE_ID
, ''
, DECODE(L.PROJECT_ID
, ''
, 'NA_EDW'
, L.PROJECT_ID||'-'|| INST.INSTANCE_CODE|| '-PJ-PRJ')
, DECODE(PL.PROJECT_ID
, ''
, DECODE(CL.PROJECT_ID
, ''
, DECODE(L.PROJECT_ID
, ''
, 'NA_EDW'
, L.PROJECT_ID||'-'||INST.INSTANCE_CODE|| '-PJ-PRJ')
, CL.PROJECT_ID||'-'|| INST.INSTANCE_CODE|| '-PJ-PRJ')
, PL.PROJECT_ID||'-'|| INST.INSTANCE_CODE|| '-PJ-PRJ')) PROJECT_FK
, DECODE(L.LINK_TO_LINE_ID
, ''
, DECODE(L.TASK_ID
, ''
, 'NA_EDW'
, L.TASK_ID ||'-'||INST.INSTANCE_CODE)
, DECODE(PL.TASK_ID
, ''
, DECODE(CL.TASK_ID
, ''
, DECODE(L.TASK_ID
, ''
, 'NA_EDW'
, L.TASK_ID||'-'||INST.INSTANCE_CODE )
, CL.TASK_ID ||'-'|| INST.INSTANCE_CODE)
, PL.TASK_ID|| '-'|| INST.INSTANCE_CODE)) TASK_FK
, EDW_UTIL.GET_EDW_BASE_UOM(MMT.TRANSACTION_UOM
, L.INVENTORY_ITEM_ID) BASE_UOM_FK
, NVL(MMT.CURRENCY_CODE
, EDW_UTIL.GET_BASE_CURRENCY( MMT.ORGANIZATION_ID)) TRX_CURRENCY_FK
, EDW_UTIL.GET_BASE_CURRENCY(MMT.ORGANIZATION_ID) BASE_CURRENCY_FK
, EDW_LOOKUP_PKG.LOOKUP_CODE_FK('SOL'
, 'ORDER_CATEGORY'
, UPPER(H.ORDER_CATEGORY_CODE) ) ORDER_CATEGORY_FK
, EDW_LOOKUP_PKG.LOOKUP_CODE_FK('SOO'
, 'ORDER_TYPE'
, UPPER(ORDTYP.NAME) ) ORDER_TYPE_FK
, DECODE(L.INVOICE_TO_ORG_ID
, ''
, 'NA_EDW'
, L.INVOICE_TO_ORG_ID||'-' ||INST.INSTANCE_CODE||'-CUST_SITE_USE') BILL_TO_SITE_FK
, DECODE(L.LINK_TO_LINE_ID
, ''
, DECODE(L.SHIP_TO_ORG_ID
, ''
, 'NA_EDW'
, L.SHIP_TO_ORG_ID||'-'||INST.INSTANCE_CODE||'-CUST_SITE_USE')
, DECODE(PL.SHIP_TO_ORG_ID
, ''
, DECODE(CL.SHIP_TO_ORG_ID
, ''
, DECODE(L.SHIP_TO_ORG_ID
, ''
, 'NA_EDW'
, L.SHIP_TO_ORG_ID ||'-' ||INST.INSTANCE_CODE||'-CUST_SITE_USE')
, CL.SHIP_TO_ORG_ID||'-'||INST.INSTANCE_CODE||'-CUST_SITE_USE')
, PL.SHIP_TO_ORG_ID||'-'||INST.INSTANCE_CODE||'-CUST_SITE_USE')) SHIP_TO_SITE_FK
, EDW_TIME_PKG.CAL_DAY_FK(H.BOOKED_DATE
, FSPA.SET_OF_BOOKS_ID
, INST.INSTANCE_CODE) MONTH_BOOKED_FK
, EDW_TIME_PKG.CAL_DAY_FK(H.BOOKED_DATE
, FSPA.SET_OF_BOOKS_ID
, INST.INSTANCE_CODE) DATE_BOOKED_FK
, EDW_TIME_PKG.CAL_DAY_FK(L.PROMISE_DATE
, FSPA.SET_OF_BOOKS_ID
, INST.INSTANCE_CODE) DATE_PROMISED_FK
, EDW_TIME_PKG.CAL_DAY_FK(L.REQUEST_DATE
, FSPA.SET_OF_BOOKS_ID
, INST.INSTANCE_CODE) DATE_REQUESTED_FK
, EDW_TIME_PKG.CAL_DAY_FK(NULL
, FSPA.SET_OF_BOOKS_ID
, INST.INSTANCE_CODE) DATE_SCHEDULED_FK
, EDW_TIME_PKG.CAL_DAY_FK(NULL
, FSPA.SET_OF_BOOKS_ID
, INST.INSTANCE_CODE) DATE_SHIPPED_FK
, DECODE( MMT.LOCATOR_ID
, NULL
, DECODE(MMT.SUBINVENTORY_CODE
, NULL
, MP.ORGANIZATION_CODE ||'-'||INST.INSTANCE_CODE||'-PLNT'
, MMT.SUBINVENTORY_CODE ||'-'|| MP.ORGANIZATION_CODE||'-'|| INST.INSTANCE_CODE||'-SUBI')
, MMT.LOCATOR_ID||'-'||MP.ORGANIZATION_CODE||'-'||INST.INSTANCE_CODE) LOCATOR_FK
, EDW_LOOKUP_PKG.LOOKUP_CODE_FK('SOS'
, 'ORDER_SOURCE'
, UPPER(PL.SOURCE_TYPE_CODE) ) ORDER_SOURCE_FK
, TO_CHAR(FSPA.SET_OF_BOOKS_ID)||'-'||INST.INSTANCE_CODE SET_OF_BOOKS_FK
, 'NA_EDW' CAMPAIGN_INIT_FK
, 'NA_EDW' CAMPAIGN_ACTL_FK
, 'NA_EDW' MEDCHN_INIT_FK
, 'NA_EDW' MEDCHN_ACTL_FK
, 'NA_EDW' OFFER_HDR_FK
, 'NA_EDW' OFFER_LINE_FK
, 'NA_EDW' MARKET_SEGMENT_FK
, 'NA_EDW' TARGET_SEGMENT_INIT_FK
, 'NA_EDW' TARGET_SEGMENT_ACTL_FK
, 'NA_EDW' CAMPAIGN_STATUS_ACTL_FK
, 'NA_EDW' CAMPAIGN_STATUS_INIT_FK
, EDW_TIME_PKG.CAL_DAY_FK(MTA.TRANSACTION_DATE
, FSPA.SET_OF_BOOKS_ID
, INST.INSTANCE_CODE) COGS_DATE_FK
, PL.LINE_ID ||'-' || INST.INSTANCE_CODE ORDER_LINE_ID
, MP.ORGANIZATION_CODE||'-'||INST.INSTANCE_CODE ||'-PLNT' SHIP_INV_LOCATOR_FK
, MTA.TRANSACTION_DATE COGS_DATE
, H.ORDERED_DATE ORDER_DATE
, TO_NUMBER(NULL) PROM_EARLY_COUNT
, TO_NUMBER(NULL) PROM_LATE_COUNT
, TO_NUMBER(NULL) REQ_EARLY_COUNT
, TO_NUMBER(NULL) REQ_LATE_COUNT
, TO_NUMBER(NULL) PROM_EARLY_VAL_G
, TO_NUMBER(NULL) PROM_LATE_VAL_G
, TO_NUMBER(NULL) REQ_EARLY_VAL_G
, TO_NUMBER(NULL) REQ_LATE_VAL_G
, TO_NUMBER(NULL) REQUEST_LEAD_TIME
, TO_NUMBER(NULL) PROMISE_LEAD_TIME
, TO_NUMBER(NULL) ORDER_LEAD_TIME
, TO_NUMBER(NULL) SHIPPED_QTY_B
, DECODE(PL.INVENTORY_ITEM_ID
, ''
, DECODE(NVL(MTA.COST_ELEMENT_ID
, -1)
, 1
, MMT.TRANSACTION_QUANTITY * EDW_UTIL.GET_UOM_CONV_RATE(MMT.TRANSACTION_UOM
, MMT.INVENTORY_ITEM_ID)
, -1
, MMT.TRANSACTION_QUANTITY * EDW_UTIL.GET_UOM_CONV_RATE(MMT.TRANSACTION_UOM
, MMT.INVENTORY_ITEM_ID)
, 0) * (-1)
, DECODE(DECODE(PL.ATO_LINE_ID
, NULL
, 'N'
, 'Y')
, 'N'
, DECODE(MMT.INVENTORY_ITEM_ID
, PL.INVENTORY_ITEM_ID
, DECODE(NVL(MTA.COST_ELEMENT_ID
, -1)
, 1
, MMT.TRANSACTION_QUANTITY * EDW_UTIL.GET_UOM_CONV_RATE(MMT.TRANSACTION_UOM
, MMT.INVENTORY_ITEM_ID)
, -1
, MMT.TRANSACTION_QUANTITY * EDW_UTIL.GET_UOM_CONV_RATE(MMT.TRANSACTION_UOM
, MMT.INVENTORY_ITEM_ID)
, 0) * (-1)
, DECODE(PL.ITEM_TYPE_CODE
, 'MODEL'
, DECODE(PL.INVENTORY_ITEM_ID
, CL.INVENTORY_ITEM_ID
, DECODE(NVL(MTA.COST_ELEMENT_ID
, 1)
, 1
, MMT.TRANSACTION_QUANTITY * EDW_UTIL.GET_UOM_CONV_RATE(MMT.TRANSACTION_UOM
, MMT.INVENTORY_ITEM_ID)
, 0)
, 0)
, 'KIT'
, DECODE(PL.INVENTORY_ITEM_ID
, CL.INVENTORY_ITEM_ID
, DECODE(NVL(MTA.COST_ELEMENT_ID
, 1)
, 1
, MMT.TRANSACTION_QUANTITY * EDW_UTIL.GET_UOM_CONV_RATE(MMT.TRANSACTION_UOM
, MMT.INVENTORY_ITEM_ID)
, 0)
, 0)
, 0) )
, 'Y'
, DECODE(PL.ITEM_TYPE_CODE
, 'MODEL'
, DECODE(NVL(MTA.COST_ELEMENT_ID
, -1)
, 1
, MMT.TRANSACTION_QUANTITY * EDW_UTIL.GET_UOM_CONV_RATE(MMT.TRANSACTION_UOM
, MMT.INVENTORY_ITEM_ID)
, -1
, MMT.TRANSACTION_QUANTITY * EDW_UTIL.GET_UOM_CONV_RATE(MMT.TRANSACTION_UOM
, MMT.INVENTORY_ITEM_ID)
, 0) * (-1)
, DECODE(MMT.INVENTORY_ITEM_ID
, PL.INVENTORY_ITEM_ID
, DECODE(NVL(MTA.COST_ELEMENT_ID
, -1)
, 1
, MMT.TRANSACTION_QUANTITY * EDW_UTIL.GET_UOM_CONV_RATE(MMT.TRANSACTION_UOM
, MMT.INVENTORY_ITEM_ID)
, -1
, MMT.TRANSACTION_QUANTITY * EDW_UTIL.GET_UOM_CONV_RATE(MMT.TRANSACTION_UOM
, MMT.INVENTORY_ITEM_ID)
, 0) * (-1)
, 0) )
, DECODE(MMT.INVENTORY_ITEM_ID
, PL.INVENTORY_ITEM_ID
, DECODE(NVL(MTA.COST_ELEMENT_ID
, -1)
, 1
, MMT.TRANSACTION_QUANTITY * EDW_UTIL.GET_UOM_CONV_RATE(MMT.TRANSACTION_UOM
, MMT.INVENTORY_ITEM_ID)
, -1
, MMT.TRANSACTION_QUANTITY * EDW_UTIL.GET_UOM_CONV_RATE(MMT.TRANSACTION_UOM
, MMT.INVENTORY_ITEM_ID)
, 0) * (-1)
, 0) ) ) RMA_QTY_B
, TO_NUMBER(NULL) ICAP_QTY_B
, NVL(MTA.TRANSACTION_VALUE
, MTA.BASE_TRANSACTION_VALUE) COGS_T
, MTA.BASE_TRANSACTION_VALUE COGS_B
, 0 COGS_G
, NVL(MTA.TRANSACTION_VALUE
, MTA.BASE_TRANSACTION_VALUE) RMA_VAL_T
, 0 RMA_VAL_G
, NULL LAST_UPDATE_DATE
, MTA.COST_ELEMENT_ID COST_ELEMENT
, MTA.REFERENCE_ACCOUNT ACCOUNT
, H.ORDER_NUMBER ORDER_NUMBER
, NULL WAYBILL_NUMBER
, NULL LOT
, NULL REVISION
, NULL SERIAL_NUMBER
, NULL USER_ATTRIBUTE1
, NULL USER_ATTRIBUTE2
, NULL USER_ATTRIBUTE3
, NULL USER_ATTRIBUTE4
, NULL USER_ATTRIBUTE5
, NULL USER_ATTRIBUTE6
, NULL USER_ATTRIBUTE7
, NULL USER_ATTRIBUTE8
, NULL USER_ATTRIBUTE9
, NULL USER_ATTRIBUTE10
, NULL USER_ATTRIBUTE11
, NULL USER_ATTRIBUTE12
, NULL USER_ATTRIBUTE13
, NULL USER_ATTRIBUTE14
, NULL USER_ATTRIBUTE15
, TO_NUMBER(NULL) USER_MEASURE1
, TO_NUMBER(NULL) USER_MEASURE2
, TO_NUMBER(NULL) USER_MEASURE3
, TO_NUMBER(NULL) USER_MEASURE4
, TO_NUMBER(NULL) USER_MEASURE5
, NULL USER_FK1
, NULL USER_FK2
, NULL USER_FK3
, NULL USER_FK4
, NULL USER_FK5
, NULL OPERATION_CODE
, TO_NUMBER(NULL) DELIVERY_ID
, EDW_CURRENCY.GET_RATE( EDW_UTIL.GET_BASE_CURRENCY(MMT.ORGANIZATION_ID)
, NVL(MTA.TRANSACTION_DATE
, MTA.CREATION_DATE)) GLOBAL_CURRENCY_RATE
FROM OPI_EDW_COGS_INC INC
, MTL_MATERIAL_TRANSACTIONS MMT
, MTL_TRANSACTION_ACCOUNTS MTA
, OE_ORDER_LINES_ALL L
, OE_ORDER_LINES_ALL CL
, OE_ORDER_LINES_ALL PL
, OE_ORDER_HEADERS_ALL H
, EDW_LOCAL_INSTANCE INST
, SO_ORDER_TYPES_ALL ORDTYP
, FINANCIALS_SYSTEM_PARAMS_ALL FSPA
, (SELECT HOU.ORGANIZATION_ID ORGANIZATION_ID
, DECODE(FPG.MULTI_ORG_FLAG
, 'Y'
, DECODE(HOI2.ORG_INFORMATION_CONTEXT
, 'ACCOUNTING INFORMATION'
, TO_NUMBER(HOI2.ORG_INFORMATION3)
, TO_NUMBER(NULL))
, TO_NUMBER(NULL)) OPERATING_UNIT
FROM HR_ORGANIZATION_UNITS HOU
, HR_ORGANIZATION_INFORMATION HOI2
, FND_PRODUCT_GROUPS FPG
WHERE HOU.ORGANIZATION_ID = HOI2.ORGANIZATION_ID
AND ( HOI2.ORG_INFORMATION_CONTEXT || '') ='ACCOUNTING INFORMATION' ) OOD
, MTL_PARAMETERS MP
WHERE MMT.TRANSACTION_ID = INC.PRIMARY_KEY1
AND ( (MMT.TRANSACTION_SOURCE_TYPE_ID = 12
AND MTA.TRANSACTION_SOURCE_TYPE_ID = 12) OR (MMT.TRANSACTION_SOURCE_TYPE_ID = 13
AND MMT.TRANSACTION_ACTION_ID = 14
AND MTA.TRANSACTION_SOURCE_TYPE_ID = 13) )
AND MMT.TRANSACTION_ID = MTA.TRANSACTION_ID
AND MTA.ACCOUNTING_LINE_TYPE IN (2
, 35)
AND ORDTYP.ORDER_TYPE_ID = H.ORDER_TYPE_ID
AND H.ORG_ID = L.ORG_ID
AND L.LINE_ID = MMT.TRX_SOURCE_LINE_ID
AND L.LINE_CATEGORY_CODE = 'RETURN'
AND CL.LINE_ID (+) = L.LINK_TO_LINE_ID
AND PL.LINE_ID (+) = NVL(CL.TOP_MODEL_LINE_ID
, CL.LINE_ID)
AND H.HEADER_ID = L.HEADER_ID
AND MP.ORGANIZATION_ID = MMT.ORGANIZATION_ID
AND OOD.ORGANIZATION_ID = MMT.ORGANIZATION_ID
AND FSPA.ORG_ID = OOD.OPERATING_UNIT UNION ALL SELECT 3 VIEW_ID
, INC.SEQ_ID SEQ_ID
, 'ICI' || '-' || TO_CHAR(AID.DISTRIBUTION_LINE_NUMBER) || '-' || TO_CHAR(AID.INVOICE_ID) || '-' || INST.INSTANCE_CODE COGS_PK
, INST.INSTANCE_CODE INSTANCE_FK
, PL.INVENTORY_ITEM_ID||'-'||NVL(PL.SHIP_FROM_ORG_ID
, L.SHIP_FROM_ORG_ID) ||'-'||INST.INSTANCE_CODE||'-IORG' TOP_MODEL_ITEM_FK
, L.INVENTORY_ITEM_ID||'-'||L.SHIP_FROM_ORG_ID ||'-'||INST.INSTANCE_CODE||'-IORG' ITEM_ORG_FK
, AID.ORG_ID ||'-'||INST.INSTANCE_CODE OPERATING_UNIT_FK
, DECODE(RCL.INTERFACE_LINE_ATTRIBUTE3
, ''
, 'NA_EDW'
, RCL.INTERFACE_LINE_ATTRIBUTE3 ||'-' ||INST.INSTANCE_CODE) INV_ORG_FK
, DECODE(H.SOLD_TO_ORG_ID
, NULL
, 'NA_EDW'
, H.SOLD_TO_ORG_ID ||'-'|| INST.INSTANCE_CODE ||'-CUST_ACCT-TPRT') CUSTOMER_FK
, DECODE(H.SALES_CHANNEL_CODE
, ''
, 'NA_EDW'
, H.SALES_CHANNEL_CODE ||'-'||INST.INSTANCE_CODE) SALES_CHANNEL_FK
, H.SALESREP_ID||'-'|| H.ORG_ID||'-'||INST.INSTANCE_CODE ||'-SALESREP-PERS' PRIM_SALES_REP_FK
, H.SALESREP_ID||'-'|| H.ORG_ID||'-'||INST.INSTANCE_CODE ||'-SALESREP-PERS' PRIM_SALESRESOURCE_FK
, EDW_GEOGRAPHY_PKG.CUSTOMER_SITE_LOCATION_FK( L.INVOICE_TO_ORG_ID
, INST.INSTANCE_CODE) BILL_TO_LOC_FK
, EDW_GEOGRAPHY_PKG.CUSTOMER_SITE_LOCATION_FK( L.SHIP_TO_ORG_ID
, INST.INSTANCE_CODE) SHIP_TO_LOC_FK
, DECODE(PL.PROJECT_ID
, ''
, 'NA_EDW'
, PL.PROJECT_ID ||'-' || INST.INSTANCE_CODE|| '-PJ-PRJ') PROJECT_FK
, DECODE(L.TASK_ID
, ''
, 'NA_EDW'
, L.TASK_ID ||'-' || INST.INSTANCE_CODE ) TASK_FK
, EDW_UTIL.GET_EDW_BASE_UOM(RCL.UOM_CODE
, L.INVENTORY_ITEM_ID) BASE_UOM_FK
, NVL(AI.INVOICE_CURRENCY_CODE
, NVL(H.TRANSACTIONAL_CURR_CODE
, 'NA_EDW')) TRX_CURRENCY_FK
, SOB.CURRENCY_CODE BASE_CURRENCY_FK
, EDW_LOOKUP_PKG.LOOKUP_CODE_FK('SOL'
, 'ORDER_CATEGORY'
, UPPER(H.ORDER_CATEGORY_CODE) ) ORDER_CATEGORY_FK
, EDW_LOOKUP_PKG.LOOKUP_CODE_FK('SOO'
, 'ORDER_TYPE'
, UPPER(ORDTYP.NAME) ) ORDER_TYPE_FK
, DECODE(L.INVOICE_TO_ORG_ID
, ''
, 'NA_EDW'
, L.INVOICE_TO_ORG_ID||'-' ||INST.INSTANCE_CODE||'-CUST_SITE_USE') BILL_TO_SITE_FK
, DECODE(L.SHIP_TO_ORG_ID
, ''
, 'NA_EDW'
, L.SHIP_TO_ORG_ID||'-' ||INST.INSTANCE_CODE||'-CUST_SITE_USE') SHIP_TO_SITE_FK
, EDW_TIME_PKG.CAL_DAY_FK(H.BOOKED_DATE
, FSPA.SET_OF_BOOKS_ID
, INST.INSTANCE_CODE) MONTH_BOOKED_FK
, EDW_TIME_PKG.CAL_DAY_FK(H.BOOKED_DATE
, FSPA.SET_OF_BOOKS_ID
, INST.INSTANCE_CODE) DATE_BOOKED_FK
, EDW_TIME_PKG.CAL_DAY_FK(L.PROMISE_DATE
, FSPA.SET_OF_BOOKS_ID
, INST.INSTANCE_CODE) DATE_PROMISED_FK
, EDW_TIME_PKG.CAL_DAY_FK(L.REQUEST_DATE
, FSPA.SET_OF_BOOKS_ID
, INST.INSTANCE_CODE) DATE_REQUESTED_FK
, EDW_TIME_PKG.CAL_DAY_FK(NULL
, FSPA.SET_OF_BOOKS_ID
, INST.INSTANCE_CODE) DATE_SCHEDULED_FK
, EDW_TIME_PKG.CAL_DAY_FK(NULL
, FSPA.SET_OF_BOOKS_ID
, INST.INSTANCE_CODE) DATE_SHIPPED_FK
, 'NA_EDW' LOCATOR_FK
, EDW_LOOKUP_PKG.LOOKUP_CODE_FK('SOS'
, 'ORDER_SOURCE'
, UPPER(PL.SOURCE_TYPE_CODE) ) ORDER_SOURCE_FK
, TO_CHAR(FSPA.SET_OF_BOOKS_ID)||'-'||INST.INSTANCE_CODE SET_OF_BOOKS_FK
, 'NA_EDW' CAMPAIGN_INIT_FK
, 'NA_EDW' CAMPAIGN_ACTL_FK
, 'NA_EDW' MEDCHN_INIT_FK
, 'NA_EDW' MEDCHN_ACTL_FK
, 'NA_EDW' OFFER_HDR_FK
, 'NA_EDW' OFFER_LINE_FK
, 'NA_EDW' MARKET_SEGMENT_FK
, 'NA_EDW' TARGET_SEGMENT_INIT_FK
, 'NA_EDW' TARGET_SEGMENT_ACTL_FK
, 'NA_EDW' CAMPAIGN_STATUS_ACTL_FK
, 'NA_EDW' CAMPAIGN_STATUS_INIT_FK
, EDW_TIME_PKG.CAL_DAY_FK(AID.ACCOUNTING_DATE
, FSPA.SET_OF_BOOKS_ID
, INST.INSTANCE_CODE) COGS_DATE_FK
, PL.LINE_ID ||'-'||INST.INSTANCE_CODE ORDER_LINE_ID
, DECODE(RCL.INTERFACE_LINE_ATTRIBUTE3
, ''
, 'NA_EDW'
, MP.ORGANIZATION_CODE ||'-'||INST.INSTANCE_CODE||'-PLNT' ) SHIP_INV_LOCATOR_FK
, AID.ACCOUNTING_DATE COGS_DATE
, H.ORDERED_DATE ORDER_DATE
, TO_NUMBER(NULL) PROM_EARLY_COUNT
, TO_NUMBER(NULL) PROM_LATE_COUNT
, TO_NUMBER(NULL) REQ_EARLY_COUNT
, TO_NUMBER(NULL) REQ_LATE_COUNT
, TO_NUMBER(NULL) PROM_EARLY_VAL_G
, TO_NUMBER(NULL) PROM_LATE_VAL_G
, TO_NUMBER(NULL) REQ_EARLY_VAL_G
, TO_NUMBER(NULL) REQ_LATE_VAL_G
, TO_NUMBER(NULL) REQUEST_LEAD_TIME
, TO_NUMBER(NULL) PROMISE_LEAD_TIME
, TO_NUMBER(NULL) ORDER_LEAD_TIME
, TO_NUMBER(NULL) SHIPPED_QTY_B
, TO_NUMBER(NULL) RMA_QTY_B
, NVL(RCL.QUANTITY_INVOICED
, 0) ICAP_QTY_B
, NVL(AID.AMOUNT
, 0) COGS_T
, NVL(AID.BASE_AMOUNT
, NVL(AID.AMOUNT
, 0)) COGS_B
, 0 COGS_G
, 0 RMA_VAL_T
, 0 RMA_VAL_G
, NULL LAST_UPDATE_DATE
, TO_NUMBER(NULL) COST_ELEMENT
, AID.ACCTS_PAY_CODE_COMBINATION_ID ACCOUNT
, H.ORDER_NUMBER ORDER_NUMBER
, NULL WAYBILL_NUMBER
, NULL LOT
, NULL REVISION
, NULL SERIAL_NUMBER
, NULL USER_ATTRIBUTE1
, NULL USER_ATTRIBUTE2
, NULL USER_ATTRIBUTE3
, NULL USER_ATTRIBUTE4
, NULL USER_ATTRIBUTE5
, NULL USER_ATTRIBUTE6
, NULL USER_ATTRIBUTE7
, NULL USER_ATTRIBUTE8
, NULL USER_ATTRIBUTE9
, NULL USER_ATTRIBUTE10
, NULL USER_ATTRIBUTE11
, NULL USER_ATTRIBUTE12
, NULL USER_ATTRIBUTE13
, NULL USER_ATTRIBUTE14
, NULL USER_ATTRIBUTE15
, TO_NUMBER(NULL) USER_MEASURE1
, TO_NUMBER(NULL) USER_MEASURE2
, TO_NUMBER(NULL) USER_MEASURE3
, TO_NUMBER(NULL) USER_MEASURE4
, TO_NUMBER(NULL) USER_MEASURE5
, NULL USER_FK1
, NULL USER_FK2
, NULL USER_FK3
, NULL USER_FK4
, NULL USER_FK5
, NULL OPERATION_CODE
, TO_NUMBER(NULL) DELIVERY_ID
, EDW_CURRENCY.GET_RATE(SOB.CURRENCY_CODE
, NVL(AID.ACCOUNTING_DATE
, AID.CREATION_DATE)) GLOBAL_CURRENCY_RATE
FROM OPI_EDW_COGS_INC INC
, AP_INVOICE_DISTRIBUTIONS_ALL AID
, AP_INVOICES_ALL AI
, RA_CUSTOMER_TRX_LINES_ALL RCL
, OE_ORDER_LINES_ALL L
, OE_ORDER_LINES_ALL PL
, OE_ORDER_HEADERS_ALL H
, EDW_LOCAL_INSTANCE INST
, FINANCIALS_SYSTEM_PARAMS_ALL FSPA
, GL_SETS_OF_BOOKS SOB
, SO_ORDER_TYPES_ALL ORDTYP
, MTL_PARAMETERS MP
, MTL_MATERIAL_TRANSACTIONS MMT
WHERE AID.INVOICE_ID = INC.PRIMARY_KEY1
AND AID.DISTRIBUTION_LINE_NUMBER = INC.PRIMARY_KEY2
AND AI.INVOICE_ID = AID.INVOICE_ID
AND TRANSLATE( LOWER(AID.REFERENCE_1)
, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ_ -+0123456789'
, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ_ -+') IS NULL
AND AI.SOURCE = 'INTERCOMPANY'
AND AI.ORG_ID = AID.ORG_ID
AND RCL.CUSTOMER_TRX_LINE_ID = TO_NUMBER(AID.REFERENCE_1)
AND AID.LINE_TYPE_LOOKUP_CODE = 'ITEM'
AND L.LINE_ID = RCL.INTERFACE_LINE_ATTRIBUTE6
AND PL.LINE_ID = NVL(L.TOP_MODEL_LINE_ID
, L.LINE_ID)
AND PL.ORG_ID = L.ORG_ID
AND H.ORG_ID = L.ORG_ID
AND H.HEADER_ID = L.HEADER_ID
AND H.HEADER_ID = PL.HEADER_ID
AND L.LINE_CATEGORY_CODE = 'ORDER'
AND PL.LINE_CATEGORY_CODE = 'ORDER'
AND ORDTYP.ORDER_TYPE_ID = H.ORDER_TYPE_ID
AND FSPA.ORG_ID = H.ORG_ID
AND FSPA.SET_OF_BOOKS_ID = SOB.SET_OF_BOOKS_ID
AND MMT.TRANSACTION_ID = RCL.INTERFACE_LINE_ATTRIBUTE7
AND NVL(MMT.LOGICAL_TRANSACTION
, -99) <> 1
AND MP.ORGANIZATION_ID(+) = RCL.INTERFACE_LINE_ATTRIBUTE3
AND MP.PROCESS_ENABLED_FLAG <> 'Y'