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

                )