OPM Formula Upload

CREATE TABLE APPS.JLN_FORMULA_HEADERS ( FORMULA_NO VARCHAR2(32 BYTE) NOT NULL, FORMULA_VERS NUMBER NOT NULL, FORMULA_TYPE NUMBER(5) NOT NULL, FORMULA_DESC1 VARCHAR2(70 BYTE), FORMULA_CLASS VARCHAR2(8 BYTE), INACTIVE_IND NUMBER(5) NOT NULL, FORMULA_UOM VARCHAR2(4 BYTE), FORMULA_STATUS VARCHAR2(30 BYTE), FLAG VARCHAR2(2 BYTE), RECORD_TYPE VARCHAR2(12 BYTE), OWNER_ORGANIZATION_CODE VARCHAR2(10 BYTE), OWNER_NAME VARCHAR2(20 BYTE), LINE_TYPE NUMBER(10), LINE_NO INTEGER, QTY NUMBER(20,10), RELEASE_TYPE VARCHAR2(10 BYTE), COST_ALLOC VARCHAR2(10 BYTE), SCALE_TYPE_HDR NUMBER(10), DETAIL_UOM VARCHAR2(10 BYTE), SCALE_TYPE_DTL INTEGER, PHANTOM_TYPE INTEGER, DELETE_MARK VARCHAR2(10 BYTE), CONTRIBUTE_YIELD_IND VARCHAR2(10 BYTE), INVENTORY_ITEM_CODE VARCHAR2(15 BYTE) ); Insert into JLN_FORMULA_HEADERS(FORMULA_NO, FORMULA_VERS, FORMULA_TYPE, FORMULA_DESC1, FORMULA_CLASS, INACTIVE_IND, FORMULA_UOM, FORMULA_STATUS, FLAG, RECORD_TYPE,OWNER_ORGANIZATION_CODE, OWNER_NAME, LINE_TYPE, LINE_NO, QTY, RELEASE_TYPE, COST_ALLOC, SCALE_TYPE_HDR, DETAIL_UOM, SCALE_TYPE_DTL, PHANTOM_TYPE, DELETE_MARK, CONTRIBUTE_YIELD_IND, INVENTORY_ITEM_CODE) Values ('35521',1,1,'Packaging formula for Vermicelli 200 gm x48 pcs ctn','',0,'PCS',100,'','','FMO','SETUP USER',1,1,1000,1,1,1,'PCS',1,0,0,'N','35521'); Insert into JLN_FORMULA_HEADERS(FORMULA_NO, FORMULA_VERS, FORMULA_TYPE, FORMULA_DESC1, FORMULA_CLASS, INACTIVE_IND, FORMULA_UOM, FORMULA_STATUS, FLAG, RECORD_TYPE,OWNER_ORGANIZATION_CODE, OWNER_NAME, LINE_TYPE, LINE_NO, QTY, RELEASE_TYPE, COST_ALLOC, SCALE_TYPE_HDR, DETAIL_UOM, SCALE_TYPE_DTL, PHANTOM_TYPE, DELETE_MARK, CONTRIBUTE_YIELD_IND, INVENTORY_ITEM_CODE) Values ('35521',1,1,'Packaging formula for Vermicelli 200 gm x48 pcs ctn','',0,'PCS',100,'','','FMO','SETUP USER',-1,1,244,1,1,1,'KG',1,0,0,'N','0130019210'); Insert into JLN_FORMULA_HEADERS(FORMULA_NO, FORMULA_VERS, FORMULA_TYPE, FORMULA_DESC1, FORMULA_CLASS, INACTIVE_IND, FORMULA_UOM, FORMULA_STATUS, FLAG, RECORD_TYPE,OWNER_ORGANIZATION_CODE, OWNER_NAME, LINE_TYPE, LINE_NO, QTY, RELEASE_TYPE, COST_ALLOC, SCALE_TYPE_HDR, DETAIL_UOM, SCALE_TYPE_DTL, PHANTOM_TYPE, DELETE_MARK, CONTRIBUTE_YIELD_IND, INVENTORY_ITEM_CODE) Values ('35521',1,1,'Packaging formula for Vermicelli 200 gm x48 pcs ctn','',0,'PCS',100,'','','FMO','SETUP USER',-1,1,8.196721,1,1,1,'KG',1,0,0,'N','0729424400'); Insert into JLN_FORMULA_HEADERS(FORMULA_NO, FORMULA_VERS, FORMULA_TYPE, FORMULA_DESC1, FORMULA_CLASS, INACTIVE_IND, FORMULA_UOM, FORMULA_STATUS, FLAG, RECORD_TYPE,OWNER_ORGANIZATION_CODE, OWNER_NAME, LINE_TYPE, LINE_NO, QTY, RELEASE_TYPE, COST_ALLOC, SCALE_TYPE_HDR, DETAIL_UOM, SCALE_TYPE_DTL, PHANTOM_TYPE, DELETE_MARK, CONTRIBUTE_YIELD_IND, INVENTORY_ITEM_CODE) Values ('35521',1,1,'Packaging formula for Vermicelli 200 gm x48 pcs ctn','',0,'PCS',100,'','','FMO','SETUP USER',-1,1,20.833333,1,1,1,'PCS',1,0,0,'N','0729880800'); Insert into JLN_FORMULA_HEADERS(FORMULA_NO, FORMULA_VERS, FORMULA_TYPE, FORMULA_DESC1, FORMULA_CLASS, INACTIVE_IND, FORMULA_UOM, FORMULA_STATUS, FLAG, RECORD_TYPE,OWNER_ORGANIZATION_CODE, OWNER_NAME, LINE_TYPE, LINE_NO, QTY, RELEASE_TYPE, COST_ALLOC, SCALE_TYPE_HDR, DETAIL_UOM, SCALE_TYPE_DTL, PHANTOM_TYPE, DELETE_MARK, CONTRIBUTE_YIELD_IND, INVENTORY_ITEM_CODE) Values ('35521',1,1,'Packaging formula for Vermicelli 200 gm x48 pcs ctn','',0,'PCS',100,'','','FMO','SETUP USER',-1,1,0.434028,1,1,1,'PCS',1,0,0,'N','0729203010'); Insert into JLN_FORMULA_HEADERS(FORMULA_NO, FORMULA_VERS, FORMULA_TYPE, FORMULA_DESC1, FORMULA_CLASS, INACTIVE_IND, FORMULA_UOM, FORMULA_STATUS, FLAG, RECORD_TYPE,OWNER_ORGANIZATION_CODE, OWNER_NAME, LINE_TYPE, LINE_NO, QTY, RELEASE_TYPE, COST_ALLOC, SCALE_TYPE_HDR, DETAIL_UOM, SCALE_TYPE_DTL, PHANTOM_TYPE, DELETE_MARK, CONTRIBUTE_YIELD_IND, INVENTORY_ITEM_CODE) Values ('35521',1,1,'Packaging formula for Vermicelli 200 gm x48 pcs ctn','',0,'PCS',100,'','','FMO','SETUP USER',-1,1,0.006,1,1,1,'PCS',1,0,0,'N','0729285200'); Insert into JLN_FORMULA_HEADERS(FORMULA_NO, FORMULA_VERS, FORMULA_TYPE, FORMULA_DESC1, FORMULA_CLASS, INACTIVE_IND, FORMULA_UOM, FORMULA_STATUS, FLAG, RECORD_TYPE,OWNER_ORGANIZATION_CODE, OWNER_NAME, LINE_TYPE, LINE_NO, QTY, RELEASE_TYPE, COST_ALLOC, SCALE_TYPE_HDR, DETAIL_UOM, SCALE_TYPE_DTL, PHANTOM_TYPE, DELETE_MARK, CONTRIBUTE_YIELD_IND, INVENTORY_ITEM_CODE) Values ('35521',1,1,'Packaging formula for Vermicelli 200 gm x48 pcs ctn','',0,'PCS',100,'','','FMO','SETUP USER',-1,1,0.011,1,1,1,'PCS',1,0,0,'N','0529574100'); COMMIT; DECLARE CURSOR c_header IS SELECT DISTINCT formula_no FROM JLN_formula_headers --mii_gmd_formula WHERE flag IS NULL; CURSOR c_master ( p_formula varchar2 --TEST_M020 ) IS SELECT * FROM JLN_formula_headers --mii_gmd_formula WHERE flag IS NULL AND formula_no = p_formula; l_formula_header_tbl gmd_formula_pub.formula_insert_hdr_tbl_type; l_formula JLN_formula_headers%ROWTYPE; -- mii_gmd_formula%ROWTYPE; l_count NUMBER := 0; l_loop_cnt NUMBER := 0; l_record_count NUMBER := 0; l_data VARCHAR2 (2000); l_return_status VARCHAR2 (1); l_status VARCHAR2 (1); i NUMBER := 1; l_dummy_cnt NUMBER; l_api_version NUMBER := 1; l_init_msg_list BOOLEAN; l_commit BOOLEAN; return_sts BOOLEAN; v_item_id number; v_organization_id number; v_user_id number; BEGIN --FND_GLOBAL.APPS_INITIALIZE(1112,22882,552,0); fnd_global.apps_initialize (user_id => 1110, resp_id => 50774, resp_appl_id => 555 ); -- FND_RESPONSIBILITY_VL , FND_user /*FND_GLOBAL.APPS_INITIALIZE(user_id, resp_id, resp_appl_id);*/ l_init_msg_list := TRUE; l_commit := TRUE; FOR l_header IN c_header LOOP OPEN c_master(l_header.formula_no); LOOP FETCH c_master INTO l_formula; exit WHEN c_master%NOTFOUND; dbms_output.put_line (c_master%ROWCOUNT); BEGIN SELECT DISTINCT inventory_item_id INTO v_item_id FROM mtl_system_items_b WHERE segment1 = UPPER(l_formula.inventory_item_code); SELECT organization_id INTO v_organization_id FROM mtl_parameters WHERE organization_code = l_formula.owner_organization_code; SELECT user_id INTO v_user_id FROM fnd_user WHERE user_name = l_formula.owner_name; EXCEPTION WHEN others THEN UPDATE JLN_formula_headers --mii_gmd_formula set flag = 'E' --, note = 'Ada exception' WHERE formula_no = l_formula.formula_no; END; l_formula_header_tbl (c_master%ROWCOUNT).record_type := 'I'; l_formula_header_tbl (c_master%ROWCOUNT).formula_no := UPPER(l_formula.formula_no); l_formula_header_tbl (c_master%ROWCOUNT).formula_vers := l_formula.formula_vers; l_formula_header_tbl (c_master%ROWCOUNT).formula_type := l_formula.formula_type; l_formula_header_tbl (c_master%ROWCOUNT).formula_desc1 := l_formula.formula_desc1; l_formula_header_tbl (c_master%ROWCOUNT).formula_class := l_formula.formula_class; l_formula_header_tbl (c_master%ROWCOUNT).inactive_ind := l_formula.inactive_ind; l_formula_header_tbl (c_master%ROWCOUNT).owner_organization_id := v_organization_id; l_formula_header_tbl (c_master%ROWCOUNT).formula_status := l_formula.formula_status; l_formula_header_tbl (c_master%ROWCOUNT).owner_id := v_user_id; l_formula_header_tbl (c_master%ROWCOUNT).line_type := l_formula.line_type; l_formula_header_tbl (c_master%ROWCOUNT).line_no := l_formula.line_no; l_formula_header_tbl (c_master%ROWCOUNT).inventory_item_id := v_item_id; l_formula_header_tbl (c_master%ROWCOUNT).qty := l_formula.qty; l_formula_header_tbl (c_master%ROWCOUNT).detail_uom := l_formula.detail_uom; l_formula_header_tbl (c_master%ROWCOUNT).release_type := l_formula.release_type; l_formula_header_tbl (c_master%ROWCOUNT).scale_type_hdr := l_formula.scale_type_hdr; l_formula_header_tbl (c_master%ROWCOUNT).scale_type_dtl := l_formula.scale_type_dtl; l_formula_header_tbl (c_master%ROWCOUNT).cost_alloc := l_formula.cost_alloc; l_formula_header_tbl (c_master%ROWCOUNT).CONTRIBUTE_YIELD_IND := 'N'; -- l_formula.CONTRIBUTE_YIELD_IND; l_formula_header_tbl (c_master%ROWCOUNT).PHANTOM_TYPE := l_formula.PHANTOM_TYPE; l_formula_header_tbl (c_master%ROWCOUNT).delete_mark := l_formula.delete_mark; l_formula_header_tbl (c_master%ROWCOUNT).CONTRIBUTE_STEP_QTY_IND := 'Y'; DBMS_OUTPUT.put_line ('Value Test ' || l_formula_header_tbl (1).formula_no); -- UPDATE mii_gmd_formula -- set flag = 'Y' -- WHERE formula_no = l_formula.formula_no -- AND line_no = l_formula.line_no -- AND inventory_item_code = l_formula.inventory_item_code; END LOOP; CLOSE c_master; gmd_formula_pub.insert_formula (p_api_version => 1.0, p_formula_header_tbl => l_formula_header_tbl, x_return_status => l_return_status, x_msg_count => l_count, x_msg_data => l_data ); DBMS_OUTPUT.put_line ('l_return_status:'||l_return_status); DBMS_OUTPUT.put_line ('l_data:'||l_data); IF l_return_status = 'E' OR l_return_status = 'U' THEN UPDATE JLN_formula_headers--mii_gmd_formula set flag = l_return_status --, note = l_data WHERE formula_no = l_formula.formula_no; ELSE UPDATE JLN_formula_headers--mii_gmd_formula set flag = 'Y' -- , note = l_data WHERE formula_no = l_formula.formula_no; END IF; END LOOP; /* IF l_count >= 0 THEN l_loop_cnt := 1; LOOP fnd_msg_pub.get (p_msg_index => l_loop_cnt, p_data => l_data, p_encoded => fnd_api.g_false, p_msg_index_out => l_dummy_cnt ); DBMS_OUTPUT.put_line ('Record = ' || l_loop_cnt); DBMS_OUTPUT.put_line (l_data); IF l_status = 'E' OR l_status = 'U' THEN l_data := CONCAT ('ERROR ', l_data); END IF; DBMS_OUTPUT.put_line (l_data); IF (l_status = 'U') THEN l_return_status := l_status; ELSIF (l_status = 'E' AND l_return_status <> 'U') THEN l_return_status := l_status; ELSE l_return_status := l_status; END IF; l_loop_cnt := l_loop_cnt + 1; IF l_loop_cnt > l_count THEN EXIT; END IF; END LOOP; END IF;*/ COMMIT; END; --fm_form_mst b, fm_form_mst_tl