[Home] [Help]
356: l_uom_warning_queue DBMS_SQL.NUMBER_TABLE;
357: l_price_limit_queue DBMS_SQL.NUMBER_TABLE;
358:
359: -- index of the po line for which description is changed;
360: -- we may need to update po_attribute_values_tlp_draft table
361: l_update_desc_queue DBMS_SQL.NUMBER_TABLE;
362: l_sync_attr_tlp_id_tbl PO_TBL_NUMBER;
363: l_sync_attr_id_tbl PO_TBL_NUMBER; --
364:
694: IF (l_item_desc_tbl(l_index) <> x_lines.item_desc_tbl(i)) THEN
695: l_item_desc_tbl(l_index) := x_lines.item_desc_tbl(i);
696:
697: -- record down the index because we may need to change description
698: -- field in po_attribute_values_tlp_draft
699: l_update_desc_queue(i) := i;
700: END IF;
701: END IF;
702:
1066: since we want to fail the line if item update failed
1067: 2. update line draft table with all changes
1068: 3. update or delete price break depending on document type
1069: 4. give warning message for lines in l_uom_warning_queue
1070: 5. update description field in po_attribute_values_tlp table if necessary
1071: 6. null out all cat attribute values in attribute_values and tlp tables
1072: */
1073: -- 1. update item master with new description
1074: l_index := l_update_item_queue.FIRST;
1353:
1354: d_position := 337;
1355: --
1356:
1357: -- 5. update description field in po_attribute_values_tlp table for current lang
1358: -- b. get rows that need to be synced from txn table to draft table;
1359: FORALL i IN INDICES OF l_update_desc_queue
1360: INSERT INTO po_session_gt
1361: (
1374: x_lines.item_desc_tbl(i),
1375: 'N',
1376: l_update_desc_queue(i), -- index to compare records in MERGE sql below
1377: NULL -- ip_category_id (Dummy insert to ensure that this field is not reused)
1378: FROM po_attribute_values_tlp
1379: WHERE po_line_id = x_lines.po_line_id_tbl(i)
1380: AND language = USERENV('LANG');
1381:
1382: d_position := 340;
1407: VALUES
1408: (
1409: l_key,
1410: (SELECT attribute_values_tlp_id
1411: FROM po_attribute_values_tlp
1412: WHERE po_line_id = x_lines.po_line_id_tbl(i)
1413: AND language = USERENV('LANG')),
1414: x_lines.draft_id_tbl(i),
1415: x_lines.item_desc_tbl(i),
1443: d_position := 360;
1444:
1445: -- c. update records in draft table
1446: FORALL i IN 1..l_sync_attr_tlp_id_tbl.COUNT
1447: UPDATE po_attribute_values_tlp_draft
1448: SET description = l_item_desc_tbl(i),
1449: ip_category_id = NVL(l_modified_ip_cat_id_tbl_tmp(i), ip_category_id) --
1450: WHERE attribute_values_tlp_id = l_sync_attr_tlp_id_tbl(i)
1451: AND draft_id = l_draft_id_tbl(i);
4879: --Start of Comments
4880: --Name: merge_po_attr_values_tlp_draft
4881: --Function:
4882: -- insert new attribute tlp values or update existing tlp values
4883: -- into po_attribute_values_tlp_draft
4884: --Parameters:
4885: --IN:
4886: --p_key
4887: -- key value used to join with gt table
4910: IF (PO_LOG.d_proc) THEN
4911: PO_LOG.proc_begin(d_module);
4912: END IF;
4913:
4914: MERGE INTO po_attribute_values_tlp_draft PAVTD
4915: USING (
4916: SELECT
4917: NUM2 AS ATTRIBUTE_VALUES_TLP_ID,
4918: NUM3 AS DRAFT_ID,
5445: PROGRAM_UPDATE_DATE
5446: )
5447: VALUES
5448: (
5449: PO_ATTRIBUTE_VALUES_TLP_S.nextval,
5450: PAVTI.DRAFT_ID,
5451: NULL, -- CHANGE_ACCEPTED_FLAG,
5452: NULL, -- DELETE_FLAG,
5453: PAVTI.PO_LINE_ID,
5849: l_key,
5850: attribute_values_tlp_id,
5851: p_draft_id_tbl(i),
5852: 'N'
5853: FROM po_attribute_values_tlp
5854: WHERE po_line_id = p_po_line_id_tbl(i);
5855:
5856: d_position := 50;
5857:
5875: d_position := 70;
5876:
5877: -- update records in draft table
5878: FORALL i IN 1..l_sync_attr_values_tlp_id_tbl.COUNT
5879: UPDATE po_attribute_values_tlp_draft
5880: SET TL_TEXT_CAT_ATTRIBUTE1 = NULL,
5881: TL_TEXT_CAT_ATTRIBUTE2 = NULL,
5882: TL_TEXT_CAT_ATTRIBUTE3 = NULL,
5883: TL_TEXT_CAT_ATTRIBUTE4 = NULL,