Remove Sunday Between To dates of a month

SELECT (TO_DATE(:TO_DATE) - (TO_DATE(:from_date)) + 1) -
       (SELECT COUNT(days)
          FROM (SELECT (TO_DATE(TO_CHAR(TO_DATE(:from_date), 'YYYYMMDD'),
                                'YYYYMMDD') + LEVEL - 1) days
                  FROM DUAL
                CONNECT BY LEVEL <=
                           TO_NUMBER(TO_CHAR(TO_DATE(:TO_DATE), 'DD')))
         WHERE TO_CHAR(days, 'DY') = 'SUN') AS total_working_days
  FROM DUAL

Converting Numeric Value to Words In Oracle

This Function will convert a numeric value into Word..
--------------------------------------------------------

You need to pass the numberic valueas a Parameter..
and it will return a words

CREATE OR REPLACE FUNCTION APPS.num_to_word (val_in IN NUMBER)
   RETURN VARCHAR2
IS
   word      VARCHAR2 (140);
   char_in   VARCHAR2 (5);
   val_now   NUMBER;
   num       NUMBER;
   unit      NUMBER;
   pos       NUMBER;
   npos      NUMBER;
   nxt       NUMBER;
   both      VARCHAR2 (1);
BEGIN
   pos := 1;
   val_now := val_in;
   word := ' Only';
   LOOP
      EXIT WHEN nxt = 0;
      IF pos = 1
      THEN
         nxt := TRUNC (val_now);
         /*            message(to_char(nxt),acknowledge);
                     pause;    */
         num := (val_now - nxt) * 100;
      /*            message(to_char(num),acknowledge);
                  pause;    */
      ELSIF ( (pos = 3) OR (pos = 7))
      THEN
         num := (val_now) MOD (10);
         nxt := (val_now - num) / 10;
      ELSE
         num := (val_now) MOD (100);
         nxt := (val_now - num) / 100;
      END IF;
      IF (num > 19)
      THEN
         unit := (num) MOD (10);
         /*            message('unit : '||to_char(unit),acknowledge);
                     pause;    */
         num := (num - unit) / 10;
      /*            message('num : '||to_char(num),acknowledge);
                  pause;    */
      ELSE
         unit := num;
         num := 0;
      END IF;
      npos := pos;
      IF (nxt > 0)
      THEN
         pos := pos + 1;
         val_now := nxt;
      END IF;
      IF ( (num != 0) AND (unit != 0))
      THEN
         both := '-';
      ELSE
         both := '';
      END IF;
      IF ( (num != 0) OR (unit != 0) OR (npos = 6))
      THEN
         IF npos = 1
         THEN
            WORD :=
                  return_tens (num)
               || both
               || return_unit (unit)
               || 'Paise '
               || word;
            IF (nxt > 0)
            THEN
               WORD := 'And ' || WORD;
            END IF;
         ELSIF npos = 2
         THEN
            WORD := return_tens (num) || both || return_unit (unit) || word;
         ELSIF npos = 3
         THEN
            WORD :=
                  return_tens (num)
               || both
               || return_unit (unit)
               || 'Hundred '
               || word;
         ELSIF npos = 4
         THEN
            WORD :=
                  return_tens (num)
               || both
               || return_unit (unit)
               || 'Thousand '
               || word;
         ELSIF npos = 5
         THEN
            WORD :=
                  return_tens (num)
               || both
               || return_unit (unit)
               || 'Lakh '
               || word;
         ELSIF npos = 6
         THEN
            WORD :=
                  return_tens (num)
               || both
               || return_unit (unit)
               || 'Crore '
               || word;
         ELSIF npos = 7
         THEN
            WORD :=
                  return_tens (num)
               || both
               || return_unit (unit)
               || 'Hundred '
               || word;
         END IF;
      END IF;
   END LOOP;
   IF npos > 1
   THEN
      word := word;
   END IF;
   RETURN (word);
END;

---------------------------------------------------------------------------------

In the above function i have used two more functions
for ten and units


Function for Returning Tens
--------------------------------

CREATE OR REPLACE FUNCTION APPS.return_tens (val_in in number) RETURN VARCHAR2 AUTHID CURRENT_USER IS
 char_in varchar2(5);
 char_out varchar2(12);
BEGIN
 char_in := to_char(val_in);
 if char_in = '0'
 then
  char_out := '';
 elsif char_in = '1'
 then
  char_out := '';
 elsif char_in = '2'
 then
  char_out := 'Twenty';
 elsif char_in = '3'
 then
  char_out := 'Thirty';
 elsif char_in = '4'
 then
  char_out := 'Forty';
 elsif char_in = '5'
 then
  char_out := 'Fifty';
 elsif char_in = '6'
 then
  char_out := 'Sixty';
 elsif char_in = '7'
 then
  char_out := 'Seventy';
 elsif char_in = '8'
 then
  char_out := 'Eighty';
 elsif char_in = '9'
 then
  char_out := 'Ninety';
 else
  char_out := 'none';
 end if;
 return (char_out);
END;



The Fucntion for Returing Units
----------------------------------------

CREATE OR REPLACE function APPS.return_unit (val_in in number) RETURN varchar2 AUTHID CURRENT_USER IS
 char_in varchar2(5);
 char_out varchar2(12);
BEGIN
 char_in := to_char(val_in);
 if char_in = '0'
 then
  char_out := ' ';
 elsif char_in = '1'
 then
  char_out := 'One ';
 elsif char_in = '2'
 then
  char_out := 'Two ';
 elsif char_in = '3'
 then
  char_out := 'Three ';
 elsif char_in = '4'
 then
  char_out := 'Four ';
 elsif char_in = '5'
 then
  char_out := 'Five ';
 elsif char_in = '6'
 then
  char_out := 'Six ';
 elsif char_in = '7'
 then
  char_out := 'Seven ';
 elsif char_in = '8'
 then
  char_out := 'Eight ';
 elsif char_in = '9'
 then
  char_out := 'Nine ';
 elsif char_in = '10'
 then
  char_out := 'Ten ';
 elsif char_in = '11'
 then
  char_out := 'Eleven ';
 elsif char_in = '12'
 then
  char_out := 'Twelve ';
 elsif char_in = '13'
 then
  char_out := 'Thirteen ';
 elsif char_in = '14'
 then
  char_out := 'Fourteen ';
 elsif char_in = '15'
 then
  char_out := 'Fifteen ';
 elsif char_in = '16'
 then
  char_out := 'Sixteen ';
 elsif char_in = '17'
 then
  char_out := 'Seventeen ';
 elsif char_in = '18'
 then
  char_out := 'Eighteen ';
 elsif char_in = '19'
 then
  char_out := 'Nineteen ';
 else
  char_out := 'none';
 end if;
 return (char_out);
END;

Delete XML Publisher Records of a Concurrent

SELECT *
  FROM XDO_DS_DEFINITIONS_B
WHERE DATA_SOURCE_CODE = 'XX_DATA_DEF_CODE';

SELECT *
  FROM XDO_DS_DEFINITIONS_TL
WHERE DATA_SOURCE_CODE = 'XX_DATA_DEF_CODE';

SELECT *
  FROM XDO_LOBS
WHERE LOB_CODE = 'XX_DATA_DEF_CODE';

SELECT *
  FROM XDO_CONFIG_VALUES
WHERE DATA_SOURCE_CODE = 'XX_DATA_DEF_CODE';

Delete the Data Definitions:

-- API to delete Data Definition from XDO_DS_DEFINITIONS_B  and XDO_DS_DEFINITIONS_TL table
BEGIN
XDO_DS_DEFINITIONS_PKG.DELETE_ROW (,);
COMMIT;
END;

-- Delete Data Templates, xml schema etc. from XDO_LOBS table (There is no API)
DELETE FROM XDO_LOBS
         WHERE LOB_CODE =
            AND APPLICATION_SHORT_NAME =
            AND LOB_TYPE IN
                   ('XML_SCHEMA',
                    'DATA_TEMPLATE',
                    'XML_SAMPLE',
                    'BURSTING_FILE');

-- Delete from XDO_CONFIG_VALUES (if required)
DELETE FROM XDO_CONFIG_VALUES
      WHERE APPLICATION_SHORT_NAME =
            AND DATA_SOURCE_CODE = ;

Queries for the Templates:

SELECT *
  FROM XDO_TEMPLATES_B
WHERE TEMPLATE_CODE = 'XX_TEMPLATE_CODE';

SELECT *
  FROM XDO_TEMPLATES_TL
WHERE TEMPLATE_CODE = 'XX_TEMPLATE_CODE';

SELECT *
  FROM XDO_LOBS
WHERE LOB_CODE = 'XX_TEMPLATE_CODE';

SELECT *
  FROM XDO_CONFIG_VALUES
WHERE TEMPLATE_CODE = 'XX_TEMPLATE_CODE';

Delete the templates:

-- API to delete Data Definition from XDO_TEMPLATES_B and XDO_TEMPLATES_TL table
BEGIN
XDO_TEMPLATES_PKG.DELETE_ROW (, );
COMMIT;
END;

-- Delete the Templates from XDO_LOBS table (There is no API)
DELETE FROM XDO_LOBS
      WHERE     LOB_CODE =
            AND APPLICATION_SHORT_NAME =
            AND LOB_TYPE IN ('TEMPLATE_SOURCE', 'TEMPLATE');

-- Delete from XDO_CONFIG_VALUES (if required)
DELETE FROM XDO_CONFIG_VALUES
      WHERE     APPLICATION_SHORT_NAME =
            AND TEMPLATE_CODE =
            AND DATA_SOURCE_CODE = ;

Query's Related to Concurrent and request group

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 'XXXX%'
AND FNRTL.LANGUAGE = 'US'
AND FAPP.LANGUAGE = 'US'


Find all responsibilities and concurrent program names based on the executable Name

select --r.rowid rrowid,p.rowid prowid,e.rowid erowid,fcp.rowid frowid,
e.executable_name,responsibility_key,user_concurrent_program_name
from fnd_responsibility r,
fnd_concurrent_programs_tl p,
fnd_request_group_units u,
fnd_executables e,
fnd_concurrent_programs fcp
where r.request_group_id=u.request_group_id
and u.request_unit_id = p.concurrent_program_id
and executable_name like '%SEARCH EXECUTABLE PATTERN%'
and fcp.executable_id = e.executable_id
and user_concurrent_program_name like '%SEARCH PATTERN%'
and fcp.concurrent_program_id = p.concurrent_program_id


Find concurrent programs attached to certain responsibility.
select * from fnd_concurrent_programs_tl
where concurrent_program_id in (
select request_unit_id from fnd_request_group_units
where request_group_id in (
select request_group_id from fnd_request_groups where request_group_id in (
select request_group_id from fnd_responsibility where responsibility_key = 'YOUR RESPONSIBILITY KEY')
))

Find all responsibilities having the "search concurrent program" attached to the request group
------->
select responsibility_key,user_concurrent_program_name
from fnd_responsibility r,
fnd_concurrent_programs_tl p,
fnd_request_group_units u
where r.request_group_id=u.request_group_id
and u.request_unit_id = p.concurrent_program_id
and user_concurrent_program_name like '%SEARCH PATTERN%'
---------------<

Find concurrent Program Names based on Responsibility Key Value
select * from fnd_concurrent_programs_tl
where concurrent_program_id in (
select request_unit_id from fnd_request_group_units
where request_group_id in (
select request_group_id from fnd_request_groups where request_group_id in (
select request_group_id from fnd_responsibility where responsibility_key = 'Your Responsibility Key Search Pattern')
))

SELECT a.application_name || '---->' ||
b.request_group_name || '---->' || b.DESCRIPTION
FROM fnd_application_vl a, fnd_request_groups b, fnd_request_group_units c ,fnd_concurrent_programs_vl d
WHERE c.request_group_id = b.request_group_id
AND c.request_unit_id = d.concurrent_program_id
AND c.application_id = a.application_id
and d.user_concurrent_program_name = 'Sona Enable Disable Users'

Finding Responsibility of a Concurrent

select resp.responsibility_name
from fnd_responsibility_vl resp fnd_request_group_units rgu fnd_concurrent_programs_vl prog
where resp.request_group_id = rgu.request_group_id
AND rgu.request_unit_id = prog.concurrent_program_id
AND rgu.unit_application_id = prog.application_id
AND prog.user_concurrent_program_name = '.................'

Supplier Ledger Query in R12

The Below query is for supplier ledger in r12..
It will give you detail of invoices made against a particular supplier ...
weather the amountis credited or debited ..
and payment made through check details will also we can get thourgh the query..

/* Formatted on 1/21/2011 4:12:53 PM (QP5 v5.115.810.9015) */
  (SELECT   AIDA.accounting_date GL_DATE,
           b.vendor_name,
           a.invoice_num INVNO,
           NVL (
              NVL (
                     (CASE
                         WHEN a.INVOICE_TYPE_LOOKUP_CODE = 'DEBIT'
                         THEN
                            (case when a.PAYMENT_CURRENCY_CODE <> 'INR' then abs(sum(aida.BASE_AMOUNT))  else ABS (sum(aida.AMOUNT)) end)
                      END),
                     (CASE
                         WHEN a.INVOICE_TYPE_LOOKUP_CODE = 'CREDIT'
                         THEN
                            (case when a.PAYMENT_CURRENCY_CODE <> 'INR' then abs(sum(aida.BASE_AMOUNT))  else ABS (sum(aida.AMOUNT)) end)
                      END)
                  ),
              0
           )
              DR_Amount,
           NVL (
              NVL (
                     (CASE
                         WHEN a.INVOICE_TYPE_LOOKUP_CODE = 'STANDARD'
                         THEN
                            (case when a.PAYMENT_CURRENCY_CODE <> 'INR' then sum(aida.BASE_AMOUNT)  else sum(aida.AMOUNT) end)
                      END),
                     (CASE
                         WHEN a.INVOICE_TYPE_LOOKUP_CODE = 'PREPAYMENT'
                         THEN
                           (case when a.PAYMENT_CURRENCY_CODE <> 'INR' then sum(aida.BASE_AMOUNT)  else sum(aida.AMOUNT) end)
                      END)
                  ),
              0
           )
              CR_Amount,
           SUBSTR (a.description, 1, 40) NARRATION,
           NVL (a.DOC_SEQUENCE_VALUE, 0) VR_NO,
           NVL (a.invoice_type_lookup_code, '-') TYPE,
           NVL (d.batch_name, '--') BATCH,
           a.org_id,
           a.SOURCE SOURCE,
           gcc.segment4 Main_code,
           b.segment1 Supplier_code,
           c.VENDOR_SITE_CODE,
           a.invoice_id
          ---------- NULL ledger_id,apps.AP_INVOICES_PKG.GET_POSTING_STATUS (A.INVOICE_ID) POSTING_FLAG,
          -------- apps.AP_INVOICES_PKG.GET_APPROVAL_STATUS (
            ---------          A.INVOICE_ID,
             -------         A.INVOICE_AMOUNT,
               ---------       A.PAYMENT_STATUS_FLAG,
               --------       A.INVOICE_TYPE_LOOKUP_CODE
                -----   ) INVOICE_STATUS
    FROM   apps.ap_invoices_all a,
           apps.ap_suppliers b,
           apps.ap_supplier_sites_all c,
           apps.AP_BATCHES_ALL d,
           apps.ap_invoice_lines_all aila,
           apps.ap_invoice_distributions_all aida,
           --apps.ap_invoice_distributions_all aida,
           apps.gl_code_combinations gcc
   WHERE       a.vendor_id = b.vendor_id
           AND a.vendor_id = c.vendor_id
           AND a.batch_id = d.batch_id(+)
           AND a.invoice_id = aila.invoice_id
           AND a.invoice_id = aida.invoice_id
           AND AILA.LINE_NUMBER = AIDA.INVOICE_LINE_NUMBER
           AND gcc.code_combination_id = a.ACCTS_PAY_CODE_COMBINATION_ID
           AND a.VENDOR_SITE_ID = c.VENDOR_SITE_ID
           ---and aida.INVOICE_LINE_NUMBER =1
           AND TRUNC (AIDA.accounting_date) BETWEEN :fromdate AND :to_date
           --and trunc(A.gl_date) between '1-apr-2010' and '31-dec-2010'
           AND (apps.AP_INVOICES_PKG.GET_POSTING_STATUS (A.INVOICE_ID) <> 'N'
                AND apps.AP_INVOICES_PKG.GET_APPROVAL_STATUS (
                      A.INVOICE_ID,
                      A.INVOICE_AMOUNT,
                      A.PAYMENT_STATUS_FLAG,
                      A.INVOICE_TYPE_LOOKUP_CODE
                   ) NOT IN
                         ('NEVER APPROVED'))
           AND apps.AP_INVOICES_PKG.GET_APPROVAL_STATUS (
                 A.INVOICE_ID,
                 A.INVOICE_AMOUNT,
                 A.PAYMENT_STATUS_FLAG,
                 A.INVOICE_TYPE_LOOKUP_CODE
              ) NOT IN
                    ('CANCELLED')
           AND A.INVOICE_TYPE_LOOKUP_CODE <> 'PREPAYMENT'
           AND a.org_id = :org_id
           AND b.vendor_id = :supplier
--           and a.invoice_id =3085480
GROUP BY   a.invoice_num,
           SUBSTR (a.description, 1, 40),
           a.DOC_SEQUENCE_VALUE,
           a.invoice_type_lookup_code,
           a.org_id,
           INVOICE_AMOUNT,
           a.SOURCE,
           b.vendor_name,
           b.segment1,a.PAYMENT_CURRENCY_CODE,
           c.VENDOR_SITE_CODE,
           d.batch_name,
           a.invoice_id,
           gcc.segment4,
           AIDA.accounting_date,
           APPS.AP_INVOICES_PKG.GET_POSTING_STATUS (A.INVOICE_ID),
           apps.AP_INVOICES_PKG.GET_APPROVAL_STATUS (
                      A.INVOICE_ID,
                      A.INVOICE_AMOUNT,
                      A.PAYMENT_STATUS_FLAG,
                      A.INVOICE_TYPE_LOOKUP_CODE
                   )
union all
SELECT   AIDA.accounting_date GL_DATE,
           b.vendor_name,
           a.invoice_num INVNO,
           NVL (
              NVL (
                     (CASE
                         WHEN a.INVOICE_TYPE_LOOKUP_CODE = 'DEBIT'
                         THEN
                            (case when a.PAYMENT_CURRENCY_CODE <> 'INR' then abs(sum(aida.BASE_AMOUNT))  else ABS (sum(aida.AMOUNT)) end)
                      END),
                     (CASE
                         WHEN a.INVOICE_TYPE_LOOKUP_CODE = 'CREDIT'
                         THEN
                            (case when a.PAYMENT_CURRENCY_CODE <> 'INR' then abs(sum(aida.BASE_AMOUNT))  else ABS (sum(aida.AMOUNT)) end)
                      END)
                  ),
              0
           )
              DR_Amount,
           NVL (
              NVL (
                     (CASE
                         WHEN a.INVOICE_TYPE_LOOKUP_CODE = 'STANDARD'
                         THEN
                            (case when a.PAYMENT_CURRENCY_CODE <> 'INR' then sum(aida.BASE_AMOUNT)  else sum(aida.AMOUNT) end)
                      END),
                     (CASE
                         WHEN a.INVOICE_TYPE_LOOKUP_CODE = 'PREPAYMENT'
                         THEN
                           (case when a.PAYMENT_CURRENCY_CODE <> 'INR' then sum(aida.BASE_AMOUNT)  else sum(aida.AMOUNT) end)
                      END)
                  ),
              0
           )
              CR_Amount,
           SUBSTR (a.description, 1, 40) NARRATION,
           NVL (a.DOC_SEQUENCE_VALUE, 0) VR_NO,
           NVL (a.invoice_type_lookup_code, '-') TYPE,
           NVL (d.batch_name, '--') BATCH,
           a.org_id,
           a.SOURCE SOURCE,
           gcc.segment4 Main_code,
           b.segment1 Supplier_code,
           c.VENDOR_SITE_CODE,
           a.invoice_id
          ---------- NULL ledger_id,apps.AP_INVOICES_PKG.GET_POSTING_STATUS (A.INVOICE_ID) POSTING_FLAG,
          -------- apps.AP_INVOICES_PKG.GET_APPROVAL_STATUS (
            ---------          A.INVOICE_ID,
             -------         A.INVOICE_AMOUNT,
               ---------       A.PAYMENT_STATUS_FLAG,
               --------       A.INVOICE_TYPE_LOOKUP_CODE
                -----   ) INVOICE_STATUS
    FROM   apps.ap_invoices_all a,
           apps.ap_suppliers b,
           apps.ap_supplier_sites_all c,
           apps.AP_BATCHES_ALL d,
           apps.ap_invoice_lines_all aila,
           apps.ap_invoice_distributions_all aida,
           --apps.ap_invoice_distributions_all aida,
           apps.gl_code_combinations gcc
   WHERE       a.vendor_id = b.vendor_id
           AND a.vendor_id = c.vendor_id
           AND a.batch_id = d.batch_id(+)
           AND a.invoice_id = aila.invoice_id
           AND a.invoice_id = aida.invoice_id
           AND AILA.LINE_NUMBER = AIDA.INVOICE_LINE_NUMBER
           AND gcc.code_combination_id = a.ACCTS_PAY_CODE_COMBINATION_ID
           AND a.VENDOR_SITE_ID = c.VENDOR_SITE_ID
           ---and aida.INVOICE_LINE_NUMBER =1
           AND TRUNC (AIDA.accounting_date) BETWEEN :fromdate AND :to_date
           --and trunc(A.gl_date) between '1-apr-2010' and '31-dec-2010'
           AND (apps.AP_INVOICES_PKG.GET_POSTING_STATUS (A.INVOICE_ID) <> 'N'
                AND apps.AP_INVOICES_PKG.GET_APPROVAL_STATUS (
                      A.INVOICE_ID,
                      A.INVOICE_AMOUNT,
                      A.PAYMENT_STATUS_FLAG,
                      A.INVOICE_TYPE_LOOKUP_CODE
                   ) IN
                         ('NEVER APPROVED'))
           AND apps.AP_INVOICES_PKG.GET_APPROVAL_STATUS (
                 A.INVOICE_ID,
                 A.INVOICE_AMOUNT,
                 A.PAYMENT_STATUS_FLAG,
                 A.INVOICE_TYPE_LOOKUP_CODE
              ) NOT IN
                    ('CANCELLED')
           AND A.INVOICE_TYPE_LOOKUP_CODE <> 'PREPAYMENT'
           AND a.org_id = :org_id
           AND b.vendor_id = :supplier
--           and a.invoice_id =3085480
GROUP BY   a.invoice_num,
           SUBSTR (a.description, 1, 40),
           a.DOC_SEQUENCE_VALUE,
           a.invoice_type_lookup_code,
           a.org_id,
           INVOICE_AMOUNT,
           a.SOURCE,
           b.vendor_name,
           b.segment1,a.PAYMENT_CURRENCY_CODE,
           c.VENDOR_SITE_CODE,
           d.batch_name,
           a.invoice_id,
           gcc.segment4,
           AIDA.accounting_date,
           APPS.AP_INVOICES_PKG.GET_POSTING_STATUS (A.INVOICE_ID),
           apps.AP_INVOICES_PKG.GET_APPROVAL_STATUS (
                      A.INVOICE_ID,
                      A.INVOICE_AMOUNT,
                      A.PAYMENT_STATUS_FLAG,
                      A.INVOICE_TYPE_LOOKUP_CODE
                   )                  
UNION all
  SELECT   AIDA.accounting_date GL_DATE,
           b.vendor_name,
           a.invoice_num INVNO,
          NVL (
              NVL (
                     (CASE
                         WHEN a.INVOICE_TYPE_LOOKUP_CODE = 'DEBIT'
                         THEN
                            (case when a.PAYMENT_CURRENCY_CODE <> 'INR' then abs(sum(aida.BASE_AMOUNT))  else ABS (sum(aida.AMOUNT)) end)
                      END),
                     (CASE
                         WHEN a.INVOICE_TYPE_LOOKUP_CODE = 'CREDIT'
                         THEN
                            (case when a.PAYMENT_CURRENCY_CODE <> 'INR' then abs(sum(aida.BASE_AMOUNT))  else ABS (sum(aida.AMOUNT)) end)
                      END)
                  ),
              0
           )
              DR_Amount,
           NVL (
              NVL (
                     (CASE
                         WHEN a.INVOICE_TYPE_LOOKUP_CODE = 'STANDARD'
                         THEN
                            (case when a.PAYMENT_CURRENCY_CODE <> 'INR' then sum(aida.BASE_AMOUNT)  else sum(aida.AMOUNT) end)
                      END),
                     (CASE
                         WHEN a.INVOICE_TYPE_LOOKUP_CODE = 'PREPAYMENT'
                         THEN
                            (case when a.PAYMENT_CURRENCY_CODE <> 'INR' then sum(aida.BASE_AMOUNT)  else sum(aida.AMOUNT) end)
                      END)
                  ),
              0
           )
              CR_Amount,
           SUBSTR (a.description, 1, 40) NARRATION,
           NVL (a.DOC_SEQUENCE_VALUE, 0) VR_NO,
           NVL (a.invoice_type_lookup_code, '-') TYPE,
           NVL (d.batch_name, '--') BATCH,
           a.org_id,
           a.SOURCE SOURCE,
           gcc.segment4 Main_code,
           b.segment1 Supplier_code,
           c.VENDOR_SITE_CODE,           a.invoice_id
--           NULL ledger_id,AP_INVOICES_PKG.GET_POSTING_STATUS (A.INVOICE_ID) POSTING_FLAG,
--           apps.AP_INVOICES_PKG.GET_APPROVAL_STATUS (
--                      A.INVOICE_ID,
--                      A.INVOICE_AMOUNT,
--                      A.PAYMENT_STATUS_FLAG,
--                      A.INVOICE_TYPE_LOOKUP_CODE
--                   ) INVOICE_STATUS
    FROM   apps.ap_invoices_all a,
           apps.ap_suppliers b,
           apps.ap_supplier_sites_all c,
           apps.AP_BATCHES_ALL d,
           apps.ap_invoice_lines_all aila,
           apps.ap_invoice_distributions_all aida,
           --apps.ap_invoice_distributions_all aida,
           apps.gl_code_combinations gcc
   WHERE       a.vendor_id = b.vendor_id
           AND a.vendor_id = c.vendor_id
           AND a.batch_id = d.batch_id(+)
           AND a.invoice_id = aila.invoice_id
           AND a.invoice_id = aida.invoice_id
           AND AILA.LINE_NUMBER = AIDA.INVOICE_LINE_NUMBER
           AND gcc.code_combination_id = a.ACCTS_PAY_CODE_COMBINATION_ID
           AND a.VENDOR_SITE_ID = c.VENDOR_SITE_ID
           ---and aida.INVOICE_LINE_NUMBER =1
           AND TRUNC (AIDA.accounting_date) BETWEEN :fromdate AND :to_date
           --and trunc(A.gl_date) between '1-apr-2010' and '31-dec-2010'
           AND (apps.AP_INVOICES_PKG.GET_POSTING_STATUS (A.INVOICE_ID) <> 'N'
                AND apps.AP_INVOICES_PKG.GET_APPROVAL_STATUS (
                      A.INVOICE_ID,
                      A.INVOICE_AMOUNT,
                      A.PAYMENT_STATUS_FLAG,
                      A.INVOICE_TYPE_LOOKUP_CODE
                   ) NOT IN
                         ('NEVER APPROVED'))
           AND apps.AP_INVOICES_PKG.GET_APPROVAL_STATUS (
                 A.INVOICE_ID,
                 A.INVOICE_AMOUNT,
                 A.PAYMENT_STATUS_FLAG,
                 A.INVOICE_TYPE_LOOKUP_CODE
              ) IN
                    ('CANCELLED')
           AND A.CANCELLED_DATE >= :to_date
           AND A.INVOICE_TYPE_LOOKUP_CODE <> 'PREPAYMENT'
           AND a.org_id = :org_id
           AND b.vendor_id = :supplier
--           and a.invoice_id =3085480
GROUP BY   a.invoice_num,
           SUBSTR (a.description, 1, 40),
           a.DOC_SEQUENCE_VALUE,
           a.invoice_type_lookup_code,
           a.org_id,
           INVOICE_AMOUNT,
           a.SOURCE,
           b.vendor_name,
           b.segment1,a.PAYMENT_CURRENCY_CODE,
           c.VENDOR_SITE_CODE,
           d.batch_name,
           a.invoice_id,
           gcc.segment4,
           AIDA.accounting_date,
           APPS.AP_INVOICES_PKG.GET_POSTING_STATUS (A.INVOICE_ID),
           apps.AP_INVOICES_PKG.GET_APPROVAL_STATUS (
                      A.INVOICE_ID,
                      A.INVOICE_AMOUNT,
                      A.PAYMENT_STATUS_FLAG,
                      A.INVOICE_TYPE_LOOKUP_CODE
                   )
UNION ALL
  SELECT   AIDA.accounting_date GL_DATE,
           b.vendor_name,
           a.invoice_num INVNO,
           NVL (
              NVL (
                     (CASE
                         WHEN a.INVOICE_TYPE_LOOKUP_CODE = 'DEBIT'
                         THEN
                           (case when a.PAYMENT_CURRENCY_CODE <> 'INR' then abs(sum(aida.BASE_AMOUNT))  else ABS (sum(aida.AMOUNT)) end)
                      END),
                     (CASE
                         WHEN a.INVOICE_TYPE_LOOKUP_CODE = 'CREDIT'
                         THEN
                           (case when a.PAYMENT_CURRENCY_CODE <> 'INR' then abs(sum(aida.BASE_AMOUNT))  else ABS (sum(aida.AMOUNT)) end)
                      END)
                  ),
              0
           )
              DR_Amount,
           NVL (
              NVL (
                     (CASE
                         WHEN a.INVOICE_TYPE_LOOKUP_CODE = 'STANDARD'
                         THEN
                            (case when a.PAYMENT_CURRENCY_CODE <> 'INR' then sum(aida.BASE_AMOUNT)  else sum(aida.AMOUNT) end)
                      END),
                     (CASE
                         WHEN a.INVOICE_TYPE_LOOKUP_CODE = 'PREPAYMENT'
                         THEN
                            (case when a.PAYMENT_CURRENCY_CODE <> 'INR' then sum(aida.BASE_AMOUNT)  else sum(aida.AMOUNT) end)
                      END)
                  ),
              0
           )
              CR_Amount,
           SUBSTR (a.description, 1, 40) NARRATION,
           NVL (a.DOC_SEQUENCE_VALUE, 0) VR_NO,
           NVL (a.invoice_type_lookup_code, '-') TYPE,
           NVL (d.batch_name, '--') BATCH,
           a.org_id,
           a.SOURCE SOURCE,
           gcc.segment4 Main_code,
           b.segment1 Supplier_code,
           c.VENDOR_SITE_CODE,
           a.invoice_id
--           NULL ledger_id,APPS.AP_INVOICES_PKG.GET_POSTING_STATUS (A.INVOICE_ID) POSTING_FLAG,
--           apps.AP_INVOICES_PKG.GET_APPROVAL_STATUS (
--                      A.INVOICE_ID,
--                      A.INVOICE_AMOUNT,
--                      A.PAYMENT_STATUS_FLAG,
--                      A.INVOICE_TYPE_LOOKUP_CODE
--                   ) INVOICE_STATUS
    FROM   apps.ap_invoices_all a,
           apps.ap_suppliers b,
           apps.ap_supplier_sites_all c,
           apps.AP_BATCHES_ALL d,
           apps.ap_invoice_lines_all aila,
           apps.ap_invoice_distributions_all aida,
           --apps.ap_invoice_distributions_all aida,
           apps.gl_code_combinations gcc
   WHERE       a.vendor_id = b.vendor_id
           AND a.vendor_id = c.vendor_id
           AND a.batch_id = d.batch_id(+)
           AND a.invoice_id = aila.invoice_id
           AND a.invoice_id = aida.invoice_id
           AND AILA.LINE_NUMBER = AIDA.INVOICE_LINE_NUMBER
           AND gcc.code_combination_id = a.ACCTS_PAY_CODE_COMBINATION_ID
           AND a.VENDOR_SITE_ID = c.VENDOR_SITE_ID
           ---and aida.INVOICE_LINE_NUMBER =1
           AND TRUNC (AIDA.accounting_date) BETWEEN :fromdate AND :to_date
           --and trunc(A.gl_date) between '1-apr-2010' and '31-dec-2010'
           AND (apps.AP_INVOICES_PKG.GET_POSTING_STATUS (A.INVOICE_ID) <> 'N'
                AND apps.AP_INVOICES_PKG.GET_APPROVAL_STATUS (
                      A.INVOICE_ID,
                      A.INVOICE_AMOUNT,
                      A.PAYMENT_STATUS_FLAG,
                      A.INVOICE_TYPE_LOOKUP_CODE
                   ) NOT IN
                         ('NEVER APPROVED'))
           AND apps.AP_INVOICES_PKG.GET_APPROVAL_STATUS (
                 A.INVOICE_ID,
                 A.INVOICE_AMOUNT,
                 A.PAYMENT_STATUS_FLAG,
                 A.INVOICE_TYPE_LOOKUP_CODE
              ) IN
                    ('CANCELLED')
           AND A.CANCELLED_DATE <= :to_date
           AND A.INVOICE_TYPE_LOOKUP_CODE <> 'PREPAYMENT'
           AND a.org_id = :org_id
           AND b.vendor_id = :supplier
--           and a.invoice_id =3085480
GROUP BY   a.invoice_num,
           SUBSTR (a.description, 1, 40),
           a.DOC_SEQUENCE_VALUE,
           a.invoice_type_lookup_code,
           a.org_id,
           INVOICE_AMOUNT,
           a.SOURCE,
           b.vendor_name,
           b.segment1,
           c.VENDOR_SITE_CODE,a.PAYMENT_CURRENCY_CODE,
           d.batch_name,
           a.invoice_id,
           gcc.segment4,
           AIDA.accounting_date,
           APPS.AP_INVOICES_PKG.GET_POSTING_STATUS (A.INVOICE_ID),
           apps.AP_INVOICES_PKG.GET_APPROVAL_STATUS (
                      A.INVOICE_ID,
                      A.INVOICE_AMOUNT,
                      A.PAYMENT_STATUS_FLAG,
                      A.INVOICE_TYPE_LOOKUP_CODE
                   )  )        
UNION
  (SELECT   c.ACCOUNTING_DATE GL_Date,
           b.vendor_name,
           a.invoice_num INVNO,
           NVL (
              SUM(NVL (
                     (CASE
                         WHEN a.INVOICE_TYPE_LOOKUP_CODE = 'STANDARD'
                         THEN
                             (case when a.PAYMENT_CURRENCY_CODE <> 'INR' then c.INVOICE_BASE_AMOUNT  else c.amount end)
                      END),
                     (CASE
                         WHEN a.INVOICE_TYPE_LOOKUP_CODE = 'PREPAYMENT'
                         THEN
                             (case when a.PAYMENT_CURRENCY_CODE <> 'INR' then c.INVOICE_BASE_AMOUNT  else c.amount end)
                      END)
                  )),
              0
           )
              DR_Amount,
           NVL (
              SUM(NVL (
                     (CASE
                         WHEN a.INVOICE_TYPE_LOOKUP_CODE = 'DEBIT'
                         THEN
                            (case when a.PAYMENT_CURRENCY_CODE <> 'INR' then abs(c.INVOICE_BASE_AMOUNT)  else ABS (c.amount) end)
                      END),
                     (CASE
                         WHEN a.INVOICE_TYPE_LOOKUP_CODE = 'CREDIT'
                         THEN
                            (case when a.PAYMENT_CURRENCY_CODE <> 'INR' then abs(c.INVOICE_BASE_AMOUNT)  else ABS (c.amount) end)
                      END)
                  )),
              0
           )
              CR_Amount,
           TO_CHAR (d.CHECK_NUMBER) Narration,
           d.DOC_SEQUENCE_VALUE Vr_no,
           NVL (TO_CHAR (d.PAYMENT_METHOD_LOOKUP_CODE), 'CHECK') TYPE,
           NULL Batch,
           a.org_id,
           NULL Source,
           f.segment4 "account code",
           b.segment1 Supplier_code,
           e.VENDOR_SITE_CODE,
           a.invoice_id
--           a.SET_OF_BOOKS_ID ledger_id,NULL POSTING_FLAG,NULL INVOICE_STATUS
    FROM   apps.ap_invoices_all a,
           apps.ap_suppliers b,
           apps.ap_supplier_sites_all e,
           apps.ap_invoice_payments_all c,
           apps.ap_checks_all d,
           apps.gl_code_combinations f
   WHERE       a.vendor_id = b.vendor_id
           AND a.org_id = c.org_id
           AND e.VENDOR_SITE_ID = a.VENDOR_SITE_ID
           AND a.vendor_id = e.vendor_id
           AND a.invoice_id = c.invoice_id(+)
           AND c.check_id = d.check_id(+)
           AND c.org_id = d.org_id(+)
           AND f.code_combination_id = a.ACCTS_PAY_CODE_COMBINATION_ID
           AND b.vendor_id = :supplier
           --    and a.SET_OF_BOOKS_ID=1
           --- and d.CHECK_NUMBER = 4951
           AND d.status_lookup_code <> 'VOIDED'
           AND a.org_id = :org_id
           AND c.ACCOUNTING_DATE BETWEEN :fromdate AND :to_date
GROUP BY   c.ACCOUNTING_DATE,
           b.vendor_name,
           a.invoice_num,
           TO_CHAR (d.CHECK_NUMBER),
           d.DOC_SEQUENCE_VALUE,
           d.PAYMENT_METHOD_LOOKUP_CODE,
           a.org_id,
           f.segment4,
           b.segment1,
           e.VENDOR_SITE_CODE,
           a.invoice_id,
           a.SET_OF_BOOKS_ID,a.PAYMENT_CURRENCY_CODE        
UNION all
  SELECT   c.ACCOUNTING_DATE GL_Date,
           b.vendor_name,
           a.invoice_num INVNO,
           NVL (
              SUM(NVL (
                     (CASE
                         WHEN a.INVOICE_TYPE_LOOKUP_CODE = 'STANDARD'
                         THEN
                            (case when a.PAYMENT_CURRENCY_CODE <> 'INR' then c.INVOICE_BASE_AMOUNT  else c.amount end)
                      END),
                     (CASE
                         WHEN a.INVOICE_TYPE_LOOKUP_CODE = 'PREPAYMENT'
                         THEN
                            (case when a.PAYMENT_CURRENCY_CODE <> 'INR' then c.INVOICE_BASE_AMOUNT  else c.amount end)
                      END)
                  )),
              NULL
           )
              DR_Amount,
           NVL (
              SUM(NVL (
                     (CASE
                         WHEN a.INVOICE_TYPE_LOOKUP_CODE = 'DEBIT'
                         THEN
                            (case when a.PAYMENT_CURRENCY_CODE <> 'INR' then abs(c.INVOICE_BASE_AMOUNT)  else ABS (c.amount) end)
                      END),
                     (CASE
                         WHEN a.INVOICE_TYPE_LOOKUP_CODE = 'CREDIT'
                         THEN
                             (case when a.PAYMENT_CURRENCY_CODE <> 'INR' then abs(c.INVOICE_BASE_AMOUNT)  else ABS (c.amount) end)
                      END)
                  )),
              NULL
           )
              CR_Amount,
           TO_CHAR (d.CHECK_NUMBER) Narration,
           d.DOC_SEQUENCE_VALUE Vr_no,
           NVL (TO_CHAR (d.PAYMENT_METHOD_LOOKUP_CODE), 'CHECK') TYPE,
           NULL Batch,
           a.org_id,
           NULL Source,
           f.segment4 "account code",
           b.segment1 Supplier_code,
           e.VENDOR_SITE_CODE,
           a.invoice_id
--           a.SET_OF_BOOKS_ID ledger_id,NULL POSTING_FLAG,NULL INVOICE_STATUS
    FROM   apps.ap_invoices_all a,
           apps.ap_suppliers b,
           apps.ap_supplier_sites_all e,
           apps.ap_invoice_payments_all c,
           apps.ap_checks_all d,
           apps.gl_code_combinations f
   WHERE       a.vendor_id = b.vendor_id
           AND a.org_id = c.org_id
           AND e.VENDOR_SITE_ID = a.VENDOR_SITE_ID
           AND a.vendor_id = e.vendor_id
           AND a.invoice_id = c.invoice_id(+)
           AND c.check_id = d.check_id(+)
           AND c.org_id = d.org_id(+)
           AND f.code_combination_id = a.ACCTS_PAY_CODE_COMBINATION_ID
           AND b.vendor_id =:supplier
           --    and a.SET_OF_BOOKS_ID=1
           --- and d.CHECK_NUMBER = 4951
           AND d.status_lookup_code = 'VOIDED'
           and c.ACCOUNTING_DATE!=d.void_date
           AND d.void_date <= :to_date
           AND a.org_id = :org_id
           AND c.ACCOUNTING_DATE BETWEEN :fromdate AND :to_date
GROUP BY   c.ACCOUNTING_DATE,
           b.vendor_name,
           a.invoice_num,
           TO_CHAR (d.CHECK_NUMBER),
           d.DOC_SEQUENCE_VALUE,
           d.PAYMENT_METHOD_LOOKUP_CODE,a.PAYMENT_CURRENCY_CODE,
           a.org_id,
           f.segment4,
           b.segment1,
           e.VENDOR_SITE_CODE,
           a.invoice_id,
           a.SET_OF_BOOKS_ID  
union all
  SELECT   c.ACCOUNTING_DATE GL_Date,
           b.vendor_name,
           a.invoice_num INVNO,
           NVL (
              NVL (
                     (CASE
                         WHEN a.INVOICE_TYPE_LOOKUP_CODE = 'DEBIT'
                         THEN
                            (case when a.PAYMENT_CURRENCY_CODE <> 'INR' then abs(sum(c.INVOICE_BASE_AMOUNT))  else ABS (sum(c.amount)) end)
                      END),
                     (CASE
                         WHEN a.INVOICE_TYPE_LOOKUP_CODE = 'CREDIT'
                         THEN
                            (case when a.PAYMENT_CURRENCY_CODE <> 'INR' then abs(sum(c.INVOICE_BASE_AMOUNT))  else ABS (sum(c.amount)) end)
                      END)
                  ),
              0
           )        
              DR_Amount,
              NVL (
              NVL (
                     (CASE
                         WHEN a.INVOICE_TYPE_LOOKUP_CODE = 'STANDARD'
                         THEN
                             (case when a.PAYMENT_CURRENCY_CODE <> 'INR' then abs(sum(c.INVOICE_BASE_AMOUNT))  else ABS (sum(c.amount)) end)
                      END),
                     (CASE
                         WHEN a.INVOICE_TYPE_LOOKUP_CODE = 'PREPAYMENT'
                         THEN
                            (case when a.PAYMENT_CURRENCY_CODE <> 'INR' then abs(sum(c.INVOICE_BASE_AMOUNT))  else ABS (sum(c.amount)) end)
                      END)
                  ),
              0
           )          
              CR_Amount,
           TO_CHAR (d.CHECK_NUMBER) Narration,
           d.DOC_SEQUENCE_VALUE Vr_no,
           NVL (TO_CHAR (d.PAYMENT_METHOD_LOOKUP_CODE), 'CHECK') TYPE,
           NULL Batch,
           a.org_id,
           NULL Source,
           f.segment4 "account code",
           b.segment1 Supplier_code,
           e.VENDOR_SITE_CODE,
          a.invoice_id
--           a.SET_OF_BOOKS_ID ledger_id,NULL POSTING_FLAG,NULL INVOICE_STATUS
    FROM   apps.ap_invoices_all a,
           apps.ap_suppliers b,
           apps.ap_supplier_sites_all e,
           apps.ap_invoice_payments_all c,
           apps.ap_checks_all d,
           apps.gl_code_combinations f
   WHERE       a.vendor_id = b.vendor_id
           AND a.org_id = c.org_id
           AND e.VENDOR_SITE_ID = a.VENDOR_SITE_ID
           AND a.vendor_id = e.vendor_id
           AND a.invoice_id = c.invoice_id(+)
           AND c.check_id = d.check_id(+)
           AND c.org_id = d.org_id(+)
           AND f.code_combination_id = a.ACCTS_PAY_CODE_COMBINATION_ID
           AND b.vendor_id =:supplier
           --    and a.SET_OF_BOOKS_ID=1
           --- and d.CHECK_NUMBER = 4951
          AND d.status_lookup_code = 'VOIDED'
          and c.ACCOUNTING_DATE=d.void_date
--           AND d.void_date >= :to_date
           AND a.org_id = :org_id
--           and a.invoice_id=2805181
           AND c.ACCOUNTING_DATE BETWEEN :fromdate AND :to_date
GROUP BY   c.ACCOUNTING_DATE,
           b.vendor_name,
           a.invoice_num,
           TO_CHAR (d.CHECK_NUMBER),
           d.DOC_SEQUENCE_VALUE,
           d.PAYMENT_METHOD_LOOKUP_CODE,
           a.org_id,
           f.segment4,a.PAYMENT_CURRENCY_CODE,
           b.segment1,
           e.VENDOR_SITE_CODE,
           a.invoice_id,
           a.SET_OF_BOOKS_ID,a.INVOICE_TYPE_LOOKUP_CODE           )
----   order by 2

General Ledger Report With inventory transaction details

The below will give all the details of the transaction in the gl account code wise..
and it will give the detail of the inventory with each and every transaction details from inventory..


/* Formatted on 5/16/2011 3:08:43 PM (QP5 v5.115.810.9015) */
SELECT                                 /*+ INDEX (C SONA_GENERAL_LEDGER_IDX)*/
      C  .SEGMENT6 CC_CD,
         A.JE_SOURCE,
         A.JE_CATEGORY,
         B.DESCRIPTION,
         c.segment4 account_code,
         A.NAME,
         B.ACCOUNTED_DR,
         B.ACCOUNTED_CR,
         A.DOC_SEQUENCE_VALUE,
         B.EFFECTIVE_DATE accounting_DATE,
         b.JE_HEADER_ID,
         B.JE_LINE_NUM,
         B.REFERENCE_2 INV_ID,
         B.REFERENCE_5 INV_NUM,
         B.GL_SL_LINK_ID LINK_ID,
         C.CODE_COMBINATION_ID,
         C.SEGMENT2,
         b.REFERENCE_4,
         b.period_name,
         a.ledger_id SET_OF_BOOKS_ID,
         (SELECT   NVL (SUM (GLB.begin_balance_dr - GLB.begin_balance_cr), 0)
            FROM   apps.gl_balances GLB
           WHERE       GLB.ledger_id = a.ledger_id
                   AND GLB.actual_flag LIKE 'A'
                   AND GLB.currency_code = 'INR'
                   AND GLB.period_name = b.period_name
                   AND GLB.code_combination_id IN
                            (SELECT   glcc.code_combination_id
                               FROM   apps.gl_code_combinations glcc
                              WHERE   TO_NUMBER (glcc.segment4) =
                                         TO_NUMBER (c.segment4)
                                      AND glcc.segment2 = c.segment2))
            opening_balance,
         DECODE (
            b.reference_4,
            'rcv_transactions',
            DECODE (
               a.je_source,
               'Purchasing India',
               (SELECT   DISTINCT poh.segment1
                  FROM   apps.po_headers_all poh,
                         apps.rcv_shipment_lines rsl,
                         apps.rcv_transactions rcts
                 WHERE       poh.po_header_id = rsl.po_header_id
                         AND rsl.shipment_line_id = rcts.shipment_line_id
                         AND rcts.transaction_id = b.reference_5
                         AND rcts.organization_id =
                               DECODE (a.ledger_id,
                                       1, 3,
                                       3, 24,
                                       4, 44,
                                       2, 45,
                                       5, 46,
                                       7, 65,
                                       8, 144,
                                       10, 165,
                                       12, 185,
                                       9999)),
               NULL
            ),
            NULL
         )
            po_number,
         DECODE (
            b.reference_4,
            'rcv_transactions',
            DECODE (
               a.je_source,
               'Purchasing India',
               (SELECT   DISTINCT pol.LINE_NUM
                  FROM   apps.po_lines_all pol,
                         apps.rcv_shipment_lines rsl,
                         apps.rcv_transactions rcts
                 WHERE       pol.PO_LINE_ID = rsl.PO_LINE_ID
                         AND rsl.shipment_line_id = rcts.shipment_line_id
                         AND rcts.transaction_id = b.reference_5
                         AND rcts.organization_id =
                               DECODE (a.ledger_id,
                                       1, 3,
                                       3, 24,
                                       4, 44,
                                       2, 45,
                                       5, 46,
                                       7, 65,
                                       8, 144,
                                       10, 165,
                                       12, 185,
                                       9999)),
               NULL
            ),
            NULL
         )
            po_line_num,
         DECODE (
            b.reference_4,
            'rcv_transactions',
            DECODE (
               a.je_source,
               'Purchasing India',
               (SELECT   DISTINCT pol.attribute6
                  FROM   apps.po_lines_all pol,
                         apps.rcv_shipment_lines rsl,
                         apps.rcv_transactions rcts
                 WHERE       pol.PO_LINE_ID = rsl.PO_LINE_ID
                         AND rsl.shipment_line_id = rcts.shipment_line_id
                         AND rcts.transaction_id = b.reference_5
                         AND rcts.organization_id =
                               DECODE (a.ledger_id,
                                       1, 3,
                                       3, 24,
                                       4, 44,
                                       2, 45,
                                       5, 46,
                                       7, 65,
                                       8, 144,
                                       10, 165,
                                       12, 185,
                                       9999)),
               NULL
            ),
            NULL
         )
            po_line_name,
         (CASE
             WHEN a.JE_SOURCE = 'Payables'
                  AND A.JE_CATEGORY = 'Purchase Invoices'
             THEN
                (SELECT   VENDOR_NAME
                   FROM   APPS.AP_SUPPLIERS AP, APPS.AP_INVOICES_ALL AIA
                  WHERE   AP.VENDOR_ID = AIA.VENDOR_ID
                          AND AIA.DOC_SEQUENCE_VALUE =
                                B.SUBLEDGER_DOC_SEQUENCE_VALUE
                          AND AIA.DOC_SEQUENCE_ID =
                                B.SUBLEDGER_DOC_SEQUENCE_ID
                          AND ROWNUM = 1)
             WHEN a.JE_SOURCE = 'Purchasing India'
                  AND A.JE_CATEGORY = 'Receiving India'
             THEN
                (SELECT   VENDOR_NAME
                   FROM   APPS.AP_SUPPLIERS AP, APPS.RCV_TRANSACTIONS AIA
                  WHERE       AP.VENDOR_ID = AIA.VENDOR_ID
                          AND AIA.TRANSACTION_ID = TO_NUMBER (B.REFERENCE_5)
                          AND ROWNUM = 1)
             ELSE
                NULL
          END)
            vendor_name,
         DECODE (
            a.je_source,
            'Purchasing',
            (SELECT   DISTINCT rsh.receipt_num
               FROM   apps.rcv_shipment_headers rsh,
                      apps.rcv_transactions rct
              WHERE       rsh.shipment_header_id = rct.shipment_header_id
                      AND rct.transaction_id = b.reference_5
                      AND rsh.organization_id = rct.organization_id
                      AND rct.organization_id =
                            DECODE (a.ledger_id,
                                    1, 3,
                                    3, 24,
                                    4, 44,
                                    2, 45,
                                    5, 46,
                                    7, 65,
                                    8, 144,
                                    10, 165,
                                    12, 185,
                                    9999)),
            'Manual',
            a.doc_sequence_value,
            (SELECT   DISTINCT glir.subledger_doc_sequence_value
               FROM   apps.gl_import_references glir
              WHERE       glir.je_header_id = a.je_header_id
                      AND glir.je_line_num = b.je_line_num
                      AND ROWNUM = 1)
         )
            vou_num,
         -- d.start_date,
         0 Quantity,
         DECODE (a.ledger_id,
                 1, 3,
                 3, 24,
                 4, 44,
                 2, 45,
                 5, 46,
                 7, 65,
                 8, 144,
                 10, 165,
                 12, 185,
                 9999)
            organization_id,
         NULL Item_code,
         (CASE
             WHEN a.JE_SOURCE = 'Payables'
                  AND A.JE_CATEGORY = 'Purchase Invoices'
             THEN
                (SELECT   ap.user_name
                   FROM   APPS.fnd_user AP, APPS.AP_INVOICES_ALL AIA
                  WHERE   AP.user_id = AIA.CREATED_BY
                          AND AIA.DOC_SEQUENCE_VALUE =
                                B.SUBLEDGER_DOC_SEQUENCE_VALUE
                          AND AIA.DOC_SEQUENCE_ID =
                                B.SUBLEDGER_DOC_SEQUENCE_ID
                          AND ROWNUM = 1)
             WHEN a.JE_SOURCE = 'Purchasing India'
                  AND A.JE_CATEGORY = 'Receiving India'
             THEN
                (SELECT   ap.user_name
                   FROM   APPS.fnd_user AP, APPS.RCV_TRANSACTIONS AIA
                  WHERE       AP.user_id = AIA.CREATED_BY
                          AND AIA.TRANSACTION_ID = TO_NUMBER (B.REFERENCE_5)
                          AND ROWNUM = 1)
             ELSE
                NULL
          END)
            user_name
  FROM   apps.GL_JE_HEADERS A,
         apps.GL_JE_LINES B,
         apps.GL_CODE_COMBINATIONS C
 --   apps.gl_periods d
 WHERE   segment2 =
            DECODE (a.ledger_id,
                    1, 'SKG',
                    3, 'SSL',
                    4, 'SPK',
                    2, 'SOP',
                    5, 'SCF',
                    7, 'TSS',
                    8, 'SKC',
                    10, 'EOU',
                    12, 'SKD',
                    'AAA')
         AND A.JE_HEADER_ID = B.JE_HEADER_ID
         AND B.CODE_COMBINATION_ID = C.CODE_COMBINATION_ID
         AND A.STATUS = 'P'
         AND A.ACTUAL_FLAG = 'A'
         AND a.LEDGER_ID = b.LEDGER_ID
         AND a.LEDGER_ID = :p_set_of_bks_id
         AND TO_NUMBER (C.SEGMENT4) BETWEEN :acc_cd1 AND :acc_cd2
         AND B.EFFECTIVE_DATE BETWEEN TO_DATE (:P_FROM_DATE || ' 00:00:00',
                                               'DD-MON-YYYY HH24:MI:SS')
                                  AND  TO_DATE (:P_TO_DATE || ' 23:59:59',
                                                'DD-MON-YYYY HH24:MI:SS')
         AND TRIM (A.JE_SOURCE || A.JE_CATEGORY) NOT IN
                  ('Cost ManagementReceiving')
         AND (TRIM (A.JE_SOURCE || A.JE_CATEGORY) NOT IN
                    ('Cost ManagementInventory')
              OR b.DESCRIPTION LIKE 'Journal Import Created')
         AND (TRIM (A.JE_SOURCE || A.JE_CATEGORY) NOT IN
                    ('Cost ManagementWIP')
              OR b.DESCRIPTION LIKE 'Journal Import Created')
UNION ALL
SELECT   C.SEGMENT6 CC_CD,
         'Cost Management' JE_SOURCE,
         'Receiving' JE_CATEGORY,
         rsl.ITEM_DESCRIPTION DESCRIPTION,
         c.segment4 account_code,
         NULL NAME,
         rrsl.ACCOUNTED_DR,
         rrsl.ACCOUNTED_CR,
         TO_NUMBER (rsh.receipt_num) doc_sequence_value,
         rrsl.accounting_DATE,
         NULL JE_HEADER_ID,
         NULL JE_LINE_NUM,
         NULL INV_ID,
         NULL INV_NUM,
         NULL LINK_ID,
         C.CODE_COMBINATION_ID,
         C.SEGMENT2,
         TO_CHAR (poh.po_header_id) REFERENCE_4,
         rrsl.PERIOD_NAME,
         rrsl.SET_OF_BOOKS_ID,
         0 opening_balance,
         poh.segment1 po_number,
         (SELECT   pol.line_num
            FROM   apps.po_lines_all pol
           WHERE   pol.po_header_id = rt.po_header_id
                   AND pol.po_line_id = rt.po_line_id)
            po_line_num,
         (SELECT   pol.attribute6
            FROM   apps.po_lines_all pol
           WHERE   pol.po_header_id = rt.po_header_id
                   AND pol.po_line_id = rt.po_line_id)
            po_line_name,
         (SELECT   DISTINCT vendor_name
            FROM   apps.ap_suppliers rr
           WHERE   rr.vendor_id = rt.VENDOR_ID)
            vendor_name,
         NULL vou_num,
         -- d.start_date,
         rt.Quantity Quantity,
         rt.organization_id,
         NULL ITEM_code,
         (SELECT   a.user_name
            FROM   apps.fnd_user a
           WHERE   a.user_id = rt.created_by)
            user_name
  FROM   apps.RCV_RECEIVING_SUB_LEDGER rrsl,
         apps.rcv_transactions rt,
         apps.rcv_shipment_headers rsh,
         apps.rcv_shipment_lines rsl,
         apps.po_headers_all poh,
         apps.GL_CODE_COMBINATIONS C
 --  apps.gl_periods d
 WHERE       rrsl.rcv_transaction_id = rt.transaction_id
         AND rt.shipment_header_id = rsh.shipment_header_id
         AND rsl.shipment_header_id = rsh.shipment_header_id
         AND rsl.shipment_line_id = rt.shipment_line_id
         AND poh.po_header_id = rt.po_header_id
         --         and rrsl.period_name = :P_PERIOD_NAME
         AND rrsl.ACCOUNTING_DATE BETWEEN TO_DATE (
                                             :P_FROM_DATE || ' 00:00:00',
                                             'DD-MON-YYYY HH24:MI:SS'
                                          )
                                      AND  TO_DATE (
                                              :P_TO_DATE || ' 23:59:59',
                                              'DD-MON-YYYY HH24:MI:SS'
                                           )
         AND rrsl.SET_OF_BOOKS_ID = :p_set_of_bks_id
         AND rrsl.ACTUAL_FLAG = 'A'
         AND rrsl.CODE_COMBINATION_ID = C.CODE_COMBINATION_ID
         AND TO_NUMBER (C.SEGMENT4) BETWEEN :acc_cd1 AND :acc_cd2
--          and C.segment2 =
--            DECODE (rrsl.SET_OF_BOOKS_ID,
--                    1, 'SKG',
--                    3, 'SSL',
--                    4, 'SPK',
--                    2, 'SOP',
--                    5, 'SCF',
--                    7, 'TSS',
--                    8, 'SKC',
--                    10, 'EOU',
--                    12, 'SKD',
--                    'AAA')
UNION ALL
SELECT                          /*+ index (mmt MTL_MATERIAL_TRANSACTIONS_N2)*/
      gcc.segment6 cc_cd,
         'INVENTORY' je_source,
         NULL je_category,
         msi.description,
         gcc.segment4 account_code,
         'MTL' || '-' || mmt.CURRENCY_CODE name,
         CASE
            WHEN mta.base_transaction_value >= 0
            THEN
               mta.base_transaction_value
            ELSE
               0
         END
            AS accounted_dr,
         CASE
            WHEN mta.base_transaction_value < 0
            THEN
               ABS (mta.base_transaction_value)
            ELSE
               0
         END
            AS accounted_cr,
         0 doc_sequence_value,
         TRUNC (mmt.transaction_date) accounting_DATE,
         0 je_header_id,
         0 je_line_num,
         NULL inv_id,
         NULL inv_num,
         0 link_id,
         gcc.code_combination_id,
         gcc.segment2,
         NULL reference_4,
         (SELECT   gp.PERIOD_NAME
            FROM   apps.gl_periods gp
           WHERE       PERIOD_SET_NAME = 'Sona Fiscal Cal'
                   AND ROWNUM = 1
                   AND gp.ADJUSTMENT_PERIOD_FLAG = 'N'
                   AND TRUNC (mmt.Transaction_date) BETWEEN gp.START_DATE
                                                        AND  gp.END_DATE)
            period_name,
         DECODE (mmt.organization_id,
                 3, 1,
                 24, 3,
                 44, 4,
                 45, 2,
                 46, 4,
                 65, 7,
                 144, 8,
                 165, 10,
                 185, 12,
                 999)
            SET_OF_BOOKS_ID,
         0 opening_balance,
         NULL po_number,
         (SELECT   DISTINCT b.LINE_NUMBER
            FROM   apps.mtl_txn_request_lines b
           WHERE   mmt.move_order_line_id = b.line_id
                   AND mmt.organization_id = B.organization_id)
            po_line_num,
         (SELECT   DISTINCT attribute12
            FROM   apps.mtl_txn_request_lines b
           WHERE   mmt.move_order_line_id = b.line_id
                   AND mmt.organization_id = B.organization_id)
            po_line_name,
         NULL vendor_name,
         (SELECT   DISTINCT REQUEST_NUMBER
            FROM   apps.mtl_txn_request_headers a,
                   apps.mtl_txn_request_lines b
           WHERE       a.header_id = b.header_id
                   AND a.organization_id = b.organization_id
                   AND mmt.move_order_line_id = b.line_id
                   AND mmt.organization_id = a.organization_id)
            Vou_Num,
         --mmt.transaction_Date Start_Date,
         mmt.primary_quantity Quantity,
         mmt.organization_id,
         msi.segment1 Item_Code,
         (SELECT   a.user_name
            FROM   apps.fnd_user a
           WHERE   a.user_id = mmt.created_by)
            user_name
  FROM   apps.mtl_material_transactions mmt,
         apps.mtl_transaction_accounts mta,
         apps.mtl_system_items_b msi,
         apps.gl_code_combinations gcc
 WHERE       mmt.TRANSACTION_ID = mta.transaction_id
         AND mmt.INVENTORY_ITEM_ID = msi.INVENTORY_ITEM_ID
         AND mmt.ORGANIZATION_ID = msi.ORGANIZATION_ID
         AND mta.REFERENCE_ACCOUNT = gcc.CODE_COMBINATION_ID
         AND mmt.TRANSACTION_DATE BETWEEN TO_DATE (
                                             :P_FROM_DATE || ' 00:00:00',
                                             'DD-MON-YYYY HH24:MI:SS'
                                          )
                                      AND  TO_DATE (
                                              :P_TO_DATE || ' 23:59:59',
                                              'DD-MON-YYYY HH24:MI:SS'
                                           )
         AND msi.organization_id = :Organization_id
         AND TO_NUMBER (GCC.SEGMENT4) BETWEEN :FROM_ACC AND :TO_ACC
UNION ALL
SELECT                                    /*+ index (wt WIP_TRANSACTIONS_N2)*/
      gcc.segment6 cc_cd,
         'INVENTORY' je_source,
         NULL je_category,
         msi.description,
         gcc.segment4 account_code,
         'WIP' || '-' || wt.CURRENCY_CODE name,
         CASE
            WHEN wta.base_transaction_value >= 0
            THEN
               wta.base_transaction_value
            ELSE
               0
         END
            AS accounted_dr,
         CASE
            WHEN wta.base_transaction_value < 0
            THEN
               ABS (wta.base_transaction_value)
            ELSE
               0
         END
            AS accounted_cr,
         0 doc_sequence_value,
         TRUNC (wt.transaction_date) accounting_DATE,
         0 je_header_id,
         0 je_line_num,
         NULL inv_id,
         NULL inv_num,
         0 link_id,
         gcc.code_combination_id,
         gcc.segment2,
         NULL reference_4,
         (SELECT   gp.PERIOD_NAME
            FROM   apps.gl_periods gp
           WHERE       PERIOD_SET_NAME = 'Sona Fiscal Cal'
                   AND ROWNUM = 1
                   AND gp.ADJUSTMENT_PERIOD_FLAG = 'N'
                   AND TRUNC (wt.Transaction_date) BETWEEN gp.START_DATE
                                                       AND  gp.END_DATE)
            period_name,
         DECODE (Wt.organization_id,
                 3, 1,
                 24, 3,
                 44, 4,
                 45, 2,
                 46, 4,
                 65, 7,
                 144, 8,
                 165, 10,
                 185, 12,
                 999)
            SET_OF_BOOKS_ID,
         0 opening_balance,
         NULL po_number,
         NULL po_line_num,
         NULL po_line_name,
         NULL vendor_name,
         --         (SELECT   DISTINCT WIP_ENTITY_NAME
         --            FROM   wip_discrete_jobs_v a
         --           WHERE   a.WIP_ENTITY_ID = wt.WIP_ENTITY_ID
         --                   AND a.ORGANIZATION_ID = wt.ORGANIZATION_ID)
         NULL Vou_Num,
         --wt.transaction_Date Start_Date,
         Wt.primary_quantity Quantity,
         wt.organization_id,
         msi.segment1 Item_Code,
         (SELECT   a.user_name
            FROM   apps.fnd_user a
           WHERE   a.user_id = wt.created_by)
            user_name
  FROM   apps.wip_transactions wt,
         apps.wip_transaction_accounts wta,
         apps.mtl_system_items_b msi,
         apps.gl_code_combinations gcc
 WHERE       wt.transaction_id = wta.transaction_id
         AND wt.PRIMARY_ITEM_ID = msi.INVENTORY_ITEM_ID
         AND wt.ORGANIZATION_ID = msi.ORGANIZATION_ID
         AND wta.REFERENCE_ACCOUNT = gcc.CODE_COMBINATION_ID
         AND WT.TRANSACTION_DATE BETWEEN TO_DATE (
                                            :P_FROM_DATE || ' 00:00:00',
                                            'DD-MON-YYYY HH24:MI:SS'
                                         )
                                     AND  TO_DATE (:P_TO_DATE || ' 23:59:59',
                                                   'DD-MON-YYYY HH24:MI:SS')
         AND msi.organization_id = :Organization_id
         AND TO_NUMBER (GCC.SEGMENT4) BETWEEN :FROM_ACC AND :TO_ACC