Oracle APPS Consultancy

Thursday, 19 March 2026

Query to find sales orders and invoices details from service contract

SELECT hou.name operating_unit, hou.organization_id org_id, ooha.order_number sales_order_number, ooha.cust_po_number po_number, ohdff.subscription_id subscription_id, trunc(ooha.ordered_date) sales_order_date, trunc(ooha.booked_date) booked_date, hca1.account_number bill_to_customer_number, hp1.party_name bill_to_customer_name, hca2.account_number ship_to_customer_number, hp2.party_name ship_to_customer_name, nvl(oe_totals_grp.get_order_total(ooha.header_id, NULL, 'LINES'), 0) order_amount, okhb.contract_number, okhb.contract_number_modifier, trunc(okhb.start_date) contract_start_date, trunc(okhb.end_date) contract_end_date, round(okhb.estimated_amount, 2) contract_amount, okhb.sts_code, okhb.id, rcta.trx_number invoice_number, trunc(rcta.trx_date) invoice_date, rcta.invoice_currency_code invoice_currency, SUM(rctla.extended_amount) invoice_amount, decode(apsa.status, 'OP', 'Open', 'CL', 'Close') invoice_status, rcta.customer_trx_id, ooha.header_id, ottt.name order_type, ooha.context FROM hr_operating_units hou, oe_order_headers_all ooha, oe_transaction_types_tl ottt, hz_cust_site_uses_all hcsua1, hz_cust_acct_sites_all hcasa1, hz_cust_accounts hca1, hz_parties hp1, hz_cust_site_uses_all hcsua2, hz_cust_acct_sites_all hcasa2, hz_cust_accounts hca2, hz_parties hp2, okc_k_rel_objs rel, okc_k_headers_all_b okhb, ra_customer_trx_all rcta, ar_payment_schedules_all apsa, ra_customer_trx_lines_all rctla, oe_order_headers_all_dfv ohdff WHERE 1 = 1 AND hou.organization_id = ooha.org_id AND ooha.order_type_id = ottt.transaction_type_id AND ottt.language = userenv('LANG') AND hcsua1.site_use_id = ooha.invoice_to_org_id AND hcasa1.cust_acct_site_id = hcsua1.cust_acct_site_id AND hca1.cust_account_id = hcasa1.cust_account_id AND hp1.party_id = hca1.party_id AND hcsua2.site_use_id = ooha.ship_to_org_id AND hcasa2.cust_acct_site_id = hcsua2.cust_acct_site_id AND hca2.cust_account_id = hcasa2.cust_account_id AND hp2.party_id = hca2.party_id AND to_number(rel.chr_id) = okhb.id AND rel.object1_id1 = ooha.header_id AND ooha.org_id = okhb.org_id AND rel.jtot_object1_code = 'OKX_ORDERHEAD' AND okhb.contract_number = rctla.interface_line_attribute1 AND okhb.contract_number_modifier = rctla.interface_line_attribute2 AND rctla.interface_line_context = 'OKS CONTRACTS' AND rctla.line_type = 'LINE' AND rcta.customer_trx_id = rctla.customer_trx_id AND rcta.org_id = rctla.org_id AND apsa.customer_trx_id = rctla.customer_trx_id AND apsa.org_id = rctla.org_id AND ohdff.rowid = ooha.rowid AND ohdff.amp_order = 'Y' AND hou.organization_id IN ( 101 ) -- AND OKHB.CONTRACT_NUMBER in ('11413838','11397088','IR5134831') GROUP BY hou.name, hou.organization_id, ooha.order_number, ooha.cust_po_number, ohdff.subscription_id, trunc(ooha.ordered_date), trunc(ooha.booked_date), hca1.account_number, hp1.party_name, hca2.account_number, hp2.party_name, okhb.contract_number, okhb.contract_number_modifier, okhb.start_date, okhb.end_date, round(okhb.estimated_amount, 2), okhb.sts_code, okhb.id, rcta.trx_number, trunc(rcta.trx_date), rcta.invoice_currency_code, apsa.status, rcta.customer_trx_id, ooha.header_id, ottt.name, ooha.context

Wednesday, 18 March 2026

Script to create lookup values (Insert values into fnd_lookup_values)

SET SERVEROUTPUT ON SIZE 1000000;
SET DEFINE OFF;

DECLARE
CURSOR cur_lookup_dtl IS
SELECT
lookup_type,
security_group_id,
view_application_id
FROM
fnd_lookup_types
WHERE
lookup_type = 'QUOTING_SALES_REP';
CURSOR cur_salesrep IS
SELECT
person_id,
resource_id,
employee_number,
full_name,
email_address,
phone_number
FROM
quoting_sales_rep_extbl
WHERE
employee_number in (111111) ;
l_rowid VARCHAR2(100) := 0;
l_userid NUMBER := -1;
l_count NUMBER := 0;
l_flag VARCHAR2(2) := NULL;
BEGIN
dbms_output.enable(NULL);
dbms_output.put_line('Script To insert sales rep to QUOTING_SALES_REP lookup');
dbms_output.put_line('--------------------------------------------------------------------------------');
dbms_output.put_line('PERSON_ID,'
|| 'RESOURCE_ID,'
|| 'EMPLOYEE_NUMBER,'
|| 'FULL_NAME,'
--|| 'EMAIL_ADDRESS,'
--|| 'PHONE_NUMBER,'
|| 'L_FLAG');
l_count := 0;
FOR rec_lookup_dtl IN cur_lookup_dtl LOOP
FOR rec_salesrep IN cur_salesrep LOOP
BEGIN
l_rowid := NULL;
l_flag := NULL;
fnd_lookup_values_pkg.insert_row(x_rowid => l_rowid,
x_lookup_type => rec_lookup_dtl.lookup_type,
x_security_group_id => rec_lookup_dtl.security_group_id,
x_view_application_id => rec_lookup_dtl.view_application_id,
x_lookup_code => rec_salesrep.resource_id,
x_tag => rec_salesrep.person_id,
x_attribute_category =>rec_lookup_dtl.lookup_type,--NULL,
x_attribute1 => rec_salesrep.email_address,
x_attribute2 => rec_salesrep.phone_number,
x_attribute3 => NULL,
x_attribute4 => NULL,
x_enabled_flag => 'Y',
x_start_date_active => sysdate,
x_end_date_active => NULL,
x_territory_code => NULL,
x_attribute5 => NULL,
x_attribute6 => NULL,
x_attribute7 => NULL,
x_attribute8 => NULL,
x_attribute9 => NULL,
x_attribute10 => NULL,
x_attribute11 => NULL,
x_attribute12 => NULL,
x_attribute13 => NULL,
x_attribute14 => NULL,
x_attribute15 => NULL,
x_meaning => rec_salesrep.employee_number,
x_description => rec_salesrep.full_name,
x_creation_date => sysdate,
x_created_by => l_userid,
x_last_update_date => sysdate,
x_last_updated_by => l_userid,
x_last_update_login => l_userid);
--COMMIT;
--DBMS_OUTPUT.put_line (j.X_LOOKUP_CODE || ' loaded');
--dbms_output.put_line('sss1: ' || l_flag);
l_flag := 'S';
--dbms_output.put_line('insert completed: ' || l_flag);
EXCEPTION
WHEN OTHERS THEN
--dbms_output.put_line('Inner Exception: ' || sqlerrm);
dbms_output.put_line('Error occurred while inserting records into the lookup for the employee number: '
|| rec_salesrep.employee_number
|| ': '
|| sqlerrm);
l_flag := 'E';
END;
dbms_output.put_line(rec_salesrep.person_id
|| ','
|| rec_salesrep.resource_id
|| ','
|| rec_salesrep.employee_number
|| ','
|| rec_salesrep.full_name
--|| ','
--|| rec_salesrep.email_address
--|| ','
--|| l_flag
--|| ','
--|| trim(rec_salesrep.phone_number)
|| ','
|| l_flag
);
l_count := l_count + 1;
END LOOP;
END LOOP;
dbms_output.put_line('--------------------------------------------------------------------------------');
dbms_output.put_line('Total Records Processed = ' || l_count);
dbms_output.put_line('--------------------------------------------------------------------------------');
COMMIT;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Error :' || sqlerrm);
END;
/

Query to find Bill To, Ship To Customer and Amount for the sales orders

SELECT
hou.name,
ooha.org_id,
ooha.order_number,
(
SELECT
okhb.contract_number
|| '-'
|| okhb.contract_number_modifier
FROM
okc_k_headers_all_b okhb,
okc_k_rel_objs rel
WHERE
1 = 1
AND rel.chr_id = okhb.id
AND rel.jtot_object1_code = 'OKX_ORDERHEAD'
AND rel.object1_id1 = ooha.header_id
and rownum <=1
) service_contract_number,
ooha.blanket_number,
ott.name order_type,
bill_hp.party_name customer_name,
bill_hca.account_number customer_account_number,
bill_hps.party_site_number bill_party_site_number,
bill_hl.address1 bill_address1,
bill_hl.address2 bill_address2,
bill_hl.city bill_city,
bill_hl.state bill_state,
bill_hl.postal_code bill_postal_code,
bill_hl.country bill_country,
ship_hps.party_site_number ship_party_site_number,
ship_hl.address1 ship_address1,
ship_hl.address2 ship_address2,
ship_hl.city ship_city,
ship_hl.state ship_state,
ship_hl.postal_code ship_postal_code,
ship_hl.country ship_country,
ooha.transactional_curr_code,
nvl(oe_totals_grp.get_order_total(ooha.header_id, NULL, 'LINES'), 0) order_subtotal,
nvl(oe_totals_grp.get_order_total(ooha.header_id, NULL, 'TAXES'), 0) order_tax,
nvl(oe_totals_grp.get_order_total(ooha.header_id, NULL, 'ALL'), 0) order_total
FROM
oe_order_headers_all ooha,
-- oe_order_lines_all oola,
oe_transaction_types_tl ott,
hz_cust_site_uses_all bill_hcsua,
hz_cust_acct_sites_all bill_hcasa,
hz_cust_accounts bill_hca,
hz_party_sites bill_hps,
hz_parties bill_hp,
hz_locations bill_hl,
hz_cust_site_uses_all ship_hcsua,
hz_cust_acct_sites_all ship_hcasa,
hz_cust_accounts ship_hca,
hz_party_sites ship_hps,
hz_parties ship_hp,
hz_locations ship_hl,
hr_operating_units hou
WHERE
1 = 1
-- AND ooha.header_id = oola.header_id
-- AND ooha.org_id = oola.org_id
AND ooha.order_type_id = ott.transaction_type_id
AND ooha.org_id = hou.organization_id
AND ooha.invoice_to_org_id = bill_hcsua.site_use_id
AND bill_hcsua.cust_acct_site_id = bill_hcasa.cust_acct_site_id
AND bill_hcasa.cust_account_id = bill_hca.cust_account_id
AND bill_hcasa.party_site_id = bill_hps.party_site_id
AND bill_hca.party_id = bill_hp.party_id
AND bill_hps.location_id = bill_hl.location_id
AND ooha.ship_to_org_id = ship_hcsua.site_use_id
AND ship_hcsua.cust_acct_site_id = ship_hcasa.cust_acct_site_id
AND ship_hcasa.cust_account_id = ship_hca.cust_account_id
AND ship_hcasa.party_site_id = ship_hps.party_site_id
AND ship_hca.party_id = ship_hp.party_id
AND ship_hps.location_id = ship_hl.location_id
-- AND oola.tax_value > 0
--AND ooha.order_number = '111111111'
--AND nvl(oe_totals_grp.get_order_total(ooha.header_id, NULL, 'TAXES'), 0) > 0
AND trunc(ooha.booked_date) >= '01-JAN-2026';

Friday, 22 August 2025

Query to find hold and release information for sales orders

SELECT
hou.name,
hca.account_number customer_number,
hp.party_name customer_name,
ooha.header_id,
ooha.order_number,
ott.name order_type,
ooha.ordered_date,
lkup3.meaning order_status,
hd.name hold_name,
lkup1.meaning hold_type,
holds.creation_date hold_applied_date,
fu.user_name hold_applied_by,
hr.creation_date released_date,
fur.user_name released_by,
lkup2.meaning release_reason,
holds.hold_release_id,
holds.hold_source_id,
hd.hold_id
FROM
oe_order_headers_all ooha,
hr_organization_units hou,
oe_order_holds_all holds,
oe_hold_sources_all hs,
oe_hold_definitions hd,
oe_hold_releases hr,
oe_transaction_types_tl ott,
oe_lookups lkup1,
oe_lookups lkup2,
oe_lookups lkup3,
hz_cust_site_uses_all hcsua,
hz_cust_acct_sites_all hcasa,
hz_cust_accounts hca,
hz_parties hp,
fnd_user fu,
fnd_user fur,
fnd_lookup_values flv
WHERE
1 = 1
AND ooha.header_id = holds.header_id
AND ooha.org_id = hou.organization_id
AND holds.hold_source_id = hs.hold_source_id
AND hd.hold_id = hs.hold_id
AND holds.hold_release_id = hr.hold_release_id
AND ooha.order_type_id = ott.transaction_type_id
AND ooha.order_type_id = flv.lookup_code
AND flv.lookup_type = 'CREDIT_HOLD_RELEASE'
AND flv.enabled_flag = 'Y'
AND trunc(nvl(flv.end_date_active, sysdate)) >= trunc(sysdate)
AND lkup1.lookup_code = hd.type_code
AND lkup1.lookup_type = 'HOLD_TYPE'
AND trunc(sysdate) BETWEEN nvl(lkup1.start_date_active, trunc(sysdate)) AND nvl(lkup1.end_date_active, trunc(sysdate))
AND lkup3.lookup_type = 'FLOW_STATUS'
AND lkup3.lookup_code = ooha.flow_status_code
AND trunc(sysdate) BETWEEN nvl(lkup3.start_date_active, trunc(sysdate)) AND nvl(lkup3.end_date_active, trunc(sysdate))
AND lkup2.lookup_type = 'RELEASE_REASON'
AND lkup2.lookup_code = hr.release_reason_code
AND trunc(sysdate) BETWEEN nvl(lkup2.start_date_active, trunc(sysdate)) AND nvl(lkup2.end_date_active, trunc(sysdate))
AND ooha.invoice_to_org_id = hcsua.site_use_id
AND hcsua.cust_acct_site_id = hcasa.cust_acct_site_id
AND hcasa.cust_account_id = hca.cust_account_id
AND hca.party_id = hp.party_id
AND fu.user_id = holds.created_by
AND fur.user_id = hr.created_by
AND hd.name = 'HOLD_NAME'
AND trunc(ooha.ordered_date) >= '01-JAN-2025'
AND ooha.order_number IN ( '111111111')
ORDER BY
fur.user_name;

Tuesday, 12 August 2025

Query to find hold and release information of sales order

SELECT

hou.name,

ooha.org_id,

ooha.order_number,

ott.name order_type,

lkup3.meaning order_status,

hd.name hold_name,

lkup.meaning hold_type,

(

SELECT

flv.meaning

FROM

fnd_lookup_values flv

WHERE

1 = 1

AND flv.lookup_code = hs.hold_entity_code

AND flv.lookup_type = 'HOLD_ENTITY_DESC'

AND flv.language = userenv('LANG')

AND flv.view_application_id = 660

AND flv.security_group_id = fnd_global.lookup_security_group(flv.lookup_type, flv.view_application_id)

) criteria,

oe_holds_pvt.entity_id_value(hs.hold_entity_code, hs.hold_entity_id) value,

holds.creation_date hold_applied_date,

hr.creation_date released_date,

lkup2.meaning release_reason,

hr.release_comment

FROM

oe_order_headers_all ooha,

hr_organization_units hou,

oe_order_holds_all holds,

oe_hold_sources_all hs,

oe_hold_definitions hd,

oe_hold_releases hr,

oe_transaction_types_tl ott,

oe_lookups lkup,

oe_lookups lkup2,

oe_lookups lkup3

WHERE

1 = 1

AND ooha.header_id = holds.header_id

AND ooha.org_id = hou.organization_id

AND holds.hold_source_id = hs.hold_source_id

AND hd.hold_id = hs.hold_id

AND holds.hold_release_id = hr.hold_release_id(+)

AND lkup.lookup_code = hd.type_code

AND ooha.order_type_id = ott.transaction_type_id

AND lkup.lookup_type = 'HOLD_TYPE'

AND trunc(sysdate) BETWEEN nvl(lkup.start_date_active, trunc(sysdate)) AND nvl(lkup.end_date_active, trunc(sysdate))

AND lkup2.lookup_type = 'RELEASE_REASON'

AND hr.release_reason_code = lkup2.lookup_code (+)

AND trunc(sysdate) BETWEEN nvl(lkup2.start_date_active, trunc(sysdate)) AND nvl(lkup2.end_date_active, trunc(sysdate))

AND lkup3.lookup_type = 'FLOW_STATUS'

AND lkup3.lookup_code = ooha.flow_status_code

AND trunc(sysdate) BETWEEN nvl(lkup3.start_date_active, trunc(sysdate)) AND nvl(lkup3.end_date_active, trunc(sysdate))

AND ooha.order_number IN ( '71669760', '71667130' );

Script to Release the Hold from Sales order

SET SERVEROUTPUT ON SIZE 100000;

SET DEFINE OFF;

DECLARE

l_return_status VARCHAR2(30);

l_msg_data VARCHAR2(4000);

l_msg_count NUMBER;

l_order_tbl oe_holds_pvt.order_tbl_type;

l_hold_id NUMBER := 1;

l_header_id NUMBER := 14621798;--14612797;

l_context VARCHAR2(2);

BEGIN

dbms_output.put_line('START');

mo_global.init('ONT');

fnd_global.apps_initialize(user_id => 233158, resp_id => 57420, resp_appl_id => 660);

mo_global.set_policy_context('S', 111);

l_order_tbl(1).header_id := l_header_id;

l_return_status := NULL;

l_msg_data := NULL;

l_msg_count := NULL;

dbms_output.put_line('Calling the API to Release hold');

oe_holds_pub.release_holds(p_api_version => 1.0, p_order_tbl => l_order_tbl,

p_hold_id => l_hold_id,

p_release_reason_code => 'CREDIT_APPROVED',

p_release_comment => 'TEST1',

x_return_status => l_return_status,

x_msg_count => l_msg_count,

x_msg_data => l_msg_data);

IF l_return_status = fnd_api.g_ret_sts_success THEN

dbms_output.put_line('success:');

COMMIT;

ELSIF l_return_status IS NULL THEN

dbms_output.put_line('Status is null');

ELSE

dbms_output.put_line('Failed: ' || l_msg_data);

FOR i IN 1..oe_msg_pub.count_msg LOOP

l_msg_data := oe_msg_pub.get(p_msg_index => i, p_encoded => 'F');

dbms_output.put_line(i

|| ') '

|| l_msg_data);

END LOOP;

ROLLBACK;

END IF;

dbms_output.put_line('END');

EXCEPTION

WHEN OTHERS THEN

dbms_output.put_line('Error is '

|| sqlcode

|| '---'

|| sqlerrm);

END;

Script to change the order status to Booked

SET SERVEROUTPUT ON SIZE 100000;

SET DEFINE OFF;

DECLARE

l_api_version_number NUMBER := 1;

l_return_status VARCHAR2(2000);

l_msg_count NUMBER;

l_msg_data VARCHAR2(2000);

-- IN Variables --

l_header_rec oe_order_pub.header_rec_type;

l_line_tbl oe_order_pub.line_tbl_type;

l_action_request_tbl oe_order_pub.request_tbl_type;

l_line_adj_tbl oe_order_pub.line_adj_tbl_type;

-- OUT Variables --

l_header_rec_out oe_order_pub.header_rec_type;

l_header_val_rec_out oe_order_pub.header_val_rec_type;

l_header_adj_tbl_out oe_order_pub.header_adj_tbl_type;

l_header_adj_val_tbl_out oe_order_pub.header_adj_val_tbl_type;

l_header_price_att_tbl_out oe_order_pub.header_price_att_tbl_type;

l_header_adj_att_tbl_out oe_order_pub.header_adj_att_tbl_type;

l_header_adj_assoc_tbl_out oe_order_pub.header_adj_assoc_tbl_type;

l_header_scredit_tbl_out oe_order_pub.header_scredit_tbl_type;

l_header_scredit_val_tbl_out oe_order_pub.header_scredit_val_tbl_type;

l_line_tbl_out oe_order_pub.line_tbl_type;

l_line_val_tbl_out oe_order_pub.line_val_tbl_type;

l_line_adj_tbl_out oe_order_pub.line_adj_tbl_type;

l_line_adj_val_tbl_out oe_order_pub.line_adj_val_tbl_type;

l_line_price_att_tbl_out oe_order_pub.line_price_att_tbl_type;

l_line_adj_att_tbl_out oe_order_pub.line_adj_att_tbl_type;

l_line_adj_assoc_tbl_out oe_order_pub.line_adj_assoc_tbl_type;

l_line_scredit_tbl_out oe_order_pub.line_scredit_tbl_type;

l_line_scredit_val_tbl_out oe_order_pub.line_scredit_val_tbl_type;

l_lot_serial_tbl_out oe_order_pub.lot_serial_tbl_type;

l_lot_serial_val_tbl_out oe_order_pub.lot_serial_val_tbl_type;

l_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 => 233158, resp_id => 57420,

resp_appl_id => 660);

mo_global.set_policy_context('S', 111);

l_action_request_tbl(1) := oe_order_pub.g_miss_request_rec;

l_action_request_tbl(1).request_type := oe_globals.g_book_order;

l_action_request_tbl(1).entity_code := oe_globals.g_entity_header;

l_action_request_tbl(1).entity_id := 14612797;--header_id

dbms_output.put_line('Starting of API');

-- Calling the API to to Book an Existing Order --Buy bestselling books online

oe_order_pub.process_order(p_api_version_number => l_api_version_number, p_header_rec => l_header_rec,

p_line_tbl => l_line_tbl,

p_action_request_tbl => l_action_request_tbl,

p_line_adj_tbl => l_line_adj_tbl

-- OUT variables

x_header_rec => l_header_rec_out,

x_header_val_rec => l_header_val_rec_out,

x_header_adj_tbl => l_header_adj_tbl_out,

x_header_adj_val_tbl => l_header_adj_val_tbl_out,

x_header_price_att_tbl => l_header_price_att_tbl_out,

x_header_adj_att_tbl => l_header_adj_att_tbl_out,

x_header_adj_assoc_tbl => l_header_adj_assoc_tbl_out,

x_header_scredit_tbl => l_header_scredit_tbl_out,

x_header_scredit_val_tbl => l_header_scredit_val_tbl_out,

x_line_tbl => l_line_tbl_out,

x_line_val_tbl => l_line_val_tbl_out,

x_line_adj_tbl => l_line_adj_tbl_out,

x_line_adj_val_tbl => l_line_adj_val_tbl_out,

x_line_price_att_tbl => l_line_price_att_tbl_out,

x_line_adj_att_tbl => l_line_adj_att_tbl_out,

x_line_adj_assoc_tbl => l_line_adj_assoc_tbl_out,

x_line_scredit_tbl => l_line_scredit_tbl_out,

x_line_scredit_val_tbl => l_line_scredit_val_tbl_out,

x_lot_serial_tbl => l_lot_serial_tbl_out,

x_lot_serial_val_tbl => l_lot_serial_val_tbl_out,

x_action_request_tbl => l_action_request_tbl_out,