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
    )

Total Material Transaction Account value in 1,14 as per acct_periods for the cost group

 select oap.acct_period_id,
        mta.accounting_line_type,
sum(mta.base_transaction_value)
  from mtl_transaction_accounts mta,
       org_acct_periods oap
 where mta.organization_id = oap.organization_id
   and mta.accounting_line_type in (1,14)
   and mta.transaction_date between oap.period_start_date
                                and oap.schedule_close_date
   and oap.organization_id = &org_id
   and mta.organization_id = &org_id
   and exists ( select 'X' 
                   from mtl_cst_actual_cost_details mcacd,
        cst_quantity_layers cql
    where mcacd.organization_id=&org_id
      and mcacd.layer_id=cql.layer_id
      and cql.cost_group_id = &cost_group_id
      and cql.organization_id=mcacd.organization_id
      and mcacd.transaction_id = mta.transaction_id
                 )
 group by oap.acct_period_id,
        mta.accounting_line_type

EAM Work Order with PO and Direct Item Transaction and Work Order Requisition

-- PO and Requisition with EAM Work Order:

    SELECT   porl.wip_entity_id,
        (SELECT  we.wip_entity_name FROM WIP_ENTITIES we
                  WHERE porl.wip_entity_id = we.wip_entity_id) work_order,
                 --  pol.category_id,    
               (SELECT organization_code 
        FROM mtl_parameters WHERE organization_id= porl.destination_organization_id ) organization_code,   
                 --  porl.wip_operation_seq_num OPERATION_SEQ_NUM,
                   porh.segment1 PR_NUMBER,
                   porl.line_num PR_LINE,
                  -- porh.creation_date PR_Request_date,
                  -- g.EMPLOYEE_NUMBER Requester_Numner,
                 --  g.FULL_NAME Requester_NAME,
                 --  porh.authorization_status PR_STATUS,
                   poh.segment1 PO_NUMBER,
                   NVL(POH.TYPE_LOOKUP_CODE,porh.type_lookup_code) PO_DOCUMENT_TYPE,
                  -- pol.line_num PO_LINE,   
                   nvl(poh.CREATION_DATE,porh.CREATION_DATE) PO_CREATION_DATE,
                   NVL(poh.authorization_status,porh.authorization_status)  PO_STATUS,
                  (SELECT SEGMENT1 FROM MTL_SYSTEM_ITEMS_B 
             WHERE inventory_item_id =nvl(pol.item_id,porl.item_id)
                           AND organization_id = NVL(pol.org_id,porl.destination_organization_id)) PO_LINE_ITEM,
(SELECT SEGMENT1 FROM MTL_SYSTEM_ITEMS_B 
             WHERE inventory_item_id =nvl(pol.item_id,porl.item_id)
                           AND organization_id = NVL(pol.org_id,porl.destination_organization_id)) PO_MATERIAL,
                   (SELECT description FROM MTL_SYSTEM_ITEMS_B 
             WHERE inventory_item_id =NVL(pol.item_id,porl.item_id)
                           AND organization_id = NVL(pol.org_id,porl.destination_organization_id)) PO_LINE_ITEM_DESCRIPTION,           
                   NVL( pol.UNIT_MEAS_LOOKUP_CODE,porl.UNIT_MEAS_LOOKUP_CODE)PO_ORDER_LINE_UOM,
   --
   (SELECT mcst.category_set_name
                   FROM   apps.mtl_item_categories MIC,
                          apps.mtl_category_sets_tl MCST
                   WHERE  MCST.category_set_id=MIC.category_set_id
                   AND category_id = NVL(pol.category_id,porl.category_id)
                   AND mic.organization_id =NVL(pol.org_id,porl.destination_organization_id)
                   AND mic.inventory_item_id = NVL(pol.item_id,porl.item_id) ) Matrial_Group,
   --
                 /*  ( SELECT   MCST.category_set_name                    
                     FROM apps.mtl_system_items_b MSB,
                          apps.mtl_categories_kfv MCK,
                          apps.mtl_item_categories MIC,
                          apps.mtl_category_sets_tl MCST
                   WHERE MIC.inventory_item_id = MSB.inventory_item_id
                     and MIC.category_id = MCK.category_id                 
                     and MSB.organization_id =MIC.organization_id                   
                     and MCST.category_set_id=MIC.category_set_id
                    -- and MCST.category_set_name = 'ALK Material Group'
                     and MSB.organization_id = porl.destination_organization_id
                     and  MSB.inventory_item_id=porl.item_id
                     and MIC.category_id = porl.category_id) Matrial_Group_v2,*/
--
(SELECT concatenated_segments from gl_code_combinations_kfv
                   WHERE code_combination_id = pord.code_combination_id) Acct_Assignment_Cat, 
                   NVL (pol.quantity, porl.quantity) Quantity_requested, 
                   (SELECT  location_code
                      FROM   hr_locations_all
                     WHERE   LOCATION_ID = NVL(poh.ship_to_location_id,porl.deliver_to_location_id)) Delivery_To_location,
                  -- rsh.creation_date Delivery_Date,
   (SELECT segment1 FROM PO_VENDORS pv   
                     WHERE vendor_id = NVL (poh.vendor_id,porl.vendor_id)) supplier_number,
                   (SELECT vendor_name FROM PO_VENDORS pv   
                      WHERE vendor_id = NVL (poh.vendor_id,porl.vendor_id)) supplier_name,
                  NVL(pol.unit_price,porl.unit_price)   Net_Price,
                  (NVL (porl.unit_price, 0) * NVL (porl.quantity, 0))  Net_Order_Value,
(SELECT  DT.TITLE||'-'||DST.SHORT_TEXT AS "TITLE  AND SHORT TEXT"
                  FROM FND_DOCUMENT_DATATYPES DAT,
                      FND_DOCUMENT_ENTITIES_TL DET    ,
                      FND_DOCUMENTS_TL DT             ,
                      FND_DOCUMENTS D                 ,
                      FND_DOCUMENT_CATEGORIES_TL DCT  , 
                      FND_ATTACHED_DOCUMENTS AD       ,
                      FND_DOCUMENTS_SHORT_TEXT DST      
                WHERE D.DOCUMENT_ID       = AD.DOCUMENT_ID
                AND DT.DOCUMENT_ID        = D.DOCUMENT_ID
                AND DCT.CATEGORY_ID       = D.CATEGORY_ID
                AND D.DATATYPE_ID         = DAT.DATATYPE_ID
                AND AD.ENTITY_NAME        = DET.DATA_OBJECT_CODE
                AND DST.MEDIA_ID          = D.MEDIA_ID
                AND DAT.NAME              = 'SHORT_TEXT'
                AND ad.pk2_value (+)      = porl.WIP_ENTITY_ID ) PO_Short_Text
            FROM   PO_REQUISITION_HEADERS_ALL porh,
                   PO_REQUISITION_LINES_ALL porl,
                   PO_REQ_DISTRIBUTIONS_ALL pord,
                   PO_DISTRIBUTIONS_ALL pod,
                   PO_LINES_ALL pol,
                   PO_HEADERS_ALL poh,
                   PO_LINE_LOCATIONS_ALL poll,
                   RCV_SHIPMENT_LINES rsl,
                   RCV_SHIPMENT_HEADERS rsh,
                   PO_RELEASES_ALL por,                   
                   (SELECT   person_id, full_name, EMPLOYEE_NUMBER
                      FROM   PER_ALL_PEOPLE_F
                     WHERE   TRUNC (SYSDATE) BETWEEN EFFECTIVE_START_DATE
                                                 AND  EFFECTIVE_END_DATE) g
           WHERE   porh.requisition_header_id = porl.requisition_header_id(+)
                   AND porl.requisition_line_id = pord.requisition_line_id(+)
                   AND pord.distribution_id = pod.req_distribution_id(+)
                   AND pod.po_header_id = pol.po_header_id(+)
                   AND pod.po_line_id = pol.po_line_id(+)
                   AND pol.po_header_id = poh.po_header_id(+)
                   AND pod.po_header_id = poll.po_header_id(+)
                   AND pod.po_line_id = poll.po_line_id(+)
                   AND pod.line_location_id = poll.line_location_id(+)
                   AND pod.po_header_id = rsl.po_header_id(+)
                   AND pod.po_line_id = rsl.po_line_id(+)
                   AND pod.line_location_id = rsl.po_line_location_id(+)
                   AND rsl.shipment_header_id = rsh.shipment_header_id(+)
                   AND porl.to_person_id = g.person_id(+)
                   AND poll.po_release_id = por.po_release_id(+)
                   AND porl.wip_entity_id IS NOT NULL ;               
                  -- AND porl.wip_entity_id = 967820
  -- AND porl.wip_entity_id = 1021821; 
                 --  AND porh.segment1 = '5500000282';

    -- Material Transaction with Work Order   in Inventory --(Direct Item)

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

SELECT (SELECT we.wip_entity_name FROM  WIP_ENTITIES we 

                   WHERE we.WIP_ENTITY_ID = wro.WIP_ENTITY_ID) work_order,

       (SELECT organization_code 

                    FROM mtl_parameters WHERE organization_id= mtrh.organization_id) organization_code,              

                   mtrh.REQUEST_NUMBER PR_MO_NUMBER,              

                   msib.segment1 item_number,

                   msib.description item_description,

                   msib.item_type,              

               ( SELECT   MCST.category_set_name                    

                 FROM apps.MTL_SYSTEM_ITEMS_B MSB,

                      apps.MTL_CATEGORIES_KFV MCK,

                      apps.MTL_ITEM_CATEGORIES MIC,

                      apps.MTL_CATEGORY_SETS_TL MCST

                   WHERE MIC.inventory_item_id = MSB.inventory_item_id

                     AND MIC.category_id = MCK.category_id                 

                     AND MSB.organization_id =MIC.organization_id

                     AND MSB.organization_id = msib.organization_id

                     AND MCST.category_set_id=MIC.category_set_id

                     AND MCST.category_set_name = 'ALK Material Group'

                     AND MSB.inventory_item_id =msib.inventory_item_id) MATRIAL_GROUP,

                      cic.ITEM_COST STANDARD_COST, 

                  msib.LIST_PRICE_PER_UNIT Price_UOM,                   

                  msib.PRIMARY_UNIT_OF_MEASURE,

                    'Standard'   Price_control,

                     (SELECT abc_class_name

                        FROM MTL_ABC_CLASSES mac,

                             MTL_ABC_ASSIGNMENTS maa,

                             MTL_ABC_ASSIGNMENT_GROUPS MAAG

                        WHERE 1 =1

                        AND maa.inventory_item_id = msib.inventory_item_id

                        AND maa.assignment_group_id = maag.assignment_group_id

                        AND maa.abc_class_id = mac.abc_class_id

                        AND maag.organization_id = msib.organization_id

                        AND maa.creation_date >=

                            (SELECT MAX(maa.creation_date)

                                FROM MTL_ABC_ASSIGNMENTS maa,

                                     MTL_ABC_ASSIGNMENT_GROUPS maag

                            WHERE 1 =1

                            AND maa.inventory_item_id = msib.inventory_item_id

                            AND maa.assignment_group_id = maag.assignment_group_id

                            AND maag.organization_id = msib.organization_id

                            )

                        ) ABC_INDICATOR,

                           'XXXXX'  VALUATION_CLASS,

                           'XXXXX' MRP_Type

                   FROM   WIP_REQUIREMENT_OPERATIONS wro,

                           MTL_TXN_REQUEST_LINES mtrl,

                           MTL_TXN_REQUEST_HEADERS mtrh,

                           CST_ITEM_COSTS cic  ,

                           MTL_SYSTEM_ITEMS_B msib

                   WHERE       wro.WIP_ENTITY_ID = mtrl.TXN_SOURCE_ID(+)

                           AND wro.inventory_item_id = mtrl.inventory_item_id(+)

                           AND wro.operation_seq_num = mtrl.TXN_SOURCE_LINE_ID(+)

                           AND mtrl.header_id = mtrh.header_id

                           AND mtrl.INVENTORY_ITEM_ID = cic.INVENTORY_ITEM_ID(+)

                           AND mtrl.ORGANIZATION_ID = cic.ORGANIZATION_ID(+)   

                           AND msib.ORGANIZATION_ID = mtrl.ORGANIZATION_ID

                           AND msib.INVENTORY_ITEM_ID = mtrl.INVENTORY_ITEM_ID

                          -- AND c.REQUEST_NUMBER='2410357'

                           AND wro.WIP_ENTITY_ID = 1021821;

EAM Work Order Details:

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

SELECT  -- wdj.ORGANIZATION_ID,  

        (SELECT organization_code 

  FROM mtl_parameters WHERE organization_id= wdj.ORGANIZATION_ID) organization_code,  

        -- wdj.WIP_ENTITY_ID,

         we.wip_entity_name work_order,

bd.department_code department,

         bd.description department_description,

         cii.instance_number asset_number,

          wdj.description wo_description,

         cii.instance_description asset_number_desc,

       --  msibk.eam_item_type asset_type,

          TRUNC(wdj.creation_date) WO_Creation_date,

         TO_CHAR(wdj.scheduled_start_date,'DD-MON-YYYY') scheduled_start_date,

         TO_CHAR(wdj.scheduled_completion_date,'DD-MON-YYYY') scheduled_completion_date,

         ROUND((wdj.SCHEDULED_COMPLETION_DATE - wdj.SCHEDULED_START_DATE) * 24,3) WO_Duration,

  (SELECT A2.MEANING 

FROM APPS.WIP_DISCRETE_JOBS A1, APPS.FND_LOOKUP_VALUES A2

WHERE A2.LOOKUP_TYPE LIKE '%WIP_JOB_STATUS%'

AND TO_CHAR(A1.STATUS_TYPE)=A2.LOOKUP_CODE

AND a1.WIP_ENTITY_ID = wdj.WIP_ENTITY_ID  

AND A2.LANGUAGE='US') WO_STATUS,

          wdj.CLASS_CODE WIP_Account,

         (SELECT A2.MEANING

           FROM APPS.WIP_DISCRETE_JOBS A1, APPS.FND_LOOKUP_VALUES A2

WHERE A2.LOOKUP_TYPE ='WIP_EAM_ACTIVITY_PRIORITY'

AND TO_CHAR(A1.priority)=A2.LOOKUP_CODE

AND a1.WIP_ENTITY_ID = wdj.WIP_ENTITY_ID  

AND A2.LANGUAGE='US') Work_Order_Priority,

          (SELECT A2.MEANING 

FROM APPS.WIP_DISCRETE_JOBS A1, APPS.FND_LOOKUP_VALUES A2

WHERE A2.LOOKUP_TYPE ='WIP_EAM_WORK_ORDER_TYPE'

AND TO_CHAR(A1.WORK_ORDER_TYPE)=A2.LOOKUP_CODE

AND a1.WIP_ENTITY_ID = wdj.WIP_ENTITY_ID  

AND A2.LANGUAGE='US') WorkOrder_Type,        

          (SELECT A2.MEANING 

FROM APPS.WIP_DISCRETE_JOBS A1, APPS.FND_LOOKUP_VALUES A2

WHERE A2.LOOKUP_TYPE ='MTL_EAM_ACTIVITY_TYPE'

AND TO_CHAR(A1.activity_type)=A2.LOOKUP_CODE

            AND  A2.LOOKUP_CODE = TO_CHAR(wdj.activity_type)

AND A1.WIP_ENTITY_ID = wdj.WIP_ENTITY_ID  

AND A2.LANGUAGE='US') WorkOrder_Activity_Type,  

     wr.work_request_number,

(SELECT  DT.TITLE||'-'||DLT.LONG_TEXT AS "TITLE  AND LONG TEXT"

          FROM FND_DOCUMENT_DATATYPES DAT,

              FND_DOCUMENT_ENTITIES_TL DET    ,

              FND_DOCUMENTS_TL DT             ,

              FND_DOCUMENTS D                 ,

              FND_DOCUMENT_CATEGORIES_TL DCT  , 

              FND_ATTACHED_DOCUMENTS AD       ,

              FND_DOCUMENTS_LONG_TEXT DLT    

        WHERE D.DOCUMENT_ID       = AD.DOCUMENT_ID

        AND DT.DOCUMENT_ID        = D.DOCUMENT_ID

        AND DCT.CATEGORY_ID       = D.CATEGORY_ID

        AND D.DATATYPE_ID         = DAT.DATATYPE_ID

        AND AD.ENTITY_NAME        = DET.DATA_OBJECT_CODE

        AND DLT.MEDIA_ID          = D.MEDIA_ID

        AND DAT.NAME              = 'LONG_TEXT'

        AND ad.pk2_value (+)      = wdj.WIP_ENTITY_ID ) Work_Order_Attachment,

wepb.actual_cost

  FROM   WIP_DISCRETE_JOBS wdj,

         WIP_ENTITIES we,

         WIP_OPERATIONS wo,

BOM_DEPARTMENTS bd,       

         CSI_ITEM_INSTANCES cii,

         WIP_EAM_WORK_REQUESTS wr,   

         MTL_SYSTEM_ITEMS_B_KFV msibk,        

         EAM_WORK_ORDER_DETAILS ewod,

        ( SELECT wepb.organization_id,

                 wepb.wip_entity_id,

                NVL(SUM(wepb.actual_mat_cost)+SUM(wepb.actual_lab_cost)+SUM(wepb.actual_eqp_cost),0) actual_cost

          FROM WIP_EAM_PERIOD_BALANCES wepb

         WHERE

(wepb.period_name,wepb.period_set_name) IN

( SELECT

period_name,

TO_CHAR(NULL) period_set_name

FROM org_acct_periods oap

UNION ALL

SELECT

gp.period_name,

gp.period_set_name

FROM

gl_periods gp

WHERE gp.adjustment_period_flag='N'

)

GROUP BY

wepb.organization_id,

wepb.wip_entity_id

) wepb  

 WHERE   1=1   

         AND wdj.WIP_ENTITY_ID = wo.WIP_ENTITY_ID(+)         

         AND wdj.WIP_ENTITY_ID = ewod.WIP_ENTITY_ID(+)

         AND wdj.MAINTENANCE_OBJECT_ID = cii.INSTANCE_ID(+)

         AND wdj.WIP_ENTITY_ID = wr.wip_entity_id(+)

         AND wdj.owning_department=bd.department_id(+)

         AND NVL(wdj.asset_group_id,wdj.rebuild_item_id)=msibk.inventory_item_id(+) 

         AND wdj.organization_id=msibk.organization_id(+) 

         AND wdj.organization_id=we.organization_id 

         AND wdj.WIP_ENTITY_ID = we.WIP_ENTITY_ID

AND wdj.organization_id=wepb.organization_id(+)

         AND wdj.wip_entity_id=wepb.wip_entity_id(+)

         AND we.entity_type IN (6,7);

         AND we.wip_entity_name='87034';