HACTL AR Standard Receipts

select
ri."Receipt Number" receipt_number,
ri."Receipt Method" receipt_method,
cha.customer_account_number,
cha.customer_name,
ri."Receipt Date" receipt_date,
ra."Apply Date" apply_date,
ath."Transaction Number" "Applied to Transaction",
atd."Transaction Description" transaction_description,
ri."Receipt Currency" receipt_currency,
ri."Amount in Receipt Currency" amount_in_receipt_currency,
ri."Amount in HKD" "Amount In HKD",
ri."Exchange Rate" exchange_rate,
ra."Applied in HKD" "Amount Applied in HKD",
ra."TRX Customer Account Number" transaction_customer_account_n,
tt."Tranaction Type" tranaction_type
from
(-- invoice title for non OM and non Deposit Invoice with Comments
Select rct.Customer_Trx_Id As "Transaction ID",
       Rct.Trx_Number As "Transaction Number",
       rct.Batch_Source_Id As "Transaction Source ID",
       Rct.Cust_Trx_Type_Id As "Transaction Type ID",
       Rct.Org_Id As "Operating Unit ID",
       Rct.Comments As "Transaction Description"
From   Ra_Customer_Trx Rct,
       Ra_Cust_Trx_Types_All Rctta
Where  Rct.Cust_Trx_Type_Id = Rctta.Cust_Trx_Type_Id
And    Rctta.Type <> 'DEP'
--And    (Rct.Interface_Header_Context Is Null 
-- Or    Rct.Interface_Header_Context <> 'ORDER ENTRY')
and    rct.comments is not null
   AND NOT EXISTS
 (SELECT 'N'
          FROM ra_customer_trx rctb, ra_customer_trx_lines rctl
         WHERE rctb.customer_trx_id = rctl.customer_trx_id
           AND rct.customer_trx_id = rctb.customer_trx_id
           AND rctl.interface_line_context = 'ORDER ENTRY')
union
-- invoice title for non OM and non Deposit invoice without Comments
Select rct.Customer_Trx_Id As "Transaction ID",
       Rct.Trx_Number As "Transaction Number",
       rct.Batch_Source_Id As "Transaction Source ID",
       Rct.Cust_Trx_Type_Id As "Transaction Type ID",
       Rct.Org_Id As "Operating Unit ID",
       Rctl.description As "Transaction Description"
From   Ra_Cust_Trx_Types_All Rctta,
       Ra_Customer_Trx Rct,
       ra_customer_trx_lines rctl
Where  Rct.Cust_Trx_Type_Id = Rctta.Cust_Trx_Type_Id
and    rct.org_id = rctta.org_id
and    rct.customer_trx_id = rctl.customer_trx_id
And    Rctta.Type <> 'DEP'
--And    (Rct.Interface_Header_Context Is Null 
-- Or    Rct.Interface_Header_Context <> 'ORDER ENTRY')
and    Rct.Comments is null
and    rctl.line_number = 1
   AND NOT EXISTS
 (SELECT 'N'
          FROM ra_customer_trx rctb, ra_customer_trx_lines rctl
         WHERE rctb.customer_trx_id = rctl.customer_trx_id
           AND rct.customer_trx_id = rctb.customer_trx_id
           AND rctl.interface_line_context = 'ORDER ENTRY')
union
-- invoice title for Deposit Invoice
Select rct.Customer_Trx_Id As "Transaction ID",
       Rct.Trx_Number As "Transaction Number",
       rct.Batch_Source_Id As "Transaction Source ID",
       Rct.Cust_Trx_Type_Id As "Transaction Type ID",
       Rct.Org_Id As "Operating Unit ID",
       Rctl.description As "Transaction Description"
From   Ra_Cust_Trx_Types_All Rctta,
       Ra_Customer_Trx Rct,
       ra_customer_trx_lines rctl
Where  Rct.Cust_Trx_Type_Id = Rctta.Cust_Trx_Type_Id
and    rct.org_id = rctta.org_id
And    Rctta.type = 'DEP'
and    rct.customer_trx_id = rctl.customer_trx_id
   AND NOT EXISTS
 (SELECT 'N'
          FROM ra_customer_trx rctb, ra_customer_trx_lines rctl
         WHERE rctb.customer_trx_id = rctl.customer_trx_id
           AND rct.customer_trx_id = rctb.customer_trx_id
           AND rctl.interface_line_context = 'ORDER ENTRY')
union
-- invoice title for SO invoices
Select rct.Customer_Trx_Id As "Transaction ID",
       Rct.Trx_Number As "Transaction Number",
       rct.Batch_Source_Id As "Transaction Source ID",
       Rct.Cust_Trx_Type_Id As "Transaction Type ID",
       Rct.Org_Id As "Operating Unit ID",
       Ooha.Attribute1 As "Transaction Description"
From   Ra_Customer_Trx Rct,
       Oe_Order_Headers_All Ooha,
       Oe_Order_Lines_All Oola
Where  1=1 --Rct.Interface_Header_Context = 'ORDER ENTRY'
And    Rct.Interface_Header_Attribute6 = Oola.Line_Id
And    Ooha.Header_Id = Oola.Header_Id
   AND EXISTS
 (SELECT 'N'
          FROM ra_customer_trx rctb, ra_customer_trx_lines rctl
         WHERE rctb.customer_trx_id = rctl.customer_trx_id
           AND rct.customer_trx_id = rctb.customer_trx_id
           AND rctl.interface_line_context = 'ORDER ENTRY')
) atd,
(Select (Select Rb.Name
        From   Ra_Batches Rb
        Where  Rb.Batch_Id = Rct.Batch_Id
       ) As "Batch Name",
       (Select Rb.Comments
        From   Ra_Batches Rb
        Where  Rb.Batch_Id = Rct.Batch_Id
       ) As "Batch Comments",
       Rct.Customer_Trx_Id As "Transaction ID",
       Rct.Trx_Number As "Transaction Number",
       rct.Batch_Source_Id As "Transaction Source ID",
       Rct.Cust_Trx_Type_Id As "Transaction Type ID",
       Rct.Trx_Date As "Transaction Date",
       Rct.Attribute2 As "Rental Contract Number",
       rct.Attribute3 As "Contract Start Date",
       Rct.Attribute4 As "Contract End Date",
       Rct.Attribute5 As "Effective Start Date",
       Rct.Attribute6 As "Effective End Date",
       rct.Attribute7 As "Transaction Currency",
       Rct.Attribute8 As "Exchange Rate",
       Rct.Bill_To_Customer_Id As "Bill-To Customer Account ID",
       Rct.Bill_To_Site_Use_Id As "Bill-To Location ID",
       Rct.Ship_To_Site_Use_Id As "Ship-To Location ID",
       rct.Term_Id As "Payment Terms ID",
       (Select Description
        From   Ra_Rules Rr
        Where  Rr.Rule_Id = Rct.Invoicing_Rule_Id
       ) As "Invoicing Rule",
       Rct.Comments As "Comments",
       Rct.Complete_Flag As "Complete Flag",
       Rct.Org_Id As "Operating Unit ID",
       Rct.Set_Of_Books_Id As "Ledger ID"
From   Ra_Customer_Trx Rct
) ath,
(Select Cust.Cust_Account_Id  As Customer_Account_ID,
       Cust_Party.Party_Name As Customer_Name,
       Cust_Party.Known_As   As Alias,
	     Cust.Account_Number   As Customer_Account_Number,
	     Cust.Attribute1       As "Tonnage Invoice Split",
	     CUST.ATTRIBUTE2       AS "CRU Services",
	     Cust.Attribute3       As "ACD Pricing Rule",
	     Cust.Attribute4       As "Tonnage items",
	     Cust.Attribute5       As "Ramp Pricing Rule",
	     CUST.ATTRIBUTE6       AS "Ramp ACD Airline",
	     Cust.Attribute7       As "ACD Invoice Split",
	     Cust.Attribute8       As "Witholding Tax",
	     Cust.Attribute9       As "OBC X-ray",
	     Cust.Attribute10      As "Billing Currency"
From   Hz_Cust_Accounts Cust,
       Hz_Parties Cust_Party
Where  Cust.Party_Id = Cust_Party.Party_Id
) cha,
(SELECT Cash_Receipt_History_Id             AS "Cash Receipt History ID",
  Applied_Customer_Trx_Id                  AS "Transaction ID", -- "Applied Customer Transaction ID",
  Apply_Date                               AS "Apply Date",
  Amount_Applied                           AS "Applied in HKD", --Amount Applied in HKD
  Trans_To_Receipt_Rate                    AS "Exchange Rate",
  NVL(amount_applied_from, amount_applied) AS "Applied in Receipt Currency", --Amount Applied in Receipt Currency
  (select cust.account_number from ra_customer_trx_all trx, Hz_Cust_Accounts cust 
  where 1=1
  and a.Applied_Customer_Trx_Id = trx.customer_trx_id
  and trx.bill_to_customer_id = cust.cust_account_id) AS "TRX Customer Account Number"
FROM Ar_Receivable_Applications a
WHERE 1=1
and Application_Type = 'CASH'
AND Status             = 'APP'
) ra,
(Select Arc.Name As "Receipt Method",
       Acrh.Cash_Receipt_History_Id As "Cash Receipt History ID",
       Acr.Receipt_Number As "Receipt Number",
       Acr.Currency_Code As "Receipt Currency",
       Acrh.Amount As "Amount in Receipt Currency", --Receipt Amount in Receipt Currency
       Acrh.Acctd_Amount As "Amount in HKD", -- Receipt Amount in HKD
       Acr.Exchange_Rate As "Exchange Rate",
       Acr.Receipt_Date As "Receipt Date",
       Acr.Pay_From_Customer As "Customer Account ID"
From   Ar_Cash_Receipts Acr,
       Ar_Cash_Receipt_History Acrh,
       Ar_Receipt_Classes Arc
Where  Acr.Cash_Receipt_Id = Acrh.Cash_Receipt_Id
And    Acr.Receipt_Method_Id = Arc.Receipt_Class_Id
And    Acrh.Current_Record_Flag = 'Y'
AND    Acrh.Status <> 'REVERSED'
) ri,
(Select Cust_Trx_Type_Id As "Transaction Type ID",
       name as "Tranaction Type"
from   RA_CUST_TRX_TYPES
) tt
where
2=2 and
ath."Transaction ID"=atd."Transaction ID"(+) and
ath."Transaction Type ID"=tt."Transaction Type ID" and
cha.customer_account_id=ri."Customer Account ID" and
ra."Transaction ID"=ath."Transaction ID"(+) and
ri."Cash Receipt History ID"=ra."Cash Receipt History ID"(+)