AR Cash Receipts Report
With Usr as (
select distinct ppf.person_id,
pu.username,
ppf.person_number Employee_Number,
ppn.full_name,
ppn.display_name
from per_users pu,
per_all_people_f ppf,
per_person_names_f ppn
where pu.person_id = ppf.person_id
and ppf.person_id = ppn.person_id
and trunc(sysdate) between trunc(nvl(ppf.effective_start_date,trunc(sysdate))) and trunc(nvl(ppf.effective_end_date,trunc(sysdate)))
and trunc(sysdate) between trunc(nvl(ppn.effective_start_date,trunc(sysdate))) and trunc(nvl(ppn.effective_end_date,trunc(sysdate)))
and nvl(ppn.name_type,'GLOBAL') = 'GLOBAL'
and trunc(sysdate) between trunc(nvl(pu.start_date,trunc(sysdate))) and trunc(nvl(pu.end_date,trunc(sysdate)))
)
SELECT * FROM
(SELECT Rch.Org_Id Org_Id,
Opr.Name Org_Name,
Substr(Opr.Name, 1, 3) Co,
Cac.Account_Number Corp_Code,
Par.Party_Name Corp_Name,
Cst.Location Cust_Code,
--Pts.Party_Site_Name Customer_Name,
Sit.attribute1 Customer_Name,
Prd.Period_Name Period,
--to_char(Rcp.Receipt_Date,'mm/dd/yyyy hh:mi:ss AM') Receipt_Date,
--To_date(to_char(Rcp.Receipt_Date,'mm/dd/yyyy hh:mi:ss AM'),'mm/dd/yyyy hh:mi:ss AM') Receipt_Date ,
-- TO_DATE(to_char(Rch.Gl_Date ,'mm/dd/yyyy hh:mi:ss AM'),'mm/dd/yyyy hh:mi:ss AM') Gl_Date,
-- to_date(to_char(Rch.Trx_Date,'mm/dd/yyyy hh:mi:ss AM'),'mm/dd/yyyy hh:mi:ss AM') Transaction_Date,
Rcp.Receipt_Date Receipt_Date,
Rch.Gl_Date Gl_Date,
Rch.Trx_Date Transaction_Date,
--Rcp.Receipt_Number Receipt_Number,
LTRIM(RTRIM(Rcp.Receipt_Number)) Receipt_Number,
Rcp.Currency_Code Currency,
Rch.Amount Receipt_Amount,
Rch.Status Rcp_Hist_Status,
--Rcp.Status Receipt_Status,
Rch.Status Receipt_Status,
Met.Name Receipt_Method,
Rcp.Type Receipt_Type,
--Rcp.Created_By,
--nvl(Usr.display_name,Users12.UserName) Created_By,
nvl(Usr.display_name,(select USERNAME from per_Users
where 1=1
AND USERNAME = Rcp.Created_By
and ACTIVE_FLAG='Y')) Created_By,
--Usr.MULTITENANCY_USERNAME
null Created_By_Desc,
--Glc.Segment3 Gac,
--Glc.Segment8 Proj,
Users12.UserName UserName1,
Rch.Account_Code_Combination_Id,
nvl(Glc.Segment3,(select GCC1.SEGMENT3
from Ar_Cash_Receipts_All Rcp1,
Ar_Cash_Receipt_History_All Rch1,
GL_code_combinations GCC1
where 1=1
and Rcp1.Receipt_Number=Rcp.Receipt_Number
and Rcp1.Cash_Receipt_Id = Rch1.Cash_Receipt_Id
AND GCC1.Code_Combination_Id= Rch1.Account_Code_Combination_Id
and Rch1.Current_Record_Flag='N'
-- and Rch1.Account_Code_Combination_Id is not null
)) Gac,
nvl(Glc.Segment8,(select GCC1.SEGMENT8
from Ar_Cash_Receipts_All Rcp1,
Ar_Cash_Receipt_History_All Rch1,
GL_code_combinations GCC1
where 1=1
and Rcp1.Receipt_Number=Rcp.Receipt_Number
and Rcp1.Cash_Receipt_Id = Rch1.Cash_Receipt_Id
AND GCC1.Code_Combination_Id= Rch1.Account_Code_Combination_Id
--and Rch1.Account_Code_Combination_Id is not null
and Rch1.Current_Record_Flag='N'
)) Proj,
--TO_CHAR(sysdate,'mm/dd/yyyy hh:mi:ss AM') timestamp,
sysdate timestamp
FROM Ar_Cash_Receipts_All Rcp,
ar_receipt_methods arm,
Ar_Cash_Receipt_History_All Rch,
Ar_Receipt_Methods Met,
Gl_Periods Prd,
Hr_Operating_Units Opr,
Hz_Cust_Accounts Cac,
Hz_Parties Par,
Hz_Cust_Acct_Sites_All Sit,
Hz_Party_Sites Pts,
Hz_Locations Loc,
Hz_Cust_Site_Uses_All Cst,
per_Users Users12,
Usr Usr,
Gl_Code_Combinations Glc
WHERE Rcp.Cash_Receipt_Id = Rch.Cash_Receipt_Id
and Rcp.receipt_method_id = arm.receipt_method_id
and arm.name not like 'TCF%'
AND Opr.Organization_Id = Rch.Org_Id
AND Cac.Cust_Account_Id(+) = Rcp.Pay_From_Customer
AND Par.Party_Id(+) = Cac.Party_Id
AND Met.Receipt_Method_Id(+) = Rcp.Receipt_Method_Id
AND Rch.Current_Record_Flag='Y'
--AND Rch.Current_Record_Flag =NVl(Rch.Account_Code_Combination_Id, 'N', 'Y')
--and DECODE(Rch.Account_Code_Combination_Id,null 'N') =Rch.Current_Record_Flag
AND Rch.Gl_Date BETWEEN Prd.Start_Date AND Prd.End_Date
AND Prd.Period_Set_Name = '4-4-5'
AND Prd.Period_Type = 'MONTH8731158251'
AND Users12.UserName = Rcp.Created_By
AND Rcp.Created_By = Usr.username(+)
AND Glc.Code_Combination_Id(+) = Rch.Account_Code_Combination_Id
AND Cst.Site_Use_Id(+) = Rcp.Customer_Site_Use_Id
AND Sit.Cust_Acct_Site_Id(+) = Cst.Cust_Acct_Site_Id
AND Pts.Party_Site_Id(+) = Sit.Party_Site_Id
AND Loc.Location_Id(+) = Pts.Location_Id
/*
AND Substr(Opr.Name, 1, 3) = nvl(:p_opr_name,Substr(Opr.Name, 1, 3))
AND Prd.Period_Name = nvl(:p_period_name,Prd.Period_Name)
AND Cst.Location = nvl(:p_cust_site_location,Cst.Location)
AND Rch.Gl_Date between nvl(to_char(:p_from_gl_date,'yyyy/MM/dd'),Rch.Gl_Date) and nvl(to_char(:p_to_gl_date,'yyyy/MM/dd'),Rch.Gl_Date)
AND Rcp.Receipt_Date between nvl(to_char(:p_from_receipt_date,'yyyy/MM/dd'),Rcp.Receipt_Date) and nvl(to_char(:p_to_receipt_date,'yyyy/MM/dd'),Rcp.Receipt_Date)
AND Rcp.Receipt_Number = nvl(:p_receipt_number,Rcp.Receipt_Number)
*/
AND ( case when Substr(Opr.Name, 1, 3) in (:p_opr_name) then 1
when (COALESCE(NULL,:p_opr_name) is NULL ) then 1
end = 1 )
AND ( case when Prd.Period_Name in (:p_period_name) then 1
when (COALESCE(NULL,:p_period_name) is NULL ) then 1
end = 1 )
AND ( case when Cst.Location in (:p_cust_site_location) then 1
when (COALESCE(NULL,:p_cust_site_location) is NULL ) then 1
end = 1 )
and ( trunc(Rch.Gl_Date) >= nvl((:p_from_gl_date),trunc(Rch.Gl_Date))
and trunc(Rch.Gl_Date) <= nvl((:p_to_gl_date),trunc(Rch.Gl_Date)) )
and ( trunc(Rcp.Receipt_Date) >= nvl((:p_from_receipt_date),trunc(Rcp.Receipt_Date))
and trunc(Rcp.Receipt_Date) <= nvl((:p_to_receipt_date),trunc(Rcp.Receipt_Date)) )
AND ( case when LTRIM(RTRIM(Rcp.Receipt_Number)) in (:p_receipt_number) then 1
when (COALESCE(NULL,:p_receipt_number) is NULL ) then 1
end = 1 )
--and Rcp.Receipt_Number='TESTmanual '
) a
WHERE 1=1
/*
AND a.Corp_Code = nvl(:p_corp_code,a.Corp_Code)
AND a.Gac = nvl(:p_gl_Segment3,a.Gac)
AND a.Proj = nvl(:p_gl_Segment8,a.Proj)
*/
AND ( case when a.Corp_Code in (:p_corp_code) then 1
when (COALESCE(NULL,:p_corp_code) is NULL ) then 1
end = 1 )
AND ( case when a.Gac in (:p_gl_gac) then 1
when (COALESCE(NULL,:p_gl_gac) is NULL ) then 1
end = 1 )
AND ( case when a.Proj in (:p_gl_proj) then 1
when (COALESCE(NULL,:p_gl_proj) is NULL ) then 1
end = 1 )
No comments:
Post a Comment