OM - AR Drill Down

SELECT   Rl.Customer_Trx_Line_Id,
         Rl.Customer_Trx_Id,
         Rl.Line_Number,
         rh.trx_number,
         Rl.Interface_Line_Attribute1,
         H.Order_Number,
         L.Line_Id,
         Rl.Sales_Order_Line,
         rl.quantity_ordered,
         rl.quantity_invoiced,
         Rl.Extended_Amount
  FROM   Ra_Customer_Trx_Lines_All Rl,
         RA_CUSTOMER_TRX_ALL rh,
         oe_order_lines_all l,
         oe_order_headers_all h
 WHERE       line_type = 'LINE'
         AND interface_line_context = 'ORDER ENTRY'
         AND h.header_id = l.header_id
         AND interface_line_attribute6 = TO_CHAR (l.line_id)
         AND Interface_Line_Attribute1 = TO_CHAR (H.Order_Number)
         AND rh.customer_trx_id = rl.customer_trx_id
         AND sales_order = h.order_number
         and h.order_number = 13001107374

GL-AR Drilldown Function with Exception Handling

An Code combination account for a month contains balance; Balance is further divided into many generals; a general header is made up of many general lines; A line is made up of several receivables lines


Create or replace function xyka_gl_ar(x_je_header_id number ,x_je_line_num number, x_code_combination_id number)
 return varchar2   Is
          x_ret varchar2(100);   
          err_code varchar2(200);  
          err_msg    varchar2(200);     
  begin
 
  SELECT   -- gjb.name Batch_name ,
--           gjh.NAME Journal_name ,
--           gjh.JE_CATEGORY,
--           gjh.Je_source,
--           gjl.ENTERED_DR,
--           gjl.ENTERED_CR,
--           fnd_flex_ext.get_segs ('SQLGL',
--                                  'GL#',
--                                  gjb.chart_of_accounts_id,
--                                  gjl.code_combination_id)
--              ACCOUNT,
--           xla_oa_functions_pkg.get_ccid_description (gjb.chart_of_accounts_id,
--                                                      gjl.code_combination_id)
--              account_desc,
--           ract.trx_number,
           (SELECT   max(PARTY_NAME)
              FROM   hz_cust_accounts hca, hz_parties hp
             WHERE   hca.party_id = hp.party_id
                     AND hca.cust_account_id = ract.bill_TO_CUSTOMER_ID)
              Customer
    into  x_ret         
    FROM   gl_je_batches gjb,
           gl_je_headers gjh,
           gl_je_lines gjl,
           gl_code_combinations gcc,
           gl_import_References gir,
           xla_ae_lines xal,
           xla_distribution_links xdl,
           ra_customer_trx_all ract,
           ra_customer_trx_lines_all ractl,
           ra_cust_trx_line_gl_dist_all rctlgdl
   WHERE       GJH.PERIOD_NAME = 'NOV-13'
          -- AND gjb.name = 'YKCOST A 3348139' 
           -- gjb.DATE_CREATED LIKE SYSDATE
           --and gcc.code_combination_id = 619725
           and gjh.je_header_id = x_je_header_id --1370490
           AND gjl.JE_LINE_NUM = x_je_line_num
           AND gjh.je_header_id = gjl.je_header_id
           AND gjb.je_batch_id = gjh.je_batch_id
           AND gjL.je_header_id = gir.je_header_id
           AND Gjl.JE_LINE_NUM = gir.JE_LINE_NUM
           AND gir.je_batch_id = gjh.je_batch_id
           AND gcc.code_combination_id = gjl.CODE_COMBINATION_ID
           and gcc.code_combination_id = x_code_combination_id
           AND gjh.Je_source = 'Receivables'
           AND gjh.JE_CATEGORY = 'Sales Invoices'
          -- AND gjh.Je_source = 'Receivables'
           AND xal.ae_header_id = xdl.ae_header_id
           AND xal.GL_SL_LINK_ID = gir.gl_sl_link_id                 --2621350
           AND xal.GL_SL_LINK_table = gir.gl_sl_link_table
           AND SOURCE_DISTRIBUTION_TYPE = 'RA_CUST_TRX_LINE_GL_DIST_ALL'
           AND xal.application_id = xdl.application_id
           AND xal.APPLICATION_ID = 222
           and gjh.LEDGER_ID = nvl(gjh.LEDGER_ID,gjh.LEDGER_ID)--:ledger_id --2021
           AND ractl.customer_trx_id = ract.customer_trx_id
           AND ractl.customer_trx_line_id = rctlgdl.customer_trx_line_id
           AND ract.customer_trx_id = rctlgdl.customer_trx_id
           AND xdl.source_distribution_id_num_1 =    rctlgdl.CUST_TRX_LINE_GL_DIST_ID
GROUP BY   gjb.name,
           gjh.NAME,
           gjh.JE_CATEGORY,
           gjh.Je_source,
           gjl.ENTERED_DR,
           gjl.ENTERED_CR,
           gjb.chart_of_accounts_id,
           gjl.code_combination_id,
           ract.trx_number,
           ract.bill_TO_CUSTOMER_ID;
          
    return(x_ret);
    Exception
         
         when Others then
          --return('An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM));  
           --raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
           err_code := SQLCODE;
           err_msg := SUBSTR(SQLERRM, 1, 200);
           return(err_msg);
    end;