Single product, after the status updates can be updated, multiple items (lines) cannot be updated, the trigger is as follows:
The create or replace the trigger AA_PR_LINES_UPDATE_T1
After the insert or update on "PO". "PO_REQUISITION_HEADERS_ALL
"Referencing the new as new
For each row
When (new AUTHORIZATION_STATUS='APPROVED' and
New. Requisition_header_id is not null)
The begin
Declare
P_header_id number;
P_line_id number;
P_line_num number;
Ax number;
Bx number;
P_item_id NUMBER;
The begin
Select PRL item_id, PRL blanket_po_header_id, PRL. Blanket_po_line_num, PRL. Requisition_line_id
Into p_item_id, ax, bx, p_line_id
The from po_requisition_lines_all PRL
Where PRL. Requisition_header_id=: new. Requisition_header_id;
The select po_header_id line_num into p_header_id, p_line_num
The from (select ROW_NUMBER () OVER (ORDER BY pol. Unit_price) rownumber
, poh. Po_header_id, pol line_num, s.v endor_name, along the vendor_site_code, poh. Vendor_id
, along. Vendor_site_id, poh segment1, pol. Unit_price, pol. Attribute1, pol. Attribute2
, pol. Item_id
The from po_headers_all poh, po_lines_all pol, ap_suppliers s, ap_supplier_sites_all along
Where poh. Po_header_id=pol. Po_header_id
And poh. Type_lookup_code='BLANKET'
And pol. Item_id=p_item_id
And poh. Approved_flag='Y'
And NVL (pol. Closed_code, 'OPEN')='OPEN'
And pol. Creation_date | | '-' | | poh. Vendor_id in (select Max (pol. Creation_date) | | '-' | | poh. Vendor_id
The from po_headers_all poh,
Po_lines_all pol
Where poh. Po_header_id=pol. Po_header_id
And poh. Type_lookup_code='BLANKET'
And pol. Item_id=p_item_id
And NVL (pol. Closed_code, 'OPEN')='OPEN'
And NVL (pol. Cancel_flag, 'N')='N'
And poh. Approved_flag='Y'
Group by poh. Vendor_id)
And NVL (pol. Cancel_flag, 'N')='N'
And poh. Vendor_id=s.v endor_id
And s.v endor_id=along. Vendor_id)
Where 1=1
And rownumber=1;

If ax<> P_header_id/* and bx<> P_line_num */then
The update PO_REQUISITION_LINES_ALL PRL
The set PRL. Blanket_po_header_id=p_header_id
, PRL. Blanket_po_line_num=p_line_num

Where PRL. Requisition_header_id=: new requisition_header_id
And PRL. Item_id=p_item_id
And PRL. Requisition_line_id=p_line_id;
commit;
end if;

The exception
The when others then null;
end;
end;
Please help to see where to write wrong,

CodePudding user response:

Lz do log run again see, screening out where is the problem,

CodePudding user response:

Novice, said to trigger the log is not very understand, would you please tell me how to do

CodePudding user response:

Write some DBMS_OUTPUT