QUERY FOR PAYMENT VOUCHED DETAILS

/* Formatted on 26-Apr-12 5:51:27 PM (QP5 v5.115.810.9015) */
SELECT   ACA.PAYMENT_TYPE_FLAG PAYMENT_TYPE,
         ACA.BANK_ACCOUNT_NAME BANK_ACCOUNT_NAME,
         ACA.CHECKRUN_NAME BATCH_NAME,
         ACA.BANK_ACCOUNT_NUM,
         FU.USER_NAME,
         ACA.CHECK_DATE GL_DATE,
         ACA.CHECK_ID,
         ACA.CHECK_ID CHECK_ID1,
         ACA.CHECK_NUMBER,
         ACA.VENDOR_ID,
         aca.vendor_site_id,
         NVL (
            (SELECT   aia.attribute2
               FROM   ap_invoice_payments_all aip, ap_invoices_all aia
              WHERE       aip.invoice_id = aia.invoice_id
                      AND aip.check_id = aca.check_id
                      AND ROWNUM = 1),
            HZP.PARTY_NAME
         )
            VENDOR_NAME,
         ACA.PARTY_ID,
         ACA.DOC_SEQUENCE_VALUE VOUCHER_NO,
         ACA.AMOUNT * NVL (ACA.EXCHANGE_RATE, 1) CREDIT_AMOUNT,
         GCC.SEGMENT2 COST_CENTER,
         GCC.SEGMENT3 || '.' || GCC.SEGMENT4 ACCOUNT_CODE,
         REPLACE (
            DECODE (GCC.SEGMENT4,
                    '00', A.DESCRIPTION,
                    A.DESCRIPTION || '.' || B.DESCRIPTION),
            'CLEARING A/C',
            NULL
         )
            DESRIPTION,
         NULL Status,
         1 Query
  FROM   AP_CHECKS_ALL ACA,
         FND_USER FU,
         CE_BANK_ACCT_USES_ALL CBAU,
         CE_BANK_ACCOUNTS CBA,
         GL_CODE_COMBINATIONS GCC,
         FND_FLEX_VALUES_VL A,
         FND_FLEX_VALUES_VL B,
         HZ_PARTIES HZP
 WHERE   ACA.ORG_ID = :P_ORG_ID AND HZP.PARTY_ID = ACA.PARTY_ID
         AND DECODE (ACA.CHECKRUN_NAME, '', 'FILIX', ACA.CHECKRUN_NAME) =
               NVL (
                  :P_BATCH_NAME,
                  DECODE (ACA.CHECKRUN_NAME, '', 'FILIX', ACA.CHECKRUN_NAME)
               )
         AND aca.doc_sequence_value BETWEEN NVL (:P_VOU_NUM,
                                                 aca.doc_sequence_value)
                                        AND  NVL (:P_VOU_TO,
                                                  aca.doc_sequence_value)
         /*AND DECODE(ACA.DOC_SEQUENCE_VALUE, '', '0', ACA.DOC_SEQUENCE_VALUE) >=
                      NVL(:P_VOU_NUM,
                          DECODE(ACA.DOC_SEQUENCE_VALUE, '', '0', ACA.DOC_SEQUENCE_VALUE))
                  AND DECODE(ACA.DOC_SEQUENCE_VALUE, '', '0', ACA.DOC_SEQUENCE_VALUE) <=
                      NVL(:P_VOU_TO,
                          DECODE(ACA.DOC_SEQUENCE_VALUE, '', '0', ACA.DOC_SEQUENCE_VALUE))*/
         AND ACA.CHECK_NUMBER >= NVL (:P_CHECK_NUMBER, ACA.CHECK_NUMBER)
         AND ACA.CHECK_NUMBER <= NVL (:P_CHECK_TO, ACA.CHECK_NUMBER)
         AND ACA.CE_BANK_ACCT_USE_ID =
               NVL (:P_BANK_ACCOUNT_ID, ACA.CE_BANK_ACCT_USE_ID)
         AND HZP.PARTY_NAME = NVL (:P_VENDOR_NAME, HZP.PARTY_NAME)
         AND FU.USER_ID = ACA.CREATED_BY
         AND CBAU.BANK_ACCT_USE_ID = ACA.CE_BANK_ACCT_USE_ID
         AND CBAU.BANK_ACCOUNT_ID = CBA.BANK_ACCOUNT_ID
         AND GCC.CODE_COMBINATION_ID = CBA.CASH_CLEARING_CCID
         AND A.FLEX_VALUE = GCC.SEGMENT3
         AND B.FLEX_VALUE = GCC.SEGMENT4
         AND B.PARENT_FLEX_VALUE_LOW = A.FLEX_VALUE
         AND aca.created_by = NVL (:p_created_by, aca.created_by)
UNION ALL
SELECT   ACA.PAYMENT_TYPE_FLAG PAYMENT_TYPE,
         ACA.BANK_ACCOUNT_NAME BANK_ACCOUNT_NAME,
         ACA.CHECKRUN_NAME BATCH_NAME,
         ACA.BANK_ACCOUNT_NUM,
         FU.USER_NAME,
         ACA.CHECK_DATE GL_DATE,
         ACA.CHECK_ID,
         ACA.CHECK_ID CHECK_ID1,
         ACA.CHECK_NUMBER,
         ACA.VENDOR_ID,
         aca.vendor_site_id,
         NVL (
            (SELECT   aia.attribute2
               FROM   ap_invoice_payments_all aip, ap_invoices_all aia
              WHERE       aip.invoice_id = aia.invoice_id
                      AND aip.check_id = aca.check_id
                      AND ROWNUM = 1),
            HZP.PARTY_NAME
         )
            VENDOR_NAME,
         ACA.PARTY_ID,
         ACA.DOC_SEQUENCE_VALUE VOUCHER_NO,
         ACA.AMOUNT * NVL (ACA.EXCHANGE_RATE, 1) * -1 CREDIT_AMOUNT,
         GCC.SEGMENT2 COST_CENTER,
         GCC.SEGMENT3 || '.' || GCC.SEGMENT4 ACCOUNT_CODE,
         REPLACE (
            DECODE (GCC.SEGMENT4,
                    '00', A.DESCRIPTION,
                    A.DESCRIPTION || '.' || B.DESCRIPTION),
            'CLEARING A/C',
            NULL
         )
            DESRIPTION,
         'VOIDED' status,
         2 Query
  FROM   AP_CHECKS_ALL ACA,
         FND_USER FU,
         CE_BANK_ACCT_USES_ALL CBAU,
         CE_BANK_ACCOUNTS CBA,
         GL_CODE_COMBINATIONS GCC,
         FND_FLEX_VALUES_VL A,
         FND_FLEX_VALUES_VL B,
         HZ_PARTIES HZP
 WHERE   ACA.ORG_ID = :P_ORG_ID AND HZP.PARTY_ID = ACA.PARTY_ID
         AND DECODE (ACA.CHECKRUN_NAME, '', 'FILIX', ACA.CHECKRUN_NAME) =
               NVL (
                  :P_BATCH_NAME,
                  DECODE (ACA.CHECKRUN_NAME, '', 'FILIX', ACA.CHECKRUN_NAME)
               )
         AND aca.doc_sequence_value BETWEEN NVL (:P_VOU_NUM,
                                                 aca.doc_sequence_value)
                                        AND  NVL (:P_VOU_TO,
                                                  aca.doc_sequence_value)
         /* AND DECODE(ACA.DOC_SEQUENCE_VALUE, '', '0', ACA.DOC_SEQUENCE_VALUE) >=
                      NVL(:P_VOU_NUM,
                          DECODE(ACA.DOC_SEQUENCE_VALUE, '', '0', ACA.DOC_SEQUENCE_VALUE))
                  AND DECODE(ACA.DOC_SEQUENCE_VALUE, '', '0', ACA.DOC_SEQUENCE_VALUE) <=
                      NVL(:P_VOU_TO,
                          DECODE(ACA.DOC_SEQUENCE_VALUE, '', '0', ACA.DOC_SEQUENCE_VALUE))*/
         AND ACA.CHECK_NUMBER >= NVL (:P_CHECK_NUMBER, ACA.CHECK_NUMBER)
         AND ACA.CHECK_NUMBER <= NVL (:P_CHECK_TO, ACA.CHECK_NUMBER)
         AND ACA.CE_BANK_ACCT_USE_ID =
               NVL (:P_BANK_ACCOUNT_ID, ACA.CE_BANK_ACCT_USE_ID)
         AND HZP.PARTY_NAME = NVL (:P_VENDOR_NAME, HZP.PARTY_NAME)
         AND FU.USER_ID = ACA.CREATED_BY
         AND CBAU.BANK_ACCT_USE_ID = ACA.CE_BANK_ACCT_USE_ID
         AND CBAU.BANK_ACCOUNT_ID = CBA.BANK_ACCOUNT_ID
         AND GCC.CODE_COMBINATION_ID = CBA.CASH_CLEARING_CCID
         AND A.FLEX_VALUE = GCC.SEGMENT3
         AND B.FLEX_VALUE = GCC.SEGMENT4
         AND B.PARENT_FLEX_VALUE_LOW = A.FLEX_VALUE
         AND ACA.status_lookup_code = 'VOIDED'
         AND aca.created_by = NVL (:p_created_by, aca.created_by)
ORDER BY   14, 20


query :2

 SELECT   ABA.BATCH_NAME INVOICE_BATCH,
           PV.VENDOR_NAME,
           AIA.INVOICE_NUM,
           AIPA.AMOUNT,
           AIA.INVOICE_ID,
           AIA.ORG_ID,
           AIPA.CHECK_ID,
           ROWNUM S_NO_INV
    FROM   AP_INVOICES_ALL AIA,
           PO_VENDORS PV,
           AP_BATCHES_ALL ABA,
           AP_INVOICE_PAYMENTS_ALL AIPA
   WHERE       ABA.BATCH_ID = AIA.BATCH_ID
           AND AIA.VENDOR_ID = PV.VENDOR_ID
           AND AIA.INVOICE_ID = AIPA.INVOICE_ID
           AND AIA.ORG_ID = AIPA.ORG_ID
           AND AIA.ORG_ID = :P_ORG_ID
--&P_DYAMIC_QUERY
ORDER BY   PV.VENDOR_NAME
              

QUERY TO FIND THE ISO ORDER AGAINST SHIP DISCREPANCY QTY

SELECT   (SELECT   nvl(SUM (quantity),0)
            FROM   rcv_shipment_headers rsh,
                   rcv_shipment_lines rsl,
                   rcv_transactions rt
           WHERE       rt.shipment_header_id = rsh.shipment_header_id
                   AND rsh.shipment_header_id = rsl.shipment_header_id
                   AND rt.shipment_line_id = rsl.shipment_line_id
                   AND rsh.shipment_num = :shipment_num
                   AND rt.transaction_type = 'RECEIVE')
            rcv_qty,                           /* rcv transaction interface */
         (SELECT   nvl(SUM (quantity),0)
            FROM   rcv_transactions_interface rt, rcv_shipment_headers rsh
           WHERE   rsh.shipment_header_id = rt.shipment_header_id
                   AND rsh.shipment_num = :shipment_num)
            interface_qty,
         (SELECT   NVL (SUM (SHIPPED_QUANTITY), 0)
            FROM   wsh_delivery_details wdd, wsh_delivery_assignments wda
           WHERE       wdd.delivery_detail_id = wda.delivery_detail_id
                   AND wdd.released_status = 'C'
                   AND wdd.INV_INTERFACED_FLAG = 'Y'
                   AND to_char(wda.delivery_id) =:shipment_num)
            order_qty
  FROM   DUAL

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(+)

ITEM CATEGORY QUERY

select a.segment1 ITEM_ID,
       a.INVENTORY_ITEM_STATUS_CODE ,
     --  a.attribute20 NPC,
       b.CONCATENATED_SEGMENTS CATEGORY
  from mtl_system_items_b a,
       mtl_categories_kfv b,
       mtl_item_categories c
       where c.inventory_item_id = a.inventory_item_id
       and c.category_id = b.category_id
      -- and a.attribute20 = 'GNU'
      and a.organization_id =c.organization_id
     and a.organization_id = 86
     and b.CONCATENATED_SEGMENTS like '%PP%'
    and a.INVENTORY_ITEM_STATUS_CODE = 'Not 4 Sale'

RECEIPT FOR THE PURCHASE ORDER AND INVENTORY

SELECT   rsh.receipt_num,
           SUM (rt.quantity) qty,
           rt.transaction_type,
           poh.segment1 po_number,
           mmt.LOCATOR_ID,
           mmt.subinventory_code
    FROM   rcv_transactions rt,
           rcv_shipment_lines rsl,
           rcv_shipment_headers rsh,
           po_headers_all poh,
           mtl_material_transactions mmt
   WHERE       transaction_type = 'DELIVER'
           AND rt.shipment_header_id = rsh.shipment_header_id
           AND rt.shipment_line_id = rsl.shipment_line_id
           AND rsh.shipment_header_id = rsl.shipment_header_id
           AND rsl.item_id = 17099
           AND rt.PO_HEADER_ID = poh.PO_HEADER_ID
           AND mmt.RCV_TRANSACTION_ID = rt.transaction_id
GROUP BY   rsh.receipt_num,
           poh.segment1,
           rt.transaction_type,
           mmt.LOCATOR_ID,
           mmt.subinventory_code

TO RESET PASSWORD FOR THE USER IN ORACLE APPLICATIONS

/* Formatted on 2012/07/14 12:43 (Formatter Plus v4.8.8) */
DECLARE
   v_user_name      VARCHAR2 (30);
   v_new_password   VARCHAR2 (30) := 'welcome1';
   v_status         BOOLEAN;

   CURSOR c1
   IS
      SELECT user_name
        FROM fnd_user
       WHERE user_name BETWEEN 'PMGNU021' AND 'PMGNU099';
BEGIN
   FOR i IN c1
   LOOP
      DBMS_OUTPUT.put_line ('enrtry');
      v_user_name := i.user_name;
      v_status :=
         fnd_user_pkg.changepassword (username         => v_user_name,
                                      newpassword      => v_new_password
                                     );
      v_user_name := NULL;

      IF v_status = TRUE
      THEN
         DBMS_OUTPUT.put_line
                          (   'The password reset successfully for the User:'
                           || i.user_name
                          );
         COMMIT;
      ELSE
         DBMS_OUTPUT.put_line (   'Unable to reset password due to'
                               || SQLCODE
                               || ' '
                               || SUBSTR (SQLERRM, 1, 100)
                              );
         ROLLBACK;
      END IF;
   END LOOP;
END;

CREATING THE INDEX

CREATE INDEX APPS.XXHMI_MTL_MAT_TRANS_N4 ON INV.MTL_MATERIAL_TRANSACTIONS
(transaction_type_id ,TRUNC (TRANSACTION_DATE), INVENTORY_ITEM_ID )
LOGGING
TABLESPACE APPS_TS_TX_DATA
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;