Credit Card Issuer Invoice Report

<![CDATA[WITH LE_TBL AS
(SELECT distinct default_Code_comb_id, PERSON_ID FROM per_all_assignments_m WHERE ASSIGNMENT_TYPE = 'E'
 AND SYSDATE 								BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE)

SELECT 
	PPNFV.FULL_NAME EMPLOYEE_NAME,
	PAPF.PERSON_NUMBER EMPLOYEE_NUMBER,
	IC.MASKED_CC_NUMBER CORPORATE_CARD_NUMBER,
	ECCT.REFERENCE_NUMBER, 
	TRUNC(ECCT.CREATION_DATE) CREATION_DATE,
	TRUNC(ECCT.POSTED_DATE) POSTED_DATE, 
    TRUNC(ECCT.BILLED_DATE) BILLED_DATE,
    TRUNC(ECCT.TRANSACTION_DATE) TRANSACTION_DATE,
	ECCT.BILLED_AMOUNT,
	HOUFTL.NAME BUSINESS_UNIT,
	--LT.name LEGAL_ENTITY,
	EE.EXPENSE_REPORT_ID,
	(SELECT gcc.segment1||' - '||gl_flexfields_pkg.get_description_sql(gcc.chart_of_accounts_id,1,gcc.segment1) FROM GL_CODE_COMBINATIONS GCC WHERE GCC.CODE_COMBINATION_ID = LT.default_Code_comb_id) LEGAL_ENTITY

FROM
	EXM_CREDIT_CARD_TRXNS ECCT,
	EXM_EXPENSES EE,
	EXM_CARDS EC,
	IBY_CREDITCARD IC,
	PER_ALL_PEOPLE_F PAPF, 
	PER_PERSON_NAMES_F_V PPNFV,
	HR_ORGANIZATION_UNITS_F_TL HOUFTL,
	LE_TBL LT
	
WHERE 1=1
	AND ECCT.CREDIT_CARD_TRXN_ID 				= EE.CREDIT_CARD_TRXN_ID
	AND ECCT.CARD_ID 							= EC.CARD_ID
	AND EC.CARD_REFERENCE_ID 					= IC.INSTRID
	AND EE.PERSON_ID 							= PAPF.PERSON_ID
	AND PAPF.PERSON_ID 							= PPNFV.PERSON_ID
	AND EE.ORG_ID 								= HOUFTL.ORGANIZATION_ID
	AND HOUFTL.LANGUAGE 						= USERENV('LANG')
	--AND ECCT.REFERENCE_NUMBER = '140110948441234'
	AND SYSDATE 								BETWEEN PPNFV.EFFECTIVE_START_DATE AND PPNFV.EFFECTIVE_END_DATE
	AND SYSDATE 								BETWEEN HOUFTL.EFFECTIVE_START_DATE AND HOUFTL.EFFECTIVE_END_DATE
	AND (-1) = (
              nvl(
                EE.ITEMIZATION_PARENT_EXPENSE_ID, -1
              )
            ) 			
	AND PAPF.PERSON_ID = LT.PERSON_ID
	AND (HOUFTL.NAME IN (:P_BU) OR 'All' IN ('All'||:P_BU))
	AND ((SELECT SEGMENT1 FROM GL_CODE_COMBINATIONS GCC WHERE GCC.CODE_COMBINATION_ID = LT.default_Code_comb_id) IN (:P_LEGAL_ENTITY) OR 'All' IN ('All'||:P_LEGAL_ENTITY))
	AND ECCT.POSTED_DATE BETWEEN :P_POSTED_DATE_FROM AND :P_POSTED_DATE_TO
	AND ECCT.CREATION_DATE BETWEEN NVL(:P_CREATION_DATE_FROM,ECCT.CREATION_DATE) AND NVL(:P_CREATION_DATE_TO,ECCT.CREATION_DATE)
	
ORDER BY
	ECCT.TRANSACTION_DATE DESC,
	PPNFV.FULL_NAME]]>