[Home] [Help]
760: SELECT pipi.interface_line_id, pipi.document_disp_line_number, pipi.auction_line_number,
761: pipi.project_id, pipi.project_task_id, pipi.project_expenditure_type,
762: pipi.project_exp_organization_id, pipi.project_expenditure_item_date,
763: pipi.auction_header_id, pipi.interface_type
764: FROM PON_ITEM_PRICES_INTERFACE pipi
765: WHERE pipi.batch_id=p_batch_id
766: AND pipi.project_id IS NOT NULL
767: AND pipi.project_task_id IS NOT NULL
768: AND pipi.project_expenditure_type IS NOT NULL
800: last_update_date, last_update_login -- 6
801: )
802: VALUES
803: (
804: interface_type, fnd_message.get_string('PON','PON_AUC_LINE_TYPE'), 'PON_ITEM_PRICES_INTERFACE', -- 1
805: line_type, 'PON_INVALID_STYLE_LINETYPE', batch_id, -- 2
806: interface_line_id, auction_header_id, s_line_number, -- 3
807: NULL, NULL, l_exp_date, -- 4
808: l_userid, SYSDATE, l_userid, -- 5
819: pipi.auction_line_number s_line_number,
820: plt.outside_operation_flag po_outside_operation_flag,
821: plt.line_type_id,
822: plt.line_type
823: FROM PON_ITEM_PRICES_INTERFACE pipi,
824: PO_LINE_TYPES plt
825: WHERE batch_id = p_batch_id
826: AND pipi.line_type_id = plt.line_type_id (+)
827: AND pipi.group_type NOT IN ('GROUP','LOT_LINE');
841: last_update_date, last_update_login -- 6
842: )
843: VALUES
844: (
845: interface_type, fnd_message.get_string('PON','PON_RETAINAGE_RATE'), 'PON_ITEM_PRICES_INTERFACE', -- 1
846: retainage_rate_percent, 'PON_RTNG_RATE_WRONG', batch_id, -- 2
847: interface_line_id, auction_header_id, s_line_number, -- 3
848: NULL, NULL, l_exp_date, -- 4
849: l_userid, SYSDATE, l_userid, -- 5
862: last_update_date, last_update_login -- 6
863: )
864: VALUES
865: (
866: interface_type, fnd_message.get_string('PON','PON_MAX_RETAINAGE_AMOUNT'), 'PON_ITEM_PRICES_INTERFACE', -- 1
867: max_retainage_amount , 'PON_MAX_RTNG_WRONG', batch_id, -- 2
868: interface_line_id, auction_header_id, s_line_number, -- 3
869: NULL, NULL, l_exp_date, -- 4
870: l_userid, SYSDATE, l_userid, -- 5
883: last_update_date, last_update_login -- 6
884: )
885: VALUES
886: (
887: interface_type, fnd_message.get_string('PON','PON_ADVANCE_AMOUNT_FLAG'), 'PON_ITEM_PRICES_INTERFACE', -- 1
888: advance_amount, 'PON_ADV_AMT_WRONG', batch_id, -- 2
889: interface_line_id, auction_header_id, s_line_number, -- 3
890: NULL, NULL, l_exp_date, -- 4
891: l_userid, SYSDATE, l_userid, -- 5
904: last_update_date, last_update_login -- 6
905: )
906: VALUES
907: (
908: interface_type, fnd_message.get_string('PON','PON_PROGRESS_PYMT_RATE'), 'PON_ITEM_PRICES_INTERFACE', -- 1
909: progress_pymt_rate_percent, 'PON_PROG_PYMT_RATE_WRONG', batch_id, -- 2
910: interface_line_id, auction_header_id, s_line_number, -- 3
911: NULL, NULL, l_exp_date, -- 4
912: l_userid, SYSDATE, l_userid, -- 5
924: last_update_date, last_update_login -- 6
925: )
926: VALUES
927: (
928: interface_type, fnd_message.get_string('PON','PON_RECOUPMENT_RATE'), 'PON_ITEM_PRICES_INTERFACE', -- 1
929: recoupment_rate_percent, 'PON_RECOUP_RATE_WRONG', batch_id, -- 2
930: interface_line_id, auction_header_id, s_line_number, -- 3
931: NULL, NULL, l_exp_date, -- 4
932: l_userid, SYSDATE, l_userid, -- 5
945: last_update_date, last_update_login -- 6
946: )
947: VALUES
948: (
949: interface_type, fnd_message.get_string('PON','PON_PROGRESS_PYMT_RATE'), 'PON_ITEM_PRICES_INTERFACE', -- 1
950: progress_pymt_rate_percent, 'PON_FIELD_MUST_BE_ENTERED', batch_id, -- 2
951: interface_line_id, auction_header_id, s_line_number, -- 3
952: NULL, NULL, l_exp_date, -- 4
953: l_userid, SYSDATE, l_userid, -- 5
969: last_update_date, last_update_login -- 6
970: )
971: VALUES
972: (
973: interface_type, fnd_message.get_string('PON','PON_RECOUPMENT_RATE'), 'PON_ITEM_PRICES_INTERFACE', -- 1
974: recoupment_rate_percent, 'PON_RECUP_NEEDED_WITH_PPRATE', batch_id, -- 2
975: interface_line_id, auction_header_id, s_line_number, -- 3
976: NULL, NULL, l_exp_date, -- 4
977: l_userid, SYSDATE, l_userid, -- 5
991: last_update_date, last_update_login -- 6
992: )
993: VALUES
994: (
995: interface_type, fnd_message.get_string('PON','PON_RECOUPMENT_RATE'), 'PON_ITEM_PRICES_INTERFACE', -- 1
996: recoupment_rate_percent, 'PON_RECUP_NEEDED_WITH_ADVAMT', batch_id, -- 2
997: interface_line_id, auction_header_id, s_line_number, -- 3
998: NULL, NULL, l_exp_date, -- 4
999: l_userid, SYSDATE, l_userid, -- 5
1013: last_update_date, last_update_login -- 6
1014: )
1015: VALUES
1016: (
1017: interface_type, fnd_message.get_string('PON','PON_ADVANCE_AMOUNT_FLAG'), 'PON_ITEM_PRICES_INTERFACE', -- 1
1018: advance_amount, 'PON_ADV_AMT_MORE', batch_id, -- 2
1019: interface_line_id, auction_header_id, s_line_number, -- 3
1020: NULL, NULL, l_exp_date, -- 4
1021: l_userid, SYSDATE, l_userid, -- 5
1036: last_update_date, last_update_login -- 6
1037: )
1038: VALUES
1039: (
1040: interface_type, fnd_message.get_string('PON','PON_RECOUPMENT_RATE'), 'PON_ITEM_PRICES_INTERFACE', -- 1
1041: recoupment_rate_percent, 'PON_RECOUP_LESS_THAN_ADV', batch_id, -- 2
1042: interface_line_id, auction_header_id, s_line_number, -- 3
1043: NULL, NULL, l_exp_date, -- 4
1044: l_userid, SYSDATE, l_userid, -- 5
1061: last_update_date, last_update_login -- 6
1062: )
1063: VALUES
1064: (
1065: interface_type, fnd_message.get_string('PON','PON_AUC_LINE_TYPE'), 'PON_ITEM_PRICES_INTERFACE', -- 1
1066: line_type, 'PON_INVALID_STYLE_LINETYPE', batch_id, -- 2
1067: interface_line_id, auction_header_id, s_line_number, -- 3
1068: NULL, NULL, l_exp_date, -- 4
1069: l_userid, SYSDATE, l_userid, -- 5
1081: last_update_date, last_update_login -- 6
1082: )
1083: VALUES
1084: (
1085: interface_type, fnd_message.get_string('PON','PON_AUCTS_PROJECT'), 'PON_ITEM_PRICES_INTERFACE', -- 1
1086: project_number, 'PON_PROJ_NUM_INVALID', batch_id, -- 2
1087: interface_line_id, auction_header_id, s_line_number, -- 3
1088: NULL, NULL, l_exp_date, -- 4
1089: l_userid, SYSDATE, l_userid, -- 5
1107: last_update_date, last_update_login -- 6
1108: )
1109: VALUES
1110: (
1111: interface_type, fnd_message.get_string('PON','PON_AUCTS_TASK'), 'PON_ITEM_PRICES_INTERFACE', -- 1
1112: project_task_number, 'PON_PROJ_TASK_INVALID', batch_id, -- 2
1113: interface_line_id, auction_header_id, s_line_number, -- 3
1114: NULL, NULL, l_exp_date, -- 4
1115: l_userid, SYSDATE, l_userid, -- 5
1138: last_update_date, last_update_login -- 6
1139: )
1140: VALUES
1141: (
1142: interface_type, fnd_message.get_string('PON','PON_AUCTS_PROJECT_AWARD'), 'PON_ITEM_PRICES_INTERFACE', -- 1
1143: project_award_number, 'PON_PROJ_AWARD_INVALID', batch_id, -- 2
1144: interface_line_id, auction_header_id, s_line_number, -- 3
1145: NULL, NULL, l_exp_date, -- 4
1146: l_userid, SYSDATE, l_userid, -- 5
1160: last_update_date, last_update_login -- 6
1161: )
1162: VALUES
1163: (
1164: interface_type, fnd_message.get_string('PON','PON_AUCTS_EXPENDITUE_ORG'), 'PON_ITEM_PRICES_INTERFACE', -- 1
1165: project_exp_organization_name,'PON_PROJ_EXPORG_INVALID', batch_id, -- 2
1166: interface_line_id, auction_header_id, s_line_number, -- 3
1167: NULL, NULL, l_exp_date, -- 4
1168: l_userid, SYSDATE, l_userid, -- 5
1189: last_update_date, last_update_login -- 6
1190: )
1191: VALUES
1192: (
1193: interface_type, fnd_message.get_string('PON','PON_AUCTS_EXPENDITUE_TYPE'), 'PON_ITEM_PRICES_INTERFACE', -- 1
1194: s_project_exp_type, 'PON_PROJ_EXPTYPE_INVALID', batch_id, -- 2
1195: interface_line_id, auction_header_id, s_line_number, -- 3
1196: NULL, NULL, l_exp_date, -- 4
1197: l_userid, SYSDATE, l_userid, -- 5
1212: last_update_date, last_update_login -- 6
1213: )
1214: VALUES
1215: (
1216: interface_type, fnd_message.get_string('PON','PON_AUCTS_PROJECT_AWARD'), 'PON_ITEM_PRICES_INTERFACE', -- 1
1217: project_award_number, 'PON_PROJ_AWARD_NULL', batch_id, -- 2
1218: interface_line_id, auction_header_id, s_line_number, -- 3
1219: NULL, NULL, l_exp_date, -- 4
1220: l_userid, SYSDATE, l_userid, -- 5
1236: last_update_date, last_update_login -- 6
1237: )
1238: VALUES
1239: (
1240: interface_type, fnd_message.get_string('PON','PON_AUCTS_PROJECT'), 'PON_ITEM_PRICES_INTERFACE', -- 1
1241: NULL, 'PON_PROJ_INFO_INCOMPLETE', batch_id, -- 2
1242: interface_line_id, auction_header_id, s_line_number, -- 3
1243: NULL, NULL, l_exp_date, -- 4
1244: l_userid, SYSDATE, l_userid, -- 5
1265: last_update_date, last_update_login -- 6
1266: )
1267: VALUES
1268: (
1269: interface_type, fnd_message.get_string('PON','PON_DEFAULT_OWNER'), 'PON_ITEM_PRICES_INTERFACE', -- 1
1270: NULL, 'PON_LIN_OWNER_INVALID', batch_id, -- 2
1271: interface_line_id, auction_header_id, s_line_number, -- 3
1272: NULL, NULL, l_exp_date, -- 4
1273: l_userid, SYSDATE, l_userid, -- 5
1302: plt.outside_operation_flag po_outside_operation_flag,
1303: plt.line_type_id,
1304: plt.line_type,
1305: Nvl(pipi.CLM_INFO_FLAG, 'N') S_CLM_INFO_FLAG
1306: FROM PON_ITEM_PRICES_INTERFACE pipi,
1307: PA_PROJECTS_EXPEND_V pro,
1308: PA_ORGANIZATIONS_EXPEND_V porg,
1309: PO_LINE_TYPES plt
1310: WHERE batch_id = p_batch_id
1313: AND pipi.line_type_id = plt.line_type_id (+)
1314: AND pipi.group_type NOT IN ('GROUP','LOT_LINE');
1315:
1316: --Derive id columns and update the interface table
1317: UPDATE PON_ITEM_PRICES_INTERFACE pipi1
1318: SET (PROJECT_ID, PROJECT_TASK_ID, PROJECT_AWARD_ID, PROJECT_EXP_ORGANIZATION_ID) =
1319: (SELECT pro.project_id, task.task_id, award.award_id, porg.organization_id
1320: FROM PA_PROJECTS_ALL pro,
1321: PA_TASKS_EXPEND_V task,
1320: FROM PA_PROJECTS_ALL pro,
1321: PA_TASKS_EXPEND_V task,
1322: GMS_AWARDS_ALL award,
1323: HR_ALL_ORGANIZATION_UNITS porg,
1324: PON_ITEM_PRICES_INTERFACE pipi
1325: WHERE pipi.project_number = pro.segment1
1326: AND pipi.project_task_number = task.task_number
1327: AND pro.project_id = task.project_id
1328: AND pipi.project_award_number = award.award_number(+)
1330: AND pipi.batch_id = pipi1.batch_id
1331: AND pipi.interface_line_id = pipi1.interface_line_id)
1332: WHERE pipi1.batch_id = p_batch_id;
1333:
1334: UPDATE PON_ITEM_PRICES_INTERFACE pipi
1335: SET (WORK_APPROVER_USER_ID) =
1336: (SELECT fu.user_id
1337: FROM FND_USER fu
1338: WHERE pipi.work_approver_user_name = fu.user_name)
1355: p_payment_id => null,
1356: p_interface_line_id => l_proj_record.interface_line_id,
1357: p_payment_display_number => null,
1358: p_batch_id => p_batch_id,
1359: p_table_name => 'PON_ITEM_PRICES_INTERFACE',
1360: p_interface_type => l_proj_record.interface_type,
1361: p_entity_type => null,
1362: p_called_from => 'LINES_SP');
1363: END LOOP;
1394:
1395: select ah.po_style_id
1396: into x_po_style_id
1397: from pon_auction_headers_all ah,
1398: pon_item_prices_interface ipi
1399: where ipi.batch_id = p_batch_id
1400: and ipi.auction_header_id = ah.auction_header_id
1401: and rownum = 1;
1402:
1450: CURSOR C_ship_to (c_batch_id NUMBER) IS
1451: SELECT
1452: INTERFACE_LINE_ID
1453: , SHIP_TO_LOCATION_ID
1454: FROM PON_ITEM_PRICES_INTERFACE
1455: WHERE BATCH_ID = c_batch_id
1456: AND SHIP_TO_LOCATION_ID <> -1
1457: AND SHIP_TO_LOCATION <> 'SHIP_NONE_ENTERED';
1458: --
1567:
1568:
1569: select auction_header_id
1570: into l_auction_header_id
1571: from pon_item_prices_interface
1572: where batch_id = p_batch_id
1573: and auction_header_id is not null
1574: and rownum =1;
1575:
1619: SELECT Count(*) INTO l_sol_line_count FROM pon_auction_item_prices_all
1620: WHERE auction_header_id = l_auction_header_id
1621: AND group_line_id IS NULL;
1622:
1623: SELECT Count(*) INTO l_int_line_count FROM pon_item_prices_interface
1624: WHERE batch_id = p_batch_id
1625: AND group_line_id IS NULL;
1626:
1627: IF( (l_sol_line_count+l_int_line_count) > 9999 ) THEN
1636: select interface_type,
1637: fnd_message.get_string('PON','PON_AUC_TYPE_REQ'),
1638: 'Number of clins can not exceed 9999',
1639: 'PON_TOO_MANY_CLINS',
1640: 'PON_ITEM_PRICES_INTERFACE',
1641: batch_id,
1642: interface_line_id
1643: from pon_item_prices_interface
1644: where batch_id = p_batch_id
1639: 'PON_TOO_MANY_CLINS',
1640: 'PON_ITEM_PRICES_INTERFACE',
1641: batch_id,
1642: interface_line_id
1643: from pon_item_prices_interface
1644: where batch_id = p_batch_id
1645: AND ROWNUM < 2;
1646:
1647: END IF;
1650: bug 9818972
1651: Number of priced slins can't exceed 576 (i.e. 24 * 24) and
1652: number of info slins can't exceed 99 for a clin)
1653: */
1654: FOR more_slins IN (SELECT group_line_id,clm_info_flag FROM pon_item_prices_interface WHERE batch_id = p_batch_id
1655: AND group_line_id IS NOT NULL
1656: GROUP BY clm_info_flag,group_line_id
1657: HAVING Count(*) > Decode(nvl(clm_info_flag,'N'),'Y',99,'N',576)) LOOP
1658:
1667: select interface_type,
1668: fnd_message.get_string('PON','PON_AUC_TYPE_REQ'),
1669: 'Slins',
1670: 'PON_TOO_MANY_SLINS',
1671: 'PON_ITEM_PRICES_INTERFACE',
1672: batch_id,
1673: interface_line_id
1674: from pon_item_prices_interface
1675: where batch_id = p_batch_id
1670: 'PON_TOO_MANY_SLINS',
1671: 'PON_ITEM_PRICES_INTERFACE',
1672: batch_id,
1673: interface_line_id
1674: from pon_item_prices_interface
1675: where batch_id = p_batch_id
1676: AND auction_line_number = more_slins.group_line_id;
1677:
1678: END LOOP;
1700: select interface_type,
1701: fnd_message.get_string('PON','PON_AUCTS_QUANTITY'),
1702: quantity,
1703: 'PON_AUCTS_PR_QT_NOT_APPLY',
1704: 'PON_ITEM_PRICES_INTERFACE',
1705: batch_id,
1706: interface_line_id
1707: from pon_item_prices_interface
1708: where nvl(price_and_quantity_apply, 'Y') = 'N' and
1703: 'PON_AUCTS_PR_QT_NOT_APPLY',
1704: 'PON_ITEM_PRICES_INTERFACE',
1705: batch_id,
1706: interface_line_id
1707: from pon_item_prices_interface
1708: where nvl(price_and_quantity_apply, 'Y') = 'N' and
1709: quantity is not null and
1710: batch_id = p_batch_id;
1711: END IF;
1723: select interface_type,
1724: fnd_message.get_string('PON','PON_AUCTION_UOM'),
1725: unit_of_measure,
1726: 'PON_AUCTS_PR_QT_NOT_APPLY',
1727: 'PON_ITEM_PRICES_INTERFACE',
1728: batch_id,
1729: interface_line_id
1730: from pon_item_prices_interface
1731: where nvl(price_and_quantity_apply, 'Y') = 'N' and
1726: 'PON_AUCTS_PR_QT_NOT_APPLY',
1727: 'PON_ITEM_PRICES_INTERFACE',
1728: batch_id,
1729: interface_line_id
1730: from pon_item_prices_interface
1731: where nvl(price_and_quantity_apply, 'Y') = 'N' and
1732: (unit_of_measure is not null and unit_of_measure <> 'UOM_NONE_ENTERED') and
1733: batch_id = p_batch_id;
1734: END IF;
1746: select interface_type,
1747: fnd_message.get_string('PON','PON_AUCTS_TARGET_PRICE'),
1748: target_price,
1749: 'PON_AUCTS_PR_QT_NOT_APPLY',
1750: 'PON_ITEM_PRICES_INTERFACE',
1751: batch_id,
1752: interface_line_id
1753: from pon_item_prices_interface
1754: where nvl(price_and_quantity_apply, 'Y') = 'N' and
1749: 'PON_AUCTS_PR_QT_NOT_APPLY',
1750: 'PON_ITEM_PRICES_INTERFACE',
1751: batch_id,
1752: interface_line_id
1753: from pon_item_prices_interface
1754: where nvl(price_and_quantity_apply, 'Y') = 'N' and
1755: target_price is not null and
1756: batch_id = p_batch_id;
1757: END IF;
1769: select interface_type,
1770: fnd_message.get_string('PON','PON_AUCTS_CURRENT_PRICE'),
1771: current_price,
1772: 'PON_AUCTS_PR_QT_NOT_APPLY',
1773: 'PON_ITEM_PRICES_INTERFACE',
1774: batch_id,
1775: interface_line_id
1776: from pon_item_prices_interface
1777: where nvl(price_and_quantity_apply, 'Y') = 'N' and
1772: 'PON_AUCTS_PR_QT_NOT_APPLY',
1773: 'PON_ITEM_PRICES_INTERFACE',
1774: batch_id,
1775: interface_line_id
1776: from pon_item_prices_interface
1777: where nvl(price_and_quantity_apply, 'Y') = 'N' and
1778: current_price is not null and
1779: batch_id = p_batch_id;
1780: END IF;
1791: interface_line_id)
1792: select interface_type,
1793: fnd_message.get_string('PON','PON_AUC_LINE_TYPE'),
1794: 'PON_FIELD_MUST_BE_ENTERED',
1795: 'PON_ITEM_PRICES_INTERFACE',
1796: batch_id,
1797: interface_line_id
1798: from pon_item_prices_interface
1799: where line_type = 'LINE_TYPE_NONE_ENTERED'
1794: 'PON_FIELD_MUST_BE_ENTERED',
1795: 'PON_ITEM_PRICES_INTERFACE',
1796: batch_id,
1797: interface_line_id
1798: from pon_item_prices_interface
1799: where line_type = 'LINE_TYPE_NONE_ENTERED'
1800: and batch_id = p_batch_id
1801: and group_type <> 'GROUP'
1802: AND Nvl(clm_info_flag,'N') <> 'Y';
1800: and batch_id = p_batch_id
1801: and group_type <> 'GROUP'
1802: AND Nvl(clm_info_flag,'N') <> 'Y';
1803:
1804: --update the pon_item_prices_interface table
1805:
1806: update pon_item_prices_interface p1
1807: set (line_type_id,order_type_lookup_code,purchase_basis,outside_operation_flag) =
1808: (select nvl(po2.line_type_id,-9999), po2.order_type_lookup_code,po2.purchase_basis,po2.outside_operation_flag
1802: AND Nvl(clm_info_flag,'N') <> 'Y';
1803:
1804: --update the pon_item_prices_interface table
1805:
1806: update pon_item_prices_interface p1
1807: set (line_type_id,order_type_lookup_code,purchase_basis,outside_operation_flag) =
1808: (select nvl(po2.line_type_id,-9999), po2.order_type_lookup_code,po2.purchase_basis,po2.outside_operation_flag
1809: FROM po_line_types_vl po2 WHERE upper(p1.line_type) = upper(po2.line_type(+))
1810: and (po2.inactive_date is null or po2.inactive_date > sysdate))
1825: select interface_type,
1826: fnd_message.get_string('PON','PON_AUC_LINE_TYPE'),
1827: line_type,
1828: 'PON_AUC_LINE_TYPE_ERR',
1829: 'PON_ITEM_PRICES_INTERFACE',
1830: batch_id,
1831: interface_line_id
1832: from pon_item_prices_interface
1833: where line_type_id is null
1828: 'PON_AUC_LINE_TYPE_ERR',
1829: 'PON_ITEM_PRICES_INTERFACE',
1830: batch_id,
1831: interface_line_id
1832: from pon_item_prices_interface
1833: where line_type_id is null
1834: and line_type <> 'LINE_TYPE_NONE_ENTERED'
1835: and batch_id = p_batch_id
1836: AND Nvl(clm_info_flag,'N') <> 'Y';
1849: select interface_type,
1850: fnd_message.get_string('PON','PON_AUC_LINE_TYPE'),
1851: line_type,
1852: 'PON_AUC_LINE_TYPE_ERR',
1853: 'PON_ITEM_PRICES_INTERFACE',
1854: batch_id,
1855: interface_line_id
1856: from pon_item_prices_interface
1857: where line_type_id not in ( select line_type_id
1852: 'PON_AUC_LINE_TYPE_ERR',
1853: 'PON_ITEM_PRICES_INTERFACE',
1854: batch_id,
1855: interface_line_id
1856: from pon_item_prices_interface
1857: where line_type_id not in ( select line_type_id
1858: from po_style_enabled_line_types
1859: where style_id = l_po_style_id)
1860: and line_type_id is not null
1877: select interface_type,
1878: fnd_message.get_string('PON','PON_AUC_LINE_TYPE'),
1879: line_type,
1880: 'PON_AUC_LINE_TYPE_ERR',
1881: 'PON_ITEM_PRICES_INTERFACE',
1882: batch_id,
1883: interface_line_id
1884: from pon_item_prices_interface
1885: where purchase_basis not in ( select purchase_basis
1880: 'PON_AUC_LINE_TYPE_ERR',
1881: 'PON_ITEM_PRICES_INTERFACE',
1882: batch_id,
1883: interface_line_id
1884: from pon_item_prices_interface
1885: where purchase_basis not in ( select purchase_basis
1886: from po_style_enabled_pur_bases
1887: where style_id = l_po_style_id)
1888: and line_type_id is not null
1910: SELECT interface_type,
1911: fnd_message.get_string('PON','PON_AUC_LINE_TYPE'),
1912: line_type,
1913: 'PON_STANDARD_LINE_TYPES',
1914: 'PON_ITEM_PRICES_INTERFACE',
1915: batch_id,
1916: interface_line_id
1917: FROM pon_item_prices_interface
1918: WHERE line_type <> 'LINE_TYPE_NONE_ENTERED'
1913: 'PON_STANDARD_LINE_TYPES',
1914: 'PON_ITEM_PRICES_INTERFACE',
1915: batch_id,
1916: interface_line_id
1917: FROM pon_item_prices_interface
1918: WHERE line_type <> 'LINE_TYPE_NONE_ENTERED'
1919: AND batch_id = p_batch_id
1920: AND purchase_basis = 'TEMP LABOR';
1921: END IF;
1933: SELECT interface_type,
1934: fnd_message.get_string('PON','PON_AUC_LINE_TYPE'),
1935: line_type,
1936: 'PON_GLOBAL_LINE_TYPES',
1937: 'PON_ITEM_PRICES_INTERFACE',
1938: batch_id,
1939: interface_line_id
1940: FROM pon_item_prices_interface
1941: WHERE line_type <> 'LINE_TYPE_NONE_ENTERED'
1936: 'PON_GLOBAL_LINE_TYPES',
1937: 'PON_ITEM_PRICES_INTERFACE',
1938: batch_id,
1939: interface_line_id
1940: FROM pon_item_prices_interface
1941: WHERE line_type <> 'LINE_TYPE_NONE_ENTERED'
1942: AND batch_id = p_batch_id
1943: AND purchase_basis = 'TEMP LABOR';
1944: END IF;
1957: SELECT interface_type,
1958: fnd_message.get_string('PON','PON_AUC_LINE_TYPE'),
1959: line_type,
1960: 'PON_AUC_GLOBAL_OP_LINE',
1961: 'PON_ITEM_PRICES_INTERFACE',
1962: batch_id,
1963: interface_line_id
1964: FROM pon_item_prices_interface
1965: WHERE line_type <> 'LINE_TYPE_NONE_ENTERED'
1960: 'PON_AUC_GLOBAL_OP_LINE',
1961: 'PON_ITEM_PRICES_INTERFACE',
1962: batch_id,
1963: interface_line_id
1964: FROM pon_item_prices_interface
1965: WHERE line_type <> 'LINE_TYPE_NONE_ENTERED'
1966: AND batch_id = p_batch_id
1967: AND outside_operation_flag = 'Y';
1968: END IF;
1980: SELECT interface_type,
1981: fnd_message.get_string('PON','PON_AUCTS_ITEM'),
1982: null,
1983: 'PON_AUC_OPL_ITEM_REQ',
1984: 'PON_ITEM_PRICES_INTERFACE',
1985: batch_id,
1986: interface_line_id
1987: FROM pon_item_prices_interface
1988: WHERE line_type <> 'LINE_TYPE_NONE_ENTERED'
1983: 'PON_AUC_OPL_ITEM_REQ',
1984: 'PON_ITEM_PRICES_INTERFACE',
1985: batch_id,
1986: interface_line_id
1987: FROM pon_item_prices_interface
1988: WHERE line_type <> 'LINE_TYPE_NONE_ENTERED'
1989: AND item_number = 'ITEM_NUMBER_NONE_ENTERED'
1990: AND batch_id = p_batch_id
1991: AND outside_operation_flag = 'Y';
2003: SELECT interface_type,
2004: fnd_message.get_string('PON','PON_ITEM_JOB'),
2005: '',
2006: 'PON_LINE_TYPE_JOB_REQ',
2007: 'PON_ITEM_PRICES_INTERFACE',
2008: batch_id,
2009: interface_line_id
2010: FROM pon_item_prices_interface
2011: WHERE batch_id = p_batch_id
2006: 'PON_LINE_TYPE_JOB_REQ',
2007: 'PON_ITEM_PRICES_INTERFACE',
2008: batch_id,
2009: interface_line_id
2010: FROM pon_item_prices_interface
2011: WHERE batch_id = p_batch_id
2012: AND purchase_basis = 'TEMP LABOR'
2013: AND item_number = 'ITEM_NUMBER_NONE_ENTERED';
2014: --
2017: -- because there are not enterable on the UI.
2018: --
2019: -- First just get the job_id...
2020: --
2021: UPDATE pon_item_prices_interface p1
2022: SET job_id =
2023: (SELECT nvl(max(poj.job_id),-1)
2024: FROM po_job_associations poj,
2025: per_jobs pj,
2034: item_number <> 'ITEM_NUMBER_NONE_ENTERED';
2035: --
2036: -- Update the rest of the information for the service lines with job id's
2037: --
2038: UPDATE pon_item_prices_interface p1
2039: SET (item_description, category_name) =
2040: (SELECT poj.job_long_description,
2041: FND_FLEX_EXT.get_segs('INV', 'MCAT', CAT.STRUCTURE_ID, CAT.CATEGORY_ID)
2042: FROM po_job_associations poj,
2061: SELECT interface_type,
2062: fnd_message.get_string('PON','PON_ITEM_JOB'),
2063: item_number,
2064: 'PON_JOB_INVALID',
2065: 'PON_ITEM_PRICES_INTERFACE',
2066: batch_id,
2067: interface_line_id
2068: FROM pon_item_prices_interface p1
2069: WHERE batch_id = p_batch_id AND
2064: 'PON_JOB_INVALID',
2065: 'PON_ITEM_PRICES_INTERFACE',
2066: batch_id,
2067: interface_line_id
2068: FROM pon_item_prices_interface p1
2069: WHERE batch_id = p_batch_id AND
2070: purchase_basis = 'TEMP LABOR' AND
2071: item_number <> 'ITEM_NUMBER_NONE_ENTERED' AND
2072: job_id = -1;
2072: job_id = -1;
2073: --
2074: -- Set quantity to null for general services lines
2075: --
2076: UPDATE pon_item_prices_interface p1
2077: SET quantity = NULL,
2078: unit_of_measure = NULL
2079: WHERE batch_id = p_batch_id
2080: AND order_type_lookup_code = 'FIXED PRICE'
2081: AND purchase_basis = 'SERVICES';
2082: --
2083: -- Set quantity to null for fixed price temp labor lines
2084: --
2085: UPDATE pon_item_prices_interface p1
2086: SET quantity = null
2087: WHERE batch_id = p_batch_id
2088: AND order_type_lookup_code = 'FIXED PRICE'
2089: AND purchase_basis = 'TEMP LABOR';
2090: --
2091: -- Default category, job_description,
2092: -- additional_job_details for temp labor lines
2093: --
2094: UPDATE pon_item_prices_interface p1
2095: SET (item_description, additional_job_details, category_id, category_name) =
2096: (SELECT poj.job_description,
2097: decode(nvl(p1.additional_job_details,'JOB_DETAILS_NONE_ENTERED'),'JOB_DETAILS_NONE_ENTERED',poj.job_long_description, p1.additional_job_details),
2098: cat.category_id,
2118: select interface_type,
2119: fnd_message.get_string('PON','PON_PRICE_DIFF_RESPONSE'),
2120: differential_response_type,
2121: 'PON_INVALID_DIFF_RESPONSE',
2122: 'PON_ITEM_PRICES_INTERFACE',
2123: batch_id,
2124: interface_line_id
2125: from pon_item_prices_interface p1
2126: where batch_id = p_batch_id and
2121: 'PON_INVALID_DIFF_RESPONSE',
2122: 'PON_ITEM_PRICES_INTERFACE',
2123: batch_id,
2124: interface_line_id
2125: from pon_item_prices_interface p1
2126: where batch_id = p_batch_id and
2127: purchase_basis = 'TEMP LABOR' and
2128: differential_response_type not in ('DIFF_NONE_ENTERED',
2129: fnd_message.get_string('PON','PON_AUCTS_REQUIRED'),
2131: fnd_message.get_string('PON','PON_AUCTS_DISPLAY_ONLY'));
2132: --
2133: -- clear out the differential response type
2134: -- if it does not apply
2135: update pon_item_prices_interface
2136: set differential_response_type = null
2137: where batch_id = p_batch_id and
2138: (purchase_basis = 'TEMP LABOR' and
2139: differential_response_type not in (fnd_message.get_string('PON','PON_AUCTS_REQUIRED'),
2159: select interface_type,
2160: fnd_message.get_string('PON','PON_AUCTS_ITEM'),
2161: item_number,
2162: 'PON_AUCTS_SS_INVALID_INV_NUM',
2163: 'PON_ITEM_PRICES_INTERFACE',
2164: batch_id,
2165: interface_line_id
2166: from pon_item_prices_interface p1
2167: where batch_id = p_batch_id and
2162: 'PON_AUCTS_SS_INVALID_INV_NUM',
2163: 'PON_ITEM_PRICES_INTERFACE',
2164: batch_id,
2165: interface_line_id
2166: from pon_item_prices_interface p1
2167: where batch_id = p_batch_id and
2168: item_number <> 'ITEM_NUMBER_NONE_ENTERED' and
2169: upper(order_type_lookup_code) <> upper('AMOUNT') and
2170: purchase_basis <> 'TEMP LABOR' and
2189: and nvl(mck.disable_date, sysdate + 1) > sysdate
2190: and (mdsv.validate_flag='Y' and mck.category_id in (select mcsv.category_id from mtl_category_set_valid_cats mcsv where mcsv.category_set_id = mdsv.category_set_id) or mdsv.validate_flag <> 'Y'));
2191:
2192: -- set item number and revision to null if this is a group
2193: update pon_item_prices_interface p1
2194: set item_number = null,
2195: item_revision = null
2196: where batch_id = p_batch_id and
2197: item_number = 'ITEM_NUMBER_NONE_ENTERED' and
2200: -- sets item number and revision to null if item number is invalid or line type is amount based
2201: -- by setting the item number to null, sourcing one-time item validation will occur for
2202: -- unit of measure, category, etc..
2203:
2204: update pon_item_prices_interface p1
2205: set item_number = null,
2206: item_revision = null
2207: where batch_id = p_batch_id AND
2208: purchase_basis = 'SERVICES' or
2232:
2233: -- set inventory item id, the description update flag, and default the description and unit of measure
2234: -- it not entered for inventory items
2235:
2236: update pon_item_prices_interface p1
2237: set (item_id, item_description, allow_item_desc_update_flag, unit_of_measure) =
2238: (select msi.inventory_item_id,
2239: decode(p1.item_description, 'ITEM_NONE_ENTERED', msitl.description, p1.item_description),
2240: msi.allow_item_desc_update_flag,
2254: where batch_id = p_batch_id and
2255: item_number is not NULL and
2256: purchase_basis = 'GOODS';
2257:
2258: UPDATE pon_item_prices_interface
2259: SET unit_of_measure = NULL
2260: WHERE batch_id = p_batch_id
2261: AND Nvl(clm_info_flag,'N') = 'Y'
2262: AND unit_of_measure = 'UOM_NONE_ENTERED'; -- bug 9504539
2262: AND unit_of_measure = 'UOM_NONE_ENTERED'; -- bug 9504539
2263:
2264: -- default the category if not entered for valid item numbers
2265:
2266: update pon_item_prices_interface p1
2267: set category_name = (select FND_FLEX_EXT.get_segs('INV', 'MCAT', MCK.STRUCTURE_ID, MCK.CATEGORY_ID) concatenated_segments
2268: from mtl_default_sets_view mdsv,
2269: mtl_item_categories mic,
2270: mtl_categories_kfv mck
2291: select interface_type,
2292: fnd_message.get_string('PON','PON_AUCTS_ITEM_DESC'),
2293: item_description,
2294: 'PON_AUCTS_INVALID_INV_DESC',
2295: 'PON_ITEM_PRICES_INTERFACE',
2296: batch_id,
2297: interface_line_id
2298: from pon_item_prices_interface p1
2299: where batch_id = p_batch_id and
2294: 'PON_AUCTS_INVALID_INV_DESC',
2295: 'PON_ITEM_PRICES_INTERFACE',
2296: batch_id,
2297: interface_line_id
2298: from pon_item_prices_interface p1
2299: where batch_id = p_batch_id and
2300: purchase_basis <> 'TEMP LABOR' and
2301: item_number is not null and
2302: allow_item_desc_update_flag = 'N' and
2322: select interface_type,
2323: fnd_message.get_string('PON','PON_AUCTS_REVISION'),
2324: item_revision,
2325: 'PON_AUCTS_INVALID_INV_REV',
2326: 'PON_ITEM_PRICES_INTERFACE',
2327: batch_id,
2328: interface_line_id
2329: from pon_item_prices_interface p1
2330: where batch_id = p_batch_id and
2325: 'PON_AUCTS_INVALID_INV_REV',
2326: 'PON_ITEM_PRICES_INTERFACE',
2327: batch_id,
2328: interface_line_id
2329: from pon_item_prices_interface p1
2330: where batch_id = p_batch_id and
2331: purchase_basis <> 'TEMP LABOR' and
2332: item_number is not null and
2333: item_revision not in (select revision
2335: where inventory_item_id = p1.item_id and
2336: organization_id = l_inventory_org_id);
2337:
2338:
2339: update pon_item_prices_interface p1
2340: set CATEGORY_NAME = (select FND_FLEX_EXT.get_segs('INV', 'MCAT', MCK.STRUCTURE_ID, MCK.CATEGORY_ID) concatenated_segments
2341: from
2342: MTL_CATEGORIES_KFV mck
2343: ,PO_LINE_TYPES plt
2346: and plt.category_id = mck.category_id)
2347: where batch_id = p_batch_id
2348: and ((CATEGORY_NAME = 'CAT_NONE_ENTERED') or (CATEGORY_NAME is null));
2349:
2350: update pon_item_prices_interface p1
2351: set UNIT_OF_MEASURE = nvl(l_amount_based_unit_of_measure,'UOM_NONE_ENTERED')
2352: where batch_id = p_batch_id
2353: and ((UNIT_OF_MEASURE = 'UOM_NONE_ENTERED') or (UNIT_OF_MEASURE is null))
2354: and ( upper(order_type_lookup_code) = upper('AMOUNT'))
2353: and ((UNIT_OF_MEASURE = 'UOM_NONE_ENTERED') or (UNIT_OF_MEASURE is null))
2354: and ( upper(order_type_lookup_code) = upper('AMOUNT'))
2355: AND Nvl(clm_info_flag,'N') <> 'Y';
2356:
2357: update pon_item_prices_interface p1
2358: set UNIT_OF_MEASURE = (select plt.unit_of_measure
2359: from
2360: PO_LINE_TYPES plt
2361: where
2377: SELECT INTERFACE_TYPE
2378: , fnd_message.get_string('PON','PON_AUCTION_UOM')
2379: , unit_of_measure
2380: , 'PON_AUC_LINE_UOM_ERR'
2381: , 'PON_ITEM_PRICES_INTERFACE'
2382: , BATCH_ID
2383: , INTERFACE_LINE_ID
2384: FROM pon_item_prices_interface
2385: where batch_id = p_batch_id
2380: , 'PON_AUC_LINE_UOM_ERR'
2381: , 'PON_ITEM_PRICES_INTERFACE'
2382: , BATCH_ID
2383: , INTERFACE_LINE_ID
2384: FROM pon_item_prices_interface
2385: where batch_id = p_batch_id
2386: and nvl(price_and_quantity_apply, 'Y') = 'Y'
2387: and order_type_lookup_code = 'AMOUNT'
2388: and NOT((upper(UNIT_OF_MEASURE) = UPPER(l_amount_based_uom) ) or
2402: INTERFACE_TYPE
2403: , fnd_message.get_string('PON',decode(p_contract_type,'STANDARD','PON_AUCTS_QUANTITY','PON_AUCTS_EST_QUANTITY'))
2404: , quantity
2405: , 'PON_AUC_LINE_QUAN_ERR'
2406: , 'PON_ITEM_PRICES_INTERFACE'
2407: , BATCH_ID
2408: , INTERFACE_LINE_ID
2409: FROM pon_item_prices_interface
2410: where batch_id = p_batch_id
2405: , 'PON_AUC_LINE_QUAN_ERR'
2406: , 'PON_ITEM_PRICES_INTERFACE'
2407: , BATCH_ID
2408: , INTERFACE_LINE_ID
2409: FROM pon_item_prices_interface
2410: where batch_id = p_batch_id
2411: and nvl(price_and_quantity_apply, 'Y') = 'Y'
2412: and ( upper(order_type_lookup_code) = upper('AMOUNT'))
2413: --Bug 16801061
2427: interface_line_id)
2428: select interface_type,
2429: fnd_message.get_string('PON','PON_AUCTS_CATEGORY'),
2430: 'PON_FIELD_MUST_BE_ENTERED',
2431: 'PON_ITEM_PRICES_INTERFACE',
2432: batch_id,
2433: interface_line_id
2434: from pon_item_prices_interface
2435: where ((category_name = 'CAT_NONE_ENTERED') or (category_name is null))
2430: 'PON_FIELD_MUST_BE_ENTERED',
2431: 'PON_ITEM_PRICES_INTERFACE',
2432: batch_id,
2433: interface_line_id
2434: from pon_item_prices_interface
2435: where ((category_name = 'CAT_NONE_ENTERED') or (category_name is null))
2436: AND batch_id = p_batch_id
2437: and group_type <> 'GROUP'
2438: AND Nvl(clm_info_flag,'N') <> 'Y';
2437: and group_type <> 'GROUP'
2438: AND Nvl(clm_info_flag,'N') <> 'Y';
2439:
2440: -- First we do case insensitive to avoid full table scan on MTL_CATEGORIES_KFV
2441: update pon_item_prices_interface p
2442: set category_id = (select Nvl(MAX(MCK.category_id),-1)
2443: FROM (select category_id,
2444: FND_FLEX_EXT.get_segs('INV', 'MCAT', STRUCTURE_ID, CATEGORY_ID) CONCATENATED_SEGMENTS,
2445: ENABLED_FLAG,
2465: where batch_id = p_batch_id
2466: and category_name <> 'CAT_NON_ENTERED';
2467:
2468: -- For those which were not caught in the previous SQL
2469: update pon_item_prices_interface p
2470: set category_id = (select Nvl(MAX(MCK.category_id),-1)
2471: FROM (select category_id,
2472: FND_FLEX_EXT.get_segs('INV', 'MCAT', STRUCTURE_ID, CATEGORY_ID) CONCATENATED_SEGMENTS,
2473: ENABLED_FLAG,
2497: -- Because we do case insensitive validation for category_name,
2498: -- we need to update all valid user entered category names to the
2499: -- actual case sensitive value
2500: /*
2501: update pon_item_prices_interface p
2502: set category_name = (select Nvl(MAX(category_name),p.category_name)
2503: from icx_por_categories_tl i
2504: where i.rt_category_id = p.category_id
2505: and type=2 and i.language= p_language)
2506: where batch_id = p_batch_id
2507: and category_name <> 'CAT_NON_ENTERED'
2508: AND category_id <> -1;
2509: */
2510: update pon_item_prices_interface p
2511: set category_name = (select Nvl(MAX(FND_FLEX_EXT.get_segs('INV', 'MCAT', i.STRUCTURE_ID, i.CATEGORY_ID)),p.category_name)
2512: from mtl_categories_kfv i
2513: where i.category_id = p.category_id)
2514: where batch_id = p_batch_id
2526: select interface_type,
2527: fnd_message.get_string('PON','PON_AUCTS_CATEGORY'),
2528: category_name,
2529: decode(purchase_basis,'TEMP LABOR','PON_INVALID_TEMP_LABOR_CAT','PON_CATEGORY_ID_NOT_FOUND'),
2530: 'PON_ITEM_PRICES_INTERFACE',
2531: batch_id,
2532: interface_line_id
2533: from pon_item_prices_interface
2534: where category_id = -1
2529: decode(purchase_basis,'TEMP LABOR','PON_INVALID_TEMP_LABOR_CAT','PON_CATEGORY_ID_NOT_FOUND'),
2530: 'PON_ITEM_PRICES_INTERFACE',
2531: batch_id,
2532: interface_line_id
2533: from pon_item_prices_interface
2534: where category_id = -1
2535: AND batch_id = p_batch_id
2536: AND category_name <> 'CAT_NON_ENTERED';
2537:
2548: select interface_type,
2549: fnd_message.get_string('PON','PON_AUCTS_CATEGORY'),
2550: category_name,
2551: 'PON_AUCTS_INVALID_INV_CAT',
2552: 'PON_ITEM_PRICES_INTERFACE',
2553: batch_id,
2554: interface_line_id
2555: from pon_item_prices_interface p1
2556: where category_id <> -1 and
2551: 'PON_AUCTS_INVALID_INV_CAT',
2552: 'PON_ITEM_PRICES_INTERFACE',
2553: batch_id,
2554: interface_line_id
2555: from pon_item_prices_interface p1
2556: where category_id <> -1 and
2557: batch_id = p_batch_id and
2558: purchase_basis <> 'TEMP LABOR' and
2559: order_type_lookup_code <> 'FIXED PRICE' and
2578: -- default shopping category (ip category) using purchasing-iP category mappings
2579: -- when shopping category is missing...only for new lines since shopping category
2580: -- is optional
2581: IF (NVL(l_progress_payment_type,'NONE') = 'NONE') THEN
2582: update pon_item_prices_interface p1
2583: set ip_category_name = (select category_name
2584: from icx_cat_categories_v
2585: where rt_category_id = decode(pon_auction_pkg.get_mapped_ip_category(p1.category_id), -2, null, pon_auction_pkg.get_mapped_ip_category(p1.category_id)) and
2586: language = p_language)
2600: interface_line_id)
2601: select interface_type,
2602: fnd_message.get_string('PON','PON_SHOPPING_CAT'),
2603: 'PON_SHOP_CAT_NOT_VALID',
2604: 'PON_ITEM_PRICES_INTERFACE',
2605: batch_id,
2606: interface_line_id
2607: from pon_item_prices_interface p1
2608: where p1.batch_id = p_batch_id and
2603: 'PON_SHOP_CAT_NOT_VALID',
2604: 'PON_ITEM_PRICES_INTERFACE',
2605: batch_id,
2606: interface_line_id
2607: from pon_item_prices_interface p1
2608: where p1.batch_id = p_batch_id and
2609: p1.ip_category_name is not null and
2610: p1.ip_category_name <> 'IP_CAT_NONE_ENTERED' and
2611: not exists (select null
2615: AND Nvl(clm_info_flag,'N') <> 'Y';
2616:
2617: -- set ip category name to null if ip cateogry is invalid
2618:
2619: update pon_item_prices_interface p1
2620: set ip_category_name = null
2621: where p1.batch_id = p_batch_id and
2622: p1.ip_category_name is not null and
2623: p1.ip_category_name <> 'IP_CAT_NONE_ENTERED' and
2626: where icx.category_name = p1.ip_category_name and
2627: icx.language = p_language);
2628:
2629: -- set ip category id
2630: update pon_item_prices_interface p1
2631: set ip_category_id = (select rt_category_id
2632: from icx_cat_categories_v icx
2633: where icx.category_name = p1.ip_category_name and
2634: language = p_language and rownum=1 )
2650: interface_line_id)
2651: select interface_type,
2652: fnd_message.get_string('PON','PON_AUCTS_ITEM_DESC'),
2653: 'PON_FIELD_MUST_BE_ENTERED',
2654: 'PON_ITEM_PRICES_INTERFACE',
2655: batch_id,
2656: interface_line_id
2657: from pon_item_prices_interface
2658: where item_description = 'ITEM_NONE_ENTERED'
2653: 'PON_FIELD_MUST_BE_ENTERED',
2654: 'PON_ITEM_PRICES_INTERFACE',
2655: batch_id,
2656: interface_line_id
2657: from pon_item_prices_interface
2658: where item_description = 'ITEM_NONE_ENTERED'
2659: AND batch_id = p_batch_id
2660: and nvl(purchase_basis,'NULL') <> 'TEMP LABOR';
2661:
2658: where item_description = 'ITEM_NONE_ENTERED'
2659: AND batch_id = p_batch_id
2660: and nvl(purchase_basis,'NULL') <> 'TEMP LABOR';
2661:
2662: update pon_item_prices_interface p1
2663: set item_description = null
2664: where batch_id = p_batch_id and
2665: item_description = 'ITEM_NONE_ENTERED';
2666:
2679: interface_line_id)
2680: select interface_type,
2681: fnd_message.get_string('PON','PON_ORDER_UNIT_H'),
2682: 'PON_FIELD_MUST_BE_ENTERED',
2683: 'PON_ITEM_PRICES_INTERFACE',
2684: batch_id,
2685: interface_line_id
2686: from pon_item_prices_interface
2687: where ((unit_of_measure = 'UOM_NONE_ENTERED') or (unit_of_measure is null))
2682: 'PON_FIELD_MUST_BE_ENTERED',
2683: 'PON_ITEM_PRICES_INTERFACE',
2684: batch_id,
2685: interface_line_id
2686: from pon_item_prices_interface
2687: where ((unit_of_measure = 'UOM_NONE_ENTERED') or (unit_of_measure is null))
2688: AND nvl(price_and_quantity_apply, 'Y') = 'Y'
2689: AND order_type_lookup_code <> 'FIXED PRICE'
2690: AND batch_id = p_batch_id
2694: -- Unit of Measure must be valid in mtl_units_of_measure_tl
2695: --
2696: -- Some modifications to avoid full table scan and more imp
2697: -- to incorporate the new demand in bug 2319969
2698: update pon_item_prices_interface p
2699: set uom_code = (select nvl(max(uom_code),'XXX') from
2700: mtl_units_of_measure_tl m
2701: where language = p_language
2702: and unit_of_measure_tl = p.unit_of_measure
2707: uom_class = FND_PROFILE.VALUE('PO_RATE_UOM_CLASS')))))
2708: where batch_id = p_batch_id
2709: and unit_of_measure <> 'UOM_NONE_ENTERED';
2710: --
2711: update pon_item_prices_interface p
2712: set uom_code = (select nvl(max(uom_code),'XXX') from
2713: mtl_units_of_measure_tl m
2714: where language = p_language
2715: and upper(unit_of_measure_tl) = upper(p.unit_of_measure)
2721: where batch_id = p_batch_id
2722: and unit_of_measure <> 'UOM_NONE_ENTERED'
2723: and uom_code = 'XXX';
2724: --
2725: update pon_item_prices_interface p
2726: set uom_code = (select nvl(max(uom_code),'XXX') from
2727: mtl_units_of_measure_tl m
2728: where language = p_language
2729: and uom_code = p.unit_of_measure
2735: where batch_id = p_batch_id
2736: and unit_of_measure <> 'UOM_NONE_ENTERED'
2737: and uom_code = 'XXX';
2738: --
2739: update pon_item_prices_interface p
2740: set uom_code = (select nvl(max(uom_code),'XXX') from
2741: mtl_units_of_measure_tl m
2742: where language = p_language
2743: and upper(uom_code) = upper(p.unit_of_measure)
2754:
2755: -- Because we do case insensitive validation for uom_code,
2756: -- we need to update all valid user entered unit of measures to the
2757: -- actual case sensitive value
2758: update pon_item_prices_interface p
2759: set unit_of_measure = (select nvl(max(unit_of_measure_tl),p.unit_of_measure) from
2760: mtl_units_of_measure_tl m
2761: where language = p_language
2762: and uom_code = p.uom_code)
2775: select interface_type,
2776: fnd_message.get_string('PON','PON_AUCTION_UOM'),
2777: unit_of_measure,
2778: decode(purchase_basis,'TEMP LABOR','PON_INVALID_TEMP_LABOR_UOM','PON_INVALID_UOM'),
2779: 'PON_ITEM_PRICES_INTERFACE',
2780: batch_id,
2781: interface_line_id
2782: from pon_item_prices_interface p
2783: where unit_of_measure <> 'UOM_NONE_ENTERED'
2778: decode(purchase_basis,'TEMP LABOR','PON_INVALID_TEMP_LABOR_UOM','PON_INVALID_UOM'),
2779: 'PON_ITEM_PRICES_INTERFACE',
2780: batch_id,
2781: interface_line_id
2782: from pon_item_prices_interface p
2783: where unit_of_measure <> 'UOM_NONE_ENTERED'
2784: AND batch_id = p_batch_id
2785: AND uom_code = 'XXX'
2786: AND nvl(price_and_quantity_apply, 'Y') = 'Y';
2798: select interface_type,
2799: fnd_message.get_string('PON','PON_AUCTION_UOM'),
2800: unit_of_measure,
2801: 'PON_AUCTS_INVALID_INV_UOM',
2802: 'PON_ITEM_PRICES_INTERFACE',
2803: batch_id,
2804: interface_line_id
2805: from pon_item_prices_interface p1
2806: where batch_id = p_batch_id and
2801: 'PON_AUCTS_INVALID_INV_UOM',
2802: 'PON_ITEM_PRICES_INTERFACE',
2803: batch_id,
2804: interface_line_id
2805: from pon_item_prices_interface p1
2806: where batch_id = p_batch_id and
2807: purchase_basis <> 'TEMP LABOR' and
2808: item_number is not null and
2809: uom_code not in (select uom_code
2814:
2815: -- Defaulting of Unit of Measure still occurs for lines where quantity and price don't apply
2816: -- For these lines, we will reset the unit of measure fields
2817: IF (is_valid_rule(p_doctype_Id, 'NO_PRICE_QUANTITY_ITEMS')) THEN
2818: update pon_item_prices_interface p1
2819: set unit_of_measure = null,
2820: uom_code = null
2821: where batch_id = p_batch_id and
2822: nvl(price_and_quantity_apply, 'Y') = 'N';
2839: interface_line_id)
2840: select interface_type,
2841: fnd_message.get_string('PON','PON_AUCTS_QUANTITY'),
2842: 'PON_FIELD_MUST_BE_ENTERED',
2843: 'PON_ITEM_PRICES_INTERFACE',
2844: batch_id,
2845: interface_line_id
2846: from pon_item_prices_interface
2847: where quantity is null
2842: 'PON_FIELD_MUST_BE_ENTERED',
2843: 'PON_ITEM_PRICES_INTERFACE',
2844: batch_id,
2845: interface_line_id
2846: from pon_item_prices_interface
2847: where quantity is null
2848: and nvl(price_and_quantity_apply, 'Y') = 'Y'
2849: and batch_id = p_batch_id
2850: and purchase_basis <> 'TEMP LABOR'
2872: quantity,
2873: 'PON_AUC_QUAN_FIXED_AMT',
2874: 'LINENUM',
2875: interface_line_id,
2876: 'PON_ITEM_PRICES_INTERFACE',
2877: batch_id,
2878: interface_line_id
2879: FROM pon_item_prices_interface
2880: WHERE
2875: interface_line_id,
2876: 'PON_ITEM_PRICES_INTERFACE',
2877: batch_id,
2878: interface_line_id
2879: FROM pon_item_prices_interface
2880: WHERE
2881: quantity IS NULL
2882: AND order_type_lookup_code <> 'FIXED PRICE'
2883: AND order_type_lookup_code <> 'AMOUNT'
2887: FROM
2888: pon_auc_price_elements_int pfs,
2889: fnd_lookup_values lookups
2890: WHERE
2891: pfs.batch_id = pon_item_prices_interface.batch_id
2892: AND pfs.auction_header_id = pon_item_prices_interface.auction_header_id
2893: AND pfs.interface_line_id = pon_item_prices_interface.interface_line_id
2894: AND lookups.lookup_type = 'PON_PRICING_BASIS'
2895: AND lookups.lookup_code = 'FIXED_AMOUNT'
2888: pon_auc_price_elements_int pfs,
2889: fnd_lookup_values lookups
2890: WHERE
2891: pfs.batch_id = pon_item_prices_interface.batch_id
2892: AND pfs.auction_header_id = pon_item_prices_interface.auction_header_id
2893: AND pfs.interface_line_id = pon_item_prices_interface.interface_line_id
2894: AND lookups.lookup_type = 'PON_PRICING_BASIS'
2895: AND lookups.lookup_code = 'FIXED_AMOUNT'
2896: AND lookups.view_application_id = 0
2889: fnd_lookup_values lookups
2890: WHERE
2891: pfs.batch_id = pon_item_prices_interface.batch_id
2892: AND pfs.auction_header_id = pon_item_prices_interface.auction_header_id
2893: AND pfs.interface_line_id = pon_item_prices_interface.interface_line_id
2894: AND lookups.lookup_type = 'PON_PRICING_BASIS'
2895: AND lookups.lookup_code = 'FIXED_AMOUNT'
2896: AND lookups.view_application_id = 0
2897: AND lookups.security_group_id = 0
2915: select interface_type,
2916: decode(p_contract_type, 'BLANKET','PON_AUCTS_EST_QUANTITY', 'CONTRACT', 'PON_AUCTS_EST_QUANTITY', 'PON_AUCTS_QUANTITY'),
2917: quantity,
2918: 'PON_MUST_BE_POSITIVE_NUMBER',
2919: 'PON_ITEM_PRICES_INTERFACE',
2920: batch_id,
2921: interface_line_id
2922: from pon_item_prices_interface
2923: where quantity <= 0
2918: 'PON_MUST_BE_POSITIVE_NUMBER',
2919: 'PON_ITEM_PRICES_INTERFACE',
2920: batch_id,
2921: interface_line_id
2922: from pon_item_prices_interface
2923: where quantity <= 0
2924: AND order_type_lookup_code <> 'FIXED PRICE'
2925: AND nvl(price_and_quantity_apply, 'Y') = 'Y'
2926: AND batch_id = p_batch_id;
2946: select interface_type,
2947: '',
2948: '',
2949: 'PON_NEED_BY_DATE_POP_DATE',
2950: 'PON_ITEM_PRICES_INTERFACE',
2951: batch_id,
2952: interface_line_id
2953: from pon_item_prices_interface
2954: where ( (nvl(l_is_fed,'N')='Y') and
2949: 'PON_NEED_BY_DATE_POP_DATE',
2950: 'PON_ITEM_PRICES_INTERFACE',
2951: batch_id,
2952: interface_line_id
2953: from pon_item_prices_interface
2954: where ( (nvl(l_is_fed,'N')='Y') and
2955: (p_contract_type = 'STANDARD') and
2956: ( (clm_need_by_date is not null and need_by_start_date is not null and need_by_date is not null)
2957: /* Bug : 13700330 : Need By or POP dates are not mandatory for non-inventory lines. */
2978: select interface_type,
2979: '',
2980: '',
2981: 'PON_POP_START_DATE_REQD',
2982: 'PON_ITEM_PRICES_INTERFACE',
2983: batch_id,
2984: interface_line_id
2985: from pon_item_prices_interface
2986: where ( (nvl(l_is_fed,'N')='Y') and
2981: 'PON_POP_START_DATE_REQD',
2982: 'PON_ITEM_PRICES_INTERFACE',
2983: batch_id,
2984: interface_line_id
2985: from pon_item_prices_interface
2986: where ( (nvl(l_is_fed,'N')='Y') and
2987: (p_contract_type = 'STANDARD') and
2988: ( (clm_need_by_date is null and (need_by_start_date is null and need_by_date is not null)))
2989: )
3004: select interface_type,
3005: '',
3006: '',
3007: 'PON_POP_END_DATE_REQUIRED',
3008: 'PON_ITEM_PRICES_INTERFACE',
3009: batch_id,
3010: interface_line_id
3011: from pon_item_prices_interface
3012: where ( (nvl(l_is_fed,'N')='Y') and
3007: 'PON_POP_END_DATE_REQUIRED',
3008: 'PON_ITEM_PRICES_INTERFACE',
3009: batch_id,
3010: interface_line_id
3011: from pon_item_prices_interface
3012: where ( (nvl(l_is_fed,'N')='Y') and
3013: (p_contract_type = 'STANDARD') and
3014: ( (clm_need_by_date is null and (need_by_start_date is not null and need_by_date is null)))
3015: )
3028: -- CLM : For federal documents, display Period Of Performance Start Date
3029: decode(l_is_fed,'Y',fnd_message.get_string('PON','PON_CLM_PERIOD_PERF_START_DATE'),fnd_message.get_string('PON','PON_AUC_NEED_BY_FROM_DATE')),
3030: need_by_start_date,
3031: 'PON_DATE_MUST_BE_GT_TODAY',
3032: 'PON_ITEM_PRICES_INTERFACE',
3033: batch_id,
3034: interface_line_id
3035: from pon_item_prices_interface
3036: where need_by_start_date < sysdate
3031: 'PON_DATE_MUST_BE_GT_TODAY',
3032: 'PON_ITEM_PRICES_INTERFACE',
3033: batch_id,
3034: interface_line_id
3035: from pon_item_prices_interface
3036: where need_by_start_date < sysdate
3037: and batch_id = p_batch_id;
3038: --
3039: -- NEED_BY_TO_DATE
3049: -- CLM : For federal documents, display Period Of Performance End Date
3050: decode(l_is_fed,'Y',fnd_message.get_string('PON','PON_CLM_PERIOD_PERF_END_DATE'),fnd_message.get_string('PON','PON_AUC_NEED_BY_TO_DATE')),
3051: need_by_date,
3052: 'PON_DATE_MUST_BE_GT_TODAY',
3053: 'PON_ITEM_PRICES_INTERFACE',
3054: batch_id,
3055: interface_line_id
3056: from pon_item_prices_interface
3057: where need_by_date < sysdate
3052: 'PON_DATE_MUST_BE_GT_TODAY',
3053: 'PON_ITEM_PRICES_INTERFACE',
3054: batch_id,
3055: interface_line_id
3056: from pon_item_prices_interface
3057: where need_by_date < sysdate
3058: and batch_id = p_batch_id;
3059:
3060:
3070: select interface_type,
3071: fnd_message.get_string('PON','PON_AUCTS_NEED_BY_DATE'),
3072: clm_need_by_date,
3073: 'PON_DATE_MUST_BE_GT_TODAY',
3074: 'PON_ITEM_PRICES_INTERFACE',
3075: batch_id,
3076: interface_line_id
3077: from pon_item_prices_interface
3078: where clm_need_by_date < sysdate
3073: 'PON_DATE_MUST_BE_GT_TODAY',
3074: 'PON_ITEM_PRICES_INTERFACE',
3075: batch_id,
3076: interface_line_id
3077: from pon_item_prices_interface
3078: where clm_need_by_date < sysdate
3079: and batch_id = p_batch_id;
3080: --
3081: -- NEED_BY_TO_DATE vs NEED_BY_FROM_DATE
3092: decode(l_is_fed,'Y',fnd_message.get_string('PON','PON_CLM_PERIOD_PERF_END_DATE'),fnd_message.get_string('PON','PON_AUC_NEED_BY_TO_DATE')),
3093: need_by_date,
3094: -- CLM : For federal display Period Of Perf dates message
3095: decode(l_is_fed,'Y','PON_AUC_IMPORT_POP_BEFORE_FROM','PON_AUC_NEEDBY_BEFORE_FROM_SS'),
3096: 'PON_ITEM_PRICES_INTERFACE',
3097: batch_id,
3098: interface_line_id
3099: from pon_item_prices_interface
3100: --Bug : 13700330 : For clm, POP Start Date should be less than End Date
3095: decode(l_is_fed,'Y','PON_AUC_IMPORT_POP_BEFORE_FROM','PON_AUC_NEEDBY_BEFORE_FROM_SS'),
3096: 'PON_ITEM_PRICES_INTERFACE',
3097: batch_id,
3098: interface_line_id
3099: from pon_item_prices_interface
3100: --Bug : 13700330 : For clm, POP Start Date should be less than End Date
3101: where ( (l_is_fed = 'Y' AND (clm_need_by_date IS NULL AND (need_by_date <= need_by_start_date)))
3102: OR (l_is_fed = 'N' AND need_by_date < need_by_start_date))
3103: and batch_id = p_batch_id;
3117: select ip.interface_type,
3118: fnd_message.get_string('PON','PON_AUCTS_NEEDBY'),
3119: null,
3120: 'PON_NEED_BY_DATE_REQ_SPD',
3121: 'PON_ITEM_PRICES_INTERFACE',
3122: ip.batch_id,
3123: ip.interface_line_id
3124: from pon_item_prices_interface ip
3125: where ip.batch_id = p_batch_id
3120: 'PON_NEED_BY_DATE_REQ_SPD',
3121: 'PON_ITEM_PRICES_INTERFACE',
3122: ip.batch_id,
3123: ip.interface_line_id
3124: from pon_item_prices_interface ip
3125: where ip.batch_id = p_batch_id
3126: and ( (l_is_fed = 'Y' AND ip.clm_need_by_date IS null)
3127: OR (l_is_fed = 'N' AND (ip.need_by_date is null AND ip.need_by_start_date is NULL)))
3128: and ip.item_id is not null
3139: END IF;
3140:
3141: -- CLM: Event Based Delivery Management
3142:
3143: update pon_item_prices_interface p
3144: set p.CLM_DELIVERY_EVENT_CODE = (SELECT lookup_code
3145: FROM fnd_lookup_values_vl
3146: WHERE lookup_type = 'CLM_DELIVERY_EVENT'
3147: AND meaning = p.CLM_DELIVERY_EVENT);
3157: select interface_type,
3158: fnd_message.get_string('PON','PON_CLM_DEL_EVENT_CODE'),
3159: CLM_DELIVERY_EVENT,
3160: 'PON_INV_DEL_EVENT_CODE',
3161: 'PON_ITEM_PRICES_INTERFACE',
3162: batch_id,
3163: interface_line_id
3164: from pon_item_prices_interface
3165: where CLM_DELIVERY_EVENT_CODE IS NULL
3160: 'PON_INV_DEL_EVENT_CODE',
3161: 'PON_ITEM_PRICES_INTERFACE',
3162: batch_id,
3163: interface_line_id
3164: from pon_item_prices_interface
3165: where CLM_DELIVERY_EVENT_CODE IS NULL
3166: and CLM_DELIVERY_EVENT IS NOT NULL;
3167:
3168: update pon_item_prices_interface p
3164: from pon_item_prices_interface
3165: where CLM_DELIVERY_EVENT_CODE IS NULL
3166: and CLM_DELIVERY_EVENT IS NOT NULL;
3167:
3168: update pon_item_prices_interface p
3169: set p.CLM_DELIVERY_PERIOD_UOM_CODE = (SELECT lookup_code
3170: FROM fnd_lookup_values_vl
3171: WHERE lookup_type = 'CLM_PERIOD'
3172: AND meaning = p.CLM_DELIVERY_PERIOD_UOM);
3182: select interface_type,
3183: fnd_message.get_string('PON','PON_CLM_DEL_PERIOD_UOM'),
3184: CLM_DELIVERY_PERIOD_UOM,
3185: 'PON_INV_PERIOD_UOM_CODE',
3186: 'PON_ITEM_PRICES_INTERFACE',
3187: batch_id,
3188: interface_line_id
3189: from pon_item_prices_interface
3190: where CLM_DELIVERY_PERIOD_UOM_CODE IS NULL
3185: 'PON_INV_PERIOD_UOM_CODE',
3186: 'PON_ITEM_PRICES_INTERFACE',
3187: batch_id,
3188: interface_line_id
3189: from pon_item_prices_interface
3190: where CLM_DELIVERY_PERIOD_UOM_CODE IS NULL
3191: and CLM_DELIVERY_PERIOD_UOM IS NOT NULL;
3192:
3193: update pon_item_prices_interface p
3189: from pon_item_prices_interface
3190: where CLM_DELIVERY_PERIOD_UOM_CODE IS NULL
3191: and CLM_DELIVERY_PERIOD_UOM IS NOT NULL;
3192:
3193: update pon_item_prices_interface p
3194: set p.CLM_POP_DURATION_UOM_CODE = (SELECT lookup_code
3195: FROM fnd_lookup_values_vl
3196: WHERE lookup_type = 'CLM_PERIOD'
3197: AND meaning = p.CLM_POP_DURATION_UOM);
3207: select interface_type,
3208: fnd_message.get_string('PON','PON_CLM_POP_DUR_UOM'),
3209: CLM_POP_DURATION_UOM,
3210: 'PON_INV_POP_UOM_CODE',
3211: 'PON_ITEM_PRICES_INTERFACE',
3212: batch_id,
3213: interface_line_id
3214: from pon_item_prices_interface
3215: where CLM_POP_DURATION_UOM_CODE IS NULL
3210: 'PON_INV_POP_UOM_CODE',
3211: 'PON_ITEM_PRICES_INTERFACE',
3212: batch_id,
3213: interface_line_id
3214: from pon_item_prices_interface
3215: where CLM_POP_DURATION_UOM_CODE IS NULL
3216: and CLM_POP_DURATION_UOM IS NOT NULL;
3217:
3218: END IF;
3234: )
3235: select interface_type
3236: , fnd_message.get_string('PON','PON_AUCTS_SHIP_TO_LOC')
3237: , 'PON_FIELD_MUST_BE_ENTERED'
3238: , 'PON_ITEM_PRICES_INTERFACE'
3239: , batch_id
3240: , interface_line_id
3241: from pon_item_prices_interface
3242: where batch_id = p_batch_id
3237: , 'PON_FIELD_MUST_BE_ENTERED'
3238: , 'PON_ITEM_PRICES_INTERFACE'
3239: , batch_id
3240: , interface_line_id
3241: from pon_item_prices_interface
3242: where batch_id = p_batch_id
3243: AND ship_to_location = 'SHIP_NONE_ENTERED'
3244: and group_type <> 'GROUP'
3245: AND Nvl(clm_info_flag,'N') <> 'Y';
3245: AND Nvl(clm_info_flag,'N') <> 'Y';
3246: END IF;
3247: -- fph
3248: -- again to avoid some full table scan
3249: update pon_item_prices_interface p
3250: set ship_to_location_id = (select (nvl(max(location_id), -1))
3251: from po_ship_to_loc_org_v po_v
3252: where po_v.location_code = p.ship_to_location)
3253: where batch_id = p_batch_id
3252: where po_v.location_code = p.ship_to_location)
3253: where batch_id = p_batch_id
3254: and ship_to_location <> 'SHIP_NONE_ENTERED';
3255: --
3256: update pon_item_prices_interface p
3257: set ship_to_location_id = (select (nvl(max(location_id), -1))
3258: from po_ship_to_loc_org_v po_v
3259: where upper(po_v.location_code) = upper(p.ship_to_location))
3260: where batch_id = p_batch_id
3263: --
3264: -- Because we do case insensitive validation for ship_to_location,
3265: -- we need to update all valid user entered shipping locations to the
3266: -- actual case sensitive value fph
3267: update pon_item_prices_interface p
3268: set ship_to_location = (select (nvl(max(location_code), -1))
3269: from po_ship_to_loc_org_v po_v
3270: where po_v.location_id = p.ship_to_location_id)
3271: where batch_id = p_batch_id
3285: select interface_type,
3286: fnd_message.get_string('PON','PON_AUCTS_SHIP_TO_LOC'),
3287: ship_to_location,
3288: 'PON_CAT_INVALID_VALUE',
3289: 'PON_ITEM_PRICES_INTERFACE',
3290: batch_id,
3291: interface_line_id
3292: from pon_item_prices_interface
3293: where ship_to_location_id = -1
3288: 'PON_CAT_INVALID_VALUE',
3289: 'PON_ITEM_PRICES_INTERFACE',
3290: batch_id,
3291: interface_line_id
3292: from pon_item_prices_interface
3293: where ship_to_location_id = -1
3294: AND ship_to_location <> 'SHIP_NONE_ENTERED'
3295: AND batch_id = p_batch_id;
3296:
3311: select interface_type,
3312: fnd_message.get_string('PON','PON_AUCTS_TARGET_PRICE'),
3313: target_price,
3314: 'PON_MUST_BE_POSITIVE_NUMBER',
3315: 'PON_ITEM_PRICES_INTERFACE',
3316: batch_id,
3317: interface_line_id
3318: from pon_item_prices_interface
3319: where TARGET_PRICE <= 0
3314: 'PON_MUST_BE_POSITIVE_NUMBER',
3315: 'PON_ITEM_PRICES_INTERFACE',
3316: batch_id,
3317: interface_line_id
3318: from pon_item_prices_interface
3319: where TARGET_PRICE <= 0
3320: and nvl(price_and_quantity_apply, 'Y') = 'Y'
3321: and batch_id = p_batch_id;
3322: END IF;
3336: select interface_type,
3337: fnd_message.get_string('PON','PON_AUCTION_BID_START_PRICE'),
3338: bid_start_price,
3339: 'PON_MUST_BE_POSITIVE_NUMBER',
3340: 'PON_ITEM_PRICES_INTERFACE',
3341: batch_id,
3342: interface_line_id
3343: from pon_item_prices_interface
3344: where bid_start_price <= 0
3339: 'PON_MUST_BE_POSITIVE_NUMBER',
3340: 'PON_ITEM_PRICES_INTERFACE',
3341: batch_id,
3342: interface_line_id
3343: from pon_item_prices_interface
3344: where bid_start_price <= 0
3345: and batch_id = p_batch_id;
3346: END IF;
3347: --
3360: select interface_type,
3361: fnd_message.get_string('PON','PON_AUCTS_CURRENT_PRICE'),
3362: current_price,
3363: 'PON_MUST_BE_POSITIVE_NUMBER',
3364: 'PON_ITEM_PRICES_INTERFACE',
3365: batch_id,
3366: interface_line_id
3367: from pon_item_prices_interface
3368: where current_price <= 0
3363: 'PON_MUST_BE_POSITIVE_NUMBER',
3364: 'PON_ITEM_PRICES_INTERFACE',
3365: batch_id,
3366: interface_line_id
3367: from pon_item_prices_interface
3368: where current_price <= 0
3369: and nvl(price_and_quantity_apply, 'Y') = 'Y'
3370: and batch_id = p_batch_id;
3371: END IF;
3385: select interface_type,
3386: fnd_message.get_string('PON','PON_AUCTS_RESERVE_PRICE'),
3387: reserve_price,
3388: 'PON_MUST_BE_POSITIVE_NUMBER',
3389: 'PON_ITEM_PRICES_INTERFACE',
3390: batch_id,
3391: interface_line_id
3392: from pon_item_prices_interface
3393: where reserve_price <= 0 AND
3388: 'PON_MUST_BE_POSITIVE_NUMBER',
3389: 'PON_ITEM_PRICES_INTERFACE',
3390: batch_id,
3391: interface_line_id
3392: from pon_item_prices_interface
3393: where reserve_price <= 0 AND
3394: batch_id = p_batch_id;
3395: END IF;
3396: --
3420: select interface_type,
3421: fnd_message.get_string('PON','PON_AUCTION_BID_START_PRICE'),
3422: bid_start_price,
3423: 'PON_TARGET_GTR_BID_START',
3424: 'PON_ITEM_PRICES_INTERFACE',
3425: batch_id,
3426: interface_line_id
3427: from pon_item_prices_interface
3428: where bid_start_price <= target_price AND
3423: 'PON_TARGET_GTR_BID_START',
3424: 'PON_ITEM_PRICES_INTERFACE',
3425: batch_id,
3426: interface_line_id
3427: from pon_item_prices_interface
3428: where bid_start_price <= target_price AND
3429: batch_id = p_batch_id;
3430: END IF;
3431: --
3445: select interface_type,
3446: fnd_message.get_string('PON','PON_AUCTION_BID_START_PRICE'),
3447: bid_start_price,
3448: 'PON_AUCTS_START_LT_TARGET',
3449: 'PON_ITEM_PRICES_INTERFACE',
3450: batch_id,
3451: interface_line_id
3452: from pon_item_prices_interface
3453: where bid_start_price >= target_price AND
3448: 'PON_AUCTS_START_LT_TARGET',
3449: 'PON_ITEM_PRICES_INTERFACE',
3450: batch_id,
3451: interface_line_id
3452: from pon_item_prices_interface
3453: where bid_start_price >= target_price AND
3454: batch_id = p_batch_id;
3455: --
3456: -- bid start price < reserve price
3467: select interface_type,
3468: fnd_message.get_string('PON','PON_AUCTION_BID_START_PRICE'),
3469: bid_start_price,
3470: 'PON_AUCTS_START_LT_RESERVE',
3471: 'PON_ITEM_PRICES_INTERFACE',
3472: batch_id,
3473: interface_line_id
3474: from pon_item_prices_interface
3475: where reserve_price < bid_start_price AND
3470: 'PON_AUCTS_START_LT_RESERVE',
3471: 'PON_ITEM_PRICES_INTERFACE',
3472: batch_id,
3473: interface_line_id
3474: from pon_item_prices_interface
3475: where reserve_price < bid_start_price AND
3476: batch_id = p_batch_id;
3477: --
3478: -- reserve price < target price
3489: select interface_type,
3490: fnd_message.get_string('PON','PON_AUCTS_RESERVE_PRICE'),
3491: reserve_price,
3492: 'PON_AUCTS_RESERVE_LT_TARGET',
3493: 'PON_ITEM_PRICES_INTERFACE',
3494: batch_id,
3495: interface_line_id
3496: from pon_item_prices_interface
3497: where reserve_price > target_price AND
3492: 'PON_AUCTS_RESERVE_LT_TARGET',
3493: 'PON_ITEM_PRICES_INTERFACE',
3494: batch_id,
3495: interface_line_id
3496: from pon_item_prices_interface
3497: where reserve_price > target_price AND
3498: batch_id = p_batch_id;
3499: --
3500: END IF;
3514: select interface_type,
3515: fnd_message.get_string('PON','PON_ITEM_PRICE_TARGET_VALUE'),
3516: unit_target_price,
3517: 'PON_AUC_POSITIVE_OR_ZERO',
3518: 'PON_ITEM_PRICES_INTERFACE',
3519: batch_id,
3520: interface_line_id
3521: from pon_item_prices_interface
3522: where UNIT_TARGET_PRICE < 0
3517: 'PON_AUC_POSITIVE_OR_ZERO',
3518: 'PON_ITEM_PRICES_INTERFACE',
3519: batch_id,
3520: interface_line_id
3521: from pon_item_prices_interface
3522: where UNIT_TARGET_PRICE < 0
3523: and nvl(price_and_quantity_apply, 'Y') = 'Y'
3524: and batch_id = p_batch_id;
3525:
3533: interface_line_id)
3534: select interface_type,
3535: fnd_message.get_string('PON','PON_CLM_OPTION_FROM_DATE'),
3536: 'PON_FIELD_MUST_BE_ENTERED',
3537: 'PON_ITEM_PRICES_INTERFACE',
3538: batch_id,
3539: interface_line_id
3540: from pon_item_prices_interface
3541: where CLM_BASE_LINE_NUM IS NOT NULL
3536: 'PON_FIELD_MUST_BE_ENTERED',
3537: 'PON_ITEM_PRICES_INTERFACE',
3538: batch_id,
3539: interface_line_id
3540: from pon_item_prices_interface
3541: where CLM_BASE_LINE_NUM IS NOT NULL
3542: AND CLM_OPTION_FROM_DATE IS NULL
3543: and batch_id = p_batch_id;
3544:
3551: interface_line_id)
3552: select interface_type,
3553: fnd_message.get_string('PON','PON_CLM_OPTION_TO_DATE'),
3554: 'PON_FIELD_MUST_BE_ENTERED',
3555: 'PON_ITEM_PRICES_INTERFACE',
3556: batch_id,
3557: interface_line_id
3558: from pon_item_prices_interface
3559: where CLM_BASE_LINE_NUM IS NOT NULL
3554: 'PON_FIELD_MUST_BE_ENTERED',
3555: 'PON_ITEM_PRICES_INTERFACE',
3556: batch_id,
3557: interface_line_id
3558: from pon_item_prices_interface
3559: where CLM_BASE_LINE_NUM IS NOT NULL
3560: AND CLM_OPTION_TO_DATE IS NULL
3561: and batch_id = p_batch_id;
3562:
3571: select interface_type,
3572: fnd_message.get_string('PON','PON_CLM_OPTION_FROM_DATE'),
3573: CLM_OPTION_FROM_DATE,
3574: 'PON_CLM_OPT_FROMDATE_ERR',
3575: 'PON_ITEM_PRICES_INTERFACE',
3576: batch_id,
3577: interface_line_id
3578: from pon_item_prices_interface
3579: WHERE CLM_BASE_LINE_NUM IS NOT NULL
3574: 'PON_CLM_OPT_FROMDATE_ERR',
3575: 'PON_ITEM_PRICES_INTERFACE',
3576: batch_id,
3577: interface_line_id
3578: from pon_item_prices_interface
3579: WHERE CLM_BASE_LINE_NUM IS NOT NULL
3580: AND CLM_OPTION_FROM_DATE < sysdate
3581: and batch_id = p_batch_id;
3582: --
3591: select interface_type,
3592: fnd_message.get_string('PON','PON_CLM_OPTION_TO_DATE'),
3593: CLM_OPTION_TO_DATE,
3594: 'PON_CLM_OPT_TODATE_ERR',
3595: 'PON_ITEM_PRICES_INTERFACE',
3596: batch_id,
3597: interface_line_id
3598: from pon_item_prices_interface
3599: where CLM_BASE_LINE_NUM IS NOT NULL
3594: 'PON_CLM_OPT_TODATE_ERR',
3595: 'PON_ITEM_PRICES_INTERFACE',
3596: batch_id,
3597: interface_line_id
3598: from pon_item_prices_interface
3599: where CLM_BASE_LINE_NUM IS NOT NULL
3600: AND CLM_OPTION_TO_DATE < CLM_OPTION_FROM_DATE
3601: and batch_id = p_batch_id;
3602:
3621: (select interface_type,
3622: fnd_message.get_string('PON', 'PON_CLM_AMOUNT'),
3623: 'Slins',
3624: 'PON_CLM_AMOUNT_ERR',
3625: 'PON_ITEM_PRICES_INTERFACE',
3626: batch_id,
3627: interface_line_id
3628: from pon_item_prices_interface P1
3629: where batch_id = p_batch_id
3624: 'PON_CLM_AMOUNT_ERR',
3625: 'PON_ITEM_PRICES_INTERFACE',
3626: batch_id,
3627: interface_line_id
3628: from pon_item_prices_interface P1
3629: where batch_id = p_batch_id
3630: AND P1.CLM_AMOUNT IS NOT NULL
3631: AND P1.LINE_TYPE_ID NOT IN (SELECT LINE_TYPE_ID FROM po_line_types_b WHERE ORDER_TYPE_LOOKUP_CODE = 'AMOUNT')
3632: );
3643: (select interface_type,
3644: fnd_message.get_string('PON', 'PON_CLM_UNIT_PRICE'),
3645: 'Slins',
3646: 'PON_CLM_UNIT_PRICE_ERR',
3647: 'PON_ITEM_PRICES_INTERFACE',
3648: batch_id,
3649: interface_line_id
3650: from pon_item_prices_interface P1
3651: where batch_id = p_batch_id
3646: 'PON_CLM_UNIT_PRICE_ERR',
3647: 'PON_ITEM_PRICES_INTERFACE',
3648: batch_id,
3649: interface_line_id
3650: from pon_item_prices_interface P1
3651: where batch_id = p_batch_id
3652: AND P1.CLM_UNIT_PRICE IS NOT NULL
3653: AND P1.LINE_TYPE_ID NOT IN (SELECT LINE_TYPE_ID FROM po_line_types_b WHERE ORDER_TYPE_LOOKUP_CODE = 'QUANTITY')
3654: );
3668: (select interface_type,
3669: fnd_message.get_string('PON', 'PON_CLM_CONTRACT_TYPE'),
3670: 'Slins',
3671: 'PON_CLM_CONTRACT_TYPE_ERR',
3672: 'PON_ITEM_PRICES_INTERFACE',
3673: batch_id,
3674: interface_line_id
3675: from pon_item_prices_interface
3676: where batch_id = p_batch_id
3671: 'PON_CLM_CONTRACT_TYPE_ERR',
3672: 'PON_ITEM_PRICES_INTERFACE',
3673: batch_id,
3674: interface_line_id
3675: from pon_item_prices_interface
3676: where batch_id = p_batch_id
3677: AND CLM_CONTRACT_TYPE IS NOT NULL
3678: AND CLM_CONTRACT_TYPE NOT IN
3679: (SELECT LOOKUP_CODE
3699: (select interface_type,
3700: fnd_message.get_string('PON', 'PON_CLM_COST_CONSTRAINT'),
3701: 'Slins',
3702: 'PON_CLM_COST_CONSTRAINT_ERR',
3703: 'PON_ITEM_PRICES_INTERFACE',
3704: batch_id,
3705: interface_line_id
3706: from pon_item_prices_interface
3707: where batch_id = p_batch_id
3702: 'PON_CLM_COST_CONSTRAINT_ERR',
3703: 'PON_ITEM_PRICES_INTERFACE',
3704: batch_id,
3705: interface_line_id
3706: from pon_item_prices_interface
3707: where batch_id = p_batch_id
3708: AND CLM_COST_CONSTRAINT IS NOT NULL
3709: AND CLM_COST_CONSTRAINT NOT IN
3710: (SELECT LOOKUP_CODE
3730: (select interface_type,
3731: fnd_message.get_string('PON', 'PON_CLM_BASE_LINE_NUM'),
3732: 'Slins',
3733: 'PON_CLM_BASE_LINE_NUM_ERR',
3734: 'PON_ITEM_PRICES_INTERFACE',
3735: batch_id,
3736: interface_line_id
3737: from pon_item_prices_interface P1
3738: where batch_id = p_batch_id
3733: 'PON_CLM_BASE_LINE_NUM_ERR',
3734: 'PON_ITEM_PRICES_INTERFACE',
3735: batch_id,
3736: interface_line_id
3737: from pon_item_prices_interface P1
3738: where batch_id = p_batch_id
3739: AND P1.CLM_BASE_LINE_NUM IS NOT NULL
3740: --bug 16567154
3741: --Modifying the condition for option lines without base lines spreadsheet import
3741: --Modifying the condition for option lines without base lines spreadsheet import
3742: AND (P1.CLM_BASE_LINE_NUM<>-9999)
3743: AND P1.CLM_BASE_LINE_NUM NOT IN
3744: (SELECT P2.AUCTION_LINE_NUMBER from
3745: pon_item_prices_interface P2
3746: where auction_header_id = l_auction_header_id )
3747: );
3748:
3749:
3761: (select interface_type,
3762: fnd_message.get_string('PON', 'PON_CLM_OPTION_INDICATOR'),
3763: 'Slins',
3764: 'PON_CLM_OPTION_INDICATOR_ERR',
3765: 'PON_ITEM_PRICES_INTERFACE',
3766: batch_id,
3767: interface_line_id
3768: from pon_item_prices_interface P1
3769: where batch_id = p_batch_id
3764: 'PON_CLM_OPTION_INDICATOR_ERR',
3765: 'PON_ITEM_PRICES_INTERFACE',
3766: batch_id,
3767: interface_line_id
3768: from pon_item_prices_interface P1
3769: where batch_id = p_batch_id
3770: AND P1.CLM_OPTION_INDICATOR = 'Y' AND
3771: (P1.CLM_OPTION_FROM_DATE IS NULL OR P1.CLM_OPTION_TO_DATE IS NULL )
3772: );
3803: select interface_type,
3804: fnd_message.get_string('PON','PON_AGREEMENT_RELEASE_QUANTITY') || ',' || fnd_message.get_string('PON','PON_AUCTS_PB_RESPONSE'),
3805: null,
3806: 'PON_AUCTS_PB_FP_LINE_ERR',
3807: 'PON_ITEM_PRICES_INTERFACE',
3808: batch_id,
3809: interface_line_id
3810: from pon_item_prices_interface
3811: where ORDER_TYPE_LOOKUP_CODE IN ( 'AMOUNT','FIXED PRICE')
3806: 'PON_AUCTS_PB_FP_LINE_ERR',
3807: 'PON_ITEM_PRICES_INTERFACE',
3808: batch_id,
3809: interface_line_id
3810: from pon_item_prices_interface
3811: where ORDER_TYPE_LOOKUP_CODE IN ( 'AMOUNT','FIXED PRICE')
3812: AND ( price_break_type IS NOT NULL OR price_break_neg_flag IS NOT NULL )
3813: AND Nvl(clm_info_flag,'N') <> 'Y'
3814: AND group_type <> 'GROUP'
3814: AND group_type <> 'GROUP'
3815: and batch_id = p_batch_id;
3816:
3817: -- Update default PB setting for amt based and fixed price line types
3818: UPDATE pon_item_prices_interface p1
3819: SET price_break_type = l_price_break_type,price_break_neg_flag = l_price_break_neg_flag
3820: WHERE ORDER_TYPE_LOOKUP_CODE IN ( 'AMOUNT','FIXED PRICE')
3821: AND Nvl(clm_info_flag,'N') <> 'Y'
3822: AND group_type <> 'GROUP'
3821: AND Nvl(clm_info_flag,'N') <> 'Y'
3822: AND group_type <> 'GROUP'
3823: and batch_id = p_batch_id;*/
3824:
3825: UPDATE pon_item_prices_interface p1
3826: SET price_break_type = null,price_break_neg_flag = null
3827: WHERE ( ORDER_TYPE_LOOKUP_CODE IN ( 'AMOUNT','FIXED PRICE')
3828: OR Nvl(clm_info_flag,'N') = 'Y'
3829: OR group_type = 'GROUP' )
3830: AND batch_id = p_batch_id;
3831:
3832:
3833: -- update the pb settings
3834: UPDATE pon_item_prices_interface p1
3835: SET p1.price_break_type =
3836: (SELECT Decode(Nvl(p2.price_break_type,'NULL'),'NULL',l_price_break_type,Decode(p2.price_break_neg_flag,l_none,'NONE',Decode(p2.price_break_type,l_cumulative,'CUMULATIVE',Decode(p2.price_break_type,l_noncumulative,'NON-CUMULATIVE',null))))
3837: FROM pon_item_prices_interface p2
3838: WHERE p2.INTERFACE_LINE_ID = p1.INTERFACE_LINE_ID
3833: -- update the pb settings
3834: UPDATE pon_item_prices_interface p1
3835: SET p1.price_break_type =
3836: (SELECT Decode(Nvl(p2.price_break_type,'NULL'),'NULL',l_price_break_type,Decode(p2.price_break_neg_flag,l_none,'NONE',Decode(p2.price_break_type,l_cumulative,'CUMULATIVE',Decode(p2.price_break_type,l_noncumulative,'NON-CUMULATIVE',null))))
3837: FROM pon_item_prices_interface p2
3838: WHERE p2.INTERFACE_LINE_ID = p1.INTERFACE_LINE_ID
3839: AND p2.batch_id = p_batch_id
3840: ),
3841: p1.price_break_neg_flag =
3839: AND p2.batch_id = p_batch_id
3840: ),
3841: p1.price_break_neg_flag =
3842: (SELECT Decode(Nvl(p2.price_break_neg_flag,'NULL'),'NULL',l_price_break_neg_flag,Decode(p2.price_break_neg_flag,l_none,l_price_break_neg_flag,Decode(p2.price_break_neg_flag,l_required,'N',Decode(p2.price_break_neg_flag,l_optional,'Y',null))))
3843: FROM pon_item_prices_interface p2
3844: WHERE p2.INTERFACE_LINE_ID = p1.INTERFACE_LINE_ID
3845: AND p2.batch_id = p_batch_id
3846: )
3847:
3865: select interface_type,
3866: fnd_message.get_string('PON','PON_AGREEMENT_RELEASE_QUANTITY') || ',' || fnd_message.get_string('PON','PON_AUCTS_PB_RESPONSE'),
3867: null,
3868: 'PON_INVALID_PB_SETTING',
3869: 'PON_ITEM_PRICES_INTERFACE',
3870: batch_id,
3871: interface_line_id
3872: from pon_item_prices_interface
3873: where ( price_break_type IS NULL OR price_break_neg_flag IS NULL)
3868: 'PON_INVALID_PB_SETTING',
3869: 'PON_ITEM_PRICES_INTERFACE',
3870: batch_id,
3871: interface_line_id
3872: from pon_item_prices_interface
3873: where ( price_break_type IS NULL OR price_break_neg_flag IS NULL)
3874: AND ORDER_TYPE_LOOKUP_CODE NOT IN( 'AMOUNT','FIXED PRICE')
3875: AND Nvl(clm_info_flag,'N') <> 'Y'
3876: AND group_type <> 'GROUP'
3888: select interface_type,
3889: fnd_message.get_string('PON','PON_AUCTS_PB_RESPONSE'),
3890: null,
3891: 'PON_AUC_PB_NOT_EMPTY',
3892: 'PON_ITEM_PRICES_INTERFACE',
3893: batch_id,
3894: interface_line_id
3895: from pon_item_prices_interface p1
3896: where price_break_type = 'NONE'
3891: 'PON_AUC_PB_NOT_EMPTY',
3892: 'PON_ITEM_PRICES_INTERFACE',
3893: batch_id,
3894: interface_line_id
3895: from pon_item_prices_interface p1
3896: where price_break_type = 'NONE'
3897: AND Nvl((SELECT has_shipments_flag FROM pon_auction_item_prices_all WHERE line_number = p1.auction_line_number AND auction_header_id = l_auction_header_id),'N') = 'Y'
3898: AND ORDER_TYPE_LOOKUP_CODE NOT IN( 'AMOUNT','FIXED PRICE')
3899: AND Nvl(clm_info_flag,'N') <> 'Y'
3919: pon_auction_pkg.getMessage('PON_AGREEMENT_RELEASE_QUANTITY'),
3920: interface_type , --INTERFACE_TYPE
3921: 'PON_AUC_BAD_PBTYPE_GLOBAL' , -- ERROR_MESSAGE_NAME
3922: batch_id , -- BATCH_ID
3923: 'PON_ITEM_PRICES_INTERFACE' , -- ENTITY_TYPE
3924: auction_header_id , -- AUCTION_HEADER_ID
3925: 'LINENUMBER' , -- TOKEN1_NAME
3926: null, -- TOKEN1_VALUE
3927: interface_line_id
3924: auction_header_id , -- AUCTION_HEADER_ID
3925: 'LINENUMBER' , -- TOKEN1_NAME
3926: null, -- TOKEN1_VALUE
3927: interface_line_id
3928: FROM pon_item_prices_interface p1
3929: where price_break_type = 'CUMULATIVE'
3930: AND Nvl((SELECT global_agreement_flag FROM pon_auction_headers_all WHERE auction_header_id = l_auction_header_id),'N') = 'Y'
3931: AND ORDER_TYPE_LOOKUP_CODE NOT IN( 'AMOUNT','FIXED PRICE')
3932: AND Nvl(clm_info_flag,'N') <> 'Y'
3945: select interface_type,
3946: fnd_message.get_string('PON','PON_AUCTS_LINENUMBER'),
3947: null,
3948: 'PON_AUCTS_PB_TBL_LINE_ERR',
3949: 'PON_ITEM_PRICES_INTERFACE',
3950: batch_id,
3951: interface_line_id
3952: from pon_item_prices_interface p1
3953: where (PURCHASE_BASIS = 'TEMP LABOR' and order_type_lookup_code = 'RATE')
3948: 'PON_AUCTS_PB_TBL_LINE_ERR',
3949: 'PON_ITEM_PRICES_INTERFACE',
3950: batch_id,
3951: interface_line_id
3952: from pon_item_prices_interface p1
3953: where (PURCHASE_BASIS = 'TEMP LABOR' and order_type_lookup_code = 'RATE')
3954: AND price_break_type = 'CUMULATIVE'
3955: AND Nvl(clm_info_flag,'N') <> 'Y'
3956: AND group_type <> 'GROUP'
3958:
3959:
3960:
3961: ELSE
3962: UPDATE pon_item_prices_interface p1
3963: SET price_break_type = l_price_break_type,price_break_neg_flag = l_price_break_neg_flag
3964: WHERE batch_id = p_batch_id;
3965:
3966: END IF;