[Home] [Help]
736: SELECT pipi.interface_line_id, pipi.document_disp_line_number, pipi.auction_line_number,
737: pipi.project_id, pipi.project_task_id, pipi.project_expenditure_type,
738: pipi.project_exp_organization_id, pipi.project_expenditure_item_date,
739: pipi.auction_header_id, pipi.interface_type
740: FROM PON_ITEM_PRICES_INTERFACE pipi
741: WHERE pipi.batch_id=p_batch_id
742: AND pipi.project_id IS NOT NULL
743: AND pipi.project_task_id IS NOT NULL
744: AND pipi.project_expenditure_type IS NOT NULL
768: last_update_date, last_update_login -- 6
769: )
770: VALUES
771: (
772: interface_type, fnd_message.get_string('PON','PON_AUC_LINE_TYPE'), 'PON_ITEM_PRICES_INTERFACE', -- 1
773: line_type, 'PON_INVALID_STYLE_LINETYPE', batch_id, -- 2
774: interface_line_id, auction_header_id, s_line_number, -- 3
775: NULL, NULL, l_exp_date, -- 4
776: l_userid, SYSDATE, l_userid, -- 5
787: pipi.auction_line_number s_line_number,
788: plt.outside_operation_flag po_outside_operation_flag,
789: plt.line_type_id,
790: plt.line_type
791: FROM PON_ITEM_PRICES_INTERFACE pipi,
792: PO_LINE_TYPES plt
793: WHERE batch_id = p_batch_id
794: AND pipi.line_type_id = plt.line_type_id (+)
795: AND pipi.group_type NOT IN ('GROUP','LOT_LINE');
808: last_update_date, last_update_login -- 6
809: )
810: VALUES
811: (
812: interface_type, fnd_message.get_string('PON','PON_RETAINAGE_RATE'), 'PON_ITEM_PRICES_INTERFACE', -- 1
813: retainage_rate_percent, 'PON_RTNG_RATE_WRONG', batch_id, -- 2
814: interface_line_id, auction_header_id, s_line_number, -- 3
815: NULL, NULL, l_exp_date, -- 4
816: l_userid, SYSDATE, l_userid, -- 5
828: last_update_date, last_update_login -- 6
829: )
830: VALUES
831: (
832: interface_type, fnd_message.get_string('PON','PON_MAX_RETAINAGE_AMOUNT'), 'PON_ITEM_PRICES_INTERFACE', -- 1
833: max_retainage_amount , 'PON_MAX_RTNG_WRONG', batch_id, -- 2
834: interface_line_id, auction_header_id, s_line_number, -- 3
835: NULL, NULL, l_exp_date, -- 4
836: l_userid, SYSDATE, l_userid, -- 5
848: last_update_date, last_update_login -- 6
849: )
850: VALUES
851: (
852: interface_type, fnd_message.get_string('PON','PON_ADVANCE_AMOUNT_FLAG'), 'PON_ITEM_PRICES_INTERFACE', -- 1
853: advance_amount, 'PON_ADV_AMT_WRONG', batch_id, -- 2
854: interface_line_id, auction_header_id, s_line_number, -- 3
855: NULL, NULL, l_exp_date, -- 4
856: l_userid, SYSDATE, l_userid, -- 5
868: last_update_date, last_update_login -- 6
869: )
870: VALUES
871: (
872: interface_type, fnd_message.get_string('PON','PON_PROGRESS_PYMT_RATE'), 'PON_ITEM_PRICES_INTERFACE', -- 1
873: progress_pymt_rate_percent, 'PON_PROG_PYMT_RATE_WRONG', batch_id, -- 2
874: interface_line_id, auction_header_id, s_line_number, -- 3
875: NULL, NULL, l_exp_date, -- 4
876: l_userid, SYSDATE, l_userid, -- 5
887: last_update_date, last_update_login -- 6
888: )
889: VALUES
890: (
891: interface_type, fnd_message.get_string('PON','PON_RECOUPMENT_RATE'), 'PON_ITEM_PRICES_INTERFACE', -- 1
892: recoupment_rate_percent, 'PON_RECOUP_RATE_WRONG', batch_id, -- 2
893: interface_line_id, auction_header_id, s_line_number, -- 3
894: NULL, NULL, l_exp_date, -- 4
895: l_userid, SYSDATE, l_userid, -- 5
907: last_update_date, last_update_login -- 6
908: )
909: VALUES
910: (
911: interface_type, fnd_message.get_string('PON','PON_PROGRESS_PYMT_RATE'), 'PON_ITEM_PRICES_INTERFACE', -- 1
912: progress_pymt_rate_percent, 'PON_FIELD_MUST_BE_ENTERED', batch_id, -- 2
913: interface_line_id, auction_header_id, s_line_number, -- 3
914: NULL, NULL, l_exp_date, -- 4
915: l_userid, SYSDATE, l_userid, -- 5
930: last_update_date, last_update_login -- 6
931: )
932: VALUES
933: (
934: interface_type, fnd_message.get_string('PON','PON_RECOUPMENT_RATE'), 'PON_ITEM_PRICES_INTERFACE', -- 1
935: recoupment_rate_percent, 'PON_RECUP_NEEDED_WITH_PPRATE', batch_id, -- 2
936: interface_line_id, auction_header_id, s_line_number, -- 3
937: NULL, NULL, l_exp_date, -- 4
938: l_userid, SYSDATE, l_userid, -- 5
951: last_update_date, last_update_login -- 6
952: )
953: VALUES
954: (
955: interface_type, fnd_message.get_string('PON','PON_RECOUPMENT_RATE'), 'PON_ITEM_PRICES_INTERFACE', -- 1
956: recoupment_rate_percent, 'PON_RECUP_NEEDED_WITH_ADVAMT', batch_id, -- 2
957: interface_line_id, auction_header_id, s_line_number, -- 3
958: NULL, NULL, l_exp_date, -- 4
959: l_userid, SYSDATE, l_userid, -- 5
972: last_update_date, last_update_login -- 6
973: )
974: VALUES
975: (
976: interface_type, fnd_message.get_string('PON','PON_ADVANCE_AMOUNT_FLAG'), 'PON_ITEM_PRICES_INTERFACE', -- 1
977: advance_amount, 'PON_ADV_AMT_MORE', batch_id, -- 2
978: interface_line_id, auction_header_id, s_line_number, -- 3
979: NULL, NULL, l_exp_date, -- 4
980: l_userid, SYSDATE, l_userid, -- 5
994: last_update_date, last_update_login -- 6
995: )
996: VALUES
997: (
998: interface_type, fnd_message.get_string('PON','PON_RECOUPMENT_RATE'), 'PON_ITEM_PRICES_INTERFACE', -- 1
999: recoupment_rate_percent, 'PON_RECOUP_LESS_THAN_ADV', batch_id, -- 2
1000: interface_line_id, auction_header_id, s_line_number, -- 3
1001: NULL, NULL, l_exp_date, -- 4
1002: l_userid, SYSDATE, l_userid, -- 5
1018: last_update_date, last_update_login -- 6
1019: )
1020: VALUES
1021: (
1022: interface_type, fnd_message.get_string('PON','PON_AUC_LINE_TYPE'), 'PON_ITEM_PRICES_INTERFACE', -- 1
1023: line_type, 'PON_INVALID_STYLE_LINETYPE', batch_id, -- 2
1024: interface_line_id, auction_header_id, s_line_number, -- 3
1025: NULL, NULL, l_exp_date, -- 4
1026: l_userid, SYSDATE, l_userid, -- 5
1038: last_update_date, last_update_login -- 6
1039: )
1040: VALUES
1041: (
1042: interface_type, fnd_message.get_string('PON','PON_AUCTS_PROJECT'), 'PON_ITEM_PRICES_INTERFACE', -- 1
1043: project_number, 'PON_PROJ_NUM_INVALID', batch_id, -- 2
1044: interface_line_id, auction_header_id, s_line_number, -- 3
1045: NULL, NULL, l_exp_date, -- 4
1046: l_userid, SYSDATE, l_userid, -- 5
1063: last_update_date, last_update_login -- 6
1064: )
1065: VALUES
1066: (
1067: interface_type, fnd_message.get_string('PON','PON_AUCTS_TASK'), 'PON_ITEM_PRICES_INTERFACE', -- 1
1068: project_task_number, 'PON_PROJ_TASK_INVALID', batch_id, -- 2
1069: interface_line_id, auction_header_id, s_line_number, -- 3
1070: NULL, NULL, l_exp_date, -- 4
1071: l_userid, SYSDATE, l_userid, -- 5
1093: last_update_date, last_update_login -- 6
1094: )
1095: VALUES
1096: (
1097: interface_type, fnd_message.get_string('PON','PON_AUCTS_PROJECT_AWARD'), 'PON_ITEM_PRICES_INTERFACE', -- 1
1098: project_award_number, 'PON_PROJ_AWARD_INVALID', batch_id, -- 2
1099: interface_line_id, auction_header_id, s_line_number, -- 3
1100: NULL, NULL, l_exp_date, -- 4
1101: l_userid, SYSDATE, l_userid, -- 5
1114: last_update_date, last_update_login -- 6
1115: )
1116: VALUES
1117: (
1118: interface_type, fnd_message.get_string('PON','PON_AUCTS_EXPENDITUE_ORG'), 'PON_ITEM_PRICES_INTERFACE', -- 1
1119: project_exp_organization_name,'PON_PROJ_EXPORG_INVALID', batch_id, -- 2
1120: interface_line_id, auction_header_id, s_line_number, -- 3
1121: NULL, NULL, l_exp_date, -- 4
1122: l_userid, SYSDATE, l_userid, -- 5
1142: last_update_date, last_update_login -- 6
1143: )
1144: VALUES
1145: (
1146: interface_type, fnd_message.get_string('PON','PON_AUCTS_EXPENDITUE_TYPE'), 'PON_ITEM_PRICES_INTERFACE', -- 1
1147: s_project_exp_type, 'PON_PROJ_EXPTYPE_INVALID', batch_id, -- 2
1148: interface_line_id, auction_header_id, s_line_number, -- 3
1149: NULL, NULL, l_exp_date, -- 4
1150: l_userid, SYSDATE, l_userid, -- 5
1164: last_update_date, last_update_login -- 6
1165: )
1166: VALUES
1167: (
1168: interface_type, fnd_message.get_string('PON','PON_AUCTS_PROJECT_AWARD'), 'PON_ITEM_PRICES_INTERFACE', -- 1
1169: project_award_number, 'PON_PROJ_AWARD_NULL', batch_id, -- 2
1170: interface_line_id, auction_header_id, s_line_number, -- 3
1171: NULL, NULL, l_exp_date, -- 4
1172: l_userid, SYSDATE, l_userid, -- 5
1187: last_update_date, last_update_login -- 6
1188: )
1189: VALUES
1190: (
1191: interface_type, fnd_message.get_string('PON','PON_AUCTS_PROJECT'), 'PON_ITEM_PRICES_INTERFACE', -- 1
1192: NULL, 'PON_PROJ_INFO_INCOMPLETE', batch_id, -- 2
1193: interface_line_id, auction_header_id, s_line_number, -- 3
1194: NULL, NULL, l_exp_date, -- 4
1195: l_userid, SYSDATE, l_userid, -- 5
1215: last_update_date, last_update_login -- 6
1216: )
1217: VALUES
1218: (
1219: interface_type, fnd_message.get_string('PON','PON_DEFAULT_OWNER'), 'PON_ITEM_PRICES_INTERFACE', -- 1
1220: NULL, 'PON_LIN_OWNER_INVALID', batch_id, -- 2
1221: interface_line_id, auction_header_id, s_line_number, -- 3
1222: NULL, NULL, l_exp_date, -- 4
1223: l_userid, SYSDATE, l_userid, -- 5
1251: porg.organization_id porg_proj_exp_organization_id,
1252: plt.outside_operation_flag po_outside_operation_flag,
1253: plt.line_type_id,
1254: plt.line_type
1255: FROM PON_ITEM_PRICES_INTERFACE pipi,
1256: PA_PROJECTS_EXPEND_V pro,
1257: PA_ORGANIZATIONS_EXPEND_V porg,
1258: PO_LINE_TYPES plt
1259: WHERE batch_id = p_batch_id
1262: AND pipi.line_type_id = plt.line_type_id (+)
1263: AND pipi.group_type NOT IN ('GROUP','LOT_LINE');
1264:
1265: --Derive id columns and update the interface table
1266: UPDATE PON_ITEM_PRICES_INTERFACE pipi1
1267: SET (PROJECT_ID, PROJECT_TASK_ID, PROJECT_AWARD_ID, PROJECT_EXP_ORGANIZATION_ID) =
1268: (SELECT pro.project_id, task.task_id, award.award_id, porg.organization_id
1269: FROM PA_PROJECTS_ALL pro,
1270: PA_TASKS task,
1269: FROM PA_PROJECTS_ALL pro,
1270: PA_TASKS task,
1271: GMS_AWARDS_ALL award,
1272: HR_ALL_ORGANIZATION_UNITS porg,
1273: PON_ITEM_PRICES_INTERFACE pipi
1274: WHERE pipi.project_number = pro.segment1
1275: AND pipi.project_task_number = task.task_number
1276: AND pro.project_id = task.project_id
1277: AND pipi.project_award_number = award.award_number(+)
1279: AND pipi.batch_id = pipi1.batch_id
1280: AND pipi.interface_line_id = pipi1.interface_line_id)
1281: WHERE pipi1.batch_id = p_batch_id;
1282:
1283: UPDATE PON_ITEM_PRICES_INTERFACE pipi
1284: SET (WORK_APPROVER_USER_ID) =
1285: (SELECT fu.user_id
1286: FROM FND_USER fu
1287: WHERE pipi.work_approver_user_name = fu.user_name)
1304: p_payment_id => null,
1305: p_interface_line_id => l_proj_record.interface_line_id,
1306: p_payment_display_number => null,
1307: p_batch_id => p_batch_id,
1308: p_table_name => 'PON_ITEM_PRICES_INTERFACE',
1309: p_interface_type => l_proj_record.interface_type,
1310: p_entity_type => null,
1311: p_called_from => 'LINES_SP');
1312: END LOOP;
1343:
1344: select ah.po_style_id
1345: into x_po_style_id
1346: from pon_auction_headers_all ah,
1347: pon_item_prices_interface ipi
1348: where ipi.batch_id = p_batch_id
1349: and ipi.auction_header_id = ah.auction_header_id
1350: and rownum = 1;
1351:
1398: CURSOR C_ship_to (c_batch_id NUMBER) IS
1399: SELECT
1400: INTERFACE_LINE_ID
1401: , SHIP_TO_LOCATION_ID
1402: FROM PON_ITEM_PRICES_INTERFACE
1403: WHERE BATCH_ID = c_batch_id
1404: AND SHIP_TO_LOCATION_ID <> -1
1405: AND SHIP_TO_LOCATION <> 'SHIP_NONE_ENTERED';
1406: --
1476:
1477:
1478: select auction_header_id
1479: into l_auction_header_id
1480: from pon_item_prices_interface
1481: where batch_id = p_batch_id
1482: and auction_header_id is not null
1483: and rownum =1;
1484:
1514: select interface_type,
1515: fnd_message.get_string('PON','PON_AUCTS_QUANTITY'),
1516: quantity,
1517: 'PON_AUCTS_PR_QT_NOT_APPLY',
1518: 'PON_ITEM_PRICES_INTERFACE',
1519: batch_id,
1520: interface_line_id
1521: from pon_item_prices_interface
1522: where nvl(price_and_quantity_apply, 'Y') = 'N' and
1517: 'PON_AUCTS_PR_QT_NOT_APPLY',
1518: 'PON_ITEM_PRICES_INTERFACE',
1519: batch_id,
1520: interface_line_id
1521: from pon_item_prices_interface
1522: where nvl(price_and_quantity_apply, 'Y') = 'N' and
1523: quantity is not null and
1524: batch_id = p_batch_id;
1525: END IF;
1537: select interface_type,
1538: fnd_message.get_string('PON','PON_AUCTION_UOM'),
1539: unit_of_measure,
1540: 'PON_AUCTS_PR_QT_NOT_APPLY',
1541: 'PON_ITEM_PRICES_INTERFACE',
1542: batch_id,
1543: interface_line_id
1544: from pon_item_prices_interface
1545: where nvl(price_and_quantity_apply, 'Y') = 'N' and
1540: 'PON_AUCTS_PR_QT_NOT_APPLY',
1541: 'PON_ITEM_PRICES_INTERFACE',
1542: batch_id,
1543: interface_line_id
1544: from pon_item_prices_interface
1545: where nvl(price_and_quantity_apply, 'Y') = 'N' and
1546: (unit_of_measure is not null and unit_of_measure <> 'UOM_NONE_ENTERED') and
1547: batch_id = p_batch_id;
1548: END IF;
1560: select interface_type,
1561: fnd_message.get_string('PON','PON_AUCTS_TARGET_PRICE'),
1562: target_price,
1563: 'PON_AUCTS_PR_QT_NOT_APPLY',
1564: 'PON_ITEM_PRICES_INTERFACE',
1565: batch_id,
1566: interface_line_id
1567: from pon_item_prices_interface
1568: where nvl(price_and_quantity_apply, 'Y') = 'N' and
1563: 'PON_AUCTS_PR_QT_NOT_APPLY',
1564: 'PON_ITEM_PRICES_INTERFACE',
1565: batch_id,
1566: interface_line_id
1567: from pon_item_prices_interface
1568: where nvl(price_and_quantity_apply, 'Y') = 'N' and
1569: target_price is not null and
1570: batch_id = p_batch_id;
1571: END IF;
1583: select interface_type,
1584: fnd_message.get_string('PON','PON_AUCTS_CURRENT_PRICE'),
1585: current_price,
1586: 'PON_AUCTS_PR_QT_NOT_APPLY',
1587: 'PON_ITEM_PRICES_INTERFACE',
1588: batch_id,
1589: interface_line_id
1590: from pon_item_prices_interface
1591: where nvl(price_and_quantity_apply, 'Y') = 'N' and
1586: 'PON_AUCTS_PR_QT_NOT_APPLY',
1587: 'PON_ITEM_PRICES_INTERFACE',
1588: batch_id,
1589: interface_line_id
1590: from pon_item_prices_interface
1591: where nvl(price_and_quantity_apply, 'Y') = 'N' and
1592: current_price is not null and
1593: batch_id = p_batch_id;
1594: END IF;
1605: interface_line_id)
1606: select interface_type,
1607: fnd_message.get_string('PON','PON_AUC_LINE_TYPE'),
1608: 'PON_FIELD_MUST_BE_ENTERED',
1609: 'PON_ITEM_PRICES_INTERFACE',
1610: batch_id,
1611: interface_line_id
1612: from pon_item_prices_interface
1613: where line_type = 'LINE_TYPE_NONE_ENTERED'
1608: 'PON_FIELD_MUST_BE_ENTERED',
1609: 'PON_ITEM_PRICES_INTERFACE',
1610: batch_id,
1611: interface_line_id
1612: from pon_item_prices_interface
1613: where line_type = 'LINE_TYPE_NONE_ENTERED'
1614: and batch_id = p_batch_id
1615: and group_type <> 'GROUP';
1616:
1613: where line_type = 'LINE_TYPE_NONE_ENTERED'
1614: and batch_id = p_batch_id
1615: and group_type <> 'GROUP';
1616:
1617: --update the pon_item_prices_interface table
1618:
1619: update pon_item_prices_interface p1
1620: set (line_type_id,order_type_lookup_code,purchase_basis,outside_operation_flag) =
1621: (select nvl(po2.line_type_id,-9999), po2.order_type_lookup_code,po2.purchase_basis,po2.outside_operation_flag
1615: and group_type <> 'GROUP';
1616:
1617: --update the pon_item_prices_interface table
1618:
1619: update pon_item_prices_interface p1
1620: set (line_type_id,order_type_lookup_code,purchase_basis,outside_operation_flag) =
1621: (select nvl(po2.line_type_id,-9999), po2.order_type_lookup_code,po2.purchase_basis,po2.outside_operation_flag
1622: FROM po_line_types_vl po2 WHERE upper(p1.line_type) = upper(po2.line_type(+))
1623: and (po2.inactive_date is null or po2.inactive_date > sysdate))
1637: select interface_type,
1638: fnd_message.get_string('PON','PON_AUC_LINE_TYPE'),
1639: line_type,
1640: 'PON_AUC_LINE_TYPE_ERR',
1641: 'PON_ITEM_PRICES_INTERFACE',
1642: batch_id,
1643: interface_line_id
1644: from pon_item_prices_interface
1645: where line_type_id is null
1640: 'PON_AUC_LINE_TYPE_ERR',
1641: 'PON_ITEM_PRICES_INTERFACE',
1642: batch_id,
1643: interface_line_id
1644: from pon_item_prices_interface
1645: where line_type_id is null
1646: and line_type <> 'LINE_TYPE_NONE_ENTERED'
1647: and batch_id = p_batch_id;
1648:
1660: select interface_type,
1661: fnd_message.get_string('PON','PON_AUC_LINE_TYPE'),
1662: line_type,
1663: 'PON_AUC_LINE_TYPE_ERR',
1664: 'PON_ITEM_PRICES_INTERFACE',
1665: batch_id,
1666: interface_line_id
1667: from pon_item_prices_interface
1668: where line_type_id not in ( select line_type_id
1663: 'PON_AUC_LINE_TYPE_ERR',
1664: 'PON_ITEM_PRICES_INTERFACE',
1665: batch_id,
1666: interface_line_id
1667: from pon_item_prices_interface
1668: where line_type_id not in ( select line_type_id
1669: from po_style_enabled_line_types
1670: where style_id = l_po_style_id)
1671: and line_type_id is not null
1688: select interface_type,
1689: fnd_message.get_string('PON','PON_AUC_LINE_TYPE'),
1690: line_type,
1691: 'PON_AUC_LINE_TYPE_ERR',
1692: 'PON_ITEM_PRICES_INTERFACE',
1693: batch_id,
1694: interface_line_id
1695: from pon_item_prices_interface
1696: where purchase_basis not in ( select purchase_basis
1691: 'PON_AUC_LINE_TYPE_ERR',
1692: 'PON_ITEM_PRICES_INTERFACE',
1693: batch_id,
1694: interface_line_id
1695: from pon_item_prices_interface
1696: where purchase_basis not in ( select purchase_basis
1697: from po_style_enabled_pur_bases
1698: where style_id = l_po_style_id)
1699: and line_type_id is not null
1720: SELECT interface_type,
1721: fnd_message.get_string('PON','PON_AUC_LINE_TYPE'),
1722: line_type,
1723: 'PON_STANDARD_LINE_TYPES',
1724: 'PON_ITEM_PRICES_INTERFACE',
1725: batch_id,
1726: interface_line_id
1727: FROM pon_item_prices_interface
1728: WHERE line_type <> 'LINE_TYPE_NONE_ENTERED'
1723: 'PON_STANDARD_LINE_TYPES',
1724: 'PON_ITEM_PRICES_INTERFACE',
1725: batch_id,
1726: interface_line_id
1727: FROM pon_item_prices_interface
1728: WHERE line_type <> 'LINE_TYPE_NONE_ENTERED'
1729: AND batch_id = p_batch_id
1730: AND purchase_basis = 'TEMP LABOR';
1731: END IF;
1743: SELECT interface_type,
1744: fnd_message.get_string('PON','PON_AUC_LINE_TYPE'),
1745: line_type,
1746: 'PON_GLOBAL_LINE_TYPES',
1747: 'PON_ITEM_PRICES_INTERFACE',
1748: batch_id,
1749: interface_line_id
1750: FROM pon_item_prices_interface
1751: WHERE line_type <> 'LINE_TYPE_NONE_ENTERED'
1746: 'PON_GLOBAL_LINE_TYPES',
1747: 'PON_ITEM_PRICES_INTERFACE',
1748: batch_id,
1749: interface_line_id
1750: FROM pon_item_prices_interface
1751: WHERE line_type <> 'LINE_TYPE_NONE_ENTERED'
1752: AND batch_id = p_batch_id
1753: AND purchase_basis = 'TEMP LABOR';
1754: END IF;
1767: SELECT interface_type,
1768: fnd_message.get_string('PON','PON_AUC_LINE_TYPE'),
1769: line_type,
1770: 'PON_AUC_GLOBAL_OP_LINE',
1771: 'PON_ITEM_PRICES_INTERFACE',
1772: batch_id,
1773: interface_line_id
1774: FROM pon_item_prices_interface
1775: WHERE line_type <> 'LINE_TYPE_NONE_ENTERED'
1770: 'PON_AUC_GLOBAL_OP_LINE',
1771: 'PON_ITEM_PRICES_INTERFACE',
1772: batch_id,
1773: interface_line_id
1774: FROM pon_item_prices_interface
1775: WHERE line_type <> 'LINE_TYPE_NONE_ENTERED'
1776: AND batch_id = p_batch_id
1777: AND outside_operation_flag = 'Y';
1778: END IF;
1790: SELECT interface_type,
1791: fnd_message.get_string('PON','PON_AUCTS_ITEM'),
1792: null,
1793: 'PON_AUC_OPL_ITEM_REQ',
1794: 'PON_ITEM_PRICES_INTERFACE',
1795: batch_id,
1796: interface_line_id
1797: FROM pon_item_prices_interface
1798: WHERE line_type <> 'LINE_TYPE_NONE_ENTERED'
1793: 'PON_AUC_OPL_ITEM_REQ',
1794: 'PON_ITEM_PRICES_INTERFACE',
1795: batch_id,
1796: interface_line_id
1797: FROM pon_item_prices_interface
1798: WHERE line_type <> 'LINE_TYPE_NONE_ENTERED'
1799: AND item_number = 'ITEM_NUMBER_NONE_ENTERED'
1800: AND batch_id = p_batch_id
1801: AND outside_operation_flag = 'Y';
1813: SELECT interface_type,
1814: fnd_message.get_string('PON','PON_ITEM_JOB'),
1815: '',
1816: 'PON_LINE_TYPE_JOB_REQ',
1817: 'PON_ITEM_PRICES_INTERFACE',
1818: batch_id,
1819: interface_line_id
1820: FROM pon_item_prices_interface
1821: WHERE batch_id = p_batch_id
1816: 'PON_LINE_TYPE_JOB_REQ',
1817: 'PON_ITEM_PRICES_INTERFACE',
1818: batch_id,
1819: interface_line_id
1820: FROM pon_item_prices_interface
1821: WHERE batch_id = p_batch_id
1822: AND purchase_basis = 'TEMP LABOR'
1823: AND item_number = 'ITEM_NUMBER_NONE_ENTERED';
1824: --
1827: -- because there are not enterable on the UI.
1828: --
1829: -- First just get the job_id...
1830: --
1831: UPDATE pon_item_prices_interface p1
1832: SET job_id =
1833: (SELECT nvl(max(poj.job_id),-1)
1834: FROM po_job_associations poj,
1835: per_jobs pj,
1844: item_number <> 'ITEM_NUMBER_NONE_ENTERED';
1845: --
1846: -- Update the rest of the information for the service lines with job id's
1847: --
1848: UPDATE pon_item_prices_interface p1
1849: SET (item_description, category_name) =
1850: (SELECT poj.job_long_description,
1851: FND_FLEX_EXT.get_segs('INV', 'MCAT', CAT.STRUCTURE_ID, CAT.CATEGORY_ID)
1852: FROM po_job_associations poj,
1871: SELECT interface_type,
1872: fnd_message.get_string('PON','PON_ITEM_JOB'),
1873: item_number,
1874: 'PON_JOB_INVALID',
1875: 'PON_ITEM_PRICES_INTERFACE',
1876: batch_id,
1877: interface_line_id
1878: FROM pon_item_prices_interface p1
1879: WHERE batch_id = p_batch_id AND
1874: 'PON_JOB_INVALID',
1875: 'PON_ITEM_PRICES_INTERFACE',
1876: batch_id,
1877: interface_line_id
1878: FROM pon_item_prices_interface p1
1879: WHERE batch_id = p_batch_id AND
1880: purchase_basis = 'TEMP LABOR' AND
1881: item_number <> 'ITEM_NUMBER_NONE_ENTERED' AND
1882: job_id = -1;
1882: job_id = -1;
1883: --
1884: -- Set quantity to null for general services lines
1885: --
1886: UPDATE pon_item_prices_interface p1
1887: SET quantity = NULL,
1888: unit_of_measure = NULL
1889: WHERE batch_id = p_batch_id
1890: AND order_type_lookup_code = 'FIXED PRICE'
1891: AND purchase_basis = 'SERVICES';
1892: --
1893: -- Set quantity to null for fixed price temp labor lines
1894: --
1895: UPDATE pon_item_prices_interface p1
1896: SET quantity = null
1897: WHERE batch_id = p_batch_id
1898: AND order_type_lookup_code = 'FIXED PRICE'
1899: AND purchase_basis = 'TEMP LABOR';
1900: --
1901: -- Default category, job_description,
1902: -- additional_job_details for temp labor lines
1903: --
1904: UPDATE pon_item_prices_interface p1
1905: SET (item_description, additional_job_details, category_id, category_name) =
1906: (SELECT poj.job_description,
1907: decode(nvl(p1.additional_job_details,'JOB_DETAILS_NONE_ENTERED'),'JOB_DETAILS_NONE_ENTERED',poj.job_long_description, p1.additional_job_details),
1908: cat.category_id,
1928: select interface_type,
1929: fnd_message.get_string('PON','PON_PRICE_DIFF_RESPONSE'),
1930: differential_response_type,
1931: 'PON_INVALID_DIFF_RESPONSE',
1932: 'PON_ITEM_PRICES_INTERFACE',
1933: batch_id,
1934: interface_line_id
1935: from pon_item_prices_interface p1
1936: where batch_id = p_batch_id and
1931: 'PON_INVALID_DIFF_RESPONSE',
1932: 'PON_ITEM_PRICES_INTERFACE',
1933: batch_id,
1934: interface_line_id
1935: from pon_item_prices_interface p1
1936: where batch_id = p_batch_id and
1937: purchase_basis = 'TEMP LABOR' and
1938: differential_response_type not in ('DIFF_NONE_ENTERED',
1939: fnd_message.get_string('PON','PON_AUCTS_REQUIRED'),
1941: fnd_message.get_string('PON','PON_AUCTS_DISPLAY_ONLY'));
1942: --
1943: -- clear out the differential response type
1944: -- if it does not apply
1945: update pon_item_prices_interface
1946: set differential_response_type = null
1947: where batch_id = p_batch_id and
1948: (purchase_basis = 'TEMP LABOR' and
1949: differential_response_type not in (fnd_message.get_string('PON','PON_AUCTS_REQUIRED'),
1969: select interface_type,
1970: fnd_message.get_string('PON','PON_AUCTS_ITEM'),
1971: item_number,
1972: 'PON_AUCTS_SS_INVALID_INV_NUM',
1973: 'PON_ITEM_PRICES_INTERFACE',
1974: batch_id,
1975: interface_line_id
1976: from pon_item_prices_interface p1
1977: where batch_id = p_batch_id and
1972: 'PON_AUCTS_SS_INVALID_INV_NUM',
1973: 'PON_ITEM_PRICES_INTERFACE',
1974: batch_id,
1975: interface_line_id
1976: from pon_item_prices_interface p1
1977: where batch_id = p_batch_id and
1978: item_number <> 'ITEM_NUMBER_NONE_ENTERED' and
1979: upper(order_type_lookup_code) <> upper('AMOUNT') and
1980: purchase_basis <> 'TEMP LABOR' and
1999: and nvl(mck.disable_date, sysdate + 1) > sysdate
2000: 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'));
2001:
2002: -- set item number and revision to null if this is a group
2003: update pon_item_prices_interface p1
2004: set item_number = null,
2005: item_revision = null
2006: where batch_id = p_batch_id and
2007: item_number = 'ITEM_NUMBER_NONE_ENTERED' and
2010: -- sets item number and revision to null if item number is invalid or line type is amount based
2011: -- by setting the item number to null, sourcing one-time item validation will occur for
2012: -- unit of measure, category, etc..
2013:
2014: update pon_item_prices_interface p1
2015: set item_number = null,
2016: item_revision = null
2017: where batch_id = p_batch_id AND
2018: purchase_basis = 'SERVICES' or
2042:
2043: -- set inventory item id, the description update flag, and default the description and unit of measure
2044: -- it not entered for inventory items
2045:
2046: update pon_item_prices_interface p1
2047: set (item_id, item_description, allow_item_desc_update_flag, unit_of_measure) =
2048: (select msi.inventory_item_id,
2049: decode(p1.item_description, 'ITEM_NONE_ENTERED', msitl.description, p1.item_description),
2050: msi.allow_item_desc_update_flag,
2066: purchase_basis = 'GOODS';
2067:
2068: -- default the category if not entered for valid item numbers
2069:
2070: update pon_item_prices_interface p1
2071: set category_name = (select FND_FLEX_EXT.get_segs('INV', 'MCAT', MCK.STRUCTURE_ID, MCK.CATEGORY_ID) concatenated_segments
2072: from mtl_default_sets_view mdsv,
2073: mtl_item_categories mic,
2074: mtl_categories_kfv mck
2095: select interface_type,
2096: fnd_message.get_string('PON','PON_AUCTS_ITEM_DESC'),
2097: item_description,
2098: 'PON_AUCTS_INVALID_INV_DESC',
2099: 'PON_ITEM_PRICES_INTERFACE',
2100: batch_id,
2101: interface_line_id
2102: from pon_item_prices_interface p1
2103: where batch_id = p_batch_id and
2098: 'PON_AUCTS_INVALID_INV_DESC',
2099: 'PON_ITEM_PRICES_INTERFACE',
2100: batch_id,
2101: interface_line_id
2102: from pon_item_prices_interface p1
2103: where batch_id = p_batch_id and
2104: purchase_basis <> 'TEMP LABOR' and
2105: item_number is not null and
2106: allow_item_desc_update_flag = 'N' and
2126: select interface_type,
2127: fnd_message.get_string('PON','PON_AUCTS_REVISION'),
2128: item_revision,
2129: 'PON_AUCTS_INVALID_INV_REV',
2130: 'PON_ITEM_PRICES_INTERFACE',
2131: batch_id,
2132: interface_line_id
2133: from pon_item_prices_interface p1
2134: where batch_id = p_batch_id and
2129: 'PON_AUCTS_INVALID_INV_REV',
2130: 'PON_ITEM_PRICES_INTERFACE',
2131: batch_id,
2132: interface_line_id
2133: from pon_item_prices_interface p1
2134: where batch_id = p_batch_id and
2135: purchase_basis <> 'TEMP LABOR' and
2136: item_number is not null and
2137: item_revision not in (select revision
2139: where inventory_item_id = p1.item_id and
2140: organization_id = l_inventory_org_id);
2141:
2142:
2143: update pon_item_prices_interface p1
2144: set CATEGORY_NAME = (select FND_FLEX_EXT.get_segs('INV', 'MCAT', MCK.STRUCTURE_ID, MCK.CATEGORY_ID) concatenated_segments
2145: from
2146: MTL_CATEGORIES_KFV mck
2147: ,PO_LINE_TYPES plt
2150: and plt.category_id = mck.category_id)
2151: where batch_id = p_batch_id
2152: and ((CATEGORY_NAME = 'CAT_NONE_ENTERED') or (CATEGORY_NAME is null));
2153:
2154: update pon_item_prices_interface p1
2155: set UNIT_OF_MEASURE = nvl(l_amount_based_unit_of_measure,'UOM_NONE_ENTERED')
2156: where batch_id = p_batch_id
2157: and ((UNIT_OF_MEASURE = 'UOM_NONE_ENTERED') or (UNIT_OF_MEASURE is null))
2158: and ( upper(order_type_lookup_code) = upper('AMOUNT'));
2156: where batch_id = p_batch_id
2157: and ((UNIT_OF_MEASURE = 'UOM_NONE_ENTERED') or (UNIT_OF_MEASURE is null))
2158: and ( upper(order_type_lookup_code) = upper('AMOUNT'));
2159:
2160: update pon_item_prices_interface p1
2161: set UNIT_OF_MEASURE = (select plt.unit_of_measure
2162: from
2163: PO_LINE_TYPES plt
2164: where
2179: SELECT INTERFACE_TYPE
2180: , fnd_message.get_string('PON','PON_AUCTION_UOM')
2181: , unit_of_measure
2182: , 'PON_AUC_LINE_UOM_ERR'
2183: , 'PON_ITEM_PRICES_INTERFACE'
2184: , BATCH_ID
2185: , INTERFACE_LINE_ID
2186: FROM pon_item_prices_interface
2187: where batch_id = p_batch_id
2182: , 'PON_AUC_LINE_UOM_ERR'
2183: , 'PON_ITEM_PRICES_INTERFACE'
2184: , BATCH_ID
2185: , INTERFACE_LINE_ID
2186: FROM pon_item_prices_interface
2187: where batch_id = p_batch_id
2188: and nvl(price_and_quantity_apply, 'Y') = 'Y'
2189: and order_type_lookup_code = 'AMOUNT'
2190: and NOT((upper(UNIT_OF_MEASURE) = UPPER(l_amount_based_uom) ) or
2203: INTERFACE_TYPE
2204: , fnd_message.get_string('PON',decode(p_contract_type,'STANDARD','PON_AUCTS_QUANTITY','PON_AUCTS_EST_QUANTITY'))
2205: , quantity
2206: , 'PON_AUC_LINE_QUAN_ERR'
2207: , 'PON_ITEM_PRICES_INTERFACE'
2208: , BATCH_ID
2209: , INTERFACE_LINE_ID
2210: FROM pon_item_prices_interface
2211: where batch_id = p_batch_id
2206: , 'PON_AUC_LINE_QUAN_ERR'
2207: , 'PON_ITEM_PRICES_INTERFACE'
2208: , BATCH_ID
2209: , INTERFACE_LINE_ID
2210: FROM pon_item_prices_interface
2211: where batch_id = p_batch_id
2212: and nvl(price_and_quantity_apply, 'Y') = 'Y'
2213: and ( upper(order_type_lookup_code) = upper('AMOUNT'))
2214: and ( NOT(nvl(quantity,-1) = 1)) ;
2225: interface_line_id)
2226: select interface_type,
2227: fnd_message.get_string('PON','PON_AUCTS_CATEGORY'),
2228: 'PON_FIELD_MUST_BE_ENTERED',
2229: 'PON_ITEM_PRICES_INTERFACE',
2230: batch_id,
2231: interface_line_id
2232: from pon_item_prices_interface
2233: where ((category_name = 'CAT_NONE_ENTERED') or (category_name is null))
2228: 'PON_FIELD_MUST_BE_ENTERED',
2229: 'PON_ITEM_PRICES_INTERFACE',
2230: batch_id,
2231: interface_line_id
2232: from pon_item_prices_interface
2233: where ((category_name = 'CAT_NONE_ENTERED') or (category_name is null))
2234: AND batch_id = p_batch_id
2235: and group_type <> 'GROUP';
2236:
2234: AND batch_id = p_batch_id
2235: and group_type <> 'GROUP';
2236:
2237: -- First we do case insensitive to avoid full table scan on MTL_CATEGORIES_KFV
2238: update pon_item_prices_interface p
2239: set category_id = (select Nvl(MAX(MCK.category_id),-1)
2240: FROM (select category_id,
2241: FND_FLEX_EXT.get_segs('INV', 'MCAT', STRUCTURE_ID, CATEGORY_ID) CONCATENATED_SEGMENTS,
2242: ENABLED_FLAG,
2262: where batch_id = p_batch_id
2263: and category_name <> 'CAT_NON_ENTERED';
2264:
2265: -- For those which were not caught in the previous SQL
2266: update pon_item_prices_interface p
2267: set category_id = (select Nvl(MAX(MCK.category_id),-1)
2268: FROM (select category_id,
2269: FND_FLEX_EXT.get_segs('INV', 'MCAT', STRUCTURE_ID, CATEGORY_ID) CONCATENATED_SEGMENTS,
2270: ENABLED_FLAG,
2294: -- Because we do case insensitive validation for category_name,
2295: -- we need to update all valid user entered category names to the
2296: -- actual case sensitive value
2297: /*
2298: update pon_item_prices_interface p
2299: set category_name = (select Nvl(MAX(category_name),p.category_name)
2300: from icx_por_categories_tl i
2301: where i.rt_category_id = p.category_id
2302: and type=2 and i.language= p_language)
2303: where batch_id = p_batch_id
2304: and category_name <> 'CAT_NON_ENTERED'
2305: AND category_id <> -1;
2306: */
2307: update pon_item_prices_interface p
2308: set category_name = (select Nvl(MAX(FND_FLEX_EXT.get_segs('INV', 'MCAT', i.STRUCTURE_ID, i.CATEGORY_ID)),p.category_name)
2309: from mtl_categories_kfv i
2310: where i.category_id = p.category_id)
2311: where batch_id = p_batch_id
2323: select interface_type,
2324: fnd_message.get_string('PON','PON_AUCTS_CATEGORY'),
2325: category_name,
2326: decode(purchase_basis,'TEMP LABOR','PON_INVALID_TEMP_LABOR_CAT','PON_CATEGORY_ID_NOT_FOUND'),
2327: 'PON_ITEM_PRICES_INTERFACE',
2328: batch_id,
2329: interface_line_id
2330: from pon_item_prices_interface
2331: where category_id = -1
2326: decode(purchase_basis,'TEMP LABOR','PON_INVALID_TEMP_LABOR_CAT','PON_CATEGORY_ID_NOT_FOUND'),
2327: 'PON_ITEM_PRICES_INTERFACE',
2328: batch_id,
2329: interface_line_id
2330: from pon_item_prices_interface
2331: where category_id = -1
2332: AND batch_id = p_batch_id
2333: AND category_name <> 'CAT_NON_ENTERED';
2334:
2345: select interface_type,
2346: fnd_message.get_string('PON','PON_AUCTS_CATEGORY'),
2347: category_name,
2348: 'PON_AUCTS_INVALID_INV_CAT',
2349: 'PON_ITEM_PRICES_INTERFACE',
2350: batch_id,
2351: interface_line_id
2352: from pon_item_prices_interface p1
2353: where category_id <> -1 and
2348: 'PON_AUCTS_INVALID_INV_CAT',
2349: 'PON_ITEM_PRICES_INTERFACE',
2350: batch_id,
2351: interface_line_id
2352: from pon_item_prices_interface p1
2353: where category_id <> -1 and
2354: batch_id = p_batch_id and
2355: purchase_basis <> 'TEMP LABOR' and
2356: order_type_lookup_code <> 'FIXED PRICE' and
2375: -- default shopping category (ip category) using purchasing-iP category mappings
2376: -- when shopping category is missing...only for new lines since shopping category
2377: -- is optional
2378: IF (NVL(l_progress_payment_type,'NONE') = 'NONE') THEN
2379: update pon_item_prices_interface p1
2380: set ip_category_name = (select category_name
2381: from icx_cat_categories_v
2382: 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
2383: language = p_language)
2397: interface_line_id)
2398: select interface_type,
2399: fnd_message.get_string('PON','PON_SHOPPING_CAT'),
2400: 'PON_SHOP_CAT_NOT_VALID',
2401: 'PON_ITEM_PRICES_INTERFACE',
2402: batch_id,
2403: interface_line_id
2404: from pon_item_prices_interface p1
2405: where p1.batch_id = p_batch_id and
2400: 'PON_SHOP_CAT_NOT_VALID',
2401: 'PON_ITEM_PRICES_INTERFACE',
2402: batch_id,
2403: interface_line_id
2404: from pon_item_prices_interface p1
2405: where p1.batch_id = p_batch_id and
2406: p1.ip_category_name is not null and
2407: p1.ip_category_name <> 'IP_CAT_NONE_ENTERED' and
2408: not exists (select null
2411: icx.language = p_language);
2412:
2413: -- set ip category name to null if ip cateogry is invalid
2414:
2415: update pon_item_prices_interface p1
2416: set ip_category_name = null
2417: where p1.batch_id = p_batch_id and
2418: p1.ip_category_name is not null and
2419: p1.ip_category_name <> 'IP_CAT_NONE_ENTERED' and
2422: where icx.category_name = p1.ip_category_name and
2423: icx.language = p_language);
2424:
2425: -- set ip category id
2426: update pon_item_prices_interface p1
2427: set ip_category_id = (select rt_category_id
2428: from icx_cat_categories_v icx
2429: where icx.category_name = p1.ip_category_name and
2430: language = p_language)
2446: interface_line_id)
2447: select interface_type,
2448: fnd_message.get_string('PON','PON_AUCTS_ITEM_DESC'),
2449: 'PON_FIELD_MUST_BE_ENTERED',
2450: 'PON_ITEM_PRICES_INTERFACE',
2451: batch_id,
2452: interface_line_id
2453: from pon_item_prices_interface
2454: where item_description = 'ITEM_NONE_ENTERED'
2449: 'PON_FIELD_MUST_BE_ENTERED',
2450: 'PON_ITEM_PRICES_INTERFACE',
2451: batch_id,
2452: interface_line_id
2453: from pon_item_prices_interface
2454: where item_description = 'ITEM_NONE_ENTERED'
2455: AND batch_id = p_batch_id
2456: and nvl(purchase_basis,'NULL') <> 'TEMP LABOR';
2457:
2454: where item_description = 'ITEM_NONE_ENTERED'
2455: AND batch_id = p_batch_id
2456: and nvl(purchase_basis,'NULL') <> 'TEMP LABOR';
2457:
2458: update pon_item_prices_interface p1
2459: set item_description = null
2460: where batch_id = p_batch_id and
2461: item_description = 'ITEM_NONE_ENTERED';
2462:
2475: interface_line_id)
2476: select interface_type,
2477: fnd_message.get_string('PON','PON_ORDER_UNIT_H'),
2478: 'PON_FIELD_MUST_BE_ENTERED',
2479: 'PON_ITEM_PRICES_INTERFACE',
2480: batch_id,
2481: interface_line_id
2482: from pon_item_prices_interface
2483: where ((unit_of_measure = 'UOM_NONE_ENTERED') or (unit_of_measure is null))
2478: 'PON_FIELD_MUST_BE_ENTERED',
2479: 'PON_ITEM_PRICES_INTERFACE',
2480: batch_id,
2481: interface_line_id
2482: from pon_item_prices_interface
2483: where ((unit_of_measure = 'UOM_NONE_ENTERED') or (unit_of_measure is null))
2484: AND nvl(price_and_quantity_apply, 'Y') = 'Y'
2485: AND order_type_lookup_code <> 'FIXED PRICE'
2486: AND batch_id = p_batch_id
2489: -- Unit of Measure must be valid in mtl_units_of_measure_tl
2490: --
2491: -- Some modifications to avoid full table scan and more imp
2492: -- to incorporate the new demand in bug 2319969
2493: update pon_item_prices_interface p
2494: set uom_code = (select nvl(max(uom_code),'XXX') from
2495: mtl_units_of_measure_tl m
2496: where language = p_language
2497: and unit_of_measure_tl = p.unit_of_measure
2502: uom_class = FND_PROFILE.VALUE('PO_RATE_UOM_CLASS')))))
2503: where batch_id = p_batch_id
2504: and unit_of_measure <> 'UOM_NONE_ENTERED';
2505: --
2506: update pon_item_prices_interface p
2507: set uom_code = (select nvl(max(uom_code),'XXX') from
2508: mtl_units_of_measure_tl m
2509: where language = p_language
2510: and upper(unit_of_measure_tl) = upper(p.unit_of_measure)
2516: where batch_id = p_batch_id
2517: and unit_of_measure <> 'UOM_NONE_ENTERED'
2518: and uom_code = 'XXX';
2519: --
2520: update pon_item_prices_interface p
2521: set uom_code = (select nvl(max(uom_code),'XXX') from
2522: mtl_units_of_measure_tl m
2523: where language = p_language
2524: and uom_code = p.unit_of_measure
2530: where batch_id = p_batch_id
2531: and unit_of_measure <> 'UOM_NONE_ENTERED'
2532: and uom_code = 'XXX';
2533: --
2534: update pon_item_prices_interface p
2535: set uom_code = (select nvl(max(uom_code),'XXX') from
2536: mtl_units_of_measure_tl m
2537: where language = p_language
2538: and upper(uom_code) = upper(p.unit_of_measure)
2549:
2550: -- Because we do case insensitive validation for uom_code,
2551: -- we need to update all valid user entered unit of measures to the
2552: -- actual case sensitive value
2553: update pon_item_prices_interface p
2554: set unit_of_measure = (select nvl(max(unit_of_measure_tl),p.unit_of_measure) from
2555: mtl_units_of_measure_tl m
2556: where language = p_language
2557: and uom_code = p.uom_code)
2570: select interface_type,
2571: fnd_message.get_string('PON','PON_AUCTION_UOM'),
2572: unit_of_measure,
2573: decode(purchase_basis,'TEMP LABOR','PON_INVALID_TEMP_LABOR_UOM','PON_INVALID_UOM'),
2574: 'PON_ITEM_PRICES_INTERFACE',
2575: batch_id,
2576: interface_line_id
2577: from pon_item_prices_interface p
2578: where unit_of_measure <> 'UOM_NONE_ENTERED'
2573: decode(purchase_basis,'TEMP LABOR','PON_INVALID_TEMP_LABOR_UOM','PON_INVALID_UOM'),
2574: 'PON_ITEM_PRICES_INTERFACE',
2575: batch_id,
2576: interface_line_id
2577: from pon_item_prices_interface p
2578: where unit_of_measure <> 'UOM_NONE_ENTERED'
2579: AND batch_id = p_batch_id
2580: AND uom_code = 'XXX'
2581: AND nvl(price_and_quantity_apply, 'Y') = 'Y';
2593: select interface_type,
2594: fnd_message.get_string('PON','PON_AUCTION_UOM'),
2595: unit_of_measure,
2596: 'PON_AUCTS_INVALID_INV_UOM',
2597: 'PON_ITEM_PRICES_INTERFACE',
2598: batch_id,
2599: interface_line_id
2600: from pon_item_prices_interface p1
2601: where batch_id = p_batch_id and
2596: 'PON_AUCTS_INVALID_INV_UOM',
2597: 'PON_ITEM_PRICES_INTERFACE',
2598: batch_id,
2599: interface_line_id
2600: from pon_item_prices_interface p1
2601: where batch_id = p_batch_id and
2602: purchase_basis <> 'TEMP LABOR' and
2603: item_number is not null and
2604: uom_code not in (select uom_code
2609:
2610: -- Defaulting of Unit of Measure still occurs for lines where quantity and price don't apply
2611: -- For these lines, we will reset the unit of measure fields
2612: IF (is_valid_rule(p_doctype_Id, 'NO_PRICE_QUANTITY_ITEMS')) THEN
2613: update pon_item_prices_interface p1
2614: set unit_of_measure = null,
2615: uom_code = null
2616: where batch_id = p_batch_id and
2617: nvl(price_and_quantity_apply, 'Y') = 'N';
2634: interface_line_id)
2635: select interface_type,
2636: fnd_message.get_string('PON','PON_AUCTS_QUANTITY'),
2637: 'PON_FIELD_MUST_BE_ENTERED',
2638: 'PON_ITEM_PRICES_INTERFACE',
2639: batch_id,
2640: interface_line_id
2641: from pon_item_prices_interface
2642: where quantity is null
2637: 'PON_FIELD_MUST_BE_ENTERED',
2638: 'PON_ITEM_PRICES_INTERFACE',
2639: batch_id,
2640: interface_line_id
2641: from pon_item_prices_interface
2642: where quantity is null
2643: and nvl(price_and_quantity_apply, 'Y') = 'Y'
2644: and batch_id = p_batch_id
2645: and purchase_basis <> 'TEMP LABOR'
2666: quantity,
2667: 'PON_AUC_QUAN_FIXED_AMT',
2668: 'LINENUM',
2669: interface_line_id,
2670: 'PON_ITEM_PRICES_INTERFACE',
2671: batch_id,
2672: interface_line_id
2673: FROM pon_item_prices_interface
2674: WHERE
2669: interface_line_id,
2670: 'PON_ITEM_PRICES_INTERFACE',
2671: batch_id,
2672: interface_line_id
2673: FROM pon_item_prices_interface
2674: WHERE
2675: quantity IS NULL
2676: AND order_type_lookup_code <> 'FIXED PRICE'
2677: AND order_type_lookup_code <> 'AMOUNT'
2681: FROM
2682: pon_auc_price_elements_int pfs,
2683: fnd_lookup_values lookups
2684: WHERE
2685: pfs.batch_id = pon_item_prices_interface.batch_id
2686: AND pfs.auction_header_id = pon_item_prices_interface.auction_header_id
2687: AND pfs.interface_line_id = pon_item_prices_interface.interface_line_id
2688: AND lookups.lookup_type = 'PON_PRICING_BASIS'
2689: AND lookups.lookup_code = 'FIXED_AMOUNT'
2682: pon_auc_price_elements_int pfs,
2683: fnd_lookup_values lookups
2684: WHERE
2685: pfs.batch_id = pon_item_prices_interface.batch_id
2686: AND pfs.auction_header_id = pon_item_prices_interface.auction_header_id
2687: AND pfs.interface_line_id = pon_item_prices_interface.interface_line_id
2688: AND lookups.lookup_type = 'PON_PRICING_BASIS'
2689: AND lookups.lookup_code = 'FIXED_AMOUNT'
2690: AND lookups.view_application_id = 0
2683: fnd_lookup_values lookups
2684: WHERE
2685: pfs.batch_id = pon_item_prices_interface.batch_id
2686: AND pfs.auction_header_id = pon_item_prices_interface.auction_header_id
2687: AND pfs.interface_line_id = pon_item_prices_interface.interface_line_id
2688: AND lookups.lookup_type = 'PON_PRICING_BASIS'
2689: AND lookups.lookup_code = 'FIXED_AMOUNT'
2690: AND lookups.view_application_id = 0
2691: AND lookups.security_group_id = 0
2708: select interface_type,
2709: decode(p_contract_type, 'BLANKET','PON_AUCTS_EST_QUANTITY', 'CONTRACT', 'PON_AUCTS_EST_QUANTITY', 'PON_AUCTS_QUANTITY'),
2710: quantity,
2711: 'PON_MUST_BE_POSITIVE_NUMBER',
2712: 'PON_ITEM_PRICES_INTERFACE',
2713: batch_id,
2714: interface_line_id
2715: from pon_item_prices_interface
2716: where quantity <= 0
2711: 'PON_MUST_BE_POSITIVE_NUMBER',
2712: 'PON_ITEM_PRICES_INTERFACE',
2713: batch_id,
2714: interface_line_id
2715: from pon_item_prices_interface
2716: where quantity <= 0
2717: AND order_type_lookup_code <> 'FIXED PRICE'
2718: AND nvl(price_and_quantity_apply, 'Y') = 'Y'
2719: AND batch_id = p_batch_id;
2737: select interface_type,
2738: fnd_message.get_string('PON','PON_AUC_NEED_BY_FROM_DATE'),
2739: need_by_start_date,
2740: 'PON_DATE_MUST_BE_GT_TODAY',
2741: 'PON_ITEM_PRICES_INTERFACE',
2742: batch_id,
2743: interface_line_id
2744: from pon_item_prices_interface
2745: where need_by_start_date < sysdate
2740: 'PON_DATE_MUST_BE_GT_TODAY',
2741: 'PON_ITEM_PRICES_INTERFACE',
2742: batch_id,
2743: interface_line_id
2744: from pon_item_prices_interface
2745: where need_by_start_date < sysdate
2746: and batch_id = p_batch_id;
2747: --
2748: -- NEED_BY_TO_DATE
2757: select interface_type,
2758: fnd_message.get_string('PON','PON_AUC_NEED_BY_TO_DATE'),
2759: need_by_date,
2760: 'PON_DATE_MUST_BE_GT_TODAY',
2761: 'PON_ITEM_PRICES_INTERFACE',
2762: batch_id,
2763: interface_line_id
2764: from pon_item_prices_interface
2765: where need_by_date < sysdate
2760: 'PON_DATE_MUST_BE_GT_TODAY',
2761: 'PON_ITEM_PRICES_INTERFACE',
2762: batch_id,
2763: interface_line_id
2764: from pon_item_prices_interface
2765: where need_by_date < sysdate
2766: and batch_id = p_batch_id;
2767: --
2768: -- NEED_BY_TO_DATE vs NEED_BY_FROM_DATE
2777: select interface_type,
2778: fnd_message.get_string('PON','PON_AUC_NEED_BY_TO_DATE'),
2779: need_by_date,
2780: 'PON_AUC_NEEDBY_BEFORE_FROM_SS',
2781: 'PON_ITEM_PRICES_INTERFACE',
2782: batch_id,
2783: interface_line_id
2784: from pon_item_prices_interface
2785: where need_by_date < need_by_start_date
2780: 'PON_AUC_NEEDBY_BEFORE_FROM_SS',
2781: 'PON_ITEM_PRICES_INTERFACE',
2782: batch_id,
2783: interface_line_id
2784: from pon_item_prices_interface
2785: where need_by_date < need_by_start_date
2786: and batch_id = p_batch_id;
2787: --
2788: -- need by required for planned inventory items for SPO
2798: select ip.interface_type,
2799: fnd_message.get_string('PON','PON_AUCTS_NEEDBY'),
2800: null,
2801: 'PON_NEED_BY_DATE_REQ_SPD',
2802: 'PON_ITEM_PRICES_INTERFACE',
2803: ip.batch_id,
2804: ip.interface_line_id
2805: from pon_item_prices_interface ip
2806: where ip.batch_id = p_batch_id
2801: 'PON_NEED_BY_DATE_REQ_SPD',
2802: 'PON_ITEM_PRICES_INTERFACE',
2803: ip.batch_id,
2804: ip.interface_line_id
2805: from pon_item_prices_interface ip
2806: where ip.batch_id = p_batch_id
2807: and ip.need_by_date is null
2808: and ip.need_by_start_date is null
2809: and ip.item_id is not null
2836: )
2837: select interface_type
2838: , fnd_message.get_string('PON','PON_AUCTS_SHIP_TO_LOC')
2839: , 'PON_FIELD_MUST_BE_ENTERED'
2840: , 'PON_ITEM_PRICES_INTERFACE'
2841: , batch_id
2842: , interface_line_id
2843: from pon_item_prices_interface
2844: where batch_id = p_batch_id
2839: , 'PON_FIELD_MUST_BE_ENTERED'
2840: , 'PON_ITEM_PRICES_INTERFACE'
2841: , batch_id
2842: , interface_line_id
2843: from pon_item_prices_interface
2844: where batch_id = p_batch_id
2845: AND ship_to_location = 'SHIP_NONE_ENTERED'
2846: and group_type <> 'GROUP';
2847: END IF;
2846: and group_type <> 'GROUP';
2847: END IF;
2848: -- fph
2849: -- again to avoid some full table scan
2850: update pon_item_prices_interface p
2851: set ship_to_location_id = (select (nvl(max(location_id), -1))
2852: from po_ship_to_loc_org_v po_v
2853: where po_v.location_code = p.ship_to_location)
2854: where batch_id = p_batch_id
2853: where po_v.location_code = p.ship_to_location)
2854: where batch_id = p_batch_id
2855: and ship_to_location <> 'SHIP_NONE_ENTERED';
2856: --
2857: update pon_item_prices_interface p
2858: set ship_to_location_id = (select (nvl(max(location_id), -1))
2859: from po_ship_to_loc_org_v po_v
2860: where upper(po_v.location_code) = upper(p.ship_to_location))
2861: where batch_id = p_batch_id
2864: --
2865: -- Because we do case insensitive validation for ship_to_location,
2866: -- we need to update all valid user entered shipping locations to the
2867: -- actual case sensitive value fph
2868: update pon_item_prices_interface p
2869: set ship_to_location = (select (nvl(max(location_code), -1))
2870: from po_ship_to_loc_org_v po_v
2871: where po_v.location_id = p.ship_to_location_id)
2872: where batch_id = p_batch_id
2886: select interface_type,
2887: fnd_message.get_string('PON','PON_AUCTS_SHIP_TO_LOC'),
2888: ship_to_location,
2889: 'PON_CAT_INVALID_VALUE',
2890: 'PON_ITEM_PRICES_INTERFACE',
2891: batch_id,
2892: interface_line_id
2893: from pon_item_prices_interface
2894: where ship_to_location_id = -1
2889: 'PON_CAT_INVALID_VALUE',
2890: 'PON_ITEM_PRICES_INTERFACE',
2891: batch_id,
2892: interface_line_id
2893: from pon_item_prices_interface
2894: where ship_to_location_id = -1
2895: AND ship_to_location <> 'SHIP_NONE_ENTERED'
2896: AND batch_id = p_batch_id;
2897:
2912: select interface_type,
2913: fnd_message.get_string('PON','PON_AUCTS_TARGET_PRICE'),
2914: target_price,
2915: 'PON_MUST_BE_POSITIVE_NUMBER',
2916: 'PON_ITEM_PRICES_INTERFACE',
2917: batch_id,
2918: interface_line_id
2919: from pon_item_prices_interface
2920: where TARGET_PRICE <= 0
2915: 'PON_MUST_BE_POSITIVE_NUMBER',
2916: 'PON_ITEM_PRICES_INTERFACE',
2917: batch_id,
2918: interface_line_id
2919: from pon_item_prices_interface
2920: where TARGET_PRICE <= 0
2921: and nvl(price_and_quantity_apply, 'Y') = 'Y'
2922: and batch_id = p_batch_id;
2923: END IF;
2937: select interface_type,
2938: fnd_message.get_string('PON','PON_AUCTION_BID_START_PRICE'),
2939: bid_start_price,
2940: 'PON_MUST_BE_POSITIVE_NUMBER',
2941: 'PON_ITEM_PRICES_INTERFACE',
2942: batch_id,
2943: interface_line_id
2944: from pon_item_prices_interface
2945: where bid_start_price <= 0
2940: 'PON_MUST_BE_POSITIVE_NUMBER',
2941: 'PON_ITEM_PRICES_INTERFACE',
2942: batch_id,
2943: interface_line_id
2944: from pon_item_prices_interface
2945: where bid_start_price <= 0
2946: and batch_id = p_batch_id;
2947: END IF;
2948: --
2961: select interface_type,
2962: fnd_message.get_string('PON','PON_AUCTS_CURRENT_PRICE'),
2963: current_price,
2964: 'PON_MUST_BE_POSITIVE_NUMBER',
2965: 'PON_ITEM_PRICES_INTERFACE',
2966: batch_id,
2967: interface_line_id
2968: from pon_item_prices_interface
2969: where current_price <= 0
2964: 'PON_MUST_BE_POSITIVE_NUMBER',
2965: 'PON_ITEM_PRICES_INTERFACE',
2966: batch_id,
2967: interface_line_id
2968: from pon_item_prices_interface
2969: where current_price <= 0
2970: and nvl(price_and_quantity_apply, 'Y') = 'Y'
2971: and batch_id = p_batch_id;
2972: END IF;
2986: select interface_type,
2987: fnd_message.get_string('PON','PON_AUCTS_RESERVE_PRICE'),
2988: reserve_price,
2989: 'PON_MUST_BE_POSITIVE_NUMBER',
2990: 'PON_ITEM_PRICES_INTERFACE',
2991: batch_id,
2992: interface_line_id
2993: from pon_item_prices_interface
2994: where reserve_price <= 0 AND
2989: 'PON_MUST_BE_POSITIVE_NUMBER',
2990: 'PON_ITEM_PRICES_INTERFACE',
2991: batch_id,
2992: interface_line_id
2993: from pon_item_prices_interface
2994: where reserve_price <= 0 AND
2995: batch_id = p_batch_id;
2996: END IF;
2997: --
3021: select interface_type,
3022: fnd_message.get_string('PON','PON_AUCTION_BID_START_PRICE'),
3023: bid_start_price,
3024: 'PON_TARGET_GTR_BID_START',
3025: 'PON_ITEM_PRICES_INTERFACE',
3026: batch_id,
3027: interface_line_id
3028: from pon_item_prices_interface
3029: where bid_start_price <= target_price AND
3024: 'PON_TARGET_GTR_BID_START',
3025: 'PON_ITEM_PRICES_INTERFACE',
3026: batch_id,
3027: interface_line_id
3028: from pon_item_prices_interface
3029: where bid_start_price <= target_price AND
3030: batch_id = p_batch_id;
3031: END IF;
3032: --
3046: select interface_type,
3047: fnd_message.get_string('PON','PON_AUCTION_BID_START_PRICE'),
3048: bid_start_price,
3049: 'PON_AUCTS_START_LT_TARGET',
3050: 'PON_ITEM_PRICES_INTERFACE',
3051: batch_id,
3052: interface_line_id
3053: from pon_item_prices_interface
3054: where bid_start_price >= target_price AND
3049: 'PON_AUCTS_START_LT_TARGET',
3050: 'PON_ITEM_PRICES_INTERFACE',
3051: batch_id,
3052: interface_line_id
3053: from pon_item_prices_interface
3054: where bid_start_price >= target_price AND
3055: batch_id = p_batch_id;
3056: --
3057: -- bid start price < reserve price
3068: select interface_type,
3069: fnd_message.get_string('PON','PON_AUCTION_BID_START_PRICE'),
3070: bid_start_price,
3071: 'PON_AUCTS_START_LT_RESERVE',
3072: 'PON_ITEM_PRICES_INTERFACE',
3073: batch_id,
3074: interface_line_id
3075: from pon_item_prices_interface
3076: where reserve_price < bid_start_price AND
3071: 'PON_AUCTS_START_LT_RESERVE',
3072: 'PON_ITEM_PRICES_INTERFACE',
3073: batch_id,
3074: interface_line_id
3075: from pon_item_prices_interface
3076: where reserve_price < bid_start_price AND
3077: batch_id = p_batch_id;
3078: --
3079: -- reserve price < target price
3090: select interface_type,
3091: fnd_message.get_string('PON','PON_AUCTS_RESERVE_PRICE'),
3092: reserve_price,
3093: 'PON_AUCTS_RESERVE_LT_TARGET',
3094: 'PON_ITEM_PRICES_INTERFACE',
3095: batch_id,
3096: interface_line_id
3097: from pon_item_prices_interface
3098: where reserve_price > target_price AND
3093: 'PON_AUCTS_RESERVE_LT_TARGET',
3094: 'PON_ITEM_PRICES_INTERFACE',
3095: batch_id,
3096: interface_line_id
3097: from pon_item_prices_interface
3098: where reserve_price > target_price AND
3099: batch_id = p_batch_id;
3100: --
3101: END IF;
3115: select interface_type,
3116: fnd_message.get_string('PON','PON_ITEM_PRICE_TARGET_VALUE'),
3117: unit_target_price,
3118: 'PON_AUC_POSITIVE_OR_ZERO',
3119: 'PON_ITEM_PRICES_INTERFACE',
3120: batch_id,
3121: interface_line_id
3122: from pon_item_prices_interface
3123: where UNIT_TARGET_PRICE < 0
3118: 'PON_AUC_POSITIVE_OR_ZERO',
3119: 'PON_ITEM_PRICES_INTERFACE',
3120: batch_id,
3121: interface_line_id
3122: from pon_item_prices_interface
3123: where UNIT_TARGET_PRICE < 0
3124: and nvl(price_and_quantity_apply, 'Y') = 'Y'
3125: and batch_id = p_batch_id;
3126: