/*******************************************************************************
* TYPE : BI Publisher Report *
* NAME : XXINV_INT150_ITEM_VARIANT_DTL_DS *
* *
* DESCRIPTION : Procedure to create item variants based on the input data *
* *
* DEVELOPMENT AND MAINTENANCE HISTORY *
* *
* DATE VER MODIFIED BY DESCRIPTION *
* ----------- ----- --------------------- --------------------------------- *
* 11-FEB-2022 1.0 v-harith.g.r Initial version. *
* *
*******************************************************************************/
DECLARE
TYPE t_refcursor IS REF CURSOR;
xdo_cursor t_refcursor;
-- Record type for input cursor
TYPE trec_ref_uni_flg IS RECORD (
RecId VARCHAR2(100),
ItemNumber VARCHAR2(100),
VariantCode VARCHAR2(100),
PackValue VARCHAR2(100),
Stk1 VARCHAR2(100),
Stk2 VARCHAR2(100),
Stk3 VARCHAR2(100),
Stk4 VARCHAR2(100),
Stk5 VARCHAR2(100)
);
TYPE ttab_ref_uni_flg IS
TABLE OF trec_ref_uni_flg INDEX BY BINARY_INTEGER;
ltab_ref_uni_flg ttab_ref_uni_flg;
-- Record type for Item details cursor
TYPE trec_itm_dtl IS RECORD (
BackToBackEnabled egp_system_items_b.Back_To_Back_Enabled%TYPE,
version egp_item_revisions_b.object_version_number%TYPE,
ItemClassName egp_item_classes_tl.Item_Class_Name%TYPE,
Revision egp_item_revisions_b.Revision%TYPE,
lengthI egp_item_relationships_b.attribute_number1%TYPE,
widthI egp_item_relationships_b.attribute_number2%TYPE,
heightI egp_item_relationships_b.attribute_number3%TYPE,
weightI egp_item_relationships_b.attribute_number5%TYPE,
lengthM egp_item_relationships_b.attribute_number1%TYPE,
widthM egp_item_relationships_b.attribute_number2%TYPE,
heightM egp_item_relationships_b.attribute_number3%TYPE,
weightM egp_item_relationships_b.attribute_number5%TYPE,
lengthP egp_item_relationships_b.attribute_number1%TYPE,
widthP egp_item_relationships_b.attribute_number2%TYPE,
heightP egp_item_relationships_b.attribute_number3%TYPE,
weightP egp_item_relationships_b.attribute_number5%TYPE,
IQTY fnd_lookup_values.ATTRIBUTE4%TYPE,
MQTY fnd_lookup_values.ATTRIBUTE5%TYPE,
PQTY fnd_lookup_values.ATTRIBUTE6%TYPE,
inventory_item_id egp_system_items_b.inventory_item_id%TYPE,
organization_id egp_system_items_b.organization_id%TYPE
);
TYPE ttab_itm_dtl IS
TABLE OF trec_itm_dtl INDEX BY BINARY_INTEGER;
ltab_itm_dtl ttab_itm_dtl;
-- Record type for billOrg dtl cursor
TYPE trec_billorgdtl IS RECORD (
common_bill_sequence_id egp_structures_b.common_bill_sequence_id%TYPE,
organization_id inv_org_parameters.organization_id%TYPE
);
TYPE ttab_billorgdtl IS
TABLE OF trec_billorgdtl INDEX BY BINARY_INTEGER;
ltab_billorgdtl ttab_billorgdtl;
-- Record type for Component detail cusor
TYPE trec_comp_dtl IS RECORD (
component egp_system_items_b.Item_Number%TYPE,
quantity egp_components_b.component_quantity%TYPE
);
TYPE ttab_comp_dtl IS
TABLE OF trec_comp_dtl INDEX BY BINARY_INTEGER;
ltab_comp_dtl ttab_comp_dtl;
-- Record type for billOrg dtl cursor to check for assort
TYPE trec_assortOfassort IS RECORD (
common_bill_sequence_id_as egp_structures_b.common_bill_sequence_id%TYPE,
organization_id_as inv_org_parameters.organization_id%TYPE
);
TYPE ttab_assortOfassort IS
TABLE OF trec_assortOfassort INDEX BY BINARY_INTEGER;
ltab_assortOfassort ttab_assortOfassort;
-- table type for duplicate sticker validtion
TYPE ttab_stk_dup IS TABLE OF VARCHAR2(500);
ttab_orig_stk ttab_stk_dup;
ttab_tmp_stk ttab_stk_dup;
l_loops NUMBER := 0;
l_data_clob CLOB;
l_data_tmp_clob CLOB;
l_itm_clob CLOB;
l_comp_clob CLOB;
l_bill_clob CLOB;
li_limit_in BINARY_INTEGER := 10000;
v_iserial VARCHAR2(30);
v_mserial VARCHAR2(30);
v_pserial VARCHAR2(30);
v_dev_entity VARCHAR2(10);
cnt NUMBER := 0;
msg1 VARCHAR(1000);
msg2 VARCHAR(1000);
msg3 VARCHAR(1000);
msg4 VARCHAR(1000);
msg5 VARCHAR(1000);
msg6 VARCHAR(1000);
msg7 VARCHAR(1000);
msg8 VARCHAR(1000);
msg9 VARCHAR(1000);
v_count NUMBER := 0;
-- Cursor to split the input based on '~' and uses ',' as the ending parameter --
-- Example of an input: 123~58649~HTC~2PK~COMP-00001~PRICE-00001~PKLPN-00001~, --
-- Multiple comma spearated inputs are aso handled by this cursor --
CURSOR cur_input_dtl IS
SELECT
rtrim(regexp_substr(splited_string, '[^~]*~', 1, 1), '~') RecId,
rtrim(regexp_substr(splited_string, '[^~]*~', 1, 2), '~') ItemNumber,
rtrim(regexp_substr(splited_string, '[^~]*~', 1, 3), '~') VariantCode,
rtrim(regexp_substr(splited_string, '[^~]*~', 1, 4), '~') PackValue,
rtrim(regexp_substr(splited_string, '[^~]*~', 1, 5), '~') Stk1,
rtrim(regexp_substr(splited_string, '[^~]*~', 1, 6), '~') Stk2,
rtrim(regexp_substr(splited_string, '[^~]*~', 1, 7), '~') Stk3,
rtrim(regexp_substr(splited_string, '[^~]*~', 1, 8), '~') Stk4,
rtrim(regexp_substr(splited_string, '[^~]*~', 1, 9), '~') Stk5
FROM
(
SELECT
regexp_substr(:p_input_dtl, '[^,]+', 1, appear_num) AS splited_string
FROM dual
CROSS JOIN (
SELECT ROWNUM AS appear_num
FROM
(
SELECT
LENGTH(regexp_replace(:p_input_dtl, '[^,]+')) AS str_len
FROM dual
)
CONNECT BY
level <= str_len
)
);
-- Cursor to fetch Base item details along with SGTIN values for ---
-- Dimension values for Inner, Master and Palltet ---
CURSOR cur_getitmdtl (
p_base_item_id IN VARCHAR,
p_packvalue IN VARCHAR,
p_variant_code IN VARCHAR
) IS
SELECT DISTINCT
esib.back_to_back_enabled,
eirb.object_version_number version,
eict.item_class_name,
eirb.Revision,
eirbI.attribute_number1 lengthI,
eirbI.attribute_number2 widthI,
eirbI.attribute_number3 heightI,
eirbI.attribute_number5 weightI,
eirbM.attribute_number1 lengthM,
eirbM.attribute_number2 widthM,
eirbM.attribute_number3 heightM,
eirbM.attribute_number5 weightM,
eirbP.attribute_number1 lengthP,
eirbP.attribute_number2 widthP,
eirbP.attribute_number3 heightP,
eirbP.attribute_number5 weightP,
flv.attribute4 IQTY,
flv.attribute5 MQTY,
flv.attribute6 PQTY,
esib.inventory_item_id,
esib.organization_id
FROM egp_system_items_b esib,
egp_item_classes_tl eict,
egp_item_revisions_b eirb,
--egp_item_status_tl eist,- status code
inv_org_parameters iop,
egp_item_relationships_b eirbI,
egp_item_relationships_b eirbP,
egp_item_relationships_b eirbM,
inv_units_of_measure_b iumbI,
inv_units_of_measure_b iumbP,
inv_units_of_measure_b iumbM,
egp_item_categories eic,
egp_categories_tl ect,
egp_category_sets_tl ecst,
fnd_lookup_values flv
WHERE eict.item_class_id = esib.item_catalog_group_id
AND esib.inventory_item_id = eirb.inventory_item_id
AND iop.organization_id = esib.organization_id
AND iop.master_organization_id = iop.organization_id
AND iop.organization_code = 'FNK_ITM'
AND eirbI.attribute_category = 'GTIN'
AND eirbI.uom_code = iumbI.uom_code
AND iumbI.attribute1 = 'INR'
AND eirbI.inventory_item_id = esib.inventory_item_id
AND eirbP.attribute_category = 'GTIN'
AND eirbP.uom_code = iumbP.uom_code
AND iumbP.attribute1 = 'PAL'
AND eirbP.inventory_item_id = esib.inventory_item_id
AND eirbM.attribute_category = 'GTIN'
AND eirbM.uom_code = iumbM.uom_code
AND iumbM.attribute1 = 'MSTR'
AND eirbM.inventory_item_id = esib.inventory_item_id
AND eic.inventory_item_id = esib.inventory_item_id
AND eic.organization_id = esib.organization_id
AND eic.category_id = ect.category_id
AND eic.category_set_id = ecst.category_set_id
AND ecst.category_set_name = 'Funko Purchasing Catalog'
AND eict.item_class_name = 'Funko Item Class'
AND esib.item_type = 'P' --P meaning purchased item (can fetch from lookup)
AND esib.approval_status = 'A' -- A meaning Approved
AND esib.inventory_item_status_code = 'Active'
AND ecst.LANGUAGE ='US'
AND ect.LANGUAGE ='US'
AND flv.LANGUAGE ='US'
AND flv.lookup_type = 'FNK_ITEM_VARIANT_SGTIN_SPECS'
AND flv.attribute2 = ect.category_name -- product line validation
AND flv.attribute3 = p_packvalue
AND flv.attribute1 = p_variant_code
AND esib.item_number = p_base_item_id;
-- cursor to fetch common_bill_sequence_id & organization_id --
-- which is used to fetch componenet details --
CURSOR cur_billorgdtl (
p_base_item_id IN VARCHAR
) IS
SELECT DISTINCT
esb.common_bill_sequence_id,
iop.organization_id
FROM
egp_structures_b esb,
egp_system_items_b esib,
inv_org_parameters iop
WHERE esib.inventory_item_id = to_number(esb.pk1_value)
AND esib.organization_id = to_number(esb.pk2_value)
AND esib.inventory_item_status_code <> 'Inactive'
AND iop.organization_id = esib.organization_id
AND iop.master_organization_id = iop.organization_id
AND esib.item_number = p_base_item_id;
-- Cursor to fetch component details --
-- based on common_bill_sequence_id & organization_id as inputs --
CURSOR curcompdtl (
p_bill_seq_id IN VARCHAR2,
p_org_id IN VARCHAR2
) IS
SELECT DISTINCT
esib_c.item_number component,
ecb.component_quantity quantity
FROM egp_components_b ecb,
egp_system_items_b esib_c
WHERE esib_c.inventory_item_id = to_number(ecb.pk1_value)
AND esib_c.organization_id = to_number(ecb.pk2_value)
AND esib_c.inventory_item_status_code <> 'Inactive'
AND ecb.bill_sequence_id = to_number(p_bill_seq_id) --300000164254781
AND esib_c.organization_id = p_org_id; --300000003749603
-- Cursor to fetch bill_sequence_id and org_id to verify for an Assor item number
CURSOR cur_assortOfassort (
p_base_item_id IN VARCHAR
) IS
SELECT DISTINCT
esb.common_bill_sequence_id,
iop.organization_id
FROM
egp_structures_b esb,
egp_system_items_b esib,
inv_org_parameters iop
WHERE esib.inventory_item_id = to_number(esb.pk1_value)
AND esib.organization_id = to_number(esb.pk2_value)
AND esib.inventory_item_status_code <> 'Inactive'
AND iop.organization_id = esib.organization_id
AND iop.master_organization_id = iop.organization_id
AND esib.item_number = p_base_item_id;
BEGIN
l_data_clob := empty_clob();
l_data_tmp_clob := empty_clob();
l_itm_clob := empty_clob();
l_comp_clob := empty_clob();
l_bill_clob := empty_clob();
--Open to fetch details from input cursor
OPEN cur_input_dtl;
FETCH cur_input_dtl
BULK COLLECT INTO ltab_ref_uni_flg
LIMIT li_limit_in;
CLOSE cur_input_dtl;
-- If input is empty, flush the clobs and go to next input
IF ltab_ref_uni_flg.count = 0 THEN
l_data_clob := empty_clob();
l_data_tmp_clob := empty_clob();
-- If input has data then validate the input
ELSE
FOR indx IN ltab_ref_uni_flg.FIRST..ltab_ref_uni_flg.LAST
LOOP
v_count := 0;
cnt :=0;
msg1 := NULL;
msg2 := NULL;
msg3 := NULL;
msg4 := NULL;
msg5 := NULL;
msg6 := NULL;
msg7 := NULL;
msg8 := NULL;
msg9 := NULL;
--Error validations--
--Check if base item is present in ERP
SELECT COUNT(*)
INTO v_count
FROM egp_system_items_b
WHERE item_number = ltab_ref_uni_flg(indx).ItemNumber;
--Only if item number is present in ERP do other validations
IF (v_count) > 0 THEN
v_count := 0;
-- check if baseitem-variantcode combination is already present in ERP
SELECT COUNT(*)
INTO v_count
FROM egp_system_items_b
WHERE item_number = CONCAT(CONCAT(ltab_ref_uni_flg(indx).ItemNumber, '-'), ltab_ref_uni_flg(indx).VariantCode);
IF (v_count) > 0
THEN
msg2 := ' baseitem-variantcode '||ltab_ref_uni_flg(indx).ItemNumber|| '-' ||ltab_ref_uni_flg(indx).VariantCode||' combination is already present in ERP,';
cnt := cnt+1;
END IF;
v_count := 0;
--Check if user type is not "Purchased Item"
SELECT COUNT(*)
INTO v_count
FROM egp_system_items_b
WHERE item_type ='P'
AND item_number = ltab_ref_uni_flg(indx).ItemNumber;
IF (v_count) = 0
THEN
msg3 := ' baseitem- '||ltab_ref_uni_flg(indx).ItemNumber||' user type is not "Purchased Item", ';
cnt := cnt+1;
END IF;
v_count := 0;
--Check if baseitem is not approved and active
SELECT COUNT(*)
INTO v_count
FROM egp_system_items_b
WHERE approval_status = 'A'
AND inventory_item_status_code = 'Active'
AND item_number = ltab_ref_uni_flg(indx).ItemNumber;
IF(v_count) = 0
THEN
msg4 := ' baseitem '||ltab_ref_uni_flg(indx).ItemNumber||' is not approved and active, ';
cnt := cnt+1;
END IF;
v_count := 0;
--Check for pack value variantcode & product line
SELECT COUNT(esib.item_number)
INTO v_count
FROM egp_system_items_b esib,
egp_item_categories eic,
egp_categories_tl ect,
fnd_lookup_values flv
WHERE eic.inventory_item_id = esib.inventory_item_id
AND eic.organization_id = esib.organization_id
AND eic.category_id = ect.category_id
AND flv.LANGUAGE ='US'
AND flv.lookup_type = 'FNK_ITEM_VARIANT_SGTIN_SPECS'
AND flv.attribute2 = ect.category_name -- product line validation
AND flv.attribute3 = ltab_ref_uni_flg(indx).PackValue
AND flv.attribute1 = ltab_ref_uni_flg(indx).VariantCode
AND esib.item_number = ltab_ref_uni_flg(indx).ItemNumber;
IF(v_count) = 0 THEN
msg5 := 'The combination of packvalue- '||ltab_ref_uni_flg(indx).PackValue||',variantcode- '||ltab_ref_uni_flg(indx).VariantCode||' and productLine is incorrect';
msg8 := ' or the provided input packvalue and variant code is invalid, please ensure that all the entered values are in uppercase only ';
cnt := cnt+1;
END IF;
v_count := 0;
--Check if baseitems class is not "Funko Item Class"
SELECT COUNT(esib.item_number)
INTO v_count
FROM egp_system_items_b esib,
egp_item_classes_tl eict
WHERE eict.item_class_id = esib.item_catalog_group_id
AND eict.item_class_name = 'Funko Item Class'
AND esib.item_number = ltab_ref_uni_flg(indx).ItemNumber;
IF(v_count) = 0 THEN
msg6 := ' baseitem '||ltab_ref_uni_flg(indx).ItemNumber||' class is not "Funko Item Class", ';
cnt := cnt+1;
END IF;
v_count := 0;
-- to check if stickers are present in ERP
-- sys.dbms_debug_vc2coll is a inbulit collection type function
-- Used to store values that can be iterated (similar to Array)
-- this allows us to use a single IF condition for all 5 stickers
--IF the stickers are given in input
IF LENGTH (ltab_ref_uni_flg(indx).Stk1) > 0 THEN
FOR i IN (SELECT column_value
FROM TABLE(
sys.dbms_debug_vc2coll(ltab_ref_uni_flg(indx).Stk1,
ltab_ref_uni_flg(indx).Stk2,
ltab_ref_uni_flg(indx).Stk3,
ltab_ref_uni_flg(indx).Stk4,
ltab_ref_uni_flg(indx).Stk5))) LOOP
-- If sticker is not empty/null
IF LENGTH (i.column_value) > 0 THEN
SELECT COUNT(*)
INTO v_count
FROM egp_system_items_b
WHERE item_number = i.column_value;
-- If sticker not present
IF (v_count) = 0 THEN
msg7 := msg7 || (' sticker ' || i.column_value || 'is not present in ERP, please ensure that all the entered values are in uppercase only ');
cnt := cnt+1;
END IF;
END IF;
END LOOP;
--If no stickers are provided in input
ELSE
msg7 := 'No sticker input is provided, ';
cnt := cnt+1;
END IF;
--For duplicate stickers --
--PL-SQL's built-in operator "set" is used to create a set of unique values --
--Another set operator "except" to fetch the duplicate values --
-- Append all values into 'ttab_orig_stk' table type --
-- Append unique values obtained from SET(ttab_orig_stk)into ttab_tmp_stk --
IF LENGTH(ltab_ref_uni_flg(indx).Stk1) > 0 THEN
ttab_orig_stk := ttab_stk_dup(ltab_ref_uni_flg(indx).Stk1,
ltab_ref_uni_flg(indx).Stk2,
ltab_ref_uni_flg(indx).Stk3,
ltab_ref_uni_flg(indx).Stk4,
ltab_ref_uni_flg(indx).Stk5
);
ttab_tmp_stk := SET(ttab_orig_stk);
-- if ttab_orig_stk and ttab_orig_stk are not equal
-- then there are duplicates
-- If not equal then the Input stickers have duplicate values
-- SET MULTISET & EXPECT in combination
-- is used to fetch the duplicate values
IF (ttab_tmp_stk.COUNT <> ttab_orig_stk.COUNT) THEN
ttab_tmp_stk := SET(ttab_orig_stk MULTISET EXCEPT ttab_tmp_stk);
FOR i IN ttab_tmp_stk.FIRST..ttab_tmp_stk.LAST
LOOP
IF(LENGTH(ttab_tmp_stk(i)) > 0) THEN
msg9:= msg9 ||' , '||(ttab_tmp_stk(i)|| ' is a duplicate sticker ');
cnt := cnt+1;
END IF;
END LOOP;
END IF;
END IF;
-- If base item is not present in ERP
ELSE
msg1 := ' baseitem '||ltab_ref_uni_flg(indx).ItemNumber|| ' is not present in ERP ';
cnt := cnt+1;
IF LENGTH(ltab_ref_uni_flg(indx).Stk1) > 0 THEN
ttab_orig_stk := ttab_stk_dup(ltab_ref_uni_flg(indx).Stk1,
ltab_ref_uni_flg(indx).Stk2,
ltab_ref_uni_flg(indx).Stk3,
ltab_ref_uni_flg(indx).Stk4,
ltab_ref_uni_flg(indx).Stk5
);
ttab_tmp_stk := SET(ttab_orig_stk);
IF (ttab_tmp_stk.COUNT <> ttab_orig_stk.COUNT) THEN
ttab_tmp_stk := SET(ttab_orig_stk MULTISET EXCEPT ttab_tmp_stk);
--FOR i IN 1 .. ttab_tmp_stk.COUNT
FOR i IN ttab_tmp_stk.FIRST..ttab_tmp_stk.LAST
LOOP
IF(LENGTH(ttab_tmp_stk(i)) > 0) THEN
msg9:= msg9 ||(ttab_tmp_stk(i)|| ' is a duplicate sticker, ');
cnt := cnt+1;
END IF;
END LOOP;
END IF;
END IF;
END IF;
-- if any validation fails
IF cnt > 0 THEN
l_data_clob := l_data_clob
|| '<item_list>'
|| '<RecId>'
|| ltab_ref_uni_flg(indx).RecId
|| '</RecId>'
|| '<VariantItem>'
|| CONCAT(CONCAT(ltab_ref_uni_flg(indx).ItemNumber, '-'), ltab_ref_uni_flg(indx).VariantCode)
|| '</VariantItem>'
|| '<ValidRecord>'
|| 'N'
|| '</ValidRecord>'
|| '<Message>'
|| 'Item Variant cannot be created because '
|| msg1
|| msg2
|| msg3
|| msg4
|| msg5
|| msg8
|| msg6
|| msg7
|| msg9
|| '</Message>'
|| '</item_list>';
l_itm_clob := empty_clob();
l_data_tmp_clob := empty_clob();
ELSE
-- to fetch details of values based on ItemNumber from input
OPEN cur_getitmdtl(
p_base_item_id => ltab_ref_uni_flg(indx).ItemNumber,
p_packvalue => ltab_ref_uni_flg(indx).PackValue,
p_variant_code => ltab_ref_uni_flg(indx).VariantCode
);
FETCH cur_getitmdtl
BULK COLLECT INTO ltab_itm_dtl
LIMIT li_limit_in;
CLOSE cur_getitmdtl;
IF ltab_itm_dtl.count = 0 THEN
l_data_clob := l_data_clob
|| '<item_list>'
|| '<RecId>'
|| ltab_ref_uni_flg(indx).RecId
|| '</RecId>'
|| '<VariantItem>'
|| CONCAT(CONCAT(ltab_ref_uni_flg(indx).ItemNumber, '-'), ltab_ref_uni_flg(indx).VariantCode)
|| '</VariantItem>'
|| '<ValidRecord>'
|| 'N'
|| '</ValidRecord>'
|| '<Message>'
|| 'There is some issue with the BaseItem '||ltab_ref_uni_flg(indx).ItemNumber||', please check if the BaseItem satisfies all the required conditions to create Item Variants '
|| '</Message>'
|| '</item_list>';
l_itm_clob := empty_clob();
l_data_tmp_clob := empty_clob();
ELSE
FOR itm IN ltab_itm_dtl.FIRST..ltab_itm_dtl.LAST LOOP
--to get item bill and org id details
OPEN cur_billorgdtl(ltab_ref_uni_flg(indx).ItemNumber);
FETCH cur_billorgdtl
BULK COLLECT INTO ltab_billorgdtl
LIMIT li_limit_in;
CLOSE cur_billorgdtl;
-- 23 digit SGTIN code is stored in attribute1
-- 5 digit URI serial code is stored in
-- lookup type 'FNK_ITEM_VARIANT_SERIAL_CODE'
-- Last 5 digits of the SGTIN code is replaced by URI serial code
-- SUBSTR is used to cut the length of string from 23 to 18
SELECT (SELECT SUBSTR(eir.attribute1,1,18)
FROM egp_item_relationships_b_v eir,
inv_units_of_measure_b ium
WHERE eir.inventory_item_id = ltab_itm_dtl(itm).inventory_item_id
AND eir.item_relationship_type = 'GTIN_XREF'
AND eir.uom_code = ium.uom_code
AND ium.attribute1 = 'INR')
|| (SELECT meaning
FROM fnd_lookup_values
WHERE lookup_type = 'FNK_ITEM_VARIANT_SERIAL_CODE'
AND lookup_code = ltab_ref_uni_flg(indx).VariantCode
AND LANGUAGE = USERENV('LANG'))
INTO v_iserial
FROM dual;
-- Similar operation but for 'Master'
SELECT (SELECT SUBSTR(eir.attribute1,1,18)
FROM egp_item_relationships_b_v eir,
inv_units_of_measure_b ium
WHERE eir.inventory_item_id = ltab_itm_dtl(itm).inventory_item_id
AND eir.item_relationship_type = 'GTIN_XREF'
AND eir.uom_code = ium.uom_code
AND ium.attribute1 = 'MSTR')
|| (SELECT meaning
FROM fnd_lookup_values
WHERE lookup_type = 'FNK_ITEM_VARIANT_SERIAL_CODE'
AND lookup_code = ltab_ref_uni_flg(indx).VariantCode
AND language = USERENV('LANG'))
INTO v_mserial
FROM dual;
-- Similar operation but for 'Master'
SELECT (SELECT SUBSTR(eir.attribute1,1,18)
FROM egp_item_relationships_b_v eir,
inv_units_of_measure_b ium
WHERE eir.inventory_item_id = ltab_itm_dtl(itm).inventory_item_id
AND eir.item_relationship_type = 'GTIN_XREF'
AND eir.uom_code = ium.uom_code
AND ium.attribute1 = 'PAL')
|| (SELECT meaning
FROM fnd_lookup_values
WHERE lookup_type = 'FNK_ITEM_VARIANT_SERIAL_CODE'
AND lookup_code = ltab_ref_uni_flg(indx).VariantCode
AND language = USERENV('LANG'))
INTO v_pserial
FROM dual;
-- To fetch Dev entity values using org_id and Inv_id
-- from getitmdtl cursor
SELECT ecb_pl.attribute1 -- dev entity
INTO v_dev_entity
FROM egp_item_categories eic_pl,
egp_categories_b ecb_pl,
egp_categories_tl ect_pl,
egp_category_sets_b ecsb_pl
WHERE eic_pl.category_id = ecb_pl.category_id
AND TRUNC (SYSDATE) BETWEEN TRUNC (NVL (eic_pl.start_date, SYSDATE - 1))
AND TRUNC (NVL (eic_pl.end_date, SYSDATE + 1))
AND ecb_pl.enabled_flag = 'Y'
AND TRUNC (NVL (ecb_pl.disable_date, SYSDATE + 1)) >= TRUNC (SYSDATE)
AND ect_pl.category_id = ecb_pl.category_id
AND ect_pl.language = USERENV ('LANG')
AND eic_pl.category_set_id = ecsb_pl.category_set_id
AND ecsb_pl.catalog_code = 'Funko_Purchasing_Catalog'
AND TRUNC (SYSDATE) BETWEEN TRUNC (NVL (ecsb_pl.start_date, SYSDATE - 1))
AND TRUNC (NVL (ecsb_pl.end_date, SYSDATE + 1))
AND eic_pl.inventory_item_id = ltab_itm_dtl(itm).inventory_item_id
AND eic_pl.organization_id = ltab_itm_dtl(itm).organization_id;
IF ltab_billorgdtl.count = 0 THEN
l_data_clob := l_data_clob
|| '<item_list>'
|| '<RecId>'
|| ltab_ref_uni_flg(indx).RecId
|| '</RecId>'
|| '<BaseItemNumber>'
|| ltab_ref_uni_flg(indx).ItemNumber
|| '</BaseItemNumber>'
|| '<VariantItem>'
|| CONCAT(CONCAT(ltab_ref_uni_flg(indx).ItemNumber, '-'), ltab_ref_uni_flg(indx).VariantCode)
|| '</VariantItem>'
|| '<ValidRecord>'
|| 'Y'
|| '</ValidRecord>'
|| '<Message>'
|| 'Success'
|| '</Message>'
|| '<packValue>'
|| ltab_ref_uni_flg(indx).PackValue
|| '</packValue>'
|| '<BOM>'
|| '<Stk1>'
|| ltab_ref_uni_flg(indx).Stk1
|| '</Stk1>'
|| '<Stk2>'
|| ltab_ref_uni_flg(indx).Stk2
|| '</Stk2>'
|| '<Stk3>'
|| ltab_ref_uni_flg(indx).Stk3
|| '</Stk3>'
|| '<Stk4>'
|| ltab_ref_uni_flg(indx).Stk4
|| '</Stk4>'
|| '<Stk5>'
|| ltab_ref_uni_flg(indx).Stk5
|| '</Stk5>'
|| '<BaseItemNumber>'
|| ltab_ref_uni_flg(indx).ItemNumber
|| '</BaseItemNumber>'
|| '</BOM>'
|| '<BackToBackEnabled>'
|| ltab_itm_dtl(itm).BackToBackEnabled
|| '</BackToBackEnabled>'
|| '<BaseItemClass>'
|| ltab_itm_dtl(itm).ItemClassName
|| '</BaseItemClass>'
|| '<RevisionValue>'
|| ltab_itm_dtl(itm).Revision
|| '</RevisionValue>'
|| '<VersionValue>'
|| ltab_itm_dtl(itm).version
|| '</VersionValue>'
|| '<InnerWT>'
|| ltab_itm_dtl(itm).weightI
|| '</InnerWT>'
|| '<InnerH>'
|| ltab_itm_dtl(itm).heightI
|| '</InnerH>'
|| '<InnerL>'
|| ltab_itm_dtl(itm).lengthI
|| '</InnerL>'
|| '<InnerWD>'
|| ltab_itm_dtl(itm).widthI
|| '</InnerWD>'
|| '<MasterWT>'
|| ltab_itm_dtl(itm).weightM
|| '</MasterWT>'
|| '<MasterH>'
|| ltab_itm_dtl(itm).heightM
|| '</MasterH>'
|| '<MasterL>'
|| ltab_itm_dtl(itm).lengthM
|| '</MasterL>'
|| '<MasterWD>'
|| ltab_itm_dtl(itm).widthM
|| '</MasterWD>'
|| '<PalleteWT>'
|| ltab_itm_dtl(itm).weightP
|| '</PalleteWT>'
|| '<PalleteH>'
|| ltab_itm_dtl(itm).heightP
|| '</PalleteH>'
|| '<PalleteL>'
|| ltab_itm_dtl(itm).lengthP
|| '</PalleteL>'
|| '<PalleteWD>'
|| ltab_itm_dtl(itm).widthP
|| '</PalleteWD>'
|| '<IQTY>'
|| ltab_itm_dtl(itm).IQTY
|| '</IQTY>'
|| '<MQTY>'
|| ltab_itm_dtl(itm).MQTY
|| '</MQTY>'
|| '<PQTY>'
|| ltab_itm_dtl(itm).PQTY
|| '</PQTY>'
|| '<ISerialCode>'
|| v_iserial
|| '</ISerialCode>'
|| '<PSerialCode>'
|| v_pserial
|| '</PSerialCode>'
|| '<MSerialCode>'
|| v_mserial
|| '</MSerialCode>'
|| '<DevEntity>'
|| v_dev_entity
|| '</DevEntity>'
|| '</item_list>';
l_bill_clob := empty_clob();
l_data_tmp_clob := empty_clob();
ELSE
FOR bill IN ltab_billorgdtl.first..ltab_billorgdtl.last
LOOP
-- use bill and org id details to fetch componenets--
OPEN curcompdtl(
p_bill_seq_id => ltab_billorgdtl(bill).common_bill_sequence_id,
p_org_id => ltab_billorgdtl(bill).organization_id
);
FETCH curcompdtl
BULK COLLECT INTO ltab_comp_dtl
LIMIT li_limit_in;
CLOSE curcompdtl;
-- if no componenet is present
IF ltab_comp_dtl.count = 0 THEN
l_data_clob := l_data_clob
|| '<item_list>'
|| '<RecId>'
|| ltab_ref_uni_flg(indx).RecId
|| '</RecId>'
|| '<BaseItemNumber>'
|| ltab_ref_uni_flg(indx).ItemNumber
|| '</BaseItemNumber>'
|| '<VariantItem>'
|| CONCAT(CONCAT(ltab_ref_uni_flg(indx).ItemNumber, '-'), ltab_ref_uni_flg(indx).VariantCode)
|| '</VariantItem>'
|| '<ValidRecord>'
|| 'Y'
|| '</ValidRecord>'
|| '<Message>'
|| 'Success'
|| '</Message>'
|| '<packValue>'
|| ltab_ref_uni_flg(indx).PackValue
|| '</packValue>'
|| '<BOM>'
|| '<Stk1>'
|| ltab_ref_uni_flg(indx).Stk1
|| '</Stk1>'
|| '<Stk2>'
|| ltab_ref_uni_flg(indx).Stk2
|| '</Stk2>'
|| '<Stk3>'
|| ltab_ref_uni_flg(indx).Stk3
|| '</Stk3>'
|| '<Stk4>'
|| ltab_ref_uni_flg(indx).Stk4
|| '</Stk4>'
|| '<Stk5>'
|| ltab_ref_uni_flg(indx).Stk5
|| '</Stk5>'
|| '<BaseItemNumber>'
|| ltab_ref_uni_flg(indx).ItemNumber
|| '</BaseItemNumber>'
|| '</BOM>'
|| '<BackToBackEnabled>'
|| ltab_itm_dtl(itm).BackToBackEnabled
|| '</BackToBackEnabled>'
|| '<BaseItemClass>'
|| ltab_itm_dtl(itm).ItemClassName
|| '</BaseItemClass>'
|| '<RevisionValue>'
|| ltab_itm_dtl(itm).Revision
|| '</RevisionValue>'
|| '<VersionValue>'
|| ltab_itm_dtl(itm).version
|| '</VersionValue>'
|| '<InnerWT>'
|| ltab_itm_dtl(itm).weightI
|| '</InnerWT>'
|| '<InnerH>'
|| ltab_itm_dtl(itm).heightI
|| '</InnerH>'
|| '<InnerL>'
|| ltab_itm_dtl(itm).lengthI
|| '</InnerL>'
|| '<InnerWD>'
|| ltab_itm_dtl(itm).widthI
|| '</InnerWD>'
|| '<MasterWT>'
|| ltab_itm_dtl(itm).weightM
|| '</MasterWT>'
|| '<MasterH>'
|| ltab_itm_dtl(itm).heightM
|| '</MasterH>'
|| '<MasterL>'
|| ltab_itm_dtl(itm).lengthM
|| '</MasterL>'
|| '<MasterWD>'
|| ltab_itm_dtl(itm).widthM
|| '</MasterWD>'
|| '<PalleteWT>'
|| ltab_itm_dtl(itm).weightP
|| '</PalleteWT>'
|| '<PalleteH>'
|| ltab_itm_dtl(itm).heightP
|| '</PalleteH>'
|| '<PalleteL>'
|| ltab_itm_dtl(itm).lengthP
|| '</PalleteL>'
|| '<PalleteWD>'
|| ltab_itm_dtl(itm).widthP
|| '</PalleteWD>'
|| '<IQTY>'
|| ltab_itm_dtl(itm).IQTY
|| '</IQTY>'
|| '<MQTY>'
|| ltab_itm_dtl(itm).MQTY
|| '</MQTY>'
|| '<PQTY>'
|| ltab_itm_dtl(itm).PQTY
|| '</PQTY>'
|| '<ISerialCode>'
|| v_iserial
|| '</ISerialCode>'
|| '<PSerialCode>'
|| v_pserial
|| '</PSerialCode>'
|| '<MSerialCode>'
|| v_mserial
|| '</MSerialCode>'
|| '<DevEntity>'
|| v_dev_entity
|| '</DevEntity>'
|| '</item_list>';
l_comp_clob := empty_clob();
l_data_tmp_clob := empty_clob();
ELSE
-- Component is present but is not assort of assort
FOR assort IN ltab_comp_dtl.FIRST..ltab_comp_dtl.LAST
LOOP
OPEN cur_assortOfassort(ltab_comp_dtl(assort).component);
FETCH cur_assortOfassort
BULK COLLECT INTO ltab_assortOfassort
LIMIT li_limit_in;
CLOSE cur_assortOfassort;
END LOOP;
IF ltab_assortOfassort.count = 0
THEN
l_data_clob := l_data_clob
|| '<item_list>'
|| '<RecId>'
|| ltab_ref_uni_flg(indx).RecId
|| '</RecId>'
|| '<BaseItemNumber>'
|| ltab_ref_uni_flg(indx).ItemNumber
|| '</BaseItemNumber>'
|| '<VariantItem>'
|| CONCAT(CONCAT(ltab_ref_uni_flg(indx).ItemNumber, '-'), ltab_ref_uni_flg(indx).VariantCode)
|| '</VariantItem>'
|| '<ValidRecord>'
|| 'Y'
|| '</ValidRecord>'
|| '<Message>'
|| 'Success'
|| '</Message>'
|| '<packValue>'
|| ltab_ref_uni_flg(indx).PackValue
|| '</packValue>'
|| '<BOM>'
|| '<Stk1>'
|| ltab_ref_uni_flg(indx).Stk1
|| '</Stk1>'
|| '<Stk2>'
|| ltab_ref_uni_flg(indx).Stk2
|| '</Stk2>'
|| '<Stk3>'
|| ltab_ref_uni_flg(indx).Stk3
|| '</Stk3>'
|| '<Stk4>'
|| ltab_ref_uni_flg(indx).Stk4
|| '</Stk4>'
|| '<Stk5>'
|| ltab_ref_uni_flg(indx).Stk5
|| '</Stk5>'
|| '<BaseItemNumber>'
|| ltab_ref_uni_flg(indx).ItemNumber
|| '</BaseItemNumber>';
FOR comp IN ltab_comp_dtl.FIRST..ltab_comp_dtl.LAST
LOOP
l_loops := l_loops + 1;
l_data_clob := l_data_clob
||'<Component' || l_loops || '>'
||CONCAT(CONCAT(ltab_comp_dtl(comp).component, '|'), ltab_comp_dtl(comp).quantity)
||'</Component'|| l_loops ||'>';
END LOOP;
l_loops :=0;
l_data_clob :=l_data_clob
|| '</BOM>'
|| '<BackToBackEnabled>'
|| ltab_itm_dtl(itm).BackToBackEnabled
|| '</BackToBackEnabled>'
|| '<BaseItemClass>'
|| ltab_itm_dtl(itm).ItemClassName
|| '</BaseItemClass>'
|| '<RevisionValue>'
|| ltab_itm_dtl(itm).Revision
|| '</RevisionValue>'
|| '<VersionValue>'
|| ltab_itm_dtl(itm).version
|| '</VersionValue>'
|| '<InnerWT>'
|| ltab_itm_dtl(itm).weightI
|| '</InnerWT>'
|| '<InnerH>'
|| ltab_itm_dtl(itm).heightI
|| '</InnerH>'
|| '<InnerL>'
|| ltab_itm_dtl(itm).lengthI
|| '</InnerL>'
|| '<InnerWD>'
|| ltab_itm_dtl(itm).widthI
|| '</InnerWD>'
|| '<MasterWT>'
|| ltab_itm_dtl(itm).weightM
|| '</MasterWT>'
|| '<MasterH>'
|| ltab_itm_dtl(itm).heightM
|| '</MasterH>'
|| '<MasterL>'
|| ltab_itm_dtl(itm).lengthM
|| '</MasterL>'
|| '<MasterWD>'
|| ltab_itm_dtl(itm).widthM
|| '</MasterWD>'
|| '<PalleteWT>'
|| ltab_itm_dtl(itm).weightP
|| '</PalleteWT>'
|| '<PalleteH>'
|| ltab_itm_dtl(itm).heightP
|| '</PalleteH>'
|| '<PalleteL>'
|| ltab_itm_dtl(itm).lengthP
|| '</PalleteL>'
|| '<PalleteWD>'
|| ltab_itm_dtl(itm).widthP
|| '</PalleteWD>'
|| '<IQTY>'
|| ltab_itm_dtl(itm).IQTY
|| '</IQTY>'
|| '<MQTY>'
|| ltab_itm_dtl(itm).MQTY
|| '</MQTY>'
|| '<PQTY>'
|| ltab_itm_dtl(itm).PQTY
|| '</PQTY>'
|| '<ISerialCode>'
|| v_iserial
|| '</ISerialCode>'
|| '<PSerialCode>'
|| v_pserial
|| '</PSerialCode>'
|| '<MSerialCode>'
|| v_mserial
|| '</MSerialCode>'
|| '<DevEntity>'
|| v_dev_entity
|| '</DevEntity>'
|| '</item_list>';
ELSE
-- If the component is of assort of assort
l_data_clob := l_data_clob
|| '<item_list>'
|| '<RecId>'
|| ltab_ref_uni_flg(indx).RecId
|| '</RecId>'
|| '<BaseItemNumber>'
|| ltab_ref_uni_flg(indx).ItemNumber
|| '</BaseItemNumber>'
|| '<VariantItem>'
|| CONCAT(CONCAT(ltab_ref_uni_flg(indx).ItemNumber, '-'), ltab_ref_uni_flg(indx).VariantCode)
|| '</VariantItem>'
|| '<ValidRecord>'
|| 'N'
|| '</ValidRecord>'
|| '<Message>'
|| 'Item Variant cannot br created because BaseItem is of type assort of assort'
|| '</Message>'
|| '</item_list>';
l_bill_clob := empty_clob();
l_data_tmp_clob := empty_clob();
END IF;
END IF;
END LOOP;
END IF;
END LOOP;
END IF;
END IF;
END LOOP;
END IF;
OPEN :xdo_cursor FOR SELECT l_data_clob FROM dual;
END; Write, Run & Share MySQL queries online using OneCompiler's MySQL online editor and compiler for free. It's one of the robust, feature-rich online editor and compiler for MySQL. Getting started with the OneCompiler's MySQL editor is really simple and pretty fast. The editor shows sample boilerplate code when you choose language as 'MySQL' and start writing queries to learn and test online without worrying about tedious process of installation.
MySQL is a open-source, free and very popular relational database management system which is developed, distributed and supported by Oracle corporation.
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
....);
CREATE TABLE EMPLOYEE (
empId INTEGER PRIMARY KEY,
name TEXT NOT NULL,
dept TEXT NOT NULL
);
ALTER TABLE Table_name ADD column_name datatype;
INSERT INTO EMPLOYEE VALUES (0001, 'Dave', 'Sales');
TRUNCATE table table_name;
DROP TABLE table_name;
RENAME TABLE table_name1 to new_table_name1;
--Line1;
/* Line1,
Line2 */
INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);
Note: Column names are optional.
INSERT INTO EMPLOYEE VALUES (0001, 'Ava', 'Sales');
SELECT column1, column2, ...
FROM table_name
[where condition];
SELECT * FROM EMPLOYEE where dept ='sales';
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
UPDATE EMPLOYEE SET dept = 'Sales' WHERE empId='0001';
DELETE FROM table_name where condition;
DELETE from EMPLOYEE where empId='0001';
CREATE INDEX index_name on table_name(column_name);
CREATE UNIQUE INDEX index_name on table_name(column_name);
DROP INDEX index_name ON table_name;
Creating a View:
CREATE VIEW View_name AS
Query;
SELECT * FROM View_name;
ALTER View View_name AS
Query;
DROP VIEW View_name;
CREATE TRIGGER trigger_name trigger_time trigger_event
ON tbl_name FOR EACH ROW [trigger_order] trigger_body
/* where
trigger_time: { BEFORE | AFTER }
trigger_event: { INSERT | UPDATE | DELETE }
trigger_order: { FOLLOWS | PRECEDES } */
DROP TRIGGER [IF EXISTS] trigger_name;
CREATE PROCEDURE sp_name(p1 datatype)
BEGIN
/*Stored procedure code*/
END;
CALL sp_name;
DROP PROCEDURE sp_name;
SELECT * FROM TABLE1 INNER JOIN TABLE2 where condition;
SELECT * FROM TABLE1 LEFT JOIN TABLE2 ON condition;
SELECT * FROM TABLE1 RIGHT JOIN TABLE2 ON condition;
SELECT select_list from TABLE1 CROSS JOIN TABLE2;