Oracle apps r12 invoiced quantity variance with the ordered quantity OM: Over Shipment Tolerance

Oracle apps r12   invoiced quantity variance with the ordered quantity

Invoice Interface code looks at the tolerances for lines over shipped

profile stores OM: Over Shipment Tolerance

If we over shipped then we will see invoiced quantity as (ordered quantity + (ordered quantity * ship tolerance above)).
For example:
OM: Over Shipment Tolerance is 50 %
Ordered quantity = 10
Shipped Quantity = 15
Invoice Quantity = (10 + (10 * .50)) = 15

One more example
OM: Over Shipment Tolerance is 0 % (please find the below screenshot)
Ordered quantity = 1 and 1
Shipped Quantity = 8 and 4
Invoice Quantity =(ordered quantity + (ordered quantity * ship tolerance above)).

Line 11= (1 + (1 * 0) = 1

Line 12= (1 + (1 * 0) = 1

Oracle BI Publisher - Leading zeros truncated for excel reports - format numbers as text


   
 
Oracle BI Publisher - Leading zeros truncated for excel reports - format numbers as text
When you want to display some data which has leading zeros in EXCEL output you will not get the desired output. But in PDF it will come as what you are expecting. This is not with the issue of your data. This is due to the unique nature of the EXCEL cell format. When you are trying to put a text data in a cell without making any change to cell format it will treat as the number and it will truncate all leading zeros and all trailing zeros after '.' . So what you have to do is to convert that data into a format that EXCEL can treat as text.  
1. In the rtf template, add two spaces before the column.
2. Use the following xml tag
<fo:bidi-override direction=”ltr” unicode-bidi=”bidi-override”> <?format-number:INVOICE_NUMBER;’09999999′?> </fo:bidi-override>” 
3. Instead of the using the tag < ? INVOICE_NUMBER? >
use  =”<?INVOICE_NUMBER?>”. 
Excel treat value as character and doesn’t trim the leading zeors of value.

How to Find the Link Between Transaction Number and Receipt Number in AR

How to Find the Link Between Transaction Number and Receipt Number in AR

 

ELECT  ACR.RECEIPT_NUMBER RECEIPT_NO,RCT.TRX_NUMBER INVOICE_NO
FROM    AR_RECEIVABLE_APPLICATIONS_ALL ARA,
        AR_CASH_RECEIPTS_ALL           ACR,
        RA_CUSTOMER_TRX_ALL            RCT
WHERE   ARA.STATUS='APP'
  AND   ARA.CASH_RECEIPT_ID=ACR.CASH_RECEIPT_ID
  AND   ARA.APPLIED_CUSTOMER_TRX_ID=RCT.CUSTOMER_TRX_ID
 
 
Select distinct acra.cash_receipt_id , araa.applied_payment_schedule_id ,
acra.amount ,
acra.status , acra.org_id ,acra.receipt_number , aps.status apsstatus ,
aps.customer_id ,aps.customer_trx_id ,
aps.amount_applied ,aps.amount_due_original ,aps.amount_due_remaining
,aps.customer_site_use_id ,
aps.cash_receipt_status_last from ar_cash_receipts_all acra ,
ar_payment_schedules_all aps ,
ar_receivable_applications_all araa, ra_customer_trx_all rcta ,
ra_customer_trx_lines_all rctla
where aps.cash_receipt_id = acra.cash_receipt_id
and acra.customer_site_use_id = aps.customer_site_use_id
andaraa.cash_receipt_id = acra.cash_receipt_id
*--and acra.status = 'APP'*
and araa.applied_payment_schedule_id is not null
and aps.class = 'PMT'
and acra.status != 'REV'
 

 

Sales order query ,Order_amount , invoice_amount oracle apps r12 sql query to find order amount

select ooh.ORDER_NUMBER     "Order Number"
      , ra.CUSTOMER_NUMBER  "Customer Number"
      , ra.CUSTOMER_NAME    "Customer Name"
      ,wnd.SOURCE_HEADER_ID "Delivery Number"
      ,ool.LINE_ID          "Order LineID"
      ,ool.LINE_NUMBER      "Order Line Number"
      ,rct.TRX_NUMBER       "Invoice Number"
      ,rct.TRX_DATE         "Invoice Date"
      ,ool.ORDERED_QUANTITY "Ordered Quantity"
      ,ool.SHIPPED_QUANTITY "Shipped Quantity"
     ,rctl.QUANTITY_INVOICED "Invoiced Quantity"
     ,ool.ORDERED_QUANTITY*ool.UNIT_SELLING_PRICE "Ordered Amount"
     ,rctl.QUANTITY_INVOICED*rctl.UNIT_SELLING_PRICE "Invoiced Amount"
     ,(select sum(l.ORDERED_QUANTITY*l.UNIT_SELLING_PRICE)
      from oe_order_headers_all h
          ,oe_order_lines_all l
      where h.HEADER_ID in l.HEADER_ID
         and h.HEADER_ID=ooh.HEADER_ID
      group by h.HEADER_ID ) "Sub Total"
     from oe_order_headers_all ooh
    ,oe_order_lines_all ool
    ,ra_customers ra
    ,wsh_new_deliveries wnd
    ,ra_customer_trx_all rct
    ,ra_customer_trx_lines_all rctl
where 1=1
   and ooh.ORDER_NUMBER     = '66415'
   and ooh.HEADER_ID        = ool.HEADER_ID
   and  ra.CUSTOMER_ID      = ooh.SOLD_TO_ORG_ID
   and ooh.HEADER_ID        = wnd.SOURCE_HEADER_ID(+)
   --and ra.CUSTOMER_ID       = wnd.CUSTOMER_ID
   and to_char(ooh.ORDER_NUMBER)     = rct.CT_REFERENCE
   and rct.SOLD_TO_CUSTOMER_ID       =  ra.CUSTOMER_ID
   and rct.CUSTOMER_TRX_ID           = rctl.CUSTOMER_TRX_ID
   and rctl.SHIP_TO_CUSTOMER_ID      = ra.CUSTOMER_ID
group by ooh.HEADER_ID
      ,ooh.ORDER_NUMBER    
      , ra.CUSTOMER_NUMBER  
      , ra.CUSTOMER_NAME    
      ,wnd.SOURCE_HEADER_ID 
      ,ool.LINE_ID          
      ,ool.LINE_NUMBER      
      ,rct.TRX_NUMBER       
      ,rct.TRX_DATE         
      ,ool.ORDERED_QUANTITY 
      ,ool.SHIPPED_QUANTITY 
     ,rctl.QUANTITY_INVOICED 
     ,ool.ORDERED_QUANTITY*ool.UNIT_SELLING_PRICE 
     ,rctl.QUANTITY_INVOICED*rctl.UNIT_SELLING_PRICE

ORA-01732: data manipulation operation not legal on this view

ORA-01732: data manipulation operation not legal on this view

Question: What causes the ORA-01732 error?

ORA-01732: data manipulation operation not legal on this view.

Answer:  The oerr utility shows this for the ORA-01732 error, showing that you cannot issue any update DML against a view:

ORA-01732: data manipulation operation not legal on this view

Cause: An attempt was made to use an UPDATE, INSERT, or DELETE statement on a view that contains expressions or functions or was derived from more than one table. If a join operation was used to create the view or the view contains virtual columns derived from functions or expressions, then the view may only be queried.

Action: UPDATE, INSERT, or DELETE rows in the base tables instead and restrict the operations on the view to queries.

Instead, you need to find the base tables from the view and issue the updates against the base tables.  You cannot update a view except with INSTEAD OF triggers if the defining query of the view contains one of the following constructs:

A set operator
A DISTINCT operator
An aggregate or analytic function
A GROUP BY, ORDER BY, MODEL, CONNECT BY, or START WITH clause
A collection expression in a SELECT list
A subquery in a SELECT list
A subquery designated WITH READ ONLY
Joins, with some exceptions . . .
As we see, the ORA-01732 can be fixed by issuing the DML against the base tables.

        
Oracle Training from Don Burleson

The best on site "Oracle training classes" are just a phone call away! You can get personalized Oracle training by Donald Burleson, right at your shop!


Bored at Home? Here's a Massive List of Museums, Zoos, and Theme Parks Offering Virtual Tours

The effects of social distancing have left many feeling, well, a little stir-crazy. Though we all try to stay busy by keeping active and working from home during the COVID-19 outbreak, it's easy to fall into a rut. Luckily, there's more than one way to stave off boredom while staying indoors. Instead of binging the best of what Netflix has to offer for the second time, it's time to broaden your horizons with some virtual tours.
"Walk" through some of the world's most prestigious cultural institutions, like The Met and The American Museum of Natural History, then go for a "ride" on Disney's new Frozen rollercoaster with the kids. And regardless of your age, we think everyone will enjoy some live footage of pandas playing at the zoo!

Museums

  • The Louvre: You don't have to book a ticket to Paris to check out some of the famous pieces in the world's largest art museum. The Louvre has free online tours of three famous exhibits, including Egyptian Antiquities.
  • Solomon R. Guggenheim Museum: The works of Pablo Picasso, Piet MondrianJeff Koons, and Franz Marc are just some of the 625 artists whose work are a part of the Guggenheim's Collection Online.
  • Smithsonian National Museum of Natural History: Move at your own pace through the 360-degree room-by-room tour of every exhibit in the museum.
  • Van Gogh Museum: You can get up close and personal with the impressionist painter's most famous work thanks to Google Arts & Culture.
  • Getty Museum: Los Angeles's premiere gallery has two virtual tours, including "Eat, Drink, and Be Merry," which is a closer look at food in the Middle Ages and Renaissance.
  • The Vatican Museum: The Sistine Chapel, St. Peter's Basilica, and Raphael's Room, are just some of the sites you can see on the Vatican's virtual tour.
  • Thyssen-Bornemisza Museum: Madrid's must-see art museum has the works of some of the continent's most celebrated artists like Rembrandt and Dali available online.
  • Georgia O’Keeffe Museum: Six virtual exhibits are available online from this museum named for the "Mother of American modernism."
  • National Museum of Anthropology, Mexico City: Dive into the pre-Hispanic history of Mexico with 23 exhibit rooms full of Mayan artifacts.
  • British Museum, London: The Rosetta Stone and Egyptian mummies are just a couple of things that you're able to see on a virtual tour of the museum.
  • NASA: Both Virginia's Langley Research Center and Ohio's Glenn Research Center offer online tours for free. Also, you can try some "augmented reality experiences" via The Space Center Houston's app.
  • National Women's History Museum: Have a late International Women's Day celebration with online exhibits and oral histories from the Virginia museum.
  • Metropolitan Museum of Art: Though the Met Gala was cancelled this year, you can still have a peak at the The Costume Institute Conversation Lab, which is one of the institution's 26 online exhibits.
  • High Museum of Art, Atlanta: This museum's popular online exhibits include "Civil Rights Photography" — photos that capture moments of social protest like the Freedom Rides and Rosa Park's arrest.
  • Detroit Institute of Arts: Mexican art icon Frida Kahlo is the focal point of two of the four available online exhibits.
  • Rijksmuseum, Amsterdam: The Golden Age of Dutch art is highlighted in this museum which includes the work of Vermeer and Rembrandt.
  • National Museum of the United States Air Force: You can't take a ride in Franklin D. Roosevelt's presidential airplane, but you can check it out, in addition to other military weapons and aircraft, online in the Air Force's official museum.
  • MoMA (The Museum of Modern Art): New York's extensive collection is available for view online.
  • Museum of Fine Arts, Boston: The 16 virtual exhibits include a special section on 21st Century Designer Fashion.

    Zoos and Aquariums

    • The Cincinnati Zoo: Check in around 3 p.m., because that's the time the Zoo holds a daily Home Safari on its Facebook Live Feed.
    • Atlanta Zoo: The Georgia zoo keeps a "Panda Cam" livestream on its website.
    • Georgia Aquarium: Sea-dwellers like African penguins and Beluga Whales are the stars of this aquarium's live cam.
    • Houston Zoo: There are plenty of different animals you can check in on with this zoo's live cam, but we highly recommend watching the playful elephants.
    • The Shedd Aquarium: This Chicago aquarium shares some pretty adorable behind-the-scenes footage of their residents on Facebook.
    • San Diego Zoo: With what may be the most live cam options, this zoo lets you switch between koalas, polar bears, and tigers in one sitting.
    • Monterey Bay Aquarium: It can be Shark Week every week thanks to live online footage of Monterey Bay's Habitat exhibit.
    • National Aquarium: Walk through tropical waters to the icy tundra in this floor-by-floor tour of the famous, Baltimore-based aquarium.

      Theme Parks

      LINK BETWEEN po_headers_all AND oe_order_headers_all ,link between sales order and purchase order in Oracle apps r12

      --LINK BETWEEN po_headers_all AND oe_order_headers_all
      --link BETWEEN po AND so IN oracle apps LINK BETWEEN po_headers_all AND oe_order_headers_all
      OE_ORDER_LINES_ALL.SOURCE_DOCUMENT_LINE_ID==PO_REQUISITION_LINES_ALL.REQUISITION_LINE_ID
      OE_ORDER_LINES_ALL.ORIG_SYS_DOCUMENT_REF=PO_REQUISITION_HEADERS_ALL.SEGMENT1(REQUISITION number)

      select OOH.ORDER_NUMBER SALES_ORDER,
        PRH.SEGMENT1 REQ,
        PH.SEGMENT1 'PO Number'
      from OE_ORDER_HEADERS_ALL OOH,
        PO_REQUISITION_HEADERS_ALL PRH,
        PO_REQUISITION_LINES_ALL PRL,
        PO_REQ_DISTRIBUTIONS_ALL RD,
        PO_HEADERS_ALL PH,
        PO_DISTRIBUTIONS_ALL D
      where 1                       = 1
      and PRH.REQUISITION_HEADER_ID = PRL.REQUISITION_HEADER_ID
      and PRH.REQUISITION_HEADER_ID = OOH.SOURCE_DOCUMENT_ID
      and ORDER_NUMBER              = '344444'  --Sales Order Number
      and PRH.SEGMENT1              = 'WE23333' -- Requisition Number
      and RD.REQUISITION_LINE_ID    = PRL.REQUISITION_LINE_ID
      and D.REQ_DISTRIBUTION_ID     = RD.DISTRIBUTION_ID
      and PH.PO_HEADER_ID           = D.PO_HEADER_ID link between PO REQUISITION and OE_ORDER_HEADERS_ALL
      select OOH.ORDER_NUMBER SALES_ORDER,
        PRH.SEGMENT1 REQUISATION
      from OE_ORDER_HEADERS_ALL OOH,
        PO_REQUISITION_HEADERS_ALL PRH,
        PO_REQUISITION_LINES_ALL PRL
      where 1                       = 1
      and PRH.REQUISITION_HEADER_ID = PRL.REQUISITION_HEADER_ID
      and PRH.REQUISITION_HEADER_ID = OOH.SOURCE_DOCUMENT_ID
      and ORDER_NUMBER              = '344444'  --Sales Order Number
      and PRH.SEGMENT1              = 'WE23333' -- Requisition Number
      and PRH.TYPE_LOOKUP_CODE      = 'INTERNAL'

      --JOIN BETWEEN rcv_shipment_lines AND oe_order_lines_all
      select OOH.ORDER_NUMBER SALES_ORDER,
        PRH.SEGMENT1 REQUISATION,
        PH.SEGMENT1 PO_NUMBER,
        RSH.RECEIPT_NUM RECEIPT_NUM
      from OE_ORDER_HEADERS_ALL OOH,
        PO_REQUISITION_HEADERS_ALL PRH,
        PO_REQUISITION_LINES_ALL PRL,
        PO_REQ_DISTRIBUTIONS_ALL RD,
        PO_HEADERS_ALL PH,
        PO_DISTRIBUTIONS_ALL D,
        RCV_SHIPMENT_LINES RSL,
        RCV_SHIPMENT_HEADERS RSH
      where 1                       = 1
      and PRH.REQUISITION_HEADER_ID = PRL.REQUISITION_HEADER_ID
      and PRH.REQUISITION_HEADER_ID = OOH.SOURCE_DOCUMENT_ID
      and ORDER_NUMBER              = '344444'  --Sales Order Number
      and PRH.SEGMENT1              = 'WE23333' -- Requisition Number
      and RD.REQUISITION_LINE_ID    = PRL.REQUISITION_LINE_ID
      and D.REQ_DISTRIBUTION_ID     = RD.DISTRIBUTION_ID
      and PH.PO_HEADER_ID           = D.PO_HEADER_ID
      and PH.PO_HEADER_ID           =RSL.PO_HEADER_ID
      and RSL.SHIPMENT_HEADER_ID    =RSH.SHIPMENT_HEADER_ID

      Query to retrieve Transaction & Receipt details.

      Query to retrieve Transaction & Receipt details.

      SELECT rcta.trx_number transaction_number ,
        haou1.name operating_unit ,
        rctta.name transaction_type ,
        rcta.trx_date transaction_date ,
        hca.account_name bill_to_customer ,
        hl.address1
        ||' ,'
        ||hl.city
        ||' ,'
        ||hl.state
        ||' ,'
        ||hl.postal_code
        ||' ,'
        ||hl.country bill_to_address,
        gsob.name ledger ,
        rcta.interface_header_attribute1 order_number ,
        rcta.interface_header_attribute2 order_type ,
        rcta.interface_header_context invoice_source ,
        CASE rcta.complete_flag
          WHEN 'Y'
          THEN 'YES'
          WHEN 'N'
          THEN 'NO'
        END transaction_complete ,
        rtt.name payment_terms ,
        haou.name ship_from_organization ,
        rctla.line_number ,
        rctla.line_type ,
        rcta.ship_via ,
        msib.segment1 item ,
        rctla.description item_description ,
        rctla.quantity_ordered ,
        rctla.quantity_invoiced ,
        rctla.quantity_credited ,
        rctla.unit_standard_price item_standard_price ,
        rctla.unit_selling_price item_selling_price ,
        fct.name currency ,
        (rctla.unit_selling_price      * rctla.quantity_invoiced) line_total ,
        (SELECT SUM(unit_selling_price * quantity_invoiced)
        FROM ra_customer_trx_lines_all
        WHERE rcta.customer_trx_id=customer_trx_id
        ) invoice_total,
        NVL(acra.receipt_number,'No receipt created for this transaction') receipt_number,
        (SELECT name
        FROM ar_receipt_methods
        WHERE acra.receipt_method_id=receipt_method_id
        ) receipt_method ,
        (SELECT meaning
        FROM fnd_lookup_values
        WHERE acra.type =lookup_code
        AND lookup_type ='CASH_RECEIPT_TYPE'
        ) receipt_type ,
        acra.receipt_date ,
        acra.amount received_amount ,
        (SELECT meaning
        FROM fnd_lookup_values
        WHERE lookup_code=araa.status
        AND lookup_type  ='PAYMENT_TYPE'
        ) receipt_status ,
        acra.comments ,
        hp1.party_name bank_name ,
        hp.party_name branch_name ,
        cba.bank_account_name ,
        cba.bank_account_num
      FROM ra_customer_trx_all rcta ,
        hz_cust_accounts_all hca ,
        hz_cust_acct_sites_all hcasa ,
        hz_party_sites hps ,
        hz_locations hl ,
        ra_terms_tl rtt ,
        gl_sets_of_books gsob ,
        ra_customer_trx_lines_all rctla ,
        ra_cust_trx_types_all rctta ,
        fnd_currencies_tl fct ,
        hr_all_organization_units haou ,
        hr_all_organization_units haou1 ,
        mtl_system_items_b msib ,
        ar_receivable_applications_all araa ,
        ar_cash_receipts_all acra ,
        hz_parties hp1,
        hz_parties hp ,
        ce_bank_accounts cba
      WHERE rcta.term_id                              =rtt.term_id      --to get payment terms
      AND rcta.invoice_currency_code                  =fct.currency_code--to get currency
      AND fct.language                                ='US'
      AND rcta.org_id                                 =haou1.organization_id--to get operating unit
      AND rcta.sold_to_customer_id                    =hca.cust_account_id  --TO GET CUSTOMER DETAIL
      AND hca.cust_account_id                         =hcasa.cust_account_id--to get account site
      AND rcta.org_id                                 =hcasa.org_id
      AND hcasa.party_site_id                         =hps.party_site_id        --to get account site
      AND hps.location_id                             =hl.location_id           --to get site location
      AND rctla.inventory_item_id                     =msib.inventory_item_id(+)--to get item
      AND rctla.org_id                                =msib.organization_id(+)
      AND rcta.set_of_books_id                        =gsob.set_of_books_id   --for set of books
      AND to_number(rcta.interface_header_attribute10)=haou.organization_id(+)--ship from organization(warehouse)
      AND rcta.cust_trx_type_id                       =rctta.cust_trx_type_id --transaction type
      AND rcta.org_id                                 =rctta.org_id
      AND rcta.customer_trx_id                        =rctla.customer_trx_id          --for line details
      AND rcta.customer_trx_id                        =araa.applied_customer_trx_id(+)--for receipt application
      AND rcta.trx_number                             = :transaction_number           --'10037542'
      AND araa.cash_receipt_id                        =acra.cash_receipt_id(+)        --to get receipt details
      AND acra.remit_bank_acct_use_id                 =cba.bank_account_id(+)         --for beneficiary bank account
      AND cba.bank_branch_id                          =hp.party_id(+)                 --for branch name
      AND cba.bank_id                                 =hp1.party_id(+);               --for bank name

      Query to Find Receipt Class and its GL Combination Query

      Query to Find Receipt Class and its GL Combinition Query

      SELECT ARC.NAME ReceiptClass,
             ARC.CREATION_METHOD_CODE Creation_Mehthod,
             DECODE(ARC.REMIT_METHOD_CODE,
                    'STANDARD',
                    'Standard',
                    NULL,
                    'No Remittance') Remittance_Method,
             DECODE(ARC.CLEAR_FLAG,
                    'Y',
                    'By Matching',
                    NULL,
                    'Directly',
                    'Directly') Clearance_Method,
             ARM.NAME Receipt_Mehtod_Name,
             ARM.PRINTED_NAME Printed_Name,
             ARM.START_DATE Effective_Date,
             ARM.END_DATE End_Date,
             HOU.NAME Operating_Unit,
             CBB.bank_name Bank_Name,
             CBB.bank_branch_name Branch_Name,
             CBA.BANK_ACCOUNT_NAME Bank_Account_Name,
             CBA.CURRENCY_CODE Currency,
             RM.MIN_RECEIPT_AMOUNT Min_Receipt_Amount,
             RM.RISK_ELIMINATION_DAYS Risk_Elimination_Days,
             RM.CLEARING_DAYS Clearing_Days,
             RM.OVERRIDE_REMIT_ACCOUNT_FLAG Override_Bank,
             RM.START_DATE Effective_Days,
             RM.END_DATE End_Days,
             RM.PRIMARY_FLAG Primary_Flag,
             (select gcc.concatenated_segments
                from gl_code_combinations_kfv gcc
               where gcc.code_combination_id = RM.CASH_CCID) Cash,
             (select gcc.concatenated_segments
                from gl_code_combinations_kfv gcc
               where gcc.code_combination_id = RM.RECEIPT_CLEARING_CCID) Receipt_Confirmation,
             (select gcc.concatenated_segments
                from gl_code_combinations_kfv gcc
               where gcc.code_combination_id = RM.REMITTANCE_CCID) Remittance,
             (select gcc.concatenated_segments
                from gl_code_combinations_kfv gcc
               where gcc.code_combination_id = RM.FACTOR_CCID) Factoring,
             (select gcc.concatenated_segments
                from gl_code_combinations_kfv gcc
               where gcc.code_combination_id = RM.SHORT_TERM_DEBT_CCID) Short_Term_Debt,
             (select gcc.concatenated_segments
                from gl_code_combinations_kfv gcc
               where gcc.code_combination_id = RM.BANK_CHARGES_CCID) Bank_Charges,
             (select gcc.concatenated_segments
                from gl_code_combinations_kfv gcc
               where gcc.code_combination_id = RM.UNAPPLIED_CCID) Unapplied_receipts,
             (select gcc.concatenated_segments
                from gl_code_combinations_kfv gcc
               where gcc.code_combination_id = RM.UNIDENTIFIED_CCID) Unidentified_Receipts,
             (select gcc.concatenated_segments
                from gl_code_combinations_kfv gcc
               where gcc.code_combination_id = RM.ON_ACCOUNT_CCID) On_Account,
             arc.receipt_class_id,
             hou.organization_id
        FROM AR_RECEIPT_CLASSES             ARC,
             AR_RECEIPT_METHODS             ARM,
             HR_ALL_ORGANIZATION_UNITS      HOU,
             CE_BANK_ACCT_USES_ALL          CBAU,
             CE_BANK_ACCOUNTS               CBA,
             CE_BANK_BRANCHES_V             CBB,
             AR_RECEIPT_METHOD_ACCOUNTS_ALL RM

       WHERE ARM.RECEIPT_CLASS_ID = ARC.RECEIPT_CLASS_ID
         AND ARM.RECEIPT_METHOD_ID = RM.RECEIPT_METHOD_ID
         AND RM.ORG_ID = HOU.ORGANIZATION_ID
         AND CBAU.BANK_ACCT_USE_ID = RM.REMIT_BANK_ACCT_USE_ID
         AND CBAU.BANK_ACCOUNT_ID = CBA.BANK_ACCOUNT_ID
         AND CBB.branch_party_id = CBA.BANK_BRANCH_ID
         AND HOU.NAME = &p_org_name
         AND RM.END_DATE IS NULL
         AND ARM.END_DATE  is null

       order by hou.name, arc.name