Mapping Between AR and OM (Transaction Flex field)

(RAL) - RA_CUSTOMER_TRX_LINES_ALL

RAL.INTERFACE_LINE_ATTRIBUTE1 Order_Num
RAL.INTERFACE_LINE_ATTRIBUTE2 Order_Type
RAL.INTERFACE_LINE_ATTRIBUTE3 Delivery ID
RAL.INTERFACE_LINE_ATTRIBUTE4 WayBill
RAL.INTERFACE_LINE_ATTRIBUTE6 Line_ID
RAL.INTERFACE_LINE_ATTRIBUTE8 Bill_Lading
RAL.INTERFACE_LINE_ATTRIBUTE10 WH_ID RAL.INTERFACE_LINE_ATTRIBUTE11 PA_ID

Query to Join OM , WSH and AR table

SELECT h.order_number,l.line_id,l.ordered_quantity,l.shipped_quantity,l.invoiced_quantity,
wdd.delivery_detail_id,wnd.delivery_id,wdd.shipped_quantity,a.org_id,
a.creation_date ,a.trx_number,b.quantity_ordered , b.quantity_invoiced ,b.interface_line_attribute1,b.interface_line_attribute3,
b.interface_line_attribute6,interface_line_attribute12
from
ra_customer_trx_all a,
ra_customer_trx_lines_all b,
oe_order_headers_all h,
oe_order_lines_all l,
wsh_delivery_details wdd,
wsh_delivery_assignments wda,
wsh_new_deliveries wnd
where a.customer_trx_id = b.customer_trx_id
and a.interface_header_context = 'ORDER ENTRY'
and b.interface_line_attribute1 = to_char(h.order_number)
and h.header_id = l.header_id
and to_char(l.line_id) = b.interface_line_attribute6
and l.line_id = wdd.source_line_id
and wdd.delivery_detail_id = wda.delivery_detail_id
and wda.delivery_id = wnd.delivery_id
and to_char(wnd.delivery_id) = b.interface_line_attribute3

Query for Total Unapplied Receipt Amount

Select Nvl(Sum(Arr.Amount_Applied), 0) Total_Unapp_Receipts
  From Hz_Cust_Accounts_All           Cust_Acct,
       Ar_Payment_Schedules_All       Ps,
       Ar_Receivable_Applications_All Arr,
       Hz_Cust_Acct_Sites_All         Acct_Site,
       Hz_Party_Sites                 Party_Site,
       Hz_Locations                   Loc,
       Hz_Cust_Site_Uses_All          Site_Uses,
       Ar_Cash_Receipts_All           Acr,
       Ar_Cash_Receipt_History_All    Crh,
       Gl_Code_Combinations           Cc
 Where Trunc(Ps.Gl_Date) <= :p_As_Of_Date
   And Ps.Customer_Id = Cust_Acct.Cust_Account_Id
   And Cust_Acct.Account_Number = :p_Account_Number
   And Ps.Customer_Id = Cust_Acct.Cust_Account_Id
   And Acct_Site.Party_Site_Id = Party_Site.Party_Site_Id
   And Loc.Location_Id = Party_Site.Location_Id
   And Ps.Cash_Receipt_Id = Acr.Cash_Receipt_Id
   And Acr.Cash_Receipt_Id = Crh.Cash_Receipt_Id
   And Crh.Account_Code_Combination_Id = Cc.Code_Combination_Id
   And Ps.Trx_Date <= :p_As_Of_Date
   And Ps.Class = 'PMT'
   And Ps.Cash_Receipt_Id = Arr.Cash_Receipt_Id
   And Arr.Status = 'UNAPP'
   And Ps.Status = 'OP'
   And Site_Uses.Site_Use_Code = 'BILL_TO'
   And Site_Uses.Cust_Acct_Site_Id = Acct_Site.Cust_Acct_Site_Id
   And Nvl(Site_Uses.Status, 'A') = 'A'
   And Cust_Acct.Cust_Account_Id = Acct_Site.Cust_Account_Id
   And Acct_Site.Cust_Acct_Site_Id = Site_Uses.Cust_Acct_Site_Id
   And Ps.Customer_Id = Acct_Site.Cust_Account_Id
   And Ps.Customer_Site_Use_Id = Site_Uses.Site_Use_Id Having
 Nvl(Sum(Arr.Amount_Applied), 0) > 0;

Query for Total On Account Receipt Amount

Select Nvl(Sum(Ps.Amount_Due_Remaining), 0) Total_Onacct_Receipts
  From Hz_Cust_Accounts_All           Cust_Acct,
       Ar_Payment_Schedules_All       Ps,
       Ar_Receivable_Applications_All Arr,
       Hz_Cust_Acct_Sites_All         Acct_Site,
       Hz_Party_Sites                 Party_Site,
       Hz_Locations                   Loc,
       Hz_Cust_Site_Uses_All          Site_Uses,
       Ar_Cash_Receipts_All           Acr,
       Ar_Cash_Receipt_History_All    Crh,
       Gl_Code_Combinations           Cc
 Where Trunc(Ps.Gl_Date) <= :p_As_Of_Date
   And Ps.Customer_Id = Cust_Acct.Cust_Account_Id
   And Cust_Acct.Account_Number = :p_Account_Number
   And Ps.Customer_Id = Cust_Acct.Cust_Account_Id
   And Acct_Site.Party_Site_Id = Party_Site.Party_Site_Id
   And Loc.Location_Id = Party_Site.Location_Id
   And Ps.Cash_Receipt_Id = Acr.Cash_Receipt_Id
   And Acr.Cash_Receipt_Id = Crh.Cash_Receipt_Id
   And Crh.Account_Code_Combination_Id = Cc.Code_Combination_Id
   And Ps.Trx_Date <= :p_As_Of_Date
   And Ps.Class = 'PMT'
   And Ps.Cash_Receipt_Id = Arr.Cash_Receipt_Id
   And Arr.Status In ('ACC')
   And Ps.Status = 'OP'
   And Site_Uses.Site_Use_Code = 'BILL_TO'
   And Site_Uses.Cust_Acct_Site_Id = Acct_Site.Cust_Acct_Site_Id
   And Nvl(Site_Uses.Status, 'A') = 'A'
   And Cust_Acct.Cust_Account_Id = Acct_Site.Cust_Account_Id
   And Acct_Site.Cust_Acct_Site_Id = Site_Uses.Cust_Acct_Site_Id
   And Ps.Customer_Id = Acct_Site.Cust_Account_Id
   And Ps.Customer_Site_Use_Id = Site_Uses.Site_Use_Id Having
 Nvl(Sum(Arr.Amount_Applied), 0) > 0;