View: PMI_EDW_MARGIN_F_FCV

View Text

SELECT SH.ORGN_CODE||'-'||SH.ORDER_NO||'-'||SD.LINE_NO||'-'||INST.INSTANCE_CODE||'-'|| 'OPM'
, SH.ORGN_CODE||'-'||SH.ORGN_CODE||'-'||'PWHS'||'-'||INST.INSTANCE_CODE||'-'||'OPM'
, '0'||'-'||IM.ITEM_NO||'-'||SD.FROM_WHSE
, SD.ORDER_UM1
, PMI_EDW_COMM_PKG.GEOG_BILL_LOC_PK(SH.BILLCUST_ID)
, BC.CUST_NAME||'-'||'TPRT'
, PMI_EDW_COMM_PKG.GEOG_SHIP_LOC_PK(SD.SHIPCUST_ID)
, SC.CUST_NAME||'-'||'TPRT'
, SD.FROM_WHSE||'-'||'PWHS'||'-'||INST.INSTANCE_CODE||'-'||'OPM'
, SUBSTR(EDW_TIME_PKG.CAL_DAY_FK(SH.ORDER_DATE
, SOB.SET_OF_BOOKS_ID)
, 1
, 120)
, SUBSTR(EDW_TIME_PKG.CAL_DAY_FK(SH.REQUESTED_SHIPDATE
, SOB.SET_OF_BOOKS_ID)
, 1
, 120)
, SUBSTR(EDW_TIME_PKG.CAL_DAY_FK(SH.PROMISED_SHIPDATE
, SOB.SET_OF_BOOKS_ID)
, 1
, 120)
, SUBSTR(EDW_TIME_PKG.CAL_DAY_FK(SD.SCHED_SHIPDATE
, SOB.SET_OF_BOOKS_ID)
, 1
, 120)
, SUBSTR(EDW_TIME_PKG.CAL_DAY_FK(SD.ACTUAL_SHIPDATE
, SOB.SET_OF_BOOKS_ID)
, 1
, 120)
, SUBSTR(EDW_TIME_PKG.CAL_DAY_FK(SD.REQUIRED_DLVDATE
, SOB.SET_OF_BOOKS_ID)
, 1
, 120)
, SUBSTR(EDW_TIME_PKG.CAL_DAY_FK(SD.ACTUAL_DLVDATE
, SOB.SET_OF_BOOKS_ID)
, 1
, 120)
, SOB.CURRENCY_CODE
, SD.QC_GRADE_WANTED||'-'||INST.INSTANCE_CODE||'-'||'OPM'
, INST.INSTANCE_CODE
, 'NA_EDW'
, 'NA_EDW'
, 'NA_EDW'
, 'NA_EDW'
, 'NA_EDW'
, SH.ORDER_NO
, SH.ORDER_STATUS
, SD.LINE_NO
, SD.LINE_STATUS
, SD.ORDER_QTY1
, SD.SHIPPER_CODE
, SD.SHIP_MTHD
, SD.SHIP_STATUS
, SD.BASE_PRICE
, SD.NET_PRICE
, DECODE(SD.BASE_CURRENCY
, SD.BILLING_CURRENCY
, SD.EXTENDED_PRICE
, DECODE(SD.MUL_DIV_SIGN
, 0
, SD.EXTENDED_PRICE*SD.EXCHANGE_RATE
, SD.EXTENDED_PRICE/SD.EXCHANGE_RATE))
, PMI_SALES_PKG.PMISA_GET_CHARGE(SD.ORDER_ID
, SD.LINE_ID
, SD.EXTENDED_PRICE
, SD.BILLING_CURRENCY
, SD.BASE_CURRENCY
, SD.EXCHANGE_RATE
, SD.MUL_DIV_SIGN)
, GMICUOM.I2UOM_CV(SD.ITEM_ID
, 0
, SD.ORDER_UM1
, SD.ORDER_QTY1
, IM.ITEM_UM)*PMI_COMMON_PKG.PMICO_GET_COST( SD.ITEM_ID
, SD.FROM_WHSE
, NULL
, SD.ACTUAL_SHIPDATE)
, SD.LIST_PRICE
, PM.GL_COST_MTHD
, SD.COMMISSION_CODE
, SD.NET_COMMISSION
, SD.NET_TAX
, SD.CUSTPO_NO
, GREATEST(SH.LAST_UPDATE_DATE
, SD.LAST_UPDATE_DATE
, SC.LAST_UPDATE_DATE
, BC.LAST_UPDATE_DATE
, IM.LAST_UPDATE_DATE
, OM.LAST_UPDATE_DATE
, SOB.LAST_UPDATE_DATE
, PM.LAST_UPDATE_DATE) LAST_UPDATE_DATE
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
FROM OP_ORDR_HDR SH
, OP_ORDR_DTL SD
, OP_CUST_MST SC
, OP_CUST_MST BC
, IC_ITEM_MST IM
, SY_ORGN_MST OM
, GL_SETS_OF_BOOKS SOB
, GL_PLCY_MST PM
, EDW_LOCAL_INSTANCE INST
WHERE SH.ORDER_ID = SD.ORDER_ID
AND SH.BILLCUST_ID = BC.CUST_ID
AND SD.SHIPCUST_ID = SC.CUST_ID
AND SD.ITEM_ID = IM.ITEM_ID
AND SH.ORGN_CODE = OM.ORGN_CODE
AND OM.CO_CODE = PM.CO_CODE
AND PM.SET_OF_BOOKS_NAME =SOB.NAME
AND SD.LINE_STATUS >= 20

Columns