AR customer transactions or opening balance query

Q1 for ar customer transaction lines 


SELECT 

distinct

hca.account_number client_number,

hp.party_name client_name,

rctt.name TRX_TYPE1,

rct.trx_number trx_number,

rct.PURCHASE_ORDER,

TO_CHAR(rct.creation_date,'DD-MON-YY') TRX_DATE,

TO_CHAR(rctg.gl_date,'DD-MON-YY') GL_DATE,

apsa.TAX_REMAINING VAT_AMOUNT,

ROUND(NVL(XAL.ACCOUNTED_DR,0),2) DEBIT,

ROUND(NVL(ACCOUNTED_CR,0),2)   CREDIT ,

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

ROUND((NVL(ACCOUNTED_CR,0)),2) balance,

xal.CURRENCY_CODE CURRENCY,

rtt.name payment_term,

TO_CHAR(apsa.due_date,'DD-MON-YY') DUE_DATE,

xal.description DESCRIPTION

FROM 

XLA.XLA_AE_LINES  XAL,

XLA.XLA_AE_HEADERS XAH,

XLA.XLA_TRANSACTION_ENTITIES XTE,

ra_customer_trx_all rct,

ra_customer_trx_lines_all rcl,

RA_CUST_TRX_LINE_GL_DIST_ALL rctg,

ra_cust_trx_types_all rctt,

ra_terms_tl rtt,

ar_payment_schedules_all apsa,

hz_cust_accounts hca,

hz_parties hp

WHERE 1=1

and rct.customer_trx_id = rcl.customer_trx_id

and rct.CUST_TRX_TYPE_ID = rctt.CUST_TRX_TYPE_ID

and rcl.customer_trx_line_id = rctg.customer_trx_line_id

and hp.PARTY_ID = hca.PARTY_ID

and rct.bill_to_customer_id = hca.cust_account_id

and rct.customer_trx_id = apsa.customer_trx_id

AND XTE.SOURCE_ID_INT_1(+)=rct.customer_trx_id

and rct.TERM_ID = rtt.TERM_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 rctg.GL_DATE BETWEEN (

        SELECT

            MIN(A.START_DATE)

        FROM

            GL_PERIODS A

        WHERE

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

    )

    AND

    (SELECT

            MAX(A.END_DATE)

        FROM

            GL_PERIODS A

        WHERE

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

    )

AND (XTE.ENTITY_CODE ='TRANSACTIONS')

and hca.account_number between nvl(:P_NUM_FROM,hca.account_number) and nvl(:P_NUM_TO,hca.account_number)

and rct.org_id = nvl(:P_ORG_ID,rct.org_id)


Q2 for opeining balance and details


with a as(

select

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,

ra_customer_trx_all rct,

ra_customer_trx_lines_all rcl,

RA_CUST_TRX_LINE_GL_DIST_ALL rctg,

ra_cust_trx_types_all rctt,

ra_terms_tl rtt,

ar_payment_schedules_all apsa,

hz_cust_accounts hca,

hz_parties hp

WHERE 1=1

and rct.customer_trx_id = rcl.customer_trx_id

and rct.CUST_TRX_TYPE_ID = rctt.CUST_TRX_TYPE_ID

and rcl.customer_trx_line_id = rctg.customer_trx_line_id

and hp.PARTY_ID = hca.PARTY_ID

and rct.bill_to_customer_id = hca.cust_account_id

and rct.customer_trx_id = apsa.customer_trx_id

AND XTE.SOURCE_ID_INT_1(+)=rct.customer_trx_id

and rct.TERM_ID = rtt.TERM_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 rctg.GL_DATE <= (

        SELECT

            MIN(A.START_DATE)

        FROM

            GL_PERIODS A

        WHERE

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

    )  

AND (XTE.ENTITY_CODE ='TRANSACTIONS')

and hca.account_number between nvl(:P_NUM_FROM,hca.account_number) and nvl(:P_NUM_TO,hca.account_number)

and rct.org_id = nvl(:P_ORG_ID,rct.org_id)

order by XAL.creation_date),

b as (select sum(INVOICE_AMOUNT) INVOICE_AMOUNT,sum(PAID_AMOUNT) PAID_AMOUNT,sum(INVOICE_AMOUNT-PAID_AMOUNT) DUE_AMOUNT from (

SELECT

max(inv.inv_Amt ) INVOICE_AMOUNT,

max(XAL.ACCOUNTED_DR) PAID_AMOUNT

FROM 

XLA.XLA_AE_LINES  XAL,

XLA.XLA_AE_HEADERS XAH,

XLA.XLA_TRANSACTION_ENTITIES XTE,

ra_customer_trx_all rct,

ra_customer_trx_lines_all rcl,

RA_CUST_TRX_LINE_GL_DIST_ALL rctg,

ra_cust_trx_types_all rctt,

ra_terms_tl rtt,

ar_payment_schedules_all apsa,

hz_cust_accounts hca,

hz_parties hp,

(select sum(extended_amount) inv_Amt , a.customer_trx_id

        from apps.ra_customer_trx_lines_all b ,apps.ra_customer_trx_all a where 1=1

                and a.customer_trx_id = b.customer_trx_id

                group by a.customer_trx_id

                  ) inv

WHERE 1=1

and inv.customer_trx_id = rct.customer_trx_id

and rct.customer_trx_id = rcl.customer_trx_id

and rct.CUST_TRX_TYPE_ID = rctt.CUST_TRX_TYPE_ID

and rcl.customer_trx_line_id = rctg.customer_trx_line_id

and hp.PARTY_ID = hca.PARTY_ID

and rct.bill_to_customer_id = hca.cust_account_id

and rct.customer_trx_id = apsa.customer_trx_id

AND XTE.SOURCE_ID_INT_1(+)=rct.customer_trx_id

and rct.TERM_ID = rtt.TERM_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 rctg.GL_DATE BETWEEN (

        SELECT

            MIN(A.START_DATE)

        FROM

            GL_PERIODS A

        WHERE

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

    )

    AND

    (SELECT

            MAX(A.END_DATE)

        FROM

            GL_PERIODS A

        WHERE

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

    )

AND (XTE.ENTITY_CODE ='TRANSACTIONS')

and hca.account_number between nvl(:P_NUM_FROM,hca.account_number) and nvl(:P_NUM_TO,hca.account_number)

and rct.org_id = nvl(:P_ORG_ID,rct.org_id)

group by rct.customer_trx_id)

)

select OPENING_BALANCE,INVOICE_AMOUNT,PAID_AMOUNT,DUE_AMOUNT from a,b



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

API SOAP to update AR DFF (oracle fusion) includes debit memo

API SOAP to update AR DFF (oracle fusion) includes debit memo WSDL  =>    https://servername/fscmService/ErpObjectDFFUpdateService?WSDL Sample Request body <!-- WSDL https://<hostName>:443/fscmService/ErpObjectDFFUpdateService --> <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:typ="http://xmlns.oracle.com/apps/financials/commonModules/shared/model/erpIntegrationService/types/" xmlns:erp="http://xmlns.oracle.com/apps/financials/commonModules/shared/model/erpIntegrationService/">    <soapenv:Header/>    <soapenv:Body>       <typ:updateDffEntityDetailsAsync>          <!--Optional:-->          <typ:operationMode>SINGLE</typ:operationMode>          <!--Optional:-->          <typ:object>             <!--Option...
Read more