Scribd
Upload
69 views10 pages

Sample Scripts

The document contains SQL statements that are inserting records into various staging tables for orders, contracts, and lines. It is testing the insertion of records, selecting the inserted records, and calling stored procedures to process the records into live tables.

Uploaded by

Srikkanth Mani
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
69 views10 pages

Sample Scripts

The document contains SQL statements that are inserting records into various staging tables for orders, contracts, and lines. It is testing the insertion of records, selecting the inserted records, and calling stored procedures to process the records into live tables.

Uploaded by

Srikkanth Mani
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
You are on page 1/ 10

BEGIN

INSERT INTO xx3d_om_cpq_header_stg (


web_order_number,
sf_header_id,
account_number,
cust_po_number,
ordered_date,
request_date,
pricing_date,
sales_person,
transactional_curr_code,
fob_point_code,
ship_to_location,
bill_to_location,
price_list_name,
payment_term,
amount,
subtotal,
totalamount,
invoice_dispatchmethod,
invoice_dispatch_details,
acknowledgmentdispatch_detail,
contact_email,
customer_po_number,
org_id
) VALUES (
'000011101',
'AABB000011101',
'8403',
'PO123',
trunc(sysdate),
trunc(sysdate),
trunc(sysdate),
'[email protected]',
'USD',
'EX Works',
'12908',
'12912',
'CorpUS',
'Net 30 days',
2500,
2500,
2500,
'EMAIL',
'[email protected]',
'[email protected]',
'[email protected]',
'PO123/0007863301',
101
);

INSERT INTO xx3d_om_cpq_line_stg (


sf_header_id,
sf_line_id,
inventory_item_id,
unit_selling_price,
ordered_quantity,
unit_list_price,
total_line_amount,
fob_point_code,
erp_totallineamount_withtax
) VALUES (
'AABB000011101',
'AABB0000111101',
'1705562',
2000,
1,
2000,
2000,
'Ex Works',
2000
);

INSERT INTO xx3d_om_cpq_line_stg (


sf_header_id,
sf_line_id,
inventory_item_id,
unit_selling_price,
ordered_quantity,
unit_list_price,
total_line_amount,
fob_point_code,
erp_totallineamount_withtax
) VALUES (
'AABB000011101',
'AABB0000211101',
'3619255',
2000,
1,
2000,
2000,
'Ex Works',
2000
);

INSERT INTO xx3d_om_cpq_line_stg (


sf_header_id,
sf_line_id,
inventory_item_id,
unit_selling_price,
ordered_quantity,
unit_list_price,
total_line_amount,
fob_point_code,
erp_totallineamount_withtax,
service_start_date,
service_end_date
) VALUES (
'AABB000011101',
'AABB0000311101',
'1705783',
500,
1,
500,
500,
'Ex Works',
500,
trunc(sysdate),
trunc(sysdate + 364)
);

COMMIT;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Exception: ' || sqlerrm);
END;

select * from XX3D_OM_CPQ_HEADER_STG


/
select * from XX3D_OM_CPQ_LINE_STG
/
delete from XX3D_OM_CPQ_HEADER_STG where sf_header_id='AABB000011101'
/
delete from XX3D_OM_CPQ_LINE_STG where sf_header_id='AABB000011101'
/
DECLARE
lv_status VARCHAR2(10);
lv_message VARCHAR2(2000);
BEGIN
xx3d_om_cpq_ord_creation_pkg.sales_order_main_prc(
'AABB000011101',
lv_status,
lv_message
);
dbms_output.put_line('Return Status ' || lv_status);
dbms_output.put_line('Return Message ' || lv_message);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Exception: ' || sqlerrm);
END;
/
select * from oe_order_headers_all where trunc (creation_date)=trunc (sysdate)

BEGIN
INSERT INTO xx3d_oks_cpq_sc_header_stg (
web_contract_number,
sf_header_id,
action_type,
leg_ent_name,
currency_code,
start_date,
end_date,
short_description,
description,
party_name,
cust_account_no,
price_list_name,
payment_term,
ship_to_location,
bill_to_location,
billing_uom
) VALUES (
'000011105',
'AABB000011105',
'CREATE',
'Oqton, Inc',
'USD',
trunc(sysdate-59),
trunc(sysdate + 30),
'Contract Created from API',
'Contract Created from API',
'PARTNERS DENTAL STUDIO',
'558977',
'CorpUS',
'Net 30 days',
'702666',
'702664',
'Monthly'--'EOC'
);

INSERT INTO xx3d_oks_cpq_sc_line_stg (


sf_header_id,
sf_line_id,
item_name,
description,
start_date,
end_date,
action_type--,acct_rule_id,acct_rule_name
) VALUES (
'AABB000011105',
'AABB0000111105',
'SUB-OQTON-1003AA',
'Contract Line form API',
trunc(sysdate-59),
trunc(sysdate + 30),
'CREATE'
);

INSERT INTO xx3d_oks_cpq_sc_line_stg (


sf_header_id,
sf_line_id,
item_name,
description,
start_date,
end_date,
action_type--,acct_rule_id,acct_rule_name
) VALUES (
'AABB000011105',
'AABB0000211105',
'SUB-OQTON-1004',
'Contract Line form API',
trunc(sysdate-59),
trunc(sysdate + 30),
'CREATE'
);

INSERT INTO xx3d_oks_cpq_sc_line_stg (


sf_header_id,
sf_line_id,
item_name,
description,
start_date,
end_date,
action_type--,acct_rule_id,acct_rule_name
) VALUES (
'AABB000011105',
'AABB0000311105',
'SUB-OQTON-1005BB',
'Contract Line form API',
trunc(sysdate-59),
trunc(sysdate + 30),
'CREATE'
);

INSERT INTO xx3d_oks_cpq_sc_line_stg (


sf_header_id,
sf_line_id,
item_name,
description,
start_date,
end_date,
action_type--,acct_rule_id,acct_rule_name
) VALUES (
'AABB000011105',
'AABB0000411105',
'SUB-OQTON-1006',
'Contract Line form API',
trunc(sysdate-59),
trunc(sysdate + 30),
'CREATE'
);

COMMIT;
END;

select * from xx3d_oks_cpq_sc_header_stg where sf_header_id ='AABB000011105'


/
select * from xx3d_oks_cpq_sc_line_stg where sf_header_id='AABB000011105'
/
delete from xx3d_oks_cpq_sc_header_stg where sf_header_id ='AABB000011104'
and record_Status is null
/
delete from xx3d_oks_cpq_sc_line_stg where sf_header_id='AABB000011104'
and record_Status is null
/
declare
lv_status varchar2(10);
lv_message varchar2(2000);
begin
xx3d_oks_cpq_int_sc_pkg.p_process_contract(
'AABB000011105',
'CREATE',
-- 'UPDATE',
-- 'ADD LINE',
lv_status,
lv_message
);
dbms_output.put_line('Return Status ' || lv_status);
dbms_output.put_line('Return Message ' || lv_message);
end;
/
select sysdate-59,sysdate+30 from dual
/
select trunc (sysdate),trunc (sysdate+30) from dual
/
select * from okc_k_headers_all where attribute10='AABB000011103'
/
select * from
fnd_lookup_values_vl
WHERE
lookup_type = 'XX3D_OKS_CPQ_SC_BILLING_UOM'
/
select * from org_organization_definitions where operating_unit=101
/
select * from hr_operating_units
/
select * from mtl_parameters
/
select * from XX3D_OM_CPQ_HEADER_STG where sf_header_id='801R0000001YCkhIAG'
/
select * from XX3D_OM_CPQ_LINE_STG where sf_header_id='801R0000001YCkhIAG'
---------------------------
DECLARE
lv_status VARCHAR2(10);
lv_message VARCHAR2(2000);
BEGIN
xx3d_om_cpq_ord_creation_pkg.sales_order_main_prc(
'801R0000001YCkhIAG',
lv_status,
lv_message
);
dbms_output.put_line('Return Status ' || lv_status);
dbms_output.put_line('Return Message ' || lv_message);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Exception: ' || sqlerrm);
END;
/
select * from XX3D_OM_CPQ_HEADER_STG where sf_header_id='801R0000001V09569'
/
select * from XX3D_OM_CPQ_LINE_STG where sf_header_id='801R0000001V09569'
/
select * from fnd_lookup_values where lookup_type='3DOM_CPQ_ORDER_TYPE'
and language='US'
/
select * from mtl_system_items_b --where organization_id=101
/
select * from hr_operating_units
/
select * from org_organization_definitions where operating_unit=101
/
select * from XX3D_OM_CPQ_LINE_STG where sf_header_id='801R0000001YCkhIAG'
/
select * from XX3D_OM_CPQ_HEADER_STG where sf_header_id='801R0000001YCkhIAG'
/
update XX3D_OM_CPQ_HEADER_STG set process_flag= null,error_message=null where
sf_header_id='801R0000001YCkhIAG'
/
update XX3D_OM_CPQ_LINE_STG set process_flag= null,error_message=null where
sf_header_id='801R0000001YCkhIAG'

BEGIN
INSERT INTO xx3d_om_cpq_header_stg (
web_order_number,
sf_header_id,
account_number,
cust_po_number,
ordered_date,
request_date,
pricing_date,
sales_person,
transactional_curr_code,
fob_point_code,
ship_to_location,
bill_to_location,
price_list_name,
payment_term,
amount,
subtotal,
totalamount,
invoice_dispatchmethod,
invoice_dispatch_details,
acknowledgmentdispatch_detail,
contact_email,
customer_po_number,
org_id
) VALUES (
'00053096r109569',
'801R0000001V09569',
'549727',
'PO123',
trunc(sysdate),
trunc(sysdate),
trunc(sysdate),
'[email protected]',
'EUR',
'EX Works',
'GEOMAGIC',
'GEOMAGIC',
'CorpEU',
'Net 30 days',
2500,
2500,
2500,
'EMAIL',
'[email protected]',
'[email protected]',
'[email protected]',
'PO123/0007863301',
121
);

INSERT INTO xx3d_om_cpq_line_stg (


sf_header_id,
sf_line_id,
inventory_item_id,
unit_selling_price,
ordered_quantity,
unit_list_price,
total_line_amount,
fob_point_code,
erp_totallineamount_withtax
) VALUES (
'801R0000001V09569',
'802R0000001V135609',
'86024000',
2000,
1,
2000,
2000,
'Ex Works',
2000
);

INSERT INTO xx3d_om_cpq_line_stg (


sf_header_id,
sf_line_id,
inventory_item_id,
unit_selling_price,
ordered_quantity,
unit_list_price,
total_line_amount,
fob_point_code,
erp_totallineamount_withtax,
service_start_date,
service_end_date
) VALUES (
'801R0000001V09569',
'802R0000001V125609',
'419535800',--'1918407',
500,
1,
500,
500,
'Ex Works',
500,
trunc(sysdate),
trunc(sysdate + 364)
);

COMMIT;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Exception: ' || sqlerrm);
END;

select * from xx3d_oks_cpq_sc_header_stg --243 --249 --258


/
select * from xx3d_oks_cpq_sc_line_stg where sf_header_id='800R0000001GXIfIAO'
/
select distinct
web_contract_number,sf_header_id,action_type,record_status,error_message,contract_n
umber
from xx3d_oks_cpq_sc_header_stg
order by web_contract_number desc
/
select distinct
web_order_number,sf_header_id,process_flag,error_message,order_number
from XX3D_OM_CPQ_HEADER_STG order by web_order_number desc
/
select * from XX3D_OM_CPQ_LINE_STG where sf_header_id='801R0000001YCkhIAG'
/
select * from XX3D_OM_CPQ_HEADER_STG where sf_header_id='801R0000001YCkhIAG' --35
-- 50 -65
order by web_order_number desc
/
SELECT
jrs.salesrep_id,jrs.email_address,jrd.source_email,jrs.org_id
FROM
jtf_rs_salesreps jrs,
jtf_rs_defresources_v jrd
WHERE
jrs.resource_id = jrd.resource_id
-- AND upper (jrs.email_address) = upper (p_email)
AND ( upper(
jrs.email_address
) like upper('alex%')
OR upper(
jrd.source_email
) like upper('alexander%') ) -- By Kranthi
-- AND jrs.org_id = p_org_id;
/
select * from xx3d_oks_cpq_sc_header_stg where sf_header_id='800R0000001GXIfIAO'
/
select * from xx3d_oks_cpq_sc_line_stg where sf_header_id='800R0000001GXB5IAO'
/
select web_contract_number,sf_header_id,start_date,end_date,
trunc (months_between (end_date,start_date)),billing_uom
from xx3d_oks_cpq_sc_header_stg where sf_header_id='800R0000001GXIfIAO'
/
select * from okc_k_headers_all_b order by id desc
/
select * from XX3D_OM_CPQ_LINE_STG where sf_header_id='801R0000001YCkhIAG'
/
select * from XX3D_OM_CPQ_HEADER_STG where sf_header_id='801R0000001YCkhIAG'
----------------

select * from xx3d_oks_cpq_sc_header_stg --243 --249 --258


/
select * from xx3d_oks_cpq_sc_line_stg where sf_header_id='800R0000001GXIfIAO'
/
select distinct
web_contract_number,sf_header_id,action_type,record_status,error_message,contract_n
umber
from xx3d_oks_cpq_sc_header_stg
order by web_contract_number desc
/
select distinct
web_order_number,sf_header_id,process_flag,error_message,order_number
from XX3D_OM_CPQ_HEADER_STG order by web_order_number desc
/
select * from XX3D_OM_CPQ_LINE_STG where sf_header_id='801R0000001YCkhIAG'
/
select * from XX3D_OM_CPQ_HEADER_STG --35 -- 50 -65
order by web_order_number desc
/
SELECT
jrs.salesrep_id,jrs.email_address,jrd.source_email,jrs.org_id
FROM
jtf_rs_salesreps jrs,
jtf_rs_defresources_v jrd
WHERE
jrs.resource_id = jrd.resource_id
-- AND upper (jrs.email_address) = upper (p_email)
AND ( upper(
jrs.email_address
) like upper('alex%')
OR upper(
jrd.source_email
) like upper('alexander%') ) -- By Kranthi
-- AND jrs.org_id = p_org_id;
/
select * from xx3d_oks_cpq_sc_header_stg where sf_header_id='800R0000001GXIfIAO'
/
select * from xx3d_oks_cpq_sc_line_stg where sf_header_id='800R0000001GXB5IAO'
/
select web_contract_number,sf_header_id,start_date,end_date,
trunc (months_between (end_date,start_date)),billing_uom
from xx3d_oks_cpq_sc_header_stg where sf_header_id='800R0000001GXIfIAO'
/
select * from okc_k_headers_all_b order by id desc
/

select * from xx3d_oks_cpq_sc_header_stg where sf_header_id ='AABB00001198'


/
select * from xx3d_oks_cpq_sc_line_stg where sf_header_id='AABB00001198'
/
delete from xx3d_oks_cpq_sc_header_stg where sf_header_id ='AABB00001197'
/
delete from xx3d_oks_cpq_sc_line_stg where sf_header_id='AABB00001197'
/
declare
lv_status varchar2(10);
lv_message varchar2(2000);
begin
xx3d_oks_cpq_int_sc_pkg.p_process_contract(
'AABB00001198',
'CREATE',
-- 'UPDATE',
-- 'ADD LINE',
lv_status,
lv_message
);
dbms_output.put_line('Return Status ' || lv_status);
dbms_output.put_line('Return Message ' || lv_message);
end;
/
select trunc (sysdate-58),trunc (sysdate-28) from dual
/
select * FROM
fnd_lookup_values_vl
WHERE
lookup_type = 'XX3D_OKS_CPQ_SC_BILLING_UOM'
/
select xx3d_oks_cpq_int_sc_pkg.validate_conc_dur(null,null,null) from dual

You might also like