Using the PO_CHANGE_API1_S.update_po, the following columns can be updated in a Purchase Order.

– Promised_date
– Need_by_date
– Quantity
– Unit_price

Importance of LAUNCH_APPROVALS_FLAG
=================================

– Indicates if you want to launch APPROVAL workflow after the update. Its value could be either ‘Y’ or ‘N’. If not provided, the default value is ‘N’.
– If we are passing launch_approvals_flag =>’N’, the PO is remaining in the Requires Reapproval status.

Post Update PO Validation:
====================
1. Set the PO status to REQUIRES REAPPROVAL.
2. Increment revision number if the PO was in APPROVED status before the update.
3. Launch the PO Approval workflow if LAUNCH_APPROVALS_FLAG = ‘Y’.
4. If the quantity was adjusted down to be equal to the total quantity received or billed, then set the appropriate closed code and roll up the closed code to line and header levels.

— R12 – PO – Sample Script to Update PO Using po_change_api1_s
–=================================================

DECLARE

CURSOR po_date_update
IS

SELECT pha.segment1 po_number, pha.revision_num,pha.po_header_id,
pha.authorization_status, pla.po_line_id, pla.line_num, pha.org_id,
pla.unit_price, pola.line_location_id, pola.shipment_num,
pola.quantity, pola.promised_date, pola.need_by_date,
pha.closed_code
FROM po_headers_all pha, po_lines_all pla,
po_line_locations_all pola
WHERE pha.po_header_id = pla.po_header_id
AND pla.po_line_id = pola.po_line_id
AND NVL (pha.approved_flag, ‘N’) = ‘Y’
AND NVL (pola.cancel_flag, ‘N’) <> ‘Y’
AND NVL (pola.closed_code, ‘OPEN’) = ‘OPEN’
AND NVL (pola.quantity_received, 0) = 0
AND NVL (pola.quantity_billed, 0) = 0
AND pha.segment1 = ‘11170007326’
AND pla.line_num = 1
AND pha.type_lookup_code = ‘STANDARD’
AND pha.org_id = 308;

l_user_id NUMBER;
l_resp_id NUMBER;
l_appl_id NUMBER;
l_result NUMBER;
l_api_errors po_api_errors_rec_type;
l_revision_num NUMBER;
l_promised_date DATE;
l_need_by_date DATE;
l_price po_lines_all.unit_price%TYPE;
l_quantity po_line_locations_all.quantity%TYPE;

BEGIN

SELECT user_id
INTO l_user_id
FROM fnd_user
WHERE user_name = ‘CONVERSION’;

SELECT responsibility_id, application_id
INTO l_resp_id, l_appl_id
FROM fnd_responsibility_vl
WHERE responsibility_name = ‘Purchasing Super User’;

fnd_global.apps_initialize (l_user_id, l_resp_id, l_appl_id);

FOR i IN po_date_update

LOOP

mo_global.set_policy_context (‘S’, i.org_id);
mo_global.init (‘PO’);

l_promised_date := i.promised_date + 30;
l_need_by_date := i.need_by_date + 35;
l_quantity := i.quantity + 100;
l_price := i.unit_price – 1;

DBMS_OUTPUT.put_line (‘Calling po_change_api1_s.update_po To Update PO’);
DBMS_OUTPUT.put_line (‘===================================’);
DBMS_OUTPUT.put_line (‘Retrieving the Current Revision Number of PO’);

select revision_num
into l_revision_num
from po_headers_all
where segment1 = i.po_number;

l_result :=
po_change_api1_s.update_po
(x_po_number => i.po_number, –Enter the PO Number
x_release_number => NULL, –Enter the Release Num
x_revision_number => l_revision_num, –Enter the Revision Number
x_line_number => i.line_num, –Enter the Line Number
x_shipment_number => i.shipment_num, –Enter the Shipment Number
new_quantity => l_quantity, –Enter the new quantity
new_price => l_price, –Enter the new price,
new_promised_date => l_promised_date, –Enter the new promised date,
new_need_by_date => l_need_by_date, –Enter the new need by date,
launch_approvals_flag => ‘Y’,
update_source => NULL,
VERSION => ‘1.0’,
x_override_date => NULL,
x_api_errors => l_api_errors,
p_buyer_name => NULL,
p_secondary_quantity => NULL,
p_preferred_grade => NULL,
p_org_id => i.org_id
);

DBMS_OUTPUT.put_line (l_result);

IF (l_result = 1)
THEN
DBMS_OUTPUT.put_line(‘Successfully update the PO :=>’);
END IF;

IF (l_result <> 1)
THEN
DBMS_OUTPUT.put_line (‘Failed to update the PO Due to Following Reasons’);
— Display the errors
FOR j IN 1 .. l_api_errors.MESSAGE_TEXT.COUNT
LOOP
DBMS_OUTPUT.put_line (l_api_errors.MESSAGE_TEXT (j));
END LOOP;
END IF;

END LOOP;

END;

R12 – Sample Script to Cancel Single AP Invoice using ap_cancel_package API
DECLARE
l_message_name VARCHAR2 (1000);
l_invoice_amount NUMBER;
l_base_amount NUMBER;
l_tax_amount NUMBER;
l_temp_cancelled_amount NUMBER;
l_cancelled_by VARCHAR2 (1000);
l_cancelled_amount NUMBER;
l_cancelled_date DATE;
l_last_update_date DATE;
l_original_prepayment_amount NUMBER;
l_pay_curr_invoice_amount NUMBER;
l_token VARCHAR2 (100);
l_boolean BOOLEAN;
l_user_id NUMBER := 2083;
l_resp_id NUMBER := 20639;
l_appl_id NUMBER := 200;

CURSOR C_Inv_Det is
SELECT distinct aia.*
FROM ap_invoices_all aia, ap_invoice_lines_all aila
WHERE aia.invoice_id = aila.invoice_id
AND aia.org_id = aila.org_id
AND aia.org_id = 308
AND aia.invoice_num = ‘300040823’
AND aia.payment_status_flag = ‘N’
AND NVL(aila.cancelled_flag, ‘N’) <> ‘Y’;
BEGIN
fnd_global.apps_initialize(l_user_id, l_resp_id, l_appl_id);
FOR l_inv_rec IN C_Inv_Det LOOP
mo_global.init (‘SQLAP’);
mo_global.set_policy_context (‘S’, l_inv_rec.org_id);
DBMS_OUTPUT.put_line (‘Calling API ap_cancel_pkg.ap_cancel_single_invoice to Cancel Invoice: ‘ l_inv_rec.invoice_num);
DBMS_OUTPUT.put_line (‘**************************************************************’);

l_boolean := ap_cancel_pkg.ap_cancel_single_invoice
(p_invoice_id => l_inv_rec.invoice_id,
p_last_updated_by => l_inv_rec.last_updated_by,
p_last_update_login => l_inv_rec.last_update_login,
p_accounting_date => l_inv_rec.gl_date,
p_message_name => l_message_name,
p_invoice_amount => l_invoice_amount,
p_base_amount => l_base_amount,
p_temp_cancelled_amount => l_temp_cancelled_amount,
p_cancelled_by => l_cancelled_by,
p_cancelled_amount => l_cancelled_amount,
p_cancelled_date => l_cancelled_date,
p_last_update_date => l_last_update_date,
p_original_prepayment_amount => l_original_prepayment_amount,
p_pay_curr_invoice_amount => l_pay_curr_invoice_amount,
P_Token => l_token,
p_calling_sequence => NULL);

DBMS_OUTPUT.put_line (‘l_message_name => ‘ l_message_name);
DBMS_OUTPUT.put_line (‘l_invoice_amount => ‘ l_invoice_amount);
DBMS_OUTPUT.put_line (‘l_base_amount => ‘ l_base_amount);
DBMS_OUTPUT.put_line (‘l_tax_amount => ‘ l_tax_amount);
DBMS_OUTPUT.put_line (‘l_temp_cancelled_amount => ‘ l_temp_cancelled_amount);
DBMS_OUTPUT.put_line (‘l_cancelled_by => ‘ l_cancelled_by);
DBMS_OUTPUT.put_line (‘l_cancelled_amount => ‘ l_cancelled_amount);
DBMS_OUTPUT.put_line (‘l_cancelled_date => ‘ l_cancelled_date);
DBMS_OUTPUT.put_line (‘P_last_update_date => ‘ l_last_update_date);
DBMS_OUTPUT.put_line (‘P_original_prepayment_amount => ‘ l_original_prepayment_amount);
DBMS_OUTPUT.put_line (‘l_pay_curr_invoice_amount => ‘ l_pay_curr_invoice_amount);

IF l_boolean
THEN
DBMS_OUTPUT.put_line (‘Successfully Cancelled the Invoice => ‘ l_inv_rec.invoice_num);
COMMIT;
ELSE
DBMS_OUTPUT.put_line (‘Failed to Cancel the Invoice => ‘ l_inv_rec.invoice_num);
ROLLBACK;
END IF;
END LOOP;
END;
DBMS Output –

Calling API ap_cancel_pkg.ap_cancel_single_invoice to Cancel Invoice: 300040823
*********************************************************************************
l_message_name =>
l_invoice_amount => 0
l_base_amount => 0
l_tax_amount =>
l_temp_cancelled_amount => 15678
l_cancelled_by => 1831
l_cancelled_amount => 15678
l_cancelled_date => 06-OCT-09
P_last_update_date => 06-OCT-09
P_original_prepayment_amount =>
l_pay_curr_invoice_amount => 0
Successfully Cancelled the Invoice => 300040823

Before approving the Purchase Order & when the PO is in INCOMPLETE status, Oracle allows us to delete the same from the Purchase Order Form. The same can be achieved using the following mentioned API.
But we need to remember few points before using this API for deleting Incomplete PO.
– The PO Should be INCOMPLETE & Should not be Approved Once.
– Once the PO is approved, records gets inserted into mtl_supply & all the archive tables.
This API never deletes the records from the mtl_supply & archive tables and only deletes the
records from the base tables.
— Sample Script to Delete Incomplete PO Using ‘PO_HEADERS_SV1’
–=================================================
DECLARE

l_deleted BOOLEAN;

CURSOR c_po_det IS
SELECT po_header_id, segment1,org_id
FROM po.po_headers_all pha
WHERE 1 = 1
AND segment1 = ‘11170002356’
AND org_id = 308
AND NVL (approved_flag, ‘N’) <> ‘Y’
AND NVL (cancel_flag, ‘N’) = ‘N’
AND type_lookup_code = ‘STANDARD’
AND authorization_status = ‘INCOMPLETE’
AND NOT EXISTS ( SELECT 1
FROM po_headers_archive_all a
WHERE pha.po_header_id = a.po_header_id
AND pha.org_id = a.org_id)
AND NOT EXISTS ( SELECT 1
FROM mtl_supply b WHERE pha.po_header_id = b.po_header_id AND
supply_type_code = ‘PO’);
BEGIN
FOR c1 IN c_po_det LOOP
DBMS_OUTPUT.put_line ( ‘Calling PO_HEADERS_SV1 API To Delete PO’);
DBMS_OUTPUT.put_line ( ‘==========================’);
l_deleted := po_headers_sv1.delete_po (c1.po_header_id, ‘STANDARD’);

IF l_deleted = TRUE
THEN
COMMIT;
DBMS_OUTPUT.put_line ( ‘Successfully Deleted the PO’);
ELSE
ROLLBACK;
DBMS_OUTPUT.put_line ( ‘Failed to Delete the PO’);
END IF;
END LOOP;
END;

Period-End process is performed at the end of each period(depends on the organization). It is very important in any organization because if the period is not closed, the accounting for that period can not be closed, which will affect the financial results reporting of the respective period.
One can not close any module without following the order.

The suggested module wise sequence to be followed for Period-End process is as follows.

1. Projects
2. Payables
3. Purchasing
4. Assets 
5. Receivables
6. Inventory
7. General Ledger

When we enter transactions in Purchasing, the accounting entries will get generated with respect to the following accounts.

1. When Creating a receipt:
Inventory Receiving A/C DR
Accrual Account CR

2. At the time of Receiving transactions:
Asset clearing A/c or Inventory valuation a/c DR
Inventory Receiving A/c CR

3. When a Payables invoice matched with PO:
Accrual A/C DR
Liability A/c

4. when asset addition done:
Asset A/c DR
Asset Clearing A/c CR(Asset item)
           (OR)
expense a/c DR
inventory Valuation A/C(Inventory item)

When we enter transactions in Payables, the accounting entries will get generated with respect to the following accounts.

1. Invoice: When we create accounting for an invoice, the accounting will be generated with respect to the following accounts.

Item Expense A/C      DR
Liability  A/C             CR

2. Payment: There are two stages in which the accounting may generate, based on the accounting option that we setup in Payables.
  
> Direct Pay-No Clearance: In this stage, there will be no clearance process separately and the cash account will get credited directly.

Liability  A/C    DR
Cash  A/C         CR

> Pay and Clear: In this stage, the payment and clearance process will have separate accounting entries.

At the time of Payment:

Liability A/C               DR
Cash Clearing A/C      CR

At the time of Clearance:

Cash Clearing A/C    DR
Cash A/C                  CR

 When we enter transactions in Receivables, the accounting entries will get generated with respect to the following accounts.

1. Accounting for invoice:
Receivables A/C DR
Revenue Account Cr

2. Credit memo:
Revenue Dr
Receivables A/C CR

3. Receipts:
Cash A/C DR
Receivables A/c CR