CREATE
TABLE XXCUST.XXJLN_FORMULA_HEADERS_RRRRRR
(
FORMULA_NO VARCHAR2(32 BYTE) NOT NULL,
FORMULA_VERS NUMBER DEFAULT 1 NOT NULL,
FORMULA_TYPE NUMBER(5) DEFAULT 1 NOT NULL,
FORMULA_DESC1 VARCHAR2(70 BYTE),
FORMULA_CLASS VARCHAR2(8 BYTE),
INACTIVE_IND NUMBER(5) DEFAULT 0 NOT NULL,
FORMULA_UOM VARCHAR2(4 BYTE),
FORMULA_STATUS VARCHAR2(30 BYTE) DEFAULT 100 NOT NULL,
FLAG VARCHAR2(2 BYTE),
RECORD_TYPE VARCHAR2(12 BYTE),
OWNER_ORGANIZATION_CODE VARCHAR2(10 BYTE) DEFAULT 'FMO' NOT NULL,
OWNER_NAME VARCHAR2(20 BYTE) DEFAULT '34220' NOT NULL,
LINE_TYPE
NUMBER(10),
LINE_NO INTEGER DEFAULT 1 NOT NULL,
QTY NUMBER(20,10),
RELEASE_TYPE VARCHAR2(10 BYTE),
COST_ALLOC VARCHAR2(10 BYTE),
SCALE_TYPE_HDR NUMBER(10) DEFAULT 1 NOT NULL,
DETAIL_UOM VARCHAR2(10 BYTE),
SCALE_TYPE_DTL INTEGER DEFAULT 1 NOT NULL,
PHANTOM_TYPE INTEGER DEFAULT 0 NOT NULL,
DELETE_MARK VARCHAR2(10 BYTE) DEFAULT 0 NOT NULL,
CONTRIBUTE_YIELD_IND VARCHAR2(10 BYTE) DEFAULT 'Y' NOT NULL,
INVENTORY_ITEM_CODE VARCHAR2(15 BYTE),
TYPE_OF_ITEM VARCHAR2(250 BYTE),
FILE_NAME VARCHAR2(250 BYTE),
ORG_CODE VARCHAR2(200 BYTE),
ORG_NAME VARCHAR2(300 BYTE),
NOTE VARCHAR2(250 BYTE)
)
--------------------------------PROCESS-------------------------------------
DECLARE
begin
/*
----------First
time for Item Assignmet-------------
delete
from APPS.xxpran_subinv_locator_stg_rrr
where USER_NAME='333';
commit;
INSERT
INTO
APPS.xxpran_subinv_locator_stg(ITEM_NUMBER,ORG_CODE,SUBINV_CODE,USER_NAME)
SELECT
distinct INVENTORY_ITEM_CODE,'FMO','FMO','34220'
FROM
XXCUST.XXJLN_FORMULA_HEADERS_REZA
where
FLAG is null;
commit;
delete
FROM
APPS.xxpran_subinv_locator_stg WHERE ITEM_NUMBER NOT IN (
SELECT
BB.SEGMENT1 FROM MTL_SYSTEM_ITEMS_B BB WHERE BB.ORGANIZATION_ID=102 AND
BB.SEGMENT1 IN (
SELECT
ITEM_NUMBER FROM APPS.xxpran_subinv_locator_stg
));
commit;
apps.xxpran_inv_assign('xxxxx');
*/
----------After
run one time then off this------------------
DECLARE
CURSOR c_header IS
SELECT DISTINCT formula_no
FROM
xxcust.XXJLN_FORMULA_HEADERS_REZA a
WHERE flag IS NULL
and
not exists (select 1 from
fm_form_mst b where a.FORMULA_NO = b.formula_no);
CURSOR c_master (
p_formula varchar2
--TEST_M020
) IS
SELECT *
FROM
xxcust.XXJLN_FORMULA_HEADERS_REZA --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
xxcust.XXJLN_FORMULA_HEADERS_REZA%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;
v_total_form 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
xxcust.XXJLN_FORMULA_HEADERS_REZA --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 := 'Y'; --
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
xxcust.XXJLN_FORMULA_HEADERS_REZA--mii_gmd_formula
set flag = l_return_status
--, note = l_data
WHERE formula_no = l_formula.formula_no;
ELSE
UPDATE
xxcust.XXJLN_FORMULA_HEADERS_REZA--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;
update
XXCUST.XXJLN_FORMULA_HEADERS_RRRR
set FLAG
=null
where
FLAG = 'E';
commit;
---select
count(1) into v_total_form from
XXCUST.XXJLN_FORMULA_HEADERS_REZA a where (a.flag is null or a.flag !='Y');
END;
END;
/
/
/
/
/
/
/
/
/
/
