[Home] [Help]
125: v_req_grp_id number;
126:
127: cursor c1(p_po_header_id in number,p_document_type in varchar2) is
128: select change_request_group_id
129: from po_change_requests
130: where document_header_id = p_po_header_id and
131: request_status in ('NEW', 'PENDING') and
132: request_level in ('HEADER', 'LINE', 'SHIPMENT') and
133: document_type = p_document_type;
133: document_type = p_document_type;
134:
135: cursor c2(p_po_release_id in number, p_document_type in varchar2) is
136: select change_request_group_id
137: from po_change_requests
138: where po_release_id = p_po_release_id and
139: request_status in ('NEW', 'PENDING') and
140: request_level in ('HEADER', 'LINE', 'SHIPMENT') and
141: document_type = p_document_type;
451: x_return_status OUT NOCOPY VARCHAR2,
452: p_po_header_id IN NUMBER,
453: p_po_release_id IN NUMBER,
454: p_revision_num IN NUMBER,
455: p_po_change_requests IN pos_chg_rec_tbl,
456: x_request_group_id OUT NOCOPY NUMBER,
457: p_chn_int_cont_num IN varchar2 default null,
458: p_chn_source IN varchar2 default null,
459: p_chn_requestor_username in varchar2 default null,
465: x_return_code varchar2(40);
466: v_request_group_id number;
467: accp_flag char(1);
468: v_buyer_id number;
469: v_document_type po_change_requests.DOCUMENT_TYPE%TYPE;
470: l_user_id NUMBER := fnd_global.user_id;
471: l_login_id NUMBER := fnd_global.login_id;
472: l_api_name CONSTANT VARCHAR2(30) := 'save_request';
473: l_api_version_number CONSTANT NUMBER := 1.0;
495: l_user_id := p_user_id;
496: l_login_id := p_login_id;
497: end if;
498:
499: rec_cnt := p_po_change_requests.count();
500: -- get the ChangerequestGroupID only when changes are requested
501: IF( rec_cnt > 0) THEN
502: -- Get the document type of the first record to get the unique request group id
503: if (p_po_release_id is not null) then
515: x_request_group_id := v_request_group_id;
516: END IF;
517:
518: FOR i in 1..rec_cnt LOOP
519: if(p_po_change_requests(i).action_type not in ('ACCEPT','REJECT')) then
520:
521: insert into po_change_requests(
522: change_request_group_id, change_request_id,
523: initiator, action_type, request_reason,
517:
518: FOR i in 1..rec_cnt LOOP
519: if(p_po_change_requests(i).action_type not in ('ACCEPT','REJECT')) then
520:
521: insert into po_change_requests(
522: change_request_group_id, change_request_id,
523: initiator, action_type, request_reason,
524: request_level, request_status, document_type,
525: document_header_id, document_num,
545: NEW_PROGRESS_TYPE,NEW_PAY_DESCRIPTION --<< Complex work changes for R12 >>
546:
547: )
548: values (x_request_group_id,po_chg_request_seq.nextval,
549: p_po_change_requests(i).initiator,
550: p_po_change_requests(i).action_type,
551: p_po_change_requests(i).request_reason,
552: p_po_change_requests(i).request_level,
553: p_po_change_requests(i).request_status,
546:
547: )
548: values (x_request_group_id,po_chg_request_seq.nextval,
549: p_po_change_requests(i).initiator,
550: p_po_change_requests(i).action_type,
551: p_po_change_requests(i).request_reason,
552: p_po_change_requests(i).request_level,
553: p_po_change_requests(i).request_status,
554: p_po_change_requests(i).document_type,
547: )
548: values (x_request_group_id,po_chg_request_seq.nextval,
549: p_po_change_requests(i).initiator,
550: p_po_change_requests(i).action_type,
551: p_po_change_requests(i).request_reason,
552: p_po_change_requests(i).request_level,
553: p_po_change_requests(i).request_status,
554: p_po_change_requests(i).document_type,
555: p_po_change_requests(i).document_header_id,
548: values (x_request_group_id,po_chg_request_seq.nextval,
549: p_po_change_requests(i).initiator,
550: p_po_change_requests(i).action_type,
551: p_po_change_requests(i).request_reason,
552: p_po_change_requests(i).request_level,
553: p_po_change_requests(i).request_status,
554: p_po_change_requests(i).document_type,
555: p_po_change_requests(i).document_header_id,
556: p_po_change_requests(i).document_num,
549: p_po_change_requests(i).initiator,
550: p_po_change_requests(i).action_type,
551: p_po_change_requests(i).request_reason,
552: p_po_change_requests(i).request_level,
553: p_po_change_requests(i).request_status,
554: p_po_change_requests(i).document_type,
555: p_po_change_requests(i).document_header_id,
556: p_po_change_requests(i).document_num,
557: to_number(p_po_change_requests(i).document_revision_num),
550: p_po_change_requests(i).action_type,
551: p_po_change_requests(i).request_reason,
552: p_po_change_requests(i).request_level,
553: p_po_change_requests(i).request_status,
554: p_po_change_requests(i).document_type,
555: p_po_change_requests(i).document_header_id,
556: p_po_change_requests(i).document_num,
557: to_number(p_po_change_requests(i).document_revision_num),
558: p_po_change_requests(i).po_release_id,
551: p_po_change_requests(i).request_reason,
552: p_po_change_requests(i).request_level,
553: p_po_change_requests(i).request_status,
554: p_po_change_requests(i).document_type,
555: p_po_change_requests(i).document_header_id,
556: p_po_change_requests(i).document_num,
557: to_number(p_po_change_requests(i).document_revision_num),
558: p_po_change_requests(i).po_release_id,
559: l_user_id,sysdate,l_login_id,sysdate,l_login_id,
552: p_po_change_requests(i).request_level,
553: p_po_change_requests(i).request_status,
554: p_po_change_requests(i).document_type,
555: p_po_change_requests(i).document_header_id,
556: p_po_change_requests(i).document_num,
557: to_number(p_po_change_requests(i).document_revision_num),
558: p_po_change_requests(i).po_release_id,
559: l_user_id,sysdate,l_login_id,sysdate,l_login_id,
560: p_po_change_requests(i).document_line_id,
553: p_po_change_requests(i).request_status,
554: p_po_change_requests(i).document_type,
555: p_po_change_requests(i).document_header_id,
556: p_po_change_requests(i).document_num,
557: to_number(p_po_change_requests(i).document_revision_num),
558: p_po_change_requests(i).po_release_id,
559: l_user_id,sysdate,l_login_id,sysdate,l_login_id,
560: p_po_change_requests(i).document_line_id,
561: p_po_change_requests(i).document_line_number,
554: p_po_change_requests(i).document_type,
555: p_po_change_requests(i).document_header_id,
556: p_po_change_requests(i).document_num,
557: to_number(p_po_change_requests(i).document_revision_num),
558: p_po_change_requests(i).po_release_id,
559: l_user_id,sysdate,l_login_id,sysdate,l_login_id,
560: p_po_change_requests(i).document_line_id,
561: p_po_change_requests(i).document_line_number,
562: p_po_change_requests(i).document_line_location_id,
556: p_po_change_requests(i).document_num,
557: to_number(p_po_change_requests(i).document_revision_num),
558: p_po_change_requests(i).po_release_id,
559: l_user_id,sysdate,l_login_id,sysdate,l_login_id,
560: p_po_change_requests(i).document_line_id,
561: p_po_change_requests(i).document_line_number,
562: p_po_change_requests(i).document_line_location_id,
563: p_po_change_requests(i).document_shipment_number,
564: p_po_change_requests(i).parent_line_location_id,
557: to_number(p_po_change_requests(i).document_revision_num),
558: p_po_change_requests(i).po_release_id,
559: l_user_id,sysdate,l_login_id,sysdate,l_login_id,
560: p_po_change_requests(i).document_line_id,
561: p_po_change_requests(i).document_line_number,
562: p_po_change_requests(i).document_line_location_id,
563: p_po_change_requests(i).document_shipment_number,
564: p_po_change_requests(i).parent_line_location_id,
565: p_po_change_requests(i).document_distribution_id,
558: p_po_change_requests(i).po_release_id,
559: l_user_id,sysdate,l_login_id,sysdate,l_login_id,
560: p_po_change_requests(i).document_line_id,
561: p_po_change_requests(i).document_line_number,
562: p_po_change_requests(i).document_line_location_id,
563: p_po_change_requests(i).document_shipment_number,
564: p_po_change_requests(i).parent_line_location_id,
565: p_po_change_requests(i).document_distribution_id,
566: p_po_change_requests(i).document_distribution_number,
559: l_user_id,sysdate,l_login_id,sysdate,l_login_id,
560: p_po_change_requests(i).document_line_id,
561: p_po_change_requests(i).document_line_number,
562: p_po_change_requests(i).document_line_location_id,
563: p_po_change_requests(i).document_shipment_number,
564: p_po_change_requests(i).parent_line_location_id,
565: p_po_change_requests(i).document_distribution_id,
566: p_po_change_requests(i).document_distribution_number,
567: p_po_change_requests(i).old_quantity,
560: p_po_change_requests(i).document_line_id,
561: p_po_change_requests(i).document_line_number,
562: p_po_change_requests(i).document_line_location_id,
563: p_po_change_requests(i).document_shipment_number,
564: p_po_change_requests(i).parent_line_location_id,
565: p_po_change_requests(i).document_distribution_id,
566: p_po_change_requests(i).document_distribution_number,
567: p_po_change_requests(i).old_quantity,
568: p_po_change_requests(i).new_quantity,
561: p_po_change_requests(i).document_line_number,
562: p_po_change_requests(i).document_line_location_id,
563: p_po_change_requests(i).document_shipment_number,
564: p_po_change_requests(i).parent_line_location_id,
565: p_po_change_requests(i).document_distribution_id,
566: p_po_change_requests(i).document_distribution_number,
567: p_po_change_requests(i).old_quantity,
568: p_po_change_requests(i).new_quantity,
569: p_po_change_requests(i).old_promised_date,
562: p_po_change_requests(i).document_line_location_id,
563: p_po_change_requests(i).document_shipment_number,
564: p_po_change_requests(i).parent_line_location_id,
565: p_po_change_requests(i).document_distribution_id,
566: p_po_change_requests(i).document_distribution_number,
567: p_po_change_requests(i).old_quantity,
568: p_po_change_requests(i).new_quantity,
569: p_po_change_requests(i).old_promised_date,
570: p_po_change_requests(i).new_promised_date,
563: p_po_change_requests(i).document_shipment_number,
564: p_po_change_requests(i).parent_line_location_id,
565: p_po_change_requests(i).document_distribution_id,
566: p_po_change_requests(i).document_distribution_number,
567: p_po_change_requests(i).old_quantity,
568: p_po_change_requests(i).new_quantity,
569: p_po_change_requests(i).old_promised_date,
570: p_po_change_requests(i).new_promised_date,
571: p_po_change_requests(i).old_supplier_part_number,
564: p_po_change_requests(i).parent_line_location_id,
565: p_po_change_requests(i).document_distribution_id,
566: p_po_change_requests(i).document_distribution_number,
567: p_po_change_requests(i).old_quantity,
568: p_po_change_requests(i).new_quantity,
569: p_po_change_requests(i).old_promised_date,
570: p_po_change_requests(i).new_promised_date,
571: p_po_change_requests(i).old_supplier_part_number,
572: p_po_change_requests(i).new_supplier_part_number,
565: p_po_change_requests(i).document_distribution_id,
566: p_po_change_requests(i).document_distribution_number,
567: p_po_change_requests(i).old_quantity,
568: p_po_change_requests(i).new_quantity,
569: p_po_change_requests(i).old_promised_date,
570: p_po_change_requests(i).new_promised_date,
571: p_po_change_requests(i).old_supplier_part_number,
572: p_po_change_requests(i).new_supplier_part_number,
573: p_po_change_requests(i).old_price,
566: p_po_change_requests(i).document_distribution_number,
567: p_po_change_requests(i).old_quantity,
568: p_po_change_requests(i).new_quantity,
569: p_po_change_requests(i).old_promised_date,
570: p_po_change_requests(i).new_promised_date,
571: p_po_change_requests(i).old_supplier_part_number,
572: p_po_change_requests(i).new_supplier_part_number,
573: p_po_change_requests(i).old_price,
574: p_po_change_requests(i).new_price,
567: p_po_change_requests(i).old_quantity,
568: p_po_change_requests(i).new_quantity,
569: p_po_change_requests(i).old_promised_date,
570: p_po_change_requests(i).new_promised_date,
571: p_po_change_requests(i).old_supplier_part_number,
572: p_po_change_requests(i).new_supplier_part_number,
573: p_po_change_requests(i).old_price,
574: p_po_change_requests(i).new_price,
575: p_po_change_requests(i).old_need_by_date,
568: p_po_change_requests(i).new_quantity,
569: p_po_change_requests(i).old_promised_date,
570: p_po_change_requests(i).new_promised_date,
571: p_po_change_requests(i).old_supplier_part_number,
572: p_po_change_requests(i).new_supplier_part_number,
573: p_po_change_requests(i).old_price,
574: p_po_change_requests(i).new_price,
575: p_po_change_requests(i).old_need_by_date,
576: p_po_change_requests(i).new_need_by_date,
569: p_po_change_requests(i).old_promised_date,
570: p_po_change_requests(i).new_promised_date,
571: p_po_change_requests(i).old_supplier_part_number,
572: p_po_change_requests(i).new_supplier_part_number,
573: p_po_change_requests(i).old_price,
574: p_po_change_requests(i).new_price,
575: p_po_change_requests(i).old_need_by_date,
576: p_po_change_requests(i).new_need_by_date,
577: p_po_change_requests(i).old_supplier_reference_number,
570: p_po_change_requests(i).new_promised_date,
571: p_po_change_requests(i).old_supplier_part_number,
572: p_po_change_requests(i).new_supplier_part_number,
573: p_po_change_requests(i).old_price,
574: p_po_change_requests(i).new_price,
575: p_po_change_requests(i).old_need_by_date,
576: p_po_change_requests(i).new_need_by_date,
577: p_po_change_requests(i).old_supplier_reference_number,
578: p_po_change_requests(i).new_supplier_reference_number,
571: p_po_change_requests(i).old_supplier_part_number,
572: p_po_change_requests(i).new_supplier_part_number,
573: p_po_change_requests(i).old_price,
574: p_po_change_requests(i).new_price,
575: p_po_change_requests(i).old_need_by_date,
576: p_po_change_requests(i).new_need_by_date,
577: p_po_change_requests(i).old_supplier_reference_number,
578: p_po_change_requests(i).new_supplier_reference_number,
579: p_po_change_requests(i).Approval_Required_Flag,
572: p_po_change_requests(i).new_supplier_part_number,
573: p_po_change_requests(i).old_price,
574: p_po_change_requests(i).new_price,
575: p_po_change_requests(i).old_need_by_date,
576: p_po_change_requests(i).new_need_by_date,
577: p_po_change_requests(i).old_supplier_reference_number,
578: p_po_change_requests(i).new_supplier_reference_number,
579: p_po_change_requests(i).Approval_Required_Flag,
580: p_po_change_requests(i).Parent_Change_request_Id,
573: p_po_change_requests(i).old_price,
574: p_po_change_requests(i).new_price,
575: p_po_change_requests(i).old_need_by_date,
576: p_po_change_requests(i).new_need_by_date,
577: p_po_change_requests(i).old_supplier_reference_number,
578: p_po_change_requests(i).new_supplier_reference_number,
579: p_po_change_requests(i).Approval_Required_Flag,
580: p_po_change_requests(i).Parent_Change_request_Id,
581: p_po_change_requests(i).Requester_id,
574: p_po_change_requests(i).new_price,
575: p_po_change_requests(i).old_need_by_date,
576: p_po_change_requests(i).new_need_by_date,
577: p_po_change_requests(i).old_supplier_reference_number,
578: p_po_change_requests(i).new_supplier_reference_number,
579: p_po_change_requests(i).Approval_Required_Flag,
580: p_po_change_requests(i).Parent_Change_request_Id,
581: p_po_change_requests(i).Requester_id,
582: p_po_change_requests(i).Old_Supplier_Order_Number,
575: p_po_change_requests(i).old_need_by_date,
576: p_po_change_requests(i).new_need_by_date,
577: p_po_change_requests(i).old_supplier_reference_number,
578: p_po_change_requests(i).new_supplier_reference_number,
579: p_po_change_requests(i).Approval_Required_Flag,
580: p_po_change_requests(i).Parent_Change_request_Id,
581: p_po_change_requests(i).Requester_id,
582: p_po_change_requests(i).Old_Supplier_Order_Number,
583: p_po_change_requests(i).New_Supplier_Order_Number,
576: p_po_change_requests(i).new_need_by_date,
577: p_po_change_requests(i).old_supplier_reference_number,
578: p_po_change_requests(i).new_supplier_reference_number,
579: p_po_change_requests(i).Approval_Required_Flag,
580: p_po_change_requests(i).Parent_Change_request_Id,
581: p_po_change_requests(i).Requester_id,
582: p_po_change_requests(i).Old_Supplier_Order_Number,
583: p_po_change_requests(i).New_Supplier_Order_Number,
584: p_po_change_requests(i).Old_Supplier_Order_Line_Number,
577: p_po_change_requests(i).old_supplier_reference_number,
578: p_po_change_requests(i).new_supplier_reference_number,
579: p_po_change_requests(i).Approval_Required_Flag,
580: p_po_change_requests(i).Parent_Change_request_Id,
581: p_po_change_requests(i).Requester_id,
582: p_po_change_requests(i).Old_Supplier_Order_Number,
583: p_po_change_requests(i).New_Supplier_Order_Number,
584: p_po_change_requests(i).Old_Supplier_Order_Line_Number,
585: p_po_change_requests(i).New_Supplier_Order_Line_Number,
578: p_po_change_requests(i).new_supplier_reference_number,
579: p_po_change_requests(i).Approval_Required_Flag,
580: p_po_change_requests(i).Parent_Change_request_Id,
581: p_po_change_requests(i).Requester_id,
582: p_po_change_requests(i).Old_Supplier_Order_Number,
583: p_po_change_requests(i).New_Supplier_Order_Number,
584: p_po_change_requests(i).Old_Supplier_Order_Line_Number,
585: p_po_change_requests(i).New_Supplier_Order_Line_Number,
586: decode(p_po_change_requests(i).request_status,'ACCEPTED','N','Y'),
579: p_po_change_requests(i).Approval_Required_Flag,
580: p_po_change_requests(i).Parent_Change_request_Id,
581: p_po_change_requests(i).Requester_id,
582: p_po_change_requests(i).Old_Supplier_Order_Number,
583: p_po_change_requests(i).New_Supplier_Order_Number,
584: p_po_change_requests(i).Old_Supplier_Order_Line_Number,
585: p_po_change_requests(i).New_Supplier_Order_Line_Number,
586: decode(p_po_change_requests(i).request_status,'ACCEPTED','N','Y'),
587: p_chn_int_cont_num,
580: p_po_change_requests(i).Parent_Change_request_Id,
581: p_po_change_requests(i).Requester_id,
582: p_po_change_requests(i).Old_Supplier_Order_Number,
583: p_po_change_requests(i).New_Supplier_Order_Number,
584: p_po_change_requests(i).Old_Supplier_Order_Line_Number,
585: p_po_change_requests(i).New_Supplier_Order_Line_Number,
586: decode(p_po_change_requests(i).request_status,'ACCEPTED','N','Y'),
587: p_chn_int_cont_num,
588: p_chn_source,
581: p_po_change_requests(i).Requester_id,
582: p_po_change_requests(i).Old_Supplier_Order_Number,
583: p_po_change_requests(i).New_Supplier_Order_Number,
584: p_po_change_requests(i).Old_Supplier_Order_Line_Number,
585: p_po_change_requests(i).New_Supplier_Order_Line_Number,
586: decode(p_po_change_requests(i).request_status,'ACCEPTED','N','Y'),
587: p_chn_int_cont_num,
588: p_chn_source,
589: p_po_change_requests(i).Additional_changes,
582: p_po_change_requests(i).Old_Supplier_Order_Number,
583: p_po_change_requests(i).New_Supplier_Order_Number,
584: p_po_change_requests(i).Old_Supplier_Order_Line_Number,
585: p_po_change_requests(i).New_Supplier_Order_Line_Number,
586: decode(p_po_change_requests(i).request_status,'ACCEPTED','N','Y'),
587: p_chn_int_cont_num,
588: p_chn_source,
589: p_po_change_requests(i).Additional_changes,
590: p_po_change_requests(i).old_start_date,
585: p_po_change_requests(i).New_Supplier_Order_Line_Number,
586: decode(p_po_change_requests(i).request_status,'ACCEPTED','N','Y'),
587: p_chn_int_cont_num,
588: p_chn_source,
589: p_po_change_requests(i).Additional_changes,
590: p_po_change_requests(i).old_start_date,
591: p_po_change_requests(i).new_start_date,
592: p_po_change_requests(i).old_expiration_date,
593: p_po_change_requests(i).new_expiration_date,
586: decode(p_po_change_requests(i).request_status,'ACCEPTED','N','Y'),
587: p_chn_int_cont_num,
588: p_chn_source,
589: p_po_change_requests(i).Additional_changes,
590: p_po_change_requests(i).old_start_date,
591: p_po_change_requests(i).new_start_date,
592: p_po_change_requests(i).old_expiration_date,
593: p_po_change_requests(i).new_expiration_date,
594: p_po_change_requests(i).old_amount,
587: p_chn_int_cont_num,
588: p_chn_source,
589: p_po_change_requests(i).Additional_changes,
590: p_po_change_requests(i).old_start_date,
591: p_po_change_requests(i).new_start_date,
592: p_po_change_requests(i).old_expiration_date,
593: p_po_change_requests(i).new_expiration_date,
594: p_po_change_requests(i).old_amount,
595: p_po_change_requests(i).new_amount,
588: p_chn_source,
589: p_po_change_requests(i).Additional_changes,
590: p_po_change_requests(i).old_start_date,
591: p_po_change_requests(i).new_start_date,
592: p_po_change_requests(i).old_expiration_date,
593: p_po_change_requests(i).new_expiration_date,
594: p_po_change_requests(i).old_amount,
595: p_po_change_requests(i).new_amount,
596: p_po_change_requests(i).SUPPLIER_DOC_REF,
589: p_po_change_requests(i).Additional_changes,
590: p_po_change_requests(i).old_start_date,
591: p_po_change_requests(i).new_start_date,
592: p_po_change_requests(i).old_expiration_date,
593: p_po_change_requests(i).new_expiration_date,
594: p_po_change_requests(i).old_amount,
595: p_po_change_requests(i).new_amount,
596: p_po_change_requests(i).SUPPLIER_DOC_REF,
597: p_po_change_requests(i).SUPPLIER_LINE_REF,
590: p_po_change_requests(i).old_start_date,
591: p_po_change_requests(i).new_start_date,
592: p_po_change_requests(i).old_expiration_date,
593: p_po_change_requests(i).new_expiration_date,
594: p_po_change_requests(i).old_amount,
595: p_po_change_requests(i).new_amount,
596: p_po_change_requests(i).SUPPLIER_DOC_REF,
597: p_po_change_requests(i).SUPPLIER_LINE_REF,
598: p_po_change_requests(i).SUPPLIER_SHIPMENT_REF,
591: p_po_change_requests(i).new_start_date,
592: p_po_change_requests(i).old_expiration_date,
593: p_po_change_requests(i).new_expiration_date,
594: p_po_change_requests(i).old_amount,
595: p_po_change_requests(i).new_amount,
596: p_po_change_requests(i).SUPPLIER_DOC_REF,
597: p_po_change_requests(i).SUPPLIER_LINE_REF,
598: p_po_change_requests(i).SUPPLIER_SHIPMENT_REF,
599: p_po_change_requests(i).NEW_PROGRESS_TYPE, --<< Complex work changes for R12 >>
592: p_po_change_requests(i).old_expiration_date,
593: p_po_change_requests(i).new_expiration_date,
594: p_po_change_requests(i).old_amount,
595: p_po_change_requests(i).new_amount,
596: p_po_change_requests(i).SUPPLIER_DOC_REF,
597: p_po_change_requests(i).SUPPLIER_LINE_REF,
598: p_po_change_requests(i).SUPPLIER_SHIPMENT_REF,
599: p_po_change_requests(i).NEW_PROGRESS_TYPE, --<< Complex work changes for R12 >>
600: p_po_change_requests(i).NEW_PAY_DESCRIPTION
593: p_po_change_requests(i).new_expiration_date,
594: p_po_change_requests(i).old_amount,
595: p_po_change_requests(i).new_amount,
596: p_po_change_requests(i).SUPPLIER_DOC_REF,
597: p_po_change_requests(i).SUPPLIER_LINE_REF,
598: p_po_change_requests(i).SUPPLIER_SHIPMENT_REF,
599: p_po_change_requests(i).NEW_PROGRESS_TYPE, --<< Complex work changes for R12 >>
600: p_po_change_requests(i).NEW_PAY_DESCRIPTION
601:
594: p_po_change_requests(i).old_amount,
595: p_po_change_requests(i).new_amount,
596: p_po_change_requests(i).SUPPLIER_DOC_REF,
597: p_po_change_requests(i).SUPPLIER_LINE_REF,
598: p_po_change_requests(i).SUPPLIER_SHIPMENT_REF,
599: p_po_change_requests(i).NEW_PROGRESS_TYPE, --<< Complex work changes for R12 >>
600: p_po_change_requests(i).NEW_PAY_DESCRIPTION
601:
602: );
595: p_po_change_requests(i).new_amount,
596: p_po_change_requests(i).SUPPLIER_DOC_REF,
597: p_po_change_requests(i).SUPPLIER_LINE_REF,
598: p_po_change_requests(i).SUPPLIER_SHIPMENT_REF,
599: p_po_change_requests(i).NEW_PROGRESS_TYPE, --<< Complex work changes for R12 >>
600: p_po_change_requests(i).NEW_PAY_DESCRIPTION
601:
602: );
603: end if;
596: p_po_change_requests(i).SUPPLIER_DOC_REF,
597: p_po_change_requests(i).SUPPLIER_LINE_REF,
598: p_po_change_requests(i).SUPPLIER_SHIPMENT_REF,
599: p_po_change_requests(i).NEW_PROGRESS_TYPE, --<< Complex work changes for R12 >>
600: p_po_change_requests(i).NEW_PAY_DESCRIPTION
601:
602: );
603: end if;
604: end loop;
638: procedure process_supplier_request (
639: p_po_header_id IN number,
640: p_po_release_id IN number,
641: p_revision_num IN number,
642: p_po_change_requests IN pos_chg_rec_tbl,
643: x_online_report_id OUT NOCOPY number,
644: x_pos_errors OUT NOCOPY POS_ERR_TYPE,
645: p_chn_int_cont_num IN varchar2 default null,
646: p_chn_source IN varchar2 default null,
666: x_return_status varchar2(20);
667: updatePoAttr boolean := false;
668: saveRequest boolean := false;
669: callWf boolean := false;
670: l_po_change_requests pos_chg_rec_tbl := NULL;
671: vAckTbl pos_ack_rec_tbl := pos_ack_rec_tbl();
672: ack_cnt number :=0;
673: callDocCheck boolean :=false;
674: accp_flag char(1);
775: x_pos_errors.text_line(1) := fnd_message.get_string('POS', 'POS_MODIFIED_PO_ROW');
776: return;
777: end if;
778: -- Copy the request into a local var
779: l_po_change_requests := p_po_change_requests;
780:
781: IF g_fnd_debug = 'Y' THEN
782: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
783: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, g_module_prefix || l_api_name ||
788: END IF;
789:
790: validate_shipment_cancel (
791: p_po_header_id,
792: p_po_change_requests,
793: x_pos_errors,
794: l_ret_sts);
795: if(l_ret_sts = 'Y') then
796: return;
795: if(l_ret_sts = 'Y') then
796: return;
797: end if;
798:
799: if ( l_po_change_requests(1).action_type in ('CANCELLATION') AND
800: l_po_change_requests(1).request_level='HEADER' ) then
801: if (l_po_change_requests.count > 1 ) then
802: x_return_status := FND_API.g_ret_sts_unexp_error;
803:
796: return;
797: end if;
798:
799: if ( l_po_change_requests(1).action_type in ('CANCELLATION') AND
800: l_po_change_requests(1).request_level='HEADER' ) then
801: if (l_po_change_requests.count > 1 ) then
802: x_return_status := FND_API.g_ret_sts_unexp_error;
803:
804: FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
797: end if;
798:
799: if ( l_po_change_requests(1).action_type in ('CANCELLATION') AND
800: l_po_change_requests(1).request_level='HEADER' ) then
801: if (l_po_change_requests.count > 1 ) then
802: x_return_status := FND_API.g_ret_sts_unexp_error;
803:
804: FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
805: IF g_fnd_debug = 'Y' THEN
834: x_return_status => l_return_status,
835: p_po_header_id => p_po_header_id,
836: p_po_release_id => p_po_release_id,
837: p_revision_num => p_revision_num,
838: p_po_change_requests => l_po_change_requests,
839: x_request_group_id => l_request_group_id
840: );
841: x_online_report_id := 0;
842: if (l_return_status <> FND_API.g_ret_sts_success) then
874: from po_releases_all
875: where po_release_id = p_po_release_id;
876: end if;
877:
878: FOR i in 1..l_po_change_requests.count()
879: LOOP
880: if (l_po_change_requests(i).action_type in ('ACCEPT','REJECT') AND
881: l_po_change_requests(i).request_level='SHIPMENT' ) then
882: callWf := true;
876: end if;
877:
878: FOR i in 1..l_po_change_requests.count()
879: LOOP
880: if (l_po_change_requests(i).action_type in ('ACCEPT','REJECT') AND
881: l_po_change_requests(i).request_level='SHIPMENT' ) then
882: callWf := true;
883: --updatePoAttr := false;
884: --callDocCheck := false;
877:
878: FOR i in 1..l_po_change_requests.count()
879: LOOP
880: if (l_po_change_requests(i).action_type in ('ACCEPT','REJECT') AND
881: l_po_change_requests(i).request_level='SHIPMENT' ) then
882: callWf := true;
883: --updatePoAttr := false;
884: --callDocCheck := false;
885:
882: callWf := true;
883: --updatePoAttr := false;
884: --callDocCheck := false;
885:
886: if (l_po_change_requests(i).action_type = 'ACCEPT') then
887: accp_flag := 'Y';
888: else
889: accp_flag := 'N';
890: end if;
892: -- Process The Acknowledgements
893:
894: PO_ACKNOWLEDGE_PO_GRP.Acknowledge_shipment(
895: 1.0,FND_API.G_FALSE,x_return_status,
896: l_po_change_requests(i).document_line_location_id,
897: l_po_change_requests(i).document_header_id,
898: l_po_change_requests(i).po_release_id,
899: l_po_change_requests(i).document_revision_num,
900: accp_flag,
893:
894: PO_ACKNOWLEDGE_PO_GRP.Acknowledge_shipment(
895: 1.0,FND_API.G_FALSE,x_return_status,
896: l_po_change_requests(i).document_line_location_id,
897: l_po_change_requests(i).document_header_id,
898: l_po_change_requests(i).po_release_id,
899: l_po_change_requests(i).document_revision_num,
900: accp_flag,
901: l_po_change_requests(i).request_reason,
894: PO_ACKNOWLEDGE_PO_GRP.Acknowledge_shipment(
895: 1.0,FND_API.G_FALSE,x_return_status,
896: l_po_change_requests(i).document_line_location_id,
897: l_po_change_requests(i).document_header_id,
898: l_po_change_requests(i).po_release_id,
899: l_po_change_requests(i).document_revision_num,
900: accp_flag,
901: l_po_change_requests(i).request_reason,
902: v_buyer_id, fnd_global.user_id);
895: 1.0,FND_API.G_FALSE,x_return_status,
896: l_po_change_requests(i).document_line_location_id,
897: l_po_change_requests(i).document_header_id,
898: l_po_change_requests(i).po_release_id,
899: l_po_change_requests(i).document_revision_num,
900: accp_flag,
901: l_po_change_requests(i).request_reason,
902: v_buyer_id, fnd_global.user_id);
903:
897: l_po_change_requests(i).document_header_id,
898: l_po_change_requests(i).po_release_id,
899: l_po_change_requests(i).document_revision_num,
900: accp_flag,
901: l_po_change_requests(i).request_reason,
902: v_buyer_id, fnd_global.user_id);
903:
904: elsif (l_po_change_requests(i).request_level='LINE' AND
905: l_po_change_requests(i).action_type='MODIFICATION') then
900: accp_flag,
901: l_po_change_requests(i).request_reason,
902: v_buyer_id, fnd_global.user_id);
903:
904: elsif (l_po_change_requests(i).request_level='LINE' AND
905: l_po_change_requests(i).action_type='MODIFICATION') then
906: callWf := true;
907: callDocCheck := true;
908: updatePoAttr := true;
901: l_po_change_requests(i).request_reason,
902: v_buyer_id, fnd_global.user_id);
903:
904: elsif (l_po_change_requests(i).request_level='LINE' AND
905: l_po_change_requests(i).action_type='MODIFICATION') then
906: callWf := true;
907: callDocCheck := true;
908: updatePoAttr := true;
909: saveRequest := true;
906: callWf := true;
907: callDocCheck := true;
908: updatePoAttr := true;
909: saveRequest := true;
910: elsif (l_po_change_requests(i).request_level='SHIPMENT' AND
911: l_po_change_requests(i).action_type='CANCELLATION') then
912: -- Do not call doc sub check for shipment cancellation
913: saveRequest := true;
914: callWf := true;
907: callDocCheck := true;
908: updatePoAttr := true;
909: saveRequest := true;
910: elsif (l_po_change_requests(i).request_level='SHIPMENT' AND
911: l_po_change_requests(i).action_type='CANCELLATION') then
912: -- Do not call doc sub check for shipment cancellation
913: saveRequest := true;
914: callWf := true;
915: --callDocCheck := false;
914: callWf := true;
915: --callDocCheck := false;
916: updatePoAttr := true;
917:
918: elsif (l_po_change_requests(i).request_level='SHIPMENT' AND
919: l_po_change_requests(i).action_type='MODIFICATION') then
920: -- If quantity,promised_date,price,Amount have not changed in the shipment level do not update po
921: /*Bug 7112734 - Start
922: During PO change process if Supplier Order Line number alone is changed then it will
915: --callDocCheck := false;
916: updatePoAttr := true;
917:
918: elsif (l_po_change_requests(i).request_level='SHIPMENT' AND
919: l_po_change_requests(i).action_type='MODIFICATION') then
920: -- If quantity,promised_date,price,Amount have not changed in the shipment level do not update po
921: /*Bug 7112734 - Start
922: During PO change process if Supplier Order Line number alone is changed then it will
923: be treated as acceptance of the PO.
921: /*Bug 7112734 - Start
922: During PO change process if Supplier Order Line number alone is changed then it will
923: be treated as acceptance of the PO.
924: */
925: if (l_po_change_requests(i).new_quantity is null AND
926: l_po_change_requests(i).new_promised_date is null AND
927: l_po_change_requests(i).new_price is null AND
928: l_po_change_requests(i).new_amount is null) then -- FPS Enhancement
929: --callDocCheck := false;
922: During PO change process if Supplier Order Line number alone is changed then it will
923: be treated as acceptance of the PO.
924: */
925: if (l_po_change_requests(i).new_quantity is null AND
926: l_po_change_requests(i).new_promised_date is null AND
927: l_po_change_requests(i).new_price is null AND
928: l_po_change_requests(i).new_amount is null) then -- FPS Enhancement
929: --callDocCheck := false;
930: --updatePoAttr := false;
923: be treated as acceptance of the PO.
924: */
925: if (l_po_change_requests(i).new_quantity is null AND
926: l_po_change_requests(i).new_promised_date is null AND
927: l_po_change_requests(i).new_price is null AND
928: l_po_change_requests(i).new_amount is null) then -- FPS Enhancement
929: --callDocCheck := false;
930: --updatePoAttr := false;
931: l_po_change_requests(i).request_status := 'ACCEPTED';
924: */
925: if (l_po_change_requests(i).new_quantity is null AND
926: l_po_change_requests(i).new_promised_date is null AND
927: l_po_change_requests(i).new_price is null AND
928: l_po_change_requests(i).new_amount is null) then -- FPS Enhancement
929: --callDocCheck := false;
930: --updatePoAttr := false;
931: l_po_change_requests(i).request_status := 'ACCEPTED';
932:
927: l_po_change_requests(i).new_price is null AND
928: l_po_change_requests(i).new_amount is null) then -- FPS Enhancement
929: --callDocCheck := false;
930: --updatePoAttr := false;
931: l_po_change_requests(i).request_status := 'ACCEPTED';
932:
933: -- If PO requires acknowledgement, post shipment-level acceptance.
934: IF (x_accp_flag = 'Y') THEN
935:
936: callWf := true;
937: -- Process The Acknowledgements
938: PO_ACKNOWLEDGE_PO_GRP.Acknowledge_shipment(
939: 1.0,FND_API.G_FALSE,x_return_status,
940: l_po_change_requests(i).document_line_location_id,
941: l_po_change_requests(i).document_header_id,
942: l_po_change_requests(i).po_release_id,
943: l_po_change_requests(i).document_revision_num,
944: 'Y',
937: -- Process The Acknowledgements
938: PO_ACKNOWLEDGE_PO_GRP.Acknowledge_shipment(
939: 1.0,FND_API.G_FALSE,x_return_status,
940: l_po_change_requests(i).document_line_location_id,
941: l_po_change_requests(i).document_header_id,
942: l_po_change_requests(i).po_release_id,
943: l_po_change_requests(i).document_revision_num,
944: 'Y',
945: l_po_change_requests(i).request_reason,
938: PO_ACKNOWLEDGE_PO_GRP.Acknowledge_shipment(
939: 1.0,FND_API.G_FALSE,x_return_status,
940: l_po_change_requests(i).document_line_location_id,
941: l_po_change_requests(i).document_header_id,
942: l_po_change_requests(i).po_release_id,
943: l_po_change_requests(i).document_revision_num,
944: 'Y',
945: l_po_change_requests(i).request_reason,
946: v_buyer_id, fnd_global.user_id);
939: 1.0,FND_API.G_FALSE,x_return_status,
940: l_po_change_requests(i).document_line_location_id,
941: l_po_change_requests(i).document_header_id,
942: l_po_change_requests(i).po_release_id,
943: l_po_change_requests(i).document_revision_num,
944: 'Y',
945: l_po_change_requests(i).request_reason,
946: v_buyer_id, fnd_global.user_id);
947:
941: l_po_change_requests(i).document_header_id,
942: l_po_change_requests(i).po_release_id,
943: l_po_change_requests(i).document_revision_num,
944: 'Y',
945: l_po_change_requests(i).request_reason,
946: v_buyer_id, fnd_global.user_id);
947:
948: END IF;
949:
955: saveRequest := true;
956: -- Bug 7112734 - End
957: end if; -- if only so has changed
958:
959: if (l_po_change_requests(i).New_Supplier_Order_Line_Number is not null) then
960: begin
961: update po_line_locations_all
962: set supplier_order_line_number = l_po_change_requests(i).New_Supplier_Order_Line_Number
963: where line_location_id = l_po_change_requests(i).document_line_location_id;
958:
959: if (l_po_change_requests(i).New_Supplier_Order_Line_Number is not null) then
960: begin
961: update po_line_locations_all
962: set supplier_order_line_number = l_po_change_requests(i).New_Supplier_Order_Line_Number
963: where line_location_id = l_po_change_requests(i).document_line_location_id;
964: exception
965: WHEN OTHERS THEN
966: x_return_status := FND_API.g_ret_sts_unexp_error;
959: if (l_po_change_requests(i).New_Supplier_Order_Line_Number is not null) then
960: begin
961: update po_line_locations_all
962: set supplier_order_line_number = l_po_change_requests(i).New_Supplier_Order_Line_Number
963: where line_location_id = l_po_change_requests(i).document_line_location_id;
964: exception
965: WHEN OTHERS THEN
966: x_return_status := FND_API.g_ret_sts_unexp_error;
967: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
973: END IF;
974: END IF;
975: END IF;
976: end; end if;
977: elsif (l_po_change_requests(i).request_level='HEADER'
978: AND l_po_change_requests(i).action_type='MODIFICATION') then
979: saveRequest := true;
980:
981: if (nvl(l_po_change_requests(i).New_Supplier_Order_Number,-1) <> nvl(l_po_change_requests(i).Old_Supplier_Order_Number,-1)) then
974: END IF;
975: END IF;
976: end; end if;
977: elsif (l_po_change_requests(i).request_level='HEADER'
978: AND l_po_change_requests(i).action_type='MODIFICATION') then
979: saveRequest := true;
980:
981: if (nvl(l_po_change_requests(i).New_Supplier_Order_Number,-1) <> nvl(l_po_change_requests(i).Old_Supplier_Order_Number,-1)) then
982:
977: elsif (l_po_change_requests(i).request_level='HEADER'
978: AND l_po_change_requests(i).action_type='MODIFICATION') then
979: saveRequest := true;
980:
981: if (nvl(l_po_change_requests(i).New_Supplier_Order_Number,-1) <> nvl(l_po_change_requests(i).Old_Supplier_Order_Number,-1)) then
982:
983: if (p_po_release_id is null ) then
984: -- Update the vendor_order_num for PO Headers no need of approval.
985: update po_headers_all
982:
983: if (p_po_release_id is null ) then
984: -- Update the vendor_order_num for PO Headers no need of approval.
985: update po_headers_all
986: set vendor_order_num = l_po_change_requests(i).New_Supplier_Order_Number
987: where po_header_id = p_po_header_id;
988: else
989: -- Update the vendor_order_num for PO Releases no need of approval.
990: update po_releases_all
987: where po_header_id = p_po_header_id;
988: else
989: -- Update the vendor_order_num for PO Releases no need of approval.
990: update po_releases_all
991: set vendor_order_num = l_po_change_requests(i).New_Supplier_Order_Number
992: where po_release_id = p_po_release_id;
993: end if;
994: end if;
995:
993: end if;
994: end if;
995:
996: -- Set startWf to false
997: if (l_po_change_requests(i).Additional_changes is not null) then
998: callWf := true;
999: updatePoAttr := true;
1000: end if;
1001: --callDocCheck := false;
1001: --callDocCheck := false;
1002:
1003: end if; -- end if accept reject
1004: END LOOP;
1005: if ((callDocCheck) AND l_po_change_requests.count() > 0 ) then
1006: IF g_fnd_debug = 'Y' THEN
1007: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1008: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, g_module_prefix || l_api_name ||
1009: '.invoked', ' Change Count : ' || TO_CHAR(l_po_change_requests.count()));
1005: if ((callDocCheck) AND l_po_change_requests.count() > 0 ) then
1006: IF g_fnd_debug = 'Y' THEN
1007: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1008: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, g_module_prefix || l_api_name ||
1009: '.invoked', ' Change Count : ' || TO_CHAR(l_po_change_requests.count()));
1010: END IF;
1011: END IF;
1012: validate_change_request (
1013: p_api_version => 1.0,
1016: x_msg_data => l_msg_data,
1017: p_po_header_id => p_po_header_id,
1018: p_po_release_id => p_po_release_id,
1019: p_revision_num => p_revision_num,
1020: p_po_change_requests => l_po_change_requests,
1021: x_online_report_id => x_online_report_id,
1022: x_pos_errors => x_pos_errors);
1023:
1024: end if;
1022: x_pos_errors => x_pos_errors);
1023:
1024: end if;
1025:
1026: if (l_po_change_requests.count() > 0 AND x_return_status = FND_API.G_RET_STS_SUCCESS) then
1027: IF g_fnd_debug = 'Y' THEN
1028: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1029: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, g_module_prefix || l_api_name ||
1030: '.invoked', 'Type: ' ||
1027: IF g_fnd_debug = 'Y' THEN
1028: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1029: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, g_module_prefix || l_api_name ||
1030: '.invoked', 'Type: ' ||
1031: ', Save Count : ' || TO_CHAR(l_po_change_requests.count()));
1032: END IF;
1033: END IF;
1034:
1035:
1043: x_return_status => x_return_status,
1044: p_po_header_id => p_po_header_id,
1045: p_po_release_id => p_po_release_id,
1046: p_revision_num => p_revision_num,
1047: p_po_change_requests => l_po_change_requests,
1048: x_request_group_id => l_request_group_id,
1049: p_chn_int_cont_num => p_chn_int_cont_num,
1050: p_chn_source => p_chn_source,
1051: p_chn_requestor_username => p_chn_requestor_username,
1234:
1235: update po_line_locations_all
1236: set approved_flag = 'R'
1237: where line_location_id in (select document_line_location_id
1238: from po_change_requests
1239: where request_level = 'SHIPMENT' and
1240: document_header_id = p_po_header_id and
1241: action_type in ('MODIFICATION','CANCELLATION') and
1242: initiator = 'SUPPLIER' and
1252:
1253: update po_line_locations_all
1254: set approved_flag = 'R'
1255: where po_header_id in (select document_header_id
1256: from po_change_requests
1257: where request_level = 'HEADER' and
1258: document_header_id = p_po_header_id and
1259: action_type ='CANCELLATION' and
1260: initiator = 'SUPPLIER' and
1263:
1264: update po_line_locations_all
1265: set approved_flag = 'R'
1266: where po_line_id in (select document_line_id
1267: from po_change_requests
1268: where request_level = 'LINE' and
1269: document_header_id = p_po_header_id and
1270: request_status = 'PENDING' and
1271: initiator = 'SUPPLIER' and
1274: approved_flag='Y' ;
1275: /* jai
1276: and
1277: line_location_id not in (select document_line_location_id
1278: from po_change_requests
1279: where request_level = 'SHIPMENT' and
1280: document_header_id = p_po_header_id and
1281: action_type = 'CANCELLATION' and
1282: initiator = 'SUPPLIER' and
1301: -- changed to prevent receiving
1302: update po_line_locations_all
1303: set approved_flag = 'R'
1304: where line_location_id in (select document_line_location_id
1305: from po_change_requests
1306: where request_level = 'SHIPMENT' and
1307: po_release_id = p_po_release_id and
1308: action_type in ('MODIFICATION','CANCELLATION') and
1309: initiator = 'SUPPLIER' and
1313: --New DBI request
1314: update po_line_locations_all
1315: set approved_flag = 'R'
1316: where po_release_id in (select po_release_id
1317: from po_change_requests
1318: where request_level = 'HEADER' and
1319: po_release_id = p_po_release_id and
1320: action_type ='CANCELLATION' and
1321: initiator = 'SUPPLIER' and
1356: x_msg_data OUT NOCOPY VARCHAR2,
1357: p_po_header_id IN number,
1358: p_po_release_id IN number,
1359: p_revision_num IN number,
1360: p_po_change_requests IN OUT NOCOPY pos_chg_rec_tbl,
1361: x_online_report_id OUT NOCOPY number,
1362: x_pos_errors OUT NOCOPY pos_err_type,
1363: x_doc_check_error_msg OUT NOCOPY Doc_Check_Return_Type) is
1364:
1432: lLine number;
1433: changeOrig varchar2(1) := 'F';
1434: l_price_break_type VARCHAR2(1) := NULL;
1435: l_cumulative_flag BOOLEAN := false;
1436: l_initiator po_change_requests.initiator%type :='SUPPLIER';
1437: --<< Complex work changes for R12 >>
1438: progress_type_tbl PO_TBL_VARCHAR30:= PO_TBL_VARCHAR30();
1439: pay_description_tbl PO_TBL_VARCHAR240:= PO_TBL_VARCHAR240();
1440:
1501:
1502: -- Set the org context before calling core po api's
1503: PO_MOAC_UTILS_PVT.set_org_context(x_org_id) ; --
1504:
1505: rec_cnt := p_po_change_requests.count();
1506: -- Now check if the document references a global agreement
1507: -- or a blanket, then get the price accordingly
1508: if (rec_cnt > 0) then
1509: l_initiator:=p_po_change_requests(1).initiator;
1505: rec_cnt := p_po_change_requests.count();
1506: -- Now check if the document references a global agreement
1507: -- or a blanket, then get the price accordingly
1508: if (rec_cnt > 0) then
1509: l_initiator:=p_po_change_requests(1).initiator;
1510: end if;
1511: if (rec_cnt > 0) then
1512: FOR i in 1..rec_cnt
1513: LOOP
1511: if (rec_cnt > 0) then
1512: FOR i in 1..rec_cnt
1513: LOOP
1514: --Construct the table of line record
1515: if(p_po_change_requests(i).request_level = 'LINE') then
1516: poLineIdTbl.extend; unitPriceTbl.extend; supItemTbl.extend;
1517: startdateTbl.extend;expirationdateTbl.extend;amountTbl.extend;
1518:
1519: line_cnt := line_cnt + 1;
1516: poLineIdTbl.extend; unitPriceTbl.extend; supItemTbl.extend;
1517: startdateTbl.extend;expirationdateTbl.extend;amountTbl.extend;
1518:
1519: line_cnt := line_cnt + 1;
1520: poLineIdTbl(line_cnt) := p_po_change_requests(i).document_line_id;
1521: unitPriceTbl(line_cnt) := p_po_change_requests(i).new_price;
1522: supItemTbl(line_cnt) := p_po_change_requests(i).new_supplier_part_number;
1523: startdateTbl(line_cnt) := p_po_change_requests(i).new_start_date;
1524: expirationdateTbl(line_cnt) := p_po_change_requests(i).new_expiration_date;
1517: startdateTbl.extend;expirationdateTbl.extend;amountTbl.extend;
1518:
1519: line_cnt := line_cnt + 1;
1520: poLineIdTbl(line_cnt) := p_po_change_requests(i).document_line_id;
1521: unitPriceTbl(line_cnt) := p_po_change_requests(i).new_price;
1522: supItemTbl(line_cnt) := p_po_change_requests(i).new_supplier_part_number;
1523: startdateTbl(line_cnt) := p_po_change_requests(i).new_start_date;
1524: expirationdateTbl(line_cnt) := p_po_change_requests(i).new_expiration_date;
1525: amountTbl(line_cnt) := p_po_change_requests(i).new_amount;
1518:
1519: line_cnt := line_cnt + 1;
1520: poLineIdTbl(line_cnt) := p_po_change_requests(i).document_line_id;
1521: unitPriceTbl(line_cnt) := p_po_change_requests(i).new_price;
1522: supItemTbl(line_cnt) := p_po_change_requests(i).new_supplier_part_number;
1523: startdateTbl(line_cnt) := p_po_change_requests(i).new_start_date;
1524: expirationdateTbl(line_cnt) := p_po_change_requests(i).new_expiration_date;
1525: amountTbl(line_cnt) := p_po_change_requests(i).new_amount;
1526: end if; -- end if line
1519: line_cnt := line_cnt + 1;
1520: poLineIdTbl(line_cnt) := p_po_change_requests(i).document_line_id;
1521: unitPriceTbl(line_cnt) := p_po_change_requests(i).new_price;
1522: supItemTbl(line_cnt) := p_po_change_requests(i).new_supplier_part_number;
1523: startdateTbl(line_cnt) := p_po_change_requests(i).new_start_date;
1524: expirationdateTbl(line_cnt) := p_po_change_requests(i).new_expiration_date;
1525: amountTbl(line_cnt) := p_po_change_requests(i).new_amount;
1526: end if; -- end if line
1527:
1520: poLineIdTbl(line_cnt) := p_po_change_requests(i).document_line_id;
1521: unitPriceTbl(line_cnt) := p_po_change_requests(i).new_price;
1522: supItemTbl(line_cnt) := p_po_change_requests(i).new_supplier_part_number;
1523: startdateTbl(line_cnt) := p_po_change_requests(i).new_start_date;
1524: expirationdateTbl(line_cnt) := p_po_change_requests(i).new_expiration_date;
1525: amountTbl(line_cnt) := p_po_change_requests(i).new_amount;
1526: end if; -- end if line
1527:
1528: -- do not send cancellation request
1521: unitPriceTbl(line_cnt) := p_po_change_requests(i).new_price;
1522: supItemTbl(line_cnt) := p_po_change_requests(i).new_supplier_part_number;
1523: startdateTbl(line_cnt) := p_po_change_requests(i).new_start_date;
1524: expirationdateTbl(line_cnt) := p_po_change_requests(i).new_expiration_date;
1525: amountTbl(line_cnt) := p_po_change_requests(i).new_amount;
1526: end if; -- end if line
1527:
1528: -- do not send cancellation request
1529: if(p_po_change_requests(i).request_level = 'SHIPMENT' AND
1525: amountTbl(line_cnt) := p_po_change_requests(i).new_amount;
1526: end if; -- end if line
1527:
1528: -- do not send cancellation request
1529: if(p_po_change_requests(i).request_level = 'SHIPMENT' AND
1530: (p_po_change_requests(i).action_type not in ('ACCEPT','REJECT','CANCEL'))) then
1531: ship_cnt := ship_cnt + 1;
1532: poLineLocIdTbl.extend; quantityTbl.extend;
1533: promisedDateTbl.extend;priceOverrideTbl.extend;
1526: end if; -- end if line
1527:
1528: -- do not send cancellation request
1529: if(p_po_change_requests(i).request_level = 'SHIPMENT' AND
1530: (p_po_change_requests(i).action_type not in ('ACCEPT','REJECT','CANCEL'))) then
1531: ship_cnt := ship_cnt + 1;
1532: poLineLocIdTbl.extend; quantityTbl.extend;
1533: promisedDateTbl.extend;priceOverrideTbl.extend;
1534: parentLineLocIdTbl.extend;
1538: pay_description_tbl.extend;
1539: -- if release / standard po referencing a GA/ Quotation
1540: -- Call Get Price Break API.
1541:
1542: --if ((p_po_change_requests(i).from_header_id is not null) or
1543: -- (p_po_change_requests(i).po_release_id is not null)) then
1544: if (p_po_change_requests(i).po_release_id is not null) then
1545:
1546: SELECT decode(price_break_lookup_code, 'CUMULATIVE', 'Y', 'N')
1539: -- if release / standard po referencing a GA/ Quotation
1540: -- Call Get Price Break API.
1541:
1542: --if ((p_po_change_requests(i).from_header_id is not null) or
1543: -- (p_po_change_requests(i).po_release_id is not null)) then
1544: if (p_po_change_requests(i).po_release_id is not null) then
1545:
1546: SELECT decode(price_break_lookup_code, 'CUMULATIVE', 'Y', 'N')
1547: INTO l_price_break_type
1540: -- Call Get Price Break API.
1541:
1542: --if ((p_po_change_requests(i).from_header_id is not null) or
1543: -- (p_po_change_requests(i).po_release_id is not null)) then
1544: if (p_po_change_requests(i).po_release_id is not null) then
1545:
1546: SELECT decode(price_break_lookup_code, 'CUMULATIVE', 'Y', 'N')
1547: INTO l_price_break_type
1548: FROM po_lines_all
1545:
1546: SELECT decode(price_break_lookup_code, 'CUMULATIVE', 'Y', 'N')
1547: INTO l_price_break_type
1548: FROM po_lines_all
1549: WHERE po_line_id = p_po_change_requests(i).document_line_id;
1550:
1551: IF (l_price_break_type = 'Y') THEN
1552: l_cumulative_flag := TRUE;
1553: ELSE
1553: ELSE
1554: l_cumulative_flag := FALSE;
1555: END IF;
1556:
1557: if(p_po_change_requests(i).new_price is null) then
1558: x_price := po_sourcing2_sv.get_break_price(
1559: nvl(p_po_change_requests(i).new_quantity,p_po_change_requests(i).old_quantity),
1560: p_po_change_requests(i).ship_to_organization_id,
1561: p_po_change_requests(i).ship_to_location_id,
1555: END IF;
1556:
1557: if(p_po_change_requests(i).new_price is null) then
1558: x_price := po_sourcing2_sv.get_break_price(
1559: nvl(p_po_change_requests(i).new_quantity,p_po_change_requests(i).old_quantity),
1560: p_po_change_requests(i).ship_to_organization_id,
1561: p_po_change_requests(i).ship_to_location_id,
1562: p_po_change_requests(i).document_line_id,
1563: l_cumulative_flag,
1556:
1557: if(p_po_change_requests(i).new_price is null) then
1558: x_price := po_sourcing2_sv.get_break_price(
1559: nvl(p_po_change_requests(i).new_quantity,p_po_change_requests(i).old_quantity),
1560: p_po_change_requests(i).ship_to_organization_id,
1561: p_po_change_requests(i).ship_to_location_id,
1562: p_po_change_requests(i).document_line_id,
1563: l_cumulative_flag,
1564: nvl(p_po_change_requests(i).new_need_by_date,p_po_change_requests(i).old_need_by_date), -- need_by_date
1557: if(p_po_change_requests(i).new_price is null) then
1558: x_price := po_sourcing2_sv.get_break_price(
1559: nvl(p_po_change_requests(i).new_quantity,p_po_change_requests(i).old_quantity),
1560: p_po_change_requests(i).ship_to_organization_id,
1561: p_po_change_requests(i).ship_to_location_id,
1562: p_po_change_requests(i).document_line_id,
1563: l_cumulative_flag,
1564: nvl(p_po_change_requests(i).new_need_by_date,p_po_change_requests(i).old_need_by_date), -- need_by_date
1565: p_po_change_requests(i).document_line_location_id);
1558: x_price := po_sourcing2_sv.get_break_price(
1559: nvl(p_po_change_requests(i).new_quantity,p_po_change_requests(i).old_quantity),
1560: p_po_change_requests(i).ship_to_organization_id,
1561: p_po_change_requests(i).ship_to_location_id,
1562: p_po_change_requests(i).document_line_id,
1563: l_cumulative_flag,
1564: nvl(p_po_change_requests(i).new_need_by_date,p_po_change_requests(i).old_need_by_date), -- need_by_date
1565: p_po_change_requests(i).document_line_location_id);
1566: p_po_change_requests(i).old_price := x_price;
1560: p_po_change_requests(i).ship_to_organization_id,
1561: p_po_change_requests(i).ship_to_location_id,
1562: p_po_change_requests(i).document_line_id,
1563: l_cumulative_flag,
1564: nvl(p_po_change_requests(i).new_need_by_date,p_po_change_requests(i).old_need_by_date), -- need_by_date
1565: p_po_change_requests(i).document_line_location_id);
1566: p_po_change_requests(i).old_price := x_price;
1567: end if;
1568: end if; -- end if release
1561: p_po_change_requests(i).ship_to_location_id,
1562: p_po_change_requests(i).document_line_id,
1563: l_cumulative_flag,
1564: nvl(p_po_change_requests(i).new_need_by_date,p_po_change_requests(i).old_need_by_date), -- need_by_date
1565: p_po_change_requests(i).document_line_location_id);
1566: p_po_change_requests(i).old_price := x_price;
1567: end if;
1568: end if; -- end if release
1569:
1562: p_po_change_requests(i).document_line_id,
1563: l_cumulative_flag,
1564: nvl(p_po_change_requests(i).new_need_by_date,p_po_change_requests(i).old_need_by_date), -- need_by_date
1565: p_po_change_requests(i).document_line_location_id);
1566: p_po_change_requests(i).old_price := x_price;
1567: end if;
1568: end if; -- end if release
1569:
1570: poLineLocIdTbl(ship_cnt) := p_po_change_requests(i).document_line_location_id;
1566: p_po_change_requests(i).old_price := x_price;
1567: end if;
1568: end if; -- end if release
1569:
1570: poLineLocIdTbl(ship_cnt) := p_po_change_requests(i).document_line_location_id;
1571: parentLineLocIdTbl(ship_cnt) := p_po_change_requests(i).parent_line_location_id;
1572: quantityTbl(ship_cnt) := p_po_change_requests(i).new_quantity;
1573: promisedDateTbl(ship_cnt) := p_po_change_requests(i).new_promised_date;
1574: priceOverrideTbl(ship_cnt) := nvl(p_po_change_requests(i).new_price,x_price);
1567: end if;
1568: end if; -- end if release
1569:
1570: poLineLocIdTbl(ship_cnt) := p_po_change_requests(i).document_line_location_id;
1571: parentLineLocIdTbl(ship_cnt) := p_po_change_requests(i).parent_line_location_id;
1572: quantityTbl(ship_cnt) := p_po_change_requests(i).new_quantity;
1573: promisedDateTbl(ship_cnt) := p_po_change_requests(i).new_promised_date;
1574: priceOverrideTbl(ship_cnt) := nvl(p_po_change_requests(i).new_price,x_price);
1575: shipmentNumTbl(ship_cnt) := p_po_change_requests(i).document_shipment_number;
1568: end if; -- end if release
1569:
1570: poLineLocIdTbl(ship_cnt) := p_po_change_requests(i).document_line_location_id;
1571: parentLineLocIdTbl(ship_cnt) := p_po_change_requests(i).parent_line_location_id;
1572: quantityTbl(ship_cnt) := p_po_change_requests(i).new_quantity;
1573: promisedDateTbl(ship_cnt) := p_po_change_requests(i).new_promised_date;
1574: priceOverrideTbl(ship_cnt) := nvl(p_po_change_requests(i).new_price,x_price);
1575: shipmentNumTbl(ship_cnt) := p_po_change_requests(i).document_shipment_number;
1576: progress_type_tbl(ship_cnt) := p_po_change_requests(i).new_progress_type;
1569:
1570: poLineLocIdTbl(ship_cnt) := p_po_change_requests(i).document_line_location_id;
1571: parentLineLocIdTbl(ship_cnt) := p_po_change_requests(i).parent_line_location_id;
1572: quantityTbl(ship_cnt) := p_po_change_requests(i).new_quantity;
1573: promisedDateTbl(ship_cnt) := p_po_change_requests(i).new_promised_date;
1574: priceOverrideTbl(ship_cnt) := nvl(p_po_change_requests(i).new_price,x_price);
1575: shipmentNumTbl(ship_cnt) := p_po_change_requests(i).document_shipment_number;
1576: progress_type_tbl(ship_cnt) := p_po_change_requests(i).new_progress_type;
1577: pay_description_tbl(ship_cnt):= p_po_change_requests(i).new_pay_description;
1570: poLineLocIdTbl(ship_cnt) := p_po_change_requests(i).document_line_location_id;
1571: parentLineLocIdTbl(ship_cnt) := p_po_change_requests(i).parent_line_location_id;
1572: quantityTbl(ship_cnt) := p_po_change_requests(i).new_quantity;
1573: promisedDateTbl(ship_cnt) := p_po_change_requests(i).new_promised_date;
1574: priceOverrideTbl(ship_cnt) := nvl(p_po_change_requests(i).new_price,x_price);
1575: shipmentNumTbl(ship_cnt) := p_po_change_requests(i).document_shipment_number;
1576: progress_type_tbl(ship_cnt) := p_po_change_requests(i).new_progress_type;
1577: pay_description_tbl(ship_cnt):= p_po_change_requests(i).new_pay_description;
1578: shipamountTbl(ship_cnt):= p_po_change_requests(i).new_amount;
1571: parentLineLocIdTbl(ship_cnt) := p_po_change_requests(i).parent_line_location_id;
1572: quantityTbl(ship_cnt) := p_po_change_requests(i).new_quantity;
1573: promisedDateTbl(ship_cnt) := p_po_change_requests(i).new_promised_date;
1574: priceOverrideTbl(ship_cnt) := nvl(p_po_change_requests(i).new_price,x_price);
1575: shipmentNumTbl(ship_cnt) := p_po_change_requests(i).document_shipment_number;
1576: progress_type_tbl(ship_cnt) := p_po_change_requests(i).new_progress_type;
1577: pay_description_tbl(ship_cnt):= p_po_change_requests(i).new_pay_description;
1578: shipamountTbl(ship_cnt):= p_po_change_requests(i).new_amount;
1579: end if; -- if shipment
1572: quantityTbl(ship_cnt) := p_po_change_requests(i).new_quantity;
1573: promisedDateTbl(ship_cnt) := p_po_change_requests(i).new_promised_date;
1574: priceOverrideTbl(ship_cnt) := nvl(p_po_change_requests(i).new_price,x_price);
1575: shipmentNumTbl(ship_cnt) := p_po_change_requests(i).document_shipment_number;
1576: progress_type_tbl(ship_cnt) := p_po_change_requests(i).new_progress_type;
1577: pay_description_tbl(ship_cnt):= p_po_change_requests(i).new_pay_description;
1578: shipamountTbl(ship_cnt):= p_po_change_requests(i).new_amount;
1579: end if; -- if shipment
1580:
1573: promisedDateTbl(ship_cnt) := p_po_change_requests(i).new_promised_date;
1574: priceOverrideTbl(ship_cnt) := nvl(p_po_change_requests(i).new_price,x_price);
1575: shipmentNumTbl(ship_cnt) := p_po_change_requests(i).document_shipment_number;
1576: progress_type_tbl(ship_cnt) := p_po_change_requests(i).new_progress_type;
1577: pay_description_tbl(ship_cnt):= p_po_change_requests(i).new_pay_description;
1578: shipamountTbl(ship_cnt):= p_po_change_requests(i).new_amount;
1579: end if; -- if shipment
1580:
1581: if (p_po_change_requests(i).request_level = 'DISTRIBUTION') then
1574: priceOverrideTbl(ship_cnt) := nvl(p_po_change_requests(i).new_price,x_price);
1575: shipmentNumTbl(ship_cnt) := p_po_change_requests(i).document_shipment_number;
1576: progress_type_tbl(ship_cnt) := p_po_change_requests(i).new_progress_type;
1577: pay_description_tbl(ship_cnt):= p_po_change_requests(i).new_pay_description;
1578: shipamountTbl(ship_cnt):= p_po_change_requests(i).new_amount;
1579: end if; -- if shipment
1580:
1581: if (p_po_change_requests(i).request_level = 'DISTRIBUTION') then
1582: dist_cnt := dist_cnt + 1;
1577: pay_description_tbl(ship_cnt):= p_po_change_requests(i).new_pay_description;
1578: shipamountTbl(ship_cnt):= p_po_change_requests(i).new_amount;
1579: end if; -- if shipment
1580:
1581: if (p_po_change_requests(i).request_level = 'DISTRIBUTION') then
1582: dist_cnt := dist_cnt + 1;
1583: distIdTbl.extend; distQtyTbl.extend; distAmtTbl.extend; -- FPS Changes
1584: distIdTbl(dist_cnt) := p_po_change_requests(i).document_distribution_id;
1585: distQtyTbl(dist_cnt) := p_po_change_requests(i).new_quantity;
1580:
1581: if (p_po_change_requests(i).request_level = 'DISTRIBUTION') then
1582: dist_cnt := dist_cnt + 1;
1583: distIdTbl.extend; distQtyTbl.extend; distAmtTbl.extend; -- FPS Changes
1584: distIdTbl(dist_cnt) := p_po_change_requests(i).document_distribution_id;
1585: distQtyTbl(dist_cnt) := p_po_change_requests(i).new_quantity;
1586: distAmtTbl(dist_cnt) := p_po_change_requests(i).new_amount; -- FPS Changes
1587:
1588: end if; -- if dist
1581: if (p_po_change_requests(i).request_level = 'DISTRIBUTION') then
1582: dist_cnt := dist_cnt + 1;
1583: distIdTbl.extend; distQtyTbl.extend; distAmtTbl.extend; -- FPS Changes
1584: distIdTbl(dist_cnt) := p_po_change_requests(i).document_distribution_id;
1585: distQtyTbl(dist_cnt) := p_po_change_requests(i).new_quantity;
1586: distAmtTbl(dist_cnt) := p_po_change_requests(i).new_amount; -- FPS Changes
1587:
1588: end if; -- if dist
1589:
1582: dist_cnt := dist_cnt + 1;
1583: distIdTbl.extend; distQtyTbl.extend; distAmtTbl.extend; -- FPS Changes
1584: distIdTbl(dist_cnt) := p_po_change_requests(i).document_distribution_id;
1585: distQtyTbl(dist_cnt) := p_po_change_requests(i).new_quantity;
1586: distAmtTbl(dist_cnt) := p_po_change_requests(i).new_amount; -- FPS Changes
1587:
1588: end if; -- if dist
1589:
1590: --end if; -- end of rec count
1615: exit when ga_ship_csr%notfound;
1616: changeOrig := 'F';
1617: FOR i in 1..rec_cnt LOOP
1618:
1619: if (p_po_change_requests(i).request_level = 'SHIPMENT' and
1620: p_po_change_requests(i).action_type = 'MODIFICATION' ) then
1621:
1622: if(p_po_change_requests(i).document_line_location_id = l_ga_lineLocId and
1623: p_po_change_requests(i).new_quantity is not null and
1616: changeOrig := 'F';
1617: FOR i in 1..rec_cnt LOOP
1618:
1619: if (p_po_change_requests(i).request_level = 'SHIPMENT' and
1620: p_po_change_requests(i).action_type = 'MODIFICATION' ) then
1621:
1622: if(p_po_change_requests(i).document_line_location_id = l_ga_lineLocId and
1623: p_po_change_requests(i).new_quantity is not null and
1624: p_po_change_requests(i).parent_line_location_id is null ) then
1618:
1619: if (p_po_change_requests(i).request_level = 'SHIPMENT' and
1620: p_po_change_requests(i).action_type = 'MODIFICATION' ) then
1621:
1622: if(p_po_change_requests(i).document_line_location_id = l_ga_lineLocId and
1623: p_po_change_requests(i).new_quantity is not null and
1624: p_po_change_requests(i).parent_line_location_id is null ) then
1625:
1626: l_qty_orig := l_qty_orig + p_po_change_requests(i).new_quantity;
1619: if (p_po_change_requests(i).request_level = 'SHIPMENT' and
1620: p_po_change_requests(i).action_type = 'MODIFICATION' ) then
1621:
1622: if(p_po_change_requests(i).document_line_location_id = l_ga_lineLocId and
1623: p_po_change_requests(i).new_quantity is not null and
1624: p_po_change_requests(i).parent_line_location_id is null ) then
1625:
1626: l_qty_orig := l_qty_orig + p_po_change_requests(i).new_quantity;
1627: changeOrig := 'T';
1620: p_po_change_requests(i).action_type = 'MODIFICATION' ) then
1621:
1622: if(p_po_change_requests(i).document_line_location_id = l_ga_lineLocId and
1623: p_po_change_requests(i).new_quantity is not null and
1624: p_po_change_requests(i).parent_line_location_id is null ) then
1625:
1626: l_qty_orig := l_qty_orig + p_po_change_requests(i).new_quantity;
1627: changeOrig := 'T';
1628: end if;
1622: if(p_po_change_requests(i).document_line_location_id = l_ga_lineLocId and
1623: p_po_change_requests(i).new_quantity is not null and
1624: p_po_change_requests(i).parent_line_location_id is null ) then
1625:
1626: l_qty_orig := l_qty_orig + p_po_change_requests(i).new_quantity;
1627: changeOrig := 'T';
1628: end if;
1629: -- Sum up all the split quantities
1630: if (p_po_change_requests(i).parent_line_location_id is not null and
1626: l_qty_orig := l_qty_orig + p_po_change_requests(i).new_quantity;
1627: changeOrig := 'T';
1628: end if;
1629: -- Sum up all the split quantities
1630: if (p_po_change_requests(i).parent_line_location_id is not null and
1631: p_po_change_requests(i).parent_line_location_id = l_ga_lineLocId and
1632: p_po_change_requests(i).new_quantity is not null) then
1633:
1634: l_qty_split := l_qty_split + p_po_change_requests(i).new_quantity;
1627: changeOrig := 'T';
1628: end if;
1629: -- Sum up all the split quantities
1630: if (p_po_change_requests(i).parent_line_location_id is not null and
1631: p_po_change_requests(i).parent_line_location_id = l_ga_lineLocId and
1632: p_po_change_requests(i).new_quantity is not null) then
1633:
1634: l_qty_split := l_qty_split + p_po_change_requests(i).new_quantity;
1635:
1628: end if;
1629: -- Sum up all the split quantities
1630: if (p_po_change_requests(i).parent_line_location_id is not null and
1631: p_po_change_requests(i).parent_line_location_id = l_ga_lineLocId and
1632: p_po_change_requests(i).new_quantity is not null) then
1633:
1634: l_qty_split := l_qty_split + p_po_change_requests(i).new_quantity;
1635:
1636: end if;
1630: if (p_po_change_requests(i).parent_line_location_id is not null and
1631: p_po_change_requests(i).parent_line_location_id = l_ga_lineLocId and
1632: p_po_change_requests(i).new_quantity is not null) then
1633:
1634: l_qty_split := l_qty_split + p_po_change_requests(i).new_quantity;
1635:
1636: end if;
1637: end if ; -- if shipment change
1638: END LOOP;
1833: x_msg_data OUT NOCOPY VARCHAR2,
1834: p_po_header_id IN number,
1835: p_po_release_id IN number,
1836: p_revision_num IN number,
1837: p_po_change_requests IN OUT NOCOPY pos_chg_rec_tbl,
1838: x_online_report_id OUT NOCOPY number,
1839: x_pos_errors OUT NOCOPY pos_err_type) is
1840:
1841: l_doc_check_error_msg Doc_Check_Return_Type := NULL;
1841: l_doc_check_error_msg Doc_Check_Return_Type := NULL;
1842: l_msg_data varchar2(2000) := NULL;
1843:
1844: BEGIN
1845: --l_po_change_requests := p_po_change_requests;
1846:
1847: validate_change_request (
1848: p_api_version => 1.0,
1849: p_init_msg_list => FND_API.G_FALSE,
1851: x_msg_data => l_msg_data,
1852: p_po_header_id => p_po_header_id,
1853: p_po_release_id => p_po_release_id,
1854: p_revision_num => p_revision_num,
1855: p_po_change_requests => p_po_change_requests,
1856: x_online_report_id => x_online_report_id,
1857: x_pos_errors => x_pos_errors,
1858: x_doc_check_error_msg => l_doc_check_error_msg);
1859:
2327: x_return_status OUT NOCOPY VARCHAR2,
2328: p_po_header_id IN number,
2329: p_po_release_id IN number,
2330: p_revision_num IN number,
2331: p_po_change_requests IN pos_chg_rec_tbl,
2332: x_request_group_id OUT NOCOPY NUMBER) is
2333:
2334: l_api_name CONSTANT VARCHAR2(30) := 'save_cancel_request';
2335: l_api_version_number CONSTANT NUMBER := 1.0;
2359: x_return_status => x_return_status,
2360: p_po_header_id => p_po_header_id,
2361: p_po_release_id => p_po_release_id,
2362: p_revision_num => p_revision_num,
2363: p_po_change_requests => p_po_change_requests,
2364: x_request_group_id => l_request_group_id);
2365:
2366: -- Call Update PO Procedure to set PO in IN PROCESS
2367: update_po_attributes(p_po_header_id,
2491: l_revision_num number;
2492:
2493: cursor c1(p_po_header_id in number) is
2494: select change_request_group_id,DOCUMENT_REVISION_NUM
2495: from po_change_requests
2496: where document_header_id = p_po_header_id and
2497: document_type = 'PO' and
2498: change_active_flag= 'Y' and
2499: initiator = 'SUPPLIER' and
2500: request_status not in ('ACCEPTED', 'REJECTED');
2501:
2502: cursor c2(p_po_release_id in number) is
2503: select change_request_group_id,DOCUMENT_REVISION_NUM
2504: from po_change_requests
2505: where po_release_id = p_po_release_id and
2506: document_type = 'RELEASE' and
2507: change_active_flag= 'Y' and
2508: initiator = 'SUPPLIER' and
2535: if (xGrpId > 0) then
2536: l_cancel_msg := fnd_message.get_string('POS','POS_AUTO_CANCEL_BY_BUYER') ;
2537: if (p_po_release_id is not null and p_po_line_id is null and p_po_line_location_id is null) then
2538: begin
2539: update po_change_requests
2540: set request_status = 'REJECTED',change_active_flag = 'N',
2541: request_reason=l_cancel_msg
2542: where po_release_id = p_po_release_id and
2543: initiator = 'SUPPLIER' and
2543: initiator = 'SUPPLIER' and
2544: request_status not in ('ACCEPTED','REJECTED') and
2545: action_type='MODIFICATION';
2546:
2547: update po_change_requests
2548: set request_status = 'ACCEPTED',
2549: change_active_flag = 'N'
2550: where initiator = 'SUPPLIER' and
2551: request_status not in ('ACCEPTED','REJECTED') and
2558: end if;
2559:
2560: if (p_po_header_id is not null and p_po_line_id is null and p_po_line_location_id is null) then
2561: begin
2562: update po_change_requests
2563: set request_status = 'REJECTED',
2564: change_active_flag = 'N',
2565: request_reason=l_cancel_msg
2566: where document_header_id = p_po_header_id and
2567: request_status not in ('ACCEPTED','REJECTED') and
2568: initiator = 'SUPPLIER' and
2569: action_type='MODIFICATION';
2570:
2571: update po_change_requests
2572: set request_status = 'ACCEPTED',
2573: change_active_flag = 'N'
2574: where request_status not in ('ACCEPTED','REJECTED') and
2575: initiator = 'SUPPLIER' and
2582: end if;
2583:
2584: if (p_po_line_location_id is not null ) then
2585: begin
2586: update po_change_requests
2587: set request_status = 'REJECTED',change_active_flag = 'N',request_reason=l_cancel_msg
2588: where document_line_location_id = p_po_line_location_id and
2589: request_level = 'SHIPMENT' and
2590: request_status not in ('ACCEPTED','REJECTED') and
2590: request_status not in ('ACCEPTED','REJECTED') and
2591: initiator = 'SUPPLIER' and
2592: action_type='MODIFICATION';
2593:
2594: update po_change_requests
2595: set request_status = 'ACCEPTED',change_active_flag = 'N'
2596: where document_line_location_id = p_po_line_location_id and
2597: request_level = 'SHIPMENT' and
2598: request_status not in ('ACCEPTED','REJECTED') and
2606: end if;
2607:
2608: if (p_po_line_id is not null and p_po_line_location_id is null ) then
2609: begin
2610: update po_change_requests
2611: set request_status='REJECTED',change_active_flag='N',request_reason=l_cancel_msg
2612: where document_line_id = p_po_line_id and
2613: request_status not in ('ACCEPTED','REJECTED') and
2614: initiator = 'SUPPLIER' and
2613: request_status not in ('ACCEPTED','REJECTED') and
2614: initiator = 'SUPPLIER' and
2615: action_type='MODIFICATION';
2616:
2617: update po_change_requests
2618: set request_status='ACCEPTED',change_active_flag='N'
2619: where document_line_id = p_po_line_id and
2620: request_status not in ('ACCEPTED','REJECTED') and
2621: initiator = 'SUPPLIER' and
2657: exception
2658: when others then
2659: raise;
2660: end;
2661: update po_change_requests
2662: set document_revision_num = l_revision_num
2663: where po_release_id = p_po_release_id and
2664: request_status not in ('ACCEPTED','REJECTED') and
2665: document_type = 'RELEASE' and
2694: exception
2695: when others then
2696: raise;
2697: end;
2698: update po_change_requests
2699: set document_revision_num=l_revision_num
2700: where document_header_id = p_po_header_id and
2701: request_status not in ('ACCEPTED','REJECTED') and
2702: document_type = 'PO' and
3160: END;
3161:
3162: procedure validate_shipment_cancel (
3163: p_po_header_id IN number,
3164: p_po_change_requests IN pos_chg_rec_tbl,
3165: x_pos_errors OUT NOCOPY POS_ERR_TYPE,
3166: x_ret_sts OUT NOCOPY varchar2
3167: ) IS
3168:
3165: x_pos_errors OUT NOCOPY POS_ERR_TYPE,
3166: x_ret_sts OUT NOCOPY varchar2
3167: ) IS
3168:
3169: l_po_change_requests pos_chg_rec_tbl := NULL;
3170: l_count_asn NUMBER;
3171: l_err_msg_name_tbl po_tbl_varchar30;
3172: l_err_msg_text_tbl po_tbl_varchar2000;
3173: l_err_count NUMBER;
3173: l_err_count NUMBER;
3174:
3175: BEGIN
3176: l_err_count := 0;
3177: l_po_change_requests := p_po_change_requests ;
3178: l_err_msg_name_tbl := po_tbl_varchar30();
3179: l_err_msg_text_tbl := po_tbl_varchar2000();
3180: x_pos_errors := POS_ERR_TYPE( l_err_msg_name_tbl,l_err_msg_text_tbl);
3181: x_ret_sts := 'N';
3178: l_err_msg_name_tbl := po_tbl_varchar30();
3179: l_err_msg_text_tbl := po_tbl_varchar2000();
3180: x_pos_errors := POS_ERR_TYPE( l_err_msg_name_tbl,l_err_msg_text_tbl);
3181: x_ret_sts := 'N';
3182: FOR j in 1..l_po_change_requests.count()
3183: LOOP
3184: if ( l_po_change_requests(j).action_type in ('CANCELLATION')) then --AND
3185: -- l_po_change_requests(j).request_level='SHIPMENT' ) then
3186: select count(*)
3180: x_pos_errors := POS_ERR_TYPE( l_err_msg_name_tbl,l_err_msg_text_tbl);
3181: x_ret_sts := 'N';
3182: FOR j in 1..l_po_change_requests.count()
3183: LOOP
3184: if ( l_po_change_requests(j).action_type in ('CANCELLATION')) then --AND
3185: -- l_po_change_requests(j).request_level='SHIPMENT' ) then
3186: select count(*)
3187: into l_count_asn
3188: from RCV_TRANSACTIONS_INTERFACE rti
3181: x_ret_sts := 'N';
3182: FOR j in 1..l_po_change_requests.count()
3183: LOOP
3184: if ( l_po_change_requests(j).action_type in ('CANCELLATION')) then --AND
3185: -- l_po_change_requests(j).request_level='SHIPMENT' ) then
3186: select count(*)
3187: into l_count_asn
3188: from RCV_TRANSACTIONS_INTERFACE rti
3189: where rti.TRANSACTION_TYPE = 'SHIP' and
3189: where rti.TRANSACTION_TYPE = 'SHIP' and
3190: rti.PROCESSING_STATUS_CODE = 'PENDING' and
3191: rti.quantity > 0 and
3192: rti.PO_HEADER_ID = p_po_header_id and
3193: (rti.po_line_location_id = l_po_change_requests(j).document_line_location_id OR l_po_change_requests(j).document_line_location_id is null);
3194: IF(l_count_asn > 0) then
3195: l_err_count := l_err_count + 1;
3196: x_pos_errors.message_name.extend;
3197: x_pos_errors.text_line.extend;
3195: l_err_count := l_err_count + 1;
3196: x_pos_errors.message_name.extend;
3197: x_pos_errors.text_line.extend;
3198: x_pos_errors.message_name(l_err_count) := null;
3199: if l_po_change_requests(j).document_line_location_id is not null then
3200: FND_MESSAGE.set_name('POS','POS_CAN_PO_LS_UNPRC_TX');
3201: fnd_message.set_token('LINE', l_po_change_requests(j).Document_Line_Number);
3202: fnd_message.set_token('SHIPMENT', l_po_change_requests(j).Document_Shipment_Number);
3203: x_pos_errors.text_line(l_err_count) := fnd_message.get;
3197: x_pos_errors.text_line.extend;
3198: x_pos_errors.message_name(l_err_count) := null;
3199: if l_po_change_requests(j).document_line_location_id is not null then
3200: FND_MESSAGE.set_name('POS','POS_CAN_PO_LS_UNPRC_TX');
3201: fnd_message.set_token('LINE', l_po_change_requests(j).Document_Line_Number);
3202: fnd_message.set_token('SHIPMENT', l_po_change_requests(j).Document_Shipment_Number);
3203: x_pos_errors.text_line(l_err_count) := fnd_message.get;
3204: else
3205: x_pos_errors.text_line(l_err_count) := fnd_message.get_String('POS', 'POS_CAN_PO_UNPRC_TX');
3198: x_pos_errors.message_name(l_err_count) := null;
3199: if l_po_change_requests(j).document_line_location_id is not null then
3200: FND_MESSAGE.set_name('POS','POS_CAN_PO_LS_UNPRC_TX');
3201: fnd_message.set_token('LINE', l_po_change_requests(j).Document_Line_Number);
3202: fnd_message.set_token('SHIPMENT', l_po_change_requests(j).Document_Shipment_Number);
3203: x_pos_errors.text_line(l_err_count) := fnd_message.get;
3204: else
3205: x_pos_errors.text_line(l_err_count) := fnd_message.get_String('POS', 'POS_CAN_PO_UNPRC_TX');
3206: return;
3210: SELECT count(*)
3211: into l_count_asn
3212: FROM RCV_SHIPMENT_LINES RSL
3213: WHERE RSL.po_header_id = p_po_header_id
3214: AND (RSL.po_line_location_id = l_po_change_requests(j).document_line_location_id OR l_po_change_requests(j).document_line_location_id is null)
3215: AND NVL(RSL.quantity_shipped,0) > NVL(RSL.quantity_received,0)
3216: AND NVL(RSL.ASN_LINE_FLAG,'N') = 'Y'
3217: AND NVL(RSL.SHIPMENT_LINE_STATUS_CODE,'EXPECTED') <> 'CANCELLED';
3218: IF(l_count_asn > 0) then
3219: l_err_count := l_err_count + 1;
3220: x_pos_errors.message_name.extend;
3221: x_pos_errors.text_line.extend;
3222: x_pos_errors.message_name(l_err_count) := null;
3223: if l_po_change_requests(j).document_line_location_id is not null then
3224: FND_MESSAGE.set_name('POS','POS_CAN_PO_LS_OPEN_ASN');
3225: fnd_message.set_token('LINE', l_po_change_requests(j).Document_Line_Number);
3226: fnd_message.set_token('SHIPMENT', l_po_change_requests(j).Document_Shipment_Number);
3227: x_pos_errors.text_line(l_err_count) := fnd_message.get;
3221: x_pos_errors.text_line.extend;
3222: x_pos_errors.message_name(l_err_count) := null;
3223: if l_po_change_requests(j).document_line_location_id is not null then
3224: FND_MESSAGE.set_name('POS','POS_CAN_PO_LS_OPEN_ASN');
3225: fnd_message.set_token('LINE', l_po_change_requests(j).Document_Line_Number);
3226: fnd_message.set_token('SHIPMENT', l_po_change_requests(j).Document_Shipment_Number);
3227: x_pos_errors.text_line(l_err_count) := fnd_message.get;
3228: else
3229: x_pos_errors.text_line(l_err_count) := fnd_message.get_String('POS', 'POS_CAN_PO_OPEN_ASN') ;
3222: x_pos_errors.message_name(l_err_count) := null;
3223: if l_po_change_requests(j).document_line_location_id is not null then
3224: FND_MESSAGE.set_name('POS','POS_CAN_PO_LS_OPEN_ASN');
3225: fnd_message.set_token('LINE', l_po_change_requests(j).Document_Line_Number);
3226: fnd_message.set_token('SHIPMENT', l_po_change_requests(j).Document_Shipment_Number);
3227: x_pos_errors.text_line(l_err_count) := fnd_message.get;
3228: else
3229: x_pos_errors.text_line(l_err_count) := fnd_message.get_String('POS', 'POS_CAN_PO_OPEN_ASN') ;
3230: return;
3234: END LOOP;
3235: if(l_err_count < 1) then
3236: validate_ship_inv_cancel (
3237: p_po_header_id,
3238: p_po_change_requests,
3239: x_pos_errors,
3240: x_ret_sts);
3241: else
3242: x_ret_sts := 'Y';
3245: END validate_shipment_cancel;
3246:
3247: procedure validate_ship_inv_cancel (
3248: p_po_header_id IN number,
3249: p_po_change_requests IN pos_chg_rec_tbl,
3250: x_pos_errors OUT NOCOPY POS_ERR_TYPE,
3251: x_ret_sts OUT NOCOPY varchar2
3252: ) IS
3253:
3250: x_pos_errors OUT NOCOPY POS_ERR_TYPE,
3251: x_ret_sts OUT NOCOPY varchar2
3252: ) IS
3253:
3254: l_po_change_requests pos_chg_rec_tbl := NULL;
3255: l_count_asn NUMBER;
3256: l_err_msg_name_tbl po_tbl_varchar30;
3257: l_err_msg_text_tbl po_tbl_varchar2000;
3258: l_err_count NUMBER;
3260: l_quan_recd NUMBER;
3261: l_quan_billed NUMBER;
3262: BEGIN
3263: l_err_count := 0;
3264: l_po_change_requests := p_po_change_requests ;
3265: l_err_msg_name_tbl := po_tbl_varchar30();
3266: l_err_msg_text_tbl := po_tbl_varchar2000();
3267: x_pos_errors := POS_ERR_TYPE( l_err_msg_name_tbl,l_err_msg_text_tbl);
3268: FOR j in 1..l_po_change_requests.count()
3264: l_po_change_requests := p_po_change_requests ;
3265: l_err_msg_name_tbl := po_tbl_varchar30();
3266: l_err_msg_text_tbl := po_tbl_varchar2000();
3267: x_pos_errors := POS_ERR_TYPE( l_err_msg_name_tbl,l_err_msg_text_tbl);
3268: FOR j in 1..l_po_change_requests.count()
3269: LOOP
3270: if ( l_po_change_requests(j).action_type in ('CANCELLATION') AND
3271: l_po_change_requests(j).request_level='SHIPMENT' ) then
3272: l_quan_ordered := -1;
3266: l_err_msg_text_tbl := po_tbl_varchar2000();
3267: x_pos_errors := POS_ERR_TYPE( l_err_msg_name_tbl,l_err_msg_text_tbl);
3268: FOR j in 1..l_po_change_requests.count()
3269: LOOP
3270: if ( l_po_change_requests(j).action_type in ('CANCELLATION') AND
3271: l_po_change_requests(j).request_level='SHIPMENT' ) then
3272: l_quan_ordered := -1;
3273: l_quan_recd := -1;
3274: l_quan_billed := -1;
3267: x_pos_errors := POS_ERR_TYPE( l_err_msg_name_tbl,l_err_msg_text_tbl);
3268: FOR j in 1..l_po_change_requests.count()
3269: LOOP
3270: if ( l_po_change_requests(j).action_type in ('CANCELLATION') AND
3271: l_po_change_requests(j).request_level='SHIPMENT' ) then
3272: l_quan_ordered := -1;
3273: l_quan_recd := -1;
3274: l_quan_billed := -1;
3275: begin
3275: begin
3276: SELECT nvl(POLL.quantity, 0), nvl(POLL.quantity_billed, 0), nvl(POLL.quantity_received, 0)
3277: into l_quan_ordered, l_quan_billed, l_quan_recd
3278: FROM PO_LINE_LOCATIONS_ALL POLL, PO_LINES_ALL POL
3279: WHERE POLL.line_location_id = l_po_change_requests(j).document_line_location_id
3280: AND POLL.po_line_id = POL.po_line_id
3281: AND nvl(POLL.cancel_flag, 'N') = 'N'
3282: AND nvl(POLL.closed_code, 'OPEN') <> 'FINALLY CLOSED'
3283: AND nvl(POLL.receipt_required_flag, 'Y') <> 'N'
3292: x_pos_errors.message_name.extend;
3293: x_pos_errors.text_line.extend;
3294: x_pos_errors.message_name(l_err_count) := null;
3295: FND_MESSAGE.set_name('POS','POS_CAN_PO_QTY_BILL_RCV');
3296: fnd_message.set_token('LINE', l_po_change_requests(j).Document_Line_Number);
3297: fnd_message.set_token('SHIPMENT', l_po_change_requests(j).Document_Shipment_Number);
3298: fnd_message.set_token('QTY_BILL', l_quan_billed) ;
3299: fnd_message.set_token('QTY_RCV', l_quan_recd) ;
3300: x_pos_errors.text_line(l_err_count) := fnd_message.get;
3293: x_pos_errors.text_line.extend;
3294: x_pos_errors.message_name(l_err_count) := null;
3295: FND_MESSAGE.set_name('POS','POS_CAN_PO_QTY_BILL_RCV');
3296: fnd_message.set_token('LINE', l_po_change_requests(j).Document_Line_Number);
3297: fnd_message.set_token('SHIPMENT', l_po_change_requests(j).Document_Shipment_Number);
3298: fnd_message.set_token('QTY_BILL', l_quan_billed) ;
3299: fnd_message.set_token('QTY_RCV', l_quan_recd) ;
3300: x_pos_errors.text_line(l_err_count) := fnd_message.get;
3301: end if;
3306: begin
3307: SELECT nvl(POLL.quantity, 0), nvl(POLL.quantity_billed, 0), nvl(POLL.quantity_received, 0)
3308: into l_quan_ordered, l_quan_billed, l_quan_recd
3309: FROM PO_LINE_LOCATIONS_ALL POLL, PO_LINES_ALL POL
3310: WHERE POLL.line_location_id = l_po_change_requests(j).document_line_location_id
3311: AND POLL.po_line_id = POL.po_line_id
3312: AND nvl(POLL.cancel_flag, 'N') = 'N'
3313: AND nvl(POLL.closed_code, 'OPEN') <> 'FINALLY CLOSED'
3314: AND nvl(POLL.receipt_required_flag, 'Y') <> 'N'
3322: x_pos_errors.message_name.extend;
3323: x_pos_errors.text_line.extend;
3324: x_pos_errors.message_name(l_err_count) := null;
3325: FND_MESSAGE.set_name('POS','POS_CAN_PO_QTY_BILL_ORD');
3326: fnd_message.set_token('LINE', l_po_change_requests(j).Document_Line_Number);
3327: fnd_message.set_token('SHIPMENT', l_po_change_requests(j).Document_Shipment_Number);
3328: fnd_message.set_token('QTY_BILL', l_quan_billed) ;
3329: fnd_message.set_token('QTY_ORD', l_quan_ordered) ;
3330: x_pos_errors.text_line(l_err_count) := fnd_message.get;
3323: x_pos_errors.text_line.extend;
3324: x_pos_errors.message_name(l_err_count) := null;
3325: FND_MESSAGE.set_name('POS','POS_CAN_PO_QTY_BILL_ORD');
3326: fnd_message.set_token('LINE', l_po_change_requests(j).Document_Line_Number);
3327: fnd_message.set_token('SHIPMENT', l_po_change_requests(j).Document_Shipment_Number);
3328: fnd_message.set_token('QTY_BILL', l_quan_billed) ;
3329: fnd_message.set_token('QTY_ORD', l_quan_ordered) ;
3330: x_pos_errors.text_line(l_err_count) := fnd_message.get;
3331: end if;