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'