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.

APPS.INV_TURNS dependencies on MTL_BIS_INV_BY_PERIOD

Line 78: FROM mtl_bis_inv_by_period

74: intransit
75: INTO p_prev_onhand,
76: p_prev_wip,
77: p_prev_intransit
78: FROM mtl_bis_inv_by_period
79: WHERE organization_id = p_organization_id
80: AND inventory_item_id = p_inventory_item_id
81: AND acct_period_id =
82: (SELECT max(acct_period_id)

Line 83: FROM mtl_bis_inv_by_period

79: WHERE organization_id = p_organization_id
80: AND inventory_item_id = p_inventory_item_id
81: AND acct_period_id =
82: (SELECT max(acct_period_id)
83: FROM mtl_bis_inv_by_period
84: WHERE organization_id = p_organization_id
85: AND inventory_item_id = p_inventory_item_id
86: AND acct_period_id < p_acct_period_id);
87:

Line 121: UPDATE mtl_bis_inv_by_period

117: p_prev_intransit IN NUMBER,
118: p_return_status OUT NOCOPY VARCHAR2 )
119: IS
120: BEGIN
121: UPDATE mtl_bis_inv_by_period
122: SET bop_onhand = nvl(p_prev_onhand,0),
123: bop_wip = nvl(p_prev_wip,0) ,
124: bop_intransit = nvl(p_prev_intransit,0)
125: WHERE organization_id = p_organization_id

Line 187: FROM mtl_bis_inv_by_period mbibp

183: mbibp.bop_wip,
184: mbibp.wip,
185: mbibp.bop_intransit,
186: mbibp.intransit
187: FROM mtl_bis_inv_by_period mbibp
188: WHERE mbibp.acct_period_id = p_acct_period_id
189: AND mbibp.organization_id = p_organization_id
190: ORDER BY
191: mbibp.organization_id,

Line 339: DELETE FROM MTL_BIS_INV_BY_PERIOD

335: l_user_id := FND_GLOBAL.USER_ID;
336:
337: SAVEPOINT CLOSED_TB;
338:
339: DELETE FROM MTL_BIS_INV_BY_PERIOD
340: WHERE organization_id = p_organization_id
341: AND acct_period_id = p_period_id;
342:
343: INSERT INTO MTL_BIS_INV_BY_PERIOD

Line 343: INSERT INTO MTL_BIS_INV_BY_PERIOD

339: DELETE FROM MTL_BIS_INV_BY_PERIOD
340: WHERE organization_id = p_organization_id
341: AND acct_period_id = p_period_id;
342:
343: INSERT INTO MTL_BIS_INV_BY_PERIOD
344: (organization_id, acct_period_id, inventory_item_id, onhand, wip,
345: intransit, cogs, last_update_date, last_updated_by, creation_date,
346: created_by, last_update_login, request_id, program_application_id,
347: program_id, program_update_date)

Line 421: Type item_id IS TABLE OF MTL_BIS_INV_BY_PERIOD.inventory_item_id%TYPE;

417: -- Changes for bug2856158
418: l_period_start_date DATE;
419: l_schedule_close_date DATE;
420: -- Define a table type parameters
421: Type item_id IS TABLE OF MTL_BIS_INV_BY_PERIOD.inventory_item_id%TYPE;
422: Type item_onhand IS TABLE OF MTL_BIS_INV_BY_PERIOD.onhand%TYPE;
423: -- Local varaible declaration
424: l_item_id item_id;
425: l_item_onhand item_onhand;

Line 422: Type item_onhand IS TABLE OF MTL_BIS_INV_BY_PERIOD.onhand%TYPE;

418: l_period_start_date DATE;
419: l_schedule_close_date DATE;
420: -- Define a table type parameters
421: Type item_id IS TABLE OF MTL_BIS_INV_BY_PERIOD.inventory_item_id%TYPE;
422: Type item_onhand IS TABLE OF MTL_BIS_INV_BY_PERIOD.onhand%TYPE;
423: -- Local varaible declaration
424: l_item_id item_id;
425: l_item_onhand item_onhand;
426: i NUMBER;

Line 434: DELETE FROM MTL_BIS_INV_BY_PERIOD

430: l_user_id := FND_GLOBAL.USER_ID;
431:
432: SAVEPOINT CLOSED_SC;
433:
434: DELETE FROM MTL_BIS_INV_BY_PERIOD
435: WHERE organization_id = p_organization_id
436: AND acct_period_id = p_period_id;
437: -- Begin changes 2856158
438: SELECT period_start_date,schedule_close_date + 1 - (1/(24*3600))

Line 444: /* INSERT INTO MTL_BIS_INV_BY_PERIOD

440: FROM ORG_ACCT_PERIODS
441: WHERE organization_id = p_organization_id
442: AND acct_period_id = p_period_id;
443:
444: /* INSERT INTO MTL_BIS_INV_BY_PERIOD
445: ( organization_id, acct_period_id, inventory_item_id,
446: onhand, wip, intransit, cogs, last_update_date,
447: last_updated_by, creation_date, created_by,
448: last_update_login, request_id, program_application_id,

Line 456: ORG_ACCT_PERIODS OAP, MTL_BIS_INV_BY_PERIOD MBI

452: SUM(NVL(MTA.base_transaction_value,0)) + NVL(MBI.onhand,0),
453: NULL, 0, NULL, SYSDATE, l_user_id, SYSDATE, l_user_id,
454: NULL,NULL,NULL,NULL,NULL
455: FROM MTL_TRANSACTION_ACCOUNTS MTA, MTL_MATERIAL_TRANSACTIONS MMT,
456: ORG_ACCT_PERIODS OAP, MTL_BIS_INV_BY_PERIOD MBI
457: WHERE (MTA.accounting_line_type = 1
458: OR MTA.accounting_line_type =
459: decode(MMT.transaction_action_id, 2, 99, 3, 99, 1))
460: AND sign(MTA.primary_quantity) =

Line 521: INSERT INTO MTL_BIS_INV_BY_PERIOD

517: AND NVL(MMT.OWNING_TP_TYPE,2) = 2
518: GROUP BY MTA.organization_id, MTA.inventory_item_id;
519: IF NVL(l_item_id.FIRST, -1) > 0 THEN /*bug 3180663 - Check l_item_id.FIRST for null*/
520: FORALL i IN l_item_id.FIRST .. l_item_id.LAST
521: INSERT INTO MTL_BIS_INV_BY_PERIOD
522: ( organization_id, acct_period_id, inventory_item_id,
523: onhand, wip, intransit, cogs, last_update_date,
524: last_updated_by, creation_date, created_by,
525: last_update_login, request_id, program_application_id,

Line 533: INSERT INTO MTL_BIS_INV_BY_PERIOD

529: l_user_id,NULL,NULL,NULL,NULL,NULL );
530: END IF; /* bug 3180663*/
531: -- End changes 2856158
532:
533: INSERT INTO MTL_BIS_INV_BY_PERIOD
534: ( organization_id, acct_period_id, inventory_item_id,
535: onhand, wip, intransit, cogs, last_update_date,
536: last_updated_by, creation_date, created_by,
537: last_update_login, request_id, program_application_id,

Line 548: FROM MTL_BIS_INV_BY_PERIOD O_MBI

544: O_MBI.onhand,
545: NULL, 0, NULL, SYSDATE,
546: l_user_id, SYSDATE, l_user_id,
547: NULL,NULL,NULL,NULL,NULL
548: FROM MTL_BIS_INV_BY_PERIOD O_MBI
549: WHERE O_MBI.organization_id = p_organization_id
550: AND O_MBI.acct_period_id = p_last_period_id
551: AND NOT EXISTS
552: (SELECT 1

Line 553: FROM MTL_BIS_INV_BY_PERIOD I_MBI

549: WHERE O_MBI.organization_id = p_organization_id
550: AND O_MBI.acct_period_id = p_last_period_id
551: AND NOT EXISTS
552: (SELECT 1
553: FROM MTL_BIS_INV_BY_PERIOD I_MBI
554: WHERE I_MBI.organization_id = p_organization_id
555: AND I_MBI.acct_period_id = p_period_id
556: AND I_MBI.inventory_item_id = O_MBI.inventory_item_id);
557:

Line 562: FROM MTL_BIS_INV_BY_PERIOD I_MBI

558: /* Replaced these conditions for bug 2747076 :
559:
560: AND O_MBI.inventory_item_id NOT IN
561: (SELECT I_MBI.inventory_item_id
562: FROM MTL_BIS_INV_BY_PERIOD I_MBI
563: WHERE I_MBI.organization_id = p_organization_id
564: AND I_MBI.acct_period_id = p_period_id);
565: */
566: CLOSED_WIP(p_organization_id, p_period_id, l_return_status);

Line 676: FROM MTL_BIS_INV_BY_PERIOD

672: IF l_last_period_id IS NOT NULL THEN
673: BEGIN
674: SELECT NVL(wip,0)
675: INTO l_last_wip
676: FROM MTL_BIS_INV_BY_PERIOD
677: WHERE organization_id = GET_CLOSED_WIPS_REC.org_id
678: AND inventory_item_id = GET_CLOSED_WIPS_REC.item_id
679: AND acct_period_id = l_last_period_id;
680: EXCEPTION

Line 689: UPDATE MTL_BIS_INV_BY_PERIOD

685: ELSE
686: l_last_wip := 0;
687: END IF;
688:
689: UPDATE MTL_BIS_INV_BY_PERIOD
690: SET wip = GET_CLOSED_WIPS_REC.balance + l_last_wip
691: WHERE organization_id = GET_CLOSED_WIPS_REC.org_id
692: AND acct_period_id = GET_CLOSED_WIPS_REC.per_id
693: AND inventory_item_id = GET_CLOSED_WIPS_REC.item_id;

Line 696: INSERT INTO MTL_BIS_INV_BY_PERIOD

692: AND acct_period_id = GET_CLOSED_WIPS_REC.per_id
693: AND inventory_item_id = GET_CLOSED_WIPS_REC.item_id;
694:
695: IF SQL%NOTFOUND THEN
696: INSERT INTO MTL_BIS_INV_BY_PERIOD
697: (organization_id, acct_period_id, inventory_item_id, onhand,
698: wip, intransit, cogs, last_update_date, last_updated_by,
699: creation_date, created_by, last_update_login, request_id,
700: program_application_id, program_id, program_update_date)

Line 791: UPDATE MTL_BIS_INV_BY_PERIOD

787: WHERE organization_id = p_organization_id
788: AND acct_period_id = p_period_id;
789:
790: FOR CLOSED_COGS_REC IN CLOSED_COGS LOOP
791: UPDATE MTL_BIS_INV_BY_PERIOD
792: SET cogs = CLOSED_COGS_REC.cogs
793: WHERE organization_id = p_organization_id
794: AND acct_period_id = p_period_id
795: AND inventory_item_id = CLOSED_COGS_REC.item_id;

Line 798: INSERT INTO MTL_BIS_INV_BY_PERIOD

794: AND acct_period_id = p_period_id
795: AND inventory_item_id = CLOSED_COGS_REC.item_id;
796:
797: IF SQL%NOTFOUND THEN
798: INSERT INTO MTL_BIS_INV_BY_PERIOD
799: (organization_id, acct_period_id, inventory_item_id, onhand,
800: wip, intransit, cogs, last_update_date, last_updated_by,
801: creation_date, created_by, last_update_login, request_id,
802: program_application_id, program_id, program_update_date)

Line 847: mtl_bis_inv_by_period mbibp

843: mbibp.wip,
844: mbibp.bop_intransit,
845: mbibp.intransit
846: FROM org_acct_periods oap,
847: mtl_bis_inv_by_period mbibp
848: WHERE oap.open_flag = 'Y'
849: AND mbibp.acct_period_id = oap.acct_period_id
850: AND mbibp.organization_id = oap.organization_id
851: ORDER BY

Line 981: and for which there is no data in MTL_BIS_INV_BY_PERIOD table .

977: END IF;
978: END calc_open_bop_values ;
979:
980: /* Process all the closed periods that have not been processed
981: and for which there is no data in MTL_BIS_INV_BY_PERIOD table .
982: This will be a case at the customers who are implementing
983: this procedure for the first time).
984: R12 changes: Restricting process orgs in closed_periods cursor itself as
985: this wouldn't be passed further.

Line 1013: FROM mtl_bis_inv_by_period)

1009: WHERE oap.open_flag = 'N'
1010: AND oap.schedule_close_date <= INV_LE_TIMEZONE_PUB.get_le_day_for_inv_org(Sysdate,oap.organization_id)
1011: MINUS
1012: SELECT organization_id, acct_period_id
1013: FROM mtl_bis_inv_by_period)
1014: ORDER BY
1015: oap.organization_id,
1016: oap.schedule_close_date asc;
1017: */

Line 1033: FROM mtl_bis_inv_by_period mbibp

1029: AND oap.schedule_close_date <= INV_LE_TIMEZONE_PUB.get_le_day_for_inv_org(Sysdate,oap.organization_id)
1030: AND mp.process_enabled_flag <> 'Y' -- Added for R12 uptake. Ignore all data in process orgs.
1031: AND EXISTS -- Changed from NOT EXISTS to EXISTS for Bug 5099039
1032: (SELECT mbibp.acct_period_id, mbibp.organization_id
1033: FROM mtl_bis_inv_by_period mbibp
1034: WHERE mbibp.acct_period_id = oap.acct_period_id
1035: AND mbibp.organization_id = oap.organization_id
1036: AND trunc(mbibp.last_update_date) <= oap.period_close_date ) -- Added this filter to collect data for all orgs
1037: -- that were closed later than prior collection date. This would ensure that any transactions after last collection

Line 1057: FROM mtl_bis_inv_by_period

1053: END IF;
1054: ELSE
1055: SELECT nvl(MAX(acct_period_id),0)
1056: INTO l_last_period_id
1057: FROM mtl_bis_inv_by_period
1058: WHERE organization_id = closed_periods_rec.organization_id
1059: AND acct_period_id < closed_periods_rec.acct_period_id ;
1060:
1061: inv_turns.closed_sc (closed_periods_rec.organization_id,

Line 1208: DELETE FROM MTL_BIS_INV_BY_PERIOD

1204: l_period_id := OPEN_PERS_REC.acct_period_id;
1205: l_period_start_date := OPEN_PERS_REC.period_start_date;
1206: l_schedule_close_date := OPEN_PERS_REC.schedule_close_date;
1207:
1208: DELETE FROM MTL_BIS_INV_BY_PERIOD
1209: WHERE organization_id = l_organization_id
1210: AND acct_period_id = l_period_id;
1211:
1212: IF l_prel_org <> l_organization_id THEN

Line 1217: UPDATE MTL_BIS_INV_BY_PERIOD

1213: IF l_prel_org <> 0 THEN
1214: COMMIT;
1215: END IF;
1216: FOR OPEN_ONHAND_REC IN OPEN_ONHAND LOOP
1217: UPDATE MTL_BIS_INV_BY_PERIOD
1218: SET onhand = OPEN_ONHAND_REC.onhand
1219: WHERE organization_id = l_organization_id
1220: AND acct_period_id = l_period_id
1221: AND inventory_item_id = OPEN_ONHAND_REC.inventory_item_id;

Line 1224: INSERT INTO MTL_BIS_INV_BY_PERIOD

1220: AND acct_period_id = l_period_id
1221: AND inventory_item_id = OPEN_ONHAND_REC.inventory_item_id;
1222:
1223: IF SQL%NOTFOUND THEN
1224: INSERT INTO MTL_BIS_INV_BY_PERIOD
1225: (organization_id, acct_period_id, inventory_item_id, onhand,
1226: wip, intransit, cogs, last_update_date, last_updated_by,
1227: creation_date, created_by, last_update_login, request_id,
1228: program_application_id, program_id, program_update_date)

Line 1240: UPDATE MTL_BIS_INV_BY_PERIOD

1236: END LOOP;
1237:
1238: FOR OPEN_TRNS_REC IN OPEN_TRNS LOOP
1239:
1240: UPDATE MTL_BIS_INV_BY_PERIOD
1241: SET intransit = OPEN_TRNS_REC.it_sum
1242: WHERE organization_id = l_organization_id
1243: AND acct_period_id = l_period_id
1244: AND inventory_item_id = OPEN_TRNS_REC.inv_item_id;

Line 1248: INSERT INTO MTL_BIS_INV_BY_PERIOD

1244: AND inventory_item_id = OPEN_TRNS_REC.inv_item_id;
1245:
1246: IF SQL%NOTFOUND THEN
1247:
1248: INSERT INTO MTL_BIS_INV_BY_PERIOD
1249: (organization_id, acct_period_id, inventory_item_id, onhand,
1250: wip, intransit, cogs, last_update_date, last_updated_by,
1251: creation_date, created_by, last_update_login, request_id,
1252: program_application_id, program_id, program_update_date)

Line 1264: UPDATE MTL_BIS_INV_BY_PERIOD

1260: END LOOP;
1261:
1262: FOR OPEN_COGS_REC IN OPEN_COGS LOOP
1263:
1264: UPDATE MTL_BIS_INV_BY_PERIOD
1265: SET cogs = OPEN_COGS_REC.cogs_val
1266: WHERE organization_id = l_organization_id
1267: AND acct_period_id = l_period_id
1268: AND inventory_item_id = OPEN_COGS_REC.inventory_item_id;

Line 1271: INSERT INTO MTL_BIS_INV_BY_PERIOD

1267: AND acct_period_id = l_period_id
1268: AND inventory_item_id = OPEN_COGS_REC.inventory_item_id;
1269:
1270: IF SQL%NOTFOUND THEN
1271: INSERT INTO MTL_BIS_INV_BY_PERIOD
1272: (organization_id, acct_period_id, inventory_item_id, onhand,
1273: wip, intransit, cogs, last_update_date, last_updated_by,
1274: creation_date, created_by, last_update_login, request_id,
1275: program_application_id, program_id, program_update_date)

Line 1295: UPDATE MTL_BIS_INV_BY_PERIOD

1291: END IF;
1292:
1293: FOR OPEN_TRNS_REC IN OPEN_TRNS LOOP
1294:
1295: UPDATE MTL_BIS_INV_BY_PERIOD
1296: SET intransit = OPEN_TRNS_REC.it_sum
1297: WHERE organization_id = l_organization_id
1298: AND acct_period_id = l_period_id
1299: AND inventory_item_id = OPEN_TRNS_REC.inv_item_id;

Line 1303: INSERT INTO MTL_BIS_INV_BY_PERIOD

1299: AND inventory_item_id = OPEN_TRNS_REC.inv_item_id;
1300:
1301: IF SQL%NOTFOUND THEN
1302:
1303: INSERT INTO MTL_BIS_INV_BY_PERIOD
1304: (organization_id, acct_period_id, inventory_item_id, onhand,
1305: wip, intransit, cogs, last_update_date, last_updated_by,
1306: creation_date, created_by, last_update_login, request_id,
1307: program_application_id, program_id, program_update_date)

Line 1319: UPDATE MTL_BIS_INV_BY_PERIOD

1315: END LOOP;
1316:
1317: FOR OPEN_COGS_REC IN OPEN_COGS LOOP
1318:
1319: UPDATE MTL_BIS_INV_BY_PERIOD
1320: SET cogs = OPEN_COGS_REC.cogs_val
1321: WHERE organization_id = l_organization_id
1322: AND acct_period_id = l_period_id
1323: AND inventory_item_id = OPEN_COGS_REC.inventory_item_id;

Line 1326: INSERT INTO MTL_BIS_INV_BY_PERIOD

1322: AND acct_period_id = l_period_id
1323: AND inventory_item_id = OPEN_COGS_REC.inventory_item_id;
1324:
1325: IF SQL%NOTFOUND THEN
1326: INSERT INTO MTL_BIS_INV_BY_PERIOD
1327: (organization_id, acct_period_id, inventory_item_id, onhand,
1328: wip, intransit, cogs, last_update_date, last_updated_by,
1329: creation_date, created_by, last_update_login, request_id,
1330: program_application_id, program_id, program_update_date)

Line 1437: INSERT INTO MTL_BIS_INV_BY_PERIOD

1433: FND_MSG_PUB.initialize;
1434:
1435: l_user_id := FND_GLOBAL.USER_ID;
1436:
1437: INSERT INTO MTL_BIS_INV_BY_PERIOD
1438: (organization_id, acct_period_id, inventory_item_id, onhand,
1439: wip, intransit, cogs, last_update_date, last_updated_by,
1440: creation_date, created_by, last_update_login, request_id,
1441: program_application_id, program_id, program_update_date)

Line 1445: FROM MTL_BIS_INV_BY_PERIOD

1441: program_application_id, program_id, program_update_date)
1442: SELECT organization_id, p_new_period, inventory_item_id, onhand,
1443: 0, 0, NULL, SYSDATE, l_user_id, SYSDATE, l_user_id, l_user_id,
1444: NULL,NULL,NULL,NULL
1445: FROM MTL_BIS_INV_BY_PERIOD
1446: WHERE organization_id = p_organization_id
1447: AND acct_period_id = p_period_id
1448: AND onhand IS NOT NULL;
1449:

Line 1452: UPDATE MTL_BIS_INV_BY_PERIOD

1448: AND onhand IS NOT NULL;
1449:
1450: FOR F_TXN_REC IN F_TXN LOOP
1451:
1452: UPDATE MTL_BIS_INV_BY_PERIOD
1453: SET onhand = onhand - F_TXN_REC.txn_val,
1454: intransit = 0
1455: WHERE organization_id = p_organization_id
1456: AND acct_period_id = p_new_period

Line 1461: INSERT INTO MTL_BIS_INV_BY_PERIOD

1457: AND inventory_item_id = F_TXN_REC.inventory_item_id;
1458:
1459: IF SQL%NOTFOUND THEN
1460:
1461: INSERT INTO MTL_BIS_INV_BY_PERIOD
1462: (organization_id, acct_period_id, inventory_item_id, onhand,
1463: wip, intransit, cogs, last_update_date, last_updated_by,
1464: creation_date, created_by, last_update_login, request_id,
1465: program_application_id, program_id, program_update_date)

Line 1494: from mtl_bis_inv_by_period

1490: l_onhand_qty number := 0.0;
1491: begin
1492: begin
1493: select onhand into l_onhand_qty
1494: from mtl_bis_inv_by_period
1495: where organization_id = x_organization_id
1496: and inventory_item_id = x_inventory_item_id
1497: and acct_period_id = x_last_period_id;
1498: exception WHEN NO_DATA_FOUND then