AR Invoice Summary By Transaction Name Report
SELECT Al1.Company_Code,
Al1.Period_Name,
SUM(Al1.Extended_Amount) Extended_Amount,
SUM(Al1.Invoice_Amt_Usd) Invoice_Amt_Usd,
Al1.Currency_Code,
Al1.Line_Type,
Al1.Transaction_Name,
Al1.Business_Class,
Al1.Business_Unit,
Al1.Transaction_Type,
Al1.Reason_Code,
Al1.Revenue_Account,
Al1.Tax_Account,
Al1.Bill_To_Cust_Code,
Al1.Ship_To_Cust_Name Ship_To_Cust_Code
FROM (SELECT Ctx.Org_Id Org_Id,
Substr(Opr.Name, 1, 3) Company_Code,
Cac.Account_Number Corp_Code,
Par.Party_Name Corp_Name,
Cst.Location Bill_To_Cust_Code,
Pts.Party_Site_Name Bill_To_Cust_Name,
--Pys.Party_Site_Name Ship_To_Cust_Name,
( Select a.PARTY_SITE_NUMBER
From hz_party_sites a,
Hz_party_site_uses b
where a.party_site_id = b.party_site_id
and b.party_site_use_id = ctx.SHIP_TO_PARTY_SITE_USE_ID) Ship_To_Cust_Name,
Shp.Location Ship_To_Cust_Code,
Loc.Province Bill_To_Province,
Lcs.Province Ship_To_Province,
Loc.State Bill_To_State,
Lcs.State Ship_To_State,
Loc.County Bill_To_County,
Lcs.County Ship_To_County,
Loc.Country Bill_To_Country,
Lcs.Country Ship_To_Country,
Cyb.Territory_Short_Name Bill_To_Country_Name,
Cys.Territory_Short_Name Ship_To_Country_Name,
Prd.Period_Name Period_Name,
Ctx.Trx_Number Invoice_Number,
Ctx.Trx_Date Invoice_Date,
Ctx.Creation_Date Hdr_Creation_Date,
Pmt.Due_Date Due_Date,
Ctx.Ct_Reference Reference_Number,
Ctx.Doc_Sequence_Value Doc_Sequence,
Trm.Name Payment_Terms,
Ctx.Invoice_Currency_Code Currency_Code,
Ctl.Line_Number Invoice_Line,
Ctl.Line_Type Line_Type,
Ctl.Unit_Selling_Price Unit_Price,
Ctl.Extended_Amount Extended_Amount,
CASE
WHEN Ctx.Invoice_Currency_Code = 'USD' THEN
Ctl.Extended_Amount
ELSE
Round((Ctl.Extended_Amount * Drt.Conversion_Rate), 2)
END Invoice_Amt_Usd,
CASE
WHEN Ctx.Invoice_Currency_Code = Trl.Currency_Code THEN
Ctl.Extended_Amount
ELSE
Round((Ctl.Extended_Amount * Trl.Conversion_Rate), 2)
END Invoicevamt_Funct,
CASE
WHEN Ctx.Invoice_Currency_Code = 'EUR' THEN
Ctl.Extended_Amount
ELSE
Round((Ctl.Extended_Amount * Ecb.Conversion_Rate), 2)
END Amtveur_Stat_Ecb,
Ctl.Taxable_Flag Taxable_Flag,
Ctl.Tax_Rate Tax_Rate,
Ctl.Tax_Recoverable Recoverable_Tax_Amt,
-- Cc1.Segment2 Receivable_Account,
Gl1.Revenue_Account Revenue_Account,--ok
Gl1.Business_Unit Business_Unit,--ok
Gl2.Tax_Account Tax_Account,--ok
Ctl.Quantity_Invoiced Line_Quantity,
Ctl.Quantity_Credited Qtyvcredited,
Msb.item_number Part_Number,
Ctl.Description Line_Description,
Ctx.Status_Trx Invoice_Status,
Cut.Name Transaction_Name,--ok
Cut.Type Transaction_Type,--ok
Cut.Accounting_Affect_Flag Affect_Accounting,
Cut.Post_To_Gl Post_To_Gl,
Ctx.Complete_Flag Complete_Flag,
Btc.Name Batch_Source,
Ctl.Sales_Order Sales_Order,
Ctl.Sales_Order_Line Sales_Order_Line,
/* Substr(Ovr.Line_Details,
(Instr(Ovr.Line_Details, '~') + 1),
Length(Ovr.Line_Details) -
(Instr(Ovr.Line_Details, '~'))) Order_Line_Line,*/
Ctx.Attribute1 Trx_Business_Class,
Ctx.Attribute2 Statutory_Rate,
Ctl.Attribute1 Price_Source,
Ctl.Attribute4 Tariff_Code,
Ctl.Interface_Line_Attribute2 Line_Information,
Ctl.Interface_Line_Attribute3 Inco_Terms,
Ctl.Interface_Line_Attribute4 Customer_Item,
Ctl.Interface_Line_Attribute5 Eccn_Number,
Ctl.Interface_Line_Attribute6 Htsus_Number,
Ctl.Interface_Line_Attribute7 Country_Of_Origin,
Ctl.Interface_Line_Attribute9 Cust_Ship_Ref,
Ctl.Interface_Line_Attribute10 Consignment_Po,
Ctl.Interface_Line_Attribute11 Citi_Number,
Ctl.Interface_Line_Attribute12 Waybill_Number,
/*Ovr.Ship_Area Ship_Area,
Ovr.Purchase_Order Customer_Po,
Usr.User_Name Inv_Created_Id,
Usr.Description Inv_Created_Name,*/
Cut.Attribute1 Reason_Code,--ok
Cut.Attribute2 Auto_Accrual_Rev,
Cut.Attribute3 Accrl_Rev_Deb_Act,
Cut.Attribute4 Recharge_161,
Cut.Attribute5 True_Bill,
Cut.Attribute6 Int_Ext,
Cut.Attribute8 Qty_Override,
Cut.Attribute10 Obr_Rebate,
Cut.Attribute11 Accrl_Rev_Cred_Act,
Cut.Attribute12 Accr_Rev_Journal_Desc,
Cut.Attribute13 Obr_Dcd_Accrual,
Cut.Attribute14 Obr_Dsc_Accrual,
Cut.Attribute15 Obr_Dsa_Accrual,
Ctx.Customer_Trx_Id Customer_Trx_Id,
Ctl.Customer_Trx_Line_Id Cust_Trx_Line_Id,
Ctx.Bill_To_Customer_Id Bill_To_Cust_Id,
Ctx.Ship_To_Customer_Id Ship_To_Cust_Id,
Ctx.Bill_To_Site_Use_Id Bill_To_Site_Use_Id,
Pmt.Term_Id Pmt_Term_Id,
Ctl.Vat_Tax_Id Vat_Tax_Id,
Ctl.Inventory_Item_Id Inventory_Item_Id,
/* Ovr.Italy_Invoice_Sequence_Num Italy_Seq_Num,
Ovr.Bill_Source_Code Bill_Source,
Ovr.Ship_Method Ship_Method,
Ovr.Customer_Tax_Id_Number Cust_Tax_Id,
Ovr.Number_Of_Cartons Number_Cartons,
Ovr.Gross_Weight Gross_Weight,*/
Loc.Address1 Bill_To_Address_1,
Loc.Address2 Bill_To_Address_2,
Loc.Address3 Bill_To_Address_3,
Loc.City Bill_To_City,
Loc.Postal_Code Bill_To_Postal_Cd,
Lcs.Address1 Ship_To_Address_1,
Lcs.Address2 Ship_To_Address_2,
Lcs.Address3 Ship_To_Address_3,
Lcs.City Ship_To_City,
Lcs.Postal_Code Ship_To_Postal_Cd,
Drt.Conversion_Rate Usd_Corp_Rate,
Trl.Conversion_Rate Funct_Rate,
Gl1.Gl_Posted_Date Gl_Posted_Date,
/* CASE
WHEN Ctx.Org_Id = 119 THEN
To_Number(Ctl.Attribute5)
ELSE
NULL
END Taiwan_Stat_Rate,
CASE
WHEN Ctx.Org_Id = 113 THEN
Nvl(To_Number(Ctl.Attribute5), Trl.Conversion_Rate)
ELSE
NULL
END Sgd_Hist_Rate,*/
CASE
WHEN Ctl.Line_Type = 'LINE' THEN
Gl1.Last_Update_Date
ELSE
Gl2.Last_Update_Date
END Accounted_Date,
-- Ovr.Link_To_Transaction Link_To_Transaction,
Shp.Attribute8 Business_Class,--ok
Shp.Attribute1 Cust_Type,
--Cst.Fob_Point Fob_Code,
--Shp.Fob_Point Fob_Ship_To,
Ctx.Attribute6 Biz_Ownership_Cd,
-- Ovr.Ship_Bank Ship_Bank,
Shp.Tax_Reference Fiscal_Acct_Num,
Cst.Tax_Reference Bill_To_Fisc_Acct,
-- Ovr.Ship_Bank_Ctry Ship_Bank_Ctry,
Zxr.Tax_Rate_Name Tax_Code,
Hou.NAME Org_Name
FROM Ra_Customer_Trx_All Ctx,
Ra_Customer_Trx_Lines_All Ctl,
Hz_Cust_Accounts Cac,
Hz_Cust_Acct_Sites_All Sit,
Hz_Cust_Site_Uses_All Cst,
Hz_Cust_Site_Uses_All Shp,
Hz_Parties Par,
Hz_Party_Sites Pts,
Hz_Locations Loc,
Hr_Operating_Units Opr,
Ra_Cust_Trx_Types_All Cut,
Gl_Periods Prd,
Gl_Daily_Rates Drt,
--Fnd_User Usr,
Ra_Batch_Sources_All Btc,
Ar_Payment_Schedules_All Pmt,
Ra_Terms_Tl Trm,
--Gl_Code_Combinations Cc1,
-- Mtl_System_Items_b Msb,
egp_system_items_b Msb,
Zx_Rates_Tl Zxr,
--Xxon_Billing_Data_Overflow Ovr,
Hz_Cust_Acct_Sites_All Sts,
Hz_Party_Sites Pys,
Hz_Locations Lcs,
Fnd_Territories_Tl Cyb,
Fnd_Territories_Tl Cys,
HR_ORGANIZATION_UNITS_F_TL Hou,
(SELECT Fnc.From_Currency,
Fnc.Conversion_Rate,
Txn.Customer_Trx_Id,
Lgr.Currency_Code
FROM Gl_Daily_Rates Fnc,
Gl_Ledgers Lgr,
Ra_Customer_Trx_All Txn
WHERE Fnc.Conversion_Type = 'Corporate'
AND Lgr.Ledger_Id = Txn.Set_Of_Books_Id
AND Fnc.Conversion_Date = Txn.Trx_Date
AND Fnc.To_Currency = Lgr.Currency_Code) Trl,
(SELECT Ecr.From_Currency,
Ecr.Conversion_Rate,
Txn.Customer_Trx_Id
FROM Gl_Daily_Rates Ecr, Ra_Customer_Trx_All Txn
WHERE Ecr.Conversion_Type = '1024' /*Stat ECB*/
AND Ecr.Conversion_Date = Txn.Trx_Date
AND Ecr.To_Currency = 'EUR') Ecb,
(SELECT Gld.Customer_Trx_Line_Id,
Gld.Last_Update_Date,
Gld.Gl_Posted_Date,
Gld.Amount,
Cc2.Segment3 Revenue_Account,
Cc2.Segment6 Business_Unit
FROM Ra_Cust_Trx_Line_Gl_Dist_All Gld,
Gl_Code_Combinations Cc2
WHERE Gld.Account_Class = 'REV'
AND Cc2.Code_Combination_Id = Gld.Code_Combination_Id
AND Gld.Event_Id =
(SELECT DISTINCT MAX(Event_Id) Over()
FROM Ra_Cust_Trx_Line_Gl_Dist_All Gld2
WHERE Gld2.Customer_Trx_Line_Id =
Gld.Customer_Trx_Line_Id)) Gl1,
(SELECT Gld.Customer_Trx_Line_Id,
Gld.Last_Update_Date,
Gld.Gl_Posted_Date,
Gld.Amount,
Cc3.Segment3 Tax_Account
FROM Ra_Cust_Trx_Line_Gl_Dist_All Gld,
Gl_Code_Combinations Cc3
WHERE Gld.Account_Class = 'TAX'
AND Cc3.Code_Combination_Id = Gld.Code_Combination_Id
AND Gld.Event_Id =
(SELECT DISTINCT MAX(Event_Id) Over()
FROM Ra_Cust_Trx_Line_Gl_Dist_All Gld2
WHERE Gld2.Customer_Trx_Line_Id =
Gld.Customer_Trx_Line_Id)) Gl2
WHERE Ctx.Customer_Trx_Id = Ctl.Customer_Trx_Id
--AND Cut.Cust_Trx_Type_Id = Ctx.Cust_Trx_Type_Id
AND Cut.cust_trx_type_id = Ctx.cust_trx_type_seq_id
--AND Cut.Org_Id = Ctx.Org_Id
AND Cac.Cust_Account_Id(+) = Ctx.Bill_To_Customer_Id
AND Par.Party_Id(+) = Cac.Party_Id
AND Cst.Site_Use_Id(+) = Ctx.Bill_To_Site_Use_Id
AND Shp.Site_Use_Id(+) = Ctx.Ship_To_Site_Use_Id
AND Opr.Organization_Id = Ctx.Org_Id
AND Ctx.Trx_Date BETWEEN Prd.Start_Date AND Prd.End_Date
AND Prd.Period_Set_Name = '4-4-5'
-- AND Prd.Period_Type = 'Month'
AND Gl1.Customer_Trx_Line_Id(+) = Ctl.Customer_Trx_Line_Id
AND Gl1.Amount(+) = Ctl.Extended_Amount
AND Gl2.Customer_Trx_Line_Id(+) = Ctl.Customer_Trx_Line_Id
AND Gl2.Amount(+) = Ctl.Extended_Amount
--AND Cc1.Code_Combination_Id(+) = Cut.Gl_Id_Rec
AND Drt.Conversion_Type(+) = 'Corporate'
AND Drt.To_Currency(+) = 'USD'
AND Drt.From_Currency(+) = Ctx.Invoice_Currency_Code
AND Drt.Conversion_Date(+) = Ctx.Trx_Date
--AND Usr.User_Id = Ctx.Created_By
-- AND Btc.Batch_Source_Id(+) = Ctx.Batch_Source_Id
AND Btc.batch_source_seq_id(+) = ctx.batch_source_seq_id
AND Trm.Term_Id(+) = Ctx.Term_Id
AND Trm.Language(+) = 'US'
AND Pmt.Customer_Trx_Id(+) = Ctx.Customer_Trx_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 Msb.Inventory_Item_Id(+) = Ctl.Inventory_Item_Id
AND Msb.Organization_Id(+) = '300000458568237'
AND Zxr.Tax_Rate_Id(+) = Ctl.Vat_Tax_Id
AND Zxr.Language(+) = 'US'
-- AND Ovr.Customer_Trx_Line_Id(+) = Ctl.Customer_Trx_Line_Id
AND Trl.From_Currency(+) = Ctx.Invoice_Currency_Code
AND Trl.Customer_Trx_Id(+) = Ctx.Customer_Trx_Id
AND Ecb.From_Currency(+) = Ctx.Invoice_Currency_Code
AND Ecb.Customer_Trx_Id(+) = Ctx.Customer_Trx_Id
AND Sts.Cust_Acct_Site_Id(+) = Shp.Cust_Acct_Site_Id
AND Pys.Party_Site_Id(+) = Sts.Party_Site_Id
AND Lcs.Location_Id(+) = Pys.Location_Id
AND Cyb.Territory_Code(+) = Loc.Country
AND Cyb.Language(+) = 'US'
AND Cys.Territory_Code(+) = Lcs.Country
AND Cys.Language(+) = 'US'
AND Hou.Organization_Id = Ctx.Org_Id
AND Hou.Language = 'US'
--AND ctx.trx_number IN ( '291010000060')
AND ( case when Hou.NAME in (:P_ORG_NAME) then 1
when (COALESCE(NULL,:P_ORG_NAME) is NULL ) then 1
end = 1 )
AND ( case when Cut.accounting_affect_flag in (:P_AFFECT_ACCOUNTING) then 1
when (COALESCE(NULL,:P_AFFECT_ACCOUNTING) is NULL ) then 1
end = 1 )
) Al1
where 1 = 1
AND ( case when Substr(Al1.Company_Code, 1, 3) in (:P_CO) then 1
when (COALESCE(NULL,:P_CO) is NULL ) then 1
end = 1 )
AND ( case when Al1.Period_Name in (:P_PERIOD_NAME) then 1
when (COALESCE(NULL,:P_PERIOD_NAME) is NULL ) then 1
end = 1 )
AND ( case when Al1.extended_amount in (:P_EXTENDED_AMOUNT) then 1
when (COALESCE(NULL,:P_EXTENDED_AMOUNT) is NULL ) then 1
end = 1 )
AND ( case when Al1.Revenue_Account in (:P_GL_ACCOUNT) then 1
when (COALESCE(NULL,:P_GL_ACCOUNT) is NULL ) then 1
end = 1 )
GROUP BY Al1.Company_Code,
Al1.Period_Name,
Al1.Currency_Code,
Al1.Line_Type,
Al1.Transaction_Name,
Al1.Business_Class,
Al1.Business_Unit,
Al1.Transaction_Type,
Al1.Reason_Code,
Al1.Revenue_Account,
Al1.Tax_Account,
Al1.Bill_To_Cust_Code,
Al1.Ship_To_Cust_Name
No comments:
Post a Comment