VIEW: APPS.OPI_EDW_OPMCOGS_FCV

Source

View Text - Preformatted

SELECT 'OPM-' || ITP.TRANS_ID || '-999-'|| ITP.LINE_ID||'-' ||INST.INSTANCE_CODE COGS_PK ,OOD.ACTUAL_SHIPDATE COGS_DATE ,EDW_TIME_PKG.CAL_DAY_FK(OOD.ACTUAL_SHIPDATE,GPM.SOB_ID) COGS_DATE_FK ,ITP.LINE_ID || '-'|| INST.INSTANCE_CODE || '-'|| 'OPM' ORDER_LINE_ID ,mp.organization_code||'-'||inst.instance_code||'-PLNT' SHIP_INV_LOCATOR_FK ,INST.INSTANCE_CODE INSTANCE_FK ,MSI.INVENTORY_ITEM_ID||'-'||IWM.MTL_ORGANIZATION_ID||'-'||INST.INSTANCE_CODE||'-IORG' TOP_MODEL_ITEM_FK ,MSI.INVENTORY_ITEM_ID||'-'||IWM.MTL_ORGANIZATION_ID||'-'||INST.INSTANCE_CODE||'-IORG' ITEM_ORG_FK ,GPM.ORG_ID||'-'||INST.INSTANCE_CODE OPERATING_UNIT_FK ,IWM.MTL_ORGANIZATION_ID||'-'||INST.INSTANCE_CODE INV_ORG_FK ,EDW_TRD_PARTNER_PKG.Customer_Site_FK( OCMBC.OF_BILL_TO_SITE_USE_ID) CUSTOMER_FK ,'NA_EDW' SALES_CHANNEL_FK ,DECODE(ood.SLSREP_CODE,NULL,'NA_EDW',ood.slsrep_code||'-'||gpm.org_id) PRIM_SALES_REP_FK ,DECODE(ood.SLSREP_CODE,NULL,'NA_EDW',ood.slsrep_code||'-'||gpm.org_id) PRIM_SALESRESOURCE_FK ,EDW_GEOGRAPHY_PKG.Customer_Site_Location_fk(OCMBC.OF_BILL_TO_SITE_USE_ID,INST.INSTANCE_CODE) BILL_TO_LOC_FK ,EDW_GEOGRAPHY_PKG.Customer_Site_Location_fk(OCMSC.OF_SHIP_TO_SITE_USE_ID,INST.INSTANCE_CODE) SHIP_TO_LOC_FK ,'NA_EDW' PROJECT_FK ,'NA_EDW' TASK_FK ,OOH.ORDER_DATE ,EDW_UTIL.GET_EDW_UOM(UOM.UOM_CODE, MSI.INVENTORY_ITEM_ID) BASE_UOM_FK ,OOD.BILLING_CURRENCY TRX_CURRENCY_FK ,GPM.BASE_CURRENCY_CODE BASE_CURRENCY_FK ,'NA_EDW' ORDER_CATEGORY_FK ,'NA_EDW' ORDER_TYPE_FK ,EDW_TRD_PARTNER_PKG.Customer_Site_FK(OCMBC.OF_BILL_TO_SITE_USE_ID) BILL_TO_SITE_FK ,EDW_TRD_PARTNER_PKG.Customer_Site_FK(OCMSC.OF_SHIP_TO_SITE_USE_ID) SHIP_TO_SITE_FK ,EDW_TIME_PKG.CAL_DAY_FK(OOH.ORDER_DATE,GPM.SOB_ID) MONTH_BOOKED_FK ,EDW_TIME_PKG.CAL_DAY_FK(OOH.ORDER_DATE,GPM.SOB_ID) DATE_BOOKED_FK ,EDW_TIME_PKG.CAL_DAY_FK(OOD.PROMISED_SHIPDATE,GPM.SOB_ID) DATE_PROMISED_FK ,EDW_TIME_PKG.CAL_DAY_FK(OOD.REQUESTED_SHIPDATE,GPM.SOB_ID) DATE_REQUESTED_FK ,EDW_TIME_PKG.CAL_DAY_FK(OOD.SCHED_SHIPDATE,GPM.SOB_ID) DATE_SCHEDULED_FK ,EDW_TIME_PKG.CAL_DAY_FK(OOD.ACTUAL_SHIPDATE,GPM.SOB_ID) DATE_SHIPPED_FK ,edw_mtl_inventory_loc_pkg.get_locator_fk (invl.INVENTORY_LOCATION_ID, iwm.mtl_organization_id,null,iwm.loct_ctl,iim.loct_ctl,itp.location, mp.organization_code, inst.instance_code) LOCATOR_FK ,'NA_EDW' ORDER_SOURCE_FK , TO_CHAR(GPM.SOB_ID)||'-' || SUBSTR(EDW_INSTANCE.GET_CODE,1,40) SET_OF_BOOKS_FK ,'NA_EDW' CAMPAIGN_INIT_FK ,'NA_EDW' CAMPAIGN_ACTL_FK ,'NA_EDW' CAMPAIGN_STATUS_ACTL_FK ,'NA_EDW' CAMPAIGN_STATUS_INIT_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 ,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 ,ITP.TRANS_QTY * -1 SHIPPED_QTY_B ,0 RMA_QTY_B ,0 ICAP_QTY_B ,0 COGS_T ,ITP.TRANS_QTY * -1 * DECODE(gpm.BASE_CURRENCY_CODE, cogs_gpm.BASE_CURRENCY_CODE, OPI_OPM_COMMON_PKG.OPMCO_GET_COST(ITP.ITEM_ID,ITP.WHSE_CODE,NULL,ITP.TRANS_DATE), OPI_OPM_COMMON_PKG.OPMCO_GET_MULCURR_AMT(cogs_gpm.BASE_CURRENCY_CODE, gpm.BASE_CURRENCY_CODE, ITP.TRANS_DATE, OPI_OPM_COMMON_PKG.OPMCO_GET_COST(ITP.ITEM_ID,ITP.WHSE_CODE,NULL,ITP.TRANS_DATE)))COGS_B , ITP.TRANS_QTY * EDW_CURRENCY.convert_global_amount( to_number(NULL), DECODE(gpm.BASE_CURRENCY_CODE, cogs_gpm.BASE_CURRENCY_CODE, OPI_OPM_COMMON_PKG.OPMCO_GET_COST( ITP.ITEM_ID,ITP.WHSE_CODE,NULL,ITP.TRANS_DATE)*-1, OPI_OPM_COMMON_PKG.OPMCO_GET_MULCURR_AMT(cogs_gpm.BASE_CURRENCY_CODE, gpm.BASE_CURRENCY_CODE, ITP.TRANS_DATE, OPI_OPM_COMMON_PKG.OPMCO_GET_COST( ITP.ITEM_ID,ITP.WHSE_CODE,NULL,ITP.TRANS_DATE)*-1)), NULL,gpm.BASE_CURRENCY_CODE,ITP.TRANS_DATE, NULL) COGS_G , 0 RMA_VAL_T , 0 RMA_VAL_G ,greatest(OOH.LAST_UPDATE_DATE, OOD.LAST_UPDATE_DATE, GPM.LAST_UPDATE_DATE, ILM.LAST_UPDATE_DATE) LAST_UPDATE_DATE ,To_CHAR(NULL) COST_ELEMENT ,To_CHAR(NULL) ACCOUNT ,OOH.ORDER_NO ORDER_NUMBER ,To_CHAR(NULL) WAYBILL_NUMBER ,ILM.LOT_NO LOT ,0 REVISION ,OOD.LINE_NO SERIAL_NUMBER ,to_char(NULL) USER_ATTRIBUTE1 ,to_char(NULL) USER_ATTRIBUTE2 ,to_char(NULL) USER_ATTRIBUTE3 ,to_char(NULL) USER_ATTRIBUTE4 ,to_char(NULL) USER_ATTRIBUTE5 ,to_char(NULL) USER_ATTRIBUTE6 ,to_char(NULL) USER_ATTRIBUTE7 ,to_char(NULL) USER_ATTRIBUTE8 ,to_char(NULL) USER_ATTRIBUTE9 ,to_char(NULL) USER_ATTRIBUTE10 ,to_char(NULL) USER_ATTRIBUTE11 ,to_char(NULL) USER_ATTRIBUTE12 ,to_char(NULL) USER_ATTRIBUTE13 ,to_char(NULL) USER_ATTRIBUTE14 ,to_char(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 ,'NA_EDW' USER_FK1 ,'NA_EDW' USER_FK2 ,'NA_EDW' USER_FK3 ,'NA_EDW' USER_FK4 ,'NA_EDW' USER_FK5 ,to_char(NULL) OPERATION_CODE ,view_id ,seq_id FROM EDW_LOCAL_INSTANCE inst, OP_ORDR_HDR OOH, OP_ORDR_DTL OOD, IC_TRAN_PND ITP, IC_WHSE_MST IWM, IC_ITEM_MST IIM, MTL_SYSTEM_ITEMS MSI, SY_ORGN_MST SOM, GL_PLCY_MST GPM, SY_ORGN_MST COGS_ORG, GL_PLCY_MST COGS_GPM, IC_LOTS_MST ILM, OP_CUST_MST OCMBC, OP_CUST_MST OCMSC, MTL_PARAMETERS MP, IC_LOCT_MST INVL, OPI_PMI_UOMS_MST UOM, OPI_EDW_OPMCOGS_INC INC WHERE OOH.ORDER_ID = OOD.ORDER_ID AND ITP.DOC_TYPE = 'OPSO' AND ITP.COMPLETED_IND = 1 AND ITP.DOC_ID = OOH.ORDER_ID AND ITP.LINE_ID = OOD.LINE_ID AND INC.LINE_ID = OOD.LINE_ID AND INC.VIEW_ID = 1 AND ITP.ITEM_ID = IIM.ITEM_ID AND ITP.LOT_ID = ILM.LOT_ID AND ITP.ITEM_ID = ILM.ITEM_ID AND IWM.WHSE_CODE = ITP.WHSE_CODE AND OOD.FROM_WHSE = ITP.WHSE_CODE AND MSI.SEGMENT1 = IIM.ITEM_NO AND MSI.ORGANIZATION_ID = IWM.MTL_ORGANIZATION_ID AND SOM.ORGN_CODE = OOH.ORGN_CODE AND SOM.CO_CODE = GPM.CO_CODE AND IWM.ORGN_CODE = COGS_ORG.ORGN_CODE AND COGS_ORG.CO_CODE = COGS_GPM.CO_CODE AND MP.ORGANIZATION_ID = IWM.MTL_ORGANIZATION_ID AND OOD.SHIPCUST_ID = OCMSC.CUST_ID AND OOH.BILLCUST_ID = OCMBC.CUST_ID AND UOM.UM_CODE = IIM.ITEM_UM AND INVL.WHSE_CODE(+) = ITP.WHSE_CODE AND INVL.LOCATION(+) = ITP.LOCATION UNION ALL SELECT 'OPM-' || ITC.TRANS_ID || '-999-'|| ITC.LINE_ID||'-' ||INST.INSTANCE_CODE COGS_PK ,OOD.ACTUAL_SHIPDATE COGS_DATE ,EDW_TIME_PKG.CAL_DAY_FK(OOD.ACTUAL_SHIPDATE,GPM.SOB_ID) COGS_DATE_FK ,ITC.LINE_ID || '-'|| INST.INSTANCE_CODE || '-'|| 'OPM' ORDER_LINE_ID ,mp.organization_code||'-'||inst.instance_code||'-PLNT' SHIP_INV_LOCATOR_FK ,INST.INSTANCE_CODE INSTANCE_FK ,MSI.INVENTORY_ITEM_ID||'-'||IWM.MTL_ORGANIZATION_ID||'-'||INST.INSTANCE_CODE||'-IORG' TOP_MODEL_ITEM_FK ,MSI.INVENTORY_ITEM_ID||'-'||IWM.MTL_ORGANIZATION_ID||'-'||INST.INSTANCE_CODE||'-IORG' ITEM_ORG_FK ,GPM.ORG_ID||'-'||INST.INSTANCE_CODE OPERATING_UNIT_FK ,IWM.MTL_ORGANIZATION_ID||'-'||INST.INSTANCE_CODE INV_ORG_FK ,EDW_TRD_PARTNER_PKG.Customer_Site_FK(OCMBC.OF_BILL_TO_SITE_USE_ID) CUSTOMER_FK ,'NA_EDW' SALES_CHANNEL_FK ,DECODE(ood.SLSREP_CODE,NULL,'NA_EDW',ood.slsrep_code||'-'||gpm.org_id) PRIM_SALES_REP_FK ,DECODE(ood.SLSREP_CODE,NULL,'NA_EDW',ood.slsrep_code||'-'||gpm.org_id) PRIM_SALESRESOURCE_FK ,EDW_GEOGRAPHY_PKG.Customer_Site_Location_fk(OCMBC.OF_BILL_TO_SITE_USE_ID,INST.INSTANCE_CODE) BILL_TO_LOC_FK ,EDW_GEOGRAPHY_PKG.Customer_Site_Location_fk(OCMSC.OF_SHIP_TO_SITE_USE_ID,INST.INSTANCE_CODE) SHIP_TO_LOC_FK ,'NA_EDW' PROJECT_FK ,'NA_EDW' TASK_FK ,OOH.ORDER_DATE ,EDW_UTIL.GET_EDW_UOM(UOM.UOM_CODE, MSI.INVENTORY_ITEM_ID) BASE_UOM_FK ,OOD.BILLING_CURRENCY TRX_CURRENCY_FK ,GPM.BASE_CURRENCY_CODE BASE_CURRENCY_FK ,'NA_EDW' ORDER_CATEGORY_FK ,'NA_EDW' ORDER_TYPE_FK ,EDW_TRD_PARTNER_PKG.Customer_Site_FK(OCMBC.OF_BILL_TO_SITE_USE_ID) BILL_TO_SITE_FK ,EDW_TRD_PARTNER_PKG.Customer_Site_FK(OCMSC.OF_SHIP_TO_SITE_USE_ID) SHIP_TO_SITE_FK ,EDW_TIME_PKG.CAL_DAY_FK(OOH.ORDER_DATE,GPM.SOB_ID) MONTH_BOOKED_FK ,EDW_TIME_PKG.CAL_DAY_FK(OOH.ORDER_DATE,GPM.SOB_ID) DATE_BOOKED_FK ,EDW_TIME_PKG.CAL_DAY_FK(OOD.PROMISED_SHIPDATE,GPM.SOB_ID) DATE_PROMISED_FK ,EDW_TIME_PKG.CAL_DAY_FK(OOD.REQUESTED_SHIPDATE,GPM.SOB_ID) DATE_REQUESTED_FK ,EDW_TIME_PKG.CAL_DAY_FK(OOD.SCHED_SHIPDATE,GPM.SOB_ID) DATE_SCHEDULED_FK ,EDW_TIME_PKG.CAL_DAY_FK(OOD.ACTUAL_SHIPDATE,GPM.SOB_ID) DATE_SHIPPED_FK ,edw_mtl_inventory_loc_pkg.get_locator_fk (invl.INVENTORY_LOCATION_ID, iwm.mtl_organization_id,null,iwm.loct_ctl,iim.loct_ctl,itc.location, mp.organization_code, inst.instance_code) LOCATOR_FK ,'NA_EDW' ORDER_SOURCE_FK , TO_CHAR(GPM.SOB_ID)||'-' || SUBSTR(EDW_INSTANCE.GET_CODE,1,40) SET_OF_BOOKS_FK ,'NA_EDW' CAMPAIGN_INIT_FK ,'NA_EDW' CAMPAIGN_ACTL_FK ,'NA_EDW' CAMPAIGN_STATUS_ACTL_FK ,'NA_EDW' CAMPAIGN_STATUS_INIT_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 ,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 ,ITC.TRANS_QTY * -1 SHIPPED_QTY_B ,0 RMA_QTY_B ,0 ICAP_QTY_B ,0 COGS_T ,ITC.TRANS_QTY * -1 * DECODE(gpm.BASE_CURRENCY_CODE, cogs_gpm.BASE_CURRENCY_CODE, OPI_OPM_COMMON_PKG.OPMCO_GET_COST( ITC.ITEM_ID,ITC.WHSE_CODE,NULL,ITC.TRANS_DATE), OPI_OPM_COMMON_PKG.OPMCO_GET_MULCURR_AMT(cogs_gpm.BASE_CURRENCY_CODE, gpm.BASE_CURRENCY_CODE, ITC.TRANS_DATE, OPI_OPM_COMMON_PKG.OPMCO_GET_COST( ITC.ITEM_ID,ITC.WHSE_CODE,NULL,ITC.TRANS_DATE)))COGS_B , ITC.TRANS_QTY * EDW_CURRENCY.convert_global_amount( to_number(NULL), DECODE(gpm.BASE_CURRENCY_CODE, cogs_gpm.BASE_CURRENCY_CODE, OPI_OPM_COMMON_PKG.OPMCO_GET_COST( ITC.ITEM_ID,ITC.WHSE_CODE,NULL,ITC.TRANS_DATE)*-1, OPI_OPM_COMMON_PKG.OPMCO_GET_MULCURR_AMT(cogs_gpm.BASE_CURRENCY_CODE, gpm.BASE_CURRENCY_CODE, ITC.TRANS_DATE, OPI_OPM_COMMON_PKG.OPMCO_GET_COST( ITC.ITEM_ID,ITC.WHSE_CODE,NULL,ITC.TRANS_DATE)*-1)), NULL,gpm.BASE_CURRENCY_CODE,ITC.TRANS_DATE, NULL) COGS_G , 0 RMA_VAL_T , 0 RMA_VAL_G ,greatest(OOH.LAST_UPDATE_DATE, OOD.LAST_UPDATE_DATE, GPM.LAST_UPDATE_DATE, ILM.LAST_UPDATE_DATE) LAST_UPDATE_DATE ,To_CHAR(NULL) COST_ELEMENT ,To_CHAR(NULL) ACCOUNT ,OOH.ORDER_NO ORDER_NUMBER ,To_CHAR(NULL) WAYBILL_NUMBER ,ILM.LOT_NO LOT ,0 REVISION ,OOD.LINE_NO SERIAL_NUMBER ,to_char(NULL) USER_ATTRIBUTE1 ,to_char(NULL) USER_ATTRIBUTE2 ,to_char(NULL) USER_ATTRIBUTE3 ,to_char(NULL) USER_ATTRIBUTE4 ,to_char(NULL) USER_ATTRIBUTE5 ,to_char(NULL) USER_ATTRIBUTE6 ,to_char(NULL) USER_ATTRIBUTE7 ,to_char(NULL) USER_ATTRIBUTE8 ,to_char(NULL) USER_ATTRIBUTE9 ,to_char(NULL) USER_ATTRIBUTE10 ,to_char(NULL) USER_ATTRIBUTE11 ,to_char(NULL) USER_ATTRIBUTE12 ,to_char(NULL) USER_ATTRIBUTE13 ,to_char(NULL) USER_ATTRIBUTE14 ,to_char(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 ,'NA_EDW' USER_FK1 ,'NA_EDW' USER_FK2 ,'NA_EDW' USER_FK3 ,'NA_EDW' USER_FK4 ,'NA_EDW' USER_FK5 ,to_char(NULL) OPERATION_CODE ,view_id ,seq_id FROM EDW_LOCAL_INSTANCE inst, OP_ORDR_HDR OOH, OP_ORDR_DTL OOD, IC_TRAN_CMP ITC, IC_WHSE_MST IWM, IC_ITEM_MST IIM, MTL_SYSTEM_ITEMS MSI, SY_ORGN_MST SOM, GL_PLCY_MST GPM, SY_ORGN_MST COGS_ORG, GL_PLCY_MST COGS_GPM, IC_LOTS_MST ILM, OP_CUST_MST OCMBC, OP_CUST_MST OCMSC, MTL_PARAMETERS MP, IC_LOCT_MST INVL, OPI_PMI_UOMS_MST UOM, OPI_EDW_OPMCOGS_INC INC WHERE OOH.ORDER_ID = OOD.ORDER_ID AND ITC.DOC_ID = OOH.ORDER_ID AND ITC.LINE_ID = OOD.LINE_ID AND ITC.DOC_TYPE = 'OPSO' AND INC.LINE_ID = OOD.LINE_ID AND INC.VIEW_ID = 1 AND ITC.ITEM_ID = IIM.ITEM_ID AND ITC.LOT_ID = ILM.LOT_ID AND ITC.ITEM_ID = ILM.ITEM_ID AND IWM.WHSE_CODE = ITC.WHSE_CODE AND OOD.FROM_WHSE = ITC.WHSE_CODE AND MSI.SEGMENT1 = IIM.ITEM_NO AND MSI.ORGANIZATION_ID = IWM.MTL_ORGANIZATION_ID AND MP.ORGANIZATION_ID = IWM.MTL_ORGANIZATION_ID AND SOM.ORGN_CODE = OOH.ORGN_CODE AND SOM.CO_CODE = GPM.CO_CODE AND IWM.ORGN_CODE = COGS_ORG.ORGN_CODE AND COGS_ORG.CO_CODE = COGS_GPM.CO_CODE AND OOD.SHIPCUST_ID = OCMSC.CUST_ID AND OOH.BILLCUST_ID = OCMBC.CUST_ID AND UOM.UM_CODE = IIM.ITEM_UM AND INVL.WHSE_CODE(+) = ITC.WHSE_CODE AND INVL.LOCATION(+) = ITC.LOCATION

View Text - HTML Formatted

SELECT 'OPM-' || ITP.TRANS_ID || '-999-'|| ITP.LINE_ID||'-' ||INST.INSTANCE_CODE COGS_PK
, OOD.ACTUAL_SHIPDATE COGS_DATE
, EDW_TIME_PKG.CAL_DAY_FK(OOD.ACTUAL_SHIPDATE
, GPM.SOB_ID) COGS_DATE_FK
, ITP.LINE_ID || '-'|| INST.INSTANCE_CODE || '-'|| 'OPM' ORDER_LINE_ID
, MP.ORGANIZATION_CODE||'-'||INST.INSTANCE_CODE||'-PLNT' SHIP_INV_LOCATOR_FK
, INST.INSTANCE_CODE INSTANCE_FK
, MSI.INVENTORY_ITEM_ID||'-'||IWM.MTL_ORGANIZATION_ID||'-'||INST.INSTANCE_CODE||'-IORG' TOP_MODEL_ITEM_FK
, MSI.INVENTORY_ITEM_ID||'-'||IWM.MTL_ORGANIZATION_ID||'-'||INST.INSTANCE_CODE||'-IORG' ITEM_ORG_FK
, GPM.ORG_ID||'-'||INST.INSTANCE_CODE OPERATING_UNIT_FK
, IWM.MTL_ORGANIZATION_ID||'-'||INST.INSTANCE_CODE INV_ORG_FK
, EDW_TRD_PARTNER_PKG.CUSTOMER_SITE_FK( OCMBC.OF_BILL_TO_SITE_USE_ID) CUSTOMER_FK
, 'NA_EDW' SALES_CHANNEL_FK
, DECODE(OOD.SLSREP_CODE
, NULL
, 'NA_EDW'
, OOD.SLSREP_CODE||'-'||GPM.ORG_ID) PRIM_SALES_REP_FK
, DECODE(OOD.SLSREP_CODE
, NULL
, 'NA_EDW'
, OOD.SLSREP_CODE||'-'||GPM.ORG_ID) PRIM_SALESRESOURCE_FK
, EDW_GEOGRAPHY_PKG.CUSTOMER_SITE_LOCATION_FK(OCMBC.OF_BILL_TO_SITE_USE_ID
, INST.INSTANCE_CODE) BILL_TO_LOC_FK
, EDW_GEOGRAPHY_PKG.CUSTOMER_SITE_LOCATION_FK(OCMSC.OF_SHIP_TO_SITE_USE_ID
, INST.INSTANCE_CODE) SHIP_TO_LOC_FK
, 'NA_EDW' PROJECT_FK
, 'NA_EDW' TASK_FK
, OOH.ORDER_DATE
, EDW_UTIL.GET_EDW_UOM(UOM.UOM_CODE
, MSI.INVENTORY_ITEM_ID) BASE_UOM_FK
, OOD.BILLING_CURRENCY TRX_CURRENCY_FK
, GPM.BASE_CURRENCY_CODE BASE_CURRENCY_FK
, 'NA_EDW' ORDER_CATEGORY_FK
, 'NA_EDW' ORDER_TYPE_FK
, EDW_TRD_PARTNER_PKG.CUSTOMER_SITE_FK(OCMBC.OF_BILL_TO_SITE_USE_ID) BILL_TO_SITE_FK
, EDW_TRD_PARTNER_PKG.CUSTOMER_SITE_FK(OCMSC.OF_SHIP_TO_SITE_USE_ID) SHIP_TO_SITE_FK
, EDW_TIME_PKG.CAL_DAY_FK(OOH.ORDER_DATE
, GPM.SOB_ID) MONTH_BOOKED_FK
, EDW_TIME_PKG.CAL_DAY_FK(OOH.ORDER_DATE
, GPM.SOB_ID) DATE_BOOKED_FK
, EDW_TIME_PKG.CAL_DAY_FK(OOD.PROMISED_SHIPDATE
, GPM.SOB_ID) DATE_PROMISED_FK
, EDW_TIME_PKG.CAL_DAY_FK(OOD.REQUESTED_SHIPDATE
, GPM.SOB_ID) DATE_REQUESTED_FK
, EDW_TIME_PKG.CAL_DAY_FK(OOD.SCHED_SHIPDATE
, GPM.SOB_ID) DATE_SCHEDULED_FK
, EDW_TIME_PKG.CAL_DAY_FK(OOD.ACTUAL_SHIPDATE
, GPM.SOB_ID) DATE_SHIPPED_FK
, EDW_MTL_INVENTORY_LOC_PKG.GET_LOCATOR_FK (INVL.INVENTORY_LOCATION_ID
, IWM.MTL_ORGANIZATION_ID
, NULL
, IWM.LOCT_CTL
, IIM.LOCT_CTL
, ITP.LOCATION
, MP.ORGANIZATION_CODE
, INST.INSTANCE_CODE) LOCATOR_FK
, 'NA_EDW' ORDER_SOURCE_FK
, TO_CHAR(GPM.SOB_ID)||'-' || SUBSTR(EDW_INSTANCE.GET_CODE
, 1
, 40) SET_OF_BOOKS_FK
, 'NA_EDW' CAMPAIGN_INIT_FK
, 'NA_EDW' CAMPAIGN_ACTL_FK
, 'NA_EDW' CAMPAIGN_STATUS_ACTL_FK
, 'NA_EDW' CAMPAIGN_STATUS_INIT_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
, 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
, ITP.TRANS_QTY * -1 SHIPPED_QTY_B
, 0 RMA_QTY_B
, 0 ICAP_QTY_B
, 0 COGS_T
, ITP.TRANS_QTY * -1 * DECODE(GPM.BASE_CURRENCY_CODE
, COGS_GPM.BASE_CURRENCY_CODE
, OPI_OPM_COMMON_PKG.OPMCO_GET_COST(ITP.ITEM_ID
, ITP.WHSE_CODE
, NULL
, ITP.TRANS_DATE)
, OPI_OPM_COMMON_PKG.OPMCO_GET_MULCURR_AMT(COGS_GPM.BASE_CURRENCY_CODE
, GPM.BASE_CURRENCY_CODE
, ITP.TRANS_DATE
, OPI_OPM_COMMON_PKG.OPMCO_GET_COST(ITP.ITEM_ID
, ITP.WHSE_CODE
, NULL
, ITP.TRANS_DATE)))COGS_B
, ITP.TRANS_QTY * EDW_CURRENCY.CONVERT_GLOBAL_AMOUNT( TO_NUMBER(NULL)
, DECODE(GPM.BASE_CURRENCY_CODE
, COGS_GPM.BASE_CURRENCY_CODE
, OPI_OPM_COMMON_PKG.OPMCO_GET_COST( ITP.ITEM_ID
, ITP.WHSE_CODE
, NULL
, ITP.TRANS_DATE)*-1
, OPI_OPM_COMMON_PKG.OPMCO_GET_MULCURR_AMT(COGS_GPM.BASE_CURRENCY_CODE
, GPM.BASE_CURRENCY_CODE
, ITP.TRANS_DATE
, OPI_OPM_COMMON_PKG.OPMCO_GET_COST( ITP.ITEM_ID
, ITP.WHSE_CODE
, NULL
, ITP.TRANS_DATE)*-1))
, NULL
, GPM.BASE_CURRENCY_CODE
, ITP.TRANS_DATE
, NULL) COGS_G
, 0 RMA_VAL_T
, 0 RMA_VAL_G
, GREATEST(OOH.LAST_UPDATE_DATE
, OOD.LAST_UPDATE_DATE
, GPM.LAST_UPDATE_DATE
, ILM.LAST_UPDATE_DATE) LAST_UPDATE_DATE
, TO_CHAR(NULL) COST_ELEMENT
, TO_CHAR(NULL) ACCOUNT
, OOH.ORDER_NO ORDER_NUMBER
, TO_CHAR(NULL) WAYBILL_NUMBER
, ILM.LOT_NO LOT
, 0 REVISION
, OOD.LINE_NO SERIAL_NUMBER
, TO_CHAR(NULL) USER_ATTRIBUTE1
, TO_CHAR(NULL) USER_ATTRIBUTE2
, TO_CHAR(NULL) USER_ATTRIBUTE3
, TO_CHAR(NULL) USER_ATTRIBUTE4
, TO_CHAR(NULL) USER_ATTRIBUTE5
, TO_CHAR(NULL) USER_ATTRIBUTE6
, TO_CHAR(NULL) USER_ATTRIBUTE7
, TO_CHAR(NULL) USER_ATTRIBUTE8
, TO_CHAR(NULL) USER_ATTRIBUTE9
, TO_CHAR(NULL) USER_ATTRIBUTE10
, TO_CHAR(NULL) USER_ATTRIBUTE11
, TO_CHAR(NULL) USER_ATTRIBUTE12
, TO_CHAR(NULL) USER_ATTRIBUTE13
, TO_CHAR(NULL) USER_ATTRIBUTE14
, TO_CHAR(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
, 'NA_EDW' USER_FK1
, 'NA_EDW' USER_FK2
, 'NA_EDW' USER_FK3
, 'NA_EDW' USER_FK4
, 'NA_EDW' USER_FK5
, TO_CHAR(NULL) OPERATION_CODE
, VIEW_ID
, SEQ_ID
FROM EDW_LOCAL_INSTANCE INST
, OP_ORDR_HDR OOH
, OP_ORDR_DTL OOD
, IC_TRAN_PND ITP
, IC_WHSE_MST IWM
, IC_ITEM_MST IIM
, MTL_SYSTEM_ITEMS MSI
, SY_ORGN_MST SOM
, GL_PLCY_MST GPM
, SY_ORGN_MST COGS_ORG
, GL_PLCY_MST COGS_GPM
, IC_LOTS_MST ILM
, OP_CUST_MST OCMBC
, OP_CUST_MST OCMSC
, MTL_PARAMETERS MP
, IC_LOCT_MST INVL
, OPI_PMI_UOMS_MST UOM
, OPI_EDW_OPMCOGS_INC INC
WHERE OOH.ORDER_ID = OOD.ORDER_ID
AND ITP.DOC_TYPE = 'OPSO'
AND ITP.COMPLETED_IND = 1
AND ITP.DOC_ID = OOH.ORDER_ID
AND ITP.LINE_ID = OOD.LINE_ID
AND INC.LINE_ID = OOD.LINE_ID
AND INC.VIEW_ID = 1
AND ITP.ITEM_ID = IIM.ITEM_ID
AND ITP.LOT_ID = ILM.LOT_ID
AND ITP.ITEM_ID = ILM.ITEM_ID
AND IWM.WHSE_CODE = ITP.WHSE_CODE
AND OOD.FROM_WHSE = ITP.WHSE_CODE
AND MSI.SEGMENT1 = IIM.ITEM_NO
AND MSI.ORGANIZATION_ID = IWM.MTL_ORGANIZATION_ID
AND SOM.ORGN_CODE = OOH.ORGN_CODE
AND SOM.CO_CODE = GPM.CO_CODE
AND IWM.ORGN_CODE = COGS_ORG.ORGN_CODE
AND COGS_ORG.CO_CODE = COGS_GPM.CO_CODE
AND MP.ORGANIZATION_ID = IWM.MTL_ORGANIZATION_ID
AND OOD.SHIPCUST_ID = OCMSC.CUST_ID
AND OOH.BILLCUST_ID = OCMBC.CUST_ID
AND UOM.UM_CODE = IIM.ITEM_UM
AND INVL.WHSE_CODE(+) = ITP.WHSE_CODE
AND INVL.LOCATION(+) = ITP.LOCATION UNION ALL SELECT 'OPM-' || ITC.TRANS_ID || '-999-'|| ITC.LINE_ID||'-' ||INST.INSTANCE_CODE COGS_PK
, OOD.ACTUAL_SHIPDATE COGS_DATE
, EDW_TIME_PKG.CAL_DAY_FK(OOD.ACTUAL_SHIPDATE
, GPM.SOB_ID) COGS_DATE_FK
, ITC.LINE_ID || '-'|| INST.INSTANCE_CODE || '-'|| 'OPM' ORDER_LINE_ID
, MP.ORGANIZATION_CODE||'-'||INST.INSTANCE_CODE||'-PLNT' SHIP_INV_LOCATOR_FK
, INST.INSTANCE_CODE INSTANCE_FK
, MSI.INVENTORY_ITEM_ID||'-'||IWM.MTL_ORGANIZATION_ID||'-'||INST.INSTANCE_CODE||'-IORG' TOP_MODEL_ITEM_FK
, MSI.INVENTORY_ITEM_ID||'-'||IWM.MTL_ORGANIZATION_ID||'-'||INST.INSTANCE_CODE||'-IORG' ITEM_ORG_FK
, GPM.ORG_ID||'-'||INST.INSTANCE_CODE OPERATING_UNIT_FK
, IWM.MTL_ORGANIZATION_ID||'-'||INST.INSTANCE_CODE INV_ORG_FK
, EDW_TRD_PARTNER_PKG.CUSTOMER_SITE_FK(OCMBC.OF_BILL_TO_SITE_USE_ID) CUSTOMER_FK
, 'NA_EDW' SALES_CHANNEL_FK
, DECODE(OOD.SLSREP_CODE
, NULL
, 'NA_EDW'
, OOD.SLSREP_CODE||'-'||GPM.ORG_ID) PRIM_SALES_REP_FK
, DECODE(OOD.SLSREP_CODE
, NULL
, 'NA_EDW'
, OOD.SLSREP_CODE||'-'||GPM.ORG_ID) PRIM_SALESRESOURCE_FK
, EDW_GEOGRAPHY_PKG.CUSTOMER_SITE_LOCATION_FK(OCMBC.OF_BILL_TO_SITE_USE_ID
, INST.INSTANCE_CODE) BILL_TO_LOC_FK
, EDW_GEOGRAPHY_PKG.CUSTOMER_SITE_LOCATION_FK(OCMSC.OF_SHIP_TO_SITE_USE_ID
, INST.INSTANCE_CODE) SHIP_TO_LOC_FK
, 'NA_EDW' PROJECT_FK
, 'NA_EDW' TASK_FK
, OOH.ORDER_DATE
, EDW_UTIL.GET_EDW_UOM(UOM.UOM_CODE
, MSI.INVENTORY_ITEM_ID) BASE_UOM_FK
, OOD.BILLING_CURRENCY TRX_CURRENCY_FK
, GPM.BASE_CURRENCY_CODE BASE_CURRENCY_FK
, 'NA_EDW' ORDER_CATEGORY_FK
, 'NA_EDW' ORDER_TYPE_FK
, EDW_TRD_PARTNER_PKG.CUSTOMER_SITE_FK(OCMBC.OF_BILL_TO_SITE_USE_ID) BILL_TO_SITE_FK
, EDW_TRD_PARTNER_PKG.CUSTOMER_SITE_FK(OCMSC.OF_SHIP_TO_SITE_USE_ID) SHIP_TO_SITE_FK
, EDW_TIME_PKG.CAL_DAY_FK(OOH.ORDER_DATE
, GPM.SOB_ID) MONTH_BOOKED_FK
, EDW_TIME_PKG.CAL_DAY_FK(OOH.ORDER_DATE
, GPM.SOB_ID) DATE_BOOKED_FK
, EDW_TIME_PKG.CAL_DAY_FK(OOD.PROMISED_SHIPDATE
, GPM.SOB_ID) DATE_PROMISED_FK
, EDW_TIME_PKG.CAL_DAY_FK(OOD.REQUESTED_SHIPDATE
, GPM.SOB_ID) DATE_REQUESTED_FK
, EDW_TIME_PKG.CAL_DAY_FK(OOD.SCHED_SHIPDATE
, GPM.SOB_ID) DATE_SCHEDULED_FK
, EDW_TIME_PKG.CAL_DAY_FK(OOD.ACTUAL_SHIPDATE
, GPM.SOB_ID) DATE_SHIPPED_FK
, EDW_MTL_INVENTORY_LOC_PKG.GET_LOCATOR_FK (INVL.INVENTORY_LOCATION_ID
, IWM.MTL_ORGANIZATION_ID
, NULL
, IWM.LOCT_CTL
, IIM.LOCT_CTL
, ITC.LOCATION
, MP.ORGANIZATION_CODE
, INST.INSTANCE_CODE) LOCATOR_FK
, 'NA_EDW' ORDER_SOURCE_FK
, TO_CHAR(GPM.SOB_ID)||'-' || SUBSTR(EDW_INSTANCE.GET_CODE
, 1
, 40) SET_OF_BOOKS_FK
, 'NA_EDW' CAMPAIGN_INIT_FK
, 'NA_EDW' CAMPAIGN_ACTL_FK
, 'NA_EDW' CAMPAIGN_STATUS_ACTL_FK
, 'NA_EDW' CAMPAIGN_STATUS_INIT_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
, 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
, ITC.TRANS_QTY * -1 SHIPPED_QTY_B
, 0 RMA_QTY_B
, 0 ICAP_QTY_B
, 0 COGS_T
, ITC.TRANS_QTY * -1 * DECODE(GPM.BASE_CURRENCY_CODE
, COGS_GPM.BASE_CURRENCY_CODE
, OPI_OPM_COMMON_PKG.OPMCO_GET_COST( ITC.ITEM_ID
, ITC.WHSE_CODE
, NULL
, ITC.TRANS_DATE)
, OPI_OPM_COMMON_PKG.OPMCO_GET_MULCURR_AMT(COGS_GPM.BASE_CURRENCY_CODE
, GPM.BASE_CURRENCY_CODE
, ITC.TRANS_DATE
, OPI_OPM_COMMON_PKG.OPMCO_GET_COST( ITC.ITEM_ID
, ITC.WHSE_CODE
, NULL
, ITC.TRANS_DATE)))COGS_B
, ITC.TRANS_QTY * EDW_CURRENCY.CONVERT_GLOBAL_AMOUNT( TO_NUMBER(NULL)
, DECODE(GPM.BASE_CURRENCY_CODE
, COGS_GPM.BASE_CURRENCY_CODE
, OPI_OPM_COMMON_PKG.OPMCO_GET_COST( ITC.ITEM_ID
, ITC.WHSE_CODE
, NULL
, ITC.TRANS_DATE)*-1
, OPI_OPM_COMMON_PKG.OPMCO_GET_MULCURR_AMT(COGS_GPM.BASE_CURRENCY_CODE
, GPM.BASE_CURRENCY_CODE
, ITC.TRANS_DATE
, OPI_OPM_COMMON_PKG.OPMCO_GET_COST( ITC.ITEM_ID
, ITC.WHSE_CODE
, NULL
, ITC.TRANS_DATE)*-1))
, NULL
, GPM.BASE_CURRENCY_CODE
, ITC.TRANS_DATE
, NULL) COGS_G
, 0 RMA_VAL_T
, 0 RMA_VAL_G
, GREATEST(OOH.LAST_UPDATE_DATE
, OOD.LAST_UPDATE_DATE
, GPM.LAST_UPDATE_DATE
, ILM.LAST_UPDATE_DATE) LAST_UPDATE_DATE
, TO_CHAR(NULL) COST_ELEMENT
, TO_CHAR(NULL) ACCOUNT
, OOH.ORDER_NO ORDER_NUMBER
, TO_CHAR(NULL) WAYBILL_NUMBER
, ILM.LOT_NO LOT
, 0 REVISION
, OOD.LINE_NO SERIAL_NUMBER
, TO_CHAR(NULL) USER_ATTRIBUTE1
, TO_CHAR(NULL) USER_ATTRIBUTE2
, TO_CHAR(NULL) USER_ATTRIBUTE3
, TO_CHAR(NULL) USER_ATTRIBUTE4
, TO_CHAR(NULL) USER_ATTRIBUTE5
, TO_CHAR(NULL) USER_ATTRIBUTE6
, TO_CHAR(NULL) USER_ATTRIBUTE7
, TO_CHAR(NULL) USER_ATTRIBUTE8
, TO_CHAR(NULL) USER_ATTRIBUTE9
, TO_CHAR(NULL) USER_ATTRIBUTE10
, TO_CHAR(NULL) USER_ATTRIBUTE11
, TO_CHAR(NULL) USER_ATTRIBUTE12
, TO_CHAR(NULL) USER_ATTRIBUTE13
, TO_CHAR(NULL) USER_ATTRIBUTE14
, TO_CHAR(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
, 'NA_EDW' USER_FK1
, 'NA_EDW' USER_FK2
, 'NA_EDW' USER_FK3
, 'NA_EDW' USER_FK4
, 'NA_EDW' USER_FK5
, TO_CHAR(NULL) OPERATION_CODE
, VIEW_ID
, SEQ_ID
FROM EDW_LOCAL_INSTANCE INST
, OP_ORDR_HDR OOH
, OP_ORDR_DTL OOD
, IC_TRAN_CMP ITC
, IC_WHSE_MST IWM
, IC_ITEM_MST IIM
, MTL_SYSTEM_ITEMS MSI
, SY_ORGN_MST SOM
, GL_PLCY_MST GPM
, SY_ORGN_MST COGS_ORG
, GL_PLCY_MST COGS_GPM
, IC_LOTS_MST ILM
, OP_CUST_MST OCMBC
, OP_CUST_MST OCMSC
, MTL_PARAMETERS MP
, IC_LOCT_MST INVL
, OPI_PMI_UOMS_MST UOM
, OPI_EDW_OPMCOGS_INC INC
WHERE OOH.ORDER_ID = OOD.ORDER_ID
AND ITC.DOC_ID = OOH.ORDER_ID
AND ITC.LINE_ID = OOD.LINE_ID
AND ITC.DOC_TYPE = 'OPSO'
AND INC.LINE_ID = OOD.LINE_ID
AND INC.VIEW_ID = 1
AND ITC.ITEM_ID = IIM.ITEM_ID
AND ITC.LOT_ID = ILM.LOT_ID
AND ITC.ITEM_ID = ILM.ITEM_ID
AND IWM.WHSE_CODE = ITC.WHSE_CODE
AND OOD.FROM_WHSE = ITC.WHSE_CODE
AND MSI.SEGMENT1 = IIM.ITEM_NO
AND MSI.ORGANIZATION_ID = IWM.MTL_ORGANIZATION_ID
AND MP.ORGANIZATION_ID = IWM.MTL_ORGANIZATION_ID
AND SOM.ORGN_CODE = OOH.ORGN_CODE
AND SOM.CO_CODE = GPM.CO_CODE
AND IWM.ORGN_CODE = COGS_ORG.ORGN_CODE
AND COGS_ORG.CO_CODE = COGS_GPM.CO_CODE
AND OOD.SHIPCUST_ID = OCMSC.CUST_ID
AND OOH.BILLCUST_ID = OCMBC.CUST_ID
AND UOM.UM_CODE = IIM.ITEM_UM
AND INVL.WHSE_CODE(+) = ITC.WHSE_CODE
AND INVL.LOCATION(+) = ITC.LOCATION