Dinamik Liste, @CultureId@
Dinamik liste de, seçilen İşyerinin varsayılan Dil seçimine göre Dil tablosundan kart adlarının gelmesi için parametre; @CultureId@
IF, FOR (Oracle, PostgreSQL)
DECLARE
BEGIN
IF '@Yerlesim@'='Detay' THEN
OPEN :result FOR
SELECT
CO.CO_CODE AS "Firma Kod",
BR.BRANCH_CODE AS "İşyeri Kod",
MM.DOC_DATE AS "Belge Tarih",
MM.NOTE1 AS "Açıklama",
IG.CARD_INTG_CODE AS "Entegrasyon Kod",
IG.DESCRIPTION AS "Entegrasyon Ad",
CU.CUR_CODE AS "Para Birim",
CM.code AS "Hesap Kod",
CM.codename AS "Hesap Ad",
DD.AMT_TRA_DEBIT AS "Döviz Borç",
DD.AMT_TRA_CREDIT AS "Döviz Alacak",
DD.AMT_TRA_BALANCE AS "Döviz Bakiye",
DD.AMT_DEBIT AS "YPB Borç Tutar",
DD.AMT_CREDIT AS "YPB Alacak Tutar",
DD.AMT_BALANCE AS "YPB Bakiye Tutar",
DD.GL_CUR_EVAL AS "Değerleme Kuru",
DD.AMT_BALANCE_EVAL AS "Değerleme Sonrası Bakiye",
DECODE(DD.PLUS_MINUS,1,'Borç',-1,'Alacak') AS "İlgili Hesap",
DD.AMT_LOSS AS "Kur Farkı Gideri (Borç)",
DD.AMT_GAIN AS "Kur Farkı Geliri (Alacak)"
FROM FINT_FIN_EVAL_D DD
INNER JOIN FINT_FIN_EVAL_M MM ON MM.FIN_EVAL_M_ID = DD.FIN_EVAL_M_ID
INNER JOIN GNLD_COMPANY CO ON MM.CO_ID = CO.CO_ID
INNER JOIN GNLD_BRANCH BR ON MM.BRANCH_ID = BR.BRANCH_ID
LEFT JOIN FIND_CARD_INTG IG ON MM.CARD_INTG_ID = IG.CARD_INTG_ID
LEFT JOIN GNLD_CURRENCY CU ON MM.CUR_TRA_ID = CU.CUR_ID
LEFT JOIN FINW_FIN_TABLES CM ON DD.ACC_ID = CM.fin_acc_id AND DD.CARD_TYPE = CM.cardtype
WHERE CO.CO_CODE IN (SELECT CODE FROM TABLE(RP_SPLIT('@CoCode@')))
AND BR.BRANCH_CODE IN (SELECT CODE FROM TABLE(RP_SPLIT('@BranchCodes@')))
AND MM.CARD_TYPE = '@CardType@'
AND (IG.CARD_INTG_CODE IN (SELECT CODE FROM TABLE(RP_SPLIT('@CardIntgCodes@'))) OR '@CardIntgCodes@' = 'null')
AND
(MM.DOC_DATE>= (CASE '@DocDateF@'
WHEN CHR(64) || 'DocDateF' || CHR(64)
THEN TO_DATE('01.01.0001', 'DD.MM.YYYY')
WHEN 'null'
THEN TO_DATE(MM.DOC_DATE, 'DD.MM.YYYY')
ELSE TO_DATE('@DocDateF@', 'DD.MM.YYYY')
END)) AND
(MM.DOC_DATE<= (CASE '@DocDateL@'
WHEN CHR(64) || 'DocDateL' || CHR(64)
THEN TO_DATE('01.01.0001', 'DD.MM.YYYY')
WHEN 'null'
THEN TO_DATE('01.01.0001', 'DD.MM.YYYY')
ELSE TO_DATE('@DocDateL@', 'DD.MM.YYYY')
END))
;
ELSE
OPEN :result FOR
SELECT
'@DocDateF@'|| '-'||'@DocDateL@' AS "Tarih",
'Kur Değerleme' AS "Kaynak",
CO.CO_CODE AS "Firma Kod",
BR.BRANCH_CODE AS "İşyeri Kod",
TO_CHAR(IG.CARD_INTG_CODE) AS "Entegrasyon Kod",
TO_CHAR(IG.DESCRIPTION) AS "Entegrasyon Ad",
CU.CUR_CODE AS "Para Birim",
CM.code AS "Hesap Kod",
CM.codename AS "Hesap Ad",
SUM(DD.AMT_LOSS) AS "Kur Farkı Gideri (Borç)",
SUM(DD.AMT_GAIN) AS "Kur Farkı Geliri (Alacak)",
SUM(DD.AMT_LOSS) - SUM(DD.AMT_GAIN) AS "Kur Farkı Bakiye"
FROM FINT_FIN_EVAL_D DD
INNER JOIN FINT_FIN_EVAL_M MM ON MM.FIN_EVAL_M_ID = DD.FIN_EVAL_M_ID
INNER JOIN GNLD_COMPANY CO ON MM.CO_ID = CO.CO_ID
INNER JOIN GNLD_BRANCH BR ON MM.BRANCH_ID = BR.BRANCH_ID
LEFT JOIN FIND_CARD_INTG IG ON MM.CARD_INTG_ID = IG.CARD_INTG_ID
LEFT JOIN GNLD_CURRENCY CU ON MM.CUR_TRA_ID = CU.CUR_ID
LEFT JOIN FINW_FIN_TABLES CM ON DD.ACC_ID = CM.fin_acc_id AND DD.CARD_TYPE = CM.cardtype
WHERE
1=1
AND CO.CO_CODE IN (SELECT CODE FROM TABLE(RP_SPLIT('@CoCode@')))
AND BR.BRANCH_CODE IN (SELECT CODE FROM TABLE(RP_SPLIT('@BranchCodes@')))
AND MM.CARD_TYPE = '@CardType@'
AND (IG.CARD_INTG_CODE IN (SELECT CODE FROM TABLE(RP_SPLIT('@CardIntgCodes@'))) OR '@CardIntgCodes@' = 'null')
AND (MM.DOC_DATE>= (CASE '@DocDateF@'
WHEN CHR(64) || 'DocDateF' || CHR(64)
THEN TO_DATE('01.01.0001', 'DD.MM.YYYY')
WHEN 'null'
THEN TO_DATE(MM.DOC_DATE, 'DD.MM.YYYY')
ELSE TO_DATE('@DocDateF@', 'DD.MM.YYYY')
END)) AND
(MM.DOC_DATE<= (CASE '@DocDateL@'
WHEN CHR(64) || 'DocDateL' || CHR(64)
THEN TO_DATE('01.01.0001', 'DD.MM.YYYY')
WHEN 'null'
THEN TO_DATE('01.01.0001', 'DD.MM.YYYY')
ELSE TO_DATE('@DocDateL@', 'DD.MM.YYYY')
END))
GROUP BY
CO.CO_CODE,
BR.BRANCH_CODE,
IG.CARD_INTG_CODE,
IG.DESCRIPTION,
CU.CUR_CODE,
CM.code,
CM.codename
UNION ALL
SELECT
'@DocDateF@'|| '-'||'@DocDateL@' AS "Tarih",
'Fatura' AS "Kaynak",
CO.CO_CODE AS "Firma Kod",
BR.BRANCH_CODE AS "İşyeri Kod",
TO_CHAR(IG.CARD_INTG_CODE) AS "Entegrasyon Kod",
TO_CHAR(IG.DESCRIPTION) AS "Entegrasyon Ad",
CU.CUR_CODE AS "Para Birim",
CM.code AS "Hesap Kod",
CM.codename AS "Hesap Ad",
SUM(CASE WHEN CD.DCARD_CODE LIKE '656%' THEN DD.AMT ELSE 0 END) AS "Kur Farkı Gideri (Borç)",
SUM(CASE WHEN CD.DCARD_CODE LIKE '646%' THEN DD.AMT ELSE 0 END) AS "Kur Farkı Geliri (Alacak)",
SUM(CASE WHEN CD.DCARD_CODE LIKE '656%' THEN DD.AMT ELSE 0 END) - SUM(CASE WHEN CD.DCARD_CODE LIKE '646%' THEN DD.AMT ELSE 0 END) AS "Kur Farkı Bakiye"
FROM PSMT_INVOICE_D DD
INNER JOIN PSMT_INVOICE_M MM ON MM.INVOICE_M_ID = DD.INVOICE_M_ID
INNER JOIN GNLD_COMPANY CO ON MM.CO_ID = CO.CO_ID
INNER JOIN GNLD_BRANCH BR ON MM.BRANCH_ID = BR.BRANCH_ID
LEFT JOIN FIND_CARD_INTG IG ON MM.CARD_INTG_ID = IG.CARD_INTG_ID
LEFT JOIN GNLD_CURRENCY CU ON MM.CUR_TRA_ID = CU.CUR_ID
LEFT JOIN FINW_FIN_TABLES CM ON MM.MCARD_ID = CM.fin_acc_id AND MM.CARD_TYPE = CM.cardtype
LEFT JOIN INVW_ITEM_TABLES CD ON DD.DCARD_ID = CD.DCARD_ID AND DD.LINE_TYPE = CD.LINETYPE
WHERE
1=1
AND
(
(MM.E_INVOICE_STATUS NOT IN(1,7)) OR
(MM.E_INVOICE_STATUS IS NULL)
)
AND MM.IS_CANCEL_INVOICE IN (0,2)
AND CO.CO_CODE IN (SELECT CODE FROM TABLE(RP_SPLIT('@CoCode@')))
AND BR.BRANCH_CODE IN (SELECT CODE FROM TABLE(RP_SPLIT('@BranchCodes@')))
AND MM.CARD_TYPE = '@CardType@'
AND (IG.CARD_INTG_CODE IN (SELECT CODE FROM TABLE(RP_SPLIT('@CardIntgCodes@'))) OR '@CardIntgCodes@' = 'null')
AND (MM.DOC_DATE>= (CASE '@DocDateF@'
WHEN CHR(64) || 'DocDateF' || CHR(64)
THEN TO_DATE('01.01.0001', 'DD.MM.YYYY')
WHEN 'null'
THEN TO_DATE(MM.DOC_DATE, 'DD.MM.YYYY')
ELSE TO_DATE('@DocDateF@', 'DD.MM.YYYY')
END)) AND
(MM.DOC_DATE<= (CASE '@DocDateL@'
WHEN CHR(64) || 'DocDateL' || CHR(64)
THEN TO_DATE('01.01.0001', 'DD.MM.YYYY')
WHEN 'null'
THEN TO_DATE('01.01.0001', 'DD.MM.YYYY')
ELSE TO_DATE('@DocDateL@', 'DD.MM.YYYY')
END))
AND MM.DOC_TRA_ID IN (SELECT DC.DOC_TRA_ID FROM GNLD_DOC_TRA DC WHERE DC.INVOICE_TYPE = 5)
AND ('@InInvoice@' = 'True')
GROUP BY
CO.CO_CODE,
BR.BRANCH_CODE,
IG.CARD_INTG_CODE,
IG.DESCRIPTION,
CU.CUR_CODE,
CM.code,
CM.codename
;
END IF;
END ;
<root MainCode="" Caption="Finans Kur Değerleme Listesi">
<tabcontrol Visibility="True">
<tabpage Caption="Kriterler">
<section Caption="" CaptionVisibility="False" Visibility="True" ColumnCount="6">
<row>
<cell colspan="2">
<control FieldName="CoCode" ControlType="ButtonEdit" Caption="Firma" DefaultValue="Session(CoCode)" ControlRequired="True">
<DataSource SourceType="Command" Source="CompanyCollection.Show" Filter="" FilterValues="" OrderByProperty="" ReturnProperties="CoCode" ReturnedProperties="CoCode" RelatedProperty="" ProcessTypeMode="1" ListPropertyName="CoCode"></DataSource>
</control>
</cell>
</row>
<row>
<cell colspan="2">
<control FieldName="BranchCodes" ControlType="ButtonEdit" Caption="Seçimli İşyeri" DefaultValue="Session(BranchCode)" ControlRequired="True">
<DataSource SourceType="Command" Source="BranchCollection.Show" Filter="CoCode=@CoCode1" FilterValues="@CoCode1**GetControlValue(CoCode)" OrderByProperty="" ReturnProperties="BranchCode" ReturnedProperties="BranchCodes" RelatedProperty="" ProcessTypeMode="2" ListPropertyName="BranchCode"></DataSource>
</control>
</cell>
</row>
<row>
<cell colspan="1">
<control FieldName = "CardType" ControlType = "ComboEdit" ControlEnabled="True" DefaultValue="2" MaxLength = "50" ControlRequired = "True" Caption = "Hesap Tipi" ControlVisible="True">
<DataSource SourceType="TextAndValue"
Text="Cari;Banka;Kasa"
Value="2;3;4"></DataSource>
</control>
</cell>
</row>
<row>
<cell colspan="1">
<control FieldName="CardIntgCodes" ControlType="ButtonEdit" Caption="Entegrasyon Kodu" ControlRequired="false" ControlEnabled="true" ControlVisible="True" ControlSingleLine="true">
<DataSource SourceType="Command" Source="CardIntgCollection.Show" Filter="CardType=@CardType;CoCode=@CoCode1" FilterValues="@CardType=GetControlValue(CardType);@CoCode1**GetControlValue(CoCode)" OrderByProperty="" ReturnProperties="CardIntgCode" ReturnedProperties="CardIntgCodes" RelatedProperty="" ProcessTypeMode="2" ListPropertyName="CardIntgCode"></DataSource>
</control>
</cell>
</row>
<row>
<cell colspan="2">
<control FieldName="DocDateF" ControlType="DateEdit" Caption="Değerleme Tarih" DefaultValue="Function(GetFirstDayOfYear)"></control>
</cell>
<cell colspan="2">
<control FieldName="DocDateL" ControlType="DateEdit" CaptionVisible="False" DefaultValue="Function(GetDate)"></control>
</cell>
</row>
<row>
<cell colspan="1">
<control FieldName = "Yerlesim"
ControlType = "ComboEdit"
ControlEnabled="True"
DefaultValue="Detay"
MaxLength = "50"
ControlRequired = "True"
Caption = "Liste Tipi"
ControlVisible="True">
<DataSource SourceType="TextAndValue" Text="Detay;Özet" Value="Detay;Özet">
</DataSource>
</control>
</cell>
</row>
<row>
<cell colspan="2">
<control FieldName="InInvoice" ControlType="CheckEdit" Caption="Faturalar Dahil" DefaultValue="True" ControlRequired = "false" ControlEnabled = "true" ControlVisible = "true" ControlSingleLine = "true"></control>
</cell>
<cell colspan="2"></cell>
</row>
<row>
<cell colspan="2">
<control FieldName="InFin" ControlType="CheckEdit" Caption="Finans Fişleri Dahil" DefaultValue="True" ControlRequired = "false" ControlEnabled = "true" ControlVisible = "true" ControlSingleLine = "true"></control>
</cell>
<cell colspan="2"></cell>
</row>
<row>
<cell colspan="2">
<control FieldName="InAcc" ControlType="CheckEdit" Caption="Muhasebe Fişleri Dahil" DefaultValue="True" ControlRequired = "false" ControlEnabled = "true" ControlVisible = "true" ControlSingleLine = "true"></control>
</cell>
<cell colspan="2"></cell>
</row>
</section>
</tabpage>
</tabcontrol>
</root>
DECLARE
a number(3) := 100;
BEGIN
IF ( a = 10 ) THEN
dbms_output.put_line('Value of a is 10' );
ELSIF ( a = 20 ) THEN
dbms_output.put_line('Value of a is 20' );
ELSIF ( a = 30 ) THEN
dbms_output.put_line('Value of a is 30' );
ELSE
dbms_output.put_line('None of the values is matching');
END IF;
dbms_output.put_line('Exact value of a is: '|| a );
END;
PostgreSQL
DO $$
DECLARE
result CONSTANT refcursor := 'result';
BEGIN
IF '@Yerlesim@'='Özet' THEN
OPEN result FOR
SELECT
WHO.WHOUSE_CODE AS "DEPO KOD",
ITE.ITEM_CODE AS "STOK KOD",
ITE.ITEM_NAME AS "STOK AD",
WHL.LOCATION_CODE AS "RAF",
LOT.LOT_CODE AS "PARTI",
A01.ITEM_ATTRIBUTE_CODE AS "ÖZELLIK-1",
CAST(COUNT(IPM.QTY) AS DECIMAL) AS "PALET SAYISI",
IPM.QTY AS "PALET İÇI MİKTAR",
IPM.QTY * COUNT(IPM.QTY) AS "STOK MİKTAR"
FROM INVD_PACKAGE_M IPM
LEFT JOIN INVD_ITEM ITE ON IPM.ITEM_ID = ITE.ITEM_ID
LEFT JOIN INVD_WHOUSE WHO ON IPM.WHOUSE_ID = WHO.WHOUSE_ID
LEFT JOIN INVD_LOT LOT ON IPM.LOT_ID = LOT.LOT_ID
LEFT JOIN INVD_ITEM_ATTRIBUTE A01 ON IPM.ITEM_ATTRIBUTE1_ID = A01.ITEM_ATTRIBUTE_ID
LEFT JOIN INVD_BWH_LOCATION WHL ON IPM.BWH_LOCATION_ID = WHL.BWH_LOCATION_ID
WHERE
1=1
AND IPM.INPUT_OUTPUT = 1
AND (WHO.WHOUSE_CODE IN (SELECT CODE FROM RP_SPLIT('@WhouseCodes@')) OR '@WhouseCodes@' = 'null')
GROUP BY
WHO.WHOUSE_CODE,
ITE.ITEM_CODE,
ITE.ITEM_NAME,
WHL.LOCATION_CODE,
LOT.LOT_CODE,
A01.ITEM_ATTRIBUTE_CODE,
IPM.QTY
;
ELSE
OPEN result FOR
SELECT
IPM.PACKAGE_NO AS "PALET NO",
WHO.WHOUSE_CODE AS "DEPO KOD",
ITE.ITEM_CODE AS "STOK KOD",
ITE.ITEM_NAME AS "STOK AD",
WHL.LOCATION_CODE AS "RAF",
LOT.LOT_CODE AS "PARTI",
A01.ITEM_ATTRIBUTE_CODE AS "ÖZELLIK-1",
IPM.QTY AS "PALET İÇI MİKTAR"
FROM INVD_PACKAGE_M IPM
LEFT JOIN INVD_ITEM ITE ON IPM.ITEM_ID = ITE.ITEM_ID
LEFT JOIN INVD_WHOUSE WHO ON IPM.WHOUSE_ID = WHO.WHOUSE_ID
LEFT JOIN INVD_LOT LOT ON IPM.LOT_ID = LOT.LOT_ID
LEFT JOIN INVD_ITEM_ATTRIBUTE A01 ON IPM.ITEM_ATTRIBUTE1_ID = A01.ITEM_ATTRIBUTE_ID
LEFT JOIN INVD_BWH_LOCATION WHL ON IPM.BWH_LOCATION_ID = WHL.BWH_LOCATION_ID
WHERE
1=1
AND IPM.INPUT_OUTPUT = 1
AND (WHO.WHOUSE_CODE IN (SELECT CODE FROM RP_SPLIT('@WhouseCodes@')) OR '@WhouseCodes@' = 'null')
;
END IF;
END $$;
FETCH ALL FROM result;
Top comments (1)
Liste tipli dinamik liste yapıldığında, sayı format, kolon ayarlama, sıralama kaydetme çalışmaz.