[Home] [Help]
70: * Private Procedure: SetReqChangeFlag
71: * Effects: set the change_pending_flag in requisition headers table
72: * if the flag is set to 'Y', which means there is pending
73: * change coming, also store the itemtype and itemkey to
74: * po_change_requests table.
75: *
76: * the process will commit when it exits.
77: *
78: * Returns:
232: procedure ValidateAndSaveRequest(
233: p_po_header_id in number,
234: p_po_release_id in number,
235: p_revision_num in number,
236: p_po_change_requests in out nocopy pos_chg_rec_tbl);
237:
238: /*************************************************************************
239: * Private Procedure: UpdateReqLine
240: * Effects: update the need by date and/or price of a requisition line
540: procedure ValidateAndSaveRequest(
541: p_po_header_id in number,
542: p_po_release_id in number,
543: p_revision_num in number,
544: p_po_change_requests in out nocopy pos_chg_rec_tbl) is
545:
546: l_doc_check_rec_type Doc_Check_Return_Type;
547: l_pos_errors pos_err_type;
548: l_online_report_id number;
578: x_progress :='PO_ReqChangeRequestWF_PVT.ValidateAndSaveRequest:001';
579:
580: -- if the doc is in approved status, call validation
581: -- api. if the validation check fails, we won't save
582: -- the po change requests, and also reject the parent
583: -- req change.
584: if(l_doc_status = 'APPROVED') then
585: PO_CHG_REQUEST_PVT.validate_change_request (
586: p_api_version => 1.0,
589: x_msg_data => l_msg_data,
590: p_po_header_id => p_po_header_id,
591: p_po_release_id => p_po_release_id,
592: p_revision_num => p_revision_num,
593: p_po_change_requests => p_po_change_requests,
594: x_online_report_id => l_online_report_id,
595: x_pos_errors => l_pos_errors,
596: x_doc_check_error_msg => l_doc_check_rec_type);
597: x_progress :='PO_ReqChangeRequestWF_PVT.ValidateAndSaveRequest:002'
612: end if;
613: l_error_message:=substr(l_error_message||l_msg_data||':', 1, 240);
614:
615: x_progress :='PO_ReqChangeRequestWF_PVT.ValidateAndSaveRequest:003';
616: l_record_count:=p_po_change_requests.count();
617: FOR i in 1..l_record_count LOOP
618: update po_change_requests
619: set request_status='REJECTED',
620: change_active_flag='N',
614:
615: x_progress :='PO_ReqChangeRequestWF_PVT.ValidateAndSaveRequest:003';
616: l_record_count:=p_po_change_requests.count();
617: FOR i in 1..l_record_count LOOP
618: update po_change_requests
619: set request_status='REJECTED',
620: change_active_flag='N',
621: response_reason=substr(fnd_message.get_string('PO',
622: 'PO_RCO_VALIDATION_ERROR')||':'||
623: l_error_message1, 1, 2000),
624: response_date=sysdate,
625: validation_error=l_error_message
626: where change_request_id=
627: p_po_change_requests(i).parent_change_request_id;
628: end loop;
629: x_progress :='PO_ReqChangeRequestWF_PVT.ValidateAndSaveRequest:004';
630: else
631: -- save the request, check for auto approval
637: x_return_status =>l_return_status,
638: p_po_header_id =>p_po_header_id,
639: p_po_release_id =>p_po_release_id,
640: p_revision_num =>p_revision_num,
641: p_po_change_requests =>p_po_change_requests,
642: x_request_group_id =>l_change_request_group_id);
643:
644: if(l_return_status <>'S') then
645: raise l_save_failure;
650: end if;
651: elsif(l_doc_status='REJECTED') then
652: -- reject the change immediately
653: x_progress :='PO_ReqChangeRequestWF_PVT.ValidateAndSaveRequest:007';
654: l_record_count:=p_po_change_requests.count();
655: FOR i in 1..l_record_count LOOP
656: update po_change_requests
657: set request_status='REJECTED',
658: change_active_flag='N',
652: -- reject the change immediately
653: x_progress :='PO_ReqChangeRequestWF_PVT.ValidateAndSaveRequest:007';
654: l_record_count:=p_po_change_requests.count();
655: FOR i in 1..l_record_count LOOP
656: update po_change_requests
657: set request_status='REJECTED',
658: change_active_flag='N',
659: response_date=sysdate,
660: response_reason=fnd_message.get_string('PO', 'PO_RCO_PO_REJECTED')
658: change_active_flag='N',
659: response_date=sysdate,
660: response_reason=fnd_message.get_string('PO', 'PO_RCO_PO_REJECTED')
661: where change_request_id=
662: p_po_change_requests(i).parent_change_request_id;
663: end loop;
664: x_progress :='PO_ReqChangeRequestWF_PVT.ValidateAndSaveRequest:008';
665: else
666: -- save the request. no auto approval at this time
672: x_return_status =>l_return_status,
673: p_po_header_id =>p_po_header_id,
674: p_po_release_id =>p_po_release_id,
675: p_revision_num =>p_revision_num,
676: p_po_change_requests =>p_po_change_requests,
677: x_request_group_id =>l_change_request_group_id);
678: if(l_return_status <>'S') then
679: raise l_save_failure;
680: end if;
681: x_progress :='PO_ReqChangeRequestWF_PVT.ValidateAndSaveRequest:010';
682: end if;
683: exception
684: when l_save_failure then
685: l_record_count:=p_po_change_requests.count();
686: FOR i in 1..l_record_count LOOP
687: update po_change_requests
688: set request_status='REJECTED',
689: change_active_flag='N',
683: exception
684: when l_save_failure then
685: l_record_count:=p_po_change_requests.count();
686: FOR i in 1..l_record_count LOOP
687: update po_change_requests
688: set request_status='REJECTED',
689: change_active_flag='N',
690: response_date=sysdate,
691: response_reason=fnd_message.get_string('PO', 'PO_RCO_EXCEPTION_WHEN_SAVING')
689: change_active_flag='N',
690: response_date=sysdate,
691: response_reason=fnd_message.get_string('PO', 'PO_RCO_EXCEPTION_WHEN_SAVING')
692: where change_request_id=
693: p_po_change_requests(i).parent_change_request_id;
694: end loop;
695: when others then
696: wf_core.context('PO_ReqChangeRequestWF_PVT',
697: 'ValidateAndSaveRequest',x_progress||sqlerrm);
864: begin
865:
866: select parent_change_request_id
867: into l_req_change_request_id
868: from po_change_requests
869: where change_request_id=p_change_request_id;
870:
871: update po_change_requests
872: set request_status='ACCEPTED',
867: into l_req_change_request_id
868: from po_change_requests
869: where change_request_id=p_change_request_id;
870:
871: update po_change_requests
872: set request_status='ACCEPTED',
873: change_active_flag='N',
874: response_date=sysdate,
875: response_reason=fnd_message.get_string('PO','PO_RCO_AUTO_ACCEPTED')
876: where change_request_id in (l_req_change_request_id, p_change_request_id);
877:
878: select document_header_id, document_line_id, document_distribution_id, action_type, request_level
879: into l_header_id, l_line_id, l_distribution_id, l_action_type, l_request_level
880: from po_change_requests
881: where change_request_id=l_req_change_request_id;
882:
883: if(l_action_type='CANCELLATION') then
884: if(l_request_level='HEADER') then
898: new_start_date, new_expiration_date
899: into l_new_need_by_date, l_new_unit_price,
900: l_new_currency_unit_price, l_new_start_date,
901: l_new_expiration_date
902: from po_change_requests
903: where change_request_id=l_req_change_request_id;
904:
905: UpdateReqLine(l_line_id, l_new_need_by_date,
906: l_new_unit_price, l_new_currency_unit_price,
907: l_new_start_date, l_new_expiration_date);
908: /* else
909: select new_quantity, old_quantity
910: into l_new_quantity, l_old_quantity
911: from po_change_requests
912: where change_request_id=l_req_change_request_id;
913:
914: UpdateReqDistribution(l_line_id, l_distribution_id,
915: l_new_quantity, l_old_quantity);
963: pcr2.old_currency_amount,
964: pcr2.document_line_id,
965: pcr2.document_distribution_id,
966: pcr2.change_request_group_id
967: from po_change_requests pcr1, po_change_requests pcr2
968: where pcr1.change_request_group_id=p_change_request_group_id
969: and pcr1.document_line_location_id=p_line_location_id
970: and pcr1.parent_change_request_id=pcr2.change_request_id;
971:
991:
992: l_line_id1:=l_line_id;
993: l_req_change_group_id1:=l_req_change_group_id;
994:
995: update po_change_requests
996: set request_status='ACCEPTED',
997: change_active_flag='N',
998: response_date=sysdate,
999: response_reason=fnd_message.get_string('PO','PO_RCO_AUTO_ACCEPTED')
1010: end if;
1011: end loop;
1012:
1013: begin
1014: update po_change_requests
1015: set request_status='ACCEPTED',
1016: change_active_flag='N',
1017: response_date=sysdate,
1018: response_reason=fnd_message.get_string('PO','PO_RCO_AUTO_ACCEPTED')
1027:
1028: begin
1029: select new_price, new_currency_unit_price
1030: into l_new_price, l_new_currency_unit_price
1031: from po_change_requests
1032: where change_request_group_id=l_req_change_group_id1
1033: and document_line_id=l_line_id1
1034: and action_type='DERIVED'
1035: and new_price is not null;
1067: l_change_request_id number;
1068: l_request_status varchar2(20);
1069:
1070:
1071: l_action_type po_change_requests.action_type%type;
1072: l_last_action_type po_change_requests.action_type%type;
1073: l_new_need_by_date DATE;
1074: l_po_need_by_date DATE;
1075: l_new_price number;
1068: l_request_status varchar2(20);
1069:
1070:
1071: l_action_type po_change_requests.action_type%type;
1072: l_last_action_type po_change_requests.action_type%type;
1073: l_new_need_by_date DATE;
1074: l_po_need_by_date DATE;
1075: l_new_price number;
1076: l_po_price number;
1120: pol.cancel_flag line_cancel_flag,
1121: pll.cancel_flag shipment_cancel_flag,
1122: poh.currency_code,
1123: pol.org_id
1124: from po_change_requests pcr,
1125: po_headers_all poh,
1126: po_lines_all pol,
1127: po_line_locations_all pll,
1128: po_distributions_all pod,
1208: if(l_request_level='DISTRIBUTION') then
1209: -- it must be quantity change
1210:
1211: -- bug 5363103
1212: -- l_new_quantity and l_new_amount are from po_change_requests table
1213: -- which are always in functional currency;
1214: -- l_po_quantity and l_po_amount are from PO.
1215: -- If PO is created in txn currency, RCO shouldn't be autoapproved
1216: -- even if the quantities (amounts) are the same ( since they are
1291: cursor l_get_id_csr(p_change_request_group_id in number) is
1292: select document_header_id,
1293: po_release_id,
1294: document_type
1295: from po_change_requests
1296: where change_request_group_id=p_change_request_group_id
1297: and request_status='PENDING';
1298:
1299: l_document_header_id number;
1358: new_expiration_date,
1359: old_amount,
1360: new_amount,
1361: parent_change_request_id
1362: from po_change_requests
1363: where change_request_group_id=p_change_request_group_id
1364: and request_status='PENDING';
1365:
1366: begin
1387: x_progress :='004';
1388: if(l_doc_status='REJECTED') then
1389: -- reject the change immediately
1390: x_progress :='005';
1391: update po_change_requests
1392: set request_status='REJECTED',
1393: change_active_flag='N',
1394: response_date=sysdate,
1395: response_reason=fnd_message.get_string('PO', 'PO_RCO_PO_REJECTED')
1394: response_date=sysdate,
1395: response_reason=fnd_message.get_string('PO', 'PO_RCO_PO_REJECTED')
1396: where change_request_id in (
1397: select parent_change_request_id
1398: from po_change_requests
1399: where change_request_group_id=p_change_request_group_id
1400: and request_status='PENDING');
1401:
1402: x_progress :='006';
1399: where change_request_group_id=p_change_request_group_id
1400: and request_status='PENDING');
1401:
1402: x_progress :='006';
1403: update po_change_requests
1404: set request_status='REJECTED',
1405: change_active_flag='N',
1406: response_date=sysdate,
1407: response_reason=fnd_message.get_string('PO', 'PO_RCO_PO_REJECTED')
1516: x_msg_data => l_msg_data,
1517: p_po_header_id => ll_document_header_id,
1518: p_po_release_id => ll_po_release_id,
1519: p_revision_num => ll_document_revision_num,
1520: p_po_change_requests => my_chg_rec_tbl,
1521: x_online_report_id => l_online_report_id,
1522: x_pos_errors => l_pos_errors,
1523: x_doc_check_error_msg => l_doc_check_rec_type);
1524:
1528: l_error_message1:=null;
1529: if(l_pos_errors is not null) then
1530: l_error_message1:=l_pos_errors.text_line(1);
1531: end if;
1532: update po_change_requests
1533: set request_status='REJECTED',
1534: change_active_flag='N',
1535: response_date=sysdate,
1536: response_reason=substr(fnd_message.get_string('PO',
1537: 'PO_RCO_VALIDATION_ERROR')||':'||
1538: l_error_message1, 1, 2000)
1539: where change_request_id in (
1540: select parent_change_request_id
1541: from po_change_requests
1542: where change_request_group_id=p_change_request_group_id
1543: and request_status='PENDING');
1544:
1545: x_progress :='013';
1542: where change_request_group_id=p_change_request_group_id
1543: and request_status='PENDING');
1544:
1545: x_progress :='013';
1546: update po_change_requests
1547: set request_status='REJECTED',
1548: change_active_flag='N',
1549: response_date=sysdate,
1550: response_reason=substr(fnd_message.get_string('PO',
1598: procedure ProcessBuyerAction(p_change_request_group_id in number, p_action in varchar2, p_launch_approvals_flag IN VARCHAR2 default 'N', p_supplier_change IN varchar2 default 'N') is
1599: pragma AUTONOMOUS_TRANSACTION;
1600:
1601: x_progress varchar2(100):= '000';
1602: l_request_level PO_CHANGE_REQUESTS.request_level%type;
1603: l_document_type PO_CHANGE_REQUESTS.document_type%type;
1604: l_document_id number;
1605: l_document_line_id number;
1606: l_document_line_id1 number:=null;
1599: pragma AUTONOMOUS_TRANSACTION;
1600:
1601: x_progress varchar2(100):= '000';
1602: l_request_level PO_CHANGE_REQUESTS.request_level%type;
1603: l_document_type PO_CHANGE_REQUESTS.document_type%type;
1604: l_document_id number;
1605: l_document_line_id number;
1606: l_document_line_id1 number:=null;
1607: l_document_shipment_id number;
1607: l_document_shipment_id number;
1608: l_document_distribution_id number;
1609: l_release_id number;
1610: l_change_request_id number;
1611: l_request_status PO_CHANGE_REQUESTS.request_status%type;
1612: l_requester_id number;
1613: l_return_code number;
1614: l_err_msg varchar2(200);
1615: l_return_msg varchar2(2000);
1613: l_return_code number;
1614: l_err_msg varchar2(200);
1615: l_return_msg varchar2(2000);
1616: l_document_subtype varchar2(100);
1617: l_change_reason PO_CHANGE_REQUESTS.request_reason%TYPE;
1618: l_return_status varchar2(1);
1619:
1620: l_old_need_by_date DATE;
1621: l_new_need_by_date DATE;
1647:
1648: /* this is for the date change request id
1649: which isnot tied by parent request id
1650: */
1651: l_date_change_id PO_CHANGE_REQUESTS.change_request_id%type;
1652: l_date_change_id1 PO_CHANGE_REQUESTS.change_request_id%type;
1653:
1654: -- Added variable l_validation_error
1655: l_validation_error PO_CHANGE_REQUESTS.VALIDATION_ERROR%type;
1648: /* this is for the date change request id
1649: which isnot tied by parent request id
1650: */
1651: l_date_change_id PO_CHANGE_REQUESTS.change_request_id%type;
1652: l_date_change_id1 PO_CHANGE_REQUESTS.change_request_id%type;
1653:
1654: -- Added variable l_validation_error
1655: l_validation_error PO_CHANGE_REQUESTS.VALIDATION_ERROR%type;
1656:
1651: l_date_change_id PO_CHANGE_REQUESTS.change_request_id%type;
1652: l_date_change_id1 PO_CHANGE_REQUESTS.change_request_id%type;
1653:
1654: -- Added variable l_validation_error
1655: l_validation_error PO_CHANGE_REQUESTS.VALIDATION_ERROR%type;
1656:
1657: cursor cancel_request is
1658: select decode (document_type, 'RELEASE', null, document_line_id), document_line_location_id,
1659: change_request_id, request_reason
1656:
1657: cursor cancel_request is
1658: select decode (document_type, 'RELEASE', null, document_line_id), document_line_location_id,
1659: change_request_id, request_reason
1660: from po_change_requests
1661: where change_request_group_id=p_change_request_group_id
1662: and request_status='BUYER_APP'
1663: and action_type='CANCELLATION';
1664:
1672: old_expiration_date, new_expiration_date,
1673: old_amount, new_amount,
1674: old_currency_amount, new_currency_amount,
1675: change_request_group_id
1676: from po_change_requests
1677: where change_request_id in
1678: (select parent_change_request_id
1679: from po_change_requests
1680: where change_request_group_id=p_change_request_group_id
1675: change_request_group_id
1676: from po_change_requests
1677: where change_request_id in
1678: (select parent_change_request_id
1679: from po_change_requests
1680: where change_request_group_id=p_change_request_group_id
1681: and request_status='BUYER_APP'
1682: and action_type='MODIFICATION')
1683: order by document_line_id, document_distribution_id;
1692: old_expiration_date, new_expiration_date,
1693: old_amount, new_amount,
1694: old_currency_amount, new_currency_amount,
1695: change_request_group_id
1696: from po_change_requests
1697: where change_request_group_id in
1698: (select parent_change_request_id
1699: from po_change_requests
1700: where change_request_group_id=p_change_request_group_id
1695: change_request_group_id
1696: from po_change_requests
1697: where change_request_group_id in
1698: (select parent_change_request_id
1699: from po_change_requests
1700: where change_request_group_id=p_change_request_group_id
1701: and request_status='BUYER_APP'
1702: and action_type='MODIFICATION')
1703: order by document_line_id, document_distribution_id;
1703: order by document_line_id, document_distribution_id;
1704:
1705: cursor l_document_id_csr is
1706: select document_type, document_header_id, po_release_id, nvl(requester_id, created_by)
1707: from po_change_requests
1708: where change_request_group_id =p_change_request_group_id;
1709:
1710: cursor l_exist_change_request_csr is
1711: select change_request_id
1708: where change_request_group_id =p_change_request_group_id;
1709:
1710: cursor l_exist_change_request_csr is
1711: select change_request_id
1712: from po_change_requests
1713: where change_request_group_id=p_change_request_group_id
1714: and request_status in ('PENDING', 'BUYER_APP')
1715: and action_type='MODIFICATION';
1716: l_doc_check_rec POS_ERR_TYPE;
1718: --This cursor doesn't include 'amount' change records
1719: -- since for 'amount'change records, both new_start_date and new_expiration_date are null
1720: cursor l_date_change_csr is
1721: select document_line_id,change_request_group_id
1722: from po_change_requests
1723: where document_type = 'REQ'
1724: and change_request_id in
1725: (select parent_change_request_id
1726: from po_change_requests pcr2
1722: from po_change_requests
1723: where document_type = 'REQ'
1724: and change_request_id in
1725: (select parent_change_request_id
1726: from po_change_requests pcr2
1727: where pcr2.change_request_group_id=p_change_request_group_id
1728: and pcr2.action_type='MODIFICATION'
1729: and ( pcr2.new_start_date is not null
1730: or pcr2.new_expiration_date is not null ) );
1739: -- when buyer response to the po change, the response_date
1740: -- response_reason and resonded_by is not carried back to
1741: -- requisition change request. thus the requisition history
1742: -- page shows null on those field
1743: update PO_CHANGE_REQUESTS pcr1
1744: set (pcr1.request_status,
1745: pcr1.change_active_flag,
1746: pcr1.response_date,
1747: pcr1.response_reason,
1756: pcr2.responded_by,
1757: fnd_global.user_id,
1758: fnd_global.login_id,
1759: sysdate
1760: from po_change_requests pcr2
1761: where pcr2.parent_change_request_id=pcr1.change_request_id
1762: and pcr2.change_request_group_id=p_change_request_group_id
1763: and pcr2.request_status='REJECTED')
1764: where pcr1.change_request_id in
1762: and pcr2.change_request_group_id=p_change_request_group_id
1763: and pcr2.request_status='REJECTED')
1764: where pcr1.change_request_id in
1765: (select parent_change_request_id
1766: from po_change_requests
1767: where change_request_group_id=p_change_request_group_id
1768: and request_status='REJECTED');
1769:
1770: x_progress:='REJECTION:002';
1782: because this situation only happens to temp labor line,
1783: let it only applies to temp labor line.
1784: */
1785:
1786: update PO_CHANGE_REQUESTS pcr1
1787: set (pcr1.request_status,
1788: pcr1.change_active_flag,
1789: pcr1.response_date,
1790: pcr1.response_reason,
1799: pcr2.responded_by,
1800: fnd_global.user_id,
1801: fnd_global.login_id,
1802: sysdate
1803: from po_change_requests pcr2
1804: where pcr2.parent_change_request_id in
1805: ( select pcr3.change_request_id
1806: from po_change_requests pcr3
1807: where
1802: sysdate
1803: from po_change_requests pcr2
1804: where pcr2.parent_change_request_id in
1805: ( select pcr3.change_request_id
1806: from po_change_requests pcr3
1807: where
1808: pcr3.change_request_group_id=pcr1.change_request_group_id
1809: and pcr3.document_line_id = pcr1.document_line_id
1810: )
1813: )
1814: where pcr1.change_request_id in (
1815: select pcr5.change_request_id
1816: from
1817: po_change_requests pcr,
1818: po_change_requests pcr4,
1819: po_change_requests pcr5,
1820: po_requisition_lines_all por
1821: where
1814: where pcr1.change_request_id in (
1815: select pcr5.change_request_id
1816: from
1817: po_change_requests pcr,
1818: po_change_requests pcr4,
1819: po_change_requests pcr5,
1820: po_requisition_lines_all por
1821: where
1822: pcr.change_request_group_id=p_change_request_group_id
1815: select pcr5.change_request_id
1816: from
1817: po_change_requests pcr,
1818: po_change_requests pcr4,
1819: po_change_requests pcr5,
1820: po_requisition_lines_all por
1821: where
1822: pcr.change_request_group_id=p_change_request_group_id
1823: and pcr.parent_change_request_id=pcr4.change_request_id
1839: last_updated_by = fnd_global.user_id,
1840: last_update_login = fnd_global.login_id
1841: WHERE line_location_id IN
1842: (SELECT document_line_location_id
1843: FROM po_change_requests
1844: WHERE
1845: request_level = 'SHIPMENT' AND
1846: change_request_group_id = p_change_request_group_id AND
1847: action_type IN ('MODIFICATION', 'CANCELLATION') AND
1959: -- response_reason and resonded_by is not carried back to
1960: -- requisition change request. thus the requisition history
1961: -- page shows null on those field
1962:
1963: update PO_CHANGE_REQUESTS pcr1
1964: set (pcr1.request_status,
1965: pcr1.change_active_flag,
1966: pcr1.response_date,
1967: pcr1.response_reason,
1976: pcr2.responded_by,
1977: fnd_global.user_id,
1978: fnd_global.login_id,
1979: sysdate
1980: from po_change_requests pcr2
1981: where pcr2.parent_change_request_id=pcr1.change_request_id
1982: and pcr2.change_request_group_id=p_change_request_group_id
1983: and pcr2.request_status in ('BUYER_APP', 'ACCEPTED')
1984: and pcr2.action_type='CANCELLATION')
1983: and pcr2.request_status in ('BUYER_APP', 'ACCEPTED')
1984: and pcr2.action_type='CANCELLATION')
1985: where pcr1.change_request_id in
1986: (select parent_change_request_id
1987: from po_change_requests
1988: where change_request_group_id=p_change_request_group_id
1989: and request_status in ('BUYER_APP', 'ACCEPTED')
1990: and action_type='CANCELLATION');
1991: x_progress:='CANCELLATION:005';
1989: and request_status in ('BUYER_APP', 'ACCEPTED')
1990: and action_type='CANCELLATION');
1991: x_progress:='CANCELLATION:005';
1992:
1993: update PO_CHANGE_REQUESTS
1994: set request_status='ACCEPTED',
1995: change_active_flag='N'
1996: where change_request_group_id=p_change_request_group_id
1997: and request_status='BUYER_APP'
2059: IF(FND_MSG_PUB.COUNT_MSG = 1) THEN
2060: l_validation_error := fnd_msg_pub.get(p_encoded => 'F');
2061: END IF;
2062:
2063: update PO_CHANGE_REQUESTS
2064: set request_status='REJECTED',
2065: change_active_flag='N',
2066: response_date=sysdate,
2067: response_reason=fnd_message.get_string('PO', 'PO_RCO_EXCEPTION_WHEN_PROCESS') ||
2067: response_reason=fnd_message.get_string('PO', 'PO_RCO_EXCEPTION_WHEN_PROCESS') ||
2068: decode(l_validation_error, NULL, '', ' : ' || l_validation_error ),
2069: validation_error =l_validation_error
2070: where change_request_id in (select parent_change_request_id
2071: from po_change_requests
2072: where change_request_group_id=p_change_request_group_id
2073: and request_status='BUYER_APP'
2074: and action_type='CANCELLATION');
2075:
2072: where change_request_group_id=p_change_request_group_id
2073: and request_status='BUYER_APP'
2074: and action_type='CANCELLATION');
2075:
2076: update PO_CHANGE_REQUESTS
2077: set request_status='REJECTED',
2078: change_active_flag='N',
2079: response_date=sysdate,
2080: response_reason=fnd_message.get_string('PO', 'PO_RCO_EXCEPTION_WHEN_PROCESS') ||
2174:
2175: begin
2176: select new_price, new_currency_unit_price
2177: into l_new_price1, l_new_currency_unit_price1
2178: from po_change_requests
2179: where change_request_group_id=l_group_id
2180: and document_line_id=l_document_line_id
2181: and action_type='DERIVED'
2182: and new_price is not null;
2194: -- move req change to req
2195: if(l_request_level='LINE') then
2196:
2197: -- update start_date and end_date
2198: -- select new_start_date and new_expiration_date from po_change_requests
2199: -- req's change requests has separate records
2200: -- for start date and end date
2201: -- while only 1 merged record for po counter part,
2202: -- the change_request cursor doesn't capture both start and end date change.
2202: -- the change_request cursor doesn't capture both start and end date change.
2203: BEGIN
2204: select new_start_date
2205: into l_new_start_date
2206: from po_change_requests
2207: where change_request_group_id = l_group_id
2208: and document_type = 'REQ'
2209: and document_line_id = l_document_line_id
2210: and request_level= 'LINE'
2219:
2220: BEGIN
2221: select new_expiration_date
2222: into l_new_expiration_date
2223: from po_change_requests
2224: where change_request_group_id = l_group_id
2225: and document_type = 'REQ'
2226: and document_line_id = l_document_line_id
2227: and request_level= 'LINE'
2311: -- when buyer response to the po change, the response_date
2312: -- response_reason and resonded_by is not carried back to
2313: -- requisition change request. thus the requisition history
2314: -- page shows null on those field
2315: update PO_CHANGE_REQUESTS pcr1
2316: set (pcr1.request_status,
2317: pcr1.change_active_flag,
2318: pcr1.response_date,
2319: pcr1.response_reason,
2328: pcr2.responded_by,
2329: fnd_global.user_id,
2330: fnd_global.login_id,
2331: sysdate
2332: from po_change_requests pcr2
2333: where pcr2.parent_change_request_id=pcr1.change_request_id
2334: and pcr2.change_request_group_id=p_change_request_group_id
2335: and pcr2.request_status='BUYER_APP'
2336: and pcr2.action_type='MODIFICATION')
2335: and pcr2.request_status='BUYER_APP'
2336: and pcr2.action_type='MODIFICATION')
2337: where pcr1.change_request_id in
2338: (select parent_change_request_id
2339: from po_change_requests
2340: where change_request_group_id=p_change_request_group_id
2341: and request_status='BUYER_APP'
2342: and action_type='MODIFICATION');
2343:
2344: x_progress:='ACCEPTANCE:010';
2345:
2346: -- When there are start_date and end_date changes, above 'update' doesn't update all of the REQ change requests
2347: -- This is because for REQ change requests, start_date and end_date changes for the same req line are stored in two different rows;
2348: -- while for PO change requests, start_date and end_date changes are stored in one row.
2349: -- Below we update the status for those remaining records.
2350:
2351: open l_date_change_csr;
2352: loop
2355: l_req_change_grp_id;
2356: exit when l_date_change_csr%NOTFOUND;
2357:
2358: if (l_req_doc_id is not null) then
2359: update PO_CHANGE_REQUESTS pcr1
2360: set (pcr1.request_status,
2361: pcr1.change_active_flag,
2362: pcr1.response_date,
2363: pcr1.response_reason,
2372: pcr2.responded_by,
2373: fnd_global.user_id,
2374: fnd_global.login_id,
2375: sysdate
2376: from po_change_requests pcr2
2377: where pcr2.document_type= 'REQ'
2378: and pcr2.change_request_group_id=l_req_change_grp_id
2379: and pcr2.document_line_id = l_req_doc_id
2380: and pcr2.request_status= 'ACCEPTED'
2398: --In case of error, continue workflow
2399: when FND_API.g_exc_error then
2400: rollback to PO_REQCHANGEREQUESTWF_PVT_SP;
2401:
2402: update PO_CHANGE_REQUESTS
2403: set request_status='REJECTED',
2404: change_active_flag='N',
2405: response_date=sysdate,
2406: response_reason=fnd_message.get_string('PO', 'PO_RCO_EXCEPTION_WHEN_PROCESS') ||
2406: response_reason=fnd_message.get_string('PO', 'PO_RCO_EXCEPTION_WHEN_PROCESS') ||
2407: decode(l_validation_error, NULL, '', ' : ' || l_validation_error ),
2408: validation_error=l_err_msg
2409: where change_request_id in (select parent_change_request_id
2410: from po_change_requests
2411: where change_request_group_id=p_change_request_group_id
2412: and request_status='BUYER_APP'
2413: and action_type='MODIFICATION');
2414:
2411: where change_request_group_id=p_change_request_group_id
2412: and request_status='BUYER_APP'
2413: and action_type='MODIFICATION');
2414:
2415: update PO_CHANGE_REQUESTS
2416: set request_status='REJECTED',
2417: change_active_flag='N',
2418: response_date=sysdate,
2419: response_reason=fnd_message.get_string('PO', 'PO_RCO_EXCEPTION_WHEN_PROCESS') ||
2449: procedure ProcessSCOAcceptance(p_change_request_group_id in number,p_launch_approvals_flag IN VARCHAR2 default 'N') is
2450: pragma AUTONOMOUS_TRANSACTION;
2451:
2452: x_progress varchar2(100):= '000';
2453: l_request_level PO_CHANGE_REQUESTS.request_level%type;
2454: l_document_type PO_CHANGE_REQUESTS.document_type%type;
2455: l_document_id number;
2456: l_document_line_id number;
2457: l_document_line_id1 number:=null;
2450: pragma AUTONOMOUS_TRANSACTION;
2451:
2452: x_progress varchar2(100):= '000';
2453: l_request_level PO_CHANGE_REQUESTS.request_level%type;
2454: l_document_type PO_CHANGE_REQUESTS.document_type%type;
2455: l_document_id number;
2456: l_document_line_id number;
2457: l_document_line_id1 number:=null;
2458: l_document_shipment_id number;
2458: l_document_shipment_id number;
2459: l_document_distribution_id number;
2460: l_release_id number;
2461: l_change_request_id number;
2462: l_request_status PO_CHANGE_REQUESTS.request_status%type;
2463: l_requester_id number;
2464: l_return_code number;
2465: l_err_msg varchar2(200);
2466: l_return_msg varchar2(2000);
2464: l_return_code number;
2465: l_err_msg varchar2(200);
2466: l_return_msg varchar2(2000);
2467: l_document_subtype varchar2(100);
2468: l_change_reason PO_CHANGE_REQUESTS.request_reason%TYPE;
2469: l_return_status varchar2(1);
2470:
2471: l_old_need_by_date DATE;
2472: l_new_need_by_date DATE;
2495:
2496: /* this is for the date change request id
2497: which isnot tied by parent request id
2498: */
2499: l_date_change_id PO_CHANGE_REQUESTS.change_request_id%type;
2500: l_date_change_id1 PO_CHANGE_REQUESTS.change_request_id%type;
2501:
2502: -- Added variable l_validation_error
2503: l_validation_error PO_CHANGE_REQUESTS.VALIDATION_ERROR%type;
2496: /* this is for the date change request id
2497: which isnot tied by parent request id
2498: */
2499: l_date_change_id PO_CHANGE_REQUESTS.change_request_id%type;
2500: l_date_change_id1 PO_CHANGE_REQUESTS.change_request_id%type;
2501:
2502: -- Added variable l_validation_error
2503: l_validation_error PO_CHANGE_REQUESTS.VALIDATION_ERROR%type;
2504: l_doc_check_rec POS_ERR_TYPE;
2499: l_date_change_id PO_CHANGE_REQUESTS.change_request_id%type;
2500: l_date_change_id1 PO_CHANGE_REQUESTS.change_request_id%type;
2501:
2502: -- Added variable l_validation_error
2503: l_validation_error PO_CHANGE_REQUESTS.VALIDATION_ERROR%type;
2504: l_doc_check_rec POS_ERR_TYPE;
2505:
2506: l_temp_date date;
2507: l_temp_reason varchar2(1000);
2518: old_expiration_date, new_expiration_date,
2519: old_amount, new_amount,
2520: old_currency_amount, new_currency_amount,
2521: change_request_group_id
2522: from po_change_requests
2523: where change_request_group_id in
2524: (select parent_change_request_id
2525: from po_change_requests
2526: where change_request_group_id=p_change_request_group_id
2521: change_request_group_id
2522: from po_change_requests
2523: where change_request_group_id in
2524: (select parent_change_request_id
2525: from po_change_requests
2526: where change_request_group_id=p_change_request_group_id
2527: and request_status='BUYER_APP'
2528: and action_type='MODIFICATION')
2529: order by document_line_id, document_distribution_id;
2528: and action_type='MODIFICATION')
2529: order by document_line_id, document_distribution_id;
2530:
2531: cursor l_document_id_csr is
2532: select document_type, document_header_id, po_release_id, nvl(requester_id, created_by) from po_change_requests
2533: where change_request_group_id =p_change_request_group_id;
2534:
2535: BEGIN
2536:
2583:
2584: begin
2585: select new_price, new_currency_unit_price
2586: into l_new_price1, l_new_currency_unit_price1
2587: from po_change_requests
2588: where change_request_group_id=l_group_id
2589: and document_line_id=l_document_line_id
2590: and action_type='DERIVED'
2591: and new_price is not null;
2616: into l_new_start_date,
2617: l_new_expiration_date,
2618: l_date_change_id1,
2619: l_date_change_id
2620: from po_change_requests pcr1, po_change_requests pcr2
2621: where pcr1.change_request_group_id = pcr2.change_request_group_id
2622: -- and pcr1.change_request_id in
2623: and pcr1.change_request_group_id in
2624: (select parent_change_request_id
2621: where pcr1.change_request_group_id = pcr2.change_request_group_id
2622: -- and pcr1.change_request_id in
2623: and pcr1.change_request_group_id in
2624: (select parent_change_request_id
2625: from po_change_requests
2626: where change_request_group_id=p_change_request_group_id
2627: and request_status='BUYER_APP'
2628: and action_type='MODIFICATION')
2629: and (pcr2.new_start_date is not null
2710: pcr.responded_by
2711: into l_temp_date,
2712: l_temp_reason,
2713: l_temp_responder
2714: from po_change_requests pcr
2715: where pcr.change_request_group_id=p_change_request_group_id
2716: and pcr.action_type='MODIFICATION'
2717: and rownum=1;
2718:
2715: where pcr.change_request_group_id=p_change_request_group_id
2716: and pcr.action_type='MODIFICATION'
2717: and rownum=1;
2718:
2719: update PO_CHANGE_REQUESTS pcr1
2720: set (pcr1.request_status,
2721: pcr1.change_active_flag,
2722: pcr1.response_date,
2723: pcr1.response_reason,
2735: sysdate
2736: from dual)
2737: where pcr1.change_request_group_id in
2738: (select parent_change_request_id
2739: from po_change_requests
2740: where change_request_group_id=p_change_request_group_id
2741: and action_type='MODIFICATION');
2742:
2743: if (l_date_change_id is not null) then
2740: where change_request_group_id=p_change_request_group_id
2741: and action_type='MODIFICATION');
2742:
2743: if (l_date_change_id is not null) then
2744: update PO_CHANGE_REQUESTS pcr1
2745: set (pcr1.request_status,
2746: pcr1.change_active_flag,
2747: pcr1.response_date,
2748: pcr1.response_reason,
2757: pcr2.responded_by,
2758: fnd_global.user_id,
2759: fnd_global.login_id,
2760: sysdate
2761: from po_change_requests pcr2
2762: where pcr2.parent_change_request_id=l_date_change_id1
2763: and pcr2.change_request_group_id=p_change_request_group_id
2764: and pcr2.action_type='MODIFICATION'
2765: and rownum=1)
2773:
2774: when FND_API.g_exc_error then
2775: rollback to PO_REQCHANGEREQUESTWF_PVT_SP;
2776:
2777: update PO_CHANGE_REQUESTS
2778: set request_status='REJECTED',
2779: change_active_flag='N',
2780: response_date=sysdate,
2781: response_reason=fnd_message.get_string('PO', 'PO_RCO_EXCEPTION_WHEN_PROCESS'),
2780: response_date=sysdate,
2781: response_reason=fnd_message.get_string('PO', 'PO_RCO_EXCEPTION_WHEN_PROCESS'),
2782: validation_error=l_err_msg
2783: where change_request_group_id in (select parent_change_request_id
2784: from po_change_requests
2785: where change_request_group_id=p_change_request_group_id
2786: and action_type='MODIFICATION');
2787:
2788: update PO_CHANGE_REQUESTS
2784: from po_change_requests
2785: where change_request_group_id=p_change_request_group_id
2786: and action_type='MODIFICATION');
2787:
2788: update PO_CHANGE_REQUESTS
2789: set request_status='REJECTED',
2790: change_active_flag='N',
2791: response_date=sysdate,
2792: response_reason=fnd_message.get_string('PO', 'PO_RCO_EXCEPTION_WHEN_PROCESS'),
2825: x_progress varchar2(3):= '000';
2826:
2827: BEGIN
2828:
2829: update PO_CHANGE_REQUESTS
2830: set last_updated_by = fnd_global.user_id,
2831: last_update_login = fnd_global.login_id,
2832: last_update_date = sysdate,
2833: request_status=p_request_status,
2907: l_converted_quantity number;
2908:
2909: -- Bug # 3862383
2910: -- htank, 06-Oct-2004
2911: -- Requester_ID was not inserted into the po_change_requests table where document_type = 'PO'
2912: -- Due to this Approval History for PO was showing wrong name in the Change Request event
2913: l_requester_id number; -- Bug # 3862383
2914:
2915: -- SQL What: get all the elements required to generate
2963: max(pll.amount) ship_amount,
2964: to_number(null) dist_amount,
2965: max(prl.matching_basis),
2966: max(pcr.requester_id) requester_id -- Bug # 3862383
2967: from po_change_requests pcr,
2968: po_line_locations_all pll,
2969: po_requisition_lines_all prl,
2970: po_headers_all poh,
2971: po_releases_all por,
3018: pll.amount ship_amount,
3019: to_number(null) dist_amount,
3020: prl.matching_basis,
3021: pcr.requester_id requester_id -- Bug # 3862383
3022: from po_change_requests pcr,
3023: po_line_locations_all pll,
3024: po_requisition_lines_all prl,
3025: po_headers_all poh,
3026: po_releases_all por,
3073: pll.amount ship_amount,
3074: pod.amount_ordered dist_amount,
3075: prl.matching_basis,
3076: pcr.requester_id requester_id -- Bug # 3862383
3077: from po_change_requests pcr,
3078: po_line_locations_all pll,
3079: po_requisition_lines_all prl,
3080: po_headers_all poh,
3081: po_releases_all por,
3313: ValidateAndSaveRequest(
3314: p_po_header_id =>l_document_header_id,
3315: p_po_release_id =>l_po_release_id,
3316: p_revision_num =>l_document_revision_num,
3317: p_po_change_requests =>my_chg_rec_tbl);
3318:
3319: temp:=1;
3320: my_chg_rec_tbl := pos_chg_rec_tbl();
3321: l_current_header_id:=l_document_header_id;
3470: ValidateAndSaveRequest(
3471: p_po_header_id =>l_document_header_id,
3472: p_po_release_id =>l_po_release_id,
3473: p_revision_num =>l_document_revision_num,
3474: p_po_change_requests =>my_chg_rec_tbl);
3475: commit;
3476:
3477: EXCEPTION
3478: WHEN OTHERS THEN
3511: if(p_request_status='MGR_APP') then
3512:
3513: x_progress := '001';
3514:
3515: update PO_CHANGE_REQUESTS
3516: set last_updated_by = fnd_global.user_id,
3517: last_update_login = fnd_global.login_id,
3518: last_update_date = sysdate,
3519: request_status='MGR_APP'
3528: if(p_request_status='MGR_APP') then
3529:
3530: x_progress := '003';
3531:
3532: update PO_CHANGE_REQUESTS
3533: set last_updated_by = fnd_global.user_id,
3534: last_update_login = fnd_global.login_id,
3535: last_update_date = sysdate,
3536: request_status='MGR_APP'
3543: elsif(p_request_status='MGR_PRE_APP') then
3544:
3545: x_progress := '005';
3546:
3547: update PO_CHANGE_REQUESTS
3548: set last_updated_by = fnd_global.user_id,
3549: last_update_login = fnd_global.login_id,
3550: last_update_date = sysdate,
3551: request_status='MGR_PRE_APP'
3556: x_progress := '006';
3557: else -- reject
3558: x_progress := '005';
3559:
3560: update PO_CHANGE_REQUESTS
3561: set last_updated_by = fnd_global.user_id,
3562: last_update_login = fnd_global.login_id,
3563: last_update_date = sysdate,
3564: request_status='REJECTED',
3590: * Private Procedure: SetReqChangeFlag
3591: * Effects: set the change_pending_flag in requisition headers table
3592: * if the flag is set to 'Y', which means there is pending
3593: * change coming, also store the itemtype and itemkey to
3594: * po_change_requests table.
3595: *
3596: * the process will commit when it exits.
3597: *
3598: * Returns:
3618:
3619: x_progress:='001';
3620:
3621: if(p_change_flag='Y') then
3622: update po_change_requests
3623: set wf_item_type=p_itemtype,
3624: wf_item_key=p_itemkey
3625: where change_request_group_id=p_change_request_group_id;
3626: end if;
3920: last_update_login = fnd_global.login_id
3921: WHERE
3922: line_location_id in
3923: (select document_line_location_id
3924: from po_change_requests
3925: where
3926: request_level = 'SHIPMENT' and
3927: document_header_id = p_document_id and
3928: action_type IN ('MODIFICATION', 'CANCELLATION') and
3977: last_update_date = sysdate,
3978: last_updated_by = fnd_global.user_id,
3979: last_update_login = fnd_global.login_id
3980: WHERE line_location_id in (select document_line_location_id
3981: from po_change_requests
3982: where request_level = 'SHIPMENT' and
3983: po_release_id = p_document_id and
3984: action_type IN ('MODIFICATION', 'CANCELLATION') and
3985: initiator = 'REQUESTER' and
4024: l_responsibility_id number;
4025:
4026: cursor get_parent_info_csr(l_change_request_group_id number) is
4027: select pcr.wf_item_type, pcr.wf_item_key
4028: from po_change_requests pcr, po_change_requests pcr2
4029: where pcr2.change_request_group_id=l_change_request_group_id
4030: and pcr.change_request_id=pcr2.parent_change_request_id
4031: and pcr.wf_item_type is not null;
4032:
4091: l_count number;
4092: item_key wf_items.item_key%type;
4093: item_type wf_items.item_type%type:='POREQCHA';
4094: l_document_id number;
4095: l_document_number po_change_requests.document_num%type;
4096: l_org_id number;
4097: l_requester_id number;
4098: l_requester_name wf_roles.name%type;
4099: l_requester_display_name wf_roles.display_name%type;
4102: l_total_amount_dsp varchar2(100);
4103: cursor change_request(l_change_request_group_id number) is
4104: select document_header_id, nvl(pcr.requester_id, por.preparer_id),
4105: document_num
4106: from po_change_requests pcr, po_requisition_headers_all por
4107: where pcr.change_request_group_id=l_change_request_group_id
4108: and pcr.document_header_id=por.requisition_header_id;
4109:
4110: l_old_req_amount varchar2(40);
4290: l_amount_for_header varchar2(400);
4291: l_amount_for_tax varchar2(400);
4292:
4293: --Bug#5114191
4294: l_inform_item_type PO_CHANGE_REQUESTS.wf_item_type%type;
4295: l_inform_item_key PO_CHANGE_REQUESTS.wf_item_key%type;
4296: l_responder_user_id number;
4297: l_responder_resp_id number;
4298: l_responder_appl_id number;
4291: l_amount_for_tax varchar2(400);
4292:
4293: --Bug#5114191
4294: l_inform_item_type PO_CHANGE_REQUESTS.wf_item_type%type;
4295: l_inform_item_key PO_CHANGE_REQUESTS.wf_item_key%type;
4296: l_responder_user_id number;
4297: l_responder_resp_id number;
4298: l_responder_appl_id number;
4299:
4306: document_revision_num,
4307: po_release_id,
4308: wf_item_type,
4309: wf_item_key
4310: from po_change_requests
4311: where change_request_group_id=l_change_request_group_id;
4312:
4313: --SQL What: get the preparer id of the req
4314: --SQL Why: wf attribute needs it
4315: --SQL Join: parent_change_request_id, requisition_header_id
4316: cursor req_preparer_id(l_change_request_group_id number) is
4317: select por.preparer_id
4318: from po_requisition_headers_all por,
4319: po_change_requests pcr1,
4320: po_change_requests pcr2
4321: where pcr2.change_request_group_id=l_change_request_group_id
4322: and pcr2.parent_change_request_id=pcr1.change_request_id
4323: and pcr1.document_header_id=por.requisition_header_id;
4316: cursor req_preparer_id(l_change_request_group_id number) is
4317: select por.preparer_id
4318: from po_requisition_headers_all por,
4319: po_change_requests pcr1,
4320: po_change_requests pcr2
4321: where pcr2.change_request_group_id=l_change_request_group_id
4322: and pcr2.parent_change_request_id=pcr1.change_request_id
4323: and pcr1.document_header_id=por.requisition_header_id;
4324:
4323: and pcr1.document_header_id=por.requisition_header_id;
4324:
4325: cursor get_parent_group_id_csr(l_change_request_group_id number) is
4326: select change_request_group_id
4327: from po_change_requests
4328: where change_request_id in
4329: (select parent_change_request_id
4330: from po_change_requests
4331: where change_request_group_id=l_change_request_group_id);
4326: select change_request_group_id
4327: from po_change_requests
4328: where change_request_id in
4329: (select parent_change_request_id
4330: from po_change_requests
4331: where change_request_group_id=l_change_request_group_id);
4332:
4333: cursor get_parent_info_csr(l_change_request_group_id number) is
4334: select prh.segment1, pcr.wf_item_type, pcr.wf_item_key, prh.requisition_header_id
4331: where change_request_group_id=l_change_request_group_id);
4332:
4333: cursor get_parent_info_csr(l_change_request_group_id number) is
4334: select prh.segment1, pcr.wf_item_type, pcr.wf_item_key, prh.requisition_header_id
4335: from po_requisition_headers_all prh, po_change_requests pcr
4336: where prh.requisition_header_id=pcr.document_header_id
4337: and pcr.change_request_group_id=l_change_request_group_id;
4338:
4339: n_varname Wf_Engine.NameTabTyp;
4658: END IF;
4659:
4660: x_progress :='StartPOChangeWF:011';
4661: if(p_process='INFORM_BUYER_PO_CHANGE') then
4662: update po_change_requests
4663: set wf_item_type=item_type,
4664: wf_item_key=item_key
4665: where change_request_group_id=p_change_request_group_id;
4666: end if;
4745: l_preparer_user_name varchar2(100);
4746:
4747: cursor change_request_group_id is
4748: select max(change_request_group_id)
4749: from po_change_requests
4750: where document_header_id=l_doc_id
4751: and initiator='REQUESTER'
4752: and request_status='NEW';
4753: BEGIN
4936: l_path_id number;
4937: l_change_request_group_id number;
4938: cursor change_request is
4939: select requester_id
4940: from po_change_requests
4941: where change_request_group_id=l_change_request_group_id;
4942:
4943:
4944: BEGIN
5055:
5056: l_doc_string varchar2(200);
5057: l_preparer_user_name varchar2(100);
5058:
5059: l_approval_flag po_change_requests.approval_required_flag%type;
5060:
5061: cursor approval_flag is
5062: select approval_required_flag
5063: from po_change_requests
5059: l_approval_flag po_change_requests.approval_required_flag%type;
5060:
5061: cursor approval_flag is
5062: select approval_required_flag
5063: from po_change_requests
5064: where change_request_group_id=l_change_request_group_id
5065: and action_type='MODIFICATION'
5066: and request_status = 'NEW';
5067:
5315: l_preparer_user_name varchar2(100);
5316:
5317: cursor accepted_change is
5318: select change_request_id
5319: from po_change_requests
5320: where change_request_group_id=l_change_request_group_id
5321: and request_status='MGR_PRE_APP';
5322:
5323: BEGIN
5553: l_preparer_user_name varchar2(100);
5554:
5555: cursor change_request_status is
5556: select request_status
5557: from po_change_requests
5558: where change_request_group_id=l_change_request_group_id
5559: and request_status='MGR_APP';
5560: BEGIN
5561:
5727: l_sequence_num number;
5728:
5729: cursor l_approved_change_request_csr(grp_id number) is
5730: select change_request_id
5731: from po_change_requests
5732: where change_request_group_id=grp_id
5733: and request_status='MGR_APP'
5734: and action_type='MODIFICATION';
5735: cursor l_rejected_change_request_csr(grp_id number) is
5733: and request_status='MGR_APP'
5734: and action_type='MODIFICATION';
5735: cursor l_rejected_change_request_csr(grp_id number) is
5736: select change_request_id
5737: from po_change_requests
5738: where change_request_group_id=grp_id
5739: and request_status='REJECTED'
5740: and action_type='MODIFICATION';
5741: cursor l_approved_cancel_request_csr(grp_id number) is
5739: and request_status='REJECTED'
5740: and action_type='MODIFICATION';
5741: cursor l_approved_cancel_request_csr(grp_id number) is
5742: select change_request_id
5743: from po_change_requests
5744: where change_request_group_id=grp_id
5745: and request_status='MGR_APP'
5746: and action_type='CANCELLATION';
5747:
6033: l_preparer_user_name varchar2(100);
6034:
6035: cursor child_request_group_id is
6036: select distinct pcr1.change_request_group_id
6037: from po_change_requests pcr1, po_change_requests pcr2
6038: where pcr1.parent_change_request_id=pcr2.change_request_id
6039: and pcr2.change_request_group_id=l_change_request_group_id
6040: and pcr1.request_status='PENDING';
6041:
6267: nvl(pcr1.new_price, prl.unit_price)*
6268: nvl(pcr2.new_quantity, prl.quantity)))), 0)
6269: into l_new_tax_amount, l_new_req_amount
6270: from po_requisition_lines_all prl,
6271: po_change_requests pcr1,
6272: po_change_requests pcr2,
6273: po_change_requests pcr3,
6274: po_change_requests pcr4
6275: where prl.requisition_line_id=pcr1.document_line_id(+)
6268: nvl(pcr2.new_quantity, prl.quantity)))), 0)
6269: into l_new_tax_amount, l_new_req_amount
6270: from po_requisition_lines_all prl,
6271: po_change_requests pcr1,
6272: po_change_requests pcr2,
6273: po_change_requests pcr3,
6274: po_change_requests pcr4
6275: where prl.requisition_line_id=pcr1.document_line_id(+)
6276: and pcr1.change_request_group_id(+)=l_change_request_group_id
6269: into l_new_tax_amount, l_new_req_amount
6270: from po_requisition_lines_all prl,
6271: po_change_requests pcr1,
6272: po_change_requests pcr2,
6273: po_change_requests pcr3,
6274: po_change_requests pcr4
6275: where prl.requisition_line_id=pcr1.document_line_id(+)
6276: and pcr1.change_request_group_id(+)=l_change_request_group_id
6277: and pcr1.request_level(+)='LINE'
6270: from po_requisition_lines_all prl,
6271: po_change_requests pcr1,
6272: po_change_requests pcr2,
6273: po_change_requests pcr3,
6274: po_change_requests pcr4
6275: where prl.requisition_line_id=pcr1.document_line_id(+)
6276: and pcr1.change_request_group_id(+)=l_change_request_group_id
6277: and pcr1.request_level(+)='LINE'
6278: and pcr1.new_price(+) is not null
6306: nvl(pcr2.new_quantity,prd.req_line_quantity))), 0)
6307: into l_new_tax_amount, l_new_req_amount
6308: from po_requisition_lines_all prl,
6309: po_req_distributions_all prd,
6310: po_change_requests pcr1,
6311: po_change_requests pcr2
6312: where prl.requisition_line_id=pcr1.document_line_id(+)
6313: and pcr1.change_request_group_id(+)=l_change_request_group_id
6314: and pcr1.request_level(+)='LINE'
6307: into l_new_tax_amount, l_new_req_amount
6308: from po_requisition_lines_all prl,
6309: po_req_distributions_all prd,
6310: po_change_requests pcr1,
6311: po_change_requests pcr2
6312: where prl.requisition_line_id=pcr1.document_line_id(+)
6313: and pcr1.change_request_group_id(+)=l_change_request_group_id
6314: and pcr1.request_level(+)='LINE'
6315: and pcr1.change_active_flag(+)='Y'
6449:
6450: --bug 5440657
6451: -- In 'signature required' case, after buyer accepts the change request,
6452: -- the authorization_status of corresponding PO/REALEASE will be 'pre_approved'(pending signature)
6453: -- This way,is_doc_approved should return 'Y' so that PO change requests will be updated to 'ACCEPTED' status later.
6454:
6455: if(nvl(l_authorization_status, 'IN PROCESS') in ('APPROVED','PRE-APPROVED') ) then
6456: resultout := wf_engine.eng_completed || ':' || 'Y' ;
6457:
6899: l_path_id number;
6900: l_change_request_group_id number;
6901: cursor change_request is
6902: select request_status
6903: from po_change_requests
6904: where change_request_group_id=l_change_request_group_id;
6905:
6906:
6907: BEGIN
6921: aname => 'CHANGE_REQUEST_GROUP_ID');
6922:
6923: select count(distinct(request_status))
6924: into l_count
6925: from po_change_requests
6926: where change_request_group_id=l_change_request_group_id;
6927:
6928: if(l_count=1) then
6929: open change_request;
7169: *
7170: * Public Procedure: Change_Acceptance_Exists
7171: * Effects: workflow procedure, used in PORPOCHA(PROCESS_BUYER_RESPONSE)
7172: *
7173: * check if there is PO change requests with status 'BUYER_APP'
7174: * exists.
7175: *
7176: ************************************************************************/
7177: procedure Change_Acceptance_Exists(itemtype in varchar2,
7190: l_preparer_user_name varchar2(100);
7191:
7192: cursor accepted_change is
7193: select change_request_id
7194: from po_change_requests
7195: where change_request_group_id=l_change_request_group_id
7196: and request_status='BUYER_APP';
7197:
7198: BEGIN
7378: l_exception exception;
7379:
7380: cursor document_id is
7381: select document_header_id, po_release_id
7382: from po_change_requests
7383: where change_request_group_id=l_change_request_group_id
7384: and request_status='BUYER_APP';
7385:
7386: --Set the authorization status of po to
7387: --approved for those change request automatically rejected
7388:
7389: cursor document_id_rejected is
7390: select document_header_id, po_release_id
7391: from po_change_requests
7392: where change_request_group_id=l_change_request_group_id
7393: and request_status='REJECTED';
7394:
7395: BEGIN
7510: --SQL Why: need it to check whether the parent change is fully responded
7511: --SQL Join: parent_change_request_id
7512: cursor pending_parent_change_csr(p_change_request_group_id number) is
7513: select pcr3.change_request_id
7514: from po_change_requests pcr1,
7515: po_change_requests pcr2,
7516: po_change_requests pcr3
7517: where pcr2.change_request_group_id=p_change_request_group_id
7518: and pcr2.parent_change_request_id=pcr1.change_request_id
7511: --SQL Join: parent_change_request_id
7512: cursor pending_parent_change_csr(p_change_request_group_id number) is
7513: select pcr3.change_request_id
7514: from po_change_requests pcr1,
7515: po_change_requests pcr2,
7516: po_change_requests pcr3
7517: where pcr2.change_request_group_id=p_change_request_group_id
7518: and pcr2.parent_change_request_id=pcr1.change_request_id
7519: and pcr1.change_request_group_id=pcr3.change_request_group_id
7512: cursor pending_parent_change_csr(p_change_request_group_id number) is
7513: select pcr3.change_request_id
7514: from po_change_requests pcr1,
7515: po_change_requests pcr2,
7516: po_change_requests pcr3
7517: where pcr2.change_request_group_id=p_change_request_group_id
7518: and pcr2.parent_change_request_id=pcr1.change_request_id
7519: and pcr1.change_request_group_id=pcr3.change_request_group_id
7520: and pcr3.action_type in ('MODIFICATION', 'CANCELLATION')
7725: x_progress := 'PO_ReqChangeRequestWF_PVT.Get_Total_Amount_Currency: 03';
7726:
7727: SELECT wf_item_type, wf_item_key
7728: INTO l_req_item_type, l_req_item_key
7729: FROM po_change_requests
7730: WHERE
7731: change_request_group_id = l_req_change_group_id and rownum=1;
7732:
7733: --bug 5379796,if POREQCHA wf has been purged before this moment,
7897:
7898: else
7899: SELECT document_header_id
7900: INTO l_document_id
7901: FROM po_change_requests pcr
7902: WHERE pcr.change_request_group_id = l_req_change_group_id and rownum=1;
7903:
7904: PO_WF_UTIL_PKG.SetItemAttrNumber ( itemtype => itemtype,
7905: itemkey => itemkey,
8020: l_preparer_user_name varchar2(100);
8021:
8022: cursor parent_change(p_change_request_group_id number) is
8023: select pcr1.change_request_group_id
8024: from po_change_requests pcr1, po_change_requests pcr2
8025: where pcr2.change_request_group_id=p_change_request_group_id
8026: and pcr2.parent_change_request_id=pcr1.change_request_id;
8027:
8028: -- this is call from child po, check for parent req
8027:
8028: -- this is call from child po, check for parent req
8029: cursor pending_change(p_change_request_group_id number) is
8030: select pcr1.change_request_id
8031: from po_change_requests pcr1
8032: where pcr1.change_request_group_id=p_change_request_group_id
8033: and pcr1.request_status not in ('ACCEPTED', 'REJECTED');
8034:
8035: cursor pending_child_change(p_change_request_group_id number) is
8033: and pcr1.request_status not in ('ACCEPTED', 'REJECTED');
8034:
8035: cursor pending_child_change(p_change_request_group_id number) is
8036: select pcr1.change_request_id
8037: from po_change_requests pcr1, po_change_requests pcr2
8038: where pcr2.change_request_group_id=p_change_request_group_id
8039: and pcr1.parent_change_request_id=pcr2.change_request_id
8040: and pcr1.request_status not in ('ACCEPTED', 'REJECTED');
8041:
8141: l_po_chg_itemkey wf_items.item_key%type;
8142:
8143: cursor l_get_group_id_po_csr is
8144: select change_request_group_id, wf_item_type, wf_item_key
8145: from po_change_requests
8146: where document_header_id=l_document_id
8147: and document_type=l_document_type
8148: and initiator='REQUESTER'
8149: and request_status in ('PENDING', 'BUYER_APP');
8148: and initiator='REQUESTER'
8149: and request_status in ('PENDING', 'BUYER_APP');
8150: cursor l_get_group_id_rel_csr is
8151: select change_request_group_id, wf_item_type, wf_item_key
8152: from po_change_requests
8153: where po_release_id=l_document_id
8154: and document_type=l_document_type
8155: and initiator='REQUESTER'
8156: and request_status in ('PENDING', 'BUYER_APP');
8155: and initiator='REQUESTER'
8156: and request_status in ('PENDING', 'BUYER_APP');
8157: cursor l_get_parent_group_id_csr is
8158: select change_request_group_id
8159: from po_change_requests
8160: where change_request_id in
8161: (select parent_change_request_id
8162: from po_change_requests
8163: where change_request_group_id=l_change_request_group_id);
8158: select change_request_group_id
8159: from po_change_requests
8160: where change_request_id in
8161: (select parent_change_request_id
8162: from po_change_requests
8163: where change_request_group_id=l_change_request_group_id);
8164: cursor change_request(l_change_request_group_id number) is
8165: select nvl(pcr.requester_id, por.preparer_id)
8166: from po_change_requests pcr, po_requisition_headers_all por
8162: from po_change_requests
8163: where change_request_group_id=l_change_request_group_id);
8164: cursor change_request(l_change_request_group_id number) is
8165: select nvl(pcr.requester_id, por.preparer_id)
8166: from po_change_requests pcr, po_requisition_headers_all por
8167: where pcr.change_request_group_id=l_change_request_group_id
8168: and pcr.document_header_id=por.requisition_header_id;
8169:
8170: BEGIN
8256: l_preparer_user_name varchar2(100);
8257:
8258: cursor new_change(p_change_request_group_id number) is
8259: select change_request_id
8260: from po_change_requests
8261: where change_request_group_id=p_change_request_group_id
8262: and request_status='PENDING';
8263:
8264: BEGIN
8326: * Public Procedure: Record_PO_Approval
8327: * Effects: workflow procedure, used in PORPOCHA(RECEIVE_REQ_CHANGE_EVENT)
8328: *
8329: * when the po is approved, update the status of the corresponding
8330: * PO change requests to ACCEPTED
8331: *
8332: ************************************************************************/
8333: procedure Record_PO_Approval(itemtype in varchar2,
8334: itemkey in varchar2,
8392: * Public Procedure: Record_PO_Rejection
8393: * Effects: workflow procedure, used in PORPOCHA(RECEIVE_REQ_CHANGE_EVENT)
8394: *
8395: * when the po is rejected, update the status of the corresponding
8396: * PO change requests to REJECTED
8397: *
8398: ************************************************************************/
8399: procedure Record_PO_Rejection(itemtype in varchar2,
8400: itemkey in varchar2,
8543: l_change_request_id number:=null;
8544: x_progress varchar2(3) := '000';
8545: cursor l_pending_req_po_chg_csr is
8546: select change_request_id
8547: from po_change_requests
8548: where document_header_id=l_document_id
8549: and request_status in ('BUYER_APP', 'PENDING')
8550: and initiator='REQUESTER'
8551: and document_type='PO';
8550: and initiator='REQUESTER'
8551: and document_type='PO';
8552: cursor l_pending_req_rel_chg_csr is
8553: select change_request_id
8554: from po_change_requests
8555: where po_release_id=l_document_id
8556: and request_status in ('BUYER_APP', 'PENDING')
8557: and initiator='REQUESTER'
8558: and document_type='RELEASE';
8662:
8663: l_index number;
8664: l_last number;
8665: l_change_request_id number;
8666: l_action_type PO_CHANGE_REQUESTS.ACTION_TYPE%TYPE;
8667: l_change_request_group_id number;
8668:
8669: l_wf_item_type wf_items.item_type%TYPE;
8670: l_wf_item_key wf_items.item_key%type;
8680: l_api_version CONSTANT NUMBER := 1.0;
8681:
8682: cursor l_pending_change_csr(group_id in number) is
8683: select change_request_id
8684: from po_change_requests
8685: where change_request_group_id=group_id
8686: and request_status in ('NEW', 'MGR_PRE_APP');
8687: cursor l_pending_change_line_csr(requisition_line_id in number) is
8688: select pcr1.change_request_id, pcr1.document_header_id,
8687: cursor l_pending_change_line_csr(requisition_line_id in number) is
8688: select pcr1.change_request_id, pcr1.document_header_id,
8689: pcr1.action_type, pcr1.change_request_group_id,
8690: pcr1.wf_item_type, pcr1.wf_item_key
8691: from po_change_requests pcr1
8692: where pcr1.document_type='REQ'
8693: and pcr1.document_line_id=requisition_line_id
8694: and pcr1.action_type <>'DERIVED'
8695: and pcr1.request_status in ('NEW', 'MGR_PRE_APP', 'MGR_APP')
8694: and pcr1.action_type <>'DERIVED'
8695: and pcr1.request_status in ('NEW', 'MGR_PRE_APP', 'MGR_APP')
8696: and not exists
8697: (select pcr2.change_request_id
8698: from po_change_requests pcr2
8699: where pcr2.parent_change_request_id=pcr1.change_request_id);
8700: begin
8701: IF NOT FND_API.compatible_api_call(l_api_version, p_api_version,
8702: l_api_name, g_pkg_name) THEN
8727: -- accept the cancellation request
8728: update po_requisition_lines_all
8729: set cancel_flag='Y'
8730: where requisition_line_id=p_CanceledReqLineIDs_tbl(l_index);
8731: update po_change_requests
8732: set request_status='ACCEPTED',
8733: change_active_flag='N',
8734: response_date=sysdate,
8735: response_reason=fnd_message.get_string('PO', 'PO_RCO_PO_CANCELLED')
8751:
8752: else
8753: -- reject the change request
8754: -- it can have multiple records, so we can't use change request id
8755: update po_change_requests
8756: set request_status='REJECTED',
8757: change_active_flag='N',
8758: response_date=sysdate,
8759: response_reason=fnd_message.get_string('PO', 'PO_RCO_PO_CANCELLED')
8851: l_preparer_id number;
8852: l_req_num po_requisition_headers_all.segment1%type;
8853: cursor change_request_group_id is
8854: select max(change_request_group_id)
8855: from po_change_requests
8856: where document_header_id = p_req_header_id
8857: and initiator='REQUESTER'
8858: and request_status='NEW';
8859:
8941: l_responsibility_id number;
8942:
8943: cursor get_parent_info_csr(l_change_request_group_id number) is
8944: select pcr.wf_item_type, pcr.wf_item_key
8945: from po_change_requests pcr, po_change_requests pcr2
8946: where pcr2.change_request_group_id=l_change_request_group_id
8947: and pcr.change_request_id=pcr2.parent_change_request_id
8948: and pcr.wf_item_type is not null;
8949:
9008: END IF;
9009:
9010: x_return_status := FND_API.g_ret_sts_success;
9011:
9012: update po_change_requests
9013: set request_status=p_acceptance_flag,
9014: change_active_flag=decode(p_acceptance_flag, 'REJECTED', 'N', 'Y'),
9015: response_date=sysdate,
9016: response_reason=p_response_reason,
9018: where change_request_id=p_change_request_id;
9019:
9020: select wf_item_type, wf_item_key
9021: into l_item_type, l_item_key
9022: from po_change_requests
9023: where change_request_id=p_change_request_id;
9024:
9025: begin
9026: open l_notif_status_csr('NEW_PO_CHANGE');
9085:
9086:
9087: cursor get_parent_info_csr(l_change_request_group_id number) is
9088: select pcr.wf_item_type, pcr.wf_item_key
9089: from po_change_requests pcr, po_change_requests pcr2
9090: where pcr2.change_request_group_id=l_change_request_group_id
9091: and pcr.change_request_id=pcr2.parent_change_request_id
9092: and pcr.wf_item_type is not null;
9093: begin
9252: x_progress := '001';
9253:
9254: select max(change_request_id)
9255: into l_change_request_id
9256: from po_change_requests
9257: where po_release_id=l_document_id
9258: and request_status in ('BUYER_APP', 'PENDING')
9259: and initiator='REQUESTER'
9260: and document_type='RELEASE'
9264: x_progress := '002';
9265:
9266: select max(change_request_id)
9267: into l_change_request_id
9268: from po_change_requests
9269: where document_header_id=l_document_id
9270: and request_status in ('BUYER_APP', 'PENDING')
9271: and initiator='REQUESTER'
9272: and document_type='PO'
9354: pcr.action_type,
9355: pol.item_id,
9356: pll.unit_meas_lookup_code,
9357: pol.unit_price
9358: FROM po_change_requests pcr, po_lines_all pol,
9359: po_line_locations_all pll
9360: WHERE pcr.change_request_group_id= p_change_request_group_id
9361: AND pcr.request_status IN ('PENDING', 'ACCEPTED')
9362: AND pcr.document_header_id=pol.po_header_id
9376: select poh.rate
9377: into l_rate
9378: from po_headers_all poh
9379: where poh.po_header_id in (
9380: select document_header_id from po_change_requests
9381: where change_request_group_id = p_change_request_group_id ) ;
9382:
9383:
9384: if (l_functional_currency_code <> p_po_currency) then
9422: if ( l_line_loc_id is null ) then
9423: Begin
9424: SELECT 1
9425: into l_shipment_chg_exists_amt
9426: FROM po_change_requests pcr
9427: WHERE pcr.change_request_group_id= p_change_request_group_id
9428: AND pcr.request_status IN ('PENDING', 'ACCEPTED')
9429: AND pcr.document_header_id= l_header_id
9430: AND pcr.document_line_id= l_line_id
9487:
9488: Begin
9489: SELECT 1
9490: into l_shipment_chg_exists_qty
9491: FROM po_change_requests pcr
9492: WHERE pcr.change_request_group_id= p_change_request_group_id
9493: AND pcr.request_status IN ('PENDING', 'ACCEPTED')
9494: AND pcr.document_header_id= l_header_id
9495: AND pcr.document_line_id= l_line_id
9694: l_change_request_group_id NUMBER;
9695: l_po_request_group_id NUMBER;
9696: l_document_header_id NUMBER;
9697: l_document_revision_num NUMBER;
9698: l_document_type po_change_requests.document_type%type;
9699: l_header_id NUMBER;
9700: l_release_id NUMBER;
9701: l_req_document_id NUMBER;
9702: x_progress VARCHAR2(3) := '000';
9710: x_progress := '001';
9711:
9712: select change_request_group_id
9713: INTO l_po_request_group_id
9714: FROM po_change_requests
9715: WHERE parent_change_request_id = l_change_request_group_id
9716: and rownum=1;
9717:
9718: x_progress := '002';
9725: --For Release the document_header_id will be null.Hence query the po_release_id
9726: --to set the release id to pass it to PO_CHANGEORDERWF_PVT.NotifySupAllChgRpdWF
9727: SELECT document_header_id, document_revision_num, document_type, po_release_id
9728: INTO l_document_header_id, l_document_revision_num, l_document_type, l_release_id
9729: FROM po_change_requests
9730: WHERE change_request_group_id = l_po_request_group_id
9731: AND rownum=1;
9732:
9733: x_progress := '004';
9787: x_progress := '002';
9788:
9789: SELECT change_request_group_id
9790: INTO l_po_chg_request_group_id
9791: FROM po_change_requests
9792: WHERE parent_change_request_id = l_change_request_group_id
9793: AND rownum=1;
9794:
9795: x_progress := '003';
9941: l_po_chg_group_id NUMBER;
9942: l_po_org_id NUMBER;
9943: l_progress VARCHAR2(100) := '000';
9944: l_creation_method po_headers_all.document_creation_method%TYPE;
9945: l_document_type po_change_requests.document_type%TYPE;
9946: BEGIN
9947:
9948: l_po_chg_group_id := PO_WF_UTIL_PKG.GetItemAttrNumber (
9949: itemtype => itemtype,
9964: SELECT poh.document_creation_method, poh.org_id
9965: INTO l_creation_method, l_po_org_id
9966: FROM
9967: po_headers_all poh,
9968: po_change_requests pcr
9969: WHERE poh.po_header_id =pcr.document_header_id
9970: AND pcr.change_request_group_id = l_po_chg_group_id
9971: AND rownum=1;
9972:
9977: SELECT por.document_creation_method, por.org_id
9978: INTO l_creation_method, l_po_org_id
9979: FROM
9980: po_releases_all por,
9981: po_change_requests pcr
9982: WHERE por.po_release_id = pcr.po_release_id
9983: AND pcr.change_request_group_id = l_po_chg_group_id
9984: AND rownum = 1;
9985:
10027: l_po_chg_request_group_id number;
10028: l_next_po_grp_id number;
10029: l_result varchar2(1) := 'N';
10030: l_change_request_grp_id number;
10031: l_po_document_type po_change_requests.document_type%type;
10032: l_po_document_rev po_change_requests.document_revision_num%type;
10033: l_po_document_id po_change_requests.document_header_id%type;
10034: l_progress VARCHAR2(100);
10035: BEGIN
10028: l_next_po_grp_id number;
10029: l_result varchar2(1) := 'N';
10030: l_change_request_grp_id number;
10031: l_po_document_type po_change_requests.document_type%type;
10032: l_po_document_rev po_change_requests.document_revision_num%type;
10033: l_po_document_id po_change_requests.document_header_id%type;
10034: l_progress VARCHAR2(100);
10035: BEGIN
10036: -- Do nothing in cancel or timeout mode
10029: l_result varchar2(1) := 'N';
10030: l_change_request_grp_id number;
10031: l_po_document_type po_change_requests.document_type%type;
10032: l_po_document_rev po_change_requests.document_revision_num%type;
10033: l_po_document_id po_change_requests.document_header_id%type;
10034: l_progress VARCHAR2(100);
10035: BEGIN
10036: -- Do nothing in cancel or timeout mode
10037: if (funcmode <> wf_engine.eng_run) then
10059:
10060: SELECT MIN(po_change.change_request_group_id)
10061: INTO l_next_po_grp_id
10062: FROM
10063: po_change_requests po_change,
10064: po_change_requests req_change
10065: WHERE
10066: po_change.parent_change_request_id = req_change.change_request_id AND
10067: req_change.change_request_group_id = l_change_request_grp_id AND
10060: SELECT MIN(po_change.change_request_group_id)
10061: INTO l_next_po_grp_id
10062: FROM
10063: po_change_requests po_change,
10064: po_change_requests req_change
10065: WHERE
10066: po_change.parent_change_request_id = req_change.change_request_id AND
10067: req_change.change_request_group_id = l_change_request_grp_id AND
10068: po_change.change_request_group_id > l_po_chg_request_group_id
10080:
10081: SELECT document_type, document_revision_num,
10082: decode(document_type, 'RELEASE', po_release_id,document_header_id)
10083: into l_po_document_type, l_po_document_rev, l_po_document_id
10084: FROM po_change_requests
10085: WHERE change_request_group_id = l_next_po_grp_id AND rownum=1;
10086:
10087: PO_WF_UTIL_PKG.SetItemAttrText( itemtype => itemtype,
10088: itemkey => itemkey,
10128: l_buyer_id number;
10129: l_buyer_name varchar2(100);
10130: l_buyer_display_name varchar2(300);
10131: l_order_date date;
10132: l_document_number po_change_requests.document_num%type;
10133: l_progress VARCHAR2(100);
10134: l_po_header_id number;
10135: l_po_release_id number;
10136: l_return_status varchar2(1);
10158: l_progress := 'PO_ReqChangeRequestWF_PVT.Accept_Po_Changes: 02';
10159:
10160: SELECT document_type, decode(document_type, 'RELEASE', po_release_id, document_header_id), document_num
10161: INTO l_document_type, l_document_id, l_document_number
10162: FROM po_change_requests
10163: WHERE change_request_group_id = l_po_change_request_group_id
10164: AND rownum=1;
10165:
10166: IF (l_document_type = 'RELEASE') THEN
10235: BEGIN
10236:
10237: SELECT document_header_id, po_release_id
10238: INTO l_po_header_id, l_po_release_id
10239: FROM po_change_requests
10240: WHERE change_request_group_id=l_po_change_request_group_id
10241: and request_status='BUYER_APP' and rownum=1;
10242: EXCEPTION
10243: WHEN NO_DATA_FOUND THEN
10395: FROM dual
10396: WHERE exists (
10397: SELECT 'N'
10398: FROM
10399: po_change_requests pcr, -- for quantity/amount change
10400: po_change_requests pcr1, -- for unit price change
10401: po_lines_all pl,
10402: po_distributions_all pod
10403: WHERE pl.po_line_id = pod.po_line_id
10396: WHERE exists (
10397: SELECT 'N'
10398: FROM
10399: po_change_requests pcr, -- for quantity/amount change
10400: po_change_requests pcr1, -- for unit price change
10401: po_lines_all pl,
10402: po_distributions_all pod
10403: WHERE pl.po_line_id = pod.po_line_id
10404: AND pcr.change_request_group_id = p_pochggrp_id
10473: l_progress varchar2(6);
10474:
10475: cursor l_line_info_csr is
10476: select distinct pcr.document_line_id, document_type
10477: from po_change_requests pcr,
10478: po_lines_all pol
10479: where change_request_group_id = p_pochggrp_id
10480: and pol.po_line_id = pcr.document_line_id
10481: and action_type <> 'DERIVED'
10494: INTO l_return_val
10495: FROM dual
10496: WHERE exists (
10497: SELECT 'N'
10498: FROM po_change_requests pcr
10499: WHERE change_request_group_id = p_pochggrp_id
10500: AND action_type='MODIFICATION'
10501: AND request_status not in ('ACCEPTED', 'REJECTED')
10502: AND request_level='SHIPMENT'
10525: INTO l_return_val
10526: FROM dual
10527: WHERE exists (
10528: SELECT 'N'
10529: FROM po_change_requests pcr
10530: WHERE change_request_group_id = p_pochggrp_id
10531: AND action_type='MODIFICATION'
10532: AND request_status not in ('ACCEPTED', 'REJECTED')
10533: AND request_level='LINE'
10655: BEGIN
10656:
10657: select distinct document_type
10658: into l_document_type
10659: from po_change_requests
10660: where change_request_group_id = p_pochggrp_id;
10661:
10662: if ( l_document_type = 'PO' ) then
10663:
10679: p_tolerances_tab(TOL_POTOTAL_IND).max_decrement,
10680: p_tolerances_tab(TOL_POTOTAL_AMT_IND).max_increment,
10681: p_tolerances_tab(TOL_POTOTAL_AMT_IND).max_decrement) INTO l_return_val
10682: FROM
10683: po_change_requests pcr,
10684: po_change_requests pcr1,
10685: po_change_requests pcr2,
10686: po_lines_all pl,
10687: po_distributions_all pod
10680: p_tolerances_tab(TOL_POTOTAL_AMT_IND).max_increment,
10681: p_tolerances_tab(TOL_POTOTAL_AMT_IND).max_decrement) INTO l_return_val
10682: FROM
10683: po_change_requests pcr,
10684: po_change_requests pcr1,
10685: po_change_requests pcr2,
10686: po_lines_all pl,
10687: po_distributions_all pod
10688: WHERE pl.po_line_id = pod.po_line_id
10681: p_tolerances_tab(TOL_POTOTAL_AMT_IND).max_decrement) INTO l_return_val
10682: FROM
10683: po_change_requests pcr,
10684: po_change_requests pcr1,
10685: po_change_requests pcr2,
10686: po_lines_all pl,
10687: po_distributions_all pod
10688: WHERE pl.po_line_id = pod.po_line_id
10689: AND pcr.change_request_group_id(+) = p_pochggrp_id
10719: pll.line_location_id)) ) )
10720:
10721: into l_old_amount_release, l_new_amount_release
10722:
10723: FROM po_change_requests pcr,
10724: po_lines_all pol,
10725: po_line_locations_all pll
10726: WHERE pcr.change_request_group_id= p_pochggrp_id
10727: AND pcr.po_release_id = p_poheader_id
10907: l_req_request_group_id NUMBER;
10908: l_po_request_group_id NUMBER;
10909: l_next_req_grp_id NUMBER;
10910: l_document_id NUMBER;
10911: l_document_num po_change_requests.document_num%type;
10912: l_result VARCHAR2(1) := 'N';
10913: l_progress VARCHAR2(100);
10914:
10915: BEGIN
10939:
10940: SELECT MIN(req_change.change_request_group_id)
10941: INTO l_next_req_grp_id
10942: FROM
10943: po_change_requests po_change,
10944: po_change_requests req_change
10945: WHERE
10946: po_change.change_request_group_id = l_po_request_group_id AND
10947: po_change.parent_change_request_id = req_change.change_request_id AND
10940: SELECT MIN(req_change.change_request_group_id)
10941: INTO l_next_req_grp_id
10942: FROM
10943: po_change_requests po_change,
10944: po_change_requests req_change
10945: WHERE
10946: po_change.change_request_group_id = l_po_request_group_id AND
10947: po_change.parent_change_request_id = req_change.change_request_id AND
10948: req_change.change_request_group_id > l_req_request_group_id;
10958: avalue => l_next_req_grp_id );
10959:
10960: SELECT document_header_id, document_num
10961: INTO l_document_id, l_document_num
10962: FROM po_change_requests
10963: WHERE change_request_group_id = l_next_req_grp_id AND rownum=1;
10964:
10965: PO_WF_UTIL_PKG.SetItemAttrNumber( itemtype => itemtype,
10966: itemkey => itemkey,
11157: decode(pol.matching_basis, 'AMOUNT',
11158: ( nvl(pll.amount,0)-nvl(pll.amount_cancelled,0) ),
11159: ( nvl(pll.quantity,0)- nvl(pll.quantity_cancelled,0)) * pol.unit_price
11160: )
11161: from po_change_requests,
11162: po_lines_all pol,
11163: po_line_locations_all pll
11164: where change_request_group_id = p_chg_request_grp_id
11165: and document_line_id = p_po_line_id
11203: if ( l_po_matching_basis = 'QUANTITY' ) then
11204: begin
11205: SELECT pcr.new_price,pcr.new_price
11206: into l_pcr_new_price,l_new_price
11207: FROM po_change_requests pcr
11208:
11209: WHERE pcr.change_request_group_id = p_chg_request_grp_id
11210: AND pcr.document_line_id = p_po_line_id
11211: AND pcr.action_type(+) = 'MODIFICATION'
11249:
11250: begin
11251: select (pcr.new_amount - pll.amount)
11252: into l_shipmt_amt_increase
11253: from po_change_requests pcr,
11254: po_line_locations_all pll
11255: where pcr.change_request_group_id = p_chg_request_grp_id
11256: and pcr.document_line_location_id = l_pcr_line_loc_id
11257: and pll.line_location_id = l_pcr_line_loc_id
11273: -- get new quantity
11274: begin
11275: SELECT pcr.new_quantity,pcr.new_quantity,pll.unit_meas_lookup_code,pll.price_override
11276: into l_pcr_new_quantity,l_new_quantity,l_unit_lookup_code,l_price_override
11277: FROM po_change_requests pcr,
11278: po_line_locations_all pll
11279: WHERE pcr.change_request_group_id = p_chg_request_grp_id
11280: AND pcr.document_line_location_id = l_pcr_line_loc_id
11281: AND pll.line_location_id = l_pcr_line_loc_id
11341:
11342: select decode(pol.matching_basis, 'AMOUNT',pol.amount,
11343: (nvl(pcr.new_price,pol.unit_price) * pol.quantity ))
11344: into l_new_amount
11345: from po_change_requests pcr,
11346: po_lines_all pol
11347: where pcr.change_request_group_id = p_chg_request_grp_id
11348: and pol.po_line_id = p_po_line_id
11349: and pcr.document_line_id = p_po_line_id
11411: p_source_type_code VARCHAR2(20) := 'INVENTORY';
11412: l_req_num po_requisition_headers_all.segment1%TYPE;
11413: CURSOR change_request_group_id IS
11414: SELECT MAX(change_request_group_id)
11415: FROM po_change_requests
11416: WHERE document_header_id = p_req_header_id
11417: AND initiator = 'REQUESTER'
11418: AND request_status = 'NEW';
11419:
11427: l_return_status varchar2(1);
11428: l_orgid number;
11429: CURSOR req_line_id_chn_csr(grp_id NUMBER) IS
11430: SELECT DISTINCT document_line_id
11431: FROM po_change_requests
11432: WHERE change_request_group_id = grp_id;
11433:
11434:
11435: BEGIN
11573: pcr.new_quantity,
11574: pcr.old_need_by_date,
11575: pcr.new_need_by_date,
11576: pcr.action_type
11577: FROM po_change_requests pcr,
11578: po_requisition_lines_all prl
11579: WHERE pcr.change_request_group_id = l_change_request_group_id
11580: AND pcr.request_status = 'MGR_APP'
11581: AND pcr.document_line_id = prl.requisition_line_id
11586:
11587: CURSOR iso_change_csr_rejected(l_change_request_group_id IN NUMBER) IS
11588: SELECT prl.requisition_header_id,
11589: prl.requisition_line_id
11590: FROM po_change_requests pcr,
11591: po_requisition_lines_all prl
11592: WHERE pcr.change_request_group_id = l_change_request_group_id
11593: AND pcr.request_status = 'REJECTED'
11594: AND pcr.document_line_id = prl.requisition_line_id
11687: --CHANGE_REQUEST_ID for the sendng of notification
11688:
11689: SELECT min(CHANGE_REQUEST_ID)
11690: into L_CHANGE_REQUEST_ID
11691: FROM po_change_requests
11692: WHERE change_request_group_id = l_change_request_group_id
11693: AND request_status = 'ACCEPTED';
11694:
11695: po_wf_util_pkg.setitemattrnumber (itemtype => itemtype,
11892: IF (p_action_type = 'MODIFICATION') THEN
11893:
11894: l_progress := '009';
11895:
11896: UPDATE po_change_requests
11897: SET request_status = 'ACCEPTED'
11898: WHERE change_request_group_id = p_chn_request_group_id
11899: AND document_header_id = l_document_header_id
11900: AND document_line_id = p_document_line_id ;
11924: - update po req tables */
11925:
11926: l_progress := '012';
11927:
11928: UPDATE po_change_requests
11929: SET request_status = 'ACCEPTED'
11930: WHERE change_request_group_id = p_chn_request_group_id
11931: AND document_header_id = l_document_header_id
11932: AND document_line_id = p_document_line_id ;
11931: AND document_header_id = l_document_header_id
11932: AND document_line_id = p_document_line_id ;
11933:
11934: IF g_debug_stmt THEN
11935: po_debug.debug_stmt(l_log_head, l_progress,'UPDATED po_change_requests');
11936: END IF;
11937:
11938: req_line_CANCEL(p_req_line_id => p_document_line_id,
11939: x_return_status =>l_return_status);
11949: END IF;
11950:
11951: ROLLBACK TO convertintosochange_SP; -- revert the so changes
11952:
11953: UPDATE po_change_requests
11954: SET request_status = 'REJECTED',
11955: change_active_flag = 'N',
11956: request_reason=l_msg_data
11957: WHERE change_request_group_id = p_chn_request_group_id
12516: varchar query should be
12517:
12518: SELECT mp.EMPLOYEE_ID
12519: into l_planner_id
12520: FROM po_change_requests pcr,
12521: po_requisition_lines_all prl,
12522: mtl_system_items_b mi,
12523: financials_system_params_all fsp,
12524: mtl_planners mp
12542: aname => 'PLANNERS_NOTIFIED');
12543:
12544: l_sql := '
12545: SELECT mp.EMPLOYEE_ID
12546: FROM po_change_requests pcr,
12547: po_requisition_lines_all prl,
12548: mtl_system_items_b mi,
12549: financials_system_params_all fsp,
12550: mtl_planners mp
12658: */
12659: BEGIN
12660: SELECT min(CHANGE_REQUEST_ID)
12661: into L_NEW_CHANGE_REQUEST_ID
12662: FROM po_change_requests
12663: WHERE change_request_group_id = l_change_request_group_id
12664: AND request_status = 'ACCEPTED'
12665: and CHANGE_REQUEST_ID > l_old_change_request_id;
12666: EXCEPTION