ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 8E2FF36EDE9A79D2E0530100007F1FF2 AR Transaction Type Y select rctta.name value, nvl(flvv.meaning,rctta.type)||': '||haouv.name description from hr_all_organization_units_vl haouv, ra_cust_trx_types_all rctta, fnd_lookup_values_vl flvv where (:$flex$.operating_unit is null or xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y') and haouv.organization_id=rctta.org_id and rctta.type=flvv.lookup_code(+) and flvv.lookup_type(+)='INV/CM/ADJ' and flvv.view_application_id(+)=222 and flvv.security_group_id(+)=0 order by rctta.name, haouv.name US 8E2FF36EDEA679D2E0530100007F1FF2 Yes Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual US EDC3A7401FA24BF4E05362FB09059065Y 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(+) andy_test.sh US HACTL AR Transaction Listings 1=1 2=2 1 10 2=2 ath."Transaction Number" >= :p_trx_number_low LOV Oracle AR_INVOICE_NUMBER Y Y select ct.trx_number id, ct.trx_number value, null description from ra_customer_trx ct where ct.customer_trx_id in ( select max(ct2.customer_trx_id) from ra_customer_trx ct2 where ct.trx_number=ct2.trx_number) order by ct.trx_number,ct.customer_trx_id US Transaction Number Low 2 20 2=2 ath."Transaction Number" <= :p_trx_number_high LOV Oracle AR_INVOICE_NUMBER Y Y select ct.trx_number id, ct.trx_number value, null description from ra_customer_trx ct where ct.customer_trx_id in ( select max(ct2.customer_trx_id) from ra_customer_trx ct2 where ct.trx_number=ct2.trx_number) order by ct.trx_number,ct.customer_trx_id :$flex$.Transaction_Number_Low US Transaction Number High 3 30 2=2 tt."Tranaction Type"=:trx_type LOV AR Transaction Type 8E2FF36EDE9A79D2E0530100007F1FF2 Y select rctta.name value, nvl(flvv.meaning,rctta.type)||': '||haouv.name description from hr_all_organization_units_vl haouv, ra_cust_trx_types_all rctta, fnd_lookup_values_vl flvv where (:$flex$.operating_unit is null or xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y') and haouv.organization_id=rctta.org_id and rctta.type=flvv.lookup_code(+) and flvv.lookup_type(+)='INV/CM/ADJ' and flvv.view_application_id(+)=222 and flvv.security_group_id(+)=0 order by rctta.name, haouv.name US Transaction Type 4 40 2=2 atd2."GL Date" >= trunc(:p_gl_date_from) Date US GL Date From GL Date From 5 50 2=2 atd2."GL Date" < trunc(:p_gl_date_to)+1 Date last_day(:$flex$.Gl_Date_From) US GL Date To GL Date To 6 60 2=2 ath."Complete Flag"=:p_complete_flag LOV Yes 8E2FF36EDEA679D2E0530100007F1FF2 Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual US Complete Flag