ERRO à
OM_TAX_UTIL NA ORDEM DE VENDA
Ver o Setup da classificação Fiscal do Item deve estar em
branco
quinta-feira, 22 de junho de 2017
sexta-feira, 18 de novembro de 2016
ORA-12638: Falha na recuperação de credencial
ORA-12638: Falha na recuperação de credencial
Esse erro e simples veja abaixo como solucionar.
Alterar no arquivo sqlnet.ora o parâmetro
“SQLNET.AUTHENTICATION_SERVICES= (NTS)” para “SQLNET.AUTHENTICATION_SERVICES=
(NONE)”
O caminho deste arquivo depente da instalação do oracle, e no meu caso
está no seguinte:
E:\oracle\product\10.2.0\db_1\network\ADMIN
O importante é saber que geralmente fica dentro da pasta
“NETWORK\ADMIN”.
sexta-feira, 19 de fevereiro de 2010
Alternativa de excluir uma Ordem de Compra Incompleta
Antes da aprovação do Pedido de Compra e quando a PO esta no estado INCOMPLETO o Oracle nos permite excluir o formulário de Pedido de Compra.
O mesmo pode ser efetuado usando a API mencionada abaixo, Mas precisamos nos lembrar de alguns pontos antes de utilizar esta API para excluir a PO incompleta.
--=================================================
DECLARE
l_deleted BOOLEAN;
CURSOR c_po_det IS
SELECT po_header_id, segment1,org_idFROM po.po_headers_all phaWHERE 1 = 1AND segment1 = '11170002356'AND org_id = 308AND NVL (approved_flag, 'N') <> 'Y'AND NVL (cancel_flag, 'N') = 'N'AND type_lookup_code = 'STANDARD'AND authorization_status = 'INCOMPLETE'AND NOT EXISTS ( SELECT 1FROM po_headers_archive_all aWHERE pha.po_header_id = a.po_header_idAND pha.org_id = a.org_id)AND NOT EXISTS ( SELECT 1FROM mtl_supply b WHERE pha.po_header_id = b.po_header_id ANDsupply_type_code = 'PO');
BEGIN
FOR c1 IN c_po_det LOOP
DBMS_OUTPUT.put_line ( 'Calling PO_HEADERS_SV1 API To Delete PO');DBMS_OUTPUT.put_line ( '==========================');
l_deleted := po_headers_sv1.delete_po (c1.po_header_id, 'STANDARD');
IF l_deleted = TRUETHENCOMMIT;DBMS_OUTPUT.put_line ( 'Successfully Deleted the PO');ELSEROLLBACK;DBMS_OUTPUT.put_line ( 'Failed to Delete the PO');END IF;END LOOP;
END;
O mesmo pode ser efetuado usando a API mencionada abaixo, Mas precisamos nos lembrar de alguns pontos antes de utilizar esta API para excluir a PO incompleta.
--=================================================
DECLARE
l_deleted BOOLEAN;
CURSOR c_po_det IS
SELECT po_header_id, segment1,org_idFROM po.po_headers_all phaWHERE 1 = 1AND segment1 = '11170002356'AND org_id = 308AND NVL (approved_flag, 'N') <> 'Y'AND NVL (cancel_flag, 'N') = 'N'AND type_lookup_code = 'STANDARD'AND authorization_status = 'INCOMPLETE'AND NOT EXISTS ( SELECT 1FROM po_headers_archive_all aWHERE pha.po_header_id = a.po_header_idAND pha.org_id = a.org_id)AND NOT EXISTS ( SELECT 1FROM mtl_supply b WHERE pha.po_header_id = b.po_header_id ANDsupply_type_code = 'PO');
BEGIN
FOR c1 IN c_po_det LOOP
DBMS_OUTPUT.put_line ( 'Calling PO_HEADERS_SV1 API To Delete PO');DBMS_OUTPUT.put_line ( '==========================');
l_deleted := po_headers_sv1.delete_po (c1.po_header_id, 'STANDARD');
IF l_deleted = TRUETHENCOMMIT;DBMS_OUTPUT.put_line ( 'Successfully Deleted the PO');ELSEROLLBACK;DBMS_OUTPUT.put_line ( 'Failed to Delete the PO');END IF;END LOOP;
END;
domingo, 3 de janeiro de 2010
PO com AP
Olá vou postar uma query muito util com o ralecionamento correto do AP com PO muito util para verificar as ordem de compras com seus pagamentos.
SELECTa.org_id "ORG ID",E.SEGMENT1 "VENDOR NUM",e.vendor_name "SUPPLIER NAME",UPPER(e.vendor_type_lookup_code) "VENDOR TYPE",f.vendor_site_code "VENDOR SITE CODE",f.ADDRESS_LINE1 "ADDRESS",f.city "CITY",f.country "COUNTRY",to_char(trunc(d.CREATION_DATE)) "PO Date",d.segment1 "PO NUM",d.type_lookup_code "PO Type",c.quantity_ordered "QTY ORDERED",c.quantity_cancelled "QTY CANCELLED",g.item_id "ITEM ID",g.item_description "ITEM DESCRIPTION",g.unit_price "UNIT PRICE",(NVL(c.quantity_ordered,0)-NVL(c.quantity_cancelled,0))*NVL(g.unit_price,0) "PO Line Amount",(select decode(ph.approved_FLAG, 'Y', 'Approved') from po.po_headers_all ph where ph.po_header_ID = d.po_header_id) "PO Approved?", a.invoice_type_lookup_code "INVOICE TYPE",a.invoice_amount "INVOICE AMOUNT",to_char(trunc(a.INVOICE_DATE)) "INVOICE DATE",a.invoice_num "INVOICE NUMBER",(select decode(x.MATCH_STATUS_FLAG, 'A', 'Approved') from ap.ap_invoice_distributions_all x wherex.invoice_distribution_id = b.invoice_distribution_id) "Invoice Approved?",a.amount_paid,h.amount,h.check_id,h.invoice_payment_id "Payment Id",i.check_number "Cheque Number",to_char(trunc(i.check_DATE)) "Payment Date"FROMAP.AP_INVOICES_ALL A,AP.AP_INVOICE_DISTRIBUTIONS_ALL B,PO.PO_DISTRIBUTIONS_ALL C,PO.PO_HEADERS_ALL D,PO.PO_VENDORS E,PO.PO_VENDOR_SITES_ALL F,PO.PO_LINES_ALL G,AP.AP_INVOICE_PAYMENTS_ALL H,AP.AP_CHECKS_ALL IWHEREa.invoice_id = b.invoice_id andb.po_distribution_id = c. po_distribution_id (+) andc.po_header_id = d.po_header_id (+) ande.vendor_id (+) = d.VENDOR_ID andf.vendor_site_id (+) = d.vendor_site_id andd.po_header_id = g.po_header_id andc.po_line_id = g.po_line_id anda.invoice_id = h.invoice_id andh.check_id = i.check_id andf.vendor_site_id = i.vendor_site_id andc.PO_HEADER_ID is not null anda.payment_status_flag = 'Y' andd.type_lookup_code != 'BLANKET'
SELECTa.org_id "ORG ID",E.SEGMENT1 "VENDOR NUM",e.vendor_name "SUPPLIER NAME",UPPER(e.vendor_type_lookup_code) "VENDOR TYPE",f.vendor_site_code "VENDOR SITE CODE",f.ADDRESS_LINE1 "ADDRESS",f.city "CITY",f.country "COUNTRY",to_char(trunc(d.CREATION_DATE)) "PO Date",d.segment1 "PO NUM",d.type_lookup_code "PO Type",c.quantity_ordered "QTY ORDERED",c.quantity_cancelled "QTY CANCELLED",g.item_id "ITEM ID",g.item_description "ITEM DESCRIPTION",g.unit_price "UNIT PRICE",(NVL(c.quantity_ordered,0)-NVL(c.quantity_cancelled,0))*NVL(g.unit_price,0) "PO Line Amount",(select decode(ph.approved_FLAG, 'Y', 'Approved') from po.po_headers_all ph where ph.po_header_ID = d.po_header_id) "PO Approved?", a.invoice_type_lookup_code "INVOICE TYPE",a.invoice_amount "INVOICE AMOUNT",to_char(trunc(a.INVOICE_DATE)) "INVOICE DATE",a.invoice_num "INVOICE NUMBER",(select decode(x.MATCH_STATUS_FLAG, 'A', 'Approved') from ap.ap_invoice_distributions_all x wherex.invoice_distribution_id = b.invoice_distribution_id) "Invoice Approved?",a.amount_paid,h.amount,h.check_id,h.invoice_payment_id "Payment Id",i.check_number "Cheque Number",to_char(trunc(i.check_DATE)) "Payment Date"FROMAP.AP_INVOICES_ALL A,AP.AP_INVOICE_DISTRIBUTIONS_ALL B,PO.PO_DISTRIBUTIONS_ALL C,PO.PO_HEADERS_ALL D,PO.PO_VENDORS E,PO.PO_VENDOR_SITES_ALL F,PO.PO_LINES_ALL G,AP.AP_INVOICE_PAYMENTS_ALL H,AP.AP_CHECKS_ALL IWHEREa.invoice_id = b.invoice_id andb.po_distribution_id = c. po_distribution_id (+) andc.po_header_id = d.po_header_id (+) ande.vendor_id (+) = d.VENDOR_ID andf.vendor_site_id (+) = d.vendor_site_id andd.po_header_id = g.po_header_id andc.po_line_id = g.po_line_id anda.invoice_id = h.invoice_id andh.check_id = i.check_id andf.vendor_site_id = i.vendor_site_id andc.PO_HEADER_ID is not null anda.payment_status_flag = 'Y' andd.type_lookup_code != 'BLANKET'
sexta-feira, 23 de outubro de 2009
Querys Uteis - PO (Oracle Purchasing) - Parte 2
Todas as POs com aprovação, NFF, e Detalhes do Pagamento:
SELECT,a.org_id "ORG ID",E.SEGMENT1 "VENDOR NUM",e.vendor_name "SUPPLIER NAME",UPPER(e.vendor_type_lookup_code) "VENDOR TYPE",f.vendor_site_code "VENDOR SITE CODE",f.ADDRESS_LINE1 "ADDRESS",f.city "CITY",f.country "COUNTRY",to_char(trunc(d.CREATION_DATE)) "PO Date",d.segment1 "PO NUM",d.type_lookup_code "PO Type",c.quantity_ordered "QTY ORDERED",c.quantity_cancelled "QTY CANCELLED",g.item_id "ITEM ID",g.item_description "ITEM DESCRIPTION",g.unit_price "UNIT PRICE",(NVL(c.quantity_ordered,0)-NVL(c.quantity_cancelled,0))*NVL(g.unit_price,0) "PO Line Amount",(select decode(ph.approved_FLAG, 'Y', 'Approved') from po.po_headers_all ph where,ph.po_header_ID = d.po_header_id) "PO Approved?", a.invoice_type_lookup_code "INVOICE TYPE",a.invoice_amount "INVOICE AMOUNT",to_char(trunc(a.INVOICE_DATE)) "INVOICE DATE",a.invoice_num "INVOICE NUMBER",(select decode(x.MATCH_STATUS_FLAG, 'A', 'Approved') from ap.ap_invoice_distributions_all x where,x.invoice_distribution_id = b.invoice_distribution_id) "Invoice Approved?",a.amount_paid,h.amount,h.check_id,h.invoice_payment_id "Payment Id",i.check_number "Cheque Number",to_char(trunc(i.check_DATE)) "Payment Date"FROM,AP.AP_INVOICES_ALL A,AP.AP_INVOICE_DISTRIBUTIONS_ALL B,PO.PO_DISTRIBUTIONS_ALL C,PO.PO_HEADERS_ALL D,PO.PO_VENDORS E,PO.PO_VENDOR_SITES_ALL F,PO.PO_LINES_ALL G,AP.AP_INVOICE_PAYMENTS_ALL H,AP.AP_CHECKS_ALL IWHERE,a.invoice_id = b.invoice_id andb.po_distribution_id = c. po_distribution_id (+) andc.po_header_id = d.po_header_id (+) ande.vendor_id (+) = d.VENDOR_ID andf.vendor_site_id (+) = d.vendor_site_id andd.po_header_id = g.po_header_id andc.po_line_id = g.po_line_id anda.invoice_id = h.invoice_id andh.check_id = i.check_id andf.vendor_site_id = i.vendor_site_id andc.PO_HEADER_ID is not null anda.payment_status_flag = 'Y' andd.type_lookup_code != 'BLANKET'
****************************
Todas as POs Aberta:
SELECT,h.segment1 "PO NUM",h.authorization_status "STATUS",l.line_num "SEQ NUM",ll.line_location_id,d.po_distribution_id,h.type_lookup_code "TYPE"FROM,po.po_headers_all h,po.po_lines_all l,po.po_line_locations_all ll,po.po_distributions_all dWHEREh.po_header_id = l.po_header_id andll.po_line_id = l.po_Line_id andll.line_location_id = d.line_location_id andh.closed_date is NULL andh.type_lookup_code not in ('QUOTATION')
*************
Etapas para depuração A ORDEM DE COMPRA pegue po_header_id primeiro e execute cada consulta em seguida analisar os dados.
Para melhor compreenção esta parcelado em 5 fases principais:
Stage1: PO CreationPO_HEADERS_ALLselect po_header_id from po_headers_all where segment1 =;select * from po_headers_all where po_header_id =;PO_LINES_ALLselect * from po_lines_all where po_header_id =;PO_LINE_LOCATIONS_ALLselect * from po_line_locations_all where po_header_id =;PO_DISTRIBUTIONS_ALL select * from po_distributions_all where po_header_id =;PO_RELEASES_ALLSELECT * FROM po_releases_all WHERE po_header_id =;
Stage 2: Once PO is received, data is moved to respective receving tables and inventory tablesRCV_SHIPMENT_HEADERSselect * from rcv_shipment_headers where shipment_header_id in(select shipment_header_id from rcv_shipment_lineswhere po_header_id =);RCV_SHIPMENT_LINESselect * from rcv_shipment_lines where po_header_id =;RCV_TRANSACTIONSselect * from rcv_transactions where po_header_id =;RCV_ACCOUNTING_EVENTSSELECT * FROM rcv_Accounting_Events WHERE rcv_transaction_id IN(select transaction_id from rcv_transactionswhere po_header_id =);RCV_RECEIVING_SUB_LEDGERselect * from rcv_receiving_sub_ledger where rcv_transaction_id IN(select transaction_id from rcv_transactions where po_header_id =);RCV_SUB_LEDGER_DETAILSselect * from rcv_sub_ledger_detailswhere rcv_transaction_id IN(select transaction_id from rcv_transactions where po_header_id =);MTL_MATERIAL_TRANSACTIONSselect * from mtl_material_transactions where transaction_source_id =;MTL_TRANSACTION_ACCOUNTS select * from mtl_transaction_accounts where transaction_id IN(select transaction_id from mtl_material_transactions where transaction_source_id =);
Stage 3: Invoicing detailsAP_INVOICE_DISTRIBUTIONS_ALLselect * from ap_invoice_distributions_all where po_distribution_id in( select po_distribution_id from po_distributions_all where po_header_id =);AP_INVOICES_ALLselect * from ap_invoices_all where invoice_id in(select invoice_id from ap_invoice_distributions_all where po_distribution_id in( select po_distribution_id from po_distributions_all where po_header_id =));
Stage 4 : Mostly there is tie up with Project related POPA_EXPENDITURE_ITEMS_ALL select * from pa_expenditure_items_all peia where peia.orig_transaction_reference IN(select to_char(transaction_id) from mtl_material_transactions where transaction_source_id = );
Stage 5 : General LedgerPrompt 17. GL_BC_PACKETS ..This is for encumbrancesSELECT * FROM gl_bc_packets WHERE reference2 IN (’‘);GL_INTERFACE select * from gl_interface gli where user_je_source_name =’Purchasing’and gl_sl_link_table =’RSL’ and reference21=’PO’ and exists(select 1 from rcv_receiving_sub_ledger rrsl where gli.reference22 =RRSL.reference2 and GLI.reference23 =RRSL.reference3AND GLI.reference24 =RRSL.reference4AND RRSL.rcv_transaction_id in(select transaction_id from rcv_transactionswhere po_header_id ));GL_IMPORT_REFERENCES SELECT *FROM gl_import_references GLIR WHERE reference_1=’PO’ AND gl_sl_link_table =’RSL’AND EXISTS( SELECT 1 FROM rcv_receiving_sub_ledger RRSLWHERE GLIR.reference_2 =RRSL.reference2AND GLIR.reference_3 =RRSL.reference3 AND GLIR.reference_4 =RRSL.reference4AND RRSL.rcv_transaction_id in(select transaction_id from rcv_transactions where po_header_id =))
SELECT,a.org_id "ORG ID",E.SEGMENT1 "VENDOR NUM",e.vendor_name "SUPPLIER NAME",UPPER(e.vendor_type_lookup_code) "VENDOR TYPE",f.vendor_site_code "VENDOR SITE CODE",f.ADDRESS_LINE1 "ADDRESS",f.city "CITY",f.country "COUNTRY",to_char(trunc(d.CREATION_DATE)) "PO Date",d.segment1 "PO NUM",d.type_lookup_code "PO Type",c.quantity_ordered "QTY ORDERED",c.quantity_cancelled "QTY CANCELLED",g.item_id "ITEM ID",g.item_description "ITEM DESCRIPTION",g.unit_price "UNIT PRICE",(NVL(c.quantity_ordered,0)-NVL(c.quantity_cancelled,0))*NVL(g.unit_price,0) "PO Line Amount",(select decode(ph.approved_FLAG, 'Y', 'Approved') from po.po_headers_all ph where,ph.po_header_ID = d.po_header_id) "PO Approved?", a.invoice_type_lookup_code "INVOICE TYPE",a.invoice_amount "INVOICE AMOUNT",to_char(trunc(a.INVOICE_DATE)) "INVOICE DATE",a.invoice_num "INVOICE NUMBER",(select decode(x.MATCH_STATUS_FLAG, 'A', 'Approved') from ap.ap_invoice_distributions_all x where,x.invoice_distribution_id = b.invoice_distribution_id) "Invoice Approved?",a.amount_paid,h.amount,h.check_id,h.invoice_payment_id "Payment Id",i.check_number "Cheque Number",to_char(trunc(i.check_DATE)) "Payment Date"FROM,AP.AP_INVOICES_ALL A,AP.AP_INVOICE_DISTRIBUTIONS_ALL B,PO.PO_DISTRIBUTIONS_ALL C,PO.PO_HEADERS_ALL D,PO.PO_VENDORS E,PO.PO_VENDOR_SITES_ALL F,PO.PO_LINES_ALL G,AP.AP_INVOICE_PAYMENTS_ALL H,AP.AP_CHECKS_ALL IWHERE,a.invoice_id = b.invoice_id andb.po_distribution_id = c. po_distribution_id (+) andc.po_header_id = d.po_header_id (+) ande.vendor_id (+) = d.VENDOR_ID andf.vendor_site_id (+) = d.vendor_site_id andd.po_header_id = g.po_header_id andc.po_line_id = g.po_line_id anda.invoice_id = h.invoice_id andh.check_id = i.check_id andf.vendor_site_id = i.vendor_site_id andc.PO_HEADER_ID is not null anda.payment_status_flag = 'Y' andd.type_lookup_code != 'BLANKET'
****************************
Todas as POs Aberta:
SELECT,h.segment1 "PO NUM",h.authorization_status "STATUS",l.line_num "SEQ NUM",ll.line_location_id,d.po_distribution_id,h.type_lookup_code "TYPE"FROM,po.po_headers_all h,po.po_lines_all l,po.po_line_locations_all ll,po.po_distributions_all dWHEREh.po_header_id = l.po_header_id andll.po_line_id = l.po_Line_id andll.line_location_id = d.line_location_id andh.closed_date is NULL andh.type_lookup_code not in ('QUOTATION')
*************
Etapas para depuração A ORDEM DE COMPRA pegue po_header_id primeiro e execute cada consulta em seguida analisar os dados.
Para melhor compreenção esta parcelado em 5 fases principais:
Stage1: PO CreationPO_HEADERS_ALLselect po_header_id from po_headers_all where segment1 =;select * from po_headers_all where po_header_id =;PO_LINES_ALLselect * from po_lines_all where po_header_id =;PO_LINE_LOCATIONS_ALLselect * from po_line_locations_all where po_header_id =;PO_DISTRIBUTIONS_ALL select * from po_distributions_all where po_header_id =;PO_RELEASES_ALLSELECT * FROM po_releases_all WHERE po_header_id =;
Stage 2: Once PO is received, data is moved to respective receving tables and inventory tablesRCV_SHIPMENT_HEADERSselect * from rcv_shipment_headers where shipment_header_id in(select shipment_header_id from rcv_shipment_lineswhere po_header_id =);RCV_SHIPMENT_LINESselect * from rcv_shipment_lines where po_header_id =;RCV_TRANSACTIONSselect * from rcv_transactions where po_header_id =;RCV_ACCOUNTING_EVENTSSELECT * FROM rcv_Accounting_Events WHERE rcv_transaction_id IN(select transaction_id from rcv_transactionswhere po_header_id =);RCV_RECEIVING_SUB_LEDGERselect * from rcv_receiving_sub_ledger where rcv_transaction_id IN(select transaction_id from rcv_transactions where po_header_id =);RCV_SUB_LEDGER_DETAILSselect * from rcv_sub_ledger_detailswhere rcv_transaction_id IN(select transaction_id from rcv_transactions where po_header_id =);MTL_MATERIAL_TRANSACTIONSselect * from mtl_material_transactions where transaction_source_id =;MTL_TRANSACTION_ACCOUNTS select * from mtl_transaction_accounts where transaction_id IN(select transaction_id from mtl_material_transactions where transaction_source_id =);
Stage 3: Invoicing detailsAP_INVOICE_DISTRIBUTIONS_ALLselect * from ap_invoice_distributions_all where po_distribution_id in( select po_distribution_id from po_distributions_all where po_header_id =);AP_INVOICES_ALLselect * from ap_invoices_all where invoice_id in(select invoice_id from ap_invoice_distributions_all where po_distribution_id in( select po_distribution_id from po_distributions_all where po_header_id =));
Stage 4 : Mostly there is tie up with Project related POPA_EXPENDITURE_ITEMS_ALL select * from pa_expenditure_items_all peia where peia.orig_transaction_reference IN(select to_char(transaction_id) from mtl_material_transactions where transaction_source_id = );
Stage 5 : General LedgerPrompt 17. GL_BC_PACKETS ..This is for encumbrancesSELECT * FROM gl_bc_packets WHERE reference2 IN (’‘);GL_INTERFACE select * from gl_interface gli where user_je_source_name =’Purchasing’and gl_sl_link_table =’RSL’ and reference21=’PO’ and exists(select 1 from rcv_receiving_sub_ledger rrsl where gli.reference22 =RRSL.reference2 and GLI.reference23 =RRSL.reference3AND GLI.reference24 =RRSL.reference4AND RRSL.rcv_transaction_id in(select transaction_id from rcv_transactionswhere po_header_id ));GL_IMPORT_REFERENCES SELECT *FROM gl_import_references GLIR WHERE reference_1=’PO’ AND gl_sl_link_table =’RSL’AND EXISTS( SELECT 1 FROM rcv_receiving_sub_ledger RRSLWHERE GLIR.reference_2 =RRSL.reference2AND GLIR.reference_3 =RRSL.reference3 AND GLIR.reference_4 =RRSL.reference4AND RRSL.rcv_transaction_id in(select transaction_id from rcv_transactions where po_header_id =))
terça-feira, 20 de outubro de 2009
Querys Uteis - PO (Oracle Purchasing) - Parte 1
No primeiro arquivo irei falar sobre querys uteis para o Modulo PO (Oracle Purchasing).
Essas consultas são utei para novos Relatorios ou Customiozações a serem feitas no modulo de PO.
1 - Requisições Canceladas:
SELECT,prh.REQUISITION_HEADER_ID,prh.PREPARER_ID,prh.SEGMENT1 "REQ NUM",trunc(prh.CREATION_DATE),prh.DESCRIPTION,prh.NOTE_TO_AUTHORIZERFROM,apps.Po_Requisition_headers_all prh,apps.po_action_history pahWHEREaction_code='CANCEL' andpah.object_type_code='REQUISITION' andpah.object_id=prh.REQUISITION_HEADER_ID
2 - Requisições internas que não possui uma ordem de venda interna.
SELECT,RQH.SEGMENT1,RQL.LINE_NUM,RQL.REQUISITION_HEADER_ID,RQL.REQUISITION_LINE_ID,RQL.ITEM_ID,RQL.UNIT_MEAS_LOOKUP_CODE,RQL.UNIT_PRICE,RQL.QUANTITY,RQL.QUANTITY_CANCELLED,RQL.QUANTITY_DELIVERED,RQL.CANCEL_FLAG,RQL.SOURCE_TYPE_CODE,RQL.SOURCE_ORGANIZATION_ID,RQL.DESTINATION_ORGANIZATION_ID,RQH.TRANSFERRED_TO_OE_FLAGFROM,PO_REQUISITION_LINES_ALL RQL,PO_REQUISITION_HEADERS_ALL RQHWHERERQL.REQUISITION_HEADER_ID = RQH.REQUISITION_HEADER_ID andRQL.SOURCE_TYPE_CODE = 'INVENTORY' andRQL.SOURCE_ORGANIZATION_ID is not null and not exists(select 'existing internal order'from OE_ORDER_LINES_ALL LIN where LIN.SOURCE_DOCUMENT_LINE_ID = RQL.REQUISITION_LINE_ID and LIN.SOURCE_DOCUMENT_TYPE_ID = 10)ORDER BY RQH.REQUISITION_HEADER_ID, RQL.LINE_NUM
3 - Requisição relacionada com Ordem de Compra
SELECT,r.segment1 "Req Num",p.segment1 "PO Num"from,po_headers_all p,po_distributions_all d,po_req_distributions_all rd,po_requisition_lines_all rl,po_requisition_headers_all rWHEREp.po_header_id = d.po_header_id andd.req_distribution_id = rd.distribution_id andrd.requisition_line_id = rl.requisition_line_id andrl.requisition_header_id = r.requisition_header_id
4 - Requisição de compra sem uma ordem de compra
SELECT,prh.segment1 "PR NUM",trunc(prh.creation_date) "CREATED ON",trunc(prl.creation_date) "Line Creation Date",prl.line_num "Seq #",msi.segment1 "Item Num",prl.item_description "Description",prl.quantity "Qty",trunc(prl.need_by_date) "Required By",ppf1.full_name "REQUESTOR",ppf2.agent_name "BUYER"FROM,po.po_requisition_headers_all prh,po.po_requisition_lines_all prl,apps.per_people_f ppf1,(select distinct agent_id,agent_name from apps.po_agents_v ) ppf2,po.po_req_distributions_all prd,inv.mtl_system_items_b msi,po.po_line_locations_all pll,po.po_lines_all pl,po.po_headers_all phWHEREprh.requisition_header_id = prl.requisition_header_id andprl.requisition_line_id = prd.requisition_line_id andppf1.person_id = prh.preparer_id andprh.creation_date between ppf1.effective_start_date and ppf1.effective_end_date andppf2.agent_id(+) = msi.buyer_id andmsi.inventory_item_id = prl.item_id andmsi.organization_id = prl.destination_organization_id andpll.line_location_id(+) = prl.line_location_id andpll.po_header_id = ph.po_header_id(+) andpll.pl_line_id = pl.po_line_id(+) andprh.authorization_status = 'APPROVED' andpll.line_location_id is NULL andprl.closed_code is NULL andnvl(prl.cancel_flag,'N') <> 'Y'ORDER BY 1,2
Douglas - MadMax
Essas consultas são utei para novos Relatorios ou Customiozações a serem feitas no modulo de PO.
1 - Requisições Canceladas:
SELECT,prh.REQUISITION_HEADER_ID,prh.PREPARER_ID,prh.SEGMENT1 "REQ NUM",trunc(prh.CREATION_DATE),prh.DESCRIPTION,prh.NOTE_TO_AUTHORIZERFROM,apps.Po_Requisition_headers_all prh,apps.po_action_history pahWHEREaction_code='CANCEL' andpah.object_type_code='REQUISITION' andpah.object_id=prh.REQUISITION_HEADER_ID
2 - Requisições internas que não possui uma ordem de venda interna.
SELECT,RQH.SEGMENT1,RQL.LINE_NUM,RQL.REQUISITION_HEADER_ID,RQL.REQUISITION_LINE_ID,RQL.ITEM_ID,RQL.UNIT_MEAS_LOOKUP_CODE,RQL.UNIT_PRICE,RQL.QUANTITY,RQL.QUANTITY_CANCELLED,RQL.QUANTITY_DELIVERED,RQL.CANCEL_FLAG,RQL.SOURCE_TYPE_CODE,RQL.SOURCE_ORGANIZATION_ID,RQL.DESTINATION_ORGANIZATION_ID,RQH.TRANSFERRED_TO_OE_FLAGFROM,PO_REQUISITION_LINES_ALL RQL,PO_REQUISITION_HEADERS_ALL RQHWHERERQL.REQUISITION_HEADER_ID = RQH.REQUISITION_HEADER_ID andRQL.SOURCE_TYPE_CODE = 'INVENTORY' andRQL.SOURCE_ORGANIZATION_ID is not null and not exists(select 'existing internal order'from OE_ORDER_LINES_ALL LIN where LIN.SOURCE_DOCUMENT_LINE_ID = RQL.REQUISITION_LINE_ID and LIN.SOURCE_DOCUMENT_TYPE_ID = 10)ORDER BY RQH.REQUISITION_HEADER_ID, RQL.LINE_NUM
3 - Requisição relacionada com Ordem de Compra
SELECT,r.segment1 "Req Num",p.segment1 "PO Num"from,po_headers_all p,po_distributions_all d,po_req_distributions_all rd,po_requisition_lines_all rl,po_requisition_headers_all rWHEREp.po_header_id = d.po_header_id andd.req_distribution_id = rd.distribution_id andrd.requisition_line_id = rl.requisition_line_id andrl.requisition_header_id = r.requisition_header_id
4 - Requisição de compra sem uma ordem de compra
SELECT,prh.segment1 "PR NUM",trunc(prh.creation_date) "CREATED ON",trunc(prl.creation_date) "Line Creation Date",prl.line_num "Seq #",msi.segment1 "Item Num",prl.item_description "Description",prl.quantity "Qty",trunc(prl.need_by_date) "Required By",ppf1.full_name "REQUESTOR",ppf2.agent_name "BUYER"FROM,po.po_requisition_headers_all prh,po.po_requisition_lines_all prl,apps.per_people_f ppf1,(select distinct agent_id,agent_name from apps.po_agents_v ) ppf2,po.po_req_distributions_all prd,inv.mtl_system_items_b msi,po.po_line_locations_all pll,po.po_lines_all pl,po.po_headers_all phWHEREprh.requisition_header_id = prl.requisition_header_id andprl.requisition_line_id = prd.requisition_line_id andppf1.person_id = prh.preparer_id andprh.creation_date between ppf1.effective_start_date and ppf1.effective_end_date andppf2.agent_id(+) = msi.buyer_id andmsi.inventory_item_id = prl.item_id andmsi.organization_id = prl.destination_organization_id andpll.line_location_id(+) = prl.line_location_id andpll.po_header_id = ph.po_header_id(+) andpll.pl_line_id = pl.po_line_id(+) andprh.authorization_status = 'APPROVED' andpll.line_location_id is NULL andprl.closed_code is NULL andnvl(prl.cancel_flag,'N') <> 'Y'ORDER BY 1,2
Douglas - MadMax
Iniciando o Blog
Olá sou Analista Funcional Tecnico Oracle EBS r11 / r12 e neste blog irei passar um pouco do meu conhecimento a quem interessar.
Assinar:
Postagens (Atom)