Miscellaneous

set serveroutput on ;
--EXEC dbms_output.enable(1000000);
set feedback off;
WHENEVER SQLERROR EXIT FAILURE ROLLBACK
declare
m_line varchar2(150);
m_transfer_id varchar2(2):='01';
m_part_no varchar2(15);
m_plant_code varchar2(2):='LT';
m_currency varchar2(2):='YN';
m_po_price varchar2(7);
m_decimal varchar2(2);
m_previous_due_date varchar2(6):='000000';
m_shipment_date date;
m_previous_issue_date varchar2(6):='000000';
m_approved_date varchar2(6);
m_revision_level varchar2(2):='00';
m_emergency_code char(1):='M';
m_reason_code char(1):=' ';
m_cancel_code char(1):=' ';
m_previous_quantity varchar2(7):='0000000';
m_shipment_via_code char(1):=' ';
m_status_change_code char(1):=' ';
m_status_change_code2 char(11):=' ';
m_fax_ref varchar2(10);
m_model char(10):=' ';
m_description char(15);
m_creation_date varchar2(6);
cursor corder1 is
select a.po_header_id,B.po_line_id,B.item_id, rpad(A.segment1,10) po_no,
lpad(to_char(B.quantity),7,0) po_qty, A.currency_code currency,
B.unit_price,B.vendor_product_num,
trunc(A.APPROVED_DATE) approved_date, a.attribute8 emergency_Ref,
a.CREATION_DATE Creation_date ,
a.attribute7 acceptance_fax_ref, freight_terms_lookup_code
from po_headers_all a, po_lines_all b
where A.segment1 = '&1'
and a.po_header_id=b.po_header_id;

begin
for corder in corder1 loop
m_line:='01';
m_po_price:=lpad(trunc(corder.unit_price),7,0);
m_decimal:=rpad(corder.unit_price*100-trunc(corder.unit_price)*100,2,0);
select rpad(segment1,15) into m_part_no
from mtl_system_items where inventory_item_id=corder.item_id and rownum=1;
select NEED_BY_DATE INTO m_shipment_date
from po_line_locations_all where po_line_id=corder.po_line_id
AND ROWNUM=1;
if corder.emergency_ref is null then
m_emergency_code:='M';
else
m_emergency_code:=substr(corder.emergency_ref,1,1);
end if;
select rpad(nvl(description,' '),15) INTO m_description
from mtl_system_items where
inventory_item_id=corder.item_id and rownum=1;
if substr(corder.freight_terms_lookup_code,1,1)='A' then
m_shipment_via_code:='A';
else
m_shipment_via_code:=' ';
end if;
if m_emergency_code='M' then
m_approved_date:='000000';
m_fax_ref:=' ';
else
m_approved_date:=rpad(nvl(to_char(corder.approved_date,'yymmdd'),' '),6);
m_fax_ref:=rpad(nvl(corder.acceptance_fax_ref,' '),10);
end if;
m_creation_date := rpad(nvl(to_char(corder.creation_date ,'yymmdd'),' '),6);
fnd_file.put_line(FND_FILE.log,'Creation date : ' || m_creation_date);
m_line:=m_line||m_part_no||m_plant_code||corder.po_no||corder.po_qty||m_currency||
m_po_price||m_decimal||rpad(nvl(corder.vendor_product_num,' '),7)||
m_previous_due_date||rpad(nvl(to_char(m_shipment_date,'yymmdd'),' '),6)||
m_previous_issue_date||m_creation_date||
m_revision_level||m_emergency_code||
m_reason_code||m_cancel_code||m_previous_quantity||
m_shipment_via_code||m_status_change_code||
m_status_change_code2||m_fax_ref||
m_model||m_description ;
--dbms_output.put_line(to_char(length(m_line)));
--dbms_output.put_line(m_line);
fnd_file.put_line(FND_FILE.output, m_line);
end loop;
end;
/