AR Customer Over Credit Limit Report

 AR Customer Over Credit Limit Report

SELECT  distinct Al1.Org_Id,

       Al1.Company_Code,

       Al1.Corp_Code,

       Al1.Corp_Name,

       Al1.Corp_Type,

       Al1.Customer_Code,

       Al1.Customer_Name,

       Al1.Cust_Purpose,

       Al2.Currency_Code,

       Al2.Remaining_Amt_Iv_Cur,

       Al1.Credit_Limit_Currency,

       Al1.Credit_Limit,

       --'=IFNA(VLOOKUP(F%GEXDROW%,BILLORDAMT,2,FALSE),0)' Bill_Net_Amt,

       --'=IFNA(VLOOKUP(F%GEXDROW%,BILLORDAMT,3,FALSE),0)' Backlog_Net_Amt,

   NULL AS Bill_Net_Amt,

   NULL AS Backlog_Net_Amt,

       SYSDATE AS TIMESTAMP

  FROM

  /*SELECT a.Org_Id,

       a.Company_Code,

       a.Corp_Code,

       a.Corp_Name,

       a.Corp_Type,

       a.Customer_Code,

       a.Customer_Name,

       a.Cust_Purpose,

       a.Credit_Limit_Currency,

       a.Credit_Limit 

  FROM */

  (SELECT 

   hro.organization_id  Org_id,

   ---Cst.Org_Id Org_Id,

               Substr(Opr.Name, 1, 3) Company_Code,

               Cac.Account_Number Corp_Code,

               Par.Party_Name Corp_Name,

               Cac.Attribute5 Corp_Type,

               Cst.Location Customer_Code,

               Pts.Party_Site_Name Customer_Name,

               Cst.Site_Use_Code Cust_Purpose,

               Pra.Currency_Code Credit_Limit_Currency,

               Pra.Overall_Credit_Limit Credit_Limit

        

          FROM fnd_setid_sets fss,

               fnd_setid_assignments fsa,

               hr_organization_units_f_tl hro,

       Hz_Cust_Accounts       Cac,

               Hz_Cust_Acct_Sites_All Sit,

               Hz_Cust_Site_Uses_All  Cst,

               --Hz_Customer_Profiles Prf,

   Hz_Customer_Profiles_f Prf,

               --Hz_Cust_Profile_Amts Pra,

   Hz_Cust_Profile_Amts_f Pra,

               Hz_Parties             Par,

               Hz_Party_Sites         Pts,

               Hz_Locations           Loc,

               Fnd_Territories_Tl     Ter,

               Hr_Operating_Units     Opr,

               Ra_Terms_Tl            Trm,

               Ar_Collectors          Col,

               --Wsh_Carrier_Services   Wcs,

   Wsh_Org_Carrier_Services Wcs,

               Wsh_Carriers           Wcr,

               Hz_Parties             Cnm

                  WHERE 1=1

           and fss.set_id = fsa.set_id

   and fss.enterprise_id = fsa.enterprise_id

           and fss.language= 'US'

    and sysdate between hro.effective_start_date and hro.effective_end_date

and sysdate between Prf.effective_start_date and Prf.effective_end_date

  and sysdate between pra.effective_start_date and pra.effective_end_date

           and fsa.reference_group_name= 'HZ_CUSTOMER_ACCOUNT_SITE'

           and DETERMINANT_TYPE = 'BU'

           --and DETERMINANT_VALUE = 300000006114783

           and fsa.DETERMINANT_VALUE = hro.organization_id

           and hro.language = 'US'

   and Ter.Territory_Code(+) = Loc.Country

           --AND Opr.Organization_Id = Cst.Org_Id

   AND Opr.organization_id = hro.organization_id

           AND Prf.Site_Use_Id(+) = Cst.Site_Use_Id

           AND Col.Collector_Id(+) = Prf.Collector_Id

           AND Cac.Cust_Account_Id(+) = Sit.Cust_Account_Id

           AND Sit.Cust_Acct_Site_Id(+) = Cst.Cust_Acct_Site_Id

           AND Pra.Cust_Account_Profile_Id(+) = Prf.Cust_Account_Profile_Id

           AND Par.Party_Id(+) = Cac.Party_Id

           AND Trm.Term_Id(+) = Prf.Standard_Terms

           AND Ter.Language(+) = 'US'

           AND Trm.Language(+) = 'US'

           AND Cst.Status = 'A'

           AND Pts.Party_Site_Id(+) = Sit.Party_Site_Id

           AND Loc.Location_Id(+) = Pts.Location_Id

           --AND Wcs.Ship_Method_Code(+) = Cst.Ship_Via

   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 hro.organization_id = nvl(:p_org_id,hro.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 hro.organization_id = nvl (:p_org_id,hro.organization_id)

   AND Opr.Name = nvl (:p_co,Opr.Name)

   --AND Cst.Location = nvl (:p_customer_code,Cst.Location)

           --AND Cac.Account_Number = nvl(:p_corp_code,Cac.Account_Number)

   --AND Cst.Site_Use_Code = nvl (:p_cust_purpose,Cst.Site_Use_Code)

        ) Al1,

       (SELECT Pmt.Org_Id Org_Id,

               Substr(Opr.Name, 1, 3) Company_Code,

               Cac.Account_Number Corp_Code,

               Par.Party_Name Corp_Name,

               Cst.Location Customer_Code,

               Substr(Nvl(Loc.Address_Lines_Phonetic, Par.Party_Name), 1, 50) Customer_Name,

               Pmt.Invoice_Currency_Code Currency_Code,

               SUM(Pmt.Amount_Due_Remaining) Remaining_Amt_Iv_Cur

          FROM Ar_Payment_Schedules_All Pmt,

               Hr_Operating_Units       Opr,

               Hz_Cust_Accounts         Cac,

               Hz_Parties               Par,

               Hz_Cust_Site_Uses_All    Cst,

               Hz_Party_Sites           Pts,

               Hz_Locations             Loc,

               Hz_Cust_Acct_Sites_All   Sit

         WHERE Opr.Organization_Id = Pmt.Org_Id

           AND Cac.Cust_Account_Id = Pmt.Customer_Id

           AND Cst.Site_Use_Id(+) = Pmt.Customer_Site_Use_Id

           AND Par.Party_Id(+) = Cac.Party_Id

           AND Pmt.Status = 'OP'

           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

         GROUP BY Pmt.Org_Id,

                  Substr(Opr.Name, 1, 3),

                  Cac.Account_Number,

                  Par.Party_Name,

                  Cst.Location,

                  Substr(Nvl(Loc.Address_Lines_Phonetic, Par.Party_Name),

                         1,

                         50),

                  Pmt.Invoice_Currency_Code) Al2

 WHERE Al2.Org_Id(+) = Al1.Org_Id

   AND Al2.Corp_Code(+) = Al1.Corp_Code

   AND Al2.Customer_Code(+) = Al1.Customer_Code