29: AND CL.ENTITY_ID = CIC.INVENTORY_ITEM_ID
30: FOR UPDATE OF CIC.LAST_UPDATED_BY NOWAIT;
31:
32: /* Removed hint from follwing Insert stmt for POSCO issue: Bug #1665358.
33: + ORDERED USE_NL(CICD CL) INDEX(CL CST_LISTS_U1) INDEX(CICD CST_ITEM_COST_DETAILS_N1)
34: */
35: CURSOR cd is
36: SELECT
37: CICD.INVENTORY_ITEM_ID
36: SELECT
37: CICD.INVENTORY_ITEM_ID
38: FROM
39: CST_LISTS CL,
40: CST_ITEM_COST_DETAILS CICD
41: WHERE CICD.ORGANIZATION_ID = l_organization_id
42: AND CL.LIST_ID = l_list_id
43: AND CL.ENTITY_ID = CICD.INVENTORY_ITEM_ID
44: AND CICD.COST_TYPE_ID = l_cost_type_id
86: BEGIN
87: if table_name = 'CST_ITEM_COSTS' then
88: OPEN cc;
89: status := SQLCODE;
90: elsif table_name = 'CST_ITEM_COST_DETAILS' then
91: OPEN cd;
92: status := SQLCODE;
93: elsif table_name = 'CST_RESOURCE_COSTS' then
94: OPEN crc;
143: end if;
144: return(status);
145: end if;
146: /*
147: ** Lock the table of CST_ITEM_COST_DETAILS
148: */
149: status := -54;
150: while (counter < NUM_TRIES and status = -54) LOOP
151: status := cstpupdt.cstulock('CST_ITEM_COST_DETAILS',
147: ** Lock the table of CST_ITEM_COST_DETAILS
148: */
149: status := -54;
150: while (counter < NUM_TRIES and status = -54) LOOP
151: status := cstpupdt.cstulock('CST_ITEM_COST_DETAILS',
152: l_cost_type_id,
153: l_organization_id,
154: l_list_id,err_buf);
155: if status = -54 then
286: FROM cst_item_costs_temp cict;
287:
288: BEGIN
289: FOR del_cicd_cur IN del_cur2 loop
290: DELETE CST_ITEM_COST_DETAILS cicd
291: WHERE organization_id = l_organization_id
292: AND cost_type_id = 1
293: AND inventory_item_id = del_cicd_cur.inventory_item_id;
294: END LOOP;
496: AND NOT EXISTS( ( SELECT rollup_source_type, level_type,
497: cost_element_id, Nvl(resource_id, -1),
498: basis_type, basis_factor,
499: usage_rate_or_amount, item_cost
500: FROM CST_ITEM_COST_DETAILS CICD1
501: WHERE CICD1.organization_id = l_org_id
502: AND CICD1.cost_type_id = b_cm_frozen_standard
503: AND CICD1.inventory_item_id = CIC2.inventory_item_id)
504: MINUS
505: ( SELECT rollup_source_type, level_type,
506: cost_element_id, Nvl(resource_id, -1),
507: basis_type, basis_factor,
508: usage_rate_or_amount, item_cost
509: FROM CST_ITEM_COST_DETAILS CICD2
510: WHERE CICD2.organization_id = l_org_id
511: AND CICD2.cost_type_id = l_cost_type_id
512: AND CICD2.inventory_item_id = CIC2.inventory_item_id))));
513: