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(+) |
