create or replace PACKAGE BODY XX_AGEDPO_CLOSE_PKG
AS
PROCEDURE main (p_errbuf OUT VARCHAR2,
p_retcode OUT NUMBER,
P_ORG_ID IN NUMBER,
p_po_age IN NUMBER,
p_catalog IN VARCHAR2,
p_facilities IN VARCHAR2)
IS
CURSOR lcu_aged_pos
IS
SELECT aps.vendor_name,
aps.segment1 vendor_num,
poh.segment1 PO_Num,
poh.po_header_id
poh.org_id
FROM po_headers_all poh,
po_lines_all pol,
po_line_locations_all poll,
po_distributions_all pod,
ap_suppliers aps,
gl_code_combinations gcc
where poh.po_header_id = pol.po_header_id
and pol.item_id is NOT NULL
and pol.po_line_id = poll.po_line_id
and poll.line_location_id = pod.line_location_id
and poh.vendor_id = aps.vendor_id
and pod.code_combination_id = gcc.code_combination_id
and nvl(aps.hold_flag, 'N') = 'N'
and nvl(poh.cancel_flag, 'N') = 'N'
and poh.type_lookup_code = 'STANDARD'
and poh.authorization_status = 'APPROVED'
and nvl(pol.cancel_flag, 'N') = 'N'
and nvl(poh.closed_code, 'OPEN') = 'OPEN'
and nvl(poll.closed_code, 'OPEN') IN ( 'OPEN','CLOSED FOR RECEIVING','CLOSED FOR INVOICE')
and not exists (select i.po_header_id
from ap_invoices_all i,ap_holds h
where i.po_header_id = poh.po_header_id
and i.invoice_id = h.invoice_id
and h.release_lookup_code IS NULL)
and poh.org_id= :P_ORG_ID
and trunc(poll.need_by_date) < sysdate - :p_po_age;
group by aps.vendor_name,
aps.segment1,
poh.segment1,
poh.po_header_id
order by aps.vendor_name,poh.segment1;
l_sql VARCHAR2 (32767);
l_success_count NUMBER := 0;
l_error_count NUMBER := 0;
lv_result BOOLEAN;
lv_return_code VARCHAR2(20);
g_user_id NUMBER := FND_GLOBAL.USER_ID;
g_resp_id NUMBER := FND_GLOBAL.RESP_ID;
g_resp_appl_id NUMBER := FND_GLOBAL.RESP_APPL_ID;
BEGIN
Fnd_Global.apps_initialize(g_user_id,
g_resp_id,
g_resp_appl_id);
FOR rec_aged_pos IN lcu_aged_pos LOOP
BEGIN
lv_result := PO_ACTIONS.main(
P_DOCID => rec_aged_pos.po_header_id,
P_DOCTYP => 'PO',
P_DOCSUBTYP => 'STANDARD',
P_LINEID => NULL,
P_SHIPID => NULL,
P_ACTION => 'CLOSE',
P_REASON => 'Close Aged Purchase Order ',
P_CALLING_MODE => 'PO',
P_CONC_FLAG => 'N',
P_RETURN_CODE => lv_return_code,
P_AUTO_CLOSE => 'N',
P_ACTION_DATE => sysdate,
P_ORIGIN_DOC_ID => NULL );
IF lv_return_code IS NULL THEN
FND_FILE.put_line(fnd_file.output,'PO_NUM:'||rec_aged_pos.PO_Num||', Org_Id:'||rec_aged_pos.org_id);
l_success_count := l_success_count + 1;
ELSE
FND_FILE.put_line(fnd_file.log,'PO_HEADER_ID: '||rec_aged_pos.po_header_id|| ', error message: '||lv_return_code);
l_error_count := l_error_count + 1;
END IF;
COMMIT;
END;
END LOOP;
FND_FILE.PUT_LINE ( FND_FILE.log,'Total POs closed: '||l_success_count );
FND_FILE.PUT_LINE ( FND_FILE.log,'Total POs failed: '||l_error_count );
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE ( FND_FILE.LOG,'Error in main procedure '||SQLERRM);
p_retcode := 2;
p_errbuf := 'Error in main procedure :'||SQLERRM;
END main;
END XX_AGEDPO_CLOSE_PKG;
/
SHOW ERRORS;
