AR Customer Print Options Report
select OPR.NAME, opr.organization_id,
Substr(Opr.Name, 1, 3) Company_Code,
Sit.Ece_Tp_Location_Code Edi_Location,
Cac.Account_Number Corp_Code,
Cst.Site_Use_Code Cust_Purpose,
Cst.Location Customer_Code,
--Cst.Fob_Point Fob_Code,
Pra.Currency_Code Currency_Code,
Pra.Overall_Credit_Limit Credit_Limit,
Cst.Tax_Code Tax_Code,
Pts.Attribute1 Tp_Reference_Ext,
Pts.Attribute2 Edi_Trading_Partner_Id,
Cnm.Party_Name Carrier_Name,
Pts.Party_Site_Name Customer_Name,
Cst.Status Cust_Status,
Loc.City City,
Loc.Country Country_Code,
Ter.Territory_Short_Name Country_Name,
Trm.Name Payment_Terms,
Col.Name Collector_Name,
CASE
WHEN Cst.Attribute_Category = 'SHIP_TO' THEN
Cst.Attribute11
ELSE
NULL
END Customer_Type,
CASE
WHEN Cst.Attribute_Category = 'BILL_TO' THEN
Cst.Attribute9
ELSE
NULL
END Ship_And_Credit_Flag,
CASE
WHEN Cst.Attribute_Category = 'BILL_TO' THEN
Cst.Attribute10
ELSE
NULL
END Dsa_Flag,
CASE
WHEN Cst.Attribute_Category = 'BILL_TO' THEN
Cst.Attribute3
ELSE
NULL
END Bill_Source,
CASE
WHEN Cst.Attribute_Category = 'BILL_TO' THEN
Cst.Attribute6
ELSE
NULL
END Invoice_Print_Exception,
CASE
WHEN Cst.Attribute_Category = 'SHIP_TO' THEN
Cst.Attribute3
ELSE
NULL
END Cust_Part_Required,
CASE
WHEN Cst.Attribute_Category = 'BILL_TO' THEN
Cst.Attribute7
ELSE
NULL
END Override_Bill_To_Addr,
CASE
WHEN Cst.Attribute_Category = 'BILL_TO' THEN
Cst.Attribute12
ELSE
NULL
END Bill_To_Order_Region,
CASE
WHEN Cst.Attribute_Category = 'SHIP_TO' THEN
Cst.Attribute1
ELSE
NULL
END End_Use_Code,
CASE
WHEN Cst.Attribute_Category = 'SHIP_TO' THEN
Cst.Attribute2
ELSE
NULL
END Sub_Corp_Code,
CASE
WHEN Cst.Attribute_Category = 'SHIP_TO' THEN
Cst.Attribute8
ELSE
NULL
END Business_Class,
CASE
WHEN Cst.Attribute_Category = 'SHIP_TO' THEN
Cst.Attribute9
ELSE
NULL
END Receipt_Bank,
CASE
WHEN Cst.Attribute_Category = 'SHIP_TO' THEN
Cst.Attribute12
ELSE
NULL
END Ship_To_Order_Region,
CASE
WHEN Cst.Attribute_Category = 'SHIP_TO' THEN
Cst.Attribute13
ELSE
NULL
END Final_Dest_Country,
CASE
WHEN Cst.Attribute_Category = 'SHIP_TO' THEN
Cst.Attribute14
ELSE
NULL
END Sales_Office,
CASE
WHEN Cst.Attribute_Category = 'SHIP_TO' THEN
Cst.Attribute16
ELSE
NULL
END Assy_Loc_Flag,
CASE
WHEN Cst.Attribute_Category = 'SHIP_TO' THEN
Cst.Attribute21
ELSE
NULL
END Handling_Code,
CASE
WHEN Cst.Attribute_Category = 'BILL_TO' THEN
Cst.Attribute4
ELSE
NULL
END Lockbox,CASE
WHEN Cst.Attribute_Category = 'SHIP_TO' THEN
Cst.Attribute4
ELSE
NULL
END Ship_To_Dsa_Flag,
CASE
WHEN Cst.Attribute_Category = 'SHIP_TO' THEN
Cst.Attribute5
ELSE
NULL
END Ship_To_Ship_And_Credit,
CASE
WHEN Cst.Attribute_Category = 'SHIP_TO' THEN
Cst.Attribute6
ELSE
NULL
END Ship_To_Bill_Source, Par.Party_Name Corp_Name,
Cst.Tax_Reference Vat_Registration_Num,
Loc.Address1 Address_Line1,
Loc.Address2 Address_Line2,
Loc.Address3 Address_Line3,
Loc.Address4 Address_Line4,
Loc.Postal_Code Postal_Code,
Loc.State State,SYSDATE AS TIMESTAMP
from
Hz_Cust_Accounts Cac,
Hz_Cust_Acct_Sites_All sit,
Hz_Cust_Site_Uses_All Cst,
Hz_Customer_Profiles_f prf,
Hz_Cust_Profile_Amts_f pra,
Hz_Parties par,
Hz_Party_Sites Pts,
Hz_Locations Loc,
Fnd_Territories_Tl Ter,
Ra_Terms_Tl Trm,
hr_organization_units_f_tl hro,
Hr_Operating_Units Opr,
Ar_Collectors col,
fnd_setid_sets fss,
fnd_setid_assignments fsa,
Wsh_org_Carrier_Services Wcs,
Wsh_Carriers Wcr,
Hz_Parties Cnm
where 1=1
and fss.set_id = fsa.set_id
and fss.language= 'US'
and fsa.reference_group_name= 'HZ_CUSTOMER_ACCOUNT_SITE'
and DETERMINANT_TYPE = 'BU'
and hro.language = 'US'
and fsa.DETERMINANT_VALUE = hro.organization_id
AND Cac.Cust_Account_Id(+) = Sit.Cust_Account_Id
AND Sit.Cust_Acct_Site_Id(+) = Cst.Cust_Acct_Site_Id
AND Cst.Status = 'A'
AND Prf.Site_Use_Id(+) = Cst.Site_Use_Id
AND Pra.Cust_Account_Profile_Id(+) = Prf.Cust_Account_Profile_Id
AND Par.Party_Id(+) = Cac.Party_Id
AND Pts.Party_Site_Id(+) = Sit.Party_Site_Id
AND Loc.Location_Id(+) = Pts.Location_Id
AND Ter.Territory_Code(+) = Loc.Country
AND Ter.Language(+) = 'US'
AND Trm.Language(+) = 'US'
AND Col.Collector_Id(+) = Prf.Collector_Id
AND Trm.Term_Id(+) = Prf.Standard_Terms
AND Opr.organization_id = hro.organization_id
AND Wcs.Organization_Id(+) =hro.organization_id
AND FSA.SET_id=Cst.Set_Id
AND Wcr.Carrier_Id(+) = Wcs.Carrier_Id
AND Cnm.Party_Id(+) = Wcr.Carrier_Id
-- and hop.Location_Id=loc.Location_Id
-- AND OPR.Organization_Id(+)= RCTA.ORG_ID
/* AND Wcr.Carrier_Id(+) = Wcs.Carrier_Id
AND HOP.ORGANIZATION_PROFILE_ID = WCs.ORGANIZATION_ID
AND Cnm.Party_Id(+) = Wcr.Carrier_Id */
--and rcta.BILL_TO_CUSTOMER_ID = par.party_id
--and rcta.Organization_Id =
/* AND Cac.Account_Number = nvl(:p_corp_code,Cac.Account_Number)
AND Cst.Location = nvl(:p_customer_code,Cst.Location)
AND Cst.Site_Use_Code= nvl(:p_cust_purpose,Cst.Site_Use_Code)
AND Opr.Name = nvl (:p_co,Opr.Name)
AND cst.Status = nvl(:CUST_STATUS,cst.status) */
AND ( CASE
WHEN Cac.Account_Number IN ( :p_corp_code ) THEN 1
WHEN ( coalesce(NULL, :p_corp_code) IS NULL ) THEN 1
END = 1 )
AND ( CASE
WHEN Cst.Location IN ( :p_customer_code ) THEN 1
WHEN ( coalesce(NULL, :p_customer_code) IS NULL ) THEN 1
END = 1 )
AND ( CASE
WHEN Cst.Site_Use_Code IN ( :p_cust_purpose ) THEN 1
WHEN ( coalesce(NULL, :p_cust_purpose) IS NULL ) THEN 1
END = 1 )
AND ( CASE
WHEN opr.Name IN ( :p_co ) THEN 1
WHEN ( coalesce(NULL, :p_co) IS NULL ) THEN 1
END = 1 )
AND ( CASE
WHEN cst.status IN ( :CUST_STATUS ) THEN 1
WHEN ( coalesce(NULL,:CUST_STATUS) IS NULL ) THEN 1
END = 1 )
AND ( CASE
WHEN opr.NAME IN ( :ORG_NAME ) THEN 1
WHEN ( coalesce(NULL, :ORG_NAME) IS NULL ) THEN 1
END = 1 )
No comments:
Post a Comment