AP OPENING BALANCE QUERIES

 <?xml version="1.0" encoding="WINDOWS-1252" ?>

<dataTemplate name ="APOPENINGBALANCE" description="AP OPENING BALANCE REPORT " Version="1.0" >

<parameters>

<parameter name="FROM_DATE" datatype="VARCHAR2"/>

<parameter name="TO_DATE" datatype="VARCHAR2"/>

</parameters> 

<dataQuery> 

<sqlStatement name="Q1">

<![CDATA[

SELECT 

ROUND(SUM(NVL(XAL.ACCOUNTED_DR,0)),2) OPENING_DEBIT,

ROUND(SUM(NVL(ACCOUNTED_CR,0)),2)   OPENING_CREDIT ,

ROUND(SUM(NVL(XAL.ACCOUNTED_DR,0)),2) -

ROUND(SUM(NVL(ACCOUNTED_CR,0)),2)   OPENING_BALANCE

FROM 

XLA.XLA_AE_LINES  XAL,

XLA.XLA_AE_HEADERS XAH,

XLA.XLA_TRANSACTION_ENTITIES XTE,

AP_INVOICES_ALL AI

WHERE 1=1

AND XTE.SOURCE_ID_INT_1(+)=AI.INVOICE_ID

AND XAH.AE_HEADER_ID = XAL.AE_HEADER_ID

AND XAH.APPLICATION_ID = XAL.APPLICATION_ID

AND XAL.APPLICATION_ID = XTE.APPLICATION_ID

AND XAH.ENTITY_ID = XTE.ENTITY_ID

AND AI.GL_DATE BETWEEN (

        SELECT

            MIN(A.START_DATE)

        FROM

            GL_PERIODS A

        WHERE

            A.PERIOD_NAME = NVL(:FROM_DATE, A.PERIOD_NAME)

    )

    AND

    (SELECT

            MAX(A.END_DATE)

        FROM

            GL_PERIODS A

        WHERE

            A.PERIOD_NAME = NVL(:TO_DATE, A.PERIOD_NAME)

    )

AND (XTE.ENTITY_CODE ='AP_INVOICES' OR

     XTE.ENTITY_CODE='AP_PAYMENTS')

]]>

</sqlStatement>

<sqlStatement name="Q2">

<![CDATA[

SELECT

   DECODE(FAP.APPLICATION_SHORT_NAME, 'SQLAP', 'AP', APPLICATION_SHORT_NAME) MODULE,

   IBM.PAYMENT_METHOD_NAME MOD_OF_PAY,

   XTE.TRANSACTION_NUMBER INVCHECKNUMBER,

   XAH.DESCRIPTION DESCRIPTION,

   AI.INVOICE_TYPE_LOOKUP_CODE TYPE,

   SUBSTR(AI.GL_DATE,1,11) GL_DATE,

   NVL(XAL.ACCOUNTED_DR,0)  DEBIT,

   NVL(XAL.ACCOUNTED_CR,0)   CREDIT

FROM

   AP_INVOICES_ALL                AI,

   XLA.XLA_TRANSACTION_ENTITIES   XTE,

   XLA.XLA_AE_HEADERS             XAH,

   XLA.XLA_AE_LINES               XAL,

   FND_APPLICATION                FAP,

   IBY_PAYMENT_METHODS_TL         IBM

WHERE

   1 = 1

   AND XTE.SOURCE_ID_INT_1 (+) = AI.INVOICE_ID

   AND XAH.AE_HEADER_ID = XAL.AE_HEADER_ID

   AND XAH.APPLICATION_ID = XAL.APPLICATION_ID

   AND XAL.APPLICATION_ID = XTE.APPLICATION_ID

   AND XAH.ENTITY_ID = XTE.ENTITY_ID

   AND AI.PAYMENT_METHOD_CODE = IBM.PAYMENT_METHOD_CODE

   AND XTE.ENTITY_CODE = 'AP_INVOICES'

   AND FAP.APPLICATION_ID (+) = XAH.APPLICATION_ID

   AND AI.GL_DATE BETWEEN (

      SELECT

         MIN(A.START_DATE)

      FROM

         GL_PERIODS A

      WHERE

         A.PERIOD_NAME = NVL(:FROM_DATE, A.PERIOD_NAME)

   ) AND (

      SELECT

         MAX(A.END_DATE)

      FROM

         GL_PERIODS A

      WHERE

         A.PERIOD_NAME = NVL(:TO_DATE, A.PERIOD_NAME)

   )

   AND ( XAL.ACCOUNTED_DR != 0

         OR XAL.ACCOUNTED_CR IS NOT NULL )

   AND ( XAL.ACCOUNTED_CR != 0

         OR XAL.ACCOUNTED_DR IS NOT NULL )

UNION ALL

SELECT

   DECODE(FAP.APPLICATION_SHORT_NAME, 'SQLAP', 'AP', APPLICATION_SHORT_NAME) MODULE,

   ACA.PAYMENT_METHOD_LOOKUP_CODE MOD_OF_PAY,

   XTE.TRANSACTION_NUMBER INVCHECKNUMBER,

   XAH.DESCRIPTION DESCRIPTION,

   ALC.DISPLAYED_FIELD TYPE,

   SUBSTR(NVL(AIPA.ACCOUNTING_DATE,XAH.ACCOUNTING_DATE),1,11) GL_DATE,

   NVL(XAL.ACCOUNTED_DR,0) DEBIT,

   NVL(XAL.ACCOUNTED_CR,0) CREDIT

FROM

   AP_CHECKS_ALL                  ACA,

   XLA.XLA_TRANSACTION_ENTITIES   XTE,

   XLA.XLA_AE_HEADERS             XAH,

   XLA.XLA_AE_LINES               XAL,

   FND_APPLICATION                FAP,

   AP_LOOKUP_CODES                ALC,

   AP_INVOICE_PAYMENTS_ALL        AIPA

WHERE

   1 = 1

   AND ACA.CHECK_ID = XTE.SOURCE_ID_INT_1 (+)

   AND AIPA.CHECK_ID = ACA.CHECK_ID

   AND AIPA.INVOICE_PAYMENT_ID = XAL.SOURCE_ID (+)

   AND XAH.AE_HEADER_ID = XAL.AE_HEADER_ID

   AND XAH.APPLICATION_ID = XAL.APPLICATION_ID

   AND XAL.APPLICATION_ID = XTE.APPLICATION_ID

   AND XAH.ENTITY_ID = XTE.ENTITY_ID

   AND XTE.ENTITY_CODE = 'AP_PAYMENTS'

   AND XAH.LEDGER_ID = XAL.LEDGER_ID

   AND FAP.APPLICATION_ID (+) = XAH.APPLICATION_ID

   AND ALC.LOOKUP_TYPE  = 'PAYMENT TYPE'

   AND ALC.LOOKUP_CODE (+) = ACA.PAYMENT_TYPE_FLAG

   AND AIPA.ACCOUNTING_DATE BETWEEN (

   SELECT

         MIN(A.START_DATE)

      FROM

         GL_PERIODS A

      WHERE

         A.PERIOD_NAME = NVL(:FROM_DATE, A.PERIOD_NAME)

   ) AND (

      SELECT

         MAX(A.END_DATE)

      FROM

         GL_PERIODS A

      WHERE

         A.PERIOD_NAME = NVL(:TO_DATE, A.PERIOD_NAME)

   )

   AND ( XAL.ACCOUNTED_DR != 0

         OR XAL.ACCOUNTED_CR IS NOT NULL )

   AND ( XAL.ACCOUNTED_CR != 0

         OR XAL.ACCOUNTED_DR IS NOT NULL )

]]>

</dataQuery>

<dataStructure>

<group name="G_OP_BAL" source="Q1">

<element name="OPENING_DEBIT" value="OPENING_DEBIT"/>

<element name="OPENING_CREDIT" value="OPENING_CREDIT"/>

<element name="OPENING_BALANCE" value="OPENING_BALANCE"/>

</group>

<group name="G_OP_PER" source="Q2">

<element name="MODULE" value="MODULE"/>

<element name="MOD_OF_PAY" value="MOD_OF_PAY"/>

<element name="INVCHECKNUMBER" value="INVCHECKNUMBER"/>

<element name="DESCRIPTION" value="DESCRIPTION"/>

<element name="TYPE" value="TYPE"/>

<element name="GL_DATE" value="GL_DATE"/>

<element name="DEBIT" value="DEBIT"/>

<element name="CREDIT" value="CREDIT"/>

</group>

</dataStructure>

</dataTemplate>


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

DOCUMENTATION ON SANDBOX and PERSONALIZATION

Image
DOCUMENTATION ON SANDBOX PERSONALIZATION Prepared by VAMSI GOVATHOTI ROLE: ORACLE APPS TECHNICAL CONSULTANT Contents A) What is a Sandbox? 2 B) Purpose of Sandboxes 2 C) Key Features 2 D) Why Use Sandboxes? 2 1. How to Customize (Enable or Disable) the Fields/Components in the Sandbox 6 2. How to Enable Direct Access from User Interface (UI) to the Desired Destination using an URL 9 3. How to Create “Role Based Access control” to particular Fields / Components 13 4. ‘More Tasks’ Tab to be renamed to 'Create' on Purchase Requisitions page. 18 5. By using Application Composer making field required in sand box 23 6. Personalization by using application composer 27 A) By using Application Composer creating new field in sand box 27 B) Creating of currency field with fixed choice list 32 C) Creating of currency field with dynamic choice list. 36 7. Creating a button in sand box 40 8. Creating a global function for runtime mess...
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