EBS 11.5.10.
2 EBS Create Customer - EBS through ISC to VCP to Demantra Test Case
Study
-- customer party id and party name
PROMPT HZ_PARTIES
PROMPT ==============================
select party_id, party_name from hz_parties
where party_name = 'EE-Party-1';
HZ_PARTIES
==============================
PARTY_ID PARTY_NAME
---------------------- -------------
58276 EE-Party-1
1 rows selected
PROMPT HZ_CUST_ACCOUNTS
PROMPT ==============================
-- customer account ids and names within the party
select party_id, cust_account_id, account_name
from hz_cust_accounts
where party_id = 58276;
HZ_CUST_ACCOUNTS
==============================
PARTY_ID CUST_ACCOUNT_ID ACCOUNT_NAME
---------------------- ---------------------- --------------
58276 13072
58276 13084 EECusAcctName1
58276 13087 EECusAcctName2
3 rows selected
PROMPT HZ_CUST_ACCT_SITES_ALL
PROMPT ==============================
-- customer account's sites within the party
select
cust_account_id,
cust_acct_site_id
party_site_id
from hz_cust_acct_sites_all
where
cust_account_id in (13087, 13084, 13077, 13072);
HZ_CUST_ACCT_SITES_ALL
==============================
CUST_ACCOUNT_ID PARTY_SITE_ID
---------------------- ----------------------
13072 7637
13077 7638
13084 7639
13087 7640
13087 7641
5 rows selected
PROMPT HZ_CUST_SITE_USES_ALL
PROMPT ==============================
-- customer account's site's ship to locations within the party
select
cust_acct_site_id,
site_use_id,
site_use_code,
location
from hz_cust_site_uses_all
where cust_acct_site_id in (7637,7638,7639,7640,7641)
and site_use_code = 'SHIP_TO';
HZ_CUST_SITE_USES_ALL
==============================
CUST_ACCT_SITE_ID SITE_USE_ID SITE_USE_CODE LOCATION
---------------------- -------------- --------------- -----------------------
7639 9308 SHIP_TO EE CusAcctShipTo Loc 1
7640 9312 SHIP_TO EE CusAcct2ShipTo Loc 1
7641 9316 SHIP_TO EE CusAcct2ShipTo Loc 2
3 rows selected
PROMPT OE_ORDER_HEADERS_ALL and OE_ORDER_LINES_ALL
PROMPT ==============================
select distinct
oha.sold_from_org_id,
oha.ship_from_org_id,
oha.sold_to_org_id,
oha.ship_to_org_id,
ola.sold_to_org_id,
ola.ship_to_org_id
from oe_order_headers_all oha, oe_order_lines_all ola
where ola.header_id = oha.header_id
and ola.org_id = oha.org_id
and oha.sold_to_org_id in (13087, 13084, 13077, 13072);
--order by ola.creation_date desc;
OE_ORDER_HEADERS_ALL and OE_ORDER_LINES_ALL
==============================
SOLD_FROM_ORG_ID SHIP_FROM_ORG_ID SOLD_TO_ORG_ID SHIP_TO_ORG_ID SOLD_TO_ORG_ID
SHIP_TO_ORG_ID
---------------------- ---------------------- --------------- --------------- --------------- ------------
---
204 207 13084 9308 13084 9308
204 207 13087 9312 13087 9312
2 rows selected
PROMPT ISC_BOOK_SUM2_PDUE2_F
PROMPT ==============================
-- distinct ids from ISC table
-- customer's id(party),
-- sold_to_org_id(customer's account ids)
-- ship_to_org_id (customer's account's ship to location id's)
select distinct customer_id, sold_to_org_id, ship_to_org_id
from ISC_BOOK_SUM2_PDUE2_F
where customer_id = 58276;
ISC_BOOK_SUM2_PDUE2_F
==============================
CUSTOMER_ID SOLD_TO_ORG_ID SHIP_TO_ORG_ID
-------------- --------------- ---------------
58276 13084 9308
58276 13087 9312
2 rows selected
PROMPT MSC_TRADING_PARTNERS;
PROMPT *********************************;
SELECT partner_id,
sr_tp_id,
sr_instance_id,
partner_type,
SUBSTR(partner_name, 1, 20),
SUBSTR(partner_number, 1, 10)
FROM msc_trading_partners
WHERE partner_name LIKE '&CustomerName%';
-- On APS Suite Applications Server
-- get trading partner source id's
MSC_TRADING_PARTNERS
PARTNER_ID SR_TP_ID SR_INSTANCE_ID PARTNER_TYPE
SUBSTR(PARTNER_NAME,1,20) SUBSTR(PARTNER_NUMBER,1,10)
---------------------- ---------------------- ---------------------- ---------------------- --------------
----------- ---------------------------
63894 13087 21 2 EE-Party-1
3888
1 rows selected
-- get trading partner site information
-- for Supplier -- EE-Supplier-1, EE-SUPSITE-1 (OU1)
-- Only collects one site per trading partner in sites table
-- Additional sites are mapped out in msc_tp_site_id_lid
PROMPT MSC_TRADING_PARTNER_SITES;
PROMPT *********************************;
SELECT partner_site_id,
sr_tp_site_id,
tp_site_code,
sr_instance_id,
partner_id,
SUBSTR(partner_address, 1, 40),
sr_tp_id,
SUBSTR(location, 1, 30)
FROM msc_trading_partner_sites
WHERE sr_tp_site_id IN(9310, 9314, 9306, 9312, 9316, 9308);
-- get trading partner site's source id's
-- could be multiple sites for one partner
-- could be same site code across multiple sites/ou's
PROMPT MSC_TP_ID_LID;
PROMPT *********************************;
SELECT sr_tp_id,
sr_instance_id,
partner_type,
tp_id,
sr_company_id
FROM msc_tp_id_lid
WHERE sr_tp_id IN(13072, 13084, 13087);
SR_TP_ID SR_INSTANCE_ID PARTNER_TYPE TP_ID SR_COMPANY_ID
---------------------- ---------------------- ---------------------- ---------------------- --------------
13072 21 2 63894 -1
13084 21 2 63894 -1
13087 21 2 63894 -1
3 rows selected
PROMPT 'PARTNER RECORD for Partner,Site,Account Id,Location';
PROMPT *********************************;
SELECT 'mtp',
SUBSTR(mtp.partner_number, 1, 10),
mtp.sr_tp_id,
mtp.partner_id,
mtp.partner_type,
SUBSTR(mtp.partner_name, 1, 30),
'tp_id_lid',
mtil.sr_tp_id,
SUBSTR(mtil.sr_cust_account_number, 1, 30),
'mtps',
mtps.partner_site_id,
mtps.sr_tp_site_id,
mtps.partner_id,
mtps.partner_type,
SUBSTR(mtps.location, 1, 30),
SUBSTR(mtps.partner_address, 1, 40),
SUBSTR(mtps.operating_unit_name, 1, 10),
SUBSTR(mtps.country, 1, 10),
SUBSTR(mtps.state, 1, 10),
SUBSTR(mtps.city, 1, 10),
mtps.postal_code,
'mla',
mla.location_id,
SUBSTR(mla.location_code, 1, 30),
'tp_site_id_lid',
mtsil.sr_tp_site_id,
mtps.tp_site_code,
mtsil.operating_unit
FROM msc_trading_partners mtp,
msc_tp_id_lid mtil,
msc_trading_partner_sites mtps,
msc_location_associations mla,
msc_tp_site_id_lid mtsil
WHERE mtps.partner_id = mtp.partner_id
AND mtps.partner_type = mtp.partner_type
AND partner_name LIKE '&CustomerName%'
AND mtil.tp_id = mtp.partner_id
AND mtsil.sr_cust_acct_id = mtil.sr_tp_id
AND mla.partner_site_id(+) = mtps.partner_site_id
AND mla.partner_id(+) = mtps.partner_id
AND mtps.partner_site_id = mtsil.tp_site_id
AND mtp.partner_type = 2;
MSC_TRADING_PARTNER_SITES
'PARTNER RECORD for Partner,Site,Account Id,Location'
'MTP' SUBSTR(MTP.PARTNER_NUMBER,1,10) SR_TP_ID PARTNER_ID PARTNER_TYPE
SUBSTR(MTP.PARTNER_NAME,1,30) 'TP_ID_LID' SR_TP_ID SUBSTR(MTIL.SR_CUST_ACCOUNT_NUMBER,1,30)
'MTPS' PARTNER_SITE_ID SR_TP_SITE_ID PARTNER_ID PARTNER_TYPE
SUBSTR(MTPS.LOCATION,1,30) SUBSTR(MTPS.PARTNER_ADDRESS,1,40)
SUBSTR(MTPS.OPERATING_UNIT_NAME,1,10) SUBSTR(MTPS.COUNTRY,1,10) SUBSTR(MTPS.STATE,1,10)
SUBSTR(MTPS.CITY,1,10) POSTAL_CODE 'MLA' LOCATION_ID
SUBSTR(MLA.LOCATION_CODE,1,30) 'TP_SITE_ID_LID' SR_TP_SITE_ID TP_SITE_CODE
OPERATING_UNIT
----- ------------------------------- ---------------------- ---------------------- ----------------------
------------------------------ ----------- ---------------------- ----------------------------------------
------ ---------------------- ---------------------- ---------------------- ---------------------- -------
----------------------- ---------------------------------------- ------------------------------------- ---
---------------------- ----------------------- ---------------------- ------------------------------------
------------------------ ----- ---------------------- ------------------------------ ---------------- ----
------------------ ------------------------------ ----------------------
mtp 3888 13087 63894 2
EE-Party-1 tp_id_lid 13084 3887
mtps 67171 9308 63894 2 EE
CusAcctShipTo Loc 1 EE 2222 Party Street 2,,,,San Francisco, Vision Ope
US CA San Franci 94100
mla tp_site_id_lid 9308
SHIP_TO
mtp 3888 13087 63894 2
EE-Party-1 tp_id_lid 13087 3888
mtps 67169 9312 63894 2 EE
CusAcct2ShipTo Loc 1 EE 9333 Party Street 3,,,,San Francisco, Vision Ope
US CA San Franci 94100
mla tp_site_id_lid 9312
SHIP_TO
mtp 3888 13087 63894 2
EE-Party-1 tp_id_lid 13087 3888
mtps 67166 9310 63894 2 EE
CusAcct2BillTo Loc 1 EE 9333 Party Street 3,,,,San Francisco, Vision Ope
US CA San Franci 94100
mla tp_site_id_lid 9310
BILL_TO
mtp 3888 13087 63894 2
EE-Party-1 tp_id_lid 13087 3888
mtps 67167 9314 63894 2 EE
CusAcct2BillTo Loc 2 EE 4444 CusAcct2 Street 4,,,,San Francis Vision Ope
US CA San Franci 94100
mla tp_site_id_lid 9314
BILL_TO
mtp 3888 13087 63894 2
EE-Party-1 tp_id_lid 13084 3887
mtps 67168 9306 63894 2 EE
CusAcctBillTo Loc 1 EE 2222 Party Street 2,,,,San Francisco, Vision Ope
US CA San Franci 94100
mla tp_site_id_lid 9306
BILL_TO
mtp 3888 13087 63894 2
EE-Party-1 tp_id_lid 13087 3888
mtps 67170 9316 63894 2 EE
CusAcct2ShipTo Loc 2 EE 4444 CusAcct2 Street 4,,,,San Francis Vision Ope
US CA San Franci 94100
mla tp_site_id_lid 9316
SHIP_TO
6 rows selected
--CREATE OR REPLACE VIEW msd_dem_sr_pdue_backlog_v(org_code, item_code, sr_customer_pk, sdate,
quantity) AS
PROMPT VIEW msd_dem_sr_pdue_backlog_v;
PROMPT *********************************;
SELECT app.organization_code,
itm.segment1,
pdue.customer_id,
pdue.time_snapshot_date_id,
SUM(pdue.pdue_qty)
FROM isc_book_sum2_pdue2_f pdue,
mtl_system_items itm,
msd_dem_app_instance_orgs app,
msd_dem_setup_parameters morg,
msd_dem_setup_parameters para2
WHERE morg.parameter_name = 'MSD_DEM_MASTER_ORG'
AND para2.parameter_name = 'MSD_DEM_TWO_LEVEL_PLANNING'
AND pdue.inventory_item_id = itm.inventory_item_id
AND pdue.inv_org_id = itm.organization_id
AND app.organization_id = pdue.inv_org_id
AND pdue.late_schedule_flag = 1
/* Past due based on Schedule Ship Date */
AND decode(nvl(itm.ato_forecast_control, 3), 3,
decode(nvl(para2.parameter_value, '2'), '1',
msd_dem_sr_util.is_product_family_forecastable(morg.parameter_value,
pdue.inventory_item_id, 2), 2), 1) = 1
AND itm.mrp_planning_code <> 6
AND segment1 LIKE 'XX%'
GROUP BY app.organization_code,
itm.segment1,
pdue.customer_id,
pdue.time_snapshot_date_id
ORDER BY pdue.time_snapshot_date_id;
ORGANIZATION_CODE SEGMENT1 CUSTOMER_ID
TIME_SNAPSHOT_DATE_ID SUM(PDUE.PDUE_QTY)
----------------------------- ---------------------------------------- ---------------------- ------------
------------- ----------------------
TST:M1 XX-Prod-D 50276 01-JAN-00
150
TST:M1 XX-Prod-D 50276 10-SEP-07
30
TST:M1 XX-Prod-D 50276 12-SEP-07
30
TST:M1 XX-Prod-D 50276 28-SEP-07
30
4 rows selected
PROMPT 'please run this script and check how many customer_id records in the isc';
PROMPT table do not exist in party_id in the hz_cust_accounts table;
PROMPT *********************************;
PROMPT check for records with customer_id in isc table;
PROMPT that are not in hz_cust_accounts table party_id;
PROMPT *********************************;
select customer_id, count(*) from ISC_BOOK_SUM2_PDUE2_F a
where not exists
(select party_id
from hz_cust_accounts
where party_id = a.customer_id)
group by customer_id;
PROMPT check for records with sold_to_org_id in isc table
PROMPT that are not in cust_account_id in hz table
PROMPT *********************************;
select sold_to_org_id, count(*) from ISC_BOOK_SUM2_PDUE2_F a
where not exists
(select cust_account_id
from hz_cust_accounts
where cust_account_id = a.sold_to_org_id)
group by sold_to_org_id;
- Run this script and check how many customer_id records in the isc' table do not exist in party_id
in the hz_cust_accounts table
- Check for records with customer_id in isc table that are not in hz_cust_accounts table party_id
PROMPT 'spot check oe_order_lines_all table'
PROMPT *********************************;
select sold_to_org_id, count(*) from oe_order_headers_all a
where not exists
(select cust_account_id
from hz_cust_accounts
where cust_account_id = a.sold_to_org_id)
group by sold_to_org_id;
CUSTOMER_ID COUNT(*)
---------------------- ----------------------
16
1 rows selected
- Check for records with sold_to_org_id in isc table that are not in cust_account_id in hz table
SOLD_TO_ORG_ID COUNT(*)
---------------------- ----------------------
1337
1 rows selected
-- Get data from MSC_TRADING_PARTNERS
SELECT sr_tp_id,
partner_id,
status,
partner_name,
partner_number
FROM msc_trading_partners
WHERE partner_name LIKE 'EE%';
msc_trading_partners
SR_TP_ID PARTNER_ID STATUS PARTNER_NAME PARTNER_NUMBER
13072 63894 A EE-Party-1 3885
In DP Collections,
the EE-Party-1:3887 and EE-Party-1:3888 are the Customers.
-- Get data from MSD_LEVEL_VALUES
SELECT level_id,
level_value,
sr_level_pk,
level_pk
FROM msd_level_values
WHERE level_value LIKE '%EE-Party-1%';
msd_level_values
LEVEL_ID LEVEL_VALUE SR_LEVEL_PK LEVEL_PK
-------- ----------- ----------- ---------
15 EE-Party-1:3887 13084 85866
15 EE-Party-1:3888 13087 85867
SQL Scripts/Output
EBS R12.1.3 Case Study Scripts
-- Verify MRP_AP_CUSTOMERS_V and MRP_AP_CUSTOMER_SITES_V views are populated
prompt MRP_AP_CUSTOMERS_V
SELECT sr_tp_id, -- customer_id
status,
partner_type,
partner_name,
partner_number, -- customer_number
attribute8,
aggregate_demand_flag
FROM mrp_ap_customers_v
WHERE partner_name LIKE 'EE%'
ORDER BY sr_tp_id;
MRP_AP_CUSTOMERS_V
SR_TP_ID STATUS PARTNER_TYPE PARTNER_NAME PARTNER_NUMBER ATTRIBUTE8
AGGREGATE_DEMAND_FLAG
----------- ------ ------------ ------------- ---------------- ----------- --
--------------------
116737 A 2 EE-Party-1 5821 2
-- Get the data from MRP_AP_CUSTOMER_SITES_V
prompt MRP_AP_CUSTOMER_SITES_V
SELECT sr_tp_id,
tp_site_code,
sr_tp_site_id,
partner_site_number,
location,
location_id,
cust_acct_site_id,
org_id,
partner_address
FROM mrp_ap_customer_sites_v --@dblinktosrc
-- WHERE location LIKE 'EE%'
where sr_tp_id = 116737
ORDER BY sr_tp_id,
sr_tp_site_id;
MRP_AP_CUSTOMER_SITES_V
SR_TP_ID TP_SITE_CODE SR_TP_SITE_ID PARTNER_SITE_NUMBER LOCATION LOCATION_ID CUST_ACCT_SITE_ID
ORG_ID PARTNER_ADDRESS
------------ ----------------- -------------- --------------------- --------- ------------- --------------------
-- ------- ---------------------------------------------------------------
116737 BILL_TO 13832 24601 13833 26397 11257
204 EE Party 1s Street 1,,,,Colorado Springs,CO,80920,,US
116737 BILL_TO 13834 24602 13836 26399 11259
204 EE Party 1s ShipTo Street 1,,,,Colorado Springs,CO,80920,,US
116737 SHIP_TO 13835 24602 13837 26399 11259
204 EE Party 1s ShipTo Street 1,,,,Colorado Springs,CO,80920,,US
VCP R12.1.3.1 Case Study Scripts
-- a. inv item
--
SELECT *
FROM mtl_system_items_b
WHERE segment1 IN ('&Model','&OptClass1','&OptItem1','&OptClass2','&OptItem2')
ORDER BY segment1,
organization_id;
--
-- b. bom bom
--
SELECT *
FROM bom_structures_b
WHERE assembly_item_id IN
(SELECT inventory_item_id
FROM mtl_system_items_b
WHERE segment1 IN ('&Model','&OptClass1','&OptItem1','&OptClass2','&OptItem2')
ORDER BY assembly_item_id;
--
-- c. bom components
--
SELECT *
FROM bom_components_b
WHERE bill_sequence_id IN
(SELECT bill_sequence_id
FROM bom_structures_b
WHERE assembly_item_id IN
(SELECT inventory_item_id
FROM mtl_system_items_b
WHERE segment1 IN
('&Model','&OptClass1','&OptItem1','&OptClass2','&OptItem2')
ORDER BY bill_sequence_id;
--
--d. msc item
--
SELECT *
FROM msc_system_items
WHERE item_name IN ('&Model','&OptClass1','&OptItem1','&OptClass2','&OptItem2')
AND sr_instance_id =
&InstID
AND plan_id = -1
-- and organization_id in (&Master, &Child);
ORDER BY item_name,
organization_id;
--
-- e. msc bom
--
SELECT *
FROM msc_boms
WHERE assembly_item_id IN
(SELECT inventory_item_id
FROM msc_system_items
WHERE item_name IN
('&Model','&OptClass1','&OptItem1','&OptClass2','&OptItem2')
AND sr_instance_id =
&InstID
AND plan_id = -1
AND sr_instance_id =
&InstID
AND plan_id = -1
-- and organization_id in (&Master, &Child)
ORDER BY assembly_item_id,
organization_id;
--
--f. msc bom components
--
SELECT *
FROM msc_bom_components
WHERE bill_sequence_id IN
(SELECT bill_sequence_id
FROM msc_boms
WHERE assembly_item_id IN
(SELECT inventory_item_id
FROM msc_system_items
WHERE item_name IN
('&Model','&OptClass1','&OptItem1','&OptClass2','&OptItem2')
AND sr_instance_id =
&InstID
AND plan_id = -1
)
AND sr_instance_id =
&InstID
AND plan_id = -1
AND sr_instance_id =
&InstID
AND plan_id = -1
ORDER BY bill_sequence_id,
inventory_item_id;
---
---
SQL Scripts/Output
VCP R12.1.3.1 Case Study Scripts
SQL>
--
-- msc atp rule
--
SELECT rule_id,
sr_instance_id,
rule_name,
description,
infinite_supply_fence_code,
infinite_supply_time_fence
FROM msc_atp_rules
WHERE rule_name = 'EE-ATP-RULE-1';
Output:
RULE_ID SR_INSTANCE_ID RULE_NAME DESCRIPTION
INFINITE_SUPPLY_FENCE_CODE INFINITE_SUPPLY_TIME_FENCE
----------- ---------------- ----------------- ----------------- ----------------
---------- --------------------------
24273 2061 EE-ATP-RULE-1 EE ATP Rule 1 4
30
--
-- manual update atp rule defined previous from 30 days to 1 day
-- so i can test this schedule out on 1 day
-- to schedule the sales order so I can complete the test case
-- this is an update of collected data,
-- and will be overwritten next collections run
UPDATE msc_atp_rules
SET infinite_supply_time_fence = 1
WHERE rule_id = 24273;
SQL> COMMIT;
