Home » RDBMS Server » Performance Tuning » Query in Loop causing performance issue (11i)
 Query in Loop causing performance issue [message #625557] |
Fri, 10 October 2014 02:27  |
 |
nishantranjan00787
Messages: 36
Registered: July 2014
Location: india
|
Member |
|
|
There is one query which is running in loop and causing performance issue.It is running forever in test instance and when we pulled out the trace we found that culprit.
SELECT COUNT (1)
FROM
XXPO_BOM_COMPS_REG WHERE SEQUENCE_ID = :B1 AND FLAG IN ('N', 'EXP')
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 79058 2.44 2.47 0 0 0 0
Fetch 79057 167081.03 167152.68 28424 678930959 0 79057
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 158116 167083.47 167155.16 28424 678930959 0 79057
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: 173 (APPS) (recursive depth: 1)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 SORT AGGREGATE (cr=8557 pr=8548 pw=0 time=12381691 us)
3830265 3830265 3830265 INLIST ITERATOR (cr=8557 pr=8548 pw=0 time=12011422 us)
3830265 3830265 3830265 INDEX RANGE SCAN XXPO_BOM_COMPS_REG_N222 (cr=8557 pr=8548 pw=0 time=11092233 us cost=2639 size=8851833 card=983537)(object id 37592234)
The query is
BEGIN
v_num_ite_count := 0;
LOOP -- infinite loop
-- INITIAL RECORD SET USING LOOP TO SET Y AND N
FOR cur_update_flag_rec IN
cur_update_flag_1 (v_num_sequence_id) -- to fetch all Ns
LOOP
IF cur_update_flag_rec.sno = 1
THEN
IF (cur_update_flag_rec.comp_user_item_type IN
('EMM', 'SA', 'CONFIGURED_ITEM')
)
THEN
v_chr_flag := 'EXP';
ELSIF cur_update_flag_rec.comp_user_item_type IN
('P', 'EMR_PURCH')
THEN
/* Query to check if the BOM exists for the parts with item type Purchased Item*/
BEGIN
SELECT COUNT (1)
INTO v_num_count_bom_first
FROM bom_bill_of_materials a,
bom_inventory_components b
WHERE a.assembly_item_id =
cur_update_flag_rec.component_item_id
AND a.organization_id = i_num_organization_id
AND b.bill_sequence_id = a.common_bill_sequence_id;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG,
'error while checking bom exists for the component'
);
END;
IF v_num_count_bom_first > 0
THEN
v_chr_flag := 'EXP';
ELSE
v_chr_flag := 'PRINT';
END IF;
ELSE
v_chr_flag := 'Y';
END IF;
ELSE
-- check on BOM of the Item (form 2nd level onwards)
BEGIN
SELECT COUNT (1)
INTO v_num_count_bom
FROM bom_bill_of_materials a,
bom_inventory_components b
WHERE a.assembly_item_id =
cur_update_flag_rec.component_item_id
AND a.organization_id = i_num_organization_id
AND b.bill_sequence_id = a.common_bill_sequence_id;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG,
'error while checking bom exists for the component'
);
END;
IF v_num_count_bom > 0
THEN
v_chr_flag := 'EXP';
ELSE
v_chr_flag := 'PRINT';
END IF;
--commentd by REGS dated 11/10/2012
-- Logic to update the duplicate records with flag = 'Y'
IF v_chr_flag = 'PRINT'
THEN
fnd_file.put_line (fnd_file.LOG,
'IF FLAG PRINT ' || V_CHR_FLAG
);
-- Query to check if any duplicate records are getting inserted
BEGIN
SELECT COUNT (1)
INTO v_num_count_fir_lvl
FROM xxpo_bom_comps_reg
WHERE assembly_item_id =
cur_update_flag_rec.assembly_item_id
AND component_item_id =
cur_update_flag_rec.component_item_id
AND top_assembly_item_id =
cur_update_flag_rec.top_assembly_item_id
AND line_number = cur_update_flag_rec.line_number ---- ADDED AS PER SR#1051394 BY INFOSYS ON 02-NOV-2012
AND flag = 'PRINT';
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
-- If the record already exists with flag = 'PRINT' update new record with flag = 'Y'
IF v_num_count_fir_lvl > 0
THEN
v_chr_flag := 'Y';
END IF;
END IF;
END IF;
BEGIN
/* Updating the custom table with the flag*/
UPDATE xxpo_bom_comps_reg
SET flag = v_chr_flag,
line_number = i_num_line_number
WHERE ROWID = cur_update_flag_rec.rowid_num;
fnd_file.put_line
(fnd_file.LOG,
'Exception updating successful records-'
|| i_num_line_number || ',' || v_chr_flag || ',' || cur_update_flag_rec.rowid_num
);
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG,
'Exception updating successful records-'
|| SQLERRM
);
END;
END LOOP; --cur_update_flag_rec IN cur_update_flag_1
FOR rec_assembly_items IN cur_assembly_items (v_num_sequence_id)
-- fetch all remaing EXP records
LOOP
v_chr_exp_flag := 'EXP_NOT';
v_chr_embase := rec_assembly_items.em_base;
IF rec_assembly_items.comp_user_item_type = 'CONFIGURED_ITEM'
THEN
v_num_sequence_id_model := v_num_sequence_id + 1;
v_chr_flag := 'Y';
fnd_file.put_line (fnd_file.LOG,
'IF FLAG ITEM TYPE ' || rec_assembly_items.comp_user_item_type);
BEGIN
/* Updating the custom table with the flag*/
UPDATE xxpo_bom_comps_reg
SET flag = v_chr_flag,
line_number = i_num_line_number
WHERE ROWID = rec_assembly_items.rowid_num;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG,
'Exception updating successful records-'
|| SQLERRM
);
END;
v_num_sequence_id_bef_exp := v_num_sequence_id;
/* Calling the procedure to explode the configured item if exists any*/
explode_bom (v_chr_errbuff,
v_num_retcode,
i_num_request_id,
i_num_organization_id,
rec_assembly_items.component_item_id,
i_num_line_number,
v_num_sequence_id_model
);
COMMIT;
v_num_sequence_id := v_num_sequence_id_bef_exp;
ELSE
FOR rec_item_type_seg IN
cur_item_type_seg (rec_assembly_items.component_item_id,
rec_assembly_items.organization_id
)
LOOP
BEGIN
INSERT INTO xxpo_bom_comps_reg
(sno, assembly_item_id,
assembly,
asmbly_user_item_type,
asmbly_bom_item_type,
component_item_id,
component,
comp_user_item_type,
comp_bom_item_type,
quantity, flag,
line_number,
top_assembly,
top_assembly_item_id,
top_asmbly_user_item_type,
top_asmbly_bom_item_type,
organization_id,
mrp_plan,
created_by, creation_date,
last_updated_by, last_updated_date,
last_updated_login, request_id,
program_application_id,
program_id,
program_update_date, em_base,
sequence_id
)
VALUES (2, rec_item_type_seg.assembly_item_id,
rec_item_type_seg.assembly,
rec_item_type_seg.asmbly_user_item_type,
rec_item_type_seg.asmbly_bom_item_type,
rec_item_type_seg.component_item_id,
rec_item_type_seg.component,
rec_item_type_seg.comp_user_item_type,
rec_item_type_seg.comp_bom_item_type,
rec_item_type_seg.quantity, 'N',
i_num_line_number,
rec_assembly_items.top_assembly,
rec_assembly_items.top_assembly_item_id,
rec_assembly_items.top_asmbly_user_item_type,
rec_assembly_items.top_asmbly_bom_item_type,
rec_assembly_items.organization_id,
rec_assembly_items.mrp_plan,
fnd_global.user_id, TRUNC (SYSDATE),
fnd_global.user_id, TRUNC (SYSDATE),
fnd_global.login_id, i_num_request_id,
fnd_global.prog_appl_id,
fnd_global.conc_program_id,
TRUNC (SYSDATE), v_chr_embase,
v_num_sequence_id
);
v_chr_exp_flag := 'EXP_DONE';
fnd_file.put_line (fnd_file.LOG,
'IF' || v_chr_exp_flag);
fnd_file.put_line (fnd_file.LOG,
'AFTER INSERT' || rec_item_type_seg.assembly_item_id);
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'---ERROR 1---' || SQLERRM
);
END;
END LOOP;
END IF;
BEGIN
UPDATE xxpo_bom_comps_reg
SET flag = v_chr_exp_flag,
line_number = i_num_line_number
WHERE ROWID = rec_assembly_items.rowid_num;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'ERROR WHILE UPDATING FLAG '
|| SQLERRM
);
END;
END LOOP; --rec_assembly_items IN cur_assembly_items
COMMIT;
BEGIN
v_num_no_count := 0;
/* Count to check if there exists any record which needs to be exploded*/
SELECT COUNT (1)
INTO v_num_no_count
FROM xxpo_bom_comps_reg
WHERE sequence_id = v_num_sequence_id AND flag IN
('N', 'EXP');
fnd_file.put_line (fnd_file.LOG,
';;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;'
);
fnd_file.put_line (fnd_file.LOG,
'v_num_no_count :=' || v_num_no_count
);
fnd_file.put_line (fnd_file.LOG,
';;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;'
);
EXCEPTION
WHEN OTHERS
THEN
v_num_no_count := 0;
END;
EXIT WHEN v_num_no_count = 0;
END LOOP;
-- Infinite loop which exist when all the records in
--custom table are 'Y'
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'Exception in the Main Package-' || SQLERRM
);
END;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'Exception in the Main Package-' || SQLERRM
);
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Query in Loop causing performance issue [message #626348 is a reply to message #626341] |
Mon, 27 October 2014 01:01  |
|
nishantranjan00787
Messages: 36
Registered: July 2014
Location: india
|
Member |
|
|
Hi Kevin,
I was not hiding the sql.The thing is it's in loop.so i just placed that part.Below is the full SQL
PROCEDURE explode_bom (
out_errbuf OUT VARCHAR2,
out_retcode OUT NUMBER,
i_num_request_id IN NUMBER,
i_num_organization_id IN NUMBER,
i_num_item_id IN NUMBER,
i_num_line_number IN NUMBER,
i_num_sequence_id IN NUMBER
)
IS
v_chr_flag VARCHAR2 (10);
v_chr_errbuff VARCHAR2 (60);
v_num_retcode NUMBER;
v_num_no_count NUMBER := 0;
v_num_ite_count NUMBER := 0;
v_chr_exp_flag VARCHAR2 (10);
v_num_count_bom NUMBER := 0;
v_num_count_fir_lvl NUMBER := 0;
v_num_count_bom_first NUMBER := 0;
v_chr_embase VARCHAR2 (40);
v_num_sequence_id NUMBER := 0;
v_num_sequence_id_model NUMBER := 0;
v_num_sequence_id_bef_exp NUMBER := 0;
/* Cursor to fetch the items which got inserted in to table for the first time */
CURSOR cur_update_flag_1 (c_num_sequence_id NUMBER)
IS
SELECT ROWID rowid_num, xbcr.*
FROM xxpo_bom_comps_reg xbcr
WHERE xbcr.flag = 'N'
AND xbcr.request_id = i_num_request_id
AND xbcr.sequence_id = c_num_sequence_id
ORDER BY xbcr.component_item_id, xbcr.sno;
/* Cursor to fetch the items which needs to be exploded */
CURSOR cur_assembly_items (c_num_sequence_id NUMBER)
IS
SELECT ROWID rowid_num, xbcr.*
FROM xxpo_bom_comps_reg xbcr
WHERE xbcr.flag = 'EXP'
AND xbcr.request_id = i_num_request_id
AND xbcr.sequence_id = c_num_sequence_id
ORDER BY xbcr.component_item_id, xbcr.sno;
/* Cursor to check if BOM exists for the item to be exploded */
CURSOR cur_item_type_seg (i_num_assembly_item_id NUMBER, i_org_id NUMBER)
IS
SELECT msi1.inventory_item_id assembly_item_id,
msi1.segment1 assembly, msi1.item_type asmbly_user_item_type,
msi1.bom_item_type asmbly_bom_item_type,
msi2.inventory_item_id component_item_id,
msi2.segment1 component, msi2.item_type comp_user_item_type,
msi2.bom_item_type comp_bom_item_type,
bic.component_quantity quantity, 'N' flag,
i_num_organization_id organization_id, 'MRP' mrp_plan
FROM bom_bill_of_materials bom,
mtl_system_items_b msi1,
bom_inventory_components bic,
mtl_system_items_b msi2
WHERE bom.organization_id = msi1.organization_id
AND msi1.inventory_item_id = bom.assembly_item_id
AND bic.bill_sequence_id = bom.common_bill_sequence_id
AND msi2.inventory_item_id = bic.component_item_id
AND msi2.organization_id = msi1.organization_id
AND bom.assembly_item_id = i_num_assembly_item_id
AND bic.implementation_date IS NOT NULL
-- AND bic.disable_date IS NULL HPOV 379694
AND bom.alternate_bom_designator IS NULL
AND bom.organization_id = i_org_id;
BEGIN
-- Getting the Components for the assembly
BEGIN
IF i_num_sequence_id = 0
THEN
v_num_sequence_id := 1;
ELSE
v_num_sequence_id := i_num_sequence_id;
END IF;
/* Calling the Insert procedure to insert the items into the table */
xxpo_bom_comps_insert_reg (v_chr_errbuff,
v_num_retcode,
1,
i_num_organization_id,
i_num_item_id,
i_num_line_number, -- ADDED AS PER SR#1051394 BY INFOSYS ON 02-NOV-2012
i_num_request_id,
v_num_sequence_id
);
/* Updating the custom table with the EM_BASe fetched form the function*/
UPDATE xxpo_bom_comps_reg xbcr
SET em_base =
xxpo_embase_fun_reg (xbcr.component_item_id,
xbcr.organization_id,
xbcr.comp_user_item_type
)
WHERE xbcr.comp_user_item_type = 'EMM';
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'Exception in the Insert Statement-' || SQLERRM
);
END;
COMMIT;
BEGIN
v_num_ite_count := 0;
LOOP -- infinite loop
-- INITIAL RECORD SET USING LOOP TO SET Y AND N
FOR cur_update_flag_rec IN
cur_update_flag_1 (v_num_sequence_id) -- to fetch all Ns
LOOP
IF cur_update_flag_rec.sno = 1
THEN
IF (cur_update_flag_rec.comp_user_item_type IN
('EMM', 'SA', 'CONFIGURED_ITEM')
)
THEN
v_chr_flag := 'EXP';
ELSIF cur_update_flag_rec.comp_user_item_type IN
('P', 'EMR_PURCH')
THEN
/* Query to check if the BOM exists for the parts with item type Purchased Item*/
BEGIN
SELECT COUNT (1)
INTO v_num_count_bom_first
FROM bom_bill_of_materials a,
bom_inventory_components b
WHERE a.assembly_item_id =
cur_update_flag_rec.component_item_id
AND a.organization_id = i_num_organization_id
AND b.bill_sequence_id = a.common_bill_sequence_id;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG,
'error while checking bom exists for the component'
);
END;
IF v_num_count_bom_first > 0
THEN
v_chr_flag := 'EXP';
ELSE
v_chr_flag := 'PRINT';
END IF;
ELSE
v_chr_flag := 'Y';
END IF;
ELSE
-- check on BOM of the Item (form 2nd level onwards)
BEGIN
SELECT COUNT (1)
INTO v_num_count_bom
FROM bom_bill_of_materials a,
bom_inventory_components b
WHERE a.assembly_item_id =
cur_update_flag_rec.component_item_id
AND a.organization_id = i_num_organization_id
AND b.bill_sequence_id = a.common_bill_sequence_id;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG,
'error while checking bom exists for the component'
);
END;
IF v_num_count_bom > 0
THEN
v_chr_flag := 'EXP';
ELSE
v_chr_flag := 'PRINT';
END IF;
--commentd by REGS dated 11/10/2012
-- Logic to update the duplicate records with flag = 'Y'
IF v_chr_flag = 'PRINT'
THEN
fnd_file.put_line (fnd_file.LOG,
'IF FLAG PRINT ' || V_CHR_FLAG
);
-- Query to check if any duplicate records are getting inserted
BEGIN
SELECT COUNT (1)
INTO v_num_count_fir_lvl
FROM xxpo_bom_comps_reg
WHERE assembly_item_id =
cur_update_flag_rec.assembly_item_id
AND component_item_id =
cur_update_flag_rec.component_item_id
AND top_assembly_item_id =
cur_update_flag_rec.top_assembly_item_id
AND line_number = cur_update_flag_rec.line_number ---- ADDED AS PER SR#1051394 BY INFOSYS ON 02-NOV-2012
AND flag = 'PRINT';
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
-- If the record already exists with flag = 'PRINT' update new record with flag = 'Y'
IF v_num_count_fir_lvl > 0
THEN
v_chr_flag := 'Y';
END IF;
END IF;
END IF;
BEGIN
/* Updating the custom table with the flag*/
UPDATE xxpo_bom_comps_reg
SET flag = v_chr_flag,
line_number = i_num_line_number
WHERE ROWID = cur_update_flag_rec.rowid_num;
fnd_file.put_line
(fnd_file.LOG,
'Exception updating successful records-'
|| i_num_line_number || ',' || v_chr_flag || ',' || cur_update_flag_rec.rowid_num
);
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG,
'Exception updating successful records-'
|| SQLERRM
);
END;
END LOOP; --cur_update_flag_rec IN cur_update_flag_1
FOR rec_assembly_items IN cur_assembly_items (v_num_sequence_id)
-- fetch all remaing EXP records
LOOP
v_chr_exp_flag := 'EXP_NOT';
v_chr_embase := rec_assembly_items.em_base;
IF rec_assembly_items.comp_user_item_type = 'CONFIGURED_ITEM'
THEN
v_num_sequence_id_model := v_num_sequence_id + 1;
v_chr_flag := 'Y';
fnd_file.put_line (fnd_file.LOG,
'IF FLAG ITEM TYPE ' || rec_assembly_items.comp_user_item_type);
BEGIN
/* Updating the custom table with the flag*/
UPDATE xxpo_bom_comps_reg
SET flag = v_chr_flag,
line_number = i_num_line_number
WHERE ROWID = rec_assembly_items.rowid_num;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG,
'Exception updating successful records-'
|| SQLERRM
);
END;
v_num_sequence_id_bef_exp := v_num_sequence_id;
/* Calling the procedure to explode the configured item if exists any*/
explode_bom (v_chr_errbuff,
v_num_retcode,
i_num_request_id,
i_num_organization_id,
rec_assembly_items.component_item_id,
i_num_line_number,
v_num_sequence_id_model
);
COMMIT;
v_num_sequence_id := v_num_sequence_id_bef_exp;
ELSE
FOR rec_item_type_seg IN
cur_item_type_seg (rec_assembly_items.component_item_id,
rec_assembly_items.organization_id
)
LOOP
BEGIN
INSERT INTO xxpo_bom_comps_reg
(sno, assembly_item_id,
assembly,
asmbly_user_item_type,
asmbly_bom_item_type,
component_item_id,
component,
comp_user_item_type,
comp_bom_item_type,
quantity, flag,
line_number,
top_assembly,
top_assembly_item_id,
top_asmbly_user_item_type,
top_asmbly_bom_item_type,
organization_id,
mrp_plan,
created_by, creation_date,
last_updated_by, last_updated_date,
last_updated_login, request_id,
program_application_id,
program_id,
program_update_date, em_base,
sequence_id
)
VALUES (2, rec_item_type_seg.assembly_item_id,
rec_item_type_seg.assembly,
rec_item_type_seg.asmbly_user_item_type,
rec_item_type_seg.asmbly_bom_item_type,
rec_item_type_seg.component_item_id,
rec_item_type_seg.component,
rec_item_type_seg.comp_user_item_type,
rec_item_type_seg.comp_bom_item_type,
rec_item_type_seg.quantity, 'N',
i_num_line_number,
rec_assembly_items.top_assembly,
rec_assembly_items.top_assembly_item_id,
rec_assembly_items.top_asmbly_user_item_type,
rec_assembly_items.top_asmbly_bom_item_type,
rec_assembly_items.organization_id,
rec_assembly_items.mrp_plan,
fnd_global.user_id, TRUNC (SYSDATE),
fnd_global.user_id, TRUNC (SYSDATE),
fnd_global.login_id, i_num_request_id,
fnd_global.prog_appl_id,
fnd_global.conc_program_id,
TRUNC (SYSDATE), v_chr_embase,
v_num_sequence_id
);
v_chr_exp_flag := 'EXP_DONE';
fnd_file.put_line (fnd_file.LOG,
'IF' || v_chr_exp_flag);
fnd_file.put_line (fnd_file.LOG,
'AFTER INSERT' || rec_item_type_seg.assembly_item_id);
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'---ERROR 1---' || SQLERRM
);
END;
END LOOP;
END IF;
BEGIN
UPDATE xxpo_bom_comps_reg
SET flag = v_chr_exp_flag,
line_number = i_num_line_number
WHERE ROWID = rec_assembly_items.rowid_num;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'ERROR WHILE UPDATING FLAG '
|| SQLERRM
);
END;
END LOOP; --rec_assembly_items IN cur_assembly_items
COMMIT;
BEGIN
v_num_no_count := 0;
/* Count to check if there exists any record which needs to be exploded*/
SELECT COUNT (1)
INTO v_num_no_count
FROM xxpo_bom_comps_reg
WHERE sequence_id = v_num_sequence_id AND flag IN
('N', 'EXP');
fnd_file.put_line (fnd_file.LOG,
';;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;'
);
fnd_file.put_line (fnd_file.LOG,
'v_num_no_count :=' || v_num_no_count
);
fnd_file.put_line (fnd_file.LOG,
';;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;'
);
EXCEPTION
WHEN OTHERS
THEN
v_num_no_count := 0;
END;
EXIT WHEN v_num_no_count = 0;
END LOOP;
-- Infinite loop which exist when all the records in
--custom table are 'Y'
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'Exception in the Main Package-' || SQLERRM
);
END;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'Exception in the Main Package-' || SQLERRM
);
END explode_bom;
PROCEDURE explode_bom_model (
out_errbuf OUT VARCHAR2,
out_retcode OUT NUMBER,
i_num_request_id IN NUMBER,
i_num_organization_id IN NUMBER,
i_num_item_id IN NUMBER,
i_num_line_number IN NUMBER,
i_num_sequence_id IN NUMBER
)
IS
v_chr_flag VARCHAR2 (10);
v_chr_errbuff VARCHAR2 (60);
v_num_retcode NUMBER;
v_num_no_count NUMBER := 0;
v_num_ite_count NUMBER := 0;
v_chr_exp_flag VARCHAR2 (10);
v_num_count_fir_lvl NUMBER := 0;
v_num_count_bom_first NUMBER := 0;
v_chr_embase VARCHAR2 (40);
v_num_sequence_id NUMBER := 0;
v_num_sequence_id_model NUMBER := 0;
v_num_sequence_id_bef_exp NUMBER := 0;
/* Cursor to fetch the items which got inserted in to table for the first time */
CURSOR cur_update_flag_1 (c_num_sequence_id NUMBER)
IS
SELECT ROWID rowid_num, xbcr.*
FROM xxpo_bom_comps_reg xbcr
WHERE flag = 'N'
AND request_id = i_num_request_id
AND sequence_id = c_num_sequence_id
ORDER BY component_item_id, sno;
/* Cursor to fetch the items which needs to be exploded */
CURSOR cur_assembly_items (c_num_sequence_id NUMBER)
IS
SELECT ROWID rowid_num, xbcr.*
FROM xxpo_bom_comps_reg xbcr
WHERE flag = 'EXP'
AND request_id = i_num_request_id
AND sequence_id = c_num_sequence_id
ORDER BY component_item_id, sno;
/* Cursor to check if BOM exists for the item to be exploded */
CURSOR cur_item_type_seg (i_num_assembly_item_id NUMBER, i_org_id NUMBER)
IS
SELECT msi1.inventory_item_id assembly_item_id,
msi1.segment1 assembly, msi1.item_type asmbly_user_item_type,
msi1.bom_item_type asmbly_bom_item_type,
msi2.inventory_item_id component_item_id,
msi2.segment1 component, msi2.item_type comp_user_item_type,
msi2.bom_item_type comp_bom_item_type,
bic.component_quantity quantity, 'N' flag,
i_num_organization_id organization_id, 'MRP' mrp_plan
FROM bom_bill_of_materials bom,
mtl_system_items_b msi1,
bom_inventory_components bic,
mtl_system_items_b msi2
WHERE bom.organization_id = msi1.organization_id
AND msi1.inventory_item_id = bom.assembly_item_id
AND bic.bill_sequence_id = bom.common_bill_sequence_id
AND msi2.inventory_item_id = bic.component_item_id
AND msi2.organization_id = msi1.organization_id
AND bom.assembly_item_id = i_num_assembly_item_id
AND bic.implementation_date IS NOT NULL
-- AND bic.disable_date IS NULL HPOV 379694
AND bom.alternate_bom_designator IS NULL
AND bom.organization_id = i_org_id;
BEGIN
-- Getting the Components for the assembly
BEGIN
IF i_num_sequence_id = 0
THEN
v_num_sequence_id := 1;
ELSE
v_num_sequence_id := i_num_sequence_id;
END IF;
/* Calling the Insert procedure to insert the items into the table */
xxpo_bom_comps_insert_reg (v_chr_errbuff,
v_num_retcode,
1,
i_num_organization_id,
i_num_item_id,
i_num_line_number, -- ADDED AS PER SR#1051394 BY INFOSYS ON 02-NOV-2012
i_num_request_id,
v_num_sequence_id
);
/* Updating the Custom table EM_BASE column for all the EM Bases*/
UPDATE xxpo_bom_comps_reg xbcr
SET em_base = xbcr.component
WHERE xbcr.comp_user_item_type = 'EMB'
AND request_id = i_num_request_id
AND sequence_id = v_num_sequence_id;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'Exception in the Insert Statement-' || SQLERRM
);
END;
COMMIT;
BEGIN
v_num_ite_count := 0;
LOOP -- infinite loop
-- INITIAL RECORD SET USING LOOP TO SET Y AND N
FOR cur_update_flag_rec IN
cur_update_flag_1 (v_num_sequence_id) -- to fetch all Ns
LOOP
/* Cursor to check if BOM exists for the item to be exploded */
BEGIN
SELECT COUNT (1)
INTO v_num_count_bom_first
FROM bom_bill_of_materials a, bom_inventory_components b
WHERE a.assembly_item_id =
cur_update_flag_rec.component_item_id
AND a.organization_id = i_num_organization_id
AND b.bill_sequence_id = a.common_bill_sequence_id;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG,
'error while checking bom exists for the component'
);
END;
IF v_num_count_bom_first > 0
THEN
v_chr_flag := 'EXP';
ELSE
v_chr_flag := 'PRINT';
END IF;
-- Logic to update the duplicate records with flag = 'Y'
IF v_chr_flag = 'PRINT'
THEN
BEGIN
SELECT COUNT (1)
INTO v_num_count_fir_lvl
FROM xxpo_bom_comps_reg
WHERE assembly_item_id =
cur_update_flag_rec.assembly_item_id
AND component_item_id =
cur_update_flag_rec.component_item_id
AND top_assembly_item_id =
cur_update_flag_rec.top_assembly_item_id
AND line_number = cur_update_flag_rec.line_number -- ADDED AS PER SR#1051394 BY INFOSYS ON 02-NOV-2012
AND flag = 'PRINT';
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
-- If the record already exists with flag = 'PRINT' update new record with flag = 'Y'
IF v_num_count_fir_lvl > 0
THEN
v_chr_flag := 'Y';
END IF;
END IF;
BEGIN
/* Updating the custom table with appropriate print flag*/
UPDATE xxpo_bom_comps_reg
SET flag = v_chr_flag,
line_number = i_num_line_number
WHERE ROWID = cur_update_flag_rec.rowid_num;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG,
'Exception updating successful records-'
|| SQLERRM
);
END;
END LOOP; --cur_update_flag_rec IN cur_update_flag_1
FOR rec_assembly_items IN cur_assembly_items (v_num_sequence_id)
-- fetch all remaing EXP records
LOOP
v_chr_exp_flag := 'EXP_NOT';
v_chr_embase := rec_assembly_items.em_base;
IF rec_assembly_items.comp_user_item_type = 'ATO'
THEN
v_num_sequence_id_model := v_num_sequence_id + 1;
v_chr_flag := 'Y';
BEGIN
/* Updating the custom table with appropriate print flag*/
UPDATE xxpo_bom_comps_reg
SET flag = v_chr_flag,
line_number = i_num_line_number
WHERE ROWID = rec_assembly_items.rowid_num;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG,
'Exception updating successful records-'
|| SQLERRM
);
END;
v_num_sequence_id_bef_exp := v_num_sequence_id;
/* Calling the procedure to explode the model item if exists any*/
explode_bom_model (v_chr_errbuff,
v_num_retcode,
i_num_request_id,
i_num_organization_id,
rec_assembly_items.component_item_id,
i_num_line_number,
v_num_sequence_id_model
);
COMMIT;
v_num_sequence_id := v_num_sequence_id_bef_exp;
ELSE
FOR rec_item_type_seg IN
cur_item_type_seg (rec_assembly_items.component_item_id,
rec_assembly_items.organization_id
)
LOOP
BEGIN
INSERT INTO xxpo_bom_comps_reg
(sno, assembly_item_id,
assembly,
asmbly_user_item_type,
asmbly_bom_item_type,
component_item_id,
component,
comp_user_item_type,
comp_bom_item_type,
quantity, flag,
line_number,
top_assembly,
top_assembly_item_id,
top_asmbly_user_item_type,
top_asmbly_bom_item_type,
organization_id,
mrp_plan,
created_by, creation_date,
last_updated_by, last_updated_date,
last_updated_login, request_id,
program_application_id,
program_id,
program_update_date, em_base,
sequence_id
)
VALUES (2, rec_item_type_seg.assembly_item_id,
rec_item_type_seg.assembly,
rec_item_type_seg.asmbly_user_item_type,
rec_item_type_seg.asmbly_bom_item_type,
rec_item_type_seg.component_item_id,
rec_item_type_seg.component,
rec_item_type_seg.comp_user_item_type,
rec_item_type_seg.comp_bom_item_type,
rec_item_type_seg.quantity, 'N',
i_num_line_number,
rec_assembly_items.top_assembly,
rec_assembly_items.top_assembly_item_id,
rec_assembly_items.top_asmbly_user_item_type,
rec_assembly_items.top_asmbly_bom_item_type,
rec_assembly_items.organization_id,
rec_assembly_items.mrp_plan,
fnd_global.user_id, TRUNC (SYSDATE),
fnd_global.user_id, TRUNC (SYSDATE),
fnd_global.login_id, i_num_request_id,
fnd_global.prog_appl_id,
fnd_global.conc_program_id,
TRUNC (SYSDATE), v_chr_embase,
v_num_sequence_id
);
v_chr_exp_flag := 'EXP_DONE';
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'---ERROR 1---' || SQLERRM
);
END;
END LOOP;
END IF;
fnd_file.put_line (fnd_file.LOG,'LOG10:' ||i_num_line_number || ' '|| v_chr_exp_flag);
--rec_item_type_seg IN cur_item_type_seg( rec_assembly_items.assembly_item_id,
BEGIN
UPDATE xxpo_bom_comps_reg
SET flag = v_chr_exp_flag,
line_number = i_num_line_number
WHERE ROWID = rec_assembly_items.rowid_num;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'ERROR WHILE UPDATING FLAG '
|| SQLERRM
);
END;
END LOOP; --rec_assembly_items IN cur_assembly_items
COMMIT;
BEGIN
v_num_no_count := 0;
/* Count to check if there exists any record which needs to be exploded*/
SELECT COUNT (1)
INTO v_num_no_count
FROM xxpo_bom_comps_reg
WHERE sequence_id = v_num_sequence_id AND flag IN
('N', 'EXP');
fnd_file.put_line (fnd_file.LOG,
';;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;'
);
fnd_file.put_line (fnd_file.LOG,
'v_num_no_count :=' || v_num_no_count
);
fnd_file.put_line (fnd_file.LOG,
';;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;'
);
EXCEPTION
WHEN OTHERS
THEN
v_num_no_count := 0;
END;
EXIT WHEN v_num_no_count = 0;
END LOOP;
-- Infinite loop which exist when all the records in
--custom table are 'Y'
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'Exception in the Main Package-' || SQLERRM
);
END;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'Exception in the Main Package-' || SQLERRM
);
END explode_bom_model;
PROCEDURE xxpo_bom_comps_insert_reg (
out_errbuf OUT VARCHAR2,
out_retcode OUT NUMBER,
i_num_seq IN NUMBER,
i_num_organization_id IN NUMBER,
i_num_item_id IN NUMBER,
i_num_line_number IN NUMBER, -- ADDED AS PER SR#1051394 BY INFOSYS ON 02-NOV-2012
i_num_request_id IN NUMBER,
i_num_sequence_id IN NUMBER
)
IS
-- ADDED AS PER SR#1051394 BY INFOSYS ON 02-NOV-2012
v_num_count NUMBER :=0;
BEGIN
SELECT COUNT(1)
INTO v_num_count
FROM bom_bill_of_materials
WHERE assembly_item_id = i_num_item_id
AND organization_id IN (select organization_id from mtl_parameters
where organization_code in ('FRX','MCK','MPW'));
IF v_num_count = 0
THEN
BEGIN
INSERT INTO xxpo_bom_comps_reg
(sno, LINE_NUMBER,assembly_item_id, assembly, asmbly_user_item_type,
asmbly_bom_item_type, component_item_id, component,
comp_user_item_type, comp_bom_item_type,flag,
top_assembly, top_assembly_item_id,
top_asmbly_user_item_type, top_asmbly_bom_item_type,
organization_id, mrp_plan, created_by, creation_date,
last_updated_by, last_updated_date, last_updated_login,
request_id, program_application_id, program_id,
program_update_date, sequence_id)
SELECT i_num_seq,i_num_LINE_NUMBER ,msi1.inventory_item_id, msi1.segment1,
msi1.item_type, msi1.bom_item_type, msi1.inventory_item_id,
msi1.segment1, msi1.item_type, msi1.bom_item_type,
'N', msi1.segment1,msi1.inventory_item_id, msi1.item_type, msi1.bom_item_type,
i_num_organization_id, 'MRP', fnd_global.user_id,
TRUNC (SYSDATE), fnd_global.user_id, TRUNC (SYSDATE),
fnd_global.login_id, i_num_request_id,
fnd_global.prog_appl_id, fnd_global.conc_program_id,
TRUNC (SYSDATE), i_num_sequence_id
FROM mtl_system_items_b msi1
WHERE organization_id = i_num_organization_id
AND inventory_item_id = i_num_item_id;
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'Error in inserting the records in xxpo_bom_comps_reg' || SQLERRM
);
END;
ELSE
-- ADDED AS PER SR#1051394 BY INFOSYS ON 02-NOV-2012 TILL HERE
BEGIN
INSERT INTO xxpo_bom_comps_reg
(sno, LINE_NUMBER,assembly_item_id, assembly, asmbly_user_item_type,
asmbly_bom_item_type, component_item_id, component,
comp_user_item_type, comp_bom_item_type, quantity, flag,
top_assembly, top_assembly_item_id,
top_asmbly_user_item_type, top_asmbly_bom_item_type,
organization_id, mrp_plan, created_by, creation_date,
last_updated_by, last_updated_date, last_updated_login,
request_id, program_application_id, program_id,
program_update_date, sequence_id)
SELECT i_num_seq,i_num_LINE_NUMBER ,msi1.inventory_item_id, msi1.segment1,
msi1.item_type, msi1.bom_item_type, msi2.inventory_item_id,
msi2.segment1, msi2.item_type, msi2.bom_item_type,
bic.component_quantity, 'N', msi1.segment1,
msi1.inventory_item_id, msi1.item_type, msi1.bom_item_type,
i_num_organization_id, 'MRP', fnd_global.user_id,
TRUNC (SYSDATE), fnd_global.user_id, TRUNC (SYSDATE),
fnd_global.login_id, i_num_request_id,
fnd_global.prog_appl_id, fnd_global.conc_program_id,
TRUNC (SYSDATE), i_num_sequence_id
FROM bom_bill_of_materials bom,
mtl_system_items_b msi1,
bom_inventory_components bic,
mtl_system_items_b msi2
WHERE bom.organization_id = msi1.organization_id
AND msi1.inventory_item_id = bom.assembly_item_id
AND bic.bill_sequence_id = bom.common_bill_sequence_id
AND msi2.inventory_item_id = bic.component_item_id
AND msi2.organization_id = msi1.organization_id
AND bic.implementation_date IS NOT NULL
-- AND bic.disable_date IS NULL HPOV 379694
AND bom.alternate_bom_designator IS NULL
-- ADDED AS PER SR#1051394 BY INFOSYS ON 02-NOV-2012
AND bom.organization_id in (select organization_id from mtl_parameters
where organization_code ='FRX' OR organization_code ='MCK'
OR organization_code = 'MPW' ) --i_num_organization_id
AND bom.assembly_item_id = i_num_item_id
group by i_num_seq,i_num_LINE_NUMBER ,msi1.inventory_item_id, msi1.segment1,
msi1.item_type, msi1.bom_item_type, msi2.inventory_item_id,
msi2.segment1, msi2.item_type, msi2.bom_item_type,
bic.component_quantity, 'N', msi1.segment1,
msi1.inventory_item_id, msi1.item_type, msi1.bom_item_type,
i_num_organization_id, 'MRP', fnd_global.user_id,
TRUNC (SYSDATE), fnd_global.user_id, TRUNC (SYSDATE),
fnd_global.login_id, i_num_request_id,
fnd_global.prog_appl_id, fnd_global.conc_program_id,
TRUNC (SYSDATE), i_num_sequence_id;
-- ADDED AS PER SR#1051394 BY INFOSYS ON 02-NOV-2012 TILL HERE
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'Error in inserting the records in xxpo_bom_comps_reg:' || SQLERRM
);
END;
END IF;
END xxpo_bom_comps_insert_reg;
FUNCTION xxpo_embase_fun_reg (
i_num_comp_item_id IN NUMBER,
i_num_organization_id IN NUMBER,
i_chr_item_type IN VARCHAR2
)
RETURN VARCHAR2
IS
v_num_assembly_item_id1 NUMBER;
v_num_comp_item_id_cur NUMBER;
v_num_assembly_item_id2 NUMBER := NULL;
v_chr_item_type1 VARCHAR2 (100);
v_num_excep_flag NUMBER := 0;
v_chr_assembly_item VARCHAR2 (40);
v_num_comp_sequence_id_cur NUMBER := 0;
BEGIN
v_num_comp_item_id_cur := i_num_comp_item_id;
v_num_excep_flag := 1;
LOOP
IF i_chr_item_type NOT IN ('CONFIGURED_ITEM', 'ATO')
THEN
BEGIN
SELECT assembly_item_id
INTO v_num_assembly_item_id1
FROM bom_bill_of_materials bom
WHERE EXISTS (
SELECT 1
FROM bom_inventory_components bic
WHERE bic.parent_bill_seq_id =
bom.common_bill_sequence_id
AND bic.component_item_id = v_num_comp_item_id_cur)
AND bom.organization_id = i_num_organization_id;
v_num_excep_flag := 0;
EXCEPTION
WHEN OTHERS
THEN
v_num_excep_flag := 1;
DBMS_OUTPUT.put_line
( 'FAILED IN CALC OF ASSEMBLY ITEM ID COMPONENT ITEM '
|| v_num_comp_item_id_cur
|| 'COMPONENT_SEQUENCE_ID'
|| v_num_comp_sequence_id_cur
);
END;
IF v_num_excep_flag = 0
THEN
BEGIN
SELECT item_type, segment1
INTO v_chr_item_type1, v_chr_assembly_item
FROM mtl_system_items_b
WHERE inventory_item_id = v_num_assembly_item_id1
AND organization_id = i_num_organization_id;
EXCEPTION
WHEN OTHERS
THEN
v_num_excep_flag := 1;
DBMS_OUTPUT.put_line ( 'FAILED IN CALC OF ITEM type'
|| v_num_assembly_item_id1
|| 'organization_ID'
|| i_num_organization_id
);
END;
END IF;
END IF;
IF v_chr_item_type1 = 'EMB' AND v_num_excep_flag = 0
THEN
v_num_assembly_item_id2 := v_num_assembly_item_id1;
v_num_comp_item_id_cur := v_num_assembly_item_id1;
EXIT;
ELSIF ( v_chr_item_type1 = 'CONFIGURED_ITEM'
OR v_chr_item_type1 = 'ATO'
)
AND v_num_excep_flag = 0
THEN
v_num_assembly_item_id2 := NULL;
v_chr_assembly_item := NULL;
EXIT;
ELSE
IF v_num_excep_flag = 0
THEN
v_num_comp_item_id_cur := v_num_assembly_item_id1;
ELSE
v_num_assembly_item_id2 := NULL;
v_chr_assembly_item := NULL;
EXIT;
END IF;
END IF;
END LOOP;
RETURN (v_chr_assembly_item);
END;
PROCEDURE vendor_name_prc (
i_num_organization_id IN NUMBER,
i_num_component_item_id IN NUMBER,
o_chr_vendor_name OUT VARCHAR2
)
IS
v_num_assgn_set_id NUMBER;
v_num_sourcing_id NUMBER;
v_num_organization_id NUMBER;
v_num_source_type NUMBER;
v_chr_sourcing_name VARCHAR2 (50);
v_num_receipt_id NUMBER;
v_chr_vendor_name VARCHAR2 (240);
v_num_org_id NUMBER;
v_num_component_item_id NUMBER;
v_chr_out_name VARCHAR2 (240);
v_chr_assign_set_name VARCHAR2 (1000)
:= fnd_profile.VALUE ('XXPO_ASS_SET_FOR_CON_REG');
v_num_vendor_id NUMBER;
BEGIN
v_num_component_item_id := i_num_component_item_id;
/* To fetch the assignment set id */
BEGIN
SELECT assignment_set_id
INTO v_num_assgn_set_id
FROM mrp_assignment_sets
WHERE assignment_set_name = v_chr_assign_set_name;
EXCEPTION
WHEN OTHERS
THEN
v_num_assgn_set_id := NULL;
END;
IF v_num_assgn_set_id IS NOT NULL
THEN
BEGIN
SELECT sourcing_rule_name
INTO v_chr_sourcing_name
FROM mrp_sr_assignments_v
WHERE assignment_set_id = v_num_assgn_set_id
AND organization_id = i_num_organization_id
AND inventory_item_id = i_num_component_item_id;
EXCEPTION
WHEN OTHERS
THEN
v_num_sourcing_id := NULL;
END;
END IF;
IF v_chr_sourcing_name IS NOT NULL
THEN
BEGIN
SELECT sourcing_rule_id
INTO v_num_sourcing_id
FROM mrp_sourcing_rules
WHERE sourcing_rule_name = v_chr_sourcing_name
AND organization_id = i_num_organization_id;
EXCEPTION
WHEN OTHERS
THEN
v_num_sourcing_id := NULL;
END;
END IF;
IF v_num_sourcing_id IS NOT NULL
THEN
BEGIN
SELECT sr_receipt_id
INTO v_num_receipt_id
FROM mrp_sr_receipt_org
WHERE sourcing_rule_id = v_num_sourcing_id;
EXCEPTION
WHEN OTHERS
THEN
v_num_receipt_id := NULL;
END;
END IF;
IF v_num_receipt_id IS NOT NULL
THEN
BEGIN
SELECT source_type, vendor_id, source_organization_id
INTO v_num_source_type, v_num_vendor_id, v_num_org_id
FROM mrp_sr_source_org
WHERE sr_receipt_id = v_num_receipt_id;
EXCEPTION
WHEN OTHERS
THEN
v_num_vendor_id := NULL;
END;
IF v_num_vendor_id IS NOT NULL
THEN
BEGIN
SELECT vendor_name
INTO v_chr_vendor_name
FROM po_vendors
WHERE vendor_id = v_num_vendor_id;
EXCEPTION
WHEN OTHERS
THEN
v_chr_vendor_name := NULL;
END;
END IF;
IF v_num_source_type <> 3
THEN
vendor_name_prc (v_num_org_id,
v_num_component_item_id,
v_chr_out_name
);
ELSE
o_chr_vendor_name := v_chr_vendor_name;
END IF;
END IF;
END vendor_name_prc;
END XXPO_BOM_COMPS_PKG_REG;
/
|
|
|
|
| Re: Query in Loop causing performance issue [message #626349 is a reply to message #626348] |
Mon, 27 October 2014 01:04 |
|
nishantranjan00787
Messages: 36
Registered: July 2014
Location: india
|
Member |
|
|
PROCEDURE explode_bom (
out_errbuf OUT VARCHAR2,
out_retcode OUT NUMBER,
i_num_request_id IN NUMBER,
i_num_organization_id IN NUMBER,
i_num_item_id IN NUMBER,
i_num_line_number IN NUMBER,
i_num_sequence_id IN NUMBER
)
IS
v_chr_flag VARCHAR2 (10);
v_chr_errbuff VARCHAR2 (60);
v_num_retcode NUMBER;
v_num_no_count NUMBER := 0;
v_num_ite_count NUMBER := 0;
v_chr_exp_flag VARCHAR2 (10);
v_num_count_bom NUMBER := 0;
v_num_count_fir_lvl NUMBER := 0;
v_num_count_bom_first NUMBER := 0;
v_chr_embase VARCHAR2 (40);
v_num_sequence_id NUMBER := 0;
v_num_sequence_id_model NUMBER := 0;
v_num_sequence_id_bef_exp NUMBER := 0;
/* Cursor to fetch the items which got inserted in to table for the first time */
CURSOR cur_update_flag_1 (c_num_sequence_id NUMBER)
IS
SELECT ROWID rowid_num, xbcr.*
FROM xxpo_bom_comps_reg xbcr
WHERE xbcr.flag = 'N'
AND xbcr.request_id = i_num_request_id
AND xbcr.sequence_id = c_num_sequence_id
ORDER BY xbcr.component_item_id, xbcr.sno;
/* Cursor to fetch the items which needs to be exploded */
CURSOR cur_assembly_items (c_num_sequence_id NUMBER)
IS
SELECT ROWID rowid_num, xbcr.*
FROM xxpo_bom_comps_reg xbcr
WHERE xbcr.flag = 'EXP'
AND xbcr.request_id = i_num_request_id
AND xbcr.sequence_id = c_num_sequence_id
ORDER BY xbcr.component_item_id, xbcr.sno;
/* Cursor to check if BOM exists for the item to be exploded */
CURSOR cur_item_type_seg (i_num_assembly_item_id NUMBER, i_org_id NUMBER)
IS
SELECT msi1.inventory_item_id assembly_item_id,
msi1.segment1 assembly, msi1.item_type asmbly_user_item_type,
msi1.bom_item_type asmbly_bom_item_type,
msi2.inventory_item_id component_item_id,
msi2.segment1 component, msi2.item_type comp_user_item_type,
msi2.bom_item_type comp_bom_item_type,
bic.component_quantity quantity, 'N' flag,
i_num_organization_id organization_id, 'MRP' mrp_plan
FROM bom_bill_of_materials bom,
mtl_system_items_b msi1,
bom_inventory_components bic,
mtl_system_items_b msi2
WHERE bom.organization_id = msi1.organization_id
AND msi1.inventory_item_id = bom.assembly_item_id
AND bic.bill_sequence_id = bom.common_bill_sequence_id
AND msi2.inventory_item_id = bic.component_item_id
AND msi2.organization_id = msi1.organization_id
AND bom.assembly_item_id = i_num_assembly_item_id
AND bic.implementation_date IS NOT NULL
-- AND bic.disable_date IS NULL HPOV 379694
AND bom.alternate_bom_designator IS NULL
AND bom.organization_id = i_org_id;
BEGIN
-- Getting the Components for the assembly
BEGIN
IF i_num_sequence_id = 0
THEN
v_num_sequence_id := 1;
ELSE
v_num_sequence_id := i_num_sequence_id;
END IF;
/* Calling the Insert procedure to insert the items into the table */
xxpo_bom_comps_insert_reg (v_chr_errbuff,
v_num_retcode,
1,
i_num_organization_id,
i_num_item_id,
i_num_line_number, -- ADDED AS PER SR#1051394 BY INFOSYS ON 02-NOV-2012
i_num_request_id,
v_num_sequence_id
);
/* Updating the custom table with the EM_BASe fetched form the function*/
UPDATE xxpo_bom_comps_reg xbcr
SET em_base =
xxpo_embase_fun_reg (xbcr.component_item_id,
xbcr.organization_id,
xbcr.comp_user_item_type
)
WHERE xbcr.comp_user_item_type = 'EMM';
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'Exception in the Insert Statement-' || SQLERRM
);
END;
COMMIT;
BEGIN
v_num_ite_count := 0;
LOOP -- infinite loop
-- INITIAL RECORD SET USING LOOP TO SET Y AND N
FOR cur_update_flag_rec IN
cur_update_flag_1 (v_num_sequence_id) -- to fetch all Ns
LOOP
IF cur_update_flag_rec.sno = 1
THEN
IF (cur_update_flag_rec.comp_user_item_type IN
('EMM', 'SA', 'CONFIGURED_ITEM')
)
THEN
v_chr_flag := 'EXP';
ELSIF cur_update_flag_rec.comp_user_item_type IN
('P', 'EMR_PURCH')
THEN
/* Query to check if the BOM exists for the parts with item type Purchased Item*/
BEGIN
SELECT COUNT (1)
INTO v_num_count_bom_first
FROM bom_bill_of_materials a,
bom_inventory_components b
WHERE a.assembly_item_id =
cur_update_flag_rec.component_item_id
AND a.organization_id = i_num_organization_id
AND b.bill_sequence_id = a.common_bill_sequence_id;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG,
'error while checking bom exists for the component'
);
END;
IF v_num_count_bom_first > 0
THEN
v_chr_flag := 'EXP';
ELSE
v_chr_flag := 'PRINT';
END IF;
ELSE
v_chr_flag := 'Y';
END IF;
ELSE
-- check on BOM of the Item (form 2nd level onwards)
BEGIN
SELECT COUNT (1)
INTO v_num_count_bom
FROM bom_bill_of_materials a,
bom_inventory_components b
WHERE a.assembly_item_id =
cur_update_flag_rec.component_item_id
AND a.organization_id = i_num_organization_id
AND b.bill_sequence_id = a.common_bill_sequence_id;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG,
'error while checking bom exists for the component'
);
END;
IF v_num_count_bom > 0
THEN
v_chr_flag := 'EXP';
ELSE
v_chr_flag := 'PRINT';
END IF;
--commentd by REGS dated 11/10/2012
-- Logic to update the duplicate records with flag = 'Y'
IF v_chr_flag = 'PRINT'
THEN
fnd_file.put_line (fnd_file.LOG,
'IF FLAG PRINT ' || V_CHR_FLAG
);
-- Query to check if any duplicate records are getting inserted
BEGIN
SELECT COUNT (1)
INTO v_num_count_fir_lvl
FROM xxpo_bom_comps_reg
WHERE assembly_item_id =
cur_update_flag_rec.assembly_item_id
AND component_item_id =
cur_update_flag_rec.component_item_id
AND top_assembly_item_id =
cur_update_flag_rec.top_assembly_item_id
AND line_number = cur_update_flag_rec.line_number ---- ADDED AS PER SR#1051394 BY INFOSYS ON 02-NOV-2012
AND flag = 'PRINT';
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
-- If the record already exists with flag = 'PRINT' update new record with flag = 'Y'
IF v_num_count_fir_lvl > 0
THEN
v_chr_flag := 'Y';
END IF;
END IF;
END IF;
BEGIN
/* Updating the custom table with the flag*/
UPDATE xxpo_bom_comps_reg
SET flag = v_chr_flag,
line_number = i_num_line_number
WHERE ROWID = cur_update_flag_rec.rowid_num;
fnd_file.put_line
(fnd_file.LOG,
'Exception updating successful records-'
|| i_num_line_number || ',' || v_chr_flag || ',' || cur_update_flag_rec.rowid_num
);
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG,
'Exception updating successful records-'
|| SQLERRM
);
END;
END LOOP; --cur_update_flag_rec IN cur_update_flag_1
FOR rec_assembly_items IN cur_assembly_items (v_num_sequence_id)
-- fetch all remaing EXP records
LOOP
v_chr_exp_flag := 'EXP_NOT';
v_chr_embase := rec_assembly_items.em_base;
IF rec_assembly_items.comp_user_item_type = 'CONFIGURED_ITEM'
THEN
v_num_sequence_id_model := v_num_sequence_id + 1;
v_chr_flag := 'Y';
fnd_file.put_line (fnd_file.LOG,
'IF FLAG ITEM TYPE ' || rec_assembly_items.comp_user_item_type);
BEGIN
/* Updating the custom table with the flag*/
UPDATE xxpo_bom_comps_reg
SET flag = v_chr_flag,
line_number = i_num_line_number
WHERE ROWID = rec_assembly_items.rowid_num;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG,
'Exception updating successful records-'
|| SQLERRM
);
END;
v_num_sequence_id_bef_exp := v_num_sequence_id;
/* Calling the procedure to explode the configured item if exists any*/
explode_bom (v_chr_errbuff,
v_num_retcode,
i_num_request_id,
i_num_organization_id,
rec_assembly_items.component_item_id,
i_num_line_number,
v_num_sequence_id_model
);
COMMIT;
v_num_sequence_id := v_num_sequence_id_bef_exp;
ELSE
FOR rec_item_type_seg IN
cur_item_type_seg (rec_assembly_items.component_item_id,
rec_assembly_items.organization_id
)
LOOP
BEGIN
INSERT INTO xxpo_bom_comps_reg
(sno, assembly_item_id,
assembly,
asmbly_user_item_type,
asmbly_bom_item_type,
component_item_id,
component,
comp_user_item_type,
comp_bom_item_type,
quantity, flag,
line_number,
top_assembly,
top_assembly_item_id,
top_asmbly_user_item_type,
top_asmbly_bom_item_type,
organization_id,
mrp_plan,
created_by, creation_date,
last_updated_by, last_updated_date,
last_updated_login, request_id,
program_application_id,
program_id,
program_update_date, em_base,
sequence_id
)
VALUES (2, rec_item_type_seg.assembly_item_id,
rec_item_type_seg.assembly,
rec_item_type_seg.asmbly_user_item_type,
rec_item_type_seg.asmbly_bom_item_type,
rec_item_type_seg.component_item_id,
rec_item_type_seg.component,
rec_item_type_seg.comp_user_item_type,
rec_item_type_seg.comp_bom_item_type,
rec_item_type_seg.quantity, 'N',
i_num_line_number,
rec_assembly_items.top_assembly,
rec_assembly_items.top_assembly_item_id,
rec_assembly_items.top_asmbly_user_item_type,
rec_assembly_items.top_asmbly_bom_item_type,
rec_assembly_items.organization_id,
rec_assembly_items.mrp_plan,
fnd_global.user_id, TRUNC (SYSDATE),
fnd_global.user_id, TRUNC (SYSDATE),
fnd_global.login_id, i_num_request_id,
fnd_global.prog_appl_id,
fnd_global.conc_program_id,
TRUNC (SYSDATE), v_chr_embase,
v_num_sequence_id
);
v_chr_exp_flag := 'EXP_DONE';
fnd_file.put_line (fnd_file.LOG,
'IF' || v_chr_exp_flag);
fnd_file.put_line (fnd_file.LOG,
'AFTER INSERT' || rec_item_type_seg.assembly_item_id);
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'---ERROR 1---' || SQLERRM
);
END;
END LOOP;
END IF;
BEGIN
UPDATE xxpo_bom_comps_reg
SET flag = v_chr_exp_flag,
line_number = i_num_line_number
WHERE ROWID = rec_assembly_items.rowid_num;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'ERROR WHILE UPDATING FLAG '
|| SQLERRM
);
END;
END LOOP; --rec_assembly_items IN cur_assembly_items
COMMIT;
BEGIN
v_num_no_count := 0;
/* Count to check if there exists any record which needs to be exploded*/
SELECT COUNT (1)
INTO v_num_no_count
FROM xxpo_bom_comps_reg
WHERE sequence_id = v_num_sequence_id AND flag IN
('N', 'EXP');
fnd_file.put_line (fnd_file.LOG,
';;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;'
);
fnd_file.put_line (fnd_file.LOG,
'v_num_no_count :=' || v_num_no_count
);
fnd_file.put_line (fnd_file.LOG,
';;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;'
);
EXCEPTION
WHEN OTHERS
THEN
v_num_no_count := 0;
END;
EXIT WHEN v_num_no_count = 0;
END LOOP;
-- Infinite loop which exist when all the records in
--custom table are 'Y'
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'Exception in the Main Package-' || SQLERRM
);
END;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'Exception in the Main Package-' || SQLERRM
);
END explode_bom;
PROCEDURE explode_bom_model (
out_errbuf OUT VARCHAR2,
out_retcode OUT NUMBER,
i_num_request_id IN NUMBER,
i_num_organization_id IN NUMBER,
i_num_item_id IN NUMBER,
i_num_line_number IN NUMBER,
i_num_sequence_id IN NUMBER
)
IS
v_chr_flag VARCHAR2 (10);
v_chr_errbuff VARCHAR2 (60);
v_num_retcode NUMBER;
v_num_no_count NUMBER := 0;
v_num_ite_count NUMBER := 0;
v_chr_exp_flag VARCHAR2 (10);
v_num_count_fir_lvl NUMBER := 0;
v_num_count_bom_first NUMBER := 0;
v_chr_embase VARCHAR2 (40);
v_num_sequence_id NUMBER := 0;
v_num_sequence_id_model NUMBER := 0;
v_num_sequence_id_bef_exp NUMBER := 0;
/* Cursor to fetch the items which got inserted in to table for the first time */
CURSOR cur_update_flag_1 (c_num_sequence_id NUMBER)
IS
SELECT ROWID rowid_num, xbcr.*
FROM xxpo_bom_comps_reg xbcr
WHERE flag = 'N'
AND request_id = i_num_request_id
AND sequence_id = c_num_sequence_id
ORDER BY component_item_id, sno;
/* Cursor to fetch the items which needs to be exploded */
CURSOR cur_assembly_items (c_num_sequence_id NUMBER)
IS
SELECT ROWID rowid_num, xbcr.*
FROM xxpo_bom_comps_reg xbcr
WHERE flag = 'EXP'
AND request_id = i_num_request_id
AND sequence_id = c_num_sequence_id
ORDER BY component_item_id, sno;
/* Cursor to check if BOM exists for the item to be exploded */
CURSOR cur_item_type_seg (i_num_assembly_item_id NUMBER, i_org_id NUMBER)
IS
SELECT msi1.inventory_item_id assembly_item_id,
msi1.segment1 assembly, msi1.item_type asmbly_user_item_type,
msi1.bom_item_type asmbly_bom_item_type,
msi2.inventory_item_id component_item_id,
msi2.segment1 component, msi2.item_type comp_user_item_type,
msi2.bom_item_type comp_bom_item_type,
bic.component_quantity quantity, 'N' flag,
i_num_organization_id organization_id, 'MRP' mrp_plan
FROM bom_bill_of_materials bom,
mtl_system_items_b msi1,
bom_inventory_components bic,
mtl_system_items_b msi2
WHERE bom.organization_id = msi1.organization_id
AND msi1.inventory_item_id = bom.assembly_item_id
AND bic.bill_sequence_id = bom.common_bill_sequence_id
AND msi2.inventory_item_id = bic.component_item_id
AND msi2.organization_id = msi1.organization_id
AND bom.assembly_item_id = i_num_assembly_item_id
AND bic.implementation_date IS NOT NULL
-- AND bic.disable_date IS NULL HPOV 379694
AND bom.alternate_bom_designator IS NULL
AND bom.organization_id = i_org_id;
BEGIN
-- Getting the Components for the assembly
BEGIN
IF i_num_sequence_id = 0
THEN
v_num_sequence_id := 1;
ELSE
v_num_sequence_id := i_num_sequence_id;
END IF;
/* Calling the Insert procedure to insert the items into the table */
xxpo_bom_comps_insert_reg (v_chr_errbuff,
v_num_retcode,
1,
i_num_organization_id,
i_num_item_id,
i_num_line_number, -- ADDED AS PER SR#1051394 BY INFOSYS ON 02-NOV-2012
i_num_request_id,
v_num_sequence_id
);
/* Updating the Custom table EM_BASE column for all the EM Bases*/
UPDATE xxpo_bom_comps_reg xbcr
SET em_base = xbcr.component
WHERE xbcr.comp_user_item_type = 'EMB'
AND request_id = i_num_request_id
AND sequence_id = v_num_sequence_id;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'Exception in the Insert Statement-' || SQLERRM
);
END;
COMMIT;
BEGIN
v_num_ite_count := 0;
LOOP -- infinite loop
-- INITIAL RECORD SET USING LOOP TO SET Y AND N
FOR cur_update_flag_rec IN
cur_update_flag_1 (v_num_sequence_id) -- to fetch all Ns
LOOP
/* Cursor to check if BOM exists for the item to be exploded */
BEGIN
SELECT COUNT (1)
INTO v_num_count_bom_first
FROM bom_bill_of_materials a, bom_inventory_components b
WHERE a.assembly_item_id =
cur_update_flag_rec.component_item_id
AND a.organization_id = i_num_organization_id
AND b.bill_sequence_id = a.common_bill_sequence_id;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG,
'error while checking bom exists for the component'
);
END;
IF v_num_count_bom_first > 0
THEN
v_chr_flag := 'EXP';
ELSE
v_chr_flag := 'PRINT';
END IF;
-- Logic to update the duplicate records with flag = 'Y'
IF v_chr_flag = 'PRINT'
THEN
BEGIN
SELECT COUNT (1)
INTO v_num_count_fir_lvl
FROM xxpo_bom_comps_reg
WHERE assembly_item_id =
cur_update_flag_rec.assembly_item_id
AND component_item_id =
cur_update_flag_rec.component_item_id
AND top_assembly_item_id =
cur_update_flag_rec.top_assembly_item_id
AND line_number = cur_update_flag_rec.line_number -- ADDED AS PER SR#1051394 BY INFOSYS ON 02-NOV-2012
AND flag = 'PRINT';
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
-- If the record already exists with flag = 'PRINT' update new record with flag = 'Y'
IF v_num_count_fir_lvl > 0
THEN
v_chr_flag := 'Y';
END IF;
END IF;
BEGIN
/* Updating the custom table with appropriate print flag*/
UPDATE xxpo_bom_comps_reg
SET flag = v_chr_flag,
line_number = i_num_line_number
WHERE ROWID = cur_update_flag_rec.rowid_num;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG,
'Exception updating successful records-'
|| SQLERRM
);
END;
END LOOP; --cur_update_flag_rec IN cur_update_flag_1
FOR rec_assembly_items IN cur_assembly_items (v_num_sequence_id)
-- fetch all remaing EXP records
LOOP
v_chr_exp_flag := 'EXP_NOT';
v_chr_embase := rec_assembly_items.em_base;
IF rec_assembly_items.comp_user_item_type = 'ATO'
THEN
v_num_sequence_id_model := v_num_sequence_id + 1;
v_chr_flag := 'Y';
BEGIN
/* Updating the custom table with appropriate print flag*/
UPDATE xxpo_bom_comps_reg
SET flag = v_chr_flag,
line_number = i_num_line_number
WHERE ROWID = rec_assembly_items.rowid_num;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG,
'Exception updating successful records-'
|| SQLERRM
);
END;
v_num_sequence_id_bef_exp := v_num_sequence_id;
/* Calling the procedure to explode the model item if exists any*/
explode_bom_model (v_chr_errbuff,
v_num_retcode,
i_num_request_id,
i_num_organization_id,
rec_assembly_items.component_item_id,
i_num_line_number,
v_num_sequence_id_model
);
COMMIT;
v_num_sequence_id := v_num_sequence_id_bef_exp;
ELSE
FOR rec_item_type_seg IN
cur_item_type_seg (rec_assembly_items.component_item_id,
rec_assembly_items.organization_id
)
LOOP
BEGIN
INSERT INTO xxpo_bom_comps_reg
(sno, assembly_item_id,
assembly,
asmbly_user_item_type,
asmbly_bom_item_type,
component_item_id,
component,
comp_user_item_type,
comp_bom_item_type,
quantity, flag,
line_number,
top_assembly,
top_assembly_item_id,
top_asmbly_user_item_type,
top_asmbly_bom_item_type,
organization_id,
mrp_plan,
created_by, creation_date,
last_updated_by, last_updated_date,
last_updated_login, request_id,
program_application_id,
program_id,
program_update_date, em_base,
sequence_id
)
VALUES (2, rec_item_type_seg.assembly_item_id,
rec_item_type_seg.assembly,
rec_item_type_seg.asmbly_user_item_type,
rec_item_type_seg.asmbly_bom_item_type,
rec_item_type_seg.component_item_id,
rec_item_type_seg.component,
rec_item_type_seg.comp_user_item_type,
rec_item_type_seg.comp_bom_item_type,
rec_item_type_seg.quantity, 'N',
i_num_line_number,
rec_assembly_items.top_assembly,
rec_assembly_items.top_assembly_item_id,
rec_assembly_items.top_asmbly_user_item_type,
rec_assembly_items.top_asmbly_bom_item_type,
rec_assembly_items.organization_id,
rec_assembly_items.mrp_plan,
fnd_global.user_id, TRUNC (SYSDATE),
fnd_global.user_id, TRUNC (SYSDATE),
fnd_global.login_id, i_num_request_id,
fnd_global.prog_appl_id,
fnd_global.conc_program_id,
TRUNC (SYSDATE), v_chr_embase,
v_num_sequence_id
);
v_chr_exp_flag := 'EXP_DONE';
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'---ERROR 1---' || SQLERRM
);
END;
END LOOP;
END IF;
fnd_file.put_line (fnd_file.LOG,'LOG10:' ||i_num_line_number || ' '|| v_chr_exp_flag);
--rec_item_type_seg IN cur_item_type_seg( rec_assembly_items.assembly_item_id,
BEGIN
UPDATE xxpo_bom_comps_reg
SET flag = v_chr_exp_flag,
line_number = i_num_line_number
WHERE ROWID = rec_assembly_items.rowid_num;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'ERROR WHILE UPDATING FLAG '
|| SQLERRM
);
END;
END LOOP; --rec_assembly_items IN cur_assembly_items
COMMIT;
BEGIN
v_num_no_count := 0;
/* Count to check if there exists any record which needs to be exploded*/
SELECT COUNT (1)
INTO v_num_no_count
FROM xxpo_bom_comps_reg
WHERE sequence_id = v_num_sequence_id AND flag IN
('N', 'EXP');
fnd_file.put_line (fnd_file.LOG,
';;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;'
);
fnd_file.put_line (fnd_file.LOG,
'v_num_no_count :=' || v_num_no_count
);
fnd_file.put_line (fnd_file.LOG,
';;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;'
);
EXCEPTION
WHEN OTHERS
THEN
v_num_no_count := 0;
END;
EXIT WHEN v_num_no_count = 0;
END LOOP;
-- Infinite loop which exist when all the records in
--custom table are 'Y'
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'Exception in the Main Package-' || SQLERRM
);
END;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'Exception in the Main Package-' || SQLERRM
);
END explode_bom_model;
PROCEDURE xxpo_bom_comps_insert_reg (
out_errbuf OUT VARCHAR2,
out_retcode OUT NUMBER,
i_num_seq IN NUMBER,
i_num_organization_id IN NUMBER,
i_num_item_id IN NUMBER,
i_num_line_number IN NUMBER, -- ADDED AS PER SR#1051394 BY INFOSYS ON 02-NOV-2012
i_num_request_id IN NUMBER,
i_num_sequence_id IN NUMBER
)
IS
-- ADDED AS PER SR#1051394 BY INFOSYS ON 02-NOV-2012
v_num_count NUMBER :=0;
BEGIN
SELECT COUNT(1)
INTO v_num_count
FROM bom_bill_of_materials
WHERE assembly_item_id = i_num_item_id
AND organization_id IN (select organization_id from mtl_parameters
where organization_code in ('FRX','MCK','MPW'));
IF v_num_count = 0
THEN
BEGIN
INSERT INTO xxpo_bom_comps_reg
(sno, LINE_NUMBER,assembly_item_id, assembly, asmbly_user_item_type,
asmbly_bom_item_type, component_item_id, component,
comp_user_item_type, comp_bom_item_type,flag,
top_assembly, top_assembly_item_id,
top_asmbly_user_item_type, top_asmbly_bom_item_type,
organization_id, mrp_plan, created_by, creation_date,
last_updated_by, last_updated_date, last_updated_login,
request_id, program_application_id, program_id,
program_update_date, sequence_id)
SELECT i_num_seq,i_num_LINE_NUMBER ,msi1.inventory_item_id, msi1.segment1,
msi1.item_type, msi1.bom_item_type, msi1.inventory_item_id,
msi1.segment1, msi1.item_type, msi1.bom_item_type,
'N', msi1.segment1,msi1.inventory_item_id, msi1.item_type, msi1.bom_item_type,
i_num_organization_id, 'MRP', fnd_global.user_id,
TRUNC (SYSDATE), fnd_global.user_id, TRUNC (SYSDATE),
fnd_global.login_id, i_num_request_id,
fnd_global.prog_appl_id, fnd_global.conc_program_id,
TRUNC (SYSDATE), i_num_sequence_id
FROM mtl_system_items_b msi1
WHERE organization_id = i_num_organization_id
AND inventory_item_id = i_num_item_id;
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'Error in inserting the records in xxpo_bom_comps_reg' || SQLERRM
);
END;
ELSE
-- ADDED AS PER SR#1051394 BY INFOSYS ON 02-NOV-2012 TILL HERE
BEGIN
INSERT INTO xxpo_bom_comps_reg
(sno, LINE_NUMBER,assembly_item_id, assembly, asmbly_user_item_type,
asmbly_bom_item_type, component_item_id, component,
comp_user_item_type, comp_bom_item_type, quantity, flag,
top_assembly, top_assembly_item_id,
top_asmbly_user_item_type, top_asmbly_bom_item_type,
organization_id, mrp_plan, created_by, creation_date,
last_updated_by, last_updated_date, last_updated_login,
request_id, program_application_id, program_id,
program_update_date, sequence_id)
SELECT i_num_seq,i_num_LINE_NUMBER ,msi1.inventory_item_id, msi1.segment1,
msi1.item_type, msi1.bom_item_type, msi2.inventory_item_id,
msi2.segment1, msi2.item_type, msi2.bom_item_type,
bic.component_quantity, 'N', msi1.segment1,
msi1.inventory_item_id, msi1.item_type, msi1.bom_item_type,
i_num_organization_id, 'MRP', fnd_global.user_id,
TRUNC (SYSDATE), fnd_global.user_id, TRUNC (SYSDATE),
fnd_global.login_id, i_num_request_id,
fnd_global.prog_appl_id, fnd_global.conc_program_id,
TRUNC (SYSDATE), i_num_sequence_id
FROM bom_bill_of_materials bom,
mtl_system_items_b msi1,
bom_inventory_components bic,
mtl_system_items_b msi2
WHERE bom.organization_id = msi1.organization_id
AND msi1.inventory_item_id = bom.assembly_item_id
AND bic.bill_sequence_id = bom.common_bill_sequence_id
AND msi2.inventory_item_id = bic.component_item_id
AND msi2.organization_id = msi1.organization_id
AND bic.implementation_date IS NOT NULL
-- AND bic.disable_date IS NULL HPOV 379694
AND bom.alternate_bom_designator IS NULL
-- ADDED AS PER SR#1051394 BY INFOSYS ON 02-NOV-2012
AND bom.organization_id in (select organization_id from mtl_parameters
where organization_code ='FRX' OR organization_code ='MCK'
OR organization_code = 'MPW' ) --i_num_organization_id
AND bom.assembly_item_id = i_num_item_id
group by i_num_seq,i_num_LINE_NUMBER ,msi1.inventory_item_id, msi1.segment1,
msi1.item_type, msi1.bom_item_type, msi2.inventory_item_id,
msi2.segment1, msi2.item_type, msi2.bom_item_type,
bic.component_quantity, 'N', msi1.segment1,
msi1.inventory_item_id, msi1.item_type, msi1.bom_item_type,
i_num_organization_id, 'MRP', fnd_global.user_id,
TRUNC (SYSDATE), fnd_global.user_id, TRUNC (SYSDATE),
fnd_global.login_id, i_num_request_id,
fnd_global.prog_appl_id, fnd_global.conc_program_id,
TRUNC (SYSDATE), i_num_sequence_id;
-- ADDED AS PER SR#1051394 BY INFOSYS ON 02-NOV-2012 TILL HERE
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'Error in inserting the records in xxpo_bom_comps_reg:' || SQLERRM
);
END;
END IF;
END xxpo_bom_comps_insert_reg;
FUNCTION xxpo_embase_fun_reg (
i_num_comp_item_id IN NUMBER,
i_num_organization_id IN NUMBER,
i_chr_item_type IN VARCHAR2
)
RETURN VARCHAR2
IS
v_num_assembly_item_id1 NUMBER;
v_num_comp_item_id_cur NUMBER;
v_num_assembly_item_id2 NUMBER := NULL;
v_chr_item_type1 VARCHAR2 (100);
v_num_excep_flag NUMBER := 0;
v_chr_assembly_item VARCHAR2 (40);
v_num_comp_sequence_id_cur NUMBER := 0;
BEGIN
v_num_comp_item_id_cur := i_num_comp_item_id;
v_num_excep_flag := 1;
LOOP
IF i_chr_item_type NOT IN ('CONFIGURED_ITEM', 'ATO')
THEN
BEGIN
SELECT assembly_item_id
INTO v_num_assembly_item_id1
FROM bom_bill_of_materials bom
WHERE EXISTS (
SELECT 1
FROM bom_inventory_components bic
WHERE bic.parent_bill_seq_id =
bom.common_bill_sequence_id
AND bic.component_item_id = v_num_comp_item_id_cur)
AND bom.organization_id = i_num_organization_id;
v_num_excep_flag := 0;
EXCEPTION
WHEN OTHERS
THEN
v_num_excep_flag := 1;
DBMS_OUTPUT.put_line
( 'FAILED IN CALC OF ASSEMBLY ITEM ID COMPONENT ITEM '
|| v_num_comp_item_id_cur
|| 'COMPONENT_SEQUENCE_ID'
|| v_num_comp_sequence_id_cur
);
END;
IF v_num_excep_flag = 0
THEN
BEGIN
SELECT item_type, segment1
INTO v_chr_item_type1, v_chr_assembly_item
FROM mtl_system_items_b
WHERE inventory_item_id = v_num_assembly_item_id1
AND organization_id = i_num_organization_id;
EXCEPTION
WHEN OTHERS
THEN
v_num_excep_flag := 1;
DBMS_OUTPUT.put_line ( 'FAILED IN CALC OF ITEM type'
|| v_num_assembly_item_id1
|| 'organization_ID'
|| i_num_organization_id
);
END;
END IF;
END IF;
IF v_chr_item_type1 = 'EMB' AND v_num_excep_flag = 0
THEN
v_num_assembly_item_id2 := v_num_assembly_item_id1;
v_num_comp_item_id_cur := v_num_assembly_item_id1;
EXIT;
ELSIF ( v_chr_item_type1 = 'CONFIGURED_ITEM'
OR v_chr_item_type1 = 'ATO'
)
AND v_num_excep_flag = 0
THEN
v_num_assembly_item_id2 := NULL;
v_chr_assembly_item := NULL;
EXIT;
ELSE
IF v_num_excep_flag = 0
THEN
v_num_comp_item_id_cur := v_num_assembly_item_id1;
ELSE
v_num_assembly_item_id2 := NULL;
v_chr_assembly_item := NULL;
EXIT;
END IF;
END IF;
END LOOP;
RETURN (v_chr_assembly_item);
END;
PROCEDURE vendor_name_prc (
i_num_organization_id IN NUMBER,
i_num_component_item_id IN NUMBER,
o_chr_vendor_name OUT VARCHAR2
)
IS
v_num_assgn_set_id NUMBER;
v_num_sourcing_id NUMBER;
v_num_organization_id NUMBER;
v_num_source_type NUMBER;
v_chr_sourcing_name VARCHAR2 (50);
v_num_receipt_id NUMBER;
v_chr_vendor_name VARCHAR2 (240);
v_num_org_id NUMBER;
v_num_component_item_id NUMBER;
v_chr_out_name VARCHAR2 (240);
v_chr_assign_set_name VARCHAR2 (1000)
:= fnd_profile.VALUE ('XXPO_ASS_SET_FOR_CON_REG');
v_num_vendor_id NUMBER;
BEGIN
v_num_component_item_id := i_num_component_item_id;
/* To fetch the assignment set id */
BEGIN
SELECT assignment_set_id
INTO v_num_assgn_set_id
FROM mrp_assignment_sets
WHERE assignment_set_name = v_chr_assign_set_name;
EXCEPTION
WHEN OTHERS
THEN
v_num_assgn_set_id := NULL;
END;
IF v_num_assgn_set_id IS NOT NULL
THEN
BEGIN
SELECT sourcing_rule_name
INTO v_chr_sourcing_name
FROM mrp_sr_assignments_v
WHERE assignment_set_id = v_num_assgn_set_id
AND organization_id = i_num_organization_id
AND inventory_item_id = i_num_component_item_id;
EXCEPTION
WHEN OTHERS
THEN
v_num_sourcing_id := NULL;
END;
END IF;
IF v_chr_sourcing_name IS NOT NULL
THEN
BEGIN
SELECT sourcing_rule_id
INTO v_num_sourcing_id
FROM mrp_sourcing_rules
WHERE sourcing_rule_name = v_chr_sourcing_name
AND organization_id = i_num_organization_id;
EXCEPTION
WHEN OTHERS
THEN
v_num_sourcing_id := NULL;
END;
END IF;
IF v_num_sourcing_id IS NOT NULL
THEN
BEGIN
SELECT sr_receipt_id
INTO v_num_receipt_id
FROM mrp_sr_receipt_org
WHERE sourcing_rule_id = v_num_sourcing_id;
EXCEPTION
WHEN OTHERS
THEN
v_num_receipt_id := NULL;
END;
END IF;
IF v_num_receipt_id IS NOT NULL
THEN
BEGIN
SELECT source_type, vendor_id, source_organization_id
INTO v_num_source_type, v_num_vendor_id, v_num_org_id
FROM mrp_sr_source_org
WHERE sr_receipt_id = v_num_receipt_id;
EXCEPTION
WHEN OTHERS
THEN
v_num_vendor_id := NULL;
END;
IF v_num_vendor_id IS NOT NULL
THEN
BEGIN
SELECT vendor_name
INTO v_chr_vendor_name
FROM po_vendors
WHERE vendor_id = v_num_vendor_id;
EXCEPTION
WHEN OTHERS
THEN
v_chr_vendor_name := NULL;
END;
END IF;
IF v_num_source_type <> 3
THEN
vendor_name_prc (v_num_org_id,
v_num_component_item_id,
v_chr_out_name
);
ELSE
o_chr_vendor_name := v_chr_vendor_name;
END IF;
END IF;
END vendor_name_prc;
END XXPO_BOM_COMPS_PKG_REG;
/
|
|
|
|
|
|
|
|
|
|
| Re: Query in Loop causing performance issue [message #626392 is a reply to message #626352] |
Mon, 27 October 2014 09:45 |
 |
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
|
Senior Member |
|
|
Thank you for posting the additional details.
You had an index that did this.
INDEX RANGE SCAN XXPO_BOM_COMPS_REG_N222 (cr=8557 pr=8548 pw=0 time=11092233 us cost=2639 size=8851833 card=983537)(object id 37592234)
and now there is an index that does this
INDEX FAST FULL SCAN XXPO_BOM_COMPS_REG_N123 (cr=13246 pr=13210 pw=0 time=5095001 us cost=7005 size=26559351 card=2951039)(object id 38138020)
I would like to see the prior index definition too please.
It would be nice to see the full plans for whatever queries you are interested in. In particular, we need to see the PREDICATE INFORMATION section of the query plan. If you would, knowing that it does have limitations, can you please provide the output of a simple EXPLAIN COMMAND for the query. To make this easier, you can go to the following link on OraFAQ and download the scripts there. The script file is located at the bottom of the first post as a .RAR.TXT file. Download it, rename it to .RAR and then unzip it to a directory you have for such things.
Then use the following:
@showindexes <owner> XXPO_BOM_COMPS_REG
explain plan for SELECT COUNT (1)
FROM
XXPO_BOM_COMPS_REG WHERE SEQUENCE_ID = :B1 AND FLAG IN ('N', 'EXP')
/
@showplan11g
@showplanfrpspreadsheet11g
Then edit the generated SQL to replace :B1 with an actual bind value and run the generated SQL.
Then learn how to use the formatting commands for this and post the results of each step as "CODE". This will give us more detail to work with. And it will tell me how hard it is for people to get the free scripts I put up so I can decide if I need to do something else.
This will provide
index descriptions for the table
a full query plan to look at
row count information on the query
From this we can better have a look at the query. However, as I noted before, your performance problems are likely related to how the process itself works and so the entire process very likely will need to be redone.
Also, you will need: select any dictionary privilege in order to view the v$ tables and dba metadata tables, and select on the data tables in order to run these scripts. If you don't have these privileges, take chocolate to your DBA as a gift, then sit with them, show them your problem, tell them about these scripts and where you got them so they can review them so they feel safe that you are using them, then get the privs or have the DBA do the work for you.
Thanks. Kevin
[Updated on: Mon, 27 October 2014 10:01] Report message to a moderator |
|
|
|
|
|
|
|
Goto Forum:
Current Time: Mon Mar 10 18:44:03 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
