[Home] [Help]
196: -- OPI_DBI_INV_VALUE_LOG -- added as part of CPCS Change.
197: -- Note: OPI_DBI_INV_TYPE_CODES is not cleaned anywhere.
198: -- Intransit Tables:
199: -- OPI_DBI_INTR_SUP_TMP
200: -- OPI_DBI_INTR_MMT_TMP
201: -- OPI_DBI_INTR_MIP_TMP
202: -- OPI_DBI_INTRANSIT_STG
203: -- WIP Tables
204: -- OPI_DBI_WIP_STG
276: OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
277: END IF;
278:
279: l_stmt_num := 70;
280: execute immediate 'truncate table ' || g_opi_schema || '.OPI_DBI_INTR_MMT_TMP';
281:
282: IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
283: l_debug_msg := 'OPI_DBI_INTR_MMT_TMP table truncated.';
284: OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg);
279: l_stmt_num := 70;
280: execute immediate 'truncate table ' || g_opi_schema || '.OPI_DBI_INTR_MMT_TMP';
281:
282: IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
283: l_debug_msg := 'OPI_DBI_INTR_MMT_TMP table truncated.';
284: OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg);
285: END IF;
286:
287: l_stmt_num := 80;
903: --In opi_dbi_intr_mip_tmp , which contains shipping network information for
904: --discrete organization till now(Pre R12), shipping network between all
905: --type of organization will be collected along with
906: --process enabled flag info
907: --Data from MMT which has been moved to opi_dbi_intr_mmt_tmp for discrete
908: --organization ,pre r12.
909: --After R12 this table will also contains the information for process
910: --organization.
911: --Get MMT Data in opi_dbi_intr_mmt_tmp. We will collect following
907: --Data from MMT which has been moved to opi_dbi_intr_mmt_tmp for discrete
908: --organization ,pre r12.
909: --After R12 this table will also contains the information for process
910: --organization.
911: --Get MMT Data in opi_dbi_intr_mmt_tmp. We will collect following
912: --transaction actions
913: -- 12 -Intransit Receipt
914: -- 21 - Intransit Shipment
915: -- 24 - Cost Update ODM
994:
995: IF p_mode = 'INIT' THEN
996: l_stmt_num :=20;
997: IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
998: l_debug_msg := 'Insert Data into opi_dbi_intr_mmt_tmp. ';
999: OPI_DBI_BOUNDS_PKG.write (g_pkg_name,l_proc_name,l_stmt_num, l_debug_msg );
1000: END IF;
1001: -- Select all intransit data from MMT into a temp table.
1002: --
1006: -- cost group associated with the organization in MTL_PARAMETERS.
1007: -- As of 11i, the default_cost_group_id is guaranteed to be
1008: -- non-null, so no nvl is needed on the selection of that column.
1009:
1010: INSERT /*append parallel(opi_dbi_intr_mmt_tmp) */
1011: INTO OPI_DBI_INTR_MMT_TMP (
1012: transaction_id,
1013: organization_id,
1014: organization_process_flag,
1007: -- As of 11i, the default_cost_group_id is guaranteed to be
1008: -- non-null, so no nvl is needed on the selection of that column.
1009:
1010: INSERT /*append parallel(opi_dbi_intr_mmt_tmp) */
1011: INTO OPI_DBI_INTR_MMT_TMP (
1012: transaction_id,
1013: organization_id,
1014: organization_process_flag,
1015: transfer_organization_id,
1050: AND p1.organization_id = mmt.transfer_organization_id
1051: --AND mmt.transaction_type_id IN (12,21,61,62,24,80,26,28) -- Removed in r12
1052: ;
1053:
1054: l_debug_msg := 'Inserted into staging table opi_dbi_intr_mmt_tmp - ' || SQL%ROWCOUNT || ' rows. ';
1055: OPI_DBI_BOUNDS_PKG.write(g_pkg_name,l_proc_name,l_stmt_num,l_debug_msg);
1056: commit;
1057:
1058: l_stmt_num :=30;
1143: --In This procedure, Inception Quantities for Intransit Inventory is collected
1144: --In Procedure Intransit setup, data have been collceted into
1145: --following tables
1146: -- opi_dbi_intr_mip_tmp,
1147: -- opi_dbi_intr_mmt_tmp
1148: -- and opi_dbi_intr_sup_tmp,
1149: --which are in sync(approx) with each other.
1150: --We will use these three tables to collect data for inception quantities.
1151: --For Pre R12 OPM data , we will use opi_dbi_intr_sup_tmp table
1234: 12,decode (mip.fob_point,
1235: 2,mmt1.transfer_cost_group_id,
1236: mmt1.cost_group_id)
1237: ),-1) cost_group_id
1238: FROM OPI_DBI_INTR_MMT_TMP mmt1,
1239: OPI_DBI_INTR_MIP_TMP mip,
1240: MTL_PARAMETERS mp,
1241: MTL_SYSTEM_ITEMS msi_fm,
1242: MTL_SYSTEM_ITEMS msi_to
1529:
1530: -- INTRANSIT_SETUP collect data into following temp tables.
1531: -- OPI_DBI_INTR_MIP_TMP
1532: -- OPI_DBI_INTR_SUP_TMP
1533: -- OPI_DBI_INTR_MMT_TMP
1534: Intransit_setup('INIT'); --R12 moved here
1535:
1536: l_stmt_num :=20;
1537: IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' then
1635: ,msi_to.organization_id, msi_to.primary_uom_code) primary_uom_code,
1636: TRUNC (mmt1.transaction_date) trx_date,
1637: mip.owning_org_process_flag process_flag -- Bug fix: 5362465
1638: FROM opi_dbi_intr_mip_tmp mip,
1639: opi_dbi_intr_mmt_tmp mmt1,
1640: (select
1641: transaction_id,
1642: sum (base_transaction_value) base_transaction_value
1643: from mtl_transaction_accounts
1685: sum (base_transaction_value) value,
1686: msi_fm.primary_uom_code,
1687: trunc(mmt1.transaction_date) trx_date,
1688: 'N' process_flag -- Bug fix: 5362465, source is only discrete here
1689: FROM opi_dbi_intr_mmt_tmp mmt1,
1690: mtl_transaction_accounts mta,
1691: mtl_system_items msi_fm,
1692: OPI_DBI_CONC_PROG_RUN_LOG col
1693: WHERE mmt1.transaction_action_id = 24
1817: ,msi_to.organization_id, msi_to.primary_uom_code) primary_uom_code,
1818:
1819: TRUNC (mmt1.transaction_date) trx_date
1820: FROM opi_dbi_intr_mip_tmp mip,
1821: opi_dbi_intr_mmt_tmp mmt1,
1822: (SELECT transaction_id,
1823: sum(txn_base_value) base_transaction_value
1824: ,accounted_flag
1825: FROM gmf_transaction_valuation gtv,