Oracle EBS: SQL failure package Po Protocol

1. query a pack of Po:

Note that different payment conditions, currencies, and tax rates may generate a new po package.

Copy the content to the clipboard program code select PLA. po_line_id,
Pla. po_header_id,
Pla. item_id,
Msib. segment1 item_code,
Pla. unit_price,
Pla. item_description,
Pla. start_date,
Pla. expiration_date,
Pla. cancel_flag,
Pla. closed_code,
PHA. po_header_id,
PHA. vendor_id,
PHA. vendor_site_id,
PHA. terms_id,
Pla. tax_code_id
From po_lines_all PLA, po_headers_all PHA, mtl_system_items_ B msib
Where nvl (PLA. closed_code, 'open ')! = 'Finally closed'
And nvl (PLA. cancel_flag, 'n') = 'n'
And PLA. po_header_id = PHA. po_header_id
And nvl (PHA. closed_code, 'open ')! = 'Finally closed'
And nvl (PHA. cancel_flag, 'n') = 'n'
And PHA. enabled_flag = 'y'
And PHA. type_lookup_code = 'blancet'
And msib. inventory_item_id = PLA. item_id

2. Find the corresponding ID to invalidate the corresponding price. Here I seem to have missing the payment conditions and currency, tax rate conditions,

Copy the content to the clipboard program code procedure setpriceexpiredate (p_item_code in varchar2,
P_vendor_code in varchar2,
P_vendor_site_code in varchar2,
P_org_id in varchar2,
V_retval out number) is
Begin
Update po_lines_all PLA
Set PLA. expiration_date = sysdate
Where PLA. item_id = getitemid (p_item_code, p_org_id)
And PLA. org_id = p_org_id
And nvl (PLA. cancel_flag, 'n') = 'n'
And nvl (closed_code, 'open ')! = 'Finally closed'
And exists
(Select *
From po_headers_all PHA
Where PHA. po_header_id = PLA. po_header_id
And PHA. type_lookup_code = 'blancet'
And PHA. vendor_id = getvendorid (p_vendor_code)
And PHA. vendor_site_id =
Getvendorsiteid (p_vendor_site_code, p_org_id ));

V_retval: = 1;
Commit;

Exception
When others then
V_retval: = 0;

End setpriceexpiredate;

3. Auxiliary Functions

Copy the content to the clipboard program code function getvendorid (p_vendor_code in varchar2) return number
V_vendor_id number;

Begin

Select pv. vendor_id
Into v_vendor_id
From po_vendors PV

Where pv. segment1 = p_vendor_code;

Return v_vendor_id;

End;

Function getvendorsiteid (p_vendor_site_code in varchar2,
P_org_id in number) return number
V_vendor_site_id number;
Begin
Select pvsa. vendor_site_id
Into v_vendor_site_id
From po_vendor_sites_all pvsa
Where pvsa. vendor_site_code = p_vendor_site_code
And pvsa. org_id = p_org_id;

Return v_vendor_site_id;

End;

Function getitemid (p_item_code in varchar2, p_org_id in number)
Return number

V_item_id number;

Begin
Select msib. inventory_item_id
Into v_item_id
From mtl_system_items_ B msib

Where msib. segment1 = p_item_code
And msib. organization_id = p_org_id;

Return v_item_id;

End;