Script to update the locator in MTL_RESERVATIONS

CREATE OR REPLACE PROCEDURE CLC_MTL_RESERVATIONS_PRG (
   errbuf    OUT VARCHAR2,
   retcode   OUT NUMBER,
  p_org_code         VARCHAR2
)
AS
l_rsv_old inv_reservation_global.mtl_reservation_rec_type;
l_rsv_new inv_reservation_global.mtl_reservation_rec_type;
l_msg_count NUMBER;
l_msg_data VARCHAR2 (240);
l_rsv_id NUMBER;
l_dummy_sn inv_reservation_global.serial_number_tbl_type;
l_status VARCHAR2 (1);
l_quantity_reserved NUMBER;
l_cnt number :=0;
l_err number :=0;

cursor c_resv (l_org_code number)is
select ooha.order_number,oola.header_id, oola.line_id, mr.RESERVATION_ID,msi.segment1--,wdd.RELEASED_STATUS
from MTL_RESERVATIONS mr,
oe_order_lines_all oola,
oe_order_headers_all ooha,
mtl_system_items_b msi,
wsh_delivery_details wdd
where
ooha.header_id =oola.header_id
and ooha.org_id =oola.org_id
and oola.line_id = mr.DEMAND_SOURCE_LINE_ID
and mr.INVENTORY_ITEM_ID =msi.INVENTORY_ITEM_ID
and mr.ORGANIZATION_ID = msi.ORGANIZATION_ID
and wdd.SOURCE_HEADER_ID = ooha.header_id
and wdd.SOURCE_LINE_ID = oola.line_id
and mr.DEMAND_SOURCE_TYPE_ID=2
and mr.ORGANIZATION_ID =l_org_code
--and mr.INVENTORY_ITEM_ID=32299
and (wdd.RELEASED_STATUS ='R'
or not exists (select 'A' from wsh_delivery_assignments wda, wsh_new_deliveries wnd
where wda.delivery_detail_id = wdd.delivery_detail_id
AND wda.delivery_id = wnd.delivery_id));

/*select oola.header_id, oola.line_id, mr.RESERVATION_ID
from MTL_RESERVATIONS mr,
oe_order_lines_all oola
where
oola.line_id = mr.DEMAND_SOURCE_LINE_ID
and mr.DEMAND_SOURCE_TYPE_ID=2
and ORGANIZATION_ID =l_org_code
--and mr.INVENTORY_ITEM_ID=32299
and not exists (select 'A' from wsh_delivery_details wdd
where  wdd.SOURCE_HEADER_ID =oola.HEADER_ID and
 wdd.SOURCE_LINE_ID=oola.line_id);*/

BEGIN

--fnd_global.apps_initialize (10688,50606,401);

   FND_FILE.PUT_LINE (FND_FILE.LOG, 'Initializing Apps');
   FND_GLOBAL.Apps_Initialize (FND_GLOBAL.USER_ID,
                               FND_GLOBAL.RESP_ID,
                               FND_GLOBAL.RESP_APPL_ID);
 --- Could be "located" by order_header_id, order_line_id, item and warehouse - in case Several reservations That Could be updated

   fnd_file.put_line (fnd_file.LOG, 'p_org_code-' ||p_org_code);
l_cnt :=0;
l_err :=0;
FOR l_resv IN c_resv (p_org_code)
LOOP

l_rsv_old.reservation_id := l_resv.reservation_id;

-- Specify the new values
l_rsv_new.reservation_id := l_resv.reservation_id;
--l_rsv_new.primary_reservation_quantity: = 10;
--l_rsv_new.requirement_date: = SYSDATE + 2;
l_rsv_new.LOCATOR_ID := null;
     
inv_reservation_pub.update_reservation (
p_api_version_number => 1.0
, P_init_msg_lst => fnd_api.g_true
, X_return_status => l_status
, X_msg_count => l_msg_count
, X_msg_data => l_msg_data
, P_original_rsv_rec => l_rsv_old
, P_to_rsv_rec => l_rsv_new
, P_original_serial_number => l_dummy_sn -- no serial contorl
, p_to_serial_number  => L_dummy_sn -- no serial control
, P_validation_flag => fnd_api.g_true
, P_check_availability => fnd_api.g_false
, P_over_reservation_flag => 0
);
IF  l_status = fnd_api.g_ret_sts_success THEN
--dbms_output.put_line ( 'reservation updated');
   fnd_file.put_line (fnd_file.LOG, 'reservation updated for Order Number' || l_resv.order_number ||' reservation_id:' ||l_resv.RESERVATION_ID ||' Item:' ||l_resv.segment1 );
   l_cnt :=l_cnt+1;
ELSE
IF l_msg_count>= 1 THEN
FOR  I IN 1..l_msg_count
LOOP
--dbms_output.put_line (  SUBSTR (FND_MSG_PUB.Get (p_encoded => FND_API.G_FALSE), 1, 255) );
--fnd_file.put_line (fnd_file.log, I || || SUBSTR (FND_MSG_PUB.Get (p_encoded => FND_API.G_FALSE), 1, 255) '.');
fnd_file.put_line (fnd_file.LOG, 'reservation error out for Order Number' || l_resv.order_number ||' reservation_id:' ||l_resv.RESERVATION_ID ||' Item:' ||l_resv.segment1 ||'-'||SUBSTR (FND_MSG_PUB.Get (p_encoded => FND_API.G_FALSE), 1, 255));
END LOOP;

   l_err :=l_err+1;
END IF;
END IF;
COMMIT;

END LOOP;
   fnd_file.put_line (fnd_file.LOG, 'total reservation updated succesfully:' ||l_cnt );
 --  fnd_file.put_line (fnd_file.LOG, 'total reservation updated succesfully:' ||l_err );

EXCEPTION
   WHEN OTHERS
   THEN
      FND_FILE.PUT_LINE (FND_FILE.LOG, ' OTHER exception:- '||sqlerrm);
END CLC_MTL_RESERVATIONS_PRG;
/

link between wsh_delivery_details and wsh_new_deliveries

select
ooha.order_number,
wdd.DELIVERY_DETAIL_ID ,
wda.delivery_detail_id,
wnd.delivery_id
from
oe_order_headers_all ooha,
oe_order_lines_all oola
,wsh_new_deliveries wnd
,wsh_delivery_assignments wda
,wsh_delivery_details wdd
where wdd.SOURCE_HEADER_ID=ooha.HEADER_ID
AND wdd.source_line_id = ool.line_id
and ooha.header_id=oola.HEADER_ID
AND wdd.delivery_detail_id = wda.delivery_detail_id
AND wnd.delivery_id = wda.delivery_id
and ooha.ORDER_NUMBER='10084042'

supplier unpaid query( Iink between po_headers_all and ap_invoices_all)

  SELECT   DISTINCT ou.name "OPERATING UNIT",
                    pv.segment1 "Supplier GSL",
                    pv.vendor_name "Supplier Name"  ,
                    apt1.name "Supplier Header Terms",
                    apt.NAME "Supplier Site Terms",
                    ph.segment1 "PO#",
                    ph.CLOSED_CODE "PO Status",
                    (SELECT   name
                       FROM   ap_terms
                      WHERE   term_id = ph.terms_id)
                       "PO Terms",
                    aia.invoice_num,
                    (SELECT   name
                       FROM   ap_terms
                      WHERE   term_id = aia.terms_id)
                       "AP Terms",
                  decode(CANCELLED_DATE,null,'', 'Cancelled')   "AP Status"
    --      ,apt.description
    --      ,pvsa.PAY_DATE_BASIS_LOOKUP_CODE "Pay Date Basis"
    FROM   apps.AP_SUPPLIERS pv,
           apps.AP_SUPPLIER_SITES_ALL pvsa,
           apps.ap_invoices_all aia,
          -- apps.ap_invoice_payments_all aip,
           ap_invoice_distributions_all aida,
           (SELECT   *
              FROM   apps.ap_terms_tl
             WHERE   lANGUAGE = 'US') apt,
           (SELECT   *
              FROM   apps.ap_terms_tl
             WHERE   lANGUAGE = 'US') apt1,
           po_headers_all ph,
           po_lines_all pla,
           po_distributions_all pda,
           hr_operating_units ou,
           ap_tolerance_templates att
   WHERE       pv.vendor_id = pvsa.vendor_id
           AND pv.vendor_id = aia.vendor_id
           AND pvsa.VENDOR_SITE_ID = aia.VENDOR_SITE_ID
           AND ou.organization_id = pvsa.org_id
          -- AND aip.invoice_id = aia.invoice_id
           AND pvsa.TERMS_ID = apt.TERM_ID(+)
           AND pv.TERMS_ID = apt1.TERM_ID(+)
           AND pvsa.TOLERANCE_ID = att.TOLERANCE_ID(+)
           AND ph.vendor_id = pv.vendor_id
           AND ph.org_id = pvsa.org_id
           AND ph.po_header_id = pla.po_header_id
           AND ph.org_id = pla.org_id
           AND ph.po_header_id = pda.po_header_id
           AND pla.po_line_id = pda.po_line_id
           AND ph.org_id = pda.org_id
           AND aida.invoice_id = aia.invoice_id
           AND pda.org_id = aida.org_id
           AND aia.org_id = aida.org_id
           AND pda.po_distribution_id = aida.po_distribution_id
           AND aia.PAYMENT_STATUS_FLAG = 'N'
           AND NVL (pv.end_DATE_ACTIVE, SYSDATE) >= SYSDATE
         -- and ph.segment1 ='700021845'
          --and CANCELLED_DATE is null
         --  AND pv.segment1 = 'E52029'
ORDER BY   pv.segment1 ASC

Supplier Extract Query

select distinct ou.name "OPERATING UNIT"
       ,pv.segment1            "Supplier Number"
       ,pv.vendor_name        "Supplier Name"
       ,PV.END_DATE_ACTIVE  "Inactive Date"
--       ,pv.NUM_1099 "Taxpayer ID"
--       ,pvsa.VAT_REGISTRATION_NUM "Tax Registration Number"
--       ,PV.ORGANIZATION_TYPE_LOOKUP_CODE "Organization Type"
--       ,pvsa.country "Site Country"
       ,pvsa.VENDOR_SITE_CODE "Site Name"
--       ,pvsa.ADDRESS_LINE1         "Site Address Line 1"
--       ,pvsa.ADDRESS_LINE2         "Site Address Line 2"
       --,pvsa.ADDRESS_LINE3         "Address Line3"
--       ,pvsa.CITY              "Site City"
--       ,pvsa.STATE             "Site State"
--       ,pvsa.PROVINCE          "Province"
--       ,pvsa.ZIP               "Postal Code"
--       ,pvsa.INACTIVE_DATE     "Site Status"
--       ,pvsa.AREA_CODE          "Phone Area Code"
--       ,pvsa.PHONE              "Phone Number"
--       ,pvsa.FAX              "Fax Number"             
--       ,pvsa.FAX_AREA_CODE      "Fax Area Code"
--       ,pvsa.EMAIL_ADDRESS        "Email Address"
--       ,PVSA.PAY_SITE_FLAG      "Address Purpose Payment"
--       ,PVSa.PURCHASING_SITE_FLAG "Address Purpose Purchasing"
       ,pvsa.PAYMENT_METHOD_LOOKUP_CODE "Site Payment Method"
       ,pv.PAYMENT_METHOD_LOOKUP_CODE "Header Payment Method"
--       ,pvsa.INVOICE_CURRENCY_CODE "Invoice Currency"
--       ,pvsa.PAYMENT_CURRENCY_CODE "Payment Currency"
--       ,pvsa.PAY_GROUP_LOOKUP_CODE "Pay Group"
--       ,att.TOLERANCE_NAME "Invoice Tolerance"  -- pvsa.TOLERANCE_ID
--       ,pv.MATCH_OPTION "Invoice Match Option"
       ,apt.NAME    "Site Payment Terms"
       ,apt1.name "Header Payment Terms"
     -- ,apt.NAME                        "Payment Terms"
--      ,apt.description
--      ,pvsa.PAY_DATE_BASIS_LOOKUP_CODE "Pay Date Basis"
from apps.AP_SUPPLIERS pv
    ,apps.AP_SUPPLIER_SITES_ALL pvsa
   -- ,apps.ap_invoices_all aia
    --,apps.ap_invoice_payments_all aip
    ,(select * from apps.ap_terms_tl where lANGUAGE = 'US') apt
    ,(select * from apps.ap_terms_tl where lANGUAGE = 'US') apt1
    ,hr_operating_units ou
    ,ap_tolerance_templates att
where  pv.vendor_id=pvsa.vendor_id
--AND pv.vendor_id=aia.vendor_id
--AND pvsa.VENDOR_SITE_ID = aia.VENDOR_SITE_ID
AND ou.organization_id=pvsa.org_id
--AND  trunc(aia.CREATION_DATE) BETWEEN nvl(trunc(to_date(:cp_trx_date_low,'yyyy/MM/DD HH24:MI:SS')),trunc(aia.CREATION_DATE))
--                                   AND     nvl(trunc(to_date(:cp_trx_date_high,'yyyy/MM/DD HH24:MI:SS')),trunc(aia.CREATION_DATE))
--AND aip.invoice_id = aia.invoice_id
and  pvsa.TERMS_ID = apt.TERM_ID(+)
AND  pv.TERMS_ID = apt1.TERM_ID(+)
AND  pvsa.TOLERANCE_ID = att.TOLERANCE_ID(+)
--AND pvsa.PAY_DATE_BASIS_LOOKUP_CODE='DISCOUNT'
Order by pv.segment1 asc