Track Germany Per Diem Report

SELECT 
 EMPLOYEE_ID
,ACC_STAT
,EMPLOYEE_NAME
,EXP_INVOICE_NUM
,INVOICE_DATE
,ACCOUNTING_DATE
,EXP_RECEIPT_DATE
,PERSON_ID
,RECEIPT_DATE
,EXPENSE_TYPE
,STATUS
,JUSTIFICATION
,MERCHANT_NAME
,LOCATION
,ATTENDEE_TYPE
,ATTENDEE_NAME
,EXP_AMOUNT
,PERDIEM_AMOUNT
,MEALS_AMT
,MEALS_REPORT
,MEALS_EXP_TYP
,PERDIEM_START_DATE
,PERDIEM_END_DATE
,PERDIEM_RATE
,EXPENSE_TYPE_CATEGORY_CODE
,EXTRA_MEALS
,MEALS_BREAKFAST_CODE
,MEALS_LUNCH_CODE
,MEALS_DINNER_CODE
,NON_MEAL_EXP_TYP
,PERDIEM_POLICY_ID
,DESCRIPTION
,DESCPERDIEM
,(NVL(EXTRA_MEALS,0)-(CASE WHEN upper(DESCPERDIEM) LIKE upper('%Breakfast%') THEN (PERDIEM_RATE*0.2)
     WHEN upper(DESCPERDIEM) LIKE upper('%Lunch%') THEN (PERDIEM_RATE*0.4)
     WHEN upper(DESCPERDIEM) LIKE upper('%Dinner%') THEN (PERDIEM_RATE*0.4)
     ELSE FREE_MEALS_DEDUCT_AMOUNT
END)) EXCEEDING_PERDIEM

FROM (

WITH NON_PERDIEM AS
(SELECT 
sum(EE.RECEIPT_AMOUNT) RECEIPT_AMOUNT,
TRUNC(EE.START_DATE) RECEIPT_DATE,
EE.person_id,
EE.EXPENSE_REPORT_ID,
EET.NAME Expense_type,

EER.EXPENSE_REPORT_NUM,
NVL((SELECT EET_Meal.DESCRIPTION  FROM exm_expense_types EET_Meal,exm_expenses Exp_Meal WHERE EET_Meal.EXPENSE_TYPE_ID = Exp_MEAL.EXPENSE_TYPE_ID AND Exp_Meal.EXPENSE_TYPE_CATEGORY_CODE = 'PER_DIEM' AND Exp_Meal.EXPENSE_REPORT_ID = EER.EXPENSE_REPORT_ID),EET.NAME) Non_Meal_Exp_Typ

FROM
exm_EXPENSES EE,
exm_expense_types EET,
EXM_EXPENSE_REPORTS EER
WHERE
EE.EXPENSE_TYPE_CATEGORY_CODE = 'MEALS'
--AND EET.NAME IN ('Breakfast','Lunch','Dinner')
AND EE.EXPENSE_REPORT_ID = EER.EXPENSE_REPORT_ID
AND EET.EXPENSE_TYPE_ID = EE.EXPENSE_TYPE_ID
GROUP BY
TRUNC(EE.START_DATE),
EE.person_id,
EE.EXPENSE_REPORT_ID,
EET.NAME,
EER.EXPENSE_REPORT_NUM,
EER.EXPENSE_REPORT_ID )

SELECT 
	PAPF.person_number EMPLOYEE_ID,
	AP_INVOICES_PKG.GET_POSTING_STATUS(AIA.INVOICE_ID) Acc_Stat,
	PPNFV.DISPLAY_NAME EMPLOYEE_NAME,
	NVL(AIA.INVOICE_NUM,EER.EXPENSE_REPORT_NUM) EXP_INVOICE_NUM,
	AIA.INVOICE_DATE,
	AIA.GL_DATE ACCOUNTING_DATE,
	EE.START_DATE EXP_RECEIPT_DATE,
	PAPF.PERSON_ID,
--DECODE(EE.EXPENSE_TYPE_CATEGORY_CODE,'MEALS',EE.START_DATE,'PER_DIEM',EEP.START_DATE) RECEIPT_DATE,
	DECODE(EE.EXPENSE_TYPE_CATEGORY_CODE,'PER_DIEM',EEP.START_DATE,EE.START_DATE) RECEIPT_DATE,
	EET.NAME Expense_type,
	EER.EXPENSE_STATUS_CODE STATUS,EPP.PERDIEM_POLICY_ID,
	EE.JUSTIFICATION JUSTIFICATION,
	EE.MERCHANT_NAME MERCHANT_NAME,
	EE.LOCATION ,
	EEA.ATTENDEE_TYPE ATTENDEE_TYPE,
	EEA.name ATTENDEE_NAME,
	NVL(EEP.MEALS_ALLOWANCE_AMOUNT,EE.RECEIPT_AMOUNT) EXP_AMOUNT,
	EEP.PERDIEM_AMOUNT PERDIEM_AMOUNT,
	MEALS.RECEIPT_AMOUNT Meals_AMT,
	MEALS.EXPENSE_REPORT_NUM Meals_Report,
	MEALS.Expense_type Meals_Exp_typ,
	EEP.START_DATE PERDIEM_START_DATE,
	EEP.END_DATE PERDIEM_END_DATE,
	EEP.PERDIEM_RATE,
        EET.DESCRIPTION ,
	EE.EXPENSE_TYPE_CATEGORY_CODE,
	(SELECT SUM(EE_MEALS.RECEIPT_AMOUNT)  FROM exm_EXPENSES EE_MEALS WHERE EE_MEALS.EXPENSE_REPORT_ID = EER.EXPENSE_REPORT_ID AND  EE_MEALS.person_id	= PAPF.person_id AND EE_MEALS.EXPENSE_TYPE_CATEGORY_CODE = 'MEALS'  AND TRUNC(EE_MEALS.START_DATE) = TRUNC(EEP.START_DATE)) Extra_Meals,

(SELECT DISTINCT EET1.DESCRIPTION
 FROM exm_EXPENSES EE_MEALS ,exm_expense_types EET1
WHERE 1=1
AND EE_MEALS.EXPENSE_REPORT_ID = EER.EXPENSE_REPORT_ID 
AND  EE_MEALS.person_id	= PAPF.person_id 
AND EET1.EXPENSE_TYPE_ID = EE_MEALS.EXPENSE_TYPE_ID
AND EE_MEALS.EXPENSE_TYPE_CATEGORY_CODE = 'MEALS'  
AND TRUNC(EE_MEALS.START_DATE) = TRUNC(EEP.START_DATE)
) DESCPERDIEM,
	EEP.MEALS_BREAKFAST_CODE,
	EEP.MEALS_LUNCH_CODE,
	EEP.MEALS_DINNER_CODE,
	EEP.FREE_MEALS_DEDUCT_AMOUNT,
	MEALS.Non_Meal_Exp_Typ
	
	FROM
	EXM_EXPENSE_REPORTS EER,
	exm_EXPENSES EE,
	PER_PERSON_NAMES_F PPNFV,
	PER_ALL_PEOPLE_F PAPF,
	AP_INVOICES_ALL AIA,
	EXM_EXPENSE_PERDIEM_DETAILS EEP,
	exm_expense_types EET,
	EXM_EXPENSE_ATTENDEES EEA,
	HR_OPERATING_UNITS HOU,
	EXM_POL_EXP_TYPE_MAP EPM,
	EXM_PERDIEM_POLICIES EPP,
	NON_PERDIEM MEALS
	WHERE 1=1 
	AND EE.EXPENSE_REPORT_ID = EER.EXPENSE_REPORT_ID
	AND TRUNC(SYSDATE) BETWEEN TRUNC(PPNFV.EFFECTIVE_START_DATE) AND TRUNC(PPNFV.EFFECTIVE_END_DATE)
	AND TRUNC(SYSDATE) BETWEEN TRUNC(PAPF.effective_start_date) AND TRUNC(PAPF.effective_end_date)
	AND EE.person_id	= PPNFV.person_id
	AND EE.person_id	= PAPF.person_id
	AND PAPF.person_id = PPNFV.person_Id
	AND AIA.INVOICE_NUM (+)= EER.EXPENSE_REPORT_NUM
	AND AP_INVOICES_PKG.GET_POSTING_STATUS(AIA.INVOICE_ID) = 'Y'
	AND EEP.EXPENSE_ID(+) = EE.EXPENSE_ID
	AND PPNFV.name_type = 'GLOBAL'
	AND EET.EXPENSE_TYPE_ID = EE.EXPENSE_TYPE_ID
	AND HOU.ORGANIZATION_ID = EE.ORG_ID
	AND UPPER(HOU.NAME) = 'DE BU EUR'--'DE BU EUR'
	AND EE.expense_id = EEA.expense_id(+)
	AND EET.EXPENSE_TYPE_ID = EPM.EXPENSE_TYPE_ID(+)
	AND EPM.POLICY_ID = EPP.PERDIEM_POLICY_ID(+)
	AND MEALS.RECEIPT_DATE(+)  = EEP.START_DATE
	AND MEALS.PERSON_ID(+)  = EE.person_id
	AND MEALS.EXPENSE_TYPE(+)  = DECODE(EEP.MEALS_BREAKFAST_CODE,'INCLUDE','Breakfast',EEP.MEALS_LUNCH_CODE,'INCLUDE','Lunch',EEP.MEALS_DINNER_CODE,'INCLUDE','Dinner')
	AND MEALS.EXPENSE_REPORT_NUM(+) <> EER.EXPENSE_REPORT_NUM
	--AND NVL(MEALS.Non_Meal_Exp_Typ,EET.NAME) = EET.NAME
	
	AND (CASE WHEN MEALS.Non_Meal_Exp_Typ LIKE 'PER DIEM%' THEN MEALS.Non_Meal_Exp_Typ ELSE EET.NAME end) = EET.NAME

	AND (EET.NAME IN (:P_EXP_TYPE) OR 'All' IN ('All'||:P_EXP_TYPE))
	AND (EE.MERCHANT_NAME IN (:P_MERCHANT_NAME) OR 'All' IN ('All'||:P_MERCHANT_NAME))
	AND (PPNFV.person_id IN (:P_PERSON_ID) OR 'All' IN ('All'||:P_PERSON_ID))
	AND TRUNC(AIA.GL_DATE) BETWEEN TRUNC(:P_FROM_ACCT_DATE) AND TRUNC(:P_TO_ACCT_DATE)
	ORDER BY EER.EXPENSE_REPORT_NUM, ACCOUNTING_DATE)