AR Customer Credit Check Report

 AR Customer Credit Check Report

SELECT '1' key,

       Op_Unit ou,

       Org_Id,

       Cust_Code,

       Customer_Name,       

       Corp_Code,

       Corp_Type,

       Currency_Code Currency,

       Credit_Checking,

       Credit_Hold,

       Trx_Credit_Limit,

       Overall_Credit_Limit,

       Backlog_Amt,

       Open_Amt Open_Ar_Amt,

       Nvl(Backlog_Amt, 0) + Nvl(Open_Amt, 0) Total_Amt,

       Payment_Terms,

       NAME Collector_Name

  FROM (SELECT Bsu.Location Cust_Code,

               Hps.Party_Site_Name Customer_Name,

               Substr(Hro.Name, 1, 3) Op_Unit,  -- Added as part of Cloud Changes

--               Bsu.Org_Id Org_Id, -- Commented as part of Cloud Changes

   Hro.organization_id Org_Id,

               Hca.Attribute5 Corp_Type,

               Hca.Account_Number Corp_Code,

               Hp.Party_Name Corp_Description,

               Arc.Name,

               Hcp.Credit_Checking,

               Hcp.Credit_Hold,

               Hcpa.Trx_Credit_Limit,

               Hcpa.Overall_Credit_Limit,

               Hcpa.Currency_Code Currency_Code,

               NVL((SELECT SUM(Round(( 

   --Oola.Ordered_Quantity * -- Commented as part of Cloud Changes

   ORDERED_QTY * -- Added as part of Cloud Changes

                                 Oola.Unit_Selling_Price),

                                 2)) Backlog_Amt

                

                  FROM doo_Headers_All Ooha,

                       doo_Lines_All   Oola

                 WHERE Ooha.Header_Id = Oola.Header_Id

                 --AND Ooha.Invoice_To_Org_Id = Bsu.Site_Use_Id   -- Commented as part of Cloud Changes

   AND Ooha.sold_To_party_Id = hp.party_id  -- Added as part of Cloud Changes

                   AND Ooha.Status_Code = 'BOOKED'   

                   AND Oola.Status_Code IN

                       ('BOOKED',  

                        'AWAITING_SHIPPING',

                        'AWAITING_FULFILLMENT',

                        'SHIPPED')),0) Backlog_Amt, 

               (SELECT SUM(Scd.Amount_Due_Remaining)

                  FROM Hz_Cust_Site_Uses_All    Bls,

                       Ar_Payment_Schedules_All Scd

                 WHERE Scd.Customer_Site_Use_Id = Bls.Site_Use_Id

                   AND Bls.Site_Use_Id = Bsu.Site_Use_Id

                   AND Scd.Status = 'OP'

                   AND Scd.Amount_Due_Remaining <> 0) Open_Amt,

               Rt.Name Payment_Terms

          FROM Hz_Cust_Site_Uses_All  Bsu,

               Hz_Customer_Profiles_f   Hcp,

               Hz_Party_Sites         Hps,

               Ar_Collectors          Arc,

               Ra_Terms               Rt,

               Hz_Parties             Hp,

               Hz_Cust_Acct_Sites_All Hcas,

               --Hr_Operating_Units     Hou, -- Commented as part of Cloud Changes

               Hz_Cust_Accounts       Hca,

               Hz_Cust_Profile_Amts_f   Hcpa,

   fnd_setid_sets         Fss, -- Added as part of Cloud Changes

   fnd_setid_assignments  Fsa, -- Added as part of Cloud Changes

   hr_organization_units_f_tl Hro -- Added as part of Cloud Changes

        

         WHERE Bsu.Site_Use_Code = 'BILL_TO'

           AND Bsu.Site_Use_Id = Hcp.Site_Use_Id

           AND Hcas.Cust_Acct_Site_Id = Bsu.Cust_Acct_Site_Id

           AND Hp.Party_Id = Hca.Party_Id

           AND Hp.Party_Type = 'ORGANIZATION'

           AND Arc.Collector_Id(+) = Hcp.Collector_Id

           AND Hps.Party_Site_Id = Hcas.Party_Site_Id

           --AND Hou.Organization_Id = Bsu.Org_Id -- Commented as part of Cloud Changes

           AND Hcas.Cust_Account_Id = Hca.Cust_Account_Id

           AND Rt.Term_Id = Hcp.Standard_Terms(+)

           AND Hcpa.Cust_Account_Profile_Id = Hcp.Cust_Account_Profile_Id

           AND Bsu.Status = 'A'

   AND Fss.set_id = Fsa.set_id -- Added as part of Cloud Changes

   AND Fss.set_id = Bsu.set_id  -- Added as part of Cloud Changes

   AND Fss.language= 'US' -- Added as part of Cloud Changes

   AND Fsa.reference_group_name= 'HZ_CUSTOMER_ACCOUNT_SITE' -- Added as part of Cloud Changes

   AND Fsa.determinant_type = 'BU' -- Added as part of Cloud Changes

   AND Fsa.determinant_value = Hro.organization_id -- Added as part of Cloud Changes

   AND Hro.language = 'US' -- Added as part of Cloud Changes

   AND Hro.organization_id = nvl(:P_ORG_ID, Hro.organization_id)

           --AND Bsu.ORG_ID = NVL(:P_ORG_ID,Bsu.ORG_ID) -- Commented as part of Cloud Changes

           AND SUBSTR(Hro.NAME,1,3) = NVL(:P_NAME,SUBSTR(Hro.NAME,1,3))

           AND Hca.Account_Number = NVL(:P_ACCT_NUM,Hca.Account_Number)

           AND Bsu.Location = NVL(:P_LOC,Bsu.Location)

           )