[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE NAME: insert_bill
=========================================================================== */
PROCEDURE insert_bill (x_rec IN OUT NOCOPY bom_bill_of_mtls_interface%ROWTYPE,
x_assembly_item_name IN VARCHAR2 DEFAULT NULL,
x_organization_code IN VARCHAR2 DEFAULT NULL,
x_error_code IN OUT NOCOPY NUMBER,
x_error_msg IN OUT NOCOPY VARCHAR2) IS
x_progress VARCHAR2(3) := NULL;
e_insert_bill EXCEPTION;
raise e_insert_bill;
/******* begin delete ******
This insert is being commented out since we will use
the BOM Business Object API in 11.i.2 instead
of the Open Interface
INSERT INTO BOM_BILL_OF_MTLS_INTERFACE(
transaction_type,
assembly_item_id,
organization_id,
alternate_bom_designator,
common_assembly_item_id,
specific_assembly_comment,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
assembly_type,
common_bill_sequence_id,
bill_sequence_id,
revision,
common_organization_id,
process_flag,
organization_code,
common_org_code,
item_number,
common_item_number
) VALUES (
'CREATE',
x_rec.assembly_item_id,
x_rec.organization_id,
x_rec.alternate_bom_designator,
x_rec.common_assembly_item_id,
x_rec.specific_assembly_comment,
x_rec.attribute_category,
x_rec.attribute1,
x_rec.attribute2,
x_rec.attribute3,
x_rec.attribute4,
x_rec.attribute5,
x_rec.attribute6,
x_rec.attribute7,
x_rec.attribute8,
x_rec.attribute9,
x_rec.attribute10,
x_rec.attribute11,
x_rec.attribute12,
x_rec.attribute13,
x_rec.attribute14,
x_rec.attribute15,
x_rec.assembly_type,
x_rec.common_bill_sequence_id,
x_rec.bill_sequence_id,
x_rec.revision,
x_rec.common_organization_id,
x_process_flag,
x_rec.organization_code,
x_rec.common_org_code,
x_rec.item_number,
x_rec.common_item_number);
****** end delete ******/
-- begin add for wsm
g_bom_header_rec.Transaction_Type := BOM_Globals.G_OPR_CREATE;
WHEN e_insert_bill THEN
x_error_code := 1;
fnd_message.set_token('OBJECT_NAME', 'WSMPPCPD.insert_bill');
x_error_msg := 'WSMPPCPD.insert_bill(' || x_progress || ')' || ' - ' || substr(sqlerrm, 1, 200);
END insert_bill;
PROCEDURE NAME: insert_component
=========================================================================== */
PROCEDURE insert_component (x_rec IN OUT NOCOPY bom_inventory_comps_interface%ROWTYPE,
x_component_name IN VARCHAR2 DEFAULT NULL,
x_organization_code IN VARCHAR2 DEFAULT NULL,
x_assembly_item_name IN VARCHAR2 DEFAULT NULL,
x_supply_locator IN VARCHAR2 DEFAULT NULL,
x_error_code IN OUT NOCOPY NUMBER,
x_error_msg IN OUT NOCOPY VARCHAR2) IS
x_progress VARCHAR2(3) := NULL;
e_insert_component EXCEPTION;
raise e_insert_component;
/* This insert will be commented since we do not want to
insert into the interface table anymore. We will use
the bom bo api with 11.i.2
INSERT INTO BOM_INVENTORY_COMPS_INTERFACE(
transaction_type,
operation_seq_num,
component_item_id,
item_num,
component_quantity,
component_yield_factor,
component_remarks,
effectivity_date,
disable_date,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
planning_factor,
quantity_related,
so_basis,
optional,
mutually_exclusive_options,
include_in_cost_rollup,
check_atp,
required_to_ship,
required_for_revenue,
include_on_ship_docs,
low_quantity,
high_quantity,
component_sequence_id,
bill_sequence_id,
wip_supply_type,
supply_subinventory,
supply_locator_id,
operation_lead_time_percent,
assembly_item_id,
alternate_bom_designator,
organization_id,
organization_code,
component_item_number,
assembly_item_number,
location_name,
reference_designator,
substitute_comp_id,
substitute_comp_number,
process_flag
) VALUES (
'CREATE',
x_rec.operation_seq_num,
x_rec.component_item_id,
x_rec.item_num,
x_rec.component_quantity,
x_rec.component_yield_factor,
x_rec.component_remarks,
x_rec.effectivity_date,
x_rec.disable_date,
x_rec.attribute_category,
x_rec.attribute1,
x_rec.attribute2,
x_rec.attribute3,
x_rec.attribute4,
x_rec.attribute5,
x_rec.attribute6,
x_rec.attribute7,
x_rec.attribute8,
x_rec.attribute9,
x_rec.attribute10,
x_rec.attribute11,
x_rec.attribute12,
x_rec.attribute13,
x_rec.attribute14,
x_rec.attribute15,
x_rec.planning_factor,
x_rec.quantity_related,
x_rec.so_basis,
x_rec.optional,
x_rec.mutually_exclusive_options,
x_rec.include_in_cost_rollup,
x_rec.check_atp,
x_rec.required_to_ship,
x_rec.required_for_revenue,
x_rec.include_on_ship_docs,
x_rec.low_quantity,
x_rec.high_quantity,
x_rec.component_sequence_id,
x_rec.bill_sequence_id,
x_rec.wip_supply_type,
x_rec.supply_subinventory,
x_rec.supply_locator_id,
x_rec.operation_lead_time_percent,
x_rec.assembly_item_id,
x_rec.alternate_bom_designator,
x_rec.organization_id,
x_rec.organization_code,
x_rec.component_item_number,
x_rec.assembly_item_number,
x_rec.location_name,
x_rec.reference_designator,
x_rec.substitute_comp_id,
x_rec.substitute_comp_number,
x_process_flag);
WHEN e_insert_component THEN
x_error_code := 1;
fnd_message.set_token('OBJECT_NAME', 'WSMPPCPD.insert_component');
x_error_msg := 'WSMPPCPD.insert_component(' || x_progress || ')' || ' - ' || substr(sqlerrm, 1, 200);
END insert_component;
PROCEDURE NAME: insert_substitute_component
=========================================================================== */
PROCEDURE insert_substitute_component (
x_rec IN OUT NOCOPY bom_sub_comps_interface%ROWTYPE,
x_co_product_name IN VARCHAR2,
x_alternate_designator IN VARCHAR2,
x_component_name IN VARCHAR2,
x_comp_start_eff_date IN DATE,
x_org_code IN VARCHAR2,
x_error_code IN OUT NOCOPY NUMBER,
x_error_msg IN OUT NOCOPY VARCHAR2) IS
x_progress VARCHAR2(3) := NULL;
e_insert_substitute EXCEPTION;
raise e_insert_substitute;
INSERT INTO BOM_SUB_COMPS_INTERFACE(
transaction_type,
substitute_component_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
substitute_item_quantity,
component_sequence_id,
acd_type,
change_notice,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
bill_sequence_id,
assembly_item_id,
alternate_bom_designator,
organization_id,
component_item_id,
operation_seq_num,
effectivity_date,
transaction_id,
process_flag,
organization_code,
substitute_comp_number,
component_item_number,
assembly_item_number
) VALUES (
'CREATE',
x_rec.substitute_component_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id,
x_rec.substitute_item_quantity,
x_rec.component_sequence_id,
x_rec.acd_type,
x_rec.change_notice,
x_rec.attribute_category,
x_rec.attribute1,
x_rec.attribute2,
x_rec.attribute3,
x_rec.attribute4,
x_rec.attribute5,
x_rec.attribute6,
x_rec.attribute7,
x_rec.attribute8,
x_rec.attribute9,
x_rec.attribute10,
x_rec.attribute11,
x_rec.attribute12,
x_rec.attribute13,
x_rec.attribute14,
x_rec.attribute15,
x_rec.bill_sequence_id,
x_rec.assembly_item_id,
x_rec.alternate_bom_designator,
x_rec.organization_id,
x_rec.component_item_id,
x_rec.operation_seq_num,
x_rec.effectivity_date,
x_rec.transaction_id,
x_process_flag,
x_rec.organization_code,
x_rec.substitute_comp_number,
x_rec.component_item_number,
x_rec.assembly_item_number);
WHEN e_insert_substitute THEN
x_error_code := 1;
fnd_message.set_token('OBJECT_NAME', 'WSMPPCPD.insert_substitute_component');
x_error_msg := x_error_msg || ' - ' || 'WSMPPCPD.insert_substitute_component('||x_progress||')';
x_error_msg := 'WSMPPCPD.insert_substitute_component(' || x_progress || ')' || ' - ' || substr(sqlerrm, 1, 200);
END insert_substitute_component;
PROCEDURE NAME: insert_sub_comps
=========================================================================== */
PROCEDURE insert_sub_comps (x_co_product_group_id IN NUMBER,
x_co_product_name IN VARCHAR2,
x_alternate_designator IN VARCHAR2,
x_component_name IN VARCHAR2,
x_comp_start_eff_date IN DATE,
x_org_code IN VARCHAR2,
x_component_sequence_id IN NUMBER,
x_qty_multiplier IN NUMBER,
x_error_code IN OUT NOCOPY NUMBER,
x_error_msg IN OUT NOCOPY VARCHAR2) IS
x_progress VARCHAR2(3) := NULL;
CURSOR S IS SELECT *
FROM wsm_co_prod_comp_substitutes
WHERE co_product_group_id = x_co_product_group_id;
g_subs_comp_tbl.delete;
WSMPPCPD.insert_substitute_component (x_rec,
x_co_product_name,
x_alternate_designator,
x_component_name,
x_comp_start_eff_date,
x_org_code,
x_error_code,
x_error_msg);
x_error_msg := x_error_msg || ' - ' || 'WSMPPCPD.insert_sub_comps('||x_progress||')';
x_error_msg := 'WSMPPCPD.insert_sub_comps(' || x_progress || ')' || ' - ' || substr(sqlerrm, 1, 200);
END insert_sub_comps;
x_last_update_login NUMBER,
x_last_updated_by NUMBER,
x_last_update_date DATE,
x_creation_date DATE,
x_created_by NUMBER,
x_substitute_item_quantity NUMBER,
x_attribute_category VARCHAR2,
x_attribute1 VARCHAR2,
x_attribute2 VARCHAR2,
x_attribute3 VARCHAR2,
x_attribute4 VARCHAR2,
x_attribute5 VARCHAR2,
x_attribute6 VARCHAR2,
x_attribute7 VARCHAR2,
x_attribute8 VARCHAR2,
x_attribute9 VARCHAR2,
x_attribute10 VARCHAR2,
x_attribute11 VARCHAR2,
x_attribute12 VARCHAR2,
x_attribute13 VARCHAR2,
x_attribute14 VARCHAR2,
x_attribute15 VARCHAR2,
x_error_code IN OUT NOCOPY NUMBER,
x_error_msg IN OUT NOCOPY VARCHAR2) IS
x_progress VARCHAR2(3) := NULL;
x_skip_sub_delete NUMBER := 0;
CURSOR S IS SELECT *
FROM wsm_co_prod_comp_substitutes
WHERE co_product_group_id = x_co_product_group_id;
CURSOR C (x_comp_seq_id NUMBER) IS SELECT rowid
FROM bom_substitute_components
WHERE component_sequence_id = x_comp_seq_id
AND substitute_component_id = x_substitute_comp_id_old
FOR UPDATE OF substitute_component_id NOWAIT;
CURSOR C_COPROD IS SELECT component_sequence_id,
bill_sequence_id
FROM wsm_co_products
WHERE co_product_group_id = x_co_product_group_id
And co_product_id is NOT NULL;
SELECT 1
INTO x_co_prod_exists
FROM wsm_co_products
WHERE co_product_group_id = x_co_product_group_id
AND co_product_id IS NOT NULL;
WSMPCPCS.insert_row (
x_rowid,
x_co_product_group_id,
x_substitute_component_id,
x_last_update_login,
x_last_updated_by,
x_last_update_date,
x_creation_date,
x_created_by,
x_substitute_item_quantity,
x_attribute_category,
x_attribute1,
x_attribute2,
x_attribute3,
x_attribute4,
x_attribute5,
x_attribute6,
x_attribute7,
x_attribute8,
x_attribute9,
x_attribute10,
x_attribute11,
x_attribute12,
x_attribute13,
x_attribute14,
x_attribute15,
null,
null,
null,
null);
SELECT component_sequence_id,
bill_sequence_id
INTO x_component_sequence_id,
x_bill_sequence_id
FROM wsm_co_products
WHERE co_product_group_id = x_co_product_group_id
And co_product_id is NOT NULL
AND NVL(primary_flag, 'N') = 'Y';
SELECT 1
INTO x_comp_exists
FROM bom_inventory_components
WHERE component_sequence_id = x_component_sequence_id;
SELECT 1
INTO x_dummy
FROM bom_substitute_components
WHERE nvl(acd_type, 1) = 1
AND substitute_component_id = x_substitute_component_id
AND component_sequence_id = x_component_sequence_id;
SELECT 1
INTO x_dummy
FROM bom_bill_of_materials bbom
WHERE bbom.common_bill_sequence_id = x_bill_sequence_id
AND bbom.organization_id <> x_org_id
AND NOT EXISTS (
SELECT null
FROM mtl_system_items msi
WHERE msi.organization_id = bbom.organization_id
AND msi.inventory_item_id = x_substitute_component_id
AND msi.bom_enabled_flag = 'Y'
AND ((bbom.assembly_type = 1
AND msi.eng_item_flag = 'N')
OR (bbom.assembly_type = 2)));
bom_sub_comps_pkg.insert_row(x_sub_rowid,
x_substitute_component_id,
x_last_update_date,
x_last_updated_by,
x_creation_date,
x_created_by,
x_last_update_login,
x_substitute_item_quantity,
x_component_sequence_id,
null,
null,
x_attribute_category,
x_attribute1,
x_attribute2,
x_attribute3,
x_attribute4,
x_attribute5,
x_attribute6,
x_attribute7,
x_attribute8,
x_attribute9,
x_attribute10,
x_attribute11,
x_attribute12,
x_attribute13,
x_attribute14,
x_attribute15);
WSMPCPCS.insert_row (x_rowid,
x_co_product_group_id,
x_substitute_component_id,
x_last_update_login,
x_last_updated_by,
x_last_update_date,
x_creation_date,
x_created_by,
x_substitute_item_quantity,
x_attribute_category,
x_attribute1,
x_attribute2,
x_attribute3,
x_attribute4,
x_attribute5,
x_attribute6,
x_attribute7,
x_attribute8,
x_attribute9,
x_attribute10,
x_attribute11,
x_attribute12,
x_attribute13,
x_attribute14,
x_attribute15,
null,
null,
null,
null);
SELECT 1
INTO x_dummy
FROM bom_substitute_components
WHERE nvl(acd_type, 1) = 1
AND substitute_component_id = x_substitute_component_id
AND component_sequence_id = x_component_sequence_id;
SELECT 1
INTO x_dummy
FROM bom_bill_of_materials bbom
WHERE bbom.common_bill_sequence_id = x_bill_sequence_id
AND bbom.organization_id <> x_org_id
AND NOT EXISTS
(SELECT null
FROM mtl_system_items msi
WHERE msi.organization_id = bbom.organization_id
AND msi.inventory_item_id = x_substitute_component_id
AND msi.bom_enabled_flag = 'Y'
AND ((bbom.assembly_type = 1
AND msi.eng_item_flag = 'N')
OR (bbom.assembly_type = 2)));
bom_sub_comps_pkg.update_row(x_sub_rowid,
x_substitute_component_id,
x_last_update_date,
x_last_updated_by,
x_last_update_login,
x_substitute_item_quantity,
x_component_sequence_id,
null,
null,
x_attribute_category,
x_attribute1,
x_attribute2,
x_attribute3,
x_attribute4,
x_attribute5,
x_attribute6,
x_attribute7,
x_attribute8,
x_attribute9,
x_attribute10,
x_attribute11,
x_attribute12,
x_attribute13,
x_attribute14,
x_attribute15);
WSMPCPCS.update_row(X_rowid,
x_co_product_group_id,
x_substitute_component_id,
x_last_update_login,
x_last_updated_by,
x_last_update_date,
x_substitute_item_quantity,
x_attribute_category,
x_attribute1,
x_attribute2,
x_attribute3,
x_attribute4,
x_attribute5,
x_attribute6,
x_attribute7,
x_attribute8,
x_attribute9,
x_attribute10,
x_attribute11,
x_attribute12,
x_attribute13,
x_attribute14,
x_attribute15,
null,
null,
null,
null);
x_skip_sub_delete := 1;
IF (x_skip_sub_delete = 0) THEN
bom_sub_comps_pkg.Delete_Row (x_sub_rowid);
WSMPCPCS.Delete_Row (x_rowid);
x_error_msg := x_error_msg || ' - ' || 'WSMPPCPD.insert_sub_comps('||x_progress||')';
x_last_update_login NUMBER,
x_last_updated_by NUMBER,
x_last_update_date DATE,
x_creation_date DATE,
x_created_by NUMBER,
x_substitute_item_quantity NUMBER,
x_attribute_category VARCHAR2,
x_attribute1 VARCHAR2,
x_attribute2 VARCHAR2,
x_attribute3 VARCHAR2,
x_attribute4 VARCHAR2,
x_attribute5 VARCHAR2,
x_attribute6 VARCHAR2,
x_attribute7 VARCHAR2,
x_attribute8 VARCHAR2,
x_attribute9 VARCHAR2,
x_attribute10 VARCHAR2,
x_attribute11 VARCHAR2,
x_attribute12 VARCHAR2,
x_attribute13 VARCHAR2,
x_attribute14 VARCHAR2,
x_attribute15 VARCHAR2,
x_basis_type NUMBER, --LBM enh
x_error_code IN OUT NOCOPY NUMBER,
x_error_msg IN OUT NOCOPY VARCHAR2) IS
x_progress VARCHAR2(3) := NULL;
x_skip_sub_delete NUMBER := 0;
CURSOR S IS SELECT *
FROM wsm_co_prod_comp_substitutes
WHERE co_product_group_id = x_co_product_group_id;
CURSOR C (x_comp_seq_id NUMBER) IS SELECT rowid
FROM bom_substitute_components
WHERE component_sequence_id = x_comp_seq_id
AND substitute_component_id = x_substitute_comp_id_old
FOR UPDATE OF substitute_component_id NOWAIT;
CURSOR C_COPROD IS SELECT component_sequence_id,
bill_sequence_id
FROM wsm_co_products
WHERE co_product_group_id = x_co_product_group_id
And co_product_id is NOT NULL
AND component_sequence_id IS NOT NULL;
IF (x_process_code = 1) THEN /* Insert */
BEGIN
-- modification begin for perf. tuning.. abedajna 10/12/00
SELECT 1
INTO x_co_prod_exists
FROM wsm_co_products
WHERE co_product_group_id = x_co_product_group_id
AND co_product_id IS NOT NULL;
/* Insert into wsm_co_prod_comp_substitutes. */
WSMPCPCS.insert_row (
x_rowid,
x_co_product_group_id,
x_substitute_component_id,
x_last_update_login,
x_last_updated_by,
x_last_update_date,
x_creation_date,
x_created_by,
x_substitute_item_quantity,
x_attribute_category,
x_attribute1,
x_attribute2,
x_attribute3,
x_attribute4,
x_attribute5,
x_attribute6,
x_attribute7,
x_attribute8,
x_attribute9,
x_attribute10,
x_attribute11,
x_attribute12,
x_attribute13,
x_attribute14,
x_attribute15,
null,
null,
null,
null,
x_basis_type); --LBM enh
/*coprod enh p2 introduced a loop that inserts the substitutes for all the coproducts*/
FOR rec in C_COPROD LOOP
/* Verify that the component exists in BOM. */
x_progress := '040';
SELECT 1
INTO x_comp_exists
FROM bom_inventory_components
WHERE component_sequence_id = rec.component_sequence_id;
IF (x_process_code = 1) THEN /* Insert */
/* Check for uniqueness in bom_component_substitutes. */
x_progress := '050';
SELECT 1
INTO x_dummy
FROM bom_substitute_components
WHERE nvl(acd_type, 1) = 1
AND substitute_component_id = x_substitute_component_id
AND component_sequence_id = rec.component_sequence_id;
SELECT 1
INTO x_dummy
FROM bom_bill_of_materials bbom
WHERE bbom.common_bill_sequence_id = rec.bill_sequence_id
AND bbom.organization_id <> x_org_id
AND NOT EXISTS (
SELECT null
FROM mtl_system_items msi
WHERE msi.organization_id = bbom.organization_id
AND msi.inventory_item_id = x_substitute_component_id
AND msi.bom_enabled_flag = 'Y'
AND ((bbom.assembly_type = 1
AND msi.eng_item_flag = 'N')
OR (bbom.assembly_type = 2)));
Insert into bom_substitute_components followed
by an insert into wsm_co_prod_comp_substitutes.
*/
x_progress := '070';
bom_sub_comps_pkg.insert_row(x_sub_rowid,
x_substitute_component_id,
x_last_update_date,
x_last_updated_by,
x_creation_date,
x_created_by,
x_last_update_login,
x_substitute_item_quantity,
rec.component_sequence_id,
null,
null,
x_attribute_category,
x_attribute1,
x_attribute2,
x_attribute3,
x_attribute4,
x_attribute5,
x_attribute6,
x_attribute7,
x_attribute8,
x_attribute9,
x_attribute10,
x_attribute11,
x_attribute12,
x_attribute13,
x_attribute14,
x_attribute15);
ELSIF (x_process_code = 2) THEN /* Update */
IF (x_substitute_component_id <> x_substitute_comp_id_old) THEN
/* Check that the new substitute component is unique. */
x_progress := '080';
SELECT 1
INTO x_dummy
FROM bom_substitute_components
WHERE nvl(acd_type, 1) = 1
AND substitute_component_id = x_substitute_component_id
AND component_sequence_id =rec.component_sequence_id;
SELECT 1
INTO x_dummy
FROM bom_bill_of_materials bbom
WHERE bbom.common_bill_sequence_id = rec.bill_sequence_id
AND bbom.organization_id <> x_org_id
AND NOT EXISTS
(SELECT null
FROM mtl_system_items msi
WHERE msi.organization_id = bbom.organization_id
AND msi.inventory_item_id = x_substitute_component_id
AND msi.bom_enabled_flag = 'Y'
AND ((bbom.assembly_type = 1
AND msi.eng_item_flag = 'N')
OR (bbom.assembly_type = 2)));
* and perform the update.
*/
OPEN C (rec.component_sequence_id);
bom_sub_comps_pkg.update_row(x_sub_rowid,
x_substitute_component_id,
x_last_update_date,
x_last_updated_by,
x_last_update_login,
x_substitute_item_quantity,
rec.component_sequence_id,
null,
null,
x_attribute_category,
x_attribute1,
x_attribute2,
x_attribute3,
x_attribute4,
x_attribute5,
x_attribute6,
x_attribute7,
x_attribute8,
x_attribute9,
x_attribute10,
x_attribute11,
x_attribute12,
x_attribute13,
x_attribute14,
x_attribute15);
ELSIF (x_process_code = 3) THEN /* Delete */
/* Lock record in bom_substitute_components. */
x_progress := '120';
x_skip_sub_delete := 1;
IF (x_skip_sub_delete = 0) THEN
bom_sub_comps_pkg.Delete_Row (x_sub_rowid);
WSMPCPCS.insert_row (x_rowid,
x_co_product_group_id,
x_substitute_component_id,
x_last_update_login,
x_last_updated_by,
x_last_update_date,
x_creation_date,
x_created_by,
x_substitute_item_quantity,
x_attribute_category,
x_attribute1,
x_attribute2,
x_attribute3,
x_attribute4,
x_attribute5,
x_attribute6,
x_attribute7,
x_attribute8,
x_attribute9,
x_attribute10,
x_attribute11,
x_attribute12,
x_attribute13,
x_attribute14,
x_attribute15,
null,
null,
null,
null,
x_basis_type); --LBM enh
WSMPCPCS.update_row(X_rowid,
x_co_product_group_id,
x_substitute_component_id,
x_last_update_login,
x_last_updated_by,
x_last_update_date,
x_substitute_item_quantity,
x_attribute_category,
x_attribute1,
x_attribute2,
x_attribute3,
x_attribute4,
x_attribute5,
x_attribute6,
x_attribute7,
x_attribute8,
x_attribute9,
x_attribute10,
x_attribute11,
x_attribute12,
x_attribute13,
x_attribute14,
x_attribute15,
null,
null,
null,
null,
x_basis_type); --LBM enh
WSMPCPCS.Delete_Row (x_rowid);
x_error_msg := x_error_msg || ' - ' || 'WSMPPCPD.insert_sub_comps('||x_progress||')';
x_bill_insert IN OUT NOCOPY BOOLEAN,
x_p_bill_insert IN OUT NOCOPY BOOLEAN,
x_comp_insert IN OUT NOCOPY BOOLEAN,
x_p_comp_insert IN OUT NOCOPY BOOLEAN,
x_error_code IN OUT NOCOPY NUMBER,
x_error_msg IN OUT NOCOPY VARCHAR2)
IS
x_progress VARCHAR2(3) := NULL;
x_bill_insert := FALSE;
x_p_bill_insert := FALSE;
x_comp_insert := FALSE;
x_p_comp_insert := FALSE;
SELECT bbom.bill_sequence_id,
bbom.common_bill_sequence_id
INTO x_existing_bom,
x_comm_bill_seq_id
FROM bom_bill_of_materials bbom
WHERE bbom.assembly_item_id = x_co_product_id
AND bbom.organization_id = x_org_id
AND bbom.alternate_bom_designator is NULL;
x_comp_insert := TRUE;
x_bill_insert := TRUE;
x_comp_insert := TRUE;
x_comp_insert := FALSE;
SELECT bbom.bill_sequence_id,
bbom.common_bill_sequence_id
INTO x_existing_bom,
x_comm_bill_seq_id
FROM bom_bill_of_materials bbom
WHERE bbom.assembly_item_id = x_co_product_id
AND bbom.organization_id = x_org_id
AND bbom.alternate_bom_designator = x_alternate_designator;
x_comp_insert := TRUE;
** SELECT 1
** INTO x_bom_exists
** FROM sys.dual
** WHERE EXISTS (SELECT 1
** FROM bom_bill_of_materials bbom
** WHERE bbom.assembly_item_id = x_co_product_id
** AND bbom.organization_id = x_org_id
** AND bbom.alternate_bom_designator is NULL);
SELECT 1
INTO x_bom_exists
FROM bom_bill_of_materials bbom
WHERE bbom.assembly_item_id = x_co_product_id
AND bbom.organization_id = x_org_id
AND bbom.alternate_bom_designator is NULL;
x_bill_insert := TRUE;
x_comp_insert := TRUE;
x_comp_insert := FALSE;
x_p_bill_insert := TRUE;
x_bill_insert := TRUE;
x_p_comp_insert := FALSE;
x_comp_insert := TRUE;
x_comp_insert := FALSE;
x_bill_insert IN BOOLEAN DEFAULT FALSE,
x_p_bill_insert IN BOOLEAN DEFAULT FALSE,
x_comp_insert IN BOOLEAN DEFAULT FALSE,
x_p_comp_insert IN BOOLEAN DEFAULT FALSE,
x_basis_type IN NUMBER , --LBM enh
x_coprod_attribute_category VARCHAR2 DEFAULT NULL,
x_coprod_attribute1 VARCHAR2 DEFAULT NULL,
x_coprod_attribute2 VARCHAR2 DEFAULT NULL,
x_coprod_attribute3 VARCHAR2 DEFAULT NULL,
x_coprod_attribute4 VARCHAR2 DEFAULT NULL,
x_coprod_attribute5 VARCHAR2 DEFAULT NULL,
x_coprod_attribute6 VARCHAR2 DEFAULT NULL,
x_coprod_attribute7 VARCHAR2 DEFAULT NULL,
x_coprod_attribute8 VARCHAR2 DEFAULT NULL,
x_coprod_attribute9 VARCHAR2 DEFAULT NULL,
x_coprod_attribute10 VARCHAR2 DEFAULT NULL,
x_coprod_attribute11 VARCHAR2 DEFAULT NULL,
x_coprod_attribute12 VARCHAR2 DEFAULT NULL,
x_coprod_attribute13 VARCHAR2 DEFAULT NULL,
x_coprod_attribute14 VARCHAR2 DEFAULT NULL,
x_coprod_attribute15 VARCHAR2 DEFAULT NULL,
x_comp_attribute_category VARCHAR2 DEFAULT NULL,
x_comp_attribute1 VARCHAR2 DEFAULT NULL,
x_comp_attribute2 VARCHAR2 DEFAULT NULL,
x_comp_attribute3 VARCHAR2 DEFAULT NULL,
x_comp_attribute4 VARCHAR2 DEFAULT NULL,
x_comp_attribute5 VARCHAR2 DEFAULT NULL,
x_comp_attribute6 VARCHAR2 DEFAULT NULL,
x_comp_attribute7 VARCHAR2 DEFAULT NULL,
x_comp_attribute8 VARCHAR2 DEFAULT NULL,
x_comp_attribute9 VARCHAR2 DEFAULT NULL,
x_comp_attribute10 VARCHAR2 DEFAULT NULL,
x_comp_attribute11 VARCHAR2 DEFAULT NULL,
x_comp_attribute12 VARCHAR2 DEFAULT NULL,
x_comp_attribute13 VARCHAR2 DEFAULT NULL,
x_comp_attribute14 VARCHAR2 DEFAULT NULL,
x_comp_attribute15 VARCHAR2 DEFAULT NULL,
x_error_code IN OUT NOCOPY NUMBER,
x_error_msg IN OUT NOCOPY VARCHAR2)
IS
x_progress VARCHAR2(3) := NULL;
SELECT 1
FROM sys.dual
WHERE EXISTS (SELECT 1
FROM bom_inventory_components bic
WHERE bic.bill_sequence_id = x_bill_seq_id
AND (x_disable_date is NULL
OR (trunc(x_disable_date) > trunc(bic.effectivity_date)))
AND ((trunc(x_effectivity_date) < trunc(bic.disable_date))
OR bic.disable_date is NULL)); */
IF (x_p_bill_insert) THEN
WSMPCOGI.get_bill_comp_sequence (x_p_bill_sequence_id,
x_error_code,
x_error_msg);
IF (x_p_comp_insert) THEN
WSMPCOGI.get_bill_comp_sequence (x_p_component_sequence_id,
x_error_code,
x_error_msg);
IF (x_bill_insert) THEN
WSMPCOGI.get_bill_comp_sequence (x_bill_sequence_id,
x_error_code,
x_error_msg);
IF (x_comp_insert) THEN
WSMPCOGI.get_bill_comp_sequence (x_component_sequence_id,
x_error_code,
x_error_msg);
IF (x_p_bill_insert) THEN
x_rec.assembly_item_id := x_co_product_id;
WSMPPCPD.insert_bill ( x_rec,
x_co_product_name,
x_org_code,
x_error_code,
x_error_msg);
g_component_tbl.delete;
g_subs_comp_tbl.delete;
any substitute component. What we did in the previous insert was
just creating a Primary Bill for the sake of being able to create
the alternate bill (designator) that was specified in the co-products
form. So in this case we will not call the bom bo api till the entire
object has been prepared.
*/
IF (x_bill_insert) THEN
x_rec.assembly_item_id := x_co_product_id;
WSMPPCPD.insert_bill ( x_rec,
x_co_product_name,
x_org_code,
x_error_code,
x_error_msg);
END IF; -- End of x_bill_insert
IF (x_comp_insert) THEN
x_rec_comp.alternate_bom_designator := x_alternate_designator;
WSMPPCPD.insert_component ( x_rec_comp,
x_component_name,
x_org_code,
x_co_product_name,
x_supply_locator,
x_error_code,
x_error_msg);
/* Insert substitutes. */
WSMPPCPD.insert_sub_comps (x_co_product_group_id,
x_co_product_name,
x_alternate_designator,
x_component_name,
--bug 2987645
-- x_effectivity_date,
l_effectivity_date,
--end bug 2987645
x_org_code,
x_component_sequence_id,
x_quantity,
x_error_code,
x_error_msg);
END IF; -- End of x_comp_insert
If x_bill_insert AND (x_comp_insert <> TRUE) Then
WSMPPCPD.call_bom_bo_api (
p_bom_header_rec => g_bom_header_rec,
x_error_code => x_error_code,
x_error_msg => x_error_msg );
** will not create primary bill (x_bill_insert = FALSE).
** But still we need to create the component definiton and hence
** we need to check only for x_comp_insert = TRUE and not both.
**
** - Bala BALAKUMAR, July 20th, 2000.
*/
If x_comp_insert Then /*Bug#1359654 fix */
WSMPPCPD.call_bom_bo_api (
p_bom_header_rec => g_bom_header_rec,
p_component_tbl => g_component_tbl,
p_subs_comp_tbl => g_subs_comp_tbl,
x_error_code => x_error_code,
x_error_msg => x_error_msg );
g_component_tbl.delete;
g_subs_comp_tbl.delete;
If x_comp_insert Then
IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
DECLARE
l_comp_eff_date DATE;
select bic.effectivity_date
into l_comp_eff_date
from bom_inventory_components bic,
bom_bill_of_materials bom
where bom.bill_sequence_id = x_bill_sequence_id
and bic.bill_sequence_id = bom.common_bill_sequence_id
and bic.component_item_id = x_component_id
and bic.operation_seq_num = 1;
End If; -- End of getCompseqId if x_comp_insert is True.
/* For Update
Bill attribute columns are not being updated
based on discussion with B. Arvindh (02/09/98)
Update component columns. */
x_progress := '150';
/* Lock corresponding component prior to update. */
WSMPPCPD.lock_component(x_component_sequence_id,
x_error_code,
x_error_msg);
** to update the component details on update mode.
** Bala
*/
UPDATE bom_inventory_components
SET component_quantity = x_quantity,
basis_type = decode(x_basis_type, 2, 2, null), --LBM enh
disable_date = x_disable_date,
effectivity_date = x_effectivity_date,
attribute_category = x_comp_attribute_category,
attribute1 = x_comp_attribute1,
attribute2 = x_comp_attribute2,
attribute3 = x_comp_attribute3,
attribute4 = x_comp_attribute4,
attribute5 = x_comp_attribute5,
attribute6 = x_comp_attribute6,
attribute7 = x_comp_attribute7,
attribute8 = x_comp_attribute8,
attribute9 = x_comp_attribute9,
attribute10 = x_comp_attribute10,
attribute11 = x_comp_attribute11,
attribute12 = x_comp_attribute12,
attribute13 = x_comp_attribute13,
attribute14 = x_comp_attribute14,
attribute15 = x_comp_attribute15
WHERE common_component_sequence_id = x_component_sequence_id
OR component_sequence_id = x_component_sequence_id;
/* Lock corresponding component prior to update. */
WSMPPCPD.lock_component (x_component_sequence_id,
x_error_code,
x_error_msg);
UPDATE bom_inventory_components
SET disable_date = sysdate
WHERE component_sequence_id = x_component_sequence_id;
SELECT bcp.bill_sequence_id
FROM wsm_co_products bcp
WHERE bcp.co_product_group_id = x_co_product_group_id
AND bcp.bill_sequence_id <> x_bill_sequence_id
AND bcp.co_product_id is NOT NULL;
SELECT count (1)
INTO x_count_comp
FROM bom_inventory_components
WHERE bill_sequence_id = S_rec.bill_sequence_id;
SELECT bbom.common_bill_sequence_id
INTO x_current_comm_bill
FROM bom_bill_of_materials bbom
WHERE bbom.bill_sequence_id = S_rec.bill_sequence_id
AND EXISTS (SELECT 1
FROM wsm_co_products bcp
WHERE bcp.bill_sequence_id = bbom.common_bill_sequence_id
AND (bcp.disable_date is NULL
OR bcp.disable_date > sysdate)
AND bcp.co_product_group_id <> x_co_product_group_id);
/* -- Lock corresponding bill prior to update. */
WSMPPCPD.lock_bill (S_rec.bill_sequence_id,
x_error_code,
x_error_msg);
UPDATE bom_bill_of_materials
SET common_assembly_item_id = x_c_assembly_id,
common_organization_id = x_c_org_id,
common_bill_sequence_id = x_c_bill_seq_id
WHERE bill_sequence_id = S_rec.bill_sequence_id;
SELECT bcp.bill_sequence_id,
bcp.organization_id,
bcp.co_product_id
INTO x_c_bill_seq_id,
x_c_org_id,
x_c_assembly_id
FROM wsm_co_products bcp
WHERE bcp.co_product_group_id = x_co_product_group_id
AND bcp.primary_flag = 'Y'
AND rownum = 1;
SELECT count(*)
INTO x_count_comp
FROM bom_inventory_components
WHERE bill_sequence_id = x_bill_sequence_id;
SELECT bbom.common_bill_sequence_id
INTO x_current_comm_bill
FROM bom_bill_of_materials bbom
WHERE bbom.bill_sequence_id = x_bill_sequence_id
AND EXISTS (SELECT 1
FROM wsm_co_products bcp
WHERE bcp.bill_sequence_id = bbom.common_bill_sequence_id
AND ( bcp.disable_date is NULL
OR bcp.disable_date > sysdate)
AND bcp.co_product_group_id <> x_co_product_group_id);
/* -- Lock corresponding bill prior to update. */
WSMPPCPD.lock_bill (x_bill_sequence_id,
x_error_code,
x_error_msg);
UPDATE bom_bill_of_materials
SET common_assembly_item_id = x_c_assembly_id,
common_organization_id = x_c_org_id,
common_bill_sequence_id = x_c_bill_seq_id
WHERE bill_sequence_id = x_bill_sequence_id;
fnd_message.set_name('WSM', 'WSM_NO_BILL_UPDATE');
PROCEDURE NAME: delete_component
=========================================================================== */
PROCEDURE delete_component(x_co_product_group_id IN NUMBER,
x_rowid IN VARCHAR2,
x_error_code IN OUT NOCOPY NUMBER,
x_error_msg IN OUT NOCOPY VARCHAR2)
IS
x_progress VARCHAR2(3) := NULL;
e_delete_component EXCEPTION;
SELECT rowid
FROM wsm_co_prod_comp_substitutes
WHERE co_product_group_id = x_co_product_group_id;
SELECT bcp.co_product_id
FROM wsm_co_products bcp
WHERE bcp.co_product_group_id = x_co_product_group_id
AND bcp.co_product_id is NOT NULL;
raise e_delete_component;
/* -- Delete all the co-products. */
x_progress := '020';
WSMPPCPD.delete_co_product (x_co_product_group_id,
S_rec.co_product_id,
x_error_code,
x_error_msg);
/* -- Delete all the substitutes. */
x_progress := '033';
WSMPCPCS.delete_row (C_rec.rowid);
WSMPCPDS.delete_row (x_rowid);
WHEN e_delete_component THEN
x_error_code := 1;
fnd_message.set_token('OBJECT_NAME', 'WSMPPCPD.delete_component');
x_error_msg := x_error_msg || ' - ' || 'WSMPPCPD.delete_component('||x_progress||')';
x_error_msg := 'WSMPPCPD.delete_component(' || x_progress || ')' || ' - ' || substr(sqlerrm, 1, 200);
END delete_component;
PROCEDURE NAME: delete_co_product
=========================================================================== */
PROCEDURE delete_co_product(x_co_product_group_id IN NUMBER,
x_co_product_id IN NUMBER,
x_error_code IN OUT NOCOPY NUMBER,
x_error_msg IN OUT NOCOPY VARCHAR2)
IS
x_progress VARCHAR2(3) := NULL;
e_delete_co_product EXCEPTION;
raise e_delete_co_product;
SELECT bcp.component_sequence_id,
bcp.component_id,
bcp.effectivity_date,
bcp.rowid
INTO x_component_sequence_id,
x_component_id,
x_effectivity_date,
x_rowid
FROM wsm_co_products bcp
WHERE bcp.co_product_group_id = x_co_product_group_id
AND bcp.co_product_id = x_co_product_id;
SELECT bcp.component_id,
bcp.effectivity_date,
bcp.bill_sequence_id,
bcp.primary_flag,
bcp.rowid
INTO x_component_id,
x_effectivity_date,
x_bill_sequence_id,
x_primary_flag,
x_rowid
FROM wsm_co_products bcp
WHERE bcp.co_product_group_id = x_co_product_group_id
AND bcp.co_product_id = x_co_product_id;
/* -- Update bill. */
x_progress := '030';
** Notes on Delete(ion) of a co-product.
** 1. WE DONOT ALLOW A PRIMARY CO-PRODUCT TO BE DELETED.
** 2. WE ALLOW SECONDARY CO-PRODUCTS DELETTION.
** 3. WE ALLOW THE WHOLE CO-PRODUCT DEFINTION TO BE DELETED.
**
** Changes as per above;
** 2. We should delete the bom_header for the secondary co-product
** when we delete it from the co-product definition because in BOM
** Header level, there is nothing like a disable date. However, we
** should NOT UPDATE the BOM_INVENTORY_COMPONENTS as it belongs to
** the primary co-product bill.
** 3. When we delete the whole co-product definition, then we should
** delete all the co-product definition as well all the bom headers
** corresponding to the co-products and all the bom_inventory_components
** corresponding to the primary co-product' bill.
** - Bala BALAKUMAR, June 23rd, 2000.
*/
-- Commenting the following code out. Please refer to bug 2816426
/* ***************************************************************************
WSMPPCPD.lock_bill( x_bill_sequence_id => x_bill_sequence_id
, x_error_code => x_error_code
, x_error_msg => x_error_msg);
delete bom_bill_of_materials
where bill_sequence_id = x_bill_sequence_id;
** we need to delete the bom_inventory_components also.
If NVL(x_primary_flag, 'N') = 'Y' Then
SELECT bcp.component_sequence_id
INTO x_component_sequence_id
FROM wsm_co_products bcp
WHERE bcp.co_product_group_id = x_co_product_group_id
AND bcp.co_product_id is not NULL
And NVL(bcp.primary_flag, 'N') = 'Y';
delete bom_inventory_components
Where bill_sequence_id = x_bill_sequence_id
And component_sequence_id = x_component_sequence_id;
** delete alone.
BEGIN
x_progress := '034';
delete bom_substitute_components
Where component_sequence_id = x_component_sequence_id;
delete bom_reference_designators
Where component_sequence_id = x_component_sequence_id;
WSMPCPSB.delete_substitutes (x_co_product_group_id,
x_co_product_id);
WSMPCPDS.delete_row (x_rowid);
WHEN e_delete_co_product THEN
x_error_code := 1;
fnd_message.set_token('OBJECT_NAME', 'WSMPPCPD.delete_co_product');
x_error_msg := x_error_msg || ' - ' || 'WSMPPCPD.delete_co_product('||x_progress||')';
x_error_msg := 'WSMPPCPD.delete_co_product(' || x_progress || ')' || ' - ' || substr(sqlerrm, 1, 200);
END delete_co_product;
PROCEDURE NAME: update_co_prod_details
=========================================================================== */
PROCEDURE update_co_prod_details(x_co_product_group_id IN NUMBER,
x_effectivity_date IN DATE,
x_disable_date IN DATE,
x_usage_rate IN NUMBER,
x_inv_usage IN NUMBER,
x_duality_flag IN VARCHAR2,
x_basis_type IN NUMBER, --LBM enh
x_comp_attribute_category IN VARCHAR2,
x_comp_attribute1 IN VARCHAR2,
x_comp_attribute2 IN VARCHAR2,
x_comp_attribute3 IN VARCHAR2,
x_comp_attribute4 IN VARCHAR2,
x_comp_attribute5 IN VARCHAR2,
x_comp_attribute6 IN VARCHAR2,
x_comp_attribute7 IN VARCHAR2,
x_comp_attribute8 IN VARCHAR2,
x_comp_attribute9 IN VARCHAR2,
x_comp_attribute10 IN VARCHAR2,
x_comp_attribute11 IN VARCHAR2,
x_comp_attribute12 IN VARCHAR2,
x_comp_attribute13 IN VARCHAR2,
x_comp_attribute14 IN VARCHAR2,
x_comp_attribute15 IN VARCHAR2,
x_error_code IN OUT NOCOPY NUMBER,
x_error_msg IN OUT NOCOPY VARCHAR2)
IS
x_progress VARCHAR2(3) := NULL;
e_update_co_prod_details EXCEPTION;
SELECT bcp.co_product_id,
bcp.component_sequence_id,
bcp.split
FROM wsm_co_products bcp
WHERE bcp.co_product_group_id = x_co_product_group_id
AND bcp.co_product_id is NOT NULL;
raise e_update_co_prod_details;
UPDATE wsm_co_products
SET effectivity_date = x_effectivity_date,
disable_date = x_disable_date,
usage_rate = x_usage_rate,
duality_flag = x_duality_flag
WHERE co_product_group_id = x_co_product_group_id;
WHEN e_update_co_prod_details THEN
x_error_code := 1;
fnd_message.set_token('OBJECT_NAME', 'WSMPPCPD.update_co_prod_details');
x_error_msg := x_error_msg || ' - ' || 'WSMPPCPD.update_co_prod_details('||x_progress||')';
x_error_msg := 'WSMPPCPD.update_co_prod_details(' || x_progress || ')' || ' - ' || substr(sqlerrm, 1, 200);
END update_co_prod_details;
CURSOR C IS SELECT *
FROM bom_bill_of_materials
WHERE bill_sequence_id = x_bill_sequence_id
FOR UPDATE OF bill_sequence_id NOWAIT;
CURSOR C IS SELECT *
FROM bom_inventory_components
WHERE bill_sequence_id = x_component_sequence_id
FOR UPDATE OF component_sequence_id NOWAIT;
SELECT value
INTO l_full_path
FROM v$parameter
WHERE name = 'utl_file_dir';
SELECT trim(substr(l_full_path, 1, decode(instr(l_full_path,',')-1,
-1, length(l_full_path),
instr(l_full_path, ',')-1
)
)
)
INTO l_file_dir
FROM dual;
** updated for the common bill sequence Id do exist
** in BOM as well and hence it is combined with the
** bom_bill_of_materials table.
** - Bala Balakumar, June 23rd, 2000.
*/
CURSOR C is
SELECT co_product_id, wcp.alternate_designator
FROM bom_bill_of_materials bbom,
wsm_co_products wcp
WHERE wcp.co_product_group_id = p_co_product_group_id
AND wcp.co_product_id IS NOT NULL
AND NVL(wcp.primary_flag, 'N') <> 'Y'
AND bbom.assembly_item_id = wcp.co_product_id
AND bbom.organization_id = p_organization_id
/*coprod enh p2 .45*/
--AND nvl(bbom.alternate_bom_designator, '$%&') = nvl(p_alternate_designator, '$%&')
AND nvl(bbom.alternate_bom_designator, '$%&') = nvl(wcp.alternate_designator, '$%&')
AND nvl(wcp.alternate_designator, '$%&') = (select nvl(wcp1.alternate_designator, '$%&')
from wsm_co_products wcp1
where wcp1.co_product_group_id=p_co_product_group_id
and wcp1.primary_flag='Y')
/*end coprod enh p2 .45*/
AND bbom.common_bill_sequence_id = wcp.bill_sequence_id;
/* This procedure uses the BOM BO API to update the Bill headers
of the Secondary Co-Products to set the Common Bill Reference. */
x_progress := '010';
SELECT co_product_id
INTO l_prim_co_prod_id
FROM wsm_co_products
WHERE co_product_group_id = p_co_product_group_id
AND co_product_id IS NOT NULL
AND nvl(primary_flag, 'N') = 'Y';
SELECT substr(concatenated_segments, 1, 80)
INTO l_prim_co_prod_name
FROM mtl_system_items_kfv
WHERE inventory_item_id = l_prim_co_prod_id
AND organization_id = p_organization_id;
SELECT substr(concatenated_segments, 1, 80)
INTO l_co_product_name
FROM mtl_system_items_kfv
WHERE inventory_item_id = c_rec.co_product_id
AND organization_id = p_organization_id;
g_bom_header_rec.Transaction_Type := BOM_Globals.G_OPR_UPDATE;