Purchasing SQL Scripts

Steps to customize the PO Approval Workflow.

1. Take the seeded workflow (POAPPRV)
2. Right click on the Item TYpe and create a new Item TYpe .
3. Open the Seeded Workflow(POAPPRV) in other window .
4. Copy and paste ATTRIBUTES, NOTIFICATIONs, FUNCTIONS and PROCESSES etc from the the seeded workflow to the Newly named workflow .
5. Verify the workflow
6. Customize the new workflow according to your needs.
7. Save the changes and port it to Oracle.

Login with a PO Super User responsibility, go to Setup -> Document Types, select the PO types for which you want to use the custom workflow, and change workflow fields 'Approval Workflow' and 'Workflow Startup Process' to the new ones.

Sql Script to extract Vendor, Project, Receipt information for a PO

SELECT pha.po_header_id, pha.segment1 po_number, pov.vendor_name,
pov.segment1 vendor_num, pla.line_num po_line_number, pla.item_id,
(SELECT MAX (segment1)
FROM mtl_system_items_b
WHERE inventory_item_id = pla.item_id) item,
DECODE (NVL (pla.item_id, 0),
0, NULL,
pla.item_description
) item_description,
DECODE (pla.cancel_flag,
'Y', 'CANCELLED',
DECODE (pha.authorization_status,
'IN PROCESS', 'PENDING APPROVAL',
NVL (pha.authorization_status, 'INCOMPLETE')
)
) authorization_status,
DECODE (pla.cancel_flag,
'Y', 'N/A',
DECODE (pha.authorization_status,
'APPROVED', DECODE (NVL (pla.closed_code, 'b'),
'CLOSED', 'CLOSED',
'FINALLY CLOSED', 'CLOSED',
'OPEN'
),
'N/A'
)
) po_status,
pha.creation_date date_issued, pla.creation_date line_creation_date,
(pda.quantity_ordered - pda.quantity_cancelled) po_quantity,
(NVL (pla.unit_price, 0) * NVL (pda.quantity_ordered, 0)
) po_line_amt,
(NVL (pla.unit_price, 0) * NVL (pda.quantity_cancelled, 0)
) po_cancelled_amount,
(NVL (pla.unit_price, 0) * NVL (pda.quantity_delivered, 0)
) po_line_received_amount,
rt.transaction_date receipt_date,
TO_CHAR (rt.transaction_date, 'MON-RR') receipt_month,
rsh.receipt_num,
(DECODE (NVL (rt.transaction_type, 'a'),
'RETURN TO VENDOR', NVL (rt.quantity * (-1), 0),
'RETURN TO RECEIVING', NVL (rt.quantity * (-1), 0),
NVL (rt.quantity, 0)
)
) receipt_quantity,
( NVL (rt.po_unit_price, 0)
* DECODE (NVL (rt.transaction_type, 'a'),
'RETURN TO VENDOR', NVL (rt.quantity * (-1), 0),
'RETURN TO RECEIVING', NVL (rt.quantity * (-1), 0),
NVL (rt.quantity, 0)
)
) receipt_amount,
ai.invoice_id, ai.invoice_num, ai.invoice_amount, ai.amount_paid,
ai.invoice_date, aid.quantity_invoiced, aid.amount inv_line_amount,
ppa.project_id, ppa.segment1 project_number, ppa.NAME project_name,
(SELECT full_name
FROM pa_project_players_v
WHERE project_id = ppa.project_id
AND UPPER (ROLE) = 'PROJECT MANAGER'
AND NVL (end_date_active, SYSDATE) IN (
SELECT MAX (NVL (end_date_active, SYSDATE))
FROM pa_project_players_v
WHERE 1 = 1
AND project_id = ppa.project_id
AND UPPER (ROLE) = 'PROJECT MANAGER')
AND ROWNUM < 2) "Project Manager",
total_billings.invoice_amount total_actual_billings
FROM po_headers_all pha,
po_lines_all pla,
po_distributions_all pda,
ap_invoices_all ai,
ap_invoice_distributions_all aid,
rcv_transactions rt,
rcv_shipment_headers rsh,
po_vendors pov,
pa_projects_all ppa,
(SELECT i.project_id, SUM (NVL (ii.inv_amount, 0)) invoice_amount
FROM pa_draft_invoices_all i, pa_draft_inv_items_bas ii
WHERE 1 = 1
AND ii.project_id(+) = i.project_id
AND ii.draft_invoice_num(+) = i.draft_invoice_num
AND i.pa_date < ((SELECT end_date
FROM pa_periods_all ppi
WHERE ppi.current_pa_period_flag = 'Y') + 1)
GROUP BY i.project_id) total_billings
WHERE pha.po_header_id = pla.po_header_id
AND pda.po_header_id = pla.po_header_id
AND pda.po_line_id = pla.po_line_id
AND pda.po_header_id = pha.po_header_id
AND pha.vendor_id = pov.vendor_id
AND pda.po_distribution_id = aid.po_distribution_id(+)
AND aid.invoice_id = ai.invoice_id(+)
AND pla.po_line_id = rt.po_line_id(+)
AND rt.shipment_header_id = rsh.shipment_header_id(+)
AND rt.destination_type_code(+) = 'RECEIVING'
AND pda.project_id = ppa.project_id(+)
AND ppa.project_id = total_billings.project_id(+)

Script to Approve PO Requisition

/* Formatted on 2010/05/15 11:23 (Formatter Plus v4.8.0) */
DECLARE
l_itemkey VARCHAR2 (200);
l_po_id NUMBER := :PO_HEADER_ID;
l_po_number VARCHAR2 (200) := :REQUISITION_NUMBER;
BEGIN
SELECT l_po_id || '-' || TO_CHAR (po_wf_itemkey_s.NEXTVAL)
INTO l_itemkey
FROM DUAL;

wf_engine.createprocess ('REQAPPRV',
l_itemkey,
'MAIN_REQAPPRV_PROCESS',
NULL,
:USER_NAME
);
wf_engine.setitemattrnumber (itemtype => 'REQAPPRV',
itemkey => l_itemkey,
aname => 'RESPONSIBILITY_ID',
avalue => :RESPONSIBILITY_ID
);
wf_engine.setitemattrnumber (itemtype => 'REQAPPRV',
itemkey => l_itemkey,
aname => 'APPLICATION_ID',
avalue => :APPLICATION_ID
);
wf_engine.setitemattrnumber (itemtype => 'REQAPPRV',
itemkey => l_itemkey,
aname => 'USER_ID',
avalue => :USER_ID
);
wf_engine.setitemattrnumber (itemtype => 'REQAPPRV',
itemkey => l_itemkey,
aname => 'SUBMITTER_ID',
avalue => :BUYER_ID
);
wf_engine.setitemattrnumber (itemtype => 'REQAPPRV',
itemkey => l_itemkey,
aname => 'DOCUMENT_ID',
avalue => l_po_id
);
wf_engine.setitemattrtext (itemtype => 'REQAPPRV',
itemkey => l_itemkey,
aname => 'DOCUMENT_NUMBER',
avalue => l_po_number
);
wf_engine.setitemattrnumber (itemtype => 'REQAPPRV',
itemkey => l_itemkey,
aname => 'ORG_ID',
avalue => :ORG_ID
);
wf_engine.setitemattrtext (itemtype => 'REQAPPRV',
itemkey => l_itemkey,
aname => 'DOCUMENT_SUBTYPE',
avalue => 'PURCHASE'
);
wf_engine.setitemattrtext (itemtype => 'REQAPPRV',
itemkey => l_itemkey,
aname => 'DOCUMENT_TYPE',
avalue => 'REQUISITION'
);
wf_engine.setitemattrtext (itemtype => 'REQAPPRV',
itemkey => l_itemkey,
aname => 'DOCUMENT_TYPE_DISP',
avalue => 'Purchase Requisition'
);
wf_engine.setitemattrtext (itemtype => 'REQAPPRV',
itemkey => l_itemkey,
aname => 'AUTHORIZATION_STATUS',
avalue => 'APPROVED'
);
wf_engine.setitemattrtext (itemtype => 'REQAPPRV',
itemkey => l_itemkey,
aname => 'AUTHORIZATION_STATUS_DISP',
avalue => 'Approved'
);
wf_engine.setitemattrtext (itemtype => 'REQAPPRV',
itemkey => l_itemkey,
aname => 'SEND_CREATEPO_TO_BACKGROUND',
avalue => 'Y'
);
wf_engine.setitemattrtext (itemtype => 'REQAPPRV',
itemkey => l_itemkey,
aname => 'INTERFACE_SOURCE_CODE',
avalue => 'PO_FORM'
);

DBMS_OUTPUT.put_line (l_itemkey);
wf_engine.startprocess ('REQAPPRV', l_itemkey);
COMMIT;
END;
/

Script to cancel PO Requisition

DECLARE
l_return_status VARCHAR2 (1000);
l_msg_count NUMBER;
l_msg_data VARCHAR2 (1000);
lv_header_id po_tbl_number;
lv_line_id po_tbl_number;
m NUMBER := NULL;
l_msg_dummy VARCHAR2 (2000);
l_output VARCHAR2 (2000);
BEGIN
m := 1;
lv_header_id := po_tbl_number (:REQ_HEADER_ID);
lv_line_id := po_tbl_number (:REQ_LINE_ID);
po_req_document_cancel_grp.cancel_requisition
(p_api_version => 1.0,
p_req_header_id => lv_header_id,
p_req_line_id => lv_line_id,
p_cancel_date => SYSDATE,
p_cancel_reason => 'Cancelled Requisition',
p_source => 'REQUISITION',
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
COMMIT;
DBMS_OUTPUT.put_line (l_return_status);

IF l_return_status <> 'S'
THEN
fnd_msg_pub.get (m, fnd_api.g_false, l_msg_data, l_msg_dummy);
l_output := (TO_CHAR (m) || ': ' || l_msg_data);
DBMS_OUTPUT.put_line (l_output);
END IF;
END;

Credit goes to the below website(s) :

http://arunrathod.blogspot.com/