Stock Transfer Report query

/* Formatted on 2015/01/06 18:00 (Formatter Plus v4.8.8) */
SELECT DISTINCT    UPPER (hl.address_line_1)
                || CHR (10)
                || hl.address_line_2
                || ','
                || hl.address_line_3
                || ','
                || CHR (10)
                || hl.town_or_city
                || ','
                || hl.postal_code
                || CHR (10)
                || hl.telephone_number_1 from_address,
                NVL (jihou.excise_duty_range, 'NOT APPLICABLE') RANGE,
                NVL (jihou.excise_duty_division, 'NOT APPLICABLE') division,
                NVL (jihou.pan_no, 'NOT APPLICABLE') pan_no,
                NVL (jihou.excise_duty_reg_no, 'NOT APPLICABLE') regn,
                NVL (jihou.ec_code, 'NOT APPLICABLE') ecc_no,
                NVL (jihou.excise_duty_comm, 'NOT APPLICABLE') pla_no,
                'NOT APPLICABLE' exemp,
                NVL (jihou.cst_reg_no, 'NOT APPLICABLE') cst_no,
                NVL (jihou.vat_reg_no, 'NOT APPLICABLE') tin_no,
                ra.cust_account_id customer_id, hp.party_name customer_name,
                oohv.order_number, oohv.ordered_date order_date,
                oohv.flow_status_code ord_status, rtm.NAME term,
                oohv.cust_po_number po_no, oohv.header_id,
                oohv.attribute2 comments,
                oohv.transactional_curr_code currency, oohv.conversion_rate,
                oohv.invoice_to_org_id bill, oohv.ship_to_org_id ship,
                oohv.orig_sys_document_ref ord_reference,
                oolv.inventory_item_id, (oolv.ordered_quantity) qty,
                oolv.order_quantity_uom uom, oolv.subinventory,
                oolv.actual_shipment_date ship_date, mk.description item_name,
                mk.concatenated_segments item_code,
                ((oolv.unit_selling_price) * NVL (oohv.conversion_rate, 1)
                ) rate,
                oolv.unit_selling_price item_price, oolv.line_id,
                oolv.line_number, oohv.attribute10 bill_branch,
                oohv.attribute11 ship_branch,
--           decode(jt.TAX_TYPE,'VALUE ADDED TAX','VAT','EXCISE_SH_EDU_CESS','Sh_Cess','EXCISE_EDUCATION_CESS','Edu_Cess',jt.tax_type) Tax_Name,
--           jo.TAX_RATE,
--           jo.FUNC_TAX_AMOUNT Tax_Amt,
                                             oohv.conversion_rate exch_rate,
                jl.order_line_id, jl.vat_invoice_no, jl.vat_invoice_date,
                ot.NAME order_type, rsa.NAME salesperson, fu.user_name,
                micv.category_set_name category_name,
                micv.category_id category_code,
               
--           jisptl.TAX_LINE_NO,
                DECODE (oohv.attribute14,
                        '10001', 'SALE AGAINST FORM-C',
                        '11111', NULL,
                        '20001', NULL,
                        '90001', 'STOCK TRANSFER AGAINST FORM-F',
                        '90002', 'STOCK TRANSFER- WITH IN STATE',
                        '90003', 'CONSIGNMENT TRANSFER AGAINST FORM-F'
                       ) form_details,
                oolv.unit_list_price price_list_price,
                oolv.ship_from_org_id org_id,
                SUBSTR (mk.concatenated_segments, 1, 1) item_class,
                (SELECT (jitc.tax_rate)
                   FROM jai_om_oe_so_taxes jptl,
                        jai_cmn_taxes_all jitc
                  WHERE jptl.tax_id = jitc.tax_id
                    AND jptl.header_id = jisptl.header_id
                    AND jptl.line_id = jisptl.line_id
                    AND jitc.tax_type = 'Excise') excise_rate1,
                (SELECT SUM (jptl.tax_amount)
                   FROM jai_om_oe_so_taxes jptl,
                        jai_cmn_taxes_all jitc
                  WHERE jptl.tax_id = jitc.tax_id
                    AND jptl.header_id = jisptl.header_id
                    AND jptl.line_id = jisptl.line_id
                    AND jitc.tax_type = 'Excise') excise_amount1,
                (SELECT (jitc.tax_rate)
                   FROM jai_om_oe_so_taxes jptl,
                        jai_cmn_taxes_all jitc
                  WHERE jptl.tax_id = jitc.tax_id
                    AND jptl.header_id = jisptl.header_id
                    AND jptl.line_id = jisptl.line_id
                    AND jitc.tax_type = 'EXCISE_EDUCATION_CESS') excise_cess1,
                (SELECT SUM (jptl.tax_amount)
                   FROM jai_om_oe_so_taxes jptl,
                        jai_cmn_taxes_all jitc
                  WHERE jptl.tax_id = jitc.tax_id
                    AND jptl.header_id = jisptl.header_id
                    AND jptl.line_id = jisptl.line_id
                    AND jitc.tax_type = 'EXCISE_EDUCATION_CESS')
                                                          excise_cess_amount1,
                (SELECT (jitc.tax_rate)
                   FROM jai_om_oe_so_taxes jptl,
                        jai_cmn_taxes_all jitc
                  WHERE jptl.tax_id = jitc.tax_id
                    AND jptl.header_id = jisptl.header_id
                    AND jptl.line_id = jisptl.line_id
                    AND jitc.tax_type = 'EXCISE_SH_EDU_CESS')
                                                         excise_sec_edu_cess1,
                (SELECT SUM (jptl.tax_amount)
                   FROM jai_om_oe_so_taxes jptl,
                        jai_cmn_taxes_all jitc
                  WHERE jptl.tax_id = jitc.tax_id
                    AND jptl.header_id = jisptl.header_id
                    AND jptl.line_id = jisptl.line_id
                    AND jitc.tax_type = 'EXCISE_SH_EDU_CESS')
                                                  excise_sec_edu_cess_amount1,
                (SELECT SUM (d.tax_amount)
                   FROM jai_om_oe_so_taxes d
                  WHERE d.header_id = oohv.header_id
                    AND d.line_id = oolv.line_id) tax_amt,
                (SELECT (jitc.tax_rate)
                   FROM jai_om_oe_so_taxes jptl,
                        jai_cmn_taxes_all jitc
                  WHERE jptl.tax_id = jitc.tax_id
                    AND jptl.header_id = jisptl.header_id
                    AND jptl.line_id = jisptl.line_id
                    AND jitc.tax_type = 'VALUE ADDED TAX') value_added_tax1,
                (SELECT SUM (jptl.tax_amount)
                   FROM jai_om_oe_so_taxes jptl,
                        jai_cmn_taxes_all jitc
                  WHERE jptl.tax_id = jitc.tax_id
                    AND jptl.header_id = jisptl.header_id
                    AND jptl.line_id = jisptl.line_id
                    AND jitc.tax_type = 'VALUE ADDED TAX')
                                                      value_added_tax_amount1,
                (SELECT (jitc.tax_rate)
                   FROM jai_om_oe_so_taxes jptl,
                        jai_cmn_taxes_all jitc
                  WHERE jptl.tax_id = jitc.tax_id
                    AND jptl.header_id = jisptl.header_id
                    AND jptl.line_id = jisptl.line_id
                    AND jitc.tax_type = 'Freight') freight,
                (SELECT SUM (jptl.tax_amount)
                   FROM jai_om_oe_so_taxes jptl,
                        jai_cmn_taxes_all jitc
                  WHERE jptl.tax_id = jitc.tax_id
                    AND jptl.header_id = jisptl.header_id
                    AND jptl.line_id = jisptl.line_id
                    AND jitc.tax_type = 'Freight') freight_amount1,
                jisptl.header_id, hp.party_id, oohv.org_id,
                jisptl.line_id inv_line, oolv.price_list_id,
                oolv.ordered_quantity receipt_qty,
--                (oolv.unit_selling_price * oolv.ordered_quantity
--                ) receipt_value,
                rsh.ship_to_org_id, rsl.mmt_transaction_id rcv_transction,
                rt.transaction_id rt_transct, rsl.requisition_line_id , mmt.transaction_id mater_tranc_id ,
                (select BASE_TRANSACTION_VALUE from cst_inv_distribution_v where transaction_id = mmt.transaction_id and BASE_TRANSACTION_VALUE >= 0) receipt_value
--        (select BASE_TRANSACTION_VALUE from mtl_transaction_accounts where TRANSACTION_ID=  rsl.mmt_transaction_id
--        and organization_id = rsh.SHIP_TO_ORG_ID  and BASE_TRANSACTION_VALUE >= 0) valuess
FROM            ra_terms rtm,
                po_requisition_headers_all prha,
                po_requisition_lines_all prla,
                rcv_shipment_lines rsl,
                rcv_shipment_headers rsh,
                rcv_transactions rt, mtl_material_transactions mmt ,
                oe_order_headers_all oohv,
                oe_transaction_types_tl ot,
                ra_salesreps_all rsa,
                oe_order_lines_all oolv,
                hz_cust_accounts ra,
                jai_cmn_inventory_orgs jihou,
                jai_om_wsh_lines_all jl,
                jai_om_oe_so_taxes jisptl,
                jai_cmn_taxes_all jt,
                hr_locations hl,
                mtl_system_items_kfv mk,
                mtl_item_categories_v micv,
                fnd_user fu,
                hz_parties hp
          WHERE rtm.term_id(+) = oohv.payment_term_id and mmt.RCV_TRANSACTION_ID = rt.transaction_id
            AND oohv.orig_sys_document_ref = prha.segment1
            AND prha.requisition_header_id = prla.requisition_header_id
            AND rsl.requisition_line_id = prla.requisition_line_id
            AND rsh.shipment_header_id = rsl.shipment_header_id
            AND mk.inventory_item_id = prla.item_id
            AND oohv.order_type_id = ot.transaction_type_id
            AND rsh.receipt_source_code = 'INTERNAL ORDER'
            AND rt.shipment_header_id = rsl.shipment_header_id
            AND rsa.salesrep_id(+) = oohv.salesrep_id
            AND rt.shipment_line_id = rsl.shipment_line_id
            AND rt.transaction_type = 'DELIVER'
            AND jl.order_header_id = oohv.header_id
            AND jl.order_line_id = oolv.line_id
            AND ra.party_id = hp.party_id
            AND oolv.inventory_item_id = mk.inventory_item_id
            AND oolv.ship_from_org_id = mk.organization_id
            AND micv.organization_id = mk.organization_id
            AND micv.inventory_item_id = mk.inventory_item_id
            AND oohv.created_by = fu.user_id
            AND jisptl.line_id(+) = oolv.line_id
            AND jisptl.tax_id = jt.tax_id(+)
            AND oohv.sold_to_org_id = ra.cust_account_id
            AND oolv.header_id = oohv.header_id
            AND oolv.org_id = oohv.org_id
            AND hl.inventory_organization_id = oolv.ship_from_org_id
            AND oolv.ship_from_org_id = jihou.organization_id
            AND jihou.location_id = hl.location_id
            AND ot.NAME LIKE '%INTERNAL%'
            AND jl.vat_invoice_no BETWEEN NVL (:from_order, jl.vat_invoice_no)
                                      AND NVL (:to_order, jl.vat_invoice_no)
            AND oolv.inventory_item_id = NVL (:item, oolv.inventory_item_id)
            AND SUBSTR (mk.concatenated_segments, 1, 1) =
                    NVL (:item_class, SUBSTR (mk.concatenated_segments, 1, 1))
            AND micv.category_id = NVL (:item_category, micv.category_id)
            AND jl.vat_invoice_date >= :p_from
            AND jl.vat_invoice_date <= :p_to + 1
--         and  oohv.order_number=nvl(:Order_No,oohv.order_number)
            AND oohv.org_id = NVL (:p_org_id, oohv.org_id)
            AND oolv.ship_from_org_id =
                               NVL (:p_organization_id, oolv.ship_from_org_id)
            AND hl.location_id =
                   DECODE ((SELECT hl1.location_id
                              FROM hr_locations hl1
                             WHERE 1 = 1
                               AND hl1.inventory_organization_id =
                                                         oolv.ship_from_org_id
                               AND hl1.inactive_date > oohv.ordered_date),
                           NULL, (SELECT hl1.location_id
                                    FROM hr_locations hl1
                                   WHERE 1 = 1
                                     AND hl1.inventory_organization_id =
                                                         oolv.ship_from_org_id
                                     AND hl1.inactive_date IS NULL),
                           (SELECT hl1.location_id
                              FROM hr_locations hl1
                             WHERE 1 = 1
                               AND hl1.inventory_organization_id =
                                                         oolv.ship_from_org_id
                               AND hl1.inactive_date > oohv.ordered_date)
                          )
       --AND  oohv.ORDER_NUMBER= '4380206968'
GROUP BY        oohv.order_number,
                oohv.ordered_date,
                oolv.inventory_item_id,
                jl.order_line_id,
                oolv.line_id,
                oolv.payment_term_id,
                oolv.unit_selling_price,
                oohv.flow_status_code,
                ra.cust_account_id,
                hp.party_name,
                hp.party_id,
                oohv.cust_po_number,
                oolv.order_quantity_uom,
                oolv.ordered_quantity,
                oolv.ship_from_org_id,
                fu.user_name,
                jl.vat_invoice_no,
                oohv.transactional_curr_code,
                oohv.orig_sys_document_ref,
                oohv.invoice_to_org_id,
                jl.vat_invoice_date,
                oohv.ship_to_org_id,
                oohv.header_id,
                oohv.attribute2,
                ot.NAME,
                rsa.NAME,
                oohv.attribute10,
                oohv.attribute11,
                micv.category_set_name,
                micv.category_id,
                oohv.org_id,
                oolv.subinventory,
                hp.city,
                oolv.actual_shipment_date,
                mk.description,
                mk.concatenated_segments,
                hp.state,
                oohv.attribute14,
                oolv.unit_list_price,
                --        rt.name,
                oohv.conversion_rate,
                oolv.price_list_id,
                jihou.excise_duty_range,
                jihou.excise_duty_division,
                jihou.pan_no,
                jihou.excise_duty_reg_no,
                jihou.ec_code,
                jihou.excise_duty_comm,
                jihou.cst_reg_no,
                jihou.excise_duty_comm,
                jihou.vat_reg_no,
--         oolv.ORG_ID,
                hl.location_id,
                hl.ship_to_location_id,
                rtm.NAME,
                hl.address_line_1,
                hl.address_line_2,
                hl.address_line_3,
                hl.town_or_city,
                hl.postal_code,
                hl.telephone_number_1,
                jisptl.header_id,
                jisptl.line_id,
                oolv.line_number,
                rsh.ship_to_org_id,
                rsl.mmt_transaction_id,
                rt.transaction_id,
                rsl.requisition_line_id , mmt.transaction_id
--      jisptl.TAX_LINE_NO,
--       jt.tax_type
--       jo.TAX_LINE_NO,
--           jo.TAX_RATE,
--           jo.FUNC_TAX_AMOUNT
ORDER BY        oohv.order_number, oolv.line_number
--              jisptl.TAX_LINE_NO