-【優(yōu)秀資料】oracleERP的SQL腳本

1、oracle erp 的 sql 腳木庫(kù)存貨位列表sqlselectinventory_locationd,organization_id,description,subinventory_code$egmentlinventor yo cation_codcfrom mtl_item_locationsorder by subinventoiy_code,inventory_location_code;庫(kù)存會(huì)計(jì)期間sqlselectstatus,period_name,period_number,period_year,start_date,end_date,close_date,rec_t

2、ype,organizationdfrom org_acct_periods_vwhere (rcc_typc = ,org_period,)order by end_date desc;庫(kù)存組織列sqlselect anization_id,from hr_all_organization_units awhere anization_id>82;事物處理活動(dòng)列表sqlselect * from mfg_lookups where lookup_type='mtl_transaction_action,order by lookup_code;事

3、物處理來(lái)源類型列表sqlselect transaction_source_type_name,description,validated.flag, transaction source type id,user dehned flag,disable datefrom mtl_txn_source_typeswhere user_dehned_flag 二 norder by transaction_source_type_name;接收事物處理sqlselect t.po_hcadcr_id, t.po_linc_id, t.po_rclcasc_id,sum (decode (t.tr

4、ansaction_type,deliver； .quantity,return to receiving1, decode (t.destination_type_code, 'inventory； -(.quantity, 'expense, -(.quantity, 0)，'correct', decode (t.destination_type_code,inventory； (.quantity, 'expense； (.quantity, 0),0)deliver_qtya,sum ( t.po_unit_price* t.currency_

5、conversion_rate* decode (t.transaction_type,deliver*, t.quantity,return to receiving1, decode(t.destinatio n_ type_code, ,inventory -(.quantity, expense；t.quantity, 0),'correct', decode (t.destination_type_code,inventory； t.quantity, expense1, (.quantity, 0)，0)deliver_amount,sum (decode (t.t

6、ransaction_typc,'receive', t.quantity,return to vendor', -t.quantity,'correct', decode (t.destination_type_code,receiving； .quantity,0),0)receive_qtya,sum ( t.po_unit_pricc* t.currency_conversion_rate* decode (t.transaction_type,'receive', t.quantity,'return to vendor

7、； -(.quantity, correct', decode (t.dcstination_typc_codc,receiving； t.quantity,0),)receive_amount,t.unit_of_measure, rsl.itemjd, rsl.item_revision,rsl.item_description, g.period_year, g.period_number, rsl.from_organization_idfrom po.rcv_transactions t,_acct_periods_v g,apps.rcv_shipment_

8、lines rslwhere ( t.transaction_type = 'deliver'or t.transaction_typc = return to receiving*or t.transaction_type = correct'or t.transaction_type = receive'or t.transaction_type = return to vendor1)and (.organization= anizationand to_date (to_char (t.transaction_date, 'yyyy-m

9、m-dd1), tyyy-mm-dd1)between g.start_dateand g.enddateand g.rec_type = 'org_period'and rsl.shipmcnt_linc_id = t.shipmcnt_lincgroup by t.po_header_id,t.poined,t.po_release_id,t.unit of measure,rsl.itcm_id,rsl.item_revision5rsl.item_description,g.period_year,g.period_number,rsl.from_organizatio

10、n_id;接收區(qū)數(shù)量金額sqlselect/* commented and added following line for 2245286nvl( ppp.costing_group_id, 1 ) cost_group_id, */nvl( pprcosting_group_id, mp.default_cost_group_id) cost_group_id,ms.item_id inventory_item_id,msi.description.msi.segment 1,rcv.po_unit_price item_cost,msto_org_primary_quantity rcv

11、qty,ms.to_org_primary_quantity *(rcv.po_unit_price + cstppacq.get_rcv_tax( rcv.transaction_id ) *nvl( decode( nvl(poll.match_option/pr),p, cstppacq.gct_po_ratc( rcv.transaction_id),r, rcv.currency_conversion_rate), 1 ) *(rcv.source_doc_quantity / rcv.primary_quantity ) rcvval, rcv.po_unit_price,cstp

12、pacq.get_rcv_tax( rcv.transaction_id ) dddd,poll.match_option,cstppacq.get_po_rate( rcv.transaction_id ) fffff, rcv.currency_conversion_rate,rcv.currency_code,rcv.currency_conversion_type, rcv.sourcc_doc_quantity, rcv.primary_quantity, clc.cost_type_id, cic.inventory_asset_flag frommtl_supply ms,/*

13、added for 2245286 */=deceiving1 and=ms.to_organizationand=ms.itemjd and=ms.rcv_transaction_id andnot in ('inventory, req) and=rcv.poine_location_id and=rcv.po_linc_id and=pol.project_id and=& p_org_1d and=&p.orgd and=msi.inventory_item_idcst_item_costs cic, rcv_transactions rcv, pojineoc

14、ations_all poll, po_lines_all pol, pj m_proj cct_paramctcrs ppp ,mtl_parameters mp jnv.mtl_systemtems_b msi wherem s. supply_type_codecic.organizatio n_id cic.inventory_item_id (+) rcv.transaction_id rcv.source_document_code pollineoca(ion_id pol.po_linc_id ppp.project_id (+) ms.to_organization_id m

15、p.organization_id ms.item_id order by msi.segment 1;接收單列表sqlselect rt.transaction_id, rt.transaction_type rt_transaction_type,rt.quantity rt_quantity, rt.quantity_billed rt_quantity_billed, rt.inspcction_status_codc rt_inspcction_status_codc, rt.inspection_quality_code rt_inspection_quality_code, rt

16、.destination_type_code rt_destination_type_code,poh.po_header_id,poh.segment 1,pol.line_num, pol.line_type_id, politem_description,pol.unit_meas_lookup_code, pol.list_price_per_unit, pol.unit_price, pol.quantity, pol.qty_rcv_tolerance, pol.closed_code, rsh.receipt_num, rsl.line_num, rsl.quantity_shi

17、pped, rsl.quantity_received, rsl.shipmentinc_status_coclc, rsl.sourcc_documcnt_codc, rsl.destination_type_code, rsl.asn_line_flagfrom po.rcv_transactions rt,po.rcv_shipment_headers rsh,po.rcv_shipmentincs rsl, po.po_hcadcrs_all poh, po.po_lines_all polwhere rsh.shipment_header_id = rt.shipment_heade

18、r_idand rsl.shipment_line_id = rt.shipment_line_idand poh.po_header_id(+) = rt.po_header_idand pol.po_linc_id(+) = rt.po_linc_idand exists(select * from apps.po_lookup_codes plcl where plcl.lookup_type = rcv transaction typeand rt.transactiontype = plclookup_code)and exists(select * from apps.po_loo

19、kup_codes plcl where plcl.lookup_type = shipment source type*and rsh.receipt_source_code = pic 1 .lookup_code)and ex1sts(select * from apps.po_lookup_codes plcl where pic 1 ,lookup_type = rcv destination type'and rt.destination_type_code = plclookup_code)and exists(select * from apps.po_lookup_c

20、odcs plcl where plcl.lookup_typc = shipment source document type1and rt.source_document_code = plcl.lookup_code);從總帳追溯到接收sqlselect jc_hcadcr_id, jc_linc_num, trx_class_namc, trx_typc_namc, trx_numbcr_displaycd, trx_date,comments, doc_sequence_name, doc_sequence_value, acct_line_type_name, currency_c

21、ode, entered_dr, entered_cr, accounteddr, accounted_cr, taxable_entered_dr, taxable_entered_cr, taxable_accounted_dr, taxablc_accountcd_cr, currcncy_convcrsion_datc, currency_user_conversion_type, currency_conversion_rate, third_party_name, third_party_number, third_party_sub_name, accounting_date,

22、gl_transfer_status_name, transfer_system_name, gl_transfer_from_to_name, accounting_complete_name, ac_linc_rcfcrcncc, tax_codc, accounting_rulc_namc, tax_cxcmpt_number, trx_line_type_name, trx_line_number, trx_detail_line_number, trx_quantity, sales_order_numbei salesrep_name, tax_rate, unit_selling

23、_price, trx_uom, trx_source_name, asset_number, assct_dcscription, assct_book_typc_codc, accounting_cvcnt_numbcr, accounting_event_type5 accounting_event_type_name, accounting_line_number, aeh_accounting_error_name, ael_accounting_error_name, userje_category_name, transfer_status_detail_name, applic

24、ation_date, applicd_to_trx_hdr_currcncy, applicd_to_trx_hdr_datc, applied_to_trx_hdr_id, applied_to_trx_hdr_number_c, applied_to_trx_hdr_number_disp, applied_to_trx_hdr_table, applied_to_trx_line_numbei; applied_to_trx_line_type, applied_to_trx_line_type_name, ar_activity_name, ar_adjustmcnt_crcatio

25、n_typc, ar_adjustmcnt_typc, bank_account_namc, bank_statement_doc_seq_id, bank_statement_doc_seq_name5 bank statement doc seq value, bank statement line number, bank_statement_number, bom_department_code, bom_department_name, chargeback_number, cost_element_id, cost_element_name, distribution_set_na

26、me, gl_batch_id, inventory_item_locator_id, inventory_item_locator_name, inventory_item_revision, item_description, inventory_organization_code, inventory_organization_id, cost_type_id, cost_type_name, mfg_operation_seq_num, payment_cleared_date, payment_date, paymcnt_dcposit_datc, paymcnt_numbcr, p

27、aymcnt_rccon_currcncy, po_line_num, po_order_number, po_order_release_num, price_override, po_order_shipment_num, po_order_distribution_num, po_order_type, rcv_receipt_num, rcv_shipment_header_id, reversalcomments, reversal_date, subinventory, trx_hdr_currency, trx_reason_name, trx_sourcc_typctrx_so

28、urcc_typc_namc, unit_cost, unit_pricc, wip_assembly, wip_basis, wip_flow_schedule_number, wipob_id、 wipob_name5 wip_line_code, wipine_id, wip_resource_id, wip_resource_name, wip_resource_seq_num, pa_employee_num, pa_employee_name, pa_supplier_num, pa_supplier_name, pa_project_num, pa_projcct_namc, p

29、a_task_num, pa_task_namc, pa_ci_cv_org, pa_nl_resource_org, pa_nl_resource, pa_ei_ev_type, pa_event_num, pa_quantity, pa_uom, pa_period_date, pa_gl_transfer_date, pa_customer_number, pa_customer_name, pa_agreement_num, pa_accrue_thru_date, pa_dri_number, pa_transpa_line_number, prvdr_organization_na

30、mc, rccvr_organization_namc, prvdr_ou_namc, recvr_ou_name5 cc_type_name, cc_proc_method_name, application_id, asset_id, asset_category_id, asset_key_ccid, code_combination_id, inventory_item_id, set_of_books_id, org_id, trx_class, trx_type_n, trx_type_c, trx_numbcr_n, trx_numbcr_c, doc_scqucncc_id,

31、trx_hdr_tablc, trx_hdr_id, acctine_type, currency_conversion_type, third_party_type, third_party_id, third_party_sub_id, gl_transfer_status, source_table, source_id, ael_table, ael_id, ach_accounting_crror_codc, acl_accounting_crror_codc, jc_catcgory, trx_line_type, trx_source_id, last_update_date,

32、last_updated_by, creation_date, created_by, last_update_login, request_id, program_applicationprogram_id, program_update_date, pa_project_id, pa_view_labor_costfrom xla_po_acl_gl_v xla_acl_gl_vwhereje_header_id= 1590and jeine_num = 419order by trx_date, rcv_receipt_num, trx_type_name;從總帳追溯到庫(kù)存sqlsele

33、ct je_header_id, je_line_num,trx_class_name, trx_type_name, trx_number_displayed, trx_date,comments, doc_sequence_name, doc_sequence_value, acct j i ne_type_name, currency_code, entered_dr, entered_cr, accounted_dr, accounted_cr, taxablc_cntcrcd_dr, taxablc_cntcrcd_cr, taxablc_accountcd_dr, taxable_

34、accounted_cr, currency_conversion_date, currency_user_conversion_type, currency_conversion_rate, third_party_name, third_party_number, third_party_sub_name, accounting_date, gl_transfer_status_name, transfer_system_name, gl_transfcr_from_to_namc, accounting_complctc_namc, ae_line_reference, tax_code

35、, accounting_rule_name5 tax_exempt_number, txine_type_name, trx_line_number, trx_detai 1_1 ine_number, trx_quantity, sales_order_number, salesrep_name, tax_rate, unit_selling_price, trx_uom, trx_source_name, asset_number, assct_dcscription, assct_book_typc_codc, accounting_cvcnt_numbcr, accounting_e

36、vent_type5 accounting_event_type_name, accounting_line_number, aeh_accounting_error_name, ael_accounting_error_name, userje_category_name, transfer_status_detail_name, application_date, applicd_to_trx_hdr_currcncy, applicd_to_trx_hdr_datc, applied_to_trx_hdr_id, applied_to_trx_hdr_number_c, applied_

37、to_trx_hdr_number_disp, applied_to_trx_hdr_table, applied_to_trx_line_numbei; applied_to_trx_line_type, applied_to_trx_line_type_name, ar_activity_name, ar_adjustmcnt_crcation_typc, ar_adjustmcnt_typc, bank_account_namc, bank_statement_doc_seq_id, bank_statement_doc_seq_name5 bank statement doc seq

38、value, bank statement line number, bank_statement_number, bom_department_code, bom_department_name, chargeback_number, cost_element_id, cost_element_name, distribution_set_name, gl_batch_id, inventory_item_locator_id, inventory_item_locator_name, inventory_item_revision, item_description, inventory_

39、organization_code, inventory_organization_id, cost_type_id, cost_type_name, mfg_opcration_scq_num, paymcnt_clcarcd_datc, paymcnt_datc, payment_deposit_date, payment_numbei payment_recon_currency, poine_num, po_order_number5 po_order_release_num, price_override, po_order_shipment_num, po_order_distri

40、bution_num, po_order_type, rcv_receipt_num, rcv_shipment_header_id, reversal_comments, rcvcrsal_datc, subinventory, trx_hdr_currcncy, trx_rcason_namc, trx_source_type_id, trx_source_type_name5 unit_cost, unit_price, wip_assembly, wip_basis, wip_flow_schedule_number, wipob_id, wipob_name, wip_line_co

41、de, wipine_id, wip_resource_id, wip_resource_name, wip_resource_seq_num, pa_employee_num, pa_cmploycc_namc, pa_supplicr_num, pa_supplicr_namc, pa_projcct_num, pa_project_name5 pa_task_num, pa_task_name, pa_ei_ev_org, pa_nl_resource_org, pa_nl_resource, pa_ei_ev_type, pa_event_num, pa_quantity, pa_uo

42、m, pa_period_date, pa_gl_transfer_date, pa_customer_number, pa_customer_name, pa_agreement_num, pa_accrue_thru_date, pa_dri_numbert pa_trans_id, pa_linc_numbcr, prvdr_organization_name, recvr_organization_name, prvdr_ou_name, recvr_ou_name, cc_type_name, cc_proc_method_name, application_id, asset_id

43、, asset_category_id,code_combination_id, inventory_item_id,sct_oflbooks_id, org_id, trx_class, trx_typc_n, trx_typc_c, trx_number_n, trx_number_c5 doc_sequence_id, trx_hdr_table, trx_hdr_id, acctine_type, currency_conversion_type, third_party_type, third_party_id, third_party_sub_id, gl_transfer_sta

44、tus, source_table, source_id, ael_table, ael_id, ach_accounting_crror_codc, acl_accounting_crror_codc, jc_catcgory, trx_line_type, trx_source_id, last_update_date, last_updated_by, creation_date, created_by, last_update_login, request_id, program_applicationprogram_id, program_update_date, pa_projec

45、t_id, pa_view_labor_costfrom xla_inv_acl_gl_v xla_acl_gl_vwhereje_header_id= 1573and je_line_num = 1205order by trx_date,trx_number_displayed, acct_li nc_typc_n amc, cost_element_name;從總帳追溯到發(fā)票sqlselect je_header_id, jeine_num, trx_class_name, trx_type_name, trx_number_displayed, trx_datc,comments, d

46、oc_sequence_name5 doc_sequence_value$ acct_iine_type_name5 currency_code, entered_dr, entered_cr, accounted_dr, accounted_cr5taxable_entered_dr, taxable_entered_cr, taxable_accounted_dr, taxablc_accountcd_cr, currcncy_convcrsion_datc, currency_user_conversion_type currency_conversion_nite, third_par

47、ty_name, third_party_number, third_party_sub_name, accounting_date, gl_transfer_status_name, transfer_system_name, gl_transfer_from_to_name, accounting_complete_name, ac_linc_rcfcrcncc, tax_codc, accounting_rulc_namc, tax_cxcmpt_number, trx_line_type_name, trx_line_number, trx_detail_line_number, tr

48、x_quantity, sales_order_number, salesrep_name, tax_rate, unit_selling_price, trx_uom, trx_source_name, asset_number, asset_description, asset_book_type_code, accounting_event_number, accounting_cvcnt_typc, accounting_cvcnt_typc_namc, accounting_line_number, aeh_accounting_error_name, ael_accounting_

49、error_name, userje_category_name, transfer_status_detail_name, application_date, applied_to_trx_hdr_currency, applied_to_trx_hdr_date, applicd_to_trx_hdr_id, applicd_to_trx_hdr_numbcr_c, applied_to_trx_hdr_number_disp5 applied_to_trx_hdr_table, applied_to_trxine_numbei applied_to_trxine_type, applie

50、d_to_trx _line_type_name, ar_activity_name, ar_adjustment_creation_type, ar_adjustment_type, bank_account_name,bank statement doc seq value, bank statement line number, bank_statement_number, bom_department_code, bom_department_name, chargebacknumber, cost_element_id, cost_element_name, distribution

51、_set_name, gl_batch_id, inventory_item_locator_id, inventorytcm_locator_namc, inventorytcm_rcvision, item_description, inventory_organization_code, inventory_organization_id, cost_type_id, cost_type_name, mfg_operation_seq_num, payment_cleared_date, payment_date, payment_deposit_date, payment_number

52、, payment_recon_currency, poinc_num, po_ordcr_numbcr, po_ordcr_rclcasc_num, pricc_ovcrridc, po_order_shipment_num, po_order_distribution_num, po_order_type, rcv_receipt_num, rcv_shipment_header_id, reversal_comments, reversal_date, subinventory, trx_hdr_currency, trx_reason_name, trx_source_type_id,

53、 trx_source_type_name, unit_cost, unit_price, wip_asscmbly, wip_basis, wip_flow_schcdulc_numbcr, wip_job wipjob_name, wip_line_code, wip_line_id, wip_resource_id, wip_resource_name, wip_resource_seq_num, pa_employee_num, pa_employee_name, pa_supplier_num, pa_supplier_name, pa_project_num, pa_project

54、_name, pa_task_num, pa_task_name, pa_ei_ev_org, pa_nl_rcsourcc_org, pa_nl_rcsourcc, pa_ci_cv_typc, pa_cvcnt_num, pa_quantity, pa_uom, pa_period_date, pa_gl_transfer_date, pa_customer_number, pa_customer_name, pa_agreement_num,pa_accrue_thru_date, pa_dri_number, pa_trans_id, pa_line_number, prvdr_org

55、anization_namc, rccvr_organization_namc, prvdr_ou_namc, recvr_ou_name5 cc_type_name, cc_proc_method_name, application_id, asset_id, asset_category_id,asset_key_ccid, code_combination_id, inventory_item_id, set_of_booksorg_id, trx_class, trx_type_n, trx_type_c, trx_numbcr_n, trx_numbcr_c, doc_scqucnc

56、c_id, trx_hdr_tablc, trx_hdr_id, acctine_type, currency_conversion_type, third_party_type, third_party_id, third_party_sub_id, gl_transfer_status, source_table, source_id, ael_table, ael_id, aeh_accounting_error_code, ael_accounting_error_code, je_category, trxinc_typc, trx_sourcc_id, last_updatc_da

57、tc, last_updatcd_by, creation_date5 created_by, last_update_login, requested, program_application_id, programjd, program_update_date, pa_project_id, pa_view_labor_costfrom xla_ap_inv_ael_gl_v xla_ael_gl_vwhere jc_hcadcr_id = 1512and je_line_num = 2080order by trx_hdr_id, accounting_event_number, acc

58、ounting_line_number;從接收追溯到接收事務(wù)sqlselect row_id, crcation_datc, crcatcd_by, last_updatc_datc, last_updatcd_by, last_update_login, note_to_receiver, pll_note_to_receiver, rma_reference, transaction_id, interface_transaction_id, displayed_trans_type, transaction_type, transact_qty, transact_uom, transaction_date, item_rev9 displayed_dest_type, deliver_toocation, delivcr_to_pcrson, subinventory, rcccipt_num, displaycd_sourcc_typc, order_num, po_release5 order_line5 po_shipment, supplier, country_of_origin5 supplier_site, source_doc_qty, source_doc_uom, vendor_item_nu