Sub Ledger Break Up Query - Oracle Apps R-12

Sub Ledger Break Up Query - Oracle Apps R-12

SELECT
  GJH.DESCRIPTION JV_HEADER_DESCRIPTION,
  GJH.NAME JV_NAME,
  GJH.JE_CATEGORY,
  GJH.JE_SOURCE,
  GJH.PERIOD_NAME,
  NVL(XAL.ACCOUNTED_CR,0) GL_CR,
  NVL(XAL.ACCOUNTED_DR,0) GL_DR,
  GJL.DESCRIPTION JV_LINE_DESCRIPTION,
  XAH.EVENT_TYPE_CODE,
  XAH.DESCRIPTION SLA_DESCRIPTION,
  XAL.AE_LINE_NUM,
  XAL.ACCOUNTING_DATE GL_DATE,
  ASUP.VENDOR_NAME,
  TO_CHAR(ACA.CHECK_NUMBER),
  ACA.CHECK_DATE,
  ACA.DOC_SEQUENCE_VALUE VOUCHER_NUMBER,
  ACA.CREATION_DATE VOUCHER_DATE,
--  DECODE (XAH.EVENT_TYPE_CODE,'PAYMENT CANCELLED',AMOUNT* NVL(EXCHANGE_RATE,1),'REFUND RECORDED',XAL.ACCOUNTED_DR,
--   0)  RECEIPT,
--  DECODE (XAH.EVENT_TYPE_CODE,'PAYMENT CREATED',AMOUNT* NVL(EXCHANGE_RATE,1),0) PAYMENT
  DECODE(XAL.ACCOUNTED_CR,NULL,XAL.ACCOUNTED_DR,0)  RECEIPT,
  DECODE(XAL.ACCOUNTED_DR,NULL,XAL.ACCOUNTED_CR,0) PAYMENT
FROM
  XLA_AE_HEADERS XAH,
  XLA_AE_LINES XAL,
  GL_JE_LINES GJL,
  GL_IMPORT_REFERENCES GIR,
  GL_JE_HEADERS GJH,
  GL_CODE_COMBINATIONS GCC,
  AP_SUPPLIERS ASUP,
  AP_CHECKS_ALL ACA
WHERE
  XAH.AE_HEADER_ID=XAL.AE_HEADER_ID AND
  GJL.JE_LINE_NUM = GIR.JE_LINE_NUM AND
  GJL.JE_HEADER_ID = GIR.JE_HEADER_ID AND
  GIR.GL_SL_LINK_TABLE = XAL.GL_SL_LINK_TABLE AND
  GIR.GL_SL_LINK_ID = XAL.GL_SL_LINK_ID AND
  GJL.JE_HEADER_ID=GJH.JE_HEADER_ID  AND
  GJL.CODE_COMBINATION_ID=GCC.CODE_COMBINATION_ID AND
  ASUP.VENDOR_ID(+)=XAL.PARTY_ID AND
  ACA.DOC_SEQUENCE_ID(+)=XAH.DOC_SEQUENCE_ID AND
  ACA.DOC_SEQUENCE_VALUE(+)=XAH.DOC_SEQUENCE_VALUE AND
  GCC.SEGMENT5=NVL(:P_ACC_NUM,GCC.SEGMENT5)  AND
  TRUNC(GJH.DEFAULT_EFFECTIVE_DATE) BETWEEN NVL(:P_FROM_DATE,TRUNC(GJH.DEFAULT_EFFECTIVE_DATE)) AND NVL(:P_TO_DATE,TRUNC(GJH.DEFAULT_EFFECTIVE_DATE)) AND
  GJH.STATUS='P' AND
  GJH.JE_SOURCE='Payables'
UNION ALL
------ DATA FROM CASH MANAGEMENT --------------------------------
SELECT
  GJH.DESCRIPTION JV_HEADER_DESCRIPTION,
  GJH.NAME JV_NAME,
  GJH.JE_CATEGORY,
  GJH.JE_SOURCE,
  GJH.PERIOD_NAME,
  NVL(XAL.ACCOUNTED_CR,0) GL_CR,
  NVL(XAL.ACCOUNTED_DR,0) GL_DR,
  GJL.DESCRIPTION JV_LINE_DESCRIPTION,
  XAH.EVENT_TYPE_CODE,
  XAH.DESCRIPTION SLA_DESCRIPTION,
  XAL.AE_LINE_NUM,
  XAL.ACCOUNTING_DATE GL_DATE,
  '' VENDOR_NAME,
  '' CHECK_NUMBER,
  NULL CHECK_DATE,
  NULL VOUCHER_NUMBER,
  NULL VOUCHER_DATE,
  DECODE(XAL.ACCOUNTED_CR,NULL,XAL.ACCOUNTED_DR,0)  RECEIPT,
  DECODE(XAL.ACCOUNTED_DR,NULL,XAL.ACCOUNTED_CR,0) PAYMENT
FROM
  XLA_AE_HEADERS XAH,
  XLA_AE_LINES XAL,
  GL_JE_LINES GJL,
  GL_IMPORT_REFERENCES GIR,
  GL_JE_HEADERS GJH,
  GL_CODE_COMBINATIONS GCC
WHERE
  XAH.AE_HEADER_ID=XAL.AE_HEADER_ID AND
  GJL.JE_LINE_NUM = GIR.JE_LINE_NUM AND
  GJL.JE_HEADER_ID = GIR.JE_HEADER_ID AND
  GIR.GL_SL_LINK_TABLE = XAL.GL_SL_LINK_TABLE AND
  GIR.GL_SL_LINK_ID = XAL.GL_SL_LINK_ID AND
  GJL.JE_HEADER_ID=GJH.JE_HEADER_ID  AND
  GJL.CODE_COMBINATION_ID=GCC.CODE_COMBINATION_ID AND
  GCC.SEGMENT5=NVL(:P_ACC_NUM,GCC.SEGMENT5)  AND
  TRUNC(GJH.DEFAULT_EFFECTIVE_DATE) BETWEEN NVL(:P_FROM_DATE,TRUNC(GJH.DEFAULT_EFFECTIVE_DATE)) AND NVL(:P_TO_DATE,TRUNC(GJH.DEFAULT_EFFECTIVE_DATE)) AND
 -- GJH.PERIOD_NAME IN ('APR-11-12','MAY-11-12','JUN-11-12','JUL-11-12') AND
  GJH.STATUS='P' AND
  GJH.JE_SOURCE='Cash Management' AND
  GJH.JE_CATEGORY='Bank Transfers'
UNION ALL
-------------------Data from Receivable --------------------------------
SELECT GJH.DESCRIPTION JV_HEADER_DESCRIPTION,
      GJH.NAME JV_NAME,
      GJH.JE_CATEGORY,
      GJH.JE_SOURCE,
      GJH.PERIOD_NAME,
      NVL(XAL.ACCOUNTED_CR,0) GL_CR,
      NVL(XAL.ACCOUNTED_DR,0) GL_DR,
      GJL.DESCRIPTION JV_LINE_DESCRIPTION,
      XAH.EVENT_TYPE_CODE,
      XAH.DESCRIPTION SLA_DESCRIPTION,
      XAL.AE_LINE_NUM,
      XAL.ACCOUNTING_DATE GL_DATE,
      (SELECT AC.CUSTOMER_NAME
      FROM AR_CUSTOMERS AC WHERE AC.CUSTOMER_ID=XAL.PARTY_ID) CUSTOMER_NAME,
      (SELECT ACR.RECEIPT_NUMBER FROM AR_CASH_RECEIPTS_ALL ACR
      WHERE ACR.DOC_SEQUENCE_ID=XAH.DOC_SEQUENCE_ID AND
      ACR.DOC_SEQUENCE_VALUE=XAH.DOC_SEQUENCE_VALUE) RECEIPT_NUMBER,
      (SELECT ACR.RECEIPT_DATE FROM AR_CASH_RECEIPTS_ALL ACR
      WHERE ACR.DOC_SEQUENCE_ID=XAH.DOC_SEQUENCE_ID AND
      ACR.DOC_SEQUENCE_VALUE=XAH.DOC_SEQUENCE_VALUE
      ) RECEIPT_DATE,
      (SELECT ACR.DOC_SEQUENCE_VALUE
      FROM AR_CASH_RECEIPTS_ALL ACR
      WHERE ACR.DOC_SEQUENCE_ID=XAH.DOC_SEQUENCE_ID AND
      ACR.DOC_SEQUENCE_VALUE=XAH.DOC_SEQUENCE_VALUE) VOUCHER_NUMBER,
      (SELECT ACR.CREATION_DATE
      FROM AR_CASH_RECEIPTS_ALL ACR
      WHERE ACR.DOC_SEQUENCE_ID=XAH.DOC_SEQUENCE_ID AND
      ACR.DOC_SEQUENCE_VALUE=XAH.DOC_SEQUENCE_VALUE)  VOUCHER_DATE,
      DECODE(XAL.ACCOUNTED_CR,NULL,XAL.ACCOUNTED_DR,0)  RECEIPT,
      DECODE(XAL.ACCOUNTED_DR,NULL,XAL.ACCOUNTED_CR,0) PAYMENT
  FROM GL_JE_BATCHES GJB,
       GL_JE_HEADERS GJH,
       GL_JE_LINES GJL,
       GL_CODE_COMBINATIONS GCC,
       GL_IMPORT_REFERENCES GIR,
       XLA_AE_LINES XAL,
       XLA_AE_HEADERS XAH,
       XLA.XLA_TRANSACTION_ENTITIES XTE
WHERE GJB.JE_BATCH_ID = GJH.JE_BATCH_ID
   AND GJH.JE_HEADER_ID = GJL.JE_HEADER_ID
   AND GJL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
   AND GJL.JE_HEADER_ID = GIR.JE_HEADER_ID
   AND GJL.JE_LINE_NUM = GIR.JE_LINE_NUM
   AND GIR.GL_SL_LINK_ID = XAL.GL_SL_LINK_ID
   AND GIR.GL_SL_LINK_TABLE = XAL.GL_SL_LINK_TABLE
   AND XAL.AE_HEADER_ID = XAH.AE_HEADER_ID
   AND XTE.APPLICATION_ID = XAH.APPLICATION_ID
   AND XTE.ENTITY_ID = XAH.ENTITY_ID
   AND GJL.STATUS = 'P'
   AND GCC.SEGMENT5 = NVL (:P_ACC_NUM, GCC.SEGMENT5)
   AND TRUNC (GJH.DEFAULT_EFFECTIVE_DATE)
          BETWEEN NVL (:P_FROM_DATE, TRUNC (GJH.DEFAULT_EFFECTIVE_DATE))
              AND NVL (:P_TO_DATE, TRUNC (GJH.DEFAULT_EFFECTIVE_DATE))
  AND GJH.JE_SOURCE = 'Receivables'
  UNION ALL
 ---------------- Manual -----------------------
SELECT GJH.DESCRIPTION JV_HEADER_DESCRIPTION,
       GJH.NAME JV_NAME,
       GJH.JE_CATEGORY,
       GJH.JE_SOURCE,
       GJH.PERIOD_NAME,
       NVL(GJL.ACCOUNTED_DR,0) ACCOUNTED_DR,
       NVL(GJL.ACCOUNTED_CR,0) ACCOUNTED_CR,
       gjl.description jv_line_description,
        '' EVENT_TYPE_CODE,
        '' SLA_DESCRIPTION,
        NULL AE_LINE_NUM,
        GJH.DEFAULT_EFFECTIVE_DATE GL_DATE,
        '' VENDOR_NAME,
        '' CHECK_NUMBER,
        NULL CHECK_DATE,
        NULL VOUCHER_NUMBER,
        NULL VOUCHER_DATE,
        NVL(GJL.ACCOUNTED_DR,0) RECEIPT,
        NVL(GJL.ACCOUNTED_CR,0) PAYMENT
 FROM GL_JE_BATCHES GJB,
      GL_JE_HEADERS GJH,
      GL_JE_LINES GJL,
      GL_CODE_COMBINATIONS GCC
 WHERE GJB.JE_BATCH_ID = GJH.JE_BATCH_ID
   AND GJH.JE_HEADER_ID = GJL.JE_HEADER_ID
   AND GJL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
   AND GJL.STATUS = 'P'
   AND GCC.SEGMENT5 = NVL (:P_ACC_NUM, GCC.SEGMENT5)
   AND TRUNC (GJH.DEFAULT_EFFECTIVE_DATE)
          BETWEEN NVL (:P_FROM_DATE, TRUNC (GJH.DEFAULT_EFFECTIVE_DATE))
              AND NVL (:P_TO_DATE, TRUNC (GJH.DEFAULT_EFFECTIVE_DATE))
   AND GJH.JE_SOURCE = 'Manual'
UNION ALL
-----ALL OTHER SOURCES OTHER THAN ABOVE----------
SELECT
GJH.DESCRIPTION JV_HEADER_DESCRIPTION,
  GJH.NAME JV_NAME,
  GJH.JE_CATEGORY,
  GJH.JE_SOURCE,
  GJH.PERIOD_NAME,
  NVL(XAL.ACCOUNTED_CR,0) GL_CR,
  NVL(XAL.ACCOUNTED_DR,0) GL_DR,
  GJL.DESCRIPTION JV_LINE_DESCRIPTION,
  XAH.EVENT_TYPE_CODE,
  XAH.DESCRIPTION SLA_DESCRIPTION,
  XAL.AE_LINE_NUM,
  XAL.ACCOUNTING_DATE GL_DATE,
  '' VENDOR_NAME,
  '' CHECK_NUMBER,
  NULL CHECK_DATE,
  NULL VOUCHER_NUMBER,
  NULL VOUCHER_DATE,
  DECODE(XAL.ACCOUNTED_CR,NULL,XAL.ACCOUNTED_DR,0)  RECEIPT,
  DECODE(XAL.ACCOUNTED_DR,NULL,XAL.ACCOUNTED_CR,0) PAYMENT
FROM
  XLA_AE_HEADERS XAH,
  XLA_AE_LINES XAL,
  GL_JE_LINES GJL,
  GL_IMPORT_REFERENCES GIR,
  GL_JE_HEADERS GJH,
  GL_CODE_COMBINATIONS GCC
WHERE
  XAH.AE_HEADER_ID=XAL.AE_HEADER_ID AND
  GJL.JE_LINE_NUM = GIR.JE_LINE_NUM AND
  GJL.JE_HEADER_ID = GIR.JE_HEADER_ID AND
  GIR.GL_SL_LINK_TABLE = XAL.GL_SL_LINK_TABLE AND
  GIR.GL_SL_LINK_ID = XAL.GL_SL_LINK_ID AND
  GJL.JE_HEADER_ID=GJH.JE_HEADER_ID  AND
  GJL.CODE_COMBINATION_ID=GCC.CODE_COMBINATION_ID AND
  GCC.SEGMENT5=NVL(:P_ACC_NUM,GCC.SEGMENT5)  AND
  TRUNC(GJH.DEFAULT_EFFECTIVE_DATE) BETWEEN NVL(:P_FROM_DATE,TRUNC(GJH.DEFAULT_EFFECTIVE_DATE)) AND NVL(:P_TO_DATE,TRUNC(GJH.DEFAULT_EFFECTIVE_DATE)) AND
  GJH.STATUS='P' AND
  GJH.JE_SOURCE NOT IN ('Receivables','Payables','Cash Management')

GL to AR (Receivable) Query -R12

Q1:- -------------------------------------------------------------------------------------------------
SELECT GJH.NAME,
        GJH.DESCRIPTION,
        TO_CHAR(GJH.DEFAULT_EFFECTIVE_DATE, 'dd-MON-yyyy') EFF_DATE,
        (SELECT HP.PARTY_NAME
            FROM RA_CUSTOMER_TRX_ALL RCTA
            , HZ_CUST_ACCOUNTS_ALL HCA
            , HZ_PARTIES HP
            WHERE RCTA.BILL_TO_CUSTOMER_ID = HCA.CUST_ACCOUNT_ID
            AND HCA.PARTY_ID = HP.PARTY_ID
            AND RCTA.CUSTOMER_TRX_ID = XTE.SOURCE_ID_INT_1) PARTY,
           (SELECT NVL(TO_CHAR(RCTA.DOC_SEQUENCE_VALUE),RCTA.TRX_NUMBER)
                FROM RA_CUSTOMER_TRX_ALL RCTA
                WHERE RCTA.CUSTOMER_TRX_ID = XTE.SOURCE_ID_INT_1
           ) DOC_SEQUENCE_VALUE,
       GJH.JE_CATEGORY,
       XAL.ACCOUNTED_DR ACCOUNTED_DR,
       XAL.ACCOUNTED_CR ACCOUNTED_CR,
       GJL.JE_HEADER_ID,
       XAL.PARTY_TYPE_CODE,
       GJH.JE_SOURCE,
       GJH.PERIOD_NAME,
       GCC.SEGMENT5,
       GJL.JE_LINE_NUM,
       GJH.DEFAULT_EFFECTIVE_DATE
  FROM GL_JE_BATCHES GJB,
       GL_JE_HEADERS GJH,
       GL_JE_LINES GJL,
       GL_CODE_COMBINATIONS GCC,
       GL_IMPORT_REFERENCES GIR,
       XLA_AE_LINES XAL,
       XLA_AE_HEADERS XAH,
       XLA.XLA_TRANSACTION_ENTITIES XTE
WHERE GJB.JE_BATCH_ID = GJH.JE_BATCH_ID
   AND GJH.JE_HEADER_ID = GJL.JE_HEADER_ID
   AND GJL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
   AND GJL.JE_HEADER_ID = GIR.JE_HEADER_ID
   AND GJL.JE_LINE_NUM = GIR.JE_LINE_NUM
   AND GIR.GL_SL_LINK_ID = XAL.GL_SL_LINK_ID
   AND GIR.GL_SL_LINK_TABLE = XAL.GL_SL_LINK_TABLE
   AND XAL.AE_HEADER_ID = XAH.AE_HEADER_ID
   AND XTE.APPLICATION_ID = XAH.APPLICATION_ID
   AND XTE.ENTITY_ID = XAH.ENTITY_ID
   AND GJL.STATUS = 'P'
   AND GCC.SEGMENT5 = NVL (:ACCOUNT_ID, GCC.SEGMENT5)
   AND TRUNC (GJH.DEFAULT_EFFECTIVE_DATE)
          BETWEEN NVL (:PERIOD_FROM, TRUNC (GJH.DEFAULT_EFFECTIVE_DATE))
              AND NVL (:PERIOD_TO, TRUNC (GJH.DEFAULT_EFFECTIVE_DATE))
  AND GJH.JE_SOURCE = 'Receivables'
----------------------------------------------------------------------------------------------------------
Q2:- --------------------------------------------------------------------------------------------------


SELECT GJH.DESCRIPTION JV_HEADER_DESCRIPTION,
      GJH.NAME JV_NAME,
      GJH.JE_CATEGORY,
      GJH.JE_SOURCE,
      GJH.PERIOD_NAME,
      NVL(XAL.ACCOUNTED_CR,0) GL_CR,
      NVL(XAL.ACCOUNTED_DR,0) GL_DR,
      GJL.DESCRIPTION JV_LINE_DESCRIPTION,
      XAH.EVENT_TYPE_CODE,
      XAH.DESCRIPTION SLA_DESCRIPTION,
      XAL.AE_LINE_NUM,
      XAL.ACCOUNTING_DATE GL_DATE,
      (SELECT AC.CUSTOMER_NAME
      FROM AR_CUSTOMERS AC WHERE AC.CUSTOMER_ID=XAL.PARTY_ID) CUSTOMER_NAME,
      (SELECT ACR.RECEIPT_NUMBER FROM AR_CASH_RECEIPTS_ALL ACR
      WHERE ACR.DOC_SEQUENCE_ID=XAH.DOC_SEQUENCE_ID AND
      ACR.DOC_SEQUENCE_VALUE=XAH.DOC_SEQUENCE_VALUE) RECEIPT_NUMBER,
      (SELECT ACR.RECEIPT_DATE FROM AR_CASH_RECEIPTS_ALL ACR
      WHERE ACR.DOC_SEQUENCE_ID=XAH.DOC_SEQUENCE_ID AND
      ACR.DOC_SEQUENCE_VALUE=XAH.DOC_SEQUENCE_VALUE
      ) RECEIPT_DATE,
      (SELECT ACR.DOC_SEQUENCE_VALUE
      FROM AR_CASH_RECEIPTS_ALL ACR
      WHERE ACR.DOC_SEQUENCE_ID=XAH.DOC_SEQUENCE_ID AND
      ACR.DOC_SEQUENCE_VALUE=XAH.DOC_SEQUENCE_VALUE) VOUCHER_NUMBER,
      (SELECT ACR.CREATION_DATE
      FROM AR_CASH_RECEIPTS_ALL ACR
      WHERE ACR.DOC_SEQUENCE_ID=XAH.DOC_SEQUENCE_ID AND
      ACR.DOC_SEQUENCE_VALUE=XAH.DOC_SEQUENCE_VALUE)  VOUCHER_DATE,
      DECODE(XAL.ACCOUNTED_CR,NULL,XAL.ACCOUNTED_DR,0)  RECEIPT,
      DECODE(XAL.ACCOUNTED_DR,NULL,XAL.ACCOUNTED_CR,0) PAYMENT
  FROM GL_JE_BATCHES GJB,
       GL_JE_HEADERS GJH,
       GL_JE_LINES GJL,
       GL_CODE_COMBINATIONS GCC,
       GL_IMPORT_REFERENCES GIR,
       XLA_AE_LINES XAL,
       XLA_AE_HEADERS XAH,
       XLA.XLA_TRANSACTION_ENTITIES XTE
WHERE GJB.JE_BATCH_ID = GJH.JE_BATCH_ID
   AND GJH.JE_HEADER_ID = GJL.JE_HEADER_ID
   AND GJL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
   AND GJL.JE_HEADER_ID = GIR.JE_HEADER_ID
   AND GJL.JE_LINE_NUM = GIR.JE_LINE_NUM
   AND GIR.GL_SL_LINK_ID = XAL.GL_SL_LINK_ID
   AND GIR.GL_SL_LINK_TABLE = XAL.GL_SL_LINK_TABLE
   AND XAL.AE_HEADER_ID = XAH.AE_HEADER_ID
   AND XTE.APPLICATION_ID = XAH.APPLICATION_ID
   AND XTE.ENTITY_ID = XAH.ENTITY_ID
   AND GJL.STATUS = 'P'
   AND GCC.SEGMENT5 = NVL (:P_ACC_NUM, GCC.SEGMENT5)
   AND TRUNC (GJH.DEFAULT_EFFECTIVE_DATE)
          BETWEEN NVL (:P_FROM_DATE, TRUNC (GJH.DEFAULT_EFFECTIVE_DATE))
              AND NVL (:P_TO_DATE, TRUNC (GJH.DEFAULT_EFFECTIVE_DATE))
  AND GJH.JE_SOURCE = 'Receivables'

GL to AP (Payable) Query....

Q1:--------------------------------------------------------------------------------------------
SELECT GJH.NAME,
       GJH.DESCRIPTION,
       TO_CHAR(GJH.DEFAULT_EFFECTIVE_DATE, 'dd-MON-yyyy') EFF_DATE,
       DECODE (XTE.ENTITY_CODE,
               'AP_INVOICES', PV.VENDOR_NAME,
               (SELECT AC.VENDOR_NAME
                  FROM AP_CHECKS_ALL AC
                 WHERE XTE.SOURCE_ID_INT_1 = AC.CHECK_ID)
       ) PARTY,
       AIA.INVOICE_NUM DOC_SEQUENCE_VALUE,
       GJH.JE_CATEGORY,
       XAL.ACCOUNTED_DR ACCOUNTED_DR,
       XAL.ACCOUNTED_CR ACCOUNTED_CR,
       GJL.JE_HEADER_ID,
       XAL.PARTY_TYPE_CODE,
       GJH.JE_SOURCE,
       GJH.PERIOD_NAME,
       GCC.SEGMENT5,
       GJL.JE_LINE_NUM,
       GJH.DEFAULT_EFFECTIVE_DATE
  FROM GL_JE_BATCHES GJB,
       GL_JE_HEADERS GJH,
       GL_JE_LINES GJL,
       GL_CODE_COMBINATIONS GCC,
       GL_IMPORT_REFERENCES GIR,
       XLA_AE_LINES XAL,
       XLA_AE_HEADERS XAH,
       XLA.XLA_TRANSACTION_ENTITIES XTE,
       AP_INVOICES_ALL AIA,
       PO_VENDORS PV
 WHERE GJB.JE_BATCH_ID = GJH.JE_BATCH_ID
   AND GJH.JE_HEADER_ID = GJL.JE_HEADER_ID
   AND GJL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
   AND GJL.JE_HEADER_ID = GIR.JE_HEADER_ID
   AND GJL.JE_LINE_NUM = GIR.JE_LINE_NUM
   AND GIR.GL_SL_LINK_ID = XAL.GL_SL_LINK_ID
   AND GIR.GL_SL_LINK_TABLE = XAL.GL_SL_LINK_TABLE
   AND XAL.AE_HEADER_ID = XAH.AE_HEADER_ID
   AND XTE.APPLICATION_ID = XAH.APPLICATION_ID
   AND XTE.ENTITY_ID = XAH.ENTITY_ID
   AND AIA.INVOICE_ID(+) = XTE.SOURCE_ID_INT_1
   AND AIA.VENDOR_ID = PV.VENDOR_ID(+)
   AND GJL.STATUS = 'P'
   AND GCC.SEGMENT5 = NVL (:ACCOUNT_ID, GCC.SEGMENT5)
   AND TRUNC (GJH.DEFAULT_EFFECTIVE_DATE)
          BETWEEN NVL (:PERIOD_FROM, TRUNC (GJH.DEFAULT_EFFECTIVE_DATE))
              AND NVL (:PERIOD_TO, TRUNC (GJH.DEFAULT_EFFECTIVE_DATE))
  AND GJH.JE_SOURCE = 'Payables'
-------------------------------------------------------------------------------------------------------
Q2:- --------------------------------------------------------------------------------------------------

SELECT
  GJH.DESCRIPTION JV_HEADER_DESCRIPTION,
  GJH.NAME JV_NAME,
  GJH.JE_CATEGORY,
  GJH.JE_SOURCE,
  GJH.PERIOD_NAME,
  NVL(XAL.ACCOUNTED_CR,0) GL_CR,
  NVL(XAL.ACCOUNTED_DR,0) GL_DR,
  GJL.DESCRIPTION JV_LINE_DESCRIPTION,
  XAH.EVENT_TYPE_CODE,
  XAH.DESCRIPTION SLA_DESCRIPTION,
  XAL.AE_LINE_NUM,
  XAL.ACCOUNTING_DATE GL_DATE,
  ASUP.VENDOR_NAME,
  TO_CHAR(ACA.CHECK_NUMBER),
  ACA.CHECK_DATE,
  ACA.DOC_SEQUENCE_VALUE VOUCHER_NUMBER,
  ACA.CREATION_DATE VOUCHER_DATE,
  DECODE(XAL.ACCOUNTED_CR,NULL,XAL.ACCOUNTED_DR,0)  RECEIPT,
  DECODE(XAL.ACCOUNTED_DR,NULL,XAL.ACCOUNTED_CR,0) PAYMENT
FROM
  XLA_AE_HEADERS XAH,
  XLA_AE_LINES XAL,
  GL_JE_LINES GJL,
  GL_IMPORT_REFERENCES GIR,
  GL_JE_HEADERS GJH,
  GL_CODE_COMBINATIONS GCC,
  AP_SUPPLIERS ASUP,
  AP_CHECKS_ALL ACA
WHERE
  XAH.AE_HEADER_ID=XAL.AE_HEADER_ID AND
  GJL.JE_LINE_NUM = GIR.JE_LINE_NUM AND
  GJL.JE_HEADER_ID = GIR.JE_HEADER_ID AND
  GIR.GL_SL_LINK_TABLE = XAL.GL_SL_LINK_TABLE AND
  GIR.GL_SL_LINK_ID = XAL.GL_SL_LINK_ID AND
  GJL.JE_HEADER_ID=GJH.JE_HEADER_ID  AND
  GJL.CODE_COMBINATION_ID=GCC.CODE_COMBINATION_ID AND
  ASUP.VENDOR_ID(+)=XAL.PARTY_ID AND
  ACA.DOC_SEQUENCE_ID(+)=XAH.DOC_SEQUENCE_ID AND
  ACA.DOC_SEQUENCE_VALUE(+)=XAH.DOC_SEQUENCE_VALUE AND
  GCC.SEGMENT5=NVL(:P_ACC_NUM,GCC.SEGMENT5)  AND
  TRUNC(GJH.DEFAULT_EFFECTIVE_DATE) BETWEEN NVL(:P_FROM_DATE,TRUNC(GJH.DEFAULT_EFFECTIVE_DATE)) AND NVL(:P_TO_DATE,TRUNC(GJH.DEFAULT_EFFECTIVE_DATE)) AND
  GJH.STATUS='P' AND
  GJH.JE_SOURCE='Payables'

Monday, 21 November 2011

Query to find Concurrent Program Respo and Request Group name

SELECT DISTINCT
  FCPL.USER_CONCURRENT_PROGRAM_NAME
, FCP.CONCURRENT_PROGRAM_NAME
, FAPP.APPLICATION_NAME
, FRG.REQUEST_GROUP_NAME
, FNRTL.RESPONSIBILITY_NAME
FROM
  APPS.FND_REQUEST_GROUPS FRG
, APPS.FND_APPLICATION_TL FAPP
, APPS.FND_REQUEST_GROUP_UNITS FRGU
, APPS.FND_CONCURRENT_PROGRAMS FCP
, APPS.FND_CONCURRENT_PROGRAMS_TL FCPL
, APPS.FND_RESPONSIBILITY FNR
, APPS.FND_RESPONSIBILITY_TL FNRTL
WHERE
          FRG.APPLICATION_ID=fapp.APPLICATION_ID
AND FRG.APPLICATION_ID = FRGU.APPLICATION_ID
AND FRG.REQUEST_GROUP_ID = FRGU.REQUEST_GROUP_ID
AND FRG.REQUEST_GROUP_ID = FNR.REQUEST_GROUP_ID
AND FRG.APPLICATION_ID = FNR.APPLICATION_ID
AND FNR.RESPONSIBILITY_ID = FNRTL.RESPONSIBILITY_ID
AND FRGU.REQUEST_UNIT_ID = FCP.CONCURRENT_PROGRAM_ID
AND FRGU.UNIT_APPLICATION_ID = FCP.APPLICATION_ID
AND FCP.CONCURRENT_PROGRAM_ID = FCPL.CONCURRENT_PROGRAM_ID
AND FCPL.USER_CONCURRENT_PROGRAM_NAME LIKE :conc_prg_name
AND FNRTL.LANGUAGE = 'US'
AND FAPP.LANGUAGE = 'US';

Apps Profile Definition Table Info

Profile definition is stored in the following tables:
FND_PROFILE_OPTIONS
FND_PROFILE_OPTIONS_TL
These tables can be joined by column PROFILE_OPTION_NAME.

The value for a profile is stored in the following table:
FND_PROFILE_OPTION_VALUES
We can use the following statement to retrieve the value of a profile during run time:

l_profile_value := FND_PROFILE.VALUE(‘<Profile Short Name>’);

One of the most widely used profile is ‘MO: Operating Unit’ Profile. This profile has a code of ORG_ID. To get the value of current operating unit, use the following statement:

l_org_id := FND_PROFILE.VALUE(‘ORG_ID’);

To set a particular operating unit (for example, in SQLPLUS or TOAD), use the following PL/SQL code:
BEGIN
DBMS_APPLICATION_INFO.SET_CLIENT_INFO(‘204’);
--204 is the ORG_ID value.
END;

Wednesday, 17 August 2011

WHO COLUMNS

WHO columns are used to track the information updated or inserted by the users against the tables. FND_STANDARD package is used for this purpose. FND_STANDARD.SET_WHO Procedure is used to update the WHO columns in a Table when a DML operation s (i.e. INSERT, UPDATE) performed.

        1) Created by

        2) Creation date

        3) Last _updated_by

        4) last_update_date

        5) last_update_login

Use fnd_profile.VALUE (‘USER_ID’) for retrieving the user_id which will be used by created_by column.
Creation date and last_update_date will be normally SYSDATE.
last_updated_by is same as created_by.
Use USERENV (‘SESSIONID’) for getting the last_update_login id.

Friday, 5 August 2011

Oracle Forms Questions

1. How do you control the constraints in forms? 

This can be done by selecting the Use Constrain Property to ON.

2.When will ON-VALIDATE-FIELD trigger executed? 

ON-VALIDATE-FIELD triggers are used for field validation. It fires when the field validation status is New or changed. However, if the field status is already valid then any further change to the value in the field will not fire this trigger.

3. What is the difference between system.current_field and system.cursor_field? 

The only difference between these two is that System.current_field gives name of the field and System.cursor_field gives name of the field with block name.

4. What are dynamic reports? How will you create them? 

Following steps should be followed to build a Dynamic Report:

Create a temporary table first.
Then insert data to the temporary table using the After Form Trigger.
Generate the report using this temporary table in the Data Model.
Delete all records from the temporary table in the After Report trigger.

5. Difference between Oracle Forms and Apps Forms.

Oracle Forms

Oracle Forms renders applications using metadata stored in an .fmx file.
It runs client-side PL/SQL.
It is accessed using a web browser and its user interface is rendered using a JVM.
It uses exact positioning.
It provides robust field-level validation and event processing.
It uses BI Beans as its integrated charting engine.
It supports a range of locking models with pessimistic as the default.
Each connected user in It maintains a synchronous connection to the Oracle database.
It uses synchronous connections to allow transactions to span multiple screen interactions.
With Oracle Forms, Application logic is processed in the Oracle database, a mid-tier Forms Server, or in the rich client.

Apps Forms

It renders applications using metadata stored in an Oracle database.
It uses server-side PL/SQL.
It is also invoked from a Web browser but its user interface is HTML and JavaScript.
It uses HTML-relative positioning.
It supports declarative page-level validation and event processing. Programmatic field-level validation and event processing requires Javascript and AJAX.
It uses Flash Charts as its integrated charting engine.
Due to its asynchronous architecture, It uses an optimistic locking model.
It does not transparently allow transactions to span page views. It programmatically supports transactions spanning page views using collections.
Its users are asynchronously connected to the Oracle database.
With Apps Forms, PL/SQL application logic is processed within the Oracle database. Client-side logic is implemented using JavaScript. HTTP communications are facilitated using Apache and Mod/PLSQL.

6. Sequence of firing triggers in forms. 

The following sequence should be used to fire triggers when a form opens:

1. Pre-Form
2. Pre-Block
3. Pre-Record
4. Pre-Text-Item
5. When-New-Form-Instance
6. When-New-Block-Instance
7. When-New-Record-Instance
8. When-New-Item-Instance

The folowing sequence should be followed when you exit from the form:

1. post text item
2. post record
3. post block
4. post form

7. What is a record Group? What are different types of record group? 

A record group is an oracle forms data structure that has a column/row framework similar to a database table.

There are three types of record groups: query record groups, non-query record groups, and static record groups.

Query record group
A query record group is a record group that has an associated SELECT statement. Query record groups can be created and modified at design time or at runtime.

Non-query record group
A non-query record group is a group that does not have an associated query. Non-query record groups can be created and modified only at runtime.

Static record group
A static record group is not associated with a query. Static record groups can be created and modified only at design time.

8. What is a trace file and how is it created? 

The files that are created when an oracle background process encounters an exception are Trace files.

Processes like dbwr, lgwr, pmon, smon create them.

Trace files are also created by ORA-00600 error or due to some diagnostic dump events

9. Can a single canvas have multiple windows ? 
ANs. NO, In a window we have more than one canvas .

Create XML Publisher report without RDF

Create XML Publisher report without RDF

 Create XML Publisher report without RDF

 1. Create a Data Definition using XML Publisher Administration Responsibility As shown below.

2. Click on Create Data Definition button and the details (Make sure you enter the concurrent program short name in Code) and click on Apply button

3. Go back to Data definition and add the Data Template file as shown below (attached sample template file for your reference , make necessary changes to template for your report

<?xml version = "1.0" encoding = "UTF-8"?>
<dataTemplate name="XXAR_CLIENT_DET_CASH_PYMT" version="1.0">
<properties>
<property name="debug_mode" value="on"/>
</properties>
 <parameters>
  </parameters>
<dataQuery>
   <sqlStatement name="Q_CLNT_DET">
       <![CDATA[
       select   distinct hca.account_number,account_name,
         hl.address4 Legacy_num,hca.attribute2 LOB,
         decode(hcsua.org_id,2,'US',22,'CAN') org_id,
         decode(hca.attribute12,'Y','CASH') Payment_Mode,
         trunc(TO_DATE(acrmv.start_date,'DD-MON-YY')) START_DATE,trunc(TO_DATE(acrmv.end_date,'DD-MON-YY')) END_DATE,
         acrm.name PAYMENT_METHOD,
         hcsua.site_use_code,
         hl.address1||','||hl.address2||','||hl.address3||','||hl.address4||','||hl.CITY||','||hl.state||','||hl.POSTAL_CODE||','||hl.country BILL_TO_ADDRESS
from
hz_locations hl,
HZ_PARTY_SITES hps,
hz_parties party,
HZ_CUST_ACCOUNTS hca,
HZ_CUST_ACCT_SITES_ALL hcasa,
HZ_CUST_SITE_USES_ALL hcsua,
HZ_CUSTOMER_PROFILES hcp,
ar_cust_receipt_methods_v acrmv,
ar_receipt_methods acrm
where
hps.location_id=hl.location_id
and party.party_id=hps.party_id
and party.party_id=hca.party_id
and hcasa.CUST_ACCOUNT_ID=hca.CUST_ACCOUNT_ID
and hcsua.CUST_ACCT_SITE_ID=hcasa.CUST_ACCT_SITE_ID
and hcp.SITE_USE_ID=hcsua.SITE_USE_ID
and hps.party_site_id=hcasa.party_site_id
and hca.cust_account_id = acrmv.customer_id
and acrmv.receipt_method_id = acrm.receipt_method_id
and hca.attribute12 = 'Y'
and hcsua.site_use_code = 'BILL_TO'
AND hcasa.BILL_TO_FLAG = 'P'
--and rownum = 1
      ]]>
   </sqlStatement>
 
</dataQuery>
<dataStructure>
     
         <group name="G_CLNT_DET" source="Q_CLNT_DET">
                    <element name ="ACCOUNT_NAME" value="ACCOUNT_NAME"/>
                    <element name ="ACCOUNT_NUMBER" value="ACCOUNT_NUMBER"/>
                    <element name ="LEGACY_NUM" value="LEGACY_NUM"/>             
                    <element name ="LOB" value="LOB"/>
                    <element name ="ORG_ID" value="ORG_ID"/>
                    <element name ="PAYMENT_MODE" value="PAYMENT_MODE"/>
                    <element name ="START_DATE" value="START_DATE"/>
                    <element name ="END_DATE" value="END_DATE"/>
                    <element name ="PAYMENT_METHOD" value="PAYMENT_METHOD"/>
                    <element name ="SITE_USE_CODE" value="SITE_USE_CODE"/>
                    <element name ="BILL_TO_ADDRESS" value="BILL_TO_ADDRESS"/>
                                       
   </group>
</dataStructure>
</dataTemplate>

4. Once you create the Data definition, please create the respective concurrent program for this report if one does not already exists. Make sure the execute for this concurrent program should XDODTEXE

5.    Make sure to enter the Token in the parameters screen. Also noted that this token should match to your parameters name in Data Template file.

6.    Once finished creation of this, please go ahead and create Template for this concurrent program using xml file generated from output of this concurrent request.