[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;
1302:
1303: d_position := 330;
1304:
1305: --
1306: -- 5.a. udpate ip category in po_attribute_values table
1307: FORALL i IN INDICES OF l_ip_cat_id_updated_queue
1308: INSERT INTO po_session_gt
1309: (
1310: key,
1318: attribute_values_id,
1319: x_lines.draft_id_tbl(i),
1320: l_modified_ip_cat_id_tbl(i),
1321: 'N'
1322: FROM po_attribute_values
1323: WHERE po_line_id = x_lines.po_line_id_tbl(i);
1324:
1325: d_position := 333;
1326:
1345: d_position := 336;
1346:
1347: -- c. update records in draft table
1348: FORALL i IN 1..l_sync_attr_id_tbl.COUNT
1349: UPDATE po_attribute_values_draft
1350: SET ip_category_id = l_modified_ip_cat_id_tbl_tmp(i)
1351: WHERE attribute_values_id = l_sync_attr_id_tbl(i)
1352: AND draft_id = l_draft_id_tbl(i);
1353:
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);
1905: WHERE p_attr_values_tlp.error_flag_tbl(i) = FND_API.g_FALSE;
1906:
1907: d_position := 20;
1908:
1909: -- insert or update attribute values rows into po_attribute_values_draft
1910: merge_po_attr_values_tlp_draft
1911: (
1912: p_key => l_key,
1913: p_attr_values_tlp => p_attr_values_tlp
3506: --Start of Comments
3507: --Name: merge_po_attr_values_draft
3508: --Function:
3509: -- insert new attribute values or update existing attribute values
3510: -- into po_attribute_values_draft
3511: --Parameters:
3512: --IN:
3513: --p_key
3514: -- key value used to join in MERGE statement
3537: IF (PO_LOG.d_proc) THEN
3538: PO_LOG.proc_begin(d_module);
3539: END IF;
3540:
3541: MERGE INTO po_attribute_values_draft PAVD
3542: USING (
3543: SELECT
3544: NUM2 AS ATTRIBUTE_VALUES_ID,
3545: NUM3 AS DRAFT_ID,
4531: PICTURE
4532: )
4533: VALUES
4534: (
4535: PO_ATTRIBUTE_VALUES_S.nextval,
4536: PAVI.DRAFT_ID,
4537: NULL, -- CHANGE_ACCEPTED_FLAG,
4538: NULL, -- DELETE_FLAG,
4539: PAVI.PO_LINE_ID,
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,
5696: l_key,
5697: attribute_values_id,
5698: p_draft_id_tbl(i),
5699: 'N'
5700: FROM po_attribute_values
5701: WHERE po_line_id = p_po_line_id_tbl(i);
5702:
5703: d_position := 10;
5704:
5722: d_position := 30;
5723:
5724: -- update records in draft table
5725: FORALL i IN 1..l_sync_attr_values_id_tbl.COUNT
5726: UPDATE po_attribute_values_draft
5727: SET TEXT_CAT_ATTRIBUTE1 = NULL,
5728: TEXT_CAT_ATTRIBUTE2 = NULL,
5729: TEXT_CAT_ATTRIBUTE3 = NULL,
5730: TEXT_CAT_ATTRIBUTE4 = NULL,
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,