HACTL AR Transaction Listings

select distinct
cha.customer_account_number,
cha.customer_name,
ath."Batch Name" batch_name,
tt."Tranaction Type" tranaction_type,
ath."Transaction Number" transaction_number,
ath."Transaction Date" transaction_date,
atd2."GL Date",
ati.due_date,
atd1."Transaction Description" transaction_description,
gac."Natural Account" natural_account,
gac."Business Segment" business_segment,
gac."Natural Account Description" natural_account_description,
atl."UOM Code" uom_code,
atl."Unit Selling Price" unit_selling_price,
atl."Line Amount" line_amount,
atd2."Distribution Amount" distribution_amount
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 AND--
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')
) atd1,
(select rctlgd.customer_trx_id as "Transaction ID",
       rctlgd.customer_trx_line_id as "Transaction Line ID",
       rctlgd.cust_trx_line_gl_dist_id as "Distribution Line ID",
       rctlgd.account_class as "Distribution Class",
       rctlgd.code_combination_id as "GL Account Combination ID",
       rctlgd.amount as "Distribution Amount",
       rctlgd.percent as "%",
       rctlgd.gl_date as "GL Date"
from   ra_cust_trx_line_gl_dist rctlgd
where account_set_flag = 'N'
) atd2,
(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 customer_id,
       trx_id,
       trx_number, 
       trx_class, 
       trx_date, 
       acctd_amount_due_original, 
       DECODE(trx_class, 'Deposit', DECODE(acctd_amount_due_remaining, 0, -commitment_balance, acctd_amount_due_remaining), acctd_amount_due_remaining) balance,
       due_date,
       payment_schedule_id,
       DECODE(trx_class, 
              'Deposit', DECODE(installment_status, 
                                'Open', 'Open', 
                                DECODE(SIGN(commitment_balance), 
                                       1, 'Open', 'Closed')), installment_status) installment_status,
       DECODE(installment_status, 'Closed', DECODE(DECODE(trx_class, 
              'Deposit', DECODE(installment_status, 
                                'Open', 'Open', 
                                DECODE(SIGN(commitment_balance), 
                                       1, 'Open', 'Closed')), installment_status), 'Open', TO_DATE('47121231', 'YYYYMMDD'), actual_date_closed), actual_date_closed) actual_date_closed
  FROM (SELECT ps.customer_id,
               COALESCE(rct.customer_trx_id, acr.cash_receipt_id) trx_id,
               COALESCE(rct.trx_number, acr.receipt_number) trx_number,
               ps.class trx_class,
               COALESCE(rct.trx_date, acr.receipt_date) trx_date,
               ps.amount_due_original * nvl(ps.exchange_rate, 1) acctd_amount_due_original,
               ps.acctd_amount_due_remaining,
               DECODE(ps.class, 'DEP', NVL((SELECT SUM(dep_line.revenue_amount )
                                              FROM ra_customer_trx_lines dep_line 
                                             WHERE rct.customer_trx_id = dep_line.customer_trx_id), 0) + 
                                       NVL(AR_INVOICE_SQL_FUNC_PUB.get_com_total_activity( rct.customer_trx_id, rct_type.type, rct.customer_trx_id ),0) + 
                                       NVL(AR_INVOICE_SQL_FUNC_PUB.get_com_amt_uninvoiced( rct.customer_trx_id ),0)) commitment_balance,
               ps.due_date,
               l.meaning installment_status,
               ps.actual_date_closed,
               ps.payment_schedule_id
          FROM ar_payment_schedules ps, 
               Ra_Customer_Trx_All Rct,  --restored on 20141208 by IBM
               ra_cust_trx_types rct_type,
               ar_cash_receipts acr,
               fnd_lookup_values l
         WHERE ps.customer_trx_id = rct.customer_trx_id(+)
           AND rct.cust_trx_type_id = rct_type.cust_trx_type_id(+)
           AND rct.org_id = rct_type.org_id(+)
           AND ps.cash_receipt_id = acr.cash_receipt_id(+)
           AND rct_type.post_to_gl = 'Y' --restored on 20141208 by IBM
           AND ps.status = l.lookup_code
           AND l.lookup_type = 'PAYMENT_SCHEDULE_STATUS'
           AND l.language = 'US'
)
) ati,
(select *
from
(
Select rctl.customer_trx_id as "Transaction ID",
       rctl.customer_trx_line_id as "Transaction Line ID",
       rctl.line_number as "Line Number",
       rctl.inventory_item_id as "Item ID",
       rctl.description as "Description",
       rctl.uom_code as "UOM Code",
       nvl(rctl.quantity_invoiced, rctl.quantity_credited) as "Quantity",
       rctl.unit_selling_price as "Unit Selling Price",
       rctl.extended_amount as "Line Amount",
       (Select name
        From   Ra_Rules Rr
        Where  Rr.Rule_Id = Rctl.Accounting_Rule_Id
       ) As "Accounting Rule",
       rctl.accounting_rule_duration as "Duration",
       rctl.rule_start_date as "Start Date",
       rctl.interface_line_context as "context",
       null as "Order Type",
       null as "Sales Order Number",
       null as "Sales Order Line ID",
       null as "Price Adjustment ID",
       rctl.attribute1 as "Billing Frequency",
       rctl.attribute2 as "Location",
       rctl.attribute3 as "Room Size",
       rctl.attribute4 as "SRF No.",
       rctl.attribute5 as "AWB",
       rctl.attribute6 as "FLT No.",
       rctl.attribute7 as "FLT Date",
       rctl.org_id as "Operating Unit ID",
       'N' "Modifier Flag"
from   ra_customer_trx_lines rctl
where  rctl.interface_line_attribute11 is null
)
union
(
Select rctl.customer_trx_id as "Transaction ID",
       rctl.customer_trx_line_id as "Transaction Line ID",
       rctl.line_number as "Line Number",
       rctl.inventory_item_id as "Item ID",
       rctl.description as "Description",
       rctl.uom_code as "UOM Code",
       nvl(rctl.quantity_invoiced, rctl.quantity_credited) as "Quantity",
       line_total.unit_selling_price as "Unit Selling Price",
       line_total.total as "Line Amount",
       (Select name
        From   Ra_Rules Rr
        Where  Rr.Rule_Id = Rctl.Accounting_Rule_Id
       ) As "Accounting Rule",
       rctl.accounting_rule_duration as "Duration",
       rctl.rule_start_date as "Start Date",
       rctl.interface_line_context as "Context",
       rctl.interface_line_attribute2 as "Order Type",
       rctl.interface_line_attribute1 as "Sales Order Number",
       to_number(rctl.interface_line_attribute6,'999999999999999999999999') as "Sales Order Line ID",
       rctl.interface_line_attribute11 as "Price Adjustment ID",
       rctl.attribute1 as "Billing Frequency",
       rctl.attribute2 as "Location",
       rctl.attribute3 as "Room Size",
       rctl.attribute4 as "SRF No.",
       rctl.attribute5 as "AWB",
       rctl.attribute6 as "FLT No.",
       rctl.attribute7 as "FLT Date",
       rctl.org_id as "Operating Unit ID",
decode(sign(line_total.line_count-1),1,'Y','N') "Modifier Flag"
from   ra_customer_trx_lines rctl,
       (select rctla.interface_line_attribute6, sum(rctla.unit_selling_price) unit_selling_price, sum(rctla.extended_amount) total,count(*) line_count
        from   ra_customer_trx_lines rctla
        group by rctla.interface_line_attribute6
       ) line_total
where  rctl.interface_line_attribute11 = 0
and    rctl.interface_line_attribute6 = line_total.interface_line_attribute6
)
) atl,
(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 Gcc.Code_Combination_Id As "GL Account Combination ID",
       Gcc.Segment1 As "Company",
       Gcc.Segment2 As "Natural Account",
       Ffvt.description as "Natural Account Description",
       Gcc.Segment3 As "Cost Centre",
       Gcc.Segment4 As "Intercompany",
       Gcc.Segment5 As "Business Segment",
       Gcc.Segment6 As "Project",
       Gcc.Segment7 As "Spare 1",
       Gcc.Segment8 As "Spare 2"
From   Gl_Code_Combinations gcc,
       Fnd_Flex_Values Ffv,
       Fnd_Flex_Values_Tl Ffvt
Where  Gcc.Segment2 = Ffv.Flex_Value
And    Ffv.Flex_Value_Id = Ffvt.Flex_Value_Id
) gac,
(Select Msib.Inventory_Item_Id As "Item ID",
       Msib.Segment1 As "Item Number",
       Msib.Description As "Item Description",
       Msib.Attribute_Category As "DFF Context Value",
       msib.attribute1 as "DFF1",
       Msib.Attribute2 As "DFF2",
       Msib.Attribute3 As "DFF3",
       Msib.Attribute4 As "DFF4",
       Msib.Attribute5 As "DFF5",
       msib.attribute6 as "DFF6",
       Msib.Attribute7 As "DFF7",
       Msib.Attribute8 As "DFF8",
       Msib.Sales_Account As "Sales Account ID",
       to_number(Hoi.Org_Information3, '99999') As "Operating Unit ID"
From   Mtl_System_Items_B Msib,
       Hr_Organization_Information Hoi
Where  Msib.Organization_Id = Hoi.Organization_Id
And    Hoi.Org_Information_Context = 'Accounting Information'
And    Hoi.Org_Information3 is not null
) im,
(Select Cust_Trx_Type_Id As "Transaction Type ID",
       name as "Tranaction Type"
from   RA_CUST_TRX_TYPES
) tt
where
2=2 and
atd2."GL Account Combination ID"=gac."GL Account Combination ID" and
ath."Transaction ID"=atd1."Transaction ID"(+) and
ath."Transaction ID"=atl."Transaction ID" and
ath."Transaction Type ID"=tt."Transaction Type ID" and
atl."Transaction ID"=atd2."Transaction ID" and
atl."Transaction Line ID"=atd2."Transaction Line ID" and
atl."Item ID"=im."Item ID"(+) and
atl."Operating Unit ID"=im."Operating Unit ID"(+) and
cha.customer_account_id=ath."Bill-To Customer Account ID" and
ath."Transaction ID"=ati.trx_id(+)