AR Customer Receipt Details

  SELECT cr.doc_sequence_value Receipt_number,

       TO_CHAR (cr.receipt_date, 'DD/MM/YYYY') "Receipt_Date",

       cr.receipt_number Cheque_Num,

       TO_CHAR (aps.due_date, 'DD/MM/YYYY') Cheque_Date,

       crh_current.acctd_amount Amount

  --   ,cr.CUSTOMER_SITE_USE_ID

FROM   ar_cash_receipts_all cr,

       ar_cash_receipt_history_all crh_current,

       ar_payment_schedules_all aps,

       hz_cust_site_uses_all hcsu,

       hz_cust_acct_sites_all hcas,

       hz_party_sites hps,

       hz_parties hp

 WHERE crh_current.cash_receipt_id = cr.cash_receipt_id

   AND crh_current.org_id = cr.org_id

   AND crh_current.current_record_flag = NVL ('Y', cr.receipt_number)

   AND crh_current.status IN ('CONFIRMED', 'REMITTED')

   AND cr.customer_site_use_id = hcsu.site_use_id

   AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id

   AND hcas.party_site_id = hps.party_site_id

   AND hp.party_id = hps.party_id

   AND hp.party_name = :p_customer_name

   AND aps.trx_number = cr.receipt_number

   and cr.org_id = nvl(:p_org_id,cr.org_id)

GL AR Aging Query

 -- Transaction balances with transaction types having open receivables set to ‘N’ and 
-- post to gl set to ‘Y’ would reflect in GL Account balance, but not in Aging, since the 
-- balances are not maintained in payment shcedules for these transactions
-- To identify the amount difference caused by these kind of transactions, please, use the 
-- below query 

select sum(acctd_amt_dr-acctd_amt_cr) gl_no_aging
from (
      select  c.code_combination_id,
              DECODE(gld.account_class,'REC',decode(sign(gld.acctd_amount), -1 ,0, gld.acctd_amount),
                                           'REV',decode(sign(gld.acctd_amount), -1, abs(gld.acctd_amount),0),
                                           'TAX',decode(sign(gld.acctd_amount), -1, abs(gld.acctd_amount),0),
                                           'ROUND',decode(sign(gld.acctd_amount), -1,abs(gld.acctd_amount),0),
                                           'UNEARN',decode(sign(gld.acctd_amount), -1, abs(gld.acctd_amount),0),
                                           'FREIGHT',decode(sign(gld.acctd_amount), -1, abs(gld.acctd_amount),0),
                                           'UNBILL',decode(sign(gld.acctd_amount), -1, abs(gld.acctd_amount),0),0) acctd_amt_dr,
            DECODE(gld.account_class, 'REC',decode(sign(gld.acctd_amount), -1 ,abs(gld.acctd_amount),0),
                                            'REV',decode(sign(gld.acctd_amount), -1, 0,gld.acctd_amount),
                                            'TAX',decode(sign(gld.acctd_amount), -1, 0,gld.acctd_amount),
                                            'ROUND',decode(sign(gld.acctd_amount), -1,0,gld.acctd_amount),
                                            'UNEARN',decode(sign(gld.acctd_amount), -1, 0,gld.acctd_amount),
                                            'FREIGHT',decode(sign(gld.acctd_amount), -1, 0,gld.acctd_amount),
                                            'UNBILL',decode(sign(gld.acctd_amount), -1, 0,gld.acctd_amount),0) acctd_amt_cr
      from ra_cust_trx_line_gl_dist_all gld
           ,ra_customer_trx_all ct
           ,ra_cust_trx_types_all ctt
           ,xla_distribution_links lk
           ,xla_ae_lines ae
           ,gl_code_combinations c
      where ct.cust_trx_type_id = ctt.cust_trx_type_id
      and   ct.customer_trx_id = gld.customer_trx_id
      and   gld.account_class = 'REC'
      and   gld.account_set_flag = 'N'
      and   gld.latest_rec_flag = 'Y'
      and   ctt.accounting_affect_flag = 'N'
      and   ctt.post_to_gl = 'Y'
      and   ct.org_id = ctt.org_id
      and   ct.org_id = gld.org_id
      and   ct.set_of_books_id = &ledger_id
      and   gld.gl_date <= to_date('&as_of_date','DD-MON-YYYY')
      and   gld.cust_trx_line_gl_dist_id = lk.source_distribution_id_num_1(+)
      and   lk.source_distribution_type(+)   = 'RA_CUST_TRX_LINE_GL_DIST_ALL'
      and   lk.application_id(+)             = 222
      and   ae.application_id(+)          = 222
      and   lk.ae_header_id                = ae.ae_header_id(+)
      and   lk.ae_line_num                 = ae.ae_line_num(+)
      and   decode(lk.accounting_line_code, '', 'Y',
                       'CM_EXCH_GAIN_LOSS', 'N',
                       'AUTO_GEN_GAIN_LOSS', 'N', 'Y') = 'Y' 
      and   decode(ae.ledger_id,'',decode(gld.posting_control_id,-3,-999999,gld.code_combination_id),gld.set_of_books_id,ae.code_combination_id,-999999)= c.code_combination_id
      and   c.code_combination_id = &code_combination_id
      );

Item- BOM with Routing Query

 ****************************************
Item BOM and Routing Query
***********************************************
SELECT ROWNUM "Sl NO", assembly_item_id,
       (SELECT hou.NAME
          FROM hr_organization_units hou
         WHERE hou.organization_id = bor.organization_id) "Organization",
       (SELECT msib.segment1
          FROM apps.mtl_system_items_b msib
         WHERE msib.inventory_item_id =
                                      bor.assembly_item_id
           AND msib.organization_id = 85) assembly_item_code,
       (SELECT msib.description
          FROM apps.mtl_system_items_b msib
         WHERE msib.inventory_item_id =
                               bor.assembly_item_id
           AND msib.organization_id = 85) assembly_item_description,
       bor.completion_subinventory, bor.completion_locator_id,
       bos.operation_seq_num, bso.operation_code, bd.department_code,
       bores.resource_seq_num, br.resource_code, br.unit_of_measure uom,
       bores.basis_type basis, bores.schedule_flag scheduling,
       (SELECT micv.category_concat_segs product_category
          FROM apps.mtl_item_categories_v micv
         WHERE micv.category_set_id = '1100000042'
           AND micv.organization_id = 85
           AND micv.inventory_item_id = bor.assembly_item_id
           AND ROWNUM <= 1) product_category,
       bos.disable_date
  FROM apps.bom_operational_routings bor,
       apps.bom_operation_sequences bos,
       apps.bom_standard_operations bso,
       apps.bom_departments bd,
       apps.bom_operation_resources bores,
       apps.bom_resources br
 WHERE bor.routing_sequence_id = bos.routing_sequence_id
   AND bos.standard_operation_id = bso.standard_operation_id(+)
   AND bos.department_id = bd.department_id
   AND bores.operation_sequence_id = bos.operation_sequence_id
   AND br.resource_id = bores.resource_id
   AND bor.assembly_item_id = 2110381;

OPM-SLA query

SELECT   period_name, gl_trans, acct_stat, event_type_code, event_desc,

         item_type, acct_class, co, loc, cc, acct, bl, futr, acct_desc,

         SUM (debit) dr, SUM (credit) cr

    FROM (SELECT /*+ full(xah) parallel (xah, 8) full (xal) parallel (xal, 16) full(gxah) parallel (gxah, 8) */

                 xah.period_name,

                 xah.gl_transfer_status_code gl_trans,

                 xah.accounting_entry_status_code acct_stat,

                 xah.event_type_code,

                 NVL (xet.description, xet.NAME) event_desc,

                 xal.accounting_class_code acct_class,

                 micv.category_concat_segs item_type,

                 gcc.segment1 co, gcc.segment2 loc, gcc.s! egment3 cc,

                 gcc.segment4 acct, gcc.segment5 bl, gcc.segment6 futr,

                 ffvv.description acct_desc,

                 NVL (xal.accounted_dr, xal.entered_dr) debit,

                 NVL (xal.accounted_cr, xal.entered_cr) credit

            FROM apps.xla_ae_headers xah,

                 apps.xla_ae_lines xal,

                 apps.gmf_xla_extract_headers gxah,

                 apps.xla_event_types_tl xet,

                 apps.gl_code_combinations gcc,

                 apps.fnd_flex_values_vl ffvv,

                 apps.fnd_flex_value_sets ffvs,

                 apps.mtl_system_items_b msib,

                 apps.mtl_item_categories_v micv

           WHERE xah.ae_header_id = xal.ae_header_id

             AND xah.event_id = gxah.event_id

             AND xah.event_type_code = xet.event_type_code

             AND xah.application_id = xet.application_id

             AND xal.code_combination_id = gcc.code_combination_id

             AND gcc.segment4 = ffvv.flex_value

             AND ffvv.flex_value_set_id = ffvs.flex_value_set_id

             AND ffvs.flex_value_set_name = 'BPIL_ACCOUNT'

             AND gxah.inventory_item_id = msib.inventory_item_id

             AND msib.organization_id = micv.organization_id

             AND msib.inventory_item_id = micv.inventory_item_id

             AND msib.organization_id = 102

             AND micv.category_set_name = 'Item GL Class'

             AND micv.category_concat_segs IN ('RM', 'PKG', 'INT', 'FG', 'BLK')

             AND gxah.reference_no <> 4

             AND xah.accounting_date BETWEEN '01-APR-2011' AND '30-APR-2011'

             AND xah.application_id = 555)

GROUP BY period_name,

         gl_trans,

         acct_stat,

         event_type_code,

         event_desc,

         item_type,

         acct_class,

         co,

         loc,

         cc,

         acct,

         bl,

         futr,

         acct_desc;

Cash Management Tables and Queries

   SELECT 
        DECODE(:P_ORG_CODE,'ALL','ALL',HAOU.NAME ) ORGANIZATION_NAME
        ,TRUNC(SYSDATE) SYSDATE1
        ,TO_CHAR(SYSDATE,'HH:MI:SS AM') SYSTIME1
      ,:P_FROM_DATE
       ,:P_TO_DATE
       ,BPIL_STATEMENT_LINE(CR.CASH_RECEIPT_ID) STATEMENT_LINE_ID
   --     ,CR.LAST_UPDATE_DATE
        ,DECODE (RT.SEGMENT1, 'W', 'WEST'
                            ,'C', 'CENTRAL'
                            ,'N', 'NORTH'
                            ,'S', 'SOUTH'
                            ,'E', 'EAST' ) REGION
        ,MP.ORGANIZATION_CODE
,        CASE HCAA.SALES_CHANNEL_CODE WHEN '0' THEN 'RETAIL'
                                     WHEN '2' THEN 'GENERAL INDUSTRIAL'
                                     WHEN '3' THEN 'AUTOMOTIVE'
                                     WHEN '4' THEN 'PROTECTON'
                                     WHEN '6' THEN 'POWDER COATING'
                                     ELSE NULL END "B_LINE"                  
        ,JRS. NAME SALES_REP_NAME
        ,HCA.ACCOUNT_NUMBER CUSTOMER_CODE
        ,HP.PARTY_NAME CUSTOMER_NAME
--        ,CON_REMM.CASH_RECEIPT_ID 
        ,CON_REMM.PR_DATE
        ,CR.RECEIPT_NUMBER PR_NUMBER
--        ,DECODE(CON_REMM.BATCH_ID,-1,NULL,CON_REMM.BATCH_ID) BATCH_ID 
        ,CON_REMM.PIS_DATE
        ,AB.NAME PIS_NUMBER
         ,CR.AMOUNT 
       --  ,RCL.STATUS
         ,CON_REMM.PR_DATE - CON_REMM.PIS_DATE DAY_DIFF_IN_PRS_AND_PIS_DATE
         ,TRUNC(RCL.TRX_DATE) CLEAR_DATE
         ,TRUNC(RCL.TRX_DATE) - CON_REMM.PIS_DATE DAY_DIFF_IN_PIS_AND_CLR_DATE
    FROM  
        (SELECT CASH_RECEIPT_ID,TRX_DATE,BATCH_ID FROM AR_CASH_RECEIPT_HISTORY_ALL A
             WHERE  status  ='CLEARED'
             AND NOT EXISTS  (SELECT 1 FROM AR_CASH_RECEIPT_HISTORY_ALL B
             WHERE B.CASH_RECEIPT_ID=A.CASH_RECEIPT_ID
             AND B.STATUS='REVERSED')
             AND A.CASH_RECEIPT_ID=230886--2308861
             ) RCL,  
        (SELECT 
            CON.CASH_RECEIPT_ID,
            CON.TRX_DATE PR_DATE,
            REMM.TRX_DATE PIS_DATE,
            NVL(REMM.BATCH_ID,-1) BATCH_ID
         FROM 
            (SELECT * FROM AR_CASH_RECEIPT_HISTORY_ALL A
             WHERE  STATUS='CONFIRMED'
             AND NOT EXISTS  (SELECT 1 FROM AR_CASH_RECEIPT_HISTORY_ALL B
             WHERE B.CASH_RECEIPT_ID=A.CASH_RECEIPT_ID
             AND B.STATUS='REVERSED' )
            AND A.CASH_RECEIPT_ID=230886--2308861
             ) CON,
          --
            (SELECT * FROM AR_CASH_RECEIPT_HISTORY_ALL A
             WHERE  STATUS='REMITTED'
             AND NOT EXISTS  (SELECT 1 FROM AR_CASH_RECEIPT_HISTORY_ALL B
             WHERE B.CASH_RECEIPT_ID=A.CASH_RECEIPT_ID
             AND B.STATUS='REVERSED' --)
             AND A.cash_receipt_id=230886)--2308861
             ) REMM
         WHERE CON.CASH_RECEIPT_ID=REMM.CASH_RECEIPT_ID(+)) CON_REMM       
       ,AR_BATCHES_ALL AB   
       ,AR_CASH_RECEIPTS_ALL CR
       ,HZ_CUST_SITE_USES_ALL HCSUA
       ,HZ_CUST_ACCT_SITES_ALL HCASA  
       ,HZ_CUST_ACCOUNTS HCA 
       ,HZ_PARTIES HP 
       ,RA_TERRITORIES RT
       ,JTF_RS_SALESREPS JRS
       ,HZ_CUST_ACCOUNTS_ALL HCAA
       ,AR_RECEIPT_METHODS ARM
       ,MTL_PARAMETERS MP
       ,HR_ALL_ORGANIZATION_UNITS HAOU
--     ,AR_PAYMENT_SCHEDULES_ALL APSA 
   WHERE  -- CON_REMM.BATCH_ID=AB.BATCH_ID(+)    
          RCL.BATCH_ID=AB.BATCH_ID(+)
        AND CON_REMM.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID
        AND CR.CUSTOMER_SITE_USE_ID = HCSUA.SITE_USE_ID  -- IF WE DO NOT ADD OUTER JOIN , ROWS WILL BE REDUCED TO 131 
        AND HCSUA.CUST_ACCT_SITE_ID  = HCASA.CUST_ACCT_SITE_ID -- IF WE DO NOT ADD OUTER JOIN , ROWS WILL BE REDUCED TO 131
        AND HCASA.CUST_ACCOUNT_ID = HCA.CUST_ACCOUNT_ID  
        AND HCA.PARTY_ID = HP.PARTY_ID
        AND HCSUA.TERRITORY_ID = RT.TERRITORY_ID
        AND HCSUA.PRIMARY_SALESREP_ID = JRS.SALESREP_ID
        AND HCAA.CUST_ACCOUNT_ID = HCASA.CUST_ACCOUNT_ID
        AND CR.RECEIPT_METHOD_ID = ARM.RECEIPT_METHOD_ID  
        AND UPPER(ARM.NAME) LIKE '%CHEQUE%'       -- THIS LINE WILL REDUCE THE LINES TO 58 FROM 131 
        AND HCSUA.WAREHOUSE_ID = MP.ORGANIZATION_ID
        AND MP.ORGANIZATION_ID = HAOU.ORGANIZATION_ID
        AND RCL.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID
        AND CR.CASH_RECEIPT_ID =230886--2308861
        AND CON_REMM.CASH_RECEIPT_ID = RCL.CASH_RECEIPT_ID
        AND  TRUNC(RCL.TRX_DATE) >= :P_FROM_DATE
        AND  TRUNC(RCL.TRX_DATE) <= :P_TO_DATE
       AND MP.ORGANIZATION_CODE = DECODE(:P_ORG_CODE,'ALL',MP.ORGANIZATION_CODE,:P_ORG_CODE)-- ON ORG_CODE = 031 LINES WILL REDUCE TO 56    
     ORDER BY MP.ORGANIZATION_CODE ASC, CR.LAST_UPDATE_DATE DESC;
   
   
   
 FROM  ce_bank_branches_v abb,
ce_bank_accts_gt_v aba,
ce_lookups l1,
ce_lookups l2,
--ce_statement_reconciliations sr,
ce_statement_reconcils_all sr,
ce_statement_lines sl,
ce_statement_headers sh
WHERE l1.lookup_type = 'BANK_TRX_TYPE'
AND l1.lookup_code = sl.trx_type
AND l2.lookup_type = 'STATEMENT_LINE_STATUS'
AND l2.lookup_code = sl.status
AND abb.branch_party_id = aba.bank_branch_id
AND aba.bank_account_id = NVL(:P_BANK_ACCOUNT_ID,aba.bank_account_id)
AND aba.bank_account_id = sh.bank_account_id
AND sr.status_flag(+) = 'M'
AND sr.current_record_flag(+) = 'Y'
AND sr.statement_line_id(+) = sl.statement_line_id
AND sl.statement_header_id  =  sh.statement_header_id

  SELECT ACA.ATTRIBUTE10 ORG,
      CBA.BANK_ACCOUNT_NAME,
      GCC.SEGMENT1||'.'||GCC.SEGMENT2||'.'||GCC.SEGMENT3||'.'||GCC.SEGMENT4||'.'||GCC.SEGMENT5||'.'||GCC.SEGMENT6 GL_CODE,
      ACA.CHECK_NUMBER,
      NVL(ACA.CHECK_VOUCHER_NUM, TO_CHAR(ACA.DOC_SEQUENCE_VALUE)) VCH,
      ACA.CHECK_DATE,
      ACA.AMOUNT,
      CLEARED_DATE, 
      ACA.STATUS_LOOKUP_CODE
 FROM AP_CHECKS_ALL ACA,
      CE_BANK_ACCT_USES_ALL CBAUA,
      CE_BANK_ACCOUNTS CBA,
      GL_CODE_COMBINATIONS GCC
 WHERE ACA.CE_BANK_ACCT_USE_ID = CBAUA.BANK_ACCT_USE_ID
  AND CBAUA.BANK_ACCOUNT_ID = CBA.BANK_ACCOUNT_ID
  AND CBA.CASH_CLEARING_CCID = GCC.CODE_COMBINATION_ID
  and cba.BANK_ACCOUNT_NAME = NVL (:BANK_ACCOUNT_NAME, cba.BANK_ACCOUNT_NAME)
  and  ACA.CHECK_DATE BETWEEN '01-DEC-2013' AND '24-MAR-2014'
  --and aca.STATUS_LOOKUP_CODE ='RECONCILED'
  ORDER BY
      ACA.CHECK_ID;

=========================================
  
CREATE OR REPLACE FUNCTION  BPIL_STATEMENT_LINE(P_RECEIPT_ID IN NUMBER)
RETURN NUMBER
IS
L_LINE_ID  NUMBER;
BEGIN
   BEGIN
    SELECT  CSL.STATEMENT_LINE_ID
     INTO L_LINE_ID
            FROM  AR_CASH_RECEIPTS_ALL ACR,  
                  CE_BANK_ACCOUNTS CBA,
                  CE_BANK_ACCT_USES_ALL CBAUA,  
                  CE_STATEMENT_HEADERS  CSA,
                  CE_STATEMENT_LINES CSL
         WHERE  CBAUA.BANK_ACCT_USE_ID = ACR.REMIT_BANK_ACCT_USE_ID
         AND CBAUA.BANK_ACCOUNT_ID = CBA.BANK_ACCOUNT_ID
         AND ACR.CASH_RECEIPT_ID=P_RECEIPT_ID--230886 
         AND CSA.STATEMENT_HEADER_ID =CSL.STATEMENT_HEADER_ID
         AND CSA.BANK_ACCOUNT_ID =  CBAUA.BANK_ACCOUNT_ID
         AND CSL.BANK_TRX_NUMBER = ACR.RECEIPT_NUMBER;   
   EXCEPTION
     WHEN NO_DATA_FOUND THEN
      RETURN NULL;
     WHEN OTHERS THEN
      RETURN NULL;
    END;
   
  
 RETURN  L_LINE_ID;
END;
   
  
 RETURN  L_LINE_ID;
END;
         
  ==============================================================

  Joins between Cash Management Tables

================================================       
    FROM ce_bank_accounts cba,
          ce_bank_acct_uses_ou remit_bank,
          zx_rates_b vat,
          zx_accounts accounts,
          hz_cust_accounts cust,
          hz_parties party,
          ar_receipt_methods rec_method,
          ar_receipt_classes rc,
          hz_cust_site_uses_all site_uses,
          ar_lookups crh_note_status,
          ar_lookups l_rev_cat,
          ar_lookups l_rev_reason,
          ar_lookups l_ref_type,
          gl_daily_conversion_types gl_dct,
          ar_cash_receipt_history_all crh_rem,
          ar_batches_all rem_bat,
          ar_receivables_trx_all rec_trx,
          ar_distribution_sets_all dist_set,
          ar_payment_schedules_all ps,
          ar_cash_receipt_history_all crh_current,
          ar_batches_all bat,
          ar_batches_all bat_br,
          ar_cash_receipts cr,
          ar_cash_receipt_history_all crh_first_posted,
          hz_parties notesbranchparty,
          hz_parties notesbankparty,
          hz_relationships notesbrrel,
          ce_bank_branches_v bb,
          iby_ext_bank_accounts_v eba
    WHERE cr.pay_from_customer = cust.cust_account_id(+)
      AND cust.party_id = party.party_id(+)
      AND crh_note_status.lookup_type(+) = 'AR_NOTE_STATUS'
      AND crh_note_status.lookup_code(+) = crh_current.note_status
      AND remit_bank.bank_acct_use_id(+) = cr.remit_bank_acct_use_id
      AND remit_bank.org_id(+) = cr.org_id
      AND vat.tax_rate_id(+) = cr.vat_tax_id
      AND accounts.tax_account_entity_id(+) = cr.vat_tax_id
      AND accounts.tax_account_entity_code(+) = 'RATES'
      AND cr.receipt_method_id = rec_method.receipt_method_id
      AND rec_method.receipt_class_id = rc.receipt_class_id
      AND cr.customer_site_use_id = site_uses.site_use_id(+)
      AND cr.org_id = site_uses.org_id(+)
      AND cr.receivables_trx_id = rec_trx.receivables_trx_id(+)
      AND cr.org_id = rec_trx.org_id(+)
      AND cr.distribution_set_id = dist_set.distribution_set_id(+)
      AND cr.org_id = dist_set.org_id(+)
      AND l_rev_cat.lookup_type(+) = 'REVERSAL_CATEGORY_TYPE'
      AND l_rev_cat.lookup_code(+) = cr.reversal_category
      AND l_rev_reason.lookup_type(+) = 'CKAJST_REASON'
      AND l_rev_reason.lookup_code(+) = cr.reversal_reason_code
      AND l_ref_type.lookup_code(+) = cr.reference_type
      AND l_ref_type.lookup_type(+) = 'CB_REFERENCE_TYPE'
      AND gl_dct.conversion_type(+) = cr.exchange_rate_type
      AND crh_rem.cash_receipt_id(+) = cr.cash_receipt_id
      AND crh_rem.org_id(+) = cr.org_id
      AND NOT EXISTS (
             SELECT /*+ INDEX(CRH3 AR_CASH_RECEIPT_HISTORY_N1) */
                    cash_receipt_history_id
               FROM ar_cash_receipt_history_all crh3
              WHERE crh3.status = 'REMITTED'
                AND crh3.cash_receipt_id = cr.cash_receipt_id
                AND crh3.cash_receipt_history_id <
                                               crh_rem.cash_receipt_history_id)
      AND crh_rem.status(+) = 'REMITTED'
      AND crh_rem.batch_id = rem_bat.batch_id(+)
      AND crh_rem.org_id = rem_bat.org_id(+)
      AND rem_bat.TYPE(+) = 'REMITTANCE'
      AND ps.cash_receipt_id(+) = cr.cash_receipt_id
      AND ps.org_id(+) = cr.org_id
      AND crh_current.cash_receipt_id = cr.cash_receipt_id
      AND crh_current.org_id = cr.org_id
      AND crh_current.current_record_flag = NVL ('Y', cr.receipt_number)
      AND crh_first_posted.batch_id = bat.batch_id(+)
      AND crh_first_posted.org_id = bat.org_id(+)
      AND bat.TYPE(+) = 'MANUAL'
      AND crh_first_posted.cash_receipt_id(+) = cr.cash_receipt_id
      AND crh_first_posted.org_id(+) = cr.org_id
      AND crh_first_posted.first_posted_record_flag(+) = 'Y'
      AND crh_first_posted.batch_id = bat_br.batch_id(+)
      AND crh_first_posted.org_id = bat_br.org_id(+)
      AND bat_br.TYPE(+) = 'BR'
      AND notesbranchparty.party_id(+) = cr.issuer_bank_branch_id
      AND notesbrrel.object_id = notesbankparty.party_id(+)
      AND notesbrrel.subject_id(+) = notesbranchparty.party_id
      AND notesbrrel.relationship_type(+) = 'BANK_AND_BRANCH'
      AND notesbrrel.relationship_code(+) = 'BRANCH_OF'
      AND notesbrrel.subject_table_name(+) = 'HZ_PARTIES'
      AND notesbrrel.subject_type(+) = 'ORGANIZATION'
      AND notesbrrel.object_table_name(+) = 'HZ_PARTIES'
      AND notesbrrel.object_type(+) = 'ORGANIZATION'
      AND remit_bank.bank_account_id = cba.bank_account_id(+)
      AND bb.branch_party_id(+) = cba.bank_branch_id
      AND eba.ext_bank_account_id(+) = cr.customer_bank_account_id
      AND cr.org_id = accounts.internal_organization_id(+)   /* bug 6804246 */
  
  select gcc.segment1,gcc.segment2,gcc.segment3,gcc.segment4,gcc.segment5,gcc.segment6,acrh.*
   from xla_ae_headers xah,
        xla_ae_lines xal,
        gl_code_combinations gcc,
        ar_cash_receipt_history_all acrh             
  where xah.ae_header_id = xal.ae_header_id
  and xah.event_id =acrh.event_id
  and acrh.account_code_combination_id=gcc.code_combination_id
  and xal.code_combination_id = gcc.code_combination_id
 -- and  gcc.code_combination_id= 769593
  and acrh.cash_receipt_id =1111645
  
   
  
SELECT *
  FROM xla_ae_headers xah, xla_ae_lines xal
 --   gl_code_combinations gcc
WHERE  xah.ae_header_id = xal.ae_header_id
   AND xal.accounting_class_code IN ('BANK_CHG', 'UNAPP')
   AND xah.je_category_name = 'Receipts'
   -- and xal.code_combination_id = gcc.code_combination_id
  -- AND xal.code_combination_id = 8221
   AND xah.application_id = 222
   AND xah.application_id = xal.application_id
   AND xah.period_name IN ('JAN-13-14','FEB-13-14','MAR-13-14','APR-13-14',
                           'MAY-13-14','JUN-13-14','JUL-13-14','AUG-13-14',
                           'SEP-13-14','OCT-13-14','NOV-14-14','DEC-13-14')
   AND xal.code_combination_id IN (7346, 8257, 8251, 7595, 8221, 8215);

AP-GL-XLA link and different query

 SELECT

  GJH.DESCRIPTION JV_HEADER_DESCRIPTION,

  GJH.NAME JV_NAME,

  GJH.JE_CATEGORY,

  GJH.JE_SOURCE,

  GJH.PERIOD_NAME,

  NVL(XAL.ACCOUNTED_CR,0) GL_CR,

  NVL(XAL.ACCOUNTED_DR,0) GL_DR,

  GJL.DESCRIPTION JV_LINE_DESCRIPTION,

  XAH.EVENT_TYPE_CODE,

  XAH.DESCRIPTION SLA_DESCRIPTION,

  XAL.AE_LINE_NUM,

  XAL.ACCOUNTING_DATE GL_DATE,

  ASUP.VENDOR_NAME,

  TO_CHAR(ACA.CHECK_NUMBER),

  ACA.CHECK_DATE,

  ACA.DOC_SEQUENCE_VALUE VOUCHER_NUMBER,

  ACA.CREATION_DATE VOUCHER_DATE,

--  DECODE (XAH.EVENT_TYPE_CODE,'PAYMENT CANCELLED',AMOUNT* NVL(EXCHANGE_RATE,1),'REFUND RECORDED',XAL.ACCOUNTED_DR,

--   0)  RECEIPT,

--  DECODE (XAH.EVENT_TYPE_CODE,'PAYMENT CREATED',AMOUNT* NVL(EXCHANGE_RATE,1),0) PAYMENT

  DECODE(XAL.ACCOUNTED_CR,NULL,XAL.ACCOUNTED_DR,0)  RECEIPT,

  DECODE(XAL.ACCOUNTED_DR,NULL,XAL.ACCOUNTED_CR,0) PAYMENT

FROM

  XLA_AE_HEADERS XAH,

  XLA_AE_LINES XAL,

  GL_JE_LINES GJL,

  GL_IMPORT_REFERENCES GIR,

  GL_JE_HEADERS GJH,

  GL_CODE_COMBINATIONS GCC,

  AP_SUPPLIERS ASUP,

  AP_CHECKS_ALL ACA

WHERE

  XAH.AE_HEADER_ID=XAL.AE_HEADER_ID AND

  GJL.JE_LINE_NUM = GIR.JE_LINE_NUM AND

  GJL.JE_HEADER_ID = GIR.JE_HEADER_ID AND

  GIR.GL_SL_LINK_TABLE = XAL.GL_SL_LINK_TABLE AND

  GIR.GL_SL_LINK_ID = XAL.GL_SL_LINK_ID AND

  GJL.JE_HEADER_ID=GJH.JE_HEADER_ID  AND

  GJL.CODE_COMBINATION_ID=GCC.CODE_COMBINATION_ID AND

  ASUP.VENDOR_ID(+)=XAL.PARTY_ID AND

  ACA.DOC_SEQUENCE_ID(+)=XAH.DOC_SEQUENCE_ID AND

  ACA.DOC_SEQUENCE_VALUE(+)=XAH.DOC_SEQUENCE_VALUE AND

  GCC.SEGMENT5=NVL(:P_ACC_NUM,GCC.SEGMENT5)  AND

  TRUNC(GJH.DEFAULT_EFFECTIVE_DATE) BETWEEN NVL(:P_FROM_DATE,TRUNC(GJH.DEFAULT_EFFECTIVE_DATE)) AND NVL(:P_TO_DATE,TRUNC(GJH.DEFAULT_EFFECTIVE_DATE)) AND

  GJH.STATUS='P' AND

  GJH.JE_SOURCE='Payables'

UNION ALL

------ DATA FROM CASH MANAGEMENT --------------------------------

SELECT

  GJH.DESCRIPTION JV_HEADER_DESCRIPTION,

  GJH.NAME JV_NAME,

  GJH.JE_CATEGORY,

  GJH.JE_SOURCE,

  GJH.PERIOD_NAME,

  NVL(XAL.ACCOUNTED_CR,0) GL_CR,

  NVL(XAL.ACCOUNTED_DR,0) GL_DR,

  GJL.DESCRIPTION JV_LINE_DESCRIPTION,

  XAH.EVENT_TYPE_CODE,

  XAH.DESCRIPTION SLA_DESCRIPTION,

  XAL.AE_LINE_NUM,

  XAL.ACCOUNTING_DATE GL_DATE,

  '' VENDOR_NAME,

  '' CHECK_NUMBER,

  NULL CHECK_DATE,

  NULL VOUCHER_NUMBER,

  NULL VOUCHER_DATE,

  DECODE(XAL.ACCOUNTED_CR,NULL,XAL.ACCOUNTED_DR,0)  RECEIPT,

  DECODE(XAL.ACCOUNTED_DR,NULL,XAL.ACCOUNTED_CR,0) PAYMENT

FROM

  XLA_AE_HEADERS XAH,

  XLA_AE_LINES XAL,

  GL_JE_LINES GJL,

  GL_IMPORT_REFERENCES GIR,

  GL_JE_HEADERS GJH,

  GL_CODE_COMBINATIONS GCC

WHERE

  XAH.AE_HEADER_ID=XAL.AE_HEADER_ID AND

  GJL.JE_LINE_NUM = GIR.JE_LINE_NUM AND

  GJL.JE_HEADER_ID = GIR.JE_HEADER_ID AND

  GIR.GL_SL_LINK_TABLE = XAL.GL_SL_LINK_TABLE AND

  GIR.GL_SL_LINK_ID = XAL.GL_SL_LINK_ID AND

  GJL.JE_HEADER_ID=GJH.JE_HEADER_ID  AND

  GJL.CODE_COMBINATION_ID=GCC.CODE_COMBINATION_ID AND

  GCC.SEGMENT5=NVL(:P_ACC_NUM,GCC.SEGMENT5)  AND

  TRUNC(GJH.DEFAULT_EFFECTIVE_DATE) BETWEEN NVL(:P_FROM_DATE,TRUNC(GJH.DEFAULT_EFFECTIVE_DATE)) AND NVL(:P_TO_DATE,TRUNC(GJH.DEFAULT_EFFECTIVE_DATE)) AND

 -- GJH.PERIOD_NAME IN ('APR-11-12','MAY-11-12','JUN-11-12','JUL-11-12') AND

  GJH.STATUS='P' AND

  GJH.JE_SOURCE='Cash Management' AND

  GJH.JE_CATEGORY='Bank Transfers'

UNION ALL

-------------------Data from Receivable --------------------------------

SELECT GJH.DESCRIPTION JV_HEADER_DESCRIPTION,

      GJH.NAME JV_NAME,

      GJH.JE_CATEGORY,

      GJH.JE_SOURCE,

      GJH.PERIOD_NAME,

      NVL(XAL.ACCOUNTED_CR,0) GL_CR,

      NVL(XAL.ACCOUNTED_DR,0) GL_DR,

      GJL.DESCRIPTION JV_LINE_DESCRIPTION,

      XAH.EVENT_TYPE_CODE,

      XAH.DESCRIPTION SLA_DESCRIPTION,

      XAL.AE_LINE_NUM,

      XAL.ACCOUNTING_DATE GL_DATE,

      (SELECT AC.CUSTOMER_NAME

      FROM AR_CUSTOMERS AC WHERE AC.CUSTOMER_ID=XAL.PARTY_ID) CUSTOMER_NAME,

      (SELECT ACR.RECEIPT_NUMBER FROM AR_CASH_RECEIPTS_ALL ACR

      WHERE ACR.DOC_SEQUENCE_ID=XAH.DOC_SEQUENCE_ID AND

      ACR.DOC_SEQUENCE_VALUE=XAH.DOC_SEQUENCE_VALUE) RECEIPT_NUMBER,

      (SELECT ACR.RECEIPT_DATE FROM AR_CASH_RECEIPTS_ALL ACR

      WHERE ACR.DOC_SEQUENCE_ID=XAH.DOC_SEQUENCE_ID AND

      ACR.DOC_SEQUENCE_VALUE=XAH.DOC_SEQUENCE_VALUE

      ) RECEIPT_DATE,

      (SELECT ACR.DOC_SEQUENCE_VALUE

      FROM AR_CASH_RECEIPTS_ALL ACR

      WHERE ACR.DOC_SEQUENCE_ID=XAH.DOC_SEQUENCE_ID AND

      ACR.DOC_SEQUENCE_VALUE=XAH.DOC_SEQUENCE_VALUE) VOUCHER_NUMBER,

      (SELECT ACR.CREATION_DATE

      FROM AR_CASH_RECEIPTS_ALL ACR

      WHERE ACR.DOC_SEQUENCE_ID=XAH.DOC_SEQUENCE_ID AND

      ACR.DOC_SEQUENCE_VALUE=XAH.DOC_SEQUENCE_VALUE)  VOUCHER_DATE,

      DECODE(XAL.ACCOUNTED_CR,NULL,XAL.ACCOUNTED_DR,0)  RECEIPT,

      DECODE(XAL.ACCOUNTED_DR,NULL,XAL.ACCOUNTED_CR,0) PAYMENT

  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_AE_HEADERS XAH,

       XLA.XLA_TRANSACTION_ENTITIES XTE

WHERE GJB.JE_BATCH_ID = GJH.JE_BATCH_ID

   AND GJH.JE_HEADER_ID = GJL.JE_HEADER_ID

   AND GJL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID

   AND GJL.JE_HEADER_ID = GIR.JE_HEADER_ID

   AND GJL.JE_LINE_NUM = GIR.JE_LINE_NUM

   AND GIR.GL_SL_LINK_ID = XAL.GL_SL_LINK_ID

   AND GIR.GL_SL_LINK_TABLE = XAL.GL_SL_LINK_TABLE

   AND XAL.AE_HEADER_ID = XAH.AE_HEADER_ID

   AND XTE.APPLICATION_ID = XAH.APPLICATION_ID

   AND XTE.ENTITY_ID = XAH.ENTITY_ID

   AND GJL.STATUS = 'P'

   AND GCC.SEGMENT5 = NVL (:P_ACC_NUM, GCC.SEGMENT5)

   AND TRUNC (GJH.DEFAULT_EFFECTIVE_DATE)

          BETWEEN NVL (:P_FROM_DATE, TRUNC (GJH.DEFAULT_EFFECTIVE_DATE))

              AND NVL (:P_TO_DATE, TRUNC (GJH.DEFAULT_EFFECTIVE_DATE))

  AND GJH.JE_SOURCE = 'Receivables'

  UNION ALL

 ---------------- Manual -----------------------

SELECT GJH.DESCRIPTION JV_HEADER_DESCRIPTION,

       GJH.NAME JV_NAME,

       GJH.JE_CATEGORY,

       GJH.JE_SOURCE,

       GJH.PERIOD_NAME,

       NVL(GJL.ACCOUNTED_DR,0) ACCOUNTED_DR,

       NVL(GJL.ACCOUNTED_CR,0) ACCOUNTED_CR,

       gjl.description jv_line_description,

        '' EVENT_TYPE_CODE,

        '' SLA_DESCRIPTION,

        NULL AE_LINE_NUM,

        GJH.DEFAULT_EFFECTIVE_DATE GL_DATE,

        '' VENDOR_NAME,

        '' CHECK_NUMBER,

        NULL CHECK_DATE,

        NULL VOUCHER_NUMBER,

        NULL VOUCHER_DATE,

        NVL(GJL.ACCOUNTED_DR,0) RECEIPT,

        NVL(GJL.ACCOUNTED_CR,0) PAYMENT

 FROM GL_JE_BATCHES GJB,

      GL_JE_HEADERS GJH,

      GL_JE_LINES GJL,

      GL_CODE_COMBINATIONS GCC

 WHERE GJB.JE_BATCH_ID = GJH.JE_BATCH_ID

   AND GJH.JE_HEADER_ID = GJL.JE_HEADER_ID

   AND GJL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID

   AND GJL.STATUS = 'P'

   AND GCC.SEGMENT5 = NVL (:P_ACC_NUM, GCC.SEGMENT5)

   AND TRUNC (GJH.DEFAULT_EFFECTIVE_DATE)

          BETWEEN NVL (:P_FROM_DATE, TRUNC (GJH.DEFAULT_EFFECTIVE_DATE))

              AND NVL (:P_TO_DATE, TRUNC (GJH.DEFAULT_EFFECTIVE_DATE))

   AND GJH.JE_SOURCE = 'Manual'

UNION ALL

-----ALL OTHER SOURCES OTHER THAN ABOVE----------

SELECT

GJH.DESCRIPTION JV_HEADER_DESCRIPTION,

  GJH.NAME JV_NAME,

  GJH.JE_CATEGORY,

  GJH.JE_SOURCE,

  GJH.PERIOD_NAME,

  NVL(XAL.ACCOUNTED_CR,0) GL_CR,

  NVL(XAL.ACCOUNTED_DR,0) GL_DR,

  GJL.DESCRIPTION JV_LINE_DESCRIPTION,

  XAH.EVENT_TYPE_CODE,

  XAH.DESCRIPTION SLA_DESCRIPTION,

  XAL.AE_LINE_NUM,

  XAL.ACCOUNTING_DATE GL_DATE,

  '' VENDOR_NAME,

  '' CHECK_NUMBER,

  NULL CHECK_DATE,

  NULL VOUCHER_NUMBER,

  NULL VOUCHER_DATE,

  DECODE(XAL.ACCOUNTED_CR,NULL,XAL.ACCOUNTED_DR,0)  RECEIPT,

  DECODE(XAL.ACCOUNTED_DR,NULL,XAL.ACCOUNTED_CR,0) PAYMENT

FROM

  XLA_AE_HEADERS XAH,

  XLA_AE_LINES XAL,

  GL_JE_LINES GJL,

  GL_IMPORT_REFERENCES GIR,

  GL_JE_HEADERS GJH,

  GL_CODE_COMBINATIONS GCC

WHERE

  XAH.AE_HEADER_ID=XAL.AE_HEADER_ID AND

  GJL.JE_LINE_NUM = GIR.JE_LINE_NUM AND

  GJL.JE_HEADER_ID = GIR.JE_HEADER_ID AND

  GIR.GL_SL_LINK_TABLE = XAL.GL_SL_LINK_TABLE AND

  GIR.GL_SL_LINK_ID = XAL.GL_SL_LINK_ID AND

  GJL.JE_HEADER_ID=GJH.JE_HEADER_ID  AND

  GJL.CODE_COMBINATION_ID=GCC.CODE_COMBINATION_ID AND

  GCC.SEGMENT5=NVL(:P_ACC_NUM,GCC.SEGMENT5)  AND

  TRUNC(GJH.DEFAULT_EFFECTIVE_DATE) BETWEEN NVL(:P_FROM_DATE,TRUNC(GJH.DEFAULT_EFFECTIVE_DATE)) AND NVL(:P_TO_DATE,TRUNC(GJH.DEFAULT_EFFECTIVE_DATE)) AND

  GJH.STATUS='P' AND

  GJH.JE_SOURCE NOT IN ('Receivables','Payables','Cash Management')

/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

SELECT distinct ai.invoice_num, ai.gl_date, xah.accounting_date,

             gjh.je_category, gjh.je_source, gjh.period_name, gjh.status,

             aid.invoice_line_number, aid.line_type_lookup_code,

             ail.description, aid.amount, aid.dist_code_combination_id

        FROM apps.gl_je_headers gjh,

             apps.gl_je_lines gjl,

             apps.gl_import_references gir,

             apps.xla_ae_lines xal,

             apps.xla_ae_headers xah,

             apps.xla_events xe,

             apps.xla_event_types_tl xet,

             apps.xla_event_classes_tl xect,

             apps.xla_distribution_links xdl,

             apps.ap_invoice_distributions_all aid,

             apps.ap_invoices_all ai,

             apps.ap_invoice_lines_all ail

       WHERE gjh.je_header_id = gjl.je_header_id

         AND gjh.je_header_id = gir.je_header_id

         AND gjl.je_header_id = gir.je_header_id

         AND gir.je_line_num = gjl.je_line_num

         AND gir.gl_sl_link_id = xal.gl_sl_link_id

         AND xal.ae_header_id = xah.ae_header_id

         AND xah.event_id = xe.event_id

         AND xe.event_type_code = xet.event_type_code

         AND xe.application_id = xet.application_id

         AND xet.LANGUAGE = USERENV ('LANG')

         AND xect.event_class_code = xet.event_class_code

         AND xect.application_id = xe.application_id

         AND xect.LANGUAGE = USERENV ('LANG')

         AND xah.ae_header_id = xdl.ae_header_id

         AND xal.ae_line_num = xdl.ae_line_num

         AND xdl.source_distribution_type = 'AP_INV_DIST'

         AND xdl.source_distribution_id_num_1 = aid.invoice_distribution_id

         AND ai.invoice_id = aid.invoice_id

         AND ai.invoice_id = ail.invoice_id

         AND ail.invoice_id = aid.invoice_id

         AND aid.invoice_line_number = ail.line_number

         AND xah.event_type_code <> ' MANUAL'

         AND gjh.je_source = 'Payables'

         AND ai.org_id = p_org_id

         AND xah.accounting_date BETWEEN p_period_start_date AND p_period_end_date;

///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

AP-SLA-GL LINK QUERY

AP-SLA-GL QUERY

SELECT

     aia.INVOICE_ID "Invoice Id",

     aia.INVOICE_NUM "Invoice Number",

     aia.INVOICE_DATE "Invoice Date",

     aia.INVOICE_AMOUNT "Amount",

     xal.ENTERED_DR "Entered DR in SLA",

     xal.ENTERED_CR "Entered CR in SLA",

     xal.ACCOUNTED_DR "Accounted DR in SLA",

     xal.ACCOUNTED_CR "Accounted CR in SLA",

      gjh.je_source ,

     gjl.ENTERED_DR "Entered DR in GL",

     gjl.ACCOUNTED_DR "Accounted DR in GL",

     xal.ACCOUNTING_CLASS_CODE "Accounting Class",

     gcc.SEGMENT1||'.'||gcc.SEGMENT2||'.'

         ||gcc.SEGMENT3||'.'||gcc.SEGMENT4||'.'

         ||gcc.SEGMENT5||'.'||gcc.SEGMENT6||'.'

         ||gcc.SEGMENT7 "Code Combination",

     aia.INVOICE_CURRENCY_CODE "Inv Curr Code",

     aia.PAYMENT_CURRENCY_CODE "Pay Curr Code",

     aia.GL_DATE "GL Date",

     xah.PERIOD_NAME "Period",

     aia.PAYMENT_METHOD_CODE "Payment Method",

     aia.VENDOR_ID "Vendor Id",

     aps.VENDOR_NAME "Vendor Name",

     xah.JE_CATEGORY_NAME "JE Category Name"

 FROM

     apps.ap_invoices_all aia,

     xla.xla_transaction_entities XTE,

     apps.xla_events xev,

     apps.xla_ae_headers XAH,

     apps.xla_ae_lines XAL,

     apps.GL_IMPORT_REFERENCES gir,

     apps.gl_je_headers gjh,

     apps.gl_je_lines  gjl,

     apps.gl_code_combinations gcc,

     apps.ap_suppliers aps,

     (select aid1.invoice_id,

             pa.project_id,

             nvl(pa.segment1,'NO PROJECT') Project

     from    apps.ap_invoice_distributions_all aid1,

             apps.PA_PROJECTS_ALL pa

     where aid1.rowid in

         (select MAx(rowid)

         from apps.ap_invoice_distributions_all aid2

         where aid1.INvoice_ID=aid2.INvoice_ID

         group by aid1.invoice_id)

     and aid1.project_id=pa.project_id(+)) sql1,

     (select aid1.invoice_id,

             pt.task_id,

             nvl(pt.task_number,'NO TASK') Task

     from    apps.ap_invoice_distributions_all aid1,

             apps.PA_TASKS pt

     where aid1.rowid in

         (select MAx(rowid)

         from apps.ap_invoice_distributions_all aid2

         where aid1.INvoice_ID=aid2.INvoice_ID

         group by aid1.invoice_id)

     and aid1.task_id=pt.task_id(+)) sql2

 WHERE

     aia.INVOICE_ID = xte.source_id_int_1

     and aia.INVOICE_ID=sql1.Invoice_ID

     and aia.INVOICE_ID=sql2.Invoice_ID

     and xev.entity_id= xte.entity_id

     and xah.entity_id= xte.entity_id

     and xah.event_id= xev.event_id

     and XAH.ae_header_id = XAL.ae_header_id

    -- and XAH.je_category_name = 'Purchase Invoices'

     and XAH.gl_transfer_status_code= 'Y'

     and XAL.GL_SL_LINK_ID=gir.GL_SL_LINK_ID

     and gir.GL_SL_LINK_TABLE = xal.GL_SL_LINK_TABLE

     and gjl.JE_HEADER_ID=gjh.JE_HEADER_ID

     and gjh.JE_HEADER_ID=gir.JE_HEADER_ID

     and gjl.JE_HEADER_ID=gir.JE_HEADER_ID

     and gir.JE_LINE_NUM=gjl.JE_LINE_NUM

     and gcc.CODE_COMBINATION_ID=XAL.CODE_COMBINATION_ID

     and gcc.CODE_COMBINATION_ID=gjl.CODE_COMBINATION_ID

     and aia.VENDOR_ID=aps.VENDOR_ID

     and gjh.STATUS='P'

     and gjh.Actual_flag='A'

     and gjh.CURRENCY_CODE='USD'

     and aia.Invoice_id=&Invoice_Id;

/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

Hi,

Below is the SQL for AP->PO->SLA->GL for for *R12*.

========================

SELECT ph.segment1 po_number, aps.vendor_name,

msi.segment1 item_number, msi.description item_description,

'A/P PO Match' accrual_transaction, 'AP*' SOURCE,

(SELECT organization_name

FROM apps.org_organization_definitions

WHERE organization_id = pd.destination_organization_id)

org,

ai.invoice_num doc_number, ai.invoice_date doc_date,

aid.invoice_line_number line, pl.unit_meas_lookup_code uom,

aid.quantity_invoiced quantity, aid.unit_price,

( NVL (xdl.unrounded_accounted_dr, 0)

- NVL (xdl.unrounded_accounted_cr, 0)

) accounted_amount,

( NVL (xdl.unrounded_entered_cr, 0)

- NVL (xdl.unrounded_entered_cr, 0)

) entered_amount,

xal.currency_code

FROM apps.gl_je_headers gjh,

apps.gl_je_lines gjl,

apps.gl_import_references gir,

apps.xla_ae_lines xal,

apps.xla_ae_headers xah,

apps.xla_events xe,

apps.xla_event_types_tl xet,

apps.xla_event_classes_tl xect,

apps.xla_distribution_links xdl,

apps.ap_invoice_distributions_all aid,

apps.ap_invoices_all ai,

apps.po_headers_all ph,

apps.po_distributions_all pd,

apps.po_lines_all pl,

apps.ap_suppliers aps,

apps.mtl_system_items_b msi

WHERE gjh.je_header_id = gjl.je_header_id

AND gjh.je_header_id = gir.je_header_id

AND gjl.je_header_id = gir.je_header_id

AND gir.je_line_num = gjl.je_line_num

AND gir.gl_sl_link_id = xal.gl_sl_link_id

AND xal.ae_header_id = xah.ae_header_id

AND xah.event_id = xe.event_id

AND xe.event_type_code = xet.event_type_code

AND xe.application_id = xet.application_id

AND xet.LANGUAGE = USERENV ('LANG')

AND xect.entity_code = xet.entity_code

AND xect.event_class_code = xet.event_class_code

AND xect.application_id = xe.application_id

AND xect.LANGUAGE = USERENV ('LANG')

AND xah.ae_header_id = xdl.ae_header_id

AND xal.ae_line_num = xdl.ae_line_num

AND xdl.source_distribution_type = 'AP_INV_DIST'

AND xdl.source_distribution_id_num_1 aid.invoice_distribution_id

AND ai.invoice_id = aid.invoice_id

AND aid.po_distribution_id = pd.po_distribution_id

AND gjh.je_source = 'Payables'

AND ph.po_header_id = pd.po_header_id

AND pl.po_header_id = ph.po_header_id

AND pd.po_line_id = pl.po_line_id

AND pd.org_id = 83

AND ph.org_id = 83

AND ai.org_id = 83

AND aid.org_id = 83

AND gjl.code_combination_id = 1011

AND ph.vendor_id = aps.vendor_id

AND msi.inventory_item_id = pl.item_id

AND msi.organization_id = pd.destination_organization_id

///////////////////////////////////////////////////////////////////////////////////////////////////////////////////

 SELECT distinct 

           aia.INVOICE_ID "R12_Invoice_Id",

           AIA.DOC_SEQUENCE_VALUE,

           aia.INVOICE_NUM ,

           aia.attribute6 "11i_INVOICE_ID",

           aia.GL_DATE,

           aia.INVOICE_AMOUNT,

           xal.ACCOUNTED_DR "Accounted DR IN SLA",

           xal.ACCOUNTED_CR "Accounted CR IN SLA",

           gjl.ACCOUNTED_CR "ACCOUNTED_CR IN GL",

           gjl.ACCOUNTED_DR "Accounted DR IN GL",         

           xev.event_type_code,

              gcc.SEGMENT1

           || '.'

           || gcc.SEGMENT2

           || '.'

           || gcc.SEGMENT3

           || '.'

           || gcc.SEGMENT4

           || '.'

           || gcc.SEGMENT5

           || '.'

           || gcc.SEGMENT6

           || '.'

           || gcc.SEGMENT7

              "CODE_COMBINATION",

           aia.GL_DATE,

           xah.PERIOD_NAME,

           aia.VENDOR_ID "Vendor Id",

           aps.VENDOR_NAME "Vendor Name",

           xah.JE_CATEGORY_NAME "JE Category Name",

                      GJH.JE_SOURCE

    FROM   ap_invoices_all aia,

           xla.xla_transaction_entities XTE,

           xla_events xev,

           xla_ae_headers XAH,

           xla_ae_lines XAL,

           GL_IMPORT_REFERENCES gir,

           gl_je_headers gjh,

           gl_je_lines gjl,

           gl_code_combinations gcc,

           ap_suppliers aps

   WHERE       aia.INVOICE_ID = xte.source_id_int_1

           and aia.ACCTS_PAY_CODE_COMBINATION_ID = gcc.code_combination_id

           AND xev.entity_id = xte.entity_id

           AND xah.entity_id = xte.entity_id

           AND xah.event_id = xev.event_id

           AND XAH.ae_header_id = XAL.ae_header_id

              and XAH.je_category_name = 'Purchase Invoices'

           AND GJH.JE_SOURCE = 'Payables'

           AND XAL.GL_SL_LINK_ID = gir.GL_SL_LINK_ID

           and gir.GL_SL_LINK_ID = gjl.GL_SL_LINK_ID

           AND gir.GL_SL_LINK_TABLE = xal.GL_SL_LINK_TABLE

           AND gjl.JE_HEADER_ID = gjh.JE_HEADER_ID

           AND gjl.ledger_id = gjh.ledger_id

           and xah.ledger_id = gjh.ledger_id

           AND gjh.JE_HEADER_ID = gir.JE_HEADER_ID

           and aia.set_of_books_id = gjh.ledger_id

           AND gjl.JE_HEADER_ID = gir.JE_HEADER_ID

           AND gir.JE_LINE_NUM = gjl.JE_LINE_NUM

           AND gcc.CODE_COMBINATION_ID = XAL.CODE_COMBINATION_ID

           AND gcc.CODE_COMBINATION_ID = gjl.CODE_COMBINATION_ID

           AND aia.VENDOR_ID = aps.VENDOR_ID

           AND gjh.PERIOD_NAME BETWEEN NVL (:PERIOD_FROM, gjh.PERIOD_NAME)

                                   AND  NVL (:PERIOD_TO, gjh.PERIOD_NAME)               

           AND gcc.SEGMENT1 = NVL (:seg1, gcc.SEGMENT1)

           AND gcc.SEGMENT3 = NVL (:seg, gcc.SEGMENT3)

ORDER BY   1, aia.GL_DATE

///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

select glcc.concatenated_segments Account_number,

invoice.gl_date gl_date,

je_lines.ENTERED_DR ENTERED_DR,

je_lines.ENTERED_CR ENTERED_CR,

je_lines.ACCOUNTED_DR ACCOUNTED_DR,

je_lines.ACCOUNTED_CR ACCOUNTED_CR,

from gl_je_lines je_lines,gl_import_references gl_ref,xla_ae_lines xla_lines,

xla_ae_headers xla_headers,xla_events xla_events,xla_transaction_entities xla_trans,

ap_invoices_all invoice,gl_je_headers h, gl_code_combinations_kfv glcc

where je_lines.je_header_id = gl_ref.je_header_id

and je_lines.je_line_num = gl_ref.je_line_num

and gl_ref.gl_sl_link_table = xla_lines.gl_sl_link_table

and gl_ref.gl_sl_link_id = xla_lines.gl_sl_link_id

and xla_lines.APPLICATION_ID = xla_headers.APPLICATION_ID

and xla_lines.ae_header_id = xla_headers.ae_header_id

and xla_headers.application_id = xla_events.application_id

and xla_headers.event_id = xla_events.event_id

and h.actual_flag = 'A'

and xla_trans.APPLICATION_ID = xla_events.APPLICATION_ID

and xla_trans.entity_id = xla_events.entity_id

--and invoice.invoice_num = xla_trans.transaction_number

and invoice.invoice_id = xla_trans.SOURCE_ID_INT_1

and invoice.invoice_id = 10020

and h.je_source = 'Payables'

and xla_lines.code_combination_id = glcc.code_combination_id

and je_lines.je_header_id = h.je_header_id;

Lets see what you have missed out:

1. You didn't use xla.xla_transaction_entities or xla_transaction_entities_upg.

You will hit the issue mentioned here.

2. Have ledger_id join in between xla.xla_transaction_entities and ap_invoices_all.

3. Use entity_code filter and nvl over source_id_int_1 (as mentioned in the link) for xla.xla_transaction_entities.

Also,

4. You may use application_id filter on all mentioned XLA tables as 200, as it is for invoice.

5. If you have transferred journal entries to GL in Summary mode, XLA will have the specific accounting entry of invoice than GL.

GL to AP (Payable) Query....

Q1:--------------------------------------------------------------------------------------------'

SELECT GJH.NAME,

       GJH.DESCRIPTION,

       TO_CHAR(GJH.DEFAULT_EFFECTIVE_DATE, 'dd-MON-yyyy') EFF_DATE,

       DECODE (XTE.ENTITY_CODE,

               'AP_INVOICES', PV.VENDOR_NAME,

               (SELECT AC.VENDOR_NAME

                  FROM AP_CHECKS_ALL AC

                 WHERE XTE.SOURCE_ID_INT_1 = AC.CHECK_ID)

       ) PARTY,

       AIA.INVOICE_NUM DOC_SEQUENCE_VALUE,

       GJH.JE_CATEGORY,

       XAL.ACCOUNTED_DR ACCOUNTED_DR,

       XAL.ACCOUNTED_CR ACCOUNTED_CR,

       GJL.JE_HEADER_ID,

       XAL.PARTY_TYPE_CODE,

       GJH.JE_SOURCE,

       GJH.PERIOD_NAME,

       GCC.SEGMENT5,

       GJL.JE_LINE_NUM,

       GJH.DEFAULT_EFFECTIVE_DATE

  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_AE_HEADERS XAH,

       XLA.XLA_TRANSACTION_ENTITIES XTE,

       AP_INVOICES_ALL AIA,

       PO_VENDORS PV

 WHERE GJB.JE_BATCH_ID = GJH.JE_BATCH_ID

   AND GJH.JE_HEADER_ID = GJL.JE_HEADER_ID

   AND GJL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID

   AND GJL.JE_HEADER_ID = GIR.JE_HEADER_ID

   AND GJL.JE_LINE_NUM = GIR.JE_LINE_NUM

   AND GIR.GL_SL_LINK_ID = XAL.GL_SL_LINK_ID

   AND GIR.GL_SL_LINK_TABLE = XAL.GL_SL_LINK_TABLE

   AND XAL.AE_HEADER_ID = XAH.AE_HEADER_ID

   AND XTE.APPLICATION_ID = XAH.APPLICATION_ID

   AND XTE.ENTITY_ID = XAH.ENTITY_ID

   AND AIA.INVOICE_ID(+) = XTE.SOURCE_ID_INT_1

   AND AIA.VENDOR_ID = PV.VENDOR_ID(+)

   AND GJL.STATUS = 'P'

   AND GCC.SEGMENT5 = NVL (:ACCOUNT_ID, GCC.SEGMENT5)

   AND TRUNC (GJH.DEFAULT_EFFECTIVE_DATE)

          BETWEEN NVL (:PERIOD_FROM, TRUNC (GJH.DEFAULT_EFFECTIVE_DATE))

              AND NVL (:PERIOD_TO, TRUNC (GJH.DEFAULT_EFFECTIVE_DATE))

  AND GJH.JE_SOURCE = 'Payables'

-------------------------------------------------------------------------------------------------------

Q2:- --------------------------------------------------------------------------------------------------

SELECT

  GJH.DESCRIPTION JV_HEADER_DESCRIPTION,

  GJH.NAME JV_NAME,

  GJH.JE_CATEGORY,

  GJH.JE_SOURCE,

  GJH.PERIOD_NAME,

  NVL(XAL.ACCOUNTED_CR,0) GL_CR,

  NVL(XAL.ACCOUNTED_DR,0) GL_DR,

  GJL.DESCRIPTION JV_LINE_DESCRIPTION,

  XAH.EVENT_TYPE_CODE,

  XAH.DESCRIPTION SLA_DESCRIPTION,

  XAL.AE_LINE_NUM,

  XAL.ACCOUNTING_DATE GL_DATE,

  ASUP.VENDOR_NAME,

  TO_CHAR(ACA.CHECK_NUMBER),

  ACA.CHECK_DATE,

  ACA.DOC_SEQUENCE_VALUE VOUCHER_NUMBER,

  ACA.CREATION_DATE VOUCHER_DATE,

  DECODE(XAL.ACCOUNTED_CR,NULL,XAL.ACCOUNTED_DR,0)  RECEIPT,

  DECODE(XAL.ACCOUNTED_DR,NULL,XAL.ACCOUNTED_CR,0) PAYMENT

FROM

  XLA_AE_HEADERS XAH,

  XLA_AE_LINES XAL,

  GL_JE_LINES GJL,

  GL_IMPORT_REFERENCES GIR,

  GL_JE_HEADERS GJH,

  GL_CODE_COMBINATIONS GCC,

  AP_SUPPLIERS ASUP,

  AP_CHECKS_ALL ACA

WHERE

  XAH.AE_HEADER_ID=XAL.AE_HEADER_ID AND

  GJL.JE_LINE_NUM = GIR.JE_LINE_NUM AND

  GJL.JE_HEADER_ID = GIR.JE_HEADER_ID AND

  GIR.GL_SL_LINK_TABLE = XAL.GL_SL_LINK_TABLE AND

  GIR.GL_SL_LINK_ID = XAL.GL_SL_LINK_ID AND

  GJL.JE_HEADER_ID=GJH.JE_HEADER_ID  AND

  GJL.CODE_COMBINATION_ID=GCC.CODE_COMBINATION_ID AND

  ASUP.VENDOR_ID(+)=XAL.PARTY_ID AND

  ACA.DOC_SEQUENCE_ID(+)=XAH.DOC_SEQUENCE_ID AND

  ACA.DOC_SEQUENCE_VALUE(+)=XAH.DOC_SEQUENCE_VALUE AND

  GCC.SEGMENT5=NVL(:P_ACC_NUM,GCC.SEGMENT5)  AND

  TRUNC(GJH.DEFAULT_EFFECTIVE_DATE) BETWEEN NVL(:P_FROM_DATE,TRUNC(GJH.DEFAULT_EFFECTIVE_DATE)) AND NVL(:P_TO_DATE,TRUNC(GJH.DEFAULT_EFFECTIVE_DATE)) AND

  GJH.STATUS='P' 

  AND   GJH.JE_SOURCE='Payables'

  

  

  

  

  ACTION: COMPLETE TRANSACTION: 

RA_CUSTOMER_TRX_ALL.CUSTOMER_TRX_ID = RA_CUST_TRX_LINE_GL_DIST_ALL.CUSTOMER_TRX_ID 

RA_CUSTOMER_TRX_ALL.CUSTOMER_TRX_ID = XLA_TRANSACTION_ENTITIES.SOURCE_ID_INT_1 

XLA_TRANSACTION_ENTITIES.ENTITY_ID = XLA_EVENTS.ENTITY_ID

ACTION: RUN CREATE ACCOUNTING: 

RA_CUST_TRX_LINE_GL_DIST_ALL.CUST_TRX_LINE_GL_DIST_ID = XLA_DISTRIBUTION_LINKS.SOURCE_DISTRIBUTION_ID_NUM_1 

XLA_AE_LINES.AE_HEADER_ID = XLA_DISTRIBUTION_LINKS.AE_HEADER_ID 

XLA_AE_HEADERS.AE_HEADER_ID = XLA_DISTRIBUTION_LINKS.AE_HEADER_ID

ACTION: RUN TRANSFER TO GL: 

 XLA_AE_LINES.GL_SL_LINK_ID = GL_JE_LINES.GL_SL_LINK_ID

XLA_AE_LINES.GL_SL_LINK_ID = GL_IMPORT_REFERENCES.GL_SL_LINK_ID 

GL_IMPORT_REFERENCES.JE_HEADER_ID = GL_JE_LINES.JE_HEADER_ID 

GL_IMPORT_REFERENCES.JE_HEADER_ID = GL_JE_HEADERS.JE_HEADER_ID

SELECT b.NAME batch_name,

              b.description batch_description,

              h.je_category,

              h.je_source,

              h.period_name je_period_name,

              h.NAME journal_name,

             h.status journal_status,

             h.description je_description,

            l.je_line_num line_number 

FROM

            gl_je_batches b,

            gl_je_headers h,

            gl_je_lines l,

            gl_code_combinations_kfv glcc,

            gl_import_references gir,

            xla_ae_lines xlal,

            xla_ae_headers xlah,

            xla_events xlae,

            xla.xla_transaction_entities xlate,

            ra_customer_trx_all rct,

            

WHERE

           b.je_batch_id = h.je_batch_id

AND   h.je_header_id = l.je_header_id

AND   xlal.code_combination_id = glcc.code_combination_id

AND    l.je_header_id = gir.je_header_id

AND    l.je_line_num = gir.je_line_num

AND    gir.gl_sl_link_table = xlal.gl_sl_link_table

AND    gir.gl_sl_link_id = xlal.gl_sl_link_id

AND    xlal.ae_header_id = xlah.ae_header_id

AND   xlah.event_id = xlae.event_id

AND   xlae.entity_id = xlate.entity_id

AND   xlae.application_id = xlate.application_id

AND   h.je_source = 'Receivables'

AND   h.period_name = '2009-10'

AND    rct.trx_number = xlate.transaction_number

AND   xlate.transaction_number ='<TranxName>'

Check to see whether there are any transactions which are costed but do not have Material Transaction Account

 /*
 Check to see whether there are any transactions which are costed
 but donot have MTA.
*/
select mmt.inventory_item_id item,
       mmt.transaction_type_id ttype,
       mmt.transaction_action_id act,
       mmt.transaction_source_type_id src,
       mmt.primary_quantity pq,
       mmt.cost_group_id cg,
       mmt.actual_cost ac,
       mmt.transaction_cost tc,
       mmt.prior_cost pc,
       mmt.new_cost nc,
       mmt.quantity_adjusted qadj,
       mmt.percentage_change pcg,
       msi.inventory_asset_flag iaf,
       mse.asset_inventory AI
 from mtl_material_transactions mmt,
      mtl_system_items msi,
      mtl_secondary_inventories mse
  where mmt.organization_id =&org_id
    and msi.inventory_item_id = mmt.inventory_item_id
    and msi.organization_id = &org_id
    and mse.secondary_inventory_name = mmt.subinventory_code
    and mse.organization_id = &org_id
    and mmt.costed_flag is null
    and not ( mmt.primary_quantity >0
              and mmt.transaction_action_id in (2,5,55,28)
     )
    AND mmt.transaction_action_id not in 
        ( 5,24,30,40,41,42,43,50,51,52)
     and not exists ( select 'x'
                       from mtl_transaction_accounts mta
         where mta.transaction_id = mmt.transaction_id
    )