Academic Program by Qlik for Students and professors

Hi Qlikers,
In continuation of my previous post-Qlik announced an academic program for students and professors where they can register the program and learn the technology.

Find the full details in the below link.

https://www.qlik.com/us/company/academic-program






Happy Learning 😊

No more free Qlik Sense Desktop after June 2020

All Qlikers,
Its a sad but true flash news for all of us, to improve the cloud and new business strategies Qlik has decided to cease the free usage of its Qlik Sense Desktop and Qlik Sense Cloud basic Free accounts.

Here is the original link ...
https://community.qlik.com/t5/Qlik-Support-Updates-Blog/Changes-to-Qlik-Sense-Desktop-in-2020/ba-p/1653306

How about QlikView to Qlik Sense migration...

Dear All,

I am back with some good to know information as always 😜.
Now a days its common for a Qlik project to be migrated to Qlik Sense, at least the UI. I have been
involved in such projects but I felt the below challenges...

1. Should the data model also be migrated to Qlik Sense ?
2. What about the features that are there in QlikView and not there in Qlik Sense ?
3. On the whole how about the reload time in Qlik Sense ?
4. How about the reduction of files into multiple files using Qlik QMC balanced using Qlik Sense QMC like Simple reduction or Loop & Reduce ?


My experience is as below
1. Should the data model also be migrated to Qlik Sense ?
Per now Qlik is providing Dual license for both QlikView and Qlik Sense so architects are in no position to disturb their bigger data models to be migrated into Qlik Sense. So what is the process here?

Data model will be still in QlikView but it is binary loaded into Qlik Sense for UI development.

2.What about the features that are there in QlikView and not there in Qlik Sense ?
Thanks to Qlik  for introducing a lot of features in Qlik Sense for the past 1 year or so but earlier it was a bit difficult to mimic the same look and feel in QlikView to Qlik Sense as there were no options or alternatives for getting them, but because of extensions this can be leveraged a bit but be careful to use them as they may not be trusted all the time to be intact with the latest version of Qlik Sense.

On the whole what I can say is we have to find new ways of presenting data to the user in Qlik Sense for scenarios where we don't have substitutes for QlikView like Hide & Show Mechanism.

3.On the whole how about the reload time in Qlik Sense ?
There is a lot of difference in this aspect and at times Qlik Sense may crash if the load time of the app is more.

What I can suggest regarding this is to have a data model in QlikView and do a binary load in Qlik Sense, if there is no Qlik View at all then plan to have multiple Transform apps for the same.

4. How about the reduction of files into multiple files using Qlik QMC balanced using Qlik Sense QMC like Simple reduction or Loop & Reduce ?

There is no way that this can be achieved using Qlik Sense QMC, the only way that can be used is
-> Create multiples apps with same UI and data model but use the expressions accordingly to limit the values
-> Create multiples apps with same UI and expressions but create multiple versions of reduced data models in QlikView and then use them in UI apps of Qlik Sense.This way works when your project is having both QlikView and Qlik Sense but the first option should be used if your data model and UI is also built in Qlik Sense.

Hope this helps for migration of projects....

Section access - Part1

Continuing my post on Section access introduction, I would like to give more insight into the topic.

What is Section access ?

Section access is a command in QlikView/Qlik Sense which is used to restrict data access on a qvw file or qvf file so that the person who is looking into the file would see only the data which is intended for him.

Let me give a simple example,

Business Scenario:
A company has 4 Sales representatives who are selling multiple products for the company, now every sales representative wants to see the information of the products he sold at the end of a quarter or a month so that he wants he know whether he achieved his quota of sales or not for that quarter or month.

Solution:
Lets assume the information regarding the sales is captured in a data base and a BI tool is being used to show the data for the reps, now if a rep logged into the tool he has to see his sales only but not the sales of other rep, in this case section access is used to dynamically show the data of a rep who logged in rather than showing all the data in the qvw file of all reps.

How Section access is implemented ?
Section access is implemented in the script and by loading data containing the access information between two keywords... Section access               ...... Section Application;




Between Section access and Section Application commands one can load data from a qvd, excel, csv or from data base select command etc but the load command should have the following fields
in capital letters.

ACCESS, USERID,PASSWORD etc.

To know the list of fields that can be used you can go into 'Insert Menu->Section Access->Load Inline' and observe the wizard which appears.

Below is the screen which pops up after selection the above....

Note:
Section access feature can be tried in QlikView Desktop but it cannot be tried in Qlik Sense Desktop.
While trying Section access in QlikView Desktop ACCESS,USERID and PASSWORD fields are compulsory where as alternatively we can use OMIT and other 'Data reduction fields with them.

Hope you have understood the beginning of how to implement Section Access in QlikView,
will come up with an example next time for your practice.

Thank You................ and Happy Learning.

Loading a set of Excel files from a folder into QlikView

Good Day,
All of you.

In the process of building a data model in QlikView/Qlik Sense sometimes we may require to load a set of files like .txt,.csv or .xlsx/.xls, but assume a case where we need to load a set of 100 files which are snap shots of data like complete weeks data kept on next Monday with a Date to it, like below.

Now here is the task to load all the files at once with out loading them individually and this has to happen every time when we are loading the qvw file such that all the older files plus the new file should be loaded. This can be achieved with a loop in QlikView/ Qlik Sense, below is one such script which I used in my real time. Hope this helps you.


Note: One thing to be observed here is the name of the files are the same but with a different date at the end.

//Code Used:

let vQlikTech = 'Your Folder Path';

Directory $(vQlikTech);

let i= 1;

For each File in filelist ('Risk Register_*.xlsx')

Risks1:     //to get sequence Snopshot ID
Load
RowNo() as Test
AutoGenerate 1;

i= peek('Test')+i;

drop table Risks1;

Concatenate
Load
*,
Date(Date#(subfield(SubField(FileName(),'_', 2),'.xlsx',1), 'DD.MM.YYYY'))as "Snapshot Date",
'$(i)' as "Snapshot ID"
FROM $(File)
(ooxml, embedded labels, table is owssvr);

Next File;

What is Section Access - For Qlik Beginners

Good Day,
All of you.
To start with a simple quote.....

                                                 ** Repetition is the crux of Education **
Section access is one such topic which can be discussed for hours together as it has a greater usage in QlikView/Qlik Sense.

First of all
What is the use of Section Access ?
Lets take a simple scenario.
A university is maintaining students data in a data base and created some analytics over that and shared that files to all the Department heads using QlikView Access point. Now the rule here is when a Department head opens that file he has to see analytics of his students not the other department students.....

Hope you got the requirement. To make it simple

University contains - 7000 students
--------------------------------------------
IT Dept                   - 1000 students
ECE Dept               - 1500 students
MCA Dept             - 1000 students
MBA Dept             - 2000 students
Civil Dept              - 1500 students

Now if a Department head of IT Dept logs in to the file, how many students analytics he has to see ?
1000 - Correct!

This is section access, it means the original file is loaded with all students data but when a department head open the file he has to see his student data only.

This concept is also known as Data Reduction (or) Row Level Security as the security is on the data or on the rows.

Hope this makes sense on understanding What is Section Access in QlikView/Qlik Sense.


I will come up with some more concepts on Section access with examples to practice in my next posts.

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;

2.

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);

3.

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);

5.

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);

6.

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);

7.

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);

8.

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);

9.

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: