EBS R12 QUERY TO GET ALL INVENTORY ORGANIZATIONS IN OPERATING UNITS

QUERY TO GET ALL INVENTORY ORGANIZATIONS IN OPERATING UNITS

SELECT  LGR.NAME "Ledger"           ,
  XLE.NAME "Legal Entity"           ,
  HOU1.NAME "Operating Unit"        ,
  HOU.NAME "Inventory Organization" ,
  hou.organization_id,
  MP.ORGANIZATION_CODE "Organization Code"
   FROM HR_ALL_ORGANIZATION_UNITS HOU ,
  HR_ORGANIZATION_INFORMATION HOI1    ,
  HR_ORGANIZATION_INFORMATION HOI2    ,
  MTL_PARAMETERS MP                   ,
  GL_LEDGERS LGR                      ,
  XLE_ENTITY_PROFILES XLE             ,
  HR_ALL_ORGANIZATION_UNITS HOU1
  WHERE HOU.ORGANIZATION_ID = HOI1.ORGANIZATION_ID
AND HOU.ORGANIZATION_ID     = HOI2.ORGANIZATION_ID
AND HOU.ORGANIZATION_ID     = MP.ORGANIZATION_ID
AND HOI1.ORG_INFORMATION1   = 'INV'
AND HOI1.ORG_INFORMATION2   = 'Y'
AND ( HOI1.ORG_INFORMATION_CONTEXT
  || '') = 'CLASS'
AND ( HOI2.ORG_INFORMATION_CONTEXT
  || '')                                                                                                             ='Accounting Information'
AND TO_NUMBER(DECODE(RTRIM(TRANSLATE(HOI2.ORG_INFORMATION1,'0123456789',' ')), NULL, HOI2.ORG_INFORMATION1,-99999))  = LGR.LEDGER_ID
AND LGR.OBJECT_TYPE_CODE                                                                                             ='L'
AND NVL(LGR.COMPLETE_FLAG,'Y')                                                                                       ='Y'
AND DECODE(HOI2.ORG_INFORMATION_CONTEXT, 'Accounting Information', TO_NUMBER(HOI2.ORG_INFORMATION2), NULL)           =XLE.LEGAL_ENTITY_ID
AND DECODE(HOI2.ORG_INFORMATION_CONTEXT, 'Accounting Information', TO_NUMBER(HOI2.ORG_INFORMATION3), TO_NUMBER(NULL))=HOU1.ORGANIZATION_ID
--AND LGR.NAME                                                                                                         ='&NAME'
order by HOU1.NAME

Comments

Post a Comment

Popular posts from this blog

opaque schema xsd (standard file used in OIC)

 opaque schema xsd (standard file used in OIC) You can use an opaque schema in a stage file action Read File or Write File operation without concern for a schema for the file.   The only condition is that whatever is sent to the opaque element in the opaque schema must be base64-encoded data.  or in other words  it is used to decode base64 files Save the below xml as  opaqueschema.xsd  file and then you can use it in the integration. <?xml version = '1.0' encoding = 'UTF-8'?> <schema targetNamespace="http://xmlns.oracle.com/pcbpel/adapter/opaque/" xmlns="http://www.w3.org/2001/XMLSchema" > <element name="opaqueElement" type="base64Binary" /> </schema>
Read more

SQL,PLSQL interview practice and DSA patterns

 --=============sql=========================== --How to find duplicate records with the number they are duplicated? select job,count(*) from emp group by job; --Write a Query to select to the Nth highest salary from a table? select min(sal) from (select  sal from emp order by sal desc) where rownum < 3;   --Can Dual table be deleted or dropped or altered or inserted? select round(123.45) from dual; --it cant be deleted or dropped or altered or inserted --List the emps in dept 20 whose sal is > the avg sal of deptno 10 emps? select * from emp where deptno = 20  and sal>(select avg(sal)from emp where deptno = 10); --List the empno, ename, sal, dname of all the 'Mgrs' and 'Analyst' working in NEWYORK, --DALLAS with an exp more than 7 years without receiving the Comma Asc order of Loc? select empno,ename,sal,dname from emp,dept where emp.deptno= dept.deptno and  loc in('NEWYORK', 'DALLAS') and job in ('manager','analyst')and trunc(mo...
Read more

QR code Generate From Oracle PLSQL

QR code Generate From Oracle PLSQL Here is a GITHUB link For necessary Files. https://github.com/Rehman0270/PLSQLQRcode 1) Download The required files from above link. 2) upload all jar archives in lib folder to your database  (Eample path in WINSCP to put the jar files : /u02/oracle/VIS/fs1/EBSapps/10.1.2/lib ) 3) After uploading open putty and change directory to lib folder in WINSCP and run below commands one by one cd  /u02/oracle/VIS/fs1/EBSapps/10.1.2/lib loadjava -force -genmissing -r -user username/password@database -verbose core-1.7.jar loadjava -force -genmissing -r -user username/password@database -verbose javase-1.7.jar loadjava -force -genmissing -r -user username/password@database -verbose qrgen-1.2.jar 4) Open sql developer and run this functions CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "QRCodeBean" as import oracle.sql.BLOB; import oracle.sql.*; import oracle.jdbc.driver.*; import java.sql.*; import javax.imageio.ImageIO; import java.awt.image.Buffered...
Read more