Dinamik Liste
Tags: uyumsoft
Published: 2022-07-28 10:23:00



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

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/2cqg26j3igfk6g4jw7u8.png)


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/dsdq5tlcrppvj5esd8r6.png)

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