eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

[Home] [Help]

APPS.CSTPLENG dependencies on CST_INV_LAYERS

Line 1198: from cst_inv_layers cil, cst_inv_layer_cost_details cilcd

1194: i_req_id,
1195: i_prg_appl_id,
1196: i_prg_id,
1197: sysdate
1198: from cst_inv_layers cil, cst_inv_layer_cost_details cilcd
1199: where cil.inv_layer_id = cilcd.inv_layer_id
1200: and cil.layer_id = cilcd.layer_id
1201: and cil.inv_layer_id = i_actual_layer_id;
1202:

Line 1273: from cst_inv_layers cil, cst_inv_layer_cost_details cilcd

1269: i_req_id,
1270: i_prg_appl_id,
1271: i_prg_id,
1272: sysdate
1273: from cst_inv_layers cil, cst_inv_layer_cost_details cilcd
1274: where cil.inv_layer_id = cilcd.inv_layer_id
1275: and cil.layer_id = cilcd.layer_id
1276: and cil.inv_layer_id = i_actual_layer_id;
1277:

Line 1315: -- create inventory layers using the sequence cst_inv_layers_s --

1311: -- * If layer created has positive quantity, then replenish all --
1312: -- negative inventory layers --
1313: -- --
1314: -- PURPOSE: --
1315: -- create inventory layers using the sequence cst_inv_layers_s --
1316: -- --
1317: -- PARAMETERS: --
1318: -- i_txn_qty : primary quantity --
1319: -- i_interorg_rec : interorg shimpment (= 0), --

Line 1573: FROM cst_inv_layers

1569: /* Find the inventory layer last created */
1570: l_stmt_num := 30;
1571: SELECT nvl(MAX(inv_layer_id),-1)
1572: INTO l_inv_layer_id
1573: FROM cst_inv_layers
1574: WHERE layer_id = i_layer_id;
1575:
1576: IF (l_debug = 'Y') THEN
1577: FND_FILE.PUT_LINE(FND_FILE.LOG,'Last Inventory Layer : ' || l_inv_layer_id);

Line 1716: FROM cst_inv_layers cil,

1712: /* Check that there is no negative layer other than the last inventory layer that is negative */
1713: l_stmt_num := 55;
1714: SELECT COUNT(*)
1715: INTO l_count
1716: FROM cst_inv_layers cil,
1717: cst_quantity_layers cql
1718: WHERE cql.layer_id = i_layer_id
1719: AND cil.inv_layer_id = l_inv_layer_id
1720: AND cil.layer_quantity < 0

Line 1733: FROM cst_inv_layers

1729: created the last inventory layer */
1730: l_stmt_num := 60;
1731: SELECT create_transaction_id
1732: INTO l_last_txn_id
1733: FROM cst_inv_layers
1734: WHERE inv_layer_id = l_inv_layer_id;
1735:
1736: IF (l_debug = 'Y') THEN
1737: FND_FILE.PUT_LINE(FND_FILE.LOG,'l_last_txn_id '||l_last_txn_id);

Line 1841: FROM cst_inv_layers

1837:
1838:
1839: SELECT nvl(SUM(layer_cost),0)
1840: INTO l_last_layer_cost
1841: FROM cst_inv_layers
1842: WHERE organization_id = i_org_id
1843: AND layer_id = i_layer_id
1844: AND inv_layer_id = l_inv_layer_id;
1845:

Line 1913: UPDATE cst_inv_layers

1909: l_src_number := get_source_number(i_txn_id,i_txn_src_type,l_src_id);
1910:
1911: /* Update last created inventory layer */
1912: l_stmt_num := 90;
1913: UPDATE cst_inv_layers
1914: SET creation_quantity = creation_quantity + i_txn_qty,
1915: layer_quantity = layer_quantity + i_txn_qty,
1916: transaction_source_id = decode(transaction_source_id, l_src_id, l_src_id, null),
1917: transaction_source = decode(transaction_source, l_src_number, l_src_number, null),

Line 1931: SELECT cst_inv_layers_s.nextval

1927: WHERE inv_layer_id = l_inv_layer_id;
1928: ELSE
1929: /* Generate Inv Layer ID */
1930: l_stmt_num := 95;
1931: SELECT cst_inv_layers_s.nextval
1932: INTO l_inv_layer_id
1933: FROM dual;
1934:
1935: IF (l_debug = 'Y') THEN

Line 1962: /* Create inventory layer with 0 cost in CST_INV_LAYERS */

1958: /* Get transaction source name */
1959: l_stmt_num := 110;
1960: l_src_number := get_source_number(i_txn_id,i_txn_src_type,l_src_id);
1961:
1962: /* Create inventory layer with 0 cost in CST_INV_LAYERS */
1963: l_stmt_num := 115;
1964: INSERT
1965: INTO cst_inv_layers (
1966: layer_id,

Line 1965: INTO cst_inv_layers (

1961:
1962: /* Create inventory layer with 0 cost in CST_INV_LAYERS */
1963: l_stmt_num := 115;
1964: INSERT
1965: INTO cst_inv_layers (
1966: layer_id,
1967: inv_layer_id,
1968: organization_id,
1969: inventory_item_id,

Line 2070: UPDATE cst_inv_layers

2066: FND_FILE.PUT_LINE(FND_FILE.LOG, sql%rowcount || ' records copied from mclacd for ' || l_inv_layer_id);
2067: /* Update layer cost in CIL */
2068: l_stmt_num := 130;
2069: IF (nvl(i_interorg_rec,-1) <> 3) THEN
2070: UPDATE cst_inv_layers
2071: SET layer_cost = (
2072: SELECT SUM(layer_cost)
2073: FROM cst_inv_layer_cost_details
2074: WHERE inv_layer_id = l_inv_layer_id

Line 2099: sql_stmt := 'select inv_layer_id, layer_quantity from cst_inv_layers ' ||

2095: END IF; /* l_create = 0 */
2096:
2097: /* Create cursor to find any negative layers, order in FIFO/LIFO method */
2098: IF (i_txn_qty > 0) THEN
2099: sql_stmt := 'select inv_layer_id, layer_quantity from cst_inv_layers ' ||
2100: 'where layer_id = :i and layer_quantity < 0 order by creation_date';
2101:
2102: IF (i_cost_method = 6) THEN
2103: sql_stmt := sql_stmt || ' desc,inv_layer_id desc';

Line 2173: UPDATE cst_inv_layers

2169: /* Update quantity for the negative layer and the quantity available
2170: for replenishment */
2171: IF (nvl(i_interorg_rec,-1) <> 3) THEN
2172: l_stmt_num := 140;
2173: UPDATE cst_inv_layers
2174: SET layer_quantity = l_neg_layer_qty + l_qty
2175: WHERE inv_layer_id = l_neg_layer_id;
2176: END IF;
2177:

Line 2224: UPDATE cst_inv_layers

2220: IF (l_neg_layer_iD=l_inv_layer_id) THEN
2221:
2222: IF (nvl(i_interorg_rec,-1) <> 3) THEN
2223:
2224: UPDATE cst_inv_layers
2225: SET layer_quantity=layer_quantity-l_qty
2226: WHERE inv_layer_id = l_inv_layer_id;
2227: END IF;
2228:

Line 2232: UPDATE cst_inv_layers

2228:
2229: ELSE
2230:
2231: IF (nvl(i_interorg_rec,-1) <> 3) THEN
2232: UPDATE cst_inv_layers
2233: SET layer_quantity = l_qty_available
2234: WHERE inv_layer_id = l_inv_layer_id;
2235: END IF;
2236: END IF;/* END OF #BUG6722228 */

Line 2333: from cst_inv_layers

2329: FND_FILE.PUT_LINE(FND_FILE.LOG,'Consuming inventory layers from CG layer : ' || to_char(i_layer_id));
2330: end if;
2331: select count(*)
2332: into l_layers_exist
2333: from cst_inv_layers
2334: where layer_id = i_layer_id;
2335:
2336: if (l_layers_exist = 0) then
2337: if (l_debug = 'Y') then

Line 2857: update cst_inv_layers

2853: end if;
2854: end if; /* l_err_num = 999 */
2855: l_stmt_num := 70;
2856: if ((nvl(i_interorg_rec,-1) <> 3) and (i_exp_flag <> 1)) then
2857: update cst_inv_layers
2858: set layer_quantity = nvl(layer_quantity,0)-l_inv_layer_table(i).layer_quantity
2859: where inv_layer_id = l_inv_layer_table(i).inv_layer_id;
2860: if (l_debug = 'Y') then
2861: FND_FILE.PUT_LINE(FND_FILE.LOG,'CIL.layer_qty changed by ' || to_char(l_inv_layer_table(i).layer_quantity));

Line 2971: FROM cst_inv_layers

2967: IF i_cost_method = 5 THEN
2968: l_stmt_num := 10;
2969: SELECT MIN(inv_layer_id)
2970: INTO l_inv_layer_id
2971: FROM cst_inv_layers
2972: WHERE layer_id = i_layer_id
2973: AND layer_quantity > 0;
2974: ELSE
2975: l_stmt_num := 15;

Line 2978: FROM cst_inv_layers

2974: ELSE
2975: l_stmt_num := 15;
2976: SELECT MAX(inv_layer_id)
2977: INTO l_inv_layer_id
2978: FROM cst_inv_layers
2979: WHERE layer_id = i_layer_id
2980: AND layer_quantity > 0;
2981: END IF;
2982: /* If no positive layers exist, pick the latest layer */

Line 2987: FROM cst_inv_layers

2983: IF l_inv_layer_id IS NULL THEN
2984: l_stmt_num := 20;
2985: SELECT MAX(inv_layer_id)
2986: INTO l_inv_layer_id
2987: FROM cst_inv_layers
2988: WHERE layer_id = i_layer_id;
2989: END IF;
2990: l_inv_layer_rec.inv_layer_id := l_inv_layer_id;
2991: l_inv_layer_rec.layer_quantity := l_required_qty;

Line 3028: FROM cst_inv_layers

3024: END IF;
3025: l_stmt_num := 30;
3026: SELECT inv_layer_id, layer_quantity
3027: INTO l_inv_layer_rec.inv_layer_id,l_inv_layer_rec.layer_quantity
3028: FROM cst_inv_layers
3029: WHERE inv_layer_id = l_custom_layer -- inventory layer id exists
3030: AND layer_id = i_layer_id; -- correct organization, item, cost group
3031: IF l_inv_layer_rec.layer_quantity > 0 THEN
3032: IF l_required_qty < l_inv_layer_rec.layer_quantity THEN

Line 3057: FROM cst_inv_layers

3053: END IF;
3054: l_stmt_num := 40;
3055: SELECT count(*)
3056: INTO l_pos_layer_exist
3057: FROM cst_inv_layers
3058: WHERE layer_id = i_layer_id
3059: AND inv_layer_id <> l_custom_layer
3060: AND layer_quantity > 0;
3061: IF l_pos_layer_exist = 0 THEN

Line 3112: FROM cst_inv_layers

3108: l_stmt_num := 55;
3109: BEGIN
3110: SELECT inv_layer_id, l_custom_layers(i).layer_quantity
3111: INTO l_inv_layer_rec.inv_layer_id, l_inv_layer_rec.layer_quantity
3112: FROM cst_inv_layers
3113: WHERE inv_layer_id = l_custom_layers(i).inv_layer_id -- valid inventory layer id
3114: AND layer_id = i_layer_id -- valid org, item, cost group
3115: AND layer_quantity >=
3116: l_custom_layers(i).layer_quantity -- enough quantity

Line 3191: ||' FROM cst_inv_layers'

3187: END IF;
3188: END;
3189: l_stmt_num := 75;
3190: sql_stmt := 'SELECT inv_layer_id, layer_quantity'
3191: ||' FROM cst_inv_layers'
3192: ||' WHERE create_transaction_id = :i'
3193: ||' AND layer_quantity > 0'
3194: ||' AND inv_layer_id <> :j';
3195: IF l_layers_hook > 0 THEN

Line 3227: sql_stmt := 'SELECT inv_layer_id,layer_quantity FROM cst_inv_layers'

3223: IF l_debug = 'Y' THEN
3224: fnd_file.put_line(fnd_file.log,'Trying other layers with the same source');
3225: END IF;
3226: l_stmt_num := 90;
3227: sql_stmt := 'SELECT inv_layer_id,layer_quantity FROM cst_inv_layers'
3228: ||' WHERE layer_id = :i AND transaction_source_id = :j AND layer_quantity > 0 '
3229: ||' AND create_transaction_id <> :k AND inv_layer_id <> :l';
3230: IF l_layers_hook > 0 THEN
3231: l_stmt_num := 95;

Line 3273: sql_stmt := 'SELECT inv_layer_id, layer_quantity FROM cst_inv_layers'

3269: 'Driving earliest/latest layer with the same source negative?'
3270: );
3271: END IF;
3272: l_stmt_num := 115;
3273: sql_stmt := 'SELECT inv_layer_id, layer_quantity FROM cst_inv_layers'
3274: ||' WHERE layer_id = :i AND inv_layer_id <> :j'
3275: ||' AND NVL(transaction_source_id,-2) <> :k'
3276: ||' AND layer_quantity > 0';
3277: IF l_layers_hook > 0 THEN

Line 3295: FROM cst_inv_layers

3291: IF i_cost_method = 5 THEN
3292: l_stmt_num := 125;
3293: SELECT MAX(inv_layer_id)
3294: INTO l_inv_layer_rec.inv_layer_id
3295: FROM cst_inv_layers
3296: WHERE layer_id = i_layer_id
3297: AND transaction_source_id = l_source_id;
3298: ELSE
3299: l_stmt_num := 130;

Line 3302: FROM cst_inv_layers

3298: ELSE
3299: l_stmt_num := 130;
3300: SELECT MIN(inv_layer_id)
3301: INTO l_inv_layer_rec.inv_layer_id
3302: FROM cst_inv_layers
3303: WHERE layer_id = i_layer_id
3304: AND transaction_source_id = l_source_id;
3305: END IF;
3306: IF l_inv_layer_rec.inv_layer_id IS NOT NULL THEN

Line 3329: sql_stmt := 'SELECT inv_layer_id,layer_quantity FROM cst_inv_layers WHERE layer_id = :i'

3325: IF l_debug = 'Y' THEN
3326: fnd_file.put_line(fnd_file.log,'General consumption');
3327: END IF;
3328: l_stmt_num := 140;
3329: sql_stmt := 'SELECT inv_layer_id,layer_quantity FROM cst_inv_layers WHERE layer_id = :i'
3330: ||' AND inv_layer_id <> :j AND NVL(transaction_source_id,-2) <> :k'
3331: ||' AND layer_quantity > 0';
3332: l_stmt_num := 145;
3333: IF l_layers_hook > 0 THEN

Line 3373: sql_stmt := 'SELECT inv_layer_id,layer_quantity FROM cst_inv_layers WHERE layer_id = :i';

3369: IF l_debug = 'Y' THEN
3370: FND_FILE.PUT_LINE(FND_FILE.LOG,'l_neg_qty ' || to_char(l_required_qty));
3371: END IF;
3372: l_stmt_num := 165;
3373: sql_stmt := 'SELECT inv_layer_id,layer_quantity FROM cst_inv_layers WHERE layer_id = :i';
3374: IF i_cost_method = 5 THEN
3375: sql_stmt := sql_stmt || ' ORDER BY creation_date DESC,inv_layer_id DESC';
3376: ELSE
3377: sql_stmt := sql_stmt || ' ORDER BY creation_date,inv_layer_id';

Line 4441: from cst_inv_layers cil

4437: ********************************************************************/
4438: -- get the total layer quantity from cil
4439: select sum(cil.layer_quantity)
4440: into l_total_layer_qty
4441: from cst_inv_layers cil
4442: where cil.layer_id = i_layer_id;
4443:
4444: /* Update clcd only if i_no_update_qty flag is not set and the total layer quantity is not zero */
4445:

Line 4452: from cst_inv_layers cil

4448: l_stmt_num := 20;
4449: /*Commented for bug 15979260-- get the total layer quantity from cil
4450: select sum(cil.layer_quantity)
4451: into l_total_layer_qty
4452: from cst_inv_layers cil
4453: where cil.layer_id = i_layer_id;*/
4454:
4455: /* Added for Bug 15979260 */
4456: -- get the total cost layer quantity from cql

Line 4571: cst_inv_layers cil

4567: i_prg_id,
4568: sysdate,
4569: (sum((cilcd.layer_cost*cil.layer_quantity)/l_total_layer_qty)) -- modified for bug#3835412
4570: from cst_inv_layer_cost_details cilcd,
4571: cst_inv_layers cil
4572: where cil.layer_id = i_layer_id*/
4573: /*commented for bug 15979260
4574: and cil.organization_id = i_org_id
4575: and cil.inventory_item_id = i_item_id*/

Line 4971: cst_inv_layers cil,

4967: 0
4968: )
4969: )
4970: FROM mtl_cst_txn_cost_details ctcd,
4971: cst_inv_layers cil,
4972: cst_inv_layer_cost_details cilcd
4973: WHERE ctcd.transaction_id = i_txn_id
4974: AND ctcd.organization_id = i_org_id
4975: AND cil.layer_id = i_layer_id

Line 5046: Update cst_inv_layers

5042: and mclacd.layer_id = i_layer_id
5043: and mclacd.inv_layer_id = l_inv_layer_id;
5044:
5045: /********************************************************************
5046: ** Update cst_inv_layers **
5047: ********************************************************************/
5048: l_stmt_num := 50;
5049:
5050: update cst_inv_layers cil

Line 5050: update cst_inv_layers cil

5046: ** Update cst_inv_layers **
5047: ********************************************************************/
5048: l_stmt_num := 50;
5049:
5050: update cst_inv_layers cil
5051: set (last_updated_by,
5052: last_update_date,
5053: last_update_login,
5054: request_id,

Line 5089: from cst_inv_layers cil

5085:
5086: -- Get transaction quantity
5087: select cil.layer_quantity
5088: into l_layer_qty
5089: from cst_inv_layers cil
5090: where cil.layer_id = i_layer_id
5091: and cil.inv_layer_id = l_inv_layer_id;
5092:
5093: FND_FILE.PUT_LINE(FND_FILE.LOG, 'layer qty = ' || to_char(l_layer_qty));

Line 5214: from cst_inv_layers

5210: if (l_cost_method = 5) then
5211: /* Try to return the first positive layer */
5212: select nvl(min(inv_layer_id),0)
5213: into l_inv_layer_id
5214: from cst_inv_layers
5215: where layer_id = i_layer_id
5216: and layer_quantity > 0;
5217: /* If there is no positive layer, return the last layer */
5218: if l_inv_layer_id = 0 then

Line 5221: from cst_inv_layers

5217: /* If there is no positive layer, return the last layer */
5218: if l_inv_layer_id = 0 then
5219: select nvl(max(inv_layer_id),0)
5220: into l_inv_layer_id
5221: from cst_inv_layers
5222: where layer_id = i_layer_id;
5223: end if;
5224: elsif (l_cost_method = 6) then
5225: /* Try to return the last positive layer */

Line 5228: from cst_inv_layers

5224: elsif (l_cost_method = 6) then
5225: /* Try to return the last positive layer */
5226: select nvl(max(inv_layer_id), 0)
5227: into l_inv_layer_id
5228: from cst_inv_layers
5229: where layer_id = i_layer_id
5230: and layer_quantity > 0;
5231: /* If there is no positive layer, return the first layer */
5232: if l_inv_layer_id = 0 then

Line 5235: from cst_inv_layers

5231: /* If there is no positive layer, return the first layer */
5232: if l_inv_layer_id = 0 then
5233: select nvl(min(inv_layer_id),0)
5234: into l_inv_layer_id
5235: from cst_inv_layers
5236: where layer_id = i_layer_id;
5237: end if;
5238: end if;
5239:

Line 5243: select cst_inv_layers_s.nextval

5239:
5240: if (l_inv_layer_id = 0) then
5241: /* No inv layers exist: Hence create one with 0 qty,cost */
5242:
5243: select cst_inv_layers_s.nextval
5244: into l_inv_layer_id
5245: from dual;
5246:
5247: insert into cst_inv_layers (

Line 5247: insert into cst_inv_layers (

5243: select cst_inv_layers_s.nextval
5244: into l_inv_layer_id
5245: from dual;
5246:
5247: insert into cst_inv_layers (
5248: create_transaction_id,
5249: layer_id,
5250: inv_layer_id,
5251: organization_id,

Line 5597: FROM CST_INV_LAYERS CIL,

5593:
5594:
5595: cursor cost_elmt_ids is
5596: SELECT CILCD.COST_ELEMENT_ID
5597: FROM CST_INV_LAYERS CIL,
5598: CST_INV_LAYER_COST_DETAILS CILCD
5599: WHERE CIL.LAYER_ID = l_layer_id
5600: AND CIL.INV_LAYER_ID = i_inv_layer_id
5601: AND CILCD.LAYER_ID = l_layer_id

Line 5655: FROM CST_INV_LAYERS

5651: end if;
5652:
5653: SELECT LAYER_COST
5654: INTO cil_layer_cost
5655: FROM CST_INV_LAYERS
5656: WHERE LAYER_ID = l_layer_id
5657: AND INV_LAYER_ID = i_inv_layer_id;
5658:
5659: /* for the case of layer cost equal zero */

Line 5712: FROM CST_INV_LAYERS CIL, CST_INV_LAYER_COST_DETAILS CILCD

5708: i_request_id,
5709: i_prog_appl_id,
5710: i_prog_id,
5711: sysdate
5712: FROM CST_INV_LAYERS CIL, CST_INV_LAYER_COST_DETAILS CILCD
5713: WHERE CIL.LAYER_ID = l_layer_id
5714: AND CIL.INV_LAYER_ID = i_inv_layer_id
5715: AND CILCD.LAYER_ID = l_layer_id
5716: AND CILCD.INV_LAYER_ID = i_inv_layer_id;

Line 6285: update cst_inv_layers

6281: i_login_id IN NUMBER)
6282: IS
6283:
6284: Begin
6285: update cst_inv_layers
6286: set last_updated_by = i_userid,
6287: last_update_date = sysdate,
6288: last_update_login = i_login_id,
6289: layer_cost = 0,

Line 6297: from cst_inv_layers

6293: and inventory_item_id = i_item_id;
6294:
6295: delete from cst_inv_layer_cost_details
6296: where inv_layer_id IN (select inv_layer_id
6297: from cst_inv_layers
6298: where organization_id = i_org_id
6299: and inventory_item_id = i_item_id);
6300: EXCEPTION
6301: when NO_DATA_FOUND then null;