[Home] [Help]
570:
571: -- start of fix for bug 3864512
572: -- update status of buyer approved cancel shipment requests to a special status WAIT_CANCEL_APP
573:
574: update po_change_requests
575: set request_status = 'WAIT_CANCEL_APP'
576: where request_status = 'BUYER_APP'
577: and action_type = 'CANCELLATION'
578: and request_level = 'SHIPMENT'
689: l_can_hdr_id number;
690: l_can_rel_id number;
691: l_can_line_id number;
692: l_can_line_loc_id number;
693: l_can_reason po_change_requests.request_reason%TYPE;
694: l_cancel_backing_req po_change_requests.cancel_backing_req%TYPE;
695: l_can_doc_type po_document_types_all.document_type_code%TYPE;
696: l_can_doc_subtype po_document_types_all.document_subtype%TYPE;
697: l_type_lookup_code po_headers_all.type_lookup_code%TYPE;
690: l_can_rel_id number;
691: l_can_line_id number;
692: l_can_line_loc_id number;
693: l_can_reason po_change_requests.request_reason%TYPE;
694: l_cancel_backing_req po_change_requests.cancel_backing_req%TYPE;
695: l_can_doc_type po_document_types_all.document_type_code%TYPE;
696: l_can_doc_subtype po_document_types_all.document_subtype%TYPE;
697: l_type_lookup_code po_headers_all.type_lookup_code%TYPE;
698: l_return_status varchar2(1);
713: document_line_location_id,
714: request_reason,
715: document_type,
716: cancel_backing_req
717: from po_change_requests
718: where change_request_group_id = grp_id
719: and action_type = 'CANCELLATION'
720: and request_level = 'SHIPMENT'
721: and request_status = 'BUYER_APP';
726: document_header_id,
727: po_release_id,
728: request_reason
729: ,document_type
730: from po_change_requests
731: where change_request_group_id = grp_id
732: and action_type = 'CANCELLATION'
733: and request_level = 'HEADER'
734: and request_status = 'BUYER_APP';
736: begin
737: x_return_status := FND_API.G_RET_STS_SUCCESS;
738: /* Code changes for bug 13114334 - Start */
739: select count(*) into l_cancel_count
740: from po_change_requests
741: where change_request_group_id=p_chg_req_grp_id
742: and request_status='BUYER_APP'
743: and request_level = 'SHIPMENT'
744: and action_type='CANCELLATION';
815:
816: --If PO Cancel API fails, we'll assume buyer has rejected the cancellation request
817: if(l_return_status <> 'S') then
818: x_return_status := FND_API.G_RET_STS_ERROR;
819: update po_change_requests
820: set request_status = 'REJECTED',
821: response_reason = l_ship_can_err_msg,
822: validation_error = l_ship_can_err_msg
823: -- change_active_flag = 'N' /* commented out due to bug 3574114 */
884:
885: --If PO Cancel API fails, we'll assume buyer has rejected the cancellation request
886: if(l_return_status <> 'S') then
887: x_return_status := FND_API.G_RET_STS_ERROR;
888: update po_change_requests
889: set request_status = 'REJECTED' ,
890: response_reason = l_hdr_can_err_msg,
891: validation_error = l_hdr_can_err_msg,
892: change_active_flag = 'N'
1549: l_id number;
1550:
1551: cursor l_header_csr(hdr_id number, rev_num number) is
1552: select change_request_id
1553: from po_change_requests
1554: where document_header_id = hdr_id
1555: and document_revision_num = rev_num
1556: and request_status = 'PENDING'
1557: and initiator = 'SUPPLIER'
1563: and po_line_location_id is not null;
1564:
1565: cursor l_release_csr(rel_id number,rev_num number) is
1566: select change_request_id
1567: from po_change_requests
1568: where po_release_id = rel_id
1569: and document_revision_num = rev_num
1570: and request_status = 'PENDING'
1571: and initiator = 'SUPPLIER'
1577: and po_line_location_id is not null;
1578:
1579: BEGIN
1580: x_result_code := 'Y';
1581: -- If any Pending records exist in PO_CHANGE_REQUESTS table, that means the PO is not really header rejected
1582: if(p_header_id is not null) then
1583: open l_header_csr(p_header_id, p_revision_num);
1584: fetch l_header_csr into l_id;
1585: close l_header_csr;
1656: lc_line_num varchar2(2000);
1657: lc_ship_num varchar2(2000);
1658: lc_buyer_pt_num mtl_system_items_kfv.concatenated_segments%TYPE;
1659: lc_old_sup_pt_num po_lines_all.vendor_product_num%TYPE;
1660: lc_new_sup_pt_num po_change_requests.new_supplier_part_number%TYPE;
1661: lc_old_prom_date varchar2(2000);
1662: lc_new_prom_date varchar2(2000);
1663: lc_old_qty varchar2(2000);
1664: lc_new_qty varchar2(2000);
1667: lc_action_type varchar2(2000);
1668: lc_item_desc po_lines_all.item_description%TYPE;
1669: lc_uom po_lines_all.unit_meas_lookup_code%TYPE;
1670: lc_ship_to_location hr_locations_all.location_code%TYPE;
1671: lc_action_code po_change_requests.ACTION_TYPE%TYPE;
1672: lc_reason po_change_requests.request_reason%TYPE;
1673: lc_split varchar2(2000);
1674: l_global_agreement_flag po_headers_all.GLOBAL_AGREEMENT_FLAG%TYPE := 'F';
1675: /*Modified as part of bug 7550964 changing date format*/
1668: lc_item_desc po_lines_all.item_description%TYPE;
1669: lc_uom po_lines_all.unit_meas_lookup_code%TYPE;
1670: lc_ship_to_location hr_locations_all.location_code%TYPE;
1671: lc_action_code po_change_requests.ACTION_TYPE%TYPE;
1672: lc_reason po_change_requests.request_reason%TYPE;
1673: lc_split varchar2(2000);
1674: l_global_agreement_flag po_headers_all.GLOBAL_AGREEMENT_FLAG%TYPE := 'F';
1675: /*Modified as part of bug 7550964 changing date format*/
1676: cursor l_po_chg_req_csr(grp_id number, l_requestor_id number) is
1718: pcr.ACTION_TYPE ACTION_CODE,
1719: pcr.request_reason REASON,
1720: null SPLIT
1721: from
1722: po_change_requests pcr,
1723: po_lines_archive_all pla,
1724: mtl_system_items_kfv msi,
1725: FINANCIALS_SYSTEM_PARAMS_ALL FSP
1726: where pla.po_line_id = pcr.document_line_id
1758: pcr.ACTION_TYPE ACTION_CODE,
1759: pcr.REQUEST_REASON REASON,
1760: null SPLIT
1761: from
1762: po_change_requests pcr,
1763: po_lines_archive_all pla,
1764: po_line_locations_archive_all plla,
1765: hr_locations_all hla, hz_locations hz,
1766: mtl_system_items_kfv msi,
1804: pcr.ACTION_TYPE ACTION_CODE,
1805: pcr.REQUEST_REASON REASON,
1806: fnd_message.get_string('PO','PO_WF_NOTIF_YES') SPLIT
1807: from
1808: po_change_requests pcr,
1809: po_lines_archive_all pla,
1810: po_line_locations_archive_all plla,
1811: hr_locations_all hla, hz_locations hz,
1812: mtl_system_items_kfv msi,
1830: )
1831: order by LINE_NUM,nvl(SHIPMENT_NUM,0);
1832:
1833: l_id number;
1834: l_header_response po_change_requests.request_status%type;
1835: l_header_cancel varchar2(1);
1836: cursor l_header_cancel_csr(grp_id number) is
1837: select change_request_id,
1838: request_status
1835: l_header_cancel varchar2(1);
1836: cursor l_header_cancel_csr(grp_id number) is
1837: select change_request_id,
1838: request_status
1839: from po_change_requests
1840: where change_request_group_id = grp_id
1841: and action_type = 'CANCELLATION'
1842: and request_level = 'HEADER';
1843:
1840: where change_request_group_id = grp_id
1841: and action_type = 'CANCELLATION'
1842: and request_level = 'HEADER';
1843:
1844: l_additional_changes po_change_requests.additional_changes%type;
1845:
1846: cursor l_header_changes_csr(grp_id number)
1847: is
1848: select additional_changes
1845:
1846: cursor l_header_changes_csr(grp_id number)
1847: is
1848: select additional_changes
1849: from po_change_requests
1850: where change_request_group_id = grp_id
1851: and request_level = 'HEADER'
1852: and additional_changes is not null;
1853:
2486: lc_line_num varchar2(2000);
2487: lc_ship_num varchar2(2000);
2488: lc_buyer_pt_num mtl_system_items_kfv.concatenated_segments%TYPE;
2489: lc_old_sup_pt_num po_lines_all.vendor_product_num%TYPE;
2490: lc_new_sup_pt_num po_change_requests.new_supplier_part_number%TYPE;
2491: lc_old_prom_date varchar2(2000);
2492: lc_new_prom_date varchar2(2000);
2493: lc_old_qty varchar2(2000);
2494: lc_new_qty varchar2(2000);
2497: lc_action_type varchar2(2000);
2498: lc_item_desc po_lines_all.item_description%TYPE;
2499: lc_uom po_lines_all.unit_meas_lookup_code%TYPE;
2500: lc_ship_to_location hr_locations_all.location_code%TYPE;
2501: lc_action_code po_change_requests.ACTION_TYPE%TYPE;
2502: lc_reason po_change_requests.request_reason%TYPE;
2503: lc_split varchar2(2000);
2504: l_global_agreement_flag po_headers_all.GLOBAL_AGREEMENT_FLAG%TYPE := 'F';
2505: /*Modified as part of bug 7550964 changing date format*/
2498: lc_item_desc po_lines_all.item_description%TYPE;
2499: lc_uom po_lines_all.unit_meas_lookup_code%TYPE;
2500: lc_ship_to_location hr_locations_all.location_code%TYPE;
2501: lc_action_code po_change_requests.ACTION_TYPE%TYPE;
2502: lc_reason po_change_requests.request_reason%TYPE;
2503: lc_split varchar2(2000);
2504: l_global_agreement_flag po_headers_all.GLOBAL_AGREEMENT_FLAG%TYPE := 'F';
2505: /*Modified as part of bug 7550964 changing date format*/
2506: cursor l_po_chg_req_csr(grp_id number, l_planner_id number) is
2551: pcr.ACTION_TYPE ACTION_CODE,
2552: pcr.request_reason REASON,
2553: null SPLIT
2554: from
2555: po_change_requests pcr,
2556: po_lines_archive_all pla,
2557: mtl_system_items_kfv msi,
2558: mtl_planners mtp,
2559: financials_system_params_all fsp
2593: pcr.ACTION_TYPE ACTION_CODE,
2594: pcr.REQUEST_REASON REASON,
2595: null SPLIT
2596: from
2597: po_change_requests pcr,
2598: po_lines_archive_all pla,
2599: po_line_locations_archive_all plla,
2600: hr_locations_all hla, hz_locations hz,
2601: mtl_system_items_kfv msi,
2642: pcr.ACTION_TYPE ACTION_CODE,
2643: pcr.REQUEST_REASON REASON,
2644: fnd_message.get_string('PO','PO_WF_NOTIF_YES') SPLIT
2645: from
2646: po_change_requests pcr,
2647: po_lines_archive_all pla,
2648: po_line_locations_archive_all plla,
2649: hr_locations_all hla, hz_locations hz,
2650: mtl_system_items_kfv msi,
2669: )
2670: order by LINE_NUM,nvl(SHIPMENT_NUM,0);
2671:
2672: l_id number;
2673: l_header_response po_change_requests.request_status%type;
2674: l_header_cancel varchar2(1);
2675: cursor l_header_cancel_csr(grp_id number) is
2676: select change_request_id,
2677: request_status
2674: l_header_cancel varchar2(1);
2675: cursor l_header_cancel_csr(grp_id number) is
2676: select change_request_id,
2677: request_status
2678: from po_change_requests
2679: where change_request_group_id = grp_id
2680: and action_type = 'CANCELLATION'
2681: and request_level = 'HEADER';
2682:
2679: where change_request_group_id = grp_id
2680: and action_type = 'CANCELLATION'
2681: and request_level = 'HEADER';
2682:
2683: l_additional_changes po_change_requests.additional_changes%type;
2684:
2685: cursor l_header_changes_csr(grp_id number)
2686: is
2687: select additional_changes
2684:
2685: cursor l_header_changes_csr(grp_id number)
2686: is
2687: select additional_changes
2688: from po_change_requests
2689: where change_request_group_id = grp_id
2690: and request_level = 'HEADER'
2691: and additional_changes is not null;
2692:
3314: /* lc_line_num varchar2(2000);
3315: lc_ship_num varchar2(2000);
3316: lc_buyer_pt_num mtl_system_items_kfv.concatenated_segments%TYPE;
3317: lc_old_sup_pt_num po_lines_all.vendor_product_num%TYPE;
3318: lc_new_sup_pt_num po_change_requests.new_supplier_part_number%TYPE;
3319: lc_old_prom_date date;
3320: lc_new_prom_date date;
3321: lc_old_qty varchar2(2000);
3322: lc_new_qty varchar2(2000);
3325: lc_action_type varchar2(2000);
3326: lc_item_desc po_lines_all.item_description%TYPE;
3327: lc_uom po_lines_all.unit_meas_lookup_code%TYPE;
3328: lc_ship_to_location hr_locations_all.location_code%TYPE;
3329: lc_action_code po_change_requests.ACTION_TYPE%TYPE;
3330: lc_reason po_change_requests.request_reason%TYPE;
3331: lc_split varchar2(2000);
3332: l_global_agreement_flag po_headers_all.GLOBAL_AGREEMENT_FLAG%TYPE;
3333: l_po_rev number;
3326: lc_item_desc po_lines_all.item_description%TYPE;
3327: lc_uom po_lines_all.unit_meas_lookup_code%TYPE;
3328: lc_ship_to_location hr_locations_all.location_code%TYPE;
3329: lc_action_code po_change_requests.ACTION_TYPE%TYPE;
3330: lc_reason po_change_requests.request_reason%TYPE;
3331: lc_split varchar2(2000);
3332: l_global_agreement_flag po_headers_all.GLOBAL_AGREEMENT_FLAG%TYPE;
3333: l_po_rev number;
3334: l_rel_rev number;
3376: pcr.ACTION_TYPE ACTION_CODE,
3377: pcr.request_reason REASON,
3378: null SPLIT
3379: from
3380: po_change_requests pcr,
3381: po_lines_archive_all pla,
3382: mtl_system_items_kfv msi,
3383: FINANCIALS_SYSTEM_PARAMS_ALL FSP
3384: where pla.po_line_id = pcr.document_line_id
3413: pcr.ACTION_TYPE ACTION_CODE,
3414: pcr.REQUEST_REASON REASON,
3415: null SPLIT
3416: from
3417: po_change_requests pcr,
3418: po_lines_archive_all pla,
3419: po_line_locations_archive_all plla,
3420: hr_locations_all hla, hz_locations hz,
3421: mtl_system_items_kfv msi,
3456: pcr.ACTION_TYPE ACTION_CODE,
3457: pcr.REQUEST_REASON REASON,
3458: fnd_message.get_string('PO','PO_WF_NOTIF_YES') SPLIT
3459: from
3460: po_change_requests pcr,
3461: po_lines_archive_all pla,
3462: po_line_locations_archive_all plla,
3463: hr_locations_all hla, hz_locations hz,
3464: mtl_system_items_kfv msi,
3587: )
3588: order by LINE_NUM,SHIPMENT_NUM;
3589:
3590: l_id number;
3591: l_header_response po_change_requests.request_status%type;
3592: l_header_cancel varchar2(1);
3593:
3594: cursor l_header_cancel_csr(grp_id number) is
3595: select change_request_id,
3593:
3594: cursor l_header_cancel_csr(grp_id number) is
3595: select change_request_id,
3596: request_status
3597: from po_change_requests
3598: where change_request_group_id = grp_id
3599: and action_type = 'CANCELLATION'
3600: and request_level = 'HEADER';
3601:
3598: where change_request_group_id = grp_id
3599: and action_type = 'CANCELLATION'
3600: and request_level = 'HEADER';
3601:
3602: l_additional_changes po_change_requests.additional_changes%type;
3603:
3604: cursor l_header_changes_csr(grp_id number)
3605: is
3606: select additional_changes
3603:
3604: cursor l_header_changes_csr(grp_id number)
3605: is
3606: select additional_changes
3607: from po_change_requests
3608: where change_request_group_id = grp_id
3609: and request_level = 'HEADER'
3610: and additional_changes is not null;
3611: */
4386: l_old_prom_date varchar2(2000);
4387: l_new_prom_date varchar2(2000);
4388:
4389: l_doc_hdr_info varchar2(2000);
4390: lc_action_code po_change_requests.action_type%type;
4391: lc_line_num varchar2(2000);
4392: lc_ship_num varchar2(2000);
4393: lc_old_sup_pt_num po_lines_all.vendor_product_num%TYPE;
4394: lc_new_sup_pt_num po_change_requests.new_supplier_part_number%TYPE;
4390: lc_action_code po_change_requests.action_type%type;
4391: lc_line_num varchar2(2000);
4392: lc_ship_num varchar2(2000);
4393: lc_old_sup_pt_num po_lines_all.vendor_product_num%TYPE;
4394: lc_new_sup_pt_num po_change_requests.new_supplier_part_number%TYPE;
4395: lc_old_prom_date date;
4396: lc_new_prom_date date;
4397: lc_old_qty varchar2(2000);
4398: lc_new_qty varchar2(2000);
4401: lc_response varchar2(2000);
4402: lc_item_desc po_lines_all.item_description%TYPE;
4403: lc_uom po_lines_all.unit_meas_lookup_code%TYPE;
4404: lc_ship_to_location hr_locations_all.location_code%TYPE;
4405: lc_reason po_change_requests.response_reason%TYPE;
4406: lc_split varchar2(2000);
4407:
4408:
4409: cursor l_po_chg_req_csr(grp_id number) is
4445: null SHIP_TO_LOCATION,
4446: pcr.response_reason REASON,
4447: null SPLIT,
4448: pcr.action_type ACTION_CODE
4449: from po_change_requests pcr, po_lines_archive_all pla
4450: where pla.po_line_id = pcr.document_line_id
4451: and pcr.change_request_group_id =grp_id
4452: --and pcr.document_revision_num = pla.revision_num
4453: and pcr.request_level = 'LINE'
4473: pcr.response_reason REASON,
4474: decode(pcr.parent_line_location_id, null, null, fnd_message.get_string('PO','PO_WF_NOTIF_YES')) SPLIT,
4475: pcr.action_type ACTION_CODE
4476:
4477: from po_change_requests pcr, po_lines_archive_all pla, po_line_locations_archive_all plla,
4478: hr_locations_all hla, hz_locations hz
4479: where change_request_group_id =grp_id
4480: and pla.po_line_id = pcr.document_line_id
4481: and pla.latest_external_flag = 'Y'
4508: nvl(hla.location_code, substr(rtrim(hz.address1)||'-'||rtrim(hz.city),1,60)) SHIP_TO_LOCATION,
4509: pcr.response_reason REASON,
4510: decode(pcr.parent_line_location_id, null, null, fnd_message.get_string('PO','PO_WF_NOTIF_YES')) SPLIT,
4511: pcr.action_type ACTION_CODE
4512: from po_change_requests pcr, po_lines_archive_all pla, po_line_locations_archive_all plla,
4513: hr_locations_all hla, hz_locations hz
4514: where change_request_group_id =grp_id
4515: and pla.po_line_id = pcr.document_line_id
4516: and pla.latest_external_flag = 'Y'
4525: )
4526: order by LINE_NUM,nvl(SHIPMENT_NUM,0);
4527:
4528: l_id number;
4529: l_header_response po_change_requests.request_status%type;
4530: l_header_cancel varchar2(1);
4531:
4532: cursor l_header_cancel_csr(grp_id number) is
4533: select change_request_id,
4531:
4532: cursor l_header_cancel_csr(grp_id number) is
4533: select change_request_id,
4534: request_status
4535: from po_change_requests
4536: where change_request_group_id = grp_id
4537: and action_type = 'CANCELLATION'
4538: and request_level = 'HEADER';
4539:
4536: where change_request_group_id = grp_id
4537: and action_type = 'CANCELLATION'
4538: and request_level = 'HEADER';
4539:
4540: l_additional_changes po_change_requests.additional_changes%type;
4541:
4542: cursor l_header_changes_csr(grp_id number)
4543: is
4544: select additional_changes, request_status
4541:
4542: cursor l_header_changes_csr(grp_id number)
4543: is
4544: select additional_changes, request_status
4545: from po_change_requests
4546: where change_request_group_id = grp_id
4547: and request_level = 'HEADER'
4548: and additional_changes is not null; */
4549:
4555: into
4556: l_document_header_id,
4557: l_document_type,
4558: l_po_release_id
4559: from po_change_requests
4560: where change_request_group_id = p_chg_req_grp_id;
4561:
4562: l_progress := '001';
4563: l_base_url := POS_URL_PKG.get_external_url;
5132: l_rel_currency_code VARCHAR2(5);
5133:
5134: CURSOR l_header_cancel_csr(grp_id number) IS
5135: select change_request_id
5136: from po_change_requests
5137: where change_request_group_id = grp_id
5138: and action_type = 'CANCELLATION'
5139: and request_level = 'HEADER';
5140:
5138: and action_type = 'CANCELLATION'
5139: and request_level = 'HEADER';
5140:
5141: l_itemtype VARCHAR2(30);
5142: l_itemkey po_change_requests.wf_item_key%TYPE;
5143: l_header_id NUMBER;
5144: l_release_id NUMBER;
5145: l_chg_req_grp_id NUMBER;
5146: l_ack_req_flag VARCHAR2(30);
5352: l_id number;
5353: l_header_cancel varchar2(1);
5354: CURSOR l_header_cancel_csr(grp_id number) IS
5355: select change_request_id
5356: from po_change_requests
5357: where change_request_group_id = grp_id
5358: and action_type = 'CANCELLATION'
5359: and request_level = 'HEADER';
5360:
5358: and action_type = 'CANCELLATION'
5359: and request_level = 'HEADER';
5360:
5361: l_itemtype VARCHAR2(30);
5362: l_itemkey po_change_requests.wf_item_key%TYPE;
5363: l_header_id NUMBER;
5364: l_release_id NUMBER;
5365: l_chg_req_grp_id NUMBER;
5366: l_revision_num NUMBER;
5537: l_id number;
5538: l_header_cancel varchar2(1);
5539: CURSOR l_header_cancel_csr(grp_id number) IS
5540: select change_request_id
5541: from po_change_requests
5542: where change_request_group_id = grp_id
5543: and action_type = 'CANCELLATION'
5544: and request_level = 'HEADER';
5545:
5543: and action_type = 'CANCELLATION'
5544: and request_level = 'HEADER';
5545:
5546: l_itemtype VARCHAR2(30);
5547: l_itemkey po_change_requests.wf_item_key%TYPE;
5548: l_header_id NUMBER;
5549: l_release_id NUMBER;
5550: l_chg_req_grp_id NUMBER;
5551:
5694: l_id number;
5695: l_header_cancel varchar2(1);
5696: CURSOR l_header_cancel_csr(grp_id number) IS
5697: select change_request_id
5698: from po_change_requests
5699: where change_request_group_id = grp_id
5700: and action_type = 'CANCELLATION'
5701: and request_level = 'HEADER';
5702:
5700: and action_type = 'CANCELLATION'
5701: and request_level = 'HEADER';
5702:
5703: l_itemtype VARCHAR2(30);
5704: l_itemkey po_change_requests.wf_item_key%TYPE;
5705: l_header_id NUMBER;
5706: l_release_id NUMBER;
5707: l_chg_req_grp_id NUMBER;
5708:
5840: l_temp number;
5841: x_progress varchar2(3) := '000';
5842: cursor l_x_csr(id number) is
5843: select pcr.document_line_locatiON_id
5844: from po_change_requests pcr,
5845: po_distributions_all pda
5846: where pcr.document_line_location_id = pda.line_location_id
5847: and pcr.change_request_group_id = id -- added checks FPS Shipment Amount, Shipment price Prorate
5848: and (pcr.new_quantity is not null or pcr.new_amount is not null or pcr.new_price is not null)
5860: itemkey => itemkey,
5861: aname => 'PO_HEADER_ID');
5862:
5863: select count(*) into l_retro_count
5864: from po_change_requests pcr, po_headers_all poh
5865: where poh.po_header_id = pcr.document_header_id
5866: and poh.po_header_id = l_po_header_id
5867: and poh.type_lookup_code = 'BLANKET'
5868: and pcr.change_request_group_id = l_chg_req_grp_id
5877: close l_x_csr;
5878:
5879: select count(*)
5880: into l_header_change
5881: from po_change_requests
5882: where change_request_group_id = l_chg_req_grp_id
5883: and request_level = 'HEADER'
5884: and additional_changes is not null;
5885:
5954: l_authorization_status := 'APPROVED';
5955:
5956: select count(*)
5957: into l_add_changes_accepted
5958: from po_change_requests
5959: where change_request_group_id = l_chg_req_grp_id
5960: -- and request_status = 'ACCEPTED'
5961: and request_status = 'BUYER_APP' /* fix for bug 3691061 */
5962: and request_level = 'HEADER'
5968:
5969: if (l_authorization_status = 'REQUIRES REAPPROVAL') then
5970:
5971: select max(responded_by) into l_responded_by
5972: from po_change_requests
5973: where request_level = 'HEADER'
5974: -- and request_status = 'ACCEPTED'
5975: and request_status = 'BUYER_APP' /* fix for bug 3691061 */
5976: and additional_changes is not null
6008:
6009: else -- authorization status is approved
6010:
6011: -- added due to bug 3574114
6012: update po_change_requests
6013: set change_active_flag = 'N'
6014: where change_request_group_id = l_chg_req_grp_id;
6015:
6016: if(l_release_id is not null) then
6229: l_header_cancel varchar2(1);
6230:
6231: cursor l_header_cancel_csr(grp_id number) is
6232: select change_request_id
6233: from po_change_requests
6234: where change_request_group_id = grp_id
6235: and action_type = 'CANCELLATION'
6236: and request_level = 'HEADER';
6237:
6250: */
6251: cursor l_get_user_list_csr(x_document_id NUMBER,x_document_type varchar2) is
6252: select distinct created_by ,
6253: change_request_group_id
6254: from po_change_requests
6255: where request_status in ('BUYER_APP', 'WAIT_CANCEL_APP')
6256: and ((document_header_id = x_document_id) OR (po_release_id = x_document_id))
6257: and initiator = 'SUPPLIER'
6258: and document_type = x_document_type;
6259: --Bug 6722239 - End
6260:
6261: CURSOR getLineLocID(l_chg_req_grp_id_csr IN NUMBER) IS
6262: SELECT document_line_location_id
6263: FROM PO_CHANGE_REQUESTS
6264: WHERE change_request_group_id = l_chg_req_grp_id_csr
6265: AND request_level = 'SHIPMENT'
6266: AND request_status = 'BUYER_APP'
6267: AND (NEW_AMOUNT is not null OR NEW_QUANTITY is not null);
6268:
6269: /* Curosr to get the change_responded by*/
6270: CURSOR getChangeRespBy(l_chg_req_grp_id_csr IN NUMBER) IS
6271: SELECT distinct fndu.employee_id
6272: FROM po_change_requests pcr,
6273: fnd_user fndu
6274: WHERE pcr.change_request_group_id = l_chg_req_grp_id_csr
6275: AND fndu.user_id = pcr.responded_by;
6276:
6290: select distinct created_by,
6291: change_request_group_id
6292: into l_supplier_user_id ,
6293: l_change_request_group_id
6294: from po_change_requests
6295: where request_status in ('BUYER_APP', 'WAIT_CANCEL_APP')
6296: and po_release_id = l_document_id
6297: and initiator = 'SUPPLIER'
6298: and document_type = 'RELEASE';
6392: select distinct created_by ,
6393: change_request_group_id
6394: into l_supplier_user_id ,
6395: l_change_request_group_id
6396: from po_change_requests
6397: where request_status in ('BUYER_APP', 'WAIT_CANCEL_APP')
6398: and document_header_id = l_document_id
6399: and initiator = 'SUPPLIER'
6400: and document_type = 'PO';
6539: END LOOP;
6540: CLOSE getLineLocID;
6541: end if;
6542:
6543: update po_change_requests
6544: set request_status = 'ACCEPTED'
6545: where change_request_group_id = l_change_request_group_id
6546: and request_status in ('BUYER_APP', 'WAIT_CANCEL_APP');
6547:
6546: and request_status in ('BUYER_APP', 'WAIT_CANCEL_APP');
6547:
6548: /* added code for bug 3574114 - update change active flag due to final buyer response */
6549:
6550: update po_change_requests
6551: set change_active_flag = 'N'
6552: where change_request_group_id = l_change_request_group_id;
6553:
6554: else
6553:
6554: else
6555: resultout := 'N';
6556:
6557: update po_change_requests
6558: set request_status = 'REJECTED',
6559: response_reason = null
6560: where change_request_group_id = l_change_request_group_id
6561: and request_status in ('BUYER_APP', 'WAIT_CANCEL_APP');
6561: and request_status in ('BUYER_APP', 'WAIT_CANCEL_APP');
6562:
6563: /* added code for bug 3574114 - update change active flag due to final buyer response */
6564:
6565: update po_change_requests
6566: set change_active_flag = 'N'
6567: where change_request_group_id = l_change_request_group_id;
6568:
6569: END if;
6634: l_expiration_date);
6635: x_resultout:=l_role_name;
6636: -- Bug 6722239 - End
6637:
6638: /* Bug 4949617 get the reponded_by id from po_change_requests table */
6639: OPEN getChangeRespBy(l_change_request_group_id);
6640: LOOP
6641: FETCH getChangeRespBy INTO l_responded_by_id;
6642: EXIT WHEN getChangeRespBy%NOTFOUND;
6777:
6778: x_progress varchar2(3) := '000';
6779: cursor l_change_csr(grp_id number) is
6780: select change_request_id from
6781: po_change_requests
6782: where action_type = 'MODIFICATION'
6783: and change_request_group_id = grp_id;
6784:
6785: BEGIN
6881:
6882: if(l_document_type = 'RELEASE') then
6883: select count(1)
6884: into l_count
6885: from po_change_requests
6886: where initiator = 'SUPPLIER'
6887: and document_type = 'RELEASE'
6888: and po_release_id = l_document_id
6889: and request_status = 'BUYER_APP';
6889: and request_status = 'BUYER_APP';
6890:
6891: select count(1)
6892: into l_cancel_app_count
6893: from po_change_requests
6894: where initiator = 'SUPPLIER'
6895: and document_type = 'RELEASE'
6896: and po_release_id = l_document_id
6897: and request_status = 'WAIT_CANCEL_APP';
6899: if ((l_count > 0) OR (l_cancel_app_count > 0)) then
6900: -- no chance for no_data_found exception.
6901: select distinct(change_request_group_id)
6902: into l_change_request_group_id
6903: from po_change_requests
6904: where initiator = 'SUPPLIER'
6905: and document_type = 'RELEASE'
6906: and po_release_id = l_document_id
6907: and request_status in ('BUYER_APP', 'WAIT_CANCEL_APP');
6911: -- so as to prevent it from blocking processing of other records
6912:
6913: if (l_count > 0 AND l_cancel_app_count > 0) then
6914:
6915: update po_change_requests set request_status = 'ACCEPTED'
6916: where initiator = 'SUPPLIER'
6917: and document_type = 'RELEASE'
6918: and po_release_id = l_document_id
6919: and request_status = 'WAIT_CANCEL_APP';
6922:
6923: else
6924: select count(1)
6925: into l_count
6926: from po_change_requests
6927: where initiator = 'SUPPLIER'
6928: and document_type = 'PO'
6929: and document_header_id = l_document_id
6930: and request_status = 'BUYER_APP';
6930: and request_status = 'BUYER_APP';
6931:
6932: select count(1)
6933: into l_cancel_app_count
6934: from po_change_requests
6935: where initiator = 'SUPPLIER'
6936: and document_type = 'PO'
6937: and document_header_id = l_document_id
6938: and request_status = 'WAIT_CANCEL_APP';
6940: if ((l_count > 0) OR (l_cancel_app_count > 0)) then
6941: -- no chance for no_data_found exception.
6942: select distinct(change_request_group_id)
6943: into l_change_request_group_id
6944: from po_change_requests
6945: where initiator = 'SUPPLIER'
6946: and document_type = 'PO'
6947: and document_header_id = l_document_id
6948: and request_status in ('BUYER_APP', 'WAIT_CANCEL_APP');
6952: -- so as to prevent it from blocking processing of other records
6953:
6954: if (l_count > 0 AND l_cancel_app_count > 0) then
6955:
6956: update po_change_requests set request_status = 'ACCEPTED'
6957: where initiator = 'SUPPLIER'
6958: and document_type = 'PO'
6959: and document_header_id = l_document_id
6960: and request_status = 'WAIT_CANCEL_APP';
7025: --l_id number;
7026: --l_header_cancel varchar2(1);
7027: --cursor l_header_cancel_csr(grp_id number) is
7028: --select change_request_id
7029: --from po_change_requests
7030: --where change_request_group_id = grp_id
7031: --and action_type = 'CANCELLATION'
7032: --and request_level = 'HEADER';
7033: --l_doc_hdr_info varchar2(2000);
7035:
7036: /* Curosr to get the change_responded by*/
7037: CURSOR getChangeRespBy(l_chg_req_grp_id_csr IN NUMBER) IS
7038: SELECT distinct fndu.employee_id
7039: FROM po_change_requests pcr,
7040: fnd_user fndu
7041: WHERE pcr.change_request_group_id = l_chg_req_grp_id_csr
7042: AND fndu.user_id = pcr.responded_by;
7043:
7076:
7077: if(l_supplier_username is null) then
7078: select max(created_by)
7079: into l_supplier_user_id
7080: from po_change_requests
7081: where change_request_group_id = p_chg_req_grp_id;
7082:
7083: select user_name
7084: into l_supplier_username
7085: from fnd_user
7086: where user_id = l_supplier_user_id;
7087: end if;
7088:
7089: /* Bug 4949617 get the reponded_by id from po_change_requests table */
7090: OPEN getChangeRespBy(p_chg_req_grp_id);
7091: LOOP
7092: FETCH getChangeRespBy INTO l_responded_by_id;
7093: EXIT WHEN getChangeRespBy%NOTFOUND;
7214: x_return_status := 'S';
7215: if(p_release_id is null) then
7216: select change_request_group_id
7217: into l_chn_req_grp_id
7218: from po_change_requests
7219: where document_header_id = p_header_id
7220: and request_status = 'PENDING'
7221: and request_level = 'HEADER'
7222: and initiator = 'SUPPLIER'
7224:
7225: else
7226: select change_request_group_id
7227: into l_chn_req_grp_id
7228: from po_change_requests
7229: where po_release_id = p_release_id
7230: and request_status = 'PENDING'
7231: and request_level = 'HEADER'
7232: and initiator = 'SUPPLIER'
7233: and action_type ='CANCELLATION';
7234: end if;
7235: l_progress := '001';
7236: if(p_acc_rej = 'A') then
7237: update po_change_requests
7238: set request_status = 'BUYER_APP',
7239: responded_by = p_user_id,
7240: response_date = sysdate,
7241: response_reason = p_reason
7240: response_date = sysdate,
7241: response_reason = p_reason
7242: where change_request_group_id = l_chn_req_grp_id;
7243: else
7244: update po_change_requests
7245: set request_status = 'REJECTED',
7246: change_active_flag = 'N',
7247: responded_by = p_user_id,
7248: response_date = sysdate,
7297: l_api_name varchar2(50):= 'ProcessResponse';
7298: l_count number;
7299: l_number_of_buyer_app number;
7300: l_acc_req_flag varchar2(1);
7301: l_item_key po_change_requests.wf_item_key%TYPE;
7302: l_return_status varchar2(1);
7303: l_po_cancel_api exception;
7304: l_org_id number;
7305: l_agent_id number;
7324: l_closed_code po_headers_all.closed_code%type;
7325:
7326: cursor l_accept_csr(grp_id number) is
7327: select change_request_id
7328: from po_change_requests
7329: where change_request_group_id = grp_id
7330: and request_status in ('ACCEPTED','BUYER_APP');
7331:
7332: cursor l_reject_csr(grp_id number) is
7330: and request_status in ('ACCEPTED','BUYER_APP');
7331:
7332: cursor l_reject_csr(grp_id number) is
7333: select change_request_id
7334: from po_change_requests
7335: where change_request_group_id = grp_id
7336: and request_status = 'REJECTED';
7337:
7338: cursor l_pending_csr(grp_id number) is
7336: and request_status = 'REJECTED';
7337:
7338: cursor l_pending_csr(grp_id number) is
7339: select change_request_id
7340: from po_change_requests
7341: where change_request_group_id = grp_id
7342: and request_status = 'PENDING';
7343:
7344:
7456: if(p_flag is null) then
7457: begin
7458: select distinct wf_item_key
7459: into l_item_key
7460: from po_change_requests
7461: where change_request_group_id = p_chg_req_grp_id;
7462: wf_engine.completeActivity('POSCHORD',l_item_key,'NOTIFY_BUYER_OF_CHN', 'Abort');
7463: exception when others then
7464: null;
7470:
7471:
7472: x_progress:='004';
7473:
7474: -- 2. Update po_change_requests table.. for those supplier change request to cancel, if accepted
7475: -- change them from BUYER_APP to ACCEPTED
7476: update po_change_requests
7477: set request_status = 'ACCEPTED'
7478: -- change_active_flag = 'N' /* commented out due to bug 3574114 */
7472: x_progress:='004';
7473:
7474: -- 2. Update po_change_requests table.. for those supplier change request to cancel, if accepted
7475: -- change them from BUYER_APP to ACCEPTED
7476: update po_change_requests
7477: set request_status = 'ACCEPTED'
7478: -- change_active_flag = 'N' /* commented out due to bug 3574114 */
7479: where change_request_group_id = p_chg_req_grp_id
7480: and request_status in ('BUYER_APP', 'WAIT_CANCEL_APP')
7509: x_progress:='005';
7510:
7511: select count(1)
7512: into l_number_of_buyer_app
7513: from po_change_requests
7514: where change_request_group_id = p_chg_req_grp_id
7515: and request_status = 'BUYER_APP';
7516:
7517: if(l_number_of_buyer_app = 0) then
7544: x_return_code := l_mc_return_code;
7545:
7546:
7547: /* COMMENTED OUT - WAIT_MGR_APP status is no longer used and is replaced by BUYER_APP
7548: update po_change_requests
7549: set request_status = 'WAIT_MGR_APP'
7550: where change_request_group_id = p_chg_req_grp_id
7551: and request_status = 'BUYER_APP';
7552: */
7599: cursor l_requestors_csr(grp_id number)
7600: is
7601: select pda.deliver_to_person_id
7602: from
7603: po_change_requests pcr,
7604: po_distributions_all pda,
7605: po_line_locations_all pll
7606: where pcr.change_request_group_id = grp_id
7607: and pcr.request_level = 'LINE'
7610: and pll.shipment_type = 'STANDARD'
7611: union
7612: select pda.deliver_to_person_id
7613: from
7614: po_change_requests pcr,
7615: po_distributions_all pda
7616: where pcr.change_request_group_id = grp_id
7617: and pcr.request_level = 'SHIPMENT'
7618: and pcr.document_line_location_id = pda.line_location_id;
7623: distinct mtp.employee_id
7624: from
7625: mtl_system_items msi,
7626: mtl_planners mtp,
7627: po_change_requests pcr,
7628: po_lines_all pla,
7629: financials_system_params_all fsp
7630: where pcr.change_request_group_id = grp_id
7631: and pcr.document_line_id = pla.po_line_id
7767: ELSE
7768: l_responded_by :=fnd_global.user_id;
7769: END IF;
7770:
7771: update po_change_requests
7772: set request_status ='BUYER_APP',
7773: responded_by = l_responded_by,
7774: response_date = sysdate
7775: where change_request_group_id = l_chg_req_grp_id
7837: END IF;
7838:
7839:
7840:
7841: update po_change_requests
7842: set request_status ='REJECTED',
7843: change_active_flag = 'N',
7844: responded_by = fnd_global.user_id,
7845: response_date = sysdate
7866: l_progress varchar2(100) := '000';
7867: l_count number;
7868: l_seq number;
7869: l_po_style varchar2(10);
7870: l_item_key po_change_requests.wf_item_key%TYPE;
7871: l_doc_type_code varchar2(1);
7872: l_buyer_notif_code varchar2(240);
7873: l_buyer_agent_id number;
7874: l_buyer_username fnd_user.user_name%TYPE;
8022: /*update data to include wf item key and item type. p_chg_req_grp_id could be null in the case where user accepts/rejects
8023: all shipments and no changes was made. Thus, in this case, we will NOT store the wf_item_key or wf_item_type anywhere.
8024: */
8025: if(p_chg_req_grp_id is not null) then
8026: update po_change_requests
8027: set wf_item_type = 'POSCHORD',wf_item_key = l_item_key
8028: where change_request_group_id = p_chg_req_grp_id;
8029: end if;
8030: l_progress := '004';
8317: x_count out nocopy number)
8318: is
8319: CURSOR additional_changes(chg_req_grp_id_csr IN NUMBER) IS
8320: select additional_changes
8321: from po_change_requests
8322: where change_request_group_id = chg_req_grp_id_csr
8323: and request_level = 'HEADER'
8324: and additional_changes is not null;
8325:
8324: and additional_changes is not null;
8325:
8326: -- returns 1 when only additional changes at header level are requested otherwise 0
8327: CURSOR getRequestLevel(chg_req_grp_id_csr IN NUMBER) IS
8328: SELECT COUNT(1) FROM po_change_requests WHERE NOT EXISTS ( SELECT 1
8329: FROM po_change_requests
8330: WHERE change_request_group_id = chg_req_grp_id_csr
8331: AND request_level IN ('LINE','SHIPMENT'))
8332: AND change_request_group_id = chg_req_grp_id_csr
8325:
8326: -- returns 1 when only additional changes at header level are requested otherwise 0
8327: CURSOR getRequestLevel(chg_req_grp_id_csr IN NUMBER) IS
8328: SELECT COUNT(1) FROM po_change_requests WHERE NOT EXISTS ( SELECT 1
8329: FROM po_change_requests
8330: WHERE change_request_group_id = chg_req_grp_id_csr
8331: AND request_level IN ('LINE','SHIPMENT'))
8332: AND change_request_group_id = chg_req_grp_id_csr
8333: AND action_type = 'MODIFICATION';
8333: AND action_type = 'MODIFICATION';
8334:
8335: -- Bug 7487461
8336: -- Modified the data type for additional changes to capture the entire text.
8337: l_additional_changes po_change_requests.additional_changes%type;
8338: l_count NUMBER(10);
8339: begin
8340: OPEN getRequestLevel(p_chg_req_grp_id);
8341: LOOP
8443: BEGIN
8444: select decode(count(distinct(action_type)),2,'BOTH',1,'CHG') into
8445: l_req_mode
8446: from
8447: po_change_requests
8448: where
8449: change_request_group_id = p_chg_req_grp_id
8450: and request_level in('LINE','SHIPMENT')
8451: and action_type in('MODIFICATION','CANCELLATION');
8455: elsif l_req_mode = 'CHG' then
8456: select distinct(action_type) into
8457: l_temp
8458: from
8459: po_change_requests
8460: where
8461: change_request_group_id = p_chg_req_grp_id
8462: and request_level in('LINE','SHIPMENT')
8463: and action_type in('MODIFICATION','CANCELLATION');
8525: l_revision_num varchar2(15) := null;
8526:
8527: CURSOR HdrCancelCsrCount(p_chg_req_grp_id_csr IN NUMBER) IS
8528: SELECT count(*)
8529: FROM po_change_requests pcr
8530: WHERE pcr.CHANGE_REQUEST_GROUP_ID = p_chg_req_grp_id_csr
8531: AND pcr.REQUEST_LEVEL = 'HEADER'
8532: AND pcr.ACTION_TYPE = 'CANCELLATION';
8533:
8535: SELECT pcr.action_type,
8536: pcr.request_status,
8537: poh.type_lookup_code,
8538: pcr.document_num
8539: FROM PO_CHANGE_REQUESTS pcr,
8540: PO_HEADERS_ALL poh
8541: WHERE pcr.CHANGE_REQUEST_GROUP_ID = p_chg_req_grp_id_csr
8542: AND pcr.REQUEST_LEVEL = 'HEADER'
8543: AND pcr.ACTION_TYPE = 'CANCELLATION'
8544: AND pcr.document_header_id = poh.po_header_id;
8545:
8546: CURSOR getRevisionNum(p_chg_req_grp_id_csr IN NUMBER) IS
8547: SELECT distinct(pcr.document_revision_num)
8548: FROM po_change_requests pcr
8549: WHERE pcr.CHANGE_REQUEST_GROUP_ID = p_chg_req_grp_id_csr;
8550:
8551: CURSOR getRevisionNumAck(po_header_id_csr IN NUMBER) IS
8552: SELECT revision_num