REM $Header: TPOSR01C.sql 2000/12/18 porting ship $ *
rem * Report Name: TPOSR01C.sql (±ÄÁʳæ-¤£®Mªí) FOR NINGBO *
rem * *
rem * ARGUMENTS: *
rem * &&1 - ORG_ID *
rem * &&2 - SEGMENT1 (PO_NUMBER) *
rem * &&3 - SEGMENT2 (PO_NUMBER) *
rem * &&4 - SEGMENT3 (PO_NUMBER) *
rem * &&5 - SEGMENT4 (PO_NUMBER) *
rem * &&6 - SEGMENT5 (PO_NUMBER) *
rem * &&7 - SEGMENT6 (PO_NUMBER) *
rem * &&8 - SEGMENT7 (PO_NUMBER) *
rem * &&9 - SEGMENT8 (PO_NUMBER) *
rem * &&10 - SEGMENT9 (PO_NUMBER) *
rem * &&11 - SEGMENT10(PO_NUMBER) *
rem * HISTORY *
rem * *
rem * Date Modified by Explanation *
rem * =========== =========== ===================================== *
rem * 12-DEC-2000 Eleana Chen Copy TPOSR01B.sql *
rem * 22-MAY-2001 Eleana Chen +TAX amount *
rem * ============ =========== ====================================== *
rem * *
rem **************************************************************************
CLEAR COLUMNS
CLEAR BREAKS
TTITLE OFF
BTITLE OFF
set termout off
set verify off
set feedback off
set heading on
set linesize 105
set pagesize 0
set space 1
column tmp1 new_value pur noprint ;
select 'txc_'||txc_request_id.nextval tmp1 from dual;
column s_page new_value skip_p noprint ;
select chr(12) s_page from dual;
column po_line_id new_value po_lineid noprint format 99999999;
column vendor_name new_value vendors noprint format a50;
column phone_no new_value phone noprint format a18;
column fax_no new_value fax noprint format a18;
column address new_value addresses noprint format a80;
column name new_value con_name noprint format a30;
column currency_code new_value code noprint format a6;
column cu_rate new_value l_rate noprint format 99.9999;
column todays new_value today_date noprint format a10;
column tax_rate new_value t_rate noprint format a2;
column tax_rate1 new_value t_rate_name noprint format 99;
column po_num new_value po_number noprint format a10;
column term_desc new_value term_description noprint format a16;
column n_vendor new_value note_vendor noprint format a100;
column location_addr new_value location_addrs noprint format a80;
TTITLE SKIP 4 COL 1 'NGB' COL 42 'q ÁÊ ³æ' COL 82 '¹q¤l¦³¤½¥q' -
SKIP 1 COL 82 'TXC(NGB) CORPORATION' -
skip 3 col 1 'Ä_ ¸¹: ' vendors col 84 '¤é ´Á: ' today_date -
skip 1 col 1 '¹q ¸Ü: ' phone col 46 '¶Ç¯u: ' fax col 84 '³æ ¸¹: ' po_number -
skip 1 col 1 '¦a §}: ' addresses -
skip 1 col 1 'Ápµ¸¤H: ' con_name col 32 '¹ô§O: 'code col 46 '¶×²v:'l_rate col 72 'µ|§O: 't_rate ' (1-À³µ| 2-§Kµ|)' -
skip 1 col 72 'µ|²v: ' t_rate_name '%' -
skip 2;
BTITLE col 1 '---------------------------------------------------------------------------------------------------------' -
SKIP 1 col 1 '1. ¥I´Ú±ø¥ó: ' term_description -
SKIP 1 col 1 ' ³Æµù: 'note_vendor -
SKIP 1 col 1 '2. ¥]¸Ë³W®æ: ' -
SKIP 1 col 1 '3. ¥æ³f¦aÂI: ' location_addrs -
SKIP 1 col 1 '4. °e³f³æ¥²»Ýµù©úq³æ¸¹½X.' -
SKIP 1 col 1 '5. ¥¼¸g¥»¤½¥q¦P·N¹O´Á¤T¤Ñ¤º¥æ³f,¼t°Ó¦P·N¥IÁ`»ù´Ú¤§2%§@¬°¹H¬ùª÷,¹O´Á¤¤Ñ§Y¸Ñ°£¦X¬ù©Ò¨ü¤§¤@¤Á·l¥¢.' -
SKIP 1 col 1 '6. ©Ò¥æ¤§³f«~¤£¦X³W®æ«~½è®É,À³©ó¤T¤Ñ¤º½Õ´«¦X®æ«~,¦p¹O¤»¤Ñ¤´µLªk½Õ´«¦X®æ«~À³¨Ì²Ä¤¶µ¤§³W©w½×©w.' -
SKIP 1 col 1 '7. Y«~½è³W®æ¤£¦X¼Æ¶qµu¯Êµ¥,¦]¦Ó»X°ê¥~ÅU«È½Ð¨D½ßÀv®É,¼t°ÓÄ@¦Û¦æt³d.' -
SKIP 1 col 1 '8. ¥»q³f¦X¬ù¸gÂù¤èñ³¹«á¥Í®Ä.' -
SKIP 1 col 1 '----------------------------------------------------------------------------------------------------------' -
SKIP 2 col 1 '®Öã: ½Æ®Ö: ¼f®Ö: ±ÄÁÊ: ¼t°Ó:'-
SKIP 2 col 1 '----------------------------------------------------------------------------------------------------------' -
SKIP 1 col 1 '¥Õ Áp : ±Ä ÁÊ Áp, ¬õ Áp:½Ð ÁÊ Áp, ¶À Áp:·| p Áp' -
skip 1 col 1 ' 'skip_p
column po_line_no heading "¶µ¦¸" format a4;
column pr_no heading "½ÐÁʳ渹" format a10;
column item_no heading "«~¸¹³W®æ" format a14;
column description heading "«~¦W" format a60 wrap;
column pr_reference_num heading "°Ñ¦Ò¸¹½X" format a13;
column dept_p heading "³¡ªù" format a4;
column account_p heading "ACCOUNT CODE" format a20;
column pdate heading "¹w¥æ¤é" format a11;
column uom heading "³æ¦ì" format a4;
column qty heading "¼Æ¶q" format 9,999,999.9999;
column price heading "³æ»ù" format 9,999,999.999999;
column amount heading "ª÷ÃB" format 999,999,999.9999;
column tax_amt heading "TAX" format 999,999,999.99
column space1 heading "space1" format a24;
column space2 heading "space2" format a2;
column space3 heading "space3" format a5;
column space4 heading "space4" format a67;
column space5 heading "space5" format a2;
column space6 heading "space6" format a1;
----------------------
-- CREATE TEMP TABLE
----------------------
CREATE TABLE &pur
( vendor varchar2(80),
phone varchar2(25),
fax_phone varchar2(25),
addr varchar2(35),
today varchar2(11),
rate number,
--- tax_rate_p number,
po_num varchar2(20),
term varchar2(240),
location_id number,
po_line varchar2(6),
po_line_id number,
pr_num varchar2(20),
item varchar2(40),
item_desc varchar2(240),
uom1 varchar2(25),
quantity number,
unit_price number,
promised_date varchar2(11),
pr_line number,
pr_reference varchar2(30),
amount number,
tamount number,
contracter varchar2(35),
vendor_contact_id number,
item_id number,
org_id number,
line_loca_id number,
currency varchar2(6),
curr_rate number,
sumamt number,
locationaddr varchar2(240),
dept_code varchar2(05),
account_code varchar2(10),
account_name varchar2(40),
note_to_vendor varchar2(100)
);
DECLARE
m_item_no varchar2(20) := '';
m_contact_name varchar2(35) := '';
m_line_location number := 0 ;
m_pr_no varchar2(20) := '';
m_pr_line number := 0 ;
m_reference_num varchar2(30) := '';
m_location_addr varchar2(240) := '';
m_dept_code varchar2(05) := '';
m_account_code varchar2(10) := '';
m_account_name varchar2(40) := '';
------------------------
-- cursor for cu_1
------------------------
CURSOR cu_1 IS
select pv.vendor_name vend,
pvs.area_code||'-'||pvs.phone ph_no,
pvs.fax_area_code||'-'||pvs.fax fa_no,
pvs.address_line1 addre,
to_char(sysdate,'dd-mm-yyyy') to_days,
atc.tax_rate tax,
ph.segment1 pono,
ap.name term_description,
pll.ship_to_location_id loca_id,
pl.line_num||'-'||pll.shipment_num po_lineno,
pl.po_line_id po_line_id,
pl.item_id item_id,
pl.org_id org_id,
pl.item_description descrip,
NVL(pl.attribute1,pl.unit_meas_lookup_code) i_uom,
pll.quantity-nvl(pll.quantity_cancelled,0) p_qty,
pl.unit_price u_price,
to_char(nvl(pll.promised_date,pll.need_by_date),'DD-MON-YYYY') pr_date,
(pll.quantity-nvl(pll.quantity_cancelled,0))*pl.unit_price samt,
(pll.quantity-nvl(pll.quantity_cancelled,0))*pl.unit_price*(atc.tax_rate/100) tamt,
ph.vendor_contact_id v_site_id,
pll.line_location_id location_id,
ph.currency_code currency,
nvl(ph.rate,1) c_rate,
ph.note_to_vendor n_vendor
from po_vendors pv,
po_vendor_sites_all pvs,
po_headers_all ph,
po_lines_all pl,
ap_terms ap,
po_line_locations_all pll,
ap_tax_codes_all atc
where ph.segment1 in (nvl('&&2' ,ph.segment1), nvl('&&3' ,''), nvl('&&4' ,''), nvl('&&5' ,''),
nvl('&&6' ,''), nvl('&&7' ,''), nvl('&&8' ,''), nvl('&&9' ,''),
nvl('&&10' ,''), nvl('&&11',''))
and ph.type_lookup_code = 'STANDARD'
and ph.org_id = 141
and ph.terms_id = ap.term_id
and atc.tax_id = pll.tax_code_id
and atc.org_id = nvl('&&1',141)
and pvs.vendor_site_id = ph.vendor_site_id
and pv.vendor_id = pvs.vendor_id
and pl.po_header_id = ph.po_header_id
and pl.org_id = ph.org_id
and pll.po_line_id = pl.po_line_id
and pll.po_header_id = pl.po_header_id
and pll.org_id = pl.org_id
and pll.quantity-nvl(pll.quantity_cancelled,0) > 0
and pll.ship_to_organization_id = 141
order by ph.segment1;
lr_1 cu_1%ROWTYPE;
-------------------
----PROC get_item_no
-------------------
PROCEDURE get_item_no IS
BEGIN
BEGIN
SELECT SUBSTRB(msi.segment1,1,12)
INTO m_item_no
FROM mtl_system_items msi
WHERE msi.inventory_item_id = lr_1.item_id
AND msi.organization_id = lr_1.org_id
;
EXCEPTION
WHEN NO_DATA_FOUND THEN
m_item_no := ' ';
END;
END;
-------------------
----PROC get_contact_name
------------------
PROCEDURE get_contact_name IS
BEGIN
BEGIN
SELECT pvc.last_name || pvc.first_name
INTO m_contact_name
FROM po_vendor_contacts pvc
WHERE pvc.vendor_contact_id = lr_1.v_site_id
;
EXCEPTION
WHEN NO_DATA_FOUND THEN
m_contact_name := ' ';
END;
END;
-------------------
----PROC get_pr_no
------------------
PROCEDURE get_pr_no IS
BEGIN
BEGIN
SELECT max(prh.segment1)
,max(prl.line_num)
,max(prl.reference_num)
,max(prl.line_location_id)
,max(substrb(gcc.segment2,1,5))
,max(substrb(gcc.segment3,1,10))
,max(SUBSTRB(fv.description,1,40) )
INTO m_pr_no
,m_pr_line
,m_reference_num
,m_line_location
,m_dept_code
,m_account_code
,m_account_name
FROM po_requisition_lines_all prl
,po_requisition_headers_all prh
,po_req_distributions_all prd
,gl_code_combinations gcc
,applsys.fnd_flex_values_tl fv
----- ,applsys.fnd_flex_value_sets fs
WHERE prl.line_location_id = lr_1.location_id
and prl.org_id = lr_1.org_id
-- AND ( prl.line_location_id <> 66631 AND substrb(gcc.segment2,1,5)<>'H14N' )
AND prh.requisition_header_id = prl.requisition_header_id
and prh.org_id = prl.org_id
and prd.requisition_line_id = prl.requisition_line_id
and prd.org_id = prl.org_id
and prd.code_combination_id = gcc.code_combination_id
and fv.flex_value_meaning = gcc.segment3
----- and fs.flex_value_set_id = fv.flex_value_set_id
----- and fs.flex_value_set_name IN ('TXC_ACCT_CODE','NGB_ACCT_CODE')
;
EXCEPTION
WHEN NO_DATA_FOUND THEN
m_pr_no := '';
m_pr_line := ' ';
m_reference_num := ' ';
m_line_location := ' ';
m_dept_code := ' ';
m_account_code := ' ';
m_account_name := ' ';
END;
END;
----------------------------------------------
--- insert data into print file
----------------------------------------------
PROCEDURE insert_data IS
BEGIN
INSERT INTO &pur VALUES (
lr_1.vend
,lr_1.ph_no
,lr_1.fa_no
,lr_1.addre
,lr_1.to_days
,lr_1.tax
------------------------ ,lr_1.tax_rate_p
,lr_1.pono
,lr_1.term_description
,lr_1.loca_id
,lr_1.po_lineno
,lr_1.po_line_id
,m_pr_no
,m_item_no
,lr_1.descrip
,lr_1.i_uom
,lr_1.p_qty
,lr_1.u_price
,lr_1.pr_date
,m_pr_line
,m_reference_num
,lr_1.samt
,lr_1.tamt
,m_contact_name
,lr_1.v_site_id
,lr_1.item_id
,lr_1.org_id
,lr_1.location_id
,lr_1.currency
,lr_1.c_rate
,0
,m_location_addr
,m_dept_code
,m_account_code
,m_account_name
,lr_1.n_vendor
);
END ;
---------------------------
-- MAIN PROGRAMS
---------------------------
BEGIN
OPEN cu_1;
LOOP
FETCH cu_1 INTO lr_1;
EXIT WHEN cu_1%NOTFOUND;
m_item_no := '';
m_contact_name := '';
m_line_location := 0 ;
m_pr_no := '';
m_pr_line := 0 ;
m_reference_num := '';
m_location_addr := '';
m_dept_code := '';
m_account_code := '';
m_account_name := '';
get_item_no;
get_contact_name;
get_pr_no;
m_location_addr := '¤¤°ê®ý¦¿';
insert_data;
END LOOP;
CLOSE CU_1;
END;
/
break on po_num skip page
COMPUT SUM OF amount tax_amt on po_num report
set pagesize 60
select vendor vendor_name,
phone phone_no,
fax_phone fax_no,
addr address,
contracter name,
today todays,
decode(rate,'','2','0','2','1') tax_rate,
rate tax_rate1,
po_num po_num,
term term_desc,
locationaddr location_addr,
currency currency_code,
curr_rate cu_rate,
po_line po_line_no,
substrb(pr_num,1,10)||'-'||substrb(to_char(pr_line),1,3) pr_no,
po_line_id,
item item_no,
item_desc description,
pr_reference pr_reference_num,
substrb(dept_code,1,4) dept_p,
substrb(account_code,1,8)||' '||substrb(account_name,1,12) account_p,
promised_date pdate,
uom1 uom,
quantity qty,
trunc(unit_price,6) price,
amount amount,
tamount tax_amt,
note_to_vendor n_vendor
from &pur
order by po_num, po_line_id ;
DROP TABLE &PUR;
COMMIT;
set verify on
clear computes
CLEAR BREAK
CLEAR COLUMNS
TTITLE OFF
BTITLE OFF
992
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
