AR INVOICE QUERY

exec mo_global.init('AR');
/* Formatted on 2011/12/26 23:23 (Formatter Plus v4.8.8) */
SELECT rct.org_id, hou.NAME ou_name, rct.customer_trx_id, rct.trx_number,
       rct.old_trx_number, TRUNC (rct.trx_date) trx_date, ctl.line_number,
       ctl.customer_trx_line_id, lgd.amount, lgd.account_class,
       lgd.acctd_amount, al_class.meaning, lgd.al_class_meaning, gcc.concatenated_segments,
       ffv.description acct_desc, rctt.TYPE, rctt.NAME trx_type_name,
       rctt.description, rctt.post_to_gl, rctt.accounting_affect_flag,
       lgd.gl_date, rct.complete_flag, rctt.status, gcc.segment3 account_no,
       ffv.description account_des, rctt.end_date
  FROM ra_cust_trx_line_gl_dist_v lgd,
       gl_code_combinations_kfv gcc,
       fnd_flex_values_vl ffv,
       ra_customer_trx_all rct,
       apps.ra_cust_trx_types_all rctt,
       hr_operating_units hou,
       ra_customer_trx_lines_all ctl,
       ra_customer_trx_lines_all ctl_line,
       ar_lookups al_class
 WHERE lgd.code_combination_id = gcc.code_combination_id
   AND ffv.flex_value = gcc.segment3
   AND rct.customer_trx_id = lgd.customer_trx_id
   AND rctt.cust_trx_type_id(+) = rct.cust_trx_type_id
   AND hou.organization_id(+) = rct.org_id
   AND lgd.customer_trx_line_id = ctl.customer_trx_line_id(+)
   AND ctl.link_to_cust_trx_line_id = ctl_line.customer_trx_line_id(+)
   AND al_class.lookup_type = 'AUTOGL_TYPE'
     AND al_class.lookup_code = lgd.account_class
   AND TRUNC (rct.trx_date) BETWEEN :p_from_date AND :p_to_date
   --AND rct.customer_trx_id = 1082
   order by rct.org_id, hou.NAME , rct.customer_trx_ID


--------------------------  NEXT ------------------------------


/* Formatted on 12/26/2011 9:20:35 PM (QP5 v5.115.810.9015) */
  SELECT   rct.org_id,
           hou.NAME ou_name,
           rct.customer_trx_id,
           rct.trx_number,
           rct.old_trx_number,
           TRUNC (rct.trx_date) trx_date,
           ctl.line_number,
           ctl.customer_trx_line_id,
           lgd.amount,
           lgd.ACCOUNT_CLASS,
           lgd.acctd_amount,
           --         lgdv.AL_CLASS_MEANING,
           gcc.concatenated_segments,
           ffv.description acct_desc,
           --         rctt.TYPE,
           --         rctt.NAME trx_type_name,
           --         rctt.description,
           rctt.post_to_gl,
           rctt.accounting_affect_flag,
           lgd.GL_DATE,
           rct.COMPLETE_FLAG,
           rctt.status,
           gcc.SEGMENT3 ACCOUNT_NO,
           ffv.DESCRIPTION ACCOUNT_DES,
           rctt.end_date,
           AL_CLASS.MEANING,
            AL_TYPE.MEANING
    FROM   ra_cust_trx_line_gl_dist_all lgd,
           ra_customer_trx_lines_all ctl,
           ra_customer_trx_all rct,
           hr_operating_units hou,
           gl_code_combinations_kfv gcc,
           apps.ra_cust_trx_types_all rctt,
           fnd_flex_values_vl ffv,
           RA_CUSTOMER_TRX_LINES CTL_LINE,
           AR_LOOKUPS AL_CLASS,
           AR_LOOKUPS AL_TYPE,
           RA_RULES RR
   WHERE       lgd.customer_trx_line_id = ctl.customer_trx_line_id(+)
           AND rct.customer_trx_id(+) = ctl.customer_trx_id
           AND hou.organization_id(+) = rct.org_id
           AND gcc.code_combination_id = lgd.code_combination_id
           AND rctt.cust_trx_type_id(+) = rct.cust_trx_type_id
           AND CTL.LINK_TO_CUST_TRX_LINE_ID = CTL_LINE.CUSTOMER_TRX_LINE_ID(+)
           AND AL_CLASS.LOOKUP_TYPE = 'AUTOGL_TYPE'
           AND AL_CLASS.LOOKUP_CODE = LGD.ACCOUNT_CLASS
           AND AL_TYPE.LOOKUP_TYPE(+) = 'STD_LINE_TYPE'
           AND AL_TYPE.LOOKUP_CODE(+) = CTL.LINE_TYPE
           AND CTL.ACCOUNTING_RULE_ID = RR.RULE_ID(+)
           AND ffv.flex_value = gcc.segment3
           AND TRUNC (rct.trx_date) BETWEEN :p_from_date AND :p_to_date
ORDER BY   rct.org_id, hou.NAME,rct.trx_number