GL TO AP QUERY

select * from
(select (select vendor_name from ap_suppliers where vendor_id=b.vendor_id) supplier,
b.invoice_id,b.INVOICE_NUM,b.voucher_no,b.SOURCE,b.INVOICE_TYPE_LOOKUP_CODE type,a.APPLIED_TO_SOURCE_ID_NUM_1,a.ACCOUNT_CODE,
--a.JE_CATEGORY_NAME,
nvl(sum(a.DR),0)-nvl(sum(a.CR),0) diff,
sum(a.DR) dr,sum(a.CR) cr
from
(
select  xah.ae_header_id,xal.ae_line_num,xdl.APPLIED_TO_SOURCE_ID_NUM_1,gcc.segment1||'.'||gcc.segment2||'.'||gcc.segment3||'.'||gcc.segment4 account_Code,xah.JE_CATEGORY_NAME,xal.ACCOUNTED_DR,xal.ACCOUNTED_cr,
sum(xdl.UNROUNDED_ACCOUNTED_DR) dr,sum(xdl.UNROUNDED_ACCOUNTED_CR) cr--,sum(xal.ACCOUNTED_DR),sum(xal.ACCOUNTED_cr)
from gl_import_references gir,xla_ae_lines xal,xla_ae_headers xah,gl_je_headers gjh,gl_je_lines gjl,gl_code_combinations gcc,
xla_distribution_links xdl--,
--ap_invoices_All aia
--xla.xla_events xev,
--xla.xla_transaction_entities XTE
where
--je_header_id=415326 and je_line_num=3
--and
 gir.GL_SL_LINK_ID=xal.GL_SL_LINK_ID
AND
gir.GL_SL_LINK_TABLE = xal.GL_SL_LINK_TABLE
AND XAH.ae_header_id = XAL.ae_header_id
AND XAH.gl_transfer_status_code= 'Y'
and trunc(gjl.effective_date) <= :to_date
and xal.code_combination_id in (SELECT code_Combination_id from gl_Code_combinations where segment4=:account_code)-- (26332,26347,48810,76059,77278,164197)
and gir.je_header_id=gjh.je_header_id
and gjh.je_header_id=gjl.je_header_id
and gir.je_header_id=gjl.je_header_id
and gir.je_line_num=gjl.je_line_num
and gjl.code_combination_id=gcc.code_combination_id
--AND xah.event_id= xev.event_id
--AND xev.entity_id= xte.entity_id
--AND xah.entity_id= xte.entity_id
and xah.ae_header_id=xdl.ae_header_id
and xal.ae_line_num=xdl.ae_line_num
and xal.ae_header_id=xdl.ae_header_id
--and aia.INVOICE_ID = xte.source_id_int_1(+)
AND gjh.STATUS='P'
AND gjh.Actual_flag='A'
AND XAH.gl_transfer_status_code= 'Y'
--and xdl.APPLIED_TO_SOURCE_ID_NUM_1=aia.invoice_id
group by
xah.ae_header_id,xal.ae_line_num,xdl.APPLIED_TO_SOURCE_ID_NUM_1,gcc.segment1,gcc.segment2,gcc.segment3,gcc.segment4,xah.JE_CATEGORY_NAME,xal.ACCOUNTED_DR,xal.ACCOUNTED_cr
) a,
(select aia.invoice_id,aia.invoice_num,aia.vendor_id,aia.source,aia.invoice_type_lookup_code,aia.DOC_SEQUENCE_VALUE voucher_no from ap_invoices_all aia) b
where
a.APPLIED_TO_SOURCE_ID_NUM_1=b.invoice_id(+)
group by
b.invoice_id,b.INVOICE_NUM,b.vendor_id,b.voucher_no,b.SOURCE,b.INVOICE_TYPE_LOOKUP_CODE,a.APPLIED_TO_SOURCE_ID_NUM_1,a.ACCOUNT_CODE--,a.JE_CATEGORY_NAME
union all
select
null supplier_name,null invoice_id,null INVOICE_NUM,null voucher_no,glh.je_source SOURCE,null  type,null APPLIED_TO_SOURCE_ID_NUM_1,
gcc.segment1||'.'||gcc.segment2||'.'||gcc.segment3||'.'||gcc.segment4 ACCOUNT_CODE,--glh.je_Category JE_CATEGORY_NAME,
nvl(sum(gjl.accounted_dr),0)-nvl(sum(gjl.accounted_cr),0) diff,
sum(gjl.accounted_dr) dr ,
sum(gjl.accounted_cr) CR
from gl_je_headers glh,gl_je_lines gjl,gl_code_combinations gcc  
where
glh.je_header_id=gjl.je_header_id
and
 trunc(gjl.effective_date) <= :to_date
and gjl.code_combination_id in (SELECT code_Combination_id from gl_Code_combinations where segment4=:account_code)
and glh.JE_SOURCE in ('Spreadsheet','Manual')
and gjl.code_Combination_id=gcc.code_combination_id
group by
glh.je_source,--glh.je_Category,
gcc.segment1,gcc.segment2,gcc.segment3,gcc.segment4
)
where
diff<>0
order by
applied_to_source_id_num_1

SALES ORDER TO AR INVOICE

SELECT   oha.order_number,
         ola.line_number so_line_number,
         ola.ordered_item item_name,
         ola.ordered_quantity * ola.unit_selling_price LINE_AMOUNT,
         rcta.trx_number Transaction_number,
         rcta.trx_date,
         rctla.line_number TRX_line_number,
         rctla.unit_selling_price unit_selling_price
  FROM   oe_order_headers_all oha,
         oe_order_lines_all ola,
         ra_customer_trx_all rcta,
         ra_customer_trx_lines_all rctla
 WHERE       oha.header_id = ola.header_id
         AND rcta.customer_trx_id = rctla.customer_trx_id
         AND rctla.interface_line_attribute6 = TO_CHAR (ola.line_id)
         AND rctla.interface_line_attribute1 = TO_CHAR (oha.order_number)
         AND order_number = :p_order_number

STRING_AGG

                                                             REGEXP_SUBSTR

/* Formatted on 2015/03/23 18:14 (Formatter Plus v4.8.8) */
CREATE OR REPLACE TYPE t_string_agg AS OBJECT (
   g_string   VARCHAR2 (32767),
   STATIC FUNCTION odciaggregateinitialize (sctx IN OUT t_string_agg)
      RETURN NUMBER,
   MEMBER FUNCTION odciaggregateiterate (
      SELF    IN OUT   t_string_agg,
      VALUE   IN       VARCHAR2
   )
      RETURN NUMBER,
   MEMBER FUNCTION odciaggregateterminate (
      SELF          IN       t_string_agg,
      returnvalue   OUT      VARCHAR2,
      flags         IN       NUMBER
   )
      RETURN NUMBER,
   MEMBER FUNCTION odciaggregatemerge (
      SELF   IN OUT   t_string_agg,
      ctx2   IN       t_string_agg
   )
      RETURN NUMBER
);
/



CREATE OR REPLACE TYPE BODY t_string_agg
IS
   STATIC FUNCTION odciaggregateinitialize (sctx IN OUT t_string_agg)
      RETURN NUMBER
   IS
   BEGIN
      sctx := t_string_agg (NULL);
      RETURN odciconst.success;
   END;
   MEMBER FUNCTION odciaggregateiterate (
      SELF    IN OUT   t_string_agg,
      VALUE   IN       VARCHAR2
   )
      RETURN NUMBER
   IS
   BEGIN
      SELF.g_string := SELF.g_string || ',' || VALUE;
      RETURN odciconst.success;
   END;
   MEMBER FUNCTION odciaggregateterminate (
      SELF          IN       t_string_agg,
      returnvalue   OUT      VARCHAR2,
      flags         IN       NUMBER
   )
      RETURN NUMBER
   IS
   BEGIN
      returnvalue := RTRIM (LTRIM (SELF.g_string, ','), ',');
      RETURN odciconst.success;
   END;
   MEMBER FUNCTION odciaggregatemerge (
      SELF   IN OUT   t_string_agg,
      ctx2   IN       t_string_agg
   )
      RETURN NUMBER
   IS
   BEGIN
      SELF.g_string := SELF.g_string || ',' || ctx2.g_string;
      RETURN odciconst.success;
   END;
END;
/



CREATE OR REPLACE FUNCTION string_agg (p_input VARCHAR2)
RETURN VARCHAR2
PARALLEL_ENABLE AGGREGATE USING t_string_agg;

select string_agg(segment1) from po_distributions_v where PO_HEADER_ID =266266

TRANSACTION HISTORICAL SUMMARY REPORT ON HAND QTY

                    TRANSACTION HISTORICAL SUMMARY REPORT ON HAND QTY


SELECT   SUM (mmt.primary_quantity)
--  INTO   i
  FROM   apps.mtl_transaction_types mtt,
         apps.MTL_MATERIAL_TRANSACTIONS mmt,
         apps.mtl_system_items msi
 WHERE       mtt.TRANSACTION_TYPE_ID = mmt.TRANSACTION_TYPE_ID
         AND mmt.organization_id = msi.organization_id
         AND mmt.inventory_item_id = msi.inventory_item_id
         AND (mmt.organization_id = 109)
         AND msi.segment1 = 'PLPCBWLAPI'
         AND TRUNC (MMT.transaction_date) <= TO_DATE (:P_FROM_DATE);

OPENING QTY IN INVENTORY

SELECT   NVL (SUM (mmt.transaction_quantity), 0) AS stock , mmt.inventory_item_id
    FROM   apps.mtl_system_items_b msib,
           apps.mtl_material_transactions mmt,
           apps.rcv_transactions rt
   WHERE   msib.inventory_item_id = mmt.inventory_item_id
           AND msib.organization_id = mmt.organization_id
           AND msib.inventory_item_id BETWEEN NVL (:P_Seg, 61674)
            AND  NVL (:P_Seg, 61676) --= NVL (p_seg, msib.inventory_item_id)
           AND rt.transaction_id(+) = mmt.transaction_id
           AND msib.organization_id = 104
           --and mmt.transaction_id = j.trancation_id
           AND mmt.transaction_date < :p_from_date
           AND transaction_action_id NOT IN (24, 30)
           AND (logical_transaction = 2 OR logical_transaction IS NULL)
group by mmt.inventory_item_id 

6AM TO 6AM DATE FORMAT

 AND (mmt.transaction_date) BETWEEN TO_DATE (
                                                                                           TO_CHAR (
                                                                                              :P_From_date,
                                                                                              'fmdd/mon/yyyy'
                                                                                           )
                                                                                           || ' '
                                                                                           || '06:00:00',
                                                                                           'fmdd/mm/yyyy hh24:mi:ss'
                                                                                        )
                                                                                    AND  TO_DATE (
                                                                                            TO_CHAR (
                                                                                               :P_TO_DATE
                                                                                               + 1,
                                                                                               'fmdd/mm/yyyy'
                                                                                            )
                                                                                            || ' '
                                                                                            || '05:59:59',
                                                                                            'fmdd/mm/yyyy hh24:mi:ss'
                                                                                         )

FINANCIAL YEAR DATE_FORMAT LIKE FROM_DATE (APR-13) AND TO_DATE(AUG-13)

TRUNC (FFIC.TRANSACTION_DATE_ENTERED) BETWEEN TO_DATE (
                                                              TO_CHAR (
                                                                 TO_DATE (
                                                                    :FROM_DATE,
                                                                    'MON-RR'
                                                                 ),
                                                                 'MON-rr'
                                                              ),
                                                              'MON-rr'
                                                           )
                                                       AND  TO_DATE (
                                                               TO_CHAR (
                                                                  TO_DATE (
                                                                     :DATE_TO,
                                                                     'MON-RR'
                                                                  ),
                                                                  'MON-rr'
                                                               ),
                                                               'MON-rr'
                                                            )