My Practices with BI(View & Sense): How to Design a Data model based on a SQL query(Oracle) in QlikView

Dear All,
It is a challenge to all of us to mimic SQL query into a Data model, quite often in the current project I face such challenges.
One question you might ask is why not directly use the query in script and convert it as a qvd but in the architecture we are in right now we load most of the tables in to qvds and question comes when you have all the tables as qvds why can't we create a model using them! Hope I am clear till now.

Below is one such query which I recently modeled as a QlikView data model.

select v1.segment1 "Item",
v1.supplier_num "Supplier Num",
v1.supplier "Supplier Name",
v1.description "Description",
v1.organization_code "Org",
v1.primary_uom_code "UOM",
v1.period "Period",
v1.period_sort "Period Sort",
v1.po_unit_price "PO Unit Price (LC)",
v1.stnd_unit_cost "Item Unit Cost(LC)",
v1.currency_code "Local Currency",
ROUND(GL_CURRENCY_API.GET_RATE(v1.currency_code,'USD', v1.delivery_date, '1020'), 6) "Rate to USD",
ROUND((v1.po_unit_price * GL_CURRENCY_API.GET_RATE(v1.currency_code,'USD', v1.delivery_date, '1020')), 5) "PO Unit Price (USD)",
ROUND((v1.stnd_unit_cost * GL_CURRENCY_API.GET_RATE(v1.currency_code,'USD', v1.delivery_date, '1020')), 5) "Item Unit Cost (USD)",
sum(v1.qty) "Qty"
from (
select msi.segment1,
msi.description,
mp.organization_code,
msi.primary_uom_code,
trunc(mmt.transaction_date) delivery_date,
H_GET.HOLX_GET_FISCAL_P(trunc(mmt.transaction_date), 'Month') period,
to_date(H_GET.HOLX_GET_FISCAL_P(trunc(mmt.transaction_date), 'Month'), 'MON-YY') period_sort,
mmt.transaction_cost po_unit_price,
case when mp.process_enabled_flag = 'N'
then mmt.actual_cost
else (select gic.acctg_cost
from gl_item_cst gic
where gic.inventory_item_id = msi.inventory_item_id
and gic.organization_id = msi.organization_id
and cost_type_id = 1000
and mmt.transaction_date between start_date and end_date)
end stnd_unit_cost,
gll.currency_code,
mmt.primary_quantity qty,
s.segment1 supplier_num,
s.vendor_name supplier
from mtl_material_transactions mmt,
mtl_system_items_b msi,
mtl_parameters mp,
cst_acct_info_v cai,
gl_ledgers gll,
rcv_transactions rt,
ap_suppliers s
where mmt.transaction_type_id != 74 --Exlcude the transfer to regular trxs
and mmt.organization_id = msi.organization_id
and mmt.inventory_item_id = msi.inventory_item_id
and mmt.organization_id = mp.organization_id
and mp.organization_id = cai.organization_id
and cai.ledger_id = gll.ledger_id
and mmt.rcv_transaction_id = rt.transaction_id
and rt.vendor_id = s.vendor_id
and mmt.transaction_date >= to_date(:FROM_DATE, 'DD-MON-YYYY')
and mmt.transaction_date < to_date(:TO_DATE, 'DD-MON-YYYY')
and mmt.transaction_source_type_id = 1
UNION ALL
select msi.segment1,
msi.description,
mp.organization_code,
msi.primary_uom_code,
trunc(mmt.transaction_date) delivery_date,
H_GET.HOLX_GET_FISCAL_P(trunc(mmt.transaction_date), 'Month') period,
to_date(H_GET.HOLX_GET_FISCAL_P(trunc(mmt.transaction_date), 'Month'), 'MON-YY') period_sort,
mmt.transaction_cost po_unit_price,
case when mp.process_enabled_flag = 'N'
then mmt.actual_cost
else (select gic.acctg_cost
from gl_item_cst gic
where gic.inventory_item_id = msi.inventory_item_id
and gic.organization_id = msi.organization_id
and cost_type_id = 1000
and mmt.transaction_date between start_date and end_date)
end stnd_unit_cost,
gll.currency_code,
mmt.primary_quantity qty,
--mmt.*
s.segment1 supplier_num,
s.vendor_name supplier
from mtl_material_transactions mmt,
mtl_system_items_b msi,
mtl_parameters mp,
cst_acct_info_v cai,
gl_ledgers gll,
po_headers_all poh,
ap_suppliers s
where mmt.transaction_type_id = 74 --Transfer to regular trxs
and mmt.primary_quantity > 0
and mmt.organization_id = msi.organization_id
and mmt.inventory_item_id = msi.inventory_item_id
and mmt.organization_id = mp.organization_id
and mp.organization_id = cai.organization_id
and cai.ledger_id = gll.ledger_id
and mmt.transaction_source_id = poh.po_header_id
and poh.vendor_id = s.vendor_id
and mmt.transaction_date >= to_date(:FROM_DATE, 'DD-MON-YYYY')
and mmt.transaction_date < to_date(:TO_DATE, 'DD-MON-YYYY')
and mmt.transaction_source_type_id = 1
) v1
group
by v1.segment1,
v1.supplier_num,
v1.supplier,
v1.description,
v1.organization_code,
v1.primary_uom_code,
v1.period,
v1.period_sort,
v1.po_unit_price,
v1.stnd_unit_cost,
v1.currency_code,
ROUND(GL_CURRENCY_API.GET_RATE(v1.currency_code,'USD', v1.delivery_date, '1020'), 6),
ROUND((v1.po_unit_price * GL_CURRENCY_API.GET_RATE(v1.currency_code,'USD', v1.delivery_date, '1020')), 5),
ROUND((v1.stnd_unit_cost * GL_CURRENCY_API.GET_RATE(v1.currency_code,'USD', v1.delivery_date, '1020')), 5)
order
by 8,1,3,5

Now comes the Data model:

Before we start on the model observe the package calls we used in the query, there are some alternates used in the model.

1. This particular part is used for Current Conversion and replaces the package code

FXDaily:

Load FX.FROM & '_' & FX.TO & '_' & FX.DATE as %FX_KEY,

Load rates.FROM_CURRENCY as FX.FROM,

rates.TO_CURRENCY as FX.TO,

rates.CONVERSION_DATE as FX.DATE,

rates.CONVERSION_RATE as FX.RATE

FROM

\..\..gl_daily_rates_all_gsdm.qvd(qvd);

FXMap:

Mapping

Load

%FX_KEY,

FX.RATE

Resident FXDaily;

Drop Table FXDaily;

Mapping loads

Supplier_Map:

Mapping LOAD

sup.VENDOR_ID as %VENDOR_ID,

sup.VENDOR_NAME as SUPPLIER

FROM

[..\..\3_Data_Repository\Oracle_EBS\apps.ap_suppliers.qvd](qvd);

SupplierNum_Map:

Mapping LOAD

sup.VENDOR_ID as %VENDOR_ID,

sup.SEGMENT1 as SUPPLIER_NUM

FROM

[..\..\3_Data_Repository\Oracle_EBS\apps.ap_suppliers.qvd](qvd);

MMT:

LOAD

mmt.TRANSACTION_ID,

AutoNumberHash128(mmt.INVENTORY_ITEM_ID & '|' & mmt.ORGANIZATION_ID) as %INVENTORYKEY,

//AutoNumberHash128(mmt.INVENTORY_ITEM_ID & '|' & mmt.ORGANIZATION_ID) as INVENTORYKEY, //Earlier used in GL_ITEM_Cost

AutoNumberHash128(mmt.INVENTORY_ITEM_ID & '|' & mmt.ORGANIZATION_ID & if(mmt.TRANSACTION_TYPE_ID <> 74,'|RCV',if(mmt.PRIMARY_QUANTITY >0,'|POH'))&

'|' & mmt.RCV_TRANSACTION_ID) as %LinkINVENTORYKEY,

mmt.INVENTORY_ITEM_ID,

mmt.ORGANIZATION_ID as %ORGANIZATION_ID,

mmt.TRANSACTION_DATE as DELIVERY_DATE,

mmt.TRANSACTION_COST as PO_UNIT_PRICE,

Date(floor(mmt.TRANSACTION_DATE)) as Date,

mmt.ACTUAL_COST,

mmt.PRIMARY_QUANTITY,

mmt.RCV_TRANSACTION_ID,

mmt.TRANSACTION_SOURCE_ID,

mmt.TRANSACTION_TYPE_ID,

mmt.LAST_UPDATE_DATE,

mmt.CURRENCY_CODE,

mmt.CURRENCY_CONVERSION_RATE,

mmt.CURRENCY_CONVERSION_TYPE,

mmt.CURRENCY_CONVERSION_DATE

;

LOAD

mmt.LAST_UPDATE_DATE,

mmt.TRANSACTION_ID,

mmt.INVENTORY_ITEM_ID,

mmt.ORGANIZATION_ID,

mmt.TRANSACTION_DATE,

mmt.TRANSACTION_COST,

mmt.PRIMARY_QUANTITY,

mmt.RCV_TRANSACTION_ID,

mmt.REVISION,

mmt.ORGANIZATION_ID,

mmt.SUBINVENTORY_CODE,

mmt.LOCATOR_ID,

mmt.TRANSACTION_TYPE_ID,

mmt.TRANSACTION_ACTION_ID,

mmt.TRANSACTION_SOURCE_TYPE_ID,

mmt.TRANSACTION_SOURCE_ID,

mmt.TRANSACTION_SOURCE_NAME,

mmt.TRANSACTION_QUANTITY,

mmt.TRANSACTION_UOM,

mmt.PRIMARY_QUANTITY,

mmt.VARIANCE_AMOUNT,

mmt.ACCT_PERIOD_ID,

mmt.TRANSACTION_REFERENCE,

mmt.REASON_ID,

mmt.DISTRIBUTION_ACCOUNT_ID,

mmt.ENCUMBRANCE_ACCOUNT,

mmt.ENCUMBRANCE_AMOUNT,

mmt.COST_UPDATE_ID,

mmt.COSTED_FLAG,

mmt.TRANSACTION_GROUP_ID,

mmt.INVOICED_FLAG,

mmt.ACTUAL_COST,

mmt.PRIOR_COST,

mmt.NEW_COST,

mmt.CURRENCY_CODE,

mmt.CURRENCY_CONVERSION_RATE,

mmt.CURRENCY_CONVERSION_TYPE,

mmt.CURRENCY_CONVERSION_DATE,

mmt.USSGL_TRANSACTION_CODE,

mmt.QUANTITY_ADJUSTED,

mmt.EMPLOYEE_CODE,

mmt.DEPARTMENT_ID,

mmt.OPERATION_SEQ_NUM,

mmt.MASTER_SCHEDULE_UPDATE_CODE,

mmt.RECEIVING_DOCUMENT,

mmt.PICKING_LINE_ID,

mmt.TRX_SOURCE_LINE_ID,

mmt.TRX_SOURCE_DELIVERY_ID,

mmt.REPETITIVE_LINE_ID,

mmt.PHYSICAL_ADJUSTMENT_ID,

mmt.CYCLE_COUNT_ID,

mmt.RMA_LINE_ID,

mmt.TRANSFER_TRANSACTION_ID,

mmt.TRANSACTION_SET_ID,

mmt.MOVE_TRANSACTION_ID,

mmt.COMPLETION_TRANSACTION_ID,

mmt.SHORTAGE_PROCESS_CODE,

mmt.SOURCE_CODE,

mmt.SOURCE_LINE_ID,

mmt.VENDOR_LOT_NUMBER,

mmt.TRANSFER_ORGANIZATION_ID,

mmt.TRANSFER_SUBINVENTORY,

mmt.TRANSFER_LOCATOR_ID,

mmt.SHIPMENT_NUMBER,

mmt.TRANSFER_COST,

mmt.TRANSPORTATION_DIST_ACCOUNT,

mmt.TRANSPORTATION_COST,

mmt.TRANSFER_COST_DIST_ACCOUNT,

mmt.WAYBILL_AIRBILL,

mmt.FREIGHT_CODE,

mmt.NUMBER_OF_CONTAINERS,

mmt.VALUE_CHANGE,

mmt.PERCENTAGE_CHANGE,

mmt.ATTRIBUTE_CATEGORY,

mmt.ATTRIBUTE1,

mmt.ATTRIBUTE2,

mmt.ATTRIBUTE3,

mmt.ATTRIBUTE4,

mmt.ATTRIBUTE5,

mmt.ATTRIBUTE6,

mmt.ATTRIBUTE7,

mmt.ATTRIBUTE8,

mmt.ATTRIBUTE9,

mmt.ATTRIBUTE10,

mmt.ATTRIBUTE11,

mmt.ATTRIBUTE12,

mmt.ATTRIBUTE13,

mmt.ATTRIBUTE14,

mmt.ATTRIBUTE15,

mmt.MOVEMENT_ID,

mmt.TASK_ID,

mmt.TO_TASK_ID,

mmt.PROJECT_ID,

mmt.TO_PROJECT_ID,

mmt.SOURCE_PROJECT_ID,

mmt.PA_EXPENDITURE_ORG_ID,

mmt.SOURCE_TASK_ID,

mmt.EXPENDITURE_TYPE,

mmt.ERROR_CODE,

mmt.ERROR_EXPLANATION,

mmt.PRIOR_COSTED_QUANTITY,

mmt.TRANSFER_PRIOR_COSTED_QUANTITY,

mmt.FINAL_COMPLETION_FLAG,

mmt.PM_COST_COLLECTED,

mmt.PM_COST_COLLECTOR_GROUP_ID,

mmt.SHIPMENT_COSTED,

mmt.TRANSFER_PERCENTAGE,

mmt.MATERIAL_ACCOUNT,

mmt.MATERIAL_OVERHEAD_ACCOUNT,

mmt.RESOURCE_ACCOUNT,

mmt.OUTSIDE_PROCESSING_ACCOUNT,

mmt.OVERHEAD_ACCOUNT,

mmt.COST_GROUP_ID,

mmt.TRANSFER_COST_GROUP_ID,

mmt.FLOW_SCHEDULE,

mmt.QA_COLLECTION_ID,

mmt.OVERCOMPLETION_TRANSACTION_QTY,

mmt.OVERCOMPLETION_PRIMARY_QTY,

mmt.OVERCOMPLETION_TRANSACTION_ID,

mmt.MVT_STAT_STATUS,

mmt.COMMON_BOM_SEQ_ID,

mmt.COMMON_ROUTING_SEQ_ID,

mmt.ORG_COST_GROUP_ID,

mmt.COST_TYPE_ID,

mmt.PERIODIC_PRIMARY_QUANTITY,

mmt.MOVE_ORDER_LINE_ID,

mmt.TASK_GROUP_ID,

mmt.PICK_SLIP_NUMBER,

mmt.LPN_ID,

mmt.TRANSFER_LPN_ID,

mmt.PICK_STRATEGY_ID,

mmt.PICK_RULE_ID,

mmt.PUT_AWAY_STRATEGY_ID,

mmt.PUT_AWAY_RULE_ID,

mmt.CONTENT_LPN_ID,

mmt.PICK_SLIP_DATE,

mmt.COST_CATEGORY_ID,

mmt.ORGANIZATION_TYPE,

mmt.TRANSFER_ORGANIZATION_TYPE,

mmt.OWNING_ORGANIZATION_ID,

mmt.OWNING_TP_TYPE,

mmt.XFR_OWNING_ORGANIZATION_ID,

mmt.TRANSFER_OWNING_TP_TYPE,

mmt.PLANNING_ORGANIZATION_ID,

mmt.PLANNING_TP_TYPE,

mmt.XFR_PLANNING_ORGANIZATION_ID,

mmt.TRANSFER_PLANNING_TP_TYPE,

mmt.SECONDARY_UOM_CODE,

mmt.SECONDARY_TRANSACTION_QUANTITY,

mmt.TRANSACTION_GROUP_SEQ,

mmt.SHIP_TO_LOCATION_ID,

mmt.RESERVATION_ID,

mmt.TRANSACTION_MODE,

mmt.TRANSACTION_BATCH_ID,

mmt.TRANSACTION_BATCH_SEQ,

mmt.INTRANSIT_ACCOUNT,

mmt.FOB_POINT,

mmt.PARENT_TRANSACTION_ID,

mmt.LOGICAL_TRX_TYPE_CODE,

mmt.TRX_FLOW_HEADER_ID,

mmt.LOGICAL_TRANSACTIONS_CREATED,

mmt.LOGICAL_TRANSACTION,

mmt.INTERCOMPANY_COST,

mmt.INTERCOMPANY_PRICING_OPTION,

mmt.INTERCOMPANY_CURRENCY_CODE,

mmt.ORIGINAL_TRANSACTION_TEMP_ID,

mmt.TRANSFER_PRICE,

mmt.EXPENSE_ACCOUNT_ID,

mmt.COGS_RECOGNITION_PERCENT,

mmt.SO_ISSUE_ACCOUNT_TYPE,

mmt.OPM_COSTED_FLAG,

mmt.MATERIAL_EXPENSE_ACCOUNT,

mmt.TRANSACTION_EXTRACTED,

mmt.XML_DOCUMENT_ID

FROM

[..\..\3_Data_Repository\Oracle_EBS\apps.mtl_material_transactions.qvd](qvd)

Where mmt.TRANSACTION_SOURCE_TYPE_ID = 1

and Date(mmt.TRANSACTION_DATE,'MM/DD/YYYY')>='10/01/2017';

4.Inner Join(MMT)

MSI:

LOAD

AutoNumberHash128(system_items_b.INVENTORY_ITEM_ID & '|' & system_items_b.ORGANIZATION_ID) as %INVENTORYKEY,

system_items_b.INVENTORY_ITEM_ID,

system_items_b.ORGANIZATION_ID,

system_items_b.SEGMENT1 as %ITEM,

system_items_b.DESCRIPTION,

system_items_b.PRIMARY_UOM_CODE,

system_items_b.PLANNING_MAKE_BUY_CODE,

if(system_items_b.PLANNING_MAKE_BUY_CODE=1,'Make','Buy') as Make_Buy_Flag;

Load

system_items_b.INVENTORY_ITEM_ID,

system_items_b.ORGANIZATION_ID,

system_items_b.SEGMENT1,

system_items_b.DESCRIPTION,

system_items_b.PRIMARY_UOM_CODE,

system_items_b.SUMMARY_FLAG,

system_items_b.ENABLED_FLAG,

system_items_b.START_DATE_ACTIVE,

system_items_b.END_DATE_ACTIVE,

system_items_b.BUYER_ID,

system_items_b.ACCOUNTING_RULE_ID,

system_items_b.INVOICING_RULE_ID,

system_items_b.PURCHASING_ITEM_FLAG,

system_items_b.SHIPPABLE_ITEM_FLAG,

system_items_b.CUSTOMER_ORDER_FLAG,

system_items_b.INTERNAL_ORDER_FLAG,

system_items_b.SERVICE_ITEM_FLAG,

system_items_b.INVENTORY_ITEM_FLAG,

system_items_b.ENG_ITEM_FLAG,

system_items_b.INVENTORY_ASSET_FLAG,

system_items_b.PURCHASING_ENABLED_FLAG,

system_items_b.CUSTOMER_ORDER_ENABLED_FLAG,

system_items_b.INTERNAL_ORDER_ENABLED_FLAG,

system_items_b.SO_TRANSACTIONS_FLAG,

system_items_b.MTL_TRANSACTIONS_ENABLED_FLAG,

system_items_b.STOCK_ENABLED_FLAG,

system_items_b.BOM_ENABLED_FLAG,

system_items_b.BUILD_IN_WIP_FLAG,

system_items_b.REVISION_QTY_CONTROL_CODE,

system_items_b.ITEM_CATALOG_GROUP_ID,

system_items_b.CATALOG_STATUS_FLAG,

system_items_b.RETURNABLE_FLAG,

system_items_b.DEFAULT_SHIPPING_ORG,

system_items_b.COLLATERAL_FLAG,

system_items_b.TAXABLE_FLAG,

system_items_b.QTY_RCV_EXCEPTION_CODE,

system_items_b.ALLOW_ITEM_DESC_UPDATE_FLAG,

system_items_b.INSPECTION_REQUIRED_FLAG,

system_items_b.RECEIPT_REQUIRED_FLAG,

system_items_b.MARKET_PRICE,

system_items_b.HAZARD_CLASS_ID,

system_items_b.RFQ_REQUIRED_FLAG,

system_items_b.QTY_RCV_TOLERANCE,

system_items_b.LIST_PRICE_PER_UNIT,

system_items_b.UN_NUMBER_ID,

system_items_b.PRICE_TOLERANCE_PERCENT,

system_items_b.ASSET_CATEGORY_ID,

system_items_b.ROUNDING_FACTOR,

system_items_b.UNIT_OF_ISSUE,

system_items_b.ENFORCE_SHIP_TO_LOCATION_CODE,

system_items_b.ALLOW_SUBSTITUTE_RECEIPTS_FLAG,

system_items_b.ALLOW_UNORDERED_RECEIPTS_FLAG,

system_items_b.ALLOW_EXPRESS_DELIVERY_FLAG,

system_items_b.DAYS_EARLY_RECEIPT_ALLOWED,

system_items_b.DAYS_LATE_RECEIPT_ALLOWED,

system_items_b.RECEIPT_DAYS_EXCEPTION_CODE,

system_items_b.RECEIVING_ROUTING_ID,

system_items_b.INVOICE_CLOSE_TOLERANCE,

system_items_b.RECEIVE_CLOSE_TOLERANCE,

system_items_b.AUTO_LOT_ALPHA_PREFIX,

system_items_b.START_AUTO_LOT_NUMBER,

system_items_b.LOT_CONTROL_CODE,

system_items_b.SHELF_LIFE_CODE,

system_items_b.SHELF_LIFE_DAYS,

system_items_b.SERIAL_NUMBER_CONTROL_CODE,

system_items_b.START_AUTO_SERIAL_NUMBER,

system_items_b.AUTO_SERIAL_ALPHA_PREFIX,

system_items_b.SOURCE_TYPE,

system_items_b.SOURCE_ORGANIZATION_ID,

system_items_b.SOURCE_SUBINVENTORY,

system_items_b.EXPENSE_ACCOUNT,

system_items_b.ENCUMBRANCE_ACCOUNT,

system_items_b.RESTRICT_SUBINVENTORIES_CODE,

system_items_b.UNIT_WEIGHT,

system_items_b.WEIGHT_UOM_CODE,

system_items_b.VOLUME_UOM_CODE,

system_items_b.UNIT_VOLUME,

system_items_b.RESTRICT_LOCATORS_CODE,

system_items_b.LOCATION_CONTROL_CODE,

system_items_b.SHRINKAGE_RATE,

system_items_b.ACCEPTABLE_EARLY_DAYS,

system_items_b.PLANNING_TIME_FENCE_CODE,

system_items_b.DEMAND_TIME_FENCE_CODE,

system_items_b.LEAD_TIME_LOT_SIZE,

system_items_b.STD_LOT_SIZE,

system_items_b.CUM_MANUFACTURING_LEAD_TIME,

system_items_b.OVERRUN_PERCENTAGE,

system_items_b.MRP_CALCULATE_ATP_FLAG,

system_items_b.ACCEPTABLE_RATE_INCREASE,

system_items_b.ACCEPTABLE_RATE_DECREASE,

system_items_b.CUMULATIVE_TOTAL_LEAD_TIME,

system_items_b.PLANNING_TIME_FENCE_DAYS,

system_items_b.DEMAND_TIME_FENCE_DAYS,

system_items_b.END_ASSEMBLY_PEGGING_FLAG,

system_items_b.REPETITIVE_PLANNING_FLAG,

system_items_b.PLANNING_EXCEPTION_SET,

system_items_b.BOM_ITEM_TYPE,

system_items_b.PICK_COMPONENTS_FLAG,

system_items_b.REPLENISH_TO_ORDER_FLAG,

system_items_b.BASE_ITEM_ID,

system_items_b.ATP_COMPONENTS_FLAG,

system_items_b.ATP_FLAG,

system_items_b.FIXED_LEAD_TIME,

system_items_b.VARIABLE_LEAD_TIME,

system_items_b.WIP_SUPPLY_LOCATOR_ID,

system_items_b.WIP_SUPPLY_TYPE,

system_items_b.WIP_SUPPLY_SUBINVENTORY,

system_items_b.PRIMARY_UNIT_OF_MEASURE,

system_items_b.ALLOWED_UNITS_LOOKUP_CODE,

system_items_b.COST_OF_SALES_ACCOUNT,

system_items_b.SALES_ACCOUNT,

system_items_b.DEFAULT_INCLUDE_IN_ROLLUP_FLAG,

system_items_b.INVENTORY_ITEM_STATUS_CODE,

system_items_b.INVENTORY_PLANNING_CODE,

system_items_b.PLANNER_CODE,

system_items_b.PLANNING_MAKE_BUY_CODE,

system_items_b.FIXED_LOT_MULTIPLIER,

system_items_b.ROUNDING_CONTROL_TYPE,

system_items_b.CARRYING_COST,

system_items_b.POSTPROCESSING_LEAD_TIME,

system_items_b.PREPROCESSING_LEAD_TIME,

system_items_b.FULL_LEAD_TIME,

system_items_b.ORDER_COST,

system_items_b.MRP_SAFETY_STOCK_PERCENT,

system_items_b.MRP_SAFETY_STOCK_CODE,

system_items_b.MIN_MINMAX_QUANTITY,

system_items_b.MAX_MINMAX_QUANTITY,

system_items_b.MINIMUM_ORDER_QUANTITY,

system_items_b.FIXED_ORDER_QUANTITY,

system_items_b.FIXED_DAYS_SUPPLY,

system_items_b.MAXIMUM_ORDER_QUANTITY,

system_items_b.ATP_RULE_ID,

system_items_b.PICKING_RULE_ID,

system_items_b.RESERVABLE_TYPE,

system_items_b.POSITIVE_MEASUREMENT_ERROR,

system_items_b.NEGATIVE_MEASUREMENT_ERROR,

system_items_b.ENGINEERING_ECN_CODE,

system_items_b.ENGINEERING_ITEM_ID,

system_items_b.ENGINEERING_DATE,

system_items_b.SERVICE_STARTING_DELAY,

system_items_b.VENDOR_WARRANTY_FLAG,

system_items_b.SERVICEABLE_COMPONENT_FLAG,

system_items_b.SERVICEABLE_PRODUCT_FLAG,

system_items_b.BASE_WARRANTY_SERVICE_ID,

system_items_b.PAYMENT_TERMS_ID,

system_items_b.PREVENTIVE_MAINTENANCE_FLAG,

system_items_b.PRIMARY_SPECIALIST_ID,

system_items_b.SECONDARY_SPECIALIST_ID,

system_items_b.SERVICEABLE_ITEM_CLASS_ID,

system_items_b.TIME_BILLABLE_FLAG,

system_items_b.MATERIAL_BILLABLE_FLAG,

system_items_b.EXPENSE_BILLABLE_FLAG,

system_items_b.PRORATE_SERVICE_FLAG,

system_items_b.COVERAGE_SCHEDULE_ID,

system_items_b.SERVICE_DURATION_PERIOD_CODE,

system_items_b.SERVICE_DURATION,

system_items_b.WARRANTY_VENDOR_ID,

system_items_b.MAX_WARRANTY_AMOUNT,

system_items_b.RESPONSE_TIME_PERIOD_CODE,

system_items_b.RESPONSE_TIME_VALUE,

system_items_b.NEW_REVISION_CODE,

system_items_b.INVOICEABLE_ITEM_FLAG,

system_items_b.TAX_CODE,

system_items_b.INVOICE_ENABLED_FLAG,

system_items_b.MUST_USE_APPROVED_VENDOR_FLAG,

system_items_b.REQUEST_ID,

system_items_b.PROGRAM_APPLICATION_ID,

system_items_b.PROGRAM_ID,

system_items_b.PROGRAM_UPDATE_DATE,

system_items_b.OUTSIDE_OPERATION_FLAG,

system_items_b.OUTSIDE_OPERATION_UOM_TYPE,

system_items_b.SAFETY_STOCK_BUCKET_DAYS,

system_items_b.AUTO_REDUCE_MPS,

system_items_b.COSTING_ENABLED_FLAG,

system_items_b.AUTO_CREATED_CONFIG_FLAG,

system_items_b.CYCLE_COUNT_ENABLED_FLAG,

system_items_b.ITEM_TYPE,

system_items_b.MODEL_CONFIG_CLAUSE_NAME,

system_items_b.SHIP_MODEL_COMPLETE_FLAG,

system_items_b.MRP_PLANNING_CODE,

system_items_b.RETURN_INSPECTION_REQUIREMENT,

system_items_b.ATO_FORECAST_CONTROL,

system_items_b.RELEASE_TIME_FENCE_CODE,

system_items_b.RELEASE_TIME_FENCE_DAYS,

system_items_b.CONTAINER_ITEM_FLAG,

system_items_b.VEHICLE_ITEM_FLAG,

system_items_b.MAXIMUM_LOAD_WEIGHT,

system_items_b.MINIMUM_FILL_PERCENT,

system_items_b.CONTAINER_TYPE_CODE,

system_items_b.INTERNAL_VOLUME,

system_items_b.WH_UPDATE_DATE,

system_items_b.PRODUCT_FAMILY_ITEM_ID,

system_items_b.GLOBAL_ATTRIBUTE_CATEGORY,

system_items_b.GLOBAL_ATTRIBUTE1,

system_items_b.GLOBAL_ATTRIBUTE2,

system_items_b.GLOBAL_ATTRIBUTE3,

system_items_b.GLOBAL_ATTRIBUTE4,

system_items_b.GLOBAL_ATTRIBUTE5,

system_items_b.GLOBAL_ATTRIBUTE6,

system_items_b.GLOBAL_ATTRIBUTE7,

system_items_b.GLOBAL_ATTRIBUTE8,

system_items_b.GLOBAL_ATTRIBUTE9,

system_items_b.GLOBAL_ATTRIBUTE10,

system_items_b.PURCHASING_TAX_CODE,

system_items_b.OVERCOMPLETION_TOLERANCE_TYPE,

system_items_b.OVERCOMPLETION_TOLERANCE_VALUE,

system_items_b.EFFECTIVITY_CONTROL,

system_items_b.CHECK_SHORTAGES_FLAG,

system_items_b.OVER_SHIPMENT_TOLERANCE,

system_items_b.UNDER_SHIPMENT_TOLERANCE,

system_items_b.OVER_RETURN_TOLERANCE,

system_items_b.UNDER_RETURN_TOLERANCE,

system_items_b.EQUIPMENT_TYPE,

system_items_b.RECOVERED_PART_DISP_CODE,

system_items_b.DEFECT_TRACKING_ON_FLAG,

system_items_b.USAGE_ITEM_FLAG,

system_items_b.EVENT_FLAG,

system_items_b.ELECTRONIC_FLAG,

system_items_b.DOWNLOADABLE_FLAG,

system_items_b.VOL_DISCOUNT_EXEMPT_FLAG,

system_items_b.COUPON_EXEMPT_FLAG,

system_items_b.COMMS_NL_TRACKABLE_FLAG,

system_items_b.ASSET_CREATION_CODE,

system_items_b.COMMS_ACTIVATION_REQD_FLAG,

system_items_b.ORDERABLE_ON_WEB_FLAG,

system_items_b.BACK_ORDERABLE_FLAG,

system_items_b.WEB_STATUS,

system_items_b.INDIVISIBLE_FLAG,

system_items_b.DIMENSION_UOM_CODE,

system_items_b.UNIT_LENGTH,

system_items_b.UNIT_WIDTH,

system_items_b.UNIT_HEIGHT,

system_items_b.BULK_PICKED_FLAG,

system_items_b.LOT_STATUS_ENABLED,

system_items_b.DEFAULT_LOT_STATUS_ID,

system_items_b.SERIAL_STATUS_ENABLED,

system_items_b.DEFAULT_SERIAL_STATUS_ID,

system_items_b.LOT_SPLIT_ENABLED,

system_items_b.LOT_MERGE_ENABLED,

system_items_b.INVENTORY_CARRY_PENALTY,

system_items_b.OPERATION_SLACK_PENALTY,

system_items_b.FINANCING_ALLOWED_FLAG,

system_items_b.EAM_ITEM_TYPE,

system_items_b.EAM_ACTIVITY_TYPE_CODE,

system_items_b.EAM_ACTIVITY_CAUSE_CODE,

system_items_b.EAM_ACT_NOTIFICATION_FLAG,

system_items_b.EAM_ACT_SHUTDOWN_STATUS,

system_items_b.DUAL_UOM_CONTROL,

system_items_b.SECONDARY_UOM_CODE,

system_items_b.DUAL_UOM_DEVIATION_HIGH,

system_items_b.DUAL_UOM_DEVIATION_LOW,

system_items_b.CONTRACT_ITEM_TYPE_CODE,

system_items_b.SUBSCRIPTION_DEPEND_FLAG,

system_items_b.SERV_REQ_ENABLED_CODE,

system_items_b.SERV_BILLING_ENABLED_FLAG,

system_items_b.SERV_IMPORTANCE_LEVEL,

system_items_b.PLANNED_INV_POINT_FLAG,

system_items_b.LOT_TRANSLATE_ENABLED,

system_items_b.DEFAULT_SO_SOURCE_TYPE,

system_items_b.CREATE_SUPPLY_FLAG,

system_items_b.SUBSTITUTION_WINDOW_CODE,

system_items_b.SUBSTITUTION_WINDOW_DAYS,

system_items_b.IB_ITEM_INSTANCE_CLASS,

system_items_b.CONFIG_MODEL_TYPE,

system_items_b.LOT_SUBSTITUTION_ENABLED,

system_items_b.MINIMUM_LICENSE_QUANTITY,

system_items_b.EAM_ACTIVITY_SOURCE_CODE,

system_items_b.LIFECYCLE_ID,

system_items_b.CURRENT_PHASE_ID,

system_items_b.OBJECT_VERSION_NUMBER,

system_items_b.TRACKING_QUANTITY_IND,

system_items_b.ONT_PRICING_QTY_SOURCE,

system_items_b.SECONDARY_DEFAULT_IND,

system_items_b.OPTION_SPECIFIC_SOURCED,

system_items_b.APPROVAL_STATUS,

system_items_b.VMI_MINIMUM_UNITS,

system_items_b.VMI_MINIMUM_DAYS,

system_items_b.VMI_MAXIMUM_UNITS,

system_items_b.VMI_MAXIMUM_DAYS,

system_items_b.VMI_FIXED_ORDER_QUANTITY,

system_items_b.SO_AUTHORIZATION_FLAG,

system_items_b.CONSIGNED_FLAG,

system_items_b.ASN_AUTOEXPIRE_FLAG,

system_items_b.VMI_FORECAST_TYPE,

system_items_b.FORECAST_HORIZON,

system_items_b.EXCLUDE_FROM_BUDGET_FLAG,

system_items_b.DAYS_TGT_INV_SUPPLY,

system_items_b.DAYS_TGT_INV_WINDOW,

system_items_b.DAYS_MAX_INV_SUPPLY,

system_items_b.DAYS_MAX_INV_WINDOW,

system_items_b.DRP_PLANNED_FLAG,

system_items_b.CRITICAL_COMPONENT_FLAG,

system_items_b.CONTINOUS_TRANSFER,

system_items_b.CONVERGENCE,

system_items_b.DIVERGENCE,

system_items_b.CONFIG_ORGS,

system_items_b.CONFIG_MATCH,

system_items_b.GLOBAL_ATTRIBUTE11,

system_items_b.GLOBAL_ATTRIBUTE12,

system_items_b.GLOBAL_ATTRIBUTE13,

system_items_b.GLOBAL_ATTRIBUTE14,

system_items_b.GLOBAL_ATTRIBUTE15,

system_items_b.GLOBAL_ATTRIBUTE16,

system_items_b.GLOBAL_ATTRIBUTE17,

system_items_b.GLOBAL_ATTRIBUTE18,

system_items_b.GLOBAL_ATTRIBUTE19,

system_items_b.GLOBAL_ATTRIBUTE20,

system_items_b.ATTRIBUTE16,

system_items_b.ATTRIBUTE17,

system_items_b.ATTRIBUTE18,

system_items_b.ATTRIBUTE19,

system_items_b.ATTRIBUTE20,

system_items_b.ATTRIBUTE21,

system_items_b.ATTRIBUTE22,

system_items_b.ATTRIBUTE23,

system_items_b.ATTRIBUTE24,

system_items_b.ATTRIBUTE25,

system_items_b.ATTRIBUTE26,

system_items_b.ATTRIBUTE27,

system_items_b.ATTRIBUTE28,

system_items_b.ATTRIBUTE29,

system_items_b.ATTRIBUTE30,

system_items_b.CAS_NUMBER,

system_items_b.CHILD_LOT_FLAG,

system_items_b.CHILD_LOT_PREFIX,

system_items_b.CHILD_LOT_STARTING_NUMBER,

system_items_b.CHILD_LOT_VALIDATION_FLAG,

system_items_b.COPY_LOT_ATTRIBUTE_FLAG,

system_items_b.DEFAULT_GRADE,

system_items_b.EXPIRATION_ACTION_CODE,

system_items_b.EXPIRATION_ACTION_INTERVAL,

system_items_b.GRADE_CONTROL_FLAG,

system_items_b.HAZARDOUS_MATERIAL_FLAG,

system_items_b.HOLD_DAYS,

system_items_b.LOT_DIVISIBLE_FLAG,

system_items_b.MATURITY_DAYS,

system_items_b.PARENT_CHILD_GENERATION_FLAG,

system_items_b.PROCESS_COSTING_ENABLED_FLAG,

system_items_b.PROCESS_EXECUTION_ENABLED_FLAG,

system_items_b.PROCESS_QUALITY_ENABLED_FLAG,

system_items_b.PROCESS_SUPPLY_LOCATOR_ID,

system_items_b.PROCESS_SUPPLY_SUBINVENTORY,

system_items_b.PROCESS_YIELD_LOCATOR_ID,

system_items_b.PROCESS_YIELD_SUBINVENTORY,

system_items_b.RECIPE_ENABLED_FLAG,

system_items_b.RETEST_INTERVAL,

system_items_b.CHARGE_PERIODICITY_CODE,

system_items_b.REPAIR_LEADTIME,

system_items_b.REPAIR_YIELD,

system_items_b.PREPOSITION_POINT,

system_items_b.REPAIR_PROGRAM,

system_items_b.SUBCONTRACTING_COMPONENT,

system_items_b.OUTSOURCED_ASSEMBLY,

system_items_b.EGO_MASTER_ITEMS_DFF_CTX,

system_items_b.GDSN_OUTBOUND_ENABLED_FLAG,

system_items_b.TRADE_ITEM_DESCRIPTOR,

system_items_b.STYLE_ITEM_ID,

system_items_b.STYLE_ITEM_FLAG,

system_items_b.LAST_SUBMITTED_NIR_ID,

system_items_b.DEFAULT_MATERIAL_STATUS_ID,

system_items_b.SERIAL_TAGGING_FLAG

FROM

[..\..\3_Data_Repository\Oracle_EBS\inv.mtl_system_items_b.qvd](qvd);

Inner Join(MMT)

PARAMETERS:

LOAD

MTL_P.ORGANIZATION_ID as %ORGANIZATION_ID,

MTL_P.ORGANIZATION_CODE,

MTL_P.PROCESS_ENABLED_FLAG;

LOAD

MTL_P.ORGANIZATION_ID,

MTL_P.ORGANIZATION_CODE,

MTL_P.LAST_UPDATE_DATE,

MTL_P.LAST_UPDATED_BY,

MTL_P.CREATION_DATE,

MTL_P.CREATED_BY,

MTL_P.LAST_UPDATE_LOGIN,

MTL_P.MASTER_ORGANIZATION_ID,

MTL_P.PRIMARY_COST_METHOD,

MTL_P.COST_ORGANIZATION_ID,

MTL_P.DEFAULT_MATERIAL_COST_ID,

MTL_P.CALENDAR_EXCEPTION_SET_ID,

MTL_P.CALENDAR_CODE,

MTL_P.GENERAL_LEDGER_UPDATE_CODE,

MTL_P.DEFAULT_ATP_RULE_ID,

MTL_P.DEFAULT_PICKING_RULE_ID,

MTL_P.DEFAULT_LOCATOR_ORDER_VALUE,

MTL_P.DEFAULT_SUBINV_ORDER_VALUE,

MTL_P.NEGATIVE_INV_RECEIPT_CODE,

MTL_P.STOCK_LOCATOR_CONTROL_CODE,

MTL_P.MATERIAL_ACCOUNT,

MTL_P.MATERIAL_OVERHEAD_ACCOUNT,

MTL_P.MATL_OVHD_ABSORPTION_ACCT,

MTL_P.RESOURCE_ACCOUNT,

MTL_P.PURCHASE_PRICE_VAR_ACCOUNT,

MTL_P.AP_ACCRUAL_ACCOUNT,

MTL_P.OVERHEAD_ACCOUNT,

MTL_P.OUTSIDE_PROCESSING_ACCOUNT,

MTL_P.INTRANSIT_INV_ACCOUNT,

MTL_P.INTERORG_RECEIVABLES_ACCOUNT,

MTL_P.INTERORG_PRICE_VAR_ACCOUNT,

MTL_P.INTERORG_PAYABLES_ACCOUNT,

MTL_P.COST_OF_SALES_ACCOUNT,

MTL_P.ENCUMBRANCE_ACCOUNT,

MTL_P.PROJECT_COST_ACCOUNT,

MTL_P.INTERORG_TRANSFER_CR_ACCOUNT,

MTL_P.MATL_INTERORG_TRANSFER_CODE,

MTL_P.INTERORG_TRNSFR_CHARGE_PERCENT,

MTL_P.SOURCE_ORGANIZATION_ID,

MTL_P.SOURCE_SUBINVENTORY,

MTL_P.SOURCE_TYPE,

MTL_P.ORG_MAX_WEIGHT,

MTL_P.ORG_MAX_WEIGHT_UOM_CODE,

MTL_P.ORG_MAX_VOLUME,

MTL_P.ORG_MAX_VOLUME_UOM_CODE,

MTL_P.SERIAL_NUMBER_TYPE,

MTL_P.AUTO_SERIAL_ALPHA_PREFIX,

MTL_P.START_AUTO_SERIAL_NUMBER,

MTL_P.AUTO_LOT_ALPHA_PREFIX,

MTL_P.LOT_NUMBER_UNIQUENESS,

MTL_P.LOT_NUMBER_GENERATION,

MTL_P.LOT_NUMBER_ZERO_PADDING,

MTL_P.LOT_NUMBER_LENGTH,

MTL_P.STARTING_REVISION,

MTL_P.ATTRIBUTE_CATEGORY,

MTL_P.ATTRIBUTE1,

MTL_P.ATTRIBUTE2,

MTL_P.ATTRIBUTE3,

MTL_P.ATTRIBUTE4,

MTL_P.ATTRIBUTE5,

MTL_P.ATTRIBUTE6,

MTL_P.ATTRIBUTE7,

MTL_P.ATTRIBUTE8,

MTL_P.ATTRIBUTE9,

MTL_P.ATTRIBUTE10,

MTL_P.ATTRIBUTE11,

MTL_P.ATTRIBUTE12,

MTL_P.ATTRIBUTE13,

MTL_P.ATTRIBUTE14,

MTL_P.ATTRIBUTE15,

MTL_P.DEFAULT_DEMAND_CLASS,

MTL_P.ENCUMBRANCE_REVERSAL_FLAG,

MTL_P.MAINTAIN_FIFO_QTY_STACK_TYPE,

MTL_P.INVOICE_PRICE_VAR_ACCOUNT,

MTL_P.AVERAGE_COST_VAR_ACCOUNT,

MTL_P.SALES_ACCOUNT,

MTL_P.EXPENSE_ACCOUNT,

MTL_P.SERIAL_NUMBER_GENERATION,

MTL_P.REQUEST_ID,

MTL_P.PROGRAM_APPLICATION_ID,

MTL_P.PROGRAM_ID,

MTL_P.PROGRAM_UPDATE_DATE,

MTL_P.GLOBAL_ATTRIBUTE_CATEGORY,

MTL_P.GLOBAL_ATTRIBUTE1,

MTL_P.GLOBAL_ATTRIBUTE2,

MTL_P.GLOBAL_ATTRIBUTE3,

MTL_P.GLOBAL_ATTRIBUTE4,

MTL_P.GLOBAL_ATTRIBUTE5,

MTL_P.GLOBAL_ATTRIBUTE6,

MTL_P.GLOBAL_ATTRIBUTE7,

MTL_P.GLOBAL_ATTRIBUTE8,

MTL_P.GLOBAL_ATTRIBUTE9,

MTL_P.GLOBAL_ATTRIBUTE10,

MTL_P.GLOBAL_ATTRIBUTE11,

MTL_P.GLOBAL_ATTRIBUTE12,

MTL_P.GLOBAL_ATTRIBUTE13,

MTL_P.GLOBAL_ATTRIBUTE14,

MTL_P.GLOBAL_ATTRIBUTE15,

MTL_P.GLOBAL_ATTRIBUTE16,

MTL_P.GLOBAL_ATTRIBUTE17,

MTL_P.GLOBAL_ATTRIBUTE18,

MTL_P.GLOBAL_ATTRIBUTE19,

MTL_P.GLOBAL_ATTRIBUTE20,

MTL_P.MAT_OVHD_COST_TYPE_ID,

MTL_P.PROJECT_REFERENCE_ENABLED,

MTL_P.PM_COST_COLLECTION_ENABLED,

MTL_P.PROJECT_CONTROL_LEVEL,

MTL_P.AVG_RATES_COST_TYPE_ID,

MTL_P.TXN_APPROVAL_TIMEOUT_PERIOD,

MTL_P.MO_SOURCE_REQUIRED,

MTL_P.MO_PICK_CONFIRM_REQUIRED,

MTL_P.MO_APPROVAL_TIMEOUT_ACTION,

MTL_P.BORRPAY_MATL_VAR_ACCOUNT,

MTL_P.BORRPAY_MOH_VAR_ACCOUNT,

MTL_P.BORRPAY_RES_VAR_ACCOUNT,

MTL_P.BORRPAY_OSP_VAR_ACCOUNT,

MTL_P.BORRPAY_OVH_VAR_ACCOUNT,

MTL_P.PROCESS_ENABLED_FLAG,

MTL_P.PROCESS_ORGN_CODE,

MTL_P.WSM_ENABLED_FLAG,

MTL_P.DEFAULT_COST_GROUP_ID,

MTL_P.LPN_PREFIX,

MTL_P.LPN_SUFFIX,

MTL_P.LPN_STARTING_NUMBER,

MTL_P.WMS_ENABLED_FLAG,

MTL_P.PREGEN_PUTAWAY_TASKS_FLAG,

MTL_P.REGENERATION_INTERVAL,

MTL_P.TIMEZONE_ID,

MTL_P.MAX_PICKS_BATCH,

MTL_P.DEFAULT_WMS_PICKING_RULE_ID,

MTL_P.DEFAULT_PUT_AWAY_RULE_ID,

MTL_P.DEFAULT_TASK_ASSIGN_RULE_ID,

MTL_P.DEFAULT_LABEL_COMP_RULE_ID,

MTL_P.DEFAULT_CARTON_RULE_ID,

MTL_P.DEFAULT_CYC_COUNT_HEADER_ID,

MTL_P.CROSSDOCK_FLAG,

MTL_P.CARTONIZATION_FLAG,

MTL_P.COST_CUTOFF_DATE,

MTL_P.ENABLE_COSTING_BY_CATEGORY,

MTL_P.COST_GROUP_ACCOUNTING,

MTL_P.ALLOCATE_SERIAL_FLAG,

MTL_P.DEFAULT_PICK_TASK_TYPE_ID,

MTL_P.DEFAULT_CC_TASK_TYPE_ID,

MTL_P.DEFAULT_PUTAWAY_TASK_TYPE_ID,

MTL_P.DEFAULT_REPL_TASK_TYPE_ID,

MTL_P.EAM_ENABLED_FLAG,

MTL_P.MAINT_ORGANIZATION_ID,

MTL_P.PRIORITIZE_WIP_JOBS,

MTL_P.DEFAULT_CROSSDOCK_SUBINVENTORY,

MTL_P.SKIP_TASK_WAITING_MINUTES,

MTL_P.QA_SKIPPING_INSP_FLAG,

MTL_P.DEFAULT_CROSSDOCK_LOCATOR_ID,

MTL_P.DEFAULT_MOXFER_TASK_TYPE_ID,

MTL_P.DEFAULT_MOISSUE_TASK_TYPE_ID,

MTL_P.DEFAULT_MATL_OVHD_COST_ID,

MTL_P.DISTRIBUTED_ORGANIZATION_FLAG,

MTL_P.CARRIER_MANIFESTING_FLAG,

MTL_P.DISTRIBUTION_ACCOUNT_ID,

MTL_P.DIRECT_SHIPPING_ALLOWED,

MTL_P.DEFAULT_PICK_OP_PLAN_ID,

MTL_P.MAX_CLUSTERS_ALLOWED,

MTL_P.CONSIGNED_FLAG,

MTL_P.CARTONIZE_SALES_ORDERS,

MTL_P.CARTONIZE_MANUFACTURING,

MTL_P.DEFER_LOGICAL_TRANSACTIONS,

MTL_P.WIP_OVERPICK_ENABLED,

MTL_P.OVPK_TRANSFER_ORDERS_ENABLED,

MTL_P.TOTAL_LPN_LENGTH,

MTL_P.UCC_128_SUFFIX_FLAG,

MTL_P.WCS_ENABLED,

MTL_P.AUTO_DEL_ALLOC_FLAG,

MTL_P.RFID_VERIF_PCNT_THRESHOLD,

MTL_P.ALLOW_DIFFERENT_STATUS,

MTL_P.CHILD_LOT_ALPHA_PREFIX,

MTL_P.CHILD_LOT_NUMBER_LENGTH,

MTL_P.CHILD_LOT_VALIDATION_FLAG,

MTL_P.CHILD_LOT_ZERO_PADDING_FLAG,

MTL_P.COPY_LOT_ATTRIBUTE_FLAG,

MTL_P.CREATE_LOT_UOM_CONVERSION,

MTL_P.GENEALOGY_FORMULA_SECURITY,

MTL_P.PARENT_CHILD_GENERATION_FLAG,

MTL_P.RULES_OVERRIDE_LOT_RESERVATION,

MTL_P.YARD_MANAGEMENT_ENABLED_FLAG,

MTL_P.TRADING_PARTNER_ORG_FLAG,

MTL_P.DEFERRED_COGS_ACCOUNT,

MTL_P.DEFAULT_CROSSDOCK_CRITERIA_ID,

MTL_P.ENFORCE_LOCATOR_ALIS_UNQ_FLAG,

MTL_P.EPC_GENERATION_ENABLED_FLAG,

MTL_P.COMPANY_PREFIX,

MTL_P.COMPANY_PREFIX_INDEX,

MTL_P.COMMERCIAL_GOVT_ENTITY_NUMBER,

MTL_P.LABOR_MANAGEMENT_ENABLED_FLAG,

MTL_P.DEFAULT_STATUS_ID,

MTL_P.LCM_ENABLED_FLAG,

MTL_P.LCM_VAR_ACCOUNT,

MTL_P.OPSM_ENABLED_FLAG,

MTL_P.ALLOCATE_LOT_FLAG,

MTL_P.CAT_WT_ACCOUNT,

MTL_P.TRADING_PARTNER_ORG_TYPE

FROM

[..\..\3_Data_Repository\Oracle_EBS\INV.MTL_PARAMETERS.qvd](qvd);

Inner Join(MMT)

CST:

LOAD cai.ORGANIZATION_ID as %ORGANIZATION_ID,

cai.LEDGER_ID as %LEDGER_ID,

cai.LEGAL_ENTITY,

cai.OPERATING_UNIT

FROM

[..\..\3_Data_Repository\Oracle_EBS\apps.cst_acct_info_v.qvd](qvd);

Inner join(MMT)

LEDGERS:

LOAD

GLLedger.LEDGER_ID as %LEDGER_ID,

GLLedger.CURRENCY_CODE;

LOAD GLLedger.LEDGER_ID,

GLLedger.CURRENCY_CODE,

GLLedger.NAME,

GLLedger.SHORT_NAME,

GLLedger.DESCRIPTION,

GLLedger.LEDGER_CATEGORY_CODE,

GLLedger.ALC_LEDGER_TYPE_CODE,

GLLedger.OBJECT_TYPE_CODE,

GLLedger.LE_LEDGER_TYPE_CODE,

GLLedger.COMPLETION_STATUS_CODE,

GLLedger.CONFIGURATION_ID,

GLLedger.CHART_OF_ACCOUNTS_ID,

GLLedger.CURRENCY_CODE,

GLLedger.PERIOD_SET_NAME,

GLLedger.ACCOUNTED_PERIOD_TYPE,

GLLedger.FIRST_LEDGER_PERIOD_NAME,

GLLedger.RET_EARN_CODE_COMBINATION_ID,

GLLedger.SUSPENSE_ALLOWED_FLAG,

GLLedger.ALLOW_INTERCOMPANY_POST_FLAG,

GLLedger.TRACK_ROUNDING_IMBALANCE_FLAG,

GLLedger.ENABLE_AVERAGE_BALANCES_FLAG,

GLLedger.CUM_TRANS_CODE_COMBINATION_ID,

GLLedger.RES_ENCUMB_CODE_COMBINATION_ID,

GLLedger.NET_INCOME_CODE_COMBINATION_ID,

GLLedger.ROUNDING_CODE_COMBINATION_ID,

GLLedger.ENABLE_BUDGETARY_CONTROL_FLAG,

GLLedger.REQUIRE_BUDGET_JOURNALS_FLAG,

GLLedger.ENABLE_JE_APPROVAL_FLAG,

GLLedger.ENABLE_AUTOMATIC_TAX_FLAG,

GLLedger.CONSOLIDATION_LEDGER_FLAG,

GLLedger.TRANSLATE_EOD_FLAG,

GLLedger.TRANSLATE_QATD_FLAG,

GLLedger.TRANSLATE_YATD_FLAG,

GLLedger.TRANSACTION_CALENDAR_ID,

GLLedger.DAILY_TRANSLATION_RATE_TYPE,

GLLedger.AUTOMATICALLY_CREATED_FLAG,

GLLedger.BAL_SEG_VALUE_OPTION_CODE,

GLLedger.BAL_SEG_COLUMN_NAME,

GLLedger.MGT_SEG_VALUE_OPTION_CODE,

GLLedger.MGT_SEG_COLUMN_NAME,

GLLedger.BAL_SEG_VALUE_SET_ID,

GLLedger.MGT_SEG_VALUE_SET_ID,

GLLedger.IMPLICIT_ACCESS_SET_ID,

GLLedger.CRITERIA_SET_ID,

GLLedger.FUTURE_ENTERABLE_PERIODS_LIMIT,

GLLedger.LEDGER_ATTRIBUTES,

GLLedger.IMPLICIT_LEDGER_SET_ID,

GLLedger.LATEST_OPENED_PERIOD_NAME,

GLLedger.LATEST_ENCUMBRANCE_YEAR,

GLLedger.PERIOD_AVERAGE_RATE_TYPE,

GLLedger.PERIOD_END_RATE_TYPE,

GLLedger.BUDGET_PERIOD_AVG_RATE_TYPE,

GLLedger.BUDGET_PERIOD_END_RATE_TYPE,

GLLedger.SLA_ACCOUNTING_METHOD_CODE,

GLLedger.SLA_ACCOUNTING_METHOD_TYPE,

GLLedger.SLA_DESCRIPTION_LANGUAGE,

GLLedger.SLA_ENTERED_CUR_BAL_SUS_CCID,

GLLedger.SLA_SEQUENCING_FLAG,

GLLedger.SLA_BAL_BY_LEDGER_CURR_FLAG,

GLLedger.SLA_LEDGER_CUR_BAL_SUS_CCID,

GLLedger.ENABLE_SECONDARY_TRACK_FLAG,

GLLedger.ENABLE_REVAL_SS_TRACK_FLAG,

GLLedger.LAST_UPDATE_DATE,

GLLedger.LAST_UPDATED_BY,

GLLedger.CREATION_DATE,

GLLedger.CREATED_BY,

GLLedger.LAST_UPDATE_LOGIN,

GLLedger.CONTEXT,

GLLedger.ATTRIBUTE1,

GLLedger.ATTRIBUTE2,

GLLedger.ATTRIBUTE3,

GLLedger.ATTRIBUTE4,

GLLedger.ATTRIBUTE5,

GLLedger.ATTRIBUTE6,

GLLedger.ATTRIBUTE7,

GLLedger.ATTRIBUTE8,

GLLedger.ATTRIBUTE9,

GLLedger.ATTRIBUTE10,

GLLedger.ATTRIBUTE11,

GLLedger.ATTRIBUTE12,

GLLedger.ATTRIBUTE13,

GLLedger.ATTRIBUTE14,

GLLedger.ATTRIBUTE15,

GLLedger.ENABLE_RECONCILIATION_FLAG,

GLLedger.CREATE_JE_FLAG,

GLLedger.SLA_LEDGER_CASH_BASIS_FLAG,

GLLedger.COMPLETE_FLAG,

GLLedger.COMMITMENT_BUDGET_FLAG,

GLLedger.NET_CLOSING_BAL_FLAG,

GLLedger.AUTOMATE_SEC_JRNL_REV_FLAG

FROM

[..\..\3_Data_Repository\Oracle_EBS\GL_Ledgers.qvd](qvd);

RCV:

LOAD

//rcv.TRANSACTION_ID & '|' & rcv.VENDOR_ID & 'RCV' as %Key,//testing

rcv.TRANSACTION_ID & '|' &'RCV' as %Key,

AutoNumberHash128(rcv.TRANSACTION_ID & '|' &'RCV') as %TRANSACTION_KEY,

rcv.VENDOR_ID, //testing

ApplyMap('SupplierNum_Map',rcv.VENDOR_ID,NULL()) as SUPPLIER_NUM,

ApplyMap('Supplier_Map',rcv.VENDOR_ID,NULL()) as SUPPLIER,

'RCV' as Source1;

LOAD

rcv.TRANSACTION_ID,

rcv.VENDOR_ID,

rcv.TRANSACTION_TYPE,

rcv.TRANSACTION_DATE,

rcv.QUANTITY,

rcv.UNIT_OF_MEASURE,

rcv.SHIPMENT_HEADER_ID,

rcv.SHIPMENT_LINE_ID,

rcv.USER_ENTERED_FLAG,

rcv.INTERFACE_SOURCE_CODE,

rcv.INTERFACE_SOURCE_LINE_ID,

rcv.INV_TRANSACTION_ID,

rcv.SOURCE_DOCUMENT_CODE,

rcv.DESTINATION_TYPE_CODE,

rcv.PRIMARY_QUANTITY,

rcv.PRIMARY_UNIT_OF_MEASURE,

rcv.UOM_CODE,

rcv.EMPLOYEE_ID,

rcv.PARENT_TRANSACTION_ID,

rcv.PO_HEADER_ID,

rcv.PO_RELEASE_ID,

rcv.PO_LINE_ID,

rcv.PO_LINE_LOCATION_ID,

rcv.PO_DISTRIBUTION_ID,

rcv.PO_REVISION_NUM,

rcv.REQUISITION_LINE_ID,

rcv.PO_UNIT_PRICE,

rcv.CURRENCY_CODE,

rcv.CURRENCY_CONVERSION_TYPE,

rcv.CURRENCY_CONVERSION_RATE,

rcv.CURRENCY_CONVERSION_DATE,

rcv.ROUTING_HEADER_ID,

rcv.ROUTING_STEP_ID,

rcv.DELIVER_TO_PERSON_ID,

rcv.DELIVER_TO_LOCATION_ID,

rcv.VENDOR_SITE_ID,

rcv.ORGANIZATION_ID,

rcv.SUBINVENTORY,

rcv.LOCATOR_ID,

rcv.WIP_ENTITY_ID,

rcv.WIP_LINE_ID,

rcv.WIP_REPETITIVE_SCHEDULE_ID,

rcv.WIP_OPERATION_SEQ_NUM,

rcv.WIP_RESOURCE_SEQ_NUM,

rcv.BOM_RESOURCE_ID,

rcv.LOCATION_ID,

rcv.SUBSTITUTE_UNORDERED_CODE,

rcv.RECEIPT_EXCEPTION_FLAG,

rcv.INSPECTION_STATUS_CODE,

rcv.ACCRUAL_STATUS_CODE,

rcv.INSPECTION_QUALITY_CODE,

rcv.VENDOR_LOT_NUM,

rcv.RMA_REFERENCE,

rcv.COMMENTS,

rcv.ATTRIBUTE_CATEGORY,

rcv.ATTRIBUTE1,

rcv.ATTRIBUTE2,

rcv.ATTRIBUTE3,

rcv.ATTRIBUTE4,

rcv.ATTRIBUTE5,

rcv.ATTRIBUTE6,

rcv.ATTRIBUTE7,

rcv.ATTRIBUTE8,

rcv.ATTRIBUTE9,

rcv.ATTRIBUTE10,

rcv.ATTRIBUTE11,

rcv.ATTRIBUTE12,

rcv.ATTRIBUTE13,

rcv.ATTRIBUTE14,

rcv.ATTRIBUTE15,

rcv.REQ_DISTRIBUTION_ID,

rcv.DEPARTMENT_CODE,

rcv.REASON_ID,

rcv.DESTINATION_CONTEXT,

rcv.LOCATOR_ATTRIBUTE,

rcv.CHILD_INSPECTION_FLAG,

rcv.SOURCE_DOC_UNIT_OF_MEASURE,

rcv.SOURCE_DOC_QUANTITY,

rcv.INTERFACE_TRANSACTION_ID,

rcv.GROUP_ID,

rcv.MOVEMENT_ID,

rcv.INVOICE_ID,

rcv.INVOICE_STATUS_CODE,

rcv.QA_COLLECTION_ID,

rcv.MRC_CURRENCY_CONVERSION_TYPE,

rcv.MRC_CURRENCY_CONVERSION_DATE,

rcv.MRC_CURRENCY_CONVERSION_RATE,

rcv.COUNTRY_OF_ORIGIN_CODE,

rcv.MVT_STAT_STATUS,

rcv.QUANTITY_BILLED,

rcv.MATCH_FLAG,

rcv.AMOUNT_BILLED,

rcv.MATCH_OPTION,

rcv.OE_ORDER_HEADER_ID,

rcv.OE_ORDER_LINE_ID,

rcv.CUSTOMER_ID,

rcv.CUSTOMER_SITE_ID,

rcv.LPN_ID,

rcv.TRANSFER_LPN_ID,

rcv.MOBILE_TXN,

rcv.SECONDARY_QUANTITY,

rcv.SECONDARY_UNIT_OF_MEASURE,

rcv.QC_GRADE,

rcv.SECONDARY_UOM_CODE,

rcv.PA_ADDITION_FLAG,

rcv.CONSIGNED_FLAG,

rcv.SOURCE_TRANSACTION_NUM,

rcv.FROM_SUBINVENTORY,

rcv.FROM_LOCATOR_ID,

rcv.AMOUNT,

rcv.DROPSHIP_TYPE_CODE,

rcv.LPN_GROUP_ID,

rcv.JOB_ID,

rcv.TIMECARD_ID,

rcv.TIMECARD_OVN,

rcv.PROJECT_ID,

rcv.TASK_ID,

rcv.REQUESTED_AMOUNT,

rcv.MATERIAL_STORED_AMOUNT,

rcv.REPLENISH_ORDER_LINE_ID,

rcv.LCM_SHIPMENT_LINE_ID,

rcv.UNIT_LANDED_COST,

rcv.RECEIPT_CONFIRMATION_EXTRACTED,

rcv.LCM_ADJUSTMENT_NUM,

rcv.XML_DOCUMENT_ID

FROM

[..\..\3_Data_Repository\Oracle_EBS\PO.RCV_Transactions.qvd](qvd)

Where exists(mmt.RCV_TRANSACTION_ID,rcv.TRANSACTION_ID);

Concatenate(RCV)

POH:

LOAD

//poh.PO_HEADER_ID & '|' & poh.VENDOR_ID & 'POH' as %Key,//testing

poh.PO_HEADER_ID & '|' & 'POH' as %Key,

AutoNumberHash128(poh.PO_HEADER_ID & '|' &'POH') as %TRANSACTION_KEY,

poh.VENDOR_ID,//testing

ApplyMap('SupplierNum_Map',poh.VENDOR_ID,Null()) as SUPPLIER_NUM,

ApplyMap('Supplier_Map',poh.VENDOR_ID,NULL()) as SUPPLIER,

'POH' as Source1,

poh.SEGMENT1 as PO_Number;

LOAD

poh.PO_HEADER_ID,

poh.VENDOR_ID,

poh.AGENT_ID,

poh.TYPE_LOOKUP_CODE,

poh.LAST_UPDATE_DATE,

poh.LAST_UPDATED_BY,

poh.SEGMENT1,

poh.SUMMARY_FLAG,

poh.ENABLED_FLAG,

poh.SEGMENT2,

poh.SEGMENT3,

poh.SEGMENT4,

poh.SEGMENT5,

poh.START_DATE_ACTIVE,

poh.END_DATE_ACTIVE,

poh.LAST_UPDATE_LOGIN,

poh.CREATION_DATE,

poh.CREATED_BY,

poh.VENDOR_SITE_ID,

poh.VENDOR_CONTACT_ID,

poh.SHIP_TO_LOCATION_ID,

poh.BILL_TO_LOCATION_ID,

poh.TERMS_ID,

poh.SHIP_VIA_LOOKUP_CODE,

poh.FOB_LOOKUP_CODE,

poh.FREIGHT_TERMS_LOOKUP_CODE,

poh.STATUS_LOOKUP_CODE,

poh.CURRENCY_CODE,

poh.RATE_TYPE,

poh.RATE_DATE,

poh.RATE,

poh.FROM_HEADER_ID,

poh.FROM_TYPE_LOOKUP_CODE,

poh.START_DATE,

poh.END_DATE,

poh.BLANKET_TOTAL_AMOUNT,

poh.AUTHORIZATION_STATUS,

poh.REVISION_NUM,

poh.REVISED_DATE,

poh.APPROVED_FLAG,

poh.APPROVED_DATE,

poh.AMOUNT_LIMIT,

poh.MIN_RELEASE_AMOUNT,

poh.NOTE_TO_AUTHORIZER,

poh.NOTE_TO_VENDOR,

poh.NOTE_TO_RECEIVER,

poh.PRINT_COUNT,

poh.PRINTED_DATE,

poh.VENDOR_ORDER_NUM,

poh.CONFIRMING_ORDER_FLAG,

poh.COMMENTS,

poh.REPLY_DATE,

poh.REPLY_METHOD_LOOKUP_CODE,

poh.RFQ_CLOSE_DATE,

poh.QUOTE_TYPE_LOOKUP_CODE,

poh.QUOTATION_CLASS_CODE,

poh.QUOTE_WARNING_DELAY_UNIT,

poh.QUOTE_WARNING_DELAY,

poh.QUOTE_VENDOR_QUOTE_NUMBER,

poh.ACCEPTANCE_REQUIRED_FLAG,

poh.ACCEPTANCE_DUE_DATE,

poh.CLOSED_DATE,

poh.USER_HOLD_FLAG,

poh.APPROVAL_REQUIRED_FLAG,

poh.CANCEL_FLAG,

poh.FIRM_STATUS_LOOKUP_CODE,

poh.FIRM_DATE,

poh.FROZEN_FLAG,

poh.SUPPLY_AGREEMENT_FLAG,

poh.EDI_PROCESSED_FLAG,

poh.EDI_PROCESSED_STATUS,

poh.ATTRIBUTE_CATEGORY,

poh.ATTRIBUTE1,

poh.ATTRIBUTE2,

poh.ATTRIBUTE3,

poh.ATTRIBUTE4,

poh.ATTRIBUTE5,

poh.ATTRIBUTE6,

poh.ATTRIBUTE7,

poh.ATTRIBUTE8,

poh.ATTRIBUTE9,

poh.ATTRIBUTE10,

poh.ATTRIBUTE11,

poh.ATTRIBUTE12,

poh.ATTRIBUTE13,

poh.ATTRIBUTE14,

poh.ATTRIBUTE15,

poh.CLOSED_CODE,

poh.USSGL_TRANSACTION_CODE,

poh.GOVERNMENT_CONTEXT,

poh.REQUEST_ID,

poh.PROGRAM_APPLICATION_ID,

poh.PROGRAM_ID,

poh.PROGRAM_UPDATE_DATE,

poh.ORG_ID,

poh.GLOBAL_ATTRIBUTE_CATEGORY,

poh.GLOBAL_ATTRIBUTE1,

poh.GLOBAL_ATTRIBUTE2,

poh.GLOBAL_ATTRIBUTE3,

poh.GLOBAL_ATTRIBUTE4,

poh.GLOBAL_ATTRIBUTE5,

poh.GLOBAL_ATTRIBUTE6,

poh.GLOBAL_ATTRIBUTE7,

poh.GLOBAL_ATTRIBUTE8,

poh.GLOBAL_ATTRIBUTE9,

poh.GLOBAL_ATTRIBUTE10,

poh.GLOBAL_ATTRIBUTE11,

poh.GLOBAL_ATTRIBUTE12,

poh.GLOBAL_ATTRIBUTE13,

poh.GLOBAL_ATTRIBUTE14,

poh.GLOBAL_ATTRIBUTE15,

poh.GLOBAL_ATTRIBUTE16,

poh.GLOBAL_ATTRIBUTE17,

poh.GLOBAL_ATTRIBUTE18,

poh.GLOBAL_ATTRIBUTE19,

poh.GLOBAL_ATTRIBUTE20,

poh.INTERFACE_SOURCE_CODE,

poh.REFERENCE_NUM,

poh.WF_ITEM_TYPE,

poh.WF_ITEM_KEY,

poh.MRC_RATE_TYPE,

poh.MRC_RATE_DATE,

poh.MRC_RATE,

poh.PCARD_ID,

poh.PRICE_UPDATE_TOLERANCE,

poh.PAY_ON_CODE,

poh.XML_FLAG,

poh.XML_SEND_DATE,

poh.XML_CHANGE_SEND_DATE,

poh.GLOBAL_AGREEMENT_FLAG,

poh.CONSIGNED_CONSUMPTION_FLAG,

poh.CBC_ACCOUNTING_DATE,

poh.CONSUME_REQ_DEMAND_FLAG,

poh.CHANGE_REQUESTED_BY,

poh.SHIPPING_CONTROL,

poh.CONTERMS_EXIST_FLAG,

poh.CONTERMS_ARTICLES_UPD_DATE,

poh.CONTERMS_DELIV_UPD_DATE,

poh.ENCUMBRANCE_REQUIRED_FLAG,

poh.PENDING_SIGNATURE_FLAG,

poh.CHANGE_SUMMARY,

poh.DOCUMENT_CREATION_METHOD,

poh.SUBMIT_DATE,

poh.SUPPLIER_NOTIF_METHOD,

poh.FAX,

poh.EMAIL_ADDRESS,

poh.RETRO_PRICE_COMM_UPDATES_FLAG,

poh.RETRO_PRICE_APPLY_UPDATES_FLAG,

poh.UPDATE_SOURCING_RULES_FLAG,

poh.AUTO_SOURCING_FLAG,

poh.CREATED_LANGUAGE,

poh.CPA_REFERENCE,

poh.LOCK_OWNER_ROLE,

poh.LOCK_OWNER_USER_ID,

poh.SUPPLIER_AUTH_ENABLED_FLAG,

poh.CAT_ADMIN_AUTH_ENABLED_FLAG,

poh.STYLE_ID,

poh.TAX_ATTRIBUTE_UPDATE_CODE,

poh.LAST_UPDATED_PROGRAM,

poh.ENABLE_ALL_SITES,

poh.PAY_WHEN_PAID,

poh.COMM_REV_NUM,

poh.CLM_DOCUMENT_NUMBER,

poh.OTM_STATUS_CODE,

poh.OTM_RECOVERY_FLAG,

poh.AME_APPROVAL_ID,

poh.AME_TRANSACTION_TYPE

FROM

[..\..\3_Data_Repository\Oracle_EBS\apps.po_headers_all.qvd](qvd)

Where exists(mmt.TRANSACTION_SOURCE_ID,poh.PO_HEADER_ID);

10.

Link_Table:

LOAD Distinct

//%INVENTORYKEY,

%LinkINVENTORYKEY,

AutoNumberHash128(mmt.RCV_TRANSACTION_ID & '|' &'RCV' ) as %TRANSACTION_KEY,

mmt.RCV_TRANSACTION_ID & '|' &'RCV' as %TempKey,

0 as Is74Flag,

'No' as Source,

mmt.PRIMARY_QUANTITY as QTY

Resident MMT

Where mmt.TRANSACTION_TYPE_ID <> 74;

Concatenate

LOAD Distinct

//%INVENTORYKEY,

%LinkINVENTORYKEY,

AutoNumberHash128(mmt.TRANSACTION_SOURCE_ID & '|' &'POH') as %TRANSACTION_KEY,

mmt.TRANSACTION_SOURCE_ID & '|' &'POH' as %TempKey,

1 as Is74Flag,

'Yes' as Source,

mmt.PRIMARY_QUANTITY as QTY

Resident MMT

Where mmt.TRANSACTION_TYPE_ID = 74 and mmt.PRIMARY_QUANTITY >0;

11.

GLITEMCOST:

LOAD

AutoNumberHash128(GLIC.INVENTORY_ITEM_ID & '|' & GLIC.ORGANIZATION_ID) as %INVENTORYKEY,

GLIC.ACCTG_COST,

GLIC.END_DATE,

GLIC.START_DATE

FROM

[..\..\3_Data_Repository\Oracle_EBS\apps.GL_ITEM_CST.qvd](qvd)

Where GLIC.COST_TYPE_ID = 1000 and exists(%INVENTORYKEY,AutoNumberHash128(GLIC.INVENTORY_ITEM_ID & '|' & GLIC.ORGANIZATION_ID));

Join(GLITEMCOST)

IntervalMatch(DELIVERY_DATE,%INVENTORYKEY)

LOAD

GLIC.START_DATE,

GLIC.END_DATE ,

%INVENTORYKEY

Resident GLITEMCOST;

STNDMAP:

Mapping LOAD

%INVENTORYKEY,

GLIC.ACCTG_COST

Resident GLITEMCOST;

MMT_TEMP:

LOAD *,

PO_UNIT_PRICE * RATE_TO_USD as PO_UNIT_PRICE_USD,

STND_UNIT_COST * RATE_TO_USD as ITEM_UNIT_COST_USD;

LOAD *,

if(GLLedger.CURRENCY_CODE = 'USD', 1,

ApplyMap('FXMap',GLLedger.CURRENCY_CODE & '_' & 'USD' & '_' & Floor(DELIVERY_DATE),Null())) as RATE_TO_USD,

if(MTL_P.PROCESS_ENABLED_FLAG = 'N', mmt.ACTUAL_COST,ApplyMap('STNDMAP',%INVENTORYKEY,Null())) as STND_UNIT_COST,

ApplyMap('STNDMAP',%INVENTORYKEY,Null()) as DISCRETE_COST

Resident MMT;

Drop table MMT,GLITEMCOST;

Rename table MMT_TEMP to MMT;

12.

left Join(MMT)

LOAD

LINE_ITEM as %ITEM,

DESCRIPTION,

if(Isnull(THIS_LEVEL_RESOURCE),0,THIS_LEVEL_RESOURCE) as THIS_LEVEL_RESOURCE,

if(Isnull(THIS_LEVEL_OVERHEAD),0,THIS_LEVEL_OVERHEAD) as THIS_LEVEL_OVERHEAD,

if(Isnull(THIS_LEVEL_MATERIAL),0,THIS_LEVEL_MATERIAL) as THIS_LEVEL_MATERIAL,

if(Isnull(LOWER_LEVEL_RESOURCE),0,LOWER_LEVEL_RESOURCE) as LOWER_LEVEL_RESOURCE,

if(Isnull(LOWER_LEVEL_OVERHEAD),0,LOWER_LEVEL_OVERHEAD) as LOWER_LEVEL_OVERHEAD,

if(Isnull(LOWER_LEVEL_MATERIAL),0,LOWER_LEVEL_MATERIAL) as LOWER_LEVEL_MATERIAL,

UNIT_COST_LESS_TLOH,

EXT_VALUE

FROM

[..\..\3_Data_Repository\Oracle_EBS\OPM_WIP_PRODUCTS.qvd](qvd);

13.

[Items and Classification]:

LOAD [Item Number] as %ITEM,

[Category Level 0],

[Category Level 1],

[Category Level 2],

[Category Level 3],

[Category Level 4]

FROM

[..\Supply Chain\VIP Savings\Items and Classifications.xlsx]

(ooxml, embedded labels, table is [Create New Report]);

14.

Left Keep(MMT)

Calendar:

LOAD Date,

FiscalYear,

FiscalQuarterId,

CalendarPeriodId,

FiscalPeriodId,

FiscalWeekId,

FiscalYearPeriodNameLong,

FiscalQuarterNameYearShort,

FiscalPeriodNumberYearShort,

FiscalPeriodNameYearShort,

FiscalPeriodSystemYearShort,

FiscalQuarterNumberYearShort,

FiscalYearQuarterNameLong,

FiscalQuarterName,

FiscalQuarterNumber,

FiscalYearShort,

FiscalPeriodNumber,

FiscalPeriodStartDate,

FiscalPeriodEndDate,

FiscalQuarterStartDate,

FiscalYearStartDate,

FiscalPeriodName

FROM

[..\Master Data\StandardCalendar.qvd](qvd);

Final Data Model:

My Practices with BI(View & Sense)

Thursday, February 7, 2019

How to Design a Data model based on a SQL query(Oracle) in QlikView

1 comment: