SET VERIFY ON -- -- ++ -- FACILITY: DIBOL 2 ORACLE Server 7/8/9 gateway -- -- MODULE DESCRIPTION: -- -- This module contains ORACLE part of the interface, wich implemented as a set of PL/SQL routines, -- these routines supposed to be called from C routines (see DBL_ORAGTWY.C module). -- -- AUTHORS: -- -- Ruslan R. Laishev -- -- CREATION DATE: 5-MAR-2002 -- -- DESIGN ISSUES: -- -- All routines are implemented as a single package, this package should be placed in the same ORACLE shema -- whith the used ORACLE tables and views. -- Be advised that ORACLE SQLCODE must be analyzed after calling an every routine. -- -- -- MODIFICATION HISTORY: -- -- {@tbs@}... -- -- -- -- -- ++ -- -- THIS IS A DECLARATION SECTION OF THE dbl_oragtwy PACKAGE -- -- -- -- CREATE OR REPLACE PACKAGE dbl_oragtwy AS -- -- item_* -- PROCEDURE item_catlist_oc ( sku IN VARCHAR2, sup IN VARCHAR2 ); PROCEDURE item_catlist_cc ; PROCEDURE item_catlist_fd ( short_name OUT VARCHAR2, feature_name OUT VARCHAR2 ); -- -- brand_* -- PROCEDURE brand_catlist_oc( sku IN VARCHAR2, sup IN VARCHAR2, config IN VARCHAR2 ); PROCEDURE brand_catlist_cc; PROCEDURE brand_catlist_fd( short_name OUT VARCHAR2, feature_name OUT VARCHAR2 ); -- -- attlist_* -- PROCEDURE attlist_oc ; PROCEDURE attlist_cc ; PROCEDURE attlist_fd ( sku1 OUT VARCHAR2, sup1 OUT VARCHAR2, conf1 OUT VARCHAR2, type1 OUT VARCHAR2, sku2 OUT VARCHAR2, sup2 OUT VARCHAR2, conf2 OUT VARCHAR2, type2 OUT VARCHAR2 ); -- -- itemlist_* -- PROCEDURE itemlist_oc ( legacy_seq IN NUMBER ); PROCEDURE itemlist_cc; PROCEDURE itemlist_fd ( ORAINSUP OUT CHAR, ORATYPE OUT CHAR, ORAITEM OUT CHAR, ORASEG2 OUT CHAR, ORASEG3 OUT CHAR, ORASHORT OUT CHAR, ORALONG OUT CHAR, ORAUOMWORD OUT CHAR, ORAUOMQUANTITY OUT SMALLINT, ORACATEGORY OUT CHAR, ORACLASS OUT CHAR, ORAOLDITEM OUT CHAR, ORACONSIGNED OUT CHAR, ORABOM OUT CHAR, ORABUILDTOORDER OUT CHAR, ORACPS OUT CHAR, ORAFORVEL OUT CHAR, ORASTATUS OUT CHAR, ORAMFG OUT CHAR, ORAMPN OUT CHAR, ORAMODEL OUT CHAR, ORASERIAL OUT CHAR, ORASERLEN OUT SMALLINT, ORADDROP OUT CHAR, ORANEWSER OUT CHAR, ORAPRELOAD OUT CHAR, ORA2SERIAL OUT CHAR, ORA2LENGTH OUT SMALLINT, ORAWEIGHT OUT NUMBER, ORASHIPMETH OUT CHAR, ORASELLTERR OUT CHAR, ORASOURCE OUT CHAR, ORACURA OUT NUMBER, ORANEWA OUT NUMBER, ORAVENCOST OUT NUMBER, ORAFUTVEN OUT NUMBER, ORABUY OUT CHAR, ORAVENDOR OUT CHAR, ORAVPN OUT CHAR, ORACASE OUT NUMBER, ORASTORAGE OUT CHAR, ORAFREIGHT OUT CHAR, ORABUNDLE OUT SMALLINT, ORAORIGIN OUT CHAR, ORAHARM1 OUT SMALLINT, ORAHARM2 OUT SMALLINT, ORAHARM3 OUT SMALLINT, ORAPREF OUT CHAR, ORALABELED OUT CHAR, ORAHEIGHT OUT SMALLINT, ORAWIDTH OUT SMALLINT, ORALENGTH OUT SMALLINT, ORABARCODE OUT CHAR, ORARENO OUT CHAR, ORAFIFODAYS OUT SMALLINT, ORAFIFO OUT CHAR, ORACONFIGURED OUT CHAR, ORAOWNBOX OUT CHAR, ORASTOCK03 OUT CHAR, ORASTOCK04 OUT CHAR, ORASTOCK05 OUT CHAR, ORASTOCK06 OUT CHAR, ORASTOCK07 OUT CHAR, ORACONSIGNFEE OUT NUMBER, ORASORT1 OUT CHAR, ORASORT2 OUT CHAR, ORABGINCLUDE OUT CHAR, ORACURBG OUT INTEGER, ORANEXTBG OUT INTEGER, ORAPREVBG OUT INTEGER, ORAGROUP OUT CHAR, ORASUB OUT CHAR, ORAMAXPERCUS OUT SMALLINT, ORACURRENCY OUT CHAR, ORASEQ OUT NUMBER, ORAUDIT OUT CHAR, ORAVAILABLE OUT CHAR, ORAFUTDATE OUT CHAR, ORAINPUTDATE OUT CHAR, ORAFORAUTOBUY OUT CHAR, ORAFORBLOWOUT03 OUT NUMBER, ORAFORBLOWOUT04 OUT NUMBER, ORAFORBLOWOUT05 OUT NUMBER, ORAFORBLOWOUT06 OUT NUMBER, ORAFORBLOWOUT07 OUT NUMBER ); PROCEDURE get_oracle_sku ( sku IN VARCHAR2, sup IN VARCHAR2, ORAINSUP OUT CHAR, ORATYPE OUT CHAR, ORAITEM OUT CHAR, ORASEG2 OUT CHAR, ORASEG3 OUT CHAR, ORASHORT OUT CHAR, ORALONG OUT CHAR, ORAUOMWORD OUT CHAR, ORAUOMQUANTITY OUT SMALLINT, ORACATEGORY OUT CHAR, ORACLASS OUT CHAR, ORAOLDITEM OUT CHAR, ORACONSIGNED OUT CHAR, ORABOM OUT CHAR, ORABUILDTOORDER OUT CHAR, ORACPS OUT CHAR, ORAFORVEL OUT CHAR, ORASTATUS OUT CHAR, ORAMFG OUT CHAR, ORAMPN OUT CHAR, ORAMODEL OUT CHAR, ORASERIAL OUT CHAR, ORASERLEN OUT SMALLINT, ORADDROP OUT CHAR, ORANEWSER OUT CHAR, ORAPRELOAD OUT CHAR, ORA2SERIAL OUT CHAR, ORA2LENGTH OUT SMALLINT, ORAWEIGHT OUT NUMBER, ORASHIPMETH OUT CHAR, ORASELLTERR OUT CHAR, ORASOURCE OUT CHAR, ORACURA OUT NUMBER, ORANEWA OUT NUMBER, ORAVENCOST OUT NUMBER, ORAFUTVEN OUT NUMBER, ORABUY OUT CHAR, ORAVENDOR OUT CHAR, ORAVPN OUT CHAR, ORACASE OUT NUMBER, ORASTORAGE OUT CHAR, ORAFREIGHT OUT CHAR, ORABUNDLE OUT SMALLINT, ORAORIGIN OUT CHAR, ORAHARM1 OUT SMALLINT, ORAHARM2 OUT SMALLINT, ORAHARM3 OUT SMALLINT, ORAPREF OUT CHAR, ORALABELED OUT CHAR, ORAHEIGHT OUT SMALLINT, ORAWIDTH OUT SMALLINT, ORALENGTH OUT SMALLINT, ORABARCODE OUT CHAR, ORARENO OUT CHAR, ORAFIFODAYS OUT SMALLINT, ORAFIFO OUT CHAR, ORACONFIGURED OUT CHAR, ORAOWNBOX OUT CHAR, ORASTOCK03 OUT CHAR, ORASTOCK04 OUT CHAR, ORASTOCK05 OUT CHAR, ORASTOCK06 OUT CHAR, ORASTOCK07 OUT CHAR, ORACONSIGNFEE OUT NUMBER, ORASORT1 OUT CHAR, ORASORT2 OUT CHAR, ORABGINCLUDE OUT CHAR, ORACURBG OUT INTEGER, ORANEXTBG OUT INTEGER, ORAPREVBG OUT INTEGER, ORAGROUP OUT CHAR, ORASUB OUT CHAR, ORAMAXPERCUS OUT SMALLINT, ORACURRENCY OUT CHAR, ORASEQ OUT NUMBER, ORAUDIT OUT CHAR, ORAVAILABLE OUT CHAR, ORAFUTDATE OUT CHAR, ORAINPUTDATE OUT CHAR, ORAFORAUTOBUY OUT CHAR, ORAFORBLOWOUT03 OUT NUMBER, ORAFORBLOWOUT04 OUT NUMBER, ORAFORBLOWOUT05 OUT NUMBER, ORAFORBLOWOUT06 OUT NUMBER, ORAFORBLOWOUT07 OUT NUMBER ); -- -- ora_* -- PROCEDURE ora_ctc ; PROCEDURE ora_rtc ; -- -- hazmatlist_* -- PROCEDURE hazmatlist_oc ( feature_name IN VARCHAR2 ); PROCEDURE hazmatlist_cc; PROCEDURE hazmatlist_fd ( yoro OUT CHAR, sku OUT VARCHAR2, sup OUT VARCHAR2 ); -- -- sublist_* -- PROCEDURE sublist_oc; PROCEDURE sublist_cc; PROCEDURE sublist_fd ( sku1 OUT VARCHAR2, sup1 OUT VARCHAR2, conf1 OUT VARCHAR2, type1 OUT VARCHAR2, sku2 OUT VARCHAR2, sup2 OUT VARCHAR2, conf2 OUT VARCHAR2, type2 OUT VARCHAR2, flag OUT CHAR ); -- -- item_sublist_* -- PROCEDURE item_sublist_oc ( subsku IN VARCHAR2, subsup IN VARCHAR2 ); PROCEDURE item_sublist_cc; PROCEDURE item_sublist_fd ( sku1 OUT VARCHAR2, sup1 OUT VARCHAR2, type1 OUT VARCHAR2, sku2 OUT VARCHAR2, sup2 OUT VARCHAR2, type2 OUT VARCHAR2 ); END dbl_oragtwy; / SHOW ERRORS PACKAGE dbl_oragtwy; CREATE OR REPLACE PACKAGE BODY dbl_oragtwy AS -- -- ++ -- -- THIS IS A dbl_oragtwy PACKAGE BODY -- -- -- -- -- -- Cursors declaration section -- CURSOR attlist IS SELECT m1.sku,m1.supplier_code, NVL(m1.configuration,' '), m1.item_type,m2.sku,m2.supplier_code, NVL(m2.configuration,' '), m2.item_type FROM tessco_item_master_oesv m1, tessco_item_master_oesv m2, tessco_item_relationships_v r WHERE m1.item_id = r.related_item_id AND m2.item_id = r.item_id AND r.relationship_type_id = 1; CURSOR item_catlist(sku VARCHAR2,sup VARCHAR2) IS SELECT NVL(c.short_name,' '),NVL(c.feature_value,' ') FROM tessco_item_master_oesv m, tessco_descr_element_values_v c WHERE m.sku = sku AND m.supplier_code = sup AND m.item_id = c.item_id ORDER BY c.feature_type_code; CURSOR brand_catlist(sku VARCHAR2,sup VARCHAR2,config VARCHAR2) IS SELECT NVL(c.short_name,' '),NVL(c.feature_value,' ') FROM tessco_item_master_oesv m, tessco_descr_element_values_v c WHERE m.item_id = c.item_id AND m.sku = sku AND m.supplier_code = sup AND m.configuration = config ORDER BY c.feature_type_code; CURSOR itemlist(legacy_seq NUMBER) IS SELECT NVL(INSERT_UPDATE,' '), NVL(ITEM_TYPE,' '), NVL(SKU,' '), NVL(SUPPLIER_CODE,' '), NVL(CONFIGURATION,' '), NVL(SHORT_DESCRIPTION,' '), NVL(LONG_DESCRIPTION,' '), NVL(LEGACY_UOM,'EACH'), NVL(LEGACY_UOM_QUANTITY,0), NVL(CATEGORY_CODE,' '), NVL(LEGACY_CLASS,' '), NVL(ALT_SKU_NUMBER,' '), NVL(CONSIGNED_ITEM,'N'), NVL(BOM,'N'), NVL(BUILD_TO_ORDER,'N'), NVL(TRACKED_IN_CPS,'N'), NVL(FORCE_VELOCITY_CODE,' '), NVL(STATUS_CODE,'A'), NVL(MANUFACTURE_CODE,' '), NVL(MANUFACTURE_PART_NUMBER,' '), NVL(MANUFACTURE_MODEL_NUMBER,' '), NVL(SERIAL_NUMBER_REQUIRED,'N'), NVL(MAIN_SERIAL_NUMBER_LENGTH,0), NVL(ADD_OR_DROP_CHAR_SCAN,' '), NVL(HOLD_NEW_SERIAL_NUMBERS,'N'), NVL(PRELOAD_SERIAL_NUMBER,'N'), NVL(SECOND_SERIAL_NUMBER_REQUIRED,'N'), NVL(SECOND_SERIAL_NUMBER_LENGTH,0), NVL(WEIGHT,0), NVL(SHIP_METHOD,'U'), NVL(TERRITORIAL_RESTRICTIONS,' '), NVL(SELLING_RESTRICTION_CODE,' '), NVL(CURRENT_A,0), NVL(NEW_A,0), NVL(VENDOR_CURRENT_COST,0), NVL(VENDOR_FUTURE_COST,0), NVL(BUY,'Y'), NVL(VENDOR_CODE,' '), NVL(VENDOR_PART_NUMBER,' '), NVL(CASE_MULTIPLE,1), NVL(TYPE_OF_STORAGE,'C'), NVL(FREIGHT_SHIPPING_CODE,' '), NVL(BUNDLING_QTY_SHIPPING,1), NVL(COUNTRY_OF_ORIGIN_ID,' '), NVL(HARM_CODE_1,0), NVL(HARM_CODE_2,0), NVL(HARM_CODE_3,0), NVL(PREF_CRITERIA,' '), NVL(LABELED,'Y'), NVL(SHIP_HEIGHT,0), NVL(SHIP_WIDTH,0), NVL(SHIP_LENGTH,0), NVL(BARCODE,' '), NVL(RENO_STOCKING_FLAG,'N'), NVL(MAX_DAYS_IN_ONE_LOC_FIFO,0), NVL(FIFO_STOCKING,'N'), NVL(CONFIGURED_ITEM,'N'), NVL(SHIP_IN_OWN_BOX,'Y'), NVL(STOCKED_AT_03,'Y'), NVL(STOCKED_AT_04,'N'), NVL(STOCKED_AT_05,'N'), NVL(STOCKED_AT_06,'N'), NVL(STOCKED_AT_07,'N'), NVL(CONSIGNED_DELIVERY_FEE,0), NVL(FIRST_SORT,' '), NVL(SECOND_SORT,' '), NVL(INCLUDE_IN_CURRENT_BG,'Y'), NVL(CURRENT_BG_PAGE,0), NVL(NEXT_BG_PAGE,0), NVL(PREVIOUS_BG_PAGE,0), NVL(LEGACY_GROUP_NUMBER,' '), NVL(LEGACY_SUBGROUP_NUMBER,' '), NVL(MAX_PER_CUSTOMER,0), NVL(CURRENCY,'US'), TXN_SN, TO_CHAR(NVL(LAST_SIZE_AND_WEIGHT_AUDIT,TO_DATE('19800101','YYYYMMDD')),'YYYYMMDD'), TO_CHAR(NVL(AVAILABLE_DATE,TO_DATE('19800101','YYYYMMDD')),'YYYYMMDD'), TO_CHAR(NVL(VEND_FUT_COST_EFFECT_DATE,TO_DATE('19800101','YYYYMMDD')),'YYYYMMDD'), TO_CHAR(NVL(CREATION_DATE,TO_DATE('19800101','YYYYMMDD')),'YYYYMMDD'), NVL(FORCED_AUTOBUY,' '), NVL(FORCED_BLOWOUT_03,0), NVL(FORCED_BLOWOUT_04,0), NVL(FORCED_BLOWOUT_05,0), NVL(FORCED_BLOWOUT_06,0), NVL(FORCED_BLOWOUT_07,0) FROM tessco_item_master_oesv WHERE txn_sn > legacy_seq ORDER BY txn_sn; CURSOR hazmatlist(feature_name VARCHAR2) IS SELECT c.feature_value,m.sku,m.supplier_code FROM tessco_descr_element_values_v c, tessco_item_master_oesv m WHERE c.short_name = feature_name AND m.item_id = c.item_id AND m.supplier_code in ('729198','646444') AND c.feature_value in ('Y','O','o','y') ; CURSOR sublist IS SELECT m1.sku,m1.supplier_code,NVL(m1.configuration,' '), m1.item_type,m2.sku,m2.supplier_code,NVL(m2.configuration,' '), m2.item_type,r.reciprocal_flag FROM tessco_item_master_oesv m1, tessco_item_master_oesv m2, tessco_item_relationships_v r WHERE m1.item_id = r.related_item_id AND m2.item_id = r.item_id AND r.relationship_type_id = 2; CURSOR item_sublist(subsku VARCHAR2,subsup VARCHAR2) IS SELECT m1.sku,m1.supplier_code,m1.item_type, m2.sku,m2.supplier_code,m2.item_type FROM tessco_item_master_oesv m1, tessco_item_master_oesv m2, tessco_item_relationships_v r WHERE r.related_item_id = m1.item_id AND r.item_id = m2.item_id AND ((m2.sku = subsku and m2.supplier_code = subsup) OR (m1.sku = subsku and m1.supplier_code = subsup AND r.reciprocal_flag = 'Y')) AND r.relationship_type_id = 2; -- -- item_* -- PROCEDURE item_catlist_oc ( sku IN VARCHAR2, sup IN VARCHAR2 ) IS BEGIN IF NOT ( item_catlist%ISOPEN ) THEN OPEN item_catlist(sku,sup); END IF; END; PROCEDURE item_catlist_cc IS BEGIN IF ( item_catlist%ISOPEN ) THEN CLOSE item_catlist; END IF; END; PROCEDURE item_catlist_fd ( short_name OUT VARCHAR2, feature_name OUT VARCHAR2 ) IS BEGIN FETCH item_catlist INTO short_name,feature_name; END; -- -- brand_* -- PROCEDURE brand_catlist_oc( sku IN VARCHAR2, sup IN VARCHAR2, config IN VARCHAR2 ) IS BEGIN IF NOT ( brand_catlist%ISOPEN ) THEN OPEN brand_catlist(sku,sup,config); END IF; END; PROCEDURE brand_catlist_cc IS BEGIN IF ( brand_catlist%ISOPEN ) THEN CLOSE brand_catlist; END IF; END; PROCEDURE brand_catlist_fd( short_name OUT VARCHAR2, feature_name OUT VARCHAR2 ) IS BEGIN FETCH brand_catlist INTO short_name,feature_name; END; -- -- attlist_* -- PROCEDURE attlist_oc IS BEGIN IF NOT ( attlist%ISOPEN ) THEN OPEN attlist; END IF; END; PROCEDURE attlist_cc IS BEGIN IF ( attlist%ISOPEN ) THEN CLOSE attlist; END IF; END; PROCEDURE attlist_fd ( sku1 OUT VARCHAR2, sup1 OUT VARCHAR2, conf1 OUT VARCHAR2, type1 OUT VARCHAR2, sku2 OUT VARCHAR2, sup2 OUT VARCHAR2, conf2 OUT VARCHAR2, type2 OUT VARCHAR2 ) IS BEGIN FETCH attlist INTO sku1,sup1,conf1,type1,sku2,sup2,conf2,type2; END; -- -- ora_* -- PROCEDURE ora_ctc IS BEGIN COMMIT; END; PROCEDURE ora_rtc IS BEGIN ROLLBACK; END; -- -- hazmatlist_* -- PROCEDURE hazmatlist_oc ( feature_name IN VARCHAR2 ) IS BEGIN IF NOT ( hazmatlist%ISOPEN ) THEN OPEN hazmatlist(feature_name); END IF; END; PROCEDURE hazmatlist_cc IS BEGIN IF ( hazmatlist%ISOPEN ) THEN CLOSE hazmatlist; END IF; END; PROCEDURE hazmatlist_fd ( yoro OUT CHAR, sku OUT VARCHAR2, sup OUT VARCHAR2 ) IS BEGIN FETCH hazmatlist INTO yoro,sku,sup; END; -- -- sublist_* -- PROCEDURE sublist_oc IS BEGIN IF NOT ( sublist%ISOPEN ) THEN OPEN sublist; END IF; END; PROCEDURE sublist_cc IS BEGIN IF ( sublist%ISOPEN ) THEN CLOSE sublist; END IF; END; PROCEDURE sublist_fd ( sku1 OUT VARCHAR2, sup1 OUT VARCHAR2, conf1 OUT VARCHAR2, type1 OUT VARCHAR2, sku2 OUT VARCHAR2, sup2 OUT VARCHAR2, conf2 OUT VARCHAR2, type2 OUT VARCHAR2, flag OUT CHAR ) IS BEGIN FETCH sublist INTO sku1,sup1,conf1,type1,sku2,sup2,conf2,type2,flag; END; -- -- item_sublist_* -- PROCEDURE item_sublist_oc ( subsku IN VARCHAR2, subsup IN VARCHAR2 ) IS BEGIN IF NOT ( item_sublist%ISOPEN ) THEN OPEN item_sublist (subsku,subsup); END IF; END; PROCEDURE item_sublist_cc IS BEGIN IF ( item_sublist%ISOPEN ) THEN CLOSE item_sublist; END IF; END; PROCEDURE item_sublist_fd ( sku1 OUT VARCHAR2, sup1 OUT VARCHAR2, type1 OUT VARCHAR2, sku2 OUT VARCHAR2, sup2 OUT VARCHAR2, type2 OUT VARCHAR2 ) IS BEGIN FETCH item_sublist INTO sku1,sup1,type1,sku2,sup2,type2; END; -- -- itemlist_* -- PROCEDURE itemlist_oc ( legacy_seq IN NUMBER ) IS BEGIN IF NOT ( itemlist%ISOPEN ) THEN OPEN itemlist(legacy_seq); END IF; END; PROCEDURE itemlist_cc IS BEGIN IF ( itemlist%ISOPEN ) THEN CLOSE itemlist; END IF; END; PROCEDURE itemlist_fd ( ORAINSUP OUT CHAR, ORATYPE OUT CHAR, ORAITEM OUT CHAR, ORASEG2 OUT CHAR, ORASEG3 OUT CHAR, ORASHORT OUT CHAR, ORALONG OUT CHAR, ORAUOMWORD OUT CHAR, ORAUOMQUANTITY OUT SMALLINT, ORACATEGORY OUT CHAR, ORACLASS OUT CHAR, ORAOLDITEM OUT CHAR, ORACONSIGNED OUT CHAR, ORABOM OUT CHAR, ORABUILDTOORDER OUT CHAR, ORACPS OUT CHAR, ORAFORVEL OUT CHAR, ORASTATUS OUT CHAR, ORAMFG OUT CHAR, ORAMPN OUT CHAR, ORAMODEL OUT CHAR, ORASERIAL OUT CHAR, ORASERLEN OUT SMALLINT, ORADDROP OUT CHAR, ORANEWSER OUT CHAR, ORAPRELOAD OUT CHAR, ORA2SERIAL OUT CHAR, ORA2LENGTH OUT SMALLINT, ORAWEIGHT OUT NUMBER, ORASHIPMETH OUT CHAR, ORASELLTERR OUT CHAR, ORASOURCE OUT CHAR, ORACURA OUT NUMBER, ORANEWA OUT NUMBER, ORAVENCOST OUT NUMBER, ORAFUTVEN OUT NUMBER, ORABUY OUT CHAR, ORAVENDOR OUT CHAR, ORAVPN OUT CHAR, ORACASE OUT NUMBER, ORASTORAGE OUT CHAR, ORAFREIGHT OUT CHAR, ORABUNDLE OUT SMALLINT, ORAORIGIN OUT CHAR, ORAHARM1 OUT SMALLINT, ORAHARM2 OUT SMALLINT, ORAHARM3 OUT SMALLINT, ORAPREF OUT CHAR, ORALABELED OUT CHAR, ORAHEIGHT OUT SMALLINT, ORAWIDTH OUT SMALLINT, ORALENGTH OUT SMALLINT, ORABARCODE OUT CHAR, ORARENO OUT CHAR, ORAFIFODAYS OUT SMALLINT, ORAFIFO OUT CHAR, ORACONFIGURED OUT CHAR, ORAOWNBOX OUT CHAR, ORASTOCK03 OUT CHAR, ORASTOCK04 OUT CHAR, ORASTOCK05 OUT CHAR, ORASTOCK06 OUT CHAR, ORASTOCK07 OUT CHAR, ORACONSIGNFEE OUT NUMBER, ORASORT1 OUT CHAR, ORASORT2 OUT CHAR, ORABGINCLUDE OUT CHAR, ORACURBG OUT INTEGER, ORANEXTBG OUT INTEGER, ORAPREVBG OUT INTEGER, ORAGROUP OUT CHAR, ORASUB OUT CHAR, ORAMAXPERCUS OUT SMALLINT, ORACURRENCY OUT CHAR, ORASEQ OUT NUMBER, ORAUDIT OUT CHAR, ORAVAILABLE OUT CHAR, ORAFUTDATE OUT CHAR, ORAINPUTDATE OUT CHAR, ORAFORAUTOBUY OUT CHAR, ORAFORBLOWOUT03 OUT NUMBER, ORAFORBLOWOUT04 OUT NUMBER, ORAFORBLOWOUT05 OUT NUMBER, ORAFORBLOWOUT06 OUT NUMBER, ORAFORBLOWOUT07 OUT NUMBER ) IS BEGIN FETCH itemlist INTO ORAINSUP , ORATYPE , ORAITEM , ORASEG2 , ORASEG3 , ORASHORT , ORALONG , ORAUOMWORD , ORAUOMQUANTITY , ORACATEGORY , ORACLASS , ORAOLDITEM , ORACONSIGNED , ORABOM , ORABUILDTOORDER , ORACPS , ORAFORVEL , ORASTATUS , ORAMFG , ORAMPN , ORAMODEL , ORASERIAL , ORASERLEN , ORADDROP , ORANEWSER , ORAPRELOAD , ORA2SERIAL , ORA2LENGTH , ORAWEIGHT , ORASHIPMETH , ORASELLTERR , ORASOURCE , ORACURA , ORANEWA , ORAVENCOST , ORAFUTVEN , ORABUY , ORAVENDOR , ORAVPN , ORACASE , ORASTORAGE , ORAFREIGHT , ORABUNDLE , ORAORIGIN , ORAHARM1 , ORAHARM2 , ORAHARM3 , ORAPREF , ORALABELED , ORAHEIGHT , ORAWIDTH , ORALENGTH , ORABARCODE , ORARENO , ORAFIFODAYS , ORAFIFO , ORACONFIGURED , ORAOWNBOX , ORASTOCK03 , ORASTOCK04 , ORASTOCK05 , ORASTOCK06 , ORASTOCK07 , ORACONSIGNFEE , ORASORT1 , ORASORT2 , ORABGINCLUDE , ORACURBG , ORANEXTBG , ORAPREVBG , ORAGROUP , ORASUB , ORAMAXPERCUS , ORACURRENCY , ORASEQ , ORAUDIT , ORAVAILABLE , ORAFUTDATE , ORAINPUTDATE , ORAFORAUTOBUY , ORAFORBLOWOUT03 , ORAFORBLOWOUT04 , ORAFORBLOWOUT05 , ORAFORBLOWOUT06 , ORAFORBLOWOUT07; END; PROCEDURE get_oracle_sku ( sku IN VARCHAR2, sup IN VARCHAR2, ORAINSUP OUT CHAR, ORATYPE OUT CHAR, ORAITEM OUT CHAR, ORASEG2 OUT CHAR, ORASEG3 OUT CHAR, ORASHORT OUT CHAR, ORALONG OUT CHAR, ORAUOMWORD OUT CHAR, ORAUOMQUANTITY OUT SMALLINT, ORACATEGORY OUT CHAR, ORACLASS OUT CHAR, ORAOLDITEM OUT CHAR, ORACONSIGNED OUT CHAR, ORABOM OUT CHAR, ORABUILDTOORDER OUT CHAR, ORACPS OUT CHAR, ORAFORVEL OUT CHAR, ORASTATUS OUT CHAR, ORAMFG OUT CHAR, ORAMPN OUT CHAR, ORAMODEL OUT CHAR, ORASERIAL OUT CHAR, ORASERLEN OUT SMALLINT, ORADDROP OUT CHAR, ORANEWSER OUT CHAR, ORAPRELOAD OUT CHAR, ORA2SERIAL OUT CHAR, ORA2LENGTH OUT SMALLINT, ORAWEIGHT OUT NUMBER, ORASHIPMETH OUT CHAR, ORASELLTERR OUT CHAR, ORASOURCE OUT CHAR, ORACURA OUT NUMBER, ORANEWA OUT NUMBER, ORAVENCOST OUT NUMBER, ORAFUTVEN OUT NUMBER, ORABUY OUT CHAR, ORAVENDOR OUT CHAR, ORAVPN OUT CHAR, ORACASE OUT NUMBER, ORASTORAGE OUT CHAR, ORAFREIGHT OUT CHAR, ORABUNDLE OUT SMALLINT, ORAORIGIN OUT CHAR, ORAHARM1 OUT SMALLINT, ORAHARM2 OUT SMALLINT, ORAHARM3 OUT SMALLINT, ORAPREF OUT CHAR, ORALABELED OUT CHAR, ORAHEIGHT OUT SMALLINT, ORAWIDTH OUT SMALLINT, ORALENGTH OUT SMALLINT, ORABARCODE OUT CHAR, ORARENO OUT CHAR, ORAFIFODAYS OUT SMALLINT, ORAFIFO OUT CHAR, ORACONFIGURED OUT CHAR, ORAOWNBOX OUT CHAR, ORASTOCK03 OUT CHAR, ORASTOCK04 OUT CHAR, ORASTOCK05 OUT CHAR, ORASTOCK06 OUT CHAR, ORASTOCK07 OUT CHAR, ORACONSIGNFEE OUT NUMBER, ORASORT1 OUT CHAR, ORASORT2 OUT CHAR, ORABGINCLUDE OUT CHAR, ORACURBG OUT INTEGER, ORANEXTBG OUT INTEGER, ORAPREVBG OUT INTEGER, ORAGROUP OUT CHAR, ORASUB OUT CHAR, ORAMAXPERCUS OUT SMALLINT, ORACURRENCY OUT CHAR, ORASEQ OUT NUMBER, ORAUDIT OUT CHAR, ORAVAILABLE OUT CHAR, ORAFUTDATE OUT CHAR, ORAINPUTDATE OUT CHAR, ORAFORAUTOBUY OUT CHAR, ORAFORBLOWOUT03 OUT NUMBER, ORAFORBLOWOUT04 OUT NUMBER, ORAFORBLOWOUT05 OUT NUMBER, ORAFORBLOWOUT06 OUT NUMBER, ORAFORBLOWOUT07 OUT NUMBER ) IS BEGIN SELECT NVL(INSERT_UPDATE,' '), NVL(ITEM_TYPE,' '), NVL(SKU,' '), NVL(SUPPLIER_CODE,' '), NVL(CONFIGURATION,' '), NVL(SHORT_DESCRIPTION,' '), NVL(LONG_DESCRIPTION,' '), NVL(LEGACY_UOM,'EACH'), NVL(LEGACY_UOM_QUANTITY,0), NVL(CATEGORY_CODE,' '), NVL(LEGACY_CLASS,' '), NVL(ALT_SKU_NUMBER,' '), NVL(CONSIGNED_ITEM,'N'), NVL(BOM,'N'), NVL(BUILD_TO_ORDER,'N'), NVL(TRACKED_IN_CPS,'N'), NVL(FORCE_VELOCITY_CODE,' '), NVL(STATUS_CODE,'A'), NVL(MANUFACTURE_CODE,' '), NVL(MANUFACTURE_PART_NUMBER,' '), NVL(MANUFACTURE_MODEL_NUMBER,' '), NVL(SERIAL_NUMBER_REQUIRED,'N'), NVL(MAIN_SERIAL_NUMBER_LENGTH,0), NVL(ADD_OR_DROP_CHAR_SCAN,' '), NVL(HOLD_NEW_SERIAL_NUMBERS,'N'), NVL(PRELOAD_SERIAL_NUMBER,'N'), NVL(SECOND_SERIAL_NUMBER_REQUIRED,'N'), NVL(SECOND_SERIAL_NUMBER_LENGTH,0), NVL(WEIGHT,0), NVL(SHIP_METHOD,'U'), NVL(TERRITORIAL_RESTRICTIONS,' '), NVL(SELLING_RESTRICTION_CODE,' '), NVL(CURRENT_A,0), NVL(NEW_A,0), NVL(VENDOR_CURRENT_COST,0), NVL(VENDOR_FUTURE_COST,0), NVL(BUY,'Y'), NVL(VENDOR_CODE,' '), NVL(VENDOR_PART_NUMBER,' '), NVL(CASE_MULTIPLE,1), NVL(TYPE_OF_STORAGE,'C'), NVL(FREIGHT_SHIPPING_CODE,' '), NVL(BUNDLING_QTY_SHIPPING,1), NVL(COUNTRY_OF_ORIGIN_ID,' '), NVL(HARM_CODE_1,0), NVL(HARM_CODE_2,0), NVL(HARM_CODE_3,0), NVL(PREF_CRITERIA,' '), NVL(LABELED,'Y'), NVL(SHIP_HEIGHT,0), NVL(SHIP_WIDTH,0), NVL(SHIP_LENGTH,0), NVL(BARCODE,' '), NVL(RENO_STOCKING_FLAG,'N'), NVL(MAX_DAYS_IN_ONE_LOC_FIFO,0), NVL(FIFO_STOCKING,'N'), NVL(CONFIGURED_ITEM,'N'), NVL(SHIP_IN_OWN_BOX,'Y'), NVL(STOCKED_AT_03,'Y'), NVL(STOCKED_AT_04,'N'), NVL(STOCKED_AT_05,'N'), NVL(STOCKED_AT_06,'N'), NVL(STOCKED_AT_07,'N'), NVL(CONSIGNED_DELIVERY_FEE,0), NVL(FIRST_SORT,' '), NVL(SECOND_SORT,' '), NVL(INCLUDE_IN_CURRENT_BG,'Y'), NVL(CURRENT_BG_PAGE,0), NVL(NEXT_BG_PAGE,0), NVL(PREVIOUS_BG_PAGE,0), NVL(LEGACY_GROUP_NUMBER,' '), NVL(LEGACY_SUBGROUP_NUMBER,' '), NVL(MAX_PER_CUSTOMER,0), NVL(CURRENCY,'US'), TXN_SN, TO_CHAR(NVL(LAST_SIZE_AND_WEIGHT_AUDIT,TO_DATE('19800101','YYYYMMDD')),'YYYYMMDD'), TO_CHAR(NVL(AVAILABLE_DATE,TO_DATE('19800101','YYYYMMDD')),'YYYYMMDD'), TO_CHAR(NVL(VEND_FUT_COST_EFFECT_DATE,TO_DATE('19800101','YYYYMMDD')),'YYYYMMDD'), TO_CHAR(NVL(CREATION_DATE,TO_DATE('19800101','YYYYMMDD')),'YYYYMMDD'), NVL(FORCED_AUTOBUY,' '), NVL(FORCED_BLOWOUT_03,0), NVL(FORCED_BLOWOUT_04,0), NVL(FORCED_BLOWOUT_05,0), NVL(FORCED_BLOWOUT_06,0), NVL(FORCED_BLOWOUT_07,0) INTO ORAINSUP , ORATYPE , ORAITEM , ORASEG2 , ORASEG3 , ORASHORT , ORALONG , ORAUOMWORD , ORAUOMQUANTITY , ORACATEGORY , ORACLASS , ORAOLDITEM , ORACONSIGNED , ORABOM , ORABUILDTOORDER , ORACPS , ORAFORVEL , ORASTATUS , ORAMFG , ORAMPN , ORAMODEL , ORASERIAL , ORASERLEN , ORADDROP , ORANEWSER , ORAPRELOAD , ORA2SERIAL , ORA2LENGTH , ORAWEIGHT , ORASHIPMETH , ORASELLTERR , ORASOURCE , ORACURA , ORANEWA , ORAVENCOST , ORAFUTVEN , ORABUY , ORAVENDOR , ORAVPN , ORACASE , ORASTORAGE , ORAFREIGHT , ORABUNDLE , ORAORIGIN , ORAHARM1 , ORAHARM2 , ORAHARM3 , ORAPREF , ORALABELED , ORAHEIGHT , ORAWIDTH , ORALENGTH , ORABARCODE , ORARENO , ORAFIFODAYS , ORAFIFO , ORACONFIGURED , ORAOWNBOX , ORASTOCK03 , ORASTOCK04 , ORASTOCK05 , ORASTOCK06 , ORASTOCK07 , ORACONSIGNFEE , ORASORT1 , ORASORT2 , ORABGINCLUDE , ORACURBG , ORANEXTBG , ORAPREVBG , ORAGROUP , ORASUB , ORAMAXPERCUS , ORACURRENCY , ORASEQ , ORAUDIT , ORAVAILABLE , ORAFUTDATE , ORAINPUTDATE , ORAFORAUTOBUY , ORAFORBLOWOUT03 , ORAFORBLOWOUT04 , ORAFORBLOWOUT05 , ORAFORBLOWOUT06 , ORAFORBLOWOUT07 FROM tessco_item_master_oesv WHERE tessco_item_master_oesv.sku = sku AND supplier_code = sup; END; END dbl_oragtwy; / SHOW ERRORS PACKAGE BODY dbl_oragtwy;