Tuesday 30 January 2018

Oracle Apps R12 Receiving Module Complete Details of the Tables and its Details

Oracle Apps R12 Receiving Module Complete Details of the Tables and its Details

Oracle Apps R12 Receiving Module Complete Details of the Tables and its Details


RCV_VRC_TXS_V


RCH_SHIPMENT_HEADERS  (important coloumns SHIPMENT_HEADER_ID,VENDOR_ID,RECEIPT_NUM,EMPLOYEE_ID,org_id)
 
shipment_header_id relates this table to RCV_TRANSACTIONS table )

RCV_SHIPMENT_LINES  

(this table describes the whole receiving like   line1  we receive that line2 we receive that by this table using po_header_id,po_line_id we join with AP_INVOICE_DISTRIBUTIONS_ALL table)
 (important cloumns are SHIPMENT_LINE_ID,SHIPMENT_HEADER_ID,LINE_NUM,QUANTITY_SHIPPED,QUANTITY_RECEIVED,ITEM_DESCRIPTION,ITEM_ID,SHIPMENT_LINE_STATUS_CODE(full or half rec),PO_HEADER_ID,PO_LINE_ID,TO_ORGANIZATION_ID)


RCV_TRANSACTIONS

---(this table joins with rcv_shipment_headers,rcv_shipment_lines THIS SHOWS ALL THE STATUS OF ONE RECEVING OR RECEIPT NO  LIKE NOW THEY R RECEIVE ,NOW THER ARE DELIVER,CORECT,INVENTORY_ITEM_ID  links with MTL_SYSTEM_ITEMS_B invetory_item_id)
('RECEIVE','CORRECT','RETURN TO VENDOR','RETURN TO RECEIVING') THESE ARE THE TYPES COMMON

(transaction_id,SHIPMENT_HEADER_ID,UNIT_OF_MEASURE,QUANTITY,TRANSACTION_DATE,PO_HEADER_ID,PO_LINE_ID,PO_LINE_LOCATION_ID,PO_DISTRIBUTION_ID,DESTINATION_TYPE_CODE(EXP,RCV,INV)


RCV_RECEIVING_SUB_LEDGER

  (THIS TABLE SHOWS THE ACCOUNTING OF EACH TRANSACTION ID LIKE WHEN ITEM RECEIVED THEN WHICH ACCOUNT CR AND DR)
 (IMPORTANT COLOUMNS -  RCV_TRANSACTION_ID(IT LINKS RCV_TRANSACTIONS TABLE TO THIS TABLE),SET_OF_BOOKS_ID,CODE_COMBINATION_ID,ACCOUNTING_DATE,ACCOUNTED_DR,CR,PERIOD_NAME,RCV_SUB_LEDGER_ID)

BUT THE LIMITATION OF ABOVE TABLE IS THAT IT SHOWS THE ACCOUNTING OF THOSE TRANSACTION WHICH ARE EXPENSE IF THEY ARE INVENTORY THEN IT SHOWS THE ACCOUNTING OF ONLY RECEVING TRANSACTION NOT SHOWING THE TRANSACTION OF DELIVER TYPE TRANSACTION )

FOR SHOWING THE ACCOUNTING OF DELVER TRANSACTION IN INVENTORY WE HAVE TO USE THIS BELOW TABLE

MTL_TRANSACTION_ACCOUNTS(REFRENCE ACCOUNT,BASE_TRANSACTION_VALUE)


THIS TABLE STORE THE INFORMATION OF ACCOUNTING OF INVENTORI ITEM DELIVER.
LIKE TWO LINES FOR 1 TRANSCTION DELIVER

MTL_TRANSACTION_ACCOUNTS  WE REACH TO THIS TABLE BY MTL_MATERIAL_TRANSACTIONS(TRANSACTION_ID)  AND WE REACH TO THIS TABLE BY RCV_TRANSACTIONS(RCV_TRANSACTION_ID)

MTL_TRANSACTION_ACCOUNTS HAS COLOUMN REFRENCE ACCOUNT WHICH ARE ACCOUNTING CODE,BASE_TRANSACTION_VALUE  AMOUNT


EXAMPLE (WHEN WE RECEIVE THE MATERIAL RECEIVING ACCOUNT DEBIT AND ACCRUAL ACCOUNT CREDIT 

WHEN WE DELIVER THE MATERIAL  RECEIVING ACCOUNT CREDIT AND DESTINATION ACCOUNT TO WHICH ITEM BELONG DEBIT 

AND WEN WE PASS THE BILL OF THIS MATERIAL IT DEBITS THE ACRUAL ACCOUNT)

if you found this post helpful and you liked it then follow this blog to get notifications for the upcoming posts.

JAI_RCV_JOURNAL_ENTRIES(TRANSACTION_ID)


  THIS IS USED FOR SHOWING THE TAX ACCOUNTING OF PARTICULAR ONE MRN NO




Item Master Sql Query in Oracle Apps R12

Item Master Sql Query in Oracle Apps R12

In this post , We will discuss about the item master sql query in oracle apps r12. This query will help to extract the inventory item details in oracle apps .We will get the complete item master informations having all the item parameters using this sql query. Please find below the detail item master sql query and tables used in this sql query in oracle apps r12.

Item Master Sql Query in Oracle Apps R12

Important Tables Used by Item Master Sql Query in Oracle Apps R12

1.mtl_system_items
2.mtl_secondary_locators
3.mtl_item_locations
4.per_all_people_f
5.mtl_item_sub_inventories
6.org_organization_definitions
7.mtl_item_categories
8.mtl_categories_vl
9.mtl_category_sets


Detail Item Master Sql Query in Oracle Apps R12


SELECT msi.segment1, msi.segment2, msi.segment3, msi.segment4, msi.segment5,
msi.segment6, msi.segment7, msi.description, msi.primary_uom_code,
msi.cycle_count_enabled_flag, msi.inspection_required_flag,
msi.attribute14 legacy_code, msi.attribute13 legacy_description,
msi.stock_enabled_flag stockable,
msi.mtl_transactions_enabled_flag transactable, msi.lot_control_code,
msi.location_control_code, msi.purchasing_enabled_flag,
msi.must_use_approved_vendor_flag, msi.buyer_id,pap.full_name buyer_name,
msi.list_price_per_unit,
(msi.expense_account) expense_account,
msi.receiving_routing_id, misi.secondary_inventory subinventory,
mil.segment1 loc_seg1, mil.segment11 loc_seg11,
mil.segment2 loc_seg2, mil.segment3 loc_seg3, mil.segment4 loc_seg4,
mil.segment5 loc_seg5, mil.segment6 loc_seg6, mil.segment7 loc_seg7,
mil.segment8 loc_seg8, mil.segment9 loc_seg9,
mil.segment10 loc_seg10, ood.organization_name,
mcv.segment1 cat_seg1, mcv.segment2 cat_seg2, mcv.segment3 cat_seg3,
mcv.segment4 cat_seg4, mcv.segment5 cat_seg5, mcv.segment6 cat_seg6,
mcv.segment7 cat_seg7, mcv.segment8 cat_seg8, mcv.segment9 cat_seg9,
mcv.segment10 cat_seg10, mcv.segment11 cat_seg11,
mcv.segment12 cat_seg12, mcv.segment13 cat_seg13
FROM mtl_system_items msi,
mtl_secondary_locators msl,
mtl_item_locations mil,
per_all_people_f pap,
mtl_item_sub_inventories misi,
org_organization_definitions ood,
mtl_item_categories mic,
mtl_categories_vl mcv,
mtl_category_sets mcs
WHERE msi.inventory_item_id = msl.inventory_item_id(+)
AND msi.organization_id = msl.organization_id(+)
AND msl.secondary_locator = mil.inventory_location_id(+)
AND msl.organization_id = mil.organization_id(+)
AND pap.person_id(+) = msi.buyer_id
AND msi.inventory_item_id = misi.inventory_item_id(+)
AND msi.organization_id = misi.organization_id(+)
AND ood.organization_id = msi.organization_id
AND mic.inventory_item_id = msi.inventory_item_id
AND mic.organization_id = msi.organization_id
AND mcs.category_set_id = mic.category_set_id
AND mcs.structure_id = mcv.structure_id
AND mcv.category_id = mic.category_id
AND msi.inventory_item_status_code = 'Active'

Item Master Sql Query in Oracle Apps R12

PLSQL Function To Get All customer Phone Number in Oracle Apps R12


PLSQL Function To Get All customer Phone Number in Oracle Apps R12



CREATE OR REPLACE FUNCTION APPS.CUSTOMER_ALL_PHONES(c_ID NUMBER)
RETURN VARCHAR2
IS
co_owner varchar2(999);
abc varchar2(100);
cursor c1 is SELECT '+'||ltrim(PHONE_COUNTRY_CODE||'-'||PHONE_AREA_CODE||'-'||PHONE_NUMBER,'-') PHONE_NUMBER_1
  FROM hz_contact_points
 WHERE owner_table_name = 'HZ_PARTIES'
 and status = 'A'
 and CONTACT_POINT_TYPE='PHONE'
   AND owner_table_id IN (SELECT party_id
                            FROM hz_cust_accounts
                           WHERE cust_account_id = c_id)
UNION
SELECT '+'||ltrim(PHONE_COUNTRY_CODE||'-'||PHONE_AREA_CODE||'-'||PHONE_NUMBER,'-') PHONE_NUMBER_2
  FROM hz_contact_points
  WHERE CONTACT_POINT_TYPE='PHONE'
  AND owner_table_name = 'HZ_PARTY_SITES'
  and status = 'A'
  AND owner_table_id IN (SELECT party_site_id
                            FROM hz_cust_acct_sites_all
                           WHERE cust_account_id = c_id)
UNION
SELECT '+'||ltrim(PHONE_COUNTRY_CODE||'-'||PHONE_AREA_CODE||'-'||PHONE_NUMBER,'-') PHONE_NUMBER_3
  FROM hz_contact_points
  WHERE owner_table_name = 'HZ_PARTIES'
  and status = 'A'
  and CONTACT_POINT_TYPE='PHONE'
  AND owner_table_id IN (
                        SELECT party_id
                          FROM hz_cust_account_roles
                         WHERE role_type = 'CONTACT'
                         AND cust_account_id = c_id);
begin
for i in c1 loop
     select i.RAW_PHONE_NUMBER into abc from dual;
     co_owner := co_owner||', '||abc;
     abc := null;
end loop;
return(co_owner);
end;

 

Cash Bank Book Report Complete Logic in Oracle apps R12.

Cash Bank Book Report Complete Logic in Oracle apps R12

In this post , We will be discuss about the Cash Bank Book Report Complete Logic in Oracle apps R12. I will share the complete logic to developed the cash bank book report in oracle apps r12. I will share the detail tables list used to developed the cash bank book report. Here below is the complete logic about Cash Bank Book Report Complete Logic in Oracle apps R12. 

Cash Bank Book Report Complete Logic in Oracle apps R12.


Hi Friends, In this post , I will share you the logic to create Cash Bank Book report in Oracle apps R12.

Steps to Developed the Cash Bank Book Report Complete Logic in Oracle apps R12


Step1:- You have to use first query for Payments. In the payment the main table will use is ap_checks_All

There will two columns in the report Payment and Receipt.
we will pick all the transactions/Checks which are clear and negotiable,reconciled,RECONCILED UNACCOUNTED,CLEARED BUT UNACCOUNTED.

The amount in ap_checks_all are mostly of +amt which is in payment_side
THE PAYMENT TYPE IN (P,OR NULL,M)

The amount in -ve are goes in receipt side (some time we take refund) so for those amt payment type flag in(R REFUND)

We will use Date parameter between  CHECK_DATE for table ap_checks_all

Other Table will be used in this as below

          ce_bank_accounts cba ,
         ce_bank_acct_uses_all cbu

-----------------------------------------------------------------------------------------------------------------------------------------------
Step2:- We will use Second query as a union with first. We will again use the second query from Payments(Ap_Checks_All)

This Query will Handle the Void Checks

We will  pick checks from Ap_checks_all with Status 'Void' and Data Parameter will work on Check Date

Payment Type similar as Above

Payments column take Amount simply from Ap_Checks_all Table AND Receipt column will be Null.

STATUS=VOIDED

TABLES Will Use
ap_checks_all apc,
          ce_bank_accounts cba ,
          ce_bank_acct_uses_all cbu


Step3:- We will use Third query as a union with first and Second. We will again use the Third query from Payments(AP_PAYMENT_HISTORY_ALL)

In this we use Special Table AP_PAYMENT_HISTORY_ALL

We will simple pick the check of STATUS=VOID

But here in this query we will put Data parameter onVOID_DATE

AND ALSO USE TRANSACTION_TYPE IN  ('PAYMENT CANCELLED','REFUND CANCELLED') OF TABLE AP_PAYMENT_HISTORY_ALL.

AMOUNT WE TAKE IN PAYMENTS and RECEIPTS columns are

RECEIPTS column will take Amount simple from Check Table and Payment Column will Null.

Step4:- This Fourth Query we will use for Those Invoices Which Use Bank GL Account in Invoice Distribution.

---------------------------------------------------------------------------------
FOR THOSE INVOICES WHICH HIT BANK IN DISTRIBUTIONS.

IN THIS WE LINK THE DIST_CODE_COMBINATION ID TO BANK ASSET CODE COMBIANTION ID

TABLES USED ARE
 ap_invoice_distributions_all apid,
        ap_invoices_all api,
        po_vendors POV,
         gl_code_combinations gcc,
         ce_bank_accounts cba 

In this Your Check Status will Always will Cleared.

if you found this post helpful and you liked it then follow this blog to get notifications for the upcoming posts.

Amount We will Take in Payment and Receipts are

Basically in Invoice Distribution amount is Negative so the amount Which is Negative will go to Payment Column and Positive Amount will go to Receipt column.



Cash Bank Book Report Complete Logic in Oracle apps R12.



GL Account Analysis SQL Query in Oracle Apps r12

GL Account Analysis SQL Query in Oracle Apps r12

In this post , we will be discuss about the GL Account Analysis SQL Query in Oracle Apps r12. This query will help to developed the Detail GL account analysis report in oracle. We will get the gl drill down report using this sql query. GL account analysis report , helps to extract the data from GL to Subledger in oracle apps. This is one of the important sql query and important report usually used in the oracle apps. Please find below the detail sql query of gl account analysis in oracle apps and the important tables used to developed the gl account analysis.

Important Tables Used By GL Account Analysis SQL Query in Oracle Apps r12

1.GL_CODE_COMBINATIONS
2.GL_JE_LINES
3.GL_JE_HEADERS
4.GL_JE_BATCHES
5.GL_IMPORT_REFERENCES
6.XLA_AE_LINES
7.XLA_TRANSACTION_ENTITIES
8.XLA_AE_HEADERS
9.AP_INVOICES_ALL
10.ap_supplier_sites_all
11.PO_VENDORS

GL Account Analysis SQL Query in Oracle Apps r12

Detail GL Account Analysis SQL Query in Oracle Apps r12


select JE_NAME,
a.LAST_UPDATE_DATE creation_date,
       JE_CATEGORY,
       MRN_NO,
       a.DESCRIPTION,DOC_SEQUENCE_VALUE,EFFECTIVE_DATE,CURRENCY_CODE,VENDOR_NAME, VENDOR_SITE_CODE,
       INVOICE_NUM,PO,(SELECT MAX(AP.INVOICE_TYPE_LOOKUP_CODE) FROM AP_INVOICES_ALL AP
                    WHERE INVOICE_NUM = A.INVOICE_NUM) INVOICE_TYPE_LOOKUP_CODE,
                    INVOICE_DATE,GL_DATE,ENTERED_DR,ENTERED_CR,DR Func_Debit,CR Func_Credit,BAL,
       JE_SOURCE,PERIOD,NAME,A.CODE_COMBINATION_ID,
       GCC.SEGMENT1 company,apps.gl_flexfields_pkg.get_description_sql(5446,1,segment1)COMPANY_DESCRIPTION,
       GCC.SEGMENT2 LOCATION,apps.gl_flexfields_pkg.get_description_sql(5446,2,segment2) LOCATION_DESCRIPTION,
       GCC.SEGMENT3 CITY,apps.gl_flexfields_pkg.get_description_sql(5446,3,segment3) CITY_DESCRIPTION,
       GCC.SEGMENT4 COSTCENTER,apps.gl_flexfields_pkg.get_description_sql(5446,4,segment4) COSTCENTRE_DESCRIPTION,
       GCC.SEGMENT5 ACCOUNT,apps.gl_flexfields_pkg.get_description_sql(5446,5,segment5) ACCOUNT_DESCRIPTION,
       GCC.SEGMENT8 FUTURE1,apps.gl_flexfields_pkg.get_description_sql(5446,8,segment8) FUTURE1_DESCRIPTION,
       GCC.SEGMENT9 FUTURE2,apps.gl_flexfields_pkg.get_description_sql(5446,9,segment9) FUTURE2_DESCRIPTION
from
(
SELECT  JE_NAME,
LAST_UPDATE_DATE,
        JE_CATEGORY,
        MRN_NO,
        DESCRIPTION,
        DOC_SEQUENCE_VALUE,
        EFFECTIVE_DATE,
        CURRENCY_CODE,
        VENDOR_NAME,
        VENDOR_SITE_CODE,
        INVOICE_NUM,
        PO,
        INVOICE_DATE,
        GL_DATE ,
        SUM(nvl(ENTERED_DR,0) ) ENTERED_DR,
        SUM(nvl(ENTERED_DR,0) ) ENTERED_CR,
        SUM(nvl(DR,0) ) DR,
        SUM(nvl(CR,0) ) CR,
        SUM(bal) bal,
        JE_SOURCE,
        PERIOD,
        NAME,
        CODE_COMBINATION_ID,
        CODE_COMINATION,
        PFROM_DATE,
        PTO_DATE,
        TYPE,
        DES
 FROM(
SELECT   JEH.NAME JE_NAME,
jeh.LAST_UPDATE_DATE,
        JEH.JE_CATEGORY,
        nvl((select receipt_num from rcv_shipment_headers
where shipment_header_id=(select shipment_header_id from rcv_transactions
where transaction_id=(SELECT MAX(RCV_TRANSACTION_ID) FROM AP_INVOICE_distributions_ALL
WHERE INVOICE_ID=AIA.INVOICE_ID
AND AMOUNT=XAL.ACCOUNTED_DR))),(select receipt_num from rcv_shipment_headers
where shipment_header_id=(select shipment_header_id from rcv_transactions
where transaction_id=(SELECT MAX(RCV_TRANSACTION_ID) FROM AP_INVOICE_distributionS_ALL
WHERE INVOICE_ID=AIA.INVOICE_ID)))) MRN_NO,
        AIA.DESCRIPTION,
        AIA.DOC_SEQUENCE_VALUE,
        JEL.EFFECTIVE_DATE,
        jeh.CURRENCY_CODE,
        POV.VENDOR_NAME,
        PSSA.VENDOR_SITE_CODE,
        AIA.INVOICE_NUM,
         (select segment1 from po_headers_all
        where po_header_id=(select max(po_header_id) from ap_invoice_lines_all
        where invoice_id=aia.invoice_id)) PO,
        AIA.INVOICE_DATE,
        AIA.GL_DATE ,
       nvl(XAL.ENTERED_DR,0)  ENTERED_DR,
     nvl(XAL.ENTERED_CR,0)  ENTERED_CR,
       nvl(XAL.ACCOUNTED_DR,0)  DR,
        nvl(XAL.ACCOUNTED_CR,0)  CR,
       nvl(XAL.ACCOUNTED_DR,0) -nvl(XAL.ACCOUNTED_CR,0)  bal,
       JE_SOURCE,
        TO_CHAR(JEL.EFFECTIVE_DATE,'MON-YY') PERIOD,
        'AP_INVOICE' NAME,
        GCC.CODE_COMBINATION_ID,GCC.SEGMENT1||'.'||GCC.SEGMENT2||'.'||GCC.SEGMENT3||'.'||GCC.SEGMENT4||'.'||GCC.SEGMENT5||'.'|| GCC.SEGMENT6||'.'|| GCC.SEGMENT7||'.'|| GCC.SEGMENT8||'.'|| GCC.SEGMENT9 CODE_COMINATION,
        decode(:FROM_DATE,null,'ALL',:FROM_DATE) PFROM_DATE,
        decode(:TO_DATE,null,'ALL',:TO_DATE) PTO_DATE,
        NULL TYPE,
        NULL DES
FROM
        GL_CODE_COMBINATIONS GCC,
        GL_JE_LINES JEL,
        GL_JE_HEADERS JEH,
        GL_JE_BATCHES JEB,
        GL_IMPORT_REFERENCES GIR,
        XLA.XLA_AE_LINES XAL,
        XLA.XLA_TRANSACTION_ENTITIES XEH,
        XLA.XLA_AE_HEADERS XAH
        ,AP_INVOICES_ALL AIA,
        ap_supplier_sites_all PSSA,
        PO_VENDORS POV--,
       -- fnd_flex_values_vl a,
        --fnd_flex_values_vl b
WHERE   JEH.JE_HEADER_ID=JEL.JE_HEADER_ID
        AND JEB.JE_BATCH_ID=JEH.JE_BATCH_ID
        AND JEL.CODE_COMBINATION_ID=GCC.CODE_COMBINATION_ID
        AND JEH.STATUS='P'
        AND TO_DATE('01'||'-'|| JEL.PERIOD_NAME)>=nvl(TO_DATE('01'||'-'||:FROM_DATE),TO_DATE('01'||'-'|| JEL.PERIOD_NAME))
        AND XEH.ENTITY_CODE='AP_INVOICES'
        AND   TO_DATE('01'||'-'|| JEL.PERIOD_NAME)<=nvl(TO_DATE('01'||'-'||:TO_DATE),TO_DATE('01'||'-'|| JEL.PERIOD_NAME))
        AND GIR.JE_HEADER_ID=JEH.JE_HEADER_ID
        AND GIR.JE_LINE_NUM=JEL.JE_LINE_NUM
        and jeh.LAST_UPDATE_DATE>:creation
        AND XAL.GL_SL_LINK_ID =GIR.GL_SL_LINK_ID
        AND AIA.VENDOR_ID=PSSA.VENDOR_ID
        AND AIA.VENDOR_SITE_ID=PSSA.VENDOR_SITE_ID
        AND XAL.AE_HEADER_ID=XAH.AE_HEADER_ID
        AND XAH.ENTITY_ID=XEH.ENTITY_ID
        AND AIA.INVOICE_ID=XEH.SOURCE_ID_INT_1
        AND POV.VENDOR_ID=AIA.VENDOR_ID
     --   AND (SELECT DISTINCT SEGMENT1 FROM PO_HEADERS_ALL
--WHERE PO_HEADER_ID IN (SELECT PO_HEADER_ID FROM PO_DISTRIBUTIONS_ALL
--WHERE PO_DISTRIBUTION_ID IN(SELECT PO_DISTRIBUTION_ID FROM AP_INVOICE_DISTRIBUTIONS_ALL
--WHERE INVOICE_ID=AIA.INVOICE_ID))) IS NOT NULL
)
GROUP BY JE_NAME,
LAST_UPDATE_DATE,
        JE_CATEGORY,
         MRN_NO,
        DESCRIPTION,
        DOC_SEQUENCE_VALUE,
        EFFECTIVE_DATE,
        CURRENCY_CODE,
        VENDOR_NAME,
        VENDOR_SITE_CODE,
        INVOICE_NUM,
         PO,
        INVOICE_DATE,
        GL_DATE ,
        JE_SOURCE,
        PERIOD,
        NAME,
        CODE_COMBINATION_ID,
        CODE_COMINATION,
        PFROM_DATE,
        PTO_DATE,
        TYPE,
        DES
        union all
------------------------------AP_PAYMENT----------------------------------
SELECT  JEH.NAME JE_NAME,
jeh.LAST_UPDATE_DATE,
        JEH.JE_CATEGORY,
        null MRN_NO,
        JEL.DESCRIPTION,
        AIA.DOC_SEQUENCE_VALUE,
        JEL.EFFECTIVE_DATE,
        jeh.CURRENCY_CODE,
        POV.VENDOR_NAME,
        AIA.VENDOR_SITE_CODE,
        TO_CHAR(AIA.CHECK_NUMBER),
        NULL PO,
        AIA.CHECK_DATE,
        AIA.CHECK_DATE GL_DATE,
        SUM(nvl(XAL.ENTERED_DR,0) ) ENTERED_DR,
        SUM(nvl(XAL.ENTERED_CR,0) ) ENTERED_CR,
        SUM(nvl(XAL.ACCOUNTED_DR,0) ) DR,
        SUM(nvl(XAL.ACCOUNTED_CR,0) ) CR,
        SUM(nvl(XAL.ACCOUNTED_DR,0) )-SUM(nvl(XAL.ACCOUNTED_CR,0) ) bal,
        JE_SOURCE,
        TO_CHAR(JEL.EFFECTIVE_DATE,'MON-YY') PERIOD,
        'AP_PAYMENT' NAME,
        GCC.CODE_COMBINATION_ID,GCC.SEGMENT1||'.'||GCC.SEGMENT2||'.'||GCC.SEGMENT3||'.'||GCC.SEGMENT4||'.'||GCC.SEGMENT5||'.'|| GCC.SEGMENT6||'.'|| GCC.SEGMENT7||'.'|| GCC.SEGMENT8||'.'|| GCC.SEGMENT9  CODE_COMINATION,
        decode(:FROM_DATE,null,'ALL',:FROM_DATE) PFROM_DATE,
        decode(:TO_DATE,null,'ALL',:TO_DATE) PTO_DATE,
        NULL TYPE,
        NULL DES
FROM
        GL_CODE_COMBINATIONS GCC,
        GL_JE_LINES JEL,
        GL_JE_HEADERS JEH,
        GL_JE_BATCHES JEB,
        GL_IMPORT_REFERENCES GIR,
        XLA.XLA_AE_LINES XAL,
        XLA.XLA_TRANSACTION_ENTITIES XEH,
        XLA.XLA_AE_HEADERS XAH
        ,AP_CHECKS_ALL AIA,
        PO_VENDORS POV
        WHERE  JEH.JE_HEADER_ID=JEL.JE_HEADER_ID
        AND JEB.JE_BATCH_ID=JEH.JE_BATCH_ID
        AND JEL.CODE_COMBINATION_ID=GCC.CODE_COMBINATION_ID
        AND JEH.STATUS='P'
        AND XEH.ENTITY_CODE='AP_PAYMENTS'
        and jeh.LAST_UPDATE_DATE>:creation
        AND TO_DATE('01'||'-'|| JEL.PERIOD_NAME) >=nvl( TO_DATE('01'||'-'||:FROM_DATE),TO_DATE('01'||'-'|| JEL.PERIOD_NAME))
        AND  TO_DATE('01'||'-'|| JEL.PERIOD_NAME) <=nvl(TO_DATE('01'||'-'||:TO_DATE),TO_DATE('01'||'-'|| JEL.PERIOD_NAME))
        AND GIR.JE_HEADER_ID=JEH.JE_HEADER_ID
        AND GIR.JE_LINE_NUM=JEL.JE_LINE_NUM
        AND XAL.GL_SL_LINK_ID =GIR.GL_SL_LINK_ID
        AND XAL.AE_HEADER_ID=XAH.AE_HEADER_ID
        AND XAH.ENTITY_ID=XEH.ENTITY_ID
        AND AIA.CHECK_ID=XEH.SOURCE_ID_INT_1
        AND POV.VENDOR_ID=AIA.VENDOR_ID
GROUP BY JEH.NAME,
jeh.LAST_UPDATE_DATE,
        JEH.JE_CATEGORY,
        JEL.DESCRIPTION,
        AIA.DOC_SEQUENCE_VALUE,
        JEL.EFFECTIVE_DATE,
        jeh.CURRENCY_CODE,
        POV.VENDOR_NAME,
        AIA.VENDOR_SITE_CODE,
        JE_SOURCE,
        AIA.CHECK_NUMBER,
        AIA.CHECK_DATE,
            AIA.CHECK_DATE,
        TO_CHAR(JEL.EFFECTIVE_DATE,'MON-YY'),
        GCC.CODE_COMBINATION_ID,GCC.SEGMENT1||'.'||GCC.SEGMENT2||'.'||GCC.SEGMENT3||'.'||GCC.SEGMENT4||'.'||GCC.SEGMENT5||'.'|| GCC.SEGMENT6||'.'|| GCC.SEGMENT7||'.'|| GCC.SEGMENT8||'.'|| GCC.SEGMENT9  
UNION all
---------------------------General_Ledger------------------------------
SELECT 
        JEH.NAME JE_NAME,
        jeh.LAST_UPDATE_DATE,
        JEH.JE_CATEGORY,
         null MRN_NO,
        JEL.DESCRIPTION,
        jeh.DOC_SEQUENCE_VALUE,
        JEL.EFFECTIVE_DATE,
        jeh.CURRENCY_CODE,
        vendor_name(jeh.ACCRUAL_REV_JE_HEADER_ID,:from_date,:to_date) VENDOR_NAME,
        null  VENDOR_SITE_CODE,
        TO_CHAR(JEH.DOC_SEQUENCE_VALUE),
        NULL PO,
        JEH.CREATION_DATE,
        JEH.DEFAULT_EFFECTIVE_DATE GL_DATE,
        SUM(nvl(JEL.ENTERED_DR,0) ) ENTERED_DR,
        SUM(nvl(JEL.ENTERED_CR,0) ) ENTERED_CR,
        SUM(nvl(JEL.ACCOUNTED_DR,0) )DR,
        SUM(nvl(JEL.ACCOUNTED_CR,0) ) CR,
        SUM(nvl(jel.ACCOUNTED_DR,0) )-SUM(nvl(jel.ACCOUNTED_CR,0) ) bal,
        JE_SOURCE,
        TO_CHAR(JEL.EFFECTIVE_DATE,'MON-YY') PERIOD,
        'GL-Manual' NAME,
        GCC.CODE_COMBINATION_ID,GCC.SEGMENT1||'.'||GCC.SEGMENT2||'.'||GCC.SEGMENT3||'.'||GCC.SEGMENT4||'.'||GCC.SEGMENT5||'.'|| GCC.SEGMENT6||'.'|| GCC.SEGMENT7||'.'|| GCC.SEGMENT8||'.'|| GCC.SEGMENT9,
        decode(:FROM_DATE,null,'ALL',:FROM_DATE) PFROM_DATE,
        decode(:TO_DATE,null,'ALL',:TO_DATE) PTO_DATE,
        NULL TYPE  ,
        NULL DES    
FROM
        GL_CODE_COMBINATIONS GCC,
        GL_JE_LINES JEL,
        GL_JE_HEADERS JEH,
        GL_JE_BATCHES JEB   
WHERE 
        JEH.JE_HEADER_ID=JEL.JE_HEADER_ID
        AND JEB.JE_BATCH_ID=JEH.JE_BATCH_ID
        and jeh.LAST_UPDATE_DATE>:creation
        AND JEL.CODE_COMBINATION_ID=GCC.CODE_COMBINATION_ID
        AND JEH.STATUS='P'
        AND TO_DATE('01'||'-'|| JEL.PERIOD_NAME) >=nvl( TO_DATE('01'||'-'||:FROM_DATE),TO_DATE('01'||'-'|| JEL.PERIOD_NAME))
        AND  TO_DATE('01'||'-'|| JEL.PERIOD_NAME) <=nvl(TO_DATE('01'||'-'||:TO_DATE),TO_DATE('01'||'-'|| JEL.PERIOD_NAME))
        AND JEH.JE_HEADER_ID NOT IN (SELECT GIR.JE_HEADER_ID FROM  GL_IMPORT_REFERENCES GIR)
GROUP BY JEH.NAME,
jeh.LAST_UPDATE_DATE,
        JEH.JE_CATEGORY,
        JEL.DESCRIPTION,
        jeh.DOC_SEQUENCE_VALUE,
        JEL.EFFECTIVE_DATE,
        jeh.CURRENCY_CODE,
        JE_SOURCE,
        JEL.EFFECTIVE_DATE,
         jeh.ACCRUAL_REV_JE_HEADER_ID,
        TO_CHAR(JEL.EFFECTIVE_DATE,'MON-YY') ,
        JEH.DOC_SEQUENCE_VALUE,
        JEH.CREATION_DATE,
            JEH.DEFAULT_EFFECTIVE_DATE,
        GCC.CODE_COMBINATION_ID,GCC.SEGMENT1||'.'||GCC.SEGMENT2||'.'||GCC.SEGMENT3||'.'||GCC.SEGMENT4||'.'||GCC.SEGMENT5||'.'|| GCC.SEGMENT6||'.'|| GCC.SEGMENT7||'.'|| GCC.SEGMENT8||'.'|| GCC.SEGMENT9
union all
SELECT 
        JEH.NAME JE_NAME,
        jeh.LAST_UPDATE_DATE,
        JEH.JE_CATEGORY,
        (select receipt_num from rcv_shipment_headers
where shipment_header_id=(select shipment_header_id from rcv_transactions
where transaction_id=jel.REFERENCE_5)) MRN_NO,
        (select ITEM_DESCRIPTION from po_lines_all
where po_header_id=(select po_header_id from rcv_transactions where transaction_id=jel.REFERENCE_5)
and po_line_id=(select po_line_id from rcv_transactions where transaction_id=jel.REFERENCE_5)) DESCRIPTION,
        jeh.DOC_SEQUENCE_VALUE,
        JEL.EFFECTIVE_DATE,
        jeh.CURRENCY_CODE,
        (select vendor_name from po_vendors
       where vendor_id=( select VENDOR_ID from rcv_transactions
        where TRANSACTION_ID=jel.REFERENCE_5)) VENDOR_NAME,
        (select city from po_vendor_sites_all
       where vendor_site_id=( select VENDOR_site_id from rcv_transactions
        where TRANSACTION_ID=jel.REFERENCE_5)) VENDOR_SITE_CODE,
        TO_CHAR(JEH.DOC_SEQUENCE_VALUE),
        NULL PO,
        JEH.CREATION_DATE,
        JEH.DEFAULT_EFFECTIVE_DATE GL_DATE,
        SUM(nvl(JEL.ENTERED_DR,0) ) ENTERED_DR,
        SUM(nvl(JEL.ENTERED_CR,0) ) ENTERED_CR,
        SUM(nvl(JEL.ACCOUNTED_DR,0) )DR,
        SUM(nvl(JEL.ACCOUNTED_CR,0) ) CR,
        SUM(nvl(jel.ACCOUNTED_DR,0) )-SUM(nvl(jel.ACCOUNTED_CR,0) ) bal,
        JE_SOURCE,
        TO_CHAR(JEL.EFFECTIVE_DATE,'MON-YY') PERIOD,
        'Purchasing_India' NAME,
        GCC.CODE_COMBINATION_ID,GCC.SEGMENT1||'.'||GCC.SEGMENT2||'.'||GCC.SEGMENT3||'.'||GCC.SEGMENT4||'.'||GCC.SEGMENT5||'.'|| GCC.SEGMENT6||'.'|| GCC.SEGMENT7||'.'|| GCC.SEGMENT8||'.'|| GCC.SEGMENT9,
        decode(:FROM_DATE,null,'ALL',:FROM_DATE) PFROM_DATE,
        decode(:TO_DATE,null,'ALL',:TO_DATE) PTO_DATE,
        NULL TYPE  ,
        NULL DES    
FROM
        GL_CODE_COMBINATIONS GCC,
        GL_JE_LINES JEL,
        GL_JE_HEADERS JEH,
        GL_JE_BATCHES JEB,
        GL_IMPORT_REFERENCES GIR  
WHERE 
        JEH.JE_HEADER_ID=JEL.JE_HEADER_ID
        AND JEB.JE_BATCH_ID=JEH.JE_BATCH_ID
        AND JEL.CODE_COMBINATION_ID=GCC.CODE_COMBINATION_ID
        and jeh.LAST_UPDATE_DATE>:creation
        AND JEH.STATUS='P'
        and JEH.JE_SOURCE='Purchasing India'
        AND TO_DATE('01'||'-'|| JEL.PERIOD_NAME) >=nvl( TO_DATE('01'||'-'||:FROM_DATE),TO_DATE('01'||'-'|| JEL.PERIOD_NAME))
        AND  TO_DATE('01'||'-'|| JEL.PERIOD_NAME) <=nvl(TO_DATE('01'||'-'||:TO_DATE),TO_DATE('01'||'-'|| JEL.PERIOD_NAME))
        AND GIR.JE_HEADER_ID=JEH.JE_HEADER_ID
        AND GIR.JE_LINE_NUM=JEL.JE_LINE_NUM      
GROUP BY JEH.NAME,
jeh.LAST_UPDATE_DATE,
        JEH.JE_CATEGORY,
        JEL.DESCRIPTION,
        jeh.DOC_SEQUENCE_VALUE,
        JEL.EFFECTIVE_DATE,
        jeh.CURRENCY_CODE,
        jel.REFERENCE_5,
        JE_SOURCE,
        JEL.EFFECTIVE_DATE,
        TO_CHAR(JEL.EFFECTIVE_DATE,'MON-YY') ,
        JEH.DOC_SEQUENCE_VALUE,
        JEH.CREATION_DATE,
            JEH.DEFAULT_EFFECTIVE_DATE,
        GCC.CODE_COMBINATION_ID,GCC.SEGMENT1||'.'||GCC.SEGMENT2||'.'||GCC.SEGMENT3||'.'||GCC.SEGMENT4||'.'||GCC.SEGMENT5||'.'|| GCC.SEGMENT6||'.'|| GCC.SEGMENT7||'.'|| GCC.SEGMENT8||'.'|| GCC.SEGMENT9
        UNION all
     -------------------------------AR_RECEIPT------------------------------------
SELECT  JEH.NAME JE_NAME,
jeh.LAST_UPDATE_DATE,
        JEH.JE_CATEGORY,
         null MRN_NO,
        RE.COMMENTS,
        RE.DOC_SEQUENCE_VALUE,
        JEL.EFFECTIVE_DATE,
        jeh.CURRENCY_CODE,
        RC.CUSTOMER_NAME,
         HL.CITY  VENDOR_SITE_CODE,
        RE.RECEIPT_NUMBER,
        NULL PO,
        XAL.ACCOUNTING_DATE RECEIPT_DATE,
        RE.RECEIPT_DATE GL_DATE,
        SUM(nvl(XAL.ENTERED_DR,0) ) ENTERED_DR,
        SUM(nvl(XAL.ENTERED_CR,0) ) ENTERED_CR, 
        SUM(NVL(XAL.ACCOUNTED_DR,0)) DEBIT,
        SUM(NVL(XAL.ACCOUNTED_CR,0)) CREDIT,
        SUM(nvl(XAL.ACCOUNTED_DR,0) )-SUM(nvl(XAL.ACCOUNTED_CR,0) ) bal,
        JE_SOURCE,
        TO_CHAR(JEL.EFFECTIVE_DATE,'MON-YY') PERIOD,
        'AR_RECEIPT' NAME ,
        GCC.CODE_COMBINATION_ID,GCC.SEGMENT1||'.'||GCC.SEGMENT2||'.'||GCC.SEGMENT3||'.'||GCC.SEGMENT4||'.'||GCC.SEGMENT5||'.'|| GCC.SEGMENT6||'.'|| GCC.SEGMENT7||'.'|| GCC.SEGMENT8||'.'|| GCC.SEGMENT9 ,
        decode(:FROM_DATE,null,'ALL',:FROM_DATE) PFROM_DATE,
        decode(:TO_DATE,null,'ALL',:TO_DATE) PTO_DATE,
        NULL TYPE,
        NULL DES
FROM
        AR_CASH_RECEIPTS_ALL  RE,
        XLA.XLA_TRANSACTION_ENTITIES XEH,
        XLA_AE_LINES XAL,
        XLA_AE_HEADERS XAH,
        GL_CODE_COMBINATIONS GCC,
        GL_IMPORT_REFERENCES GIR,
        AR_CUSTOMERS RC,
        GL_JE_LINES JEL,
        GL_JE_HEADERS JEH,
        GL_JE_BATCHES JEB,
        hz_cust_site_uses_all HCSU,
        hz_cust_acct_sites_all HCSU1,
        hz_party_sites HPS,
        HZ_LOCATIONS HL
WHERE
        RE.CASH_RECEIPT_ID=XEH.SOURCE_ID_INT_1
        AND XAL.LEDGER_ID=XEH.LEDGER_ID
        AND XEH.ENTITY_ID=XAH.ENTITY_ID
        and jeh.LAST_UPDATE_DATE>:creation
        AND XAH.AE_HEADER_ID=XAL.AE_HEADER_ID
        AND GCC.CODE_COMBINATION_ID=XAL.CODE_COMBINATION_ID
       AND TO_DATE('01'||'-'|| JEL.PERIOD_NAME) >=nvl( TO_DATE('01'||'-'||:FROM_DATE),TO_DATE('01'||'-'|| JEL.PERIOD_NAME))
        AND  TO_DATE('01'||'-'|| JEL.PERIOD_NAME) <=nvl(TO_DATE('01'||'-'||:TO_DATE),TO_DATE('01'||'-'|| JEL.PERIOD_NAME))
        AND XEH.ENTITY_CODE='RECEIPTS'
        AND GIR.JE_HEADER_ID=JEH.JE_HEADER_ID
        and RE.CUSTOMER_SITE_USE_ID=HCSU.SITE_USE_ID
        AND HCSU.CUST_ACCT_SITE_ID=HCSU1.CUST_ACCT_SITE_ID
        AND RE.PAY_FROM_CUSTOMER=HCSU1.CUST_ACCOUNT_ID
        AND HCSU1.PARTY_SITE_ID=HPS.PARTY_SITE_ID
        AND HPS.LOCATION_ID=HL.LOCATION_ID
        AND GIR.JE_LINE_NUM=JEL.JE_LINE_NUM
        AND XAL.GL_SL_LINK_ID =GIR.GL_SL_LINK_ID
        AND JEH.STATUS='P'
        AND NVL(RE.PAY_FROM_CUSTOMER,11)=RC.CUSTOMER_ID(+)
        AND JEH.JE_HEADER_ID=JEL.JE_HEADER_ID
        AND JEB.JE_BATCH_ID=JEH.JE_BATCH_ID
        AND JEL.CODE_COMBINATION_ID=GCC.CODE_COMBINATION_ID
        AND NVL(XAL.ACCOUNTED_DR,0)-NVL(XAL.ACCOUNTED_CR,0)<>0
GROUP BY JEH.NAME,
jeh.LAST_UPDATE_DATE,
        JEH.JE_CATEGORY,
        RE.COMMENTS,
        RE.DOC_SEQUENCE_VALUE,
        JEL.EFFECTIVE_DATE,
        jeh.CURRENCY_CODE,
        RC.CUSTOMER_NAME,
      HL.CITY,
        RE.RECEIPT_NUMBER,
        XAL.ACCOUNTING_DATE,
         RE.RECEIPT_DATE,
        JE_SOURCE,
        TO_CHAR(JEL.EFFECTIVE_DATE,'MON-YY'),
        GCC.CODE_COMBINATION_ID,GCC.SEGMENT1||'.'||GCC.SEGMENT2||'.'||GCC.SEGMENT3||'.'||GCC.SEGMENT4||'.'||GCC.SEGMENT5||'.'|| GCC.SEGMENT6||'.'|| GCC.SEGMENT7||'.'|| GCC.SEGMENT8||'.'|| GCC.SEGMENT9  
UNION all
-------------------------------AR_TRANSACTION-------------------------------
SELECT  JEH_NAME JE_NAME,
LAST_UPDATE_DATE,
        JE_CATEGORY,
         MRN_NO,
        DESCRIPTION,
        DOC_SEQUENCE_VALUE,
        EFFECTIVE_DATE,
        CURRENCY_CODE,
  CUSTOMER_NAME, VENDOR_SITE_CODE,TRX_NUMBER,PO,TRX_DATE,GL_DATE,ENTERED_DR,ENTERED_DR,DR, CR, bal,JE_SOURCE,PERIOD,NAME,CODE_COMBINATION_ID,CODE_COMINATION,
  PFROM_DATE,PTO_DATE,TYPE,
  (SELECT MAX(RCTL.DESCRIPTION)
   FROM RA_CUSTOMER_TRX_LINES_ALL RCTL
   WHERE RCTL.CUSTOMER_TRX_ID=CUSTOMER_TRX_ID) dES
FROM
(SELECT JEH.NAME JEH_NAME,
jeh.LAST_UPDATE_DATE,
        JEH.JE_CATEGORY,
         null MRN_NO,
        JEL.DESCRIPTION,
        RE.DOC_SEQUENCE_VALUE,
        JEL.EFFECTIVE_DATE,
        jeh.CURRENCY_CODE,
        RC.CUSTOMER_NAME,
        hl.city  VENDOR_SITE_CODE,
        RE.TRX_NUMBER,
         NULL PO,
        RE.TRX_DATE,
        RE.TRX_DATE GL_DATE,
        SUM(nvl(XAL.ENTERED_DR,0) ) ENTERED_DR,
        SUM(nvl(XAL.ENTERED_CR,0) ) ENTERED_CR,
        SUM(NVL(XAL.ACCOUNTED_DR,0)) DR,
        SUM(NVL(XAL.ACCOUNTED_CR,0)) CR,
        SUM(nvl(XAL.ACCOUNTED_DR,0) )-SUM(nvl(XAL.ACCOUNTED_CR,0) ) bal,
        JE_SOURCE,
        TO_CHAR(JEL.EFFECTIVE_DATE,'MON-YY') PERIOD,
        'AR_TRANSACTION' NAME ,
        GCC.CODE_COMBINATION_ID,GCC.SEGMENT1||'.'||GCC.SEGMENT2||'.'||GCC.SEGMENT3||'.'||GCC.SEGMENT4||'.'||GCC.SEGMENT5||'.'|| GCC.SEGMENT6||'.'|| GCC.SEGMENT7||'.'|| GCC.SEGMENT8||'.'|| GCC.SEGMENT9 CODE_COMINATION,
        decode(:FROM_DATE,null,'ALL',:FROM_DATE) PFROM_DATE,
        decode(:TO_DATE,null,'ALL',:TO_DATE) PTO_DATE,
        RCTP.TYPE    ,
        RE.CUSTOMER_TRX_ID
FROM
        RA_CUSTOMER_TRX_ALL RE,
        RA_CUST_TRX_TYPES_ALL RCTP,
        XLA.XLA_TRANSACTION_ENTITIES XEH,
        XLA_AE_LINES XAL,
        XLA_AE_HEADERS XAH,
        GL_CODE_COMBINATIONS GCC,
        GL_IMPORT_REFERENCES GIR,
        AR_CUSTOMERS RC ,
        GL_JE_LINES JEL,
        GL_JE_HEADERS JEH,
        GL_JE_BATCHES JEB,
         hz_cust_site_uses_all HCSU,
        hz_cust_acct_sites_all HCSU1,
        hz_party_sites HPS,
        HZ_LOCATIONS HL
WHERE   RE.CUSTOMER_TRX_ID=XEH.SOURCE_ID_INT_1
        AND XAL.LEDGER_ID=XEH.LEDGER_ID
        AND XEH.ENTITY_ID=XAH.ENTITY_ID
        AND XAH.AE_HEADER_ID=XAL.AE_HEADER_ID
        and jeh.LAST_UPDATE_DATE>:creation
        AND RCTP.CUST_TRX_TYPE_ID=re.CUST_TRX_TYPE_ID
        AND RCTP.ORG_ID=RE.ORG_ID
        AND GCC.CODE_COMBINATION_ID=XAL.CODE_COMBINATION_ID
         AND TO_DATE('01'||'-'|| JEL.PERIOD_NAME) >=nvl( TO_DATE('01'||'-'||:FROM_DATE),TO_DATE('01'||'-'|| JEL.PERIOD_NAME))
        AND  TO_DATE('01'||'-'|| JEL.PERIOD_NAME) <=nvl(TO_DATE('01'||'-'||:TO_DATE),TO_DATE('01'||'-'|| JEL.PERIOD_NAME))
        AND XEH.ENTITY_CODE='TRANSACTIONS'
        AND JEH.STATUS='P' 
        AND RC.CUSTOMER_ID=RE.BILL_TO_CUSTOMER_ID
         and RE.BILL_TO_SITE_USE_ID=HCSU.SITE_USE_ID
        AND HCSU.CUST_ACCT_SITE_ID=HCSU1.CUST_ACCT_SITE_ID
        AND RE.SOLD_TO_CUSTOMER_ID=HCSU1.CUST_ACCOUNT_ID
        AND HCSU1.PARTY_SITE_ID=HPS.PARTY_SITE_ID
        AND HPS.LOCATION_ID=HL.LOCATION_ID
        AND JEH.JE_HEADER_ID=JEL.JE_HEADER_ID
        AND JEB.JE_BATCH_ID=JEH.JE_BATCH_ID
        AND JEL.CODE_COMBINATION_ID=GCC.CODE_COMBINATION_ID
        AND GIR.JE_HEADER_ID=JEH.JE_HEADER_ID
        AND GIR.JE_LINE_NUM=JEL.JE_LINE_NUM
        AND XAL.GL_SL_LINK_ID =GIR.GL_SL_LINK_ID
GROUP BY JEH.NAME,
jeh.LAST_UPDATE_DATE,
        JEH.JE_CATEGORY,
        JEL.DESCRIPTION,
        RE.DOC_SEQUENCE_VALUE,
        JEL.EFFECTIVE_DATE,
        jeh.CURRENCY_CODE,
        RC.CUSTOMER_NAME,
        hl.city,
        RCTP.TYPE,
        RE.TRX_NUMBER,
        RE.CUSTOMER_TRX_ID,
        RE.TRX_DATE,
        RE.TRX_DATE,
        JE_SOURCE,
        TO_CHAR(JEL.EFFECTIVE_DATE,'MON-YY'),
        GCC.CODE_COMBINATION_ID,GCC.SEGMENT1||'.'||GCC.SEGMENT2||'.'||GCC.SEGMENT3||'.'||GCC.SEGMENT4||'.'||GCC.SEGMENT5||'.'|| GCC.SEGMENT6||'.'|| GCC.SEGMENT7||'.'|| GCC.SEGMENT8||'.'|| GCC.SEGMENT9)
UNION all
--  inventory----------------------
select
JE_NAME,
LAST_UPDATE_DATE,
 JE_CATEGORY,
   MRN_NO,
  DESCRIPTION1,
  DOC_SEQUENCE_VALUE,
  EFFECTIVE_DATE,
   CURRENCY_CODE,
     VENDOR_NAME,
      VENDOR_SITE_CODE,
       doc_value,
         PO,
        --CBA.BANK_ACCOUNT_NUM,
       ACCOUNTING_DATE,
       GL_DATE,
        SUM(nvl(ENTERED_DR,0) ) ENTERED_DR,
        SUM(nvl(ENTERED_CR,0) ) ENTERED_CR,
        SUM(NVL(ACCOUNTED_DR,0)),
        SUM(NVL(ACCOUNTED_CR,0)),
        SUM(nvl(ACCOUNTED_DR,0) )-SUM(nvl(ACCOUNTED_CR,0) ) bal,
        JE_SOURCE,
        PERIOD,
        NAME ,
        CODE_COMBINATION_ID,
        code,
      PFROM_DATE,
       PTO_DATE,
         TYPE,
         DES from (SELECT  JEH.NAME JE_NAME,
         jeh.LAST_UPDATE_DATE,
        JEH.JE_CATEGORY,
        (select receipt_num from rcv_shipment_headers
where shipment_header_id=(select shipment_header_id from rcv_transactions
where transaction_id=rcv.transaction_id)) MRN_NO,
       (select ITEM_DESCRIPTION from po_lines_all
where po_header_id=(select po_header_id from rcv_transactions where transaction_id=rcv.transaction_id)
and po_line_id=(select po_line_id from rcv_transactions where transaction_id=rcv.transaction_id)) DESCRIPTION1,
        jeh.DOC_SEQUENCE_VALUE,
        JEL.EFFECTIVE_DATE,
        jeh.CURRENCY_CODE,
        (select VENDOR_NAME from po_vendors
        where vendor_id=rcv.vendor_id) VENDOR_NAME,
      (select VENDOR_SITE_code from po_vendor_sites_all
        where VENDOR_SITE_ID=rcv.VENDOR_SITE_ID) VENDOR_SITE_CODE,
         (select max(invoice_num) from ap_invoices_all
        where invoice_id=(select max(invoice_id) from ap_invoice_lines_all
        where PO_HEADER_ID=(select distinct po_header_id from rcv_transactions where transaction_id=rcv.transaction_id)
        and po_line_id=(select distinct  po_line_id from rcv_transactions where transaction_id=rcv.transaction_id))) doc_value,
         (select segment1 from po_headers_all where po_header_id=(select po_header_id from rcv_transactions where transaction_id=rcv.transaction_id)) PO,
        --CBA.BANK_ACCOUNT_NUM,
        XAL.ACCOUNTING_DATE,
        XAL.ACCOUNTING_DATE GL_DATE,
       XAL.ENTERED_DR,
       XAL.ENTERED_CR,
       XAL.ACCOUNTED_DR,
       XAL.ACCOUNTED_CR,
        JE_SOURCE,
        TO_CHAR(JEL.EFFECTIVE_DATE,'MON-YY') PERIOD,
        'CASH_MANAGEMENT' NAME ,
        GCC.CODE_COMBINATION_ID,GCC.SEGMENT1||'.'||GCC.SEGMENT2||'.'||GCC.SEGMENT3||'.'||GCC.SEGMENT4||'.'||GCC.SEGMENT5||'.'|| GCC.SEGMENT6||'.'|| GCC.SEGMENT7||'.'|| GCC.SEGMENT8||'.'|| GCC.SEGMENT9  code,
        decode(:FROM_DATE,null,'ALL',:FROM_DATE) PFROM_DATE,
        decode(:TO_DATE,null,'ALL',:TO_DATE) PTO_DATE,
        NULL TYPE,
        NULL DES
FROM
        XLA.XLA_TRANSACTION_ENTITIES XEH,
        XLA_AE_LINES XAL,
        XLA_AE_HEADERS XAH,
        GL_CODE_COMBINATIONS GCC,
        GL_IMPORT_REFERENCES GIR,
        GL_JE_LINES JEL,
        GL_JE_HEADERS JEH,
        GL_JE_BATCHES JEB,
        rcv_transactions rcv
WHERE
        XAL.LEDGER_ID=XEH.LEDGER_ID
        AND XEH.ENTITY_ID=XAH.ENTITY_ID
        AND XAH.AE_HEADER_ID=XAL.AE_HEADER_ID
        AND GCC.CODE_COMBINATION_ID=XAL.CODE_COMBINATION_ID
        AND XEH.ENTITY_CODE='RCV_ACCOUNTING_EVENTS'
        and xeh.SOURCE_ID_INT_1=rcv.transaction_id
       -- and rcv.transaction_id=2009
        AND JEH.STATUS='P'
        AND XAL.GL_SL_LINK_ID =GIR.GL_SL_LINK_ID
        and jeh.LAST_UPDATE_DATE>:creation
        AND JEL.CODE_COMBINATION_ID=GCC.CODE_COMBINATION_ID
        AND GIR.JE_LINE_NUM=JEL.JE_LINE_NUM
        AND GIR.JE_HEADER_ID=JEH.JE_HEADER_ID
        AND JEH.JE_HEADER_ID=JEL.JE_HEADER_ID
        AND JEB.JE_BATCH_ID=JEH.JE_BATCH_ID
      AND TO_DATE('01'||'-'|| JEL.PERIOD_NAME) >=nvl( TO_DATE('01'||'-'||:FROM_DATE),TO_DATE('01'||'-'|| JEL.PERIOD_NAME))
        AND  TO_DATE('01'||'-'|| JEL.PERIOD_NAME) <=nvl(TO_DATE('01'||'-'||:TO_DATE),TO_DATE('01'||'-'|| JEL.PERIOD_NAME)))
GROUP BY JE_NAME,
LAST_UPDATE_DATE,
        JE_CATEGORY,
        MRN_NO,
        DEscription1,
       DOC_SEQUENCE_VALUE,
        EFFECTIVE_DATE,
         VENDOR_NAME,
          VENDOR_SITE_CODE,
       CURRENCY_CODE,
        doc_value,
        PO,
        --CBA.BANK_ACCOUNT_NUM,
         ACCOUNTING_DATE,
         gl_DATE,
        JE_SOURCE,
         TYPE,
        PERIOD,
        NAME,
  CODE_COMBINATION_ID,code, PFROM_DATE,
       PTO_DATE
        UNION all
        SELECT  JEH.NAME JE_NAME,
        jeh.LAST_UPDATE_DATE,
        JEH.JE_CATEGORY,
          (select receipt_num from rcv_shipment_headers
where shipment_header_id=(select shipment_header_id from rcv_transactions
where to_char(transaction_id)=nvl(to_char(mmt.Rcv_transaction_id),to_char(mmt.TRANSACTION_REFERENCE)))) MRN_NO,
       MSI.DESCRIPTION,
        jeh.DOC_SEQUENCE_VALUE,
        JEL.EFFECTIVE_DATE,
        jeh.CURRENCY_CODE,
        (select VENDOR_NAME from po_vendors
        where vendor_id=(select vendor_id from rcv_transactions
where to_char(TRANSACTION_ID)=nvl(to_char(mmt.Rcv_transaction_id),to_char(mmt.TRANSACTION_REFERENCE)))) VENDOR_NAME,
        null  VENDOR_SITE_CODE,
        TO_CHAR(jeh.DOC_SEQUENCE_VALUE),
         (select segment1 from po_headers_all where po_header_id=(select po_header_id from rcv_transactions where to_char(transaction_id)=nvl(to_char(mmt.Rcv_transaction_id),to_char(mmt.TRANSACTION_REFERENCE)))) PO,
        --CBA.BANK_ACCOUNT_NUM,
        XAL.ACCOUNTING_DATE,
        XAL.ACCOUNTING_DATE GL_DATE,
        SUM(nvl(XAL.ENTERED_DR,0) ) ENTERED_DR,
        SUM(nvl(XAL.ENTERED_CR,0) ) ENTERED_CR,
        SUM(NVL(XAL.ACCOUNTED_DR,0)),
        SUM(NVL(XAL.ACCOUNTED_CR,0)),
        SUM(nvl(XAL.ACCOUNTED_DR,0) )-SUM(nvl(XAL.ACCOUNTED_CR,0) ) bal,
        JE_SOURCE,
        TO_CHAR(JEL.EFFECTIVE_DATE,'MON-YY') PERIOD,
        'INVENTORY' NAME ,
        GCC.CODE_COMBINATION_ID,GCC.SEGMENT1||'.'||GCC.SEGMENT2||'.'||GCC.SEGMENT3||'.'||GCC.SEGMENT4||'.'||GCC.SEGMENT5||'.'|| GCC.SEGMENT6||'.'|| GCC.SEGMENT7||'.'|| GCC.SEGMENT8||'.'|| GCC.SEGMENT9 ,
        decode(:FROM_DATE,null,'ALL',:FROM_DATE) PFROM_DATE,
        decode(:TO_DATE,null,'ALL',:TO_DATE) PTO_DATE,
        NULL TYPE,
        NULL DES
FROM
        XLA.XLA_TRANSACTION_ENTITIES XEH,
        XLA_AE_LINES XAL,
        XLA_AE_HEADERS XAH,
        GL_CODE_COMBINATIONS GCC,
        GL_IMPORT_REFERENCES GIR,
        GL_JE_LINES JEL,
        GL_JE_HEADERS JEH,
        GL_JE_BATCHES JEB,
        MTL_MATERIAL_TRANSACTIONS MMT,
        MTL_SYSTEM_ITEMS_B MSI,
        XLA_EVENTS XE
WHERE
        XAL.LEDGER_ID=XEH.LEDGER_ID
        AND XEH.ENTITY_ID=XAH.ENTITY_ID
        AND XAH.AE_HEADER_ID=XAL.AE_HEADER_ID
        AND GCC.CODE_COMBINATION_ID=XAL.CODE_COMBINATION_ID
        AND XEH.ENTITY_CODE='MTL_ACCOUNTING_EVENTS'
        and jeh.LAST_UPDATE_DATE>:creation
        AND MMT.TRANSACTION_ID=XEH.SOURCE_ID_INT_1
      AND MSI.INVENTORY_ITEM_ID=MMT.INVENTORY_ITEM_ID
      AND MSI.ORGANIZATION_ID=MMT.ORGANIZATION_ID
        AND JEH.STATUS='P'
        AND XE.EVENT_ID=XAH.EVENT_ID
         AND XE.PROCESS_STATUS_CODE='P'
         AND XE.EVENT_STATUS_CODE='P'
        AND XAL.GL_SL_LINK_ID =GIR.GL_SL_LINK_ID
        AND JEL.CODE_COMBINATION_ID=GCC.CODE_COMBINATION_ID
        AND XAH.ACCOUNTING_ENTRY_STATUS_CODE='F'
        AND XAL.APPLICATION_ID=XAH.APPLICATION_ID
       AND XE.APPLICATION_ID=XAH.APPLICATION_ID
       AND XEH.ENTITY_ID=XE.ENTITY_ID
        AND XEH.APPLICATION_ID=XE.APPLICATION_ID
        AND GIR.JE_LINE_NUM=JEL.JE_LINE_NUM
        AND GIR.JE_HEADER_ID=JEH.JE_HEADER_ID
        and NVL(XAL.ACCOUNTED_DR,0)-NVL(XAL.ACCOUNTED_CR,0)<>0
        AND JEH.JE_HEADER_ID=JEL.JE_HEADER_ID
        AND JEB.JE_BATCH_ID=JEH.JE_BATCH_ID
      AND TO_DATE('01'||'-'|| JEL.PERIOD_NAME) >=nvl( TO_DATE('01'||'-'||:FROM_DATE),TO_DATE('01'||'-'|| JEL.PERIOD_NAME))
        AND  TO_DATE('01'||'-'|| JEL.PERIOD_NAME) <=nvl(TO_DATE('01'||'-'||:TO_DATE),TO_DATE('01'||'-'|| JEL.PERIOD_NAME))
GROUP BY JEH.NAME,
jeh.LAST_UPDATE_DATE,
        JEH.JE_CATEGORY,
       MSI.DESCRIPTION,
        jeh.DOC_SEQUENCE_VALUE,
        JEL.EFFECTIVE_DATE,
      mmt.Rcv_transaction_id,mmt.TRANSACTION_REFERENCE,
        jeh.CURRENCY_CODE,
        TO_CHAR(jeh.DOC_SEQUENCE_VALUE),
        --CBA.BANK_ACCOUNT_NUM,
        XAL.ACCOUNTING_DATE,
          XAL.ACCOUNTING_DATE,
        JE_SOURCE,
        TO_CHAR(JEL.EFFECTIVE_DATE,'MON-YY'),
        GCC.CODE_COMBINATION_ID,GCC.SEGMENT1||'.'||GCC.SEGMENT2||'.'||
        GCC.SEGMENT3||'.'||GCC.SEGMENT4||'.'||GCC.SEGMENT5||'.'||
        GCC.SEGMENT6||'.'|| GCC.SEGMENT7||'.'|| GCC.SEGMENT8||'.'|| GCC.SEGMENT9
        union all
        SELECT  JEH.NAME JE_NAME,
        jeh.LAST_UPDATE_DATE,
        JEH.JE_CATEGORY,
         null MRN_NO,
        fma.DESCRIPTION,
        gir.SUBLEDGER_DOC_SEQUENCE_VALUE,
        JEL.EFFECTIVE_DATE,
        jeh.CURRENCY_CODE,
        POV.VENDOR_NAME,
        null VENDOR_SITE_CODE,
        TO_CHAR(fma.INVOICE_NUMBER),
        fma.PO_NUMBER PO,
        jeh.DEFAULT_EFFECTIVE_DATE,
        jeh.DEFAULT_EFFECTIVE_DATE GL_DATE,
        SUM(nvl(XAL.ENTERED_DR,0) ) ENTERED_DR,
        SUM(nvl(XAL.ENTERED_CR,0) ) ENTERED_CR,
        SUM(nvl(XAL.ACCOUNTED_DR,0) ) DR,
        SUM(nvl(XAL.ACCOUNTED_CR,0) ) CR,
        SUM(nvl(XAL.ACCOUNTED_DR,0) )-SUM(nvl(XAL.ACCOUNTED_CR,0) ) bal,
        JE_SOURCE,
        TO_CHAR(JEL.EFFECTIVE_DATE,'MON-YY') PERIOD,
        'Assets' NAME,
        GCC.CODE_COMBINATION_ID,GCC.SEGMENT1||'.'||GCC.SEGMENT2||'.'||GCC.SEGMENT3||'.'||GCC.SEGMENT4||'.'||GCC.SEGMENT5||'.'|| GCC.SEGMENT6||'.'|| GCC.SEGMENT7||'.'|| GCC.SEGMENT8||'.'|| GCC.SEGMENT9  CODE_COMINATION,
        decode(:FROM_DATE,null,'ALL',:FROM_DATE) PFROM_DATE,
        decode(:TO_DATE,null,'ALL',:TO_DATE) PTO_DATE,
        NULL TYPE,
        NULL DES
FROM
        GL_CODE_COMBINATIONS GCC,
        GL_JE_LINES JEL,
        GL_JE_HEADERS JEH,
        GL_JE_BATCHES JEB,
        GL_IMPORT_REFERENCES GIR,
        XLA.XLA_AE_LINES XAL,
        XLA.XLA_TRANSACTION_ENTITIES XEH,
        XLA.XLA_AE_HEADERS XAH,
        FA_ADDITIONS_B fab,
        FA_MASS_ADDITIONS fma,
        PO_VENDORS POV
        WHERE  JEH.JE_HEADER_ID=JEL.JE_HEADER_ID
        AND JEB.JE_BATCH_ID=JEH.JE_BATCH_ID
        AND JEL.CODE_COMBINATION_ID=GCC.CODE_COMBINATION_ID
        AND JEH.STATUS='P'
        AND XEH.ENTITY_CODE='DEPRECIATION'
        AND TO_DATE('01'||'-'|| JEL.PERIOD_NAME) >=nvl( TO_DATE('01'||'-'||:FROM_DATE),TO_DATE('01'||'-'|| JEL.PERIOD_NAME))
        AND  TO_DATE('01'||'-'|| JEL.PERIOD_NAME) <=nvl(TO_DATE('01'||'-'||:TO_DATE),TO_DATE('01'||'-'|| JEL.PERIOD_NAME))
        AND GIR.JE_HEADER_ID=JEH.JE_HEADER_ID
        AND GIR.JE_LINE_NUM=JEL.JE_LINE_NUM
        and jeh.LAST_UPDATE_DATE>:creation
        AND XAL.GL_SL_LINK_ID =GIR.GL_SL_LINK_ID
        AND XAL.AE_HEADER_ID=XAH.AE_HEADER_ID
        AND XAH.ENTITY_ID=XEH.ENTITY_ID
        AND fab.ASSET_ID=XEH.SOURCE_ID_INT_1
        and fab.ASSET_ID=fma.ASSET_NUMBER
        AND POV.VENDOR_ID=fma.PO_VENDOR_ID
GROUP BY JEH.NAME,
jeh.LAST_UPDATE_DATE,
        JEH.JE_CATEGORY,
        fma.DESCRIPTION,
        gir.SUBLEDGER_DOC_SEQUENCE_VALUE,
        JEL.EFFECTIVE_DATE,
        jeh.CURRENCY_CODE,
        POV.VENDOR_NAME,
        null,
        JE_SOURCE,
        fma.INVOICE_NUMBER,
        fma.PO_NUMBER,
        jeh.DEFAULT_EFFECTIVE_DATE,
           jeh.DEFAULT_EFFECTIVE_DATE,
        TO_CHAR(JEL.EFFECTIVE_DATE,'MON-YY'),
        GCC.CODE_COMBINATION_ID,GCC.SEGMENT1||'.'||GCC.SEGMENT2||'.'||GCC.SEGMENT3||'.'||GCC.SEGMENT4||'.'||GCC.SEGMENT5||'.'|| GCC.SEGMENT6||'.'|| GCC.SEGMENT7||'.'|| GCC.SEGMENT8||'.'|| GCC.SEGMENT9  
        union all
-----------------------------CASH_MANAGEMENT-----------------------------
SELECT  JEH.NAME JE_NAME,
jeh.LAST_UPDATE_DATE,
        JEH.JE_CATEGORY,
         null MRN_NO,
        JEL.DESCRIPTION,
        jeh.DOC_SEQUENCE_VALUE,
        JEL.EFFECTIVE_DATE,
        jeh.CURRENCY_CODE,
        nvl(CB.BANK_NAME,gcc.SEGMENT4) VENDOR_NAME,
        null  VENDOR_SITE_CODE,
        TO_CHAR(CCF.TRXN_REFERENCE_NUMBER),
         NULL PO,
        --CBA.BANK_ACCOUNT_NUM,
        XAL.ACCOUNTING_DATE,
        CCF.CASHFLOW_DATE GL_DATE,
        SUM(nvl(XAL.ENTERED_DR,0) ) ENTERED_DR,
        SUM(nvl(XAL.ENTERED_CR,0) ) ENTERED_CR,
        SUM(NVL(XAL.ACCOUNTED_DR,0)),
        SUM(NVL(XAL.ACCOUNTED_CR,0)),
        SUM(nvl(XAL.ACCOUNTED_DR,0) )-SUM(nvl(XAL.ACCOUNTED_CR,0) ) bal,
        JE_SOURCE,
        TO_CHAR(JEL.EFFECTIVE_DATE,'MON-YY') PERIOD,
        'CASH_MANAGEMENT' NAME ,
        GCC.CODE_COMBINATION_ID,GCC.SEGMENT1||'.'||GCC.SEGMENT2||'.'||GCC.SEGMENT3||'.'||GCC.SEGMENT4||'.'||GCC.SEGMENT5||'.'|| GCC.SEGMENT6||'.'|| GCC.SEGMENT7||'.'|| GCC.SEGMENT8||'.'|| GCC.SEGMENT9 ,
        decode(:FROM_DATE,null,'ALL',:FROM_DATE) PFROM_DATE,
        decode(:TO_DATE,null,'ALL',:TO_DATE) PTO_DATE,
        NULL TYPE,
        NULL DES
FROM
        XLA.XLA_TRANSACTION_ENTITIES XEH,
        XLA_AE_LINES XAL,
        XLA_AE_HEADERS XAH,
        GL_CODE_COMBINATIONS GCC,
        CE_CASHFLOWS CCF,
        CE_BANK_ACCOUNTS CBA,
        CE_BANK_ACCT_USES_ALL CBU,
        CE_BANKS_V CB,
        GL_IMPORT_REFERENCES GIR,
        GL_JE_LINES JEL,
        GL_JE_HEADERS JEH,
        GL_JE_BATCHES JEB
WHERE
        XAL.LEDGER_ID=XEH.LEDGER_ID
        AND XEH.ENTITY_ID=XAH.ENTITY_ID
        AND XAH.AE_HEADER_ID=XAL.AE_HEADER_ID
        AND GCC.CODE_COMBINATION_ID=XAL.CODE_COMBINATION_ID
        AND XEH.SOURCE_ID_INT_1=CCF.CASHFLOW_ID
        AND XEH.ENTITY_CODE='CE_CASHFLOWS'
        AND CCF.CASHFLOW_BANK_ACCOUNT_ID=CBU.BANK_ACCT_USE_ID(+)
        AND CBU.BANK_ACCOUNT_ID=CBA.BANK_ACCOUNT_ID(+)
        AND CBA.BANK_ID=CB.BANK_PARTY_ID(+)
        and jeh.LAST_UPDATE_DATE>:creation
        AND JEH.STATUS='P'
        AND XAL.GL_SL_LINK_ID =GIR.GL_SL_LINK_ID
        AND JEL.CODE_COMBINATION_ID=GCC.CODE_COMBINATION_ID
        AND GIR.JE_LINE_NUM=JEL.JE_LINE_NUM
        AND GIR.JE_HEADER_ID=JEH.JE_HEADER_ID
        AND JEH.JE_HEADER_ID=JEL.JE_HEADER_ID
        AND JEB.JE_BATCH_ID=JEH.JE_BATCH_ID
      AND TO_DATE('01'||'-'|| JEL.PERIOD_NAME) >=nvl( TO_DATE('01'||'-'||:FROM_DATE),TO_DATE('01'||'-'|| JEL.PERIOD_NAME))
        AND  TO_DATE('01'||'-'|| JEL.PERIOD_NAME) <=nvl(TO_DATE('01'||'-'||:TO_DATE),TO_DATE('01'||'-'|| JEL.PERIOD_NAME))
GROUP BY JEH.NAME,
jeh.LAST_UPDATE_DATE,
        JEH.JE_CATEGORY,
        JEL.DESCRIPTION,
        jeh.DOC_SEQUENCE_VALUE,
        JEL.EFFECTIVE_DATE,
        jeh.CURRENCY_CODE,
        nvl(CB.BANK_NAME,gcc.SEGMENT4),
        TO_CHAR(CCF.TRXN_REFERENCE_NUMBER),
        --CBA.BANK_ACCOUNT_NUM,
        XAL.ACCOUNTING_DATE,
         CCF.CASHFLOW_DATE,
        JE_SOURCE,
        TO_CHAR(JEL.EFFECTIVE_DATE,'MON-YY'),
        GCC.CODE_COMBINATION_ID,GCC.SEGMENT1||'.'||GCC.SEGMENT2||'.'||
        GCC.SEGMENT3||'.'||GCC.SEGMENT4||'.'||GCC.SEGMENT5||'.'||
        GCC.SEGMENT6||'.'|| GCC.SEGMENT7||'.'|| GCC.SEGMENT8||'.'|| GCC.SEGMENT9
 )a,GL_CODE_COMBINATIONS GCC
where a.JE_SOURCE=nvl(:p_source,a.JE_SOURCE)
AND A.CODE_COMBINATION_ID=GCC.CODE_COMBINATION_ID
and gcc.segment1=nvl(:p_company,gcc.segment1)
and gcc.segment2=nvl(:p_location,gcc.segment2)
and gcc.segment3=nvl(:p_lob,gcc.segment3)
and gcc.segment4=nvl(:p_cost_centre,gcc.segment4)
and gcc.segment5=nvl(:p_account,gcc.segment5)
and gcc.segment6=nvl(:p_sub_account,gcc.segment6)
and gcc.segment7=nvl(:p_project,gcc.segment7)
order by gl_date



GL Account Analysis SQL Query in Oracle Apps r12

Monday 29 January 2018

SQL Query to Fetch EBS Taxes with Operating Unit and GL Code in Oracle Apps r12



SQL Query to Fetch EBS Taxes with Operating Unit and GL Code in Oracle Apps r12



SELECT hou.organization_id            org_id,
       led.name                       ledger,
       hou.name                       operating_unit,
       --
       zxr.tax_regime_code            tax_regime_code,
       zxr.tax                        tax_code,
       zxr.tax_status_code            tax_status_code,
       zxr.tax_rate_code              tax_rate_code,
       zxr.tax_jurisdiction_code      tax_jurisdiction_code,
       --
       zxr.rate_type_code             rate_type_code,
       zxr.percentage_rate            percentage_rate,
       zxr.effective_from             rate_effective_from,
       zxr.effective_to               rate_effective_to,
       --
       acc.tax_account_ccid           tax_account_ccid,
       gcc.concatenated_segments      tax_account
  FROM
       zx_rates_vl                  zxr,
       zx_accounts                  acc,
       hr_operating_units           hou,
       gl_ledgers                   led,
       gl_code_combinations_kfv  gcc
 WHERE
       1=1
   --
   -- AND zxr.tax_regime_code = 'GR VAT'
   -- AND zxr.tax_rate_code = 'GR_AR_DOM'
   --
   AND acc.tax_account_entity_code = 'RATES'
   AND zxr.active_flag = 'Y'
   AND TRUNC (SYSDATE) BETWEEN
          TRUNC (zxr.effective_from) AND
          NVL (TRUNC (zxr.effective_to), TRUNC (SYSDATE) + 1)
   --
   AND led.ledger_id = hou.set_of_books_id
   AND gcc.code_combination_id = acc.tax_account_ccid
   AND hou.organization_id = acc.internal_organization_id
   and  hou.organization_id=7890
   AND acc.tax_account_entity_id = zxr.tax_rate_id
   --
   ;

SQL Query to Fetch Oracle Customer Banking Details in Oracle apps R12




SQL Query to Fetch Oracle Customer Banking Details in Oracle apps R12


select DISTINCT ac.customer_name
,ac.customer_number, bankacct.*
from iby_pmt_instr_uses_all instr_assign,iby_external_payers_all payee,iby_ext_bank_accounts bankacct,hz_cust_accounts_all hcal,ar_customers acwhere instr_assign.instrument_id = bankacct.ext_bank_account_id
and instr_assign.ext_pmt_party_id = payee.ext_payer_id
and instr_assign.instrument_type = 'BANKACCOUNT'and instr_assign.payment_flow = 'FUNDS_CAPTURE'and instr_assign.payment_function = 'CUSTOMER_PAYMENT'and hcal.cust_account_id = payee.cust_account_id
and ac.customer_number = hcal.account_number
and bankacct.bank_account_num = '4456677777' (Pass Banks Account or leave blank)

if you found this post helpful and you liked it then follow this blog to get notifications for the upcoming posts.

If you want to see Other OAF Related Posts , Please visit Below URL.
https://rpforacle.blogspot.in/2013/03/oaf_10.html

If you want to see  Oracle Fusion Related Posts , Please visit Below URL.
https://rpforacle.blogspot.in/2018/01/oracle-fusion-learning-tutorial.html
If you want to Learn Oracle Workflow Builder, Please visit Below URL.
https://rpforacle.blogspot.in/2018/01/oracle-workflow-learning-tutorial-1.html

SQL Query to Fetch Oracle Apps User accounts with Responsibility Names in Oracle Apps R12 for Specific Operating Unit




SQL Query to Fetch Oracle Apps User accounts with Responsibility Names in Oracle apps R12 For Specific Operating Unit





SELECT fu.user_name,
  (SELECT FULL_NAME FROM PER_PEOPLE_F
   WHERE PERSON_ID=FU.EMPLOYEE_ID
   AND EFFECTIVE_END_DATE>SYSDATE) EMPLOYEE_NAME
      ,fr.responsibility_name
  FROM apps.fnd_user_resp_groups_all furg
      ,apps.fnd_user fu
      ,apps.fnd_responsibility_vl fr
      ,apps.fnd_profile_option_values fpov
      ,apps.fnd_profile_options_vl fpo
 WHERE fu.user_id= furg.user_id
   AND fu.user_name=nvl(:P_USER,fu.user_name)
   AND fr.responsibility_name=nvl(:P_RESP,fr.responsibility_name)
   AND fr.responsibility_id= furg.responsibility_id
   AND (furg.end_date >sysdate OR furg.end_date IS NULL)
   AND fpov.level_value=fr.responsibility_id
   AND fpov.profile_option_id= fpo.profile_option_id
   AND fpov.application_id=fpo.application_id
   AND FU.END_DATE IS NULL
   AND FR.END_DATE IS NULL
   AND fpo.user_profile_option_name='MO: Operating Unit'
   AND fpov.profile_option_value=189
   ORDER BY 1

SQL Query to Fetch Oracle Apps User accounts with Responsibility Names in Oracle Apps R12 for Specific Operating Unit

Contact us for any Collaboration, Project Support & On Job Support Work

Name

Email *

Message *