R12 Banks Queries
R12 Banks Queries
SELECT aps.vendor_name "VERDOR NAME",
apss.vendor_site_code "VENDOR SITE CODE",
ieb.bank_name "BANK NAME",
iebb.bank_branch_name "BANK BRANCH NAME",
iebb.branch_number "BRANCH NUMBER",
ieba.BANK_ACCOUNT_NUM "BANK ACCOUNT NUMBER",
ieba.BANK_ACCOUNT_NAME "BANK ACCOUNT NAME"
FROM ap.ap_suppliers aps,
ap.ap_supplier_sites_all apss,
apps.iby_ext_bank_accounts ieba,
apps.iby_account_owners iao,
apps.iby_ext_banks_v ieb,
apps.iby_ext_bank_branches_v iebb
WHERE aps.vendor_id = apss.vendor_id
and iao.account_owner_party_id = aps.party_id
and ieba.ext_bank_account_id = iao.ext_bank_account_id
and ieb.bank_party_id = iebb.bank_party_id
and ieba.branch_id = iebb.branch_party_id
and ieba.bank_id = ieb.bank_party_id;
SELECT party_supp.party_name supplier_name
, aps.segment1 supplier_number
, ass.vendor_site_code supplier_site
, ieb.bank_account_num
, ieb.bank_account_name
, party_bank.party_name bank_name
, branch_prof.bank_or_branch_number bank_number
, party_branch.party_name branch_name
, branch_prof.bank_or_branch_number branch_number
FROM hz_parties party_supp
, ap_suppliers aps
, hz_party_sites site_supp
, ap_supplier_sites_all ass
, iby_external_payees_all iep
, iby_pmt_instr_uses_all ipi
, iby_ext_bank_accounts ieb
, hz_parties party_bank
, hz_parties party_branch
, hz_organization_profiles bank_prof
, hz_organization_profiles branch_prof
WHERE party_supp.party_id = aps.party_id
AND party_supp.party_id = site_supp.party_id
AND site_supp.party_site_id = ass.party_site_id
AND ass.vendor_id = aps.vendor_id
AND iep.payee_party_id = party_supp.party_id
AND iep.party_site_id = site_supp.party_site_id
AND iep.supplier_site_id = ass.vendor_site_id
AND iep.ext_payee_id = ipi.ext_pmt_party_id
AND ipi.instrument_id = ieb.ext_bank_account_id
AND ieb.bank_id = party_bank.party_id
AND ieb.bank_id = party_branch.party_id
AND party_branch.party_id = branch_prof.party_id
AND party_bank.party_id = bank_prof.party_id
ORDER BY party_supp.party_name
, ass.vendor_site_code;
Requisition Interface
Requisition Interface - PO_REQUISITIONS_INTERFACE_ALL
-- insert data into Interface tables
INSERT INTO PO_REQUISITIONS_INTERFACE_ALL
(interface_source_code
,source_type_code
,requisition_type
,destination_type_code
,item_id
,item_description
,quantity
,authorization_status
,preparer_id
,autosource_flag
,req_number_segment1 *** see the note
,header_attribute13 ---xtra infomation
,line_attribute15 ---xtra infomation
,uom_code
,destination_organization_id
,destination_subinventory
,deliver_to_location_id
,deliver_to_requestor_id
,need_by_date
,gl_date
,charge_account_id
,accrual_account_id
,variance_account_id
,org_id
,suggested_vendor_id
,suggested_vendor_site_id
,unit_price
,creation_date
,created_by
,last_update_date
,last_updated_by
)
VALUES ('INV'
,'VENDOR'
,'PURCHASE'
,'INVENTORY'
,rec_get_lines_info.inventory_item_id
,rec_get_lines_info.item_desc
,rec_get_lines_info.ordered_quantity
,l_authorization_status --------'INCOMPLETE' or 'APPROVED'
,g_employee_id
,'P'
,l_req_segment1
,'ZZ' ---xtra infomation
,rec_get_lines_info.ship_to_org_id ---xtra infomation
,rec_get_lines_info.uom_code
,rec_get_lines_info.ship_from_org_id
,rec_get_lines_info.subinventory
,rec_get_lines_info.location_id
,get_requestor (fnd_global.user_id) --rec_get_lines_info.requestor
,rec_get_lines_info.schedule_ship_date
,SYSDATE
,rec_get_lines_info.charge_account
,rec_get_lines_info.ap_accrual_account
,rec_get_lines_info.invoice_price_var_account
,g_org_id
, rec_get_lines_info.vendor_id
, rec_get_lines_info.vendor_site_id
,rec_get_lines_info.list_price
,SYSDATE
,fnd_global.user_id
,SYSDATE
,fnd_global.user_id
);
Note:
Standard grouping rules provided by Oracle are by
Buyer
Category
Item
Location
Vendor or
ALL , these grouping rules can be over written by populating "req_number_segment1" .
When we use req_number_segment1 with ALL grouping option, requisiton will be grouped by req_number_segment1
Concurrent Program:
apps.fnd_request.submit_request (application => 'PO' --Application,
program => 'REQIMPORT' --Program,
argument1 => 'INV' --Interface Source code,
argument2 => '' --Batch ID,
argument3 => 'ALL'--Group By,
argument4 => ''--Last Req Number,
argument5 => ''--Multi Distributions,
argument6 => 'N' --Initiate Approval after ReqImport
);
Ship To address and Bill to Address Qry
Ship To address and Bill to Address Qry
SELECT hp.party_name
FROM hz_parties hp
,hz_party_sites site
,hz_locations loc
,hz_party_site_uses uses
,fnd_territories_vl terr
,hz_cust_site_uses hcsua
,hz_cust_acct_sites hcasa
WHERE site.party_id = hp.party_id
AND site.location_id = loc.location_id
AND site.party_site_id = uses.party_site_id(+)
AND loc.country = terr.territory_code
AND hp.status = 'A'
AND site.status = 'A'
AND hcsua.status = 'A'
AND uses.site_use_type = 'SHIP_TO'
AND NVL (uses.status, 'A') = 'A'
AND hcsua.cust_acct_site_id = hcasa.cust_acct_site_id
AND hcasa.party_site_id = site.party_site_id
AND hcsua.site_use_code = uses.site_use_type
AND hcsua.site_use_id = ol.ship_to_org_id
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SELECT hp.party_name
FROM hz_parties hp
,hz_party_sites site
,hz_locations loc
,hz_party_site_uses uses
,fnd_territories_vl terr
,hz_cust_site_uses hcsua
,hz_cust_acct_sites hcasa
WHERE site.party_id = hp.party_id
AND site.location_id = loc.location_id
AND site.party_site_id = uses.party_site_id(+)
AND loc.country = terr.territory_code
AND hp.status = 'A'
AND site.status = 'A'
AND hcsua.status = 'A'
AND uses.site_use_type = 'BILL_TO'
AND NVL (uses.status, 'A') = 'A'
AND hcsua.cust_acct_site_id = hcasa.cust_acct_site_id
AND hcasa.party_site_id = site.party_site_id
AND hcsua.site_use_code = uses.site_use_type
AND hcsua.site_use_id = ol.invoice_to_org_id
Query to get UNPAID Invoices (Oracle Payables)
Below is Simple Query to get UNPAID Invoices (Oracle Payables). It also includes the condition to exclude ZERO Dollar Invoices.
select i.invoice_num,
v.vendor_name,
i.invoice_date,
ps.due_date,
i.invoice_amount,
i.invoice_amount,
i.amount_paid,
ps.amount_remaining,
SUM(i.invoice_amount),
sum(ps.amount_remaining)
FROM ap_payment_schedules_all ps,
ap_invoices_all i,
po_vendors v,
po_vendor_sites_all vs
WHERE i.invoice_id = ps.invoice_id
AND i.vendor_id = v.vendor_id
AND i.vendor_site_id = vs.vendor_site_id
AND i.payment_status_flag ='N'
AND (nvl(ps.amount_remaining, 0) * nvl(i.exchange_rate,1)) != 0
AND i.cancelled_date is not null
group by v.vendor_name,
FROM ap_payment_schedules_all ps,
ap_invoices_all i,
po_vendors v,
po_vendor_sites_all vs
WHERE i.invoice_id = ps.invoice_id
AND i.vendor_id = v.vendor_id
AND i.vendor_site_id = vs.vendor_site_id
AND i.payment_status_flag ='N'
AND (nvl(ps.amount_remaining, 0) * nvl(i.exchange_rate,1)) != 0
AND i.cancelled_date is not null
group by v.vendor_name,
i.invoice_num,
i.invoice_date,
ps.due_date,
i.invoice_amount,
i.amount_paid,
ps.amount_remaining
Order by v.vendor_name,i.invoice_num
ps.amount_remaining
Order by v.vendor_name,i.invoice_num
API to get the concatenated segment values for a code combination id (FND_FLEX_EXT.GET_SEGS)
This API FND_FLEX_EXT.GET_SEGS
- Returns segment values for the given combination id for the specific key flexfield
- Does not check value security rules.
- Returns TRUE if combination found and places displayed segment values in segments(1) through segments(n_segments) inclusive.
- Returns FALSE, sets n_segments = 0, and sets global error message using the FND_MESSAGE utility on error or if combination not found.
Script:
SET SERVEROUTPUT ON;
SET SERVEROUTPUT ON;
DECLARE
v_application_short_name VARCHAR2(200);
v_key_flex_code VARCHAR2(200);
v_structure_number NUMBER; -- Chart of Accounts ID
v_combination_id NUMBER;
v_Return VARCHAR2(200);
BEGIN
v_application_short_name := 'SQLGL';
v_key_flex_code := 'GL#';
v_structure_number := 50408;-- Chart of Accounts ID
v_combination_id := 3254;
v_Return := FND_FLEX_EXT.GET_SEGS
(
application_short_name => v_application_short_name,
key_flex_code => v_key_flex_code,
structure_number => v_structure_number,
combination_id => v_combination_id
);
DBMS_OUTPUT.PUT_LINE('v_Return = ' || v_Return);
END;
API script to Delete an Sales Order Line
Sample script to delete a new line to an existing sales order using an API OE_ORDER_PUB.PROCESS_ORDER.
TEST INSTANCE: R12.1.1
SCRIPT:
SET SERVEROUTPUT ON;
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;
v_msg_index NUMBER;
v_data VARCHAR2 (2000);
v_loop_count NUMBER;
v_debug_file VARCHAR2 (200);
b_return_status VARCHAR2 (200);
b_msg_count NUMBER;
b_msg_data VARCHAR2 (2000);
BEGIN
DBMS_OUTPUT.PUT_LINE('Starting of script');
-- Setting the Enviroment --
mo_global.init('ONT');
fnd_global.apps_initialize ( user_id => 2585
,resp_id => 50864
,resp_appl_id => 660);
mo_global.set_policy_context('S',83);
-- This to delete a line --
v_line_tbl (1) := oe_order_pub.g_miss_line_rec;
v_line_tbl (1).operation := OE_GLOBALS.G_OPR_DELETE;
v_line_tbl (1).header_id := 6006; -- Existing order
v_line_tbl (1).line_id := 4696; -- Existing line to be deleted
DBMS_OUTPUT.PUT_LINE('Starting of API');
-- Calling the API to delete a line from 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
-- OUT variables
, 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 ('Delete a line from an Existing Order Success ');
ELSE
DBMS_OUTPUT.put_line ('Delete a line from an 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;
/
Script to delete an existing sales order using an API OE_ORDER_PUB.PROCESS_ORDER
Sample script to delete an existing sales order using an API OE_ORDER_PUB.PROCESS_ORDER.
TEST INSTANCE: R12.1.1
SCRIPT:
SET SERVEROUTPUT ON;
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 => 2585
,resp_id => 50864
,resp_appl_id => 660);
mo_global.set_policy_context('S',83);
-- Header Record --
v_header_rec := oe_order_pub.g_miss_header_rec;
v_header_rec.operation := OE_GLOBALS.G_OPR_DELETE;
v_header_rec.header_id := 6005;
DBMS_OUTPUT.PUT_LINE('Starting of API');
-- CALLING THE API TO DELETE AN 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
-- OUT variables
, 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 ('Order Deletion Success : '||v_header_rec_out.header_id);
ELSE
DBMS_OUTPUT.put_line ('Order Deletion 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;
/
Script to cancel a line in an existing sales order using an API OE_ORDER_PUB.PROCESS_ORDER
Sample script to cancel a line in an existing sales order using an API OE_ORDER_PUB.PROCESS_ORDER.
TEST INSTANCE: R12.1.1
SCRIPT:
SET SERVEROUTPUT ON;
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 => 2585
,resp_id => 50864
,resp_appl_id => 660);
mo_global.set_policy_context('S',83);
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 := 6006;
v_line_tbl (1).line_id := 4697;
v_line_tbl (1).ordered_quantity := 0;
v_line_tbl (1).cancelled_flag := 'Y';
v_line_tbl (1).change_reason := 'Not Provided';
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
-- OUT variables
, 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;
/
Script to cancel an existing sales order using an API OE_ORDER_PUB.PROCESS_ORDER
Sample script to cancel an existing sales order using an API OE_ORDER_PUB.PROCESS_ORDER.
TEST INSTANCE: R12.1.1
SCRIPT:
SET SERVEROUTPUT ON;
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;
v_msg_index NUMBER;
v_data VARCHAR2 (2000);
v_loop_count NUMBER;
v_debug_file VARCHAR2 (200);
b_return_status VARCHAR2 (200);
b_msg_count NUMBER;
b_msg_data VARCHAR2 (2000);
BEGIN
DBMS_OUTPUT.PUT_LINE('Starting of script');
-- Setting the Enviroment --
mo_global.init('ONT');
fnd_global.apps_initialize ( user_id => 2585
,resp_id => 50864
,resp_appl_id => 660);
mo_global.set_policy_context('S',83);
-- CANCEL HEADER --
v_header_rec := oe_order_pub.g_miss_header_rec;
v_header_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
v_header_rec.header_id := 6006;
v_header_rec.cancelled_flag := 'Y';
v_header_rec.change_reason := 'Not provided';
DBMS_OUTPUT.PUT_LINE('Starting of API');
-- CALLING THE API TO CANCEL AN 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
-- OUT variables
, 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 ('Order Cancellation Success : '||v_header_rec_out.header_id);
ELSE
DBMS_OUTPUT.put_line ('Order Cancellation 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;
/
Subscribe to:
Posts (Atom)