View: CST_EDW_COGS_V

View Text

SELECT 'INV' || '-' || TO_CHAR(MMT.TRANSACTION_ID) || '-' || TO_CHAR(NVL(MTA.COST_ELEMENT_ID
, 999)) || '-' || TO_CHAR(PICKING_LINE_DETAIL_ID) || '-' || INST.INSTANCE_CODE COGS_PK
, SUBSTR(BIS_EDW_INSTANCE.GET_CODE
, 0
, 40) INSTANCE_FK
, EDW_DIM_FK.ITEM_ORG(PL.INVENTORY_ITEM_ID
, PL.WAREHOUSE_ID) TOP_MODEL_ITEM_FK
, EDW_DIM_FK.ITEM_ORG(L.INVENTORY_ITEM_ID
, L.WAREHOUSE_ID) ITEM_ORG_FK
, -- CONSIDER ONLY COGS IN THE REPORTING -- (SHIPPING) OU
, NOT THE OE (SELLING) OU EDW_DIM_FK.OPERATING_UNIT(MMT.ORGANIZATION_ID) OPERATING_UNIT_FK
, EDW_DIM_FK.BUS_UNIT(1
, MMT.ORGANIZATION_ID) INV_ORG_FK
, EDW_DIM_FK.TRADING_PARTNER(H.CUSTOMER_ID) CUSTOMER_FK
, NVL(H.SALES_CHANNEL_CODE
, 'NA_EDW') || '-SALES_CHANNEL-SO_LOOKUPS' SALES_CHANNEL_FK
, EDW_DIM_FK.EMPLOYEE(3
, SALESREP.SALESREP_ID) PRIM_SALES_REP_FK
, NVL(RT.NAME
, 'NA_EDW') TERRITORY_FK
, EDW_DIM_FK.PROJECT(L.PROJECT_ID) PROJECT_FK
, EDW_DIM_FK.TASK(L.TASK_ID) TASK_FK
, MTA.TRANSACTION_DATE TRX_DATE_FK
, -- UOM SHOULD BE BASED ON THE REPORTING OU
, NOT THE OE OU EDW_UOM_PKG.GET_BASE_UOM_CODE(MMT.TRANSACTION_UOM
, NULL) BASE_UOM_FK
, NVL(MMT.CURRENCY_CODE
, EDW_UTIL.GET_BASE_CURRENCY( MMT.ORGANIZATION_ID)) TRX_CURRENCY_FK
, NVL(H.ORDER_CATEGORY
, 'NA_EDW') || '-ORDER_CATEGORY-SO_LOOKUPS' ORDER_CATEGORY_FK
, NVL(ORDTYP.NAME
, 'NA_EDW') || '-ORDER_TYPE-SO_ORDER_TYPES' ORDER_TYPE_FK
, EDW_DIM_FK.BUS_UNIT(2
, H.INVOICE_TO_SITE_USE_ID) BILL_TO_SITE_FK
, EDW_DIM_FK.BUS_UNIT(2
, L.SHIP_TO_SITE_USE_ID) SHIP_TO_SITE_FK
, L.S1_DATE DATE_BOOKED_FK
, L.PROMISE_DATE DATE_PROMISED_FK
, L.DATE_REQUESTED_CURRENT DATE_REQUESTED_FK
, PLD.SCHEDULE_DATE DATE_SCHEDULED_FK
, NVL(DEP.ACTUAL_DEPARTURE_DATE
, PLANNED_DEPARTURE_DATE) DATE_SHIPPED_FK
, -- ASSUME MMT
AND PLD SHOULD HAVE THE SAME SUBINVENTORY
AND LOCATOR EDW_DIM_FK.LOCATOR(MMT.ORGANIZATION_ID
, NULL
, PLD.SUBINVENTORY) SUB_INVENTORY_FK
, EDW_DIM_FK.LOCATOR(MMT.ORGANIZATION_ID
, PLD.INVENTORY_LOCATION_ID
, NULL) LOCATOR_FK
, /* DECODE(SIGN(L.PROMISE_DATE - NVL(DEP.ACTUAL_DEPARTURE_DATE
, PLANNED_DEPARTURE_DATE))
, 1
, 1
, 0
, 1
, 0) PROM_EARLY_COUNT
, DECODE(SIGN(L.PROMISE_DATE - NVL(DEP.ACTUAL_DEPARTURE_DATE
, PLANNED_DEPARTURE_DATE))
, -1
, 1
, 0) PROM_LATE_COUNT
, DECODE(SIGN(L.DATE_REQUESTED_CURRENT - NVL(DEP.ACTUAL_DEPARTURE_DATE
, PLANNED_DEPARTURE_DATE))
, 1
, 1
, 0
, 1
, 0) REQ_EARLY_COUNT
, DECODE(SIGN(L.DATE_REQUESTED_CURRENT - NVL(DEP.ACTUAL_DEPARTURE_DATE
, PLANNED_DEPARTURE_DATE))
, -1
, 1
, 0) REQ_LATE_COUNT
, */ 0 PROM_EARLY_COUNT
, 0 PROM_LATE_COUNT
, 0 REQ_EARLY_COUNT
, 0 REQ_LATE_COUNT
, DECODE(SIGN(L.PROMISE_DATE - NVL(DEP.ACTUAL_DEPARTURE_DATE
, PLANNED_DEPARTURE_DATE))
, 1
, EDW_CURRENCY.CONVERT_GLOBAL_AMOUNT( (NVL(MTA.TRANSACTION_VALUE
, MTA.BASE_TRANSACTION_VALUE) * PLD.SHIPPED_QUANTITY / MMT.TRANSACTION_QUANTITY * (-1))
, NULL
, H.CURRENCY_CODE
, EDW_DIM_FK.BUS_UNIT_ID(4
, NVL(L.WAREHOUSE_ID
, H.WAREHOUSE_ID))
, H.CONVERSION_DATE
, H.CONVERSION_TYPE_CODE )
, 0
, EDW_CURRENCY.CONVERT_GLOBAL_AMOUNT( (NVL(MTA.TRANSACTION_VALUE
, MTA.BASE_TRANSACTION_VALUE) * PLD.SHIPPED_QUANTITY / MMT.TRANSACTION_QUANTITY * (-1))
, NULL
, H.CURRENCY_CODE
, EDW_DIM_FK.BUS_UNIT_ID(4
, NVL(L.WAREHOUSE_ID
, H.WAREHOUSE_ID))
, H.CONVERSION_DATE
, H.CONVERSION_TYPE_CODE )
, 0) PROM_EARLY_VAL_G
, DECODE(SIGN(L.PROMISE_DATE - NVL(DEP.ACTUAL_DEPARTURE_DATE
, PLANNED_DEPARTURE_DATE))
, -1
, EDW_CURRENCY.CONVERT_GLOBAL_AMOUNT( (NVL(MTA.TRANSACTION_VALUE
, MTA.BASE_TRANSACTION_VALUE) * PLD.SHIPPED_QUANTITY / MMT.TRANSACTION_QUANTITY * (-1))
, NULL
, H.CURRENCY_CODE
, EDW_DIM_FK.BUS_UNIT_ID(4
, NVL(L.WAREHOUSE_ID
, H.WAREHOUSE_ID))
, H.CONVERSION_DATE
, H.CONVERSION_TYPE_CODE )
, 0) PROM_LATE_VAL_G
, DECODE(SIGN(L.DATE_REQUESTED_CURRENT - NVL(DEP.ACTUAL_DEPARTURE_DATE
, PLANNED_DEPARTURE_DATE))
, 1
, EDW_CURRENCY.CONVERT_GLOBAL_AMOUNT( (NVL(MTA.TRANSACTION_VALUE
, MTA.BASE_TRANSACTION_VALUE) * PLD.SHIPPED_QUANTITY / MMT.TRANSACTION_QUANTITY * (-1))
, NULL
, H.CURRENCY_CODE
, EDW_DIM_FK.BUS_UNIT_ID(4
, NVL(L.WAREHOUSE_ID
, H.WAREHOUSE_ID))
, H.CONVERSION_DATE
, H.CONVERSION_TYPE_CODE )
, 0
, EDW_CURRENCY.CONVERT_GLOBAL_AMOUNT( (NVL(MTA.TRANSACTION_VALUE
, MTA.BASE_TRANSACTION_VALUE) * PLD.SHIPPED_QUANTITY / MMT.TRANSACTION_QUANTITY * (-1))
, NULL
, H.CURRENCY_CODE
, EDW_DIM_FK.BUS_UNIT_ID(4
, NVL(L.WAREHOUSE_ID
, H.WAREHOUSE_ID))
, H.CONVERSION_DATE
, H.CONVERSION_TYPE_CODE )
, 0) REQ_EARLY_VAL_G
, DECODE(SIGN(L.DATE_REQUESTED_CURRENT - NVL(DEP.ACTUAL_DEPARTURE_DATE
, PLANNED_DEPARTURE_DATE))
, -1
, EDW_CURRENCY.CONVERT_GLOBAL_AMOUNT( (NVL(MTA.TRANSACTION_VALUE
, MTA.BASE_TRANSACTION_VALUE) * PLD.SHIPPED_QUANTITY / MMT.TRANSACTION_QUANTITY * (-1))
, NULL
, H.CURRENCY_CODE
, EDW_DIM_FK.BUS_UNIT_ID(4
, NVL(L.WAREHOUSE_ID
, H.WAREHOUSE_ID))
, H.CONVERSION_DATE
, H.CONVERSION_TYPE_CODE )
, 0) REQ_LATE_VAL_G
, MONTHS_BETWEEN(NVL(DEP.ACTUAL_DEPARTURE_DATE
, PLANNED_DEPARTURE_DATE)
, L.DATE_REQUESTED_CURRENT) * 31 REQUEST_LEAD_TIME
, MONTHS_BETWEEN(NVL(DEP.ACTUAL_DEPARTURE_DATE
, PLANNED_DEPARTURE_DATE)
, L.PROMISE_DATE) * 31 PROMISE_LEAD_TIME
, MONTHS_BETWEEN(NVL(DEP.ACTUAL_DEPARTURE_DATE
, PLANNED_DEPARTURE_DATE)
, L.S1_DATE) * 31 ORDER_LEAD_TIME
, DECODE(PL.ATO_FLAG
, 'N'
, DECODE(MMT.INVENTORY_ITEM_ID
, PL.INVENTORY_ITEM_ID
, -- ONLY RETURN QTY FOR MATERIAL COST ELEMENT OR FOR NO ELEMENT DECODE(NVL(MTA.COST_ELEMENT_ID
, -1)
, 1
, PLD.SHIPPED_QUANTITY * -- UOM BASED ON THE REPORTING OU
, NOT THE OE OU EDW_UOM_PKG.GET_UOM_CONVERSION_RATE(MMT.TRANSACTION_UOM
, MMT.INVENTORY_ITEM_ID
, NULL)
, -1
, PLD.SHIPPED_QUANTITY * EDW_UOM_PKG.GET_UOM_CONVERSION_RATE(MMT.TRANSACTION_UOM
, MMT.INVENTORY_ITEM_ID
, NULL)
, 0)
, DECODE(PL.ITEM_TYPE_CODE
, 'MODEL'
, DECODE(PL.INVENTORY_ITEM_ID
, L.INVENTORY_ITEM_ID
, DECODE(NVL(MTA.COST_ELEMENT_ID
, -1)
, -- RETURN A FRACTION OF THE QTY OF THE TOP MODEL -- SO THAT WHEN THESE ROWS ARE AGGREGATED -- IT SUMS UP TO THE QTY OF THE TOP MODEL 1
, PLD.SHIPPED_QUANTITY / EDW_UTIL.GET_PTO_MMT_COUNT(L.LINE_ID) * EDW_UOM_PKG.GET_UOM_CONVERSION_RATE(MMT.TRANSACTION_UOM
, MMT.INVENTORY_ITEM_ID
, NULL)
, -1
, PLD.SHIPPED_QUANTITY / EDW_UTIL.GET_PTO_MMT_COUNT(L.LINE_ID) * EDW_UOM_PKG.GET_UOM_CONVERSION_RATE(MMT.TRANSACTION_UOM
, MMT.INVENTORY_ITEM_ID
, NULL)
, 0)
, 0)
, 'KIT'
, DECODE(PL.INVENTORY_ITEM_ID
, L.INVENTORY_ITEM_ID
, DECODE(NVL(MTA.COST_ELEMENT_ID
, -1)
, 1
, PLD.SHIPPED_QUANTITY / EDW_UTIL.GET_PTO_MMT_COUNT(L.LINE_ID) * EDW_UOM_PKG.GET_UOM_CONVERSION_RATE(MMT.TRANSACTION_UOM
, MMT.INVENTORY_ITEM_ID
, NULL)
, -1
, PLD.SHIPPED_QUANTITY / EDW_UTIL.GET_PTO_MMT_COUNT(L.LINE_ID) * EDW_UOM_PKG.GET_UOM_CONVERSION_RATE(MMT.TRANSACTION_UOM
, MMT.INVENTORY_ITEM_ID
, NULL)
, 0)
, 0)
, 0) )
, 'Y'
, DECODE(PL.ITEM_TYPE_CODE
, 'MODEL'
, -- CASE OF ATO MODEL DECODE(NVL(MTA.COST_ELEMENT_ID
, -1)
, 1
, PLD.SHIPPED_QUANTITY * EDW_UOM_PKG.GET_UOM_CONVERSION_RATE(MMT.TRANSACTION_UOM
, MMT.INVENTORY_ITEM_ID
, NULL)
, -1
, PLD.SHIPPED_QUANTITY * EDW_UOM_PKG.GET_UOM_CONVERSION_RATE(MMT.TRANSACTION_UOM
, MMT.INVENTORY_ITEM_ID
, NULL)
, 0)
, DECODE(MMT.INVENTORY_ITEM_ID
, PL.INVENTORY_ITEM_ID
, DECODE(NVL(MTA.COST_ELEMENT_ID
, -1)
, 1
, PLD.SHIPPED_QUANTITY * EDW_UOM_PKG.GET_UOM_CONVERSION_RATE(MMT.TRANSACTION_UOM
, MMT.INVENTORY_ITEM_ID
, NULL)
, -1
, PLD.SHIPPED_QUANTITY * EDW_UOM_PKG.GET_UOM_CONVERSION_RATE(MMT.TRANSACTION_UOM
, MMT.INVENTORY_ITEM_ID
, NULL)
, 0)
, 0) )
, DECODE(MMT.INVENTORY_ITEM_ID
, PL.INVENTORY_ITEM_ID
, DECODE(NVL(MTA.COST_ELEMENT_ID
, -1)
, 1
, PLD.SHIPPED_QUANTITY * EDW_UOM_PKG.GET_UOM_CONVERSION_RATE(MMT.TRANSACTION_UOM
, MMT.INVENTORY_ITEM_ID
, NULL)
, -1
, PLD.SHIPPED_QUANTITY * EDW_UOM_PKG.GET_UOM_CONVERSION_RATE(MMT.TRANSACTION_UOM
, MMT.INVENTORY_ITEM_ID
, NULL)
, 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) * PLD.SHIPPED_QUANTITY / MMT.TRANSACTION_QUANTITY * (-1) COGS_T
, EDW_CURRENCY.CONVERT_GLOBAL_AMOUNT( (NVL(MTA.TRANSACTION_VALUE
, MTA.BASE_TRANSACTION_VALUE) * PLD.SHIPPED_QUANTITY / MMT.TRANSACTION_QUANTITY * (-1))
, NULL
, H.CURRENCY_CODE
, EDW_DIM_FK.BUS_UNIT_ID(4
, NVL(L.WAREHOUSE_ID
, H.WAREHOUSE_ID))
, H.CONVERSION_DATE
, H.CONVERSION_TYPE_CODE ) COGS_G
, GREATEST(PLD.LAST_UPDATE_DATE
, L.LAST_UPDATE_DATE
, PL.LAST_UPDATE_DATE
, MTA.LAST_UPDATE_DATE
, MMT.LAST_UPDATE_DATE
, H.LAST_UPDATE_DATE
, DEL.LAST_UPDATE_DATE
, DEP.LAST_UPDATE_DATE) LAST_UPDATE_DATE
, MTA.COST_ELEMENT_ID COST_ELEMENT
, MTA.REFERENCE_ACCOUNT ACCOUNT
, H.ORDER_NUMBER ORDER_NUMBER
, DEL.WAYBILL WAYBILL_NUMBER
, PLD.LOT_NUMBER LOT
, PLD.REVISION REVISION
, PLD.SERIAL_NUMBER SERIAL_NUMBER
FROM RA_SALESREPS_ALL SALESREP
, RA_CUSTOMERS CUST
, EDW_LOCAL_INSTANCE INST
, RA_SITE_USES_ALL SHIP_TO_SITE
, RA_TERRITORIES RT
, SO_ORDER_TYPES_ALL ORDTYP
, SO_HEADERS_ALL H
, SO_LINES_ALL PL
, -- PARENT LINE SO_LINES_ALL L
, -- CHILD LINE MTL_MATERIAL_TRANSACTIONS MMT
, MTL_TRANSACTION_ACCOUNTS MTA
, WSH_DELIVERIES DEL
, WSH_DEPARTURES DEP
, SO_PICKING_LINE_DETAILS PLD
WHERE MMT.TRANSACTION_SOURCE_TYPE_ID = 2
AND MTA.TRANSACTION_SOURCE_TYPE_ID = 2
AND MMT.TRANSACTION_ID = MTA.TRANSACTION_ID
AND MTA.ACCOUNTING_LINE_TYPE <> 1
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_TYPE_CODE IN ('REGULAR'
, 'DETAIL')
AND PL.LINE_TYPE_CODE IN ('REGULAR'
, 'DETAIL')
AND PL.LINE_ID = NVL(L.PARENT_LINE_ID
, L.LINE_ID)
AND H.HEADER_ID = L.HEADER_ID
AND H.HEADER_ID = PL.HEADER_ID
AND PLD.DELIVERY = MMT.TRX_SOURCE_DELIVERY_ID
AND DEL.DELIVERY_ID (+) = PLD.DELIVERY_ID
AND DEP.DEPARTURE_ID (+) = DEL.ACTUAL_DEPARTURE_ID
AND DEP.STATUS_CODE (+) = 'CL'
AND SALESREP.SALESREP_ID (+) = H.SALESREP_ID
AND SALESREP.ORG_ID (+) = H.ORG_ID
AND CUST.CUSTOMER_ID (+) = H.CUSTOMER_ID
AND SHIP_TO_SITE.SITE_USE_ID (+) = L.SHIP_TO_SITE_USE_ID
AND SHIP_TO_SITE.TERRITORY_ID = RT.TERRITORY_ID (+) AND( PLD.LAST_UPDATE_DATE > TO_DATE('1000/01/01'
, 'YYYY/MM/DD') OR L.LAST_UPDATE_DATE > TO_DATE('1000/01/01'
, 'YYYY/MM/DD') OR PL.LAST_UPDATE_DATE > TO_DATE('1000/01/01'
, 'YYYY/MM/DD') OR MTA.LAST_UPDATE_DATE > TO_DATE('1000/01/01'
, 'YYYY/MM/DD') OR MMT.LAST_UPDATE_DATE > TO_DATE('1000/01/01'
, 'YYYY/MM/DD') OR H.LAST_UPDATE_DATE > TO_DATE('1000/01/01'
, 'YYYY/MM/DD') OR DEL.LAST_UPDATE_DATE > TO_DATE('1000/01/01'
, 'YYYY/MM/DD') OR DEP.LAST_UPDATE_DATE > TO_DATE('1000/01/01'
, 'YYYY/MM/DD') ) UNION ALL -- THIS PART RETRIEVES COGS DATA AS A RESULT OF RMA SELECT 'RMA' || '-' || TO_CHAR(MMT.TRANSACTION_ID) || '-' || TO_CHAR(NVL(MTA.COST_ELEMENT_ID
, 999)) || '-' || INST.INSTANCE_CODE COGS_PK
, SUBSTR(BIS_EDW_INSTANCE.GET_CODE
, 0
, 40) INSTANCE_FK
, EDW_DIM_FK.ITEM_ORG(NVL(PL.INVENTORY_ITEM_ID
, NVL(CL.INVENTORY_ITEM_ID
, L.INVENTORY_ITEM_ID))
, NVL(PL.WAREHOUSE_ID
, NVL(CL.WAREHOUSE_ID
, L.WAREHOUSE_ID))) TOP_MODEL_ITEM_FK
, EDW_DIM_FK.ITEM_ORG(NVL(CL.INVENTORY_ITEM_ID
, L.INVENTORY_ITEM_ID)
, NVL(CL.WAREHOUSE_ID
, L.WAREHOUSE_ID)) TOP_MODEL_ITEM_FK
, -- CONSIDER ONLY COGS IN THE REPORTING -- (SHIPPING) OU
, NOT THE OE (SELLING) OU EDW_DIM_FK.OPERATING_UNIT(MMT.ORGANIZATION_ID) OPERATING_UNIT_FK
, EDW_DIM_FK.BUS_UNIT(1
, MMT.ORGANIZATION_ID) INV_ORG_FK
, EDW_DIM_FK.TRADING_PARTNER(H.CUSTOMER_ID) CUSTOMER_FK
, NVL(H.SALES_CHANNEL_CODE
, 'NA_EDW') || '-SALES_CHANNEL-SO_LOOKUPS' SALES_CHANNEL_FK
, EDW_DIM_FK.EMPLOYEE(3
, SALESREP.SALESREP_ID) PRIM_SALES_REP_FK
, DECODE(L.LINK_TO_LINE_ID
, ''
, DECODE(L.SHIP_TO_SITE_USE_ID
, ''
, 'NA_EDW'
, RT.NAME)
, DECODE(PL.SHIP_TO_SITE_USE_ID
, ''
, DECODE(CL.SHIP_TO_SITE_USE_ID
, ''
, DECODE(L.SHIP_TO_SITE_USE_ID
, ''
, 'NA_EDW'
, RT.NAME)
, CL_RT.NAME)
, PL_RT.NAME) ) TERRITORY_FK
, DECODE(L.LINK_TO_LINE_ID
, ''
, DECODE(L.PROJECT_ID
, ''
, 'NA_EDW'
, EDW_DIM_FK.PROJECT(L.PROJECT_ID))
, DECODE(PL.PROJECT_ID
, ''
, DECODE(CL.PROJECT_ID
, ''
, DECODE(L.PROJECT_ID
, ''
, 'NA_EDW'
, EDW_DIM_FK.PROJECT(L.PROJECT_ID))
, EDW_DIM_FK.PROJECT(CL.PROJECT_ID))
, EDW_DIM_FK.PROJECT(PL.PROJECT_ID)) ) PROJECT_FK
, DECODE(L.LINK_TO_LINE_ID
, ''
, DECODE(L.TASK_ID
, ''
, 'NA_EDW'
, EDW_DIM_FK.TASK(L.TASK_ID))
, DECODE(PL.TASK_ID
, ''
, DECODE(CL.TASK_ID
, ''
, DECODE(L.TASK_ID
, ''
, 'NA_EDW'
, EDW_DIM_FK.TASK(L.TASK_ID))
, EDW_DIM_FK.TASK(CL.TASK_ID))
, EDW_DIM_FK.TASK(PL.TASK_ID)) ) TASK_FK
, MTA.TRANSACTION_DATE TRX_DATE_FK
, EDW_UOM_PKG.GET_BASE_UOM_CODE(MMT.TRANSACTION_UOM
, NULL) BASE_UOM_FK
, NVL(MMT.CURRENCY_CODE
, EDW_UTIL.GET_BASE_CURRENCY( MMT.ORGANIZATION_ID)) TRX_CURRENCY_FK
, NVL(H.ORDER_CATEGORY
, 'NA_EDW') || '-ORDER_CATEGORY-SO_LOOKUPS' ORDER_CATEGORY_FK
, NVL(ORDTYP.NAME
, 'NA_EDW') || '-ORDER_TYPE-SO_ORDER_TYPES' ORDER_TYPE_FK
, EDW_DIM_FK.BUS_UNIT(2
, H.INVOICE_TO_SITE_USE_ID) BILL_TO_SITE_FK
, DECODE(L.LINK_TO_LINE_ID
, ''
, DECODE(L.SHIP_TO_SITE_USE_ID
, ''
, 'NA_EDW'
, EDW_DIM_FK.BUS_UNIT(2
, L.SHIP_TO_SITE_USE_ID))
, DECODE(PL.SHIP_TO_SITE_USE_ID
, ''
, DECODE(CL.SHIP_TO_SITE_USE_ID
, ''
, DECODE(L.SHIP_TO_SITE_USE_ID
, ''
, 'NA_EDW'
, EDW_DIM_FK.BUS_UNIT(2
, L.SHIP_TO_SITE_USE_ID))
, EDW_DIM_FK.BUS_UNIT(2
, CL.SHIP_TO_SITE_USE_ID))
, EDW_DIM_FK.BUS_UNIT(2
, PL.SHIP_TO_SITE_USE_ID)) ) SHIP_TO_SITE_FK
, L.S1_DATE DATE_BOOKED_FK
, L.PROMISE_DATE DATE_PROMISED_FK
, L.DATE_REQUESTED_CURRENT DATE_REQUESTED_FK
, TO_DATE(NULL) DATE_SCHEDULED_FK
, TO_DATE(NULL) DATE_SHIPPED_FK
, EDW_DIM_FK.LOCATOR(MMT.ORGANIZATION_ID
, NULL
, MMT.SUBINVENTORY_CODE) SUB_INVENTORY_FK
, EDW_DIM_FK.LOCATOR(MMT.ORGANIZATION_ID
, MMT.LOCATOR_ID
, NULL) LOCATOR_FK
, 0 PROM_EARLY_COUNT
, 0 PROM_LATE_COUNT
, 0 REQ_EARLY_COUNT
, 0 REQ_LATE_COUNT
, 0 PROM_EARLY_VAL_G
, 0 PROM_LATE_VAL_G
, 0 REQ_EARLY_VAL_G
, 0 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_UOM_PKG.GET_UOM_CONVERSION_RATE(MMT.TRANSACTION_UOM
, MMT.INVENTORY_ITEM_ID
, NULL)
, -1
, MMT.TRANSACTION_QUANTITY * EDW_UOM_PKG.GET_UOM_CONVERSION_RATE(MMT.TRANSACTION_UOM
, MMT.INVENTORY_ITEM_ID
, NULL)
, 0) * (-1)
, DECODE(PL.ATO_FLAG
, 'N'
, DECODE(MMT.INVENTORY_ITEM_ID
, PL.INVENTORY_ITEM_ID
, -- ONLY RETURN QTY FOR MATERIAL COST ELEMENT OR FOR NO ELEMENT DECODE(NVL(MTA.COST_ELEMENT_ID
, -1)
, 1
, MMT.TRANSACTION_QUANTITY * EDW_UOM_PKG.GET_UOM_CONVERSION_RATE(MMT.TRANSACTION_UOM
, MMT.INVENTORY_ITEM_ID
, NULL)
, -1
, MMT.TRANSACTION_QUANTITY * EDW_UOM_PKG.GET_UOM_CONVERSION_RATE(MMT.TRANSACTION_UOM
, MMT.INVENTORY_ITEM_ID
, NULL)
, 0) * (-1)
, DECODE(PL.ITEM_TYPE_CODE
, 'MODEL'
, DECODE(PL.INVENTORY_ITEM_ID
, CL.INVENTORY_ITEM_ID
, DECODE(NVL(MTA.COST_ELEMENT_ID
, -1)
, -- RETURN A FRACTION OF THE QTY OF THE TOP MODEL -- SO THAT WHEN THESE ROWS ARE AGGREGATED -- IT SUMS UP TO THE QTY OF THE TOP MODEL 1
, MMT.TRANSACTION_QUANTITY / EDW_UTIL.GET_PTO_MMT_COUNT(L.LINE_ID) * EDW_UOM_PKG.GET_UOM_CONVERSION_RATE(MMT.TRANSACTION_UOM
, MMT.INVENTORY_ITEM_ID
, NULL)
, -1
, MMT.TRANSACTION_QUANTITY / EDW_UTIL.GET_PTO_MMT_COUNT(L.LINE_ID) * EDW_UOM_PKG.GET_UOM_CONVERSION_RATE(MMT.TRANSACTION_UOM
, MMT.INVENTORY_ITEM_ID
, NULL)
, 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_PTO_MMT_COUNT(L.LINE_ID) * EDW_UOM_PKG.GET_UOM_CONVERSION_RATE(MMT.TRANSACTION_UOM
, MMT.INVENTORY_ITEM_ID
, NULL)
, -1
, MMT.TRANSACTION_QUANTITY / EDW_UTIL.GET_PTO_MMT_COUNT(L.LINE_ID) * EDW_UOM_PKG.GET_UOM_CONVERSION_RATE(MMT.TRANSACTION_UOM
, MMT.INVENTORY_ITEM_ID
, NULL)
, 0)
, 0)
, 0) )
, 'Y'
, DECODE(PL.ITEM_TYPE_CODE
, 'MODEL'
, DECODE(NVL(MTA.COST_ELEMENT_ID
, -1)
, 1
, MMT.TRANSACTION_QUANTITY * EDW_UOM_PKG.GET_UOM_CONVERSION_RATE(MMT.TRANSACTION_UOM
, MMT.INVENTORY_ITEM_ID
, NULL)
, -1
, MMT.TRANSACTION_QUANTITY * EDW_UOM_PKG.GET_UOM_CONVERSION_RATE(MMT.TRANSACTION_UOM
, MMT.INVENTORY_ITEM_ID
, NULL)
, 0) * (-1)
, DECODE(MMT.INVENTORY_ITEM_ID
, PL.INVENTORY_ITEM_ID
, DECODE(NVL(MTA.COST_ELEMENT_ID
, -1)
, 1
, MMT.TRANSACTION_QUANTITY * EDW_UOM_PKG.GET_UOM_CONVERSION_RATE( MMT.TRANSACTION_UOM
, MMT.INVENTORY_ITEM_ID
, NULL)
, -1
, MMT.TRANSACTION_QUANTITY * EDW_UOM_PKG.GET_UOM_CONVERSION_RATE( MMT.TRANSACTION_UOM
, MMT.INVENTORY_ITEM_ID
, NULL)
, 0) * (-1)
, 0) )
, DECODE(MMT.INVENTORY_ITEM_ID
, PL.INVENTORY_ITEM_ID
, DECODE(NVL(MTA.COST_ELEMENT_ID
, -1)
, 1
, MMT.TRANSACTION_QUANTITY * EDW_UOM_PKG.GET_UOM_CONVERSION_RATE( MMT.TRANSACTION_UOM
, MMT.INVENTORY_ITEM_ID
, NULL)
, -1
, MMT.TRANSACTION_QUANTITY * EDW_UOM_PKG.GET_UOM_CONVERSION_RATE( MMT.TRANSACTION_UOM
, MMT.INVENTORY_ITEM_ID
, NULL)
, 0) * (-1)
, 0) ) ) RMA_QTY_B
, TO_NUMBER(NULL) ICAP_QTY_B
, NVL(MTA.TRANSACTION_VALUE
, MTA.BASE_TRANSACTION_VALUE) COGS_T
, EDW_CURRENCY.CONVERT_GLOBAL_AMOUNT( NVL(MTA.TRANSACTION_VALUE
, MTA.BASE_TRANSACTION_VALUE)
, NULL
, H.CURRENCY_CODE
, EDW_DIM_FK.BUS_UNIT_ID(4
, NVL(L.WAREHOUSE_ID
, H.WAREHOUSE_ID))
, H.CONVERSION_DATE
, H.CONVERSION_TYPE_CODE ) COGS_G
, GREATEST(L.LAST_UPDATE_DATE
, PL.LAST_UPDATE_DATE
, MTA.LAST_UPDATE_DATE
, MMT.LAST_UPDATE_DATE
, H.LAST_UPDATE_DATE) 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
FROM RA_SALESREPS_ALL SALESREP
, RA_CUSTOMERS CUST
, EDW_LOCAL_INSTANCE INST
, RA_SITE_USES_ALL PL_SHIP_TO_SITE
, RA_SITE_USES_ALL CL_SHIP_TO_SITE
, RA_SITE_USES_ALL SHIP_TO_SITE
, RA_TERRITORIES RT
, RA_TERRITORIES CL_RT
, RA_TERRITORIES PL_RT
, SO_ORDER_TYPES_ALL ORDTYP
, SO_HEADERS_ALL H
, SO_LINES_ALL PL
, -- PARENT SALES ORDER LINE SO_LINES_ALL CL
, -- CHILD SALES ORDER LINE SO_LINES_ALL L
, -- RMA LINE MTL_MATERIAL_TRANSACTIONS MMT
, MTL_TRANSACTION_ACCOUNTS MTA
WHERE MMT.TRANSACTION_SOURCE_TYPE_ID = 12
AND MTA.TRANSACTION_SOURCE_TYPE_ID = 12
AND MMT.TRANSACTION_ID = MTA.TRANSACTION_ID
AND MTA.ACCOUNTING_LINE_TYPE <> 1
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_TYPE_CODE IN ('RETURN')
AND CL.LINE_ID (+) = L.LINK_TO_LINE_ID
AND PL.LINE_ID (+) = NVL(CL.PARENT_LINE_ID
, CL.LINE_ID)
AND H.HEADER_ID = L.HEADER_ID
AND SALESREP.SALESREP_ID (+) = H.SALESREP_ID
AND SALESREP.ORG_ID (+) = H.ORG_ID
AND CUST.CUSTOMER_ID (+) = H.CUSTOMER_ID
AND PL_SHIP_TO_SITE.SITE_USE_ID (+) = PL.SHIP_TO_SITE_USE_ID
AND PL_SHIP_TO_SITE.TERRITORY_ID = PL_RT.TERRITORY_ID (+)
AND CL_SHIP_TO_SITE.SITE_USE_ID (+) = CL.SHIP_TO_SITE_USE_ID
AND CL_SHIP_TO_SITE.TERRITORY_ID = CL_RT.TERRITORY_ID (+)
AND SHIP_TO_SITE.SITE_USE_ID (+) = L.SHIP_TO_SITE_USE_ID
AND SHIP_TO_SITE.TERRITORY_ID = RT.TERRITORY_ID (+) AND( L.LAST_UPDATE_DATE > TO_DATE('1000/01/01'
, 'YYYY/MM/DD') OR PL.LAST_UPDATE_DATE > TO_DATE('1000/01/01'
, 'YYYY/MM/DD') OR MTA.LAST_UPDATE_DATE > TO_DATE('1000/01/01'
, 'YYYY/MM/DD') OR MMT.LAST_UPDATE_DATE > TO_DATE('1000/01/01'
, 'YYYY/MM/DD') OR H.LAST_UPDATE_DATE > TO_DATE('1000/01/01'
, 'YYYY/MM/DD') ) UNION ALL -- THIS PART RETRIEVES COGS DATA AS A RESULT OF INTERCOMPANY ACCOUNT PAYABLE INVOICES SELECT 'ICI' || '-' || TO_CHAR(AID.DISTRIBUTION_LINE_NUMBER) || '-' || TO_CHAR(AID.INVOICE_ID) || '-' || INST.INSTANCE_CODE COGS_PK
, SUBSTR(BIS_EDW_INSTANCE.GET_CODE
, 0
, 40) INSTANCE_FK
, EDW_DIM_FK.ITEM_ORG(PL.INVENTORY_ITEM_ID
, PL.WAREHOUSE_ID) TOP_MODEL_ITEM_FK
, EDW_DIM_FK.ITEM_ORG(L.INVENTORY_ITEM_ID
, L.WAREHOUSE_ID) ITEM_ORG_FK
, -- OPERATING UNIT IS THE SELLING OU WHICH IS LIABLE -- FOR THE AP INVOICE EDW_DIM_FK.OPERATING_UNIT(AID.ORG_ID) OPERATING_UNIT_FK
, -- SHIPPING WAREHOUSE IS STORED IN INTERFACE_LINE_ATTRIBUTE3 -- BY THE INTERCOMPANY INVOICING PROGRAM EDW_DIM_FK.BUS_UNIT(1
, TO_NUMBER(RCL.INTERFACE_LINE_ATTRIBUTE3)) INV_ORG_FK
, EDW_DIM_FK.TRADING_PARTNER(H.CUSTOMER_ID) CUSTOMER_FK
, NVL(H.SALES_CHANNEL_CODE
, 'NA_EDW') || '-SALES_CHANNEL-SO_LOOKUPS' SALES_CHANNEL_FK
, EDW_DIM_FK.EMPLOYEE(3
, SALESREP.SALESREP_ID) PRIM_SALES_REP_FK
, NVL(RT.NAME
, 'NA_EDW') TERRITORY_FK
, EDW_DIM_FK.PROJECT(L.PROJECT_ID) PROJECT_FK
, EDW_DIM_FK.TASK(L.TASK_ID) TASK_FK
, AID.ACCOUNTING_DATE TRX_DATE_FK
, EDW_UOM_PKG.GET_BASE_UOM_CODE(RCL.UOM_CODE
, NULL) BASE_UOM_FK
, -- ASSUME TXN CURRENCY
FROM OE IS CONSISTENT -- WITH SETS_OF_BOOKS_ID IN AP NVL(AI.INVOICE_CURRENCY_CODE
, NVL(H.CURRENCY_CODE
, 'NA_EDW')) TRX_CURRENCY_FK
, NVL(H.ORDER_CATEGORY
, 'NA_EDW') || '-ORDER_CATEGORY-SO_LOOKUPS' ORDER_CATEGORY_FK
, NVL(ORDTYP.NAME
, 'NA_EDW') || '-ORDER_TYPE-SO_ORDER_TYPES' ORDER_TYPE_FK
, EDW_DIM_FK.BUS_UNIT(2
, H.INVOICE_TO_SITE_USE_ID) BILL_TO_SITE_FK
, EDW_DIM_FK.BUS_UNIT(2
, L.SHIP_TO_SITE_USE_ID) SHIP_TO_SITE_FK
, L.S1_DATE DATE_BOOKED_FK
, L.PROMISE_DATE DATE_PROMISED_FK
, L.DATE_REQUESTED_CURRENT DATE_REQUESTED_FK
, TO_DATE(NULL) DATE_SCHEDULED_FK
, TO_DATE(NULL) DATE_SHIPPED_FK
, TO_CHAR(NULL) SUB_INVENTORY_FK
, TO_CHAR(NULL) LOCATOR_FK
, 0 PROM_EARLY_COUNT
, 0 PROM_LATE_COUNT
, 0 REQ_EARLY_COUNT
, 0 REQ_LATE_COUNT
, 0 PROM_EARLY_VAL_G
, 0 PROM_LATE_VAL_G
, 0 REQ_EARLY_VAL_G
, 0 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
, RCL.QUANTITY_INVOICED ICAP_QTY_B
, NVL(AID.AMOUNT
, 0) COGS_T
, EDW_CURRENCY.CONVERT_GLOBAL_AMOUNT( NVL(AID.AMOUNT
, 0)
, NULL
, H.CURRENCY_CODE
, EDW_DIM_FK.BUS_UNIT_ID(4
, NVL(L.WAREHOUSE_ID
, H.WAREHOUSE_ID))
, H.CONVERSION_DATE
, H.CONVERSION_TYPE_CODE ) COGS_G
, GREATEST(AID.LAST_UPDATE_DATE
, L.LAST_UPDATE_DATE
, PL.LAST_UPDATE_DATE
, AI.LAST_UPDATE_DATE
, H.LAST_UPDATE_DATE) 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
FROM RA_SALESREPS_ALL SALESREP
, RA_CUSTOMERS CUST
, EDW_LOCAL_INSTANCE INST
, RA_SITE_USES_ALL SHIP_TO_SITE
, RA_TERRITORIES RT
, SO_ORDER_TYPES_ALL ORDTYP
, SO_HEADERS_ALL H
, SO_LINES_ALL PL
, -- PARENT LINE SO_LINES_ALL L
, -- CHILD LINE RA_CUSTOMER_TRX_LINES_ALL RCL
, AP_INVOICES_ALL AI
, AP_INVOICE_DISTRIBUTIONS_ALL AID
WHERE AI.INVOICE_ID = AID.INVOICE_ID
AND AI.SOURCE = 'INTERCOMPANY'
AND AI.ORG_ID = AID.ORG_ID
AND TO_CHAR(RCL.CUSTOMER_TRX_LINE_ID) = AID.REFERENCE_1
AND RCL.INTERFACE_LINE_ATTRIBUTE6 = L.LINE_ID
AND PL.LINE_ID = NVL(L.PARENT_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 ORDTYP.ORDER_TYPE_ID (+) = H.ORDER_TYPE_ID
AND SALESREP.SALESREP_ID (+) = H.SALESREP_ID
AND SALESREP.ORG_ID (+) = H.ORG_ID
AND CUST.CUSTOMER_ID (+) = H.CUSTOMER_ID
AND SHIP_TO_SITE.SITE_USE_ID (+) = L.SHIP_TO_SITE_USE_ID
AND SHIP_TO_SITE.TERRITORY_ID = RT.TERRITORY_ID (+) AND( AID.LAST_UPDATE_DATE > TO_DATE('1000/01/01'
, 'YYYY/MM/DD') OR L.LAST_UPDATE_DATE > TO_DATE('1000/01/01'
, 'YYYY/MM/DD') OR PL.LAST_UPDATE_DATE > TO_DATE('1000/01/01'
, 'YYYY/MM/DD') OR AI.LAST_UPDATE_DATE > TO_DATE('1000/01/01'
, 'YYYY/MM/DD') OR H.LAST_UPDATE_DATE > TO_DATE('1000/01/01'
, 'YYYY/MM/DD') )

Columns