Pending RMA List

select ORDER_NUMBER ,
       (select ORGANIZATION_CODE from org_organization_definitions where ORGANIZATION_ID = oola.SHIP_FROM_ORG_ID) ORGANIZATION_CODE,
        (select ORGANIZATION_name from org_organization_definitions where ORGANIZATION_ID = oola.SHIP_FROM_ORG_ID) ORGANIZATION_name,
       oola.ORDERED_QUANTITY ,
       oola.FULFILLED_QUANTITY
from oe_order_headers_all ooha,
     oe_order_lines_all oola
where ooha.header_id = oola.header_id
and ooha.org_id = oola.org_id
and ORDER_CATEGORY_CODE = 'RETURN'
and oola.FLOW_STATUS_CODE = 'AWAITING_RETURN'
and oola.OPEN_FLAG = 'Y' --
order by 2
--and order_number = 3711800202

Item Reservations Query

SELECT   (select msib.segment1 || '|' || msib.segment2
         from mtl_system_items_b msib
         where msib.inventory_item_id = moq.inventory_item_id
               and msib.organization_id = moq.organization_id ) PART_NUMBER,
         moq.organization_id,
         ((SELECT   ORGANIZATION_code
                     FROM   org_organization_definitions odd
                    WHERE   organization_name LIKE 'UET%'
                            AND odd.organization_id = moq.organization_id)) Org_code,
        mr.reserve_quantity reserved_quantity,
         moq.transaction_quantity - mr.reserve_quantity available_toreserve_qty,
        moq.transaction_quantity        
  FROM   (  SELECT   moq.inventory_item_id inventory_item_id,
                     moq.organization_id organization_id,
                     SUM (moq.primary_transaction_quantity)
                        transaction_quantity
              FROM   mtl_onhand_quantities_detail moq
             WHERE   Moq.Organization_Id = nvl(:Organization_Id,Organization_Id)
                    -- AND moq.subinventory_code = 'XXXX'
          GROUP BY   moq.inventory_item_id, moq.organization_id) moq,
         (  SELECT   SUM (primary_reservation_quantity) reserve_quantity,
                     inventory_item_id,
                     organization_id
              FROM   Mtl_Reservations
             WHERE   Organization_Id = nvl(:Organization_Id,Organization_Id)
                   -- AND subinventory_code = ' XXXX'
          GROUP BY   Organization_Id, inventory_item_id) mr
 WHERE   moq.inventory_item_id = mr.inventory_item_id
         AND Moq.Organization_Id = Mr.Organization_Id
         and Moq.Organization_Id in (SELECT   ORGANIZATION_id
                                      FROM   org_organization_definitions odd
                                      WHERE   organization_name LIKE 'UET%')

INTERORG SHIPMENT Query

SELECT  
         ms.quantity,
         ms.receipt_date,
         ms.from_organization_id from_org,
         ms.to_organization_id to_org,
         ms.unit_of_measure uom,
         rsh.shipment_num,
         ms.item_id,
         mp1.organization_code from_org_code,
         mp2.organization_code to_org_code
    FROM
         mtl_supply ms,
         rcv_shipment_headers rsh,
         mtl_parameters mp1,
         mtl_parameters mp2
   WHERE
         ms.supply_type_code = 'SHIPMENT'
     AND ms.shipment_header_id = rsh.shipment_header_id
     AND (rsh.shipment_num = :p_shipment_no OR :p_shipment_no IS NULL)
     AND ms.from_organization_id = mp1.organization_id
     AND ms.to_organization_id = mp2.organization_id
ORDER BY receipt_date, mp2.organization_code

Business Groups / LE / OU / INV Query

Try the following ...
1) For Business Groups

select
o.organization_id ,
o.organization_id ,
otl.name ,
o.date_from ,
o.date_to ,
o.internal_address_line ,
o.location_id ,
o.comments ,
o2.org_information1 ,
o2.org_information2 ,
o2.org_information3 ,
o2.org_information4 ,
o3.org_information1 ,
o3.org_information2 ,
o3.org_information3 ,
o3.org_information4 ,
o3.org_information5 ,
o3.org_information6 ,
o3.org_information7 ,
o3.org_information8 ,
o3.org_information9 ,
o3.org_information10 ,
o3.org_information14 ,
o4.org_information2 ,
o3.org_information15 ,
o3.org_information16
from hr_all_organization_units o ,
hr_all_organization_units_tl otl ,
hr_organization_information o2 ,
hr_organization_information o3 ,
hr_organization_information o4
where o.organization_id = otl.organization_id
and o.organization_id = o2.organization_id (+)
and o.organization_id = o3.organization_id
and o.organization_id = o4.organization_id
and o3.org_information_context = 'Business Group Information'
and o2.org_information_context (+) = 'Work Day Information'
and o4.org_information_context = 'CLASS'
and o4.org_information1 = 'HR_BG'
and o4.org_information2 = 'Y'

2) For LEs :-

start with the same query as above but replace the 2 AND conditions for the columns shown as below ...

and o3.org_information_context = 'Legal Entity Accounting'
and o4.org_information1 = 'HR_LEGAL'

3) For OUs:-

start with the same query as above but replace the 2 AND conditions for the columns shown as below ...

and o3.org_information_context = 'Operating Unit Information'
and o4.org_information1 = 'OPERATING_UNIT'

4) For IOs:-

start with the same query as above but replace the 2 AND conditions for the columns shown as below ...

and o3.org_information_context = 'Accounting Information'
and o4.org_information1 = 'INV'

And finally, if you want to show OU, LE, SOB and SOB currency for your inv orgs, you can use this:

SELECT
o.organization_id
, otl.name
, o.date_from
, o.date_to
, o.internal_address_line
, loc.location_code as location
, o.comments
, les.business_group_id
, otl2.name as business_group
, les.organization_id as le_id
, les.name as legal_entity
, les.set_of_books_id
, sob.name as set_of_books
, sob.short_name
, sob.currency_code as currency
, opu.organization_id as ou_id
, opu.name as operating_unit
, les.vat_registration_number
FROM
hr_all_organization_units o
, hr_all_organization_units_tl otl
, hr_organization_information o2
, hr_organization_information o3
, hr_organization_information o4
, hr_legal_entities les
, hr_all_organization_units otl2
, hr_locations loc
, hr_operating_units opu
, gl_sets_of_books sob
where 1=1
and o.organization_id = otl.organization_id
AND o.organization_id = o2.organization_id (+)
and o.organization_id = o3.organization_id
and o.organization_id = o4.organization_id
AND o3.org_information_context = 'Accounting Information'
and o2.org_information_context (+) = 'Work Day Information'
and o4.org_information_context = 'CLASS'
and o4.org_information1 = 'INV'
and o4.org_information2 = 'Y'
and o3.org_information2 = les.organization_id
and les.business_group_id = otl2.organization_id
and o.location_id = loc.location_id(+)
and o3.org_information3 = opu.organization_id
and les.set_of_books_id = sob.set_of_books_id
order by o.organization_id

Payables Bank Acccount Update

Note -  Steps from Down to Up --
commit;

DECLARE
  CURSOR C1 IS SELECT * FROM NBB_EXT_ACC_ID;
BEGIN
  FOR C1_REC IN C1
   LOOP
   update pay_external_accounts pea set segment3 = C1_REC.ACCOUNT_NO where pea.external_account_id = C1_REC.EXTERNAL_ACCOUNT_ID;
   DBMS_OUTPUT.PUT_LiNE(C1_REC.ACCOUNT_NO || ' - ' ||C1_REC.EXTERNAL_ACCOUNT_ID );
  
 END LOOP;
END;


SELECT * FROM NBB_EXT_ACC_ID 

CREATE TABLE NBB_EXT_ACC_ID AS(
   Select EMPLOYEE_NUMBER,
         -- '0' || segment3 account_no ,
         (SELECT ACCOUNT_NO FROM NBB_ACCOUNT_INFO where EMP_NO = EMPLOYEE_NUMBER ) ACCOUNT_NO,
          pea.external_account_id
   FROM  per_all_people_f ppf,
         per_all_assignments_f paf,
         pay_personal_payment_methods_f pppm,
         pay_external_accounts pea
   WHERE     pppm.external_account_id = pea.external_account_id
         AND ppf.person_id = paf.person_id
         AND pppm.assignment_id = paf.assignment_id
         AND pppm.business_group_id = ppf.business_group_id
         AND TRUNC (SYSDATE) BETWEEN ppf.effective_Start_Date
                                 AND  ppf.effective_end_date
         AND TRUNC (SYSDATE) BETWEEN paf.effective_start_Date
                                 AND  paf.effective_end_date
         AND TRUNC (SYSDATE) BETWEEN pppm.effective_start_date
                                 AND  pppm.effective_end_date
         AND paf.business_group_id = 81
         and ppf.EMPLOYEE_NUMBER IN (SELECT EMP_NO FROM NBB_ACCOUNT_INFO)
         and pppm.org_payment_method_id = 61 )

SELECT * FROM NBB_Account_info

create table NBB_ACCOUNT_INFO
( EMP_NO VARCHAR2(5) , ACCOUNT_NO VARCHAR2(15) ) ;

INSERT INTO NBB_Account_info VALUES ('154454556','0266448895');
INSERT INTO NBB_Account_info VALUES ('154545968','0273123564');
INSERT INTO NBB_Account_info VALUES ('115445440','0274278774');

Update Bank Account Button Disabled

Update Bank Account Button Disabled ... Dont worry !!



1. Login as System Administrator user  (SYSADMIN User) and then switch responsibility to User Management. ( If responsibility is not added pls add same )
2. Click in Roles & Role Inheritance link
3. Do the following search: - Type: Role and Responsibilities - Category: Miscellaneous - Application: Cash Management Choose the Cash Management responsibility where you want to create and ma intain bank accounts.
4. Click in the Update icon 5. Click in Security Wizard button
6. Run the Wizard for CE UMX Security wizard.
7. Click in Add Legal entities and add the legal entity you will give the selected role access to the all bank accounts within this legal entity and choose grants that you want to assign to this role on the bank accounts of this legal entity ( Use, Maintenance, Bank Account Transfers )
8. Click in the Apply button and then click in the save button.
9. Now try to create or update a bank account in the chosen Cash Management Responsibility
---end of note

GL - XLA - AR Drill Down Query

/* GL - XLA - AR Drill Down Query */ 


SELECT   gjb.name,
         gjh.doc_sequence_value,
         gjl.ENTERED_DR,
         gjl.ENTERED_CR,
         (   gcc.segment1
          || '-'
          || gcc.segment2
          || '-'
          || gcc.segment3
          || '-'
          || gcc.segment4
          || '-'
          || gcc.segment5
          || '-'
          || gcc.segment6
          || '-'
          || gcc.segment7
          || '-'
          || gcc.segment8)
            Account,
         (SELECT   TRX_NUMBER
            FROM   xla_ae_lines xal,
                   xla_distribution_links xdl,
                   ra_customer_trx_all ract,
                   ra_customer_trx_lines_all ractl,
                   ra_cust_trx_line_gl_dist_all rctlgdl
           WHERE   xal.ae_header_id = xdl.ae_header_id
                   AND xal.GL_SL_LINK_ID = gir.gl_sl_link_id         --2621350
                   AND SOURCE_DISTRIBUTION_TYPE =  'RA_CUST_TRX_LINE_GL_DIST_ALL'
                   AND xal.application_id = xdl.application_id
                   AND xal.APPLICATION_ID = 222
                   AND ractl.customer_trx_id = ract.customer_trx_id
                   AND ractl.customer_trx_line_id =
                         rctlgdl.customer_trx_line_id
                   AND ract.customer_trx_id = rctlgdl.customer_trx_id
                   AND xdl.source_distribution_id_num_1 =
                         rctlgdl.CUST_TRX_LINE_GL_DIST_ID)
            TRX_NUMBER,
         gir.SUBLEDGER_DOC_SEQUENCE_VALUE
  FROM   gl_je_batches gjb,
         gl_je_headers gjh,
         gl_je_lines gjl,
         gl_code_combinations gcc,
         gl_import_References gir
 WHERE       gjb.je_batch_id = gjh.je_batch_id
         AND gjh.je_header_id = gjl.je_header_id
         AND gcc.code_combination_id = gjl.CODE_COMBINATION_ID
         AND gir.je_header_id = gjh.je_header_id
         AND gir.JE_LINE_NUM = gjl.JE_LINE_NUM
         AND gir.je_batch_id = gjh.je_batch_id
         AND gjb.DATE_CREATED LIKE SYSDATE