[Home] [Help]
505: AND owning_organization_id = p_vendor_site_id
506: AND TRUNC(transaction_date) = TRUNC(p_transaction_date)
507: AND inventory_item_id IN
508: (SELECT inventory_item_id
509: FROM MTL_CONSUMPTION_TXN_TEMP
510: WHERE transaction_source_id = p_transaction_source_id));
511: */
512: -- Bug 5092489, Query modified to eliminate use of MMT in subquery
513: /* UPDATE MTL_CONSUMPTION_TRANSACTIONS mct
533: AND owning_organization_id = p_vendor_site_id
534: AND distribution_account_id = p_accrual_account_id
535: AND inventory_item_id IN
536: (SELECT inventory_item_id
537: FROM MTL_CONSUMPTION_TXN_TEMP
538: WHERE transaction_source_id = p_transaction_source_id));
539: */
540:
541: ELSE
566: AND owning_organization_id = p_vendor_site_id
567: AND TRUNC(transaction_date) = TRUNC(p_transaction_date)
568: AND inventory_item_id IN
569: (SELECT inventory_item_id
570: FROM MTL_CONSUMPTION_TXN_TEMP
571: WHERE transaction_source_id = p_transaction_source_id));
572: */
573: END IF;
574:
639:
640: -- Insert records to the temp table
641: -- Group the records by use of the distinct clause
642: -- Bug 5092489. Query modified to eliminate use of MMT and DISTINCT clause
643: INSERT INTO MTL_CONSUMPTION_TXN_TEMP mctt
644: ( mctt.transaction_source_id
645: , mctt.inventory_item_id
646: , mctt.organization_id
647: , mctt.owning_organization_id
755: AND mct.batch_id = p_batch_id;
756: */
757:
758: select count(*) into l_count
759: from MTL_CONSUMPTION_TXN_TEMP where batch_id = p_batch_id;
760:
761: IF (l_debug = 1)
762: THEN
763: INV_LOG_UTIL.trace
818: );
819: END IF;
820:
821: --Bug 5092489. Modified insert statement to eliminate use of MMT and DISTINCT clause
822: INSERT INTO MTL_CONSUMPTION_TXN_TEMP mctt
823: ( mctt.transaction_source_id
824: , mctt.inventory_item_id
825: , mctt.organization_id
826: , mctt.owning_organization_id
902:
903: /*
904: -- Insert records to the temp table that do belong to
905: -- a global agreement
906: INSERT INTO MTL_CONSUMPTION_TXN_TEMP mctt
907: ( mctt.transaction_source_id
908: , mctt.inventory_item_id
909: , mctt.organization_id
910: , mctt.owning_organization_id
968:
969: -- Insert records to the temp table that do belong to
970: -- a global agreement
971:
972: INSERT INTO mtl_consumption_txn_temp mctt
973: ( mctt.transaction_source_id
974: , mctt.inventory_item_id
975: , mctt.organization_id
976: , mctt.owning_organization_id
1028: AND mmt.transaction_type_id = 74
1029: AND mct.batch_id = p_batch_id;
1030: */
1031: /* Start Bug 6388514 INSERT into MCT for global_agreement_flag = 'N' */
1032: INSERT INTO MTL_CONSUMPTION_TXN_TEMP mctt
1033: ( mctt.transaction_source_id
1034: , mctt.inventory_item_id
1035: , mctt.organization_id
1036: , mctt.owning_organization_id
1100: ;
1101: /* End Bug 6388514 */
1102: -- Set the transaction date of each group to the latest date
1103:
1104: --UPDATE/*+ leading(mctt) */ MTL_CONSUMPTION_TXN_TEMP mctt
1105: --SET mctt.transaction_date =
1106: -- (SELECT
1107: -- TRUNC(MAX(mct.transaction_date))
1108: -- FROM
1129: -- AND mct.consumption_processed_flag IN ('N', 'E'))
1130: --WHERE mctt.transaction_date IS NULL;
1131:
1132: /* Start bug 6388514 Update transaction_date in MCTT */
1133: UPDATE/*+ leading(mctt) */ MTL_CONSUMPTION_TXN_TEMP mctt
1134: SET mctt.transaction_date =
1135: (SELECT
1136: TRUNC(MAX(mct.transaction_date))
1137: FROM
1159: WHERE mctt.transaction_date IS NULL;
1160: /* End bug 6388514 */
1161:
1162: select count(*) into l_count
1163: from MTL_CONSUMPTION_TXN_TEMP where batch_id = p_batch_id;
1164:
1165: IF (l_debug = 1)
1166: THEN
1167: INV_LOG_UTIL.trace
1212: transaction_source_id
1213: , inventory_item_id
1214: , owning_organization_id
1215: FROM
1216: mtl_consumption_txn_temp
1217: WHERE batch_id=p_batch_id;
1218:
1219: --=================
1220: -- VARIABLES
1289: ELSE
1290: l_valid_flag := 'Y';
1291: END IF;
1292:
1293: UPDATE mtl_consumption_txn_temp
1294: SET valid_flag = l_valid_flag
1295: ,org_id = l_org_id
1296: WHERE transaction_source_id = l_header_id
1297: AND inventory_item_id = l_item_id
1304: );
1305:
1306: ELSE
1307:
1308: UPDATE mtl_consumption_txn_temp
1309: SET valid_flag = 'N'
1310: ,org_id = l_org_id
1311: WHERE transaction_source_id = l_header_id
1312: AND inventory_item_id = l_item_id
1332: AND consumption_processed_flag IN ('N','E')
1333: AND batch_id = p_batch_id;
1334: */
1335:
1336: DELETE FROM mtl_consumption_txn_temp
1337: WHERE valid_flag = 'N'
1338: AND org_id = l_org_id
1339: AND transaction_source_id = l_header_id
1340: AND inventory_item_id = l_item_id;
1373: -- PARAMETERS: p_transaction_source_id Material transaction id
1374: -- p_batch_id Batch id
1375: -- COMMENT : This procedure is called from the worker. It loads
1376: -- : unprocessed summarized change of ownership transactions
1377: -- : from the consumption temp table MTL_CONSUMPTION_TXN_TEMP
1378: -- : into the PO interface tables in preperation for the
1379: -- : creation of either a consumption advice or standard PO
1380: -- : Once this is done the autocreate procedure is called
1381: -- : to process the interface tables and create the release.
1529: , rate
1530: , owning_organization_id
1531: , currency_code
1532: FROM
1533: mtl_consumption_txn_temp
1534: WHERE batch_id = p_batch_id;
1535:
1536: -- Cursor to get the line info to insert into the
1537: -- po_lines_interface table for the corresponding header that is inserted.
1551: , non_recoverable_tax
1552: , tax_recovery_rate
1553: , asl_id
1554: FROM
1555: mtl_consumption_txn_temp
1556: WHERE transaction_source_id = l_blanket_id
1557: AND valid_flag = l_valid_flag
1558: AND TRUNC(transaction_date) = NVL(l_transaction_date, TRUNC(transaction_date))
1559: AND rate_type = NVL(l_global_rate_type,'##')
2749: -- MTL_CONSUMPTION_TRANSACTIONS for a unique combination of
2750: -- transaction_source_id, inventory_item_id, organization_id
2751: -- transaction cost, tax code id, accrual account, variance
2752: -- account, charge account
2753: -- present in MTL_CONSUMPTION_TXN_TEMP. The result updates the
2754: -- net quantity column in MTL_CONSUMPTION_TXN_TEMP
2755: -- CHANGE : INVCONV Added secondary_net_quantity to support process attributes
2756: -- for inventory convergence project.
2757: --=========================================================================
2750: -- transaction_source_id, inventory_item_id, organization_id
2751: -- transaction cost, tax code id, accrual account, variance
2752: -- account, charge account
2753: -- present in MTL_CONSUMPTION_TXN_TEMP. The result updates the
2754: -- net quantity column in MTL_CONSUMPTION_TXN_TEMP
2755: -- CHANGE : INVCONV Added secondary_net_quantity to support process attributes
2756: -- for inventory convergence project.
2757: --=========================================================================
2758: PROCEDURE load_summarized_quantity
2835: IF p_txn_source_tab.COUNT > 0
2836: THEN
2837:
2838: FORALL i IN p_txn_source_tab.FIRST..p_txn_source_tab.LAST
2839: UPDATE/*+ leading(mctt) */ MTL_CONSUMPTION_TXN_TEMP mctt
2840: SET (mctt.net_qty,mctt.secondary_net_qty) =
2841: (SELECT SUM(mct.net_qty),SUM(mct.secondary_net_qty)
2842:
2843: FROM MTL_CONSUMPTION_TRANSACTIONS mct
2924: -- MTL_CONSUMPTION_TRANSACTIONS for a unique combination of
2925: -- transaction_source_id, inventory_item_id, organization_id
2926: -- transaction cost, tax code id, accrual account, variance
2927: -- account, charge account
2928: -- present in MTL_CONSUMPTION_TXN_TEMP. The result updates the
2929: -- net quantity column in MTL_CONSUMPTION_TXN_TEMP
2930: -- CHANGE : INVCONV Added secondary_net_quantity to support process attributes
2931: -- for inventory convergence project.
2932: --=========================================================================
2925: -- transaction_source_id, inventory_item_id, organization_id
2926: -- transaction cost, tax code id, accrual account, variance
2927: -- account, charge account
2928: -- present in MTL_CONSUMPTION_TXN_TEMP. The result updates the
2929: -- net quantity column in MTL_CONSUMPTION_TXN_TEMP
2930: -- CHANGE : INVCONV Added secondary_net_quantity to support process attributes
2931: -- for inventory convergence project.
2932: --=========================================================================
2933: PROCEDURE load_summarized_quantity_prf
3009: IF p_txn_source_tab.COUNT > 0
3010: THEN
3011:
3012: FORALL i IN p_txn_source_tab.FIRST..p_txn_source_tab.LAST
3013: UPDATE/*+ leading(mctt) */ MTL_CONSUMPTION_TXN_TEMP mctt
3014: SET (mctt.net_qty,mctt.secondary_net_qty) =
3015: (SELECT SUM(mct.net_qty),SUM(mct.secondary_net_qty)
3016:
3017: FROM MTL_CONSUMPTION_TRANSACTIONS mct
3059: /*Bug 4969421 - Ends here */
3060: AND mctt.global_agreement_flag = 'Y';
3061:
3062: FORALL i IN p_txn_source_tab.FIRST..p_txn_source_tab.LAST
3063: UPDATE/*+ leading(mctt) */ MTL_CONSUMPTION_TXN_TEMP mctt
3064: SET mctt.net_qty =
3065: (SELECT SUM(mct.net_qty)
3066: FROM MTL_CONSUMPTION_TRANSACTIONS mct
3067: --, MTL_MATERIAL_TRANSACTIONS mmt
3172: , 9
3173: );
3174: END IF;
3175:
3176: DELETE FROM mtl_consumption_txn_temp mctt
3177: WHERE mctt.transaction_source_id = p_txn_source_id
3178: AND mctt.inventory_item_id = p_inventory_item_id
3179: AND mctt.organization_id = p_organization_id
3180: AND mctt.owning_organization_id = p_own_org_id
3441: , mctt.rate
3442: , mctt.rate_type
3443: , mctt.transaction_date
3444: , mctt.tax_recovery_rate -- Bug 4969420
3445: FROM MTL_CONSUMPTION_TXN_TEMP mctt
3446: /* bug 5113064 - Start */
3447: /* filter just for the given batch */
3448: WHERE batch_id =p_batch_id ;
3449: /* bug 5113064 - End*/
3578: ,l_tax_rec_rate_tab -- Bug 4969420
3579: LIMIT 1000;
3580:
3581: /* But 5006151 - Start */
3582: /* When the number of records in the MTL_CONSUMPTION_TXN_TEMP table is a
3583: multiple of 1000, the worker does not exit correctly with the check
3584: 'EXIT WHEN l_loop_count < 1000' as l_loop_count equals '1000'.
3585: The following check would ensure that the worker exits correctly */
3586: EXIT WHEN l_bulk_count = cons_temp_csr_type%ROWCOUNT;
3714: END IF;
3715:
3716: -- Update the temp table with the currency of the blanket
3717:
3718: UPDATE/*+ leading(mctt) */ MTL_CONSUMPTION_TXN_TEMP mctt
3719: SET mctt.currency_code = (SELECT poa.currency_code
3720: FROM po_headers_all poa
3721: WHERE poa.po_header_id
3722: = l_txn_source_tab(i))
4155: , mctt.variance_account_id
4156: , mctt.rate
4157: , mctt.rate_type
4158: , mctt.transaction_date
4159: FROM MTL_CONSUMPTION_TXN_TEMP mctt
4160: ORDER BY mctt.transaction_source_id
4161: , mctt.organization_id;
4162:
4163: --=================
4327: );
4328: END IF;
4329:
4330: -- Query how many summarized records have been loaded into
4331: -- in the temporary table MTL_CONSUMPTION_TXN_TEMP for the
4332: -- current blanket and set the group size accordingly
4333:
4334: SELECT
4335: COUNT(*)
4335: COUNT(*)
4336: INTO
4337: l_group_size
4338: FROM
4339: mtl_consumption_txn_temp mctt
4340: WHERE mctt.transaction_source_id = l_txn_source_tab(l_current_cons_index);
4341:
4342: LOOP
4343:
4382: COUNT(*)
4383: INTO
4384: l_group_size
4385: FROM
4386: mtl_consumption_txn_temp mctt
4387: WHERE mctt.transaction_source_id =
4388: l_txn_source_tab(l_next_cons_index);
4389:
4390: l_group_count := 1;