[Home] [Help]
529: AND owning_organization_id = p_vendor_site_id
530: AND TRUNC(transaction_date) = TRUNC(p_transaction_date)
531: AND inventory_item_id IN
532: (SELECT inventory_item_id
533: FROM MTL_CONSUMPTION_TXN_TEMP
534: WHERE transaction_source_id = p_transaction_source_id));
535: */
536: END IF;
537:
602:
603: -- Insert records to the temp table
604: -- Group the records by use of the distinct clause
605: -- Bug 5092489. Query modified to eliminate use of MMT and DISTINCT clause
606: INSERT INTO MTL_CONSUMPTION_TXN_TEMP mctt
607: ( mctt.transaction_source_id
608: , mctt.inventory_item_id
609: , mctt.organization_id
610: , mctt.owning_organization_id
687:
688: -- Deleted unused commented code as part of bug 11900144
689:
690: select count(*) into l_count
691: from MTL_CONSUMPTION_TXN_TEMP where batch_id = p_batch_id;
692:
693: IF (l_debug = 1)
694: THEN
695: INV_LOG_UTIL.trace
750: );
751: END IF;
752:
753: --Bug 5092489. Modified insert statement to eliminate use of MMT and DISTINCT clause
754: INSERT INTO MTL_CONSUMPTION_TXN_TEMP mctt
755: ( mctt.transaction_source_id
756: , mctt.inventory_item_id
757: , mctt.organization_id
758: , mctt.owning_organization_id
840:
841: -- Deleted unused commented code as part of bug 11900144
842:
843: /* Start Bug 6388514 INSERT into MCT for global_agreement_flag = 'N' */
844: INSERT INTO MTL_CONSUMPTION_TXN_TEMP mctt
845: ( mctt.transaction_source_id
846: , mctt.inventory_item_id
847: , mctt.organization_id
848: , mctt.owning_organization_id
918: ;
919: /* End Bug 6388514 */
920:
921: /* Start bug 6388514 Update transaction_date in MCTT */
922: UPDATE/*+ leading(mctt) */ MTL_CONSUMPTION_TXN_TEMP mctt
923: SET mctt.transaction_date =
924: (SELECT
925: TRUNC(MAX(mct.transaction_date))
926: FROM
950: WHERE mctt.transaction_date IS NULL;
951: /* End bug 6388514 */
952:
953: select count(*) into l_count
954: from MTL_CONSUMPTION_TXN_TEMP where batch_id = p_batch_id;
955:
956: IF (l_debug = 1)
957: THEN
958: INV_LOG_UTIL.trace
1003: transaction_source_id
1004: , inventory_item_id
1005: , owning_organization_id
1006: FROM
1007: mtl_consumption_txn_temp
1008: WHERE batch_id=p_batch_id;
1009:
1010: --=================
1011: -- VARIABLES
1081: l_valid_flag := 'Y';
1082: END IF;
1083:
1084: /* Bug 13843698. Added owning org id in the below where clause */
1085: UPDATE mtl_consumption_txn_temp
1086: SET valid_flag = l_valid_flag
1087: ,org_id = l_org_id
1088: WHERE transaction_source_id = l_header_id
1089: AND inventory_item_id = l_item_id
1098:
1099: ELSE
1100:
1101: /* Bug 13843698. Added owning org id in the below where clause */
1102: UPDATE mtl_consumption_txn_temp
1103: SET valid_flag = 'N'
1104: ,org_id = l_org_id
1105: WHERE transaction_source_id = l_header_id
1106: AND inventory_item_id = l_item_id
1127: AND consumption_processed_flag IN ('N','E')
1128: AND batch_id = p_batch_id;
1129: */
1130:
1131: DELETE FROM mtl_consumption_txn_temp
1132: WHERE valid_flag = 'N'
1133: AND org_id = l_org_id
1134: AND transaction_source_id = l_header_id
1135: AND inventory_item_id = l_item_id;
1168: -- PARAMETERS: p_transaction_source_id Material transaction id
1169: -- p_batch_id Batch id
1170: -- COMMENT : This procedure is called from the worker. It loads
1171: -- : unprocessed summarized change of ownership transactions
1172: -- : from the consumption temp table MTL_CONSUMPTION_TXN_TEMP
1173: -- : into the PO interface tables in preperation for the
1174: -- : creation of either a consumption advice or standard PO
1175: -- : Once this is done the autocreate procedure is called
1176: -- : to process the interface tables and create the release.
1327: , rate
1328: , owning_organization_id
1329: , currency_code
1330: FROM
1331: mtl_consumption_txn_temp
1332: WHERE batch_id = p_batch_id;
1333:
1334: -- Cursor to get the line info to insert into the
1335: -- po_lines_interface table for the corresponding header that is inserted.
1351: , asl_id
1352: /* Bug 11900144. Addition of po_line_id */
1353: , po_line_id
1354: FROM
1355: mtl_consumption_txn_temp
1356: WHERE transaction_source_id = l_blanket_id
1357: AND valid_flag = l_valid_flag
1358: AND TRUNC(transaction_date) = NVL(l_transaction_date, TRUNC(transaction_date))
1359: AND rate_type = NVL(l_global_rate_type,'##')
2463: -- MTL_CONSUMPTION_TRANSACTIONS for a unique combination of
2464: -- transaction_source_id, inventory_item_id, organization_id
2465: -- transaction cost, tax code id, accrual account, variance
2466: -- account, charge account
2467: -- present in MTL_CONSUMPTION_TXN_TEMP. The result updates the
2468: -- net quantity column in MTL_CONSUMPTION_TXN_TEMP
2469: -- CHANGE : INVCONV Added secondary_net_quantity to support process attributes
2470: -- for inventory convergence project.
2471: --=========================================================================
2464: -- transaction_source_id, inventory_item_id, organization_id
2465: -- transaction cost, tax code id, accrual account, variance
2466: -- account, charge account
2467: -- present in MTL_CONSUMPTION_TXN_TEMP. The result updates the
2468: -- net quantity column in MTL_CONSUMPTION_TXN_TEMP
2469: -- CHANGE : INVCONV Added secondary_net_quantity to support process attributes
2470: -- for inventory convergence project.
2471: --=========================================================================
2472: PROCEDURE load_summarized_quantity
2549: IF p_txn_source_tab.COUNT > 0
2550: THEN
2551:
2552: FORALL i IN p_txn_source_tab.FIRST..p_txn_source_tab.LAST
2553: UPDATE/*+ leading(mctt) */ MTL_CONSUMPTION_TXN_TEMP mctt
2554: SET (mctt.net_qty,mctt.secondary_net_qty) =
2555: (SELECT SUM(mct.net_qty),SUM(mct.secondary_net_qty)
2556:
2557: FROM MTL_CONSUMPTION_TRANSACTIONS mct
2640: -- MTL_CONSUMPTION_TRANSACTIONS for a unique combination of
2641: -- transaction_source_id, inventory_item_id, organization_id
2642: -- transaction cost, tax code id, accrual account, variance
2643: -- account, charge account
2644: -- present in MTL_CONSUMPTION_TXN_TEMP. The result updates the
2645: -- net quantity column in MTL_CONSUMPTION_TXN_TEMP
2646: -- CHANGE : INVCONV Added secondary_net_quantity to support process attributes
2647: -- for inventory convergence project.
2648: --=========================================================================
2641: -- transaction_source_id, inventory_item_id, organization_id
2642: -- transaction cost, tax code id, accrual account, variance
2643: -- account, charge account
2644: -- present in MTL_CONSUMPTION_TXN_TEMP. The result updates the
2645: -- net quantity column in MTL_CONSUMPTION_TXN_TEMP
2646: -- CHANGE : INVCONV Added secondary_net_quantity to support process attributes
2647: -- for inventory convergence project.
2648: --=========================================================================
2649: PROCEDURE load_summarized_quantity_prf
2725: IF p_txn_source_tab.COUNT > 0
2726: THEN
2727:
2728: FORALL i IN p_txn_source_tab.FIRST..p_txn_source_tab.LAST
2729: UPDATE/*+ leading(mctt) */ MTL_CONSUMPTION_TXN_TEMP mctt
2730: SET (mctt.net_qty,mctt.secondary_net_qty) =
2731: (SELECT SUM(mct.net_qty),SUM(mct.secondary_net_qty)
2732:
2733: FROM MTL_CONSUMPTION_TRANSACTIONS mct
2777: /*Bug 4969421 - Ends here */
2778: AND mctt.global_agreement_flag = 'Y';
2779:
2780: FORALL i IN p_txn_source_tab.FIRST..p_txn_source_tab.LAST
2781: UPDATE/*+ leading(mctt) */ MTL_CONSUMPTION_TXN_TEMP mctt
2782: SET mctt.net_qty =
2783: (SELECT SUM(mct.net_qty)
2784: FROM MTL_CONSUMPTION_TRANSACTIONS mct
2785: --, MTL_MATERIAL_TRANSACTIONS mmt
2892: , 9
2893: );
2894: END IF;
2895:
2896: DELETE FROM mtl_consumption_txn_temp mctt
2897: WHERE mctt.transaction_source_id = p_txn_source_id
2898: AND mctt.inventory_item_id = p_inventory_item_id
2899: AND mctt.organization_id = p_organization_id
2900: AND mctt.owning_organization_id = p_own_org_id
3299: , mctt.rate
3300: , mctt.rate_type
3301: , mctt.transaction_date
3302: , mctt.tax_recovery_rate -- Bug 4969420
3303: FROM MTL_CONSUMPTION_TXN_TEMP mctt
3304: /* bug 5113064 - Start */
3305: /* filter just for the given batch */
3306: WHERE batch_id =p_batch_id ;
3307: /* bug 5113064 - End*/
3436: ,l_tax_rec_rate_tab -- Bug 4969420
3437: LIMIT 1000;
3438:
3439: /* But 5006151 - Start */
3440: /* When the number of records in the MTL_CONSUMPTION_TXN_TEMP table is a
3441: multiple of 1000, the worker does not exit correctly with the check
3442: 'EXIT WHEN l_loop_count < 1000' as l_loop_count equals '1000'.
3443: The following check would ensure that the worker exits correctly */
3444: EXIT WHEN l_bulk_count = cons_temp_csr_type%ROWCOUNT;
3572: END IF;
3573:
3574: -- Update the temp table with the currency of the blanket
3575:
3576: UPDATE/*+ leading(mctt) */ MTL_CONSUMPTION_TXN_TEMP mctt
3577: SET mctt.currency_code = (SELECT poa.currency_code
3578: FROM po_headers_all poa
3579: WHERE poa.po_header_id
3580: = l_txn_source_tab(i))
4015: , mctt.variance_account_id
4016: , mctt.rate
4017: , mctt.rate_type
4018: , mctt.transaction_date
4019: FROM MTL_CONSUMPTION_TXN_TEMP mctt
4020: ORDER BY mctt.transaction_source_id
4021: , mctt.organization_id;
4022:
4023: --=================
4187: );
4188: END IF;
4189:
4190: -- Query how many summarized records have been loaded into
4191: -- in the temporary table MTL_CONSUMPTION_TXN_TEMP for the
4192: -- current blanket and set the group size accordingly
4193:
4194: SELECT
4195: COUNT(*)
4195: COUNT(*)
4196: INTO
4197: l_group_size
4198: FROM
4199: mtl_consumption_txn_temp mctt
4200: WHERE mctt.transaction_source_id = l_txn_source_tab(l_current_cons_index);
4201:
4202: LOOP
4203:
4242: COUNT(*)
4243: INTO
4244: l_group_size
4245: FROM
4246: mtl_consumption_txn_temp mctt
4247: WHERE mctt.transaction_source_id =
4248: l_txn_source_tab(l_next_cons_index);
4249:
4250: l_group_count := 1;