[Home] [Help]
983: 0
984: )
985: )
986: INTO v_qty
987: FROM ic_tran_pnd i, pm_matl_dtl_bak l
988: WHERE doc_id = p_batch_id AND
989: doc_type IN ('PROD', 'FPO') AND
990: i.line_id = p_line_id AND
991: i.line_id = l.line_id AND
1015: RETURN v_qty;
1016: EXCEPTION
1017: WHEN OTHERS THEN
1018: insert_message (
1019: p_table_name => 'ic_tran_pnd',
1020: p_procedure_name => 'get_actual_qty',
1021: p_parameters => 'batch_id = '
1022: || p_batch_id
1023: || ' line_id = '
1071: 0
1072: )
1073: )
1074: INTO v_qty
1075: FROM ic_tran_pnd i, pm_matl_dtl_bak l
1076: WHERE doc_id = p_batch_id AND
1077: doc_type IN ('PROD', 'FPO') AND
1078: i.line_id = p_line_id AND
1079: i.line_id = l.line_id AND
1114: RETURN v_qty;
1115: EXCEPTION
1116: WHEN plan_qty_null THEN
1117: insert_message (
1118: p_table_name => 'ic_tran_pnd',
1119: p_procedure_name => 'get_planned_qty',
1120: p_parameters => 'batch_id = '
1121: || p_batch_id
1122: || ' line_id = '
1131: );
1132: RETURN p_plan_qty;
1133: WHEN OTHERS THEN
1134: insert_message (
1135: p_table_name => 'ic_tran_pnd',
1136: p_procedure_name => 'get_planned_qty',
1137: p_parameters => 'batch_id = '
1138: || p_batch_id
1139: || ' line_id = '
1201: 0
1202: )
1203: )
1204: INTO v_qty
1205: FROM ic_tran_pnd
1206: WHERE doc_id = p_batch_id AND
1207: doc_type IN ('PROD', 'FPO') AND
1208: line_id = p_line_id AND
1209: delete_mark = 0;
1219: RETURN v_qty;
1220: EXCEPTION
1221: WHEN OTHERS THEN
1222: insert_message (
1223: p_table_name => 'ic_tran_pnd',
1224: p_procedure_name => 'get_wip_planned_qty',
1225: p_parameters => 'batch_id = '
1226: || p_batch_id
1227: || ' line_id = '
1242: l_batch_header pm_btch_hdr_bak%ROWTYPE;
1243: l_return_status VARCHAR2 (1);
1244: l_trans_is_reversed NUMBER;
1245: l_is_plain BOOLEAN;
1246: l_def_lot_id ic_tran_pnd.trans_id%TYPE;
1247: l_completed_ind ic_tran_pnd.completed_ind%TYPE;
1248: l_trans_qty ic_tran_pnd.trans_qty%TYPE;
1249: l_ic_tran_cmp_out ic_tran_cmp%ROWTYPE;
1250: l_ic_tran_pnd_out ic_tran_pnd%ROWTYPE;
1243: l_return_status VARCHAR2 (1);
1244: l_trans_is_reversed NUMBER;
1245: l_is_plain BOOLEAN;
1246: l_def_lot_id ic_tran_pnd.trans_id%TYPE;
1247: l_completed_ind ic_tran_pnd.completed_ind%TYPE;
1248: l_trans_qty ic_tran_pnd.trans_qty%TYPE;
1249: l_ic_tran_cmp_out ic_tran_cmp%ROWTYPE;
1250: l_ic_tran_pnd_out ic_tran_pnd%ROWTYPE;
1251: l_tran_row ic_tran_pnd%ROWTYPE;
1244: l_trans_is_reversed NUMBER;
1245: l_is_plain BOOLEAN;
1246: l_def_lot_id ic_tran_pnd.trans_id%TYPE;
1247: l_completed_ind ic_tran_pnd.completed_ind%TYPE;
1248: l_trans_qty ic_tran_pnd.trans_qty%TYPE;
1249: l_ic_tran_cmp_out ic_tran_cmp%ROWTYPE;
1250: l_ic_tran_pnd_out ic_tran_pnd%ROWTYPE;
1251: l_tran_row ic_tran_pnd%ROWTYPE;
1252: l_tran_reverse gmi_trans_engine_pub.ictran_rec;
1246: l_def_lot_id ic_tran_pnd.trans_id%TYPE;
1247: l_completed_ind ic_tran_pnd.completed_ind%TYPE;
1248: l_trans_qty ic_tran_pnd.trans_qty%TYPE;
1249: l_ic_tran_cmp_out ic_tran_cmp%ROWTYPE;
1250: l_ic_tran_pnd_out ic_tran_pnd%ROWTYPE;
1251: l_tran_row ic_tran_pnd%ROWTYPE;
1252: l_tran_reverse gmi_trans_engine_pub.ictran_rec;
1253: l_tran_pending gmi_trans_engine_pub.ictran_rec;
1254: l_lock_status BOOLEAN;
1247: l_completed_ind ic_tran_pnd.completed_ind%TYPE;
1248: l_trans_qty ic_tran_pnd.trans_qty%TYPE;
1249: l_ic_tran_cmp_out ic_tran_cmp%ROWTYPE;
1250: l_ic_tran_pnd_out ic_tran_pnd%ROWTYPE;
1251: l_tran_row ic_tran_pnd%ROWTYPE;
1252: l_tran_reverse gmi_trans_engine_pub.ictran_rec;
1253: l_tran_pending gmi_trans_engine_pub.ictran_rec;
1254: l_lock_status BOOLEAN;
1255: l_reversible BOOLEAN;
1302: l_load_trans_fail := TRUE;
1303: l_def_lot_id := NULL;
1304:
1305: insert_message (
1306: p_table_name => 'gme_inventory_txns_gtmp/ic_tran_pnd',
1307: p_procedure_name => 'check_wip_batches',
1308: p_parameters => 'batch_id = '
1309: || l_batch_header.batch_id
1310: || ' line_id = '
1332: -- for that was already written, and if load_trans failed, it follows that def lot
1333: -- could not be found.
1334: IF l_load_trans_fail = FALSE THEN
1335: insert_message (
1336: p_table_name => 'gme_inventory_txns_gtmp/ic_tran_pnd',
1337: p_procedure_name => 'check_wip_batches',
1338: p_parameters => 'batch_id = '
1339: || l_batch_header.batch_id
1340: || ' line_id = '
1348: SELECT completed_ind,
1349: trans_qty
1350: INTO l_completed_ind,
1351: l_trans_qty
1352: FROM ic_tran_pnd
1353: WHERE trans_id = l_def_lot_id;
1354: l_pos := 8;
1355:
1356: IF (l_is_plain = TRUE OR
1370: IF p_reverse_compl_def_txns THEN
1371: /* Let's reverse the completed transaction and create a pending transaction for the same qty. */
1372: SELECT *
1373: INTO l_tran_row
1374: FROM ic_tran_pnd
1375: WHERE trans_id = l_def_lot_id;
1376:
1377: /* The cursor is only retrieving ingredients and products (only phantom products will ever */
1378: /* fall into this because phantom products are the only products that can be completed */
1439: g_mig_date,
1440: g_date_format
1441: );
1442: insert_message (
1443: p_table_name => 'IC_TRAN_PND',
1444: p_procedure_name => 'CHECK_WIP_BATCHES',
1445: p_parameters => 'Batch_Id=>'
1446: || TO_CHAR (get_rec.batch_id),
1447: p_message => l_message,
1468: /* B2417758 Moved this line from the statemet above
1469: So that the message will have the correct date */
1470: l_tran_reverse.trans_date := g_mig_date;
1471: insert_message (
1472: p_table_name => 'IC_TRAN_PND',
1473: p_procedure_name => 'CHECK_WIP_BATCHES',
1474: p_parameters => 'Batch_Id=>'
1475: || TO_CHAR (get_rec.batch_id),
1476: p_message => l_message,
1497: x_tran_row => l_ic_tran_cmp_out,
1498: x_return_status => l_return_status,
1499: x_msg_count => l_msg_count,
1500: x_msg_data => l_msg_data,
1501: p_table_name => 'IC_TRAN_PND'
1502: );
1503:
1504: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1505:
1527: END IF;
1528:
1529: l_return_status := FND_API.G_RET_STS_ERROR;
1530: insert_message (
1531: p_table_name => 'IC_TRAN_PND',
1532: p_procedure_name => 'CHECK_WIP_BATCHES',
1533: p_parameters => 'Batch_Id=>'
1534: || TO_CHAR (get_rec.batch_id)
1535: || ' line_id=>'
1576: FND_API.g_false,
1577: FND_API.g_false,
1578: FND_API.g_valid_level_full,
1579: l_tran_pending,
1580: l_ic_tran_pnd_out,
1581: l_return_status,
1582: l_msg_count,
1583: l_msg_data
1584: );
1606: END IF;
1607:
1608: l_return_status := FND_API.G_RET_STS_ERROR;
1609: insert_message (
1610: p_table_name => 'IC_TRAN_PND',
1611: p_procedure_name => 'CHECK_WIP_BATCHES',
1612: p_parameters => 'Batch_Id=>'
1613: || TO_CHAR (get_rec.batch_id)
1614: || ' line_id=>'
1621: RAISE error_create_tran;
1622: END IF;
1623:
1624: insert_inv_txns_gtmp (
1625: p_batch_id => l_ic_tran_pnd_out.doc_id,
1626: p_doc_type => l_ic_tran_pnd_out.doc_type,
1627: p_trans_id => l_ic_tran_pnd_out.trans_id,
1628: x_return_status => l_return_status
1629: );
1622: END IF;
1623:
1624: insert_inv_txns_gtmp (
1625: p_batch_id => l_ic_tran_pnd_out.doc_id,
1626: p_doc_type => l_ic_tran_pnd_out.doc_type,
1627: p_trans_id => l_ic_tran_pnd_out.trans_id,
1628: x_return_status => l_return_status
1629: );
1630:
1623:
1624: insert_inv_txns_gtmp (
1625: p_batch_id => l_ic_tran_pnd_out.doc_id,
1626: p_doc_type => l_ic_tran_pnd_out.doc_type,
1627: p_trans_id => l_ic_tran_pnd_out.trans_id,
1628: x_return_status => l_return_status
1629: );
1630:
1631: IF l_return_status <> x_return_status THEN
1651: || ' line number '
1652: || get_rec.line_no;
1653: l_return_status := FND_API.G_RET_STS_ERROR;
1654: insert_message (
1655: p_table_name => 'IC_TRAN_PND',
1656: p_procedure_name => 'CHECK_WIP_BATCHES',
1657: p_parameters => 'Batch_Id=>'
1658: || TO_CHAR (get_rec.batch_id),
1659: p_message => l_message,
1663: END IF; /* IF l_lock_status OR */
1664: END IF; /* IF p_reverse_compl_def_txns THEN */
1665:
1666: insert_message (
1667: p_table_name => 'IC_TRAN_PND',
1668: p_procedure_name => 'CHECK_WIP_BATCHES',
1669: p_parameters => 'Batch_Id=>' || TO_CHAR (get_rec.batch_id),
1670: p_message => 'Completed default lot transaction found for batch '
1671: || get_rec.batch_no
1682: END IF; /* IF l_def_lot_id IS NULL OR l_return_status ... */
1683: END LOOP;
1684:
1685: insert_message (
1686: p_table_name => 'ic_tran_pnd',
1687: p_procedure_name => 'check_wip_batches',
1688: p_parameters => 'none',
1689: p_message => 'number of transactions reversed = ' || l_reversal_count,
1690: p_error_type => 'P'
1694: x_return_status := l_return_status;
1695: RAISE;
1696: WHEN OTHERS THEN
1697: insert_message (
1698: p_table_name => 'IC_TRAN_PND',
1699: p_procedure_name => 'CHECK_WIP_BATCHES',
1700: p_parameters => '',
1701: p_message => SQLERRM,
1702: p_error_type => 'D'
1704: RAISE;
1705: END check_wip_batches;
1706:
1707: PROCEDURE build_gmi_trans (
1708: p_ic_tran_row IN ic_tran_pnd%ROWTYPE,
1709: x_tran_row OUT NOCOPY gmi_trans_engine_pub.ictran_rec,
1710: x_return_status OUT NOCOPY VARCHAR2
1711: ) IS
1712: l_api_name CONSTANT VARCHAR2 (30) := 'BUILD_GMI_TRANS';
1749: EXCEPTION
1750: WHEN OTHERS THEN
1751: x_return_status := 'D';
1752: insert_message (
1753: p_table_name => 'IC_TRAN_PND',
1754: p_procedure_name => 'BUILD_GMI_TRANS',
1755: p_parameters => 'Batch_Id=>'
1756: || TO_CHAR (p_ic_tran_row.doc_id)
1757: || 'Trans ID=>'
2210: COMMIT;
2211: END IF;
2212: END IF;
2213:
2214: IF (is_table_migrated (p_table_name => 'IC_TRAN_PND_WIP') = FALSE) THEN
2215: l_pos := 4;
2216: -- check_wip_batches must be before insert_material_details because it only checks those
2217: -- materials that have not been migrated
2218: check_wip_batches (
2225: RAISE error_detail;
2226: END IF;
2227:
2228: set_table_migrated (
2229: p_table_name => 'IC_TRAN_PND_WIP',
2230: x_return_status => l_return_status
2231: );
2232:
2233: IF p_commit THEN
4216: b.batch_type = 0
4217: ORDER BY m.batch_id;
4218:
4219: l_matl cur_get_materials%ROWTYPE;
4220: l_tran_rec ic_tran_pnd%ROWTYPE;
4221: l_def_lot_id ic_tran_pnd.trans_id%TYPE;
4222: l_trans_id ic_tran_pnd.trans_id%TYPE;
4223: l_is_plain BOOLEAN;
4224: l_load_trans_fail BOOLEAN;
4217: ORDER BY m.batch_id;
4218:
4219: l_matl cur_get_materials%ROWTYPE;
4220: l_tran_rec ic_tran_pnd%ROWTYPE;
4221: l_def_lot_id ic_tran_pnd.trans_id%TYPE;
4222: l_trans_id ic_tran_pnd.trans_id%TYPE;
4223: l_is_plain BOOLEAN;
4224: l_load_trans_fail BOOLEAN;
4225: l_batch_header pm_btch_hdr_bak%ROWTYPE;
4218:
4219: l_matl cur_get_materials%ROWTYPE;
4220: l_tran_rec ic_tran_pnd%ROWTYPE;
4221: l_def_lot_id ic_tran_pnd.trans_id%TYPE;
4222: l_trans_id ic_tran_pnd.trans_id%TYPE;
4223: l_is_plain BOOLEAN;
4224: l_load_trans_fail BOOLEAN;
4225: l_batch_header pm_btch_hdr_bak%ROWTYPE;
4226: l_pos NUMBER := 0;
4245: l_load_trans_fail := TRUE;
4246: l_def_lot_id := NULL;
4247:
4248: insert_message (
4249: p_table_name => 'gme_inventory_txns_gtmp/ic_tran_pnd',
4250: p_procedure_name => 'check_wip_batches',
4251: p_parameters => 'batch_id = '
4252: || l_batch_header.batch_id
4253: || ' line_id = '
4273: -- for that was already written, and if load_trans failed, it follows that def lot
4274: -- could not be found.
4275: IF l_load_trans_fail = FALSE THEN
4276: insert_message (
4277: p_table_name => 'gme_inventory_txns_gtmp/ic_tran_pnd',
4278: p_procedure_name => 'check_wip_batches',
4279: p_parameters => 'batch_id = '
4280: || l_batch_header.batch_id
4281: || ' line_id = '
4286: END IF;
4287: ELSE
4288: SELECT *
4289: INTO l_tran_rec
4290: FROM ic_tran_pnd
4291: WHERE trans_id = l_def_lot_id;
4292:
4293: IF l_tran_rec.completed_ind = 1 THEN
4294: IF (l_is_plain = FALSE) THEN
4298: Note, that data condition is only valid in WIP batch. */
4299: -- Can get here for non-plain items which could not be reversed in check_wip_batches.
4300: -- So, before flipping the completed ind, make sure it's zero qty and that we can flip the ind.
4301: IF l_tran_rec.trans_qty = 0 THEN
4302: UPDATE ic_tran_pnd
4303: SET completed_ind = 0
4304: WHERE trans_id = l_tran_rec.trans_id;
4305: END IF;
4306:
4307: l_flip_count := l_flip_count + 1;
4308: ELSE -- this is a plain item
4309: -- Just create a new 0 transaction record that's pending if the qty is non zero.
4310: IF l_tran_rec.trans_qty = 0 THEN
4311: UPDATE ic_tran_pnd
4312: SET completed_ind = 0
4313: WHERE trans_id = l_tran_rec.trans_id;
4314: ELSE
4315: SELECT gem5_trans_id_s.NEXTVAL
4315: SELECT gem5_trans_id_s.NEXTVAL
4316: INTO l_trans_id
4317: FROM sys.DUAL;
4318:
4319: INSERT INTO ic_tran_pnd
4320: (trans_id,
4321: item_id,
4322: line_id,
4323: co_code,
4408: END LOOP; /* WHILE cur_get_materials%FOUND LOOP */
4409:
4410: CLOSE cur_get_materials;
4411: insert_message (
4412: p_table_name => 'ic_tran_pnd',
4413: p_procedure_name => 'split_trans_line',
4414: p_parameters => 'none',
4415: p_message => 'number of zero quantity transactions uncompleted = '
4416: || l_flip_count,
4416: || l_flip_count,
4417: p_error_type => 'P'
4418: );
4419: insert_message (
4420: p_table_name => 'ic_tran_pnd',
4421: p_procedure_name => 'split_trans_line',
4422: p_parameters => 'none',
4423: p_message => 'number of zero quantity transactions inserted = '
4424: || l_new_txn_count,
4427: EXCEPTION
4428: WHEN OTHERS THEN
4429: x_return_status := 'D';
4430: insert_message (
4431: p_table_name => 'gme_inventory_txns_gtmp/ic_tran_pnd',
4432: p_procedure_name => 'SPLIT_TRANS_LINE',
4433: p_parameters => 'Batch ID='
4434: || l_matl.batch_id
4435: || ' Line ID='
4498:
4499: l_api_name CONSTANT VARCHAR2 (30) := 'LOAD_TRANS';
4500: l_inv_exists NUMBER := 0;
4501: l_batch_id pm_btch_hdr_bak.batch_id%TYPE;
4502: l_doc_type ic_tran_pnd.doc_type%TYPE;
4503: init_revs c_get_init_reversal%ROWTYPE;
4504: match_revs c_get_match_reversal%ROWTYPE;
4505: l_last_txn c_get_cmplt_zero_def_txns%ROWTYPE;
4506: l_current_txn c_get_cmplt_zero_def_txns%ROWTYPE;
4672: END load_trans;
4673:
4674: PROCEDURE insert_inv_txns_gtmp (
4675: p_batch_id IN pm_btch_hdr_bak.batch_id%TYPE,
4676: p_doc_type IN ic_tran_pnd.doc_type%TYPE,
4677: x_return_status OUT NOCOPY VARCHAR2,
4678: p_trans_id IN ic_tran_pnd.trans_id%TYPE DEFAULT NULL
4679: ) IS
4680: l_all_txns VARCHAR2 (100);
4674: PROCEDURE insert_inv_txns_gtmp (
4675: p_batch_id IN pm_btch_hdr_bak.batch_id%TYPE,
4676: p_doc_type IN ic_tran_pnd.doc_type%TYPE,
4677: x_return_status OUT NOCOPY VARCHAR2,
4678: p_trans_id IN ic_tran_pnd.trans_id%TYPE DEFAULT NULL
4679: ) IS
4680: l_all_txns VARCHAR2 (100);
4681: BEGIN
4682: x_return_status := FND_API.G_RET_STS_SUCCESS;
4750: 0,
4751: NULL,
4752: NULL,
4753: NULL
4754: FROM ic_tran_pnd i
4755: WHERE doc_id = p_batch_id AND
4756: doc_type = p_doc_type AND
4757: -- retrieve only the trans_id passed or if that's NULL, all txns for the batch
4758: (i.trans_id = p_trans_id OR
4791: END insert_inv_txns_gtmp;
4792:
4793: PROCEDURE get_default_lot (
4794: p_line_id IN pm_matl_dtl_bak.line_id%TYPE,
4795: x_def_lot_id OUT NOCOPY ic_tran_pnd.trans_id%TYPE,
4796: x_is_plain OUT NOCOPY BOOLEAN,
4797: x_return_status OUT NOCOPY VARCHAR2
4798: ) IS
4799: l_matl_dtl pm_matl_dtl_bak%ROWTYPE;
4797: x_return_status OUT NOCOPY VARCHAR2
4798: ) IS
4799: l_matl_dtl pm_matl_dtl_bak%ROWTYPE;
4800: l_item_mst ic_item_mst%ROWTYPE;
4801: l_ic_tran_pnd ic_tran_pnd%ROWTYPE;
4802: l_whse_loct_ctl ic_whse_mst.whse_code%TYPE;
4803: l_def_lot_found BOOLEAN;
4804:
4805: CURSOR cur_get_def_trans (
4927: IF l_def_lot_found = FALSE THEN
4928: l_pos := 13;
4929: x_is_plain := TRUE;
4930: SELECT *
4931: INTO l_ic_tran_pnd
4932: FROM ic_tran_pnd
4933: WHERE trans_id = get_rec.trans_id;
4934: deduce_transaction_warehouse (
4935: p_transaction => l_ic_tran_pnd,
4928: l_pos := 13;
4929: x_is_plain := TRUE;
4930: SELECT *
4931: INTO l_ic_tran_pnd
4932: FROM ic_tran_pnd
4933: WHERE trans_id = get_rec.trans_id;
4934: deduce_transaction_warehouse (
4935: p_transaction => l_ic_tran_pnd,
4936: p_item_master => l_item_mst,
4931: INTO l_ic_tran_pnd
4932: FROM ic_tran_pnd
4933: WHERE trans_id = get_rec.trans_id;
4934: deduce_transaction_warehouse (
4935: p_transaction => l_ic_tran_pnd,
4936: p_item_master => l_item_mst,
4937: x_whse_code => l_tran_whse
4938: );
4939: l_pos := 14;
4937: x_whse_code => l_tran_whse
4938: );
4939: l_pos := 14;
4940:
4941: IF (l_tran_whse = l_ic_tran_pnd.whse_code) THEN
4942: x_def_lot_id := get_rec.trans_id;
4943:
4944: IF l_ic_tran_pnd.completed_ind = 0 OR
4945: l_ic_tran_pnd.trans_qty = 0 THEN
4940:
4941: IF (l_tran_whse = l_ic_tran_pnd.whse_code) THEN
4942: x_def_lot_id := get_rec.trans_id;
4943:
4944: IF l_ic_tran_pnd.completed_ind = 0 OR
4945: l_ic_tran_pnd.trans_qty = 0 THEN
4946: l_def_lot_found := TRUE;
4947: END IF;
4948: END IF;
4941: IF (l_tran_whse = l_ic_tran_pnd.whse_code) THEN
4942: x_def_lot_id := get_rec.trans_id;
4943:
4944: IF l_ic_tran_pnd.completed_ind = 0 OR
4945: l_ic_tran_pnd.trans_qty = 0 THEN
4946: l_def_lot_found := TRUE;
4947: END IF;
4948: END IF;
4949: END IF;
4980: );
4981: END get_default_lot;
4982:
4983: PROCEDURE deduce_transaction_warehouse (
4984: p_transaction IN ic_tran_pnd%ROWTYPE,
4985: p_item_master IN ic_item_mst%ROWTYPE,
4986: x_whse_code OUT NOCOPY ps_whse_eff.whse_code%TYPE
4987: ) IS
4988: CURSOR cur_eff_whse (