SELECT 'A' "RECORD_TYPE"
,dfn_source_system_id
,transaction_ref
,dfn_account_id
,dfn_product_id
,dfn_vessel_id
,dfn_location_id "DFN_LOC_ID"
,dfn_stock_point_id
,delivery_date
,invoice_date
,period_num_acctg "PERIOD_NUM"
,purchase_order_number
,manta_number
,ord_num
,manta_order_date
,contra_ind
,inv_status
,payment_due_date
,ar_due_date
,payment_date
,business_type
,data_quality_ind
,contract_assignment
,dlvy_note_num
,dw.distr_chan
,dw.division
,salesorg
,MAX(investment_id) investment_id
,MAX(rebate_id) rebate_id
,SUM(decode(dfn_waterfall_element_id,98,amount,0)) std_vol,
SUM(decode(dfn_waterfall_element_id,0,amount*exchange_rate_inv_to_local*exchange_rate_lcl_to_usd*ct.factor,0)) product_list_price,
SUM(decode(dfn_waterfall_element_id,1,amount*exchange_rate_inv_to_local*exchange_rate_lcl_to_usd*ct.factor,0)) price_adjustment_surcharge,
SUM(decode(dfn_waterfall_element_id,2,amount*exchange_rate_inv_to_local*exchange_rate_lcl_to_usd*ct.factor,0)) port_differential,
SUM(decode(dfn_waterfall_element_id,3,amount*exchange_rate_inv_to_local*exchange_rate_lcl_to_usd*ct.factor,0)) pack_differential,
SUM(decode(dfn_waterfall_element_id,4,amount*exchange_rate_inv_to_local*exchange_rate_lcl_to_usd*ct.factor,0)) other_surcharges,
0 product_port_list_price,
SUM(decode(dfn_waterfall_element_id,6,amount*exchange_rate_inv_to_local*exchange_rate_lcl_to_usd*ct.factor,0)) technical_offer_charges,
0 total_offer_price,
SUM(decode(dfn_waterfall_element_id,14,amount*exchange_rate_inv_to_local*exchange_rate_lcl_to_usd*ct.factor,0)) product_discount,
SUM(decode(dfn_waterfall_element_id,11,amount*exchange_rate_inv_to_local*exchange_rate_lcl_to_usd*ct.factor,0)) price_adjustment_discount,
SUM(decode(dfn_waterfall_element_id,5,amount*exchange_rate_inv_to_local*exchange_rate_lcl_to_usd*ct.factor*-1,0)) other_delivery_discount,
SUM(decode(dfn_waterfall_element_id,9999,amount*exchange_rate_inv_to_local*exchange_rate_lcl_to_usd*ct.factor,9,amount*exchange_rate_ inv_to_local*exchange_rate_lcl_to_usd*ct.factor,0)) invoice_price,
SUM(decode(dfn_waterfall_element_id,17,amount*exchange_rate_inv_to_local*exchange_rate_lcl_to_usd*ct.factor,0)) capitalisation_benefit,
0 adjusted_invoice_price,
SUM(decode(dfn_waterfall_element_id,15,amount*exchange_rate_inv_to_local*exchange_rate_lcl_to_usd*ct.factor,0)) rebate,
SUM(decode(dfn_waterfall_element_id,12,amount*exchange_rate_inv_to_local*exchange_rate_lcl_to_usd*ct.factor,0)) investment_amortisation,
SUM(decode(dfn_waterfall_element_id,7,amount*exchange_rate_inv_to_local*exchange_rate_lcl_to_usd*ct.factor*-1,0)) non_compliance_credits,
SUM(decode(dfn_waterfall_element_id,10,amount*exchange_rate_inv_to_local*exchange_rate_lcl_to_usd*ct.factor*-1,0)) other_credit_notes,
0 pocket_price,
SUM(decode(dfn_waterfall_element_id,13,amount*exchange_rate_inv_to_local*exchange_rate_lcl_to_usd*ct.factor,0)) cogs,
SUM(decode(dfn_waterfall_element_id,19,amount*exchange_rate_inv_to_local*exchange_rate_lcl_to_usd*ct.factor,0)) cosa,
0 gross_margin,
SUM(decode(dfn_waterfall_element_id,16,amount*exchange_rate_inv_to_local*exchange_rate_lcl_to_usd*ct.factor,0)) logistics_cost,
0 gross_profit
FROM dfn_dw_sales_fact dw,
dfn_account a,
dfn_condition_type ct
WHERE dw.dfn_account_id = a.id
AND dw.dfn_condition_type_id = ct.id
AND period_num_acctg BETWEEN TO_CHAR(sysdate,'YYYY')- 3 ||'01' AND TO_CHAR(sysdate,'YYYYMM')
AND NVL(a.intra_company,'N') <> 'Y'
AND dw.trade = 'L'
AND dw.transaction_ref NOT LIKE 'USBO%'
AND nvl(a.brand_offer,'XXX') <> 'BP Fuels'
GROUP BY 'A', dfn_source_system_id, transaction_ref, dfn_account_id, dfn_product_id,
dfn_vessel_id, dfn_location_id,dfn_stock_point_id,delivery_date, invoice_date, period_num_acctg,
purchase_order_number, manta_number, ord_num,
manta_order_date, contra_ind, inv_status,
payment_due_date, ar_due_date, payment_date,
business_type,data_quality_ind,contract_assignment,
dlvy_note_num,dw.distr_chan,dw.division,salesorg
UNION
SELECT
''N'' "RECORD_TYPE"
,3 dfn_source_system_id
,'' '' transaction_ref
,dfn_account_id
,0 dfn_product_id
,0 dfn_vessel_id
,0 dfn_loc_id
,0 dfn_stock_point_id
,TO_DATE(NVL(accounting_period,''190001'')||''01'',''YYYYMMDD'') delivery_date
,TO_DATE(NVL(accounting_period,''190001'')||''01'',''YYYYMMDD'')invoice_date
,TO_NUMBER(accounting_period) "PERIOD_NUM"
,NULL purchase_order_number
,0 manta_number
,0 ord_num
,to_date(''01011900'', ''DDMMYYYY'') manta_order_date
,NULL contra_ind
,NULL inv_status
,TO_DATE(''01011900'', ''DDMMYYYY'') payment_due_date
,TO_DATE(''01011900'', ''DDMMYYYY'') ar_due_date
,TO_DATE(''01011900'', ''DDMMYYYY'') payment_date
,NULL business_type
,NULL data_quality_ind
,NULL contract_assignment
,NULL dlvy_note_num
,''07'' distr_chan
,''02'' division
,NULL salesorg
,NULL investment_id
,NULL rebate_id
,SUM(volume) std_vol,
SUM(proceeds*exchange_rate_to_rcu) product_list_price,
0 price_adjustment_surcharge,
0 port_differential,
0 pack_differential,
0 other_surcharges,
SUM(NVL(proceeds,0)*exchange_rate_to_rcu) product_port_list_price,
0 technical_offer_charges,
SUM(NVL(proceeds,0)*exchange_rate_to_rcu)total_offer_price,
0 product_discount,
0 price_adjustment_discount,
0 other_delivery_discount,
SUM(proceeds*exchange_rate_to_rcu) invoice_price,
0 capitalisation_benefit,
SUM(proceeds*exchange_rate_to_rcu) adjusted_invoice_price,
0 rebate,
0 investment_amortisation,
0 non_compliance_credits,
0 other_credit_notes,
SUM(NVL(proceeds,0)*exchange_rate_to_rcu) pocket_price,
SUM(NVL(cogs,0)*exchange_rate_to_rcu) cogs,
0 cosa,
SUM(NVL(proceeds,0)*exchange_rate_to_rcu) - SUM(NVL(cogs,0)*exchange_rate_to_rcu) gross_margin,
SUM(NVL(logistics,0)*NVL(logistics_exch_rate,exchange_rate_to_rcu)) logistics_cost,
SUM(NVL(proceeds,0)*exchange_rate_to_rcu) - SUM(NVL(cogs,0)*exchange_rate_to_rcu)
- SUM(NVL(logistics,0)*NVL(logistics_exch_rate,exchange_rate_to_rcu)) gross_profit
FROM dfn_non_isp_fact
GROUP by dfn_account_id,
TO_DATE(NVL(accounting_period,''190001'')||''01'',''YYYYMMDD'')
,TO_NUMBER(accounting_period)';
|
] 
Blog
Search
Help
Members
Register
Login
Home
