PO主要SQL
--职位 position
select * from PER_POSITIONS_V
where (position_id,job_id) in (select position_id,job_id from PO_POSITION_CONTROLS_ALL where org_id = 108)
--职位层次结构
select * from PER_POSITION_STRUCTURES_V
--组织
select * from HR_ORGANIZATION_UNITS_V where organization_id = 89
select * from hr_operating_units --OU
--职务job
select * from PER_JOBS_VL
--采购员
select * from PO_AGENTS_V
--审批分配--23295700889.市物流采购专项管理
select * from PO_POSITION_CONTROLS_ALL where org_id = 108
--审批组
select * from PO_CONTROL_GROUPS_ALL
--质量检验代码
select * from PO_QUALITY_CODES
--计划员
select * from MTL_PLANNERS
4. POSITION_CLASS的验证程序为：
select c.description
from fnd_flex_values a,
fnd_flex_value_sets b,
fnd_flex_values_tl c
where a.flex_value_set_id = b.flex_value_set_id
and a.flex_value_id = c.flex_value_id
and b.flex_value_set_name='POSITION_CLASS_CODE'
and c.language='ZHS'
and a.enabled_flag='Y'
and trunc(sysdate) between trunc(nvl(a. start_date_active,sysdate)) and trunc(nvl(a. end_date_active,sysdate))
and a.flex_value=:$FLEX$.POSITION_CLASS_CODE
select * from PO_QUALITY_CODES
select * from fnd_user where user_name = 'SYSADMIN'
SELECT * from per_people_f where employee_number ='23010331'
person_id = 60118
begin
dbms_application_info.set_client_info(83);
end;
--1. 选料好
select * from all_tables where table_name like '%ITEM%'
select segment1,organization_id from MTL_SYSTEM_ITEMS
--where organization_id = 89 and description like '%SIM%'--12030400000009
where inventory_item_id = 238
130453 4, 82
--Account
select * from gl_code_combinations where code_combination_id = 1381
select * from gl_code_combinations_kfv
--2. PR
select * from po_requisition_headers_all where /*org_id = 83*/
segment1= '621000001' for update;--authorization_flag incomplete-->approved
select * from po_requisition_headers_all where requisition_header_id = 60;
select * from po_requisition_lines_all where requisition_header_id = 60;
select * from po_req_line where requisition_header_id = 60;
select * from po_req_distributions_all where requisition_line_id = 29;
--询价
select * from PO_HEADERS_RFQQT_V where segment1= '200320001';
select * from po_headers_all where segment1= '200320001';
--报价
select * from PO_HEADERS_RFQQT_V where segment1= '200340001';
select * from po_headers_all where segment1= '200340001';
PO_LINES_RFQQT_V
PO_LINE_LOCATIONS_V
3. PO
--题头
select * from po_headers_all where po_header_id = 146001
segment1 in('821022672','821020797') ;
821022668
821022672
--行
select * from po_lines_all where po_header_id >146001
--发运
select * from po_line_locations_all where po_line_id in( 674735,697544)
--分配
select * from po_distributions_all where po_header_id = 146002
and line_location_id in(705320,705321) for update
update po_headers_all
set authorization_status = 'APPROVED',approved_flag = 'Y',approved_date = sysdATE
where po_header_id = 146001;
update po_line_locations_all --审核还需要修改line_location
set approved_flag = 'Y',approved_date = sysdATE
where po_line_id =697544
--4. Blanket,Planned PO release
select * from PO_RELEASES_ALL where po_header_id = 146002;
select * from gl_code_combinations_kfv;
update PO_RELEASES_ALL
set authorization_status = 'APPROVED',approved_flag = 'Y',approved_date = sysdATE
where po_header_id = 146002;
--5, ASN
装配单？没找到
select * from all_tables where table_name like '%ASN%'
--6. Receipt
select * from RCV_SHIPMENT_HEADERS where receipt_num in( '21000993','26003676') and ship_to_org_id = 89
and shipment_header_id in(1040776,863647);
select * from RCV_SHIPMENT_lineS where shipment_header_id in(1040776 )
--PO_HEADER_ID line_id,line_location_id, distribution_id;
--验收
select TRANSACTION_ID,
TRANSACTION_TYPE,
QUANTITY,
UNIT_OF_MEASURE,
SHIPMENT_HEADER_ID,
SHIPMENT_LINE_ID,
INTERFACE_SOURCE_CODE,
SOURCE_DOCUMENT_CODE,
DESTINATION_TYPE_CODE,
PRIMARY_QUANTITY,
PO_HEADER_ID,
PO_LINE_ID,
PO_LINE_LOCATION_ID,
PO_DISTRIBUTION_ID
from RCV_transactions
where shipment_header_id = 1040776
and interface_source_code = 'RCV';
select * from rcv_transactions_interface where interface_transaction_id = 1133579
select TRANSACTION_ID,
TRANSACTION_TYPE_ID,
TRANSACTION_ACTION_ID,
TRANSACTION_SOURCE_TYPE_ID,
TRANSACTION_SOURCE_ID,
TRANSACTION_QUANTITY,
TRANSACTION_UOM,
DISTRIBUTION_ACCOUNT_ID,
TRANSACTION_SET_ID,
RCV_TRANSACTION_ID,
SOURCE_CODE,
SOURCE_LINE_ID
from MTL_MATERIAL_TRANSACTIONS
where rcv_transaction_id = 5267053 --deliver transaction id
and transaction_source_type_id = 1; --Purchasing Order
SELECT nvl(SUM(nvl(mmt.primary_quantity, 0)), 0)
FROM mtl_material_transactions mmt
WHERE mmt.rcv_transaction_id = 5267053 --deliver transaction id
and mmt.transaction_source_type_id = 1;
select * from Mtl_Transaction_Accounts
where transaction_id = 35233774;
---退货
select rcv_transaction_id from mtl_material_transactions where primary_quantity<0
and rcv_transaction_id = 840528;
select TRANSACTION_ID,
TRANSACTION_TYPE_ID,
TRANSACTION_ACTION_ID,
TRANSACTION_SOURCE_TYPE_ID,
TRANSACTION_SOURCE_ID,
TRANSACTION_QUANTITY,
TRANSACTION_UOM,
DISTRIBUTION_ACCOUNT_ID,
TRANSACTION_SET_ID,
RCV_TRANSACTION_ID,
SOURCE_CODE,
SOURCE_LINE_ID
from MTL_MATERIAL_TRANSACTIONS
where rcv_transaction_id = 840528 --deliver transaction id
and transaction_source_type_id = 1;
