[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
557: procedure ValidateAndSaveRequest(
558: p_po_header_id in number,
559: p_po_release_id in number,
560: p_revision_num in number,
561: p_po_change_requests in out nocopy pos_chg_rec_tbl) is
562:
563: l_doc_check_rec_type Doc_Check_Return_Type;
564: l_pos_errors pos_err_type;
565: l_online_report_id number;
595: x_progress :='PO_ReqChangeRequestWF_PVT.ValidateAndSaveRequest:001';
596:
597: -- if the doc is in approved status, call validation
598: -- api. if the validation check fails, we won't save
599: -- the po change requests, and also reject the parent
600: -- req change.
601: if(l_doc_status = 'APPROVED') then
602: PO_CHG_REQUEST_PVT.validate_change_request (
603: p_api_version => 1.0,
606: x_msg_data => l_msg_data,
607: p_po_header_id => p_po_header_id,
608: p_po_release_id => p_po_release_id,
609: p_revision_num => p_revision_num,
610: p_po_change_requests => p_po_change_requests,
611: x_online_report_id => l_online_report_id,
612: x_pos_errors => l_pos_errors,
613: x_doc_check_error_msg => l_doc_check_rec_type);
614: x_progress :='PO_ReqChangeRequestWF_PVT.ValidateAndSaveRequest:002'
629: end if;
630: l_error_message:=substr(l_error_message||l_msg_data||':', 1, 240);
631:
632: x_progress :='PO_ReqChangeRequestWF_PVT.ValidateAndSaveRequest:003';
633: l_record_count:=p_po_change_requests.count();
634: FOR i in 1..l_record_count LOOP
635: update po_change_requests
636: set request_status='REJECTED',
637: change_active_flag='N',
631:
632: x_progress :='PO_ReqChangeRequestWF_PVT.ValidateAndSaveRequest:003';
633: l_record_count:=p_po_change_requests.count();
634: FOR i in 1..l_record_count LOOP
635: update po_change_requests
636: set request_status='REJECTED',
637: change_active_flag='N',
638: response_reason=substr(fnd_message.get_string('PO',
639: 'PO_RCO_VALIDATION_ERROR')||':'||
640: l_error_message1, 1, 2000),
641: response_date=sysdate,
642: validation_error=l_error_message
643: where change_request_id=
644: p_po_change_requests(i).parent_change_request_id;
645: end loop;
646: x_progress :='PO_ReqChangeRequestWF_PVT.ValidateAndSaveRequest:004';
647: else
648: -- save the request, check for auto approval
654: x_return_status =>l_return_status,
655: p_po_header_id =>p_po_header_id,
656: p_po_release_id =>p_po_release_id,
657: p_revision_num =>p_revision_num,
658: p_po_change_requests =>p_po_change_requests,
659: x_request_group_id =>l_change_request_group_id);
660:
661: if(l_return_status <>'S') then
662: raise l_save_failure;
667: end if;
668: elsif(l_doc_status='REJECTED') then
669: -- reject the change immediately
670: x_progress :='PO_ReqChangeRequestWF_PVT.ValidateAndSaveRequest:007';
671: l_record_count:=p_po_change_requests.count();
672: FOR i in 1..l_record_count LOOP
673: update po_change_requests
674: set request_status='REJECTED',
675: change_active_flag='N',
669: -- reject the change immediately
670: x_progress :='PO_ReqChangeRequestWF_PVT.ValidateAndSaveRequest:007';
671: l_record_count:=p_po_change_requests.count();
672: FOR i in 1..l_record_count LOOP
673: update po_change_requests
674: set request_status='REJECTED',
675: change_active_flag='N',
676: response_date=sysdate,
677: response_reason=fnd_message.get_string('PO', 'PO_RCO_PO_REJECTED')
675: change_active_flag='N',
676: response_date=sysdate,
677: response_reason=fnd_message.get_string('PO', 'PO_RCO_PO_REJECTED')
678: where change_request_id=
679: p_po_change_requests(i).parent_change_request_id;
680: end loop;
681: x_progress :='PO_ReqChangeRequestWF_PVT.ValidateAndSaveRequest:008';
682: else
683: -- save the request. no auto approval at this time
689: x_return_status =>l_return_status,
690: p_po_header_id =>p_po_header_id,
691: p_po_release_id =>p_po_release_id,
692: p_revision_num =>p_revision_num,
693: p_po_change_requests =>p_po_change_requests,
694: x_request_group_id =>l_change_request_group_id);
695: if(l_return_status <>'S') then
696: raise l_save_failure;
697: end if;
698: x_progress :='PO_ReqChangeRequestWF_PVT.ValidateAndSaveRequest:010';
699: end if;
700: exception
701: when l_save_failure then
702: l_record_count:=p_po_change_requests.count();
703: FOR i in 1..l_record_count LOOP
704: update po_change_requests
705: set request_status='REJECTED',
706: change_active_flag='N',
700: exception
701: when l_save_failure then
702: l_record_count:=p_po_change_requests.count();
703: FOR i in 1..l_record_count LOOP
704: update po_change_requests
705: set request_status='REJECTED',
706: change_active_flag='N',
707: response_date=sysdate,
708: response_reason=fnd_message.get_string('PO', 'PO_RCO_EXCEPTION_WHEN_SAVING')
706: change_active_flag='N',
707: response_date=sysdate,
708: response_reason=fnd_message.get_string('PO', 'PO_RCO_EXCEPTION_WHEN_SAVING')
709: where change_request_id=
710: p_po_change_requests(i).parent_change_request_id;
711: end loop;
712: when others then
713: wf_core.context('PO_ReqChangeRequestWF_PVT',
714: 'ValidateAndSaveRequest',x_progress||sqlerrm);
881: begin
882:
883: select parent_change_request_id
884: into l_req_change_request_id
885: from po_change_requests
886: where change_request_id=p_change_request_id;
887:
888: update po_change_requests
889: set request_status='ACCEPTED',
884: into l_req_change_request_id
885: from po_change_requests
886: where change_request_id=p_change_request_id;
887:
888: update po_change_requests
889: set request_status='ACCEPTED',
890: change_active_flag='N',
891: response_date=sysdate,
892: response_reason=fnd_message.get_string('PO','PO_RCO_AUTO_ACCEPTED')
893: where change_request_id in (l_req_change_request_id, p_change_request_id);
894:
895: select document_header_id, document_line_id, document_distribution_id, action_type, request_level
896: into l_header_id, l_line_id, l_distribution_id, l_action_type, l_request_level
897: from po_change_requests
898: where change_request_id=l_req_change_request_id;
899:
900: if(l_action_type='CANCELLATION') then
901: if(l_request_level='HEADER') then
915: new_start_date, new_expiration_date
916: into l_new_need_by_date, l_new_unit_price,
917: l_new_currency_unit_price, l_new_start_date,
918: l_new_expiration_date
919: from po_change_requests
920: where change_request_id=l_req_change_request_id;
921:
922: UpdateReqLine(l_line_id, l_new_need_by_date,
923: l_new_unit_price, l_new_currency_unit_price,
924: l_new_start_date, l_new_expiration_date);
925: /* else
926: select new_quantity, old_quantity
927: into l_new_quantity, l_old_quantity
928: from po_change_requests
929: where change_request_id=l_req_change_request_id;
930:
931: UpdateReqDistribution(l_line_id, l_distribution_id,
932: l_new_quantity, l_old_quantity);
980: pcr2.old_currency_amount,
981: pcr2.document_line_id,
982: pcr2.document_distribution_id,
983: pcr2.change_request_group_id
984: from po_change_requests pcr1, po_change_requests pcr2
985: where pcr1.change_request_group_id=p_change_request_group_id
986: and pcr1.document_line_location_id=p_line_location_id
987: and pcr1.parent_change_request_id=pcr2.change_request_id;
988:
1008:
1009: l_line_id1:=l_line_id;
1010: l_req_change_group_id1:=l_req_change_group_id;
1011:
1012: update po_change_requests
1013: set request_status='ACCEPTED',
1014: change_active_flag='N',
1015: response_date=sysdate,
1016: response_reason=fnd_message.get_string('PO','PO_RCO_AUTO_ACCEPTED')
1027: end if;
1028: end loop;
1029:
1030: begin
1031: update po_change_requests
1032: set request_status='ACCEPTED',
1033: change_active_flag='N',
1034: response_date=sysdate,
1035: response_reason=fnd_message.get_string('PO','PO_RCO_AUTO_ACCEPTED')
1044:
1045: begin
1046: select new_price, new_currency_unit_price
1047: into l_new_price, l_new_currency_unit_price
1048: from po_change_requests
1049: where change_request_group_id=l_req_change_group_id1
1050: and document_line_id=l_line_id1
1051: and action_type='DERIVED'
1052: and new_price is not null;
1084: l_change_request_id number;
1085: l_request_status varchar2(20);
1086:
1087:
1088: l_action_type po_change_requests.action_type%type;
1089: l_last_action_type po_change_requests.action_type%type;
1090: l_new_need_by_date DATE;
1091: l_po_need_by_date DATE;
1092: l_new_price number;
1085: l_request_status varchar2(20);
1086:
1087:
1088: l_action_type po_change_requests.action_type%type;
1089: l_last_action_type po_change_requests.action_type%type;
1090: l_new_need_by_date DATE;
1091: l_po_need_by_date DATE;
1092: l_new_price number;
1093: l_po_price number;
1137: pol.cancel_flag line_cancel_flag,
1138: pll.cancel_flag shipment_cancel_flag,
1139: poh.currency_code,
1140: pol.org_id
1141: from po_change_requests pcr,
1142: po_headers_all poh,
1143: po_lines_all pol,
1144: po_line_locations_all pll,
1145: po_distributions_all pod,
1225: if(l_request_level='DISTRIBUTION') then
1226: -- it must be quantity change
1227:
1228: -- bug 5363103
1229: -- l_new_quantity and l_new_amount are from po_change_requests table
1230: -- which are always in functional currency;
1231: -- l_po_quantity and l_po_amount are from PO.
1232: -- If PO is created in txn currency, RCO shouldn't be autoapproved
1233: -- even if the quantities (amounts) are the same ( since they are
1308: cursor l_get_id_csr(p_change_request_group_id in number) is
1309: select document_header_id,
1310: po_release_id,
1311: document_type
1312: from po_change_requests
1313: where change_request_group_id=p_change_request_group_id
1314: and request_status='PENDING';
1315:
1316: l_document_header_id number;
1375: new_expiration_date,
1376: old_amount,
1377: new_amount,
1378: parent_change_request_id
1379: from po_change_requests
1380: where change_request_group_id=p_change_request_group_id
1381: and request_status='PENDING';
1382:
1383: begin
1404: x_progress :='004';
1405: if(l_doc_status='REJECTED') then
1406: -- reject the change immediately
1407: x_progress :='005';
1408: update po_change_requests
1409: set request_status='REJECTED',
1410: change_active_flag='N',
1411: response_date=sysdate,
1412: response_reason=fnd_message.get_string('PO', 'PO_RCO_PO_REJECTED')
1411: response_date=sysdate,
1412: response_reason=fnd_message.get_string('PO', 'PO_RCO_PO_REJECTED')
1413: where change_request_id in (
1414: select parent_change_request_id
1415: from po_change_requests
1416: where change_request_group_id=p_change_request_group_id
1417: and request_status='PENDING');
1418:
1419: x_progress :='006';
1416: where change_request_group_id=p_change_request_group_id
1417: and request_status='PENDING');
1418:
1419: x_progress :='006';
1420: update po_change_requests
1421: set request_status='REJECTED',
1422: change_active_flag='N',
1423: response_date=sysdate,
1424: response_reason=fnd_message.get_string('PO', 'PO_RCO_PO_REJECTED')
1533: x_msg_data => l_msg_data,
1534: p_po_header_id => ll_document_header_id,
1535: p_po_release_id => ll_po_release_id,
1536: p_revision_num => ll_document_revision_num,
1537: p_po_change_requests => my_chg_rec_tbl,
1538: x_online_report_id => l_online_report_id,
1539: x_pos_errors => l_pos_errors,
1540: x_doc_check_error_msg => l_doc_check_rec_type);
1541:
1545: l_error_message1:=null;
1546: if(l_pos_errors is not null) then
1547: l_error_message1:=l_pos_errors.text_line(1);
1548: end if;
1549: update po_change_requests
1550: set request_status='REJECTED',
1551: change_active_flag='N',
1552: response_date=sysdate,
1553: response_reason=substr(fnd_message.get_string('PO',
1554: 'PO_RCO_VALIDATION_ERROR')||':'||
1555: l_error_message1, 1, 2000)
1556: where change_request_id in (
1557: select parent_change_request_id
1558: from po_change_requests
1559: where change_request_group_id=p_change_request_group_id
1560: and request_status='PENDING');
1561:
1562: x_progress :='013';
1559: where change_request_group_id=p_change_request_group_id
1560: and request_status='PENDING');
1561:
1562: x_progress :='013';
1563: update po_change_requests
1564: set request_status='REJECTED',
1565: change_active_flag='N',
1566: response_date=sysdate,
1567: response_reason=substr(fnd_message.get_string('PO',
1615: 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', p_req_chg_initiator IN VARCHAR2 DEFAULT NULL) is
1616: pragma AUTONOMOUS_TRANSACTION;
1617:
1618: x_progress varchar2(100):= '000';
1619: l_request_level PO_CHANGE_REQUESTS.request_level%type;
1620: l_document_type PO_CHANGE_REQUESTS.document_type%type;
1621: l_document_id number;
1622: l_document_line_id number;
1623: l_document_line_id1 number:=null;
1616: pragma AUTONOMOUS_TRANSACTION;
1617:
1618: x_progress varchar2(100):= '000';
1619: l_request_level PO_CHANGE_REQUESTS.request_level%type;
1620: l_document_type PO_CHANGE_REQUESTS.document_type%type;
1621: l_document_id number;
1622: l_document_line_id number;
1623: l_document_line_id1 number:=null;
1624: l_document_shipment_id number;
1624: l_document_shipment_id number;
1625: l_document_distribution_id number;
1626: l_release_id number;
1627: l_change_request_id number;
1628: l_request_status PO_CHANGE_REQUESTS.request_status%type;
1629: l_requester_id number;
1630: l_return_code number;
1631: l_err_msg varchar2(200);
1632: l_return_msg varchar2(2000);
1630: l_return_code number;
1631: l_err_msg varchar2(200);
1632: l_return_msg varchar2(2000);
1633: l_document_subtype varchar2(100);
1634: l_change_reason PO_CHANGE_REQUESTS.request_reason%TYPE;
1635: l_return_status varchar2(1);
1636:
1637: l_old_need_by_date DATE;
1638: l_new_need_by_date DATE;
1664:
1665: /* this is for the date change request id
1666: which isnot tied by parent request id
1667: */
1668: l_date_change_id PO_CHANGE_REQUESTS.change_request_id%type;
1669: l_date_change_id1 PO_CHANGE_REQUESTS.change_request_id%type;
1670:
1671: -- Added variable l_validation_error
1672: l_validation_error PO_CHANGE_REQUESTS.VALIDATION_ERROR%type;
1665: /* this is for the date change request id
1666: which isnot tied by parent request id
1667: */
1668: l_date_change_id PO_CHANGE_REQUESTS.change_request_id%type;
1669: l_date_change_id1 PO_CHANGE_REQUESTS.change_request_id%type;
1670:
1671: -- Added variable l_validation_error
1672: l_validation_error PO_CHANGE_REQUESTS.VALIDATION_ERROR%type;
1673:
1668: l_date_change_id PO_CHANGE_REQUESTS.change_request_id%type;
1669: l_date_change_id1 PO_CHANGE_REQUESTS.change_request_id%type;
1670:
1671: -- Added variable l_validation_error
1672: l_validation_error PO_CHANGE_REQUESTS.VALIDATION_ERROR%type;
1673:
1674: --Bug#6132339
1675: l_cancel_count number;
1676: l_cancel_index number;
1682:
1683: cursor cancel_request is
1684: select decode (document_type, 'RELEASE', null, document_line_id), document_line_location_id,
1685: change_request_id, request_reason
1686: from po_change_requests
1687: where change_request_group_id=p_change_request_group_id
1688: and request_status='BUYER_APP'
1689: and action_type='CANCELLATION';
1690:
1698: old_expiration_date, new_expiration_date,
1699: old_amount, new_amount,
1700: old_currency_amount, new_currency_amount,
1701: change_request_group_id
1702: from po_change_requests
1703: where change_request_id in
1704: (select parent_change_request_id
1705: from po_change_requests
1706: where change_request_group_id=p_change_request_group_id
1701: change_request_group_id
1702: from po_change_requests
1703: where change_request_id in
1704: (select parent_change_request_id
1705: from po_change_requests
1706: where change_request_group_id=p_change_request_group_id
1707: and request_status='BUYER_APP'
1708: and action_type='MODIFICATION')
1709: order by document_line_id, document_distribution_id;
1718: old_expiration_date, new_expiration_date,
1719: old_amount, new_amount,
1720: old_currency_amount, new_currency_amount,
1721: change_request_group_id
1722: from po_change_requests
1723: where change_request_group_id in
1724: (select parent_change_request_id
1725: from po_change_requests
1726: where change_request_group_id=p_change_request_group_id
1721: change_request_group_id
1722: from po_change_requests
1723: where change_request_group_id in
1724: (select parent_change_request_id
1725: from po_change_requests
1726: where change_request_group_id=p_change_request_group_id
1727: and request_status='BUYER_APP'
1728: and action_type='MODIFICATION')
1729: order by document_line_id, document_distribution_id;
1729: order by document_line_id, document_distribution_id;
1730:
1731: cursor l_document_id_csr is
1732: select document_type, document_header_id, po_release_id, nvl(requester_id, created_by)
1733: from po_change_requests
1734: where change_request_group_id =p_change_request_group_id;
1735:
1736: cursor l_exist_change_request_csr is
1737: select change_request_id
1734: where change_request_group_id =p_change_request_group_id;
1735:
1736: cursor l_exist_change_request_csr is
1737: select change_request_id
1738: from po_change_requests
1739: where change_request_group_id=p_change_request_group_id
1740: and request_status in ('PENDING', 'BUYER_APP')
1741: and action_type='MODIFICATION';
1742: l_doc_check_rec POS_ERR_TYPE;
1744: --This cursor doesn't include 'amount' change records
1745: -- since for 'amount'change records, both new_start_date and new_expiration_date are null
1746: cursor l_date_change_csr is
1747: select document_line_id,change_request_group_id
1748: from po_change_requests
1749: where document_type = 'REQ'
1750: and change_request_id in
1751: (select parent_change_request_id
1752: from po_change_requests pcr2
1748: from po_change_requests
1749: where document_type = 'REQ'
1750: and change_request_id in
1751: (select parent_change_request_id
1752: from po_change_requests pcr2
1753: where pcr2.change_request_group_id=p_change_request_group_id
1754: and pcr2.action_type='MODIFICATION'
1755: and ( pcr2.new_start_date is not null
1756: or pcr2.new_expiration_date is not null ) );
1758:
1759: cursor update_allocation_csr is
1760: select pcr.document_line_id, nvl(sum(pcr.new_amount),sum(pcr.new_quantity))
1761: from po_req_distributions_all prd,
1762: po_change_requests pcr
1763: where pcr.document_distribution_id=prd.DISTRIBUTION_ID
1764: and pcr.change_request_id in
1765: (select parent_change_request_id
1766: from po_change_requests
1762: po_change_requests pcr
1763: where pcr.document_distribution_id=prd.DISTRIBUTION_ID
1764: and pcr.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='BUYER_APP'
1769: and action_type='MODIFICATION')
1770: group by document_line_id;
1780: -- when buyer response to the po change, the response_date
1781: -- response_reason and resonded_by is not carried back to
1782: -- requisition change request. thus the requisition history
1783: -- page shows null on those field
1784: update PO_CHANGE_REQUESTS pcr1
1785: set (pcr1.request_status,
1786: pcr1.change_active_flag,
1787: pcr1.response_date,
1788: pcr1.response_reason,
1797: pcr2.responded_by,
1798: fnd_global.user_id,
1799: fnd_global.login_id,
1800: sysdate
1801: from po_change_requests pcr2
1802: where pcr2.parent_change_request_id=pcr1.change_request_id
1803: and pcr2.change_request_group_id=p_change_request_group_id
1804: and pcr2.request_status='REJECTED')
1805: where pcr1.change_request_id in
1803: and pcr2.change_request_group_id=p_change_request_group_id
1804: and pcr2.request_status='REJECTED')
1805: where pcr1.change_request_id in
1806: (select parent_change_request_id
1807: from po_change_requests
1808: where change_request_group_id=p_change_request_group_id
1809: and request_status='REJECTED');
1810:
1811: x_progress:='REJECTION:002';
1823: because this situation only happens to temp labor line,
1824: let it only applies to temp labor line.
1825: */
1826:
1827: update PO_CHANGE_REQUESTS pcr1
1828: set (pcr1.request_status,
1829: pcr1.change_active_flag,
1830: pcr1.response_date,
1831: pcr1.response_reason,
1840: pcr2.responded_by,
1841: fnd_global.user_id,
1842: fnd_global.login_id,
1843: sysdate
1844: from po_change_requests pcr2
1845: where pcr2.parent_change_request_id in
1846: ( select pcr3.change_request_id
1847: from po_change_requests pcr3
1848: where
1843: sysdate
1844: from po_change_requests pcr2
1845: where pcr2.parent_change_request_id in
1846: ( select pcr3.change_request_id
1847: from po_change_requests pcr3
1848: where
1849: pcr3.change_request_group_id=pcr1.change_request_group_id
1850: and pcr3.document_line_id = pcr1.document_line_id
1851: )
1854: )
1855: where pcr1.change_request_id in (
1856: select pcr5.change_request_id
1857: from
1858: po_change_requests pcr,
1859: po_change_requests pcr4,
1860: po_change_requests pcr5,
1861: po_requisition_lines_all por
1862: where
1855: where pcr1.change_request_id in (
1856: select pcr5.change_request_id
1857: from
1858: po_change_requests pcr,
1859: po_change_requests pcr4,
1860: po_change_requests pcr5,
1861: po_requisition_lines_all por
1862: where
1863: pcr.change_request_group_id=p_change_request_group_id
1856: select pcr5.change_request_id
1857: from
1858: po_change_requests pcr,
1859: po_change_requests pcr4,
1860: po_change_requests pcr5,
1861: po_requisition_lines_all por
1862: where
1863: pcr.change_request_group_id=p_change_request_group_id
1864: and pcr.parent_change_request_id=pcr4.change_request_id
1880: last_updated_by = fnd_global.user_id,
1881: last_update_login = fnd_global.login_id
1882: WHERE line_location_id IN
1883: (SELECT document_line_location_id
1884: FROM po_change_requests
1885: WHERE
1886: request_level = 'SHIPMENT' AND
1887: change_request_group_id = p_change_request_group_id AND
1888: action_type IN ('MODIFICATION', 'CANCELLATION') AND
1926: --Bug#8224603:Store count of changes to pass p_launch_approval_flag
1927: -- as 'Y'for the last cancel record.
1928:
1929: select count(*) into l_cancel_count
1930: from po_change_requests
1931: where change_request_group_id=p_change_request_group_id
1932: and request_status='BUYER_APP'
1933: and action_type='CANCELLATION';
1934:
1936: --exists. If so, then we will not call approval here. Approval
1937: --will be kicked off after process change acceptance.
1938:
1939: select count(*) into l_buy_app_chg_count
1940: from po_change_requests
1941: where change_request_group_id=p_change_request_group_id
1942: and request_status='BUYER_APP'
1943: and action_type='MODIFICATION';
1944:
2050: -- response_reason and resonded_by is not carried back to
2051: -- requisition change request. thus the requisition history
2052: -- page shows null on those field
2053:
2054: update PO_CHANGE_REQUESTS pcr1
2055: set (pcr1.request_status,
2056: pcr1.change_active_flag,
2057: pcr1.response_date,
2058: pcr1.response_reason,
2067: pcr2.responded_by,
2068: fnd_global.user_id,
2069: fnd_global.login_id,
2070: sysdate
2071: from po_change_requests pcr2
2072: where pcr2.parent_change_request_id=pcr1.change_request_id
2073: and pcr2.change_request_group_id=p_change_request_group_id
2074: and pcr2.request_status in ('BUYER_APP', 'ACCEPTED')
2075: and pcr2.action_type='CANCELLATION')
2074: and pcr2.request_status in ('BUYER_APP', 'ACCEPTED')
2075: and pcr2.action_type='CANCELLATION')
2076: where pcr1.change_request_id in
2077: (select parent_change_request_id
2078: from po_change_requests
2079: where change_request_group_id=p_change_request_group_id
2080: and request_status in ('BUYER_APP', 'ACCEPTED')
2081: and action_type='CANCELLATION');
2082: x_progress:='CANCELLATION:005';
2080: and request_status in ('BUYER_APP', 'ACCEPTED')
2081: and action_type='CANCELLATION');
2082: x_progress:='CANCELLATION:005';
2083:
2084: update PO_CHANGE_REQUESTS
2085: set request_status='ACCEPTED',
2086: change_active_flag='N'
2087: where change_request_group_id=p_change_request_group_id
2088: and request_status='BUYER_APP'
2150: IF(FND_MSG_PUB.COUNT_MSG = 1) THEN
2151: l_validation_error := fnd_msg_pub.get(p_encoded => 'F');
2152: END IF;
2153:
2154: update PO_CHANGE_REQUESTS
2155: set request_status='REJECTED',
2156: change_active_flag='N',
2157: response_date=sysdate,
2158: response_reason=fnd_message.get_string('PO', 'PO_RCO_EXCEPTION_WHEN_PROCESS') ||
2158: response_reason=fnd_message.get_string('PO', 'PO_RCO_EXCEPTION_WHEN_PROCESS') ||
2159: decode(l_validation_error, NULL, '', ' : ' || l_validation_error ),
2160: validation_error =l_validation_error
2161: where change_request_id in (select parent_change_request_id
2162: from po_change_requests
2163: where change_request_group_id=p_change_request_group_id
2164: and request_status='BUYER_APP'
2165: and action_type='CANCELLATION');
2166:
2163: where change_request_group_id=p_change_request_group_id
2164: and request_status='BUYER_APP'
2165: and action_type='CANCELLATION');
2166:
2167: update PO_CHANGE_REQUESTS
2168: set request_status='REJECTED',
2169: change_active_flag='N',
2170: response_date=sysdate,
2171: response_reason=fnd_message.get_string('PO', 'PO_RCO_EXCEPTION_WHEN_PROCESS') ||
2265:
2266: begin
2267: select new_price, new_currency_unit_price
2268: into l_new_price1, l_new_currency_unit_price1
2269: from po_change_requests
2270: where change_request_group_id=l_group_id
2271: and document_line_id=l_document_line_id
2272: and action_type='DERIVED'
2273: and new_price is not null;
2285: -- move req change to req
2286: if(l_request_level='LINE') then
2287:
2288: -- update start_date and end_date
2289: -- select new_start_date and new_expiration_date from po_change_requests
2290: -- req's change requests has separate records
2291: -- for start date and end date
2292: -- while only 1 merged record for po counter part,
2293: -- the change_request cursor doesn't capture both start and end date change.
2293: -- the change_request cursor doesn't capture both start and end date change.
2294: BEGIN
2295: select new_start_date
2296: into l_new_start_date
2297: from po_change_requests
2298: where change_request_group_id = l_group_id
2299: and document_type = 'REQ'
2300: and document_line_id = l_document_line_id
2301: and request_level= 'LINE'
2310:
2311: BEGIN
2312: select new_expiration_date
2313: into l_new_expiration_date
2314: from po_change_requests
2315: where change_request_group_id = l_group_id
2316: and document_type = 'REQ'
2317: and document_line_id = l_document_line_id
2318: and request_level= 'LINE'
2422: -- when buyer response to the po change, the response_date
2423: -- response_reason and resonded_by is not carried back to
2424: -- requisition change request. thus the requisition history
2425: -- page shows null on those field
2426: update PO_CHANGE_REQUESTS pcr1
2427: set (pcr1.request_status,
2428: pcr1.change_active_flag,
2429: pcr1.response_date,
2430: pcr1.response_reason,
2439: pcr2.responded_by,
2440: fnd_global.user_id,
2441: fnd_global.login_id,
2442: sysdate
2443: from po_change_requests pcr2
2444: where pcr2.parent_change_request_id=pcr1.change_request_id
2445: and pcr2.change_request_group_id=p_change_request_group_id
2446: and pcr2.request_status='BUYER_APP'
2447: and pcr2.action_type='MODIFICATION')
2446: and pcr2.request_status='BUYER_APP'
2447: and pcr2.action_type='MODIFICATION')
2448: where pcr1.change_request_id in
2449: (select parent_change_request_id
2450: from po_change_requests
2451: where change_request_group_id=p_change_request_group_id
2452: and request_status='BUYER_APP'
2453: and action_type='MODIFICATION');
2454:
2455: x_progress:='ACCEPTANCE:010';
2456:
2457: -- When there are start_date and end_date changes, above 'update' doesn't update all of the REQ change requests
2458: -- This is because for REQ change requests, start_date and end_date changes for the same req line are stored in two different rows;
2459: -- while for PO change requests, start_date and end_date changes are stored in one row.
2460: -- Below we update the status for those remaining records.
2461:
2462: open l_date_change_csr;
2463: loop
2466: l_req_change_grp_id;
2467: exit when l_date_change_csr%NOTFOUND;
2468:
2469: if (l_req_doc_id is not null) then
2470: update PO_CHANGE_REQUESTS pcr1
2471: set (pcr1.request_status,
2472: pcr1.change_active_flag,
2473: pcr1.response_date,
2474: pcr1.response_reason,
2483: pcr2.responded_by,
2484: fnd_global.user_id,
2485: fnd_global.login_id,
2486: sysdate
2487: from po_change_requests pcr2
2488: where pcr2.document_type= 'REQ'
2489: and pcr2.change_request_group_id=l_req_change_grp_id
2490: and pcr2.document_line_id = l_req_doc_id
2491: and pcr2.request_status= 'ACCEPTED'
2520: --In case of error, continue workflow
2521: when FND_API.g_exc_error then
2522: rollback to PO_REQCHANGEREQUESTWF_PVT_SP;
2523:
2524: update PO_CHANGE_REQUESTS
2525: set request_status='REJECTED',
2526: change_active_flag='N',
2527: response_date=sysdate,
2528: response_reason=fnd_message.get_string('PO', 'PO_RCO_EXCEPTION_WHEN_PROCESS') ||
2528: response_reason=fnd_message.get_string('PO', 'PO_RCO_EXCEPTION_WHEN_PROCESS') ||
2529: decode(l_validation_error, NULL, '', ' : ' || l_validation_error ),
2530: validation_error=l_err_msg
2531: where change_request_id in (select parent_change_request_id
2532: from po_change_requests
2533: where change_request_group_id=p_change_request_group_id
2534: and request_status='BUYER_APP'
2535: and action_type='MODIFICATION');
2536:
2533: where change_request_group_id=p_change_request_group_id
2534: and request_status='BUYER_APP'
2535: and action_type='MODIFICATION');
2536:
2537: update PO_CHANGE_REQUESTS
2538: set request_status='REJECTED',
2539: change_active_flag='N',
2540: response_date=sysdate,
2541: response_reason=fnd_message.get_string('PO', 'PO_RCO_EXCEPTION_WHEN_PROCESS') ||
2571: procedure ProcessSCOAcceptance(p_change_request_group_id in number,p_launch_approvals_flag IN VARCHAR2 default 'N') is
2572: pragma AUTONOMOUS_TRANSACTION;
2573:
2574: x_progress varchar2(100):= '000';
2575: l_request_level PO_CHANGE_REQUESTS.request_level%type;
2576: l_document_type PO_CHANGE_REQUESTS.document_type%type;
2577: l_document_id number;
2578: l_document_line_id number;
2579: l_document_line_id1 number:=null;
2572: pragma AUTONOMOUS_TRANSACTION;
2573:
2574: x_progress varchar2(100):= '000';
2575: l_request_level PO_CHANGE_REQUESTS.request_level%type;
2576: l_document_type PO_CHANGE_REQUESTS.document_type%type;
2577: l_document_id number;
2578: l_document_line_id number;
2579: l_document_line_id1 number:=null;
2580: l_document_shipment_id number;
2580: l_document_shipment_id number;
2581: l_document_distribution_id number;
2582: l_release_id number;
2583: l_change_request_id number;
2584: l_request_status PO_CHANGE_REQUESTS.request_status%type;
2585: l_requester_id number;
2586: l_return_code number;
2587: l_err_msg varchar2(200);
2588: l_return_msg varchar2(2000);
2586: l_return_code number;
2587: l_err_msg varchar2(200);
2588: l_return_msg varchar2(2000);
2589: l_document_subtype varchar2(100);
2590: l_change_reason PO_CHANGE_REQUESTS.request_reason%TYPE;
2591: l_return_status varchar2(1);
2592:
2593: l_old_need_by_date DATE;
2594: l_new_need_by_date DATE;
2617:
2618: /* this is for the date change request id
2619: which isnot tied by parent request id
2620: */
2621: l_date_change_id PO_CHANGE_REQUESTS.change_request_id%type;
2622: l_date_change_id1 PO_CHANGE_REQUESTS.change_request_id%type;
2623:
2624: -- Added variable l_validation_error
2625: l_validation_error PO_CHANGE_REQUESTS.VALIDATION_ERROR%type;
2618: /* this is for the date change request id
2619: which isnot tied by parent request id
2620: */
2621: l_date_change_id PO_CHANGE_REQUESTS.change_request_id%type;
2622: l_date_change_id1 PO_CHANGE_REQUESTS.change_request_id%type;
2623:
2624: -- Added variable l_validation_error
2625: l_validation_error PO_CHANGE_REQUESTS.VALIDATION_ERROR%type;
2626: l_doc_check_rec POS_ERR_TYPE;
2621: l_date_change_id PO_CHANGE_REQUESTS.change_request_id%type;
2622: l_date_change_id1 PO_CHANGE_REQUESTS.change_request_id%type;
2623:
2624: -- Added variable l_validation_error
2625: l_validation_error PO_CHANGE_REQUESTS.VALIDATION_ERROR%type;
2626: l_doc_check_rec POS_ERR_TYPE;
2627:
2628: l_temp_date date;
2629: l_temp_reason varchar2(1000);
2640: old_expiration_date, new_expiration_date,
2641: old_amount, new_amount,
2642: old_currency_amount, new_currency_amount,
2643: change_request_group_id
2644: from po_change_requests
2645: where change_request_group_id in
2646: (select parent_change_request_id
2647: from po_change_requests
2648: where change_request_group_id=p_change_request_group_id
2643: change_request_group_id
2644: from po_change_requests
2645: where change_request_group_id in
2646: (select parent_change_request_id
2647: from po_change_requests
2648: where change_request_group_id=p_change_request_group_id
2649: and request_status='BUYER_APP'
2650: and action_type='MODIFICATION')
2651: order by document_line_id, document_distribution_id;
2650: and action_type='MODIFICATION')
2651: order by document_line_id, document_distribution_id;
2652:
2653: cursor l_document_id_csr is
2654: select document_type, document_header_id, po_release_id, nvl(requester_id, created_by) from po_change_requests
2655: where change_request_group_id =p_change_request_group_id;
2656:
2657: BEGIN
2658:
2705:
2706: begin
2707: select new_price, new_currency_unit_price
2708: into l_new_price1, l_new_currency_unit_price1
2709: from po_change_requests
2710: where change_request_group_id=l_group_id
2711: and document_line_id=l_document_line_id
2712: and action_type='DERIVED'
2713: and new_price is not null;
2738: into l_new_start_date,
2739: l_new_expiration_date,
2740: l_date_change_id1,
2741: l_date_change_id
2742: from po_change_requests pcr1, po_change_requests pcr2
2743: where pcr1.change_request_group_id = pcr2.change_request_group_id
2744: -- and pcr1.change_request_id in
2745: and pcr1.change_request_group_id in
2746: (select parent_change_request_id
2743: where pcr1.change_request_group_id = pcr2.change_request_group_id
2744: -- and pcr1.change_request_id in
2745: and pcr1.change_request_group_id in
2746: (select parent_change_request_id
2747: from po_change_requests
2748: where change_request_group_id=p_change_request_group_id
2749: and request_status='BUYER_APP'
2750: and action_type='MODIFICATION')
2751: and (pcr2.new_start_date is not null
2832: pcr.responded_by
2833: into l_temp_date,
2834: l_temp_reason,
2835: l_temp_responder
2836: from po_change_requests pcr
2837: where pcr.change_request_group_id=p_change_request_group_id
2838: and pcr.action_type='MODIFICATION'
2839: and rownum=1;
2840:
2837: where pcr.change_request_group_id=p_change_request_group_id
2838: and pcr.action_type='MODIFICATION'
2839: and rownum=1;
2840:
2841: update PO_CHANGE_REQUESTS pcr1
2842: set (pcr1.request_status,
2843: pcr1.change_active_flag,
2844: pcr1.response_date,
2845: pcr1.response_reason,
2857: sysdate
2858: from dual)
2859: where pcr1.change_request_group_id in
2860: (select parent_change_request_id
2861: from po_change_requests
2862: where change_request_group_id=p_change_request_group_id
2863: and action_type='MODIFICATION');
2864:
2865: if (l_date_change_id is not null) then
2862: where change_request_group_id=p_change_request_group_id
2863: and action_type='MODIFICATION');
2864:
2865: if (l_date_change_id is not null) then
2866: update PO_CHANGE_REQUESTS pcr1
2867: set (pcr1.request_status,
2868: pcr1.change_active_flag,
2869: pcr1.response_date,
2870: pcr1.response_reason,
2879: pcr2.responded_by,
2880: fnd_global.user_id,
2881: fnd_global.login_id,
2882: sysdate
2883: from po_change_requests pcr2
2884: where pcr2.parent_change_request_id=l_date_change_id1
2885: and pcr2.change_request_group_id=p_change_request_group_id
2886: and pcr2.action_type='MODIFICATION'
2887: and rownum=1)
2895:
2896: when FND_API.g_exc_error then
2897: rollback to PO_REQCHANGEREQUESTWF_PVT_SP;
2898:
2899: update PO_CHANGE_REQUESTS
2900: set request_status='REJECTED',
2901: change_active_flag='N',
2902: response_date=sysdate,
2903: response_reason=fnd_message.get_string('PO', 'PO_RCO_EXCEPTION_WHEN_PROCESS'),
2902: response_date=sysdate,
2903: response_reason=fnd_message.get_string('PO', 'PO_RCO_EXCEPTION_WHEN_PROCESS'),
2904: validation_error=l_err_msg
2905: where change_request_group_id in (select parent_change_request_id
2906: from po_change_requests
2907: where change_request_group_id=p_change_request_group_id
2908: and action_type='MODIFICATION');
2909:
2910: update PO_CHANGE_REQUESTS
2906: from po_change_requests
2907: where change_request_group_id=p_change_request_group_id
2908: and action_type='MODIFICATION');
2909:
2910: update PO_CHANGE_REQUESTS
2911: set request_status='REJECTED',
2912: change_active_flag='N',
2913: response_date=sysdate,
2914: response_reason=fnd_message.get_string('PO', 'PO_RCO_EXCEPTION_WHEN_PROCESS'),
2947: x_progress varchar2(3):= '000';
2948:
2949: BEGIN
2950:
2951: update PO_CHANGE_REQUESTS
2952: set last_updated_by = fnd_global.user_id,
2953: last_update_login = fnd_global.login_id,
2954: last_update_date = sysdate,
2955: request_status=p_request_status,
3029: l_converted_quantity number;
3030:
3031: -- Bug # 3862383
3032: -- htank, 06-Oct-2004
3033: -- Requester_ID was not inserted into the po_change_requests table where document_type = 'PO'
3034: -- Due to this Approval History for PO was showing wrong name in the Change Request event
3035: l_requester_id number; -- Bug # 3862383
3036:
3037: -- SQL What: get all the elements required to generate
3085: max(pll.amount) ship_amount,
3086: to_number(null) dist_amount,
3087: max(prl.matching_basis),
3088: max(pcr.requester_id) requester_id -- Bug # 3862383
3089: from po_change_requests pcr,
3090: po_line_locations_all pll,
3091: po_requisition_lines_all prl,
3092: po_headers_all poh,
3093: po_releases_all por,
3141: pll.amount ship_amount,
3142: to_number(null) dist_amount,
3143: prl.matching_basis,
3144: pcr.requester_id requester_id -- Bug # 3862383
3145: from po_change_requests pcr,
3146: po_line_locations_all pll,
3147: po_requisition_lines_all prl,
3148: po_headers_all poh,
3149: po_releases_all por,
3204: pll.amount ship_amount,
3205: pod.amount_ordered dist_amount,
3206: prl.matching_basis,
3207: pcr.requester_id requester_id -- Bug # 3862383
3208: from po_change_requests pcr,
3209: po_line_locations_all pll,
3210: po_requisition_lines_all prl,
3211: po_headers_all poh,
3212: po_releases_all por,
3444: ValidateAndSaveRequest(
3445: p_po_header_id =>l_document_header_id,
3446: p_po_release_id =>l_po_release_id,
3447: p_revision_num =>l_document_revision_num,
3448: p_po_change_requests =>my_chg_rec_tbl);
3449:
3450: temp:=1;
3451: my_chg_rec_tbl := pos_chg_rec_tbl();
3452: l_current_header_id:=l_document_header_id;
3601: ValidateAndSaveRequest(
3602: p_po_header_id =>l_document_header_id,
3603: p_po_release_id =>l_po_release_id,
3604: p_revision_num =>l_document_revision_num,
3605: p_po_change_requests =>my_chg_rec_tbl);
3606: commit;
3607:
3608: EXCEPTION
3609: WHEN OTHERS THEN
3642: if(p_request_status='MGR_APP') then
3643:
3644: x_progress := '001';
3645:
3646: update PO_CHANGE_REQUESTS
3647: set last_updated_by = fnd_global.user_id,
3648: last_update_login = fnd_global.login_id,
3649: last_update_date = sysdate,
3650: request_status='MGR_APP'
3659: if(p_request_status='MGR_APP') then
3660:
3661: x_progress := '003';
3662:
3663: update PO_CHANGE_REQUESTS
3664: set last_updated_by = fnd_global.user_id,
3665: last_update_login = fnd_global.login_id,
3666: last_update_date = sysdate,
3667: request_status='MGR_APP'
3674: elsif(p_request_status='MGR_PRE_APP') then
3675:
3676: x_progress := '005';
3677:
3678: update PO_CHANGE_REQUESTS
3679: set last_updated_by = fnd_global.user_id,
3680: last_update_login = fnd_global.login_id,
3681: last_update_date = sysdate,
3682: request_status='MGR_PRE_APP'
3687: x_progress := '006';
3688: else -- reject
3689: x_progress := '005';
3690:
3691: update PO_CHANGE_REQUESTS
3692: set last_updated_by = fnd_global.user_id,
3693: last_update_login = fnd_global.login_id,
3694: last_update_date = sysdate,
3695: request_status='REJECTED',
3721: * Private Procedure: SetReqChangeFlag
3722: * Effects: set the change_pending_flag in requisition headers table
3723: * if the flag is set to 'Y', which means there is pending
3724: * change coming, also store the itemtype and itemkey to
3725: * po_change_requests table.
3726: *
3727: * the process will commit when it exits.
3728: *
3729: * Returns:
3749:
3750: x_progress:='001';
3751:
3752: if(p_change_flag='Y') then
3753: update po_change_requests
3754: set wf_item_type=p_itemtype,
3755: wf_item_key=p_itemkey
3756: where change_request_group_id=p_change_request_group_id;
3757: end if;
4073: last_update_login = fnd_global.login_id
4074: WHERE
4075: line_location_id in
4076: (select document_line_location_id
4077: from po_change_requests
4078: where
4079: request_level = 'SHIPMENT' and
4080: document_header_id = p_document_id and
4081: action_type IN ('MODIFICATION', 'CANCELLATION') and
4130: last_update_date = sysdate,
4131: last_updated_by = fnd_global.user_id,
4132: last_update_login = fnd_global.login_id
4133: WHERE line_location_id in (select document_line_location_id
4134: from po_change_requests
4135: where request_level = 'SHIPMENT' and
4136: po_release_id = p_document_id and
4137: action_type IN ('MODIFICATION', 'CANCELLATION') and
4138: initiator = 'REQUESTER' and
4177: l_responsibility_id number;
4178:
4179: cursor get_parent_info_csr(l_change_request_group_id number) is
4180: select pcr.wf_item_type, pcr.wf_item_key
4181: from po_change_requests pcr, po_change_requests pcr2
4182: where pcr2.change_request_group_id=l_change_request_group_id
4183: and pcr.change_request_id=pcr2.parent_change_request_id
4184: and pcr.wf_item_type is not null;
4185:
4244: l_count number;
4245: item_key wf_items.item_key%type;
4246: item_type wf_items.item_type%type:='POREQCHA';
4247: l_document_id number;
4248: l_document_number po_change_requests.document_num%type;
4249: l_org_id number;
4250: l_requester_id number;
4251: l_requester_name wf_roles.name%type;
4252: l_requester_display_name wf_roles.display_name%type;
4255: l_total_amount_dsp varchar2(100);
4256: cursor change_request(l_change_request_group_id number) is
4257: select document_header_id, nvl(pcr.requester_id, por.preparer_id),
4258: document_num
4259: from po_change_requests pcr, po_requisition_headers_all por
4260: where pcr.change_request_group_id=l_change_request_group_id
4261: and pcr.document_header_id=por.requisition_header_id;
4262:
4263: l_old_req_amount varchar2(40);
4443: l_amount_for_header varchar2(400);
4444: l_amount_for_tax varchar2(400);
4445:
4446: --Bug#5114191
4447: l_inform_item_type PO_CHANGE_REQUESTS.wf_item_type%type;
4448: l_inform_item_key PO_CHANGE_REQUESTS.wf_item_key%type;
4449: l_responder_user_id number;
4450: l_responder_resp_id number;
4451: l_responder_appl_id number;
4444: l_amount_for_tax varchar2(400);
4445:
4446: --Bug#5114191
4447: l_inform_item_type PO_CHANGE_REQUESTS.wf_item_type%type;
4448: l_inform_item_key PO_CHANGE_REQUESTS.wf_item_key%type;
4449: l_responder_user_id number;
4450: l_responder_resp_id number;
4451: l_responder_appl_id number;
4452: l_note varchar2(240); --bug 9685961
4460: document_revision_num,
4461: po_release_id,
4462: wf_item_type,
4463: wf_item_key
4464: from po_change_requests
4465: where change_request_group_id=l_change_request_group_id;
4466:
4467: --SQL What: get the preparer id of the req
4468: --SQL Why: wf attribute needs it
4469: --SQL Join: parent_change_request_id, requisition_header_id
4470: cursor req_preparer_id(l_change_request_group_id number) is
4471: select por.preparer_id
4472: from po_requisition_headers_all por,
4473: po_change_requests pcr1,
4474: po_change_requests pcr2
4475: where pcr2.change_request_group_id=l_change_request_group_id
4476: and pcr2.parent_change_request_id=pcr1.change_request_id
4477: and pcr1.document_header_id=por.requisition_header_id;
4470: cursor req_preparer_id(l_change_request_group_id number) is
4471: select por.preparer_id
4472: from po_requisition_headers_all por,
4473: po_change_requests pcr1,
4474: po_change_requests pcr2
4475: where pcr2.change_request_group_id=l_change_request_group_id
4476: and pcr2.parent_change_request_id=pcr1.change_request_id
4477: and pcr1.document_header_id=por.requisition_header_id;
4478:
4477: and pcr1.document_header_id=por.requisition_header_id;
4478:
4479: cursor get_parent_group_id_csr(l_change_request_group_id number) is
4480: select change_request_group_id
4481: from po_change_requests
4482: where change_request_id in
4483: (select parent_change_request_id
4484: from po_change_requests
4485: where change_request_group_id=l_change_request_group_id);
4480: select change_request_group_id
4481: from po_change_requests
4482: where change_request_id in
4483: (select parent_change_request_id
4484: from po_change_requests
4485: where change_request_group_id=l_change_request_group_id);
4486:
4487: cursor get_parent_info_csr(l_change_request_group_id number) is
4488: select prh.segment1, pcr.wf_item_type, pcr.wf_item_key, prh.requisition_header_id
4485: where change_request_group_id=l_change_request_group_id);
4486:
4487: cursor get_parent_info_csr(l_change_request_group_id number) is
4488: select prh.segment1, pcr.wf_item_type, pcr.wf_item_key, prh.requisition_header_id
4489: from po_requisition_headers_all prh, po_change_requests pcr
4490: where prh.requisition_header_id=pcr.document_header_id
4491: and pcr.change_request_group_id=l_change_request_group_id;
4492:
4493: n_varname Wf_Engine.NameTabTyp;
4846: END IF;
4847:
4848: x_progress :='StartPOChangeWF:011';
4849: if(p_process='INFORM_BUYER_PO_CHANGE') then
4850: update po_change_requests
4851: set wf_item_type=item_type,
4852: wf_item_key=item_key
4853: where change_request_group_id=p_change_request_group_id;
4854: end if;
4933: l_preparer_user_name varchar2(100);
4934:
4935: cursor change_request_group_id is
4936: select max(change_request_group_id)
4937: from po_change_requests
4938: where document_header_id=l_doc_id
4939: and initiator='REQUESTER'
4940: and request_status='NEW';
4941: BEGIN
5124: l_path_id number;
5125: l_change_request_group_id number;
5126: cursor change_request is
5127: select requester_id
5128: from po_change_requests
5129: where change_request_group_id=l_change_request_group_id;
5130:
5131:
5132: BEGIN
5243:
5244: l_doc_string varchar2(200);
5245: l_preparer_user_name varchar2(100);
5246:
5247: l_approval_flag po_change_requests.approval_required_flag%type;
5248:
5249: cursor approval_flag is
5250: select approval_required_flag
5251: from po_change_requests
5247: l_approval_flag po_change_requests.approval_required_flag%type;
5248:
5249: cursor approval_flag is
5250: select approval_required_flag
5251: from po_change_requests
5252: where change_request_group_id=l_change_request_group_id
5253: and action_type='MODIFICATION'
5254: and request_status = 'NEW';
5255:
5503: l_preparer_user_name varchar2(100);
5504:
5505: cursor accepted_change is
5506: select change_request_id
5507: from po_change_requests
5508: where change_request_group_id=l_change_request_group_id
5509: and request_status='MGR_PRE_APP';
5510:
5511: BEGIN
5741: l_preparer_user_name varchar2(100);
5742:
5743: cursor change_request_status is
5744: select request_status
5745: from po_change_requests
5746: where change_request_group_id=l_change_request_group_id
5747: and request_status='MGR_APP';
5748: BEGIN
5749:
5915: l_sequence_num number;
5916:
5917: cursor l_approved_change_request_csr(grp_id number) is
5918: select change_request_id
5919: from po_change_requests
5920: where change_request_group_id=grp_id
5921: and request_status='MGR_APP'
5922: and action_type='MODIFICATION';
5923: cursor l_rejected_change_request_csr(grp_id number) is
5921: and request_status='MGR_APP'
5922: and action_type='MODIFICATION';
5923: cursor l_rejected_change_request_csr(grp_id number) is
5924: select change_request_id
5925: from po_change_requests
5926: where change_request_group_id=grp_id
5927: and request_status='REJECTED'
5928: and action_type='MODIFICATION';
5929: cursor l_approved_cancel_request_csr(grp_id number) is
5927: and request_status='REJECTED'
5928: and action_type='MODIFICATION';
5929: cursor l_approved_cancel_request_csr(grp_id number) is
5930: select change_request_id
5931: from po_change_requests
5932: where change_request_group_id=grp_id
5933: and request_status='MGR_APP'
5934: and action_type='CANCELLATION';
5935:
6221: l_preparer_user_name varchar2(100);
6222:
6223: cursor child_request_group_id is
6224: select distinct pcr1.change_request_group_id
6225: from po_change_requests pcr1, po_change_requests pcr2
6226: where pcr1.parent_change_request_id=pcr2.change_request_id
6227: and pcr2.change_request_group_id=l_change_request_group_id
6228: and pcr1.request_status='PENDING';
6229:
6463: nvl(pcr1.new_price, prl.unit_price)*
6464: nvl(pcr2.new_quantity, prl.quantity)))), 0)
6465: into l_new_tax_amount, l_new_req_amount
6466: from po_requisition_lines_all prl,
6467: po_change_requests pcr1,
6468: po_change_requests pcr2,
6469: po_change_requests pcr3,
6470: po_change_requests pcr4
6471: where prl.requisition_line_id=pcr1.document_line_id(+)
6464: nvl(pcr2.new_quantity, prl.quantity)))), 0)
6465: into l_new_tax_amount, l_new_req_amount
6466: from po_requisition_lines_all prl,
6467: po_change_requests pcr1,
6468: po_change_requests pcr2,
6469: po_change_requests pcr3,
6470: po_change_requests pcr4
6471: where prl.requisition_line_id=pcr1.document_line_id(+)
6472: and pcr1.change_request_group_id(+)=l_change_request_group_id
6465: into l_new_tax_amount, l_new_req_amount
6466: from po_requisition_lines_all prl,
6467: po_change_requests pcr1,
6468: po_change_requests pcr2,
6469: po_change_requests pcr3,
6470: po_change_requests pcr4
6471: where prl.requisition_line_id=pcr1.document_line_id(+)
6472: and pcr1.change_request_group_id(+)=l_change_request_group_id
6473: and pcr1.request_level(+)='LINE'
6466: from po_requisition_lines_all prl,
6467: po_change_requests pcr1,
6468: po_change_requests pcr2,
6469: po_change_requests pcr3,
6470: po_change_requests pcr4
6471: where prl.requisition_line_id=pcr1.document_line_id(+)
6472: and pcr1.change_request_group_id(+)=l_change_request_group_id
6473: and pcr1.request_level(+)='LINE'
6474: and pcr1.new_price(+) is not null
6525: nvl(pcr2.new_quantity,prd.req_line_quantity))), 0)
6526: into l_new_tax_amount, l_new_req_amount
6527: from po_requisition_lines_all prl,
6528: po_req_distributions_all prd,
6529: po_change_requests pcr1,
6530: po_change_requests pcr2
6531: where prl.requisition_line_id=pcr1.document_line_id(+)
6532: and pcr1.change_request_group_id(+)=l_change_request_group_id
6533: and pcr1.request_level(+)='LINE'
6526: into l_new_tax_amount, l_new_req_amount
6527: from po_requisition_lines_all prl,
6528: po_req_distributions_all prd,
6529: po_change_requests pcr1,
6530: po_change_requests pcr2
6531: where prl.requisition_line_id=pcr1.document_line_id(+)
6532: and pcr1.change_request_group_id(+)=l_change_request_group_id
6533: and pcr1.request_level(+)='LINE'
6534: and pcr1.change_active_flag(+)='Y'
6682:
6683: --bug 5440657
6684: -- In 'signature required' case, after buyer accepts the change request,
6685: -- the authorization_status of corresponding PO/REALEASE will be 'pre_approved'(pending signature)
6686: -- This way,is_doc_approved should return 'Y' so that PO change requests will be updated to 'ACCEPTED' status later.
6687:
6688: if(nvl(l_authorization_status, 'IN PROCESS') in ('APPROVED','PRE-APPROVED') ) then
6689: resultout := wf_engine.eng_completed || ':' || 'Y' ;
6690:
7132: l_path_id number;
7133: l_change_request_group_id number;
7134: cursor change_request is
7135: select request_status
7136: from po_change_requests
7137: where change_request_group_id=l_change_request_group_id;
7138:
7139:
7140: BEGIN
7154: aname => 'CHANGE_REQUEST_GROUP_ID');
7155:
7156: select count(distinct(request_status))
7157: into l_count
7158: from po_change_requests
7159: where change_request_group_id=l_change_request_group_id;
7160:
7161: if(l_count=1) then
7162: open change_request;
7406: *
7407: * Public Procedure: Change_Acceptance_Exists
7408: * Effects: workflow procedure, used in PORPOCHA(PROCESS_BUYER_RESPONSE)
7409: *
7410: * check if there is PO change requests with status 'BUYER_APP'
7411: * exists.
7412: *
7413: ************************************************************************/
7414: procedure Change_Acceptance_Exists(itemtype in varchar2,
7427: l_preparer_user_name varchar2(100);
7428:
7429: cursor accepted_change is
7430: select change_request_id
7431: from po_change_requests
7432: where change_request_group_id=l_change_request_group_id
7433: and request_status='BUYER_APP';
7434:
7435: BEGIN
7615: l_exception exception;
7616:
7617: cursor document_id is
7618: select document_header_id, po_release_id
7619: from po_change_requests
7620: where change_request_group_id=l_change_request_group_id
7621: and request_status='BUYER_APP';
7622:
7623: --Set the authorization status of po to
7624: --approved for those change request automatically rejected
7625:
7626: cursor document_id_rejected is
7627: select document_header_id, po_release_id
7628: from po_change_requests
7629: where change_request_group_id=l_change_request_group_id
7630: and request_status='REJECTED';
7631:
7632: BEGIN
7747: --SQL Why: need it to check whether the parent change is fully responded
7748: --SQL Join: parent_change_request_id
7749: cursor pending_parent_change_csr(p_change_request_group_id number) is
7750: select pcr3.change_request_id
7751: from po_change_requests pcr1,
7752: po_change_requests pcr2,
7753: po_change_requests pcr3
7754: where pcr2.change_request_group_id=p_change_request_group_id
7755: and pcr2.parent_change_request_id=pcr1.change_request_id
7748: --SQL Join: parent_change_request_id
7749: cursor pending_parent_change_csr(p_change_request_group_id number) is
7750: select pcr3.change_request_id
7751: from po_change_requests pcr1,
7752: po_change_requests pcr2,
7753: po_change_requests pcr3
7754: where pcr2.change_request_group_id=p_change_request_group_id
7755: and pcr2.parent_change_request_id=pcr1.change_request_id
7756: and pcr1.change_request_group_id=pcr3.change_request_group_id
7749: cursor pending_parent_change_csr(p_change_request_group_id number) is
7750: select pcr3.change_request_id
7751: from po_change_requests pcr1,
7752: po_change_requests pcr2,
7753: po_change_requests pcr3
7754: where pcr2.change_request_group_id=p_change_request_group_id
7755: and pcr2.parent_change_request_id=pcr1.change_request_id
7756: and pcr1.change_request_group_id=pcr3.change_request_group_id
7757: and pcr3.action_type in ('MODIFICATION', 'CANCELLATION')
7969: x_progress := 'PO_ReqChangeRequestWF_PVT.Get_Total_Amount_Currency: 03';
7970:
7971: SELECT wf_item_type, wf_item_key
7972: INTO l_req_item_type, l_req_item_key
7973: FROM po_change_requests
7974: WHERE
7975: change_request_group_id = l_req_change_group_id and rownum=1;
7976:
7977: --bug 5379796,if POREQCHA wf has been purged before this moment,
8183:
8184: else
8185: SELECT document_header_id
8186: INTO l_document_id
8187: FROM po_change_requests pcr
8188: WHERE pcr.change_request_group_id = l_req_change_group_id and rownum=1;
8189:
8190: PO_WF_UTIL_PKG.SetItemAttrNumber ( itemtype => itemtype,
8191: itemkey => itemkey,
8336: l_preparer_user_name varchar2(100);
8337:
8338: cursor parent_change(p_change_request_group_id number) is
8339: select pcr1.change_request_group_id
8340: from po_change_requests pcr1, po_change_requests pcr2
8341: where pcr2.change_request_group_id=p_change_request_group_id
8342: and pcr2.parent_change_request_id=pcr1.change_request_id;
8343:
8344: -- this is call from child po, check for parent req
8343:
8344: -- this is call from child po, check for parent req
8345: cursor pending_change(p_change_request_group_id number) is
8346: select pcr1.change_request_id
8347: from po_change_requests pcr1
8348: where pcr1.change_request_group_id=p_change_request_group_id
8349: and pcr1.request_status not in ('ACCEPTED', 'REJECTED');
8350:
8351: cursor pending_child_change(p_change_request_group_id number) is
8349: and pcr1.request_status not in ('ACCEPTED', 'REJECTED');
8350:
8351: cursor pending_child_change(p_change_request_group_id number) is
8352: select pcr1.change_request_id
8353: from po_change_requests pcr1, po_change_requests pcr2
8354: where pcr2.change_request_group_id=p_change_request_group_id
8355: and pcr1.parent_change_request_id=pcr2.change_request_id
8356: and pcr1.request_status not in ('ACCEPTED', 'REJECTED');
8357:
8455: l_buyer_id number;
8456: l_buyer_name varchar2(100);
8457: l_buyer_display_name varchar2(300);
8458: l_document_type_disp varchar2(200);
8459: l_document_num po_change_requests.document_num%type;
8460: l_document_revision NUMBER;
8461:
8462: cursor l_get_group_id_po_csr is
8463: select change_request_group_id, wf_item_type, wf_item_key
8460: l_document_revision NUMBER;
8461:
8462: cursor l_get_group_id_po_csr is
8463: select change_request_group_id, wf_item_type, wf_item_key
8464: from po_change_requests
8465: where document_header_id=l_document_id
8466: and document_type=l_document_type
8467: and initiator='REQUESTER'
8468: and request_status in ('PENDING', 'BUYER_APP');
8467: and initiator='REQUESTER'
8468: and request_status in ('PENDING', 'BUYER_APP');
8469: cursor l_get_group_id_rel_csr is
8470: select change_request_group_id, wf_item_type, wf_item_key
8471: from po_change_requests
8472: where po_release_id=l_document_id
8473: and document_type=l_document_type
8474: and initiator='REQUESTER'
8475: and request_status in ('PENDING', 'BUYER_APP');
8474: and initiator='REQUESTER'
8475: and request_status in ('PENDING', 'BUYER_APP');
8476: cursor l_get_parent_group_id_csr is
8477: select change_request_group_id
8478: from po_change_requests
8479: where change_request_id in
8480: (select parent_change_request_id
8481: from po_change_requests
8482: where change_request_group_id=l_change_request_group_id);
8477: select change_request_group_id
8478: from po_change_requests
8479: where change_request_id in
8480: (select parent_change_request_id
8481: from po_change_requests
8482: where change_request_group_id=l_change_request_group_id);
8483: cursor change_request(l_change_request_group_id number) is
8484: select nvl(pcr.requester_id, por.preparer_id)
8485: from po_change_requests pcr, po_requisition_headers_all por
8481: from po_change_requests
8482: where change_request_group_id=l_change_request_group_id);
8483: cursor change_request(l_change_request_group_id number) is
8484: select nvl(pcr.requester_id, por.preparer_id)
8485: from po_change_requests pcr, po_requisition_headers_all por
8486: where pcr.change_request_group_id=l_change_request_group_id
8487: and pcr.document_header_id=por.requisition_header_id;
8488:
8489: BEGIN
8643: l_preparer_user_name varchar2(100);
8644:
8645: cursor new_change(p_change_request_group_id number) is
8646: select change_request_id
8647: from po_change_requests
8648: where change_request_group_id=p_change_request_group_id
8649: and request_status='PENDING';
8650:
8651: BEGIN
8713: * Public Procedure: Record_PO_Approval
8714: * Effects: workflow procedure, used in PORPOCHA(RECEIVE_REQ_CHANGE_EVENT)
8715: *
8716: * when the po is approved, update the status of the corresponding
8717: * PO change requests to ACCEPTED
8718: *
8719: ************************************************************************/
8720: procedure Record_PO_Approval(itemtype in varchar2,
8721: itemkey in varchar2,
8779: * Public Procedure: Record_PO_Rejection
8780: * Effects: workflow procedure, used in PORPOCHA(RECEIVE_REQ_CHANGE_EVENT)
8781: *
8782: * when the po is rejected, update the status of the corresponding
8783: * PO change requests to REJECTED
8784: *
8785: ************************************************************************/
8786: procedure Record_PO_Rejection(itemtype in varchar2,
8787: itemkey in varchar2,
8930: l_change_request_id number:=null;
8931: x_progress varchar2(3) := '000';
8932: cursor l_pending_req_po_chg_csr is
8933: select change_request_id
8934: from po_change_requests
8935: where document_header_id=l_document_id
8936: and request_status in ('BUYER_APP', 'PENDING')
8937: and initiator='REQUESTER'
8938: and document_type='PO';
8937: and initiator='REQUESTER'
8938: and document_type='PO';
8939: cursor l_pending_req_rel_chg_csr is
8940: select change_request_id
8941: from po_change_requests
8942: where po_release_id=l_document_id
8943: and request_status in ('BUYER_APP', 'PENDING')
8944: and initiator='REQUESTER'
8945: and document_type='RELEASE';
9049:
9050: l_index number;
9051: l_last number;
9052: l_change_request_id number;
9053: l_action_type PO_CHANGE_REQUESTS.ACTION_TYPE%TYPE;
9054: l_change_request_group_id number;
9055:
9056: l_wf_item_type wf_items.item_type%TYPE;
9057: l_wf_item_key wf_items.item_key%type;
9067: l_api_version CONSTANT NUMBER := 1.0;
9068:
9069: cursor l_pending_change_csr(group_id in number) is
9070: select change_request_id
9071: from po_change_requests
9072: where change_request_group_id=group_id
9073: and request_status in ('NEW', 'MGR_PRE_APP');
9074: cursor l_pending_change_line_csr(requisition_line_id in number) is
9075: select pcr1.change_request_id, pcr1.document_header_id,
9074: cursor l_pending_change_line_csr(requisition_line_id in number) is
9075: select pcr1.change_request_id, pcr1.document_header_id,
9076: pcr1.action_type, pcr1.change_request_group_id,
9077: pcr1.wf_item_type, pcr1.wf_item_key
9078: from po_change_requests pcr1
9079: where pcr1.document_type='REQ'
9080: and pcr1.document_line_id=requisition_line_id
9081: and pcr1.action_type <>'DERIVED'
9082: and pcr1.request_status in ('NEW', 'MGR_PRE_APP', 'MGR_APP')
9081: and pcr1.action_type <>'DERIVED'
9082: and pcr1.request_status in ('NEW', 'MGR_PRE_APP', 'MGR_APP')
9083: and not exists
9084: (select pcr2.change_request_id
9085: from po_change_requests pcr2
9086: where pcr2.parent_change_request_id=pcr1.change_request_id);
9087: begin
9088: IF NOT FND_API.compatible_api_call(l_api_version, p_api_version,
9089: l_api_name, g_pkg_name) THEN
9114: -- accept the cancellation request
9115: update po_requisition_lines_all
9116: set cancel_flag='Y'
9117: where requisition_line_id=p_CanceledReqLineIDs_tbl(l_index);
9118: update po_change_requests
9119: set request_status='ACCEPTED',
9120: change_active_flag='N',
9121: response_date=sysdate,
9122: response_reason=fnd_message.get_string('PO', 'PO_RCO_PO_CANCELLED')
9138:
9139: else
9140: -- reject the change request
9141: -- it can have multiple records, so we can't use change request id
9142: update po_change_requests
9143: set request_status='REJECTED',
9144: change_active_flag='N',
9145: response_date=sysdate,
9146: response_reason=fnd_message.get_string('PO', 'PO_RCO_PO_CANCELLED')
9238: l_preparer_id number;
9239: l_req_num po_requisition_headers_all.segment1%type;
9240: cursor change_request_group_id is
9241: select max(change_request_group_id)
9242: from po_change_requests
9243: where document_header_id = p_req_header_id
9244: and initiator='REQUESTER'
9245: and request_status='NEW';
9246:
9328: l_responsibility_id number;
9329:
9330: cursor get_parent_info_csr(l_change_request_group_id number) is
9331: select pcr.wf_item_type, pcr.wf_item_key
9332: from po_change_requests pcr, po_change_requests pcr2
9333: where pcr2.change_request_group_id=l_change_request_group_id
9334: and pcr.change_request_id=pcr2.parent_change_request_id
9335: and pcr.wf_item_type is not null;
9336:
9395: END IF;
9396:
9397: x_return_status := FND_API.g_ret_sts_success;
9398:
9399: update po_change_requests
9400: set request_status=p_acceptance_flag,
9401: change_active_flag=decode(p_acceptance_flag, 'REJECTED', 'N', 'Y'),
9402: response_date=sysdate,
9403: response_reason=p_response_reason,
9405: where change_request_id=p_change_request_id;
9406:
9407: select wf_item_type, wf_item_key
9408: into l_item_type, l_item_key
9409: from po_change_requests
9410: where change_request_id=p_change_request_id;
9411:
9412: begin
9413: open l_notif_status_csr('NEW_PO_CHANGE');
9472:
9473:
9474: cursor get_parent_info_csr(l_change_request_group_id number) is
9475: select pcr.wf_item_type, pcr.wf_item_key
9476: from po_change_requests pcr, po_change_requests pcr2
9477: where pcr2.change_request_group_id=l_change_request_group_id
9478: and pcr.change_request_id=pcr2.parent_change_request_id
9479: and pcr.wf_item_type is not null;
9480: begin
9639: x_progress := '001';
9640:
9641: select max(change_request_id)
9642: into l_change_request_id
9643: from po_change_requests
9644: where po_release_id=l_document_id
9645: and request_status in ('BUYER_APP', 'PENDING')
9646: and initiator='REQUESTER'
9647: and document_type='RELEASE'
9651: x_progress := '002';
9652:
9653: select max(change_request_id)
9654: into l_change_request_id
9655: from po_change_requests
9656: where document_header_id=l_document_id
9657: and request_status in ('BUYER_APP', 'PENDING')
9658: and initiator='REQUESTER'
9659: and document_type='PO'
9741: pcr.action_type,
9742: pol.item_id,
9743: pll.unit_meas_lookup_code,
9744: pol.unit_price
9745: FROM po_change_requests pcr, po_lines_all pol,
9746: po_line_locations_all pll
9747: WHERE pcr.change_request_group_id= p_change_request_group_id
9748: AND pcr.request_status IN ('PENDING', 'ACCEPTED')
9749: AND pcr.document_header_id=pol.po_header_id
9763: select poh.rate
9764: into l_rate
9765: from po_headers_all poh
9766: where poh.po_header_id in (
9767: select document_header_id from po_change_requests
9768: where change_request_group_id = p_change_request_group_id ) ;
9769:
9770:
9771: if (l_functional_currency_code <> p_po_currency) then
9809: if ( l_line_loc_id is null ) then
9810: Begin
9811: SELECT 1
9812: into l_shipment_chg_exists_amt
9813: FROM po_change_requests pcr
9814: WHERE pcr.change_request_group_id= p_change_request_group_id
9815: AND pcr.request_status IN ('PENDING', 'ACCEPTED')
9816: AND pcr.document_header_id= l_header_id
9817: AND pcr.document_line_id= l_line_id
9874:
9875: Begin
9876: SELECT 1
9877: into l_shipment_chg_exists_qty
9878: FROM po_change_requests pcr
9879: WHERE pcr.change_request_group_id= p_change_request_group_id
9880: AND pcr.request_status IN ('PENDING', 'ACCEPTED')
9881: AND pcr.document_header_id= l_header_id
9882: AND pcr.document_line_id= l_line_id
10081: l_change_request_group_id NUMBER;
10082: l_po_request_group_id NUMBER;
10083: l_document_header_id NUMBER;
10084: l_document_revision_num NUMBER;
10085: l_document_type po_change_requests.document_type%type;
10086: l_header_id NUMBER;
10087: l_release_id NUMBER;
10088: l_req_document_id NUMBER;
10089: x_progress VARCHAR2(3) := '000';
10097: x_progress := '001';
10098:
10099: select change_request_group_id
10100: INTO l_po_request_group_id
10101: FROM po_change_requests
10102: WHERE parent_change_request_id = l_change_request_group_id
10103: and rownum=1;
10104:
10105: x_progress := '002';
10112: --For Release the document_header_id will be null.Hence query the po_release_id
10113: --to set the release id to pass it to PO_CHANGEORDERWF_PVT.NotifySupAllChgRpdWF
10114: SELECT document_header_id, document_revision_num, document_type, po_release_id
10115: INTO l_document_header_id, l_document_revision_num, l_document_type, l_release_id
10116: FROM po_change_requests
10117: WHERE change_request_group_id = l_po_request_group_id
10118: AND rownum=1;
10119:
10120: x_progress := '004';
10174: x_progress := '002';
10175:
10176: SELECT change_request_group_id
10177: INTO l_po_chg_request_group_id
10178: FROM po_change_requests
10179: WHERE parent_change_request_id = l_change_request_group_id
10180: AND rownum=1;
10181:
10182: x_progress := '003';
10328: l_po_chg_group_id NUMBER;
10329: l_po_org_id NUMBER;
10330: l_progress VARCHAR2(100) := '000';
10331: l_creation_method po_headers_all.document_creation_method%TYPE;
10332: l_document_type po_change_requests.document_type%TYPE;
10333: BEGIN
10334:
10335: l_po_chg_group_id := PO_WF_UTIL_PKG.GetItemAttrNumber (
10336: itemtype => itemtype,
10351: SELECT poh.document_creation_method, poh.org_id
10352: INTO l_creation_method, l_po_org_id
10353: FROM
10354: po_headers_all poh,
10355: po_change_requests pcr
10356: WHERE poh.po_header_id =pcr.document_header_id
10357: AND pcr.change_request_group_id = l_po_chg_group_id
10358: AND rownum=1;
10359:
10364: SELECT por.document_creation_method, por.org_id
10365: INTO l_creation_method, l_po_org_id
10366: FROM
10367: po_releases_all por,
10368: po_change_requests pcr
10369: WHERE por.po_release_id = pcr.po_release_id
10370: AND pcr.change_request_group_id = l_po_chg_group_id
10371: AND rownum = 1;
10372:
10414: l_po_chg_request_group_id number;
10415: l_next_po_grp_id number;
10416: l_result varchar2(1) := 'N';
10417: l_change_request_grp_id number;
10418: l_po_document_type po_change_requests.document_type%type;
10419: l_po_document_rev po_change_requests.document_revision_num%type;
10420: l_po_document_id po_change_requests.document_header_id%type;
10421: l_progress VARCHAR2(100);
10422: BEGIN
10415: l_next_po_grp_id number;
10416: l_result varchar2(1) := 'N';
10417: l_change_request_grp_id number;
10418: l_po_document_type po_change_requests.document_type%type;
10419: l_po_document_rev po_change_requests.document_revision_num%type;
10420: l_po_document_id po_change_requests.document_header_id%type;
10421: l_progress VARCHAR2(100);
10422: BEGIN
10423: -- Do nothing in cancel or timeout mode
10416: l_result varchar2(1) := 'N';
10417: l_change_request_grp_id number;
10418: l_po_document_type po_change_requests.document_type%type;
10419: l_po_document_rev po_change_requests.document_revision_num%type;
10420: l_po_document_id po_change_requests.document_header_id%type;
10421: l_progress VARCHAR2(100);
10422: BEGIN
10423: -- Do nothing in cancel or timeout mode
10424: if (funcmode <> wf_engine.eng_run) then
10446:
10447: SELECT MIN(po_change.change_request_group_id)
10448: INTO l_next_po_grp_id
10449: FROM
10450: po_change_requests po_change,
10451: po_change_requests req_change
10452: WHERE
10453: po_change.parent_change_request_id = req_change.change_request_id AND
10454: req_change.change_request_group_id = l_change_request_grp_id AND
10447: SELECT MIN(po_change.change_request_group_id)
10448: INTO l_next_po_grp_id
10449: FROM
10450: po_change_requests po_change,
10451: po_change_requests req_change
10452: WHERE
10453: po_change.parent_change_request_id = req_change.change_request_id AND
10454: req_change.change_request_group_id = l_change_request_grp_id AND
10455: po_change.change_request_group_id > l_po_chg_request_group_id
10467:
10468: SELECT document_type, document_revision_num,
10469: decode(document_type, 'RELEASE', po_release_id,document_header_id)
10470: into l_po_document_type, l_po_document_rev, l_po_document_id
10471: FROM po_change_requests
10472: WHERE change_request_group_id = l_next_po_grp_id AND rownum=1;
10473:
10474: PO_WF_UTIL_PKG.SetItemAttrText( itemtype => itemtype,
10475: itemkey => itemkey,
10515: l_buyer_id number;
10516: l_buyer_name varchar2(100);
10517: l_buyer_display_name varchar2(300);
10518: l_order_date date;
10519: l_document_number po_change_requests.document_num%type;
10520: l_progress VARCHAR2(100);
10521: l_po_header_id number;
10522: l_po_release_id number;
10523: l_return_status varchar2(1);
10545: l_progress := 'PO_ReqChangeRequestWF_PVT.Accept_Po_Changes: 02';
10546:
10547: SELECT document_type, decode(document_type, 'RELEASE', po_release_id, document_header_id), document_num
10548: INTO l_document_type, l_document_id, l_document_number
10549: FROM po_change_requests
10550: WHERE change_request_group_id = l_po_change_request_group_id
10551: AND rownum=1;
10552:
10553: IF (l_document_type = 'RELEASE') THEN
10622: BEGIN
10623:
10624: SELECT document_header_id, po_release_id
10625: INTO l_po_header_id, l_po_release_id
10626: FROM po_change_requests
10627: WHERE change_request_group_id=l_po_change_request_group_id
10628: and request_status='BUYER_APP' and rownum=1;
10629: EXCEPTION
10630: WHEN NO_DATA_FOUND THEN
10785: FROM dual
10786: WHERE exists (
10787: SELECT 'N'
10788: FROM
10789: po_change_requests pcr, -- for quantity/amount change
10790: po_change_requests pcr1, -- for unit price change
10791: po_lines_all pl,
10792: po_distributions_all pod
10793: WHERE pl.po_line_id = pod.po_line_id
10786: WHERE exists (
10787: SELECT 'N'
10788: FROM
10789: po_change_requests pcr, -- for quantity/amount change
10790: po_change_requests pcr1, -- for unit price change
10791: po_lines_all pl,
10792: po_distributions_all pod
10793: WHERE pl.po_line_id = pod.po_line_id
10794: AND pcr.change_request_group_id = p_pochggrp_id
10863: l_progress varchar2(6);
10864:
10865: cursor l_line_info_csr is
10866: select distinct pcr.document_line_id, document_type
10867: from po_change_requests pcr,
10868: po_lines_all pol
10869: where change_request_group_id = p_pochggrp_id
10870: and pol.po_line_id = pcr.document_line_id
10871: and action_type <> 'DERIVED'
10884: INTO l_return_val
10885: FROM dual
10886: WHERE exists (
10887: SELECT 'N'
10888: FROM po_change_requests pcr
10889: WHERE change_request_group_id = p_pochggrp_id
10890: AND action_type='MODIFICATION'
10891: AND request_status not in ('ACCEPTED', 'REJECTED')
10892: AND request_level='SHIPMENT'
10915: INTO l_return_val
10916: FROM dual
10917: WHERE exists (
10918: SELECT 'N'
10919: FROM po_change_requests pcr
10920: WHERE change_request_group_id = p_pochggrp_id
10921: AND action_type='MODIFICATION'
10922: AND request_status not in ('ACCEPTED', 'REJECTED')
10923: AND request_level='LINE'
11045: BEGIN
11046:
11047: select distinct document_type
11048: into l_document_type
11049: from po_change_requests
11050: where change_request_group_id = p_pochggrp_id;
11051:
11052: if ( l_document_type = 'PO' ) then
11053:
11069: p_tolerances_tab(TOL_POTOTAL_IND).max_decrement,
11070: p_tolerances_tab(TOL_POTOTAL_AMT_IND).max_increment,
11071: p_tolerances_tab(TOL_POTOTAL_AMT_IND).max_decrement) INTO l_return_val
11072: FROM
11073: po_change_requests pcr,
11074: po_change_requests pcr1,
11075: po_change_requests pcr2,
11076: po_lines_all pl,
11077: po_distributions_all pod
11070: p_tolerances_tab(TOL_POTOTAL_AMT_IND).max_increment,
11071: p_tolerances_tab(TOL_POTOTAL_AMT_IND).max_decrement) INTO l_return_val
11072: FROM
11073: po_change_requests pcr,
11074: po_change_requests pcr1,
11075: po_change_requests pcr2,
11076: po_lines_all pl,
11077: po_distributions_all pod
11078: WHERE pl.po_line_id = pod.po_line_id
11071: p_tolerances_tab(TOL_POTOTAL_AMT_IND).max_decrement) INTO l_return_val
11072: FROM
11073: po_change_requests pcr,
11074: po_change_requests pcr1,
11075: po_change_requests pcr2,
11076: po_lines_all pl,
11077: po_distributions_all pod
11078: WHERE pl.po_line_id = pod.po_line_id
11079: AND pcr.change_request_group_id(+) = p_pochggrp_id
11109: pll.line_location_id)) ) )
11110:
11111: into l_old_amount_release, l_new_amount_release
11112:
11113: FROM po_change_requests pcr,
11114: po_lines_all pol,
11115: po_line_locations_all pll
11116: WHERE pcr.change_request_group_id= p_pochggrp_id
11117: AND pcr.po_release_id = p_poheader_id
11297: l_req_request_group_id NUMBER;
11298: l_po_request_group_id NUMBER;
11299: l_next_req_grp_id NUMBER;
11300: l_document_id NUMBER;
11301: l_document_num po_change_requests.document_num%type;
11302: l_result VARCHAR2(1) := 'N';
11303: l_progress VARCHAR2(100);
11304:
11305: BEGIN
11329:
11330: SELECT MIN(req_change.change_request_group_id)
11331: INTO l_next_req_grp_id
11332: FROM
11333: po_change_requests po_change,
11334: po_change_requests req_change
11335: WHERE
11336: po_change.change_request_group_id = l_po_request_group_id AND
11337: po_change.parent_change_request_id = req_change.change_request_id AND
11330: SELECT MIN(req_change.change_request_group_id)
11331: INTO l_next_req_grp_id
11332: FROM
11333: po_change_requests po_change,
11334: po_change_requests req_change
11335: WHERE
11336: po_change.change_request_group_id = l_po_request_group_id AND
11337: po_change.parent_change_request_id = req_change.change_request_id AND
11338: req_change.change_request_group_id > l_req_request_group_id;
11348: avalue => l_next_req_grp_id );
11349:
11350: SELECT document_header_id, document_num
11351: INTO l_document_id, l_document_num
11352: FROM po_change_requests
11353: WHERE change_request_group_id = l_next_req_grp_id AND rownum=1;
11354:
11355: PO_WF_UTIL_PKG.SetItemAttrNumber( itemtype => itemtype,
11356: itemkey => itemkey,
11561: decode(pol.matching_basis, 'AMOUNT',
11562: ( nvl(pll.amount,0)-nvl(pll.amount_cancelled,0) ),
11563: ( nvl(pll.quantity,0)- nvl(pll.quantity_cancelled,0)) * pol.unit_price
11564: )
11565: from po_change_requests,
11566: po_lines_all pol,
11567: po_line_locations_all pll
11568: where change_request_group_id = p_chg_request_grp_id
11569: and document_line_id = p_po_line_id
11607: if ( l_po_matching_basis = 'QUANTITY' ) then
11608: begin
11609: SELECT pcr.new_price,pcr.new_price
11610: into l_pcr_new_price,l_new_price
11611: FROM po_change_requests pcr
11612:
11613: WHERE pcr.change_request_group_id = p_chg_request_grp_id
11614: AND pcr.document_line_id = p_po_line_id
11615: AND pcr.action_type(+) = 'MODIFICATION'
11653:
11654: begin
11655: select (pcr.new_amount - pll.amount)
11656: into l_shipmt_amt_increase
11657: from po_change_requests pcr,
11658: po_line_locations_all pll
11659: where pcr.change_request_group_id = p_chg_request_grp_id
11660: and pcr.document_line_location_id = l_pcr_line_loc_id
11661: and pll.line_location_id = l_pcr_line_loc_id
11677: -- get new quantity
11678: begin
11679: SELECT pcr.new_quantity,pcr.new_quantity,pll.unit_meas_lookup_code,pll.price_override
11680: into l_pcr_new_quantity,l_new_quantity,l_unit_lookup_code,l_price_override
11681: FROM po_change_requests pcr,
11682: po_line_locations_all pll
11683: WHERE pcr.change_request_group_id = p_chg_request_grp_id
11684: AND pcr.document_line_location_id = l_pcr_line_loc_id
11685: AND pll.line_location_id = l_pcr_line_loc_id
11745:
11746: select decode(pol.matching_basis, 'AMOUNT',pol.amount,
11747: (nvl(pcr.new_price,pol.unit_price) * pol.quantity ))
11748: into l_new_amount
11749: from po_change_requests pcr,
11750: po_lines_all pol
11751: where pcr.change_request_group_id = p_chg_request_grp_id
11752: and pol.po_line_id = p_po_line_id
11753: and pcr.document_line_id = p_po_line_id
11815: p_source_type_code VARCHAR2(20) := 'INVENTORY';
11816: l_req_num po_requisition_headers_all.segment1%TYPE;
11817: CURSOR change_request_group_id IS
11818: SELECT MAX(change_request_group_id)
11819: FROM po_change_requests
11820: WHERE document_header_id = p_req_header_id
11821: AND initiator = 'REQUESTER'
11822: AND request_status = 'NEW';
11823:
11831: l_return_status varchar2(1);
11832: l_orgid number;
11833: CURSOR req_line_id_chn_csr(grp_id NUMBER) IS
11834: SELECT DISTINCT document_line_id
11835: FROM po_change_requests
11836: WHERE change_request_group_id = grp_id;
11837:
11838:
11839: BEGIN
11987: pcr.new_quantity,
11988: pcr.old_need_by_date,
11989: pcr.new_need_by_date,
11990: pcr.action_type
11991: FROM po_change_requests pcr,
11992: po_requisition_lines_all prl
11993: WHERE pcr.change_request_group_id = l_change_request_group_id
11994: AND pcr.request_status = 'MGR_APP'
11995: AND pcr.document_line_id = prl.requisition_line_id
12000:
12001: CURSOR iso_change_csr_rejected(l_change_request_group_id IN NUMBER) IS
12002: SELECT prl.requisition_header_id,
12003: prl.requisition_line_id
12004: FROM po_change_requests pcr,
12005: po_requisition_lines_all prl
12006: WHERE pcr.change_request_group_id = l_change_request_group_id
12007: AND pcr.request_status = 'REJECTED'
12008: AND pcr.document_line_id = prl.requisition_line_id
12115: --CHANGE_REQUEST_ID for the sendng of notification
12116:
12117: SELECT min(CHANGE_REQUEST_ID)
12118: into L_CHANGE_REQUEST_ID
12119: FROM po_change_requests
12120: WHERE change_request_group_id = l_change_request_group_id
12121: AND request_status = 'ACCEPTED';
12122:
12123: po_wf_util_pkg.setitemattrnumber (itemtype => itemtype,
12345: IF (p_action_type = 'MODIFICATION') THEN
12346:
12347: l_progress := '009';
12348:
12349: UPDATE po_change_requests
12350: SET request_status = 'ACCEPTED'
12351: WHERE change_request_group_id = p_chn_request_group_id
12352: AND document_header_id = l_document_header_id
12353: AND document_line_id = p_document_line_id ;
12379: - update po req tables */
12380:
12381: l_progress := '012';
12382:
12383: UPDATE po_change_requests
12384: SET request_status = 'ACCEPTED'
12385: WHERE change_request_group_id = p_chn_request_group_id
12386: AND document_header_id = l_document_header_id
12387: AND document_line_id = p_document_line_id ;
12386: AND document_header_id = l_document_header_id
12387: AND document_line_id = p_document_line_id ;
12388:
12389: IF g_debug_stmt THEN
12390: po_debug.debug_stmt(l_log_head, l_progress,'UPDATED po_change_requests');
12391: END IF;
12392:
12393: req_line_CANCEL(p_req_line_id => p_document_line_id,
12394: x_return_status =>l_return_status);
12404: END IF;
12405:
12406: ROLLBACK TO convertintosochange_SP; -- revert the so changes
12407:
12408: UPDATE po_change_requests
12409: SET request_status = 'REJECTED',
12410: change_active_flag = 'N',
12411: response_reason=substr(l_msg_data, 1, 2000),
12412: response_date=sysdate
13108: varchar query should be
13109:
13110: SELECT mp.EMPLOYEE_ID
13111: into l_planner_id
13112: FROM po_change_requests pcr,
13113: po_requisition_lines_all prl,
13114: mtl_system_items_b mi,
13115: financials_system_params_all fsp,
13116: mtl_planners mp
13134: aname => 'PLANNERS_NOTIFIED');
13135:
13136: l_sql := '
13137: SELECT mp.EMPLOYEE_ID
13138: FROM po_change_requests pcr,
13139: po_requisition_lines_all prl,
13140: mtl_system_items_b mi,
13141: financials_system_params_all fsp,
13142: mtl_planners mp
13250: */
13251: BEGIN
13252: SELECT min(CHANGE_REQUEST_ID)
13253: into L_NEW_CHANGE_REQUEST_ID
13254: FROM po_change_requests
13255: WHERE change_request_group_id = l_change_request_group_id
13256: AND request_status = 'ACCEPTED'
13257: and CHANGE_REQUEST_ID > l_old_change_request_id;
13258: EXCEPTION
13678: l_line_id NUMBER;
13679: l_line_loc_id NUMBER;
13680:
13681: BEGIN
13682: SELECT DOCUMENT_LINE_LOCATION_ID INTO l_line_loc_id FROM po_change_requests WHERE CHANGE_REQUEST_ID = l_change_request_id ;
13683:
13684: IF(l_line_loc_id IS NOT NULL) THEN
13685: SELECT REQUISITION_LINE_ID INTO l_line_id FROM po_requisition_lines_all WHERE LINE_LOCATION_ID = l_line_loc_id;
13686: RETURN l_line_id;
13684: IF(l_line_loc_id IS NOT NULL) THEN
13685: SELECT REQUISITION_LINE_ID INTO l_line_id FROM po_requisition_lines_all WHERE LINE_LOCATION_ID = l_line_loc_id;
13686: RETURN l_line_id;
13687: ELSE
13688: SELECT DOCUMENT_LINE_ID INTO l_line_id FROM po_change_requests WHERE CHANGE_REQUEST_ID
13689: = (SELECT PARENT_CHANGE_REQUEST_ID FROM po_change_requests WHERE CHANGE_REQUEST_ID= l_change_request_id);
13690: RETURN l_line_id;
13691: END IF;
13692: EXCEPTION
13685: SELECT REQUISITION_LINE_ID INTO l_line_id FROM po_requisition_lines_all WHERE LINE_LOCATION_ID = l_line_loc_id;
13686: RETURN l_line_id;
13687: ELSE
13688: SELECT DOCUMENT_LINE_ID INTO l_line_id FROM po_change_requests WHERE CHANGE_REQUEST_ID
13689: = (SELECT PARENT_CHANGE_REQUEST_ID FROM po_change_requests WHERE CHANGE_REQUEST_ID= l_change_request_id);
13690: RETURN l_line_id;
13691: END IF;
13692: EXCEPTION
13693: WHEN OTHERS THEN