PL SQL Script: Sample Package Body
whenever sqlerror exit failure rollback;
create or replace package body cz_user_autoselection as
/**********************************************************************************/
/* $Header: CZUASLTB.pls 110.2 97/07/15 17:52:53 appldev ship $ */
/**********************************************************************************/
/* Copyright (c) 1997 Oracle Corporation Redwood Shores, California, USA */
/* All rights reserved. */
/* Oracle Manufacturing */
/**********************************************************************************/
/* */
/* Program: CZUASLTB.pls */
/* Description: This file contains the prototypes for the following functions */
/* */
/* 1. User_AutoSelection */
/* This serves as an extension to the built in capabilities */
/* of Oracle Product Configurator. */
/* */
/* The User AutoSelection function is enabled by selecting the */
/* User Function Based Items and Quantities AutoSelection */
/* Option from the Assign Constraints form. The Name of the */
/* PL/SQL Package is entered in the Function field of the */
/* AutoSelection Items window. The Product Configurator engine */
/* calls the named package and validates the items and */
/* quantities the PL/SQL package returns against the orderable bill */
/* of material. Items must exist uniquely and the quantity must */
/* be permitted for that item in the orderable bill of material. */
/* */
/* 2. User_Optimize_Expression */
/* A packaged function that evaluates a complex optimization */
/* expression external to the Product Configurator, */
/* and returns the value to the Product Configurator */
/* to be used for the AutoSelection/AutoExclusion */
/* Types: Range Based Items and Quantities, Optimized Quantities */
/* and Optimized Items and Quantities. */
/* */
/* The User_Optimize_Expression function is enabled by checking */
/* the User Function check box on the AutoSelection Items window */
/* and entering the Name of the PL/SQL Package in the */
/* Optimization Expression field of the same window. */
/* The Product Configurator engine calls the named package which */
/* returns a character or numeric value to be used for */
/* evaluation with the defined ranges of the specified */
/* AutoSelection/AutoExclusion Type. */
/* */
/* 3. User_Clause_Function */
/* A packaged function that evaluates and returns a value of */
/* true/false/stop to the Product Configurator to be used by */
/* the evaluating class. */
/* */
/* The User_Clause_Function is invoked by entering the name of the */
/* packagefunction in the User Validate Function column of the */
/* constraints form. The function column in the Defined */
/* Constraint form should be 'User Validate' and the operator */
/* should be 'NA'. */
/* */
/* Context Information: */
/* The previous autoselections that have occured for the sales */
/* order line in this run of Configuration validation are */
/* viewable through the view CZ_COMPONENT_SELECTIONS_V and */
/* filtered based on the group_id which is passed in to the */
/* User_AutoSelection function. Item_svrid column contains the */
/* constraint assignment_id that has autoselected the item, */
/* while those with NULL value represent customer selections. */
/* */
/* Called By: CZ_VALIDATE_CONFIGURATION (CZVCFGRS/B.pls) */
/* */
/* Changed by: Who When What */
/* Tony Gaughan 9/17/97 Updated layout (standards) */
/* */
/**********************************************************************************/
/**********************************************************************************/
/* Function: User_AutoSelection */
/* */
/* Define user extensible rules to perform autoselections. */
/* AutoSelected items are inserted into BOM_CONFIG_EXPLOSIONS */
/* where the following rules must apply. */
/* */
/* Validation: (Done after exiting this procedure) */
/* */
/* 1. inventory_item_id, organization_id and sort_order */
/* should be NOT NULL */
/* 2. the record should represent a valid component on the */
/* bill of the model ordered on the sales order line */
/* 3. quantity should be NOT NULL and consistent with the */
/* BOM component quantities */
/* */
/* Parameters In: pnLineID */
/* The sales order line that is being validated. This */
/* always points to the top model that has been ordered. */
/* */
/* pnAssignmentD */
/* Unique identifier of constraint assignment that is being */
/* evaluated */
/* */
/* pnGroup ID */
/* Used to identify prior autoselections that have occured */
/* in this run of validation */
/* */
/* Out: xcErrorMessage */
/* Message string which is displayed to user */
/* */
/* */
/* Returns: Integer Value ( 0 - Failure, 1 - Success) */
/* */
/**********************************************************************************/
function User_AutoSelections( pnLineID in number,
pnAssignmentID in number,
pnGroupID in number,
xcErrorMessage out varchar2 )
return integer is
lnStatement number := 0; -- define any locals
begin
lnStatement := 10;
/**********************************************************************************/
/* Function Logic goes here */
/* Execute custom code that will evaluate a complex optimization */
/* expression and return the value to be used */
/* items to be added to the sales order line that is being validated */
/**********************************************************************************/
/**********************************************************************************/
/* Template for insert into Bom_Config_Explosions table */
/**********************************************************************************/
/*
insert into bom_config_explosions
(
top_bill_sequence_id,
bill_sequence_id,
organization_id,
explosion_type,
component_sequence_id,
component_item_id,
item_svrid,
plan_level,
extended_quantity,
sort_order,
creation_date,
created_by,
last_update_date,
last_updated_by,
group_id,
session_id,
select_flag,
select_quantity,
top_item_id,
context,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
component_quantity,
so_basis,
optional,
mutually_exclusive_options,
check_atp,
shipping_allowed,
required_to_ship,
required_for_revenue,
include_on_ship_docs,
include_on_bill_docs,
low_quantity,
high_quantity,
pick_components,
primary_uom_code,
primary_unit_of_measure,
base_item_id,
atp_components_flag,
atp_flag,
bom_item_type,
pick_components_flag,
replenish_to_order_flag,
shippable_item_flag,
customer_order_flag,
internal_order_flag,
customer_order_enabled_flag,
internal_order_enabled_flag,
so_transactions_flag,
description,
assembly_item_id,
configurator_flag,
rounding_factor,
component_code,
loop_flag,
parent_bom_item_type,
operation_seq_num,
item_num,
effectivity_date,
disable_date,
implementation_date,
rexplode_flag,
common_bill_sequence_id,
comp_bill_seq_id,
comp_common_bill_seq_id,
num_col1,
num_col2,
num_col3,
date_col1,
date_col2,
date_col3,
char_col1,
char_col2,
char_col3
)
select top_bill_sequence_id,
bill_sequence_id,
be.organization_id,
'OPTIONAL',
be.component_sequence_id,
component_item_id,
x_assignment_id,
plan_level,
extended_quantity,
sort_order,
sysdate,
1,
sysdate,
1,
x_group_id,
null, -- session_id
'Y', -- select_flag
1, -- select_qty
top_item_id,
be.context,
be.attribute1,
be.attribute2,
be.attribute3,
be.attribute4,
be.attribute5,
be.attribute6,
be.attribute7,
be.attribute8,
be.attribute9,
be.attribute10,
be.attribute11,
be.attribute12,
be.attribute13,
be.attribute14,
be.attribute15,
component_quantity,
so_basis,
optional,
mutually_exclusive_options,
check_atp,
shipping_allowed,
required_to_ship,
required_for_revenue,
include_on_ship_docs,
include_on_bill_docs,
low_quantity,
high_quantity,
pick_components,
primary_uom_code,
primary_unit_of_measure,
base_item_id,
atp_components_flag,
atp_flag,
bom_item_type,
pick_components_flag,
replenish_to_order_flag,
shippable_item_flag,
customer_order_flag,
internal_order_flag,
customer_order_enabled_flag,
internal_order_enabled_flag,
so_transactions_flag,
description,
assembly_item_id,
null,
null,
be.component_code,
loop_flag,
parent_bom_item_type,
operation_seq_num,
item_num,
sysdate,
disable_date,
implementation_date,
rexplode_flag,
common_bill_sequence_id,
comp_bill_seq_id,
comp_common_bill_seq_id,
null, -- sequence number
num_col2,
num_col3,
date_col1,
date_col2,
date_col3,
char_col1,
char_col2,
char_col3
from so_lines_all sol,
bom_explosions be
where sol.line_id = x_line_id
and be.top_bill_sequence_id = sol.component_sequence_id
and be.component_code = sol.component_code
and be.organization_id = sol.warehouse_id
and be.explosion_type = 'OPTIONAL'
and be.bom_item_type not in ( 1, 2 )
and be.effectivity_date <= sol.creation_date
and be.disable_date > sol.creation_date;
*/
return (1); -- success
exception
when others then
xcErrorMessage := 'CZ_USER_SELECTION.User_AutoSelections(' ||
to_char( lcStatement) || '):' || SQLCODE || ':' ||
substr( SQLERRM, 1, 60 );
return (0); -- failure
end User_AutoSelections;
/**********************************************************************************/
/* Function: User_Optimization_Expression */
/* User extensible function to allow complex optimization */
/* calculations */
/* */
/* Parameters In: pnLineID */
/* The sales order line that is being validated. This */
/* always points to the top model that has been ordered. */
/* */
/* pnAssignmentD */
/* Unique identifier of constraint assignment that is being */
/* evaluated */
/* */
/* pnGroup ID */
/* Used to identify prior autoselections that have occured */
/* in this run of validation */
/* */
/* Out: xcExpressionValue */
/* Optimization expression value to be returned */
/* */
/* xcDataType */
/* Data Type of returned expression (number, varchar2) */
/* */
/* xcErrorMessage */
/* Message string which is displayed to user */
/* */
/* */
/* Returns: Integer Value ( 0 - Failure, 1 - Success) */
/* */
/**********************************************************************************/
function User_Optimization_Expression( pnLineID in NUMBER,
pnAssignmentID in NUMBER,
pnGroupID in NUMBER,
xcExpressionValue out VARCHAR2,
xcDataType out VARCHAR2,
xcErrorMessage out VARCHAR2 )
return integer is
lnStatement number := 0; -- local variables
begin
lnStatement := 10;
/**********************************************************************************/
/* Function Logic goes here */
/* Execute custom code that will return value for the optimized expression */
/**********************************************************************************/
return (1); -- success, no internal exception
exception
when others then
xcErrorMessage := 'CZ_USER_SELECTION.User_Optimize_Expression(' ||
to_char( lnStatement ) || '):' || SQLCODE || ':' ||
substr( SQLERRM, 1, 60 );
return (0); -- failure, internal exception
end User_Optimization_Expression;
/**********************************************************************************/
/* Function: User_Clause_Function */
/* User extensible function to allow complex clause */
/* execution */
/* */
/* Parameters In: pnLineID */
/* The sales order line that is being validated. This */
/* always points to the top model that has been ordered. */
/* */
/* pnAssignmentD */
/* Unique identifier of constraint assignment that is being */
/* evaluated */
/* */
/* pnGroup ID */
/* Used to identify prior autoselections that have occured */
/* in this run of validation */
/* */
/* Out: xnReturnedValue */
/* Value of clause evaluation 0 clause evaluates to FALSE */
/* 1 clause evaluates to TRUE */
/* 2 clause evaluates to STOP */
/* */
/* xcErrorMessage */
/* Message string which is displayed to user */
/* */
/* Returns: Integer Value ( 0 - Failure, 1 - Success) */
/* */
/**********************************************************************************/
function User_Clause_Function( pnLineID in number,
pnAssignmentID in number,
pnGroupID in number,
xnReturnedValue out number,
xcErrorMessage out varchar2 )
return integer is
lnStatement Number := 0; -- local variables
begin
lnStatement := 10;
/**********************************************************************************/
/* Function Logic goes here */
/* Execute custom code that will return TRUE, FALSE or STOP for the */
/* clause being evaluated */
/**********************************************************************************/
if (Success) then
xnReturnedValue := 1;
elsif (Stop) -- stop evaluation of constraint
xnReturnedValue := 2;
else (Failure) -- default
xnReturnedValue := 0;
end if;
*/ return (1); -- success, no internal exception
exception
when others then
xcErrorMessage := 'CZ_USER_SELECTION.User_Clause_Function(' ||
to_char( lnStatement ) || '):' || SQLCODE || ':' ||
substr( SQLERRM, 1, 60 );
return (0); -- failure, internal exception
end User_Clause_Function;
end CZ_User_Autoselection;
/
exit;
