Pardavimo dokumentų rodymo praplėtimas MGAMA dokumentų sąraše
MGAMA dokumentų sąraše galima parodyti Rivile GAMA pardavimo dokumentus MGAMA - Rivile GAMA dokumentai.
Standartinis Rivile GAMA pardavimo dokumentų informacijos parodymas papildytas:
1. Skolos informacija
2. Internetine nuoroda į failą (iš aprašymo 1 lauko).
- Sukuriama DVS rūšis, analogiška kaip sisteminė. Pvz., MGAMA_PP_F09 - " (F09) Pardavimų operacijos "
Servisas -> DVS -> DVS rūšys
Paspaudus mygtuką "Vartotojų leidimai" suadministruojama kas galės matyti pardavimo dokumentų informaciją (pažymimi vartotojai arba vartotojų grupės):
- Sukuriama formulė naujam pardavimų informacijos rodymui: MGAMA_PP_F09.
Servisas -> Kortelės -> Formulės
Jei dirbama su MSSQL duomenų baze:
select @statusasPavadinimas='Skola'
select @statusasSpalva='RGB(255,128,128)'
select @add_data=left(convert(char(19),getdate(),120),16)
select @result=''
-- Operacijos turinys
declare @pav varchar(250)
, @apr1 varchar(150)
, @apr2 varchar(150)
, @apr3 varchar(150)
, @aprasymas varchar(8000)
, @pastabos varchar(500)
, @dokSuma numeric(12,2)
, @dokSkola numeric(16,2)
, @link varchar(250)
select @apr1=rtrim(I06_PAV),
@apr2 = rtrim(I06_DOK_NR),
@apr3 = left(convert(varchar,I06_DOK_DATA,120),10),
@pastabos = I06_PASTABOS,
@dokSuma = I06_SUMA+I06_SUMA_PVM,
@dokSkola = (case when i06_op_tip=51 and i06_perkelta=2 then isnull((select SUM(T03_SUMA_DB-T03_SUMA_CR) as skola from T03_SDOK where T03_KODAS_KS=i06_kodas_ks and T03_DOK_NR=i06_dok_nr),0) else null end),
@link = isnull(i06_aprasymas1,'')
from dbo.I06_PARH
where I06_KODAS_PO=@kodas_op
-- Detalios eilutės
declare @det_eil xml
select @det_eil=cast((
SELECT dbo.RGI_F_XML_ENCODE(rtrim(I07_PAV)) As [key],
Ltrim(str(Sum(I07_ALT_KIEKIS/I07_ALT_FRAK), 16, Cast(Ceiling(Log10(I07_ALT_FRAK)) as Int)))
+ ' (' + Rtrim(I07_KODAS_US_A) + '), '
+ Ltrim(str(Sum(I07_SUMA + I07_PVM), 12,2))+'€ '
+ '(' + case when I07_NUOLAIDA=0 then '' else '-'+Ltrim(Str(I07_NUOLAIDA, 12, 2))+'% ' end
+ case when right(Str(I07_KAINA_BE, 12, 4),2)='00'
then Ltrim(Str(I07_KAINA_BE, 12, 2))
else Ltrim(Str(I07_KAINA_BE, 12, 4))
end
+ '€/' + RTRIM(I07_KODAS_US_A) + ')' AS [value]
, Sum(I07_SUMA + I07_PVM) AS numValue
, 'inline' AS [type]
FROM I07_PARD as [schema]
WHERE I07_KODAS_PO = @kodas_op AND I07_ALT_FRAK<>0 and I07_TIPAS in (1,2,3,6)
group by I07_PAV, I07_KODAS_US_A, I07_ALT_FRAK, I07_NUOLAIDA, I07_KAINA_BE
FOR XML auto, elements) as XML)
-- Formuojamas turinys
declare @xml_str varchar(max)
select @xml_str=cast((
select * from (
select 0 as eil, 'Klientas' as [key], dbo.RGI_F_XML_ENCODE(rtrim(@apr1)) as [value], 'inline' as [type], null as , null as [data]
UNION ALL
select 1 as eil, 'Dokumento Nr.' as [key], dbo.RGI_F_XML_ENCODE(rtrim(@apr2)) as [value], 'inline' as [type], null as , null as [data]
UNION ALL
select 2 as eil, 'Dok. data' as [key], dbo.RGI_F_XML_ENCODE(rtrim(@apr3)) as [value], 'inline' as [type], null as , null as [data]
UNION ALL
select 3 as eil, 'Dok. suma' as [key], ltrim(str(@dokSuma, 12, 2))+'€' as [value], 'inline' as [type], null as , null as [data]
UNION ALL
select 4 as eil, 'Skola' as [key], ltrim(str(@dokSkola, 16, 2))+'€' as [value], 'inline' as [type], 'red' as , null as [data] where isnull(@dokSkola,0)>0
UNION ALL
select 5 as eil, 'Nuoroda į failą' as [key], @link as [value], 'link' as [type], null as , null as [data] where @link<>''
UNION ALL
select 24 as eil, 'Pastabos' as [key], dbo.RGI_F_XML_ENCODE(rtrim(@pastabos)) as [value], 'block' as [type], null as , null as [data]
where isnull(@pastabos, '') <> ''
UNION ALL
select 25 as eil, 'Detali informacija' as [key], null as [value], 'list' as [type], null as , @det_eil as [data]
) as [schema] order by eil asc FOR XML auto, elements
) as varchar(max)
)
if (isnull(@dokSkola,0)<=0)
begin
select @statusasPavadinimas='Apmokėta'
select @statusasSpalva='RGB(204,235,205)'
end
if (@dokSkola is null)
begin
select @statusasPavadinimas='Nesuformuota'
select @statusasSpalva='RGB(247,226,142)'
end
-- Formuojamas bendras rezultatas
select @result =
+'<config><key>SHOW_HISTORY</key><value>false</value></config>'
+ @xml_strJei dirbama su Sybase duomenų baze:
select @statusasPavadinimas='Skola'
select @statusasSpalva='RGB(255,128,128)'
select @add_data=left(convert(char(19),getdate(),120),16)
select @result=''
-- Operacijos turinys
declare @pav varchar(250)
, @apr1 varchar(150)
, @apr2 varchar(150)
, @apr3 varchar(150)
, @aprasymas varchar(8000)
, @pastabos varchar(500)
, @dokSuma numeric(12,2)
, @dokSkola numeric(16,2)
, @link varchar(250)
select @apr1=rtrim(I06_PAV),
@apr2 = rtrim(I06_DOK_NR),
@apr3 = left(convert(varchar,I06_DOK_DATA,120),10),
@pastabos = I06_PASTABOS,
@dokSuma = I06_SUMA+I06_SUMA_PVM,
@dokSkola = (case when i06_op_tip=51 and i06_perkelta=2 then isnull((select SUM(T03_SUMA_DB-T03_SUMA_CR) as skola from T03_SDOK where T03_KODAS_KS=i06_kodas_ks and T03_DOK_NR=i06_dok_nr),0) else null end),
@link = isnull(i06_aprasymas1,'')
from dbo.I06_PARH
where I06_KODAS_PO=@kodas_op
-- Detalios eilutės
declare @det_eil xml
select @det_eil=cast((
SELECT dbo.RGI_F_SPEC_SYMB(rtrim(I07_PAV)) As [key],
Ltrim(str(Sum(I07_ALT_KIEKIS/I07_ALT_FRAK), 16, Cast(Ceiling(Log10(I07_ALT_FRAK)) as Int)))
+ ' (' + Rtrim(I07_KODAS_US_A) + '), '
+ Ltrim(str(Sum(I07_SUMA + I07_PVM), 12,2))+'€ '
+ '(' + case when I07_NUOLAIDA=0 then '' else '-'+Ltrim(Str(I07_NUOLAIDA, 12, 2))+'% ' end
+ case when right(Str(I07_KAINA_BE, 12, 4),2)='00'
then Ltrim(Str(I07_KAINA_BE, 12, 2))
else Ltrim(Str(I07_KAINA_BE, 12, 4))
end
+ '€/' + RTRIM(I07_KODAS_US_A) + ')' AS [value]
, Sum(I07_SUMA + I07_PVM) AS numValue
, 'inline' AS [type]
FROM I07_PARD as [schema]
WHERE I07_KODAS_PO = @kodas_op AND I07_ALT_FRAK<>0 and I07_TIPAS in (1,2,3,6)
group by I07_PAV, I07_KODAS_US_A, I07_ALT_FRAK, I07_NUOLAIDA, I07_KAINA_BE
FOR XML auto, elements) as XML)
-- Formuojamas turinys
declare @xml_str long varchar
select @xml_str=cast((
select * from (
select 0 as eil, 'Klientas' as [key], dbo.RGI_F_SPEC_SYMB(rtrim(@apr1)) as [value], 'inline' as [type], null as , null as [data]
UNION ALL
select 1 as eil, 'Dokumento Nr.' as [key], dbo.RGI_F_SPEC_SYMB(rtrim(@apr2)) as [value], 'inline' as [type], null as , null as [data]
UNION ALL
select 2 as eil, 'Dok. data' as [key], dbo.RGI_F_SPEC_SYMB(rtrim(@apr3)) as [value], 'inline' as [type], null as , null as [data]
UNION ALL
select 3 as eil, 'Dok. suma' as [key], ltrim(str(@dokSuma, 12, 2))+'€' as [value], 'inline' as [type], null as , null as [data]
UNION ALL
select 4 as eil, 'Skola' as [key], ltrim(str(@dokSkola, 16, 2))+'€' as [value], 'inline' as [type], 'red' as , null as [data] where isnull(@dokSkola,0)>0
UNION ALL
select 5 as eil, 'Nuoroda į failą' as [key], @link as [value], 'link' as [type], null as , null as [data] where @link<>''
UNION ALL
select 24 as eil, 'Pastabos' as [key], dbo.RGI_F_SPEC_SYMB(rtrim(@pastabos)) as [value], 'block' as [type], null as , null as [data]
where isnull(@pastabos, '') <> ''
UNION ALL
select 25 as eil, 'Detali informacija' as [key], null as [value], 'list' as [type], null as , @det_eil as [data]
) as [schema] order by eil asc FOR XML auto, elements
) as long varchar
)
if (isnull(@dokSkola,0)<=0)
begin
select @statusasPavadinimas='Apmokėta'
select @statusasSpalva='RGB(204,235,205)'
end
if (@dokSkola is null)
begin
select @statusasPavadinimas='Nesuformuota'
select @statusasSpalva='RGB(247,226,142)'
end
-- Formuojamas bendras rezultatas
select @result =
'<config><key>SHOW_HISTORY</key><value>false</value></config>'
+ @xml_str
- Pakoreguojama esama dokumentų sąrašo nuskaitymo formulė MG_DV_NUSK.
Pakoreguojama pardavimo operacijų sąrašo nuskaitymo dalis.
Servisas -> Kortelės -> Formulės
-- Pardavimų operacijų sąrašas
select @kodas_rd='MGAMA_PP_F09'
if exists(select * from #RGI_MGAMA_DVS_OP_LIST_leidimai where kodas_rd=@kodas_rd and galioja=1) -- Patikrinamos teisės ir DVS rūšies galiojimas
insert into #RGI_MGAMA_DVS_OP_LIST (kodas_rd,modul,kodas_op,op_tipas,busena,apr1,apr2,apr3,op_data,r_date,search_expr,statusasPavadinimas,statusasSpalva)
select kodas_rd,modul,kodas_op,op_tipas,busena,apr1,apr2,apr3,op_data,r_date,search_expr,
(case when skola is null then 'Nesuformuota' when skola>0 then 'Skola' else 'Apmokėta' end) as statusasPavadinimas,
(case when skola is null then 'RGB(247,226,142)' when skola>0 then 'RGB(255,128,128)' else 'RGB(204,235,205)' end) as statusasSpalva
from (
select @kodas_rd as kodas_rd,
'RO' as modul,
I06_KODAS_PO as kodas_op,
I06_OP_TIP as op_tipas,
0 as busena,
(case when i06_op_tip=51 and i06_perkelta=2 then isnull((select SUM(T03_SUMA_DB-T03_SUMA_CR) as skolos_suma from T03_SDOK where T03_KODAS_KS=i06_kodas_ks and T03_DOK_NR=i06_dok_nr),0) else null end) as skola,
rtrim(rtrim(I06_PAV)) as apr1,
'Pardavimai: '+
(case when I06_OP_TIP in (51) then 'Sąskaita '
when I06_OP_TIP in (52) then 'Grąžinimas '
when I06_OP_TIP in (53) then 'Užsakymas '
when I06_OP_TIP in (55) then 'Pasiūlymas '
when I06_OP_TIP in (54) then 'Rezervavimas '
else 'Dokumentas ' end) as apr2,
rtrim(I06_DOK_NR)+' '+left(convert(varchar(23),I06_OP_DATA,102),10)+' '+rtrim(I06_PASTABOS) as apr3,
I06_DOK_DATA as op_data,
I06_R_DATE as r_date,
'' as search_expr
from dbo.I06_PARH
where I06_OP_TIP in (51,52) -- Tik važtaraštis ir grąžinimas
and I06_DOK_DATA>=convert(datetime,left(convert(varchar(23),dateadd(year,-2,getdate()),102),4)+'.01.01',102) -- Paskutiniai 2 metai
) as A- Sukuriama nauja MGAMA forma
MGAMA - >Nustatymai -> Dokumentų formos
Formos INIT - nurodoma pardavimo informacijos rodymo formulė MGAMA_PP_F09.
DVS rūšis - nurodomas anksčiau sukurtos DVS rūšies kodas.
Forma skirta tik dokumentų peržiūrai, todėl pažymima kaip "Neaktyvi".