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


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


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'

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.


Hi Friends, In this post , I will share you the logic to create Cash Bank Book report 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.






 

GL Account Analysis SQL Query in Oracle Apps r12


GL Account Analysis SQL Query in Oracle Apps r12 :- This query will give you the Detail from GL to Sub ledgers.





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


 

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

Interface to Upload Approved supplier List(ASL) in Oracle Apps R12 & SQL Query



Interface to Upload Approved supplier List in Oracle Apps R12


DECLARE
x_row_id VARCHAR2(20);
x_row_id1 VARCHAR2(20);
x_asl_id NUMBER;

BEGIN

apps.po_asl_ths.insert_row
(x_row_id,
x_asl_id,
87, --using_organization_id
87, --owning_organization_id,
'DIRECT', --vendor_business_type,
2, --status_id,
SYSDATE, --last_updated_date
0, --last_updated_by,
SYSDATE, --creation_date,
0, --created_by,
NULL,
4408, --vendor_id,
484915, --inventory_item_id,
NULL,
7888, --vendor_site_id,
null, --primary_vendor_item,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL);
 
 
apps.po_asl_attributes_ths.insert_row

(x_row_id1,
x_asl_id, --asl_id
87, --using_organization_id,
SYSDATE, --last_updated_date
0, --last_updated_by,
SYSDATE, --creation_date
0, --created_by,
'ASL', --document_sourcing_method
'CREATE_AND_APPROVE', --release_generation_method
NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,
4408, --vendor_id,
7888, --vendor_site_id,
484915, --inventory_item_id,
NULL,
null, --attribute_category
null, --state(attribute1),
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,

null, --country_of_origin_code,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL);

COMMIT;

END;



SQL Query to fetch Approved Supplier List in Oracle



SELECT hou.NAME operating_unit, asl.vendor_business_type,
       pov.segment1 vendor_code, pov.vendor_name, sites.vendor_site_code,
       msi.segment1 item_code, msi.description item_desc,
       using_organization_id, owning_organization_id,plc.displayed_field , past.status Supplier_Status
  FROM po_approved_supplier_list asl,
       po_vendors pov,
       po_vendor_sites_all sites,
       mtl_system_items_b msi,
       hr_operating_units hou,
       po_lookup_codes plc,
       po_asl_statuses past
 WHERE asl.vendor_id = pov.vendor_id
   AND asl.vendor_site_id = sites.vendor_site_id
   AND msi.inventory_item_id = asl.item_id
   AND msi.organization_id = asl.using_organization_id
   AND sites.org_id = hou.organization_id
   and asl.vendor_business_type = plc.lookup_code
   AND asl.asl_status_id = past.status_id
   AND plc.lookup_type = 'ASL_VENDOR_BUSINESS_TYPE'
   and past.status='Approved';

 

Reconciliation of AP and GL in Oracle Apps R12


Reconciliation of  AP and GL in Oracle Apps R12


In Friends , in this post I will share you the SQL queries by which you can easily reconcile from AP and GL in Oracle Apps R12.




Step1:- First you need to run this below query from Oracle Apps DB for your AP Liability accounts.In this example I am doing reconciliation of my AP Liability Natural account 24103390 and you can select any dates of the month for which you want to do reconciliation.


select JE_SOURCE,sum(nvl(accounted_dr,0))-sum(nvl(accounted_cr,0)) Balanace from gl_je_headers a1,gl_je_lines a2,gl_code_combinations gcc
 where a1.JE_HEADER_ID=a2.JE_HEADER_ID
 and a2.CODE_COMBINATION_ID=gcc.CODE_COMBINATION_ID
 and gcc.segment5=24103390
 and effective_date between :p_from_date and :p_to_date
 and A2.status='P'
 GROUP BY JE_SOURCE

This above query will give you the source from which your data is coming in GL. You have to put Manual Invoice amount separate in the sheet to do the reco between AP and GL because manual entry will not come in AP and this entry is directly entered in GL so when you will do the reco you will always get a difference of that manual entry value but by this query you will know this difference.


Step2:- Now that you need to run this query with Account wise and put that in Excel.

select JE_SOURCE,GCC.CONCATENATED_SEGMENTS,sum(nvl(accounted_dr,0))-sum(nvl(accounted_cr,0)) Balanace from gl_je_headers a1,gl_je_lines a2,gl_code_combinations_kfv gcc
 where a1.JE_HEADER_ID=a2.JE_HEADER_ID
 and a2.CODE_COMBINATION_ID=gcc.CODE_COMBINATION_ID
 and gcc.segment5=24103390
 and effective_date between :p_from_date and :p_to_date
 and A2.status='P'
 GROUP BY JE_SOURCE,GCC.CONCATENATED_SEGMENTS



Step3:- Now run this query which will fetch the Payables/AP data for you Liability accounts.In this example I am doing reconciliation of my AP Liability Natural account 24103390 and you can select any dates of the month for which you want to do reconciliation.

 select CONCATENATED_SEGMENTS,sum(T_LEDGER_dr)-sum(T_LEDGER_Cr) bal from (
 SELECT
CASE WHEN
        XAL.PARTY_ID = (SELECT MAX(DUPLICATE_VENDOR_ID) FROM AP_DUPLICATE_VENDORS_ALL WHERE DUPLICATE_VENDOR_ID = XAL.PARTY_ID) THEN
        (SELECT MAX(VENDOR_ID) FROM AP_DUPLICATE_VENDORS_ALL WHERE DUPLICATE_VENDOR_ID = XAL.PARTY_ID)
        ELSE XAL.PARTY_ID
        END PARTY_ID,
        NVL(CASE WHEN
        XAL.PARTY_SITE_ID = (SELECT MAX(DUPLICATE_VENDOR_SITE_ID) FROM AP_DUPLICATE_VENDORS_ALL WHERE DUPLICATE_VENDOR_ID = XAL.PARTY_ID AND DUPLICATE_VENDOR_SITE_ID = XAL.PARTY_SITE_ID) THEN
        (SELECT MAX(VENDOR_SITE_ID) FROM AP_DUPLICATE_VENDORS_ALL WHERE DUPLICATE_VENDOR_ID = XAL.PARTY_ID AND DUPLICATE_VENDOR_SITE_ID = XAL.PARTY_SITE_ID)
        ELSE XAL.PARTY_SITE_ID
        END,XAL.PARTY_SITE_ID) PARTY_SITE_ID,
 GCC.CONCATENATED_SEGMENTS,GCC.CODE_COMBINATION_ID,
 0 O_LEDGER_CR,0 O_LEDGER_DR,
 NVL(XAL.ACCOUNTED_CR,0) T_LEDGER_CR,
 NVL(XAL.ACCOUNTED_DR,0) T_LEDGER_DR
 FROM
 XLA.XLA_TRANSACTION_ENTITIES XTE,
 XLA.XLA_AE_HEADERS XAH,
 XLA.XLA_AE_LINES XAL,
 GL_CODE_COMBINATIONS_KFV GCC
 WHERE 1 = 1
 AND :P_APPLICATION_ID=200
 AND XAL.APPLICATION_ID=200
 AND XTE.LEDGER_ID = XAH.LEDGER_ID
 AND XTE.ENTITY_ID = XAH.ENTITY_ID
 AND XTE.APPLICATION_ID = XAL.APPLICATION_ID
 AND XAH.AE_HEADER_ID = XAL.AE_HEADER_ID
 AND XAL.GL_SL_LINK_TABLE = 'XLAJEL'
 AND ACCOUNTING_CLASS_CODE IN ('LIABILITY','PREPAID_EXPENSE')
 AND XAL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
 and xal.CODE_COMBINATION_ID in  (SELECT CODE_COMBINATION_ID FROM GL_CODE_COMBINATIONS
 WHERE 1=1
 AND SEGMENT5=24103390)
-- AND XAL.PARTY_ID=&VENDOR_ID
 AND NVL(XAL.GL_TRANSFER_MODE_CODE,'N')='S'
 AND TRUNC(XAL.ACCOUNTING_DATE) >= :FROM_DATE
 AND TRUNC(XAL.ACCOUNTING_DATE) <= :TO_DATE
 AND SECURITY_ID_INT_1=:P_ORG_ID)
 group by CONCATENATED_SEGMENTS


Step4:- Now match the accounts fetched with Step 2 and 3 and find the difference.




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