[Home] [Help]
502:
503: -- start of fix for bug 3864512
504: -- update status of buyer approved cancel shipment requests to a special status WAIT_CANCEL_APP
505:
506: update po_change_requests
507: set request_status = 'WAIT_CANCEL_APP'
508: where request_status = 'BUYER_APP'
509: and action_type = 'CANCELLATION'
510: and request_level = 'SHIPMENT'
619: l_can_hdr_id number;
620: l_can_rel_id number;
621: l_can_line_id number;
622: l_can_line_loc_id number;
623: l_can_reason po_change_requests.request_reason%TYPE;
624: l_cancel_backing_req po_change_requests.cancel_backing_req%TYPE;
625: l_can_doc_type po_document_types_all.document_type_code%TYPE;
626: l_can_doc_subtype po_document_types_all.document_subtype%TYPE;
627: l_type_lookup_code po_headers_all.type_lookup_code%TYPE;
620: l_can_rel_id number;
621: l_can_line_id number;
622: l_can_line_loc_id number;
623: l_can_reason po_change_requests.request_reason%TYPE;
624: l_cancel_backing_req po_change_requests.cancel_backing_req%TYPE;
625: l_can_doc_type po_document_types_all.document_type_code%TYPE;
626: l_can_doc_subtype po_document_types_all.document_subtype%TYPE;
627: l_type_lookup_code po_headers_all.type_lookup_code%TYPE;
628: l_return_status varchar2(1);
638: document_line_location_id,
639: request_reason,
640: document_type,
641: cancel_backing_req
642: from po_change_requests
643: where change_request_group_id = grp_id
644: and action_type = 'CANCELLATION'
645: and request_level = 'SHIPMENT'
646: and request_status = 'BUYER_APP';
651: document_header_id,
652: po_release_id,
653: request_reason
654: ,document_type
655: from po_change_requests
656: where change_request_group_id = grp_id
657: and action_type = 'CANCELLATION'
658: and request_level = 'HEADER'
659: and request_status = 'BUYER_APP';
722:
723: --If PO Cancel API fails, we'll assume buyer has rejected the cancellation request
724: if(l_return_status <> 'S') then
725: x_return_status := FND_API.G_RET_STS_ERROR;
726: update po_change_requests
727: set request_status = 'REJECTED',
728: response_reason = l_ship_can_err_msg,
729: validation_error = l_ship_can_err_msg
730: -- change_active_flag = 'N' /* commented out due to bug 3574114 */
789:
790: --If PO Cancel API fails, we'll assume buyer has rejected the cancellation request
791: if(l_return_status <> 'S') then
792: x_return_status := FND_API.G_RET_STS_ERROR;
793: update po_change_requests
794: set request_status = 'REJECTED' ,
795: response_reason = l_hdr_can_err_msg,
796: validation_error = l_hdr_can_err_msg,
797: change_active_flag = 'N'
1408: l_id number;
1409:
1410: cursor l_header_csr(hdr_id number, rev_num number) is
1411: select change_request_id
1412: from po_change_requests
1413: where document_header_id = hdr_id
1414: and document_revision_num = rev_num
1415: and request_status = 'PENDING'
1416: and initiator = 'SUPPLIER'
1422: and po_line_location_id is not null;
1423:
1424: cursor l_release_csr(rel_id number,rev_num number) is
1425: select change_request_id
1426: from po_change_requests
1427: where po_release_id = rel_id
1428: and document_revision_num = rev_num
1429: and request_status = 'PENDING'
1430: and initiator = 'SUPPLIER'
1436: and po_line_location_id is not null;
1437:
1438: BEGIN
1439: x_result_code := 'Y';
1440: -- If any Pending records exist in PO_CHANGE_REQUESTS table, that means the PO is not really header rejected
1441: if(p_header_id is not null) then
1442: open l_header_csr(p_header_id, p_revision_num);
1443: fetch l_header_csr into l_id;
1444: close l_header_csr;
1515: lc_line_num varchar2(2000);
1516: lc_ship_num varchar2(2000);
1517: lc_buyer_pt_num mtl_system_items_kfv.concatenated_segments%TYPE;
1518: lc_old_sup_pt_num po_lines_all.vendor_product_num%TYPE;
1519: lc_new_sup_pt_num po_change_requests.new_supplier_part_number%TYPE;
1520: lc_old_prom_date varchar2(2000);
1521: lc_new_prom_date varchar2(2000);
1522: lc_old_qty varchar2(2000);
1523: lc_new_qty varchar2(2000);
1526: lc_action_type varchar2(2000);
1527: lc_item_desc po_lines_all.item_description%TYPE;
1528: lc_uom po_lines_all.unit_meas_lookup_code%TYPE;
1529: lc_ship_to_location hr_locations_all.location_code%TYPE;
1530: lc_action_code po_change_requests.ACTION_TYPE%TYPE;
1531: lc_reason po_change_requests.request_reason%TYPE;
1532: lc_split varchar2(2000);
1533: l_global_agreement_flag po_headers_all.GLOBAL_AGREEMENT_FLAG%TYPE := 'F';
1534: cursor l_po_chg_req_csr(grp_id number, l_requestor_id number) is
1527: lc_item_desc po_lines_all.item_description%TYPE;
1528: lc_uom po_lines_all.unit_meas_lookup_code%TYPE;
1529: lc_ship_to_location hr_locations_all.location_code%TYPE;
1530: lc_action_code po_change_requests.ACTION_TYPE%TYPE;
1531: lc_reason po_change_requests.request_reason%TYPE;
1532: lc_split varchar2(2000);
1533: l_global_agreement_flag po_headers_all.GLOBAL_AGREEMENT_FLAG%TYPE := 'F';
1534: cursor l_po_chg_req_csr(grp_id number, l_requestor_id number) is
1535: select
1574: pcr.ACTION_TYPE ACTION_CODE,
1575: pcr.request_reason REASON,
1576: null SPLIT
1577: from
1578: po_change_requests pcr,
1579: po_lines_archive_all pla,
1580: mtl_system_items_kfv msi,
1581: FINANCIALS_SYSTEM_PARAMS_ALL FSP
1582: where pla.po_line_id = pcr.document_line_id
1614: pcr.ACTION_TYPE ACTION_CODE,
1615: pcr.REQUEST_REASON REASON,
1616: null SPLIT
1617: from
1618: po_change_requests pcr,
1619: po_lines_archive_all pla,
1620: po_line_locations_archive_all plla,
1621: hr_locations_all hla, hz_locations hz,
1622: mtl_system_items_kfv msi,
1660: pcr.ACTION_TYPE ACTION_CODE,
1661: pcr.REQUEST_REASON REASON,
1662: fnd_message.get_string('PO','PO_WF_NOTIF_YES') SPLIT
1663: from
1664: po_change_requests pcr,
1665: po_lines_archive_all pla,
1666: po_line_locations_archive_all plla,
1667: hr_locations_all hla, hz_locations hz,
1668: mtl_system_items_kfv msi,
1686: )
1687: order by LINE_NUM,nvl(SHIPMENT_NUM,0);
1688:
1689: l_id number;
1690: l_header_response po_change_requests.request_status%type;
1691: l_header_cancel varchar2(1);
1692: cursor l_header_cancel_csr(grp_id number) is
1693: select change_request_id,
1694: request_status
1691: l_header_cancel varchar2(1);
1692: cursor l_header_cancel_csr(grp_id number) is
1693: select change_request_id,
1694: request_status
1695: from po_change_requests
1696: where change_request_group_id = grp_id
1697: and action_type = 'CANCELLATION'
1698: and request_level = 'HEADER';
1699:
1696: where change_request_group_id = grp_id
1697: and action_type = 'CANCELLATION'
1698: and request_level = 'HEADER';
1699:
1700: l_additional_changes po_change_requests.additional_changes%type;
1701:
1702: cursor l_header_changes_csr(grp_id number)
1703: is
1704: select additional_changes
1701:
1702: cursor l_header_changes_csr(grp_id number)
1703: is
1704: select additional_changes
1705: from po_change_requests
1706: where change_request_group_id = grp_id
1707: and request_level = 'HEADER'
1708: and additional_changes is not null;
1709:
2338: lc_line_num varchar2(2000);
2339: lc_ship_num varchar2(2000);
2340: lc_buyer_pt_num mtl_system_items_kfv.concatenated_segments%TYPE;
2341: lc_old_sup_pt_num po_lines_all.vendor_product_num%TYPE;
2342: lc_new_sup_pt_num po_change_requests.new_supplier_part_number%TYPE;
2343: lc_old_prom_date varchar2(2000);
2344: lc_new_prom_date varchar2(2000);
2345: lc_old_qty varchar2(2000);
2346: lc_new_qty varchar2(2000);
2349: lc_action_type varchar2(2000);
2350: lc_item_desc po_lines_all.item_description%TYPE;
2351: lc_uom po_lines_all.unit_meas_lookup_code%TYPE;
2352: lc_ship_to_location hr_locations_all.location_code%TYPE;
2353: lc_action_code po_change_requests.ACTION_TYPE%TYPE;
2354: lc_reason po_change_requests.request_reason%TYPE;
2355: lc_split varchar2(2000);
2356: l_global_agreement_flag po_headers_all.GLOBAL_AGREEMENT_FLAG%TYPE := 'F';
2357:
2350: lc_item_desc po_lines_all.item_description%TYPE;
2351: lc_uom po_lines_all.unit_meas_lookup_code%TYPE;
2352: lc_ship_to_location hr_locations_all.location_code%TYPE;
2353: lc_action_code po_change_requests.ACTION_TYPE%TYPE;
2354: lc_reason po_change_requests.request_reason%TYPE;
2355: lc_split varchar2(2000);
2356: l_global_agreement_flag po_headers_all.GLOBAL_AGREEMENT_FLAG%TYPE := 'F';
2357:
2358: cursor l_po_chg_req_csr(grp_id number, l_planner_id number) is
2401: pcr.ACTION_TYPE ACTION_CODE,
2402: pcr.request_reason REASON,
2403: null SPLIT
2404: from
2405: po_change_requests pcr,
2406: po_lines_archive_all pla,
2407: mtl_system_items_kfv msi,
2408: mtl_planners mtp,
2409: financials_system_params_all fsp
2443: pcr.ACTION_TYPE ACTION_CODE,
2444: pcr.REQUEST_REASON REASON,
2445: null SPLIT
2446: from
2447: po_change_requests pcr,
2448: po_lines_archive_all pla,
2449: po_line_locations_archive_all plla,
2450: hr_locations_all hla, hz_locations hz,
2451: mtl_system_items_kfv msi,
2492: pcr.ACTION_TYPE ACTION_CODE,
2493: pcr.REQUEST_REASON REASON,
2494: fnd_message.get_string('PO','PO_WF_NOTIF_YES') SPLIT
2495: from
2496: po_change_requests pcr,
2497: po_lines_archive_all pla,
2498: po_line_locations_archive_all plla,
2499: hr_locations_all hla, hz_locations hz,
2500: mtl_system_items_kfv msi,
2519: )
2520: order by LINE_NUM,nvl(SHIPMENT_NUM,0);
2521:
2522: l_id number;
2523: l_header_response po_change_requests.request_status%type;
2524: l_header_cancel varchar2(1);
2525: cursor l_header_cancel_csr(grp_id number) is
2526: select change_request_id,
2527: request_status
2524: l_header_cancel varchar2(1);
2525: cursor l_header_cancel_csr(grp_id number) is
2526: select change_request_id,
2527: request_status
2528: from po_change_requests
2529: where change_request_group_id = grp_id
2530: and action_type = 'CANCELLATION'
2531: and request_level = 'HEADER';
2532:
2529: where change_request_group_id = grp_id
2530: and action_type = 'CANCELLATION'
2531: and request_level = 'HEADER';
2532:
2533: l_additional_changes po_change_requests.additional_changes%type;
2534:
2535: cursor l_header_changes_csr(grp_id number)
2536: is
2537: select additional_changes
2534:
2535: cursor l_header_changes_csr(grp_id number)
2536: is
2537: select additional_changes
2538: from po_change_requests
2539: where change_request_group_id = grp_id
2540: and request_level = 'HEADER'
2541: and additional_changes is not null;
2542:
3162: /* lc_line_num varchar2(2000);
3163: lc_ship_num varchar2(2000);
3164: lc_buyer_pt_num mtl_system_items_kfv.concatenated_segments%TYPE;
3165: lc_old_sup_pt_num po_lines_all.vendor_product_num%TYPE;
3166: lc_new_sup_pt_num po_change_requests.new_supplier_part_number%TYPE;
3167: lc_old_prom_date date;
3168: lc_new_prom_date date;
3169: lc_old_qty varchar2(2000);
3170: lc_new_qty varchar2(2000);
3173: lc_action_type varchar2(2000);
3174: lc_item_desc po_lines_all.item_description%TYPE;
3175: lc_uom po_lines_all.unit_meas_lookup_code%TYPE;
3176: lc_ship_to_location hr_locations_all.location_code%TYPE;
3177: lc_action_code po_change_requests.ACTION_TYPE%TYPE;
3178: lc_reason po_change_requests.request_reason%TYPE;
3179: lc_split varchar2(2000);
3180: l_global_agreement_flag po_headers_all.GLOBAL_AGREEMENT_FLAG%TYPE;
3181: l_po_rev number;
3174: lc_item_desc po_lines_all.item_description%TYPE;
3175: lc_uom po_lines_all.unit_meas_lookup_code%TYPE;
3176: lc_ship_to_location hr_locations_all.location_code%TYPE;
3177: lc_action_code po_change_requests.ACTION_TYPE%TYPE;
3178: lc_reason po_change_requests.request_reason%TYPE;
3179: lc_split varchar2(2000);
3180: l_global_agreement_flag po_headers_all.GLOBAL_AGREEMENT_FLAG%TYPE;
3181: l_po_rev number;
3182: l_rel_rev number;
3224: pcr.ACTION_TYPE ACTION_CODE,
3225: pcr.request_reason REASON,
3226: null SPLIT
3227: from
3228: po_change_requests pcr,
3229: po_lines_archive_all pla,
3230: mtl_system_items_kfv msi,
3231: FINANCIALS_SYSTEM_PARAMS_ALL FSP
3232: where pla.po_line_id = pcr.document_line_id
3261: pcr.ACTION_TYPE ACTION_CODE,
3262: pcr.REQUEST_REASON REASON,
3263: null SPLIT
3264: from
3265: po_change_requests pcr,
3266: po_lines_archive_all pla,
3267: po_line_locations_archive_all plla,
3268: hr_locations_all hla, hz_locations hz,
3269: mtl_system_items_kfv msi,
3304: pcr.ACTION_TYPE ACTION_CODE,
3305: pcr.REQUEST_REASON REASON,
3306: fnd_message.get_string('PO','PO_WF_NOTIF_YES') SPLIT
3307: from
3308: po_change_requests pcr,
3309: po_lines_archive_all pla,
3310: po_line_locations_archive_all plla,
3311: hr_locations_all hla, hz_locations hz,
3312: mtl_system_items_kfv msi,
3435: )
3436: order by LINE_NUM,SHIPMENT_NUM;
3437:
3438: l_id number;
3439: l_header_response po_change_requests.request_status%type;
3440: l_header_cancel varchar2(1);
3441:
3442: cursor l_header_cancel_csr(grp_id number) is
3443: select change_request_id,
3441:
3442: cursor l_header_cancel_csr(grp_id number) is
3443: select change_request_id,
3444: request_status
3445: from po_change_requests
3446: where change_request_group_id = grp_id
3447: and action_type = 'CANCELLATION'
3448: and request_level = 'HEADER';
3449:
3446: where change_request_group_id = grp_id
3447: and action_type = 'CANCELLATION'
3448: and request_level = 'HEADER';
3449:
3450: l_additional_changes po_change_requests.additional_changes%type;
3451:
3452: cursor l_header_changes_csr(grp_id number)
3453: is
3454: select additional_changes
3451:
3452: cursor l_header_changes_csr(grp_id number)
3453: is
3454: select additional_changes
3455: from po_change_requests
3456: where change_request_group_id = grp_id
3457: and request_level = 'HEADER'
3458: and additional_changes is not null;
3459: */
4234: l_old_prom_date varchar2(2000);
4235: l_new_prom_date varchar2(2000);
4236:
4237: l_doc_hdr_info varchar2(2000);
4238: lc_action_code po_change_requests.action_type%type;
4239: lc_line_num varchar2(2000);
4240: lc_ship_num varchar2(2000);
4241: lc_old_sup_pt_num po_lines_all.vendor_product_num%TYPE;
4242: lc_new_sup_pt_num po_change_requests.new_supplier_part_number%TYPE;
4238: lc_action_code po_change_requests.action_type%type;
4239: lc_line_num varchar2(2000);
4240: lc_ship_num varchar2(2000);
4241: lc_old_sup_pt_num po_lines_all.vendor_product_num%TYPE;
4242: lc_new_sup_pt_num po_change_requests.new_supplier_part_number%TYPE;
4243: lc_old_prom_date date;
4244: lc_new_prom_date date;
4245: lc_old_qty varchar2(2000);
4246: lc_new_qty varchar2(2000);
4249: lc_response varchar2(2000);
4250: lc_item_desc po_lines_all.item_description%TYPE;
4251: lc_uom po_lines_all.unit_meas_lookup_code%TYPE;
4252: lc_ship_to_location hr_locations_all.location_code%TYPE;
4253: lc_reason po_change_requests.response_reason%TYPE;
4254: lc_split varchar2(2000);
4255:
4256:
4257: cursor l_po_chg_req_csr(grp_id number) is
4293: null SHIP_TO_LOCATION,
4294: pcr.response_reason REASON,
4295: null SPLIT,
4296: pcr.action_type ACTION_CODE
4297: from po_change_requests pcr, po_lines_archive_all pla
4298: where pla.po_line_id = pcr.document_line_id
4299: and pcr.change_request_group_id =grp_id
4300: --and pcr.document_revision_num = pla.revision_num
4301: and pcr.request_level = 'LINE'
4321: pcr.response_reason REASON,
4322: decode(pcr.parent_line_location_id, null, null, fnd_message.get_string('PO','PO_WF_NOTIF_YES')) SPLIT,
4323: pcr.action_type ACTION_CODE
4324:
4325: from po_change_requests pcr, po_lines_archive_all pla, po_line_locations_archive_all plla,
4326: hr_locations_all hla, hz_locations hz
4327: where change_request_group_id =grp_id
4328: and pla.po_line_id = pcr.document_line_id
4329: and pla.latest_external_flag = 'Y'
4356: nvl(hla.location_code, substr(rtrim(hz.address1)||'-'||rtrim(hz.city),1,60)) SHIP_TO_LOCATION,
4357: pcr.response_reason REASON,
4358: decode(pcr.parent_line_location_id, null, null, fnd_message.get_string('PO','PO_WF_NOTIF_YES')) SPLIT,
4359: pcr.action_type ACTION_CODE
4360: from po_change_requests pcr, po_lines_archive_all pla, po_line_locations_archive_all plla,
4361: hr_locations_all hla, hz_locations hz
4362: where change_request_group_id =grp_id
4363: and pla.po_line_id = pcr.document_line_id
4364: and pla.latest_external_flag = 'Y'
4373: )
4374: order by LINE_NUM,nvl(SHIPMENT_NUM,0);
4375:
4376: l_id number;
4377: l_header_response po_change_requests.request_status%type;
4378: l_header_cancel varchar2(1);
4379:
4380: cursor l_header_cancel_csr(grp_id number) is
4381: select change_request_id,
4379:
4380: cursor l_header_cancel_csr(grp_id number) is
4381: select change_request_id,
4382: request_status
4383: from po_change_requests
4384: where change_request_group_id = grp_id
4385: and action_type = 'CANCELLATION'
4386: and request_level = 'HEADER';
4387:
4384: where change_request_group_id = grp_id
4385: and action_type = 'CANCELLATION'
4386: and request_level = 'HEADER';
4387:
4388: l_additional_changes po_change_requests.additional_changes%type;
4389:
4390: cursor l_header_changes_csr(grp_id number)
4391: is
4392: select additional_changes, request_status
4389:
4390: cursor l_header_changes_csr(grp_id number)
4391: is
4392: select additional_changes, request_status
4393: from po_change_requests
4394: where change_request_group_id = grp_id
4395: and request_level = 'HEADER'
4396: and additional_changes is not null; */
4397:
4403: into
4404: l_document_header_id,
4405: l_document_type,
4406: l_po_release_id
4407: from po_change_requests
4408: where change_request_group_id = p_chg_req_grp_id;
4409:
4410: l_progress := '001';
4411: l_base_url := POS_URL_PKG.get_external_url;
4980: l_rel_currency_code VARCHAR2(5);
4981:
4982: CURSOR l_header_cancel_csr(grp_id number) IS
4983: select change_request_id
4984: from po_change_requests
4985: where change_request_group_id = grp_id
4986: and action_type = 'CANCELLATION'
4987: and request_level = 'HEADER';
4988:
4986: and action_type = 'CANCELLATION'
4987: and request_level = 'HEADER';
4988:
4989: l_itemtype VARCHAR2(30);
4990: l_itemkey po_change_requests.wf_item_key%TYPE;
4991: l_header_id NUMBER;
4992: l_release_id NUMBER;
4993: l_chg_req_grp_id NUMBER;
4994: l_ack_req_flag VARCHAR2(30);
5200: l_id number;
5201: l_header_cancel varchar2(1);
5202: CURSOR l_header_cancel_csr(grp_id number) IS
5203: select change_request_id
5204: from po_change_requests
5205: where change_request_group_id = grp_id
5206: and action_type = 'CANCELLATION'
5207: and request_level = 'HEADER';
5208:
5206: and action_type = 'CANCELLATION'
5207: and request_level = 'HEADER';
5208:
5209: l_itemtype VARCHAR2(30);
5210: l_itemkey po_change_requests.wf_item_key%TYPE;
5211: l_header_id NUMBER;
5212: l_release_id NUMBER;
5213: l_chg_req_grp_id NUMBER;
5214: l_revision_num NUMBER;
5383: l_id number;
5384: l_header_cancel varchar2(1);
5385: CURSOR l_header_cancel_csr(grp_id number) IS
5386: select change_request_id
5387: from po_change_requests
5388: where change_request_group_id = grp_id
5389: and action_type = 'CANCELLATION'
5390: and request_level = 'HEADER';
5391:
5389: and action_type = 'CANCELLATION'
5390: and request_level = 'HEADER';
5391:
5392: l_itemtype VARCHAR2(30);
5393: l_itemkey po_change_requests.wf_item_key%TYPE;
5394: l_header_id NUMBER;
5395: l_release_id NUMBER;
5396: l_chg_req_grp_id NUMBER;
5397:
5540: l_id number;
5541: l_header_cancel varchar2(1);
5542: CURSOR l_header_cancel_csr(grp_id number) IS
5543: select change_request_id
5544: from po_change_requests
5545: where change_request_group_id = grp_id
5546: and action_type = 'CANCELLATION'
5547: and request_level = 'HEADER';
5548:
5546: and action_type = 'CANCELLATION'
5547: and request_level = 'HEADER';
5548:
5549: l_itemtype VARCHAR2(30);
5550: l_itemkey po_change_requests.wf_item_key%TYPE;
5551: l_header_id NUMBER;
5552: l_release_id NUMBER;
5553: l_chg_req_grp_id NUMBER;
5554:
5686: l_temp number;
5687: x_progress varchar2(3) := '000';
5688: cursor l_x_csr(id number) is
5689: select pcr.document_line_locatiON_id
5690: from po_change_requests pcr,
5691: po_distributions_all pda
5692: where pcr.document_line_location_id = pda.line_location_id
5693: and pcr.change_request_group_id = id -- added checks FPS Shipment Amount, Shipment price Prorate
5694: and (pcr.new_quantity is not null or pcr.new_amount is not null or pcr.new_price is not null)
5706: itemkey => itemkey,
5707: aname => 'PO_HEADER_ID');
5708:
5709: select count(*) into l_retro_count
5710: from po_change_requests pcr, po_headers_all poh
5711: where poh.po_header_id = pcr.document_header_id
5712: and poh.po_header_id = l_po_header_id
5713: and poh.type_lookup_code = 'BLANKET'
5714: and pcr.change_request_group_id = l_chg_req_grp_id
5723: close l_x_csr;
5724:
5725: select count(*)
5726: into l_header_change
5727: from po_change_requests
5728: where change_request_group_id = l_chg_req_grp_id
5729: and request_level = 'HEADER'
5730: and additional_changes is not null;
5731:
5800: l_authorization_status := 'APPROVED';
5801:
5802: select count(*)
5803: into l_add_changes_accepted
5804: from po_change_requests
5805: where change_request_group_id = l_chg_req_grp_id
5806: -- and request_status = 'ACCEPTED'
5807: and request_status = 'BUYER_APP' /* fix for bug 3691061 */
5808: and request_level = 'HEADER'
5814:
5815: if (l_authorization_status = 'REQUIRES REAPPROVAL') then
5816:
5817: select max(responded_by) into l_responded_by
5818: from po_change_requests
5819: where request_level = 'HEADER'
5820: -- and request_status = 'ACCEPTED'
5821: and request_status = 'BUYER_APP' /* fix for bug 3691061 */
5822: and additional_changes is not null
5854:
5855: else -- authorization status is approved
5856:
5857: -- added due to bug 3574114
5858: update po_change_requests
5859: set change_active_flag = 'N'
5860: where change_request_group_id = l_chg_req_grp_id;
5861:
5862: if(l_release_id is not null) then
6071: l_header_cancel varchar2(1);
6072:
6073: cursor l_header_cancel_csr(grp_id number) is
6074: select change_request_id
6075: from po_change_requests
6076: where change_request_group_id = grp_id
6077: and action_type = 'CANCELLATION'
6078: and request_level = 'HEADER';
6079:
6092: */
6093: cursor l_get_user_list_csr(x_document_id NUMBER,x_document_type varchar2) is
6094: select distinct created_by ,
6095: change_request_group_id
6096: from po_change_requests
6097: where request_status in ('BUYER_APP', 'WAIT_CANCEL_APP')
6098: and ((document_header_id = x_document_id) OR (po_release_id = x_document_id))
6099: and initiator = 'SUPPLIER'
6100: and document_type = x_document_type;
6101: --Bug 6722239 - End
6102:
6103: CURSOR getLineLocID(l_chg_req_grp_id_csr IN NUMBER) IS
6104: SELECT document_line_location_id
6105: FROM PO_CHANGE_REQUESTS
6106: WHERE change_request_group_id = l_chg_req_grp_id_csr
6107: AND request_level = 'SHIPMENT'
6108: AND request_status = 'BUYER_APP'
6109: AND (NEW_AMOUNT is not null OR NEW_QUANTITY is not null);
6110:
6111: /* Curosr to get the change_responded by*/
6112: CURSOR getChangeRespBy(l_chg_req_grp_id_csr IN NUMBER) IS
6113: SELECT distinct fndu.employee_id
6114: FROM po_change_requests pcr,
6115: fnd_user fndu
6116: WHERE pcr.change_request_group_id = l_chg_req_grp_id_csr
6117: AND fndu.user_id = pcr.responded_by;
6118:
6132: select distinct created_by,
6133: change_request_group_id
6134: into l_supplier_user_id ,
6135: l_change_request_group_id
6136: from po_change_requests
6137: where request_status in ('BUYER_APP', 'WAIT_CANCEL_APP')
6138: and po_release_id = l_document_id
6139: and initiator = 'SUPPLIER'
6140: and document_type = 'RELEASE';
6234: select distinct created_by ,
6235: change_request_group_id
6236: into l_supplier_user_id ,
6237: l_change_request_group_id
6238: from po_change_requests
6239: where request_status in ('BUYER_APP', 'WAIT_CANCEL_APP')
6240: and document_header_id = l_document_id
6241: and initiator = 'SUPPLIER'
6242: and document_type = 'PO';
6381: END LOOP;
6382: CLOSE getLineLocID;
6383: end if;
6384:
6385: update po_change_requests
6386: set request_status = 'ACCEPTED'
6387: where change_request_group_id = l_change_request_group_id
6388: and request_status in ('BUYER_APP', 'WAIT_CANCEL_APP');
6389:
6388: and request_status in ('BUYER_APP', 'WAIT_CANCEL_APP');
6389:
6390: /* added code for bug 3574114 - update change active flag due to final buyer response */
6391:
6392: update po_change_requests
6393: set change_active_flag = 'N'
6394: where change_request_group_id = l_change_request_group_id;
6395:
6396: else
6395:
6396: else
6397: resultout := 'N';
6398:
6399: update po_change_requests
6400: set request_status = 'REJECTED',
6401: response_reason = null
6402: where change_request_group_id = l_change_request_group_id
6403: and request_status in ('BUYER_APP', 'WAIT_CANCEL_APP');
6403: and request_status in ('BUYER_APP', 'WAIT_CANCEL_APP');
6404:
6405: /* added code for bug 3574114 - update change active flag due to final buyer response */
6406:
6407: update po_change_requests
6408: set change_active_flag = 'N'
6409: where change_request_group_id = l_change_request_group_id;
6410:
6411: END if;
6476: l_expiration_date);
6477: x_resultout:=l_role_name;
6478: -- Bug 6722239 - End
6479:
6480: /* Bug 4949617 get the reponded_by id from po_change_requests table */
6481: OPEN getChangeRespBy(l_change_request_group_id);
6482: LOOP
6483: FETCH getChangeRespBy INTO l_responded_by_id;
6484: EXIT WHEN getChangeRespBy%NOTFOUND;
6619:
6620: x_progress varchar2(3) := '000';
6621: cursor l_change_csr(grp_id number) is
6622: select change_request_id from
6623: po_change_requests
6624: where action_type = 'MODIFICATION'
6625: and change_request_group_id = grp_id;
6626:
6627: BEGIN
6723:
6724: if(l_document_type = 'RELEASE') then
6725: select count(1)
6726: into l_count
6727: from po_change_requests
6728: where initiator = 'SUPPLIER'
6729: and document_type = 'RELEASE'
6730: and po_release_id = l_document_id
6731: and request_status = 'BUYER_APP';
6731: and request_status = 'BUYER_APP';
6732:
6733: select count(1)
6734: into l_cancel_app_count
6735: from po_change_requests
6736: where initiator = 'SUPPLIER'
6737: and document_type = 'RELEASE'
6738: and po_release_id = l_document_id
6739: and request_status = 'WAIT_CANCEL_APP';
6741: if ((l_count > 0) OR (l_cancel_app_count > 0)) then
6742: -- no chance for no_data_found exception.
6743: select distinct(change_request_group_id)
6744: into l_change_request_group_id
6745: from po_change_requests
6746: where initiator = 'SUPPLIER'
6747: and document_type = 'RELEASE'
6748: and po_release_id = l_document_id
6749: and request_status in ('BUYER_APP', 'WAIT_CANCEL_APP');
6753: -- so as to prevent it from blocking processing of other records
6754:
6755: if (l_count > 0 AND l_cancel_app_count > 0) then
6756:
6757: update po_change_requests set request_status = 'ACCEPTED'
6758: where initiator = 'SUPPLIER'
6759: and document_type = 'RELEASE'
6760: and po_release_id = l_document_id
6761: and request_status = 'WAIT_CANCEL_APP';
6764:
6765: else
6766: select count(1)
6767: into l_count
6768: from po_change_requests
6769: where initiator = 'SUPPLIER'
6770: and document_type = 'PO'
6771: and document_header_id = l_document_id
6772: and request_status = 'BUYER_APP';
6772: and request_status = 'BUYER_APP';
6773:
6774: select count(1)
6775: into l_cancel_app_count
6776: from po_change_requests
6777: where initiator = 'SUPPLIER'
6778: and document_type = 'PO'
6779: and document_header_id = l_document_id
6780: and request_status = 'WAIT_CANCEL_APP';
6782: if ((l_count > 0) OR (l_cancel_app_count > 0)) then
6783: -- no chance for no_data_found exception.
6784: select distinct(change_request_group_id)
6785: into l_change_request_group_id
6786: from po_change_requests
6787: where initiator = 'SUPPLIER'
6788: and document_type = 'PO'
6789: and document_header_id = l_document_id
6790: and request_status in ('BUYER_APP', 'WAIT_CANCEL_APP');
6794: -- so as to prevent it from blocking processing of other records
6795:
6796: if (l_count > 0 AND l_cancel_app_count > 0) then
6797:
6798: update po_change_requests set request_status = 'ACCEPTED'
6799: where initiator = 'SUPPLIER'
6800: and document_type = 'PO'
6801: and document_header_id = l_document_id
6802: and request_status = 'WAIT_CANCEL_APP';
6867: --l_id number;
6868: --l_header_cancel varchar2(1);
6869: --cursor l_header_cancel_csr(grp_id number) is
6870: --select change_request_id
6871: --from po_change_requests
6872: --where change_request_group_id = grp_id
6873: --and action_type = 'CANCELLATION'
6874: --and request_level = 'HEADER';
6875: --l_doc_hdr_info varchar2(2000);
6877:
6878: /* Curosr to get the change_responded by*/
6879: CURSOR getChangeRespBy(l_chg_req_grp_id_csr IN NUMBER) IS
6880: SELECT distinct fndu.employee_id
6881: FROM po_change_requests pcr,
6882: fnd_user fndu
6883: WHERE pcr.change_request_group_id = l_chg_req_grp_id_csr
6884: AND fndu.user_id = pcr.responded_by;
6885:
6918:
6919: if(l_supplier_username is null) then
6920: select max(created_by)
6921: into l_supplier_user_id
6922: from po_change_requests
6923: where change_request_group_id = p_chg_req_grp_id;
6924:
6925: select user_name
6926: into l_supplier_username
6927: from fnd_user
6928: where user_id = l_supplier_user_id;
6929: end if;
6930:
6931: /* Bug 4949617 get the reponded_by id from po_change_requests table */
6932: OPEN getChangeRespBy(p_chg_req_grp_id);
6933: LOOP
6934: FETCH getChangeRespBy INTO l_responded_by_id;
6935: EXIT WHEN getChangeRespBy%NOTFOUND;
7056: x_return_status := 'S';
7057: if(p_release_id is null) then
7058: select change_request_group_id
7059: into l_chn_req_grp_id
7060: from po_change_requests
7061: where document_header_id = p_header_id
7062: and request_status = 'PENDING'
7063: and request_level = 'HEADER'
7064: and initiator = 'SUPPLIER'
7066:
7067: else
7068: select change_request_group_id
7069: into l_chn_req_grp_id
7070: from po_change_requests
7071: where po_release_id = p_release_id
7072: and request_status = 'PENDING'
7073: and request_level = 'HEADER'
7074: and initiator = 'SUPPLIER'
7075: and action_type ='CANCELLATION';
7076: end if;
7077: l_progress := '001';
7078: if(p_acc_rej = 'A') then
7079: update po_change_requests
7080: set request_status = 'BUYER_APP',
7081: responded_by = p_user_id,
7082: response_date = sysdate,
7083: response_reason = p_reason
7082: response_date = sysdate,
7083: response_reason = p_reason
7084: where change_request_group_id = l_chn_req_grp_id;
7085: else
7086: update po_change_requests
7087: set request_status = 'REJECTED',
7088: change_active_flag = 'N',
7089: responded_by = p_user_id,
7090: response_date = sysdate,
7139: l_api_name varchar2(50):= 'ProcessResponse';
7140: l_count number;
7141: l_number_of_buyer_app number;
7142: l_acc_req_flag varchar2(1);
7143: l_item_key po_change_requests.wf_item_key%TYPE;
7144: l_return_status varchar2(1);
7145: l_po_cancel_api exception;
7146: l_org_id number;
7147: l_agent_id number;
7166: l_closed_code po_headers_all.closed_code%type;
7167:
7168: cursor l_accept_csr(grp_id number) is
7169: select change_request_id
7170: from po_change_requests
7171: where change_request_group_id = grp_id
7172: and request_status in ('ACCEPTED','BUYER_APP');
7173:
7174: cursor l_reject_csr(grp_id number) is
7172: and request_status in ('ACCEPTED','BUYER_APP');
7173:
7174: cursor l_reject_csr(grp_id number) is
7175: select change_request_id
7176: from po_change_requests
7177: where change_request_group_id = grp_id
7178: and request_status = 'REJECTED';
7179:
7180: cursor l_pending_csr(grp_id number) is
7178: and request_status = 'REJECTED';
7179:
7180: cursor l_pending_csr(grp_id number) is
7181: select change_request_id
7182: from po_change_requests
7183: where change_request_group_id = grp_id
7184: and request_status = 'PENDING';
7185:
7186:
7298: if(p_flag is null) then
7299: begin
7300: select distinct wf_item_key
7301: into l_item_key
7302: from po_change_requests
7303: where change_request_group_id = p_chg_req_grp_id;
7304: wf_engine.completeActivity('POSCHORD',l_item_key,'NOTIFY_BUYER_OF_CHN', 'Abort');
7305: exception when others then
7306: null;
7312:
7313:
7314: x_progress:='004';
7315:
7316: -- 2. Update po_change_requests table.. for those supplier change request to cancel, if accepted
7317: -- change them from BUYER_APP to ACCEPTED
7318: update po_change_requests
7319: set request_status = 'ACCEPTED'
7320: -- change_active_flag = 'N' /* commented out due to bug 3574114 */
7314: x_progress:='004';
7315:
7316: -- 2. Update po_change_requests table.. for those supplier change request to cancel, if accepted
7317: -- change them from BUYER_APP to ACCEPTED
7318: update po_change_requests
7319: set request_status = 'ACCEPTED'
7320: -- change_active_flag = 'N' /* commented out due to bug 3574114 */
7321: where change_request_group_id = p_chg_req_grp_id
7322: and request_status in ('BUYER_APP', 'WAIT_CANCEL_APP')
7351: x_progress:='005';
7352:
7353: select count(1)
7354: into l_number_of_buyer_app
7355: from po_change_requests
7356: where change_request_group_id = p_chg_req_grp_id
7357: and request_status = 'BUYER_APP';
7358:
7359: if(l_number_of_buyer_app = 0) then
7386: x_return_code := l_mc_return_code;
7387:
7388:
7389: /* COMMENTED OUT - WAIT_MGR_APP status is no longer used and is replaced by BUYER_APP
7390: update po_change_requests
7391: set request_status = 'WAIT_MGR_APP'
7392: where change_request_group_id = p_chg_req_grp_id
7393: and request_status = 'BUYER_APP';
7394: */
7441: cursor l_requestors_csr(grp_id number)
7442: is
7443: select pda.deliver_to_person_id
7444: from
7445: po_change_requests pcr,
7446: po_distributions_all pda,
7447: po_line_locations_all pll
7448: where pcr.change_request_group_id = grp_id
7449: and pcr.request_level = 'LINE'
7452: and pll.shipment_type = 'STANDARD'
7453: union
7454: select pda.deliver_to_person_id
7455: from
7456: po_change_requests pcr,
7457: po_distributions_all pda
7458: where pcr.change_request_group_id = grp_id
7459: and pcr.request_level = 'SHIPMENT'
7460: and pcr.document_line_location_id = pda.line_location_id;
7465: distinct mtp.employee_id
7466: from
7467: mtl_system_items msi,
7468: mtl_planners mtp,
7469: po_change_requests pcr,
7470: po_lines_all pla,
7471: financials_system_params_all fsp
7472: where pcr.change_request_group_id = grp_id
7473: and pcr.document_line_id = pla.po_line_id
7609: ELSE
7610: l_responded_by :=fnd_global.user_id;
7611: END IF;
7612:
7613: update po_change_requests
7614: set request_status ='BUYER_APP',
7615: responded_by = l_responded_by,
7616: response_date = sysdate
7617: where change_request_group_id = l_chg_req_grp_id
7679: END IF;
7680:
7681:
7682:
7683: update po_change_requests
7684: set request_status ='REJECTED',
7685: change_active_flag = 'N',
7686: responded_by = fnd_global.user_id,
7687: response_date = sysdate
7708: l_progress varchar2(100) := '000';
7709: l_count number;
7710: l_seq number;
7711: l_po_style varchar2(10);
7712: l_item_key po_change_requests.wf_item_key%TYPE;
7713: l_doc_type_code varchar2(1);
7714: l_buyer_notif_code varchar2(240);
7715: l_buyer_agent_id number;
7716: l_buyer_username fnd_user.user_name%TYPE;
7864: /*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
7865: all shipments and no changes was made. Thus, in this case, we will NOT store the wf_item_key or wf_item_type anywhere.
7866: */
7867: if(p_chg_req_grp_id is not null) then
7868: update po_change_requests
7869: set wf_item_type = 'POSCHORD',wf_item_key = l_item_key
7870: where change_request_group_id = p_chg_req_grp_id;
7871: end if;
7872: l_progress := '004';
8042: x_count out nocopy number)
8043: is
8044: CURSOR additional_changes(chg_req_grp_id_csr IN NUMBER) IS
8045: select additional_changes
8046: from po_change_requests
8047: where change_request_group_id = chg_req_grp_id_csr
8048: and request_level = 'HEADER'
8049: and additional_changes is not null;
8050:
8049: and additional_changes is not null;
8050:
8051: -- returns 1 when only additional changes at header level are requested otherwise 0
8052: CURSOR getRequestLevel(chg_req_grp_id_csr IN NUMBER) IS
8053: SELECT COUNT(1) FROM po_change_requests WHERE NOT EXISTS ( SELECT 1
8054: FROM po_change_requests
8055: WHERE change_request_group_id = chg_req_grp_id_csr
8056: AND request_level IN ('LINE','SHIPMENT'))
8057: AND change_request_group_id = chg_req_grp_id_csr
8050:
8051: -- returns 1 when only additional changes at header level are requested otherwise 0
8052: CURSOR getRequestLevel(chg_req_grp_id_csr IN NUMBER) IS
8053: SELECT COUNT(1) FROM po_change_requests WHERE NOT EXISTS ( SELECT 1
8054: FROM po_change_requests
8055: WHERE change_request_group_id = chg_req_grp_id_csr
8056: AND request_level IN ('LINE','SHIPMENT'))
8057: AND change_request_group_id = chg_req_grp_id_csr
8058: AND action_type = 'MODIFICATION';
8058: AND action_type = 'MODIFICATION';
8059:
8060: -- Bug 7487461
8061: -- Modified the data type for additional changes to capture the entire text.
8062: l_additional_changes po_change_requests.additional_changes%type;
8063: l_count NUMBER(10);
8064: begin
8065: OPEN getRequestLevel(p_chg_req_grp_id);
8066: LOOP
8168: BEGIN
8169: select decode(count(distinct(action_type)),2,'BOTH',1,'CHG') into
8170: l_req_mode
8171: from
8172: po_change_requests
8173: where
8174: change_request_group_id = p_chg_req_grp_id
8175: and request_level in('LINE','SHIPMENT')
8176: and action_type in('MODIFICATION','CANCELLATION');
8180: elsif l_req_mode = 'CHG' then
8181: select distinct(action_type) into
8182: l_temp
8183: from
8184: po_change_requests
8185: where
8186: change_request_group_id = p_chg_req_grp_id
8187: and request_level in('LINE','SHIPMENT')
8188: and action_type in('MODIFICATION','CANCELLATION');
8250: l_revision_num varchar2(15) := null;
8251:
8252: CURSOR HdrCancelCsrCount(p_chg_req_grp_id_csr IN NUMBER) IS
8253: SELECT count(*)
8254: FROM po_change_requests pcr
8255: WHERE pcr.CHANGE_REQUEST_GROUP_ID = p_chg_req_grp_id_csr
8256: AND pcr.REQUEST_LEVEL = 'HEADER'
8257: AND pcr.ACTION_TYPE = 'CANCELLATION';
8258:
8260: SELECT pcr.action_type,
8261: pcr.request_status,
8262: poh.type_lookup_code,
8263: pcr.document_num
8264: FROM PO_CHANGE_REQUESTS pcr,
8265: PO_HEADERS_ALL poh
8266: WHERE pcr.CHANGE_REQUEST_GROUP_ID = p_chg_req_grp_id_csr
8267: AND pcr.REQUEST_LEVEL = 'HEADER'
8268: AND pcr.ACTION_TYPE = 'CANCELLATION'
8269: AND pcr.document_header_id = poh.po_header_id;
8270:
8271: CURSOR getRevisionNum(p_chg_req_grp_id_csr IN NUMBER) IS
8272: SELECT distinct(pcr.document_revision_num)
8273: FROM po_change_requests pcr
8274: WHERE pcr.CHANGE_REQUEST_GROUP_ID = p_chg_req_grp_id_csr;
8275:
8276: CURSOR getRevisionNumAck(po_header_id_csr IN NUMBER) IS
8277: SELECT revision_num