SLA or XLA(AP-GL,RECEIPT-GL,AR-GL)(SubledgerAccounting)

SELECT DISTINCT PS.PERIOD_NAME,
GJL.REFERENCE_2,
TO_CHAR (NULL) PO_ORDER_NUMBER,
TO_CHAR (NULL) "RECEIPT NUMBER",
TO_DATE (NULL) "RECEIPT DATE",
TO_CHAR (NULL) "INVOICE NUMBER",
TO_DATE (NULL) "INVOICE DATE",
TO_CHAR (NULL) "VENDOR NAME",
TO_CHAR (NULL) "VENDOR NUMBER",
JE_SOURCE "SOURCE",
GJH.LEDGER_ID "SET_OF_BOOKS_ID" ,
FND_FLEX_EXT.GET_SEGS('SQLGL','GL#',GSCV.CHART_OF_ACCOUNTS_ID, GSCV.CODE_COMBINATION_ID) SEGMENT,
FND_FLEX_EXT.GET_SEGS('SQLGL','GL#',GSCV.CHART_OF_ACCOUNTS_ID, GSCV.CODE_COMBINATION_ID) SEGMENT2,
GJH.CURRENCY_CODE,
GJL.ENTERED_DR ,
GJL.ENTERED_CR,
TO_NUMBER(DECODE ( LR.RELATIONSHIP_TYPE_CODE, 'BALANCE', NULL, GJL.ACCOUNTED_DR )) ACCOUNTED_DR,
TO_NUMBER(DECODE ( LR.RELATIONSHIP_TYPE_CODE, 'BALANCE', NULL, GJL.ACCOUNTED_CR )) ACCOUNTED_CR,
GJH.JE_HEADER_ID,
GJL.JE_LINE_NUM,
GJL.CODE_COMBINATION_ID ,
GJH.DOC_SEQUENCE_VALUE "VOCHER NUMBER",
GJH.DEFAULT_EFFECTIVE_DATE GL_DATE,
GJL.ATTRIBUTE1  "STAFF NUMBER",
GJL.ATTRIBUTE2 "PROJECT",
GJL.ATTRIBUTE3  "CUSTOMER NUM/NAME" ,
GJL.DESCRIPTION DESCRIPTION
FROM GL_JE_LINES GJL , GL_JE_HEADERS GJH, GL_SUMMARY_COMBINATIONS_V GSCV, GL_LEDGERS,GL_PERIOD_STATUSES PS,GL_JE_BATCHES B,
GL_LEDGER_RELATIONSHIPS LR
WHERE GSCV.CODE_COMBINATION_ID =GJL.CODE_COMBINATION_ID
AND GJH.JE_HEADER_ID=GJL.JE_HEADER_ID
AND GJL.PERIOD_NAME       = PS.PERIOD_NAME
AND PS.LEDGER_ID            = GJL.LEDGER_ID
AND B.JE_BATCH_ID           = GJH.JE_BATCH_ID
AND GJH.LEDGER_ID             = LR.SOURCE_LEDGER_ID
AND LR.SOURCE_LEDGER_ID = LR.TARGET_LEDGER_ID
AND B.ACTUAL_FLAG = 'A'
AND B.STATUS || '' = 'P'
AND GJH.CURRENCY_CODE != 'STAT'
AND JE_SOURCE NOT IN ('PAYABLES','COST MANAGEMENT','CONSOLIDATION','RECEIVABLES')
AND GJH.LEDGER_ID = GL_LEDGERS.LEDGER_ID
---  AND GL_LEDGERS.SUSPENSE_ALLOWED_FLAG = 'P'
AND (    NVL (PS.EFFECTIVE_PERIOD_NUM, 0) >= :CF_PERIOD_FROM
AND (NVL (PS.EFFECTIVE_PERIOD_NUM, 0) <= :CF_PERIOD_TO)
)
AND (    NVL (GSCV.SEGMENT5, 0) >= :P_ACCOUNT_FROM
AND (NVL (GSCV.SEGMENT5, 0) <= :P_ACCOUNT_TO)
)
AND (    NVL (GSCV.SEGMENT1, 0) >=:P_COMPANY_FROM
AND (NVL (GSCV.SEGMENT1, 0) <= :P_COMPANY_TO)
)
AND GJH.LEDGER_ID NOT IN ( 1014,1015,1016 )
AND GJH.CURRENCY_CODE =DECODE(:P_CURRENCY_CODE,'INR','INR',
'ALL',GJH.CURRENCY_CODE,
'NON INR',DECODE(GJH.CURRENCY_CODE,'INR','###',GJH.CURRENCY_CODE))
UNION ALL

          
SELECT DISTINCT B.PERIOD_NAME, D.REFERENCE_2, AAA.PO_ORDER_NUMBER, AAA.RCV_RECEIPT_NUM "RECEIPT NUMBER",
AAA.TRX_DATE "RECEIPT DATE", AAA.TRX_NUMBER_DISPLAYED "INVOICE NUMBER",
AAA.ACCOUNTING_DATE "INVOICE DATE", AAA.THIRD_PARTY_NAME "VENDOR NAME",
AAA.THIRD_PARTY_NUMBER "VENDOR NUMBER", B.JE_SOURCE "SOURCE",  B.LEDGER_ID "SET_OF_BOOKS_ID" ,
FND_FLEX_EXT.GET_SEGS('SQLGL','GL#',CCC.CHART_OF_ACCOUNTS_ID, CCC.CODE_COMBINATION_ID) SEGMENT,
FND_FLEX_EXT.GET_SEGS('SQLGL','GL#',CCC.CHART_OF_ACCOUNTS_ID, CCC.CODE_COMBINATION_ID)  SEGMENT2,
AAA.CURRENCY_CODE, AAA.ENTERED_DR, AAA.ENTERED_CR, AAA.ACCOUNTED_DR,
AAA.ACCOUNTED_CR, AAA.JE_HEADER_ID, AAA.JE_LINE_NUM, AAA.CODE_COMBINATION_ID,
AAA.DOC_SEQUENCE_VALUE "VOCHER NUMBER", AAA.GL_DATE "GL_DATE",
AAA.ATTRIBUTE1 "STAFF NUMBER", AAA.ATTRIBUTE2 "PROJECT",
AAA.ATTRIBUTE3 "CUSTOMER NUM/NAME", AAA.DESCRIPTION "DESCRIPTION"
FROM (SELECT DISTINCT POH.SEGMENT1 PO_ORDER_NUMBER, RSH.RECEIPT_NUM RCV_RECEIPT_NUM,
               RCT.TRANSACTION_DATE TRX_DATE, API.INVOICE_NUM TRX_NUMBER_DISPLAYED,
               RRS.ACCOUNTING_DATE ACCOUNTING_DATE,
               POV.VENDOR_NAME THIRD_PARTY_NAME,
               POV.SEGMENT1 THIRD_PARTY_NUMBER, RRS.CURRENCY_CODE,
               RRS.ENTERED_DR, RRS.ENTERED_CR, RRS.ACCOUNTED_DR,
               RRS.ACCOUNTED_CR, R.JE_HEADER_ID, R.JE_LINE_NUM,
               RRS.CODE_COMBINATION_ID, 201 APPLICATION_ID,
               TO_DATE (NULL) GL_DATE, TO_NUMBER (NULL) DOC_SEQUENCE_VALUE,
               TO_CHAR (NULL) DESCRIPTION, TO_CHAR (NULL) ATTRIBUTE1,
               TO_CHAR (NULL) ATTRIBUTE2, TO_CHAR (NULL) ATTRIBUTE3
               --POH.ORG_ID
          FROM   PO_HEADERS_ALL POH, PO_LINES_ALL PL, PO_DISTRIBUTIONS_ALL POD
, RCV_SHIPMENT_LINES RSL, RCV_SHIPMENT_HEADERS RSH, RCV_TRANSACTIONS RCT, RCV_RECEIVING_SUB_LEDGER RRS
, AP_INVOICE_DISTRIBUTIONS_ALL AIDA,AP_INVOICES_ALL API , XLA_AE_HEADERS XAH , XLA_AE_LINES XAL
,GL_IMPORT_REFERENCES R
,AP_SUPPLIERS POV
     WHERE  1=1
AND POH.PO_HEADER_ID=PL.PO_HEADER_ID
AND POH.PO_HEADER_ID=POD.PO_HEADER_ID
AND POH.PO_HEADER_ID= RSL.PO_HEADER_ID(+)
AND RSL.SHIPMENT_HEADER_ID=RSH.SHIPMENT_HEADER_ID(+)
AND RSH.SHIPMENT_HEADER_ID=RCT.SHIPMENT_HEADER_ID(+)
AND POD.PO_DISTRIBUTION_ID= AIDA.PO_DISTRIBUTION_ID(+)
AND API.INVOICE_ID(+)=AIDA.INVOICE_ID
AND POD.PO_DISTRIBUTION_ID = RRS.REFERENCE3
AND RCT.TRANSACTION_ID = RRS.RCV_TRANSACTION_ID
AND AIDA.ACCOUNTING_EVENT_ID=XAH.EVENT_ID(+)
AND XAH.AE_HEADER_ID=XAL.AE_HEADER_ID(+)
AND XAL.GL_SL_LINK_ID= R.GL_SL_LINK_ID(+)
AND R.REFERENCE_8=XAL.AE_LINE_NUM
AND POH.VENDOR_ID=POV.VENDOR_ID
AND AIDA.LINE_TYPE_LOOKUP_CODE(+)='ACCRUAL'
AND RRS.ACCOUNTING_LINE_TYPE='ACCRUAL'
AND XAL.ACCOUNTING_CLASS_CODE(+)='LIABILITY'
AND POD.DESTINATION_TYPE_CODE='EXPENSE'
           AND EXISTS (SELECT 1
                 FROM AP_INVOICE_DISTRIBUTIONS_ALL AID,AP_INVOICES_ALL AI
        WHERE AID.PO_DISTRIBUTION_ID=POD.PO_DISTRIBUTION_ID
        AND AI.INVOICE_ID = AID.INVOICE_ID
                       AND AI.VENDOR_ID = POV.VENDOR_ID) )           AAA,
       GL_JE_HEADERS  B, GL_JE_LINES D,GL_JE_BATCHES E, GL_PERIOD_STATUSES PS,
       GL_SUMMARY_COMBINATIONS_V         CCC
WHERE B.JE_HEADER_ID = AAA.JE_HEADER_ID
AND D.JE_LINE_NUM = AAA.JE_LINE_NUM
AND B.JE_HEADER_ID=D.JE_HEADER_ID
AND D.CODE_COMBINATION_ID = CCC.CODE_COMBINATION_ID
AND AAA.CODE_COMBINATION_ID = D.CODE_COMBINATION_ID
AND PS.LEDGER_ID            = D.LEDGER_ID
AND B.ACTUAL_FLAG = 'A'
AND B.STATUS || '' = 'P'
AND B.CURRENCY_CODE != 'STAT'
AND JE_SOURCE NOT IN ('PAYABLES', 'PURCHASING','CONSOLIDATION')
--AND     AAA.ORG_ID=:P_ORG
AND (    NVL (PS.EFFECTIVE_PERIOD_NUM, 0) >= :CF_PERIOD_FROM
AND (NVL (PS.EFFECTIVE_PERIOD_NUM, 0) <= :CF_PERIOD_TO)
)
AND (    NVL (CCC.SEGMENT5, 0) >= :P_ACCOUNT_FROM
AND (NVL (CCC.SEGMENT5, 0) <= :P_ACCOUNT_TO)
)
AND (    NVL (CCC.SEGMENT1, 0) >=:P_COMPANY_FROM
AND (NVL (CCC.SEGMENT1, 0) <= :P_COMPANY_TO)
)
AND  B.LEDGER_ID NOT IN ( 1014,1015,1016 )
AND B.CURRENCY_CODE  =DECODE(:P_CURRENCY_CODE,'INR','INR',
'ALL',B.CURRENCY_CODE,
'NON INR',DECODE(B.CURRENCY_CODE,'INR','###',B.CURRENCY_CODE))
UNION ALL
--PO-RECEIPT-GL
SELECT  GPS.PERIOD_NAME,
        L.REFERENCE_2,
        PHA.SEGMENT1 PO_ORDER_NUMBER,
        RSH.RECEIPT_NUM "RECEIPT NUMBER",
        RT.TRANSACTION_DATE  "RECEIPT DATE",
        NULL   "INVOICE NUMBER",
        NULL   "INVOICE DATE",
        PV.VENDOR_NAME "VENDOR NAME",
        PV.SEGMENT1 "VENDOR NUMBER",
        H.JE_SOURCE "SOURCE",
        H.LEDGER_ID "SET_OF_BOOKS_ID" ,
        GCC.CONCATENATED_SEGMENTS SEGMENT,
        GCC.CONCATENATED_SEGMENTS SEGMENT2,
        PHA.CURRENCY_CODE,
--       PLA.LINE_NUM,
--       PLLA.SHIPMENT_NUM,
--       PLA.ITEM_DESCRIPTION,
--       PLA.UNIT_PRICE,
--       PLLA.QUANTITY,
--       PLLA.QUANTITY_RECEIVED,
--       PLLA.QUANTITY_BILLED,
--       RT.TRANSACTION_TYPE,
       --RT.TRANSACTION_DATE,
       XAL.ENTERED_DR,
       XAL.ENTERED_CR,
       XAL.ACCOUNTED_DR,
       XAL.ACCOUNTED_CR,
       GIR.JE_HEADER_ID,
       GIR.JE_LINE_NUM,
       RRSL.CODE_COMBINATION_ID,
       --GPS.APPLICATION_ID,
       --
       TO_NUMBER (NULL) "VOCHER NUMBER",
TO_DATE (NULL) GL_DATE,      
                TO_CHAR (NULL)  "STAFF NUMBER",
               TO_CHAR (NULL) "PROJECT",
               TO_CHAR (NULL) "CUSTOMER NUM/NAME" ,
               --PHA.ORG_ID,
--               TO_CHAR (NULL) DESCRIPTION
               RSL.ITEM_DESCRIPTION "DESCRIPTION"
  FROM GL.GL_JE_HEADERS            H,
       GL.GL_JE_LINES                L,
       GL_CODE_COMBINATIONS_KFV     GCC,
       GL.GL_IMPORT_REFERENCES     GIR,
       APPS.XLA_AE_LINES             XAL,
       APPS.XLA_DISTRIBUTION_LINKS   XDL,
       PO.RCV_RECEIVING_SUB_LEDGER RRSL,
       PO.RCV_TRANSACTIONS RT,
       PO.PO_HEADERS_ALL PHA,
       PO.PO_LINES_ALL PLA,
       PO.PO_LINE_LOCATIONS_ALL PLLA,
       APPS.PO_VENDORS PV,
       RCV_SHIPMENT_HEADERS RSH,
       RCV_SHIPMENT_LINES RSL,
       GL_PERIOD_STATUSES GPS
 WHERE 1=1
 AND H.JE_HEADER_ID = L.JE_HEADER_ID
 AND L.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
 AND (    NVL (GCC.SEGMENT5, 0) >=:P_ACCOUNT_FROM
 AND (NVL (GCC.SEGMENT5, 0) <= :P_ACCOUNT_TO)
       ) 
      
     AND (    NVL (GCC.SEGMENT1, 0) >=:P_COMPANY_FROM
        AND (NVL (GCC.SEGMENT1, 0) <= :P_COMPANY_TO)
       )         
   AND L.JE_HEADER_ID = GIR.JE_HEADER_ID
   AND L.JE_LINE_NUM = GIR.JE_LINE_NUM
   AND GIR.GL_SL_LINK_ID = XAL.GL_SL_LINK_ID
   AND XAL.AE_HEADER_ID = XDL.AE_HEADER_ID
   AND XAL.AE_LINE_NUM = XDL.AE_LINE_NUM
   AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 = RRSL.RCV_SUB_LEDGER_ID
   AND RRSL.RCV_TRANSACTION_ID = RT.TRANSACTION_ID
   AND RT.PO_HEADER_ID = PHA.PO_HEADER_ID
   AND RT.PO_HEADER_ID = PLA.PO_HEADER_ID
   AND RT.PO_LINE_ID = PLA.PO_LINE_ID
   AND PLA.PO_HEADER_ID = PLLA.PO_HEADER_ID
   AND PLA.PO_LINE_ID = PLLA.PO_LINE_ID
   AND PHA.VENDOR_ID = PV.VENDOR_ID
   --AND H.LEDGER_ID = 2041--UTSC
   AND H.JE_SOURCE = 'COST MANAGEMENT'
   AND RT.SHIPMENT_HEADER_ID=RSH.SHIPMENT_HEADER_ID
   AND RSH.SHIPMENT_HEADER_ID=RSL.SHIPMENT_HEADER_ID
   AND RT.SHIPMENT_LINE_ID  =RSL.SHIPMENT_LINE_ID
   AND L.PERIOD_NAME       = GPS.PERIOD_NAME
   AND GPS.LEDGER_ID            = L.LEDGER_ID
   AND GPS.APPLICATION_ID       = 101 
  AND (    NVL (GPS.EFFECTIVE_PERIOD_NUM, 0) >=:CF_PERIOD_FROM
        AND (NVL (GPS.EFFECTIVE_PERIOD_NUM, 0) <=:CF_PERIOD_TO)
             )   
  
   --AND H.JE_CATEGORY = 'RECEIVING'
   --AND H.STATUS = 'P'
   --AND H.PERIOD_NAME IN ('MAY-12')
AND H.LEDGER_ID NOT IN ( 1014,1015,1016 ) 
AND   H.CURRENCY_CODE != 'STAT'
AND   H.CURRENCY_CODE =DECODE(:P_CURRENCY_CODE,'INR','INR',
                                              'ALL',H.CURRENCY_CODE,
                                              'NON INR',DECODE(H.CURRENCY_CODE,'INR','###',H.CURRENCY_CODE))
UNION ALL

SELECT 
GPS.PERIOD_NAME,
GJL.REFERENCE_2,
(SELECT  DISTINCT PHA.SEGMENT1  FROM AP_INVOICE_LINES_ALL AILLA, PO_HEADERS_ALL PHA WHERE AILLA.INVOICE_ID=AIA.INVOICE_ID AND AILLA.PO_HEADER_ID=PHA.PO_HEADER_ID) PO_ORDER_NUMBER,

(SELECT DISTINCT RSH.RECEIPT_NUM
FROM RCV_SHIPMENT_HEADERS RSH, RCV_SHIPMENT_LINES RSL,RCV_TRANSACTIONS RT, AP_INVOICE_LINES_ALL AILA
WHERE RSH.SHIPMENT_HEADER_ID=RSL.SHIPMENT_HEADER_ID
AND RSL.SHIPMENT_HEADER_ID=RT.SHIPMENT_HEADER_ID
AND RSL.SHIPMENT_LINE_ID=RT.SHIPMENT_LINE_ID
AND RT.TRANSACTION_ID=(SELECT DISTINCT RCV_TRANSACTION_ID FROM AP_INVOICE_LINES_ALL AILA WHERE AILA.INVOICE_ID=AIA.INVOICE_ID AND ROWNUM=1 AND RCV_TRANSACTION_ID IS NOT NULL) ) "RECEIPT NUMBER",
(SELECT DISTINCT RT.TRANSACTION_DATE
FROM RCV_SHIPMENT_HEADERS RSH, RCV_SHIPMENT_LINES RSL,RCV_TRANSACTIONS RT, AP_INVOICE_LINES_ALL AILA
WHERE RSH.SHIPMENT_HEADER_ID=RSL.SHIPMENT_HEADER_ID
AND RSL.SHIPMENT_HEADER_ID=RT.SHIPMENT_HEADER_ID
AND RSL.SHIPMENT_LINE_ID=RT.SHIPMENT_LINE_ID
AND RT.TRANSACTION_ID=(SELECT DISTINCT RCV_TRANSACTION_ID FROM AP_INVOICE_LINES_ALL AILA WHERE AILA.INVOICE_ID=AIA.INVOICE_ID AND ROWNUM=1 AND RCV_TRANSACTION_ID IS NOT NULL)) "RECEIPT DATE",
        AIA.INVOICE_NUM   "INVOICE NUMBER",
        AIA.GL_DATE   "INVOICE DATE",
        APS.VENDOR_NAME "VENDOR NAME",
        APS.SEGMENT1 "VENDOR NUMBER",
        GJH.JE_SOURCE "SOURCE",
        GJH.LEDGER_ID "SET_OF_BOOKS_ID" ,
        CC.CONCATENATED_SEGMENTS SEGMENT,
        CC.CONCATENATED_SEGMENTS SEGMENT2,
        AIA.PAYMENT_CURRENCY_CODE CURRENCY_CODE,
       XAL.ENTERED_DR,
       XAL.ENTERED_CR,
       XAL.ACCOUNTED_DR,
       XAL.ACCOUNTED_CR,
       GIR.JE_HEADER_ID,
       GIR.JE_LINE_NUM,
       CC.CODE_COMBINATION_ID,
       --GPS.APPLICATION_ID,
       --
       AIA.DOC_SEQUENCE_VALUE "VOCHER NUMBER"
          ,AIA.GL_DATE "GL_DATE"
          ,NULL "STAFF NUMBER"
          ,NULL "PROJECT"
          ,NULL "CUSTOMER NUM/NAME"
          ,XAL.DESCRIPTION "DESCRIPTION"
FROM APPS.AP_INVOICES_ALL AIA,
    XLA.XLA_TRANSACTION_ENTITIES XTE,
    APPS.XLA_EVENTS XEV,
    APPS.XLA_AE_HEADERS XAH,
    APPS.XLA_AE_LINES XAL,
    APPS.GL_IMPORT_REFERENCES GIR,
    APPS.GL_JE_HEADERS GJH,
    APPS.GL_JE_LINES  GJL,
    APPS.GL_CODE_COMBINATIONS_KFV CC,
    APPS.AP_SUPPLIERS APS,
    GL_PERIOD_STATUSES GPS
   
WHERE  AIA.INVOICE_ID = XTE.SOURCE_ID_INT_1
    AND XEV.ENTITY_ID= XTE.ENTITY_ID
    AND XAH.ENTITY_ID= XTE.ENTITY_ID
    AND XAH.EVENT_ID= XEV.EVENT_ID
    AND XAH.AE_HEADER_ID = XAL.AE_HEADER_ID
    --AND XAH.JE_CATEGORY_NAME = 'PURCHASE INVOICES'
    AND XAH.GL_TRANSFER_STATUS_CODE= 'Y'
    AND GJH.STATUS = 'P'
    AND XAL.GL_SL_LINK_ID=GIR.GL_SL_LINK_ID
    AND GIR.GL_SL_LINK_TABLE = XAL.GL_SL_LINK_TABLE
    AND GJL.JE_HEADER_ID=GJH.JE_HEADER_ID
    AND GJH.JE_HEADER_ID=GIR.JE_HEADER_ID
    AND GJL.JE_HEADER_ID=GIR.JE_HEADER_ID
    AND GIR.JE_LINE_NUM=GJL.JE_LINE_NUM
    AND CC.CODE_COMBINATION_ID=XAL.CODE_COMBINATION_ID
    AND CC.CODE_COMBINATION_ID=GJL.CODE_COMBINATION_ID
    AND AIA.VENDOR_ID=APS.VENDOR_ID
    AND GJH.STATUS='P'
    AND GJH.ACTUAL_FLAG='A'
    AND GJH.LEDGER_ID NOT IN ( 1014,1015,1016 )
    --AND GJL.EFFECTIVE_DATE BETWEEN TO_DATE(:P_START_DATE) AND TO_DATE(:P_END_DATE)
    --AND XAL.ACCOUNTING_CLASS_CODE IN ('PREPAID_EXPENSE','ITEM EXPENSE','ACCRUAL','LIABILITY','INTRA')
    --AND CC.SEGMENT1=111
    AND GJH.LEDGER_ID=GPS.LEDGER_ID
    AND  101=GPS.APPLICATION_ID      
    AND GPS.SET_OF_BOOKS_ID=GJH.LEDGER_ID
    AND GJH.JE_SOURCE ='PAYABLES'
    AND GJH.LEDGER_ID NOT IN ( 1014,1015,1016 )
    AND GJH.PERIOD_NAME       = GPS.PERIOD_NAME
    --AND CC.SEGMENT5 IN (:P_MIN_FLEX,:P_MAX_FLEX)
    AND ( NVL(XAL.ACCOUNTED_CR,0)<>0 OR NVL(XAL.ACCOUNTED_DR,0)<>0)
   
    --AND AIA.ORG_ID=:P_ORG
    AND XAH.JE_CATEGORY_NAME='PURCHASE INVOICES'
    AND (    NVL (GPS.EFFECTIVE_PERIOD_NUM, 0) >=:CF_PERIOD_FROM
    AND (NVL (GPS.EFFECTIVE_PERIOD_NUM, 0) <=:CF_PERIOD_TO)
             ) 
--           AND 24116=:P_ACCOUNT_FROM
--           AND 24116=:P_ACCOUNT_TO
                AND (    NVL (CC.SEGMENT5, 0) >= :P_ACCOUNT_FROM
        AND (NVL (CC.SEGMENT5, 0) <= :P_ACCOUNT_TO)
       )
          
     AND (    NVL (CC.SEGMENT1, 0) >=:P_COMPANY_FROM
        AND (NVL (CC.SEGMENT1, 0) <= :P_COMPANY_TO)
       ) 
       AND AIA.PAYMENT_CURRENCY_CODE =DECODE(:P_CURRENCY_CODE,'INR','INR',
                                              'ALL',AIA.PAYMENT_CURRENCY_CODE,
                                              'NON INR',DECODE(AIA.PAYMENT_CURRENCY_CODE,'INR','###',AIA.PAYMENT_CURRENCY_CODE))
                                             
                                             
                                             
UNION ALL
SELECT
              GJH.PERIOD_NAME,
              NULL REFERENCE_2,
              NULL PO_ORDER_NUMBER,
              NULL  "RECEIPT NUMBER",
              NULL "RECEIPT DATE",
          ENT.TRANSACTION_NUMBER "INVOICE NUMBER",
          AIA.CHECK_DATE "INVOICE DATE",
          AV.VENDOR_NAME "VENDOR NAME",
          AV.SEGMENT1 "VENDOR NUMBER",
          GJH.JE_SOURCE "SOURCE",
          GJH.LEDGER_ID "SET_OF_BOOKS_ID" ,
          GCCK.CONCATENATED_SEGMENTS SEGMENT,
          GCCK.CONCATENATED_SEGMENTS SEGMENT21,
          AIA.CURRENCY_CODE CURRENCY_CODE
          ,AEL.ENTERED_DR,
          AEL.ENTERED_CR,
          AEL.ACCOUNTED_DR,
          AEL.ACCOUNTED_CR,
          GIR.JE_HEADER_ID,
          GIR.JE_LINE_NUM,
          GCCK.CODE_COMBINATION_ID
          ,GIR.SUBLEDGER_DOC_SEQUENCE_VALUE "VOCHER NUMBER"
          ,AEH.ACCOUNTING_DATE "GL_DATE"
          ,NULL "STAFF NUMBER"
          ,NULL "PROJECT"
          ,NULL "CUSTOMER NUM/NAME"
          ,AEL.DESCRIPTION "DESCRIPTION"
          FROM
          XLA_AE_HEADERS                   AEH
         ,XLA_AE_LINES                     AEL
         ,XLA_EVENTS                       XLE
         ,XLA_EVENT_TYPES_TL               XET
         ,XLA_TRANSACTION_ENTITIES         ENT
         ,GL_LEDGERS                       GLG
         ,GL_PERIODS                       GLP
         ,XLA_SUBLEDGERS                   XLS
         ,GL_CODE_COMBINATIONS_KFV         GCCK
               ,GL_IMPORT_REFERENCES             GIR
          ,GL_JE_LINES                      GJL
          ,GL_JE_HEADERS                    GJH
          ,GL_JE_BATCHES                    GJB
          --,AP_INVOICE_DISTRIBUTIONS_ALL AID
--          ,AP_INVOICE_LINES_ALL AILA
          ,AP_CHECKS_ALL AIA
          ,GL_PERIOD_STATUSES GPS
          ,AP_SUPPLIERS AV
          --,PA_PROJECT_CUSTOMERS_V PPCV
          --,PA_PROJECTS_ALL PPA
           WHERE  1=1
           AND  AEH.LEDGER_ID              = GLG.LEDGER_ID
           AND  AEL.APPLICATION_ID         =  AEH.APPLICATION_ID
           AND  AEL.AE_HEADER_ID           =  AEH.AE_HEADER_ID
           AND  XLE.APPLICATION_ID         =  AEH.APPLICATION_ID
           AND  XLE.EVENT_ID               =  AEH.EVENT_ID
           AND  XET.APPLICATION_ID         =  XLE.APPLICATION_ID
           AND  XET.EVENT_TYPE_CODE        =  XLE.EVENT_TYPE_CODE
           AND  XET.LANGUAGE               =  USERENV('LANG')
           AND  ENT.APPLICATION_ID         =  AEH.APPLICATION_ID
           AND  ENT.ENTITY_ID              =  AEH.ENTITY_ID
           AND  GLP.PERIOD_NAME            =  AEH.PERIOD_NAME
           AND  GLP.PERIOD_SET_NAME        =  GLG.PERIOD_SET_NAME
           AND  XLS.APPLICATION_ID         =  AEH.APPLICATION_ID
           AND  GCCK.CODE_COMBINATION_ID   =  AEL.CODE_COMBINATION_ID
           AND AEH.ACCOUNTING_ENTRY_STATUS_CODE <> 'N'  AND
AEH.APPLICATION_ID = 200 AND AEH.BALANCE_TYPE_CODE = 'A' AND (
NVL(AEL.ACCOUNTED_CR,0) <> 0
                                       OR NVL(AEL.ACCOUNTED_DR,0) <> 0 )
           AND  GIR.GL_SL_LINK_ID(+)       =  AEL.GL_SL_LINK_ID
           AND  GIR.GL_SL_LINK_TABLE(+)    =  AEL.GL_SL_LINK_TABLE
           AND  GJL.JE_HEADER_ID(+)        =  GIR.JE_HEADER_ID
           AND  GJL.JE_LINE_NUM(+)         =  GIR.JE_LINE_NUM
           AND  GJH.JE_HEADER_ID(+)        =  GIR.JE_HEADER_ID
           AND  GJB.JE_BATCH_ID (+)        =  GIR.JE_BATCH_ID
           AND  DECODE(GJH.JE_HEADER_ID,NULL,'Y',GJH.JE_FROM_SLA_FLAG) IN ('U', 'Y')
           --AND AEL.ACCOUNTING_CLASS_CODE   IN ('ITEM EXPENSE','CASH_CLEARING','LIABILITY','PREPAID_EXPENSE','RTAX','INTRA')
 AND ENT.SOURCE_ID_INT_1 = AIA.CHECK_ID
          -- AND AIA.DOC_SEQUENCE_VALUE=GIR.SUBLEDGER_DOC_SEQUENCE_VALUE
           AND AIA.VENDOR_ID=AV.VENDOR_ID
           AND GJH.JE_SOURCE ='PAYABLES'
           AND AEH.JE_CATEGORY_NAME='PAYMENTS'
AND GJH.LEDGER_ID=GPS.LEDGER_ID
  AND  101=GPS.APPLICATION_ID
  AND GPS.SET_OF_BOOKS_ID=GJH.LEDGER_ID
  AND GJH.LEDGER_ID NOT IN ( 1014,1015,1016 )
AND GJH.PERIOD_NAME       = GPS.PERIOD_NAME
  --AND AIA.ORG_ID=:P_ORG
  AND (    NVL (GPS.EFFECTIVE_PERIOD_NUM, 0) >=:CF_PERIOD_FROM
        AND (NVL (GPS.EFFECTIVE_PERIOD_NUM, 0) <=:CF_PERIOD_TO)
             )
                AND (    NVL (GCCK.SEGMENT5, 0) >= :P_ACCOUNT_FROM
        AND (NVL (GCCK.SEGMENT5, 0) <= :P_ACCOUNT_TO)
       )
     AND (    NVL (GCCK.SEGMENT1, 0) >=:P_COMPANY_FROM
        AND (NVL (GCCK.SEGMENT1, 0) <= :P_COMPANY_TO)
       )
       AND AIA.CURRENCY_CODE =DECODE(:P_CURRENCY_CODE,'INR','INR',
                                              'ALL',AIA.CURRENCY_CODE,
                                              'NON INR',DECODE(AIA.CURRENCY_CODE,'INR','###',AIA.CURRENCY_CODE))
                                             
                                             
                                             
UNION ALL

--ADDED BY MANOHAR
 SELECT     GJH.PERIOD_NAME,
             NULL REFERENCE_2,
             NULL PO_ORDER_NUMBER,
             CASE
             WHEN (GJH.JE_CATEGORY IN ('RECEIPTS','MISC RECEIPTS')) THEN
             XTE.TRANSACTION_NUMBER
             ELSE
               NULL
              END     "RECEIPT NUMBER",
              CASE
             WHEN (GJH.JE_CATEGORY IN ('RECEIPTS','MISC RECEIPTS')) THEN
             XAH.ACCOUNTING_DATE
             ELSE
               NULL
              END     "RECEIPT DATE",
              CASE
              WHEN (GJH.JE_CATEGORY NOT IN ('RECEIPTS','MISC RECEIPTS')) THEN
              XTE.TRANSACTION_NUMBER  ELSE
             NULL
             END    "INVOICE NUMBER",
             CASE
              WHEN (GJH.JE_CATEGORY NOT IN ('RECEIPTS','MISC RECEIPTS')) THEN
               XAH.ACCOUNTING_DATE
               ELSE
             NULL
             END    "INVOICE DATE",
          CASE
              WHEN XAL.PARTY_TYPE_CODE = 'S' THEN
                 (SELECT         APS.SEGMENT1
                        ||'|'||APS.VENDOR_NAME
                        ||'|'||HZP.JGZZ_FISCAL_CODE
                        ||'|'||HZP.TAX_REFERENCE
                        ||'|'||HPS.PARTY_SITE_NUMBER
                        ||'|'||HPS.PARTY_SITE_NAME
                        ||'|'||NULL
                   FROM  AP_SUPPLIERS          APS
                        ,AP_SUPPLIER_SITES_ALL APSS
                        ,HZ_PARTIES            HZP
                        ,HZ_PARTY_SITES        HPS
                        ,XLA_AE_LINES          AEL2
                  WHERE  APS.VENDOR_ID          = AEL2.PARTY_ID
                    AND  HZP.PARTY_ID           = APS.PARTY_ID
                    AND  APSS.VENDOR_SITE_ID(+) = AEL2.PARTY_SITE_ID
                    AND  HPS.PARTY_SITE_ID(+)   = APSS.PARTY_SITE_ID
                    AND  AEL2.APPLICATION_ID    = XAL.APPLICATION_ID
                    AND  AEL2.AE_HEADER_ID      = XAL.AE_HEADER_ID
                    AND  AEL2.AE_LINE_NUM       = XAL.AE_LINE_NUM )
              WHEN (XAL.PARTY_TYPE_CODE = 'C' AND XAL.PARTY_ID IS NOT NULL) THEN
                 (SELECT         --HCA.ACCOUNT_NUMBER||'|'||
                 HZP.PARTY_NAME
                        --||'|'||HZP.JGZZ_FISCAL_CODE
                        --||'|'||HZP.TAX_REFERENCE
                        --||'|'||HPS.PARTY_SITE_NUMBER
                        --||'|'||HPS.PARTY_SITE_NAME
                        --||'|'||HZCU.TAX_REFERENCE
                   FROM  HZ_CUST_ACCOUNTS        HCA
                        ,HZ_CUST_ACCT_SITES_ALL  HCAS
                        ,HZ_CUST_SITE_USES_ALL   HZCU
                        ,HZ_PARTIES              HZP
                        ,HZ_PARTY_SITES          HPS
                        ,XLA_AE_LINES            AEL2
                  WHERE  HCA.CUST_ACCOUNT_ID       = AEL2.PARTY_ID
                    AND  HZP.PARTY_ID              = HCA.PARTY_ID
                    AND  HZCU.SITE_USE_ID(+)       = AEL2.PARTY_SITE_ID
                    AND  HCAS.CUST_ACCT_SITE_ID(+) = HZCU.CUST_ACCT_SITE_ID
                    AND  HPS.PARTY_SITE_ID(+)      = HCAS.PARTY_SITE_ID
                    AND  AEL2.APPLICATION_ID       = XAL.APPLICATION_ID
                    AND  AEL2.AE_HEADER_ID         = XAL.AE_HEADER_ID
                    AND  AEL2.AE_LINE_NUM          = XAL.AE_LINE_NUM )
              ELSE
                NULL
              END       "VENDOR NAME",
              CASE
              WHEN (XAL.PARTY_TYPE_CODE = 'C' AND XAL.PARTY_ID IS NOT NULL) THEN
                 (SELECT         HCA.ACCOUNT_NUMBER
                 --||'|'||  HZP.PARTY_NAME
                        --||'|'||HZP.JGZZ_FISCAL_CODE
                        --||'|'||HZP.TAX_REFERENCE
                        --||'|'||HPS.PARTY_SITE_NUMBER
                        --||'|'||HPS.PARTY_SITE_NAME
                        --||'|'||HZCU.TAX_REFERENCE
                   FROM  HZ_CUST_ACCOUNTS        HCA
                        ,HZ_CUST_ACCT_SITES_ALL  HCAS
                        ,HZ_CUST_SITE_USES_ALL   HZCU
                        ,HZ_PARTIES              HZP
                        ,HZ_PARTY_SITES          HPS
                        ,XLA_AE_LINES            AEL2
                  WHERE  HCA.CUST_ACCOUNT_ID       = AEL2.PARTY_ID
                    AND  HZP.PARTY_ID              = HCA.PARTY_ID
                    AND  HZCU.SITE_USE_ID(+)       = AEL2.PARTY_SITE_ID
                    AND  HCAS.CUST_ACCT_SITE_ID(+) = HZCU.CUST_ACCT_SITE_ID
                    AND  HPS.PARTY_SITE_ID(+)      = HCAS.PARTY_SITE_ID
                    AND  AEL2.APPLICATION_ID       = XAL.APPLICATION_ID
                    AND  AEL2.AE_HEADER_ID         = XAL.AE_HEADER_ID
                    AND  AEL2.AE_LINE_NUM          = XAL.AE_LINE_NUM )
              ELSE
                NULL
              END       "VENDOR NUMBER",
              GJH.JE_SOURCE"SOURCE",
              GJH.LEDGER_ID "SET_OF_BOOKS_ID" ,
                        CC.CONCATENATED_SEGMENTS SEGMENT,
            CC.CONCATENATED_SEGMENTS SEGMENT21,
              XAL.CURRENCY_CODE CURRENCY_CODE,
              XAL.ENTERED_DR,XAL.ENTERED_CR,
              XAL.ACCOUNTED_DR,XAL.ACCOUNTED_CR,
              GIR.JE_HEADER_ID,
            GIR.JE_LINE_NUM,
            CC.CODE_COMBINATION_ID
            ,GIR.SUBLEDGER_DOC_SEQUENCE_VALUE "VOCHER NUMBER"
            ,XAH.ACCOUNTING_DATE "GL_DATE"
            ,NULL "STAFF NUMBER"
            ,NULL "PROJECT"
            ,NULL "CUSTOMER NUM/NAME"
            ,XAH.DESCRIPTION "DESCRIPTION"
 
  FROM XLA.XLA_TRANSACTION_ENTITIES XTE,
      APPS.XLA_EVENTS XEV,
      APPS.XLA_AE_HEADERS XAH,
      APPS.XLA_AE_LINES XAL,
      APPS.GL_IMPORT_REFERENCES GIR,
      APPS.GL_JE_HEADERS GJH,
      APPS.GL_JE_LINES  GJL,
      APPS.GL_CODE_COMBINATIONS_KFV CC,
      --APPS.AP_SUPPLIERS APS,
      GL_PERIOD_STATUSES GPS
     
     
  WHERE  1=1--AIA.INVOICE_ID = XTE.SOURCE_ID_INT_1
      AND XEV.ENTITY_ID= XTE.ENTITY_ID
      AND XAH.ENTITY_ID= XTE.ENTITY_ID
      AND XAH.EVENT_ID= XEV.EVENT_ID
      AND XAH.AE_HEADER_ID = XAL.AE_HEADER_ID
      --AND XAH.JE_CATEGORY_NAME = 'PURCHASE INVOICES'
      AND XAH.GL_TRANSFER_STATUS_CODE= 'Y'
      AND GJH.STATUS = 'P'
      AND XAL.GL_SL_LINK_ID=GIR.GL_SL_LINK_ID
      AND GIR.GL_SL_LINK_TABLE = XAL.GL_SL_LINK_TABLE
      AND GJL.JE_HEADER_ID=GJH.JE_HEADER_ID
      AND GJH.JE_HEADER_ID=GIR.JE_HEADER_ID
      AND GJL.JE_HEADER_ID=GIR.JE_HEADER_ID
      AND GIR.JE_LINE_NUM=GJL.JE_LINE_NUM
      AND CC.CODE_COMBINATION_ID=XAL.CODE_COMBINATION_ID
      AND CC.CODE_COMBINATION_ID=GJL.CODE_COMBINATION_ID
      --AND AIA.VENDOR_ID=APS.VENDOR_ID
      AND GJH.STATUS='P'
      AND GJH.ACTUAL_FLAG='A'
      AND GJH.LEDGER_ID NOT IN ( 1014,1015,1016 )
      --AND GJL.EFFECTIVE_DATE BETWEEN TO_DATE(:P_START_DATE) AND TO_DATE(:P_END_DATE)
      --AND XAL.ACCOUNTING_CLASS_CODE IN ('PREPAID_EXPENSE','ITEM EXPENSE','ACCRUAL','LIABILITY','INTRA')
      --AND CC.SEGMENT1=111
      AND GJH.LEDGER_ID=GPS.LEDGER_ID
    AND  101=GPS.APPLICATION_ID      
    AND GPS.SET_OF_BOOKS_ID=GJH.LEDGER_ID
       AND GJH.JE_SOURCE ='RECEIVABLES'
    AND GJH.LEDGER_ID NOT IN ( 1014,1015,1016 )
    --AND XAL.ENTERED_DR='136547.55'
  AND GJH.PERIOD_NAME       = GPS.PERIOD_NAME
      --AND CC.SEGMENT5 IN (:P_MIN_FLEX,:P_MAX_FLEX)
      AND ( NVL(XAL.ACCOUNTED_CR,0)<>0 OR NVL(XAL.ACCOUNTED_DR,0)<>0)
     
      --AND AIA.ORG_ID=:P_ORG
      --AND XAH.JE_CATEGORY_NAME='PURCHASE INVOICES'
    AND (    NVL (GPS.EFFECTIVE_PERIOD_NUM, 0) >=:CF_PERIOD_FROM
          AND (NVL (GPS.EFFECTIVE_PERIOD_NUM, 0) <=:CF_PERIOD_TO)
               ) 
  --           AND 24116=:P_ACCOUNT_FROM
  --           AND 24116=:P_ACCOUNT_TO
                  AND (    NVL (CC.SEGMENT5, 0) >= :P_ACCOUNT_FROM
          AND (NVL (CC.SEGMENT5, 0) <= :P_ACCOUNT_TO)
         )
            
       AND (    NVL (CC.SEGMENT1, 0) >=:P_COMPANY_FROM
          AND (NVL (CC.SEGMENT1, 0) <= :P_COMPANY_TO)
         )
         --AND XTE.TRANSACTION_NUMBER='101914 - ZPF TDS APRIL 12'
         AND XAL.CURRENCY_CODE =DECODE(:P_CURRENCY_CODE,'INR','INR',
                                                'ALL',XAL.CURRENCY_CODE,
                                                'NON INR',DECODE(XAL.CURRENCY_CODE,'INR','###',XAL.CURRENCY_CODE));