CREATE OR REPLACE PACKAGE xx_bom_import_pkg
AS
/*
-------------------------------------------------------------
Package Name : xx_bom_import_pkg
Author's Name : Krishanu Ghosh
Date Written : 23-Feb-2007
RICEW Object id : BOM_CNV_01
Purpose : Package Specification for BOM load
Program Style :
Maintenance History:
Date: Name Remarks
----------- -------------- ------------------
17-Dec-2014 MadhuDhare Initial version
--------------------------------------------------------------
*/
/*-----------------------------------------------------------
Public Constant Declaration Section
------------------------------------------------------------*/
g_program_type xx_emf_message_headers.program_type%TYPE
:= 'CONC_PRGM';
/*-----------------------------------------------------------
Global Variable Declaration Section
-------------------------------------------------------------*/
g_debug_level NUMBER := 50;
-- 50 is default, that is all messages(logged at level 10,20..50)
-- would be visible
g_retention_period NUMBER := 30;
-- This is the number of days for which error records are going
-- to be retained in the EMF tables
g_err_col1_width NUMBER := 10;
g_err_col2_width NUMBER := 40;
g_err_col3_width NUMBER := 10;
g_err_col4_width NUMBER := 20;
g_err_col5_width NUMBER := 20;
g_err_col6_width NUMBER := 0;
g_err_col7_width NUMBER := 0;
g_err_col8_width NUMBER := 0;
-- These 8 should add up to 100 for best display, you can use
-- as many columns as you want, adding up to 100, leaving the rest as 0.
/*-----------------------------------------------------------
Public Procedure/Function Declaration Section
Purpose-Main calling Procedure to Import Item Transaction
-----------------------------------------------------------*/
PROCEDURE main (
p_errbuf OUT VARCHAR2
, p_retcode OUT VARCHAR2
, p_run_mode IN VARCHAR2
);
END xx_bom_import_pkg;
/
-----------------------------------------------------------------------------------------
-- Sequence Name : XX_BOM_IMPORT_STG_S
-- Author's name : MadhuDhare
-- Date written : 22-02-2014
-- RICEW Object id : BOM_CNV_01
-- Description : Sequence Script for record id of Staging Table
-- Program Style :
--
-- Maintenance History :
--
-- Date Issue# Name Remarks
-- ----------- -------- ---------------------- ------------------------------------------
-- 23-02-2014 Madhu Dhare Initial version
------------------------------------------------------------------------------------------
DROP SEQUENCE xx_bom_import_stg_s
/
CREATE SEQUENCE xx_bom_import_stg_s
START WITH 1
INCREMENT BY 1
/
AS
/*
-------------------------------------------------------------
Package Name : xx_bom_import_pkg
Author's Name : Krishanu Ghosh
Date Written : 23-Feb-2007
RICEW Object id : BOM_CNV_01
Purpose : Package Specification for BOM load
Program Style :
Maintenance History:
Date: Name Remarks
----------- -------------- ------------------
17-Dec-2014 MadhuDhare Initial version
--------------------------------------------------------------
*/
/*-----------------------------------------------------------
Public Constant Declaration Section
------------------------------------------------------------*/
g_program_type xx_emf_message_headers.program_type%TYPE
:= 'CONC_PRGM';
/*-----------------------------------------------------------
Global Variable Declaration Section
-------------------------------------------------------------*/
g_debug_level NUMBER := 50;
-- 50 is default, that is all messages(logged at level 10,20..50)
-- would be visible
g_retention_period NUMBER := 30;
-- This is the number of days for which error records are going
-- to be retained in the EMF tables
g_err_col1_width NUMBER := 10;
g_err_col2_width NUMBER := 40;
g_err_col3_width NUMBER := 10;
g_err_col4_width NUMBER := 20;
g_err_col5_width NUMBER := 20;
g_err_col6_width NUMBER := 0;
g_err_col7_width NUMBER := 0;
g_err_col8_width NUMBER := 0;
-- These 8 should add up to 100 for best display, you can use
-- as many columns as you want, adding up to 100, leaving the rest as 0.
/*-----------------------------------------------------------
Public Procedure/Function Declaration Section
Purpose-Main calling Procedure to Import Item Transaction
-----------------------------------------------------------*/
PROCEDURE main (
p_errbuf OUT VARCHAR2
, p_retcode OUT VARCHAR2
, p_run_mode IN VARCHAR2
);
END xx_bom_import_pkg;
/
-----------------------------------------------------------------------------------------
-- Sequence Name : XX_BOM_IMPORT_STG_S
-- Author's name : MadhuDhare
-- Date written : 22-02-2014
-- RICEW Object id : BOM_CNV_01
-- Description : Sequence Script for record id of Staging Table
-- Program Style :
--
-- Maintenance History :
--
-- Date Issue# Name Remarks
-- ----------- -------- ---------------------- ------------------------------------------
-- 23-02-2014 Madhu Dhare Initial version
------------------------------------------------------------------------------------------
DROP SEQUENCE xx_bom_import_stg_s
/
CREATE SEQUENCE xx_bom_import_stg_s
START WITH 1
INCREMENT BY 1
/
-------------------------------------------------------------------------------------------
-- Table Name : XX_BOM_IMPORT_STG
-- Author's name :Madhu Dhare
-- Date written : 23-02-2014
-- RICEW Object id : BOM_CNV_01
-- Description : Table creation Script for staging table
-- Program Style :
--
-- Maintenance History :
--
-- Date Issue# Name Remarks
-- ----------- -------- ---------------------- ------------------------------------------
-- 23-02-2014 MadhuDhare Initial version
------------------------------------------------------------------------------------------
DROP TABLE xx_bom_import_stg;
CREATE TABLE xx_bom_import_stg (
status VARCHAR2(2)
, record_id NUMBER PRIMARY KEY
, rec_type VARCHAR2(3)
, record_count NUMBER
, org_code VARCHAR2 (3)
, assembly_item_number VARCHAR2 (40)
, revision VARCHAR2 (3)
, component_item_number VARCHAR2 (40)
, component_quantity NUMBER
, effectivity_date DATE
, assembly_type NUMBER
, process_flag NUMBER
, transaction_type NUMBER
, supply_subinventory VARCHAR2(10)
, supply_locator_id NUMBER
, location_name VARCHAR2(100)
, operation_seq_num NUMBER
, wip_supply_type NUMBER
, planning_factor NUMBER
, optional NUMBER
, mutually_exclusive_options NUMBER
, include_on_ship_docs NUMBER
, last_update_date DATE
, last_updated_by NUMBER
, creation_date DATE
, created_by NUMBER
, last_update_login NUMBER
);
-------------------------------------------------------------------------------------------
-- Table Name : XX_BOM_BILL_OF_MTLS_PREINT
-- Author's name : Madhu Dhare
-- Date written : 23-02-2014
-- RICEW Object id : BOM_CNV_01
-- Description : Table creation Script for BOM Pre interface Header table
-- Program Style :
--
-- Maintenance History :
--
-- Date Issue# Name Remarks
-- ----------- -------- ---------------------- ------------------------------------------
-- 23-02-2014 Madhu Dhare Initial version
------------------------------------------------------------------------------------------
DROP TABLE xx_bom_bill_of_mtls_preint;
CREATE TABLE xx_bom_bill_of_mtls_preint
(
status VARCHAR2(2),
bom_preint_id NUMBER PRIMARY KEY,
assembly_item_id NUMBER,
organization_id NUMBER,
alternate_bom_designator VARCHAR2(10),
last_update_date DATE,
last_updated_by NUMBER,
creation_date DATE,
created_by NUMBER,
last_update_login NUMBER,
common_assembly_item_id NUMBER,
specific_assembly_comment VARCHAR2(240),
pending_from_ecn VARCHAR2(10),
attribute_category VARCHAR2(30),
attribute1 VARCHAR2(150),
attribute2 VARCHAR2(150),
attribute3 VARCHAR2(150),
attribute4 VARCHAR2(150),
attribute5 VARCHAR2(150),
attribute6 VARCHAR2(150),
attribute7 VARCHAR2(150),
attribute8 VARCHAR2(150),
attribute9 VARCHAR2(150),
attribute10 VARCHAR2(150),
attribute11 VARCHAR2(150),
attribute12 VARCHAR2(150),
attribute13 VARCHAR2(150),
attribute14 VARCHAR2(150),
attribute15 VARCHAR2(150),
assembly_type NUMBER,
common_bill_sequence_id NUMBER,
bill_sequence_id NUMBER,
request_id NUMBER,
program_application_id NUMBER,
program_id NUMBER,
program_update_date DATE,
demand_source_line VARCHAR2(30),
set_id VARCHAR2(10),
common_organization_id NUMBER,
demand_source_type NUMBER,
demand_source_header_id NUMBER,
transaction_id NUMBER,
process_flag NUMBER,
organization_code VARCHAR2(3),
common_org_code VARCHAR2(3),
item_number VARCHAR2(240),
common_item_number VARCHAR2(240),
next_explode_date DATE,
revision VARCHAR2(3),
transaction_type VARCHAR2(10),
delete_group_name VARCHAR2(10),
dg_description VARCHAR2(240),
original_system_reference VARCHAR2(50),
implementation_date DATE
);
-------------------------------------------------------------------------------------------
-- Table Name : XX_BOM_INVENTORY_COMPS_PREINT
-- Author's name : Krishanu Ghosh
-- Date written : 23-02-2014
-- RICEW Object id : BOM_CNV_01
-- Description : Table creation Script for BOM Pre interface component details table
-- Program Style :
--
-- Maintenance History :
--
-- Date Issue# Name Remarks
-- ----------- -------- ---------------------- ------------------------------------------
-- 23-02-2014 Madhu Dhare Initial version
------------------------------------------------------------------------------------------
DROP TABLE xx_bom_inv_comps_preint;
CREATE TABLE xx_bom_inv_comps_preint
(
status VARCHAR2(2),
bom_preint_id NUMBER,
operation_seq_num NUMBER,
component_item_id NUMBER,
last_update_date DATE,
last_updated_by NUMBER,
creation_date DATE,
created_by NUMBER,
last_update_login NUMBER,
item_num NUMBER,
component_quantity NUMBER,
component_yield_factor NUMBER,
component_remarks VARCHAR2(240),
effectivity_date DATE,
change_notice VARCHAR2(10),
implementation_date DATE,
disable_date DATE,
attribute_category VARCHAR2(30),
attribute1 VARCHAR2(150),
attribute2 VARCHAR2(150),
attribute3 VARCHAR2(150),
attribute4 VARCHAR2(150),
attribute5 VARCHAR2(150),
attribute6 VARCHAR2(150),
attribute7 VARCHAR2(150),
attribute8 VARCHAR2(150),
attribute9 VARCHAR2(150),
attribute10 VARCHAR2(150),
attribute11 VARCHAR2(150),
attribute12 VARCHAR2(150),
attribute13 VARCHAR2(150),
attribute14 VARCHAR2(150),
attribute15 VARCHAR2(150),
planning_factor NUMBER,
quantity_related NUMBER,
so_basis NUMBER,
optional NUMBER,
mutually_exclusive_options NUMBER,
include_in_cost_rollup NUMBER,
check_atp NUMBER,
shipping_allowed NUMBER,
required_to_ship NUMBER,
required_for_revenue NUMBER,
include_on_ship_docs NUMBER,
low_quantity NUMBER,
high_quantity NUMBER,
acd_type NUMBER,
old_component_sequence_id NUMBER,
component_sequence_id NUMBER,
bill_sequence_id NUMBER,
request_id NUMBER,
program_application_id NUMBER,
program_id NUMBER,
program_update_date DATE,
wip_supply_type NUMBER,
supply_subinventory VARCHAR2(10),
supply_locator_id NUMBER,
revised_item_sequence_id NUMBER,
model_comp_seq_id NUMBER,
assembly_item_id NUMBER,
alternate_bom_designator VARCHAR2(10),
organization_id NUMBER,
organization_code VARCHAR2(3),
component_item_number VARCHAR2(240),
assembly_item_number VARCHAR2(240),
revised_item_number VARCHAR2(240),
location_name VARCHAR2(81),
reference_designator VARCHAR2(15),
substitute_comp_id NUMBER,
substitute_comp_number VARCHAR2(240),
transaction_id NUMBER,
process_flag NUMBER,
bom_item_type NUMBER,
operation_lead_time_percent NUMBER,
cost_factor NUMBER,
include_on_bill_docs NUMBER,
pick_components NUMBER,
ddf_context1 VARCHAR2(30),
ddf_context2 VARCHAR2(30),
new_operation_seq_num NUMBER,
old_operation_seq_num NUMBER,
new_effectivity_date DATE,
old_effectivity_date DATE,
assembly_type NUMBER,
interface_entity_type VARCHAR2(4),
transaction_type VARCHAR2(10),
bom_inventory_comps_ifce_key VARCHAR2(30),
eng_revised_items_ifce_key VARCHAR2(30),
eng_changes_ifce_key VARCHAR2(30),
to_end_item_unit_number VARCHAR2(30),
from_end_item_unit_number VARCHAR2(30),
new_from_end_item_unit_number VARCHAR2(30),
delete_group_name VARCHAR2(10),
dg_description VARCHAR2(240),
original_system_reference VARCHAR2(50),
enforce_int_requirements VARCHAR2(80),
optional_on_model NUMBER,
parent_bill_seq_id NUMBER,
plan_level NUMBER,
auto_request_material VARCHAR2(1),
suggested_vendor_name VARCHAR2(240),
unit_price NUMBER,
new_revised_item_revision VARCHAR2(3)
);
No comments :
Post a Comment