Bhaskar Reddy Oracle Applications: Package for extracting PO's of OB10 Suppliers

CREATE OR REPLACE PACKAGE BODY XXBR_AP_int_ob10_po_ext_pkg

g_num_org_id NUMBER := FND_PROFILE.VALUE('ORG_ID');

PROCEDURE XXBR_AP_ob10_po_extract_proc(p_out_retcode OUT NOCOPY VARCHAR2,

p_out_errbuf OUT NOCOPY VARCHAR2,

p_in_format IN VARCHAR2,

p_in_closedcode IN VARCHAR2,

p_in_authorisation IN VARCHAR2,

p_in_output_filename IN VARCHAR2,

p_in_output_directory IN VARCHAR2)

--p_in_module_name IN VARCHAR2,

-- p_in_local_file IN VARCHAR2,

--p_in_remote_file IN VARCHAR2,

--p_in_arch_path IN VARCHAR2,

--p_in_dest_path IN VARCHAR2)

-------------------------------------------------

---Cursor to fetch data for PO Extract----

--------------------------------------------

CURSOR XXBR_get_ob10_po_info_cur(l_po_closedcode VARCHAR2,

l_po_authorisation VARCHAR2,

l_org_id NUMBER)

SELECT pva.segment1 vendor_num,

pva.vendor_name,

pvs.vendor_site_code,

pha.segment1 po_num,

decode(pha.closed_code,NULL,'OPEN',pha.closed_code) closed_code,

nvl(sum(pla.unit_price*pla.quantity),0) amount,

pha.org_id,

pha.currency_code

FROM po_headers_all pha,

po_vendors pva,

po_vendor_sites pvs,

po_lines_all pla

WHERE pha.vendor_id=pva.vendor_id

AND pha.vendor_site_id=pvs.vendor_site_id(+)

AND pha.po_header_id=pla.po_header_id

AND nvl(pha.closed_code,'OPEN')=l_po_closedcode

AND pha.authorization_status=l_po_authorisation

AND pha.org_id=l_org_id

--AND pva.attribute1 ='P'

--AND nvl(pha.cancel_flag,'N')='N'

--AND nvl(pla.cancel_flag,'N')='N'

--AND pva.attribute_category='OB10-Header'

GROUP BY pha.segment1,

pva.segment1,

pva.vendor_name,

pvs.vendor_site_code,

pha.closed_code,

pha.org_id,

pha.currency_code;

cursor XXBR_stg_obten_PO_extract

SELECT modified_flag,

segment1

FROM XXBR_obten_PO_extract;

l_file_cust UTL_FILE.FILE_TYPE;

l_chr_filename VARCHAR2(30);

l_num_rec_count NUMBER :=0;

/* l_chr_appl_name VARCHAR2(60) :='XXBR';

l_chr_prm_shrname VARCHAR2(60) :='XXBRXCOM';

l_chr_prm_desc VARCHAR2(60) :='XXBR - XCOM Transfer of files Inbound/Outbound';

l_chr_interface VARCHAR2(60) :='OUT';

l_chr_modu_name VARCHAR2(200) :=p_in_module_name;

l_chr_local_name VARCHAR2(200) :=p_in_local_file||l_chr_filename;

l_chr_remote_file VARCHAR2(200) :=p_in_remote_file||l_chr_filename;

l_num_success_flag NUMBER;

l_chr_req_phase VARCHAR2(200);

l_chr_req_status VARCHAR2(200);

l_chr_req_dev_phase VARCHAR2(200);

l_chr_req_dev_status VARCHAR2(200);

l_chr_req_message VARCHAR2(200);

l_bol_req_wait BOOLEAN;

l_chr_arch_path VARCHAR2(200);

l_chr_arch_dest_path VARCHAR2(200); */

XXBR_get_ob10_po_info_rec.CLOSED_CODE='OPEN'

THEN

FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD(XXBR_get_ob10_po_info_rec.Vendor_Num,7)||

RPAD(XXBR_get_ob10_po_info_rec.vendor_name,100)||

RPAD(XXBR_get_ob10_po_info_rec.vendor_site_code,15)||

RPAD(XXBR_get_ob10_po_info_rec.PO_NUM,13)||

RPAD(XXBR_get_ob10_po_info_rec.CLOSED_CODE,15)||

RPAD(XXBR_get_ob10_po_info_rec.Amount,13)||

RPAD(XXBR_get_ob10_po_info_rec.ORG_ID,4)||

RPAD(XXBR_get_ob10_po_info_rec.currency_code,4)||

-RPAD('00',6)||

RPAD(' ',129));

---------------------------------------------

-- Writting the Supplier details in a file --

---------------------------------------------

UTL_FILE.PUT_LINE(l_file_cust,RPAD(XXBR_get_ob10_po_info_rec.Vendor_Num,7)||

RPAD(XXBR_get_ob10_po_info_rec.vendor_name,100)||

RPAD(XXBR_get_ob10_po_info_rec.vendor_site_code,15)||

RPAD(XXBR_get_ob10_po_info_rec.PO_NUM,13)||

RPAD(XXBR_get_ob10_po_info_rec.CLOSED_CODE,15)||

RPAD(XXBR_get_ob10_po_info_rec.Amount,13)||

RPAD(XXBR_get_ob10_po_info_rec.ORG_ID,4)||

RPAD(XXBR_get_ob10_po_info_rec.currency_code,4)||

RPAD('00',6)||

RPAD(' ',129));

ELSIF XXBR_get_ob10_po_info_rec.CLOSED_CODE='CLOSED'

THEN

FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD(XXBR_get_ob10_po_info_rec.Vendor_Num,7)||

RPAD(XXBR_get_ob10_po_info_rec.vendor_name,100)||

RPAD(XXBR_get_ob10_po_info_rec.vendor_site_code,15)||

RPAD(XXBR_get_ob10_po_info_rec.PO_NUM,13)||

RPAD(XXBR_get_ob10_po_info_rec.CLOSED_CODE,15)||

RPAD(XXBR_get_ob10_po_info_rec.Amount,13)||

RPAD(XXBR_get_ob10_po_info_rec.ORG_ID,4)||

RPAD(XXBR_get_ob10_po_info_rec.currency_code,4)||

RPAD('22',6)||

RPAD(' ',129));

---------------------------------------------

-- Writting the Supplier details in a file --

---------------------------------------------

UTL_FILE.PUT_LINE(l_file_cust,RPAD(XXBR_get_ob10_po_info_rec.Vendor_Num,7)||

RPAD(XXBR_get_ob10_po_info_rec.vendor_name,100)||

RPAD(XXBR_get_ob10_po_info_rec.vendor_site_code,15)||

RPAD(XXBR_get_ob10_po_info_rec.PO_NUM,13)||

RPAD(XXBR_get_ob10_po_info_rec.CLOSED_CODE,15)||

RPAD(XXBR_get_ob10_po_info_rec.Amount,13)||

RPAD(XXBR_get_ob10_po_info_rec.ORG_ID,4)||

RPAD(XXBR_get_ob10_po_info_rec.currency_code,4)||

RPAD('22',6)||

RPAD(' ',129));

END IF;

END LOOP;

XXBR_get_ob10_po_info_rec.CLOSED_CODE='OPEN'

THEN

FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD(XXBR_get_ob10_po_info_rec.Vendor_Num,7)||

RPAD(XXBR_get_ob10_po_info_rec.vendor_name,100)||

RPAD(XXBR_get_ob10_po_info_rec.vendor_site_code,15)||

RPAD(XXBR_get_ob10_po_info_rec.PO_NUM,13)||

RPAD(XXBR_get_ob10_po_info_rec.CLOSED_CODE,15)||

RPAD(XXBR_get_ob10_po_info_rec.Amount,13)||

RPAD(XXBR_get_ob10_po_info_rec.ORG_ID,4)||

RPAD(XXBR_get_ob10_po_info_rec.currency_code,4)||

-RPAD('00',6)||

RPAD(' ',129));

---------------------------------------------

-- Writting the Supplier details in a file --

---------------------------------------------

UTL_FILE.PUT_LINE(l_file_cust,RPAD(XXBR_get_ob10_po_info_rec.Vendor_Num,7)||

RPAD(XXBR_get_ob10_po_info_rec.vendor_name,100)||

RPAD(XXBR_get_ob10_po_info_rec.vendor_site_code,15)||

RPAD(XXBR_get_ob10_po_info_rec.PO_NUM,13)||

RPAD(XXBR_get_ob10_po_info_rec.CLOSED_CODE,15)||

RPAD(XXBR_get_ob10_po_info_rec.Amount,13)||

RPAD(XXBR_get_ob10_po_info_rec.ORG_ID,4)||

RPAD(XXBR_get_ob10_po_info_rec.currency_code,4)||

RPAD('00',6)||

RPAD(' ',129));

ELSIF

XXBR_get_ob10_po_info_rec.CLOSED_CODE='CLOSED'

THEN

FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD(XXBR_get_ob10_po_info_rec.Vendor_Num,7)||

RPAD(XXBR_get_ob10_po_info_rec.vendor_name,100)||

RPAD(XXBR_get_ob10_po_info_rec.vendor_site_code,15)||

RPAD(XXBR_get_ob10_po_info_rec.PO_NUM,13)||

RPAD(XXBR_get_ob10_po_info_rec.CLOSED_CODE,15)||

RPAD(XXBR_get_ob10_po_info_rec.Amount,13)||

RPAD(XXBR_get_ob10_po_info_rec.ORG_ID,4)||

RPAD(XXBR_get_ob10_po_info_rec.currency_code,4)||

RPAD('22',6)||

RPAD(' ',129));

---------------------------------------------

-- Writting the Supplier details in a file --

---------------------------------------------

UTL_FILE.PUT_LINE(l_file_cust,RPAD(XXBR_get_ob10_po_info_rec.Vendor_Num,7)||

RPAD(XXBR_get_ob10_po_info_rec.vendor_name,100)||

RPAD(XXBR_get_ob10_po_info_rec.vendor_site_code,15)||

RPAD(XXBR_get_ob10_po_info_rec.PO_NUM,13)||

RPAD(XXBR_get_ob10_po_info_rec.CLOSED_CODE,15)||

RPAD(XXBR_get_ob10_po_info_rec.Amount,13)||

RPAD(XXBR_get_ob10_po_info_rec.ORG_ID,4)||

RPAD(XXBR_get_ob10_po_info_rec.currency_code,4)||

RPAD('22',6)||

RPAD(' ',129));

END IF;

END LOOP;

l_num_rec_count := l_num_rec_count + 1;

END LOOP;

END IF;

UTL_FILE.FCLOSE(l_file_cust);

IF l_num_rec_count > 0

THEN

FND_FILE.PUT_LINE(FND_FILE.LOG,'Completed the PO Extract Successfully');

FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'**************************');

FND_FILE.PUT_LINE(FND_FILE.LOG,'Total Number of Records Extracted : ' || l_num_rec_count);

FND_FILE.PUT_LINE(FND_FILE.LOG,'**************************************');

FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'***********************************');

FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Total Number of Records Extracted : ' || l_num_rec_count);

FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'***********************************');

END IF;

COMMIT;

EXCEPTION

WHEN UTL_FILE.INVALID_PATH

THEN

FND_FILE.PUT_LINE(FND_FILE.LOG,'ERROR: utl_file.invalid_path: '||p_in_output_directory );

FND_FILE.PUT_LINE(FND_FILE.LOG, SUBSTR(SQLERRM,1,400));

RAISE PROGRAM_ERROR;

WHEN

UTL_FILE.INVALID_MODE

THEN

FND_FILE.PUT_LINE(FND_FILE.LOG,'ERROR: utl_file.invalid_mode: '||l_chr_filename);

FND_FILE.PUT_LINE(FND_FILE.LOG, SUBSTR(SQLERRM,1,400));

RAISE PROGRAM_ERROR;

WHEN UTL_FILE.INVALID_OPERATION THEN FND_FILE.PUT_LINE(FND_FILE.LOG,'ERROR: utl_file.invalid_operation:'||l_chr_filename);

FND_FILE.PUT_LINE(FND_FILE.log, SUBSTR(SQLERRM,1,400));

RAISE PROGRAM_ERROR;

WHEN UTL_FILE.INVALID_FILEHANDLE THEN FND_FILE.PUT_LINE(FND_FILE.LOG,'ERROR: utl_file.invalid_filehandle:'||l_chr_filename);

FND_FILE.PUT_LINE(FND_FILE.LOG, SUBSTR(SQLERRM,1,400));

RAISE PROGRAM_ERROR;

WHEN UTL_FILE.WRITE_ERROR THEN FND_FILE.PUT_LINE(FND_FILE.LOG,'ERROR: utl_file.write_error:'||l_chr_filename);

FND_FILE.PUT_LINE(FND_FILE.LOG, SUBSTR(SQLERRM,1,400));

RAISE PROGRAM_ERROR;

WHEN OTHERS THEN FND_FILE.PUT_LINE(FND_FILE.LOG,'Error while creating PO extract File: '||l_chr_filename || ' '|| SQLERRM);

FND_FILE.PUT_LINE(FND_FILE.LOG, SUBSTR(SQLERRM,1,400));

RAISE PROGRAM_ERROR;

END;

END XXBR_AP_ob10_po_extract_proc;

END XXBR_AP_int_ob10_po_ext_pkg;

Bhaskar Reddy Oracle Applications

Package for extracting PO's of OB10 Suppliers

No comments:

Post a Comment

How to find all cancel Requisitions