GL DRILL Down Query From GL into Fixed Assets Sub ledger Module.

The below query will  provide the complete breakup of posted Journals with transaction distribution wise breakup details of Fixed assets(all sources) sub-ledger modules.

SELECT  GCC.concatenated_segments segments, 

               GJL.period_name,

               GJH.NAME journal_name, 

               GJB.NAME batch_name,

               GJH.je_source journal_source, 

               GJH.je_category journal_category,

               GCC.segment1 entity_segment, 

               GCC.segment2 project_segment,

               FFV.attribute1 project_vertical_dff,

               GCC.segment3, 

               GCC.segment4, 

               GCC.segment5,

               GCC.segment6, 

               GCC.segment7, 

               TO_CHAR (GJH.doc_sequence_value) gl_doc_no,

               TO_CHAR (GJH.default_effective_date, 'DD-MON-YYYY') gl_date,

               TO_CHAR (GJH.posted_date, 'DD-MON-YYYY') posted_date,

               NULL sla_event_class,

               XAH.event_type_code sla_event_type,

               TO_CHAR (FTH.asset_id) document_number,

               FT.description document_description,

               TO_CHAR (FB.date_placed_in_service, 'DD-MON-YYYY') document_date,

       NVL ((XAL.unrounded_entered_cr) * -1,XAL.unrounded_entered_dr) transaction_cur_amount,

               GL.currency_code entered_currency_code, 

  NVL((XAL.unrounded_accounted_cr)*-1,XAL.unrounded_accounted_dr) functional_currency_amount,

  FROM   xla_ae_lines XAL,

               xla_ae_headers XAH,

               xla.xla_transaction_entities XTE,

               gl_je_headers GJH,

               gl_je_lines GJL,

               gl_je_batches GJB,

               gl_import_references GIR,

               gl_je_categories GJC,

               gl_ledgers GL,

               gl_code_combinations_kfv GCC,

               fa_additions_tl FT,

               fa_books FB,

               fa_transaction_headers FTH,

               fnd_flex_value_sets FVS,

               fnd_flex_values FFV

 WHERE         1 = 1

   AND         GJL.je_header_id                     = GJH.je_header_id

   AND         GJC.je_category_name            = GJH.je_category

   AND         GCC.code_combination_id         = GJL.code_combination_id

   AND         GIR.je_header_id                     = GJH.je_header_id

   AND         XAL.gl_sl_link_id                      = GIR.gl_sl_link_id

   AND         GIR.je_line_num                       = GJL.je_line_num

   AND         GJB.je_batch_id                       = GJH.je_batch_id

   AND         XAH.ae_header_id                   = XAL.ae_header_id

   AND         XTE.entity_id                            = XAH.entity_id

   AND         FB.date_ineffective  IS NULL

   AND         GJH.actual_flag                        = 'A'

   AND         GJH.status                                = 'P'

   AND         GJH.je_source                          = 'Assets'

   AND         GJC.user_je_category_name  IN   ('Transfer', 'Reclass')

   AND         GJH.je_source                         <> 'Consolidation'

   AND         FT.asset_id                                = FTH.asset_id

   AND         FT.asset_id                                =  FB.asset_id

   AND         XTE.source_id_int_1(+)            = FTH.transaction_header_id

   AND         FB.book_type_code                  = FTH.book_type_code

   AND         GJH.ledger_id                           = GL.ledger_id

   AND         FFV.flex_value_set_id               = FVS.flex_value_set_id(+)

   AND         GCC.segment2                          = FFV.flex_value(+)

   AND         UPPER (FVS.flex_value_set_name) = UPPER ('PLL_Project')

   AND         GJH.default_effective_date     >= lc_gl_date_from

   AND         GJH.default_effective_date     <= lc_gl_date_to

 AND TRUNC (gjh.posted_date) BETWEEN NVL (lc_gl_posted_from,TRUNC (gjh.posted_date))AND NVL (lc_gl_posted_to,TRUNC (gjh.posted_date))

   AND          GJH.je_source                  = NVL (p_gl_source, GJH.je_source)

   AND          GJH.je_category                = NVL (p_gl_category, GJH.je_category) 

--   AND      gcc.concatenated_segments BETWEEN (p_account_from) AND (p_account_to)

   AND          GCC.segment1 BETWEEN lc_segment_from(1) AND lc_segment_to(1)

   AND          GCC.segment2 BETWEEN lc_segment_from(2) AND lc_segment_to(2)

   AND          GCC.segment3 BETWEEN lc_segment_from(3) AND lc_segment_to(3)

   AND          GCC.segment4 BETWEEN lc_segment_from(4) AND lc_segment_to(4)

   AND          GCC.segment5 BETWEEN lc_segment_from(5) AND lc_segment_to(5)

   AND          GCC.segment6 BETWEEN lc_segment_from(6) AND lc_segment_to(6)

   AND          GCC.segment7 BETWEEN lc_segment_from(7) AND lc_segment_to(7)

   AND          NVL (FFV.attribute1, '-1')      = NVL (p_proj_vertical_dff, NVL (FFV.attribute1, '-1'))

UNION ALL

SELECT         GCC.concatenated_segments segments, 

               GJL.period_name,

               GJH.NAME journal_name, 

               GJB.NAME batch_name,

               GJH.je_source journal_source, 

               GJH.je_category journal_category,

               GCC.segment1 entity_segment, 

               GCC.segment2 project_segment,

               FFV.attribute1 project_vertical_dff,

               GCC.segment3, 

               GCC.segment4, 

               GCC.segment5,

               GCC.segment6, 

               GCC.segment7, 

               TO_CHAR (GJH.doc_sequence_value) gl_doc_no,

               TO_CHAR (GJH.default_effective_date, 'DD-MON-YYYY') gl_date,

               TO_CHAR (GJH.posted_date, 'DD-MON-YYYY') posted_date,

               NULL sla_event_class, 

               XAH.event_type_code sla_event_type,

               TO_CHAR (FTH.asset_id) document_number,

               FT.description document_description,

               TO_CHAR (FB.date_placed_in_service, 'DD-MON-YYYY') document_date,

        NVL ((XAL.unrounded_entered_cr) * -1,XAL.unrounded_entered_dr) transaction_cur_amount,

               GL.currency_code entered_currency_code,

               NVL ((XAL.unrounded_accounted_cr) * -1,XAL.unrounded_accounted_dr) functional_currency_amount,

  FROM         xla_ae_lines XAL,

               xla_ae_headers XAH,

               xla.xla_transaction_entities XTE,

               gl_je_headers GJH,

               gl_je_lines GJL,

               gl_je_batches GJB,

               gl_import_references GIR,

               gl_je_categories GJC,

               gl_ledgers GL,

               gl_code_combinations_kfv GCC,

               fa_additions_tl FT,

               fa_books FB,

               fa_transaction_headers FTH,

               fnd_flex_value_sets FVS,

               fnd_flex_values FFV

 WHERE         1 = 1

   AND         GJL.je_header_id                           = GJH.je_header_id

   AND         GJC.je_category_name                  = GJH.je_category

   AND         GCC.code_combination_id             = GJL.code_combination_id

   AND         GIR.je_header_id                           = GJH.je_header_id

   AND         XAL.gl_sl_link_id                            = GIR.gl_sl_link_id

   AND         GIR.je_line_num                            = GJL.je_line_num

   AND         GJB.je_batch_id                            = GJH.je_batch_id

   AND         XAH.ae_header_id                        = XAL.ae_header_id

   AND         XTE.entity_id                                 = XAH.entity_id

   AND         GJH.ledger_id                                = GL.ledger_id

   AND         GJH.actual_flag                             = 'A'

   AND         GJH.status                                     = 'P'

   AND         GJH.je_source                               = 'Assets'

   AND         FB.date_ineffective    IS NULL

   AND         GJC.user_je_category_name    IN('Addition', 'CIP Addition', 'Adjustment', 'CIP Adjustment','Retirement','CIP Retirement')

   AND         GJH.je_source                              <> 'Consolidation'

   AND         FT.asset_id                                      = FTH.asset_id

   AND         FT.asset_id                                      = FB.asset_id

   AND         XTE.source_id_int_1(+)                   = FTH.transaction_header_id

   AND         FB.book_type_code                         = FTH.book_type_code

   AND         GJH.default_effective_date            >= lc_gl_date_from

   AND         GJH.default_effective_date            <= lc_gl_date_to

   AND         FFV.flex_value_set_id                      = FVS.flex_value_set_id(+)

   AND         GCC.segment2                                 = FFV.flex_value(+)

   AND         UPPER (FVS.flex_value_set_name) = UPPER ('PLL_Project')

   AND         TRUNC (GJH.posted_date) BETWEEN NVL (lc_gl_posted_from,TRUNC (GJH.posted_date))AND NVL (lc_gl_posted_to,TRUNC (GJH.posted_date))

   AND          GJH.je_source                  = NVL (p_gl_source, GJH.je_source)

   AND          GJH.je_category                = NVL (p_gl_category, GJH.je_category) 

--   AND      gcc.concatenated_segments BETWEEN (p_account_from) AND (p_account_to)

   AND          GCC.segment1 BETWEEN lc_segment_from(1) AND lc_segment_to(1)

   AND          GCC.segment2 BETWEEN lc_segment_from(2) AND lc_segment_to(2)

   AND          GCC.segment3 BETWEEN lc_segment_from(3) AND lc_segment_to(3)

   AND          GCC.segment4 BETWEEN lc_segment_from(4) AND lc_segment_to(4)

   AND          GCC.segment5 BETWEEN lc_segment_from(5) AND lc_segment_to(5)

   AND          GCC.segment6 BETWEEN lc_segment_from(6) AND lc_segment_to(6)

   AND          GCC.segment7 BETWEEN lc_segment_from(7) AND lc_segment_to(7)

   AND          NVL (FFV.attribute1, '-1')      = NVL (p_proj_vertical_dff, NVL (FFV.attribute1, '-1'))

   UNION ALL

   SELECT            GLCC.concatenated_segments segments

                    ,GJL.period_name

                    ,GJH.name journal_name

                    ,GJB.name batch_name

                    ,GJH.je_source journal_source

                    ,GJH.je_category journal_category

                    ,GLCC.segment1 entity_segment

                    ,GLCC.segment2 project_segment

                    ,FFV.attribute1 project_vertical_dff

                    ,GLCC.segment3

                    ,GLCC.segment4

                    ,GLCC.segment5

                    ,GLCC.segment6

                    ,GLCC.segment7

                    ,TO_CHAR (GJH.doc_sequence_value) gl_doc_no

                    ,TO_CHAR (GJH.default_effective_date, 'DD-MON-YYYY') gl_date

                    ,TO_CHAR (GJH.posted_date, 'DD-MON-YYYY') posted_date

                    ,(SELECT NAME

                             FROM xla_event_classes_tl XECT

                            WHERE XECT.event_class_code = XDL.event_class_code

                              AND XECT.application_id = XDL.application_id) sla_event_class

                    ,XAH.event_type_code sla_event_type

                    ,TO_CHAR(FDS.asset_id) document_number

                    ,FAD.description document_description

                    ,TO_CHAR (FB.date_placed_in_service, 'DD-MON-YYYY') document_date

                    ,NVL((XDL.unrounded_accounted_cr)*-1,(XDL.unrounded_accounted_dr)) transaction_cur_amount

                    ,GL.currency_code entered_currency_code

                    ,NVL((XDL.unrounded_accounted_cr)*-1,(XDL.unrounded_accounted_dr)) functional_currency_amount

  FROM         fa_additions FAD

                    ,fa_books FB

                    ,fa_book_controls FBC

                    ,fa_deprn_summary FDS

                    ,xla_distribution_links XDL

                    ,xla_ae_lines XAL

                    ,xla_ae_headers XAH

                    ,gl_import_references GIR

                    ,gl_je_lines GJL

                    ,gl_je_headers GJH

                    ,gl_je_batches GJB

                    ,gl_code_combinations_kfv GLCC

                    ,gl_ledgers GL

                    ,fnd_flex_value_sets FVS

                    ,fnd_flex_values FFV

 WHERE      1=1

   AND      FAD.asset_id                             = FB.asset_id

   AND      FB.book_type_code                  = FBC.book_type_code

   AND      FB.DATE_INEFFECTIVE IS NULL

   AND      FAD.asset_id                             = FDS.asset_id

   AND      FB.book_type_code                  =  FDS.book_type_code

   AND      FBC.book_type_code               = FDS.book_type_code

   AND      fds.deprn_source_code            ='DEPRN' 

   AND      FDS.asset_id                            = XDL.source_distribution_id_num_1

   AND      FB.book_type_code                 = XDL.source_distribution_id_char_4

   AND      FBC.set_of_books_id               = GL.ledger_id

   AND      XDL.ae_header_id                   = XAL.ae_header_id

   AND      XDL.ae_line_num                    = XAL.ae_line_num

   AND      XAL.ae_header_id                   = XAH.ae_header_id

   AND      XAH.application_id                  = XAL.application_id

   AND      XAL.gl_sl_link_id                      = GIR.gl_sl_link_id

   AND      XAL.gl_sl_link_table                 = GIR.gl_sl_link_table

   AND      GIR.je_line_num                      = GJL.je_line_num

   AND      GIR.je_header_id                    = GJL.je_header_id

   AND      GIR.je_header_id                    = GJH.je_header_id

   AND      GJH.je_batch_id                      = GJB.je_batch_id

   AND      GJL.code_combination_id       = GLCC.code_combination_id

   AND      GJH.status                               = 'P'

-- and UPPER(XDL.rounding_class_code) ='ASSET'

   AND      UPPER(FDS.deprn_source_code) IN ('BOOKS','DEPRN')

   AND      UPPER (GJH.je_source)                  = 'ASSETS' 

   AND      UPPER (GJH.je_category)               = 'DEPRECIATION'

   AND      FFV.flex_value_set_id                      = FVS.flex_value_set_id(+)

   AND      GLCC.segment2                               = FFV.flex_value(+) 

   AND      UPPER (FVS.flex_value_set_name) = UPPER ('PLL_Project')

   AND      GJH.default_effective_date     >= lc_gl_date_from

   AND      GJH.default_effective_date     <= lc_gl_date_to

   AND      TRUNC (GJH.posted_date) BETWEEN NVL (lc_gl_posted_from,TRUNC (GJH.posted_date))AND NVL (lc_gl_posted_to,TRUNC (GJH.posted_date))

   AND      GJH.je_source                   = NVL (p_gl_source, GJH.je_source)

   AND      GJH.je_category                 = NVL (p_gl_category, GJH.je_category)

   AND      GLCC.segment1 BETWEEN lc_segment_from(1) AND lc_segment_to(1)

   AND      GLCC.segment2 BETWEEN lc_segment_from(2) AND lc_segment_to(2)

   AND      GLCC.segment3 BETWEEN lc_segment_from(3) AND lc_segment_to(3)

   AND      GLCC.segment4 BETWEEN lc_segment_from(4) AND lc_segment_to(4)

   AND      GLCC.segment5 BETWEEN lc_segment_from(5) AND lc_segment_to(5)

   AND      GLCC.segment6 BETWEEN lc_segment_from(6) AND lc_segment_to(6)

   AND      GLCC.segment7 BETWEEN lc_segment_from(7) AND lc_segment_to(7)

   AND      NVL (FFV.attribute1, '-1')      = NVL (p_proj_vertical_dff, NVL (FFV.attribute1, '-1'))

--SK.

Drill Down Query From GL into Project Accounting subledger

The below query will  provide the complete breakup of posted Journals with transaction distribution wise breakup details of Project Accounting sub-ledger modules.

SELECT      GLCC.concatenated_segments segments

                    ,GJL.period_name

                    ,GJH.name journal_name

                    ,GJB.name batch_name

                    ,GJH.je_source journal_source

                    ,GJH.je_category journal_category

                    ,GLCC.segment1 entity_segment

                    ,GLCC.segment2 project_segment

                    ,FFV.attribute1 project_vertical_dff

                    ,GLCC.segment3

                    ,GLCC.segment4

                    ,GLCC.segment5

                    ,GLCC.segment6

                    ,GLCC.segment7

                    ,TO_CHAR (GJH.doc_sequence_value) gl_doc_no

                    ,TO_CHAR (GJH.default_effective_date, 'DD-MON-YYYY') gl_date

                    ,TO_CHAR (GJH.posted_date, 'DD-MON-YYYY') posted_date

                    ,PE.expenditure_group document_number

                    ,PE.expenditure_group document_description

                    ,TO_CHAR (PEI.expenditure_item_date, 'DD-MON-YYYY')  document_date

--                  ,PPA.project_status_code document_status     

                    ,PPA.segment1 project_code 

                    ,PT.task_number task_code        

                    ,PEI.expenditure_type  

                    ,PEI.raw_cost transaction_cur_amount

                    ,PPA.project_currency_code entered_currency_code

                    ,PPA.project_rate_type exchange_rate_type

                    ,PEI.project_exchange_rate exchange_rat

   FROM       gl_je_batches GJB

                    ,gl_je_headers GJH

                    ,gl_je_lines   GJL

                    ,gl_code_combinations_kfv GLCC

                    ,gl_import_references GIR

                    ,xla_ae_headers  XAH

                    ,xla_ae_lines    XAL

                    ,xla_events      XE

                    ,xla_distribution_links XDL

                    ,pa_cost_distribution_lines_all PDL

                    ,pa_expenditure_items_all PEI

                    ,pa_expenditures_all PE

                    ,pa_tasks PT

                    ,pa_projects_all PPA

                    ,fnd_flex_value_sets FVS

                    ,fnd_flex_values FFV

         WHERE  GJB.je_batch_id         = GJH.je_batch_id          

         AND    GJH.je_header_id        = GJL.je_header_id

         AND    GJL.code_combination_id = GLCC.code_combination_id

         AND    GJL.je_header_id        = GIR.je_header_id

         AND    GJH.je_batch_id         = GIR.je_batch_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    XAH.ae_header_id        = XAL.ae_header_id

         AND    XAH.application_id      = XAL.application_id

         AND    XAH.event_id            = XE.event_id

         AND    XAL.ae_header_id        = XDL.ae_header_id

         AND    XAL.ae_line_num         = XDL.ae_line_num

         AND    XDL.source_distribution_id_num_1 = PDL.expenditure_item_id 

         AND    PDL.expenditure_item_id  = PEI.expenditure_item_id

         AND    PEI.expenditure_id       = PE.expenditure_id

         AND    PEI.task_id              = PT.task_id

         AND    PT.project_id            = PPA.project_id

         AND    FFV.flex_value_set_id    = FVS.flex_value_set_id(+)

         AND    GLCC.segment2            = FFV.flex_value(+) 

         AND UPPER (FVS.flex_value_set_name) = UPPER ('PLL_Project')

    --  AND    XAH.entity_id        = XTE.entity_id

    --  AND xte.application_id = 275

        AND    GJH.je_source               = 'Project Accounting'

        AND    GJH.je_category             = 'Miscellaneous Transaction'

        AND    GJH.status                  = 'P' 

        AND    GJH.default_effective_date >= lc_gl_date_from

        AND    GJH.default_effective_date <= lc_gl_date_to

        AND    TRUNC (GJH.posted_date) BETWEEN NVL (lc_gl_posted_from,TRUNC (GJH.posted_date))AND NVL (lc_gl_posted_to ,TRUNC (GJH.posted_date))

        AND    GJH.je_source               = NVL (p_gl_source, GJH.je_source)

        AND    GJH.je_category             = NVL (p_gl_category, GJH.je_category)

        --        AND    GLCC.concatenated_segments BETWEEN (p_account_from) AND (p_account_to)

        AND    GLCC.segment1 BETWEEN  lc_segment_from(1) AND  lc_segment_to(1)

        AND    GLCC.segment2 BETWEEN  lc_segment_from(2) AND  lc_segment_to(2)

        AND    GLCC.segment3 BETWEEN  lc_segment_from(3) AND  lc_segment_to(3)

        AND    GLCC.segment4 BETWEEN  lc_segment_from(4) AND  lc_segment_to(4)

        AND    GLCC.segment5 BETWEEN  lc_segment_from(5) AND  lc_segment_to(5)

        AND    GLCC.segment6 BETWEEN  lc_segment_from(6) AND  lc_segment_to(6)

        AND    GLCC.segment7 BETWEEN  lc_segment_from(7) AND  lc_segment_to(7)

        AND    NVL (FFV.attribute1, '-1')      = NVL (p_proj_vertical_dff, NVL (FFV.attribute1, '-1'))

ISO Receipt Register -GST Query

SELECT shipment_num, shipped_date, stnno, receipt_num, rcvd_date, send_orgid,

       send_orgname, recv_orgid, recv_org_name, gst_no, segment1, description,

       lot_num, pri_rec_uom, uomcode, LINK, rcv_qty, iso_price, iso_price_uom,

       NVL (tax_amount, 0) tax_amount,

       ((rcv_qty * iso_price) + NVL (tax_amount, 0)) stnvalue, option1, lrno,

       lrdate, freight

  FROM (SELECT shipment_num, shipped_date, stnno, receipt_num, rcvd_date,

               send_orgid, send_orgname, recv_orgid, recv_org_name, gst_no,

               segment1, description, lot_num, pri_rec_uom, uomcode, rcv_qty,

               iso_price_uom,

               CASE

                  WHEN

                      --NVL(iso_price_uom, 'NA') <> NVL(UOMCODE, 'NA') AND

                      NVL (iso_price_uom, 'NA') =

                                                NVL (uommain, 'NA')

                     THEN iso_price / LINK

                  ELSE iso_price

               END iso_price,

               tax_amount,

               CASE

                  WHEN UPPER (:opt1) = 'SENDORG'

                     THEN send_orgname

                  WHEN UPPER (:opt1) = 'SHIPNO'

                     THEN shipment_num

                  WHEN UPPER (:opt1) = 'RCPTNO'

                     THEN receipt_num

                  WHEN UPPER (:opt1) = 'RCPTDATE'

                     THEN TO_CHAR (rcvd_date, 'dd-mon-yyyy')

               END option1,

               lrno, lrdate, freight, LINK

          FROM (SELECT a.shipment_num, a.receipt_num,

                       c.transaction_date rcvd_date,

                       a.ship_to_org_id recv_orgid, e.NAME recv_org_name,

                       i.gst_no, f.organization_id send_orgid,

                       f.NAME send_orgname, a.shipped_date, d.segment1,

                       d.description, g.primary_quantity rcvd_qty, g.lot_num,

                       c.quantity rcv_qty, c.unit_of_measure rec_uom,

                       c.primary_unit_of_measure pri_rec_uom,

                       a.shipment_header_id, b.shipment_line_id,

                       c.transaction_id, b.requisition_line_id,

                       b.item_id AS inventory_item_id, h.LINK, h.uomcode,

                       i.tax_amount, h.uommain

                  FROM rcv_shipment_headers a,

                       rcv_shipment_lines b,

                       rcv_transactions c,

                       (SELECT inventory_item_id, segment1, description

                          FROM mtl_system_items

                         WHERE organization_id = 83) d,

                       hr_all_organization_units e,

                       (SELECT a.vat_reg_no tin_no, a.organization_id,

                               b.location_code NAME, a.location_id

                          FROM jai_cmn_inventory_orgs a,

                               hr_locations b,

                               hr_all_organization_units c

                         -- ja_in_hr_organization_units changed for R12

                        WHERE  a.organization_id = b.inventory_organization_id

                           AND b.inactive_date IS NULL

                           AND a.location_id = b.location_id

                           AND a.organization_id = c.organization_id

                           AND a.location_id = c.location_id) f,

                       rcv_lot_transactions g

                       (SELECT   trx_id, trx_line_id, trx_number,

                                 first_party_primary_reg_num gst_no,

                                 organization_id,

                                 SUM (rounded_tax_amt_tax_curr) tax_amount,

                                 trx_loc_line_id

                            FROM jai_tax_lines_all

                           WHERE trx_type = 'RECEIVE'

                             AND data_source = 'BASE_TABLE'

                             AND entity_code = 'RCV_TRANSACTION'

                             AND event_class_code = 'RECEIVING'

                             AND applied_from_entity_code =

                                                           'SALES_ORDER_ISSUE'

                             AND applied_from_event_class_code =

                                                           'SALES_ORDER_ISSUE'

                             AND TRUNC (trx_date) BETWEEN :fdate AND :tdate

                        GROUP BY trx_id,

                                 trx_line_id,

                                 trx_number,

                                 first_party_primary_reg_num,

                                 organization_id,

                                 trx_loc_line_id) i

                 ---wsh_new_deliveries  g

                WHERE  a.shipment_header_id = b.shipment_header_id

                   AND b.shipment_header_id = c.shipment_header_id

                   AND b.shipment_line_id = c.shipment_line_id

                   AND i.trx_id (+)= c.shipment_header_id

                   AND i.trx_line_id (+)= c.shipment_line_id

--                AND i.trx_line_id = c.shipment_line_id

--                AND i.trx_number = a.receipt_num

                   AND d.inventory_item_id = b.item_id

--                AND i.trx_number(+) = a.receipt_num

                   AND e.organization_id = a.ship_to_org_id

                   AND a.organization_id = f.organization_id

                   AND b.from_organization_id = f.organization_id

                   AND b.to_organization_id = e.organization_id

--                AND b.shipment_line_id=g.shipment_line_id

--                AND c.shipment_line_id=g.shipment_line_id

                   AND c.transaction_id = g.transaction_id(+)

                   AND c.transaction_id = i.trx_loc_line_id(+)

                   AND d.segment1 = h.appscode

                   AND a.ship_to_org_id = :orgid

                   AND c.transaction_type = 'RECEIVE'

                   AND TRUNC (c.transaction_date) BETWEEN :fdate AND :tdate

                   AND TRUNC (c.transaction_date) >= '04-nov-2011'

                   AND a.receipt_source_code = 'INTERNAL ORDER'

--                and a.receipt_num in ('216040')--206397

               )order by option1

                )