1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51
| CREATE OR REPLACE FORCE VIEW ALM.VW_WDO_VTE
(ID_TPS_JOU, ID_WDO_VTE_REF_CLA, ID_WDO_VTE_PVT, ID_WDO_VTE_EMP, ID_WDO_VTE_MCI_JOU_HEU,
NB_WDO_VTE_PRP, NB_WDO_VTE_TKV, DC_WDO_VTE_MNT_CA_NET_HT, DC_WDO_VTE_MNT_CA_BRT_INI, DC_WDO_VTE_MNT_CA_BRT_BAS,
DC_WDO_VTE_MNT_CA_NET_TTC, DC_WDO_VTE_MNT_MRG, DC_WDO_VTE_MNT_RAB_REM, DC_WDO_VTE_MNT_REM, DC_WDO_VTE_VND_HEU,
NB_WDO_VTE_RMB, NB_WDO_VTE_ECH, NB_WDO_VTE_RET_CLI, NB_WDO_VTE_QTE, DC_WDO_VTE_MNT_RET_CLI,
DC_WDO_VTE_MNT_ECH, DC_WDO_VTE_MNT_RMB, DC_WDO_VTE_AMP_HOR, LB_WDO_VTE_STA, NB_WDO_VTE_CLI)
AS
SELECT /*+ leading (g_t3 IX_FK_ENT_TKV_TPS_JOU) */ g_t3.id_tps_jou, g_t4.id_ref, g_t3.id_pvt, g_t3.id_emp,
TO_NUMBER (TO_CHAR (g_t3.dt_ent_tkv, 'yyyymmddHH24')), 0, 0,
SUM (( ( (g_t1.dc_lig_tkv_prx_ven_brt * g_t1.nb_lig_tkv_qt)
- g_t1.dc_lig_tkv_mnt_rem
)
- g_t1.dc_lig_tkv_mnt_tva
)
),
SUM ((g_t2.dc_prx_brt_ini * g_t1.nb_lig_tkv_qt)),
SUM ((g_t1.nb_lig_tkv_qt * g_t2.dc_prx_brt_bas)),
SUM (( (g_t1.dc_lig_tkv_prx_ven_brt * g_t1.nb_lig_tkv_qt)
- g_t1.dc_lig_tkv_mnt_rem
)
),
( SUM (( ( (g_t1.dc_lig_tkv_prx_ven_brt * g_t1.nb_lig_tkv_qt)
- g_t1.dc_lig_tkv_mnt_rem
)
- g_t1.dc_lig_tkv_mnt_tva
)
)
- SUM ((g_t1.nb_lig_tkv_qt * g_t2.dc_prmp))
),
( SUM (( (g_t2.dc_prx_brt_bas - g_t1.dc_lig_tkv_prx_ven_brt)
* g_t1.nb_lig_tkv_qt
)
)
+ SUM (g_t1.dc_lig_tkv_mnt_rem)
),
SUM (g_t1.dc_lig_tkv_mnt_rem), 0, 0, 0, 0,
SUM (g_t1.nb_lig_tkv_qt), 0, 0, 0, 0, 'AA', 0
FROM ods.tb_o_lig_tkv@xdwa g_t1,
alm.tb_wdo_prx_ref_jou@xdwa g_t2,
ods.tb_o_ent_tkv@xdwa g_t3,
ods.tb_o_uvc@xdwa g_t4
WHERE g_t3.id_tps_jou = g_t2.id_jou
AND g_t1.id_uvc = g_t4.id_uvc
AND g_t3.id_ent_tkv = g_t1.id_ent_tkv
AND g_t1.cd_lig_tkv_typ <> 'MVTFI'
AND g_t4.id_ref = g_t2.id_uvc
GROUP BY TO_NUMBER (TO_CHAR (g_t3.dt_ent_tkv, 'yyyymmddHH24')),
g_t3.id_tps_jou,
g_t4.id_ref,
g_t3.id_pvt,
g_t3.id_emp; |
Partager