QUERY FOR STOCK TRANSFER

/* Formatted on 6/30/2012 3:38:35 PM (QP5 v5.115.810.9015) */
--SELECT   a.*, operand current_rate
--  FROM   (

CREATE TABLE xxhmi_stock_txn
AS
   (SELECT   hou.NAME,
             (SELECT   org.organization_name
                FROM   org_organization_definitions org
               WHERE   jowl.organization_id = org.organization_id)
                inventory_org,
             hp.party_name dealer_name,
             hca.account_number dealer_number,
             (   hl_bill.address1
              || ','
              || hl_bill.address2
              || ','
              || hl_bill.address3
              || ','
              || hl_bill.address4)
                dealer_address,
             hl_bill.city dealer_city,
             hl_bill.state dealer_state,
             ft_bill.nls_territory dealer_country,
             otl.NAME sales_order_type,
             oh.order_number sales_order_number,
             ol.line_number sales_order_line_no,
             oh.ordered_date sales_order_date,
             ' ' ar_invoice_number,
             ' ' ar_invoice_date,
             (SELECT   MAX (jis.item_tariff)
                FROM   jai_inv_itm_setups jis
               WHERE   jis.inventory_item_id = ol.inventory_item_id
                       AND jis.organization_id = ol.ship_from_org_id)
                excise_tariff_no,
             ' ' excise_invoice_num,
             wnd.attribute2 challan_no,
             jowl.EXCISE_INVOICE_DATE excise_invoice_date,
             jowl.vat_invoice_no,
             wnd.attribute6 sale_invoice_no,
             msi.segment1 part_no,
             msi.description descripition_of_part,
             jowl.quantity qty,
             0 current_mrp_price,
             (SELECT   NVL (SUM (jowst.func_tax_amount), 0)
                FROM   jai_om_wsh_line_taxes jowst, jai_cmn_taxes_all jct
               WHERE       jowst.tax_id = jct.tax_id
                       AND jct.tax_type = 'Excise'
                       AND delivery_detail_id = jowl.delivery_detail_id)
                excise,
             (SELECT   NVL (SUM (jowst.func_tax_amount), 0)
                FROM   jai_om_wsh_line_taxes jowst, jai_cmn_taxes_all jct
               WHERE       jowst.tax_id = jct.tax_id
                       AND jct.tax_type = 'EXCISE_EDUCATION_CESS'
                       AND delivery_detail_id = jowl.delivery_detail_id)
                ed_cess,
             (SELECT   NVL (SUM (jowst.func_tax_amount), 0)
                FROM   jai_om_wsh_line_taxes jowst, jai_cmn_taxes_all jct
               WHERE       jowst.tax_id = jct.tax_id
                       AND jct.tax_type = 'EXCISE_SH_EDU_CESS'
                       AND delivery_detail_id = jowl.delivery_detail_id)
                she_cess,
             (SELECT   NVL (SUM (jowst.func_tax_amount), 0)
                FROM   jai_om_wsh_line_taxes jowst, jai_cmn_taxes_all jct
               WHERE       jowst.tax_id = jct.tax_id
                       AND jct.tax_type IN ('Addl. Excise', 'CVD')
                       AND delivery_detail_id = jowl.delivery_detail_id)
                addl_excise_cvd,
             (SELECT   NVL (SUM (jowst.func_tax_amount), 0)
                FROM   jai_om_wsh_line_taxes jowst, jai_cmn_taxes_all jct
               WHERE       jowst.tax_id = jct.tax_id
                       AND jct.tax_type = ('CST')
                       AND delivery_detail_id = jowl.delivery_detail_id)
                cst,
             (SELECT   NVL (SUM (jowst.func_tax_amount), 0)
                FROM   jai_om_wsh_line_taxes jowst, jai_cmn_taxes_all jct
               WHERE       jowst.tax_id = jct.tax_id
                       AND jct.tax_type = ('VALUE ADDED TAX')
                       AND delivery_detail_id = jowl.delivery_detail_id)
                vat,
             (jowl.quantity * ol.unit_list_price) base_value,
             oh.transactional_curr_code,
             ' ' exchange_rate,
             wnd.NAME delivery_no,
             wnd.ultimate_dropoff_date transporter_ship_to_date,
             ol.attribute10 dealer_po_number,
             ol.attribute11 dealer_po_line_number,
             (SELECT   booking_number
                FROM   wsh_delivery_legs
               ---WSH_BOLS_DB_V --- ADDED
               WHERE   delivery_id = jowl.delivery_id)
                booking_number,
             (SELECT   service_contract
                FROM   wsh_delivery_legs
               ---WSH_BOLS_DB_V --- ADDED
               WHERE   delivery_id = jowl.delivery_id)
                service_contract,
             (SELECT   aetc_number
                FROM   wsh_delivery_legs
               ---WSH_BOLS_DB_V --- ADDED
               WHERE   delivery_id = jowl.delivery_id)
                aetc_number,
             (SELECT   wc.freight_code
                FROM   wsh_carriers wc
               WHERE   wnd.carrier_id = wc.carrier_id)
                transporter_name,
             (SELECT   ROUND (item_cost, 2)
                FROM   cst_item_costs
               WHERE       inventory_item_id = ol.inventory_item_id
                       AND organization_id = ol.ship_from_org_id
                       AND cost_type_id = 2)
                average_cost_per_unit,
             ' ' average_trn_value,
             (SELECT   mck1.concatenated_segments
                FROM   mtl_item_categories mic1,
                       mtl_categories_b mcb1,
                       mtl_categories_kfv mck1
               WHERE       mic1.category_set_id = 1100000044
                       AND mic1.category_id = mcb1.category_id
                       AND mcb1.structure_id = 50351
                       AND mcb1.structure_id = mck1.structure_id
                       AND mic1.category_id = mck1.category_id
                       AND ol.inventory_item_id = mic1.inventory_item_id
                       AND ol.ship_from_org_id = mic1.organization_id)
                basic_set,
             (SELECT   msit.long_description
                FROM   mtl_system_items_tl msit
               WHERE   msit.inventory_item_id = ol.inventory_item_id
                       AND msit.organization_id = ol.ship_from_org_id)
                model_code,
             SUBSTR (hca.customer_class_code, 1, 2) customer_classification,
             oh.creation_date order_upload_date,
             (SELECT   TRUNC (xwoa1.po_creation_date)
                FROM   xxhmi_wave_order_all xwoa1
               WHERE       hca.cust_account_id = xwoa1.customer_id
                       AND ol.inventory_item_id = xwoa1.inventory_item_id
                       AND ROWNUM = 1)
                dlr_po_date,
             (SELECT   mck.concatenated_segments
                FROM   mtl_categories_kfv mck,
                       mtl_item_categories mic,
                       mtl_categories_b mcb
               WHERE       mcb.structure_id = mck.structure_id              --
                       AND mic.category_id = mck.category_id                --
                       AND ol.inventory_item_id = mic.inventory_item_id
                       AND ROWNUM = 1)
                inventory_set,
             'FOC' inv_type,
             ( ( (SELECT   NVL (SUM (jowst.func_tax_amount), 0)
                    FROM   jai_om_wsh_line_taxes jowst, jai_cmn_taxes_all jct
                   WHERE   jowst.tax_id = jct.tax_id
                           AND jct.tax_type NOT IN
                                    ('EXCISE_EDUCATION_CESS',
                                     'EXCISE_SH_EDU_CESS',
                                     'Excise',
                                     'VALUE ADDED TAX',
                                     'CST',
                                     'Freight',
                                     'Insurance')
                           AND delivery_detail_id = jowl.delivery_detail_id)
                + DECODE (
                     oh.transactional_curr_code,
                     'INR',
                     ( (SELECT   ABS (NVL (SUM (o.adjusted_amount), 0))
                          FROM   oe_price_adjustments o
                         --OE_PRICE_ADJUSTMENTS_V o
                         WHERE       header_id = jowl.order_header_id
                                 AND o.line_id = jowl.order_line_id
                                 AND o.list_line_type_code = 'SUR')
                      * jowl.quantity),
                     ( (SELECT   ABS (NVL (SUM (o.adjusted_amount), 0))
                          FROM   oe_price_adjustments o
                         --OE_PRICE_ADJUSTMENTS_V o
                         WHERE       header_id = jowl.order_header_id
                                 AND o.line_id = jowl.order_line_id
                                 AND o.list_line_type_code = 'SUR')
                      * jowl.quantity)
                     * NVL (
                          (SELECT   conversion_rate
                             FROM   gl_daily_rates
                            WHERE   from_currency =
                                       oh.transactional_curr_code
                                    AND to_currency = 'INR'
                                    AND conversion_type =
                                          oh.conversion_type_code
                                    AND conversion_date =
                                          TO_CHAR (wnd.ultimate_dropoff_date,
                                                   'DD-Mon-YYYY')),
                          0
                       )
                  )))
                others_tax,
             ol.inventory_item_id,
             oh.ORDERED_DATE,
             jowl.ASSESSABLE_VALUE mrp_per_unit
      FROM   oe_order_headers_all oh,
             oe_transaction_types_tl otl,
             oe_order_lines_all ol,
             jai_om_wsh_lines_all jowl,
             wsh_new_deliveries wnd,
             hz_cust_accounts hca,
             hz_parties hp,
             hz_party_sites hps,
             hz_cust_acct_sites_all hcas_bill,
             hz_cust_site_uses_all hcsu_bill,
             hz_locations hl_bill,
             fnd_territories ft_bill,
             hr_operating_units hou,
             mtl_system_items_b msi
     WHERE       oh.header_id = ol.header_id
             AND hou.organization_id = oh.org_id
             AND oh.org_id = ol.org_id
             AND oh.order_source_id = 10
--             AND otl.transaction_type_id IN
--                      (1149, 1068, 1249, 1169, 1018, 1192, 1098, 1083, 1054)
             AND otl.transaction_type_id = oh.order_type_id
             AND msi.inventory_item_id = ol.inventory_item_id
             AND msi.organization_id = ol.ship_from_org_id
             AND wnd.delivery_id = jowl.delivery_id
             AND oh.header_id = jowl.order_header_id
             AND jowl.order_line_id = ol.line_id
             AND jowl.order_header_id = ol.header_id
             AND hp.party_id = hca.party_id
             AND hps.party_id = hp.party_id
             AND hcas_bill.cust_account_id = hca.cust_account_id
             AND hcsu_bill.cust_acct_site_id = hcas_bill.cust_acct_site_id
             AND hcsu_bill.site_use_id = oh.invoice_to_org_id
             AND hcsu_bill.site_use_code = 'BILL_TO'
             AND hl_bill.location_id = hps.location_id
             AND hcas_bill.party_site_id = hps.party_site_id
             AND hcsu_bill.org_id = ol.org_id
             AND hca.cust_account_id = ol.sold_to_org_id
             AND hl_bill.country = ft_bill.territory_code
--                      AND oh.order_number = '1210006057' --                   AND msi.segment1 = '83510KYJ900'
             AND jowl.organization_id = 81)
             AND TRUNC (jowl.EXCISE_INVOICE_DATE) BETWEEN '01-APR-2012'
                                                      AND  '27-JUN-2012')



--
/*                                                  ) a,
         (SELECT   qpa.product_attr_value,
                   NVL (qll.operand, 0) operand,
                   start_date_active,
                   end_date_active
            FROM   qp_list_lines qll, qp_pricing_attributes qpa
           WHERE   qll.list_line_id = qpa.list_line_id
                   AND qll.list_header_id = 11015) qpl
 WHERE       qpl.product_attr_value(+) = a.inventory_item_id
         AND a.ORDERED_DATE>= qpl.start_date_active(+)
         AND a.ORDERED_DATE <= qpl.end_date_active(+)*/










--                         AND ( (p_organization_id IS NOT NULL
--                                AND jowl.organization_id = p_organization_id)
--                              OR p_organization_id IS NULL)
--                         AND ( (p_org_id IS NOT NULL
--                                AND hou.organization_id = p_org_id)
--                              OR p_org_id IS NULL)
--                         AND ( (p_part_no IS NOT NULL
--                                AND msi.segment1 = p_part_no)
--                              OR p_part_no IS NULL)
--                         AND ( (p_dealer_code IS NOT NULL
--                                AND hca.account_number = p_dealer_code)
--                              OR p_dealer_code IS NULL)
--                         AND ( (p_business_line IS NOT NULL
--                                AND SUBSTR (hca.customer_class_code, 1, 2) =
--                                      p_business_line)
--                              OR p_business_line IS NULL)
--                         AND TRUNC (wnd.confirm_date) BETWEEN :p_from_date
--                                                          AND  :p_to_date
--                                                          ) y;







select a.*, operand  from xxhmi_stock_txn a,(SELECT   qpa.product_attr_value,
                   NVL (qll.operand, 0) operand,
                   start_date_active,
                   end_date_active
            FROM   qp_list_lines qll, qp_pricing_attributes qpa
           WHERE   qll.list_line_id = qpa.list_line_id
                   AND qll.list_header_id = 11015) qpl
 WHERE       qpl.product_attr_value(+) = a.inventory_item_id
         AND a.EXCISE_INVOICE_DATE>= qpl.start_date_active(+)
         AND a.EXCISE_INVOICE_DATE<= qpl.end_date_active(+)