Yevmiye Defteri - Electronic

/* WITH SECURITY_TBL AS
(
SELECT DISTINCT GL.LEDGER_ID
FROM GL_LEDGERS GL
,(
SELECT DISTINCT GL.LEDGER_ID
FROM 
GL_LEDGERS GL,
GL_ACCESS_SETS GAS,
	FUN_USER_ROLE_DATA_ASGNMNTS FURDA,
	PER_ROLES_DN PRD,
	PER_USER_ROLES PUR,
	PER_USERS PU

WHERE 
UPPER(GL.LEDGER_CATEGORY_CODE) <> 'NONE'
AND GAS.ACCESS_SET_ID=GL.IMPLICIT_ACCESS_SET_ID
	AND (GAS.ACCESS_SET_ID =FURDA.ACCESS_SET_ID OR GL.LEDGER_ID=FURDA.LEDGER_ID)
	AND UPPER(FURDA.role_name) in ('XXC_GL_INQUIRY_JOB','XXC_GENERAL_ACCOUNTANT_JOB','XXC_GENERAL_ACCOUNTING_MANAGER_JOB')
	AND UPPER(FURDA.ACTIVE_FLAG) = 'Y'
	AND UPPER(FURDA.ROLE_NAME) = UPPER(PRD.ROLE_COMMON_NAME)
	AND PRD.ROLE_ID = PUR.ROLE_ID
	AND PUR.USER_ID = PU.USER_ID
	AND  UPPER(PU.USERNAME) = UPPER(:xdo_user_name)
	AND PU.USER_GUID = FURDA.USER_GUID

) ST
WHERE 
1=1

AND ST.LEDGER_ID=GL.LEDGER_ID
) */
select
	CREATED_BY,
	POSTED_DATE,
	POSTING_ACCT_SEQ_VALUE,
	CLOSE_ACCT_SEQ_VALUE,
	JE_DESC,
	PARENT_ACCOUNT_CODE,
	PARENT_ACC_NAME,
	CHILD_ACCOUNT_CODE,
	CHILD_ACCOUNT_NAME,
	(CASE WHEN AMOUNT < 1 THEN TO_CHAR(AMOUNT, 'fm0D00')
	ELSE TO_CHAR(AMOUNT, 'fm99999999999D00') END) AMOUNT,
	DEBIT_CREDIT_CODE,
	CASE WHEN (DOC_NUM IS NOT NULL OR DOC_DATE IS NOT NULL) AND CTGY NOT IN ('Satış Faturaları','Alınan Ödemeler','Satın Alma Faturaları','Ödemeler') THEN 'Other' ELSE DOC_TYPE END DOC_TYPE,
	DOC_TYPE_DESC,
	PAYMENT_METHOD,
	DOC_DATE,
	DOC_NUM,
	JE_DESCS
FROM
(
SELECT
NVL((case when XAH.CREATED_BY like '%@%' then 
NVL((select distinct
PPNF.display_name
from
per_users PEA,
per_person_names_f_v PPNF
where
XAH.CREATED_BY = PEA.username
and PEA.person_id = PPNF.person_id
and sysdate between PPNF.effective_start_date and PPNF.effective_end_date),XAH.CREATED_BY)
else 
XAH.CREATED_BY
end 
),(case when GJH.CREATED_BY like '%@%' then 
NVL(
(select distinct
PPNF.display_name
from
per_users PEA,
per_person_names_f_v PPNF
where
GJH.CREATED_BY = PEA.username
and PEA.person_id = PPNF.person_id
and sysdate between PPNF.effective_start_date and PPNF.effective_end_date)
,GJH.CREATED_BY)
else 
GJH.CREATED_BY
end 
)) CREATED_BY,
TO_CHAR(GJH.DEFAULT_EFFECTIVE_DATE,'DD/MM/YY','NLS_DATE_LANGUAGE = turkish') POSTED_DATE,
GJH.POSTING_ACCT_SEQ_VALUE,
GJH.CLOSE_ACCT_SEQ_VALUE,
(
CASE WHEN GJSV.USER_JE_SOURCE_NAME = 'Receivables' THEN 
GJCV.USER_JE_CATEGORY_NAME||'/'||(SELECT distinct
	HP.PARTY_NAME||'/'||HP.PARTY_NUMBER
	FROM 
	hz_cust_accounts HCA,
	hz_parties HP
	WHERE 
	1=1
	AND HCA.party_id = HP.party_id
	AND XAL.party_id = HCA.cust_account_id
	) 
	WHEN GJSV.USER_JE_SOURCE_NAME = 'Payables' AND XTE.SOURCE_APPLICATION_ID = 200 
	AND XTE.ENTITY_CODE = 'AP_INVOICES' THEN
	GJCV.USER_JE_CATEGORY_NAME ||'/'|| (SELECT distinct
	Party.PARTY_NAME AS PARTY_PARTY_NAME 
	FROM 
	AP_INVOICES_ALL InvoiceHeader, 
	HZ_PARTIES Party 
	WHERE 
	1=1
	AND InvoiceHeader.PARTY_ID = Party.PARTY_ID(+)
	and InvoiceHeader.INVOICE_ID = XTE.SOURCE_ID_INT_1
	) ||'/'|| (SELECT distinct
	Party.PARTY_NUMBER
	FROM 
	AP_INVOICES_ALL InvoiceHeader, 
	HZ_PARTIES Party 
	WHERE 
	1=1
	AND InvoiceHeader.PARTY_ID = Party.PARTY_ID(+)
	and InvoiceHeader.INVOICE_ID = XTE.SOURCE_ID_INT_1
	) 
	WHEN GJSV.USER_JE_SOURCE_NAME = 'Payables' AND XTE.SOURCE_APPLICATION_ID = 200 
	AND XTE.ENTITY_CODE = 'AP_PAYMENTS' THEN
	GJCV.USER_JE_CATEGORY_NAME ||'/'|| (
	SELECT DISTINCT
	VENDOR_NAME
	FROM
	AP_CHECKS_ALL 
	WHERE
	1=1
	AND CHECK_ID = XTE.SOURCE_ID_INT_1
	) ||'/'|| (
	SELECT DISTINCT
	POZ.SEGMENT1
	FROM
	AP_CHECKS_ALL ACA,
	POZ_SUPPLIERS POZ
	WHERE
	1=1
	AND ACA.VENDOR_ID = POZ.VENDOR_ID
	AND ACA.CHECK_ID = XTE.SOURCE_ID_INT_1
	)
	WHEN GJSV.USER_JE_SOURCE_NAME = 'Assets' THEN
	GJCV.USER_JE_CATEGORY_NAME
	WHEN GJSV.USER_JE_SOURCE_NAME = 'Cash Management' THEN
	(
	SELECT
	DISTINCT
	CSH.STATEMENT_NUMBER
	FROM
	CE_EXTERNAL_TRANSACTIONS CET,
	CE_STATEMENT_LINES CSL,
	CE_STATEMENT_HEADERS CSH
	WHERE 1=1
	AND NVL(XTE.SOURCE_ID_INT_1,-99) = CET.TRANSACTION_ID
	AND CET.STATEMENT_LINE_ID = CSL.STATEMENT_LINE_ID
	AND CSL.STATEMENT_HEADER_ID = CSH.STATEMENT_HEADER_ID
	)
	ELSE
	GJL.DESCRIPTION
END
) JE_DESC,
(

SELECT  DISTINCT
	SUBSTR(CFT.ANCESTOR_PK1_VALUE,1,3)
	FROM
	GL_SEG_VAL_HIER_RF     CFT,
	fnd_flex_values_vl A4,
	fnd_flex_values_tl T4
	WHERE
	1=1
	AND CFT.DISTANCE=1
	AND NVL(CFT.IS_LEAF, 'Y')<>'N'
	AND NVL(CFT.ANCESTOR_PK1_VALUE, '-1') NOT IN ('-1', 'NI0000')
	AND UPPER(CFT.TREE_CODE)= 'TURKEY ACCOUNT'
	AND CFT.PK1_VALUE = GCC.SEGMENT4
	AND A4.flex_value = CFT.ANCESTOR_PK1_VALUE
	and A4.FLEX_VALUE_ID=T4.FLEX_VALUE_ID
	and A4.VALUE_CATEGORY = 'TURKEY ACCOUNT VALUE SET'
	AND T4.LANGUAGE='TR'
) PARENT_ACCOUNT_CODE,
(
SELECT  DISTINCT
	T4.description
	FROM
	GL_SEG_VAL_HIER_RF     CFT,
	fnd_flex_values_vl A4,
	fnd_flex_values_tl T4
	WHERE
	1=1
	AND CFT.DISTANCE=1
	AND NVL(CFT.IS_LEAF, 'Y')<>'N'
	AND NVL(CFT.ANCESTOR_PK1_VALUE, '-1') NOT IN ('-1', 'NI0000')
	AND UPPER(CFT.TREE_CODE)= 'TURKEY ACCOUNT'
	AND CFT.PK1_VALUE = GCC.SEGMENT4
	AND A4.flex_value = CFT.ANCESTOR_PK1_VALUE
	and A4.FLEX_VALUE_ID=T4.FLEX_VALUE_ID
	and A4.VALUE_CATEGORY = 'TURKEY ACCOUNT VALUE SET'
	AND T4.LANGUAGE='TR'
) PARENT_ACC_NAME,
GCC.SEGMENT4 CHILD_ACCOUNT_CODE,
FFVV.DESCRIPTION CHILD_ACCOUNT_NAME,
NVL(NVL(XAL.ACCOUNTED_DR, XAL.ACCOUNTED_CR),NVL(GJL.ACCOUNTED_DR, GJL.ACCOUNTED_CR)) AMOUNT,

(CASE WHEN XAL.ACCOUNTED_DR IS NULL AND GJL.ACCOUNTED_DR IS NULL THEN 'C' ELSE 'D' END) DEBIT_CREDIT_CODE,
(
CASE WHEN GJSV.USER_JE_SOURCE_NAME IN ('Payables','Receivables') THEN 
GJCV.USER_JE_CATEGORY_NAME  
END
) DOC_TYPE_DESC,
(
CASE WHEN GJSV.USER_JE_SOURCE_NAME = 'Receivables' and GJCV.USER_JE_CATEGORY_NAME = 'Satış Faturaları' THEN 
'Invoice' 
WHEN GJSV.USER_JE_SOURCE_NAME = 'Receivables' and GJCV.USER_JE_CATEGORY_NAME = 'Alınan Ödemeler' THEN 
'Receipt' 
WHEN GJSV.USER_JE_SOURCE_NAME = 'Payables' and GJCV.USER_JE_CATEGORY_NAME = 'Satın Alma Faturaları' THEN 
'Invoice' 
WHEN GJSV.USER_JE_SOURCE_NAME = 'Payables' and GJCV.USER_JE_CATEGORY_NAME = 'Ödemeler' THEN 
'Receipt' 
ELSE
NULL
END
) DOC_TYPE,
( 
CASE WHEN GJSV.USER_JE_SOURCE_NAME = 'Payables' and GJCV.USER_JE_CATEGORY_NAME = 'Ödemeler' 
THEN
(
	SELECT DISTINCT
	ACA.PAYMENT_METHOD_CODE
	FROM
	AP_CHECKS_ALL ACA
	WHERE
	1=1
	AND ACA.CHECK_ID = XTE.SOURCE_ID_INT_1
)
WHEN GJSV.USER_JE_SOURCE_NAME = 'Payables' and GJCV.USER_JE_CATEGORY_NAME = 'Satın Alma Faturaları' 
THEN
(
	SELECT DISTINCT
	AIA.PAYMENT_METHOD_CODE
	FROM
	AP_INVOICES_ALL AIA
	WHERE
	1=1
	AND AIA.INVOICE_ID = XTE.SOURCE_ID_INT_1
)
END
) PAYMENT_METHOD,
(
CASE WHEN GJSV.USER_JE_SOURCE_NAME IN ('Payables','Receivables') THEN 
TO_CHAR(XAL.ACCOUNTING_DATE,'DD/MM/YY','NLS_DATE_LANGUAGE = turkish') ELSE NULL 
END
) DOC_DATE,
(
CASE WHEN GJSV.USER_JE_SOURCE_NAME IN ('Payables','Receivables') THEN XTE.TRANSACTION_NUMBER ELSE NULL 
END
) DOC_NUM,
(
CASE WHEN GJSV.USER_JE_SOURCE_NAME = 'Receivables' THEN 
GJCV.USER_JE_CATEGORY_NAME||'/'||(SELECT distinct
	HP.PARTY_NAME||'/'||HP.PARTY_NUMBER
	FROM 
	hz_cust_accounts HCA,
	hz_parties HP
	WHERE 
	1=1
	AND HCA.party_id = HP.party_id
	AND XAL.party_id = HCA.cust_account_id
	) 
	WHEN GJSV.USER_JE_SOURCE_NAME = 'Payables' AND XTE.SOURCE_APPLICATION_ID = 200 
	AND XTE.ENTITY_CODE = 'AP_INVOICES' THEN
	GJCV.USER_JE_CATEGORY_NAME ||'/'|| (SELECT distinct
	Party.PARTY_NAME AS PARTY_PARTY_NAME 
	FROM 
	AP_INVOICES_ALL InvoiceHeader, 
	HZ_PARTIES Party 
	WHERE 
	1=1
	AND InvoiceHeader.PARTY_ID = Party.PARTY_ID(+)
	and InvoiceHeader.INVOICE_ID = XTE.SOURCE_ID_INT_1
	) ||'/'|| (SELECT distinct
	Party.PARTY_NUMBER
	FROM 
	AP_INVOICES_ALL InvoiceHeader, 
	HZ_PARTIES Party 
	WHERE 
	1=1
	AND InvoiceHeader.PARTY_ID = Party.PARTY_ID(+)
	and InvoiceHeader.INVOICE_ID = XTE.SOURCE_ID_INT_1
	) 
	WHEN GJSV.USER_JE_SOURCE_NAME = 'Payables' AND XTE.SOURCE_APPLICATION_ID = 200 
	AND XTE.ENTITY_CODE = 'AP_PAYMENTS' THEN
	GJCV.USER_JE_CATEGORY_NAME ||'/'|| (
	SELECT DISTINCT
	VENDOR_NAME
	FROM
	AP_CHECKS_ALL 
	WHERE
	1=1
	AND CHECK_ID = XTE.SOURCE_ID_INT_1
	) ||'/'|| (
	SELECT DISTINCT
	POZ.SEGMENT1
	FROM
	AP_CHECKS_ALL ACA,
	POZ_SUPPLIERS POZ
	WHERE
	1=1
	AND ACA.VENDOR_ID = POZ.VENDOR_ID
	AND ACA.CHECK_ID = XTE.SOURCE_ID_INT_1
	)
	WHEN GJSV.USER_JE_SOURCE_NAME = 'Assets' THEN
	GJCV.USER_JE_CATEGORY_NAME
	WHEN GJSV.USER_JE_SOURCE_NAME = 'Cash Management' THEN
	(
	SELECT
	DISTINCT
	CSH.STATEMENT_NUMBER
	FROM
	CE_EXTERNAL_TRANSACTIONS CET,
	CE_STATEMENT_LINES CSL,
	CE_STATEMENT_HEADERS CSH
	WHERE 1=1
	AND NVL(XTE.SOURCE_ID_INT_1,-99) = CET.TRANSACTION_ID
	AND CET.STATEMENT_LINE_ID = CSL.STATEMENT_LINE_ID
	AND CSL.STATEMENT_HEADER_ID = CSH.STATEMENT_HEADER_ID
	)
	ELSE
	GJL.DESCRIPTION
END
) JE_DESCS,
GJCV.USER_JE_CATEGORY_NAME CTGY

FROM
XLA_AE_HEADERS XAH,
XLA_AE_LINES XAL,
XLA_TRANSACTION_ENTITIES XTE,
GL_JE_HEADERS GJH,
GL_JE_LINES GJL,
GL_IMPORT_REFERENCES GIR,
GL_JE_SOURCES_VL GJSV,
GL_PERIODS GP,
GL_LEDGERS GL,
GL_CODE_COMBINATIONS GCC,
FND_FLEX_VALUES FFV,
FND_FLEX_VALUES_TL FFVV,
GL_JE_CATEGORIES_TL GJCV/* ,
SECURITY_TBL ST */
WHERE
1=1
/* AND ST.ledger_id						= GL.LEDGER_ID */
AND GJH.JE_HEADER_ID 				= GJL.JE_HEADER_ID
AND GJH.JE_SOURCE 					= GJSV.JE_SOURCE_NAME
AND GJL.JE_HEADER_ID 				= GIR.JE_HEADER_ID(+)
AND GJL.JE_LINE_NUM 				= GIR.JE_LINE_NUM(+)
AND GIR.GL_SL_LINK_ID 				= XAL.GL_SL_LINK_ID(+)
AND GIR.GL_SL_LINK_TABLE 			= XAL.GL_SL_LINK_TABLE(+)
AND XAL.AE_HEADER_ID				= XAH.AE_HEADER_ID(+)
AND XAH.ENTITY_ID					= XTE.ENTITY_ID(+)
AND XAH.APPLICATION_ID 				= XTE.APPLICATION_ID(+)
AND GJH.PERIOD_NAME 				= GP.PERIOD_NAME
AND GJH.LEDGER_ID					= GL.LEDGER_ID
AND GL.PERIOD_SET_NAME				= GP.PERIOD_SET_NAME
AND GJL.CODE_COMBINATION_ID			= GCC.CODE_COMBINATION_ID
AND FFV.FLEX_VALUE_ID 				= FFVV.FLEX_VALUE_ID
AND FFV.flex_value					= GCC.segment4
AND FFV.VALUE_CATEGORY 				= 'TURKEY ACCOUNT VALUE SET'
AND FFVV.LANGUAGE					= 'TR'
AND GJH.JE_CATEGORY					= GJCV.JE_CATEGORY_NAME
AND GL.NAME							= 'PL TURKEY TRY LOCAL'
AND GJCV.LANGUAGE					= 'TR'
AND GP.PERIOD_NAME					= :P_PERIOD

ORDER BY GJH.POSTING_ACCT_SEQ_VALUE, GJL.JE_LINE_NUM
)