We help IT Professionals succeed at work.

following packaged procedure takes lot of time

pardeshirahul
pardeshirahul asked
on
is there a way i can tune it registration.docx

this is the main packaged procedure called
 PROCEDURE tpco_get_pur_usage_all_org (errbuff          OUT VARCHAR2,
                                         retcode          OUT VARCHAR2,
                                         p_global      IN     VARCHAR2,
                                         p_org_id      IN     NUMBER,
                                         p_date        IN     VARCHAR2,
                                         p_item_type   IN     VARCHAR2)
Comment
Watch Question

Author

Commented:
need to tune the code

Author

Commented:
xxx
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
We would need to see the code to make suggestion on where to tweak it.

If you are asking about how to go about tuning a procedure, I typically add debugging statements to the procedure at various areas to track what parts take the most time then I focus on those areas.

Author

Commented:
package

Author

Commented:
/* Formatted on 11/29/2011 11:41:27 AM (QP5 v5.149.1003.31008) */
CREATE OR REPLACE PACKAGE BODY BOLINF.tpco_supp_release_stat
AS
   /******************************************************************************
      NAME:       TPCO_SUPP_RELEASE_STAT
      PURPOSE:

      REVISIONS:
      Ver        Date        Author           Description
      ---------  ----------  ---------------  ------------------------------------
      1.0        6/20/2008             1. Poornima Hegde
      1.3        8/1/2008                         Poornima Hegde -Changes to fit in additional requirements by Keith
      1.4        8/20/2008        Poornima Hegde           Added few additional fields
      1.5       2/23/2009          Poornima Hegde   Removed IS/IR from previous years
                 26/1/2010          Poornima Hegde    Safety stock issue fixed
      1.6                         Poornima Hegde   All item types added other than the inactive
                9/27/10           Poornima Hegde     Us past transaction for the org only keith mond change
   ******************************************************************************/
   /*Assumption while coding: This code brings the item and supplier in formation for the open orders based on the input date parmeter.
   Input date parameter is compared against the need by date of POs.
   The date range for  the open orders info is from 6 months from the begining Monday of the input date parameter
   */
   PROCEDURE tpco_get_pur_dtl_all_org (errbuff       OUT VARCHAR2,
                                       retcode       OUT VARCHAR2,
                                       p_global   IN     VARCHAR2,
                                       p_org_id   IN     NUMBER,
                                       p_date     IN     VARCHAR2)
   IS
      CURSOR c1
      IS
         SELECT organization_id FROM apps.org_organization_definitions;

      l_begin_weekdate   DATE;
      l_end_date         DATE;
      l_date             DATE;
      l_from_ord_date    DATE;
      l_end_ord_date     DATE;
      l_shipped_sales    NUMBER;
   BEGIN
      fnd_file.put_line (fnd_file.LOG, 'TPCO Supplier Release Info');

      IF p_global = 'Y'
      THEN
         FOR rec1 IN c1
         LOOP
            --   tpco_get_purchase_dtl (rec1.organization_id, p_date);
            tpco_get_purchase_itm_dtl (rec1.organization_id, p_date);
         END LOOP;
      ELSE
         fnd_file.put_line (fnd_file.LOG, '1');
         -- tpco_get_purchase_dtl (p_org_id, p_date);
         tpco_get_purchase_itm_dtl (p_org_id, p_date);
      END IF;
   EXCEPTION
      WHEN OTHERS
      THEN
         retcode := 2;
         fnd_file.put_line (fnd_file.LOG, 'Error:      ' || SQLERRM);
   END;

   FUNCTION tpco_get_open_po_ord_qty (p_org_id           IN NUMBER,
                                      p_item_id          IN NUMBER,
                                      p_vendor_id        IN NUMBER,
                                      p_vendor_site_id   IN NUMBER,
                                      p_req_from_date    IN DATE,
                                      p_req_to_date      IN DATE)
      RETURN NUMBER
   IS
      l_ord_qty   NUMBER := 0;
   BEGIN
      l_ord_qty := 0;

      SELECT NVL (
                SUM (
                     plla.quantity
                   - NVL (plla.quantity_cancelled, 0)
                   - NVL (plla.quantity_received, 0)),
                0)
                qty_ordered
        INTO l_ord_qty
        FROM po_line_locations_all plla, po_lines_all pl, po_headers_all poh
       WHERE     plla.po_line_id = pl.po_line_id
             AND NVL (poh.closed_code, 'OPEN') = 'OPEN'
             AND NVL (pl.closed_code, 'OPEN') = 'OPEN'
             AND NVL (plla.closed_code, 'OPEN') = 'OPEN'
             AND poh.org_id = plla.org_id
             AND poh.org_id = plla.org_id
             AND poh.po_header_id = pl.po_header_id
             AND plla.ship_to_organization_id = p_org_id
             AND poh.vendor_id = p_vendor_id
             AND poh.vendor_site_id = p_vendor_site_id
             AND pl.item_id = p_item_id
             AND (poh.cancel_flag = 'N' OR poh.cancel_flag IS NULL)
             AND (pl.cancel_flag = 'N' OR pl.cancel_flag IS NULL)
             AND TRUNC (NVL (plla.need_by_date, plla.promised_date)) BETWEEN TRUNC (
                                                                                TO_DATE (
                                                                                   p_req_from_date))
                                                                         AND TRUNC (
                                                                                TO_DATE (
                                                                                   p_req_to_date))
             AND plla.quantity > NVL (plla.quantity_received, 0);

      --    AND poh.type_lookup_code <> 'BLANKET';
      --  fnd_file.put_line (fnd_file.LOG, 'l_ord_qty  ' || l_ord_qty);
      RETURN l_ord_qty;
   EXCEPTION
      WHEN OTHERS
      THEN
         l_ord_qty := 0;
   END;

   PROCEDURE tpco_get_purchase_itm_dtl (p_org_id   IN NUMBER,
                                        p_date     IN VARCHAR2)
   IS
      l_begin_weekdate           DATE;
      l_end_date                 DATE;
      l_date                     DATE;
      l_from_ord_date            DATE;
      l_end_ord_date             DATE;
      l_shipped_sales            NUMBER;
      l_6mth_date                DATE;
      l_org_name                 apps.org_organization_definitions.organization_name%TYPE;
      l_intransit_qty            NUMBER;
      l_ohand_qty                NUMBER;
      l_total_qty                NUMBER;
      l_vendor_id                po_vendors.vendor_id%TYPE;
      l_vendor_site_id           po_vendor_sites_all.vendor_site_id%TYPE;
      l_vendor_name              po_vendors.vendor_name%TYPE;
      l_vendor_site              po_vendor_sites_all.vendor_site_code%TYPE;
      l_city                     po_vendor_sites_all.city%TYPE;
      l_state                    po_vendor_sites_all.state%TYPE;
      l_country                  po_vendor_sites_all.country%TYPE;
      l_pur_price                NUMBER;
      l_currency                 po_headers_all.currency_code%TYPE;
      l_uom                      mtl_system_items_b.primary_unit_of_measure%TYPE;
      l_qty_recvd                NUMBER;
      l_qty_recvd_last_yr        NUMBER;
      l_ltst_trans_date          VARCHAR2 (50);
      l_wk1_qty                  NUMBER;
      l_wk2_qty                  NUMBER;
      l_wk3_qty                  NUMBER;
      l_wk4_qty                  NUMBER;
      l_wk5_qty                  NUMBER;
      l_wk6_qty                  NUMBER;
      l_wk7_qty                  NUMBER;
      l_wk8_qty                  NUMBER;
      l_mth_begin_date           DATE;
      l_mth1_qty                 NUMBER;
      l_mth2_qty                 NUMBER;
      l_mth3_qty                 NUMBER;
      l_ord_qty_bf               NUMBER;
      l_safety_qty               NUMBER;
      l_minimum_order_quantity   mtl_system_items_b.minimum_order_quantity%TYPE;
      l_maximum_order_quantity   mtl_system_items_b.maximum_order_quantity%TYPE;
      l_unit_weight              mtl_system_items_b.unit_weight%TYPE;
      l_fixed_lead_time          mtl_system_items_b.fixed_lead_time%TYPE;
      l_variable_lead_time       mtl_system_items_b.variable_lead_time%TYPE;
      l_fixed_lot_multiplier     mtl_system_items_b.fixed_lot_multiplier%TYPE;
      l_qty_recvd_last_yr2       NUMBER;
      l_type_flag                VARCHAR2 (2);
      l_dest_org_id              NUMBER;
      lcnt                       NUMBER;
      l_last_vendor              po_vendors.vendor_name%TYPE;
      l_total_openorders         NUMBER;
      l_cost                     NUMBER;
      l_lst_vendor_site_code     VARCHAR2 (50);
      l_lst_vendor_state         VARCHAR2 (50);
      l_lst_vendor_country       VARCHAR2 (50);
      l_last_qty                 NUMBER;

      CURSOR c_purchased_item_c
      IS
           SELECT msi.segment1 item_num,
                  minimum_order_quantity,
                  maximum_order_quantity,
                  unit_weight,
                  fixed_lead_time,
                  variable_lead_time,
                  fixed_lot_multiplier,
                  full_lead_time full_lead_time,
                  primary_unit_of_measure,
                  msi.inventory_item_id item_id,
                  msi.item_type item_type,
                  REPLACE (msi.description, CHR (9), ' ') description,
                  msi.planner_code
             FROM mtl_system_items_b msi
            WHERE (msi.planning_make_buy_code = 2
                   OR msi.purchasing_item_flag = 'Y')
                  --  WHERE msi.item_type = 'P'
                  --AND MSI.INVENTORY_ITEM_ID IN (71142,75725)
                  --     AND msi.inventory_item_status_code = 'Active'
                  AND msi.organization_id = p_org_id
         -- AND msi.segment1 = '10118-C5'
         ORDER BY msi.segment1;

      CURSOR c_po_price (
         p_vendor_id        IN NUMBER,
         p_vendor_site_id   IN NUMBER,
         p_item_id          IN NUMBER,
         p_per_begin_date   IN DATE,
         p_per_end_date     IN DATE)
      IS
           SELECT poh.currency_code, plla.price_override
             FROM po_line_locations_all plla,
                  po_lines_all pl,
                  po_headers_all poh
            WHERE     plla.po_line_id = pl.po_line_id
                  AND poh.org_id = plla.org_id
                  AND poh.org_id = plla.org_id
                  AND poh.po_header_id = pl.po_header_id
                  AND plla.ship_to_organization_id = p_org_id
                  AND poh.vendor_id = p_vendor_id
                  AND poh.vendor_site_id = p_vendor_site_id
                  AND pl.item_id = p_item_id
                  AND (poh.cancel_flag = 'N' OR poh.cancel_flag IS NULL)
                  AND (pl.cancel_flag = 'N' OR pl.cancel_flag IS NULL)
                  AND (plla.quantity - NVL (plla.quantity_cancelled, 0)) > 0
                  AND TRUNC (NVL (plla.need_by_date, plla.promised_date)) BETWEEN TRUNC (
                                                                                     p_per_begin_date)
                                                                              AND TRUNC (
                                                                                     p_per_end_date)
         --   AND poh.type_lookup_code <> 'BLANKET'
         ORDER BY plla.creation_date;

      CURSOR c_ir_price (
         p_destination_org_id   IN NUMBER,
         p_source_org_id        IN NUMBER,
         p_item_id              IN NUMBER,
         p_per_begin_date       IN DATE,
         p_per_end_date         IN DATE)
      IS
           SELECT prl.base_unit_price, prl.currency_code currency_code
             FROM po_requisition_headers_all prh, po_requisition_lines_all prl
            WHERE     prh.org_id = prl.org_id
                  AND prh.requisition_header_id = prl.requisition_header_id
                  AND prl.item_id = p_item_id
                  --AND b.source_type_code = 'INVENTORY'
                  AND prh.type_lookup_code = 'INTERNAL'
                  AND prl.destination_organization_id = p_destination_org_id
                  AND prl.source_organization_id =
                         NVL (l_vendor_id, prl.source_organization_id)
                  AND TRUNC (prl.need_by_date) BETWEEN TRUNC (p_per_begin_date)
                                                   AND TRUNC (p_per_end_date)
                  AND (prl.cancel_flag = 'N' OR prl.cancel_flag IS NULL)
         ORDER BY prl.creation_date;
   BEGIN
      fnd_file.new_line (fnd_file.LOG, 2);
      fnd_file.
       put_line (
         fnd_file.LOG,
         '**********************************************************************');
      fnd_file.put_line (fnd_file.LOG, 'TPCO SUPPLIER RELEASE INFORMATION');
      fnd_file.
       put_line (
         fnd_file.LOG,
         'ORG_ID:    ' || p_org_id || '        Date:        ' || p_date);
      l_date := TO_DATE (p_date, 'DD-MM-YYYY');

      BEGIN
         --The below query gets Monday of the week they enter.week1 always starts from Monday and we add 6 days to it to get the week end date
         SELECT TO_DATE (
                   DECODE (
                      RTRIM (TO_CHAR (l_date, 'DAY')),
                      'THURSDAY', TO_CHAR ( (l_date - 3), 'DD-MM-YYYY'),
                      'FRIDAY', TO_CHAR ( (l_date - 4), 'DD-MM-YYYY'),
                      'SATURDAY', TO_CHAR ( (l_date - 5), 'DD-MM-YYYY'),
                      'SUNDAY', TO_CHAR ( (l_date - 6), 'DD-MM-YYYY'),
                      'MONDAY', TO_CHAR ( (l_date - 0), 'DD-MM-YYYY'),
                      'TUESDAY', TO_CHAR ( (l_date - 1), 'DD-MM-YYYY'),
                      'WEDNESDAY', TO_CHAR ( (l_date - 4), 'DD-MM-YYYY')),
                   'DD-MM-YYYY')
           INTO l_begin_weekdate
           FROM DUAL;
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            fnd_file.put_line (fnd_file.LOG, 'error1   ' || SQLERRM);
            l_begin_weekdate := SYSDATE;
            NULL;
         WHEN OTHERS
         THEN
            fnd_file.put_line (fnd_file.LOG, 'error12 ' || SQLERRM);
            l_begin_weekdate := SYSDATE;
            NULL;
      END;

      fnd_file.
       put_line (fnd_file.LOG, 'l_begin_weekdate     ' || l_begin_weekdate);
      l_end_date := l_begin_weekdate + 6;
      l_6mth_date := l_begin_weekdate + 146;
      fnd_file.put_line (fnd_file.LOG, 'l_6mth_date     ' || l_6mth_date);

      BEGIN
         l_org_name := NULL;

         SELECT organization_code
           INTO l_org_name
           FROM apps.org_organization_definitions
          WHERE organization_id = p_org_id;
      EXCEPTION
         WHEN OTHERS
         THEN
            l_org_name := NULL;
      END;

      fnd_file.
       put_line (
         fnd_file.output,
            'Organization:    '
         || l_org_name
         || '        Date:        '
         || p_date);
      fnd_file.
       put_line (
         fnd_file.output,
            'Supplier Site'
         || '|'
         || 'Supplier'
         || '|'
         || 'City'
         || '|'
         || 'State'
         || '|'
         || 'Country'
         || '|'
         || 'Oracle Part Number'
         || '|'
         || 'Item Type'
         || '|'
         || 'Desc'
         || '|'
         || 'Planner Code'
         || '|'
         || 'Standard Cost'
         || '|'
         || 'Purchase Price'
         || '|'
         || 'Currency'
         || '|'
         || 'Unit OF Measure'
         || '|'
         || 'Safety Stock'
         || '|'
         || 'Standard Pack'
         || '|'
         || 'MIN Qty'
         || '|'
         || 'MAX Qty'
         || '|'
         || 'Full Lead Time'
         || '|'
         || 'IN-transit Inventory'
         || '|'
         || 'ON Hand Inventory'
         || '|'
         || 'Total Inventory'
         || '|'
         || ' Total Open Orders'
         || '|'
         || 'Past Due Orders'
         || '|'
         || 'WK1'
         || '|'
         || 'WK2'
         || '|'
         || 'WK3'
         || '|'
         || 'WK4'
         || '|'
         || 'WK5'
         || '|'
         || 'WK6'
         || '|'
         || 'WK7'
         || '|'
         || 'WK8'
         || '|'
         || 'MONTH 3'
         || '|'
         || 'MONTH 4'
         || '|'
         || 'MONTH 5'
         || '|'
         || 'Last Received Date'
         || '|'
         || 'Last Received from Vendor'
         || '|'
         || 'Last Received Quantity'
         || '|'
         || 'YTD Received'
         || '|'
         || TO_CHAR (TO_NUMBER (TO_CHAR (l_date, 'YYYY')) - 1)
         || ' Received'
         || '|'
         || TO_CHAR (TO_NUMBER (TO_CHAR (l_date, 'YYYY')) - 2)
         || ' Received');

      BEGIN
         l_wk1_qty := 0;
         l_wk2_qty := 0;
         l_wk3_qty := 0;
         l_wk4_qty := 0;
         l_wk5_qty := 0;
         l_wk6_qty := 0;
         l_wk7_qty := 0;
         l_wk8_qty := 0;
         l_mth1_qty := 0;
         l_mth2_qty := 0;
         l_mth3_qty := 0;
         l_ord_qty_bf := 0;
         l_vendor_name := NULL;
         l_vendor_site := NULL;
         l_city := NULL;
         l_state := NULL;
         l_country := NULL;
         l_pur_price := NULL;
         l_currency := NULL;
         l_total_openorders := 0;
         l_cost := 0;
         l_lst_vendor_site_code := NULL;
         l_lst_vendor_state := NULL;
         l_lst_vendor_country := NULL;
         l_last_qty := NULL;

         INSERT INTO tpco_supp_rel_global_temp (vendor_id,
                                                vendor_site_id,
                                                item_id,
                                                org_id,
                                                type_flag)
              SELECT poh.vendor_id,
                     poh.vendor_site_id,
                     pl.item_id,
                     p_org_id,
                     'ES'
                FROM po_line_locations_all plla,
                     po_lines_all pl,
                     po_headers_all poh
               WHERE     plla.po_line_id = pl.po_line_id
                     AND poh.org_id = plla.org_id
                     AND poh.org_id = plla.org_id
                     AND poh.po_header_id = pl.po_header_id
                     AND plla.ship_to_organization_id = p_org_id
                     AND (poh.cancel_flag = 'N' OR poh.cancel_flag IS NULL)
                     AND (pl.cancel_flag = 'N' OR pl.cancel_flag IS NULL)
                     AND TRUNC (NVL (plla.need_by_date, promised_date)) BETWEEN TRUNC (
                                                                                   TO_DATE (
                                                                                      l_begin_weekdate))
                                                                            AND TRUNC (
                                                                                   TO_DATE (
                                                                                      l_6mth_date))
                     AND plla.quantity > NVL (plla.quantity_received, 0)
                     AND (plla.quantity - NVL (plla.quantity_cancelled, 0)) > 0
                     --      AND poh.type_lookup_code <> 'BLANKET'
                     AND poh.vendor_id IS NOT NULL
                     AND pl.item_id IS NOT NULL
            -- AND POH.VENDOR_ID =17956
            --  AND pl.item_id = 83006
            GROUP BY poh.vendor_id,
                     poh.vendor_site_id,
                     p_org_id,
                     pl.item_id,
                     'ES';
      EXCEPTION
         WHEN OTHERS
         THEN
            fnd_file.put_line (fnd_file.LOG, 'ERROR  ' || SQLERRM);
            NULL;
      END;

      fnd_file.put_line (fnd_file.LOG, '1');

      BEGIN
         INSERT INTO tpco_supp_rel_global_temp (vendor_id,
                                                vendor_site_id,
                                                item_id,
                                                org_id,
                                                type_flag)
              SELECT pla.source_organization_id,
                     NULL,
                     item_id,
                     pla.destination_organization_id v,
                     'IS'
                FROM po_requisition_lines_all pla,
                     po_requisition_headers_all prb
               WHERE pla.requisition_header_id = prb.requisition_header_id
                     AND pla.destination_organization_id = p_org_id
                     -- AND pla.destination_ORGANIZATION_ID  = 17956
                     AND TRUNC (pla.need_by_date) BETWEEN TRUNC (
                                                             TO_DATE (
                                                                l_begin_weekdate))
                                                      AND TRUNC (
                                                             TO_DATE (
                                                                l_6mth_date))
                     AND pla.quantity > NVL (pla.quantity_received, 0)
                     AND (pla.quantity - NVL (pla.quantity_cancelled, 0)) > 0
                     AND (pla.cancel_flag = 'N' OR pla.cancel_flag IS NULL)
                     AND prb.type_lookup_code = 'INTERNAL'
                     AND pla.item_id IS NOT NULL
            --   AND pla.item_id = 87980
            GROUP BY pla.source_organization_id,
                     NULL,
                     pla.item_id,
                     pla.destination_organization_id,
                     'IS';
      EXCEPTION
         WHEN OTHERS
         THEN
            fnd_file.put_line (fnd_file.LOG, 'ERROR  ' || SQLERRM);
            NULL;
      END;

      SELECT COUNT (*) INTO lcnt FROM tpco_supp_rel_global_temp;

      fnd_file.put_line (fnd_file.LOG, '2' || 'lcnt ' || lcnt);

      FOR rec1 IN c_purchased_item_c
      LOOP
         l_intransit_qty := 0;
         l_cost := 0;
         l_cost := tpco_get_item_cost (p_org_id, rec1.item_id);

         SELECT                                           -- CURRENT_INTRANSIT
               NVL (
                   SUM (
                      DECODE (
                         ms.intransit_owning_org_id,
                         ms.from_organization_id, inv_convert.
                                                   inv_um_convert (
                                                     ms.item_id,
                                                     NULL,
                                                     ms.quantity,
                                                     NULL,
                                                     NULL,
                                                     ms.
                                                      unit_of_measure,
                                                     msi_from.
                                                      primary_unit_of_measure),
                         ms.to_org_primary_quantity)),
                   0)
           INTO l_intransit_qty
           FROM mtl_supply ms,
                mtl_parameters mp,
                mtl_interorg_parameters mip,
                mtl_material_transactions mmt,
                rcv_shipment_lines rsl,
                mtl_system_items msi_from
          WHERE     ms.to_organization_id = p_org_id
                AND ms.item_id = rec1.item_id
                AND ms.supply_type_code IN ('SHIPMENT', 'RECEIVING')
                AND ms.destination_type_code = 'INVENTORY'
                AND mp.organization_id = ms.intransit_owning_org_id
                -- AND MS.intransit_owning_org_id = p_org_id
                AND rsl.shipment_line_id = ms.shipment_line_id
                AND mmt.transaction_id(+) = rsl.mmt_transaction_id
                AND mip.from_organization_id(+) = ms.from_organization_id
                AND mip.to_organization_id(+) = ms.to_organization_id
                AND mip.fob_point(+) =
                       DECODE (ms.intransit_owning_org_id,
                               ms.from_organization_id, 2,
                               ms.to_organization_id, 1)
                AND msi_from.inventory_item_id = ms.item_id
                AND msi_from.organization_id = ms.from_organization_id;

         --On Hand quantity
         l_ohand_qty := 0;

         SELECT NVL (SUM (primary_transaction_quantity), 0)
           INTO l_ohand_qty
           FROM mtl_onhand_quantities_detail
          WHERE organization_id = p_org_id
                AND inventory_item_id = rec1.item_id;

         l_total_qty := 0;
         l_total_qty := l_ohand_qty + l_intransit_qty;
         l_type_flag := NULL;
         l_vendor_id := NULL;
         l_vendor_site_id := NULL;
         l_dest_org_id := NULL;

         --  fnd_file.put_line (fnd_file.LOG, 'rec1.item_id;   '||rec1.item_id);
         BEGIN
            SELECT vendor_id,
                   vendor_site_id,
                   type_flag,
                   org_id
              INTO l_vendor_id,
                   l_vendor_site_id,
                   l_type_flag,
                   l_dest_org_id
              FROM tpco_supp_rel_global_temp
             WHERE org_id = p_org_id AND item_id = rec1.item_id;
         EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
               l_vendor_id := NULL;
               l_vendor_site_id := NULL;
            WHEN OTHERS
            THEN
               NULL;
         END;

         /*        fnd_file.put_line (fnd_file.LOG,
                                       'type_flag '
                                    || l_type_flag
                                    || 'lvednorid   '
                                    || l_vendor_id
                                   );*/
         l_qty_recvd := 0;
         l_qty_recvd_last_yr := 0;
         l_qty_recvd_last_yr2 := 0;
         l_ltst_trans_date := 0;
         l_vendor_name := NULL;
         l_vendor_site := NULL;
         l_city := NULL;
         l_state := NULL;
         l_country := NULL;
         l_ltst_trans_date := NULL;
         l_pur_price := NULL;
         l_currency := NULL;
         l_lst_vendor_site_code := NULL;
         l_lst_vendor_state := NULL;
         l_lst_vendor_country := NULL;
         l_last_qty := NULL;

         IF l_vendor_id IS NOT NULL AND l_type_flag = 'ES'
         THEN
            SELECT po.vendor_name,
                   pos.vendor_site_code,
                   pos.city,
                   NVL (pos.state, pos.province),
                   pos.country
              INTO l_vendor_name,
                   l_vendor_site,
                   l_city,
                   l_state,
                   l_country
              FROM po_vendors po, po_vendor_sites_all pos
             WHERE     po.vendor_id = pos.vendor_id
                   AND po.vendor_id = l_vendor_id
                   AND pos.vendor_site_id = l_vendor_site_id;

            /* fnd_file.put_line (fnd_file.LOG,
                                        'week1 '
                                     || l_begin_weekdate
                                     || '          '
                                     || l_end_date
                                    );*/
            l_wk1_qty := 0;
            l_wk2_qty := 0;
            l_wk3_qty := 0;
            l_wk4_qty := 0;
            l_wk5_qty := 0;
            l_wk6_qty := 0;
            l_wk7_qty := 0;
            l_wk8_qty := 0;
            l_mth1_qty := 0;
            l_mth2_qty := 0;
            l_mth3_qty := 0;
            l_ord_qty_bf := 0;
            l_wk1_qty :=
               tpco_get_open_po_ord_qty (p_org_id,
                                         rec1.item_id,
                                         l_vendor_id,
                                         l_vendor_site_id,
                                         l_begin_weekdate,
                                         l_end_date);
            /*      fnd_file.put_line (fnd_file.LOG,
                                     'week1 ' || l_begin_weekdate || '  '
                                     || l_end_date
                                    );*/
            l_wk2_qty :=
               tpco_get_open_po_ord_qty (p_org_id,
                                         rec1.item_id,
                                         l_vendor_id,
                                         l_vendor_site_id,
                                         l_end_date + 1,
                                         (l_end_date + 1 + 6));
            /*     fnd_file.put_line (fnd_file.LOG,
                                       'week2 '
                                    || TO_CHAR (l_end_date + 1)
                                    || '  '
                                    || TO_CHAR ((l_end_date + 1 + 6))
                                   );*/
            l_wk3_qty :=
               tpco_get_open_po_ord_qty (p_org_id,
                                         rec1.item_id,
                                         l_vendor_id,
                                         l_vendor_site_id,
                                         l_end_date + 8,
                                         (l_end_date + 8 + 6));
            --  fnd_file.put_line (fnd_file.LOG, 'week3 ' ||  TO_CHAR(l_end_date + 8)||'  '||TO_CHAR((l_end_date + 8 + 6)));
            l_wk4_qty :=
               tpco_get_open_po_ord_qty (p_org_id,
                                         rec1.item_id,
                                         l_vendor_id,
                                         l_vendor_site_id,
                                         l_end_date + 15,
                                         (l_end_date + 15 + 6));
            --fnd_file.put_line (fnd_file.LOG, 'week4 ' || TO_CHAR(l_end_date + 15)||'  '||TO_CHAR((l_end_date + 15 + 6)));
            l_wk5_qty :=
               tpco_get_open_po_ord_qty (p_org_id,
                                         rec1.item_id,
                                         l_vendor_id,
                                         l_vendor_site_id,
                                         l_end_date + 22,
                                         (l_end_date + 22 + 6));
            --  fnd_file.put_line (fnd_file.LOG, 'week5 ' || TO_CHAR(l_end_date + 22)||'  '||TO_CHAR((l_end_date + 22 + 6)));
            l_wk6_qty :=
               tpco_get_open_po_ord_qty (p_org_id,
                                         rec1.item_id,
                                         l_vendor_id,
                                         l_vendor_site_id,
                                         l_end_date + 29,
                                         (l_end_date + 29 + 6));
            -- fnd_file.put_line (fnd_file.LOG, 'week6 ' ||TO_CHAR( l_end_date + 29)||'  '|| TO_CHAR((l_end_date + 29 + 6)));
            l_wk7_qty :=
               tpco_get_open_po_ord_qty (p_org_id,
                                         rec1.item_id,
                                         l_vendor_id,
                                         l_vendor_site_id,
                                         l_end_date + 36,
                                         (l_end_date + 36 + 6));
            -- fnd_file.put_line (fnd_file.LOG, 'week7 ' || TO_CHAR(l_end_date + 36)||'  '||  TO_CHAR((l_end_date + 36 + 6)));
            l_wk8_qty :=
               tpco_get_open_po_ord_qty (p_org_id,
                                         rec1.item_id,
                                         l_vendor_id,
                                         l_vendor_site_id,
                                         l_end_date + 43,
                                         (l_end_date + 43 + 6));
            /* fnd_file.put_line (fnd_file.LOG,
                                   'week8 '
                                || TO_CHAR (l_end_date + 43)
                                || '  '
                                || TO_CHAR ((l_end_date + 43 + 6))
                               );*/
            l_mth_begin_date := l_end_date + 50;
            l_mth1_qty :=
               tpco_get_open_po_ord_qty (p_org_id,
                                         rec1.item_id,
                                         l_vendor_id,
                                         l_vendor_site_id,
                                         l_mth_begin_date,
                                         (l_mth_begin_date + 30));
            /*  fnd_file.put_line (fnd_file.LOG,
                                    'mn1 '
                                 || TO_CHAR (l_mth_begin_date)
                                 || '  '
                                 || TO_CHAR ((l_mth_begin_date + 30))
                                );*/
            l_mth2_qty :=
               tpco_get_open_po_ord_qty (p_org_id,
                                         rec1.item_id,
                                         l_vendor_id,
                                         l_vendor_site_id,
                                         l_mth_begin_date + 31,
                                         (l_mth_begin_date + 61));
            /*     fnd_file.put_line (fnd_file.LOG,
                                        'mn2 '
                                     || TO_CHAR (l_mth_begin_date + 31)
                                     || '  '
                                     || TO_CHAR (l_mth_begin_date + 61)
                                    );*/
            l_mth3_qty :=
               tpco_get_open_po_ord_qty (p_org_id,
                                         rec1.item_id,
                                         l_vendor_id,
                                         l_vendor_site_id,
                                         (l_mth_begin_date + 62),
                                         (l_mth_begin_date + 92));

            BEGIN
               l_ord_qty_bf := 0;

               SELECT NVL (
                         SUM (
                              plla.quantity
                            - NVL (plla.quantity_cancelled, 0)
                            - NVL (plla.quantity_received, 0)),
                         0)
                         qty_ordered
                 INTO l_ord_qty_bf
                 FROM po_line_locations_all plla,
                      po_lines_all pl,
                      po_headers_all poh
                WHERE     plla.po_line_id = pl.po_line_id
                      AND poh.org_id = plla.org_id
                      AND NVL (poh.closed_code, 'OPEN') = 'OPEN'
                      AND NVL (pl.closed_code, 'OPEN') = 'OPEN'
                      AND NVL (plla.closed_code, 'OPEN') = 'OPEN'
                      AND poh.org_id = plla.org_id
                      AND poh.po_header_id = pl.po_header_id
                      AND plla.ship_to_organization_id = p_org_id
                      AND poh.vendor_id = l_vendor_id
                      AND poh.vendor_site_id = l_vendor_site_id
                      AND pl.item_id = rec1.item_id
                      AND (poh.cancel_flag = 'N' OR poh.cancel_flag IS NULL)
                      AND (pl.cancel_flag = 'N' OR pl.cancel_flag IS NULL)
                      AND TRUNC (NVL (plla.need_by_date, plla.promised_date)) <
                             TRUNC (TO_DATE (l_begin_weekdate))
                      AND plla.quantity > NVL (plla.quantity_received, 0);
            EXCEPTION
               WHEN OTHERS
               THEN
                  l_ord_qty_bf := 0;
                  NULL;
            END;

            l_pur_price := NULL;
            l_currency := NULL;

            FOR rec_price IN c_po_price (l_vendor_id,
                                         l_vendor_site_id,
                                         rec1.item_id,
                                         l_begin_weekdate,
                                         (l_begin_weekdate + 146))
            LOOP
               --fnd_file.put_line (fnd_file.LOG, 'recprice loop');
               l_pur_price := rec_price.price_override;
               l_currency := rec_price.currency_code;
            END LOOP;
         END IF;

         IF l_type_flag IS NULL
         THEN
            l_wk1_qty := 0;
            l_wk2_qty := 0;
            l_wk3_qty := 0;
            l_wk4_qty := 0;
            l_wk5_qty := 0;
            l_wk6_qty := 0;
            l_wk7_qty := 0;
            l_wk8_qty := 0;
            l_mth1_qty := 0;
            l_mth2_qty := 0;
            l_mth3_qty := 0;
            l_ord_qty_bf := 0;
         END IF;

         IF l_vendor_id IS NOT NULL AND l_type_flag = 'IS'
         THEN
            l_vendor_name := NULL;
            l_vendor_site := NULL;
            l_city := NULL;
            l_state := NULL;
            l_country := NULL;

            SELECT organization_name
              INTO l_vendor_name
              FROM apps.org_organization_definitions
             WHERE organization_id = l_vendor_id;

            -- fnd_file.put_line (fnd_file.LOG, '4 '||l_vendor_name);
            /*


                /* fnd_file.put_line (fnd_file.LOG,
                                            'week1 '
                                         || l_begin_weekdate
                                         || '          '
                                         || l_end_date
                                        );*/
            l_wk1_qty := 0;
            l_wk2_qty := 0;
            l_wk3_qty := 0;
            l_wk4_qty := 0;
            l_wk5_qty := 0;
            l_wk6_qty := 0;
            l_wk7_qty := 0;
            l_wk8_qty := 0;
            l_mth1_qty := 0;
            l_mth2_qty := 0;
            l_mth3_qty := 0;
            l_ord_qty_bf := 0;
            l_wk1_qty :=
               tpco_get_open_ir_ord_qty (rec1.item_id,
                                         l_vendor_id,
                                         l_dest_org_id,
                                         l_begin_weekdate,
                                         l_end_date);
            --   fnd_file.put_line (fnd_file.LOG, 'week1 ' || l_begin_weekdate||'  '||l_end_date);
            l_wk2_qty :=
               tpco_get_open_ir_ord_qty (rec1.item_id,
                                         l_vendor_id,
                                         l_dest_org_id,
                                         l_end_date + 1,
                                         (l_end_date + 1 + 6));
            -- fnd_file.put_line (fnd_file.LOG, 'week2 ' ||TO_CHAR( l_end_date + 1)||'  '||TO_CHAR((l_end_date + 1 + 6)));
            l_wk3_qty :=
               tpco_get_open_ir_ord_qty (rec1.item_id,
                                         l_vendor_id,
                                         l_dest_org_id,
                                         l_end_date + 8,
                                         (l_end_date + 8 + 6));
            --   fnd_file.put_line (fnd_file.LOG, 'week3 ' ||  TO_CHAR(l_end_date + 8)||'  '||TO_CHAR((l_end_date + 8 + 6)));
            l_wk4_qty :=
               tpco_get_open_ir_ord_qty (rec1.item_id,
                                         l_vendor_id,
                                         l_dest_org_id,
                                         l_end_date + 15,
                                         (l_end_date + 15 + 6));
            --  fnd_file.put_line (fnd_file.LOG, 'week4 ' || TO_CHAR(l_end_date + 15)||'  '||TO_CHAR((l_end_date + 15 + 6)));
            l_wk5_qty :=
               tpco_get_open_ir_ord_qty (rec1.item_id,
                                         l_vendor_id,
                                         l_dest_org_id,
                                         l_end_date + 22,
                                         (l_end_date + 22 + 6));
            --   fnd_file.put_line (fnd_file.LOG, 'week5 ' || TO_CHAR(l_end_date + 22)||'  '||TO_CHAR((l_end_date + 22 + 6)));
            l_wk6_qty :=
               tpco_get_open_ir_ord_qty (rec1.item_id,
                                         l_vendor_id,
                                         l_dest_org_id,
                                         l_end_date + 29,
                                         (l_end_date + 29 + 6));
            --  fnd_file.put_line (fnd_file.LOG, 'week6 ' ||TO_CHAR( l_end_date + 29)||'  '|| TO_CHAR((l_end_date + 29 + 6)));
            l_wk7_qty :=
               tpco_get_open_ir_ord_qty (rec1.item_id,
                                         l_vendor_id,
                                         l_dest_org_id,
                                         l_end_date + 36,
                                         (l_end_date + 36 + 6));
            --  fnd_file.put_line (fnd_file.LOG, 'week7 ' || TO_CHAR(l_end_date + 36)||'  '||  TO_CHAR((l_end_date + 36 + 6)));
            l_wk8_qty :=
               tpco_get_open_ir_ord_qty (rec1.item_id,
                                         l_vendor_id,
                                         l_dest_org_id,
                                         l_end_date + 43,
                                         (l_end_date + 43 + 6));
            --          fnd_file.put_line (fnd_file.LOG, 'week8 ' || TO_CHAR(l_end_date + 43)||'  '|| TO_CHAR((l_end_date + 43 + 6)));
            l_mth_begin_date := l_end_date + 50;
            l_mth1_qty :=
               tpco_get_open_ir_ord_qty (rec1.item_id,
                                         l_vendor_id,
                                         l_dest_org_id,
                                         l_mth_begin_date,
                                         (l_mth_begin_date + 30));
            -- fnd_file.put_line (fnd_file.LOG, 'mn1 ' ||TO_CHAR(l_mth_begin_date)||'  '||TO_CHAR((l_mth_begin_date + 30)));
            l_mth2_qty :=
               tpco_get_open_ir_ord_qty (rec1.item_id,
                                         l_vendor_id,
                                         l_dest_org_id,
                                         l_mth_begin_date + 31,
                                         (l_mth_begin_date + 61));
            -- fnd_file.put_line (fnd_file.LOG, 'mn3 ' || TO_CHAR(l_mth_begin_date + 31)||'  '||TO_CHAR(l_mth_begin_date + 61));
            l_mth3_qty :=
               tpco_get_open_ir_ord_qty (rec1.item_id,
                                         l_vendor_id,
                                         l_dest_org_id,
                                         (l_mth_begin_date + 62),
                                         (l_mth_begin_date + 92));

            --  fnd_file.put_line (fnd_file.LOG, 'mn3 ' || TO_CHAR(l_mth_begin_date + 62)||'  '||TO_CHAR(l_mth_begin_date + 92));
            BEGIN
               l_ord_qty_bf := 0;

               SELECT NVL (
                         SUM (
                              pla.quantity
                            - NVL (pla.quantity_cancelled, 0)
                            - NVL (pla.quantity_received, 0)),
                         0)
                         qty_ordered
                 INTO l_ord_qty_bf
                 FROM po_requisition_lines_all pla,
                      po_requisition_headers_all prb
                WHERE pla.requisition_header_id = prb.requisition_header_id
                      AND pla.destination_organization_id = l_dest_org_id
                      AND NVL (prb.closed_code, 'OPEN') = 'OPEN'
                      AND pla.source_organization_id =
                             NVL (l_vendor_id, pla.source_organization_id)
                      AND TRUNC (pla.need_by_date) <
                             TRUNC (TO_DATE (l_begin_weekdate))
                      AND (pla.cancel_flag = 'N' OR pla.cancel_flag IS NULL)
                      AND pla.quantity > NVL (pla.quantity_received, 0)
                      AND pla.item_id = rec1.item_id
                      AND prb.type_lookup_code = 'INTERNAL'
                      AND pla.item_id IS NOT NULL;
            --  fnd_file.put_line (fnd_file.LOG, 'l_ord_qty  ' || l_ord_qty);
            EXCEPTION
               WHEN OTHERS
               THEN
                  fnd_file.
                   put_line (fnd_file.LOG, 'error in open IR  ' || SQLERRM);
                  l_ord_qty_bf := 0;
            END;

            l_pur_price := NULL;
            l_currency := NULL;

            --  fnd_file.put_line (fnd_file.LOG, 'BEFORErecprice loop');
            FOR rec_price IN c_ir_price (l_dest_org_id,
                                         l_vendor_id,
                                         rec1.item_id,
                                         l_begin_weekdate,
                                         (l_begin_weekdate + 146))
            LOOP
               --      fnd_file.put_line (fnd_file.LOG, 'recprice loop');
               l_pur_price := rec_price.base_unit_price;
            END LOOP;

            BEGIN
               SELECT currency_code
                 INTO l_currency
                 FROM gl_sets_of_books glsob,
                      org_organization_definitions ood
                WHERE glsob.set_of_books_id = ood.set_of_books_id
                      AND ood.organization_id = l_dest_org_id;
            EXCEPTION
               WHEN OTHERS
               THEN
                  l_currency := NULL;
            END;
         END IF;

         l_safety_qty := NULL;

         BEGIN
            SELECT safety_stock_quantity
              INTO l_safety_qty
              FROM mtl_safety_stocks a
             WHERE a.organization_id = p_org_id
                   AND a.inventory_item_id = rec1.item_id
                   AND TRUNC (a.effectivity_date) =
                          (SELECT MAX (TRUNC (b.effectivity_date))
                             FROM mtl_safety_stocks b
                            WHERE b.organization_id = p_org_id
                                  AND b.inventory_item_id = rec1.item_id)
                   AND ROWNUM = 1;
         EXCEPTION
            WHEN OTHERS
            THEN
               l_safety_qty := NULL;
         END;

         --  IF l_vendor_id IS NULL
         -- THEN
         l_qty_recvd :=
            tpco_get_item_receipt (
               p_org_id,
               rec1.item_id,
               TO_CHAR (TO_DATE (p_date, 'DD-MM-YYYY'), 'YYYY'));
         l_qty_recvd_last_yr :=
            tpco_get_item_receipt (
               p_org_id,
               rec1.item_id,
               TO_NUMBER (TO_CHAR (TO_DATE (p_date, 'DD-MM-YYYY'), 'YYYY'))
               - 1);
         l_qty_recvd_last_yr2 :=
            tpco_get_item_receipt (
               p_org_id,
               rec1.item_id,
               TO_NUMBER (TO_CHAR (TO_DATE (p_date, 'DD-MM-YYYY'), 'YYYY'))
               - 2);
         l_last_vendor := NULL;
         l_lst_vendor_site_code := NULL;
         l_lst_vendor_state := NULL;
         l_lst_vendor_country := NULL;
         l_last_qty := 0;
         tpco_get_last_supp_add (p_org_id,
                                 rec1.item_id,
                                 l_ltst_trans_date,
                                 l_last_vendor,
                                 l_lst_vendor_site_code,
                                 l_lst_vendor_state,
                                 l_lst_vendor_country,
                                 l_last_qty);
         --    END IF;
         l_total_openorders := 0;
         l_total_openorders :=
              l_wk1_qty
            + l_wk2_qty
            + l_wk3_qty
            + l_wk4_qty
            + l_wk5_qty
            + l_wk6_qty
            + l_wk7_qty
            + l_wk8_qty
            + l_mth1_qty
            + l_mth2_qty
            + l_mth3_qty
            + l_ord_qty_bf;

         IF (   l_total_qty <> 0
             OR l_wk1_qty <> 0
             OR l_wk2_qty <> 0
             OR l_wk3_qty <> 0
             OR l_wk4_qty <> 0
             OR l_wk5_qty <> 0
             OR l_wk6_qty <> 0
             OR l_wk7_qty <> 0
             OR l_wk8_qty <> 0
             OR l_mth1_qty <> 0
             OR l_mth2_qty <> 0
             OR l_mth3_qty <> 0
             OR l_qty_recvd <> 0
             OR l_qty_recvd_last_yr <> 0
             OR l_qty_recvd_last_yr2 <> 0
             OR l_ord_qty_bf <> 0)
         THEN
            --    fnd_file.put_line (fnd_file.LOG, 'mn3 ' || TO_CHAR(l_mth_begin_date + 62)||'  '||TO_CHAR(l_mth_begin_date + 92));
            fnd_file.
             put_line (
               fnd_file.output,
                  l_vendor_site
               || '|'
               || l_vendor_name
               || '|'
               || l_city
               || '|'
               || l_state
               || '|'
               || l_country
               || '|'
               || rec1.item_num
               || '|'
               || rec1.item_type
               || '|'
               || rec1.description
               || '|'
               || rec1.planner_code
               || '|'
               || l_cost
               || '|'
               || l_pur_price
               || '|'
               || l_currency
               || '|'
               || rec1.primary_unit_of_measure
               || '|'
               || l_safety_qty
               || '|'
               || rec1.fixed_lot_multiplier
               || '|'
               || rec1.minimum_order_quantity
               || '|'
               || rec1.maximum_order_quantity
               || '|'
               || rec1.full_lead_time
               || '|'
               || l_intransit_qty
               || '|'
               || l_ohand_qty
               || '|'
               || l_total_qty
               || '|'
               || l_total_openorders
               || '|'
               || l_ord_qty_bf
               || '|'
               || l_wk1_qty
               || '|'
               || l_wk2_qty
               || '|'
               || l_wk3_qty
               || '|'
               || l_wk4_qty
               || '|'
               || l_wk5_qty
               || '|'
               || l_wk6_qty
               || '|'
               || l_wk7_qty
               || '|'
               || l_wk8_qty
               || '|'
               || l_mth1_qty
               || '|'
               || l_mth2_qty
               || '|'
               || l_mth3_qty
               || '|'
               || l_ltst_trans_date
               || '|'
               || l_last_vendor
               || '|'
               || l_last_qty
               || '|'
               || l_qty_recvd
               || '|'
               || l_qty_recvd_last_yr
               || '|'
               || l_qty_recvd_last_yr2);
         END IF;
      END LOOP;
   EXCEPTION
      WHEN OTHERS
      THEN
         fnd_file.put_line (fnd_file.LOG, 'Error:      ' || SQLERRM);
   END;

   FUNCTION tpco_get_open_ir_ord_qty (p_item_id              IN NUMBER,
                                      p_source_org_id        IN NUMBER,
                                      p_destination_org_id   IN NUMBER,
                                      p_req_from_date        IN DATE,
                                      p_req_to_date          IN DATE)
      RETURN NUMBER
   IS
      l_ord_qty   NUMBER := 0;
   BEGIN
      SELECT NVL (
                SUM (
                     pla.quantity
                   - NVL (pla.quantity_cancelled, 0)
                   - NVL (pla.quantity_received, 0)),
                0)
                qty_ordered
        INTO l_ord_qty
        FROM po_requisition_lines_all pla, po_requisition_headers_all prb
       WHERE pla.requisition_header_id = prb.requisition_header_id
             AND pla.destination_organization_id = p_destination_org_id
             AND pla.source_organization_id =
                    NVL (p_source_org_id, pla.source_organization_id)
             AND TRUNC (pla.need_by_date) BETWEEN TRUNC (p_req_from_date)
                                              AND TRUNC (p_req_to_date)
             AND (pla.cancel_flag = 'N' OR pla.cancel_flag IS NULL)
             AND pla.quantity > NVL (pla.quantity_received, 0)
             AND pla.item_id = p_item_id
             AND prb.type_lookup_code = 'INTERNAL'
             AND pla.item_id IS NOT NULL;

      --  fnd_file.put_line (fnd_file.LOG, 'l_ord_qty  ' || l_ord_qty);
      RETURN l_ord_qty;
   EXCEPTION
      WHEN OTHERS
      THEN
         fnd_file.put_line (fnd_file.LOG, 'error in open IR  ' || SQLERRM);
         l_ord_qty := 0;
   END;

   FUNCTION tpco_get_item_receipt (p_org_id    IN NUMBER,
                                   p_item_id   IN NUMBER,
                                   p_date      IN VARCHAR2)
      RETURN NUMBER
   IS
      l_qty_recvd   NUMBER;
   BEGIN
      l_qty_recvd := 0;

      SELECT NVL (SUM (rt.quantity), 0) qty_received
        INTO l_qty_recvd
        FROM rcv_transactions rt, rcv_shipment_lines rcl
       WHERE     rt.shipment_line_id = rcl.shipment_line_id
             AND rt.organization_id = NVL (p_org_id, rt.organization_id)
             AND rcl.item_id = p_item_id
             AND rt.transaction_type IN ('RECEIVE')
             AND rt.source_document_code IN ('PO', 'REQ')
             AND TO_CHAR (rt.transaction_date, 'YYYY') = p_date;

      RETURN l_qty_recvd;
   EXCEPTION
      WHEN OTHERS
      THEN
         l_qty_recvd := 0;
         RETURN l_qty_recvd;
   END;

   PROCEDURE tpco_get_last_supp (p_org_id          IN     NUMBER,
                                 p_item_id         IN     NUMBER,
                                 op_max_rct_date      OUT VARCHAR2,
                                 op_last_supp         OUT VARCHAR2)
   IS
      l_qty_date         DATE;
      l_last_vendor_id   po_vendors.vendor_id%TYPE;
      l_source_doc       rcv_transactions.source_document_code%TYPE;
      l_vendor_name      po_vendors.vendor_name%TYPE;

      CURSOR c_last_vendor
      IS
           SELECT MAX (transaction_date) trans_date,
                  NVL (rt.vendor_id, rcl.from_organization_id) vendor_id,
                  rt.source_document_code source_doc
             FROM rcv_transactions rt, rcv_shipment_lines rcl
            WHERE     rt.shipment_line_id = rcl.shipment_line_id
                  AND rt.organization_id = NVL (p_org_id, rt.organization_id)
                  AND rcl.item_id = p_item_id
                  AND rt.transaction_type IN ('RECEIVE')
                  AND rt.source_document_code IN ('PO', 'REQ')
         GROUP BY NVL (rt.vendor_id, rcl.from_organization_id),
                  rt.source_document_code
         -- ORDER BY 1 DESC;
         ORDER BY 1;
   BEGIN
      l_qty_date := NULL;
      op_max_rct_date := l_qty_date;
      l_source_doc := NULL;
      l_last_vendor_id := NULL;

      BEGIN
         FOR rec1 IN c_last_vendor
         LOOP
            l_qty_date := rec1.trans_date;
            l_last_vendor_id := rec1.vendor_id;
            l_source_doc := rec1.source_doc;
         END LOOP;
      -- fnd_file.put_line (fnd_file.LOG, 'l_qty_date  ' || l_qty_date);
      EXCEPTION
         WHEN OTHERS
         THEN
            l_last_vendor_id := NULL;
            l_source_doc := NULL;
            l_qty_date := NULL;
      END;

      /*   BEGIN
            SELECT NVL (vendor_id, rcl.from_organization_id),
                   rt.source_document_code
              INTO l_last_vendor_id,
                   l_source_doc
              FROM rcv_transactions rt, rcv_shipment_lines rcl
             WHERE rt.shipment_line_id = rcl.shipment_line_id
               AND rt.organization_id = NVL (p_org_id, rt.organization_id)
               AND rcl.item_id = p_item_id
               AND rt.transaction_type IN ('RECEIVE')
            AND RT.TRANSACTION_DATE(
            --   AND TRUNC (rt.transaction_date) = TRUNC(NVL(TO_DATE(l_qty_date), SYSDATE));

             fnd_file.put_line (fnd_file.LOG, 'l_last_vendor_id    ' || l_last_vendor_id||' l_source_doc '|| l_source_doc);

         EXCEPTION
            WHEN OTHERS
            THEN
               l_last_vendor_id := NULL;
               l_source_doc := NULL;
         END;*/
      IF l_source_doc = 'PO'
      THEN
         SELECT vendor_name
           INTO l_vendor_name
           FROM po_vendors
          WHERE vendor_id = l_last_vendor_id;
      -- fnd_file.put_line (fnd_file.LOG, ' 1 ');
      ELSIF l_source_doc = 'REQ'
      THEN
         SELECT organization_name
           INTO l_vendor_name
           FROM apps.org_organization_definitions
          WHERE organization_id = l_last_vendor_id;

         fnd_file.put_line (fnd_file.LOG, ' 2 ');
      END IF;

      --  fnd_file.put_line (fnd_file.LOG, ' l_vendor_name    ' || l_last_vendor_id||'  l_vendor_name '|| l_source_doc);
      op_last_supp := l_vendor_name;
      op_max_rct_date := l_qty_date;
   EXCEPTION
      WHEN OTHERS
      THEN
         op_last_supp := NULL;
         op_max_rct_date := NULL;
   END;

   FUNCTION tpco_get_item_cost (p_org_id IN NUMBER, p_item_id IN NUMBER)
      RETURN NUMBER
   IS
      lcost   NUMBER;
   BEGIN
      SELECT a.item_cost
        INTO lcost
        FROM apps.cst_item_cost_type_v a
       WHERE     inventory_item_id = p_item_id
             AND organization_id = p_org_id
             AND cost_type = 'Frozen'
             AND TRUNC (last_update_date) =
                    (SELECT TRUNC (MAX (last_update_date))
                       FROM apps.cst_item_cost_type_v b
                      WHERE     a.inventory_item_id = b.inventory_item_id
                            AND a.organization_id = b.organization_id
                            AND b.cost_type = 'Frozen');

      --  fnd_file.put_line (fnd_file.LOG, 'lcost' || lcost);
      RETURN lcost;
   EXCEPTION
      WHEN OTHERS
      THEN
         lcost := 0;
         RETURN lcost;
         NULL;
   END;

   PROCEDURE tpco_get_last_supp_add (p_org_id              IN     NUMBER,
                                     p_item_id             IN     NUMBER,
                                     op_max_rct_date          OUT VARCHAR2,
                                     op_last_supp             OUT VARCHAR2,
                                     op_vendor_site_code      OUT VARCHAR2,
                                     op_vendor_state          OUT VARCHAR2,
                                     op_vendor_country        OUT VARCHAR2,
                                     op_last_qty              OUT NUMBER)
   IS
      l_qty_date         DATE;
      l_last_vendor_id   po_vendors.vendor_id%TYPE;
      l_source_doc       rcv_transactions.source_document_code%TYPE;
      l_vendor_name      po_vendors.vendor_name%TYPE;
      l_vendor_site_id   po_vendor_sites_all.vendor_site_id%TYPE;
      l_site_code        po_vendor_sites_all.vendor_site_code%TYPE;
      l_state            po_vendor_sites_all.state%TYPE;
      l_country          po_vendor_sites_all.country%TYPE;
      l_last_qty         NUMBER := 0;

      CURSOR c_last_vendor
      IS
           SELECT MAX (transaction_date) trans_date,
                  NVL (rt.vendor_id, rcl.from_organization_id) vendor_id,
                  rt.vendor_site_id,
                  rt.source_document_code source_doc
             FROM rcv_transactions rt, rcv_shipment_lines rcl
            WHERE     rt.shipment_line_id = rcl.shipment_line_id
                  AND rt.organization_id = NVL (p_org_id, rt.organization_id)
                  AND rcl.item_id = p_item_id
                  AND rt.transaction_type IN ('RECEIVE')
                  AND rt.source_document_code IN ('PO', 'REQ')
         GROUP BY NVL (rt.vendor_id, rcl.from_organization_id),
                  rt.source_document_code,
                  rt.vendor_site_id
         -- ORDER BY 1 DESC;
         ORDER BY 1;
   BEGIN
      l_qty_date := NULL;
      op_max_rct_date := l_qty_date;
      l_source_doc := NULL;
      l_last_vendor_id := NULL;
      l_site_code := NULL;
      l_state := NULL;
      l_country := NULL;
      l_last_qty := NULL;

      BEGIN
         FOR rec1 IN c_last_vendor
         LOOP
            l_qty_date := rec1.trans_date;
            l_last_vendor_id := rec1.vendor_id;
            l_source_doc := rec1.source_doc;
            l_vendor_site_id := rec1.vendor_site_id;
         END LOOP;
      -- fnd_file.put_line (fnd_file.LOG, 'l_qty_date  ' || l_qty_date);

      -- ORDER BY 1 DESC;
      EXCEPTION
         WHEN OTHERS
         THEN
            l_last_vendor_id := NULL;
            l_source_doc := NULL;
            l_qty_date := NULL;
      END;

      /*   BEGIN
            SELECT NVL (vendor_id, rcl.from_organization_id),
                   rt.source_document_code
              INTO l_last_vendor_id,
                   l_source_doc
              FROM rcv_transactions rt, rcv_shipment_lines rcl
             WHERE rt.shipment_line_id = rcl.shipment_line_id
               AND rt.organization_id = NVL (p_org_id, rt.organization_id)
               AND rcl.item_id = p_item_id
               AND rt.transaction_type IN ('RECEIVE')
            AND RT.TRANSACTION_DATE(
            --   AND TRUNC (rt.transaction_date) = TRUNC(NVL(TO_DATE(l_qty_date), SYSDATE));

             fnd_file.put_line (fnd_file.LOG, 'l_last_vendor_id    ' || l_last_vendor_id||' l_source_doc '|| l_source_doc);

         EXCEPTION
            WHEN OTHERS
            THEN
               l_last_vendor_id := NULL;
               l_source_doc := NULL;
         END;*/
      IF l_source_doc = 'PO'
      THEN
         SELECT vendor_name
           INTO l_vendor_name
           FROM po_vendors
          WHERE vendor_id = l_last_vendor_id;

         SELECT vendor_site_code, NVL (state, province), country
           INTO l_site_code, l_state, l_country
           FROM po_vendor_sites_all
          WHERE vendor_site_id = l_vendor_site_id;

         -- fnd_file.put_line (fnd_file.LOG, ' 1 ');
         SELECT NVL (SUM (primary_quantity), 0) last_qty
           INTO l_last_qty
           FROM rcv_transactions rt, rcv_shipment_lines rcl
          WHERE     rt.shipment_line_id = rcl.shipment_line_id
                AND rt.organization_id = NVL (p_org_id, rt.organization_id)
                AND rcl.item_id = p_item_id
                AND rt.transaction_type IN ('RECEIVE')
                AND TRUNC (rt.transaction_date) = TRUNC (l_qty_date)
                AND rt.vendor_id = l_last_vendor_id;
      ELSIF l_source_doc = 'REQ'
      THEN
         SELECT organization_name
           INTO l_vendor_name
           FROM apps.org_organization_definitions
          WHERE organization_id = l_last_vendor_id;

         SELECT NVL (SUM (primary_quantity), 0) last_qty
           INTO l_last_qty
           FROM rcv_transactions rt, rcv_shipment_lines rcl
          WHERE     rt.shipment_line_id = rcl.shipment_line_id
                AND rt.organization_id = NVL (p_org_id, rt.organization_id)
                AND rcl.item_id = p_item_id
                AND rt.transaction_type IN ('RECEIVE')
                AND TRUNC (rt.transaction_date) = TRUNC (l_qty_date)
                AND rt.vendor_id = l_last_vendor_id;

         fnd_file.put_line (fnd_file.LOG, ' 2 ');
      END IF;

      --  fnd_file.put_line (fnd_file.LOG, ' l_vendor_name    ' || l_last_vendor_id||'  l_vendor_name '|| l_source_doc);
      op_last_supp := l_vendor_name;
      op_max_rct_date := l_qty_date;
      op_vendor_site_code := l_site_code;
      op_vendor_state := l_state;
      op_vendor_country := l_country;
      op_last_qty := l_last_qty;
   EXCEPTION
      WHEN OTHERS
      THEN
         op_last_supp := NULL;
         op_max_rct_date := NULL;
         op_vendor_site_code := NULL;
         op_vendor_state := NULL;
         op_vendor_country := NULL;
         op_last_qty := NULL;
   END;

   PROCEDURE tpco_get_consume_date (p_item_id       IN     NUMBER,
                                    p_org_id        IN     NUMBER,
                                    op_trans_date      OUT VARCHAR2,
                                    op_trans_qty       OUT VARCHAR2)
   IS
      l_date    DATE;
      l_qty     NUMBER;
      l_date1   VARCHAR2 (15);

      CURSOR c_trans (
         p_date IN VARCHAR2)
      IS
           SELECT NVL (primary_quantity, 0) qty
             FROM mtl_material_transactions mmt
            WHERE     mmt.inventory_item_id = p_item_id
                  AND mmt.organization_id = p_org_id
                  AND transaction_type_id = 44       --added for kieth'request
                  AND TRUNC (transaction_date) = TO_DATE (p_date)
         ORDER BY transaction_id DESC;
   BEGIN
      l_date := NULL;
      fnd_file.put_line (fnd_file.LOG, 'in get consume date');

      SELECT NVL (MAX (TRUNC (transaction_date)), NULL)
        INTO l_date
        FROM mtl_material_transactions mmt
       WHERE     mmt.inventory_item_id = p_item_id
             AND mmt.organization_id = p_org_id
             AND transaction_type_id = 44 --added for kieth'request wip assembly completion
                                         ;

      l_date1 := NULL;
      l_date1 := TO_CHAR (l_date, 'DD-MON-YYYY');
      op_trans_date := l_date1;
      fnd_file.put_line (fnd_file.LOG, 'l_date1  ' || l_date1);
      l_qty := 0;

      FOR rec1 IN c_trans (l_date1)
      LOOP
         l_qty := rec1.qty;
      END LOOP;

      /*SELECT
                  NVL(SUM(primary_quantity),0)
             INTO l_qty
          FROM mtl_material_transactions mmt
       WHERE mmt.inventory_item_id =p_item_id
         AND  mmt.organization_id = p_org_id
      AND TRUNC(transaction_date) = TO_DATE(l_date1);*/
      fnd_file.put_line (fnd_file.LOG, 'l_qty  ' || l_qty);
      op_trans_qty := l_qty;
   EXCEPTION
      WHEN OTHERS
      THEN
         fnd_file.put_line (fnd_file.LOG, 'ERROR  ' || l_qty);
         op_trans_date := NULL;
         op_trans_qty := 0;
   END;

   PROCEDURE tpco_get_pur_usage_all_org (errbuff          OUT VARCHAR2,
                                         retcode          OUT VARCHAR2,
                                         p_global      IN     VARCHAR2,
                                         p_org_id      IN     NUMBER,
                                         p_date        IN     VARCHAR2,
                                         p_item_type   IN     VARCHAR2)
   IS
      CURSOR c1
      IS
         SELECT organization_id FROM apps.org_organization_definitions;
   BEGIN
      fnd_file.put_line (fnd_file.LOG, 'TPCO Purchased Item Usage Report');
      fnd_file.put_line (fnd_file.LOG, 'p_global  ' || p_global);

      IF p_global = 'Y'
      THEN
         fnd_file.put_line (fnd_file.LOG, '1');

         FOR rec1 IN c1
         LOOP
            --   tpco_get_purchase_dtl (rec1.organization_id, p_date);
            tpco_get_purchase_usage_dtl (rec1.organization_id,
                                         p_date,
                                         p_item_type);
         END LOOP;
      ELSE
         fnd_file.put_line (fnd_file.LOG, '1a');
         -- tpco_get_purchase_dtl (p_org_id, p_date);
         tpco_get_purchase_usage_dtl (p_org_id, p_date, p_item_type);
      END IF;
   EXCEPTION
      WHEN OTHERS
      THEN
         retcode := 2;
         fnd_file.put_line (fnd_file.LOG, 'Error:      ' || SQLERRM);
   END;

   PROCEDURE tpco_get_purchase_usage_dtl (p_org_id      IN NUMBER,
                                          p_date        IN VARCHAR2,
                                          p_item_type   IN VARCHAR2)
   IS
      l_last_vendor          po_vendors.vendor_name%TYPE;
      l_ltst_trans_date      VARCHAR2 (50);
      l_cost                 NUMBER;
      l_wip_qty_ytd          NUMBER;
      l_name                 apps.org_organization_definitions.organization_name%TYPE;
      l_vendor_id            po_vendors.vendor_id%TYPE;
      l_pur_price            NUMBER;
      l_currency             VARCHAR2 (50);
      l_wip_qty_lst_yr       NUMBER;
      l_wip_qty_lst_prv_yr   NUMBER;
      lcnt1                  NUMBER;
      l_ohand_qty            NUMBER;
      l_last_qty             NUMBER := 0;
      l_last_rec_qty         NUMBER := 0;
      l_max_trans_date       VARCHAR2 (50);
      l_max_trans_qty        VARCHAR2 (200);
      l_intransit_qty        NUMBER;
      l_total_qty            NUMBER;
      l_safety_qty           NUMBER;
      l_site_code            VARCHAR2 (250);
      l_state                VARCHAR2 (100);
      l_country              VARCHAR2 (100);
      l_prod_fam             VARCHAR2 (20);
      l_prod_line            VARCHAR2 (20);

      CURSOR c_purchased_item
      IS
         SELECT msi.segment1 item_num,
                planner_code,
                primary_unit_of_measure,
                msi.inventory_item_id item_id,
                DECODE (msi.planning_make_buy_code,
                        1, 'Make',
                        2, 'Buy',
                        msi.planning_make_buy_code)
                   plan_item,
                REPLACE (msi.description, CHR (9), ' ') description,
                msi.min_minmax_quantity min_minmax_quantity,
                msi.max_minmax_quantity max_minmax_quantity,
                postprocessing_lead_time,
                preprocessing_lead_time,
                fixed_lead_time,
                cumulative_total_lead_time,
                minimum_order_quantity,
                planning_time_fence_days,
                demand_time_fence_days,
                cum_manufacturing_lead_time,
                fixed_lot_multiplier,
                DECODE (mrp_planning_code,
                        7, 'MRP and MPP planning',
                        3, 'MRP planning',
                        4, 'MPS planning',
                        6, 'Not planned',
                        8, 'MPS and MPP plannin g',
                        9, 'MPP planning',
                        mrp_planning_code)
                   plan_code,
                full_lead_time processing_lead_time,
                msi.item_type,
                msi.inventory_item_status_code inventory_item_status_code
           FROM mtl_system_items_b msi
          WHERE msi.item_type = p_item_type --  AND inventory_item_status_code <> 'Active'
                                         --AND msi.segment1 = 'AE630AR-717-A2'
                AND msi.organization_id = p_org_id                          --
                                         -- AND msi.inventory_item_id = 504640
   ;

      /* SELECT msi.segment1 item_num, primary_unit_of_measure,
             msi.inventory_item_id item_id,
             REPLACE (msi.description, CHR (9), ' ') description
        FROM mtl_system_items_b msi,TPCO_WIP_SALE_ISSUE tws
       WHERE msi.organization_id = tws.org_id
         AND msi.inventory_item_id = tws.item_id
      AND tws.item_type = p_item_type
                   AND tws.org_id = p_org_id
              UNION
              SELECT msi.segment1 item_num, primary_unit_of_measure,
             msi.inventory_item_id item_id,
             REPLACE (msi.description, CHR (9), ' ') description
        FROM mtl_system_items_b msi,TPCO_WIP_SALE_ISSUE_TEMP tws1
       WHERE msi.organization_id = tws1.org_id
         AND msi.inventory_item_id = tws1.item_id
      AND tws1.item_type = p_item_type
                   AND tws1.org_id = p_org_id;*/

      /*   AND  EXISTS  ( SELECT 'X'
                    FROM apps.bom_components_b bic,
                         apps.bom_bill_of_materials bom
                   WHERE bic.component_item_id = msi.inventory_item_id
                     AND bom.bill_sequence_id = bic.bill_sequence_id
                     AND bom.organization_id = msi.organization_id)
      -- AND msi.segment1 = '10118-C5'
      ORDER BY msi.segment1;*/
      -- AND msi.segment1 = '10118-C5'
      CURSOR c_po_price (
         p_vendor_id   IN NUMBER,
         p_item_id     IN NUMBER)
      IS
           SELECT poh.currency_code, plla.price_override
             FROM po_line_locations_all plla,
                  po_lines_all pl,
                  po_headers_all poh
            WHERE     plla.po_line_id = pl.po_line_id
                  AND poh.org_id = plla.org_id
                  AND poh.org_id = plla.org_id
                  AND poh.po_header_id = pl.po_header_id
                  AND plla.ship_to_organization_id = p_org_id
                  AND poh.vendor_id = p_vendor_id
                  AND pl.item_id = p_item_id
                  AND (poh.cancel_flag = 'N' OR poh.cancel_flag IS NULL)
                  AND (pl.cancel_flag = 'N' OR pl.cancel_flag IS NULL)
                  AND (plla.quantity - NVL (plla.quantity_cancelled, 0)) > 0
         --   AND poh.type_lookup_code <> 'BLANKET'
         ORDER BY plla.creation_date;

      CURSOR c_ir_price (
         p_destination_org_id   IN NUMBER,
         p_source_org_id        IN NUMBER,
         p_item_id              IN NUMBER)
      IS
           SELECT prl.base_unit_price, prl.currency_code currency_code
             FROM po_requisition_headers_all prh, po_requisition_lines_all prl
            WHERE     prh.org_id = prl.org_id
                  AND prh.requisition_header_id = prl.requisition_header_id
                  AND prl.item_id = p_item_id
                  --AND b.source_type_code = 'INVENTORY'
                  AND prh.type_lookup_code = 'INTERNAL'
                  AND prl.destination_organization_id = p_destination_org_id
                  AND prl.source_organization_id =
                         NVL (p_source_org_id, prl.source_organization_id)
                  --   AND TRUNC (prl.need_by_date) BETWEEN TRUNC (p_per_begin_date)
                  --   AND TRUNC (p_per_end_date)
                  AND (prl.cancel_flag = 'N' OR prl.cancel_flag IS NULL)
         ORDER BY prl.creation_date;
   BEGIN
 

      fnd_file.put_line (fnd_file.LOG, 'in the program');
      fnd_file.
       put_line (
         fnd_file.output,
            'Item Type'
         || '|'
         || 'Make or Buy Item'
         || '|'
         || 'Product Family'
         || '|'
         || 'Planner_code'
         || '|'
         || 'Oracle Part Number'
         || '|'
         || 'Description'
         || '|'
         || 'Item Status'
         || '|'
         || 'Unit of Measure'
         || '|'
         || 'Production Line'
         || '|'
         || 'Pre Processing Lead Time'
         || '|'
         || 'Processing Lead Time'
         || '|'
         || 'Post Processing Lead Time'
         || '|'
         || 'Fixed Lead Time'
         || '|'
         || 'Cumulative Total Lead Time'
         || '|'
         || 'Cum Manufacturing Lead Time'
         || '|'
         || 'Last Received Qty'
         || '|'
         || 'Minimum Order Quantity'
         || '|'
         || 'Fixed Lot Multiplier'
         || '|'
         || 'Last Received Date'
         || '|'
         || 'Last Received from Vendor'
         || '|'
         || 'Last Rcv Vendor Site Code'
         || '|'
         || 'Last Rcv Vendor State'
         || '|'
         || 'Last Rcv Vendor Country'
         || '|'
         || 'Currency'
         || '|'
         || 'Last Purchase Price'
         || '|'
         || 'Safety Stock'
         || '|'
         || 'Min Minmax Qty'
         || '|'
         || 'Max Minmax Qty'
         || '|'
         || 'Onhand Quantity'
         || '|'
         || 'Intransit Quantity'
         || '|'
         || 'Total Quantity'
         || '|'
         || 'Standard Cost'
         || '|'
         || 'Onhand Value'
         || '|'
         || ' Last Production Date'
         || '|'
         || 'Last Production Quantity'
         || '|'
         || 'YTD Useage'
         || '|'
         || TO_CHAR (TO_NUMBER (p_date) - 1)
         || '|'
         || TO_CHAR (TO_NUMBER (p_date) - 2)
         || '|'
         || 'Planning  Fence Days'
         || '|'
         || 'Demand Fence Days'
         || '|'
         || 'Planning Code');

      BEGIN
         lcnt1 := 0;

         SELECT COUNT (*)
           INTO lcnt1
           FROM tpco_wip_sale_issue
          WHERE item_type = p_item_type AND trans_year = p_date;

         IF lcnt1 = 0
         THEN
            INSERT INTO tpco_wip_sale_issue_temp (item_id,
                                                  org_id,
                                                  trans_year,
                                                  item_type,
                                                  qty)
                 SELECT mmt.inventory_item_id,
                        p_org_id,
                        p_date,
                        msi.item_type,
                        SUM (primary_quantity)
                   FROM mtl_material_transactions mmt, mtl_system_items_b msi
                  WHERE     mmt.inventory_item_id = msi.inventory_item_id
                        AND mmt.organization_id = msi.organization_id
                        AND mmt.organization_id = p_org_id
                        AND mmt.transaction_type_id IN (35, 33, 62, 34)
                        AND TO_CHAR (mmt.transaction_date, 'YYYY') = p_date
                        AND msi.item_type = p_item_type
               GROUP BY mmt.inventory_item_id,
                        p_org_id,
                        p_date,
                        msi.item_type;
         END IF;
      --  fnd_file.put_line (fnd_file.LOG,'inserted    ');
      EXCEPTION
         WHEN OTHERS
         THEN
            fnd_file.
             put_line (fnd_file.LOG, 'Error inserting to temp ' || SQLERRM);
      END;

      l_ohand_qty := 0;
      l_pur_price := 0;
      l_currency := 0;

      FOR rec1 IN c_purchased_item
      LOOP
         BEGIN
            l_prod_fam := NULL;

            SELECT segment3
              INTO l_prod_fam
              FROM apps.mtl_item_categories_v mtc
             WHERE     mtc.category_set_name = 'TPC PLANNING CATEGORY'
                   AND inventory_item_id = rec1.item_id
                   AND organization_id = p_org_id;
         EXCEPTION
            WHEN OTHERS
            THEN
               l_prod_fam := NULL;
         END;

         l_ohand_qty := 0;

         SELECT NVL (SUM (primary_transaction_quantity), 0)
           INTO l_ohand_qty
           FROM mtl_onhand_quantities_detail
          WHERE organization_id = p_org_id
                AND inventory_item_id = rec1.item_id;

         l_intransit_qty := 0;

         SELECT                                           -- CURRENT_INTRANSIT
               NVL (
                   SUM (
                      DECODE (
                         ms.intransit_owning_org_id,
                         ms.from_organization_id, inv_convert.
                                                   inv_um_convert (
                                                     ms.item_id,
                                                     NULL,
                                                     ms.quantity,
                                                     NULL,
                                                     NULL,
                                                     ms.
                                                      unit_of_measure,
                                                     msi_from.
                                                      primary_unit_of_measure),
                         ms.to_org_primary_quantity)),
                   0)
           INTO l_intransit_qty
           FROM mtl_supply ms,
                mtl_parameters mp,
                mtl_interorg_parameters mip,
                mtl_material_transactions mmt,
                rcv_shipment_lines rsl,
                mtl_system_items msi_from
          WHERE     ms.to_organization_id = p_org_id
                AND ms.item_id = rec1.item_id
                AND ms.supply_type_code IN ('SHIPMENT', 'RECEIVING')
                AND ms.destination_type_code = 'INVENTORY'
                AND mp.organization_id = ms.intransit_owning_org_id
                -- AND MS.intransit_owning_org_id = p_org_id
                AND rsl.shipment_line_id = ms.shipment_line_id
                AND mmt.transaction_id(+) = rsl.mmt_transaction_id
                AND mip.from_organization_id(+) = ms.from_organization_id
                AND mip.to_organization_id(+) = ms.to_organization_id
                AND mip.fob_point(+) =
                       DECODE (ms.intransit_owning_org_id,
                               ms.from_organization_id, 2,
                               ms.to_organization_id, 1)
                AND msi_from.inventory_item_id = ms.item_id
                AND msi_from.organization_id = ms.from_organization_id;

         l_safety_qty := NULL;

         BEGIN
            SELECT safety_stock_quantity
              INTO l_safety_qty
              FROM mtl_safety_stocks a
             WHERE a.organization_id = p_org_id
                   AND a.inventory_item_id = rec1.item_id
                   AND TRUNC (a.effectivity_date) =
                          (SELECT MAX (TRUNC (b.effectivity_date))
                             FROM mtl_safety_stocks b
                            WHERE b.organization_id = p_org_id
                                  AND b.inventory_item_id = rec1.item_id)
                   AND ROWNUM = 1;
         EXCEPTION
            WHEN OTHERS
            THEN
               l_safety_qty := 0;
         END;

         l_prod_line := NULL;

         BEGIN
            SELECT line_code
              INTO l_prod_line
              FROM apps.bom_operational_routings_v
             WHERE organization_id = p_org_id
                   AND assembly_item_id = rec1.item_id;
         --           fnd_file.put_line (fnd_file.LOG, 'prod line');
         EXCEPTION
            WHEN OTHERS
            THEN
               l_prod_line := NULL;
               NULL;
         END;

         l_total_qty := 0;
         l_total_qty := NVL (l_intransit_qty, 0) + NVL (l_ohand_qty, 0);
         -- fnd_file.put_line (fnd_file.LOG,'3');
         l_last_vendor := NULL;
         l_ltst_trans_date := NULL;
         l_last_qty := 0;
         l_cost := NULL;
         l_site_code := NULL;
         l_state := NULL;
         l_country := NULL;
         tpco_get_last_supp_add (p_org_id,
                                 rec1.item_id,
                                 l_ltst_trans_date,
                                 l_last_vendor,
                                 l_site_code,
                                 l_state,
                                 l_country,
                                 l_last_rec_qty);
         fnd_file.
          put_line (
            fnd_file.LOG,
               ' l_ltst_trans_date :'
            || l_ltst_trans_date
            || ' l_last_vendor'
            || l_last_vendor);
         l_cost := tpco_get_item_cost (p_org_id, rec1.item_id);

         -- fnd_file.put_line (fnd_file.LOG,'5');
         IF l_last_vendor IS NOT NULL
         THEN
            BEGIN
               l_name := NULL;

               SELECT organization_id
                 INTO l_name
                 FROM apps.org_organization_definitions
                WHERE organization_name = l_last_vendor;
            EXCEPTION
               WHEN OTHERS
               THEN
                  l_name := NULL;
            END;

            fnd_file.put_line (fnd_file.LOG, '7' || l_name);
            l_vendor_id := NULL;

            IF l_name IS NULL
            THEN
               fnd_file.
                put_line (
                  fnd_file.LOG,
                  '7a' || l_name || 'l_last_vendor ' || l_last_vendor);

               SELECT vendor_id
                 INTO l_vendor_id
                 FROM po_vendors
                WHERE vendor_name = l_last_vendor;

               l_last_qty := 0;

               SELECT SUM (quantity)
                 INTO l_last_qty
                 FROM rcv_transactions rt, rcv_shipment_lines rcl
                WHERE rt.shipment_line_id = rcl.shipment_line_id
                      AND rt.organization_id =
                             NVL (p_org_id, rt.organization_id)
                      AND rcl.item_id = rec1.item_id
                      AND rt.transaction_type IN ('RECEIVE')
                      AND rt.source_document_code = 'PO'
                      AND rt.vendor_id = l_vendor_id
                      AND TRUNC (rt.transaction_date) =
                             TO_DATE (l_ltst_trans_date);

               fnd_file.put_line (fnd_file.LOG, '7ab' || l_name);
               l_pur_price := 0;
               l_currency := 0;

               FOR rec2 IN c_po_price (l_vendor_id, rec1.item_id)
               LOOP
                  l_pur_price := 0;
                  l_pur_price := rec2.price_override;
                  l_currency := rec2.currency_code;
               END LOOP;
            ELSIF l_name IS NOT NULL
            THEN
               l_last_qty := 0;

               SELECT SUM (quantity)
                 INTO l_last_qty
                 FROM rcv_transactions rt, rcv_shipment_lines rcl
                WHERE rt.shipment_line_id = rcl.shipment_line_id
                      AND rt.organization_id =
                             NVL (p_org_id, rt.organization_id)
                      AND rcl.item_id = rec1.item_id
                      AND rt.transaction_type IN ('RECEIVE')
                      AND rt.source_document_code = 'REQ'
                      AND rcl.from_organization_id = l_name
                      AND TRUNC (rt.transaction_date) =
                             TO_DATE (l_ltst_trans_date);

               FOR rec3 IN c_ir_price (p_org_id, l_name, rec1.item_id)
               LOOP
                  l_pur_price := 0;
                  l_pur_price := rec3.base_unit_price;
               END LOOP;

               BEGIN
                  SELECT currency_code
                    INTO l_currency
                    FROM gl_sets_of_books glsob,
                         org_organization_definitions ood
                   WHERE glsob.set_of_books_id = ood.set_of_books_id
                         AND ood.organization_id = p_org_id;
               EXCEPTION
                  WHEN OTHERS
                  THEN
                     l_currency := NULL;
               END;
            ELSE
               l_pur_price := NULL;
               l_currency := NULL;
            END IF;
         END IF;

         IF l_last_vendor IS NULL
         THEN
            l_pur_price := NULL;
            l_currency := NULL;
         END IF;

         BEGIN
            l_wip_qty_ytd := 0;

            /*  SELECT NVL(SUM(qty),0)
                INTO  l_wip_qty_ytd
                FROM TPCO_WIP_SALE_ISSUE
                  WHERE item_id = rec1.item_id
                                    AND org_id = p_org_id
                                   AND Trans_year =   p_date;*/

            /*(  SELECT NVL (SUM (transaction_quantity), 0)
                  INTO l_wip_qty_ytd
                    FROM mtl_material_transactions mmt
                      WHERE  mmt.inventory_item_id = rec1.item_id
                           AND mmt.organization_id = p_org_id
                          AND TO_CHAR (mmt.transaction_date, 'YYYY') =   p_date
                                    AND transaction_type_id IN (35,33)  ;*/
            IF lcnt1 = 0
            THEN
               fnd_file.
                put_line (
                  fnd_file.LOG,
                  ' l_wip_qty_ytd here111:             ' || l_wip_qty_ytd);
               l_wip_qty_ytd :=
                  tpco_get_trans_quan (p_org_id, rec1.item_id, p_date);
            ELSIF lcnt1 <> 0
            THEN
               fnd_file.
                put_line (
                  fnd_file.LOG,
                  ' l_wip_qty_ytd here222222:             ' || l_wip_qty_ytd);
               l_wip_qty_ytd :=
                  tpco_get_trans_quan_prv_yr (p_org_id,
                                              rec1.item_id,
                                              TO_NUMBER (p_date));
            END IF;
         --Transaction type is WIP Component Issue
         --Source type is job or schedule
         EXCEPTION
            WHEN OTHERS
            THEN
               l_wip_qty_ytd := 0;
               NULL;
         END;

         BEGIN
            l_wip_qty_lst_yr := 0;
            l_wip_qty_lst_yr :=
               tpco_get_trans_quan_prv_yr (p_org_id,
                                           rec1.item_id,
                                           TO_NUMBER (p_date) - 1);
         /*  SELECT NVL(SUM(qty),0)
       INTO  l_wip_qty_lst_yr
       FROM TPCO_WIP_SALE_ISSUE
         WHERE item_id = rec1.item_id
                           AND org_id = p_org_id
                          AND Trans_year =   TO_NUMBER (p_date) - 1;*/

         /*  SELECT NVL (SUM (transaction_quantity), 0)
              INTO  l_wip_qty_lst_yr
                FROM mtl_material_transactions mmt
                  WHERE  mmt.inventory_item_id = rec1.item_id
                       AND mmt.organization_id = p_org_id
                      AND TO_CHAR (mmt.transaction_date, 'YYYY') =    TO_NUMBER (p_date) - 1
                                AND transaction_type_id IN (35,33)  ;   */
         --Transaction type is WIP Component Issue
         --Source type is job or schedule
         EXCEPTION
            WHEN OTHERS
            THEN
               l_wip_qty_lst_yr := 0;
               NULL;
         END;

         BEGIN
            l_wip_qty_lst_prv_yr := 0;
            l_wip_qty_lst_prv_yr :=
               tpco_get_trans_quan_prv_yr (p_org_id,
                                           rec1.item_id,
                                           TO_NUMBER (p_date) - 2);
         /*                 SELECT NVL(SUM(qty),0)
             INTO   l_wip_qty_lst_prv_yr
             FROM TPCO_WIP_SALE_ISSUE
               WHERE item_id = rec1.item_id
                                 AND org_id = p_org_id
                                AND Trans_year =   TO_NUMBER (p_date) - 2;*/

         /*       SELECT NVL (SUM (transaction_quantity), 0)
                     INTO   l_wip_qty_lst_prv_yr
                       FROM mtl_material_transactions mmt
                         WHERE  mmt.inventory_item_id = rec1.item_id
                              AND mmt.organization_id = p_org_id
                             AND TO_CHAR (mmt.transaction_date, 'YYYY') =    TO_NUMBER (p_date) - 2
                                 AND transaction_type_id IN (35,33)  ;   */
         --Transaction type is WIP Component Issue
         --Source type is job or schedule
         EXCEPTION
            WHEN OTHERS
            THEN
               l_wip_qty_lst_prv_yr := 0;
               NULL;
         END;

         BEGIN
            l_max_trans_date := NULL;
            l_max_trans_qty := 0;
            fnd_file.put_line (fnd_file.LOG, 'TPCO_GET_CONSUME_DATE BEF');
            tpco_get_consume_date (rec1.item_id,
                                   p_org_id,
                                   l_max_trans_date,
                                   l_max_trans_qty);
            fnd_file.put_line (fnd_file.LOG, 'TPCO_GET_CONSUME_DATE AFTER');
         EXCEPTION
            WHEN OTHERS
            THEN
               l_max_trans_date := NULL;
               l_max_trans_qty := 0;
               NULL;
         END;

         --     IF    (l_wip_qty_ytd <> 0)
         --      OR (l_wip_qty_lst_yr <> 0)
         --       OR (l_wip_qty_lst_prv_yr <> 0)
         --       THEN
         fnd_file.
          put_line (
            fnd_file.output,
               rec1.item_type
            || '|'
            || rec1.plan_item
            || '|'
            || l_prod_fam
            || '|'
            || rec1.planner_code
            || '|'
            || rec1.item_num
            || '|'
            || rec1.description
            || '|'
            || REC1.inventory_item_status_code
            || '|'
            || rec1.primary_unit_of_measure
            || '|'
            || l_prod_line
            || '|'
            || rec1.preprocessing_lead_time
            || '|'
            || rec1.processing_lead_time
            || '|'
            || rec1.postprocessing_lead_time
            || '|'
            || rec1.fixed_lead_time
            || '|'
            || rec1.cumulative_total_lead_time
            || '|'
            || rec1.cum_manufacturing_lead_time
            || '|'
            || l_last_qty
            || '|'
            || rec1.minimum_order_quantity
            || '|'
            || rec1.fixed_lot_multiplier
            || '|'
            || l_ltst_trans_date
            || '|'
            || l_last_vendor
            || '|'
            || l_site_code
            || '|'
            || l_state
            || '|'
            || l_country
            || '|'
            || l_currency
            || '|'
            || l_pur_price
            || '|'
            || l_safety_qty
            || '|'
            || rec1.min_minmax_quantity
            || '|'
            || rec1.max_minmax_quantity
            || '|'
            || l_ohand_qty
            || '|'
            || l_intransit_qty
            || '|'
            || l_total_qty
            || '|'
            || l_cost
            || '|'
            || NVL (l_ohand_qty, 0) * NVL (l_cost, 0)
            || '|'
            || l_max_trans_date
            || '|'
            || l_max_trans_qty
            || '|'
            || l_wip_qty_ytd * -1
            || '|'
            || l_wip_qty_lst_yr * -1
            || '|'
            || l_wip_qty_lst_prv_yr * -1
            || '|'
            || rec1.planning_time_fence_days
            || '|'
            || rec1.demand_time_fence_days
            || '|'
            || rec1.plan_code);
      --   END IF;
      END LOOP;
   EXCEPTION
      WHEN OTHERS
      THEN
         fnd_file.
          put_line (fnd_file.LOG, 'Error here             ' || SQLERRM);
   END;

   FUNCTION tpco_get_trans_quan (p_org_id    IN NUMBER,
                                 p_item_id   IN NUMBER,
                                 p_date      IN VARCHAR2)
      RETURN NUMBER
   IS
      l_wip_qty_ytd   NUMBER;
      l_date1         DATE := NULL;
      l_date2         DATE := NULL;
   BEGIN
      l_wip_qty_ytd := 0;
      --l_date1 := TO_DATE('01-JAN-'||p_date);
      --l_date2 := TO_DATE('31-DEC-'||p_date);
      /*  SELECT NVL (SUM (transaction_quantity), 0)
          INTO l_wip_qty_ytd
          FROM mtl_material_transactions mmt
         WHERE mmt.inventory_item_id = p_item_id
           AND mmt.organization_id = p_org_id
           AND transaction_type_id IN (35, 33)
           --   AND TRUNC(mmt.transaction_date) BETWEEN l_date1 AND l_date2;
           AND TO_CHAR (mmt.transaction_date, 'YYYY') = p_date;*/
      fnd_file.
       put_line (fnd_file.LOG,
                 ' in the proce' || p_org_id || 'p_date' || p_date);

      SELECT NVL (SUM (qty), 0)
        INTO l_wip_qty_ytd
        FROM tpco_wip_sale_issue_temp mmt
       WHERE     mmt.item_id = p_item_id
             AND mmt.org_id = p_org_id
             AND mmt.trans_year = p_date;

      fnd_file.
       put_line (
         fnd_file.LOG,
         ' l_wip_qty_ytd ' || l_wip_qty_ytd || 'p_item_id  ' || p_item_id);
      RETURN l_wip_qty_ytd;
   EXCEPTION
      WHEN OTHERS
      THEN
         fnd_file.
          put_line (fnd_file.LOG,
                    ' error in getting TPCO_WIP_SALE_ISSUE_TEMP ' || SQLERRM);
         l_wip_qty_ytd := 0;
         RETURN l_wip_qty_ytd;
   END;

   FUNCTION tpco_get_trans_quan_prv_yr (p_org_id    IN NUMBER,
                                        p_item_id   IN NUMBER,
                                        p_date      IN VARCHAR2)
      RETURN NUMBER
   IS
      l_wip_qty_ytd1     NUMBER;
      l_operating_unit   NUMBER;
   BEGIN
      l_wip_qty_ytd1 := 0;
      l_operating_unit := NULL;

      SELECT operating_unit
        INTO l_operating_unit
        FROM apps.org_organization_definitions
       WHERE organization_id = p_org_id;

      SELECT NVL (SUM (qty), 0)
        INTO l_wip_qty_ytd1
        FROM tpco_wip_sale_issue
       /* WHERE org_id IN (SELECT organization_id
                            FROM apps.org_organization_definitions
                           WHERE operating_unit = l_operating_unit)*/
       WHERE     org_id = p_org_id
             AND item_id = p_item_id
             AND trans_year = p_date;

      RETURN l_wip_qty_ytd1;
   EXCEPTION
      WHEN OTHERS
      THEN
         l_wip_qty_ytd1 := 0;
         RETURN l_wip_qty_ytd1;
   END;

   PROCEDURE tpco_load_mtl_txn_prv_yr (errbuff       OUT VARCHAR2,
                                       retcode       OUT VARCHAR2,
                                       p_org_id   IN     NUMBER,
                                       p_year     IN     VARCHAR2)
   IS
      l_exist       VARCHAR2 (1);
      l_oper_unit   NUMBER;
   BEGIN
      l_exist := NULL;
      l_oper_unit := NULL;

      BEGIN
         SELECT DISTINCT 'X'
           INTO l_exist
           FROM tpco_wip_sale_issue
          WHERE org_id = p_org_id AND trans_year = p_year;
      EXCEPTION
         WHEN OTHERS
         THEN
            l_exist := NULL;
      END;

      IF l_exist = 'X'
      THEN
         fnd_file.
          put_line (
            fnd_file.LOG,
            'Data Exists for this year for this Org. So deleteing the data for reloading');

         DELETE tpco_wip_sale_issue
          WHERE org_id = p_org_id AND trans_year = p_year;
      END IF;

      BEGIN
         SELECT operating_unit
           INTO l_oper_unit
           FROM apps.org_organization_definitions
          WHERE organization_id = p_org_id;
      EXCEPTION
         WHEN OTHERS
         THEN
            l_oper_unit := 161;
      END;

      IF l_oper_unit = 161
      THEN
         INSERT INTO tpco_wip_sale_issue (item_id,
                                          org_id,
                                          trans_year,
                                          item_type,
                                          qty)
              SELECT mmt.inventory_item_id,
                     p_org_id,
                     p_year,
                     msi.item_type,
                     SUM (primary_quantity)
                FROM mtl_material_transactions mmt, mtl_system_items_b msi
               WHERE     mmt.inventory_item_id = msi.inventory_item_id
                     AND mmt.organization_id = msi.organization_id
                     AND mmt.organization_id = p_org_id
                     --   AND mmt.transaction_type_id IN (35, 33,34,62)
                     AND mmt.transaction_type_id IN (35, 33)
                     -- Kieth wants for the previous years only sales and wip issue for previous years
                     AND TO_CHAR (mmt.transaction_date, 'YYYY') = p_year
                     AND msi.item_type IN ('P', 'FG', 'SA')
            GROUP BY mmt.inventory_item_id,
                     p_org_id,
                     p_year,
                     item_type;
      ELSE
         INSERT INTO tpco_wip_sale_issue (item_id,
                                          org_id,
                                          trans_year,
                                          item_type,
                                          qty)
              SELECT mmt.inventory_item_id,
                     p_org_id,
                     p_year,
                     msi.item_type,
                     SUM (primary_quantity)
                FROM mtl_material_transactions mmt, mtl_system_items_b msi
               WHERE     mmt.inventory_item_id = msi.inventory_item_id
                     AND mmt.organization_id = msi.organization_id
                     AND mmt.organization_id = p_org_id
                     AND mmt.transaction_type_id IN (35, 33, 34, 62)
                     --   AND mmt.transaction_type_id IN (35, 33)-- Kieth wants for the previous years only sales and wip issue for previous years
                     AND TO_CHAR (mmt.transaction_date, 'YYYY') = p_year
                     AND msi.item_type IN ('P', 'FG', 'SA')
            GROUP BY mmt.inventory_item_id,
                     p_org_id,
                     p_year,
                     item_type;
      END IF;

      COMMIT;
   EXCEPTION
      WHEN OTHERS
      THEN
         fnd_file.
          put_line (fnd_file.LOG, 'Error loading the table' || SQLERRM);
         ROLLBACK;
   END;
END tpco_supp_release_stat;
/

Author

Commented:
/* Formatted on 11/29/2011 11:41:27 AM (QP5 v5.149.1003.31008) */
CREATE OR REPLACE PACKAGE BODY BOLINF.tpco_supp_release_stat
AS
   /******************************************************************************
      NAME:       TPCO_SUPP_RELEASE_STAT
      PURPOSE:

      REVISIONS:
      Ver        Date        Author           Description
      ---------  ----------  ---------------  ------------------------------------
      1.0        6/20/2008             1. Poornima Hegde
      1.3        8/1/2008                         Poornima Hegde -Changes to fit in additional requirements by Keith
      1.4        8/20/2008        Poornima Hegde           Added few additional fields
      1.5       2/23/2009          Poornima Hegde   Removed IS/IR from previous years
                 26/1/2010          Poornima Hegde    Safety stock issue fixed
      1.6                         Poornima Hegde   All item types added other than the inactive
                9/27/10           Poornima Hegde     Us past transaction for the org only keith mond change
   ******************************************************************************/
   /*Assumption while coding: This code brings the item and supplier in formation for the open orders based on the input date parmeter.
   Input date parameter is compared against the need by date of POs.
   The date range for  the open orders info is from 6 months from the begining Monday of the input date parameter
   */
   PROCEDURE tpco_get_pur_dtl_all_org (errbuff       OUT VARCHAR2,
                                       retcode       OUT VARCHAR2,
                                       p_global   IN     VARCHAR2,
                                       p_org_id   IN     NUMBER,
                                       p_date     IN     VARCHAR2)
   IS
      CURSOR c1
      IS
         SELECT organization_id FROM apps.org_organization_definitions;

      l_begin_weekdate   DATE;
      l_end_date         DATE;
      l_date             DATE;
      l_from_ord_date    DATE;
      l_end_ord_date     DATE;
      l_shipped_sales    NUMBER;
   BEGIN
      fnd_file.put_line (fnd_file.LOG, 'TPCO Supplier Release Info');

      IF p_global = 'Y'
      THEN
         FOR rec1 IN c1
         LOOP
            --   tpco_get_purchase_dtl (rec1.organization_id, p_date);
            tpco_get_purchase_itm_dtl (rec1.organization_id, p_date);
         END LOOP;
      ELSE
         fnd_file.put_line (fnd_file.LOG, '1');
         -- tpco_get_purchase_dtl (p_org_id, p_date);
         tpco_get_purchase_itm_dtl (p_org_id, p_date);
      END IF;
   EXCEPTION
      WHEN OTHERS
      THEN
         retcode := 2;
         fnd_file.put_line (fnd_file.LOG, 'Error:      ' || SQLERRM);
   END;

   FUNCTION tpco_get_open_po_ord_qty (p_org_id           IN NUMBER,
                                      p_item_id          IN NUMBER,
                                      p_vendor_id        IN NUMBER,
                                      p_vendor_site_id   IN NUMBER,
                                      p_req_from_date    IN DATE,
                                      p_req_to_date      IN DATE)
      RETURN NUMBER
   IS
      l_ord_qty   NUMBER := 0;
   BEGIN
      l_ord_qty := 0;

      SELECT NVL (
                SUM (
                     plla.quantity
                   - NVL (plla.quantity_cancelled, 0)
                   - NVL (plla.quantity_received, 0)),
                0)
                qty_ordered
        INTO l_ord_qty
        FROM po_line_locations_all plla, po_lines_all pl, po_headers_all poh
       WHERE     plla.po_line_id = pl.po_line_id
             AND NVL (poh.closed_code, 'OPEN') = 'OPEN'
             AND NVL (pl.closed_code, 'OPEN') = 'OPEN'
             AND NVL (plla.closed_code, 'OPEN') = 'OPEN'
             AND poh.org_id = plla.org_id
             AND poh.org_id = plla.org_id
             AND poh.po_header_id = pl.po_header_id
             AND plla.ship_to_organization_id = p_org_id
             AND poh.vendor_id = p_vendor_id
             AND poh.vendor_site_id = p_vendor_site_id
             AND pl.item_id = p_item_id
             AND (poh.cancel_flag = 'N' OR poh.cancel_flag IS NULL)
             AND (pl.cancel_flag = 'N' OR pl.cancel_flag IS NULL)
             AND TRUNC (NVL (plla.need_by_date, plla.promised_date)) BETWEEN TRUNC (
                                                                                TO_DATE (
                                                                                   p_req_from_date))
                                                                         AND TRUNC (
                                                                                TO_DATE (
                                                                                   p_req_to_date))
             AND plla.quantity > NVL (plla.quantity_received, 0);

      --    AND poh.type_lookup_code <> 'BLANKET';
      --  fnd_file.put_line (fnd_file.LOG, 'l_ord_qty  ' || l_ord_qty);
      RETURN l_ord_qty;
   EXCEPTION
      WHEN OTHERS
      THEN
         l_ord_qty := 0;
   END;

   PROCEDURE tpco_get_purchase_itm_dtl (p_org_id   IN NUMBER,
                                        p_date     IN VARCHAR2)
   IS
      l_begin_weekdate           DATE;
      l_end_date                 DATE;
      l_date                     DATE;
      l_from_ord_date            DATE;
      l_end_ord_date             DATE;
      l_shipped_sales            NUMBER;
      l_6mth_date                DATE;
      l_org_name                 apps.org_organization_definitions.organization_name%TYPE;
      l_intransit_qty            NUMBER;
      l_ohand_qty                NUMBER;
      l_total_qty                NUMBER;
      l_vendor_id                po_vendors.vendor_id%TYPE;
      l_vendor_site_id           po_vendor_sites_all.vendor_site_id%TYPE;
      l_vendor_name              po_vendors.vendor_name%TYPE;
      l_vendor_site              po_vendor_sites_all.vendor_site_code%TYPE;
      l_city                     po_vendor_sites_all.city%TYPE;
      l_state                    po_vendor_sites_all.state%TYPE;
      l_country                  po_vendor_sites_all.country%TYPE;
      l_pur_price                NUMBER;
      l_currency                 po_headers_all.currency_code%TYPE;
      l_uom                      mtl_system_items_b.primary_unit_of_measure%TYPE;
      l_qty_recvd                NUMBER;
      l_qty_recvd_last_yr        NUMBER;
      l_ltst_trans_date          VARCHAR2 (50);
      l_wk1_qty                  NUMBER;
      l_wk2_qty                  NUMBER;
      l_wk3_qty                  NUMBER;
      l_wk4_qty                  NUMBER;
      l_wk5_qty                  NUMBER;
      l_wk6_qty                  NUMBER;
      l_wk7_qty                  NUMBER;
      l_wk8_qty                  NUMBER;
      l_mth_begin_date           DATE;
      l_mth1_qty                 NUMBER;
      l_mth2_qty                 NUMBER;
      l_mth3_qty                 NUMBER;
      l_ord_qty_bf               NUMBER;
      l_safety_qty               NUMBER;
      l_minimum_order_quantity   mtl_system_items_b.minimum_order_quantity%TYPE;
      l_maximum_order_quantity   mtl_system_items_b.maximum_order_quantity%TYPE;
      l_unit_weight              mtl_system_items_b.unit_weight%TYPE;
      l_fixed_lead_time          mtl_system_items_b.fixed_lead_time%TYPE;
      l_variable_lead_time       mtl_system_items_b.variable_lead_time%TYPE;
      l_fixed_lot_multiplier     mtl_system_items_b.fixed_lot_multiplier%TYPE;
      l_qty_recvd_last_yr2       NUMBER;
      l_type_flag                VARCHAR2 (2);
      l_dest_org_id              NUMBER;
      lcnt                       NUMBER;
      l_last_vendor              po_vendors.vendor_name%TYPE;
      l_total_openorders         NUMBER;
      l_cost                     NUMBER;
      l_lst_vendor_site_code     VARCHAR2 (50);
      l_lst_vendor_state         VARCHAR2 (50);
      l_lst_vendor_country       VARCHAR2 (50);
      l_last_qty                 NUMBER;

      CURSOR c_purchased_item_c
      IS
           SELECT msi.segment1 item_num,
                  minimum_order_quantity,
                  maximum_order_quantity,
                  unit_weight,
                  fixed_lead_time,
                  variable_lead_time,
                  fixed_lot_multiplier,
                  full_lead_time full_lead_time,
                  primary_unit_of_measure,
                  msi.inventory_item_id item_id,
                  msi.item_type item_type,
                  REPLACE (msi.description, CHR (9), ' ') description,
                  msi.planner_code
             FROM mtl_system_items_b msi
            WHERE (msi.planning_make_buy_code = 2
                   OR msi.purchasing_item_flag = 'Y')
                  --  WHERE msi.item_type = 'P'
                  --AND MSI.INVENTORY_ITEM_ID IN (71142,75725)
                  --     AND msi.inventory_item_status_code = 'Active'
                  AND msi.organization_id = p_org_id
         -- AND msi.segment1 = '10118-C5'
         ORDER BY msi.segment1;

      CURSOR c_po_price (
         p_vendor_id        IN NUMBER,
         p_vendor_site_id   IN NUMBER,
         p_item_id          IN NUMBER,
         p_per_begin_date   IN DATE,
         p_per_end_date     IN DATE)
      IS
           SELECT poh.currency_code, plla.price_override
             FROM po_line_locations_all plla,
                  po_lines_all pl,
                  po_headers_all poh
            WHERE     plla.po_line_id = pl.po_line_id
                  AND poh.org_id = plla.org_id
                  AND poh.org_id = plla.org_id
                  AND poh.po_header_id = pl.po_header_id
                  AND plla.ship_to_organization_id = p_org_id
                  AND poh.vendor_id = p_vendor_id
                  AND poh.vendor_site_id = p_vendor_site_id
                  AND pl.item_id = p_item_id
                  AND (poh.cancel_flag = 'N' OR poh.cancel_flag IS NULL)
                  AND (pl.cancel_flag = 'N' OR pl.cancel_flag IS NULL)
                  AND (plla.quantity - NVL (plla.quantity_cancelled, 0)) > 0
                  AND TRUNC (NVL (plla.need_by_date, plla.promised_date)) BETWEEN TRUNC (
                                                                                     p_per_begin_date)
                                                                              AND TRUNC (
                                                                                     p_per_end_date)
         --   AND poh.type_lookup_code <> 'BLANKET'
         ORDER BY plla.creation_date;

      CURSOR c_ir_price (
         p_destination_org_id   IN NUMBER,
         p_source_org_id        IN NUMBER,
         p_item_id              IN NUMBER,
         p_per_begin_date       IN DATE,
         p_per_end_date         IN DATE)
      IS
           SELECT prl.base_unit_price, prl.currency_code currency_code
             FROM po_requisition_headers_all prh, po_requisition_lines_all prl
            WHERE     prh.org_id = prl.org_id
                  AND prh.requisition_header_id = prl.requisition_header_id
                  AND prl.item_id = p_item_id
                  --AND b.source_type_code = 'INVENTORY'
                  AND prh.type_lookup_code = 'INTERNAL'
                  AND prl.destination_organization_id = p_destination_org_id
                  AND prl.source_organization_id =
                         NVL (l_vendor_id, prl.source_organization_id)
                  AND TRUNC (prl.need_by_date) BETWEEN TRUNC (p_per_begin_date)
                                                   AND TRUNC (p_per_end_date)
                  AND (prl.cancel_flag = 'N' OR prl.cancel_flag IS NULL)
         ORDER BY prl.creation_date;
   BEGIN
      fnd_file.new_line (fnd_file.LOG, 2);
      fnd_file.
       put_line (
         fnd_file.LOG,
         '**********************************************************************');
      fnd_file.put_line (fnd_file.LOG, 'TPCO SUPPLIER RELEASE INFORMATION');
      fnd_file.
       put_line (
         fnd_file.LOG,
         'ORG_ID:    ' || p_org_id || '        Date:        ' || p_date);
      l_date := TO_DATE (p_date, 'DD-MM-YYYY');

      BEGIN
         --The below query gets Monday of the week they enter.week1 always starts from Monday and we add 6 days to it to get the week end date
         SELECT TO_DATE (
                   DECODE (
                      RTRIM (TO_CHAR (l_date, 'DAY')),
                      'THURSDAY', TO_CHAR ( (l_date - 3), 'DD-MM-YYYY'),
                      'FRIDAY', TO_CHAR ( (l_date - 4), 'DD-MM-YYYY'),
                      'SATURDAY', TO_CHAR ( (l_date - 5), 'DD-MM-YYYY'),
                      'SUNDAY', TO_CHAR ( (l_date - 6), 'DD-MM-YYYY'),
                      'MONDAY', TO_CHAR ( (l_date - 0), 'DD-MM-YYYY'),
                      'TUESDAY', TO_CHAR ( (l_date - 1), 'DD-MM-YYYY'),
                      'WEDNESDAY', TO_CHAR ( (l_date - 4), 'DD-MM-YYYY')),
                   'DD-MM-YYYY')
           INTO l_begin_weekdate
           FROM DUAL;
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            fnd_file.put_line (fnd_file.LOG, 'error1   ' || SQLERRM);
            l_begin_weekdate := SYSDATE;
            NULL;
         WHEN OTHERS
         THEN
            fnd_file.put_line (fnd_file.LOG, 'error12 ' || SQLERRM);
            l_begin_weekdate := SYSDATE;
            NULL;
      END;

      fnd_file.
       put_line (fnd_file.LOG, 'l_begin_weekdate     ' || l_begin_weekdate);
      l_end_date := l_begin_weekdate + 6;
      l_6mth_date := l_begin_weekdate + 146;
      fnd_file.put_line (fnd_file.LOG, 'l_6mth_date     ' || l_6mth_date);

      BEGIN
         l_org_name := NULL;

         SELECT organization_code
           INTO l_org_name
           FROM apps.org_organization_definitions
          WHERE organization_id = p_org_id;
      EXCEPTION
         WHEN OTHERS
         THEN
            l_org_name := NULL;
      END;

      fnd_file.
       put_line (
         fnd_file.output,
            'Organization:    '
         || l_org_name
         || '        Date:        '
         || p_date);
      fnd_file.
       put_line (
         fnd_file.output,
            'Supplier Site'
         || '|'
         || 'Supplier'
         || '|'
         || 'City'
         || '|'
         || 'State'
         || '|'
         || 'Country'
         || '|'
         || 'Oracle Part Number'
         || '|'
         || 'Item Type'
         || '|'
         || 'Desc'
         || '|'
         || 'Planner Code'
         || '|'
         || 'Standard Cost'
         || '|'
         || 'Purchase Price'
         || '|'
         || 'Currency'
         || '|'
         || 'Unit OF Measure'
         || '|'
         || 'Safety Stock'
         || '|'
         || 'Standard Pack'
         || '|'
         || 'MIN Qty'
         || '|'
         || 'MAX Qty'
         || '|'
         || 'Full Lead Time'
         || '|'
         || 'IN-transit Inventory'
         || '|'
         || 'ON Hand Inventory'
         || '|'
         || 'Total Inventory'
         || '|'
         || ' Total Open Orders'
         || '|'
         || 'Past Due Orders'
         || '|'
         || 'WK1'
         || '|'
         || 'WK2'
         || '|'
         || 'WK3'
         || '|'
         || 'WK4'
         || '|'
         || 'WK5'
         || '|'
         || 'WK6'
         || '|'
         || 'WK7'
         || '|'
         || 'WK8'
         || '|'
         || 'MONTH 3'
         || '|'
         || 'MONTH 4'
         || '|'
         || 'MONTH 5'
         || '|'
         || 'Last Received Date'
         || '|'
         || 'Last Received from Vendor'
         || '|'
         || 'Last Received Quantity'
         || '|'
         || 'YTD Received'
         || '|'
         || TO_CHAR (TO_NUMBER (TO_CHAR (l_date, 'YYYY')) - 1)
         || ' Received'
         || '|'
         || TO_CHAR (TO_NUMBER (TO_CHAR (l_date, 'YYYY')) - 2)
         || ' Received');

      BEGIN
         l_wk1_qty := 0;
         l_wk2_qty := 0;
         l_wk3_qty := 0;
         l_wk4_qty := 0;
         l_wk5_qty := 0;
         l_wk6_qty := 0;
         l_wk7_qty := 0;
         l_wk8_qty := 0;
         l_mth1_qty := 0;
         l_mth2_qty := 0;
         l_mth3_qty := 0;
         l_ord_qty_bf := 0;
         l_vendor_name := NULL;
         l_vendor_site := NULL;
         l_city := NULL;
         l_state := NULL;
         l_country := NULL;
         l_pur_price := NULL;
         l_currency := NULL;
         l_total_openorders := 0;
         l_cost := 0;
         l_lst_vendor_site_code := NULL;
         l_lst_vendor_state := NULL;
         l_lst_vendor_country := NULL;
         l_last_qty := NULL;

         INSERT INTO tpco_supp_rel_global_temp (vendor_id,
                                                vendor_site_id,
                                                item_id,
                                                org_id,
                                                type_flag)
              SELECT poh.vendor_id,
                     poh.vendor_site_id,
                     pl.item_id,
                     p_org_id,
                     'ES'
                FROM po_line_locations_all plla,
                     po_lines_all pl,
                     po_headers_all poh
               WHERE     plla.po_line_id = pl.po_line_id
                     AND poh.org_id = plla.org_id
                     AND poh.org_id = plla.org_id
                     AND poh.po_header_id = pl.po_header_id
                     AND plla.ship_to_organization_id = p_org_id
                     AND (poh.cancel_flag = 'N' OR poh.cancel_flag IS NULL)
                     AND (pl.cancel_flag = 'N' OR pl.cancel_flag IS NULL)
                     AND TRUNC (NVL (plla.need_by_date, promised_date)) BETWEEN TRUNC (
                                                                                   TO_DATE (
                                                                                      l_begin_weekdate))
                                                                            AND TRUNC (
                                                                                   TO_DATE (
                                                                                      l_6mth_date))
                     AND plla.quantity > NVL (plla.quantity_received, 0)
                     AND (plla.quantity - NVL (plla.quantity_cancelled, 0)) > 0
                     --      AND poh.type_lookup_code <> 'BLANKET'
                     AND poh.vendor_id IS NOT NULL
                     AND pl.item_id IS NOT NULL
            -- AND POH.VENDOR_ID =17956
            --  AND pl.item_id = 83006
            GROUP BY poh.vendor_id,
                     poh.vendor_site_id,
                     p_org_id,
                     pl.item_id,
                     'ES';
      EXCEPTION
         WHEN OTHERS
         THEN
            fnd_file.put_line (fnd_file.LOG, 'ERROR  ' || SQLERRM);
            NULL;
      END;

      fnd_file.put_line (fnd_file.LOG, '1');

      BEGIN
         INSERT INTO tpco_supp_rel_global_temp (vendor_id,
                                                vendor_site_id,
                                                item_id,
                                                org_id,
                                                type_flag)
              SELECT pla.source_organization_id,
                     NULL,
                     item_id,
                     pla.destination_organization_id v,
                     'IS'
                FROM po_requisition_lines_all pla,
                     po_requisition_headers_all prb
               WHERE pla.requisition_header_id = prb.requisition_header_id
                     AND pla.destination_organization_id = p_org_id
                     -- AND pla.destination_ORGANIZATION_ID  = 17956
                     AND TRUNC (pla.need_by_date) BETWEEN TRUNC (
                                                             TO_DATE (
                                                                l_begin_weekdate))
                                                      AND TRUNC (
                                                             TO_DATE (
                                                                l_6mth_date))
                     AND pla.quantity > NVL (pla.quantity_received, 0)
                     AND (pla.quantity - NVL (pla.quantity_cancelled, 0)) > 0
                     AND (pla.cancel_flag = 'N' OR pla.cancel_flag IS NULL)
                     AND prb.type_lookup_code = 'INTERNAL'
                     AND pla.item_id IS NOT NULL
            --   AND pla.item_id = 87980
            GROUP BY pla.source_organization_id,
                     NULL,
                     pla.item_id,
                     pla.destination_organization_id,
                     'IS';
      EXCEPTION
         WHEN OTHERS
         THEN
            fnd_file.put_line (fnd_file.LOG, 'ERROR  ' || SQLERRM);
            NULL;
      END;

      SELECT COUNT (*) INTO lcnt FROM tpco_supp_rel_global_temp;

      fnd_file.put_line (fnd_file.LOG, '2' || 'lcnt ' || lcnt);

      FOR rec1 IN c_purchased_item_c
      LOOP
         l_intransit_qty := 0;
         l_cost := 0;
         l_cost := tpco_get_item_cost (p_org_id, rec1.item_id);

         SELECT                                           -- CURRENT_INTRANSIT
               NVL (
                   SUM (
                      DECODE (
                         ms.intransit_owning_org_id,
                         ms.from_organization_id, inv_convert.
                                                   inv_um_convert (
                                                     ms.item_id,
                                                     NULL,
                                                     ms.quantity,
                                                     NULL,
                                                     NULL,
                                                     ms.
                                                      unit_of_measure,
                                                     msi_from.
                                                      primary_unit_of_measure),
                         ms.to_org_primary_quantity)),
                   0)
           INTO l_intransit_qty
           FROM mtl_supply ms,
                mtl_parameters mp,
                mtl_interorg_parameters mip,
                mtl_material_transactions mmt,
                rcv_shipment_lines rsl,
                mtl_system_items msi_from
          WHERE     ms.to_organization_id = p_org_id
                AND ms.item_id = rec1.item_id
                AND ms.supply_type_code IN ('SHIPMENT', 'RECEIVING')
                AND ms.destination_type_code = 'INVENTORY'
                AND mp.organization_id = ms.intransit_owning_org_id
                -- AND MS.intransit_owning_org_id = p_org_id
                AND rsl.shipment_line_id = ms.shipment_line_id
                AND mmt.transaction_id(+) = rsl.mmt_transaction_id
                AND mip.from_organization_id(+) = ms.from_organization_id
                AND mip.to_organization_id(+) = ms.to_organization_id
                AND mip.fob_point(+) =
                       DECODE (ms.intransit_owning_org_id,
                               ms.from_organization_id, 2,
                               ms.to_organization_id, 1)
                AND msi_from.inventory_item_id = ms.item_id
                AND msi_from.organization_id = ms.from_organization_id;

         --On Hand quantity
         l_ohand_qty := 0;

         SELECT NVL (SUM (primary_transaction_quantity), 0)
           INTO l_ohand_qty
           FROM mtl_onhand_quantities_detail
          WHERE organization_id = p_org_id
                AND inventory_item_id = rec1.item_id;

         l_total_qty := 0;
         l_total_qty := l_ohand_qty + l_intransit_qty;
         l_type_flag := NULL;
         l_vendor_id := NULL;
         l_vendor_site_id := NULL;
         l_dest_org_id := NULL;

         --  fnd_file.put_line (fnd_file.LOG, 'rec1.item_id;   '||rec1.item_id);
         BEGIN
            SELECT vendor_id,
                   vendor_site_id,
                   type_flag,
                   org_id
              INTO l_vendor_id,
                   l_vendor_site_id,
                   l_type_flag,
                   l_dest_org_id
              FROM tpco_supp_rel_global_temp
             WHERE org_id = p_org_id AND item_id = rec1.item_id;
         EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
               l_vendor_id := NULL;
               l_vendor_site_id := NULL;
            WHEN OTHERS
            THEN
               NULL;
         END;

         /*        fnd_file.put_line (fnd_file.LOG,
                                       'type_flag '
                                    || l_type_flag
                                    || 'lvednorid   '
                                    || l_vendor_id
                                   );*/
         l_qty_recvd := 0;
         l_qty_recvd_last_yr := 0;
         l_qty_recvd_last_yr2 := 0;
         l_ltst_trans_date := 0;
         l_vendor_name := NULL;
         l_vendor_site := NULL;
         l_city := NULL;
         l_state := NULL;
         l_country := NULL;
         l_ltst_trans_date := NULL;
         l_pur_price := NULL;
         l_currency := NULL;
         l_lst_vendor_site_code := NULL;
         l_lst_vendor_state := NULL;
         l_lst_vendor_country := NULL;
         l_last_qty := NULL;

         IF l_vendor_id IS NOT NULL AND l_type_flag = 'ES'
         THEN
            SELECT po.vendor_name,
                   pos.vendor_site_code,
                   pos.city,
                   NVL (pos.state, pos.province),
                   pos.country
              INTO l_vendor_name,
                   l_vendor_site,
                   l_city,
                   l_state,
                   l_country
              FROM po_vendors po, po_vendor_sites_all pos
             WHERE     po.vendor_id = pos.vendor_id
                   AND po.vendor_id = l_vendor_id
                   AND pos.vendor_site_id = l_vendor_site_id;

            /* fnd_file.put_line (fnd_file.LOG,
                                        'week1 '
                                     || l_begin_weekdate
                                     || '          '
                                     || l_end_date
                                    );*/
            l_wk1_qty := 0;
            l_wk2_qty := 0;
            l_wk3_qty := 0;
            l_wk4_qty := 0;
            l_wk5_qty := 0;
            l_wk6_qty := 0;
            l_wk7_qty := 0;
            l_wk8_qty := 0;
            l_mth1_qty := 0;
            l_mth2_qty := 0;
            l_mth3_qty := 0;
            l_ord_qty_bf := 0;
            l_wk1_qty :=
               tpco_get_open_po_ord_qty (p_org_id,
                                         rec1.item_id,
                                         l_vendor_id,
                                         l_vendor_site_id,
                                         l_begin_weekdate,
                                         l_end_date);
            /*      fnd_file.put_line (fnd_file.LOG,
                                     'week1 ' || l_begin_weekdate || '  '
                                     || l_end_date
                                    );*/
            l_wk2_qty :=
               tpco_get_open_po_ord_qty (p_org_id,
                                         rec1.item_id,
                                         l_vendor_id,
                                         l_vendor_site_id,
                                         l_end_date + 1,
                                         (l_end_date + 1 + 6));
            /*     fnd_file.put_line (fnd_file.LOG,
                                       'week2 '
                                    || TO_CHAR (l_end_date + 1)
                                    || '  '
                                    || TO_CHAR ((l_end_date + 1 + 6))
                                   );*/
            l_wk3_qty :=
               tpco_get_open_po_ord_qty (p_org_id,
                                         rec1.item_id,
                                         l_vendor_id,
                                         l_vendor_site_id,
                                         l_end_date + 8,
                                         (l_end_date + 8 + 6));
            --  fnd_file.put_line (fnd_file.LOG, 'week3 ' ||  TO_CHAR(l_end_date + 8)||'  '||TO_CHAR((l_end_date + 8 + 6)));
            l_wk4_qty :=
               tpco_get_open_po_ord_qty (p_org_id,
                                         rec1.item_id,
                                         l_vendor_id,
                                         l_vendor_site_id,
                                         l_end_date + 15,
                                         (l_end_date + 15 + 6));
            --fnd_file.put_line (fnd_file.LOG, 'week4 ' || TO_CHAR(l_end_date + 15)||'  '||TO_CHAR((l_end_date + 15 + 6)));
            l_wk5_qty :=
               tpco_get_open_po_ord_qty (p_org_id,
                                         rec1.item_id,
                                         l_vendor_id,
                                         l_vendor_site_id,
                                         l_end_date + 22,
                                         (l_end_date + 22 + 6));
            --  fnd_file.put_line (fnd_file.LOG, 'week5 ' || TO_CHAR(l_end_date + 22)||'  '||TO_CHAR((l_end_date + 22 + 6)));
            l_wk6_qty :=
               tpco_get_open_po_ord_qty (p_org_id,
                                         rec1.item_id,
                                         l_vendor_id,
                                         l_vendor_site_id,
                                         l_end_date + 29,
                                         (l_end_date + 29 + 6));
            -- fnd_file.put_line (fnd_file.LOG, 'week6 ' ||TO_CHAR( l_end_date + 29)||'  '|| TO_CHAR((l_end_date + 29 + 6)));
            l_wk7_qty :=
               tpco_get_open_po_ord_qty (p_org_id,
                                         rec1.item_id,
                                         l_vendor_id,
                                         l_vendor_site_id,
                                         l_end_date + 36,
                                         (l_end_date + 36 + 6));
            -- fnd_file.put_line (fnd_file.LOG, 'week7 ' || TO_CHAR(l_end_date + 36)||'  '||  TO_CHAR((l_end_date + 36 + 6)));
            l_wk8_qty :=
               tpco_get_open_po_ord_qty (p_org_id,
                                         rec1.item_id,
                                         l_vendor_id,
                                         l_vendor_site_id,
                                         l_end_date + 43,
                                         (l_end_date + 43 + 6));
            /* fnd_file.put_line (fnd_file.LOG,
                                   'week8 '
                                || TO_CHAR (l_end_date + 43)
                                || '  '
                                || TO_CHAR ((l_end_date + 43 + 6))
                               );*/
            l_mth_begin_date := l_end_date + 50;
            l_mth1_qty :=
               tpco_get_open_po_ord_qty (p_org_id,
                                         rec1.item_id,
                                         l_vendor_id,
                                         l_vendor_site_id,
                                         l_mth_begin_date,
                                         (l_mth_begin_date + 30));
            /*  fnd_file.put_line (fnd_file.LOG,
                                    'mn1 '
                                 || TO_CHAR (l_mth_begin_date)
                                 || '  '
                                 || TO_CHAR ((l_mth_begin_date + 30))
                                );*/
            l_mth2_qty :=
               tpco_get_open_po_ord_qty (p_org_id,
                                         rec1.item_id,
                                         l_vendor_id,
                                         l_vendor_site_id,
                                         l_mth_begin_date + 31,
                                         (l_mth_begin_date + 61));
            /*     fnd_file.put_line (fnd_file.LOG,
                                        'mn2 '
                                     || TO_CHAR (l_mth_begin_date + 31)
                                     || '  '
                                     || TO_CHAR (l_mth_begin_date + 61)
                                    );*/
            l_mth3_qty :=
               tpco_get_open_po_ord_qty (p_org_id,
                                         rec1.item_id,
                                         l_vendor_id,
                                         l_vendor_site_id,
                                         (l_mth_begin_date + 62),
                                         (l_mth_begin_date + 92));

            BEGIN
               l_ord_qty_bf := 0;

               SELECT NVL (
                         SUM (
                              plla.quantity
                            - NVL (plla.quantity_cancelled, 0)
                            - NVL (plla.quantity_received, 0)),
                         0)
                         qty_ordered
                 INTO l_ord_qty_bf
                 FROM po_line_locations_all plla,
                      po_lines_all pl,
                      po_headers_all poh
                WHERE     plla.po_line_id = pl.po_line_id
                      AND poh.org_id = plla.org_id
                      AND NVL (poh.closed_code, 'OPEN') = 'OPEN'
                      AND NVL (pl.closed_code, 'OPEN') = 'OPEN'
                      AND NVL (plla.closed_code, 'OPEN') = 'OPEN'
                      AND poh.org_id = plla.org_id
                      AND poh.po_header_id = pl.po_header_id
                      AND plla.ship_to_organization_id = p_org_id
                      AND poh.vendor_id = l_vendor_id
                      AND poh.vendor_site_id = l_vendor_site_id
                      AND pl.item_id = rec1.item_id
                      AND (poh.cancel_flag = 'N' OR poh.cancel_flag IS NULL)
                      AND (pl.cancel_flag = 'N' OR pl.cancel_flag IS NULL)
                      AND TRUNC (NVL (plla.need_by_date, plla.promised_date)) <
                             TRUNC (TO_DATE (l_begin_weekdate))
                      AND plla.quantity > NVL (plla.quantity_received, 0);
            EXCEPTION
               WHEN OTHERS
               THEN
                  l_ord_qty_bf := 0;
                  NULL;
            END;

            l_pur_price := NULL;
            l_currency := NULL;

            FOR rec_price IN c_po_price (l_vendor_id,
                                         l_vendor_site_id,
                                         rec1.item_id,
                                         l_begin_weekdate,
                                         (l_begin_weekdate + 146))
            LOOP
               --fnd_file.put_line (fnd_file.LOG, 'recprice loop');
               l_pur_price := rec_price.price_override;
               l_currency := rec_price.currency_code;
            END LOOP;
         END IF;

         IF l_type_flag IS NULL
         THEN
            l_wk1_qty := 0;
            l_wk2_qty := 0;
            l_wk3_qty := 0;
            l_wk4_qty := 0;
            l_wk5_qty := 0;
            l_wk6_qty := 0;
            l_wk7_qty := 0;
            l_wk8_qty := 0;
            l_mth1_qty := 0;
            l_mth2_qty := 0;
            l_mth3_qty := 0;
            l_ord_qty_bf := 0;
         END IF;

         IF l_vendor_id IS NOT NULL AND l_type_flag = 'IS'
         THEN
            l_vendor_name := NULL;
            l_vendor_site := NULL;
            l_city := NULL;
            l_state := NULL;
            l_country := NULL;

            SELECT organization_name
              INTO l_vendor_name
              FROM apps.org_organization_definitions
             WHERE organization_id = l_vendor_id;

            -- fnd_file.put_line (fnd_file.LOG, '4 '||l_vendor_name);
            /*


                /* fnd_file.put_line (fnd_file.LOG,
                                            'week1 '
                                         || l_begin_weekdate
                                         || '          '
                                         || l_end_date
                                        );*/
            l_wk1_qty := 0;
            l_wk2_qty := 0;
            l_wk3_qty := 0;
            l_wk4_qty := 0;
            l_wk5_qty := 0;
            l_wk6_qty := 0;
            l_wk7_qty := 0;
            l_wk8_qty := 0;
            l_mth1_qty := 0;
            l_mth2_qty := 0;
            l_mth3_qty := 0;
            l_ord_qty_bf := 0;
            l_wk1_qty :=
               tpco_get_open_ir_ord_qty (rec1.item_id,
                                         l_vendor_id,
                                         l_dest_org_id,
                                         l_begin_weekdate,
                                         l_end_date);
            --   fnd_file.put_line (fnd_file.LOG, 'week1 ' || l_begin_weekdate||'  '||l_end_date);
            l_wk2_qty :=
               tpco_get_open_ir_ord_qty (rec1.item_id,
                                         l_vendor_id,
                                         l_dest_org_id,
                                         l_end_date + 1,
                                         (l_end_date + 1 + 6));
            -- fnd_file.put_line (fnd_file.LOG, 'week2 ' ||TO_CHAR( l_end_date + 1)||'  '||TO_CHAR((l_end_date + 1 + 6)));
            l_wk3_qty :=
               tpco_get_open_ir_ord_qty (rec1.item_id,
                                         l_vendor_id,
                                         l_dest_org_id,
                                         l_end_date + 8,
                                         (l_end_date + 8 + 6));
            --   fnd_file.put_line (fnd_file.LOG, 'week3 ' ||  TO_CHAR(l_end_date + 8)||'  '||TO_CHAR((l_end_date + 8 + 6)));
            l_wk4_qty :=
               tpco_get_open_ir_ord_qty (rec1.item_id,
                                         l_vendor_id,
                                         l_dest_org_id,
                                         l_end_date + 15,
                                         (l_end_date + 15 + 6));
            --  fnd_file.put_line (fnd_file.LOG, 'week4 ' || TO_CHAR(l_end_date + 15)||'  '||TO_CHAR((l_end_date + 15 + 6)));
            l_wk5_qty :=
               tpco_get_open_ir_ord_qty (rec1.item_id,
                                         l_vendor_id,
                                         l_dest_org_id,
                                         l_end_date + 22,
                                         (l_end_date + 22 + 6));
            --   fnd_file.put_line (fnd_file.LOG, 'week5 ' || TO_CHAR(l_end_date + 22)||'  '||TO_CHAR((l_end_date + 22 + 6)));
            l_wk6_qty :=
               tpco_get_open_ir_ord_qty (rec1.item_id,
                                         l_vendor_id,
                                         l_dest_org_id,
                                         l_end_date + 29,
                                         (l_end_date + 29 + 6));
            --  fnd_file.put_line (fnd_file.LOG, 'week6 ' ||TO_CHAR( l_end_date + 29)||'  '|| TO_CHAR((l_end_date + 29 + 6)));
            l_wk7_qty :=
               tpco_get_open_ir_ord_qty (rec1.item_id,
                                         l_vendor_id,
                                         l_dest_org_id,
                                         l_end_date + 36,
                                         (l_end_date + 36 + 6));
            --  fnd_file.put_line (fnd_file.LOG, 'week7 ' || TO_CHAR(l_end_date + 36)||'  '||  TO_CHAR((l_end_date + 36 + 6)));
            l_wk8_qty :=
               tpco_get_open_ir_ord_qty (rec1.item_id,
                                         l_vendor_id,
                                         l_dest_org_id,
                                         l_end_date + 43,
                                         (l_end_date + 43 + 6));
            --          fnd_file.put_line (fnd_file.LOG, 'week8 ' || TO_CHAR(l_end_date + 43)||'  '|| TO_CHAR((l_end_date + 43 + 6)));
            l_mth_begin_date := l_end_date + 50;
            l_mth1_qty :=
               tpco_get_open_ir_ord_qty (rec1.item_id,
                                         l_vendor_id,
                                         l_dest_org_id,
                                         l_mth_begin_date,
                                         (l_mth_begin_date + 30));
            -- fnd_file.put_line (fnd_file.LOG, 'mn1 ' ||TO_CHAR(l_mth_begin_date)||'  '||TO_CHAR((l_mth_begin_date + 30)));
            l_mth2_qty :=
               tpco_get_open_ir_ord_qty (rec1.item_id,
                                         l_vendor_id,
                                         l_dest_org_id,
                                         l_mth_begin_date + 31,
                                         (l_mth_begin_date + 61));
            -- fnd_file.put_line (fnd_file.LOG, 'mn3 ' || TO_CHAR(l_mth_begin_date + 31)||'  '||TO_CHAR(l_mth_begin_date + 61));
            l_mth3_qty :=
               tpco_get_open_ir_ord_qty (rec1.item_id,
                                         l_vendor_id,
                                         l_dest_org_id,
                                         (l_mth_begin_date + 62),
                                         (l_mth_begin_date + 92));

            --  fnd_file.put_line (fnd_file.LOG, 'mn3 ' || TO_CHAR(l_mth_begin_date + 62)||'  '||TO_CHAR(l_mth_begin_date + 92));
            BEGIN
               l_ord_qty_bf := 0;

               SELECT NVL (
                         SUM (
                              pla.quantity
                            - NVL (pla.quantity_cancelled, 0)
                            - NVL (pla.quantity_received, 0)),
                         0)
                         qty_ordered
                 INTO l_ord_qty_bf
                 FROM po_requisition_lines_all pla,
                      po_requisition_headers_all prb
                WHERE pla.requisition_header_id = prb.requisition_header_id
                      AND pla.destination_organization_id = l_dest_org_id
                      AND NVL (prb.closed_code, 'OPEN') = 'OPEN'
                      AND pla.source_organization_id =
                             NVL (l_vendor_id, pla.source_organization_id)
                      AND TRUNC (pla.need_by_date) <
                             TRUNC (TO_DATE (l_begin_weekdate))
                      AND (pla.cancel_flag = 'N' OR pla.cancel_flag IS NULL)
                      AND pla.quantity > NVL (pla.quantity_received, 0)
                      AND pla.item_id = rec1.item_id
                      AND prb.type_lookup_code = 'INTERNAL'
                      AND pla.item_id IS NOT NULL;
            --  fnd_file.put_line (fnd_file.LOG, 'l_ord_qty  ' || l_ord_qty);
            EXCEPTION
               WHEN OTHERS
               THEN
                  fnd_file.
                   put_line (fnd_file.LOG, 'error in open IR  ' || SQLERRM);
                  l_ord_qty_bf := 0;
            END;

            l_pur_price := NULL;
            l_currency := NULL;

            --  fnd_file.put_line (fnd_file.LOG, 'BEFORErecprice loop');
            FOR rec_price IN c_ir_price (l_dest_org_id,
                                         l_vendor_id,
                                         rec1.item_id,
                                         l_begin_weekdate,
                                         (l_begin_weekdate + 146))
            LOOP
               --      fnd_file.put_line (fnd_file.LOG, 'recprice loop');
               l_pur_price := rec_price.base_unit_price;
            END LOOP;

            BEGIN
               SELECT currency_code
                 INTO l_currency
                 FROM gl_sets_of_books glsob,
                      org_organization_definitions ood
                WHERE glsob.set_of_books_id = ood.set_of_books_id
                      AND ood.organization_id = l_dest_org_id;
            EXCEPTION
               WHEN OTHERS
               THEN
                  l_currency := NULL;
            END;
         END IF;

         l_safety_qty := NULL;

         BEGIN
            SELECT safety_stock_quantity
              INTO l_safety_qty
              FROM mtl_safety_stocks a
             WHERE a.organization_id = p_org_id
                   AND a.inventory_item_id = rec1.item_id
                   AND TRUNC (a.effectivity_date) =
                          (SELECT MAX (TRUNC (b.effectivity_date))
                             FROM mtl_safety_stocks b
                            WHERE b.organization_id = p_org_id
                                  AND b.inventory_item_id = rec1.item_id)
                   AND ROWNUM = 1;
         EXCEPTION
            WHEN OTHERS
            THEN
               l_safety_qty := NULL;
         END;

         --  IF l_vendor_id IS NULL
         -- THEN
         l_qty_recvd :=
            tpco_get_item_receipt (
               p_org_id,
               rec1.item_id,
               TO_CHAR (TO_DATE (p_date, 'DD-MM-YYYY'), 'YYYY'));
         l_qty_recvd_last_yr :=
            tpco_get_item_receipt (
               p_org_id,
               rec1.item_id,
               TO_NUMBER (TO_CHAR (TO_DATE (p_date, 'DD-MM-YYYY'), 'YYYY'))
               - 1);
         l_qty_recvd_last_yr2 :=
            tpco_get_item_receipt (
               p_org_id,
               rec1.item_id,
               TO_NUMBER (TO_CHAR (TO_DATE (p_date, 'DD-MM-YYYY'), 'YYYY'))
               - 2);
         l_last_vendor := NULL;
         l_lst_vendor_site_code := NULL;
         l_lst_vendor_state := NULL;
         l_lst_vendor_country := NULL;
         l_last_qty := 0;
         tpco_get_last_supp_add (p_org_id,
                                 rec1.item_id,
                                 l_ltst_trans_date,
                                 l_last_vendor,
                                 l_lst_vendor_site_code,
                                 l_lst_vendor_state,
                                 l_lst_vendor_country,
                                 l_last_qty);
         --    END IF;
         l_total_openorders := 0;
         l_total_openorders :=
              l_wk1_qty
            + l_wk2_qty
            + l_wk3_qty
            + l_wk4_qty
            + l_wk5_qty
            + l_wk6_qty
            + l_wk7_qty
            + l_wk8_qty
            + l_mth1_qty
            + l_mth2_qty
            + l_mth3_qty
            + l_ord_qty_bf;

         IF (   l_total_qty <> 0
             OR l_wk1_qty <> 0
             OR l_wk2_qty <> 0
             OR l_wk3_qty <> 0
             OR l_wk4_qty <> 0
             OR l_wk5_qty <> 0
             OR l_wk6_qty <> 0
             OR l_wk7_qty <> 0
             OR l_wk8_qty <> 0
             OR l_mth1_qty <> 0
             OR l_mth2_qty <> 0
             OR l_mth3_qty <> 0
             OR l_qty_recvd <> 0
             OR l_qty_recvd_last_yr <> 0
             OR l_qty_recvd_last_yr2 <> 0
             OR l_ord_qty_bf <> 0)
         THEN
            --    fnd_file.put_line (fnd_file.LOG, 'mn3 ' || TO_CHAR(l_mth_begin_date + 62)||'  '||TO_CHAR(l_mth_begin_date + 92));
            fnd_file.
             put_line (
               fnd_file.output,
                  l_vendor_site
               || '|'
               || l_vendor_name
               || '|'
               || l_city
               || '|'
               || l_state
               || '|'
               || l_country
               || '|'
               || rec1.item_num
               || '|'
               || rec1.item_type
               || '|'
               || rec1.description
               || '|'
               || rec1.planner_code
               || '|'
               || l_cost
               || '|'
               || l_pur_price
               || '|'
               || l_currency
               || '|'
               || rec1.primary_unit_of_measure
               || '|'
               || l_safety_qty
               || '|'
               || rec1.fixed_lot_multiplier
               || '|'
               || rec1.minimum_order_quantity
               || '|'
               || rec1.maximum_order_quantity
               || '|'
               || rec1.full_lead_time
               || '|'
               || l_intransit_qty
               || '|'
               || l_ohand_qty
               || '|'
               || l_total_qty
               || '|'
               || l_total_openorders
               || '|'
               || l_ord_qty_bf
               || '|'
               || l_wk1_qty
               || '|'
               || l_wk2_qty
               || '|'
               || l_wk3_qty
               || '|'
               || l_wk4_qty
               || '|'
               || l_wk5_qty
               || '|'
               || l_wk6_qty
               || '|'
               || l_wk7_qty
               || '|'
               || l_wk8_qty
               || '|'
               || l_mth1_qty
               || '|'
               || l_mth2_qty
               || '|'
               || l_mth3_qty
               || '|'
               || l_ltst_trans_date
               || '|'
               || l_last_vendor
               || '|'
               || l_last_qty
               || '|'
               || l_qty_recvd
               || '|'
               || l_qty_recvd_last_yr
               || '|'
               || l_qty_recvd_last_yr2);
         END IF;
      END LOOP;
   EXCEPTION
      WHEN OTHERS
      THEN
         fnd_file.put_line (fnd_file.LOG, 'Error:      ' || SQLERRM);
   END;

   FUNCTION tpco_get_open_ir_ord_qty (p_item_id              IN NUMBER,
                                      p_source_org_id        IN NUMBER,
                                      p_destination_org_id   IN NUMBER,
                                      p_req_from_date        IN DATE,
                                      p_req_to_date          IN DATE)
      RETURN NUMBER
   IS
      l_ord_qty   NUMBER := 0;
   BEGIN
      SELECT NVL (
                SUM (
                     pla.quantity
                   - NVL (pla.quantity_cancelled, 0)
                   - NVL (pla.quantity_received, 0)),
                0)
                qty_ordered
        INTO l_ord_qty
        FROM po_requisition_lines_all pla, po_requisition_headers_all prb
       WHERE pla.requisition_header_id = prb.requisition_header_id
             AND pla.destination_organization_id = p_destination_org_id
             AND pla.source_organization_id =
                    NVL (p_source_org_id, pla.source_organization_id)
             AND TRUNC (pla.need_by_date) BETWEEN TRUNC (p_req_from_date)
                                              AND TRUNC (p_req_to_date)
             AND (pla.cancel_flag = 'N' OR pla.cancel_flag IS NULL)
             AND pla.quantity > NVL (pla.quantity_received, 0)
             AND pla.item_id = p_item_id
             AND prb.type_lookup_code = 'INTERNAL'
             AND pla.item_id IS NOT NULL;

      --  fnd_file.put_line (fnd_file.LOG, 'l_ord_qty  ' || l_ord_qty);
      RETURN l_ord_qty;
   EXCEPTION
      WHEN OTHERS
      THEN
         fnd_file.put_line (fnd_file.LOG, 'error in open IR  ' || SQLERRM);
         l_ord_qty := 0;
   END;

   FUNCTION tpco_get_item_receipt (p_org_id    IN NUMBER,
                                   p_item_id   IN NUMBER,
                                   p_date      IN VARCHAR2)
      RETURN NUMBER
   IS
      l_qty_recvd   NUMBER;
   BEGIN
      l_qty_recvd := 0;

      SELECT NVL (SUM (rt.quantity), 0) qty_received
        INTO l_qty_recvd
        FROM rcv_transactions rt, rcv_shipment_lines rcl
       WHERE     rt.shipment_line_id = rcl.shipment_line_id
             AND rt.organization_id = NVL (p_org_id, rt.organization_id)
             AND rcl.item_id = p_item_id
             AND rt.transaction_type IN ('RECEIVE')
             AND rt.source_document_code IN ('PO', 'REQ')
             AND TO_CHAR (rt.transaction_date, 'YYYY') = p_date;

      RETURN l_qty_recvd;
   EXCEPTION
      WHEN OTHERS
      THEN
         l_qty_recvd := 0;
         RETURN l_qty_recvd;
   END;

   PROCEDURE tpco_get_last_supp (p_org_id          IN     NUMBER,
                                 p_item_id         IN     NUMBER,
                                 op_max_rct_date      OUT VARCHAR2,
                                 op_last_supp         OUT VARCHAR2)
   IS
      l_qty_date         DATE;
      l_last_vendor_id   po_vendors.vendor_id%TYPE;
      l_source_doc       rcv_transactions.source_document_code%TYPE;
      l_vendor_name      po_vendors.vendor_name%TYPE;

      CURSOR c_last_vendor
      IS
           SELECT MAX (transaction_date) trans_date,
                  NVL (rt.vendor_id, rcl.from_organization_id) vendor_id,
                  rt.source_document_code source_doc
             FROM rcv_transactions rt, rcv_shipment_lines rcl
            WHERE     rt.shipment_line_id = rcl.shipment_line_id
                  AND rt.organization_id = NVL (p_org_id, rt.organization_id)
                  AND rcl.item_id = p_item_id
                  AND rt.transaction_type IN ('RECEIVE')
                  AND rt.source_document_code IN ('PO', 'REQ')
         GROUP BY NVL (rt.vendor_id, rcl.from_organization_id),
                  rt.source_document_code
         -- ORDER BY 1 DESC;
         ORDER BY 1;
   BEGIN
      l_qty_date := NULL;
      op_max_rct_date := l_qty_date;
      l_source_doc := NULL;
      l_last_vendor_id := NULL;

      BEGIN
         FOR rec1 IN c_last_vendor
         LOOP
            l_qty_date := rec1.trans_date;
            l_last_vendor_id := rec1.vendor_id;
            l_source_doc := rec1.source_doc;
         END LOOP;
      -- fnd_file.put_line (fnd_file.LOG, 'l_qty_date  ' || l_qty_date);
      EXCEPTION
         WHEN OTHERS
         THEN
            l_last_vendor_id := NULL;
            l_source_doc := NULL;
            l_qty_date := NULL;
      END;

      /*   BEGIN
            SELECT NVL (vendor_id, rcl.from_organization_id),
                   rt.source_document_code
              INTO l_last_vendor_id,
                   l_source_doc
              FROM rcv_transactions rt, rcv_shipment_lines rcl
             WHERE rt.shipment_line_id = rcl.shipment_line_id
               AND rt.organization_id = NVL (p_org_id, rt.organization_id)
               AND rcl.item_id = p_item_id
               AND rt.transaction_type IN ('RECEIVE')
            AND RT.TRANSACTION_DATE(
            --   AND TRUNC (rt.transaction_date) = TRUNC(NVL(TO_DATE(l_qty_date), SYSDATE));

             fnd_file.put_line (fnd_file.LOG, 'l_last_vendor_id    ' || l_last_vendor_id||' l_source_doc '|| l_source_doc);

         EXCEPTION
            WHEN OTHERS
            THEN
               l_last_vendor_id := NULL;
               l_source_doc := NULL;
         END;*/
      IF l_source_doc = 'PO'
      THEN
         SELECT vendor_name
           INTO l_vendor_name
           FROM po_vendors
          WHERE vendor_id = l_last_vendor_id;
      -- fnd_file.put_line (fnd_file.LOG, ' 1 ');
      ELSIF l_source_doc = 'REQ'
      THEN
         SELECT organization_name
           INTO l_vendor_name
           FROM apps.org_organization_definitions
          WHERE organization_id = l_last_vendor_id;

         fnd_file.put_line (fnd_file.LOG, ' 2 ');
      END IF;

      --  fnd_file.put_line (fnd_file.LOG, ' l_vendor_name    ' || l_last_vendor_id||'  l_vendor_name '|| l_source_doc);
      op_last_supp := l_vendor_name;
      op_max_rct_date := l_qty_date;
   EXCEPTION
      WHEN OTHERS
      THEN
         op_last_supp := NULL;
         op_max_rct_date := NULL;
   END;

   FUNCTION tpco_get_item_cost (p_org_id IN NUMBER, p_item_id IN NUMBER)
      RETURN NUMBER
   IS
      lcost   NUMBER;
   BEGIN
      SELECT a.item_cost
        INTO lcost
        FROM apps.cst_item_cost_type_v a
       WHERE     inventory_item_id = p_item_id
             AND organization_id = p_org_id
             AND cost_type = 'Frozen'
             AND TRUNC (last_update_date) =
                    (SELECT TRUNC (MAX (last_update_date))
                       FROM apps.cst_item_cost_type_v b
                      WHERE     a.inventory_item_id = b.inventory_item_id
                            AND a.organization_id = b.organization_id
                            AND b.cost_type = 'Frozen');

      --  fnd_file.put_line (fnd_file.LOG, 'lcost' || lcost);
      RETURN lcost;
   EXCEPTION
      WHEN OTHERS
      THEN
         lcost := 0;
         RETURN lcost;
         NULL;
   END;

   PROCEDURE tpco_get_last_supp_add (p_org_id              IN     NUMBER,
                                     p_item_id             IN     NUMBER,
                                     op_max_rct_date          OUT VARCHAR2,
                                     op_last_supp             OUT VARCHAR2,
                                     op_vendor_site_code      OUT VARCHAR2,
                                     op_vendor_state          OUT VARCHAR2,
                                     op_vendor_country        OUT VARCHAR2,
                                     op_last_qty              OUT NUMBER)
   IS
      l_qty_date         DATE;
      l_last_vendor_id   po_vendors.vendor_id%TYPE;
      l_source_doc       rcv_transactions.source_document_code%TYPE;
      l_vendor_name      po_vendors.vendor_name%TYPE;
      l_vendor_site_id   po_vendor_sites_all.vendor_site_id%TYPE;
      l_site_code        po_vendor_sites_all.vendor_site_code%TYPE;
      l_state            po_vendor_sites_all.state%TYPE;
      l_country          po_vendor_sites_all.country%TYPE;
      l_last_qty         NUMBER := 0;

      CURSOR c_last_vendor
      IS
           SELECT MAX (transaction_date) trans_date,
                  NVL (rt.vendor_id, rcl.from_organization_id) vendor_id,
                  rt.vendor_site_id,
                  rt.source_document_code source_doc
             FROM rcv_transactions rt, rcv_shipment_lines rcl
            WHERE     rt.shipment_line_id = rcl.shipment_line_id
                  AND rt.organization_id = NVL (p_org_id, rt.organization_id)
                  AND rcl.item_id = p_item_id
                  AND rt.transaction_type IN ('RECEIVE')
                  AND rt.source_document_code IN ('PO', 'REQ')
         GROUP BY NVL (rt.vendor_id, rcl.from_organization_id),
                  rt.source_document_code,
                  rt.vendor_site_id
         -- ORDER BY 1 DESC;
         ORDER BY 1;
   BEGIN
      l_qty_date := NULL;
      op_max_rct_date := l_qty_date;
      l_source_doc := NULL;
      l_last_vendor_id := NULL;
      l_site_code := NULL;
      l_state := NULL;
      l_country := NULL;
      l_last_qty := NULL;

      BEGIN
         FOR rec1 IN c_last_vendor
         LOOP
            l_qty_date := rec1.trans_date;
            l_last_vendor_id := rec1.vendor_id;
            l_source_doc := rec1.source_doc;
            l_vendor_site_id := rec1.vendor_site_id;
         END LOOP;
      -- fnd_file.put_line (fnd_file.LOG, 'l_qty_date  ' || l_qty_date);

      -- ORDER BY 1 DESC;
      EXCEPTION
         WHEN OTHERS
         THEN
            l_last_vendor_id := NULL;
            l_source_doc := NULL;
            l_qty_date := NULL;
      END;

      /*   BEGIN
            SELECT NVL (vendor_id, rcl.from_organization_id),
                   rt.source_document_code
              INTO l_last_vendor_id,
                   l_source_doc
              FROM rcv_transactions rt, rcv_shipment_lines rcl
             WHERE rt.shipment_line_id = rcl.shipment_line_id
               AND rt.organization_id = NVL (p_org_id, rt.organization_id)
               AND rcl.item_id = p_item_id
               AND rt.transaction_type IN ('RECEIVE')
            AND RT.TRANSACTION_DATE(
            --   AND TRUNC (rt.transaction_date) = TRUNC(NVL(TO_DATE(l_qty_date), SYSDATE));

             fnd_file.put_line (fnd_file.LOG, 'l_last_vendor_id    ' || l_last_vendor_id||' l_source_doc '|| l_source_doc);

         EXCEPTION
            WHEN OTHERS
            THEN
               l_last_vendor_id := NULL;
               l_source_doc := NULL;
         END;*/
      IF l_source_doc = 'PO'
      THEN
         SELECT vendor_name
           INTO l_vendor_name
           FROM po_vendors
          WHERE vendor_id = l_last_vendor_id;

         SELECT vendor_site_code, NVL (state, province), country
           INTO l_site_code, l_state, l_country
           FROM po_vendor_sites_all
          WHERE vendor_site_id = l_vendor_site_id;

         -- fnd_file.put_line (fnd_file.LOG, ' 1 ');
         SELECT NVL (SUM (primary_quantity), 0) last_qty
           INTO l_last_qty
           FROM rcv_transactions rt, rcv_shipment_lines rcl
          WHERE     rt.shipment_line_id = rcl.shipment_line_id
                AND rt.organization_id = NVL (p_org_id, rt.organization_id)
                AND rcl.item_id = p_item_id
                AND rt.transaction_type IN ('RECEIVE')
                AND TRUNC (rt.transaction_date) = TRUNC (l_qty_date)
                AND rt.vendor_id = l_last_vendor_id;
      ELSIF l_source_doc = 'REQ'
      THEN
         SELECT organization_name
           INTO l_vendor_name
           FROM apps.org_organization_definitions
          WHERE organization_id = l_last_vendor_id;

         SELECT NVL (SUM (primary_quantity), 0) last_qty
           INTO l_last_qty
           FROM rcv_transactions rt, rcv_shipment_lines rcl
          WHERE     rt.shipment_line_id = rcl.shipment_line_id
                AND rt.organization_id = NVL (p_org_id, rt.organization_id)
                AND rcl.item_id = p_item_id
                AND rt.transaction_type IN ('RECEIVE')
                AND TRUNC (rt.transaction_date) = TRUNC (l_qty_date)
                AND rt.vendor_id = l_last_vendor_id;

         fnd_file.put_line (fnd_file.LOG, ' 2 ');
      END IF;

      --  fnd_file.put_line (fnd_file.LOG, ' l_vendor_name    ' || l_last_vendor_id||'  l_vendor_name '|| l_source_doc);
      op_last_supp := l_vendor_name;
      op_max_rct_date := l_qty_date;
      op_vendor_site_code := l_site_code;
      op_vendor_state := l_state;
      op_vendor_country := l_country;
      op_last_qty := l_last_qty;
   EXCEPTION
      WHEN OTHERS
      THEN
         op_last_supp := NULL;
         op_max_rct_date := NULL;
         op_vendor_site_code := NULL;
         op_vendor_state := NULL;
         op_vendor_country := NULL;
         op_last_qty := NULL;
   END;

   PROCEDURE tpco_get_consume_date (p_item_id       IN     NUMBER,
                                    p_org_id        IN     NUMBER,
                                    op_trans_date      OUT VARCHAR2,
                                    op_trans_qty       OUT VARCHAR2)
   IS
      l_date    DATE;
      l_qty     NUMBER;
      l_date1   VARCHAR2 (15);

      CURSOR c_trans (
         p_date IN VARCHAR2)
      IS
           SELECT NVL (primary_quantity, 0) qty
             FROM mtl_material_transactions mmt
            WHERE     mmt.inventory_item_id = p_item_id
                  AND mmt.organization_id = p_org_id
                  AND transaction_type_id = 44       --added for kieth'request
                  AND TRUNC (transaction_date) = TO_DATE (p_date)
         ORDER BY transaction_id DESC;
   BEGIN
      l_date := NULL;
      fnd_file.put_line (fnd_file.LOG, 'in get consume date');

      SELECT NVL (MAX (TRUNC (transaction_date)), NULL)
        INTO l_date
        FROM mtl_material_transactions mmt
       WHERE     mmt.inventory_item_id = p_item_id
             AND mmt.organization_id = p_org_id
             AND transaction_type_id = 44 --added for kieth'request wip assembly completion
                                         ;

      l_date1 := NULL;
      l_date1 := TO_CHAR (l_date, 'DD-MON-YYYY');
      op_trans_date := l_date1;
      fnd_file.put_line (fnd_file.LOG, 'l_date1  ' || l_date1);
      l_qty := 0;

      FOR rec1 IN c_trans (l_date1)
      LOOP
         l_qty := rec1.qty;
      END LOOP;

      /*SELECT
                  NVL(SUM(primary_quantity),0)
             INTO l_qty
          FROM mtl_material_transactions mmt
       WHERE mmt.inventory_item_id =p_item_id
         AND  mmt.organization_id = p_org_id
      AND TRUNC(transaction_date) = TO_DATE(l_date1);*/
      fnd_file.put_line (fnd_file.LOG, 'l_qty  ' || l_qty);
      op_trans_qty := l_qty;
   EXCEPTION
      WHEN OTHERS
      THEN
         fnd_file.put_line (fnd_file.LOG, 'ERROR  ' || l_qty);
         op_trans_date := NULL;
         op_trans_qty := 0;
   END;

   PROCEDURE tpco_get_pur_usage_all_org (errbuff          OUT VARCHAR2,
                                         retcode          OUT VARCHAR2,
                                         p_global      IN     VARCHAR2,
                                         p_org_id      IN     NUMBER,
                                         p_date        IN     VARCHAR2,
                                         p_item_type   IN     VARCHAR2)
   IS
      CURSOR c1
      IS
         SELECT organization_id FROM apps.org_organization_definitions;
   BEGIN
      fnd_file.put_line (fnd_file.LOG, 'TPCO Purchased Item Usage Report');
      fnd_file.put_line (fnd_file.LOG, 'p_global  ' || p_global);

      IF p_global = 'Y'
      THEN
         fnd_file.put_line (fnd_file.LOG, '1');

         FOR rec1 IN c1
         LOOP
            --   tpco_get_purchase_dtl (rec1.organization_id, p_date);
            tpco_get_purchase_usage_dtl (rec1.organization_id,
                                         p_date,
                                         p_item_type);
         END LOOP;
      ELSE
         fnd_file.put_line (fnd_file.LOG, '1a');
         -- tpco_get_purchase_dtl (p_org_id, p_date);
         tpco_get_purchase_usage_dtl (p_org_id, p_date, p_item_type);
      END IF;
   EXCEPTION
      WHEN OTHERS
      THEN
         retcode := 2;
         fnd_file.put_line (fnd_file.LOG, 'Error:      ' || SQLERRM);
   END;

   PROCEDURE tpco_get_purchase_usage_dtl (p_org_id      IN NUMBER,
                                          p_date        IN VARCHAR2,
                                          p_item_type   IN VARCHAR2)
   IS
      l_last_vendor          po_vendors.vendor_name%TYPE;
      l_ltst_trans_date      VARCHAR2 (50);
      l_cost                 NUMBER;
      l_wip_qty_ytd          NUMBER;
      l_name                 apps.org_organization_definitions.organization_name%TYPE;
      l_vendor_id            po_vendors.vendor_id%TYPE;
      l_pur_price            NUMBER;
      l_currency             VARCHAR2 (50);
      l_wip_qty_lst_yr       NUMBER;
      l_wip_qty_lst_prv_yr   NUMBER;
      lcnt1                  NUMBER;
      l_ohand_qty            NUMBER;
      l_last_qty             NUMBER := 0;
      l_last_rec_qty         NUMBER := 0;
      l_max_trans_date       VARCHAR2 (50);
      l_max_trans_qty        VARCHAR2 (200);
      l_intransit_qty        NUMBER;
      l_total_qty            NUMBER;
      l_safety_qty           NUMBER;
      l_site_code            VARCHAR2 (250);
      l_state                VARCHAR2 (100);
      l_country              VARCHAR2 (100);
      l_prod_fam             VARCHAR2 (20);
      l_prod_line            VARCHAR2 (20);

      CURSOR c_purchased_item
      IS
         SELECT msi.segment1 item_num,
                planner_code,
                primary_unit_of_measure,
                msi.inventory_item_id item_id,
                DECODE (msi.planning_make_buy_code,
                        1, 'Make',
                        2, 'Buy',
                        msi.planning_make_buy_code)
                   plan_item,
                REPLACE (msi.description, CHR (9), ' ') description,
                msi.min_minmax_quantity min_minmax_quantity,
                msi.max_minmax_quantity max_minmax_quantity,
                postprocessing_lead_time,
                preprocessing_lead_time,
                fixed_lead_time,
                cumulative_total_lead_time,
                minimum_order_quantity,
                planning_time_fence_days,
                demand_time_fence_days,
                cum_manufacturing_lead_time,
                fixed_lot_multiplier,
                DECODE (mrp_planning_code,
                        7, 'MRP and MPP planning',
                        3, 'MRP planning',
                        4, 'MPS planning',
                        6, 'Not planned',
                        8, 'MPS and MPP plannin g',
                        9, 'MPP planning',
                        mrp_planning_code)
                   plan_code,
                full_lead_time processing_lead_time,
                msi.item_type,
                msi.inventory_item_status_code inventory_item_status_code
           FROM mtl_system_items_b msi
          WHERE msi.item_type = p_item_type --  AND inventory_item_status_code <> 'Active'
                                         --AND msi.segment1 = 'AE630AR-717-A2'
                AND msi.organization_id = p_org_id                          --
                                         -- AND msi.inventory_item_id = 504640
   ;

      /* SELECT msi.segment1 item_num, primary_unit_of_measure,
             msi.inventory_item_id item_id,
             REPLACE (msi.description, CHR (9), ' ') description
        FROM mtl_system_items_b msi,TPCO_WIP_SALE_ISSUE tws
       WHERE msi.organization_id = tws.org_id
         AND msi.inventory_item_id = tws.item_id
      AND tws.item_type = p_item_type
                   AND tws.org_id = p_org_id
              UNION
              SELECT msi.segment1 item_num, primary_unit_of_measure,
             msi.inventory_item_id item_id,
             REPLACE (msi.description, CHR (9), ' ') description
        FROM mtl_system_items_b msi,TPCO_WIP_SALE_ISSUE_TEMP tws1
       WHERE msi.organization_id = tws1.org_id
         AND msi.inventory_item_id = tws1.item_id
      AND tws1.item_type = p_item_type
                   AND tws1.org_id = p_org_id;*/

      /*   AND  EXISTS  ( SELECT 'X'
                    FROM apps.bom_components_b bic,
                         apps.bom_bill_of_materials bom
                   WHERE bic.component_item_id = msi.inventory_item_id
                     AND bom.bill_sequence_id = bic.bill_sequence_id
                     AND bom.organization_id = msi.organization_id)
      -- AND msi.segment1 = '10118-C5'
      ORDER BY msi.segment1;*/
      -- AND msi.segment1 = '10118-C5'
      CURSOR c_po_price (
         p_vendor_id   IN NUMBER,
         p_item_id     IN NUMBER)
      IS
           SELECT poh.currency_code, plla.price_override
             FROM po_line_locations_all plla,
                  po_lines_all pl,
                  po_headers_all poh
            WHERE     plla.po_line_id = pl.po_line_id
                  AND poh.org_id = plla.org_id
                  AND poh.org_id = plla.org_id
                  AND poh.po_header_id = pl.po_header_id
                  AND plla.ship_to_organization_id = p_org_id
                  AND poh.vendor_id = p_vendor_id
                  AND pl.item_id = p_item_id
                  AND (poh.cancel_flag = 'N' OR poh.cancel_flag IS NULL)
                  AND (pl.cancel_flag = 'N' OR pl.cancel_flag IS NULL)
                  AND (plla.quantity - NVL (plla.quantity_cancelled, 0)) > 0
         --   AND poh.type_lookup_code <> 'BLANKET'
         ORDER BY plla.creation_date;

      CURSOR c_ir_price (
         p_destination_org_id   IN NUMBER,
         p_source_org_id        IN NUMBER,
         p_item_id              IN NUMBER)
      IS
           SELECT prl.base_unit_price, prl.currency_code currency_code
             FROM po_requisition_headers_all prh, po_requisition_lines_all prl
            WHERE     prh.org_id = prl.org_id
                  AND prh.requisition_header_id = prl.requisition_header_id
                  AND prl.item_id = p_item_id
                  --AND b.source_type_code = 'INVENTORY'
                  AND prh.type_lookup_code = 'INTERNAL'
                  AND prl.destination_organization_id = p_destination_org_id
                  AND prl.source_organization_id =
                         NVL (p_source_org_id, prl.source_organization_id)
                  --   AND TRUNC (prl.need_by_date) BETWEEN TRUNC (p_per_begin_date)
                  --   AND TRUNC (p_per_end_date)
                  AND (prl.cancel_flag = 'N' OR prl.cancel_flag IS NULL)
         ORDER BY prl.creation_date;
   BEGIN
 

      fnd_file.put_line (fnd_file.LOG, 'in the program');
      fnd_file.
       put_line (
         fnd_file.output,
            'Item Type'
         || '|'
         || 'Make or Buy Item'
         || '|'
         || 'Product Family'
         || '|'
         || 'Planner_code'
         || '|'
         || 'Oracle Part Number'
         || '|'
         || 'Description'
         || '|'
         || 'Item Status'
         || '|'
         || 'Unit of Measure'
         || '|'
         || 'Production Line'
         || '|'
         || 'Pre Processing Lead Time'
         || '|'
         || 'Processing Lead Time'
         || '|'
         || 'Post Processing Lead Time'
         || '|'
         || 'Fixed Lead Time'
         || '|'
         || 'Cumulative Total Lead Time'
         || '|'
         || 'Cum Manufacturing Lead Time'
         || '|'
         || 'Last Received Qty'
         || '|'
         || 'Minimum Order Quantity'
         || '|'
         || 'Fixed Lot Multiplier'
         || '|'
         || 'Last Received Date'
         || '|'
         || 'Last Received from Vendor'
         || '|'
         || 'Last Rcv Vendor Site Code'
         || '|'
         || 'Last Rcv Vendor State'
         || '|'
         || 'Last Rcv Vendor Country'
         || '|'
         || 'Currency'
         || '|'
         || 'Last Purchase Price'
         || '|'
         || 'Safety Stock'
         || '|'
         || 'Min Minmax Qty'
         || '|'
         || 'Max Minmax Qty'
         || '|'
         || 'Onhand Quantity'
         || '|'
         || 'Intransit Quantity'
         || '|'
         || 'Total Quantity'
         || '|'
         || 'Standard Cost'
         || '|'
         || 'Onhand Value'
         || '|'
         || ' Last Production Date'
         || '|'
         || 'Last Production Quantity'
         || '|'
         || 'YTD Useage'
         || '|'
         || TO_CHAR (TO_NUMBER (p_date) - 1)
         || '|'
         || TO_CHAR (TO_NUMBER (p_date) - 2)
         || '|'
         || 'Planning  Fence Days'
         || '|'
         || 'Demand Fence Days'
         || '|'
         || 'Planning Code');

      BEGIN
         lcnt1 := 0;

         SELECT COUNT (*)
           INTO lcnt1
           FROM tpco_wip_sale_issue
          WHERE item_type = p_item_type AND trans_year = p_date;

         IF lcnt1 = 0
         THEN
            INSERT INTO tpco_wip_sale_issue_temp (item_id,
                                                  org_id,
                                                  trans_year,
                                                  item_type,
                                                  qty)
                 SELECT mmt.inventory_item_id,
                        p_org_id,
                        p_date,
                        msi.item_type,
                        SUM (primary_quantity)
                   FROM mtl_material_transactions mmt, mtl_system_items_b msi
                  WHERE     mmt.inventory_item_id = msi.inventory_item_id
                        AND mmt.organization_id = msi.organization_id
                        AND mmt.organization_id = p_org_id
                        AND mmt.transaction_type_id IN (35, 33, 62, 34)
                        AND TO_CHAR (mmt.transaction_date, 'YYYY') = p_date
                        AND msi.item_type = p_item_type
               GROUP BY mmt.inventory_item_id,
                        p_org_id,
                        p_date,
                        msi.item_type;
         END IF;
      --  fnd_file.put_line (fnd_file.LOG,'inserted    ');
      EXCEPTION
         WHEN OTHERS
         THEN
            fnd_file.
             put_line (fnd_file.LOG, 'Error inserting to temp ' || SQLERRM);
      END;

      l_ohand_qty := 0;
      l_pur_price := 0;
      l_currency := 0;

      FOR rec1 IN c_purchased_item
      LOOP
         BEGIN
            l_prod_fam := NULL;

            SELECT segment3
              INTO l_prod_fam
              FROM apps.mtl_item_categories_v mtc
             WHERE     mtc.category_set_name = 'TPC PLANNING CATEGORY'
                   AND inventory_item_id = rec1.item_id
                   AND organization_id = p_org_id;
         EXCEPTION
            WHEN OTHERS
            THEN
               l_prod_fam := NULL;
         END;

         l_ohand_qty := 0;

         SELECT NVL (SUM (primary_transaction_quantity), 0)
           INTO l_ohand_qty
           FROM mtl_onhand_quantities_detail
          WHERE organization_id = p_org_id
                AND inventory_item_id = rec1.item_id;

         l_intransit_qty := 0;

         SELECT                                           -- CURRENT_INTRANSIT
               NVL (
                   SUM (
                      DECODE (
                         ms.intransit_owning_org_id,
                         ms.from_organization_id, inv_convert.
                                                   inv_um_convert (
                                                     ms.item_id,
                                                     NULL,
                                                     ms.quantity,
                                                     NULL,
                                                     NULL,
                                                     ms.
                                                      unit_of_measure,
                                                     msi_from.
                                                      primary_unit_of_measure),
                         ms.to_org_primary_quantity)),
                   0)
           INTO l_intransit_qty
           FROM mtl_supply ms,
                mtl_parameters mp,
                mtl_interorg_parameters mip,
                mtl_material_transactions mmt,
                rcv_shipment_lines rsl,
                mtl_system_items msi_from
          WHERE     ms.to_organization_id = p_org_id
                AND ms.item_id = rec1.item_id
                AND ms.supply_type_code IN ('SHIPMENT', 'RECEIVING')
                AND ms.destination_type_code = 'INVENTORY'
                AND mp.organization_id = ms.intransit_owning_org_id
                -- AND MS.intransit_owning_org_id = p_org_id
                AND rsl.shipment_line_id = ms.shipment_line_id
                AND mmt.transaction_id(+) = rsl.mmt_transaction_id
                AND mip.from_organization_id(+) = ms.from_organization_id
                AND mip.to_organization_id(+) = ms.to_organization_id
                AND mip.fob_point(+) =
                       DECODE (ms.intransit_owning_org_id,
                               ms.from_organization_id, 2,
                               ms.to_organization_id, 1)
                AND msi_from.inventory_item_id = ms.item_id
                AND msi_from.organization_id = ms.from_organization_id;

         l_safety_qty := NULL;

         BEGIN
            SELECT safety_stock_quantity
              INTO l_safety_qty
              FROM mtl_safety_stocks a
             WHERE a.organization_id = p_org_id
                   AND a.inventory_item_id = rec1.item_id
                   AND TRUNC (a.effectivity_date) =
                          (SELECT MAX (TRUNC (b.effectivity_date))
                             FROM mtl_safety_stocks b
                            WHERE b.organization_id = p_org_id
                                  AND b.inventory_item_id = rec1.item_id)
                   AND ROWNUM = 1;
         EXCEPTION
            WHEN OTHERS
            THEN
               l_safety_qty := 0;
         END;

         l_prod_line := NULL;

         BEGIN
            SELECT line_code
              INTO l_prod_line
              FROM apps.bom_operational_routings_v
             WHERE organization_id = p_org_id
                   AND assembly_item_id = rec1.item_id;
         --           fnd_file.put_line (fnd_file.LOG, 'prod line');
         EXCEPTION
            WHEN OTHERS
            THEN
               l_prod_line := NULL;
               NULL;
         END;

         l_total_qty := 0;
         l_total_qty := NVL (l_intransit_qty, 0) + NVL (l_ohand_qty, 0);
         -- fnd_file.put_line (fnd_file.LOG,'3');
         l_last_vendor := NULL;
         l_ltst_trans_date := NULL;
         l_last_qty := 0;
         l_cost := NULL;
         l_site_code := NULL;
         l_state := NULL;
         l_country := NULL;
         tpco_get_last_supp_add (p_org_id,
                                 rec1.item_id,
                                 l_ltst_trans_date,
                                 l_last_vendor,
                                 l_site_code,
                                 l_state,
                                 l_country,
                                 l_last_rec_qty);
         fnd_file.
          put_line (
            fnd_file.LOG,
               ' l_ltst_trans_date :'
            || l_ltst_trans_date
            || ' l_last_vendor'
            || l_last_vendor);
         l_cost := tpco_get_item_cost (p_org_id, rec1.item_id);

         -- fnd_file.put_line (fnd_file.LOG,'5');
         IF l_last_vendor IS NOT NULL
         THEN
            BEGIN
               l_name := NULL;

               SELECT organization_id
                 INTO l_name
                 FROM apps.org_organization_definitions
                WHERE organization_name = l_last_vendor;
            EXCEPTION
               WHEN OTHERS
               THEN
                  l_name := NULL;
            END;

            fnd_file.put_line (fnd_file.LOG, '7' || l_name);
            l_vendor_id := NULL;

            IF l_name IS NULL
            THEN
               fnd_file.
                put_line (
                  fnd_file.LOG,
                  '7a' || l_name || 'l_last_vendor ' || l_last_vendor);

               SELECT vendor_id
                 INTO l_vendor_id
                 FROM po_vendors
                WHERE vendor_name = l_last_vendor;

               l_last_qty := 0;

               SELECT SUM (quantity)
                 INTO l_last_qty
                 FROM rcv_transactions rt, rcv_shipment_lines rcl
                WHERE rt.shipment_line_id = rcl.shipment_line_id
                      AND rt.organization_id =
                             NVL (p_org_id, rt.organization_id)
                      AND rcl.item_id = rec1.item_id
                      AND rt.transaction_type IN ('RECEIVE')
                      AND rt.source_document_code = 'PO'
                      AND rt.vendor_id = l_vendor_id
                      AND TRUNC (rt.transaction_date) =
                             TO_DATE (l_ltst_trans_date);

               fnd_file.put_line (fnd_file.LOG, '7ab' || l_name);
               l_pur_price := 0;
               l_currency := 0;

               FOR rec2 IN c_po_price (l_vendor_id, rec1.item_id)
               LOOP
                  l_pur_price := 0;
                  l_pur_price := rec2.price_override;
                  l_currency := rec2.currency_code;
               END LOOP;
            ELSIF l_name IS NOT NULL
            THEN
               l_last_qty := 0;

               SELECT SUM (quantity)
                 INTO l_last_qty
                 FROM rcv_transactions rt, rcv_shipment_lines rcl
                WHERE rt.shipment_line_id = rcl.shipment_line_id
                      AND rt.organization_id =
                             NVL (p_org_id, rt.organization_id)
                      AND rcl.item_id = rec1.item_id
                      AND rt.transaction_type IN ('RECEIVE')
                      AND rt.source_document_code = 'REQ'
                      AND rcl.from_organization_id = l_name
                      AND TRUNC (rt.transaction_date) =
                             TO_DATE (l_ltst_trans_date);

               FOR rec3 IN c_ir_price (p_org_id, l_name, rec1.item_id)
               LOOP
                  l_pur_price := 0;
                  l_pur_price := rec3.base_unit_price;
               END LOOP;

               BEGIN
                  SELECT currency_code
                    INTO l_currency
                    FROM gl_sets_of_books glsob,
                         org_organization_definitions ood
                   WHERE glsob.set_of_books_id = ood.set_of_books_id
                         AND ood.organization_id = p_org_id;
               EXCEPTION
                  WHEN OTHERS
                  THEN
                     l_currency := NULL;
               END;
            ELSE
               l_pur_price := NULL;
               l_currency := NULL;
            END IF;
         END IF;

         IF l_last_vendor IS NULL
         THEN
            l_pur_price := NULL;
            l_currency := NULL;
         END IF;

         BEGIN
            l_wip_qty_ytd := 0;

            /*  SELECT NVL(SUM(qty),0)
                INTO  l_wip_qty_ytd
                FROM TPCO_WIP_SALE_ISSUE
                  WHERE item_id = rec1.item_id
                                    AND org_id = p_org_id
                                   AND Trans_year =   p_date;*/

            /*(  SELECT NVL (SUM (transaction_quantity), 0)
                  INTO l_wip_qty_ytd
                    FROM mtl_material_transactions mmt
                      WHERE  mmt.inventory_item_id = rec1.item_id
                           AND mmt.organization_id = p_org_id
                          AND TO_CHAR (mmt.transaction_date, 'YYYY') =   p_date
                                    AND transaction_type_id IN (35,33)  ;*/
            IF lcnt1 = 0
            THEN
               fnd_file.
                put_line (
                  fnd_file.LOG,
                  ' l_wip_qty_ytd here111:             ' || l_wip_qty_ytd);
               l_wip_qty_ytd :=
                  tpco_get_trans_quan (p_org_id, rec1.item_id, p_date);
            ELSIF lcnt1 <> 0
            THEN
               fnd_file.
                put_line (
                  fnd_file.LOG,
                  ' l_wip_qty_ytd here222222:             ' || l_wip_qty_ytd);
               l_wip_qty_ytd :=
                  tpco_get_trans_quan_prv_yr (p_org_id,
                                              rec1.item_id,
                                              TO_NUMBER (p_date));
            END IF;
         --Transaction type is WIP Component Issue
         --Source type is job or schedule
         EXCEPTION
            WHEN OTHERS
            THEN
               l_wip_qty_ytd := 0;
               NULL;
         END;

         BEGIN
            l_wip_qty_lst_yr := 0;
            l_wip_qty_lst_yr :=
               tpco_get_trans_quan_prv_yr (p_org_id,
                                           rec1.item_id,
                                           TO_NUMBER (p_date) - 1);
         /*  SELECT NVL(SUM(qty),0)
       INTO  l_wip_qty_lst_yr
       FROM TPCO_WIP_SALE_ISSUE
         WHERE item_id = rec1.item_id
                           AND org_id = p_org_id
                          AND Trans_year =   TO_NUMBER (p_date) - 1;*/

         /*  SELECT NVL (SUM (transaction_quantity), 0)
              INTO  l_wip_qty_lst_yr
                FROM mtl_material_transactions mmt
                  WHERE  mmt.inventory_item_id = rec1.item_id
                       AND mmt.organization_id = p_org_id
                      AND TO_CHAR (mmt.transaction_date, 'YYYY') =    TO_NUMBER (p_date) - 1
                                AND transaction_type_id IN (35,33)  ;   */
         --Transaction type is WIP Component Issue
         --Source type is job or schedule
         EXCEPTION
            WHEN OTHERS
            THEN
               l_wip_qty_lst_yr := 0;
               NULL;
         END;

         BEGIN
            l_wip_qty_lst_prv_yr := 0;
            l_wip_qty_lst_prv_yr :=
               tpco_get_trans_quan_prv_yr (p_org_id,
                                           rec1.item_id,
                                           TO_NUMBER (p_date) - 2);
         /*                 SELECT NVL(SUM(qty),0)
             INTO   l_wip_qty_lst_prv_yr
             FROM TPCO_WIP_SALE_ISSUE
               WHERE item_id = rec1.item_id
                                 AND org_id = p_org_id
                                AND Trans_year =   TO_NUMBER (p_date) - 2;*/

         /*       SELECT NVL (SUM (transaction_quantity), 0)
                     INTO   l_wip_qty_lst_prv_yr
                       FROM mtl_material_transactions mmt
                         WHERE  mmt.inventory_item_id = rec1.item_id
                              AND mmt.organization_id = p_org_id
                             AND TO_CHAR (mmt.transaction_date, 'YYYY') =    TO_NUMBER (p_date) - 2
                                 AND transaction_type_id IN (35,33)  ;   */
         --Transaction type is WIP Component Issue
         --Source type is job or schedule
         EXCEPTION
            WHEN OTHERS
            THEN
               l_wip_qty_lst_prv_yr := 0;
               NULL;
         END;

         BEGIN
            l_max_trans_date := NULL;
            l_max_trans_qty := 0;
            fnd_file.put_line (fnd_file.LOG, 'TPCO_GET_CONSUME_DATE BEF');
            tpco_get_consume_date (rec1.item_id,
                                   p_org_id,
                                   l_max_trans_date,
                                   l_max_trans_qty);
            fnd_file.put_line (fnd_file.LOG, 'TPCO_GET_CONSUME_DATE AFTER');
         EXCEPTION
            WHEN OTHERS
            THEN
               l_max_trans_date := NULL;
               l_max_trans_qty := 0;
               NULL;
         END;

         --     IF    (l_wip_qty_ytd <> 0)
         --      OR (l_wip_qty_lst_yr <> 0)
         --       OR (l_wip_qty_lst_prv_yr <> 0)
         --       THEN
         fnd_file.
          put_line (
            fnd_file.output,
               rec1.item_type
            || '|'
            || rec1.plan_item
            || '|'
            || l_prod_fam
            || '|'
            || rec1.planner_code
            || '|'
            || rec1.item_num
            || '|'
            || rec1.description
            || '|'
            || REC1.inventory_item_status_code
            || '|'
            || rec1.primary_unit_of_measure
            || '|'
            || l_prod_line
            || '|'
            || rec1.preprocessing_lead_time
            || '|'
            || rec1.processing_lead_time
            || '|'
            || rec1.postprocessing_lead_time
            || '|'
            || rec1.fixed_lead_time
            || '|'
            || rec1.cumulative_total_lead_time
            || '|'
            || rec1.cum_manufacturing_lead_time
            || '|'
            || l_last_qty
            || '|'
            || rec1.minimum_order_quantity
            || '|'
            || rec1.fixed_lot_multiplier
            || '|'
            || l_ltst_trans_date
            || '|'
            || l_last_vendor
            || '|'
            || l_site_code
            || '|'
            || l_state
            || '|'
            || l_country
            || '|'
            || l_currency
            || '|'
            || l_pur_price
            || '|'
            || l_safety_qty
            || '|'
            || rec1.min_minmax_quantity
            || '|'
            || rec1.max_minmax_quantity
            || '|'
            || l_ohand_qty
            || '|'
            || l_intransit_qty
            || '|'
            || l_total_qty
            || '|'
            || l_cost
            || '|'
            || NVL (l_ohand_qty, 0) * NVL (l_cost, 0)
            || '|'
            || l_max_trans_date
            || '|'
            || l_max_trans_qty
            || '|'
            || l_wip_qty_ytd * -1
            || '|'
            || l_wip_qty_lst_yr * -1
            || '|'
            || l_wip_qty_lst_prv_yr * -1
            || '|'
            || rec1.planning_time_fence_days
            || '|'
            || rec1.demand_time_fence_days
            || '|'
            || rec1.plan_code);
      --   END IF;
      END LOOP;
   EXCEPTION
      WHEN OTHERS
      THEN
         fnd_file.
          put_line (fnd_file.LOG, 'Error here             ' || SQLERRM);
   END;

   FUNCTION tpco_get_trans_quan (p_org_id    IN NUMBER,
                                 p_item_id   IN NUMBER,
                                 p_date      IN VARCHAR2)
      RETURN NUMBER
   IS
      l_wip_qty_ytd   NUMBER;
      l_date1         DATE := NULL;
      l_date2         DATE := NULL;
   BEGIN
      l_wip_qty_ytd := 0;
      --l_date1 := TO_DATE('01-JAN-'||p_date);
      --l_date2 := TO_DATE('31-DEC-'||p_date);
      /*  SELECT NVL (SUM (transaction_quantity), 0)
          INTO l_wip_qty_ytd
          FROM mtl_material_transactions mmt
         WHERE mmt.inventory_item_id = p_item_id
           AND mmt.organization_id = p_org_id
           AND transaction_type_id IN (35, 33)
           --   AND TRUNC(mmt.transaction_date) BETWEEN l_date1 AND l_date2;
           AND TO_CHAR (mmt.transaction_date, 'YYYY') = p_date;*/
      fnd_file.
       put_line (fnd_file.LOG,
                 ' in the proce' || p_org_id || 'p_date' || p_date);

      SELECT NVL (SUM (qty), 0)
        INTO l_wip_qty_ytd
        FROM tpco_wip_sale_issue_temp mmt
       WHERE     mmt.item_id = p_item_id
             AND mmt.org_id = p_org_id
             AND mmt.trans_year = p_date;

      fnd_file.
       put_line (
         fnd_file.LOG,
         ' l_wip_qty_ytd ' || l_wip_qty_ytd || 'p_item_id  ' || p_item_id);
      RETURN l_wip_qty_ytd;
   EXCEPTION
      WHEN OTHERS
      THEN
         fnd_file.
          put_line (fnd_file.LOG,
                    ' error in getting TPCO_WIP_SALE_ISSUE_TEMP ' || SQLERRM);
         l_wip_qty_ytd := 0;
         RETURN l_wip_qty_ytd;
   END;

   FUNCTION tpco_get_trans_quan_prv_yr (p_org_id    IN NUMBER,
                                        p_item_id   IN NUMBER,
                                        p_date      IN VARCHAR2)
      RETURN NUMBER
   IS
      l_wip_qty_ytd1     NUMBER;
      l_operating_unit   NUMBER;
   BEGIN
      l_wip_qty_ytd1 := 0;
      l_operating_unit := NULL;

      SELECT operating_unit
        INTO l_operating_unit
        FROM apps.org_organization_definitions
       WHERE organization_id = p_org_id;

      SELECT NVL (SUM (qty), 0)
        INTO l_wip_qty_ytd1
        FROM tpco_wip_sale_issue
       /* WHERE org_id IN (SELECT organization_id
                            FROM apps.org_organization_definitions
                           WHERE operating_unit = l_operating_unit)*/
       WHERE     org_id = p_org_id
             AND item_id = p_item_id
             AND trans_year = p_date;

      RETURN l_wip_qty_ytd1;
   EXCEPTION
      WHEN OTHERS
      THEN
         l_wip_qty_ytd1 := 0;
         RETURN l_wip_qty_ytd1;
   END;

   PROCEDURE tpco_load_mtl_txn_prv_yr (errbuff       OUT VARCHAR2,
                                       retcode       OUT VARCHAR2,
                                       p_org_id   IN     NUMBER,
                                       p_year     IN     VARCHAR2)
   IS
      l_exist       VARCHAR2 (1);
      l_oper_unit   NUMBER;
   BEGIN
      l_exist := NULL;
      l_oper_unit := NULL;

      BEGIN
         SELECT DISTINCT 'X'
           INTO l_exist
           FROM tpco_wip_sale_issue
          WHERE org_id = p_org_id AND trans_year = p_year;
      EXCEPTION
         WHEN OTHERS
         THEN
            l_exist := NULL;
      END;

      IF l_exist = 'X'
      THEN
         fnd_file.
          put_line (
            fnd_file.LOG,
            'Data Exists for this year for this Org. So deleteing the data for reloading');

         DELETE tpco_wip_sale_issue
          WHERE org_id = p_org_id AND trans_year = p_year;
      END IF;

      BEGIN
         SELECT operating_unit
           INTO l_oper_unit
           FROM apps.org_organization_definitions
          WHERE organization_id = p_org_id;
      EXCEPTION
         WHEN OTHERS
         THEN
            l_oper_unit := 161;
      END;

      IF l_oper_unit = 161
      THEN
         INSERT INTO tpco_wip_sale_issue (item_id,
                                          org_id,
                                          trans_year,
                                          item_type,
                                          qty)
              SELECT mmt.inventory_item_id,
                     p_org_id,
                     p_year,
                     msi.item_type,
                     SUM (primary_quantity)
                FROM mtl_material_transactions mmt, mtl_system_items_b msi
               WHERE     mmt.inventory_item_id = msi.inventory_item_id
                     AND mmt.organization_id = msi.organization_id
                     AND mmt.organization_id = p_org_id
                     --   AND mmt.transaction_type_id IN (35, 33,34,62)
                     AND mmt.transaction_type_id IN (35, 33)
                     -- Kieth wants for the previous years only sales and wip issue for previous years
                     AND TO_CHAR (mmt.transaction_date, 'YYYY') = p_year
                     AND msi.item_type IN ('P', 'FG', 'SA')
            GROUP BY mmt.inventory_item_id,
                     p_org_id,
                     p_year,
                     item_type;
      ELSE
         INSERT INTO tpco_wip_sale_issue (item_id,
                                          org_id,
                                          trans_year,
                                          item_type,
                                          qty)
              SELECT mmt.inventory_item_id,
                     p_org_id,
                     p_year,
                     msi.item_type,
                     SUM (primary_quantity)
                FROM mtl_material_transactions mmt, mtl_system_items_b msi
               WHERE     mmt.inventory_item_id = msi.inventory_item_id
                     AND mmt.organization_id = msi.organization_id
                     AND mmt.organization_id = p_org_id
                     AND mmt.transaction_type_id IN (35, 33, 34, 62)
                     --   AND mmt.transaction_type_id IN (35, 33)-- Kieth wants for the previous years only sales and wip issue for previous years
                     AND TO_CHAR (mmt.transaction_date, 'YYYY') = p_year
                     AND msi.item_type IN ('P', 'FG', 'SA')
            GROUP BY mmt.inventory_item_id,
                     p_org_id,
                     p_year,
                     item_type;
      END IF;

      COMMIT;
   EXCEPTION
      WHEN OTHERS
      THEN
         fnd_file.
          put_line (fnd_file.LOG, 'Error loading the table' || SQLERRM);
         ROLLBACK;
   END;
END tpco_supp_release_stat;
/

Author

Commented:
i dont know the how to attach the package and package specifications
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
What function/procedure are you looking to tune?  I don't think anyone here will walk through all that code and tune the entire package.

It also looks like you posted the same code twice?  Which one is correct?

Author

Commented:
they both are the same by mistake i have pasted them

The packae has the procedure  PROCEDURE tpco_get_pur_usage_all_org (errbuff          OUT VARCHAR2,
                                         retcode          OUT VARCHAR2,
                                         p_global      IN     VARCHAR2,
                                         p_org_id      IN     NUMBER,
                                         p_date        IN     VARCHAR2,
                                         p_item_type   IN     VARCHAR2)

is the one which is called from the concurrent program
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
>>The packae has the procedure  PROCEDURE tpco_get_pur_usage_all_org

Basically all tpco_get_pur_usage_all_org does in go int oa loop and call    tpco_get_purchase_itm_dtl many times.

I would debug tpco_get_purchase_itm_dtl.  I would create a timing table and insert records into the table at several points throughout tpco_get_purchase_itm_dtl until you can narrow down what is causing the problems then focus your efforts there.