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 Go to next message
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 #626229 is a reply to message #625557] Thu, 23 October 2014 01:20 Go to previous messageGo to next message
michael_bialik
Messages: 621
Registered: July 2006
Senior Member
How many rows are in that table?
How the index XXPO_BOM_COMPS_REG_N222 is defined?

In order to support your query it's supposed to be defined as ( SEQUENCE_ID, FLAG ).

However I would rewrite the main loop to retrieve all rows having "FLAG IN ('N', 'EXP')" and updating these rows instead of counting it each time.

HTH

[Updated on: Thu, 23 October 2014 01:26]

Report message to a moderator

Re: Query in Loop causing performance issue [message #626232 is a reply to message #626229] Thu, 23 October 2014 01:48 Go to previous messageGo to next message
nishantranjan00787
Messages: 36
Registered: July 2014
Location: india
Member
Sequence id and flag
There are 8millions record in this custom table and already i have created index as N222 on Sequence id and flag.But still performance issue
Re: Query in Loop causing performance issue [message #626238 is a reply to message #626232] Thu, 23 October 2014 04:26 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Any reason for the optimizer mode been set to choose? Are the stats up to date? If the optimizer goal is set to CHOOSE, then CBO will be used for all queries that touch AT LEAST
one table that was analyzed (or access an object that requires the CBO as above). RBO will be used
otherwise.
Re: Query in Loop causing performance issue [message #626341 is a reply to message #625557] Sun, 26 October 2014 14:08 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
There are several missing pieces of information to perform a good analysis.

1. the plan data is far too incomplete to know what is happening.  For example, no PREDICATE INFORMATION has been provided and so we cannot know what Oracle is doing with ACCESS/FILTER/COVERAGE related to the index.

2. there is no proof of time spent in various parts of the PL/SQL.

3. it is beyond me how someone can say "we use this index" and then not post the details of the index.  How do you expect anyone to validate that your index is good if you don't tell them what columns are in the index and in what order?

4. and of course you have hidden all the cursor SQL so we can't consider any of that.

I am not going to spend a lot of time reviewing the procedure since it is clear you did not spend a lot of time trying to post necessary information. But I can offer these comments, some of which is a duplicate of what has already been said.

1. the use of COUNT(*) to check row counts when all you really need is one to exist is one of the top-50 common mistakes.

2. the need to record messages after every change would also be very expensive, particularly if you are doing to row by row which is what use of ROWID suggests.

3. use of COMMIT in multiple places renders the entire process suspect in its validity.

In view of these major problems, if I were doing a code review of this work, I would reject it and tell the developers to re-think the entire process with performance and transaction semantics at the fore of the analysis. For example, try rewriting the process without PL/SQL. You first step should be to write down on paper a description of what the process is supposed to do with thinking about the code or how the desired result will be achieved. This should be something your business users will understand. Then start thinking about how to do it without using PL/SQL.

Good luck. Kevin
Re: Query in Loop causing performance issue [message #626348 is a reply to message #626341] Mon, 27 October 2014 01:01 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #626350 is a reply to message #626349] Mon, 27 October 2014 01:06 Go to previous messageGo to next message
nishantranjan00787
Messages: 36
Registered: July 2014
Location: india
Member

CREATE INDEX XXPO_BOM_COMPS_REG_N123 ON XXPO_BOM_COMPS_REG
(SEQUENCE_ID, FLAG)
NOLOGGING
TABLESPACE EMRPOX
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          2M
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       2147483645
            PCTINCREASE      0
            FREELISTS        1
            FREELIST GROUPS  1
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;

Re: Query in Loop causing performance issue [message #626351 is a reply to message #626350] Mon, 27 October 2014 01:09 Go to previous messageGo to next message
nishantranjan00787
Messages: 36
Registered: July 2014
Location: india
Member

SELECT COUNT (1) 
FROM
 XXPO_BOM_COMPS_REG WHERE SEQUENCE_ID = :B1 AND FLAG IN ('N', 'EXP') AND 
  ROWNUM < 2


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute  58831      1.71       1.73          0          0          0           0
Fetch    58830  64185.42   64250.41      24255  779262180          0       58830
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total   117662  64187.13   64252.15      24255  779262180          0       58830

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=13246 pr=13210 pw=0 time=5095035 us)
         1          1          1   COUNT STOPKEY (cr=13246 pr=13210 pw=0 time=5095018 us)
         1          1          1    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)


Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   MODE: CHOOSE
      1   SORT (AGGREGATE)
      1    COUNT (STOPKEY)
      1     INDEX   MODE: ANALYZED (FAST FULL SCAN) OF 
                'XXPO_BOM_COMPS_REG_N123' (INDEX)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  db file scattered read                       1839        0.00          0.01
  latch: cache buffers chains                   119        0.00          0.00
  db file sequential read                     10658        1.19         49.12
  latch free                                      3        0.00          0.00
********************************************************************************


Re: Query in Loop causing performance issue [message #626352 is a reply to message #626351] Mon, 27 October 2014 01:12 Go to previous messageGo to next message
nishantranjan00787
Messages: 36
Registered: July 2014
Location: india
Member
Hi Michae,

Any comment
Re: Query in Loop causing performance issue [message #626392 is a reply to message #626352] Mon, 27 October 2014 09:45 Go to previous messageGo to next message
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

Re: Query in Loop causing performance issue [message #626680 is a reply to message #626392] Thu, 30 October 2014 11:45 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
any update on this?
Re: Query in Loop causing performance issue [message #626796 is a reply to message #626680] Mon, 03 November 2014 00:48 Go to previous message
michael_bialik
Messages: 621
Registered: July 2006
Senior Member
Can you post XXPO_BOM_COMPS_REG table definitions?
Previous Topic: SQL tuning advisor being retarted
Next Topic: Delete taking long due to index maintenance?
Goto Forum:
  


Current Time: Thu Mar 28 11:34:15 CDT 2024