[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;
554: x_return_status OUT NOCOPY VARCHAR2,
555: p_po_header_id IN NUMBER,
556: p_po_release_id IN NUMBER,
557: p_revision_num IN NUMBER,
558: p_po_change_requests IN pos_chg_rec_tbl,
559: x_request_group_id OUT NOCOPY NUMBER,
560: p_chn_int_cont_num IN varchar2 default null,
561: p_chn_source IN varchar2 default null,
562: p_chn_requestor_username in varchar2 default null,
568: x_return_code varchar2(40);
569: v_request_group_id number;
570: accp_flag char(1);
571: v_buyer_id number;
572: v_document_type po_change_requests.DOCUMENT_TYPE%TYPE;
573: l_user_id NUMBER := fnd_global.user_id;
574: l_login_id NUMBER := fnd_global.login_id;
575: l_api_name CONSTANT VARCHAR2(30) := 'save_request';
576: l_api_version_number CONSTANT NUMBER := 1.0;
598: l_user_id := p_user_id;
599: l_login_id := p_login_id;
600: end if;
601:
602: rec_cnt := p_po_change_requests.count();
603: -- get the ChangerequestGroupID only when changes are requested
604: IF( rec_cnt > 0) THEN
605: -- Get the document type of the first record to get the unique request group id
606: if (p_po_release_id is not null) then
618: x_request_group_id := v_request_group_id;
619: END IF;
620:
621: FOR i in 1..rec_cnt LOOP
622: if(p_po_change_requests(i).action_type not in ('ACCEPT','REJECT')) then
623:
624: insert into po_change_requests(
625: change_request_group_id, change_request_id,
626: initiator, action_type, request_reason,
620:
621: FOR i in 1..rec_cnt LOOP
622: if(p_po_change_requests(i).action_type not in ('ACCEPT','REJECT')) then
623:
624: insert into po_change_requests(
625: change_request_group_id, change_request_id,
626: initiator, action_type, request_reason,
627: request_level, request_status, document_type,
628: document_header_id, document_num,
648: NEW_PROGRESS_TYPE,NEW_PAY_DESCRIPTION --<< Complex work changes for R12 >>
649:
650: )
651: values (x_request_group_id,po_chg_request_seq.nextval,
652: p_po_change_requests(i).initiator,
653: p_po_change_requests(i).action_type,
654: p_po_change_requests(i).request_reason,
655: p_po_change_requests(i).request_level,
656: p_po_change_requests(i).request_status,
649:
650: )
651: values (x_request_group_id,po_chg_request_seq.nextval,
652: p_po_change_requests(i).initiator,
653: p_po_change_requests(i).action_type,
654: p_po_change_requests(i).request_reason,
655: p_po_change_requests(i).request_level,
656: p_po_change_requests(i).request_status,
657: p_po_change_requests(i).document_type,
650: )
651: values (x_request_group_id,po_chg_request_seq.nextval,
652: p_po_change_requests(i).initiator,
653: p_po_change_requests(i).action_type,
654: p_po_change_requests(i).request_reason,
655: p_po_change_requests(i).request_level,
656: p_po_change_requests(i).request_status,
657: p_po_change_requests(i).document_type,
658: p_po_change_requests(i).document_header_id,
651: values (x_request_group_id,po_chg_request_seq.nextval,
652: p_po_change_requests(i).initiator,
653: p_po_change_requests(i).action_type,
654: p_po_change_requests(i).request_reason,
655: p_po_change_requests(i).request_level,
656: p_po_change_requests(i).request_status,
657: p_po_change_requests(i).document_type,
658: p_po_change_requests(i).document_header_id,
659: p_po_change_requests(i).document_num,
652: p_po_change_requests(i).initiator,
653: p_po_change_requests(i).action_type,
654: p_po_change_requests(i).request_reason,
655: p_po_change_requests(i).request_level,
656: p_po_change_requests(i).request_status,
657: p_po_change_requests(i).document_type,
658: p_po_change_requests(i).document_header_id,
659: p_po_change_requests(i).document_num,
660: to_number(p_po_change_requests(i).document_revision_num),
653: p_po_change_requests(i).action_type,
654: p_po_change_requests(i).request_reason,
655: p_po_change_requests(i).request_level,
656: p_po_change_requests(i).request_status,
657: p_po_change_requests(i).document_type,
658: p_po_change_requests(i).document_header_id,
659: p_po_change_requests(i).document_num,
660: to_number(p_po_change_requests(i).document_revision_num),
661: p_po_change_requests(i).po_release_id,
654: p_po_change_requests(i).request_reason,
655: p_po_change_requests(i).request_level,
656: p_po_change_requests(i).request_status,
657: p_po_change_requests(i).document_type,
658: p_po_change_requests(i).document_header_id,
659: p_po_change_requests(i).document_num,
660: to_number(p_po_change_requests(i).document_revision_num),
661: p_po_change_requests(i).po_release_id,
662: l_user_id,sysdate,l_login_id,sysdate,l_login_id,
655: p_po_change_requests(i).request_level,
656: p_po_change_requests(i).request_status,
657: p_po_change_requests(i).document_type,
658: p_po_change_requests(i).document_header_id,
659: p_po_change_requests(i).document_num,
660: to_number(p_po_change_requests(i).document_revision_num),
661: p_po_change_requests(i).po_release_id,
662: l_user_id,sysdate,l_login_id,sysdate,l_login_id,
663: p_po_change_requests(i).document_line_id,
656: p_po_change_requests(i).request_status,
657: p_po_change_requests(i).document_type,
658: p_po_change_requests(i).document_header_id,
659: p_po_change_requests(i).document_num,
660: to_number(p_po_change_requests(i).document_revision_num),
661: p_po_change_requests(i).po_release_id,
662: l_user_id,sysdate,l_login_id,sysdate,l_login_id,
663: p_po_change_requests(i).document_line_id,
664: p_po_change_requests(i).document_line_number,
657: p_po_change_requests(i).document_type,
658: p_po_change_requests(i).document_header_id,
659: p_po_change_requests(i).document_num,
660: to_number(p_po_change_requests(i).document_revision_num),
661: p_po_change_requests(i).po_release_id,
662: l_user_id,sysdate,l_login_id,sysdate,l_login_id,
663: p_po_change_requests(i).document_line_id,
664: p_po_change_requests(i).document_line_number,
665: p_po_change_requests(i).document_line_location_id,
659: p_po_change_requests(i).document_num,
660: to_number(p_po_change_requests(i).document_revision_num),
661: p_po_change_requests(i).po_release_id,
662: l_user_id,sysdate,l_login_id,sysdate,l_login_id,
663: p_po_change_requests(i).document_line_id,
664: p_po_change_requests(i).document_line_number,
665: p_po_change_requests(i).document_line_location_id,
666: p_po_change_requests(i).document_shipment_number,
667: p_po_change_requests(i).parent_line_location_id,
660: to_number(p_po_change_requests(i).document_revision_num),
661: p_po_change_requests(i).po_release_id,
662: l_user_id,sysdate,l_login_id,sysdate,l_login_id,
663: p_po_change_requests(i).document_line_id,
664: p_po_change_requests(i).document_line_number,
665: p_po_change_requests(i).document_line_location_id,
666: p_po_change_requests(i).document_shipment_number,
667: p_po_change_requests(i).parent_line_location_id,
668: p_po_change_requests(i).document_distribution_id,
661: p_po_change_requests(i).po_release_id,
662: l_user_id,sysdate,l_login_id,sysdate,l_login_id,
663: p_po_change_requests(i).document_line_id,
664: p_po_change_requests(i).document_line_number,
665: p_po_change_requests(i).document_line_location_id,
666: p_po_change_requests(i).document_shipment_number,
667: p_po_change_requests(i).parent_line_location_id,
668: p_po_change_requests(i).document_distribution_id,
669: p_po_change_requests(i).document_distribution_number,
662: l_user_id,sysdate,l_login_id,sysdate,l_login_id,
663: p_po_change_requests(i).document_line_id,
664: p_po_change_requests(i).document_line_number,
665: p_po_change_requests(i).document_line_location_id,
666: p_po_change_requests(i).document_shipment_number,
667: p_po_change_requests(i).parent_line_location_id,
668: p_po_change_requests(i).document_distribution_id,
669: p_po_change_requests(i).document_distribution_number,
670: p_po_change_requests(i).old_quantity,
663: p_po_change_requests(i).document_line_id,
664: p_po_change_requests(i).document_line_number,
665: p_po_change_requests(i).document_line_location_id,
666: p_po_change_requests(i).document_shipment_number,
667: p_po_change_requests(i).parent_line_location_id,
668: p_po_change_requests(i).document_distribution_id,
669: p_po_change_requests(i).document_distribution_number,
670: p_po_change_requests(i).old_quantity,
671: p_po_change_requests(i).new_quantity,
664: p_po_change_requests(i).document_line_number,
665: p_po_change_requests(i).document_line_location_id,
666: p_po_change_requests(i).document_shipment_number,
667: p_po_change_requests(i).parent_line_location_id,
668: p_po_change_requests(i).document_distribution_id,
669: p_po_change_requests(i).document_distribution_number,
670: p_po_change_requests(i).old_quantity,
671: p_po_change_requests(i).new_quantity,
672: p_po_change_requests(i).old_promised_date,
665: p_po_change_requests(i).document_line_location_id,
666: p_po_change_requests(i).document_shipment_number,
667: p_po_change_requests(i).parent_line_location_id,
668: p_po_change_requests(i).document_distribution_id,
669: p_po_change_requests(i).document_distribution_number,
670: p_po_change_requests(i).old_quantity,
671: p_po_change_requests(i).new_quantity,
672: p_po_change_requests(i).old_promised_date,
673: p_po_change_requests(i).new_promised_date,
666: p_po_change_requests(i).document_shipment_number,
667: p_po_change_requests(i).parent_line_location_id,
668: p_po_change_requests(i).document_distribution_id,
669: p_po_change_requests(i).document_distribution_number,
670: p_po_change_requests(i).old_quantity,
671: p_po_change_requests(i).new_quantity,
672: p_po_change_requests(i).old_promised_date,
673: p_po_change_requests(i).new_promised_date,
674: p_po_change_requests(i).old_supplier_part_number,
667: p_po_change_requests(i).parent_line_location_id,
668: p_po_change_requests(i).document_distribution_id,
669: p_po_change_requests(i).document_distribution_number,
670: p_po_change_requests(i).old_quantity,
671: p_po_change_requests(i).new_quantity,
672: p_po_change_requests(i).old_promised_date,
673: p_po_change_requests(i).new_promised_date,
674: p_po_change_requests(i).old_supplier_part_number,
675: p_po_change_requests(i).new_supplier_part_number,
668: p_po_change_requests(i).document_distribution_id,
669: p_po_change_requests(i).document_distribution_number,
670: p_po_change_requests(i).old_quantity,
671: p_po_change_requests(i).new_quantity,
672: p_po_change_requests(i).old_promised_date,
673: p_po_change_requests(i).new_promised_date,
674: p_po_change_requests(i).old_supplier_part_number,
675: p_po_change_requests(i).new_supplier_part_number,
676: p_po_change_requests(i).old_price,
669: p_po_change_requests(i).document_distribution_number,
670: p_po_change_requests(i).old_quantity,
671: p_po_change_requests(i).new_quantity,
672: p_po_change_requests(i).old_promised_date,
673: p_po_change_requests(i).new_promised_date,
674: p_po_change_requests(i).old_supplier_part_number,
675: p_po_change_requests(i).new_supplier_part_number,
676: p_po_change_requests(i).old_price,
677: p_po_change_requests(i).new_price,
670: p_po_change_requests(i).old_quantity,
671: p_po_change_requests(i).new_quantity,
672: p_po_change_requests(i).old_promised_date,
673: p_po_change_requests(i).new_promised_date,
674: p_po_change_requests(i).old_supplier_part_number,
675: p_po_change_requests(i).new_supplier_part_number,
676: p_po_change_requests(i).old_price,
677: p_po_change_requests(i).new_price,
678: p_po_change_requests(i).old_need_by_date,
671: p_po_change_requests(i).new_quantity,
672: p_po_change_requests(i).old_promised_date,
673: p_po_change_requests(i).new_promised_date,
674: p_po_change_requests(i).old_supplier_part_number,
675: p_po_change_requests(i).new_supplier_part_number,
676: p_po_change_requests(i).old_price,
677: p_po_change_requests(i).new_price,
678: p_po_change_requests(i).old_need_by_date,
679: p_po_change_requests(i).new_need_by_date,
672: p_po_change_requests(i).old_promised_date,
673: p_po_change_requests(i).new_promised_date,
674: p_po_change_requests(i).old_supplier_part_number,
675: p_po_change_requests(i).new_supplier_part_number,
676: p_po_change_requests(i).old_price,
677: p_po_change_requests(i).new_price,
678: p_po_change_requests(i).old_need_by_date,
679: p_po_change_requests(i).new_need_by_date,
680: p_po_change_requests(i).old_supplier_reference_number,
673: p_po_change_requests(i).new_promised_date,
674: p_po_change_requests(i).old_supplier_part_number,
675: p_po_change_requests(i).new_supplier_part_number,
676: p_po_change_requests(i).old_price,
677: p_po_change_requests(i).new_price,
678: p_po_change_requests(i).old_need_by_date,
679: p_po_change_requests(i).new_need_by_date,
680: p_po_change_requests(i).old_supplier_reference_number,
681: p_po_change_requests(i).new_supplier_reference_number,
674: p_po_change_requests(i).old_supplier_part_number,
675: p_po_change_requests(i).new_supplier_part_number,
676: p_po_change_requests(i).old_price,
677: p_po_change_requests(i).new_price,
678: p_po_change_requests(i).old_need_by_date,
679: p_po_change_requests(i).new_need_by_date,
680: p_po_change_requests(i).old_supplier_reference_number,
681: p_po_change_requests(i).new_supplier_reference_number,
682: p_po_change_requests(i).Approval_Required_Flag,
675: p_po_change_requests(i).new_supplier_part_number,
676: p_po_change_requests(i).old_price,
677: p_po_change_requests(i).new_price,
678: p_po_change_requests(i).old_need_by_date,
679: p_po_change_requests(i).new_need_by_date,
680: p_po_change_requests(i).old_supplier_reference_number,
681: p_po_change_requests(i).new_supplier_reference_number,
682: p_po_change_requests(i).Approval_Required_Flag,
683: p_po_change_requests(i).Parent_Change_request_Id,
676: p_po_change_requests(i).old_price,
677: p_po_change_requests(i).new_price,
678: p_po_change_requests(i).old_need_by_date,
679: p_po_change_requests(i).new_need_by_date,
680: p_po_change_requests(i).old_supplier_reference_number,
681: p_po_change_requests(i).new_supplier_reference_number,
682: p_po_change_requests(i).Approval_Required_Flag,
683: p_po_change_requests(i).Parent_Change_request_Id,
684: p_po_change_requests(i).Requester_id,
677: p_po_change_requests(i).new_price,
678: p_po_change_requests(i).old_need_by_date,
679: p_po_change_requests(i).new_need_by_date,
680: p_po_change_requests(i).old_supplier_reference_number,
681: p_po_change_requests(i).new_supplier_reference_number,
682: p_po_change_requests(i).Approval_Required_Flag,
683: p_po_change_requests(i).Parent_Change_request_Id,
684: p_po_change_requests(i).Requester_id,
685: p_po_change_requests(i).Old_Supplier_Order_Number,
678: p_po_change_requests(i).old_need_by_date,
679: p_po_change_requests(i).new_need_by_date,
680: p_po_change_requests(i).old_supplier_reference_number,
681: p_po_change_requests(i).new_supplier_reference_number,
682: p_po_change_requests(i).Approval_Required_Flag,
683: p_po_change_requests(i).Parent_Change_request_Id,
684: p_po_change_requests(i).Requester_id,
685: p_po_change_requests(i).Old_Supplier_Order_Number,
686: p_po_change_requests(i).New_Supplier_Order_Number,
679: p_po_change_requests(i).new_need_by_date,
680: p_po_change_requests(i).old_supplier_reference_number,
681: p_po_change_requests(i).new_supplier_reference_number,
682: p_po_change_requests(i).Approval_Required_Flag,
683: p_po_change_requests(i).Parent_Change_request_Id,
684: p_po_change_requests(i).Requester_id,
685: p_po_change_requests(i).Old_Supplier_Order_Number,
686: p_po_change_requests(i).New_Supplier_Order_Number,
687: p_po_change_requests(i).Old_Supplier_Order_Line_Number,
680: p_po_change_requests(i).old_supplier_reference_number,
681: p_po_change_requests(i).new_supplier_reference_number,
682: p_po_change_requests(i).Approval_Required_Flag,
683: p_po_change_requests(i).Parent_Change_request_Id,
684: p_po_change_requests(i).Requester_id,
685: p_po_change_requests(i).Old_Supplier_Order_Number,
686: p_po_change_requests(i).New_Supplier_Order_Number,
687: p_po_change_requests(i).Old_Supplier_Order_Line_Number,
688: p_po_change_requests(i).New_Supplier_Order_Line_Number,
681: p_po_change_requests(i).new_supplier_reference_number,
682: p_po_change_requests(i).Approval_Required_Flag,
683: p_po_change_requests(i).Parent_Change_request_Id,
684: p_po_change_requests(i).Requester_id,
685: p_po_change_requests(i).Old_Supplier_Order_Number,
686: p_po_change_requests(i).New_Supplier_Order_Number,
687: p_po_change_requests(i).Old_Supplier_Order_Line_Number,
688: p_po_change_requests(i).New_Supplier_Order_Line_Number,
689: decode(p_po_change_requests(i).request_status,'ACCEPTED','N','Y'),
682: p_po_change_requests(i).Approval_Required_Flag,
683: p_po_change_requests(i).Parent_Change_request_Id,
684: p_po_change_requests(i).Requester_id,
685: p_po_change_requests(i).Old_Supplier_Order_Number,
686: p_po_change_requests(i).New_Supplier_Order_Number,
687: p_po_change_requests(i).Old_Supplier_Order_Line_Number,
688: p_po_change_requests(i).New_Supplier_Order_Line_Number,
689: decode(p_po_change_requests(i).request_status,'ACCEPTED','N','Y'),
690: p_chn_int_cont_num,
683: p_po_change_requests(i).Parent_Change_request_Id,
684: p_po_change_requests(i).Requester_id,
685: p_po_change_requests(i).Old_Supplier_Order_Number,
686: p_po_change_requests(i).New_Supplier_Order_Number,
687: p_po_change_requests(i).Old_Supplier_Order_Line_Number,
688: p_po_change_requests(i).New_Supplier_Order_Line_Number,
689: decode(p_po_change_requests(i).request_status,'ACCEPTED','N','Y'),
690: p_chn_int_cont_num,
691: p_chn_source,
684: p_po_change_requests(i).Requester_id,
685: p_po_change_requests(i).Old_Supplier_Order_Number,
686: p_po_change_requests(i).New_Supplier_Order_Number,
687: p_po_change_requests(i).Old_Supplier_Order_Line_Number,
688: p_po_change_requests(i).New_Supplier_Order_Line_Number,
689: decode(p_po_change_requests(i).request_status,'ACCEPTED','N','Y'),
690: p_chn_int_cont_num,
691: p_chn_source,
692: p_po_change_requests(i).Additional_changes,
685: p_po_change_requests(i).Old_Supplier_Order_Number,
686: p_po_change_requests(i).New_Supplier_Order_Number,
687: p_po_change_requests(i).Old_Supplier_Order_Line_Number,
688: p_po_change_requests(i).New_Supplier_Order_Line_Number,
689: decode(p_po_change_requests(i).request_status,'ACCEPTED','N','Y'),
690: p_chn_int_cont_num,
691: p_chn_source,
692: p_po_change_requests(i).Additional_changes,
693: p_po_change_requests(i).old_start_date,
688: p_po_change_requests(i).New_Supplier_Order_Line_Number,
689: decode(p_po_change_requests(i).request_status,'ACCEPTED','N','Y'),
690: p_chn_int_cont_num,
691: p_chn_source,
692: p_po_change_requests(i).Additional_changes,
693: p_po_change_requests(i).old_start_date,
694: p_po_change_requests(i).new_start_date,
695: p_po_change_requests(i).old_expiration_date,
696: p_po_change_requests(i).new_expiration_date,
689: decode(p_po_change_requests(i).request_status,'ACCEPTED','N','Y'),
690: p_chn_int_cont_num,
691: p_chn_source,
692: p_po_change_requests(i).Additional_changes,
693: p_po_change_requests(i).old_start_date,
694: p_po_change_requests(i).new_start_date,
695: p_po_change_requests(i).old_expiration_date,
696: p_po_change_requests(i).new_expiration_date,
697: p_po_change_requests(i).old_amount,
690: p_chn_int_cont_num,
691: p_chn_source,
692: p_po_change_requests(i).Additional_changes,
693: p_po_change_requests(i).old_start_date,
694: p_po_change_requests(i).new_start_date,
695: p_po_change_requests(i).old_expiration_date,
696: p_po_change_requests(i).new_expiration_date,
697: p_po_change_requests(i).old_amount,
698: p_po_change_requests(i).new_amount,
691: p_chn_source,
692: p_po_change_requests(i).Additional_changes,
693: p_po_change_requests(i).old_start_date,
694: p_po_change_requests(i).new_start_date,
695: p_po_change_requests(i).old_expiration_date,
696: p_po_change_requests(i).new_expiration_date,
697: p_po_change_requests(i).old_amount,
698: p_po_change_requests(i).new_amount,
699: p_po_change_requests(i).SUPPLIER_DOC_REF,
692: p_po_change_requests(i).Additional_changes,
693: p_po_change_requests(i).old_start_date,
694: p_po_change_requests(i).new_start_date,
695: p_po_change_requests(i).old_expiration_date,
696: p_po_change_requests(i).new_expiration_date,
697: p_po_change_requests(i).old_amount,
698: p_po_change_requests(i).new_amount,
699: p_po_change_requests(i).SUPPLIER_DOC_REF,
700: p_po_change_requests(i).SUPPLIER_LINE_REF,
693: p_po_change_requests(i).old_start_date,
694: p_po_change_requests(i).new_start_date,
695: p_po_change_requests(i).old_expiration_date,
696: p_po_change_requests(i).new_expiration_date,
697: p_po_change_requests(i).old_amount,
698: p_po_change_requests(i).new_amount,
699: p_po_change_requests(i).SUPPLIER_DOC_REF,
700: p_po_change_requests(i).SUPPLIER_LINE_REF,
701: p_po_change_requests(i).SUPPLIER_SHIPMENT_REF,
694: p_po_change_requests(i).new_start_date,
695: p_po_change_requests(i).old_expiration_date,
696: p_po_change_requests(i).new_expiration_date,
697: p_po_change_requests(i).old_amount,
698: p_po_change_requests(i).new_amount,
699: p_po_change_requests(i).SUPPLIER_DOC_REF,
700: p_po_change_requests(i).SUPPLIER_LINE_REF,
701: p_po_change_requests(i).SUPPLIER_SHIPMENT_REF,
702: p_po_change_requests(i).NEW_PROGRESS_TYPE, --<< Complex work changes for R12 >>
695: p_po_change_requests(i).old_expiration_date,
696: p_po_change_requests(i).new_expiration_date,
697: p_po_change_requests(i).old_amount,
698: p_po_change_requests(i).new_amount,
699: p_po_change_requests(i).SUPPLIER_DOC_REF,
700: p_po_change_requests(i).SUPPLIER_LINE_REF,
701: p_po_change_requests(i).SUPPLIER_SHIPMENT_REF,
702: p_po_change_requests(i).NEW_PROGRESS_TYPE, --<< Complex work changes for R12 >>
703: p_po_change_requests(i).NEW_PAY_DESCRIPTION
696: p_po_change_requests(i).new_expiration_date,
697: p_po_change_requests(i).old_amount,
698: p_po_change_requests(i).new_amount,
699: p_po_change_requests(i).SUPPLIER_DOC_REF,
700: p_po_change_requests(i).SUPPLIER_LINE_REF,
701: p_po_change_requests(i).SUPPLIER_SHIPMENT_REF,
702: p_po_change_requests(i).NEW_PROGRESS_TYPE, --<< Complex work changes for R12 >>
703: p_po_change_requests(i).NEW_PAY_DESCRIPTION
704:
697: p_po_change_requests(i).old_amount,
698: p_po_change_requests(i).new_amount,
699: p_po_change_requests(i).SUPPLIER_DOC_REF,
700: p_po_change_requests(i).SUPPLIER_LINE_REF,
701: p_po_change_requests(i).SUPPLIER_SHIPMENT_REF,
702: p_po_change_requests(i).NEW_PROGRESS_TYPE, --<< Complex work changes for R12 >>
703: p_po_change_requests(i).NEW_PAY_DESCRIPTION
704:
705: );
698: p_po_change_requests(i).new_amount,
699: p_po_change_requests(i).SUPPLIER_DOC_REF,
700: p_po_change_requests(i).SUPPLIER_LINE_REF,
701: p_po_change_requests(i).SUPPLIER_SHIPMENT_REF,
702: p_po_change_requests(i).NEW_PROGRESS_TYPE, --<< Complex work changes for R12 >>
703: p_po_change_requests(i).NEW_PAY_DESCRIPTION
704:
705: );
706: end if;
699: p_po_change_requests(i).SUPPLIER_DOC_REF,
700: p_po_change_requests(i).SUPPLIER_LINE_REF,
701: p_po_change_requests(i).SUPPLIER_SHIPMENT_REF,
702: p_po_change_requests(i).NEW_PROGRESS_TYPE, --<< Complex work changes for R12 >>
703: p_po_change_requests(i).NEW_PAY_DESCRIPTION
704:
705: );
706: end if;
707: end loop;
741: procedure process_supplier_request (
742: p_po_header_id IN number,
743: p_po_release_id IN number,
744: p_revision_num IN number,
745: p_po_change_requests IN pos_chg_rec_tbl,
746: x_online_report_id OUT NOCOPY number,
747: x_pos_errors OUT NOCOPY POS_ERR_TYPE,
748: p_chn_int_cont_num IN varchar2 default null,
749: p_chn_source IN varchar2 default null,
769: x_return_status varchar2(20);
770: updatePoAttr boolean := false;
771: saveRequest boolean := false;
772: callWf boolean := false;
773: l_po_change_requests pos_chg_rec_tbl := NULL;
774: vAckTbl pos_ack_rec_tbl := pos_ack_rec_tbl();
775: ack_cnt number :=0;
776: callDocCheck boolean :=false;
777: accp_flag char(1);
878: x_pos_errors.text_line(1) := fnd_message.get_string('POS', 'POS_MODIFIED_PO_ROW');
879: return;
880: end if;
881: -- Copy the request into a local var
882: l_po_change_requests := p_po_change_requests;
883:
884: IF g_fnd_debug = 'Y' THEN
885: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
886: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, g_module_prefix || l_api_name ||
892:
893: validate_shipment_cancel (
894: p_po_header_id,
895: p_po_release_id,
896: p_po_change_requests,
897: x_pos_errors,
898: l_ret_sts);
899: if(l_ret_sts = 'Y') then
900: return;
899: if(l_ret_sts = 'Y') then
900: return;
901: end if;
902:
903: if ( l_po_change_requests(1).action_type in ('CANCELLATION') AND
904: l_po_change_requests(1).request_level='HEADER' ) then
905: if (l_po_change_requests.count > 1 ) then
906: x_return_status := FND_API.g_ret_sts_unexp_error;
907:
900: return;
901: end if;
902:
903: if ( l_po_change_requests(1).action_type in ('CANCELLATION') AND
904: l_po_change_requests(1).request_level='HEADER' ) then
905: if (l_po_change_requests.count > 1 ) then
906: x_return_status := FND_API.g_ret_sts_unexp_error;
907:
908: FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
901: end if;
902:
903: if ( l_po_change_requests(1).action_type in ('CANCELLATION') AND
904: l_po_change_requests(1).request_level='HEADER' ) then
905: if (l_po_change_requests.count > 1 ) then
906: x_return_status := FND_API.g_ret_sts_unexp_error;
907:
908: FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
909: IF g_fnd_debug = 'Y' THEN
938: x_return_status => l_return_status,
939: p_po_header_id => p_po_header_id,
940: p_po_release_id => p_po_release_id,
941: p_revision_num => p_revision_num,
942: p_po_change_requests => l_po_change_requests,
943: x_request_group_id => l_request_group_id
944: );
945: x_online_report_id := 0;
946: if (l_return_status <> FND_API.g_ret_sts_success) then
978: from po_releases_all
979: where po_release_id = p_po_release_id;
980: end if;
981:
982: FOR i in 1..l_po_change_requests.count()
983: LOOP
984: if (l_po_change_requests(i).action_type in ('ACCEPT','REJECT') AND
985: l_po_change_requests(i).request_level='SHIPMENT' ) then
986: callWf := true;
980: end if;
981:
982: FOR i in 1..l_po_change_requests.count()
983: LOOP
984: if (l_po_change_requests(i).action_type in ('ACCEPT','REJECT') AND
985: l_po_change_requests(i).request_level='SHIPMENT' ) then
986: callWf := true;
987: --updatePoAttr := false;
988: --callDocCheck := false;
981:
982: FOR i in 1..l_po_change_requests.count()
983: LOOP
984: if (l_po_change_requests(i).action_type in ('ACCEPT','REJECT') AND
985: l_po_change_requests(i).request_level='SHIPMENT' ) then
986: callWf := true;
987: --updatePoAttr := false;
988: --callDocCheck := false;
989:
986: callWf := true;
987: --updatePoAttr := false;
988: --callDocCheck := false;
989:
990: if (l_po_change_requests(i).action_type = 'ACCEPT') then
991: accp_flag := 'Y';
992: else
993: accp_flag := 'N';
994: end if;
996: -- Process The Acknowledgements
997:
998: PO_ACKNOWLEDGE_PO_GRP.Acknowledge_shipment(
999: 1.0,FND_API.G_FALSE,x_return_status,
1000: l_po_change_requests(i).document_line_location_id,
1001: l_po_change_requests(i).document_header_id,
1002: l_po_change_requests(i).po_release_id,
1003: l_po_change_requests(i).document_revision_num,
1004: accp_flag,
997:
998: PO_ACKNOWLEDGE_PO_GRP.Acknowledge_shipment(
999: 1.0,FND_API.G_FALSE,x_return_status,
1000: l_po_change_requests(i).document_line_location_id,
1001: l_po_change_requests(i).document_header_id,
1002: l_po_change_requests(i).po_release_id,
1003: l_po_change_requests(i).document_revision_num,
1004: accp_flag,
1005: l_po_change_requests(i).request_reason,
998: PO_ACKNOWLEDGE_PO_GRP.Acknowledge_shipment(
999: 1.0,FND_API.G_FALSE,x_return_status,
1000: l_po_change_requests(i).document_line_location_id,
1001: l_po_change_requests(i).document_header_id,
1002: l_po_change_requests(i).po_release_id,
1003: l_po_change_requests(i).document_revision_num,
1004: accp_flag,
1005: l_po_change_requests(i).request_reason,
1006: v_buyer_id, fnd_global.user_id);
999: 1.0,FND_API.G_FALSE,x_return_status,
1000: l_po_change_requests(i).document_line_location_id,
1001: l_po_change_requests(i).document_header_id,
1002: l_po_change_requests(i).po_release_id,
1003: l_po_change_requests(i).document_revision_num,
1004: accp_flag,
1005: l_po_change_requests(i).request_reason,
1006: v_buyer_id, fnd_global.user_id);
1007:
1001: l_po_change_requests(i).document_header_id,
1002: l_po_change_requests(i).po_release_id,
1003: l_po_change_requests(i).document_revision_num,
1004: accp_flag,
1005: l_po_change_requests(i).request_reason,
1006: v_buyer_id, fnd_global.user_id);
1007:
1008: elsif (l_po_change_requests(i).request_level='LINE' AND
1009: l_po_change_requests(i).action_type='MODIFICATION') then
1004: accp_flag,
1005: l_po_change_requests(i).request_reason,
1006: v_buyer_id, fnd_global.user_id);
1007:
1008: elsif (l_po_change_requests(i).request_level='LINE' AND
1009: l_po_change_requests(i).action_type='MODIFICATION') then
1010: callWf := true;
1011: callDocCheck := true;
1012: updatePoAttr := true;
1005: l_po_change_requests(i).request_reason,
1006: v_buyer_id, fnd_global.user_id);
1007:
1008: elsif (l_po_change_requests(i).request_level='LINE' AND
1009: l_po_change_requests(i).action_type='MODIFICATION') then
1010: callWf := true;
1011: callDocCheck := true;
1012: updatePoAttr := true;
1013: saveRequest := true;
1010: callWf := true;
1011: callDocCheck := true;
1012: updatePoAttr := true;
1013: saveRequest := true;
1014: elsif (l_po_change_requests(i).request_level='SHIPMENT' AND
1015: l_po_change_requests(i).action_type='CANCELLATION') then
1016: -- Do not call doc sub check for shipment cancellation
1017: saveRequest := true;
1018: callWf := true;
1011: callDocCheck := true;
1012: updatePoAttr := true;
1013: saveRequest := true;
1014: elsif (l_po_change_requests(i).request_level='SHIPMENT' AND
1015: l_po_change_requests(i).action_type='CANCELLATION') then
1016: -- Do not call doc sub check for shipment cancellation
1017: saveRequest := true;
1018: callWf := true;
1019: --callDocCheck := false;
1018: callWf := true;
1019: --callDocCheck := false;
1020: updatePoAttr := true;
1021:
1022: elsif (l_po_change_requests(i).request_level='SHIPMENT' AND
1023: l_po_change_requests(i).action_type='MODIFICATION') then
1024: -- If quantity,promised_date,price,Amount have not changed in the shipment level do not update po
1025: /*Bug 7112734 - Start
1026: During PO change process if Supplier Order Line number alone is changed then it will
1019: --callDocCheck := false;
1020: updatePoAttr := true;
1021:
1022: elsif (l_po_change_requests(i).request_level='SHIPMENT' AND
1023: l_po_change_requests(i).action_type='MODIFICATION') then
1024: -- If quantity,promised_date,price,Amount have not changed in the shipment level do not update po
1025: /*Bug 7112734 - Start
1026: During PO change process if Supplier Order Line number alone is changed then it will
1027: be treated as acceptance of the PO.
1025: /*Bug 7112734 - Start
1026: During PO change process if Supplier Order Line number alone is changed then it will
1027: be treated as acceptance of the PO.
1028: */
1029: if (l_po_change_requests(i).new_quantity is null AND
1030: l_po_change_requests(i).new_promised_date is null AND
1031: l_po_change_requests(i).new_price is null AND
1032: l_po_change_requests(i).new_amount is null) then -- FPS Enhancement
1033: --callDocCheck := false;
1026: During PO change process if Supplier Order Line number alone is changed then it will
1027: be treated as acceptance of the PO.
1028: */
1029: if (l_po_change_requests(i).new_quantity is null AND
1030: l_po_change_requests(i).new_promised_date is null AND
1031: l_po_change_requests(i).new_price is null AND
1032: l_po_change_requests(i).new_amount is null) then -- FPS Enhancement
1033: --callDocCheck := false;
1034: --updatePoAttr := false;
1027: be treated as acceptance of the PO.
1028: */
1029: if (l_po_change_requests(i).new_quantity is null AND
1030: l_po_change_requests(i).new_promised_date is null AND
1031: l_po_change_requests(i).new_price is null AND
1032: l_po_change_requests(i).new_amount is null) then -- FPS Enhancement
1033: --callDocCheck := false;
1034: --updatePoAttr := false;
1035: l_po_change_requests(i).request_status := 'ACCEPTED';
1028: */
1029: if (l_po_change_requests(i).new_quantity is null AND
1030: l_po_change_requests(i).new_promised_date is null AND
1031: l_po_change_requests(i).new_price is null AND
1032: l_po_change_requests(i).new_amount is null) then -- FPS Enhancement
1033: --callDocCheck := false;
1034: --updatePoAttr := false;
1035: l_po_change_requests(i).request_status := 'ACCEPTED';
1036:
1031: l_po_change_requests(i).new_price is null AND
1032: l_po_change_requests(i).new_amount is null) then -- FPS Enhancement
1033: --callDocCheck := false;
1034: --updatePoAttr := false;
1035: l_po_change_requests(i).request_status := 'ACCEPTED';
1036:
1037: -- If PO requires acknowledgement, post shipment-level acceptance.
1038: IF (x_accp_flag = 'Y') THEN
1039:
1040: callWf := true;
1041: -- Process The Acknowledgements
1042: PO_ACKNOWLEDGE_PO_GRP.Acknowledge_shipment(
1043: 1.0,FND_API.G_FALSE,x_return_status,
1044: l_po_change_requests(i).document_line_location_id,
1045: l_po_change_requests(i).document_header_id,
1046: l_po_change_requests(i).po_release_id,
1047: l_po_change_requests(i).document_revision_num,
1048: 'Y',
1041: -- Process The Acknowledgements
1042: PO_ACKNOWLEDGE_PO_GRP.Acknowledge_shipment(
1043: 1.0,FND_API.G_FALSE,x_return_status,
1044: l_po_change_requests(i).document_line_location_id,
1045: l_po_change_requests(i).document_header_id,
1046: l_po_change_requests(i).po_release_id,
1047: l_po_change_requests(i).document_revision_num,
1048: 'Y',
1049: l_po_change_requests(i).request_reason,
1042: PO_ACKNOWLEDGE_PO_GRP.Acknowledge_shipment(
1043: 1.0,FND_API.G_FALSE,x_return_status,
1044: l_po_change_requests(i).document_line_location_id,
1045: l_po_change_requests(i).document_header_id,
1046: l_po_change_requests(i).po_release_id,
1047: l_po_change_requests(i).document_revision_num,
1048: 'Y',
1049: l_po_change_requests(i).request_reason,
1050: v_buyer_id, fnd_global.user_id);
1043: 1.0,FND_API.G_FALSE,x_return_status,
1044: l_po_change_requests(i).document_line_location_id,
1045: l_po_change_requests(i).document_header_id,
1046: l_po_change_requests(i).po_release_id,
1047: l_po_change_requests(i).document_revision_num,
1048: 'Y',
1049: l_po_change_requests(i).request_reason,
1050: v_buyer_id, fnd_global.user_id);
1051:
1045: l_po_change_requests(i).document_header_id,
1046: l_po_change_requests(i).po_release_id,
1047: l_po_change_requests(i).document_revision_num,
1048: 'Y',
1049: l_po_change_requests(i).request_reason,
1050: v_buyer_id, fnd_global.user_id);
1051:
1052: END IF;
1053:
1059: saveRequest := true;
1060: -- Bug 7112734 - End
1061: end if; -- if only so has changed
1062:
1063: if (l_po_change_requests(i).New_Supplier_Order_Line_Number is not null) then
1064: begin
1065: update po_line_locations_all
1066: set supplier_order_line_number = l_po_change_requests(i).New_Supplier_Order_Line_Number
1067: where line_location_id = l_po_change_requests(i).document_line_location_id;
1062:
1063: if (l_po_change_requests(i).New_Supplier_Order_Line_Number is not null) then
1064: begin
1065: update po_line_locations_all
1066: set supplier_order_line_number = l_po_change_requests(i).New_Supplier_Order_Line_Number
1067: where line_location_id = l_po_change_requests(i).document_line_location_id;
1068: exception
1069: WHEN OTHERS THEN
1070: x_return_status := FND_API.g_ret_sts_unexp_error;
1063: if (l_po_change_requests(i).New_Supplier_Order_Line_Number is not null) then
1064: begin
1065: update po_line_locations_all
1066: set supplier_order_line_number = l_po_change_requests(i).New_Supplier_Order_Line_Number
1067: where line_location_id = l_po_change_requests(i).document_line_location_id;
1068: exception
1069: WHEN OTHERS THEN
1070: x_return_status := FND_API.g_ret_sts_unexp_error;
1071: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1077: END IF;
1078: END IF;
1079: END IF;
1080: end; end if;
1081: elsif (l_po_change_requests(i).request_level='HEADER'
1082: AND l_po_change_requests(i).action_type='MODIFICATION') then
1083: saveRequest := true;
1084:
1085: if (nvl(l_po_change_requests(i).New_Supplier_Order_Number,-1) <> nvl(l_po_change_requests(i).Old_Supplier_Order_Number,-1)) then
1078: END IF;
1079: END IF;
1080: end; end if;
1081: elsif (l_po_change_requests(i).request_level='HEADER'
1082: AND l_po_change_requests(i).action_type='MODIFICATION') then
1083: saveRequest := true;
1084:
1085: if (nvl(l_po_change_requests(i).New_Supplier_Order_Number,-1) <> nvl(l_po_change_requests(i).Old_Supplier_Order_Number,-1)) then
1086:
1081: elsif (l_po_change_requests(i).request_level='HEADER'
1082: AND l_po_change_requests(i).action_type='MODIFICATION') then
1083: saveRequest := true;
1084:
1085: if (nvl(l_po_change_requests(i).New_Supplier_Order_Number,-1) <> nvl(l_po_change_requests(i).Old_Supplier_Order_Number,-1)) then
1086:
1087: if (p_po_release_id is null ) then
1088: -- Update the vendor_order_num for PO Headers no need of approval.
1089: update po_headers_all
1086:
1087: if (p_po_release_id is null ) then
1088: -- Update the vendor_order_num for PO Headers no need of approval.
1089: update po_headers_all
1090: set vendor_order_num = l_po_change_requests(i).New_Supplier_Order_Number
1091: where po_header_id = p_po_header_id;
1092: else
1093: -- Update the vendor_order_num for PO Releases no need of approval.
1094: update po_releases_all
1091: where po_header_id = p_po_header_id;
1092: else
1093: -- Update the vendor_order_num for PO Releases no need of approval.
1094: update po_releases_all
1095: set vendor_order_num = l_po_change_requests(i).New_Supplier_Order_Number
1096: where po_release_id = p_po_release_id;
1097: end if;
1098: end if;
1099:
1097: end if;
1098: end if;
1099:
1100: -- Set startWf to false
1101: if (l_po_change_requests(i).Additional_changes is not null) then
1102: callWf := true;
1103: updatePoAttr := true;
1104: end if;
1105: --callDocCheck := false;
1105: --callDocCheck := false;
1106:
1107: end if; -- end if accept reject
1108: END LOOP;
1109: if ((callDocCheck) AND l_po_change_requests.count() > 0 ) then
1110: IF g_fnd_debug = 'Y' THEN
1111: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1112: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, g_module_prefix || l_api_name ||
1113: '.invoked', ' Change Count : ' || TO_CHAR(l_po_change_requests.count()));
1109: if ((callDocCheck) AND l_po_change_requests.count() > 0 ) then
1110: IF g_fnd_debug = 'Y' THEN
1111: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1112: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, g_module_prefix || l_api_name ||
1113: '.invoked', ' Change Count : ' || TO_CHAR(l_po_change_requests.count()));
1114: END IF;
1115: END IF;
1116:
1117: IS_ASN_EXIST( p_po_header_id,
1115: END IF;
1116:
1117: IS_ASN_EXIST( p_po_header_id,
1118: p_po_release_id,
1119: p_po_change_requests,
1120: x_pos_errors,
1121: l_ret_sts);
1122: IF( l_ret_sts = 'Y')
1123: THEN RETURN;
1129: x_msg_data => l_msg_data,
1130: p_po_header_id => p_po_header_id,
1131: p_po_release_id => p_po_release_id,
1132: p_revision_num => p_revision_num,
1133: p_po_change_requests => l_po_change_requests,
1134: x_online_report_id => x_online_report_id,
1135: x_pos_errors => x_pos_errors);
1136:
1137: end if;
1135: x_pos_errors => x_pos_errors);
1136:
1137: end if;
1138:
1139: if (l_po_change_requests.count() > 0 AND x_return_status = FND_API.G_RET_STS_SUCCESS) then
1140: IF g_fnd_debug = 'Y' THEN
1141: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1142: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, g_module_prefix || l_api_name ||
1143: '.invoked', 'Type: ' ||
1140: IF g_fnd_debug = 'Y' THEN
1141: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1142: FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, g_module_prefix || l_api_name ||
1143: '.invoked', 'Type: ' ||
1144: ', Save Count : ' || TO_CHAR(l_po_change_requests.count()));
1145: END IF;
1146: END IF;
1147:
1148:
1156: x_return_status => x_return_status,
1157: p_po_header_id => p_po_header_id,
1158: p_po_release_id => p_po_release_id,
1159: p_revision_num => p_revision_num,
1160: p_po_change_requests => l_po_change_requests,
1161: x_request_group_id => l_request_group_id,
1162: p_chn_int_cont_num => p_chn_int_cont_num,
1163: p_chn_source => p_chn_source,
1164: p_chn_requestor_username => p_chn_requestor_username,
1347:
1348: update po_line_locations_all
1349: set approved_flag = 'R'
1350: where line_location_id in (select document_line_location_id
1351: from po_change_requests
1352: where request_level = 'SHIPMENT' and
1353: document_header_id = p_po_header_id and
1354: action_type in ('MODIFICATION','CANCELLATION') and
1355: initiator = 'SUPPLIER' and
1365:
1366: update po_line_locations_all
1367: set approved_flag = 'R'
1368: where po_header_id in (select document_header_id
1369: from po_change_requests
1370: where request_level = 'HEADER' and
1371: document_header_id = p_po_header_id and
1372: action_type ='CANCELLATION' and
1373: initiator = 'SUPPLIER' and
1376:
1377: update po_line_locations_all
1378: set approved_flag = 'R'
1379: where po_line_id in (select document_line_id
1380: from po_change_requests
1381: where request_level = 'LINE' and
1382: document_header_id = p_po_header_id and
1383: request_status = 'PENDING' and
1384: initiator = 'SUPPLIER' and
1388: and po_release_id is null; --This condition added for bug 8768745
1389: /* jai
1390: and
1391: line_location_id not in (select document_line_location_id
1392: from po_change_requests
1393: where request_level = 'SHIPMENT' and
1394: document_header_id = p_po_header_id and
1395: action_type = 'CANCELLATION' and
1396: initiator = 'SUPPLIER' and
1415: -- changed to prevent receiving
1416: update po_line_locations_all
1417: set approved_flag = 'R'
1418: where line_location_id in (select document_line_location_id
1419: from po_change_requests
1420: where request_level = 'SHIPMENT' and
1421: po_release_id = p_po_release_id and
1422: action_type in ('MODIFICATION','CANCELLATION') and
1423: initiator = 'SUPPLIER' and
1427: --New DBI request
1428: update po_line_locations_all
1429: set approved_flag = 'R'
1430: where po_release_id in (select po_release_id
1431: from po_change_requests
1432: where request_level = 'HEADER' and
1433: po_release_id = p_po_release_id and
1434: action_type ='CANCELLATION' and
1435: initiator = 'SUPPLIER' and
1470: x_msg_data OUT NOCOPY VARCHAR2,
1471: p_po_header_id IN number,
1472: p_po_release_id IN number,
1473: p_revision_num IN number,
1474: p_po_change_requests IN OUT NOCOPY pos_chg_rec_tbl,
1475: x_online_report_id OUT NOCOPY number,
1476: x_pos_errors OUT NOCOPY pos_err_type,
1477: x_doc_check_error_msg OUT NOCOPY Doc_Check_Return_Type) is
1478:
1546: lLine number;
1547: changeOrig varchar2(1) := 'F';
1548: l_price_break_type VARCHAR2(1) := NULL;
1549: l_cumulative_flag BOOLEAN := false;
1550: l_initiator po_change_requests.initiator%type :='SUPPLIER';
1551: --<< Complex work changes for R12 >>
1552: progress_type_tbl PO_TBL_VARCHAR30:= PO_TBL_VARCHAR30();
1553: pay_description_tbl PO_TBL_VARCHAR240:= PO_TBL_VARCHAR240();
1554:
1628:
1629: -- Set the org context before calling core po api's
1630: PO_MOAC_UTILS_PVT.set_org_context(x_org_id) ; --
1631:
1632: rec_cnt := p_po_change_requests.count();
1633: -- Now check if the document references a global agreement
1634: -- or a blanket, then get the price accordingly
1635: if (rec_cnt > 0) then
1636: l_initiator:=p_po_change_requests(1).initiator;
1632: rec_cnt := p_po_change_requests.count();
1633: -- Now check if the document references a global agreement
1634: -- or a blanket, then get the price accordingly
1635: if (rec_cnt > 0) then
1636: l_initiator:=p_po_change_requests(1).initiator;
1637: end if;
1638: if (rec_cnt > 0) then
1639: FOR i in 1..rec_cnt
1640: LOOP
1638: if (rec_cnt > 0) then
1639: FOR i in 1..rec_cnt
1640: LOOP
1641: --Construct the table of line record
1642: if(p_po_change_requests(i).request_level = 'LINE') then
1643: poLineIdTbl.extend; unitPriceTbl.extend; supItemTbl.extend;
1644: startdateTbl.extend;expirationdateTbl.extend;amountTbl.extend;
1645:
1646: line_cnt := line_cnt + 1;
1643: poLineIdTbl.extend; unitPriceTbl.extend; supItemTbl.extend;
1644: startdateTbl.extend;expirationdateTbl.extend;amountTbl.extend;
1645:
1646: line_cnt := line_cnt + 1;
1647: poLineIdTbl(line_cnt) := p_po_change_requests(i).document_line_id;
1648: unitPriceTbl(line_cnt) := p_po_change_requests(i).new_price;
1649: supItemTbl(line_cnt) := p_po_change_requests(i).new_supplier_part_number;
1650: startdateTbl(line_cnt) := p_po_change_requests(i).new_start_date;
1651: expirationdateTbl(line_cnt) := p_po_change_requests(i).new_expiration_date;
1644: startdateTbl.extend;expirationdateTbl.extend;amountTbl.extend;
1645:
1646: line_cnt := line_cnt + 1;
1647: poLineIdTbl(line_cnt) := p_po_change_requests(i).document_line_id;
1648: unitPriceTbl(line_cnt) := p_po_change_requests(i).new_price;
1649: supItemTbl(line_cnt) := p_po_change_requests(i).new_supplier_part_number;
1650: startdateTbl(line_cnt) := p_po_change_requests(i).new_start_date;
1651: expirationdateTbl(line_cnt) := p_po_change_requests(i).new_expiration_date;
1652: amountTbl(line_cnt) := p_po_change_requests(i).new_amount;
1645:
1646: line_cnt := line_cnt + 1;
1647: poLineIdTbl(line_cnt) := p_po_change_requests(i).document_line_id;
1648: unitPriceTbl(line_cnt) := p_po_change_requests(i).new_price;
1649: supItemTbl(line_cnt) := p_po_change_requests(i).new_supplier_part_number;
1650: startdateTbl(line_cnt) := p_po_change_requests(i).new_start_date;
1651: expirationdateTbl(line_cnt) := p_po_change_requests(i).new_expiration_date;
1652: amountTbl(line_cnt) := p_po_change_requests(i).new_amount;
1653: end if; -- end if line
1646: line_cnt := line_cnt + 1;
1647: poLineIdTbl(line_cnt) := p_po_change_requests(i).document_line_id;
1648: unitPriceTbl(line_cnt) := p_po_change_requests(i).new_price;
1649: supItemTbl(line_cnt) := p_po_change_requests(i).new_supplier_part_number;
1650: startdateTbl(line_cnt) := p_po_change_requests(i).new_start_date;
1651: expirationdateTbl(line_cnt) := p_po_change_requests(i).new_expiration_date;
1652: amountTbl(line_cnt) := p_po_change_requests(i).new_amount;
1653: end if; -- end if line
1654:
1647: poLineIdTbl(line_cnt) := p_po_change_requests(i).document_line_id;
1648: unitPriceTbl(line_cnt) := p_po_change_requests(i).new_price;
1649: supItemTbl(line_cnt) := p_po_change_requests(i).new_supplier_part_number;
1650: startdateTbl(line_cnt) := p_po_change_requests(i).new_start_date;
1651: expirationdateTbl(line_cnt) := p_po_change_requests(i).new_expiration_date;
1652: amountTbl(line_cnt) := p_po_change_requests(i).new_amount;
1653: end if; -- end if line
1654:
1655: -- do not send cancellation request
1648: unitPriceTbl(line_cnt) := p_po_change_requests(i).new_price;
1649: supItemTbl(line_cnt) := p_po_change_requests(i).new_supplier_part_number;
1650: startdateTbl(line_cnt) := p_po_change_requests(i).new_start_date;
1651: expirationdateTbl(line_cnt) := p_po_change_requests(i).new_expiration_date;
1652: amountTbl(line_cnt) := p_po_change_requests(i).new_amount;
1653: end if; -- end if line
1654:
1655: -- do not send cancellation request
1656: if(p_po_change_requests(i).request_level = 'SHIPMENT' AND
1652: amountTbl(line_cnt) := p_po_change_requests(i).new_amount;
1653: end if; -- end if line
1654:
1655: -- do not send cancellation request
1656: if(p_po_change_requests(i).request_level = 'SHIPMENT' AND
1657: (p_po_change_requests(i).action_type not in ('ACCEPT','REJECT','CANCEL'))) then
1658: ship_cnt := ship_cnt + 1;
1659: poLineLocIdTbl.extend; quantityTbl.extend;
1660: promisedDateTbl.extend;priceOverrideTbl.extend;
1653: end if; -- end if line
1654:
1655: -- do not send cancellation request
1656: if(p_po_change_requests(i).request_level = 'SHIPMENT' AND
1657: (p_po_change_requests(i).action_type not in ('ACCEPT','REJECT','CANCEL'))) then
1658: ship_cnt := ship_cnt + 1;
1659: poLineLocIdTbl.extend; quantityTbl.extend;
1660: promisedDateTbl.extend;priceOverrideTbl.extend;
1661: parentLineLocIdTbl.extend;
1665: pay_description_tbl.extend;
1666: -- if release / standard po referencing a GA/ Quotation
1667: -- Call Get Price Break API.
1668:
1669: --if ((p_po_change_requests(i).from_header_id is not null) or
1670: -- (p_po_change_requests(i).po_release_id is not null)) then
1671: if (p_po_change_requests(i).po_release_id is not null) then
1672:
1673: SELECT decode(price_break_lookup_code, 'CUMULATIVE', 'Y', 'N')
1666: -- if release / standard po referencing a GA/ Quotation
1667: -- Call Get Price Break API.
1668:
1669: --if ((p_po_change_requests(i).from_header_id is not null) or
1670: -- (p_po_change_requests(i).po_release_id is not null)) then
1671: if (p_po_change_requests(i).po_release_id is not null) then
1672:
1673: SELECT decode(price_break_lookup_code, 'CUMULATIVE', 'Y', 'N')
1674: INTO l_price_break_type
1667: -- Call Get Price Break API.
1668:
1669: --if ((p_po_change_requests(i).from_header_id is not null) or
1670: -- (p_po_change_requests(i).po_release_id is not null)) then
1671: if (p_po_change_requests(i).po_release_id is not null) then
1672:
1673: SELECT decode(price_break_lookup_code, 'CUMULATIVE', 'Y', 'N')
1674: INTO l_price_break_type
1675: FROM po_lines_all
1672:
1673: SELECT decode(price_break_lookup_code, 'CUMULATIVE', 'Y', 'N')
1674: INTO l_price_break_type
1675: FROM po_lines_all
1676: WHERE po_line_id = p_po_change_requests(i).document_line_id;
1677:
1678: IF (l_price_break_type = 'Y') THEN
1679: l_cumulative_flag := TRUE;
1680: ELSE
1680: ELSE
1681: l_cumulative_flag := FALSE;
1682: END IF;
1683:
1684: if(p_po_change_requests(i).new_price is null) then
1685: x_price := po_sourcing2_sv.get_break_price(
1686: nvl(p_po_change_requests(i).new_quantity,p_po_change_requests(i).old_quantity),
1687: p_po_change_requests(i).ship_to_organization_id,
1688: p_po_change_requests(i).ship_to_location_id,
1682: END IF;
1683:
1684: if(p_po_change_requests(i).new_price is null) then
1685: x_price := po_sourcing2_sv.get_break_price(
1686: nvl(p_po_change_requests(i).new_quantity,p_po_change_requests(i).old_quantity),
1687: p_po_change_requests(i).ship_to_organization_id,
1688: p_po_change_requests(i).ship_to_location_id,
1689: p_po_change_requests(i).document_line_id,
1690: l_cumulative_flag,
1683:
1684: if(p_po_change_requests(i).new_price is null) then
1685: x_price := po_sourcing2_sv.get_break_price(
1686: nvl(p_po_change_requests(i).new_quantity,p_po_change_requests(i).old_quantity),
1687: p_po_change_requests(i).ship_to_organization_id,
1688: p_po_change_requests(i).ship_to_location_id,
1689: p_po_change_requests(i).document_line_id,
1690: l_cumulative_flag,
1691: nvl(p_po_change_requests(i).new_need_by_date,p_po_change_requests(i).old_need_by_date), -- need_by_date
1684: if(p_po_change_requests(i).new_price is null) then
1685: x_price := po_sourcing2_sv.get_break_price(
1686: nvl(p_po_change_requests(i).new_quantity,p_po_change_requests(i).old_quantity),
1687: p_po_change_requests(i).ship_to_organization_id,
1688: p_po_change_requests(i).ship_to_location_id,
1689: p_po_change_requests(i).document_line_id,
1690: l_cumulative_flag,
1691: nvl(p_po_change_requests(i).new_need_by_date,p_po_change_requests(i).old_need_by_date), -- need_by_date
1692: p_po_change_requests(i).document_line_location_id);
1685: x_price := po_sourcing2_sv.get_break_price(
1686: nvl(p_po_change_requests(i).new_quantity,p_po_change_requests(i).old_quantity),
1687: p_po_change_requests(i).ship_to_organization_id,
1688: p_po_change_requests(i).ship_to_location_id,
1689: p_po_change_requests(i).document_line_id,
1690: l_cumulative_flag,
1691: nvl(p_po_change_requests(i).new_need_by_date,p_po_change_requests(i).old_need_by_date), -- need_by_date
1692: p_po_change_requests(i).document_line_location_id);
1693: p_po_change_requests(i).old_price := x_price;
1687: p_po_change_requests(i).ship_to_organization_id,
1688: p_po_change_requests(i).ship_to_location_id,
1689: p_po_change_requests(i).document_line_id,
1690: l_cumulative_flag,
1691: nvl(p_po_change_requests(i).new_need_by_date,p_po_change_requests(i).old_need_by_date), -- need_by_date
1692: p_po_change_requests(i).document_line_location_id);
1693: p_po_change_requests(i).old_price := x_price;
1694: end if;
1695: end if; -- end if release
1688: p_po_change_requests(i).ship_to_location_id,
1689: p_po_change_requests(i).document_line_id,
1690: l_cumulative_flag,
1691: nvl(p_po_change_requests(i).new_need_by_date,p_po_change_requests(i).old_need_by_date), -- need_by_date
1692: p_po_change_requests(i).document_line_location_id);
1693: p_po_change_requests(i).old_price := x_price;
1694: end if;
1695: end if; -- end if release
1696:
1689: p_po_change_requests(i).document_line_id,
1690: l_cumulative_flag,
1691: nvl(p_po_change_requests(i).new_need_by_date,p_po_change_requests(i).old_need_by_date), -- need_by_date
1692: p_po_change_requests(i).document_line_location_id);
1693: p_po_change_requests(i).old_price := x_price;
1694: end if;
1695: end if; -- end if release
1696:
1697: poLineLocIdTbl(ship_cnt) := p_po_change_requests(i).document_line_location_id;
1693: p_po_change_requests(i).old_price := x_price;
1694: end if;
1695: end if; -- end if release
1696:
1697: poLineLocIdTbl(ship_cnt) := p_po_change_requests(i).document_line_location_id;
1698: parentLineLocIdTbl(ship_cnt) := p_po_change_requests(i).parent_line_location_id;
1699: quantityTbl(ship_cnt) := p_po_change_requests(i).new_quantity;
1700: promisedDateTbl(ship_cnt) := p_po_change_requests(i).new_promised_date;
1701: priceOverrideTbl(ship_cnt) := nvl(p_po_change_requests(i).new_price,x_price);
1694: end if;
1695: end if; -- end if release
1696:
1697: poLineLocIdTbl(ship_cnt) := p_po_change_requests(i).document_line_location_id;
1698: parentLineLocIdTbl(ship_cnt) := p_po_change_requests(i).parent_line_location_id;
1699: quantityTbl(ship_cnt) := p_po_change_requests(i).new_quantity;
1700: promisedDateTbl(ship_cnt) := p_po_change_requests(i).new_promised_date;
1701: priceOverrideTbl(ship_cnt) := nvl(p_po_change_requests(i).new_price,x_price);
1702: shipmentNumTbl(ship_cnt) := p_po_change_requests(i).document_shipment_number;
1695: end if; -- end if release
1696:
1697: poLineLocIdTbl(ship_cnt) := p_po_change_requests(i).document_line_location_id;
1698: parentLineLocIdTbl(ship_cnt) := p_po_change_requests(i).parent_line_location_id;
1699: quantityTbl(ship_cnt) := p_po_change_requests(i).new_quantity;
1700: promisedDateTbl(ship_cnt) := p_po_change_requests(i).new_promised_date;
1701: priceOverrideTbl(ship_cnt) := nvl(p_po_change_requests(i).new_price,x_price);
1702: shipmentNumTbl(ship_cnt) := p_po_change_requests(i).document_shipment_number;
1703: progress_type_tbl(ship_cnt) := p_po_change_requests(i).new_progress_type;
1696:
1697: poLineLocIdTbl(ship_cnt) := p_po_change_requests(i).document_line_location_id;
1698: parentLineLocIdTbl(ship_cnt) := p_po_change_requests(i).parent_line_location_id;
1699: quantityTbl(ship_cnt) := p_po_change_requests(i).new_quantity;
1700: promisedDateTbl(ship_cnt) := p_po_change_requests(i).new_promised_date;
1701: priceOverrideTbl(ship_cnt) := nvl(p_po_change_requests(i).new_price,x_price);
1702: shipmentNumTbl(ship_cnt) := p_po_change_requests(i).document_shipment_number;
1703: progress_type_tbl(ship_cnt) := p_po_change_requests(i).new_progress_type;
1704: pay_description_tbl(ship_cnt):= p_po_change_requests(i).new_pay_description;
1697: poLineLocIdTbl(ship_cnt) := p_po_change_requests(i).document_line_location_id;
1698: parentLineLocIdTbl(ship_cnt) := p_po_change_requests(i).parent_line_location_id;
1699: quantityTbl(ship_cnt) := p_po_change_requests(i).new_quantity;
1700: promisedDateTbl(ship_cnt) := p_po_change_requests(i).new_promised_date;
1701: priceOverrideTbl(ship_cnt) := nvl(p_po_change_requests(i).new_price,x_price);
1702: shipmentNumTbl(ship_cnt) := p_po_change_requests(i).document_shipment_number;
1703: progress_type_tbl(ship_cnt) := p_po_change_requests(i).new_progress_type;
1704: pay_description_tbl(ship_cnt):= p_po_change_requests(i).new_pay_description;
1705: shipamountTbl(ship_cnt):= p_po_change_requests(i).new_amount;
1698: parentLineLocIdTbl(ship_cnt) := p_po_change_requests(i).parent_line_location_id;
1699: quantityTbl(ship_cnt) := p_po_change_requests(i).new_quantity;
1700: promisedDateTbl(ship_cnt) := p_po_change_requests(i).new_promised_date;
1701: priceOverrideTbl(ship_cnt) := nvl(p_po_change_requests(i).new_price,x_price);
1702: shipmentNumTbl(ship_cnt) := p_po_change_requests(i).document_shipment_number;
1703: progress_type_tbl(ship_cnt) := p_po_change_requests(i).new_progress_type;
1704: pay_description_tbl(ship_cnt):= p_po_change_requests(i).new_pay_description;
1705: shipamountTbl(ship_cnt):= p_po_change_requests(i).new_amount;
1706: end if; -- if shipment
1699: quantityTbl(ship_cnt) := p_po_change_requests(i).new_quantity;
1700: promisedDateTbl(ship_cnt) := p_po_change_requests(i).new_promised_date;
1701: priceOverrideTbl(ship_cnt) := nvl(p_po_change_requests(i).new_price,x_price);
1702: shipmentNumTbl(ship_cnt) := p_po_change_requests(i).document_shipment_number;
1703: progress_type_tbl(ship_cnt) := p_po_change_requests(i).new_progress_type;
1704: pay_description_tbl(ship_cnt):= p_po_change_requests(i).new_pay_description;
1705: shipamountTbl(ship_cnt):= p_po_change_requests(i).new_amount;
1706: end if; -- if shipment
1707:
1700: promisedDateTbl(ship_cnt) := p_po_change_requests(i).new_promised_date;
1701: priceOverrideTbl(ship_cnt) := nvl(p_po_change_requests(i).new_price,x_price);
1702: shipmentNumTbl(ship_cnt) := p_po_change_requests(i).document_shipment_number;
1703: progress_type_tbl(ship_cnt) := p_po_change_requests(i).new_progress_type;
1704: pay_description_tbl(ship_cnt):= p_po_change_requests(i).new_pay_description;
1705: shipamountTbl(ship_cnt):= p_po_change_requests(i).new_amount;
1706: end if; -- if shipment
1707:
1708: if (p_po_change_requests(i).request_level = 'DISTRIBUTION') then
1701: priceOverrideTbl(ship_cnt) := nvl(p_po_change_requests(i).new_price,x_price);
1702: shipmentNumTbl(ship_cnt) := p_po_change_requests(i).document_shipment_number;
1703: progress_type_tbl(ship_cnt) := p_po_change_requests(i).new_progress_type;
1704: pay_description_tbl(ship_cnt):= p_po_change_requests(i).new_pay_description;
1705: shipamountTbl(ship_cnt):= p_po_change_requests(i).new_amount;
1706: end if; -- if shipment
1707:
1708: if (p_po_change_requests(i).request_level = 'DISTRIBUTION') then
1709: dist_cnt := dist_cnt + 1;
1704: pay_description_tbl(ship_cnt):= p_po_change_requests(i).new_pay_description;
1705: shipamountTbl(ship_cnt):= p_po_change_requests(i).new_amount;
1706: end if; -- if shipment
1707:
1708: if (p_po_change_requests(i).request_level = 'DISTRIBUTION') then
1709: dist_cnt := dist_cnt + 1;
1710: distIdTbl.extend; distQtyTbl.extend; distAmtTbl.extend; -- FPS Changes
1711: distIdTbl(dist_cnt) := p_po_change_requests(i).document_distribution_id;
1712: distQtyTbl(dist_cnt) := p_po_change_requests(i).new_quantity;
1707:
1708: if (p_po_change_requests(i).request_level = 'DISTRIBUTION') then
1709: dist_cnt := dist_cnt + 1;
1710: distIdTbl.extend; distQtyTbl.extend; distAmtTbl.extend; -- FPS Changes
1711: distIdTbl(dist_cnt) := p_po_change_requests(i).document_distribution_id;
1712: distQtyTbl(dist_cnt) := p_po_change_requests(i).new_quantity;
1713: distAmtTbl(dist_cnt) := p_po_change_requests(i).new_amount; -- FPS Changes
1714:
1715: end if; -- if dist
1708: if (p_po_change_requests(i).request_level = 'DISTRIBUTION') then
1709: dist_cnt := dist_cnt + 1;
1710: distIdTbl.extend; distQtyTbl.extend; distAmtTbl.extend; -- FPS Changes
1711: distIdTbl(dist_cnt) := p_po_change_requests(i).document_distribution_id;
1712: distQtyTbl(dist_cnt) := p_po_change_requests(i).new_quantity;
1713: distAmtTbl(dist_cnt) := p_po_change_requests(i).new_amount; -- FPS Changes
1714:
1715: end if; -- if dist
1716:
1709: dist_cnt := dist_cnt + 1;
1710: distIdTbl.extend; distQtyTbl.extend; distAmtTbl.extend; -- FPS Changes
1711: distIdTbl(dist_cnt) := p_po_change_requests(i).document_distribution_id;
1712: distQtyTbl(dist_cnt) := p_po_change_requests(i).new_quantity;
1713: distAmtTbl(dist_cnt) := p_po_change_requests(i).new_amount; -- FPS Changes
1714:
1715: end if; -- if dist
1716:
1717: --end if; -- end of rec count
1737: SKIP_LINE := 0;
1738:
1739: FOR I IN 1..REC_CNT LOOP
1740:
1741: IF ( P_PO_CHANGE_REQUESTS(I).REQUEST_LEVEL = 'LINE' AND
1742: P_PO_CHANGE_REQUESTS(I).DOCUMENT_LINE_ID = L_GA_LINEID AND
1743: NVL(P_PO_CHANGE_REQUESTS(I).NEW_PRICE,-1) <> NVL(P_PO_CHANGE_REQUESTS(I).OLD_PRICE,-1) )
1744: THEN
1745: SKIP_LINE := 1;
1738:
1739: FOR I IN 1..REC_CNT LOOP
1740:
1741: IF ( P_PO_CHANGE_REQUESTS(I).REQUEST_LEVEL = 'LINE' AND
1742: P_PO_CHANGE_REQUESTS(I).DOCUMENT_LINE_ID = L_GA_LINEID AND
1743: NVL(P_PO_CHANGE_REQUESTS(I).NEW_PRICE,-1) <> NVL(P_PO_CHANGE_REQUESTS(I).OLD_PRICE,-1) )
1744: THEN
1745: SKIP_LINE := 1;
1746:
1739: FOR I IN 1..REC_CNT LOOP
1740:
1741: IF ( P_PO_CHANGE_REQUESTS(I).REQUEST_LEVEL = 'LINE' AND
1742: P_PO_CHANGE_REQUESTS(I).DOCUMENT_LINE_ID = L_GA_LINEID AND
1743: NVL(P_PO_CHANGE_REQUESTS(I).NEW_PRICE,-1) <> NVL(P_PO_CHANGE_REQUESTS(I).OLD_PRICE,-1) )
1744: THEN
1745: SKIP_LINE := 1;
1746:
1747: END IF;
1762: exit when ga_ship_csr%notfound;
1763: changeOrig := 'F';
1764: FOR i in 1..rec_cnt LOOP
1765:
1766: if (p_po_change_requests(i).request_level = 'SHIPMENT' and
1767: p_po_change_requests(i).action_type = 'MODIFICATION' ) then
1768:
1769: if(p_po_change_requests(i).document_line_location_id = l_ga_lineLocId and
1770: p_po_change_requests(i).new_quantity is not null and
1763: changeOrig := 'F';
1764: FOR i in 1..rec_cnt LOOP
1765:
1766: if (p_po_change_requests(i).request_level = 'SHIPMENT' and
1767: p_po_change_requests(i).action_type = 'MODIFICATION' ) then
1768:
1769: if(p_po_change_requests(i).document_line_location_id = l_ga_lineLocId and
1770: p_po_change_requests(i).new_quantity is not null and
1771: p_po_change_requests(i).parent_line_location_id is null ) then
1765:
1766: if (p_po_change_requests(i).request_level = 'SHIPMENT' and
1767: p_po_change_requests(i).action_type = 'MODIFICATION' ) then
1768:
1769: if(p_po_change_requests(i).document_line_location_id = l_ga_lineLocId and
1770: p_po_change_requests(i).new_quantity is not null and
1771: p_po_change_requests(i).parent_line_location_id is null ) then
1772:
1773: l_qty_orig := l_qty_orig + p_po_change_requests(i).new_quantity;
1766: if (p_po_change_requests(i).request_level = 'SHIPMENT' and
1767: p_po_change_requests(i).action_type = 'MODIFICATION' ) then
1768:
1769: if(p_po_change_requests(i).document_line_location_id = l_ga_lineLocId and
1770: p_po_change_requests(i).new_quantity is not null and
1771: p_po_change_requests(i).parent_line_location_id is null ) then
1772:
1773: l_qty_orig := l_qty_orig + p_po_change_requests(i).new_quantity;
1774: changeOrig := 'T';
1767: p_po_change_requests(i).action_type = 'MODIFICATION' ) then
1768:
1769: if(p_po_change_requests(i).document_line_location_id = l_ga_lineLocId and
1770: p_po_change_requests(i).new_quantity is not null and
1771: p_po_change_requests(i).parent_line_location_id is null ) then
1772:
1773: l_qty_orig := l_qty_orig + p_po_change_requests(i).new_quantity;
1774: changeOrig := 'T';
1775: end if;
1769: if(p_po_change_requests(i).document_line_location_id = l_ga_lineLocId and
1770: p_po_change_requests(i).new_quantity is not null and
1771: p_po_change_requests(i).parent_line_location_id is null ) then
1772:
1773: l_qty_orig := l_qty_orig + p_po_change_requests(i).new_quantity;
1774: changeOrig := 'T';
1775: end if;
1776: -- Sum up all the split quantities
1777: if (p_po_change_requests(i).parent_line_location_id is not null and
1773: l_qty_orig := l_qty_orig + p_po_change_requests(i).new_quantity;
1774: changeOrig := 'T';
1775: end if;
1776: -- Sum up all the split quantities
1777: if (p_po_change_requests(i).parent_line_location_id is not null and
1778: p_po_change_requests(i).parent_line_location_id = l_ga_lineLocId and
1779: p_po_change_requests(i).new_quantity is not null) then
1780:
1781: l_qty_split := l_qty_split + p_po_change_requests(i).new_quantity;
1774: changeOrig := 'T';
1775: end if;
1776: -- Sum up all the split quantities
1777: if (p_po_change_requests(i).parent_line_location_id is not null and
1778: p_po_change_requests(i).parent_line_location_id = l_ga_lineLocId and
1779: p_po_change_requests(i).new_quantity is not null) then
1780:
1781: l_qty_split := l_qty_split + p_po_change_requests(i).new_quantity;
1782:
1775: end if;
1776: -- Sum up all the split quantities
1777: if (p_po_change_requests(i).parent_line_location_id is not null and
1778: p_po_change_requests(i).parent_line_location_id = l_ga_lineLocId and
1779: p_po_change_requests(i).new_quantity is not null) then
1780:
1781: l_qty_split := l_qty_split + p_po_change_requests(i).new_quantity;
1782:
1783: end if;
1777: if (p_po_change_requests(i).parent_line_location_id is not null and
1778: p_po_change_requests(i).parent_line_location_id = l_ga_lineLocId and
1779: p_po_change_requests(i).new_quantity is not null) then
1780:
1781: l_qty_split := l_qty_split + p_po_change_requests(i).new_quantity;
1782:
1783: end if;
1784: end if ; -- if shipment change
1785: END LOOP;
1980: x_msg_data OUT NOCOPY VARCHAR2,
1981: p_po_header_id IN number,
1982: p_po_release_id IN number,
1983: p_revision_num IN number,
1984: p_po_change_requests IN OUT NOCOPY pos_chg_rec_tbl,
1985: x_online_report_id OUT NOCOPY number,
1986: x_pos_errors OUT NOCOPY pos_err_type) is
1987:
1988: l_doc_check_error_msg Doc_Check_Return_Type := NULL;
1988: l_doc_check_error_msg Doc_Check_Return_Type := NULL;
1989: l_msg_data varchar2(2000) := NULL;
1990:
1991: BEGIN
1992: --l_po_change_requests := p_po_change_requests;
1993:
1994: validate_change_request (
1995: p_api_version => 1.0,
1996: p_init_msg_list => FND_API.G_FALSE,
1998: x_msg_data => l_msg_data,
1999: p_po_header_id => p_po_header_id,
2000: p_po_release_id => p_po_release_id,
2001: p_revision_num => p_revision_num,
2002: p_po_change_requests => p_po_change_requests,
2003: x_online_report_id => x_online_report_id,
2004: x_pos_errors => x_pos_errors,
2005: x_doc_check_error_msg => l_doc_check_error_msg);
2006:
2474: x_return_status OUT NOCOPY VARCHAR2,
2475: p_po_header_id IN number,
2476: p_po_release_id IN number,
2477: p_revision_num IN number,
2478: p_po_change_requests IN pos_chg_rec_tbl,
2479: x_request_group_id OUT NOCOPY NUMBER) is
2480:
2481: l_api_name CONSTANT VARCHAR2(30) := 'save_cancel_request';
2482: l_api_version_number CONSTANT NUMBER := 1.0;
2506: x_return_status => x_return_status,
2507: p_po_header_id => p_po_header_id,
2508: p_po_release_id => p_po_release_id,
2509: p_revision_num => p_revision_num,
2510: p_po_change_requests => p_po_change_requests,
2511: x_request_group_id => l_request_group_id);
2512:
2513: -- Call Update PO Procedure to set PO in IN PROCESS
2514: update_po_attributes(p_po_header_id,
2638: l_revision_num number;
2639:
2640: cursor c1(p_po_header_id in number) is
2641: select change_request_group_id,DOCUMENT_REVISION_NUM
2642: from po_change_requests
2643: where document_header_id = p_po_header_id and
2644: document_type = 'PO' and
2645: change_active_flag= 'Y' and
2646: initiator = 'SUPPLIER' and
2647: request_status not in ('ACCEPTED', 'REJECTED');
2648:
2649: cursor c2(p_po_release_id in number) is
2650: select change_request_group_id,DOCUMENT_REVISION_NUM
2651: from po_change_requests
2652: where po_release_id = p_po_release_id and
2653: document_type = 'RELEASE' and
2654: change_active_flag= 'Y' and
2655: initiator = 'SUPPLIER' and
2682: if (xGrpId > 0) then
2683: l_cancel_msg := fnd_message.get_string('POS','POS_AUTO_CANCEL_BY_BUYER') ;
2684: if (p_po_release_id is not null and p_po_line_id is null and p_po_line_location_id is null) then
2685: begin
2686: update po_change_requests
2687: set request_status = 'REJECTED',change_active_flag = 'N',
2688: request_reason=l_cancel_msg
2689: where po_release_id = p_po_release_id and
2690: initiator = 'SUPPLIER' and
2690: initiator = 'SUPPLIER' and
2691: request_status not in ('ACCEPTED','REJECTED') and
2692: action_type='MODIFICATION';
2693:
2694: update po_change_requests
2695: set request_status = 'ACCEPTED',
2696: change_active_flag = 'N'
2697: where initiator = 'SUPPLIER' and
2698: request_status not in ('ACCEPTED','REJECTED') and
2705: end if;
2706:
2707: if (p_po_header_id is not null and p_po_line_id is null and p_po_line_location_id is null) then
2708: begin
2709: update po_change_requests
2710: set request_status = 'REJECTED',
2711: change_active_flag = 'N',
2712: request_reason=l_cancel_msg
2713: where document_header_id = p_po_header_id and
2714: request_status not in ('ACCEPTED','REJECTED') and
2715: initiator = 'SUPPLIER' and
2716: action_type='MODIFICATION';
2717:
2718: update po_change_requests
2719: set request_status = 'ACCEPTED',
2720: change_active_flag = 'N'
2721: where request_status not in ('ACCEPTED','REJECTED') and
2722: initiator = 'SUPPLIER' and
2729: end if;
2730:
2731: if (p_po_line_location_id is not null ) then
2732: begin
2733: update po_change_requests
2734: set request_status = 'REJECTED',change_active_flag = 'N',request_reason=l_cancel_msg
2735: where document_line_location_id = p_po_line_location_id and
2736: request_level = 'SHIPMENT' and
2737: request_status not in ('ACCEPTED','REJECTED') and
2737: request_status not in ('ACCEPTED','REJECTED') and
2738: initiator = 'SUPPLIER' and
2739: action_type='MODIFICATION';
2740:
2741: update po_change_requests
2742: set request_status = 'ACCEPTED',change_active_flag = 'N'
2743: where document_line_location_id = p_po_line_location_id and
2744: request_level = 'SHIPMENT' and
2745: request_status not in ('ACCEPTED','REJECTED') and
2753: end if;
2754:
2755: if (p_po_line_id is not null and p_po_line_location_id is null ) then
2756: begin
2757: update po_change_requests
2758: set request_status='REJECTED',change_active_flag='N',request_reason=l_cancel_msg
2759: where document_line_id = p_po_line_id and
2760: request_status not in ('ACCEPTED','REJECTED') and
2761: initiator = 'SUPPLIER' and
2760: request_status not in ('ACCEPTED','REJECTED') and
2761: initiator = 'SUPPLIER' and
2762: action_type='MODIFICATION';
2763:
2764: update po_change_requests
2765: set request_status='ACCEPTED',change_active_flag='N'
2766: where document_line_id = p_po_line_id and
2767: request_status not in ('ACCEPTED','REJECTED') and
2768: initiator = 'SUPPLIER' and
2804: exception
2805: when others then
2806: raise;
2807: end;
2808: update po_change_requests
2809: set document_revision_num = l_revision_num
2810: where po_release_id = p_po_release_id and
2811: request_status not in ('ACCEPTED','REJECTED') and
2812: document_type = 'RELEASE' and
2841: exception
2842: when others then
2843: raise;
2844: end;
2845: update po_change_requests
2846: set document_revision_num=l_revision_num
2847: where document_header_id = p_po_header_id and
2848: request_status not in ('ACCEPTED','REJECTED') and
2849: document_type = 'PO' and
3535: /*Added release_id as part of fix for bug 12903291 */
3536: procedure validate_shipment_cancel (
3537: p_po_header_id IN number,
3538: p_po_release_id IN number,
3539: p_po_change_requests IN pos_chg_rec_tbl,
3540: x_pos_errors OUT NOCOPY POS_ERR_TYPE,
3541: x_ret_sts OUT NOCOPY varchar2
3542: ) IS
3543:
3540: x_pos_errors OUT NOCOPY POS_ERR_TYPE,
3541: x_ret_sts OUT NOCOPY varchar2
3542: ) IS
3543:
3544: l_po_change_requests pos_chg_rec_tbl := NULL;
3545: l_count_asn NUMBER;
3546: l_err_msg_name_tbl po_tbl_varchar30;
3547: l_err_msg_text_tbl po_tbl_varchar2000;
3548: l_err_count NUMBER;
3548: l_err_count NUMBER;
3549:
3550: BEGIN
3551: l_err_count := 0;
3552: l_po_change_requests := p_po_change_requests ;
3553: l_err_msg_name_tbl := po_tbl_varchar30();
3554: l_err_msg_text_tbl := po_tbl_varchar2000();
3555: x_pos_errors := POS_ERR_TYPE( l_err_msg_name_tbl,l_err_msg_text_tbl);
3556: x_ret_sts := 'N';
3553: l_err_msg_name_tbl := po_tbl_varchar30();
3554: l_err_msg_text_tbl := po_tbl_varchar2000();
3555: x_pos_errors := POS_ERR_TYPE( l_err_msg_name_tbl,l_err_msg_text_tbl);
3556: x_ret_sts := 'N';
3557: FOR j in 1..l_po_change_requests.count()
3558: LOOP
3559: if ( l_po_change_requests(j).action_type in ('CANCELLATION')) then --AND
3560: -- l_po_change_requests(j).request_level='SHIPMENT' ) then
3561: IF(p_po_header_id IS NOT NULL AND p_po_release_id IS NOT NULL) THEN
3555: x_pos_errors := POS_ERR_TYPE( l_err_msg_name_tbl,l_err_msg_text_tbl);
3556: x_ret_sts := 'N';
3557: FOR j in 1..l_po_change_requests.count()
3558: LOOP
3559: if ( l_po_change_requests(j).action_type in ('CANCELLATION')) then --AND
3560: -- l_po_change_requests(j).request_level='SHIPMENT' ) then
3561: IF(p_po_header_id IS NOT NULL AND p_po_release_id IS NOT NULL) THEN
3562: select count(*)
3563: into l_count_asn
3556: x_ret_sts := 'N';
3557: FOR j in 1..l_po_change_requests.count()
3558: LOOP
3559: if ( l_po_change_requests(j).action_type in ('CANCELLATION')) then --AND
3560: -- l_po_change_requests(j).request_level='SHIPMENT' ) then
3561: IF(p_po_header_id IS NOT NULL AND p_po_release_id IS NOT NULL) THEN
3562: select count(*)
3563: into l_count_asn
3564: from RCV_TRANSACTIONS_INTERFACE rti
3566: rti.PROCESSING_STATUS_CODE = 'PENDING' and
3567: rti.quantity > 0 and
3568: rti.PO_HEADER_ID = p_po_header_id and
3569: rti.po_release_id (+) = p_po_release_id and
3570: (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);
3571: ELSE
3572: select count(*)
3573: into l_count_asn
3574: from RCV_TRANSACTIONS_INTERFACE rti
3575: where rti.TRANSACTION_TYPE = 'SHIP' and
3576: rti.PROCESSING_STATUS_CODE = 'PENDING' and
3577: rti.quantity > 0 and
3578: rti.PO_HEADER_ID = p_po_header_id AND
3579: (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);
3580: END IF;
3581:
3582: IF(l_count_asn > 0) then
3583: l_err_count := l_err_count + 1;
3583: l_err_count := l_err_count + 1;
3584: x_pos_errors.message_name.extend;
3585: x_pos_errors.text_line.extend;
3586: x_pos_errors.message_name(l_err_count) := null;
3587: if l_po_change_requests(j).document_line_location_id is not null then
3588: FND_MESSAGE.set_name('POS','POS_CAN_PO_LS_UNPRC_TX');
3589: fnd_message.set_token('LINE', l_po_change_requests(j).Document_Line_Number);
3590: fnd_message.set_token('SHIPMENT', l_po_change_requests(j).Document_Shipment_Number);
3591: x_pos_errors.text_line(l_err_count) := fnd_message.get;
3585: x_pos_errors.text_line.extend;
3586: x_pos_errors.message_name(l_err_count) := null;
3587: if l_po_change_requests(j).document_line_location_id is not null then
3588: FND_MESSAGE.set_name('POS','POS_CAN_PO_LS_UNPRC_TX');
3589: fnd_message.set_token('LINE', l_po_change_requests(j).Document_Line_Number);
3590: fnd_message.set_token('SHIPMENT', l_po_change_requests(j).Document_Shipment_Number);
3591: x_pos_errors.text_line(l_err_count) := fnd_message.get;
3592: else
3593: x_pos_errors.text_line(l_err_count) := fnd_message.get_String('POS', 'POS_CAN_PO_UNPRC_TX');
3586: x_pos_errors.message_name(l_err_count) := null;
3587: if l_po_change_requests(j).document_line_location_id is not null then
3588: FND_MESSAGE.set_name('POS','POS_CAN_PO_LS_UNPRC_TX');
3589: fnd_message.set_token('LINE', l_po_change_requests(j).Document_Line_Number);
3590: fnd_message.set_token('SHIPMENT', l_po_change_requests(j).Document_Shipment_Number);
3591: x_pos_errors.text_line(l_err_count) := fnd_message.get;
3592: else
3593: x_pos_errors.text_line(l_err_count) := fnd_message.get_String('POS', 'POS_CAN_PO_UNPRC_TX');
3594: return;
3600: into l_count_asn
3601: FROM RCV_SHIPMENT_LINES RSL
3602: WHERE RSL.po_header_id = p_po_header_id
3603: AND RSL.po_release_id (+) = p_po_release_id
3604: 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)
3605: AND NVL(RSL.quantity_shipped,0) > NVL(RSL.quantity_received,0)
3606: AND NVL(RSL.ASN_LINE_FLAG,'N') = 'Y'
3607: AND NVL(RSL.SHIPMENT_LINE_STATUS_CODE,'EXPECTED') <> 'CANCELLED';
3608: ELSE
3609: SELECT count(*)
3610: into l_count_asn
3611: FROM RCV_SHIPMENT_LINES RSL
3612: WHERE RSL.po_header_id = p_po_header_id
3613: 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)
3614: AND NVL(RSL.quantity_shipped,0) > NVL(RSL.quantity_received,0)
3615: AND NVL(RSL.ASN_LINE_FLAG,'N') = 'Y'
3616: AND NVL(RSL.SHIPMENT_LINE_STATUS_CODE,'EXPECTED') <> 'CANCELLED';
3617: END IF;
3620: l_err_count := l_err_count + 1;
3621: x_pos_errors.message_name.extend;
3622: x_pos_errors.text_line.extend;
3623: x_pos_errors.message_name(l_err_count) := null;
3624: if l_po_change_requests(j).document_line_location_id is not null then
3625: FND_MESSAGE.set_name('POS','POS_CAN_PO_LS_OPEN_ASN');
3626: fnd_message.set_token('LINE', l_po_change_requests(j).Document_Line_Number);
3627: fnd_message.set_token('SHIPMENT', l_po_change_requests(j).Document_Shipment_Number);
3628: x_pos_errors.text_line(l_err_count) := fnd_message.get;
3622: x_pos_errors.text_line.extend;
3623: x_pos_errors.message_name(l_err_count) := null;
3624: if l_po_change_requests(j).document_line_location_id is not null then
3625: FND_MESSAGE.set_name('POS','POS_CAN_PO_LS_OPEN_ASN');
3626: fnd_message.set_token('LINE', l_po_change_requests(j).Document_Line_Number);
3627: fnd_message.set_token('SHIPMENT', l_po_change_requests(j).Document_Shipment_Number);
3628: x_pos_errors.text_line(l_err_count) := fnd_message.get;
3629: else
3630: x_pos_errors.text_line(l_err_count) := fnd_message.get_String('POS', 'POS_CAN_PO_OPEN_ASN') ;
3623: x_pos_errors.message_name(l_err_count) := null;
3624: if l_po_change_requests(j).document_line_location_id is not null then
3625: FND_MESSAGE.set_name('POS','POS_CAN_PO_LS_OPEN_ASN');
3626: fnd_message.set_token('LINE', l_po_change_requests(j).Document_Line_Number);
3627: fnd_message.set_token('SHIPMENT', l_po_change_requests(j).Document_Shipment_Number);
3628: x_pos_errors.text_line(l_err_count) := fnd_message.get;
3629: else
3630: x_pos_errors.text_line(l_err_count) := fnd_message.get_String('POS', 'POS_CAN_PO_OPEN_ASN') ;
3631: return;
3635: END LOOP;
3636: if(l_err_count < 1) then
3637: validate_ship_inv_cancel (
3638: p_po_header_id,
3639: p_po_change_requests,
3640: x_pos_errors,
3641: x_ret_sts);
3642: else
3643: x_ret_sts := 'Y';
3646: END validate_shipment_cancel;
3647:
3648: procedure validate_ship_inv_cancel (
3649: p_po_header_id IN number,
3650: p_po_change_requests IN pos_chg_rec_tbl,
3651: x_pos_errors OUT NOCOPY POS_ERR_TYPE,
3652: x_ret_sts OUT NOCOPY varchar2
3653: ) IS
3654:
3651: x_pos_errors OUT NOCOPY POS_ERR_TYPE,
3652: x_ret_sts OUT NOCOPY varchar2
3653: ) IS
3654:
3655: l_po_change_requests pos_chg_rec_tbl := NULL;
3656: l_count_asn NUMBER;
3657: l_err_msg_name_tbl po_tbl_varchar30;
3658: l_err_msg_text_tbl po_tbl_varchar2000;
3659: l_err_count NUMBER;
3661: l_quan_recd NUMBER;
3662: l_quan_billed NUMBER;
3663: BEGIN
3664: l_err_count := 0;
3665: l_po_change_requests := p_po_change_requests ;
3666: l_err_msg_name_tbl := po_tbl_varchar30();
3667: l_err_msg_text_tbl := po_tbl_varchar2000();
3668: x_pos_errors := POS_ERR_TYPE( l_err_msg_name_tbl,l_err_msg_text_tbl);
3669: FOR j in 1..l_po_change_requests.count()
3665: l_po_change_requests := p_po_change_requests ;
3666: l_err_msg_name_tbl := po_tbl_varchar30();
3667: l_err_msg_text_tbl := po_tbl_varchar2000();
3668: x_pos_errors := POS_ERR_TYPE( l_err_msg_name_tbl,l_err_msg_text_tbl);
3669: FOR j in 1..l_po_change_requests.count()
3670: LOOP
3671: if ( l_po_change_requests(j).action_type in ('CANCELLATION') AND
3672: l_po_change_requests(j).request_level='SHIPMENT' ) then
3673: l_quan_ordered := -1;
3667: l_err_msg_text_tbl := po_tbl_varchar2000();
3668: x_pos_errors := POS_ERR_TYPE( l_err_msg_name_tbl,l_err_msg_text_tbl);
3669: FOR j in 1..l_po_change_requests.count()
3670: LOOP
3671: if ( l_po_change_requests(j).action_type in ('CANCELLATION') AND
3672: l_po_change_requests(j).request_level='SHIPMENT' ) then
3673: l_quan_ordered := -1;
3674: l_quan_recd := -1;
3675: l_quan_billed := -1;
3668: x_pos_errors := POS_ERR_TYPE( l_err_msg_name_tbl,l_err_msg_text_tbl);
3669: FOR j in 1..l_po_change_requests.count()
3670: LOOP
3671: if ( l_po_change_requests(j).action_type in ('CANCELLATION') AND
3672: l_po_change_requests(j).request_level='SHIPMENT' ) then
3673: l_quan_ordered := -1;
3674: l_quan_recd := -1;
3675: l_quan_billed := -1;
3676: begin
3676: begin
3677: SELECT nvl(POLL.quantity, 0), nvl(POLL.quantity_billed, 0), nvl(POLL.quantity_received, 0)
3678: into l_quan_ordered, l_quan_billed, l_quan_recd
3679: FROM PO_LINE_LOCATIONS_ALL POLL, PO_LINES_ALL POL
3680: WHERE POLL.line_location_id = l_po_change_requests(j).document_line_location_id
3681: AND POLL.po_line_id = POL.po_line_id
3682: AND nvl(POLL.cancel_flag, 'N') = 'N'
3683: AND nvl(POLL.closed_code, 'OPEN') <> 'FINALLY CLOSED'
3684: AND nvl(POLL.receipt_required_flag, 'Y') <> 'N'
3693: x_pos_errors.message_name.extend;
3694: x_pos_errors.text_line.extend;
3695: x_pos_errors.message_name(l_err_count) := null;
3696: FND_MESSAGE.set_name('POS','POS_CAN_PO_QTY_BILL_RCV');
3697: fnd_message.set_token('LINE', l_po_change_requests(j).Document_Line_Number);
3698: fnd_message.set_token('SHIPMENT', l_po_change_requests(j).Document_Shipment_Number);
3699: fnd_message.set_token('QTY_BILL', l_quan_billed) ;
3700: fnd_message.set_token('QTY_RCV', l_quan_recd) ;
3701: x_pos_errors.text_line(l_err_count) := fnd_message.get;
3694: x_pos_errors.text_line.extend;
3695: x_pos_errors.message_name(l_err_count) := null;
3696: FND_MESSAGE.set_name('POS','POS_CAN_PO_QTY_BILL_RCV');
3697: fnd_message.set_token('LINE', l_po_change_requests(j).Document_Line_Number);
3698: fnd_message.set_token('SHIPMENT', l_po_change_requests(j).Document_Shipment_Number);
3699: fnd_message.set_token('QTY_BILL', l_quan_billed) ;
3700: fnd_message.set_token('QTY_RCV', l_quan_recd) ;
3701: x_pos_errors.text_line(l_err_count) := fnd_message.get;
3702: end if;
3707: begin
3708: SELECT nvl(POLL.quantity, 0), nvl(POLL.quantity_billed, 0), nvl(POLL.quantity_received, 0)
3709: into l_quan_ordered, l_quan_billed, l_quan_recd
3710: FROM PO_LINE_LOCATIONS_ALL POLL, PO_LINES_ALL POL
3711: WHERE POLL.line_location_id = l_po_change_requests(j).document_line_location_id
3712: AND POLL.po_line_id = POL.po_line_id
3713: AND nvl(POLL.cancel_flag, 'N') = 'N'
3714: AND nvl(POLL.closed_code, 'OPEN') <> 'FINALLY CLOSED'
3715: AND nvl(POLL.receipt_required_flag, 'Y') <> 'N'
3723: x_pos_errors.message_name.extend;
3724: x_pos_errors.text_line.extend;
3725: x_pos_errors.message_name(l_err_count) := null;
3726: FND_MESSAGE.set_name('POS','POS_CAN_PO_QTY_BILL_ORD');
3727: fnd_message.set_token('LINE', l_po_change_requests(j).Document_Line_Number);
3728: fnd_message.set_token('SHIPMENT', l_po_change_requests(j).Document_Shipment_Number);
3729: fnd_message.set_token('QTY_BILL', l_quan_billed) ;
3730: fnd_message.set_token('QTY_ORD', l_quan_ordered) ;
3731: x_pos_errors.text_line(l_err_count) := fnd_message.get;
3724: x_pos_errors.text_line.extend;
3725: x_pos_errors.message_name(l_err_count) := null;
3726: FND_MESSAGE.set_name('POS','POS_CAN_PO_QTY_BILL_ORD');
3727: fnd_message.set_token('LINE', l_po_change_requests(j).Document_Line_Number);
3728: fnd_message.set_token('SHIPMENT', l_po_change_requests(j).Document_Shipment_Number);
3729: fnd_message.set_token('QTY_BILL', l_quan_billed) ;
3730: fnd_message.set_token('QTY_ORD', l_quan_ordered) ;
3731: x_pos_errors.text_line(l_err_count) := fnd_message.get;
3732: end if;
3742:
3743: /*Added for bug#14155598*/
3744: PROCEDURE IS_ASN_EXIST(p_po_header_id IN NUMBER,
3745: p_po_release_id IN NUMBER,
3746: p_po_change_requests IN POS_CHG_REC_TBL,
3747: x_pos_errors OUT nocopy POS_ERR_TYPE,
3748: x_ret_sts OUT nocopy VARCHAR2)
3749: IS
3750: l_po_change_requests POS_CHG_REC_TBL := NULL;
3746: p_po_change_requests IN POS_CHG_REC_TBL,
3747: x_pos_errors OUT nocopy POS_ERR_TYPE,
3748: x_ret_sts OUT nocopy VARCHAR2)
3749: IS
3750: l_po_change_requests POS_CHG_REC_TBL := NULL;
3751: l_count_asn NUMBER;
3752: l_err_msg_name_tbl PO_TBL_VARCHAR30;
3753: l_err_msg_text_tbl PO_TBL_VARCHAR2000;
3754: l_err_count NUMBER;
3759: FROM po_line_locations_all
3760: WHERE po_line_id = p_po_line_id;
3761: BEGIN
3762: l_err_count := 0;
3763: l_po_change_requests := p_po_change_requests;
3764: l_err_msg_name_tbl := Po_tbl_varchar30();
3765: l_err_msg_text_tbl := Po_tbl_varchar2000();
3766: x_pos_errors := Pos_err_type(l_err_msg_name_tbl, l_err_msg_text_tbl);
3767: x_ret_sts := 'N';
3777: ||Nvl(To_char(p_po_release_id), 'null'));
3778: END IF;
3779: END IF;
3780:
3781: FOR j IN 1..l_po_change_requests.Count() LOOP
3782: IF( l_po_change_requests(j).request_level = 'SHIPMENT' AND l_po_change_requests(j).action_type = 'MODIFICATION' ) THEN
3783: IF( p_po_header_id IS NOT NULL AND p_po_release_id IS NOT NULL ) THEN
3784: SELECT Count(*)
3785: INTO l_count_asn
3778: END IF;
3779: END IF;
3780:
3781: FOR j IN 1..l_po_change_requests.Count() LOOP
3782: IF( l_po_change_requests(j).request_level = 'SHIPMENT' AND l_po_change_requests(j).action_type = 'MODIFICATION' ) THEN
3783: IF( p_po_header_id IS NOT NULL AND p_po_release_id IS NOT NULL ) THEN
3784: SELECT Count(*)
3785: INTO l_count_asn
3786: FROM rcv_transactions_interface rti
3787: WHERE rti.processing_status_code = 'PENDING'
3788: AND rti.quantity > 0
3789: AND rti.po_header_id = p_po_header_id
3790: AND rti.po_release_id (+) = p_po_release_id
3791: AND rti.po_line_location_id =l_po_change_requests(j).document_line_location_id;
3792: ELSE
3793: SELECT Count(*)
3794: INTO l_count_asn
3795: FROM rcv_transactions_interface rti
3795: FROM rcv_transactions_interface rti
3796: WHERE rti.processing_status_code = 'PENDING'
3797: AND rti.quantity > 0
3798: AND rti.po_header_id = p_po_header_id
3799: AND rti.po_line_location_id =l_po_change_requests(j).document_line_location_id;
3800: END IF;
3801:
3802: IF( l_count_asn > 0 ) THEN
3803: l_err_count := l_err_count + 1;
3803: l_err_count := l_err_count + 1;
3804: x_pos_errors.message_name.extend;
3805: x_pos_errors.text_line.extend;
3806: x_pos_errors.Message_name(l_err_count) := NULL;
3807: IF l_po_change_requests(j).document_line_location_id IS NOT NULL
3808: THEN
3809: fnd_message.Set_name('POS', 'POS_CHG_PO_UNPRC_TRX');
3810: fnd_message.Set_token('LINE',l_po_change_requests(j).document_line_number);
3811: fnd_message.Set_token('SHIPMENT',l_po_change_requests(j).document_shipment_number);
3806: x_pos_errors.Message_name(l_err_count) := NULL;
3807: IF l_po_change_requests(j).document_line_location_id IS NOT NULL
3808: THEN
3809: fnd_message.Set_name('POS', 'POS_CHG_PO_UNPRC_TRX');
3810: fnd_message.Set_token('LINE',l_po_change_requests(j).document_line_number);
3811: fnd_message.Set_token('SHIPMENT',l_po_change_requests(j).document_shipment_number);
3812: x_pos_errors.Text_line(l_err_count) := fnd_message.get;
3813: END IF;
3814: END IF;
3807: IF l_po_change_requests(j).document_line_location_id IS NOT NULL
3808: THEN
3809: fnd_message.Set_name('POS', 'POS_CHG_PO_UNPRC_TRX');
3810: fnd_message.Set_token('LINE',l_po_change_requests(j).document_line_number);
3811: fnd_message.Set_token('SHIPMENT',l_po_change_requests(j).document_shipment_number);
3812: x_pos_errors.Text_line(l_err_count) := fnd_message.get;
3813: END IF;
3814: END IF;
3815:
3818: INTO l_count_asn
3819: FROM rcv_shipment_lines RSL
3820: WHERE RSL.po_header_id = p_po_header_id
3821: AND RSL.po_release_id (+) = p_po_release_id
3822: AND RSL.po_line_location_id =l_po_change_requests(j).document_line_location_id
3823: AND Nvl(RSL.quantity_shipped, 0) > Nvl(RSL.quantity_received, 0)
3824: AND Nvl(RSL.asn_line_flag, 'N') = 'Y'
3825: AND Nvl(RSL.shipment_line_status_code, 'EXPECTED') <>'CANCELLED';
3826: ELSE
3827: SELECT Count(*)
3828: INTO l_count_asn
3829: FROM rcv_shipment_lines RSL
3830: WHERE RSL.po_header_id = p_po_header_id
3831: AND RSL.po_line_location_id = l_po_change_requests(j).document_line_location_id
3832: AND Nvl(RSL.quantity_shipped, 0) > Nvl(RSL.quantity_received, 0)
3833: AND Nvl(RSL.asn_line_flag, 'N') = 'Y'
3834: AND Nvl(RSL.shipment_line_status_code, 'EXPECTED') <>'CANCELLED';
3835: END IF;
3838: l_err_count := l_err_count + 1;
3839: x_pos_errors.message_name.extend;
3840: x_pos_errors.text_line.extend;
3841: x_pos_errors.Message_name(l_err_count) := NULL;
3842: IF L_po_change_requests(j).document_line_location_id IS NOT NULL
3843: THEN
3844: fnd_message.Set_name('POS', 'POS_CHG_PO_OPEN_ASN');
3845: fnd_message.Set_token('LINE',l_po_change_requests(j).document_line_number);
3846: fnd_message.Set_token('SHIPMENT',l_po_change_requests(j).document_shipment_number);
3841: x_pos_errors.Message_name(l_err_count) := NULL;
3842: IF L_po_change_requests(j).document_line_location_id IS NOT NULL
3843: THEN
3844: fnd_message.Set_name('POS', 'POS_CHG_PO_OPEN_ASN');
3845: fnd_message.Set_token('LINE',l_po_change_requests(j).document_line_number);
3846: fnd_message.Set_token('SHIPMENT',l_po_change_requests(j).document_shipment_number);
3847: x_pos_errors.Text_line(l_err_count) := fnd_message.get;
3848: END IF;
3849: END IF;
3842: IF L_po_change_requests(j).document_line_location_id IS NOT NULL
3843: THEN
3844: fnd_message.Set_name('POS', 'POS_CHG_PO_OPEN_ASN');
3845: fnd_message.Set_token('LINE',l_po_change_requests(j).document_line_number);
3846: fnd_message.Set_token('SHIPMENT',l_po_change_requests(j).document_shipment_number);
3847: x_pos_errors.Text_line(l_err_count) := fnd_message.get;
3848: END IF;
3849: END IF;
3850: END IF;
3848: END IF;
3849: END IF;
3850: END IF;
3851:
3852: IF ( l_po_change_requests(j).request_level = 'LINE' AND l_po_change_requests(j).action_type = 'MODIFICATION' ) THEN
3853: FOR ship_rec IN ship_cur(l_po_change_requests(j).document_line_id)
3854: LOOP
3855: IF( p_po_header_id IS NOT NULL AND p_po_release_id IS NOT NULL ) THEN
3856: SELECT Count(*)
3849: END IF;
3850: END IF;
3851:
3852: IF ( l_po_change_requests(j).request_level = 'LINE' AND l_po_change_requests(j).action_type = 'MODIFICATION' ) THEN
3853: FOR ship_rec IN ship_cur(l_po_change_requests(j).document_line_id)
3854: LOOP
3855: IF( p_po_header_id IS NOT NULL AND p_po_release_id IS NOT NULL ) THEN
3856: SELECT Count(*)
3857: INTO l_count_asn
3876: x_pos_errors.message_name.extend;
3877: x_pos_errors.text_line.extend;
3878: x_pos_errors.Message_name(l_err_count) := NULL;
3879:
3880: IF l_po_change_requests(j).document_line_id IS NOT NULL
3881: THEN
3882: fnd_message.Set_name('POS', 'POS_CHG_LINE_UNPRC_TRX');
3883: fnd_message.Set_token('LINE',l_po_change_requests(j).document_line_number);
3884: fnd_message.Set_token('SHIPMENT',ship_rec.shipment_num);
3879:
3880: IF l_po_change_requests(j).document_line_id IS NOT NULL
3881: THEN
3882: fnd_message.Set_name('POS', 'POS_CHG_LINE_UNPRC_TRX');
3883: fnd_message.Set_token('LINE',l_po_change_requests(j).document_line_number);
3884: fnd_message.Set_token('SHIPMENT',ship_rec.shipment_num);
3885: x_pos_errors.Text_line(l_err_count) := fnd_message.get;
3886: END IF;
3887: END IF;
3911: l_err_count := l_err_count + 1;
3912: x_pos_errors.message_name.extend;
3913: x_pos_errors.text_line.extend;
3914: x_pos_errors.Message_name(l_err_count) := NULL;
3915: IF l_po_change_requests(j).document_line_id IS NOT NULL
3916: THEN
3917: fnd_message.Set_name('POS', 'POS_CHG_LINE_OPEN_ASN');
3918: fnd_message.Set_token('LINE',l_po_change_requests(j).document_line_number);
3919: fnd_message.Set_token('SHIPMENT',ship_rec.shipment_num);
3914: x_pos_errors.Message_name(l_err_count) := NULL;
3915: IF l_po_change_requests(j).document_line_id IS NOT NULL
3916: THEN
3917: fnd_message.Set_name('POS', 'POS_CHG_LINE_OPEN_ASN');
3918: fnd_message.Set_token('LINE',l_po_change_requests(j).document_line_number);
3919: fnd_message.Set_token('SHIPMENT',ship_rec.shipment_num);
3920: x_pos_errors.Text_line(l_err_count) := fnd_message.get;
3921: END IF;
3922: END IF;