PO - RCV - INV - XLA - GL

/* Formatted on 03-Nov-15 2:28:17 PM (QP5 v5.114.809.3010) */
/* Formatted on 03-Nov-15 2:28:45 PM (QP5 v5.114.809.3010) */
SELECT   *
  FROM   po_headers_all
 WHERE   segment1 = 1361170636

-- Accounting Entries for PO --

/* Formatted on 03-Nov-15 2:28:49 PM (QP5 v5.114.809.3010) */
SELECT   *
  FROM   po_headers_all
 WHERE   segment1 = 1361170636

--PO Receipt --
/* Formatted on 03-Nov-15 2:28:52 PM (QP5 v5.114.809.3010) */
SELECT   *
  FROM   rcv_transactions
 WHERE   po_header_id = 1182531

--RECEIVE     --  RECEIVING   --  rcv_receiving_sub_ledger
--DELIVER     -- INVENTORY  --  mtl_material_transactions

-- For DELIVER
/* Formatted on 03-Nov-15 2:28:55 PM (QP5 v5.114.809.3010) */
SELECT   *
  FROM   mtl_material_transactions
 WHERE   RCV_TRANSACTION_ID IN (SELECT   transaction_id
                                  FROM   rcv_transactions
                                 WHERE   po_header_id = 1182531)
-- 2699101
/* Formatted on 03-Nov-15 2:29:00 PM (QP5 v5.114.809.3010) */
SELECT   *
  FROM   mtl_transaction_accounts
 WHERE   transaction_id = 18820381
      -- 18820381
-- For Receive
--Accrual_Method_Flag = 'O'
--JE_SOURCE_NAME ='Purchasing'

--Transactions with accrue_on_receipt_flag ='N' ie Period End Accruals are
--      classified as:
--Accrual_Method_Flag = 'P'
--JE_SOURCE_NAME ='Purchasing'

select * from rcv_receiving_sub_ledger where RCV_TRANSACTION_ID in (select
      transaction_id
      from rcv_transactions
      where po_header_id = 1182531 )

/* Formatted on 03-Nov-15 2:28:30 PM (QP5 v5.114.809.3010) */
SELECT   jel.*
  FROM   gl_import_references gli,
         xla_ae_lines xll,
         xla_ae_headers xlh,
         xla_distribution_links xld,
         rcv_receiving_sub_ledger rsl,
         gl_je_lines jel
 WHERE       gli.gl_sl_link_table = xll.gl_sl_link_table
         AND gli.gl_sl_link_id = xll.gl_sl_link_id
         AND xll.ae_header_id = xlh.ae_header_id
         AND xld.ae_header_id = xlh.ae_header_id
         AND jel.je_header_id = gli.je_header_id
         AND jel.je_line_num = gli.je_line_num
         AND xld.source_distribution_id_num_1 = rsl.rcv_sub_ledger_id
         AND rsl.RCV_TRANSACTION_ID IN (SELECT   transaction_id
                                          FROM   rcv_transactions
                                         WHERE   po_header_id = 1182531)
         AND xld.source_distribution_type = 'RCV_RECEIVING_SUB_LEDGER'
         AND xll.gl_sl_link_table = 'XLAJEL'

How to open Oracle Apps R12 forms in Internet Explorer 11 (IE. 11)

1) In internet explorer 11 , navigate to Tools –> Compatibility View Settings . Then add Domain name in the URL (ex: orapps.com in erp.oraapps.com) using Add this website option.

2) Navigate to Tools –> Internet options –> Security . Select internet and click on Custom level. Scroll down till end of the options and change the option Enable XSS filter to  Disable.

This solution worked for me and I am able to open R12 forms in Internet Explorer version 11.

Restart Browser and Try again

The Bill To address id must exist in Oracle Receivables, and it must be assigned to the Bill To customer (ORIG_SYSTEM_BILL_ADDRESS_ID) (33778)

The Bill To address id must exist in Oracle Receivables, and it must be assigned to the Bill To customer (ORIG_SYSTEM_BILL_ADDRESS_ID) (33778)


select
      c.cust_acct_site_id orig_system_bill_address_id,
      b.cust_account_id orig_system_bill_customer_id
from
      hz_parties a,
      hz_cust_accounts b,
      hz_cust_acct_sites_all c,
      hz_cust_site_uses_all d
where
      b.ACCOUNT_NUMBER = 'U99995'  --Enter Your Customer Name Here
and a.party_id = b.party_id
and c.cust_account_id = b.cust_account_id
and c.cust_acct_site_id = d.cust_acct_site_id
and d.site_use_code = 'BILL_TO';


select ORIG_SYSTEM_BILL_ADDRESS_ID from RA_INTERFACE_LINES_all where interface_line_id = 1702930
-- current value - 33778

update RA_INTERFACE_LINES_all
set ORIG_SYSTEM_BILL_ADDRESS_ID = 34035
where interface_line_id = 1702930

R12 Sales Person Query - OM

select   rs.salesrep_number,
         --rs.NAME salesrep_name,
         res.resource_name salesrep_name,
         hou.NAME org_name,
         rs.salesrep_id,
         rs.org_id,
         rs.start_date_active,
         rs.end_date_active
  FROM   apps.jtf_rs_salesreps rs,
         apps.JTF_RS_RESOURCE_EXTNS_VL RES,
         hr_organization_units hou
  WHERE  hou.organization_id = rs.org_id
    AND  rs.resource_id = res.resource_id ;

Payroll Update Employee Email / Attributes

DECLARE
   l_employee_number             per_people_f.employee_number%TYPE;
   l_person_id                   per_people_f.person_id%TYPE;
   l_per_object_version_number   per_people_f.object_version_number%TYPE;
   v_effective_start_date        date;
   v_effective_end_date          date;
   v_full_name                   varchar2 (100);
   v_comment_id                  number;
   v_name_combination_warning    boolean;
   v_assign_payroll_warning      boolean;
   v_orig_hire_warning           boolean;
   Cursor update_flag is select * from xyka_update_email ;
BEGIN
 
   FOR rec IN UPDATE_FLAG
   Loop


   SELECT   max(object_version_number), employee_number , person_id
     INTO   l_per_object_version_number, l_employee_number , l_person_id
     FROM   per_people_f
    WHERE   EMPLOYEE_NUMBER = rec.EMP_NO
     AND SYSDATE BETWEEN effective_start_date and effective_end_date
     Group by object_version_number , employee_number , person_id;
    --person_id = l_person_id;

   hr_person_api.update_person (
      p_validate                   => FALSE,
      p_effective_date             => TO_DATE ('01-JUN-2015', 'DD-MON-YYYY'),
      p_datetrack_update_mode      => 'CORRECTION',
      p_person_id                  => l_person_id,
      p_object_version_number      => l_per_object_version_number,
      p_employee_number            => l_employee_number,
      p_email_address              => rec.EMAIL,
      p_attribute6                 => 'Y',
      p_effective_start_date       => v_effective_start_date,
      p_effective_end_date         => v_effective_end_date,
      p_full_name                  => v_full_name,
      p_comment_id                 => v_comment_id,
      p_name_combination_warning   => v_name_combination_warning,
      p_assign_payroll_warning     => v_assign_payroll_warning,
      p_orig_hire_warning          => v_orig_hire_warning
   );
 
   COMMIT;

end loop;

  -- COMMIT;
END;
/






select attribute6 from per_all_people_f
where ATTRIBUTE6 is not null
and LAST_UPDATE_DATE is SYSDATE

Cancel Sales Order Lines

DECLARE
v_api_version_number           NUMBER  := 1;
v_return_status                VARCHAR2 (2000);
v_msg_count                    NUMBER;
v_msg_data                     VARCHAR2 (2000);

-- IN Variables --
v_header_rec                   oe_order_pub.header_rec_type;
v_line_tbl                     oe_order_pub.line_tbl_type;
v_action_request_tbl           oe_order_pub.request_tbl_type;
v_line_adj_tbl                 oe_order_pub.line_adj_tbl_type;

-- OUT Variables --
v_header_rec_out               oe_order_pub.header_rec_type;
v_header_val_rec_out           oe_order_pub.header_val_rec_type;
v_header_adj_tbl_out           oe_order_pub.header_adj_tbl_type;
v_header_adj_val_tbl_out       oe_order_pub.header_adj_val_tbl_type;
v_header_price_att_tbl_out     oe_order_pub.header_price_att_tbl_type;
v_header_adj_att_tbl_out       oe_order_pub.header_adj_att_tbl_type;
v_header_adj_assoc_tbl_out     oe_order_pub.header_adj_assoc_tbl_type;
v_header_scredit_tbl_out       oe_order_pub.header_scredit_tbl_type;
v_header_scredit_val_tbl_out   oe_order_pub.header_scredit_val_tbl_type;
v_line_tbl_out                 oe_order_pub.line_tbl_type;
v_line_val_tbl_out             oe_order_pub.line_val_tbl_type;
v_line_adj_tbl_out             oe_order_pub.line_adj_tbl_type;
v_line_adj_val_tbl_out         oe_order_pub.line_adj_val_tbl_type;
v_line_price_att_tbl_out       oe_order_pub.line_price_att_tbl_type;
v_line_adj_att_tbl_out         oe_order_pub.line_adj_att_tbl_type;
v_line_adj_assoc_tbl_out       oe_order_pub.line_adj_assoc_tbl_type;
v_line_scredit_tbl_out         oe_order_pub.line_scredit_tbl_type;
v_line_scredit_val_tbl_out     oe_order_pub.line_scredit_val_tbl_type;
v_lot_serial_tbl_out           oe_order_pub.lot_serial_tbl_type;
v_lot_serial_val_tbl_out       oe_order_pub.lot_serial_val_tbl_type;
v_action_request_tbl_out       oe_order_pub.request_tbl_type;

BEGIN

DBMS_OUTPUT.PUT_LINE('Starting of script');

-- Setting the Enviroment --

mo_global.init('ONT');
fnd_global.apps_initialize ( user_id      => 1793
                            ,resp_id      => 52751
                            ,resp_appl_id => 660);
mo_global.set_policy_context('S',613);

FOR  I IN (select oeh.header_id, oel.line_id, oel.flow_status_code from oe_order_headers_all oeh, oe_order_lines_all oel
where  oeh.header_id = oel.header_id
and    oeh.ordered_date between '01-Jun-13' AND '30-Dec-13'
and    oeh.bookED_flag = 'Y' and oeh.open_flag = 'Y' AND OEH.CANCELLED_FLAG = 'N'
and    oel.flow_status_code not in ('CLOSED', 'CANCELLED','AWAITING_FULFILLMENT','PRE-BILLING_ACCEPTANCE')
--AND    OEH.ORDER_NUMBER = '1331211155'
AND    OEH.ORG_ID = 613)
LOOP
         

v_action_request_tbl (1) := oe_order_pub.g_miss_request_rec;


-- Cancel a Line Record --
v_line_tbl (1)                      := oe_order_pub.g_miss_line_rec;
v_line_tbl (1).operation            := OE_GLOBALS.G_OPR_UPDATE;
v_line_tbl (1).header_id            := I.HEADER_ID;
v_line_tbl (1).line_id              := I.LINE_ID;
v_line_tbl (1).ordered_quantity     := 0;
v_line_tbl (1).cancelled_flag       := 'Y';
v_line_tbl (1).change_reason        := 'SYSTEM';

DBMS_OUTPUT.PUT_LINE('Starting of API');

-- Calling the API to cancel a line from an Existing Order --

OE_ORDER_PUB.PROCESS_ORDER (
p_api_version_number            => v_api_version_number
, p_header_rec                  => v_header_rec
, p_line_tbl                    => v_line_tbl
, p_action_request_tbl          => v_action_request_tbl
, p_line_adj_tbl                => v_line_adj_tbl
, x_header_rec                  => v_header_rec_out
, x_header_val_rec              => v_header_val_rec_out
, x_header_adj_tbl              => v_header_adj_tbl_out
, x_header_adj_val_tbl          => v_header_adj_val_tbl_out
, x_header_price_att_tbl        => v_header_price_att_tbl_out
, x_header_adj_att_tbl          => v_header_adj_att_tbl_out
, x_header_adj_assoc_tbl        => v_header_adj_assoc_tbl_out
, x_header_scredit_tbl          => v_header_scredit_tbl_out
, x_header_scredit_val_tbl      => v_header_scredit_val_tbl_out
, x_line_tbl                    => v_line_tbl_out
, x_line_val_tbl                => v_line_val_tbl_out
, x_line_adj_tbl                => v_line_adj_tbl_out
, x_line_adj_val_tbl            => v_line_adj_val_tbl_out
, x_line_price_att_tbl          => v_line_price_att_tbl_out
, x_line_adj_att_tbl            => v_line_adj_att_tbl_out
, x_line_adj_assoc_tbl          => v_line_adj_assoc_tbl_out
, x_line_scredit_tbl            => v_line_scredit_tbl_out
, x_line_scredit_val_tbl        => v_line_scredit_val_tbl_out
, x_lot_serial_tbl              => v_lot_serial_tbl_out
, x_lot_serial_val_tbl          => v_lot_serial_val_tbl_out
, x_action_request_tbl          => v_action_request_tbl_out
, x_return_status               => v_return_status
, x_msg_count                   => v_msg_count
, x_msg_data                    => v_msg_data
);

DBMS_OUTPUT.PUT_LINE('Completion of API');


IF v_return_status = fnd_api.g_ret_sts_success THEN
    COMMIT;
    DBMS_OUTPUT.put_line ('Line Cancelation in Existing Order is Success ');
ELSE
    DBMS_OUTPUT.put_line ('Line Cancelation in Existing Order failed:'||v_msg_data);
    ROLLBACK;
    FOR i IN 1 .. v_msg_count
    LOOP
      v_msg_data := oe_msg_pub.get( p_msg_index => i, p_encoded => 'F');
      dbms_output.put_line( i|| ') '|| v_msg_data);
    END LOOP;
END IF;
END LOOP;
END;
/




SELECT OPEN_FLAG, BOOKED_FLAG, CANCELLED_FLAG
FROM OE_ORDER_HEADERS_ALL
WHERE ORDER_NUMBER = '1531210964'

Order Receivable Invoice Query

SELECT   ooh.order_number,
         --    ool.line_id,
         ool.ordered_quantity,
         ool.shipped_quantity,
         ool.invoiced_quantity,
         wdd.delivery_detail_id,
         wnd.delivery_id,
         TO_CHAR (wdd.CREATION_DATE, 'DD-MON-YY') delivery_cre_date,
         (SELECT   trx_number
            FROM   ra_customer_trx_all rct, ra_customer_trx_lines_all rctl
           WHERE   rctl.interface_line_attribute1 =
                      TO_CHAR (ooh.order_number)
                   AND rctl.interface_line_attribute6 = TO_CHAR (ool.line_id)
                   AND rctl.interface_line_attribute3 =
                         TO_CHAR (wnd.delivery_id)
                   AND rctl.customer_trx_id = rct.customer_trx_id
                   AND rct.interface_header_context = 'ORDER ENTRY')
            trx_number
  FROM   oe_order_headers_all ooh,
         oe_order_lines_all ool,
         wsh_delivery_details wdd,
         wsh_new_deliveries wnd,
         wsh_delivery_assignments wda
 WHERE       ooh.header_Id = ool.header_id
         AND ooh.org_id = ool.org_id
         AND wdd.source_header_id = ooh.header_id
         AND wdd.SOURCE_LINE_ID = ool.line_id
         AND wdd.delivery_detail_Id = wda.delivery_detail_id
         AND wda.delivery_id = wnd.delivery_id
         -- AND rctl.interface_line_attribute1 = TO_CHAR (ooh.order_number)
         -- AND rctl.interface_line_attribute6 = TO_CHAR (ool.line_id)
         -- AND rctl.interface_line_attribute3 = TO_CHAR (wnd.delivery_id)
         -- AND rctl.customer_trx_id = rct.customer_trx_id
         -- AND rct.interface_header_context = 'ORDER ENTRY'
         AND wdd.CREATION_DATE BETWEEN '01-MAY-15' AND '30-MAY-15'
--and wda.delivery_id = 2555078
--  and ooh.order_number = 1531311781
-- AND rct.org_id = 613

AR Joins

All Below Queries are based on Input Parameters- p_as_of_date & p_account_number

-- Query for Customer Transaction Balance

SELECT NVL (SUM (ps.amount_due_remaining), 0) invoice_balance
FROM ra_cust_trx_types_all rtt,
ra_customer_trx_all rta,
ra_cust_trx_line_gl_dist_all rgld,
gl_code_combinations cc,
hz_cust_accounts_all cust_acct,
ar_payment_schedules_all ps,
hz_cust_acct_sites_all acct_site,
hz_party_sites party_site,
hz_locations loc,
hz_cust_site_uses_all site_uses
WHERE TRUNC (ps.gl_date) <= :p_as_of_date AND cust_acct.account_number = :p_account_number AND ps.customer_id = cust_acct.cust_account_id AND ps.cust_trx_type_id = rtt.cust_trx_type_id AND ps.trx_date <= :p_as_of_date AND ps.CLASS NOT IN ('CM', 'PMT') AND site_uses.site_use_code = 'BILL_TO' AND acct_site.party_site_id = party_site.party_site_id AND loc.location_id = party_site.location_id AND NVL (site_uses.status, 'A') = 'A' AND cust_acct.cust_account_id = acct_site.cust_account_id AND acct_site.cust_acct_site_id = site_uses.cust_acct_site_id AND ps.customer_id = acct_site.cust_account_id AND ps.customer_site_use_id = site_uses.site_use_id AND rta.customer_trx_id = ps.customer_trx_id AND rta.customer_trx_id = rgld.customer_trx_id AND rgld.code_combination_id = cc.code_combination_id AND rgld.account_class = 'REV'

-- Query for Credit Memo Balance

SELECT NVL (SUM (ps.amount_due_remaining), 0) cr_memo_balance
FROM ra_cust_trx_types_all rtt,
ra_customer_trx_all rta,
ra_cust_trx_line_gl_dist_all rgld,
gl_code_combinations cc,
hz_cust_accounts_all cust_acct,
ar_payment_schedules_all ps,
hz_cust_acct_sites_all acct_site,
hz_party_sites party_site,
hz_locations loc,
hz_cust_site_uses_all site_uses
WHERE TRUNC (ps.gl_date) <= :p_as_of_date
AND cust_acct.account_number = :p_account_number
AND ps.customer_id = cust_acct.cust_account_id
AND ps.cust_trx_type_id = rtt.cust_trx_type_id
AND ps.trx_date <= :p_as_of_date
AND ps.CLASS = 'CM'
AND ps.status = 'OP'
AND site_uses.site_use_code = 'BILL_TO'
AND site_uses.cust_acct_site_id = acct_site.cust_acct_site_id
AND acct_site.party_site_id = party_site.party_site_id
AND loc.location_id = party_site.location_id
AND NVL (site_uses.status, 'A') = 'A'
AND cust_acct.cust_account_id = acct_site.cust_account_id
AND acct_site.cust_acct_site_id = site_uses.cust_acct_site_id
AND ps.customer_id = acct_site.cust_account_id
AND ps.customer_site_use_id = site_uses.site_use_id
AND rta.customer_trx_id = ps.customer_trx_id
AND rta.customer_trx_id = rgld.customer_trx_id
AND rgld.code_combination_id = cc.code_combination_id
AND rgld.account_class = 'REV'

Purchase Requisition Approval Status

/* Formatted on 21-Apr-15 12:00:02 PM (QP5 v5.114.809.3010) */
  SELECT   DISTINCT prha.segment1 req,
                    prha.DESCRIPTION ,
                    AUTHORIZATION_STATUS,
                    papf.full_name req_raised_by,
                    reqah.full_name req_last_approver,
                    DECODE (AUTHORIZATION_STATUS,
                            'APPROVED',
                            NULL,
                            'IN PROCESS',
                            reqah1.full_name,
                            NULL)
                       pending_with
    FROM   po.po_requisition_headers_all prha,
           po.po_requisition_lines_all prla,
           hr.per_all_people_f papf,
           (SELECT   papf.full_name, pah.action_code, pah.object_id
              FROM   po.po_action_history pah,
                     po.po_requisition_headers_all prha,
                     applsys.fnd_user fu,
                     hr.per_all_people_f papf
             WHERE       object_id = prha.requisition_header_id
                     AND pah.employee_id = fu.employee_id
                     AND fu.employee_id = papf.person_id
                     AND SYSDATE BETWEEN papf.effective_start_date
                                     AND  papf.effective_end_date
                     AND pah.object_type_code = 'REQUISITION'
                     AND pah.action_code = 'APPROVE'
                     AND pah.sequence_num =
                           (SELECT   MAX (sequence_num)
                              FROM   po.po_action_history pah1
                             WHERE       pah1.object_id = pah.object_id
                                     AND pah1.object_type_code = 'REQUISITION'
                                     AND pah1.action_code = 'APPROVE')) reqah,
           (SELECT   DISTINCT papf.full_name, pah.action_code, pah.object_id
              FROM   po.po_action_history pah,
                     po.po_requisition_headers_all prha,
                     applsys.fnd_user fu,
                     hr.per_all_people_f papf
             WHERE       prha.requisition_header_id = object_id
                     AND prha.requisition_header_id = pah.OBJECT_ID
                     AND pah.employee_id = fu.employee_id
                     AND fu.employee_id = papf.person_id
                     AND SYSDATE BETWEEN papf.effective_start_date
                                     AND  papf.effective_end_date
                     AND pah.object_type_code = 'REQUISITION'
                     AND pah.sequence_num =
                           (SELECT   MAX (sequence_num)
                              FROM   po.po_action_history pah1
                             WHERE   pah1.object_id = pah.object_id
                                     AND pah1.object_type_code = 'REQUISITION'))   reqah1
   WHERE       prha.requisition_header_id = prla.requisition_header_id
           AND reqah.object_id = prha.requisition_header_id
           AND reqah1.object_id = prha.requisition_header_id
           AND prha.preparer_id = papf.person_id
           AND SYSDATE BETWEEN papf.effective_start_date
                           AND  papf.effective_end_date
           AND prha.creation_date >= :start_date
           AND prha.creation_date <= :end_date
           AND prha.ORG_ID = :org_id --  in (611 , 612 , 613,614 , 615 , 616 , 617)
--  AND prha.segment1 = 1531610037
ORDER BY    3 desc

select * from hr_operating_units

Shipping API

/* Formatted on 31-Dec-14 1:29:47 PM (QP5 v5.114.809.3010) */
DECLARE
   x_return_status          VARCHAR2 (3000);
   x_msg_count              NUMBER;
   x_msg_data               VARCHAR2 (3000);
   --Standard Parameters.
   p_api_version_number     NUMBER;
   init_msg_list            VARCHAR2 (30);
   x_msg_details            VARCHAR2 (3000);
   x_msg_summary            VARCHAR2 (3000);
   p_validation_level       NUMBER;
   p_commit                 VARCHAR2 (30);
   --Parameters for WSH_DELIVERIES_PUB.Delivery_Action.
   p_action_code            VARCHAR2 (15);
   p_delivery_id            NUMBER;
   p_delivery_name          VARCHAR2 (30);
   p_asg_trip_id            NUMBER;
   p_asg_trip_name          VARCHAR2 (30);
   p_asg_pickup_stop_id     NUMBER;
   p_asg_pickup_loc_id      NUMBER;
   p_asg_pickup_loc_code    VARCHAR2 (30);
   p_asg_pickup_arr_date    DATE;
   p_asg_pickup_dep_date    DATE;
   p_asg_dropoff_stop_id    NUMBER;
   p_asg_dropoff_loc_id     NUMBER;
   p_asg_dropoff_loc_code   VARCHAR2 (30);
   p_asg_dropoff_arr_date   DATE;
   p_asg_dropoff_dep_date   DATE;
   p_sc_action_flag         VARCHAR2 (10);
   p_sc_close_trip_flag     VARCHAR2 (10);
   p_sc_create_bol_flag     VARCHAR2 (10);
   p_sc_stage_del_flag      VARCHAR2 (10);
   p_sc_trip_ship_method    VARCHAR2 (30);
   p_sc_actual_dep_date     VARCHAR2 (30);
   p_sc_report_set_id       NUMBER;
   p_sc_report_set_name     VARCHAR2 (60);
   p_wv_override_flag       VARCHAR2 (10);
   x_trip_id                VARCHAR2 (30);
   x_trip_name              VARCHAR2 (30);
   p_sc_defer_interface_flag    VARCHAR2(1);
   p_init_msg_list              VARCHAR2(30);
   /*Handle exceptions*/
   fail_api EXCEPTION;
 Cursor c1 is SELECT   DELIVERY_ID,
         name,
         status_name,
         ORGANIZATION_ID,
         (SELECT   organization_code
            FROM   org_organization_definitions ood
           WHERE   ood.organization_id = wn.organization_id
           )  Org_name
  FROM   WSH_NEW_DELIVERIES_V wn
 WHERE   status_code = 'CO'
  and wn.organization_id IN ( select ORGANIZATION_ID from org_organization_definitions where SET_OF_BOOKS_ID = 2021)
-- and delivery_id = 2488014
 order by 4;
BEGIN
   /* Initialize return status*/

for rec in c1
 Loop
  x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
   /* Call this procedure to initialize applications parameters. To determine
      parameter values, refer to the Application Paramater Initialization section of
   this chapter. */
   FND_GLOBAL.APPS_INITIALIZE (user_id        => 1204,
                               resp_id        => 53370,
                               resp_appl_id   => 660);


   /* Values to be set for action code 3 are */
   p_action_code := 'RE-OPEN';             -- The action code for ship confirm
   p_delivery_id := rec.delivery_id; -- The delivery that needs to be confirmed.  See note below
   -- p_delivery_name := '5341'; -- The delivery name.  This is probably not needed
  -- p_sc_action_flag := 'C';                                   -- Backorder all  commented by Rushi
  -- P_sc_stage_del_flag := 'N';                                              -- commented by Rushi
   --  p_sc_trip_ship_method := 'GROUND'; -- The ship method code.  If backordering deliveries, this does not need to be set
   /*Call to WSH_DELIVERIES_PUB.Delivery_Action. */
   WSH_DELIVERIES_PUB.Delivery_Action (
      p_api_version_number     => 1.0,
      p_init_msg_list          => init_msg_list,
      x_return_status          => x_return_status,
      x_msg_count              => x_msg_count,
      x_msg_data               => x_msg_data,
      p_action_code            => p_action_code,
      p_delivery_id            => p_delivery_id,
      p_delivery_name          => p_delivery_name,
      p_asg_trip_id            => p_asg_trip_id,
      p_asg_trip_name          => p_asg_trip_name,
      p_asg_pickup_stop_id     => p_asg_pickup_stop_id,
      p_asg_pickup_loc_id      => p_asg_pickup_loc_id,
      p_asg_pickup_loc_code    => p_asg_pickup_loc_code,
      p_asg_pickup_arr_date    => p_asg_pickup_arr_date,
      p_asg_pickup_dep_date    => p_asg_pickup_dep_date,
      p_asg_dropoff_stop_id    => p_asg_dropoff_stop_id,
      p_asg_dropoff_loc_id     => p_asg_dropoff_loc_id,
      p_asg_dropoff_loc_code   => p_asg_dropoff_loc_code,
      p_asg_dropoff_arr_date   => p_asg_dropoff_arr_date,
      p_asg_dropoff_dep_date   => p_asg_dropoff_dep_date,
      p_sc_action_flag         => p_sc_action_flag,
      p_sc_close_trip_flag     => p_sc_close_trip_flag,
      p_sc_create_bol_flag     => p_sc_create_bol_flag,
      p_sc_stage_del_flag      => p_sc_stage_del_flag,
      p_sc_trip_ship_method    => p_sc_trip_ship_method,
      p_sc_actual_dep_date     => p_sc_actual_dep_date,
      p_sc_report_set_id       => p_sc_report_set_id,
      p_sc_report_set_name     => p_sc_report_set_name,
      p_wv_override_flag       => p_wv_override_flag,
      x_trip_id                => x_trip_id,
      x_trip_name              => x_trip_name
   );
 
   dbms_output.put_line('The REOPEN action on the delivery '||p_delivery_id||' is successful');

   IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS)
   THEN
      RAISE fail_api;
   END IF;
 
 
   ----------------------------------------------------------
    p_action_code                 := 'CONFIRM'; -- The action code for ship confirm
    p_delivery_id                 := rec.delivery_id;   -- The delivery that needs to be confirmed
    p_sc_action_flag              := 'S';       -- Ship entered quantity.
    p_sc_close_trip_flag          := 'Y';       -- Close the trip after ship confirm
  --  p_sc_trip_ship_method         := 'DHL';     -- The ship method code
    p_sc_defer_interface_flag     := 'N';

    -- Call to WSH_DELIVERIES_PUB.Delivery_Action.
    WSH_DELIVERIES_PUB.Delivery_Action(
        p_api_version_number         => 1.0,
        p_init_msg_list              => p_init_msg_list,
        x_return_status              => x_return_status,
        x_msg_count                  => x_msg_count,
        x_msg_data                   => x_msg_data,
        p_action_code                => p_action_code,
        p_delivery_id                => p_delivery_id,
        p_delivery_name              => p_delivery_name,
        p_asg_trip_id                => p_asg_trip_id,
        p_asg_trip_name              => p_asg_trip_name,
        p_asg_pickup_stop_id         => p_asg_pickup_stop_id,
        p_asg_pickup_loc_id          => p_asg_pickup_loc_id,
        p_asg_pickup_loc_code        => p_asg_pickup_loc_code,
        p_asg_pickup_arr_date        => p_asg_pickup_arr_date,
        p_asg_pickup_dep_date        => p_asg_pickup_dep_date,
        p_asg_dropoff_stop_id        => p_asg_dropoff_stop_id,
        p_asg_dropoff_loc_id         => p_asg_dropoff_loc_id,
        p_asg_dropoff_loc_code       => p_asg_dropoff_loc_code,
        p_asg_dropoff_arr_date       => p_asg_dropoff_arr_date,
        p_asg_dropoff_dep_date       => p_asg_dropoff_dep_date,
        p_sc_action_flag             => p_sc_action_flag,
        p_sc_close_trip_flag         => p_sc_close_trip_flag,
        p_sc_create_bol_flag         => p_sc_create_bol_flag,
        p_sc_stage_del_flag          => p_sc_stage_del_flag,
        p_sc_trip_ship_method        => p_sc_trip_ship_method,
        p_sc_actual_dep_date         => p_sc_actual_dep_date,
        p_sc_report_set_id           => p_sc_report_set_id,
        p_sc_report_set_name         => p_sc_report_set_name,
        p_wv_override_flag           => p_wv_override_flag,
        p_sc_defer_interface_flag    => p_sc_defer_interface_flag  ,        
        x_trip_id                    => x_trip_id,
        x_trip_name                  => x_trip_name);
       
   ----------------------------------------------------------------------------    
 
    dbms_output.put_line('The confirm action on the delivery '||p_delivery_id||' is successful');
   end loop;
 
EXCEPTION
   WHEN fail_api
   THEN
   dbms_output.put_line('Delivery id '||p_delivery_id||' is unsuccessful');
      WSH_UTIL_CORE.get_messages ('Y',
                                  x_msg_summary,
                                  x_msg_details,
                                  x_msg_count);

      IF x_msg_count > 1
      THEN
         x_msg_data := x_msg_summary || x_msg_details;
      ELSE
         x_msg_data := x_msg_summary;
      END IF;
END;