Extracting Data for Oracle Supply Chain Planning Cloud – Steps to Customize the Integration Logic
Introduction
Oracle Supply Chain Planning (SCP) Cloud is a powerful tool for demand forecasting, inventory optimization, and supply planning. However, organizations often need to extract data efficiently and customize the integration logic to align with specific business processes. This blog outlines the steps to customize the data extraction process for Oracle SCP Cloud.
Steps to Customize the Integration Logic
The integration can be customized to modify the data being extracted in various stages. A new custom package MSC_APCL_CUSTOM holds all the procedures where custom code can be embedded.
Custom hooks for data extraction
- Customization applicable for all the entities being extracted:
Place this type of customization code inside the procedure called MSC_APCL_CUSTOM.FRAMEWORK_PRE_PROCESS
This procedure gets called at the beginning of the data extraction process. Below are the input/output parameters of this procedure.
PROCEDURE framework_pre_process (
p_errbuf OUT NOCOPY VARCHAR2,— Return error message if any
p_retcode OUT NOCOPY VARCHAR2— Return status- 0(success),1(error), 2(warning)
p_custom_catlog_impl OUT NOCOPY NUMBER,— Return 1(yes) if custom catalog is implemented, else return 2(no)
p_request_id I
Place the custom code inside these two procedures below:
MSC_APCL_CUSTOM.ENTITY_PRE_PROCESS
This procedure is invoked in the beginning of data extraction for the entity
procedure entity_pre_process(
p_errbuf out nocopy varchar2, –Return error message if any
p_retcode out nocopy varchar2, — Return status
p_instance_code in varchar2,
p_entity_name in varchar2,
p_coll_mode in varchar2,
p_org_filter in varchar2,
p_catalog_filter in varchar2,
p_prd in varchar2, — Date in ‘yyyy/mm/ddhh24:mi:ss’ format as string
p_prn in varchar2,
p_lrn in varchar2,
p_date_from in varchar2 default null, — Date in ‘yyyy/mm/dd’ format as
string
p_date_to in varchar2 default null, — date in ‘yyyy/mm/dd’ format as
string
p_coll_iso in number default 2,
p_coll_all_ord_types in number default 2,
p_incl_ord_types in varchar2 default null)
MSC_APCL_CUSTOM.ENTITY_POST_PROCESS
This procedure is invoked at the end of data extraction for the entity
procedure entity_post_process(
p_errbuf out nocopy varchar2,
p_retcode out nocopy varchar2,
p_instance_code in varchar2,
p_entity_name in varchar2,
p_coll_mode in varchar2,
p_org_filter in varchar2,
p_catalog_filter in varchar2,
p_prd in varchar2, — Date in ‘yyyy/mm/dd hh24:mi:ss’ format asstring
p_prn in varchar2,
p_lrn in varchar2,
p_date_from in varchar2 default null, — Date will be in’yyyy/mm/dd’ format as string
p_date_to in varchar2 default null, — Date will be in ‘yyyy/mm/dd’ format as string
p_coll_iso in number default 2,
p_coll_all_ord_types in number default 2,
p_incl_ord_types in varchar2 default null)
- To improve performance of the extract users can set custom_filter, custom_extract_group and custom_fetch_size for each entity being extracted. To do this users need to update table MSC_EBS_FUSION_COLL_INTG to set these 3 columns for each entity.
-
- custom_filter : This filter would restrict the data being read from the EBS source view for the given entity.
- custom_fetch_size : This value is used to fetch number of records to be flushed to the .csv file being generated for the given entity. This can be set to higher value for the entities that produce large number of records.
- custom_extract_group : Data extraction happens in groups. All the entites are segregated across 10 groups based on their size to distribute the load across 10 workers. If customer has different data distribution for the entities being extracted then the detault grouping can be overridden using this column.
- Custom hooks for release
Following procedures enable users to customize the import of SCP cloud recommendations into EBS.
MSC_APCL_CUSTOM.EXTEND_FSCP_RELEASE_MSC_DATA
This custom hook can be used to modify records in MSC_RELEASE_LINES_INT table for the current batch being released to ERP source system.
procedure extend_fscp_release_msc_data(
p_errbuf out nocopy varchar2,
p_retcode out nocopy varchar2,
p_batch_id in number) –Pass batch_id of the records loaded intoMSC_RELEASE_LINES_INT through CSV files in current release data load
MSC_APCL_CUSTOM.EXTEND_FSCP_RELEASE_ERP_DATA
This custom hook can be used to modify records in PO and WIP interface tables for the current batch being released to ERP source system
procedure extend_fscp_release_erp_data(
p_errbuf out nocopy varchar2,
p_retcode out nocopy varchar2,
p_po_batch_id in number, — Pass batch_id of the records loaded into PO interface table
p_wip_batch_id in number) — Pass batch_id of the records loaded into WIP interface table
Conclusion
Customizing the data extraction process for Oracle SCP Cloud enables businesses to optimize supply chain visibility and decision-making. By following structured steps and best practices, organizations can ensure seamless and efficient integration with their existing systems.
