[Home] [Help]
4348: --
4349: begin
4350:
4351: G_ERROR_LOCATION := 16;
4352: insert into oe_upgrade_log
4353: (
4354: header_id,
4355: old_line_id,
4356: old_line_detail_id,
5280:
5281: cursor log_product_lines (p_service_parent_line_id IN NUMBER) IS
5282:
5283: select old_line_id, new_line_id from
5284: oe_upgrade_log_v where
5285: old_line_id = p_service_parent_line_id
5286: and old_line_id <> new_line_id
5287: order by new_line_id;
5288:
5288:
5289: cursor log_service_lines (p_line_id IN NUMBER) IS
5290:
5291: select old_line_id, new_line_id from
5292: oe_upgrade_log_v where
5293: old_line_id = p_line_id
5294: and old_line_id <> new_line_id
5295: order by new_line_id;
5296:
5858: /*Hints are changed to fix the bug 1826762 for the bug1974897*/
5859: select /*+ ORDERED USE_NL(sla1,sla2,upg,oeol)
5860: INDEX (sla1 SO_LINES_N1)
5861: INDEX (sla2 SO_LINES_N18)
5862: INDEX (upg OE_UPGRADE_LOG_N1)
5863: INDEX (oeol OE_ORDER_LINES_U1)
5864: */
5865: sla1.header_id,
5866: sla1.line_id,
5867: sla1.line_number,
5868: sla1.inventory_item_id,
5869: sla1.unit_code
5870: from
5871: so_lines_all sla1, so_lines_all sla2, oe_upgrade_log upg,
5872: oe_order_lines_all oeol
5873: where sla1.header_id = G_HEADER_ID
5874: and sla1.line_type_code = 'PARENT'
5875: and sla1.item_type_code in ('KIT','MODEL','STANDARD')
5883: /* Performance changes bug 1961136 */
5884: cursor c3 is
5885:
5886: SELECT /*+ ORDERED USE_NL(OOLA SLA UPG) INDEX(oola OE_ORDER_LINES_N1)
5887: INDEX(upg OE_UPGRADE_LOG_N6) index(sla SO_LINES_U1) */
5888: OOLA.LINE_ID,OOLA.LINE_NUMBER
5889: FROM OE_ORDER_LINES_ALL OOLA,
5890: OE_UPGRADE_LOG UPG ,
5891: SO_LINES_ALL SLA
5886: SELECT /*+ ORDERED USE_NL(OOLA SLA UPG) INDEX(oola OE_ORDER_LINES_N1)
5887: INDEX(upg OE_UPGRADE_LOG_N6) index(sla SO_LINES_U1) */
5888: OOLA.LINE_ID,OOLA.LINE_NUMBER
5889: FROM OE_ORDER_LINES_ALL OOLA,
5890: OE_UPGRADE_LOG UPG ,
5891: SO_LINES_ALL SLA
5892: WHERE OOLA.HEADER_ID = G_HEADER_ID AND
5893: OOLA.LINE_ID = UPG.NEW_LINE_ID AND
5894: UPG.OLD_LINE_ID = SLA.LINE_ID AND
5912: and parent_line_id is null -- To filter out options (included on Leena's instn.)
5913: -- and sla.line_id in
5914: and exists
5915: (select /*+ INDEX(ln OE_ORDER_LINES_N1) */lg.old_line_id
5916: from oe_upgrade_log_v lg, oe_order_lines_all ln
5917: where ln.header_id = sla.header_id
5918: and lg.old_line_id = sla.line_id
5919: and lg.new_line_id = ln.line_id
5920: and ln.item_type_code not in ('INCLUDED','CONFIG')
5921: group by lg.old_line_id
5922: having count(*) > 1);
5923:
5924: cursor c7 is
5925: select /*+ INDEX(oe_upgrade_log OE_UPGRADE_LOG_N1) */ --bug5909908
5926: new_line_id line_id,
5927: new_line_number line_number
5928: from oe_upgrade_log
5929: where old_line_id = v_line_id
5924: cursor c7 is
5925: select /*+ INDEX(oe_upgrade_log OE_UPGRADE_LOG_N1) */ --bug5909908
5926: new_line_id line_id,
5927: new_line_number line_number
5928: from oe_upgrade_log
5929: where old_line_id = v_line_id
5930: and old_line_id is not null;
5931: v_ctr number;
5932: p_line_number number;
7139: /* bring old_line_id from log table using the L_Line_id as the new_line_id */
7140:
7141: G_OLD_LINE_ID := 0;
7142: begin
7143: select max(old_line_id) into G_OLD_LINE_ID from oe_upgrade_log_v
7144: where new_line_id = p_orig_line_id;
7145: exception
7146: when others then
7147: G_OLD_LINE_ID := 0;
8842:
8843: cursor log_product_lines (p_service_parent_line_id IN NUMBER) IS
8844:
8845: select old_line_id, new_line_id from
8846: oe_upgrade_log_v where
8847: old_line_id = p_service_parent_line_id
8848: and old_line_id <> new_line_id
8849: order by new_line_id;
8850:
8850:
8851: cursor log_service_lines (p_line_id IN NUMBER) IS
8852:
8853: select old_line_id, new_line_id from
8854: oe_upgrade_log_v where
8855: old_line_id = p_line_id
8856: and old_line_id <> new_line_id
8857: order by new_line_id;
8858: